<?php
declare(strict_types=1);

require_once __DIR__ . '/../../security/auth_guard.php';
require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../core/Response.php';

$db = Database::getInstance();

/**
 * Helper
 */
function fetchAll(PDO $db, string $sql, array $params = []): array {
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * =========================
 * 1. Leads - Last 30 Days
 * =========================
 */
$dailyLeads = fetchAll($db, "
    SELECT
        DATE(created_at) AS label,
        COUNT(*) AS value
    FROM leads
    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY DATE(created_at)
    ORDER BY label ASC
");

/**
 * =========================
 * 2. Leads - Last 12 Months
 * =========================
 */
$monthlyLeads = fetchAll($db, "
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS label,
        COUNT(*) AS value
    FROM leads
    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
    ORDER BY label ASC
");

/**
 * =========================
 * 3. Lead Source Distribution
 * =========================
 */
$sourceDistribution = fetchAll($db, "
    SELECT
        source_type AS label,
        COUNT(*) AS value
    FROM leads
    GROUP BY source_type
");

/**
 * =========================
 * 4. Top 10 Dealers by Leads
 * =========================
 */
$topDealers = fetchAll($db, "
    SELECT
        d.dealer_name AS label,
        COUNT(l.id) AS value
    FROM leads l
    JOIN dealers d ON d.id = l.dealer_id
    GROUP BY l.dealer_id
    ORDER BY value DESC
    LIMIT 10
");

/**
 * =========================
 * Final Response
 * =========================
 */
Response::success('Dashboard graphs data fetched successfully', [
    'leads_daily_30_days'   => $dailyLeads,
    'leads_monthly_12_months' => $monthlyLeads,
    'lead_source_distribution' => $sourceDistribution,
    'top_dealers' => $topDealers
]);
