<?php
declare(strict_types=1);

namespace App\Services;

use App\Core\Auth;
use App\Models\Account;
use App\Models\Activity;
use App\Models\Campaign;
use App\Models\EmailLog;
use App\Models\EmailTemplate;
use App\Models\Lead;
use App\Models\MarketingList;
use App\Models\Meeting;
use App\Models\Opportunity;
use App\Models\Role;
use App\Models\Unsubscribe;
use App\Models\Task;

final class DashboardService
{
    public static function salesperson(int $userId): array
    {
        $types = Activity::typeCountsThisMonth($userId);
        $mkt = \App\Models\Campaign::monthSummary();
        return [
            'tasks_today' => Task::dueTodayCount($userId),
            'meetings_today' => Meeting::todayCount($userId),
            'open_opportunities' => self::openCount($userId),
            'revenue_month' => Opportunity::forecast($userId)['won_this_month'] ?? 0,
            'emails_month' => EmailLog::sentThisMonth($userId),
            'tasks' => Task::forHomeDue($userId, 8),
            'meetings' => Meeting::upcoming($userId, 6),
            'recent' => Activity::forHome($userId, 5),
            'overdue' => Task::overdueCount($userId),
            'new_leads' => Lead::newCount($userId),
            'new_lead_rows' => Lead::newForHome($userId, 6),
            'needs_action' => Opportunity::needingAction($userId, 6),
            'risk_deals' => SmartTipsService::atRiskDeals($userId, 6),
            'risk_accounts' => SmartTipsService::atRiskAccounts($userId, 4),
            'type_counts' => $types,
            'visits' => \App\Industry\Catalog::isServiceSetup() ? Account::visits($userId) : [],
            'marketing' => self::marketingSnapshot($mkt),
        ];
    }

    public static function service(int $userId): array
    {
        $visits = Account::visits($userId);
        $today = date('Y-m-d');
        $late = $todayN = $soon = 0;
        foreach ($visits as $v) {
            if (visit_attended($v)) {
                continue;
            }
            $day = date('Y-m-d', strtotime((string) ($v['next_visit_at'] ?? '')));
            if ($day === '' || $day === '1970-01-01') {
                continue;
            }
            if ($day < $today) {
                $late++;
            } elseif ($day === $today) {
                $todayN++;
            } else {
                $soon++;
            }
        }
        $meetings = Meeting::upcoming($userId, 6);
        return [
            'visits' => $visits,
            'late' => $late,
            'today' => $todayN,
            'upcoming' => $soon,
            'tasks_open' => Task::dueTodayCount($userId) + Task::overdueCount($userId),
            'tasks' => Task::forHomeDue($userId, 8),
            'meetings' => $meetings,
            'recent' => Activity::forHome($userId, 6),
        ];
    }

    public static function manager(): array
    {
        $pipeline = Opportunity::pipelineByStage();
        $forecast = Opportunity::forecast();
        $types = Activity::typeCountsThisMonth();
        $typesY = Activity::typeCountsThisMonthBeforeToday();
        $open = Opportunity::openTotals();
        $openY = Opportunity::openTotals(true);
        $won = Opportunity::wonCount();
        $wonY = Opportunity::wonCount(true);
        $leads = Lead::newCount();
        $leadsY = Lead::newCount(null, true);
        $emails = EmailLog::sentThisMonth();
        $emailsY = EmailLog::sentThisMonth(null, true);
        $tasksDue = Task::dueOpenCount();
        $tasksY = Task::dueOpenCount(null, false);
        $mkt = \App\Models\Campaign::monthSummary();

        return [
            'new_leads' => $leads,
            'opportunities' => $open['count'],
            'pipeline_value' => $open['value'],
            'won_deals' => $won,
            'emails_month' => $emails,
            'tasks_due' => $tasksDue,
            'deltas' => [
                'new_leads' => delta_vs($leads, $leadsY),
                'opportunities' => delta_vs($open['count'], $openY['count']),
                'pipeline_value' => delta_vs($open['value'], $openY['value']),
                'won_deals' => delta_vs($won, $wonY),
                'emails_month' => delta_vs($emails, $emailsY),
                'tasks_due' => delta_vs($tasksDue, $tasksY),
                'call' => delta_vs($types['call'] ?? 0, $typesY['call'] ?? 0),
                'meeting' => delta_vs($types['meeting'] ?? 0, $typesY['meeting'] ?? 0),
                'email' => delta_vs($types['email'] ?? 0, $typesY['email'] ?? 0),
                'note' => delta_vs($types['note'] ?? 0, $typesY['note'] ?? 0),
            ],
            'pipeline' => $pipeline,
            'by_rep' => Opportunity::pipelineByRep(),
            'forecast' => $forecast,
            'activity_chart' => self::activityChart(),
            'type_counts' => $types,
            'tasks' => Task::forHomeDue(null, 8),
            'meetings' => Meeting::upcoming(null, 6),
            'recent' => Activity::forHome(null, 5),
            'top_opps' => self::topOpps(),
            'needs_action' => Opportunity::needingAction(null, 8),
            'new_lead_rows' => Lead::newForHome(null, 6),
            'risk_deals' => SmartTipsService::atRiskDeals(null, 6),
            'risk_accounts' => SmartTipsService::atRiskAccounts(null, 4),
            'overdue' => Task::overdueCount(),
            'marketing' => self::marketingSnapshot($mkt),
        ];
    }

