<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Auth;
use App\Core\Database;

final class PortalData
{
    private static function scope(): array
    {
        $tid = Auth::instance()->tenantId();
        $aid = Auth::instance()->accountId();
        if (!$tid || !$aid) {
            throw new \RuntimeException('No customer session.');
        }
        return ['tid' => $tid, 'aid' => $aid];
    }

    public static function invoices(): array
    {
        return Database::instance()->fetchAll(
            'SELECT id, number, title, amount, paid_amount, status, due_date, paid_at, file_name
             FROM invoices WHERE tenant_id = :tid AND account_id = :aid
             ORDER BY created_at DESC',
            self::scope()
        );
    }

    public static function invoice(int $id): ?array
    {
        return Database::instance()->fetch(
            'SELECT id, number, title, file_name
             FROM invoices WHERE id = :id AND tenant_id = :tid AND account_id = :aid',
            self::scope() + ['id' => $id]
        );
    }

    /** @return list<string> */
    public static function documentTabs(): array
    {
        return ['quotes', 'contracts', 'files'];
    }

    private static function documentTabClause(string $tab): string
    {
        $quote = "(d.related_type = 'quote' OR d.kind = 'quote')";
        $contract = "(d.related_type = 'contract' OR d.kind IN ('contract','esign'))";
        return match ($tab) {
            'quotes' => $quote,
            'contracts' => "($contract AND NOT $quote)",
            default => "(NOT $quote AND NOT $contract)",
        };
    }

    public static function documents(string $tab = 'quotes'): array
    {
        $tab = in_array($tab, self::documentTabs(), true) ? $tab : 'quotes';
        $clause = self::documentTabClause($tab);
        return Database::instance()->fetchAll(
            "SELECT d.id, d.title, d.kind, d.body, d.file_name, d.created_at, s.status AS sign_status, s.signed_at, s.signed_file_name, s.signer_name
             FROM portal_documents d
             LEFT JOIN signatures s ON s.document_id = d.id
               AND s.id = (SELECT MAX(s2.id) FROM signatures s2 WHERE s2.document_id = d.id)
             WHERE d.tenant_id = :tid AND d.account_id = :aid AND d.shared = 1 AND $clause
             ORDER BY d.created_at DESC",
            self::scope()
        );
    }

    /** @return array{quotes: int, contracts: int, files: int} */
    public static function documentTabCounts(): array
    {
        $quote = "(related_type = 'quote' OR kind = 'quote')";
        $contract = "(related_type = 'contract' OR kind IN ('contract','esign'))";
        $row = Database::instance()->fetch(
            "SELECT
                COALESCE(SUM($quote), 0) AS quotes,
                COALESCE(SUM(($contract) AND NOT ($quote)), 0) AS contracts,
                COALESCE(SUM(NOT ($quote) AND NOT ($contract)), 0) AS files
             FROM portal_documents
             WHERE tenant_id = :tid AND account_id = :aid AND shared = 1",
            self::scope()
        );
        return [
            'quotes' => (int) ($row['quotes'] ?? 0),
            'contracts' => (int) ($row['contracts'] ?? 0),
            'files' => (int) ($row['files'] ?? 0),
        ];
    }

    public static function document(int $id): ?array
    {
        $p = self::scope();
        $p['id'] = $id;
        return Database::instance()->fetch(
            'SELECT d.*, s.id AS signature_id, s.status AS sign_status, s.signer_name, s.signed_at, s.signed_file_name
             FROM portal_documents d
             LEFT JOIN signatures s ON s.document_id = d.id
               AND s.id = (SELECT MAX(s2.id) FROM signatures s2 WHERE s2.document_id = d.id)
             WHERE d.id = :id AND d.tenant_id = :tid AND d.account_id = :aid AND d.shared = 1',
            $p
        );
    }

    public static function pendingSigns(): array
    {
        return Database::instance()->fetchAll(
            "SELECT s.id, s.document_id, d.title, d.body, d.file_name
             FROM signatures s
             INNER JOIN portal_documents d ON d.id = s.document_id AND d.shared = 1
             WHERE s.tenant_id = :tid AND s.account_id = :aid AND s.status = 'pending'
             ORDER BY s.created_at",
            self::scope()
        );
    }

    public static function pendingSign(int $signatureId): ?array
    {
        $p = self::scope();
        $p['id'] = $signatureId;
        return Database::instance()->fetch(
            "SELECT s.id, s.document_id FROM signatures s
             INNER JOIN portal_documents d ON d.id = s.document_id AND d.shared = 1
             WHERE s.id = :id AND s.tenant_id = :tid AND s.account_id = :aid AND s.status = 'pending'",
            $p
        );
    }

