<?php
declare(strict_types=1);

namespace App\Services;

use App\Core\Auth;
use App\Core\Database;
use App\Models\Account;
use App\Models\Activity;
use App\Models\Equipment;
use App\Models\Opportunity;
use App\Models\ServiceJob;
use App\Models\Task;
use App\Models\User;

/**
 * Rule-based Home tips. No third-party AI and no extra menu.
 */
final class SmartTipsService
{
    public const ASK_HINTS = [
        'cold deals' => 'Open deals that look stuck',
        'at risk' => 'Customers who need attention',
        'overdue' => 'Deals missing a next step',
        'renewals' => 'Renewals in the next 60 days',
        'no order' => 'No recent order',
        'call today' => 'People to call today',
        'late visits' => 'Site visits that are late',
    ];

    public static function enabled(): bool
    {
        $tenant = Auth::instance()->tenant();
        if (!$tenant) {
            return false;
        }
        if (!array_key_exists('smart_tips_on', $tenant)) {
            return true;
        }
        return (int) $tenant['smart_tips_on'] === 1;
    }

    public static function setEnabled(bool $on): void
    {
        $auth = Auth::instance();
        $tid = $auth->tenantId();
        if (!$tid) {
            return;
        }
        Database::instance()->query(
            'UPDATE tenants SET smart_tips_on = :on WHERE id = :tid',
            ['on' => $on ? 1 : 0, 'tid' => $tid]
        );
        $userId = (int) $auth->id();
        $tenant = User::tenantById($userId, $tid);
        if ($tenant) {
            $auth->setTenant($tenant, $auth->roles());
        }
    }

    /** @return array<string, mixed> */
    public static function salesperson(int $userId): array
    {
        $call = self::callToday($userId);
        $cold = self::coldDeals($userId, 6);
        $need = Opportunity::needingAction($userId, 8);
        $overdue = Task::overdueCount($userId);
        $summary = self::sentence([
            count($call) . (count($call) === 1 ? ' person to call today' : ' people to call today'),
            count($cold) . (count($cold) === 1 ? ' deal looks cold' : ' deals look cold'),
            $overdue > 0 ? ($overdue === 1 ? '1 late task' : $overdue . ' late tasks') : '',
        ]);
        return [
            'summary' => $summary !== '' ? $summary : 'Nothing urgent on your list. Check tasks if you want work.',
            'call_today' => $call,
            'cold' => $cold,
            'need_step' => self::dealItems($need, 'Add or update the next step'),
        ];
    }

    /** @return array<string, mixed> */
    public static function manager(): array
    {
        $cold = self::coldDeals(null, 8);
        $need = Opportunity::needingAction(null, 8);
        $riskAccounts = self::riskAccounts(8);
        $summary = self::sentence([
            count($cold) . (count($cold) === 1 ? ' deal at risk' : ' deals at risk'),
            count($riskAccounts) . (count($riskAccounts) === 1 ? ' customer at risk' : ' customers at risk'),
            count($need) . (count($need) === 1 ? ' deal with no next step' : ' deals with no next step'),
        ]);
        return [
            'summary' => $summary !== '' ? $summary : 'No risk flags right now.',
            'risk_deals' => $cold,
            'risk_accounts' => $riskAccounts,
            'need_step' => self::dealItems($need, 'Owner should set the next step'),
        ];
    }

    /** @return array<string, mixed> */
    public static function ask(string $raw): array
    {
        $q = mb_strtolower(trim(mb_substr($raw, 0, 80)));
        $hints = self::ASK_HINTS;
        if ($q === '') {
            return ['q' => '', 'intent' => '', 'label' => '', 'rows' => [], 'hints' => $hints];
        }
        $intent = self::intent($q);
        if ($intent === '') {
            return [
                'q' => $raw,
                'intent' => '',
                'label' => 'Try one of these',
                'rows' => [],
                'hints' => $hints,
            ];
        }
        [$label, $rows] = match ($intent) {
            'cold' => ['Deals that look cold', self::coldDeals(null, 20)],
            'risk' => ['Customers at risk', self::riskAccounts(20)],
            'overdue' => ['Deals missing a next step', self::dealItems(Opportunity::needingAction(null, 20), 'No next step')],
            'renewals' => ['Renewals in the next 60 days', self::renewalItems()],
            'reorder' => ['No recent order', self::reorderItems()],
            'call' => ['Call today', self::callToday(null)],
            'visits' => ['Late or due visits', self::visitItems(null, true)],
            default => ['', []],
        };
        return ['q' => $raw, 'intent' => $intent, 'label' => $label, 'rows' => $rows, 'hints' => $hints];
    }

