<?php
header('Content-Type: application/json');
require_once __DIR__ . '/../core/api.php';

try {
    $months = [];
    for ($i = 11; $i >= 0; $i--) {
        $months[] = date('Y-m', strtotime("-$i months"));
    }

    $data = [];

    foreach ($months as $month) {
        $data[$month] = [
            'leads' => 0,
            'clientes' => 0,
            'conversionRate' => 0,
            'serviciosVendidos' => 0,
            'facturacion' => 0.00,
            'ingresoReal' => 0.00,
            'porCobrar' => 0.00,
            'cotizacionesGeneradas' => 0,
            'citasNuevas' => 0,
            'fuentes' => []
        ];

        // A. LEADS (Prospectos)
        $stmt = $pdo->prepare("SELECT COUNT(*) as total FROM leads WHERE DATE_FORMAT(created_at, '%Y-%m') = :m");
        $stmt->execute(['m' => $month]);
        $data[$month]['leads'] = $stmt->fetchColumn();

        // B. CLIENTES NUEVOS
        $stmt = $pdo->prepare("SELECT COUNT(*) as total FROM clients WHERE DATE_FORMAT(created_at, '%Y-%m') = :m");
        $stmt->execute(['m' => $month]);
        $data[$month]['clientes'] = $stmt->fetchColumn();

        if ($data[$month]['leads'] > 0) {
            $data[$month]['conversionRate'] = round(($data[$month]['clientes'] / $data[$month]['leads']) * 100, 1);
        }

        // C. FINANZAS (Usando tabla 'invoices')
        $stmt = $pdo->prepare("SELECT SUM(total_amount) FROM invoices WHERE DATE_FORMAT(invoice_date, '%Y-%m') = :m");
        $stmt->execute(['m' => $month]);
        $data[$month]['facturacion'] = (float)$stmt->fetchColumn();

        $stmt = $pdo->prepare("SELECT SUM(total_amount) FROM invoices WHERE status = 'pagada' AND DATE_FORMAT(invoice_date, '%Y-%m') = :m");
        $stmt->execute(['m' => $month]);
        $data[$month]['ingresoReal'] = (float)$stmt->fetchColumn();

        // Por Cobrar 
        $stmt = $pdo->prepare("SELECT SUM(total_amount) FROM invoices WHERE status NOT IN ('pagada', 'cancelada') AND DATE_FORMAT(invoice_date, '%Y-%m') = :m");
        $stmt->execute(['m' => $month]);
        $data[$month]['porCobrar'] = (float)$stmt->fetchColumn();

        // D. COTIZACIONES (quotes - Corregido de 'estimates')
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM quotes WHERE DATE_FORMAT(estimate_date, '%Y-%m') = :m");
        $stmt->execute(['m' => $month]);
        $data[$month]['cotizacionesGeneradas'] = $stmt->fetchColumn();

        // E. CITAS
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM unique_appointments WHERE DATE_FORMAT(appointment_date, '%Y-%m') = :m");
        $stmt->execute(['m' => $month]);
        $data[$month]['citasNuevas'] = $stmt->fetchColumn();

        // F. FUENTES DE LEADS
        $stmt = $pdo->prepare("
            SELECT source, COUNT(*) as count 
            FROM leads 
            WHERE DATE_FORMAT(created_at, '%Y-%m') = :m 
            GROUP BY source 
            ORDER BY count DESC 
            LIMIT 5
        ");
        $stmt->execute(['m' => $month]);
        $data[$month]['fuentes'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    echo json_encode(['success' => true, 'data' => $data]);

} catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => 'Error de base de datos: ' . $e->getMessage()]);
}
?>