<?php
declare(strict_types=1);

namespace App\Services;

use App\Core\Database;
use App\Industry\Catalog;
use App\Models\Unsubscribe;

final class ListQuery
{
    /**
     * Live contacts who can receive a marketing email.
     * Filters: territory_id, industry, stage_id, last_order (within_90|none_90),
     *          engagement (opened|clicked|opened_no_click|never_opened|high).
     *
     * @return list<array<string,mixed>>
     */
    public static function contacts(array $filters, int $limit = 500): array
    {
        $tid = \App\Core\Auth::instance()->tenantId();
        $sql = "SELECT c.id, c.account_id, c.first_name, c.last_name, c.email, c.email_status,
                       a.name AS account_name, a.industry, a.territory_id, a.attrs
                FROM contacts c
                INNER JOIN accounts a ON a.id = c.account_id AND a.tenant_id = c.tenant_id AND a.deleted_at IS NULL AND a.status = 'active'
                WHERE c.tenant_id = :tid AND c.deleted_at IS NULL
                  AND c.email IS NOT NULL AND c.email <> '' AND c.email_status = 'ok'";
        $params = ['tid' => $tid];

        if (!empty($filters['territory_id'])) {
            $sql .= ' AND a.territory_id = :terr';
            $params['terr'] = (int) $filters['territory_id'];
        }
        if (!empty($filters['industry'])) {
            $sql .= ' AND a.industry = :ind';
            $params['ind'] = (string) $filters['industry'];
        }
        if (!empty($filters['stage_id'])) {
            $sql .= ' AND EXISTS (
                SELECT 1 FROM opportunities o
                WHERE o.account_id = a.id AND o.tenant_id = a.tenant_id AND o.deleted_at IS NULL AND o.stage_id = :sid
            )';
            $params['sid'] = (int) $filters['stage_id'];
        }

        $sql .= ' ORDER BY a.name, c.last_name LIMIT ' . max(1, min(500, $limit));
        $rows = Database::instance()->fetchAll($sql, $params);

