<?php
declare(strict_types=1);

namespace App\Models;

final class Contract extends BaseModel
{
    protected static string $table = 'contracts';

    public static function paginate(?int $ownerId, string $status, string $q, int $page, int $perPage, int $accountId = 0, string $renewal = 'all', string $signature = 'all'): array
    {
        $where = 'c.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $where .= ' AND c.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($status === 'renewing') {
            $where .= " AND c.status = 'active' AND c.renewal_date IS NOT NULL AND c.renewal_date <= DATE_ADD(CURDATE(), INTERVAL 60 DAY)";
        } elseif ($status !== 'all' && $status !== '') {
            $where .= ' AND c.status = :st';
            $params['st'] = $status;
        }
        if ($accountId > 0) {
            $where .= ' AND c.account_id = :aid';
            $params['aid'] = $accountId;
        }
        if ($renewal === 'overdue') {
            $where .= ' AND c.renewal_date IS NOT NULL AND c.renewal_date < CURDATE()';
        } elseif ($renewal === 'week') {
            $where .= ' AND c.renewal_date IS NOT NULL AND YEARWEEK(c.renewal_date, 1) = YEARWEEK(CURDATE(), 1)';
        } elseif ($renewal === 'month') {
            $where .= ' AND c.renewal_date IS NOT NULL AND YEAR(c.renewal_date) = YEAR(CURDATE()) AND MONTH(c.renewal_date) = MONTH(CURDATE())';
        }
        if ($signature === 'pending') {
            $where .= " AND s.status = 'pending'";
        } elseif ($signature === 'signed') {
            $where .= " AND s.status = 'signed'";
        } elseif ($signature === 'none') {
            $where .= ' AND s.id IS NULL';
        }
        if ($q !== '') {
            $where .= ' AND (c.title LIKE :q OR c.number LIKE :q2 OR a.name LIKE :q3)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
        }
        $from = "contracts c
             INNER JOIN accounts a ON a.id = c.account_id
             LEFT JOIN users u ON u.id = c.owner_id
             LEFT JOIN signatures s ON s.id = (
               SELECT s2.id FROM signatures s2
               INNER JOIN portal_documents d ON d.id = s2.document_id
               WHERE d.related_type = 'contract' AND d.related_id = c.id AND s2.tenant_id = c.tenant_id
               ORDER BY FIELD(s2.status, 'signed', 'pending', 'declined'), s2.id DESC
               LIMIT 1
             )";
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM $from WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT c.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name,
                    s.status AS sign_status
             FROM $from
             WHERE $where
             ORDER BY c.renewal_date IS NULL, c.renewal_date, c.id DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function counts(?int $ownerId): array
    {
        $where = 'c.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $where .= ' AND c.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $rows = static::db()->fetchAll(
            "SELECT c.status, COUNT(*) n FROM contracts c INNER JOIN accounts a ON a.id = c.account_id WHERE $where GROUP BY c.status",
            $params
        );
        $out = ['all' => 0, 'draft' => 0, 'active' => 0, 'expired' => 0, 'cancelled' => 0, 'renewing' => 0];
        foreach ($rows as $r) {
            $out[(string) $r['status']] = (int) $r['n'];
            $out['all'] += (int) $r['n'];
        }
        $out['renewing'] = (int) static::db()->fetchColumn(
            "SELECT COUNT(*) FROM contracts c INNER JOIN accounts a ON a.id = c.account_id
             WHERE $where AND c.status = 'active' AND c.renewal_date IS NOT NULL AND c.renewal_date <= DATE_ADD(CURDATE(), INTERVAL 60 DAY)",
            $params
        );
        return $out;
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT c.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM contracts c
             INNER JOIN accounts a ON a.id = c.account_id
             LEFT JOIN users u ON u.id = c.owner_id
             WHERE c.id = :id AND c.tenant_id = :tid",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

    public static function dueSoon(?int $ownerId = null): array
    {
        $where = "c.tenant_id = :tid AND c.status = 'active' AND c.renewal_date IS NOT NULL AND c.renewal_date <= DATE_ADD(CURDATE(), INTERVAL 60 DAY)
            AND c.renewed_from_id IS NULL AND c.title NOT LIKE 'Renewal: %'";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $where .= ' AND c.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $rows = static::db()->fetchAll(
            "SELECT c.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM contracts c
             INNER JOIN accounts a ON a.id = c.account_id
             LEFT JOIN users u ON u.id = c.owner_id
             WHERE $where
             ORDER BY c.renewal_date",
            $params
        );
        if ($rows === []) {
            return $rows;
        }
        $children = static::db()->fetchAll(
            "SELECT id, renewed_from_id, account_id, title, status, number
             FROM contracts
             WHERE tenant_id = :tid AND (renewed_from_id IS NOT NULL OR title LIKE 'Renewal: %')
             ORDER BY id DESC",
            ['tid' => static::tenantId()]
        );
        $byParent = [];
        $byKey = [];
        foreach ($children as $ch) {
            $pid = (int) ($ch['renewed_from_id'] ?? 0);
            if ($pid > 0) {
                $byParent[$pid][] = $ch;
            }
            $key = (int) $ch['account_id'] . "\n" . (string) $ch['title'];
            if (!isset($byKey[$key])) {
                $byKey[$key] = $ch;
            }
        }
        $open = [];
        foreach ($rows as $r) {
            $ch = self::latestSentFor((int) $r['id'], $byParent);
            if (!$ch) {
                $ch = $byKey[(int) $r['account_id'] . "\nRenewal: " . (string) $r['title']] ?? null;
            }
            $sentId = (int) ($ch['id'] ?? 0);
            $sentSt = (string) ($ch['status'] ?? '');
            if ($sentId > 0 && $sentSt !== 'cancelled') {
                continue;
            }
            $open[] = $r;
        }
        return $open;
    }

    /**
     * Newest contract in the renewal chain started from this contract.
     *
     * @param array<int, list<array<string, mixed>>> $byParent
     * @return array<string, mixed>|null
     */
    private static function latestSentFor(int $id, array $byParent): ?array
    {
        $node = null;
        $current = $id;
        $guard = 0;
        while (!empty($byParent[$current]) && $guard++ < 20) {
            $node = $byParent[$current][0];
            $current = (int) $node['id'];
        }
        return $node;
    }

    public static function forAccount(int $accountId): array
    {
        return static::db()->fetchAll(
            'SELECT * FROM contracts WHERE tenant_id = :tid AND account_id = :aid ORDER BY renewal_date',
            ['tid' => static::tenantId(), 'aid' => $accountId]
        );
    }

    public static function forOpportunity(int $oppId): ?array
    {
        return static::db()->fetch(
            'SELECT * FROM contracts WHERE tenant_id = :tid AND opportunity_id = :oid ORDER BY id DESC LIMIT 1',
            ['tid' => static::tenantId(), 'oid' => $oppId]
        );
    }
}
