<?php
declare(strict_types=1);

namespace App\Models;

final class PortalDocument extends BaseModel
{
    protected static string $table = 'portal_documents';

    public static function forAccount(int $accountId, bool $sharedOnly = false): array
    {
        $sql = 'SELECT d.*, s.status AS sign_status, s.signed_at, s.signer_name, 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.tenant_id = :tid AND d.account_id = :aid';
        $params = ['tid' => static::tenantId(), 'aid' => $accountId];
        if ($sharedOnly) {
            $sql .= ' AND d.shared = 1';
        }
        $sql .= ' ORDER BY d.created_at DESC';
        return static::db()->fetchAll($sql, $params);
    }

    public static function findByRelated(string $type, int $id): ?array
    {
        $rows = self::allByRelated($type, $id);
        return $rows[0] ?? null;
    }

    /** @return list<array<string, mixed>> Newest first. */
    public static function allByRelated(string $type, int $id): array
    {
        return static::db()->fetchAll(
            'SELECT * FROM portal_documents WHERE tenant_id = :tid AND related_type = :t AND related_id = :id ORDER BY id DESC',
            ['tid' => static::tenantId(), 't' => $type, 'id' => $id]
        );
    }

    /** Proposal and agreement PDFs attached to a deal. Newest first. */
    public static function papersForOpportunity(int $opportunityId): array
    {
        return static::db()->fetchAll(
            "SELECT * FROM portal_documents
             WHERE tenant_id = :tid AND related_type = 'opportunity' AND related_id = :id
               AND (body IN ('proposal','agreement') OR title LIKE 'Proposal —%' OR title LIKE 'Proposal -%'
                    OR title LIKE 'Agreement —%' OR title LIKE 'Agreement -%')
             ORDER BY id DESC",
            ['tid' => static::tenantId(), 'id' => $opportunityId]
        );
    }

    public static function latestPaper(int $opportunityId, string $kind): ?array
    {
        if (!in_array($kind, ['proposal', 'agreement'], true)) {
            return null;
        }
        $label = $kind === 'agreement' ? 'Agreement' : 'Proposal';
        return static::db()->fetch(
            "SELECT * FROM portal_documents
             WHERE tenant_id = :tid AND related_type = 'opportunity' AND related_id = :id
               AND (body = :kind OR title LIKE :pref OR title LIKE :pref2)
             ORDER BY id DESC LIMIT 1",
            ['tid' => static::tenantId(), 'id' => $opportunityId, 'kind' => $kind, 'pref' => $label . ' —%', 'pref2' => $label . ' -%']
        );
    }

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

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

    public static function tabClause(string $tab): string
    {
        return self::typeClause($tab === 'files' ? 'others' : $tab);
    }

    public static function typeClause(string $type): string
    {
        $quote = "(d.related_type = 'quote' OR d.kind = 'quote')";
        $contract = "(d.related_type = 'contract' OR d.kind IN ('contract','esign'))";
        return match ($type) {
            'quotes' => $quote,
            'contracts' => "($contract AND NOT $quote)",
            'others' => "(NOT $quote AND NOT $contract)",
            default => '1=1',
        };
    }

    public static function paginate(string $type, array $filters, int $page, int $perPage): array
    {
        $type = in_array($type, self::types(), true) ? $type : 'all';
        [$where, $params] = self::listFilters($type, $filters);
        $from = "portal_documents d
             INNER JOIN accounts a ON a.id = d.account_id
             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)
             LEFT JOIN users u ON u.id = d.owner_id";
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM $from WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT d.*, a.name AS account_name, s.status AS sign_status, s.signed_file_name, s.signer_name,
                    CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM $from
             WHERE $where
             ORDER BY d.created_at DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    /** @return array{quotes: int, contracts: int, files: int} */
    public static function tabCounts(?int $ownerId = null): array
    {
        $quote = "(related_type = 'quote' OR kind = 'quote')";
        $contract = "(related_type = 'contract' OR kind IN ('contract','esign'))";
        $where = 'tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $where .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $row = static::db()->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 $where",
            $params
        );
        return [
            'quotes' => (int) ($row['quotes'] ?? 0),
            'contracts' => (int) ($row['contracts'] ?? 0),
            'files' => (int) ($row['files'] ?? 0),
        ];
    }

    /**
     * @param array{q?:string,account_id?:int,signature?:string,created?:string,owner_id?:int|null} $filters
     * @return array{0:string,1:array<string,mixed>}
     */
    private static function listFilters(string $type, array $filters): array
    {
        $where = 'd.tenant_id = :tid AND ' . self::typeClause($type);
        $params = ['tid' => static::tenantId()];
        $ownerId = $filters['owner_id'] ?? null;
        if ($ownerId) {
            $where .= ' AND d.owner_id = :oid';
            $params['oid'] = (int) $ownerId;
        }
        $accountId = (int) ($filters['account_id'] ?? 0);
        if ($accountId > 0) {
            $where .= ' AND d.account_id = :aid';
            $params['aid'] = $accountId;
        }
        $signature = (string) ($filters['signature'] ?? 'all');
        if ($signature === 'pending') {
            $where .= " AND s.status = 'pending'";
        } elseif ($signature === 'signed') {
            $where .= " AND s.status = 'signed'";
        } elseif ($signature === 'declined') {
            $where .= " AND s.status = 'declined'";
        } elseif ($signature === 'none') {
            $where .= ' AND s.id IS NULL';
        }
        $created = (string) ($filters['created'] ?? 'all');
        if ($created === 'today') {
            $where .= ' AND DATE(d.created_at) = CURDATE()';
        } elseif ($created === 'week') {
            $where .= ' AND YEARWEEK(d.created_at, 1) = YEARWEEK(CURDATE(), 1)';
        } elseif ($created === 'month') {
            $where .= ' AND YEAR(d.created_at) = YEAR(CURDATE()) AND MONTH(d.created_at) = MONTH(CURDATE())';
        }
        $q = trim((string) ($filters['q'] ?? ''));
        if ($q !== '') {
            $where .= ' AND (d.title LIKE :q OR d.file_name LIKE :q2 OR a.name LIKE :q3 OR s.signer_name LIKE :q4)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
            $params['q4'] = $like;
        }
        return [$where, $params];
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            'SELECT d.*, a.name AS account_name FROM portal_documents d
             INNER JOIN accounts a ON a.id = d.account_id
             WHERE d.id = :id AND d.tenant_id = :tid',
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }
}