    public static function sign(int $signatureId, string $name, string $signedFileName): ?array
    {
        $p = self::scope();
        $row = Database::instance()->fetch(
            "SELECT s.id, s.document_id, d.related_type, d.related_id FROM signatures s
             INNER JOIN portal_documents d ON d.id = s.document_id AND d.shared = 1
             WHERE s.id = :id AND s.tenant_id = :tid AND s.account_id = :aid AND s.status = 'pending'",
            $p + ['id' => $signatureId]
        );
        if (!$row) {
            return null;
        }
        Database::instance()->query(
            "UPDATE signatures SET status = 'signed', signer_name = :n, signed_file_name = :f, signed_at = NOW()
             WHERE id = :id AND tenant_id = :tid AND account_id = :aid",
            $p + ['id' => $signatureId, 'n' => $name, 'f' => $signedFileName]
        );
        if (($row['related_type'] ?? '') === 'quote' && (int) ($row['related_id'] ?? 0) > 0) {
            Database::instance()->query(
                "UPDATE quotes SET status = 'signed'
                 WHERE id = :qid AND tenant_id = :tid AND account_id = :aid
                   AND status IN ('sent','approved')",
                $p + ['qid' => (int) $row['related_id']]
            );
        }
        return $row;
    }

    public static function orders(): array
    {
        return Database::instance()->fetchAll(
            'SELECT number, title, amount, status, ordered_at
             FROM orders WHERE tenant_id = :tid AND account_id = :aid
             ORDER BY ordered_at DESC',
            self::scope()
        );
    }

    public static function contracts(): array
    {
        return Database::instance()->fetchAll(
            'SELECT id, number, title, amount, start_date, end_date, renewal_date, status, file_name, renewed_from_id
             FROM contracts WHERE tenant_id = :tid AND account_id = :aid
             ORDER BY id DESC',
            self::scope()
        );
    }

    public static function contract(int $id): ?array
    {
        return Database::instance()->fetch(
            'SELECT id, number, title, file_name
             FROM contracts WHERE id = :id AND tenant_id = :tid AND account_id = :aid',
            self::scope() + ['id' => $id]
        );
    }

    public static function equipment(): array
    {
        return Database::instance()->fetchAll(
            "SELECT name, model, serial_no, location, status, installed_at
             FROM equipment WHERE tenant_id = :tid AND account_id = :aid AND status = 'active'
             ORDER BY name",
            self::scope()
        );
    }

    public static function jobs(): array
    {
        return Database::instance()->fetchAll(
            "SELECT number, subject, status, source, visit_at, created_at
             FROM service_jobs WHERE tenant_id = :tid AND account_id = :aid
             ORDER BY id DESC LIMIT 20",
            self::scope()
        );
    }

    public static function createRequest(string $subject, string $body, ?int $customerId, string $visitAt): int
    {
        $p = self::scope();
        $tmp = 'tmp-' . bin2hex(random_bytes(4));
        $account = Database::instance()->fetch(
            'SELECT service_assignee_id FROM accounts WHERE id = :aid AND tenant_id = :tid AND deleted_at IS NULL',
            $p
        );
        $assignee = !empty($account['service_assignee_id']) ? (int) $account['service_assignee_id'] : null;
        Database::instance()->query(
            "INSERT INTO service_jobs (tenant_id, account_id, assignee_id, number, subject, body, status, source, visit_at)
             VALUES (:tid, :aid, :sid, :num, :sub, :body, 'open', 'customer', :visit)",
            $p + [
                'sid' => $assignee,
                'num' => $tmp,
                'sub' => $subject,
                'body' => $body,
                'visit' => $visitAt,
            ]
        );
        $id = (int) Database::instance()->lastId();
        $number = 'JOB-' . str_pad((string) $id, 4, '0', STR_PAD_LEFT);
        Database::instance()->query(
            'UPDATE service_jobs SET number = :n WHERE id = :id AND tenant_id = :tid AND account_id = :aid',
            $p + ['n' => $number, 'id' => $id]
        );
        Database::instance()->query(
            'UPDATE accounts SET next_visit_at = :visit, visit_subject = :sub, visit_detail = :det
             WHERE id = :aid AND tenant_id = :tid AND deleted_at IS NULL',
            $p + ['visit' => $visitAt, 'sub' => $subject, 'det' => $body]
        );
        if ($customerId) {
            Database::instance()->query(
                'INSERT INTO job_notes (tenant_id, job_id, customer_user_id, body) VALUES (:tid, :jid, :uid, :body)',
                ['tid' => $p['tid'], 'jid' => $id, 'uid' => $customerId, 'body' => $body]
            );
        }
        return $id;
    }
}