        $blocked = Unsubscribe::emailsForTenant($tid);
        $engagement = self::engagementMap($tid);
        $lastOrders = [];
        try {
            $lastOrders = \App\Models\Order::lastOrderMap();
        } catch (\Throwable $e) {
            $lastOrders = [];
        }
        $out = [];
        foreach ($rows as $row) {
            $email = strtolower(trim((string) $row['email']));
            if ($email === '' || isset($blocked[$email])) {
                continue;
            }
            if (!self::matchesLastOrder($row, (string) ($filters['last_order'] ?? ''), $lastOrders)) {
                continue;
            }
            $cid = (int) ($row['id'] ?? 0);
            $eng = $engagement[$cid] ?? ['opens' => 0, 'clicks' => 0, 'sent' => 0];
            if (!self::matchesEngagement($eng, (string) ($filters['engagement'] ?? ''))) {
                continue;
            }
            $row['opens'] = $eng['opens'];
            $row['clicks'] = $eng['clicks'];
            $row['score'] = ($eng['opens'] * 1) + ($eng['clicks'] * 2);
            $out[] = $row;
        }
        return $out;
    }

    /** @param array<int, string> $lastOrders */
    private static function matchesLastOrder(array $row, string $mode, array $lastOrders): bool
    {
        if ($mode === '' || $mode === 'any') {
            return true;
        }
        $accountId = (int) ($row['account_id'] ?? 0);
        $last = $lastOrders[$accountId] ?? '';
        if ($last === '') {
            $attrs = IndustryService::decodeAttrs($row['attrs'] ?? null);
            $last = (string) ($attrs['last_order_at'] ?? '');
        }
        $ts = $last !== '' ? strtotime($last) : false;
        $cutoff = strtotime('-90 days');
        $recent = $ts !== false && $ts >= $cutoff;
        if ($mode === 'within_90') {
            return $recent;
        }
        if ($mode === 'none_90') {
            return !$recent;
        }
        return true;
    }

    public static function normalizeFilters(array $input): array
    {
        $out = [];
        if (!empty($input['territory_id'])) {
            $out['territory_id'] = (int) $input['territory_id'];
        }
        if (!empty($input['industry'])) {
            $out['industry'] = mb_substr(trim((string) $input['industry']), 0, 80);
        }
        if (!empty($input['stage_id'])) {
            $out['stage_id'] = (int) $input['stage_id'];
        }
        $last = (string) ($input['last_order'] ?? '');
        if (in_array($last, ['within_90', 'none_90'], true)) {
            $out['last_order'] = $last;
        }
        $eng = (string) ($input['engagement'] ?? '');
        if (in_array($eng, ['opened', 'clicked', 'opened_no_click', 'never_opened', 'high'], true)) {
            $out['engagement'] = $eng;
        }
        return $out;
    }

    /**
     * @param array<string,mixed> $filters
     * @param array<int,string> $territoryNames
     * @param array<int,string> $stageNames
     * @return list<string>
     */
    public static function labels(array $filters, array $territoryNames, array $stageNames): array
    {
        $chips = [];
        if (!empty($filters['territory_id'])) {
            $id = (int) $filters['territory_id'];
            $chips[] = $territoryNames[$id] ?? ('Area #' . $id);
        }
        if (!empty($filters['industry'])) {
            $chips[] = (string) $filters['industry'];
        }
        if (!empty($filters['stage_id'])) {
            $id = (int) $filters['stage_id'];
            $chips[] = $stageNames[$id] ?? ('Stage #' . $id);
        }
        if (($filters['last_order'] ?? '') === 'within_90') {
            $chips[] = 'Ordered in last 90 days';
        }
        if (($filters['last_order'] ?? '') === 'none_90') {
            $chips[] = 'No order in 90 days';
        }
        $engLabel = [
            'opened' => 'Opened',
            'clicked' => 'Clicked',
            'opened_no_click' => 'Opened, no click',
            'never_opened' => 'Never opened',
            'high' => 'High score',
        ];
        if (!empty($filters['engagement']) && isset($engLabel[$filters['engagement']])) {
            $chips[] = $engLabel[$filters['engagement']];
        }
        return $chips;
    }

    /** @return array<int, array{opens:int,clicks:int,sent:int}> */
    private static function engagementMap(int $tenantId): array
    {
        try {
            $rows = Database::instance()->fetchAll(
                "SELECT contact_id,
                        SUM(opened_at IS NOT NULL) AS opens,
                        SUM(clicked_at IS NOT NULL) AS clicks,
                        SUM(sent_at IS NOT NULL OR status IN ('sent','bounced','unsubscribed')) AS sent
                 FROM campaign_recipients
                 WHERE tenant_id = :tid
                 GROUP BY contact_id",
                ['tid' => $tenantId]
            );
        } catch (\Throwable $e) {
            return [];
        }
        $map = [];
        foreach ($rows as $r) {
            $map[(int) $r['contact_id']] = [
                'opens' => (int) $r['opens'],
                'clicks' => (int) $r['clicks'],
                'sent' => (int) $r['sent'],
            ];
        }
        return $map;
    }

    /** @param array{opens:int,clicks:int,sent:int} $eng */
    private static function matchesEngagement(array $eng, string $mode): bool
    {
        if ($mode === '') {
            return true;
        }
        $opens = $eng['opens'];
        $clicks = $eng['clicks'];
        $sent = $eng['sent'];
        return match ($mode) {
            'opened' => $opens > 0,
            'clicked' => $clicks > 0,
            'opened_no_click' => $opens > 0 && $clicks === 0,
            'never_opened' => $sent > 0 && $opens === 0,
            'high' => $clicks > 0 || $opens >= 2,
            default => true,
        };
    }
}