    /** @param array<string,mixed> $mkt */
    private static function marketingSnapshot(array $mkt): array
    {
        $delivered = (int) ($mkt['delivered'] ?? 0);
        return [
            'campaigns_sent' => (int) ($mkt['campaigns_sent'] ?? 0),
            'delivered' => $delivered,
            'opened' => (int) ($mkt['opened'] ?? 0),
            'clicked' => (int) ($mkt['clicked'] ?? 0),
            'unsubscribed' => (int) ($mkt['unsubscribed'] ?? 0),
            'bounced' => (int) ($mkt['bounced'] ?? 0),
            'open_pct' => $delivered ? round(((int) ($mkt['opened'] ?? 0) / $delivered) * 100, 1) : 0,
            'click_pct' => $delivered ? round(((int) ($mkt['clicked'] ?? 0) / $delivered) * 100, 1) : 0,
            'unsub_pct' => $delivered ? round(((int) ($mkt['unsubscribed'] ?? 0) / $delivered) * 100, 1) : 0,
            'bounce_pct' => $delivered ? round(((int) ($mkt['bounced'] ?? 0) / $delivered) * 100, 1) : 0,
        ];
    }

    /** @return array{labels:list<string>,values:list<int>} */
    private static function activityChart(): array
    {
        $rows = Activity::monthCounts();
        $byDay = [];
        foreach ($rows as $r) {
            $byDay[(string) $r['d']] = (int) $r['c'];
        }
        $labels = [];
        $values = [];
        $start = new \DateTimeImmutable(date('Y-m-01'));
        $end = new \DateTimeImmutable('today');
        for ($d = $start; $d <= $end; $d = $d->modify('+1 day')) {
            $key = $d->format('Y-m-d');
            $labels[] = $d->format('M j');
            $values[] = $byDay[$key] ?? 0;
        }
        return ['labels' => $labels, 'values' => $values];
    }

    public static function admin(): array
    {
        $tid = Auth::instance()->tenantId();
        $db = \App\Core\Database::instance();
        $people = $db->fetchAll(
            'SELECT u.id, u.email, u.first_name, u.last_name, u.last_login_at, tu.status AS login_status,
                    GROUP_CONCAT(r.code ORDER BY r.id SEPARATOR ",") AS role_codes,
                    GROUP_CONCAT(r.name ORDER BY r.id SEPARATOR ", ") AS role_names
             FROM users u
             INNER JOIN tenant_users tu ON tu.user_id = u.id AND tu.tenant_id = :tid
             LEFT JOIN user_roles ur ON ur.user_id = u.id AND ur.tenant_id = :tid2
             LEFT JOIN roles r ON r.id = ur.role_id
             WHERE u.deleted_at IS NULL
             GROUP BY u.id
             ORDER BY tu.status = \'active\' DESC, u.first_name, u.last_name
             LIMIT 8',
            ['tid' => $tid, 'tid2' => $tid]
        );
        $stopped = (int) $db->fetchColumn(
            "SELECT COUNT(*) FROM tenant_users WHERE tenant_id = :tid AND status <> 'active'",
            ['tid' => $tid]
        );
        $roleCounts = [];
        $roleActive = [];
        foreach ($db->fetchAll(
            "SELECT r.code,
                    COUNT(ur.user_id) AS total_n,
                    COALESCE(SUM(tu.status = 'active'), 0) AS active_n
             FROM roles r
             LEFT JOIN user_roles ur ON ur.role_id = r.id AND ur.tenant_id = :tid
             LEFT JOIN tenant_users tu ON tu.user_id = ur.user_id AND tu.tenant_id = :tid2
             GROUP BY r.id, r.code",
            ['tid' => $tid, 'tid2' => $tid]
        ) as $row) {
            $code = (string) ($row['code'] ?? '');
            $roleCounts[$code] = (int) ($row['total_n'] ?? 0);
            $roleActive[$code] = (int) ($row['active_n'] ?? 0);
        }
        return [
            'users' => (int) $db->fetchColumn(
                "SELECT COUNT(*) FROM tenant_users WHERE tenant_id = :tid AND status = 'active'",
                ['tid' => $tid]
            ),
            'stopped' => $stopped,
            'roles' => Role::all(),
            'roleCounts' => $roleCounts,
            'roleActive' => $roleActive,
            'people' => $people,
            'industry' => \App\Industry\Catalog::current(),
            'unsubscribes' => (int) (Unsubscribe::paginate(1, 1)['total'] ?? 0),
        ];
    }

    public static function marketing(): array
    {
        $campaigns = Campaign::paginate(1, 5)['rows'];
        foreach ($campaigns as &$c) {
            $c['stats'] = Campaign::stats((int) $c['id']);
        }
        unset($c);
        return [
            'summary' => Campaign::monthSummary(),
            'counts' => Campaign::boardCounts(),
            'campaigns' => $campaigns,
            'lists' => (int) (MarketingList::paginate(1, 1)['total'] ?? 0),
            'templates' => (int) (EmailTemplate::paginate(1, 1)['total'] ?? 0),
        ];
    }

    private static function openCount(?int $ownerId): int
    {
        $sql = "SELECT COUNT(*) FROM opportunities o
                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' => Auth::instance()->tenantId()];
        if ($ownerId) {
            $sql .= ' AND o.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        return (int) \App\Core\Database::instance()->fetchColumn($sql, $params);
    }

    private static function topOpps(): array
    {
        return \App\Core\Database::instance()->fetchAll(
            "SELECT o.id, o.name, o.amount, a.name AS account_name
             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
             ORDER BY o.amount DESC LIMIT 4",
            ['tid' => Auth::instance()->tenantId()]
        );
    }
}