    /** @return array<string, mixed> */
    public static function service(int $userId): array
    {
        $recap = self::visitRecap($userId);
        $follow = self::serviceFollowup($userId);
        $summary = $recap
            ? ('Next: ' . $recap['name'] . ' · ' . $recap['when'])
            : 'No visit on your calendar. Suggested follow-up is below if a site is late.';
        return [
            'summary' => $summary,
            'recap' => $recap,
            'followup' => $follow,
        ];
    }

    /** @return array<string, mixed> */
    public static function marketing(): array
    {
        $suggestions = [];
        foreach ([
            ['opened_no_click', 'Opened, no click', 'They opened a campaign but did not click. Good for a short follow-up.'],
            ['clicked', 'Clicked', 'Highest score — they clicked. Good for a next offer.'],
            ['never_opened', 'Never opened', 'Got mail and did not open. Try a different subject, not the same list again.'],
            ['high', 'High score', 'Opened more than once or clicked. Best list to start with.'],
        ] as [$key, $label, $why]) {
            $n = count(ListQuery::contacts(['engagement' => $key], 500));
            $suggestions[] = [
                'key' => $key,
                'label' => $label,
                'why' => $why,
                'count' => $n,
                'url' => url('lists') . '?engagement=' . rawurlencode($key),
            ];
        }
        $best = $suggestions[0];
        foreach ($suggestions as $s) {
            if ($s['count'] > $best['count']) {
                $best = $s;
            }
        }
        $summary = $best['count'] > 0
            ? ('Start with “' . $best['label'] . '” — ' . $best['count'] . ' people from opens and clicks.')
            : 'Send a campaign first. Scores appear after opens and clicks.';
        return [
            'summary' => $summary,
            'suggestions' => $suggestions,
        ];
    }

    private static function intent(string $q): string
    {
        if (preg_match('/\b(cold|stuck|stale|going cold)\b/', $q)) {
            return 'cold';
        }
        if (preg_match('/\b(at risk|risk|customers? at risk)\b/', $q)) {
            return 'risk';
        }
        if (preg_match('/\b(overdue|late deal|next step|no next)\b/', $q)) {
            return 'overdue';
        }
        if (preg_match('/\b(renew|renewal)\b/', $q)) {
            return 'renewals';
        }
        if (preg_match('/\b(reorder|no order|last order)\b/', $q)) {
            return 'reorder';
        }
        if (preg_match('/\b(call|today|who to call)\b/', $q)) {
            return 'call';
        }
        if (preg_match('/\b(visit|site)\b/', $q)) {
            return 'visits';
        }
        return '';
    }

