<?php
declare(strict_types=1);

namespace App\Models;

final class Lead extends BaseModel
{
    protected static string $table = 'leads';
    protected static bool $softDelete = true;

    public static function paginate(
        string $q,
        string $status,
        int $page,
        int $perPage,
        ?int $unassignedOnly = null,
        ?int $ownerId = null,
        string $source = '',
        int $territoryId = 0,
        string $created = 'all'
    ): array {
        $where = 'l.tenant_id = :tid AND l.deleted_at IS NULL';
        $params = ['tid' => static::tenantId()];
        if ($q !== '') {
            $where .= " AND (CONCAT(l.first_name, ' ', l.last_name) LIKE :q OR l.company LIKE :q2 OR l.email LIKE :q3)";
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
        }
        if ($status !== '' && $status !== 'all') {
            $where .= ' AND l.status = :st';
            $params['st'] = $status;
        }
        if ($source !== '') {
            $where .= ' AND l.source = :src';
            $params['src'] = $source;
        }
        if ($territoryId > 0) {
            $where .= ' AND l.territory_id = :ter';
            $params['ter'] = $territoryId;
        }
        if ($created === 'today') {
            $where .= ' AND DATE(l.created_at) = CURDATE()';
        } elseif ($created === 'week') {
            $where .= ' AND YEARWEEK(l.created_at, 1) = YEARWEEK(CURDATE(), 1)';
        } elseif ($created === 'month') {
            $where .= ' AND YEAR(l.created_at) = YEAR(CURDATE()) AND MONTH(l.created_at) = MONTH(CURDATE())';
        } elseif ($created === 'last_week') {
            $where .= ' AND YEARWEEK(l.created_at, 1) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 7 DAY), 1)';
        } elseif ($created === 'last_month') {
            $where .= " AND DATE(l.created_at) >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                        AND DATE(l.created_at) < DATE_FORMAT(CURDATE(), '%Y-%m-01')";
        }
        if ($unassignedOnly) {
            $where .= ' AND l.owner_id IS NULL';
        }
        if ($ownerId) {
            $where .= ' AND l.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM leads l WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT l.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name, t.name AS territory_name
             FROM leads l
             LEFT JOIN users u ON u.id = l.owner_id
             LEFT JOIN territories t ON t.id = l.territory_id
             WHERE $where
             ORDER BY l.created_at DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT l.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name, t.name AS territory_name,
                    acc.name AS converted_account_name, opp.name AS converted_opportunity_name
             FROM leads l
             LEFT JOIN users u ON u.id = l.owner_id
             LEFT JOIN territories t ON t.id = l.territory_id
             LEFT JOIN accounts acc ON acc.id = l.converted_account_id
             LEFT JOIN opportunities opp ON opp.id = l.converted_opportunity_id
             WHERE l.id = :id AND l.tenant_id = :tid AND l.deleted_at IS NULL",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

    /** @return array<string,string> */
    public static function statusLabels(): array
    {
        return [
            'new' => 'New',
            'qualified' => 'Qualified',
            'unqualified' => 'Not a fit',
            'converted' => 'Converted',
        ];
    }

    public static function statusLabel(?string $status): string
    {
        $status = (string) $status;
        return self::statusLabels()[$status] ?? ($status !== '' ? $status : 'New');
    }

    public static function allForSelect(): array
    {
        return static::db()->fetchAll(
            "SELECT id, first_name, last_name, company
             FROM leads
             WHERE tenant_id = :tid AND deleted_at IS NULL AND status IN ('new','qualified')
             ORDER BY first_name, last_name
             LIMIT 200",
            ['tid' => static::tenantId()]
        );
    }

    /**
     * @param list<array<string,mixed>> $timeline
     * @return list<array{status:string,label:string,from:?string,from_label:?string,at:?string,who:string,note:string}>
     */
    public static function statusHistory(array $lead, array $timeline): array
    {
        $labels = self::statusLabels();
        $events = [[
            'status' => 'new',
            'label' => $labels['new'],
            'from' => null,
            'from_label' => null,
            'at' => $lead['created_at'] ?? null,
            'who' => trim((string) ($lead['owner_name'] ?? '')),
            'note' => 'Lead created',
        ]];

        foreach ($timeline as $t) {
            $parsed = self::statusChangeFromActivity($t);
            if ($parsed === null) {
                continue;
            }
            $to = $parsed['to'];
            $from = $parsed['from'];
            $events[] = [
                'status' => $to,
                'label' => $labels[$to] ?? $to,
                'from' => $from,
                'from_label' => $from !== null ? ($labels[$from] ?? $from) : null,
                'at' => $t['activity_at'] ?? null,
                'who' => trim((string) ($t['owner_name'] ?? '')),
                'note' => $parsed['note'] !== '' ? $parsed['note'] : (string) ($t['subject'] ?? ''),
            ];
        }

        $current = (string) ($lead['status'] ?? 'new');
        $hasCurrent = false;
        foreach ($events as $e) {
            if (($e['status'] ?? '') === $current) {
                $hasCurrent = true;
                break;
            }
        }
        if (!$hasCurrent && $current !== '') {
            $at = $current === 'converted' && !empty($lead['converted_at'])
                ? $lead['converted_at']
                : ($lead['updated_at'] ?? $lead['created_at'] ?? null);
            $events[] = [
                'status' => $current,
                'label' => $labels[$current] ?? $current,
                'from' => $current === 'new' ? null : 'new',
                'from_label' => $current === 'new' ? null : $labels['new'],
                'at' => $at,
                'who' => trim((string) ($lead['owner_name'] ?? '')),
                'note' => 'Status set to ' . ($labels[$current] ?? $current),
            ];
        }

        usort($events, static function (array $a, array $b): int {
            $cmp = strcmp((string) ($a['at'] ?? ''), (string) ($b['at'] ?? ''));
            if ($cmp !== 0) {
                return $cmp;
            }
            $rank = ['new' => 0, 'qualified' => 1, 'unqualified' => 1, 'converted' => 2];
            return ($rank[$a['status'] ?? ''] ?? 9) <=> ($rank[$b['status'] ?? ''] ?? 9);
        });

        $out = [];
        foreach ($events as $e) {
            $prev = $out !== [] ? $out[count($out) - 1] : null;
            if ($prev && ($prev['status'] ?? '') === ($e['status'] ?? '')) {
                continue;
            }
            $out[] = $e;
        }
        return $out;
    }

    /** @return array{from:?string,to:string,note:string}|null */
    private static function statusChangeFromActivity(array $t): ?array
    {
        $type = (string) ($t['type'] ?? '');
        $subject = trim((string) ($t['subject'] ?? ''));
        $body = trim((string) ($t['body'] ?? ''));
        $flip = array_flip(self::statusLabels());
        $from = null;
        $to = null;
        $note = '';

        if ($body !== '' && str_starts_with($body, '{')) {
            $json = json_decode($body, true);
            if (is_array($json)) {
                $toCand = (string) ($json['to'] ?? '');
                $fromCand = (string) ($json['from'] ?? '');
                if (isset(self::statusLabels()[$toCand])) {
                    $to = $toCand;
                }
                if (isset(self::statusLabels()[$fromCand])) {
                    $from = $fromCand;
                }
                $note = trim((string) ($json['note'] ?? ''));
            }
        }

        if ($to === null && preg_match('/^(New|Qualified|Not a fit|Converted)\s*→\s*(New|Qualified|Not a fit|Converted)$/u', $subject, $m)) {
            $from = $flip[$m[1]] ?? $from;
            $to = $flip[$m[2]] ?? null;
        }

        if ($to === null && preg_match('/marked\s+(qualified|unqualified|converted)/i', $subject, $m)) {
            $to = strtolower($m[1]);
        }

        if ($to === null && $type === 'stage' && preg_match('/converted/i', $subject)) {
            $to = 'converted';
        }

        if ($to === null || $to === 'new') {
            return null;
        }

        if ($note === '' && $body !== '' && !str_starts_with($body, '{')) {
            $note = $body;
        }

        return ['from' => $from, 'to' => $to, 'note' => $note];
    }

    public static function newForHome(?int $ownerId = null, int $limit = 6): array
    {
        $sql = "SELECT l.id, l.first_name, l.last_name, l.company, l.email, l.phone, l.source, l.created_at, l.owner_id,
                       CONCAT(u.first_name, ' ', u.last_name) AS owner_name
                FROM leads l
                LEFT JOIN users u ON u.id = l.owner_id
                WHERE l.tenant_id = :tid AND l.deleted_at IS NULL AND l.status = 'new'";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND l.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' ORDER BY l.created_at DESC, l.id DESC LIMIT ' . (int) $limit;
        return static::db()->fetchAll($sql, $params);
    }

    public static function newCount(?int $ownerId = null, bool $createdBeforeToday = false): int
    {
        $sql = "SELECT COUNT(*) FROM leads WHERE tenant_id = :tid AND deleted_at IS NULL AND status = 'new'";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($createdBeforeToday) {
            $sql .= ' AND created_at < CURDATE()';
        }
        return (int) static::db()->fetchColumn($sql, $params);
    }

    /** @return list<string> */
    public static function sources(): array
    {
        $known = ['Website', 'Referral', 'Call', 'Trade show', 'Other'];
        $rows = static::db()->fetchAll(
            "SELECT DISTINCT source FROM leads
             WHERE tenant_id = :tid AND deleted_at IS NULL AND source IS NOT NULL AND source <> ''
             ORDER BY source",
            ['tid' => static::tenantId()]
        );
        $out = [];
        foreach (array_merge($known, array_column($rows, 'source')) as $src) {
            $src = trim((string) $src);
            if ($src !== '' && !in_array($src, $out, true)) {
                $out[] = $src;
            }
        }
        return $out;
    }

    public static function boardCounts(?int $ownerId = null, string $source = ''): array
    {
        $sql = "SELECT
                    SUM(status = 'new') AS new_n,
                    SUM(status = 'qualified') AS qualified_n,
                    SUM(status = 'unqualified') AS unqualified_n,
                    SUM(status = 'converted') AS converted_n
                FROM leads
                WHERE tenant_id = :tid AND deleted_at IS NULL";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($source !== '') {
            $sql .= ' AND source = :src';
            $params['src'] = $source;
        }
        $row = static::db()->fetch($sql, $params) ?: [];
        $new = (int) ($row['new_n'] ?? 0);
        $qualified = (int) ($row['qualified_n'] ?? 0);
        $unqualified = (int) ($row['unqualified_n'] ?? 0);
        $converted = (int) ($row['converted_n'] ?? 0);
        return [
            'new' => $new,
            'qualified' => $qualified,
            'unqualified' => $unqualified,
            'converted' => $converted,
            'all' => $new + $qualified + $unqualified + $converted,
        ];
    }

    public static function search(string $q, int $limit = 8): array
    {
        $like = '%' . $q . '%';
        return static::db()->fetchAll(
            "SELECT id, first_name, last_name, company, email, phone, status FROM leads
             WHERE tenant_id = :tid AND deleted_at IS NULL
               AND (first_name LIKE :q OR last_name LIKE :q2 OR company LIKE :q3 OR email LIKE :q4 OR phone LIKE :q5)
             ORDER BY created_at DESC LIMIT $limit",
            ['tid' => static::tenantId(), 'q' => $like, 'q2' => $like, 'q3' => $like, 'q4' => $like, 'q5' => $like]
        );
    }

    public static function unassigned(): array
    {
        return static::db()->fetchAll(
            "SELECT l.*, t.name AS territory_name
             FROM leads l
             LEFT JOIN territories t ON t.id = l.territory_id
             WHERE l.tenant_id = :tid AND l.deleted_at IS NULL AND l.owner_id IS NULL AND l.status = 'new'
             ORDER BY l.created_at DESC",
            ['tid' => static::tenantId()]
        );
    }

    public static function convertedToAccount(int $accountId): ?array
    {
        return static::db()->fetch(
            "SELECT id, first_name, last_name, converted_at
             FROM leads
             WHERE tenant_id = :tid AND converted_account_id = :aid AND deleted_at IS NULL
             ORDER BY converted_at DESC, id DESC
             LIMIT 1",
            ['tid' => static::tenantId(), 'aid' => $accountId]
        );
    }
}