    /** @return list<array<string, string>> */
    private static function callToday(?int $ownerId): array
    {
        $sql = "SELECT a.id, a.name, a.phone, a.next_action, a.next_action_at,
                       c.id AS contact_id, c.first_name, c.last_name, c.phone AS contact_phone
                FROM accounts a
                LEFT JOIN contacts c ON c.account_id = a.id AND c.tenant_id = a.tenant_id AND c.deleted_at IS NULL
                WHERE a.tenant_id = :tid AND a.deleted_at IS NULL AND a.status = 'active'
                  AND a.next_action_at IS NOT NULL AND DATE(a.next_action_at) <= CURDATE()";
        $params = ['tid' => self::tid()];
        if ($ownerId) {
            $sql .= ' AND a.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' ORDER BY a.next_action_at ASC, a.id, c.id LIMIT 40';
        $seen = [];
        $out = [];
        foreach (Database::instance()->fetchAll($sql, $params) as $row) {
            $aid = (int) $row['id'];
            if (isset($seen[$aid])) {
                continue;
            }
            $seen[$aid] = true;
            $person = trim((string) ($row['first_name'] ?? '') . ' ' . (string) ($row['last_name'] ?? ''));
            $phone = trim((string) (($row['contact_phone'] ?: $row['phone']) ?? ''));
            $due = due_label($row['next_action_at'] ?? null);
            $title = $person !== '' ? ($person . ' · ' . $row['name']) : (string) $row['name'];
            $why = trim((string) ($row['next_action'] ?? ''));
            if ($why === '') {
                $why = 'Next action is ' . ($due['text'] !== '' ? strtolower($due['text']) : 'due');
            } else {
                $why .= $due['text'] !== '' ? (' · ' . $due['text']) : '';
            }
            if ($phone !== '') {
                $why .= ' · ' . $phone;
            }
            $out[] = [
                'title' => $title,
                'why' => $why,
                'url' => url('accounts/' . $aid),
                'tone' => ($due['class'] ?? '') === 'due-overdue' ? 'warn' : 'now',
            ];
            if (count($out) >= 6) {
                break;
            }
        }
        return $out;
    }

    /** @return list<array<string, string>> */
    private static function coldDeals(?int $ownerId, int $limit): array
    {
        $sql = "SELECT o.id, o.name, o.next_step, o.next_step_at, o.close_date, o.created_at, o.amount,
                       a.name AS account_name, s.name AS stage_name,
                       (SELECT MAX(act.activity_at) FROM activities act
                         WHERE act.tenant_id = o.tenant_id
                           AND ((act.related_type = 'opportunity' AND act.related_id = o.id)
                             OR (act.related_type = 'account' AND act.related_id = o.account_id))
                       ) AS last_touch
                FROM opportunities o
                INNER JOIN accounts a ON a.id = o.account_id
                INNER JOIN pipeline_stages s ON s.id = o.stage_id
                WHERE o.tenant_id = :tid AND o.deleted_at IS NULL AND s.is_won = 0 AND s.is_lost = 0";
        $params = ['tid' => self::tid()];
        if ($ownerId) {
            $sql .= ' AND o.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' ORDER BY o.next_step_at IS NULL, o.next_step_at ASC LIMIT 40';
        $cutoff = strtotime('-14 days');
        $out = [];
        foreach (Database::instance()->fetchAll($sql, $params) as $row) {
            $why = self::coldWhy($row, $cutoff);
            if ($why === '') {
                continue;
            }
            $out[] = [
                'title' => (string) $row['name'],
                'why' => $why . ' · ' . (string) $row['account_name'] . ' · ' . (string) $row['stage_name'],
                'url' => url('opportunities/' . (int) $row['id']),
                'tone' => 'warn',
                'related_type' => 'opportunity',
                'related_id' => (int) $row['id'],
            ];
            if (count($out) >= $limit) {
                break;
            }
        }
        return $out;
    }

    /** @param array<string, mixed> $row */
    private static function coldWhy(array $row, int $cutoff): string
    {
        $next = strtotime((string) ($row['next_step_at'] ?? ''));
        if ($next !== false && $next < strtotime('today')) {
            return 'Next step is late';
        }
        $close = strtotime((string) ($row['close_date'] ?? ''));
        if ($close !== false && $close < strtotime('today')) {
            return 'Close date has passed';
        }
        $touch = strtotime((string) ($row['last_touch'] ?? ''));
        if ($touch !== false && $touch < $cutoff) {
            return 'No activity in 14 days';
        }
        $created = strtotime((string) ($row['created_at'] ?? ''));
        $hasStep = trim((string) ($row['next_step'] ?? '')) !== '' && !empty($row['next_step_at']);
        if (!$hasStep && $created !== false && $created < $cutoff) {
            return 'No next step for 14 days';
        }
        if ($touch === false && $created !== false && $created < $cutoff) {
            return 'No activity since it was opened';
        }
        return '';
    }

    /** @return list<array<string, mixed>> */
    public static function atRiskDeals(?int $ownerId, int $limit = 6): array
    {
        return self::coldDeals($ownerId, $limit);
    }

    /** @return list<array<string, mixed>> */
    public static function atRiskAccounts(?int $ownerId, int $limit = 6): array
    {
        return self::riskAccounts($limit, $ownerId);
    }

    private static function riskAccounts(int $limit, ?int $ownerId = null): array
    {
        $out = [];
        $sql = "SELECT id, name, email, phone, next_action, next_action_at
                FROM accounts
                WHERE tenant_id = :tid AND deleted_at IS NULL AND status = 'active'
                  AND next_action_at IS NOT NULL AND DATE(next_action_at) < CURDATE()";
        $params = ['tid' => self::tid()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' ORDER BY next_action_at ASC LIMIT 10';
        foreach (Database::instance()->fetchAll($sql, $params) as $row) {
            $due = due_label($row['next_action_at'] ?? null);
            $out[] = [
                'title' => (string) $row['name'],
                'why' => trim((string) ($row['next_action'] ?: 'Next action')) . ($due['text'] !== '' ? ' · ' . $due['text'] : ''),
                'url' => url('accounts/' . (int) $row['id']),
                'tone' => 'warn',
                'related_type' => 'account',
                'related_id' => (int) $row['id'],
                'email' => (string) ($row['email'] ?? ''),
                'phone' => (string) ($row['phone'] ?? ''),
            ];
        }
        if ($ownerId === null) {
            foreach (IndustryReportService::reorders() as $row) {
                $out[] = [
                    'title' => (string) $row['name'],
                    'why' => 'Last order ' . (string) ($row['last_order_at'] ?? '') . ' · past reorder cycle',
                    'url' => url('accounts/' . (int) $row['id']),
                    'tone' => 'warn',
                    'related_type' => 'account',
                    'related_id' => (int) $row['id'],
                ];
            }
            foreach (IndustryReportService::renewals() as $row) {
                $out[] = [
                    'title' => (string) $row['name'],
                    'why' => 'Renewal ' . (string) ($row['renewal_date'] ?? ''),
                    'url' => url('accounts/' . (int) $row['id']),
                    'tone' => 'now',
                    'related_type' => 'account',
                    'related_id' => (int) $row['id'],
                ];
            }
        }
        $seen = [];
        $uniq = [];
        foreach ($out as $item) {
            $key = $item['url'];
            if (isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $uniq[] = $item;
            if (count($uniq) >= $limit) {
                break;
            }
        }
        return $uniq;
    }

    /** @param list<array<string, mixed>> $rows */
    private static function dealItems(array $rows, string $why): array
    {
        $out = [];
        foreach ($rows as $row) {
            $step = trim((string) ($row['next_step'] ?? ''));
            $out[] = [
                'title' => (string) $row['name'],
                'why' => ($step !== '' ? $step . ' · ' : '') . $why . ' · ' . (string) ($row['account_name'] ?? ''),
                'url' => url('opportunities/' . (int) $row['id']),
                'tone' => 'now',
            ];
        }
        return $out;
    }

    /** @return list<array<string, string>> */
    private static function renewalItems(): array
    {
        $out = [];
        foreach (IndustryReportService::renewals() as $row) {
            $out[] = [
                'title' => (string) $row['name'],
                'why' => 'Renewal ' . (string) ($row['renewal_date'] ?? ''),
                'url' => url('accounts/' . (int) $row['id']),
                'tone' => 'now',
            ];
        }
        return $out;
    }

    /** @return list<array<string, string>> */
    private static function reorderItems(): array
    {
        $out = [];
        foreach (IndustryReportService::reorders() as $row) {
            $out[] = [
                'title' => (string) $row['name'],
                'why' => 'Last order ' . (string) ($row['last_order_at'] ?? ''),
                'url' => url('accounts/' . (int) $row['id']),
                'tone' => 'warn',
            ];
        }
        return $out;
    }

    /** @return list<array<string, string>> */
    private static function visitItems(?int $assigneeId, bool $lateOnly): array
    {
        $sql = "SELECT id, name, next_visit_at, city
                FROM accounts
                WHERE tenant_id = :tid AND deleted_at IS NULL AND status = 'active'
                  AND " . Account::openVisitClause('');
        $params = ['tid' => self::tid()];
        if ($assigneeId) {
            $sql .= ' AND service_assignee_id = :aid';
            $params['aid'] = $assigneeId;
        }
        if ($lateOnly) {
            $sql .= ' AND DATE(next_visit_at) < CURDATE()';
        }
        $sql .= ' ORDER BY next_visit_at ASC LIMIT 20';
        $out = [];
        foreach (Database::instance()->fetchAll($sql, $params) as $row) {
            $due = due_label($row['next_visit_at'] ?? null);
            $whyBit = match ($due['class'] ?? '') {
                'due-overdue' => 'Late',
                'due-today' => 'Today',
                default => 'Coming up',
            };
            $out[] = [
                'title' => (string) $row['name'],
                'why' => trim($whyBit . ' · ' . (string) ($row['city'] ?? '')),
                'url' => url('service/' . (int) $row['id']),
                'tone' => ($due['class'] ?? '') === 'due-overdue' ? 'warn' : 'now',
            ];
        }
        return $out;
    }

    /** @return array<string, mixed>|null */
    private static function visitRecap(int $userId): ?array
    {
        $row = Database::instance()->fetch(
            "SELECT a.id, a.name, a.phone, a.address_line, a.city, a.state, a.next_visit_at, a.last_visit_at
             FROM accounts a
             WHERE a.tenant_id = :tid AND a.deleted_at IS NULL AND a.status = 'active'
               AND a.service_assignee_id = :aid AND " . Account::openVisitClause('a') . "
             ORDER BY CASE WHEN DATE(a.next_visit_at) = CURDATE() THEN 0
                           WHEN DATE(a.next_visit_at) < CURDATE() THEN 1
                           ELSE 2 END, a.next_visit_at ASC
             LIMIT 1",
            ['tid' => self::tid(), 'aid' => $userId]
        );
        if (!$row) {
            return null;
        }
        $notes = Activity::timeline('account', (int) $row['id'], 1);
        $note = $notes[0] ?? null;
        $jobs = 0;
        $gear = 0;
        try {
            foreach (ServiceJob::forAccount((int) $row['id']) as $j) {
                if (in_array((string) ($j['status'] ?? ''), ['open', 'in_progress'], true)) {
                    $jobs++;
                }
            }
            $gear = count(Equipment::forAccount((int) $row['id']));
        } catch (\Throwable $e) {
            $jobs = 0;
            $gear = 0;
        }
        $loc = trim((string) ($row['address_line'] ?? ''));
        $city = trim((string) ($row['city'] ?? '') . ((string) ($row['state'] ?? '') !== '' ? ', ' . $row['state'] : ''));
        return [
            'id' => (int) $row['id'],
            'name' => (string) $row['name'],
            'when' => visit_status_label($row)['text'],
            'phone' => (string) ($row['phone'] ?? ''),
            'place' => trim($loc . ($loc !== '' && $city !== '' ? ' · ' : '') . $city),
            'last_visit' => !empty($row['last_visit_at']) ? dt_fmt($row['last_visit_at'], 'M j') : '',
            'last_note' => $note ? (string) ($note['subject'] ?? $note['body'] ?? '') : '',
            'jobs' => $jobs,
            'equipment' => $gear,
            'url' => url('service/' . (int) $row['id']),
        ];
    }

    /** @return list<array<string, string>> */
    private static function serviceFollowup(int $userId): array
    {
        $out = [];
        foreach (self::visitItems($userId, true) as $item) {
            $item['why'] = 'Reschedule or close this late visit';
            $out[] = $item;
        }
        $recent = Database::instance()->fetchAll(
            "SELECT id, name, last_visit_at, next_visit_at
             FROM accounts
             WHERE tenant_id = :tid AND deleted_at IS NULL AND status = 'active'
               AND service_assignee_id = :aid
               AND last_visit_at IS NOT NULL
               AND last_visit_at >= DATE_SUB(NOW(), INTERVAL 5 DAY)
               AND next_visit_at IS NULL
             ORDER BY last_visit_at DESC LIMIT 6",
            ['tid' => self::tid(), 'aid' => $userId]
        );
        foreach ($recent as $row) {
            $out[] = [
                'title' => (string) $row['name'],
                'why' => 'Last visit ' . dt_fmt($row['last_visit_at'], 'M j') . ' · add a follow-up visit',
                'url' => url('accounts/' . (int) $row['id']),
                'tone' => 'now',
            ];
        }
        $seen = [];
        $uniq = [];
        foreach ($out as $item) {
            if (isset($seen[$item['url']])) {
                continue;
            }
            $seen[$item['url']] = true;
            $uniq[] = $item;
            if (count($uniq) >= 6) {
                break;
            }
        }
        return $uniq;
    }

    /** @param list<string> $bits */
    private static function sentence(array $bits): string
    {
        $bits = array_values(array_filter($bits, static function (string $b): bool {
            return $b !== '' && !str_starts_with($b, '0 ');
        }));
        if ($bits === []) {
            return '';
        }
        if (count($bits) === 1) {
            return ucfirst($bits[0]) . '.';
        }
        $last = array_pop($bits);
        return ucfirst(implode(', ', $bits) . ', and ' . $last . '.');
    }

    private static function tid(): int
    {
        return (int) Auth::instance()->tenantId();
    }
}
