<?php
// db/performance-read-data.php

function getPerformanceData($pdo, $userId, $currentYear = null) {
    if (is_null($currentYear)) {
        $currentYear = date('Y');
    }

    $sql = "
        SELECT
            month,
            SUM(leads) AS leads,
            SUM(clientes) AS clientes,
            SUM(serviciosVendidos) AS serviciosVendidos,
            SUM(facturacion) AS facturacion,
            SUM(cotizacionesGeneradas) AS cotizacionesGeneradas,
            SUM(facturasPagadas) AS facturasPagadas,
            SUM(citasNuevas) AS citasNuevas,
            SUM(citasRecurrentes) AS citasRecurrentes,
            SUM(proyectosCompletados) AS proyectosCompletados
        FROM (
            -- 1. Leads
            SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS leads, 0 AS clientes, 0 AS serviciosVendidos, 0 AS facturacion, 0 AS cotizacionesGeneradas, 0 AS facturasPagadas, 0 AS citasNuevas, 0 AS citasRecurrentes, 0 AS proyectosCompletados FROM leads WHERE user_id = :u1 AND YEAR(created_at) = :y1 GROUP BY month
            UNION ALL
            -- 2. Clientes
            SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, 0, COUNT(*) AS clientes, 0, 0, 0, 0, 0, 0, 0 FROM clients WHERE user_id = :u2 AND YEAR(created_at) = :y2 GROUP BY month
            UNION ALL
            -- 3. Servicios Vendidos (invoices, invoice_items) - FILTRO POR RELACIÓN
            SELECT 
                DATE_FORMAT(i.invoice_date, '%Y-%m') AS month, 0, 0, SUM(ii.quantity) AS serviciosVendidos, 0, 0, 0, 0, 0, 0 
            FROM invoices i 
            JOIN invoice_items ii ON i.id = ii.invoice_id 
            LEFT JOIN clients c ON i.client_id = c.id 
            LEFT JOIN leads l ON i.lead_id = l.id
            WHERE i.status = 'pagada' AND (c.user_id = :u3a OR l.user_id = :u3b) AND YEAR(i.invoice_date) = :y3 
            GROUP BY month
            UNION ALL
            -- 4. Facturación (finances) - FILTRO POR RELACIÓN (incluye nulos para finanzas generales)
            SELECT 
                DATE_FORMAT(f.transaction_date, '%Y-%m') AS month, 0, 0, 0, SUM(f.amount) AS facturacion, 0, 0, 0, 0, 0 
            FROM finances f
            LEFT JOIN clients c ON f.client_id = c.id
            WHERE f.type = 'ingreso' AND (c.user_id = :u4a OR f.client_id IS NULL) AND YEAR(f.transaction_date) = :y4 
            GROUP BY month
            UNION ALL
            -- 5. Cotizaciones Generadas (estimates) - FILTRO POR RELACIÓN
            SELECT 
                DATE_FORMAT(e.estimate_date, '%Y-%m') AS month, 0, 0, 0, 0, COUNT(*) AS cotizacionesGeneradas, 0, 0, 0, 0 
            FROM estimates e
            LEFT JOIN clients c ON e.client_id = c.id 
            LEFT JOIN leads l ON e.lead_id = l.id
            WHERE (c.user_id = :u5a OR l.user_id = :u5b) AND YEAR(e.estimate_date) = :y5 
            GROUP BY month
            UNION ALL
            -- 6. Facturas Pagadas (invoices) - FILTRO POR RELACIÓN
            SELECT 
                DATE_FORMAT(i.invoice_date, '%Y-%m') AS month, 0, 0, 0, 0, 0, COUNT(*) AS facturasPagadas, 0, 0, 0 
            FROM invoices i
            LEFT JOIN clients c ON i.client_id = c.id 
            LEFT JOIN leads l ON i.lead_id = l.id
            WHERE i.status = 'pagada' AND (c.user_id = :u6a OR l.user_id = :u6b) AND YEAR(i.invoice_date) = :y6 
            GROUP BY month
            UNION ALL
            -- 7. Citas Nuevas (unique_appointments) - Vinculamos a través de CLIENTES o PROSPECTOS
            SELECT 
                DATE_FORMAT(ua.appointment_date, '%Y-%m') AS month, 0, 0, 0, 0, 0, 0, COUNT(*) AS citasNuevas, 0, 0 
            FROM unique_appointments ua
            LEFT JOIN clients c ON ua.client_id = c.id
            LEFT JOIN leads l ON ua.lead_id = l.id
            WHERE (c.user_id = :u7a OR l.user_id = :u7b) AND YEAR(ua.appointment_date) = :y7 
            GROUP BY month
            UNION ALL
            -- 8. Citas Recurrentes (recurring_appointments) - Vinculamos a través de CLIENTES o PROSPECTOS
            SELECT 
                DATE_FORMAT(ra.next_appointment_date, '%Y-%m') AS month, 0, 0, 0, 0, 0, 0, 0, COUNT(*) AS citasRecurrentes, 0 
            FROM recurring_appointments ra
            LEFT JOIN clients c ON ra.client_id = c.id
            LEFT JOIN leads l ON ra.lead_id = l.id
            WHERE (c.user_id = :u8a OR l.user_id = :u8b) AND YEAR(ra.next_appointment_date) = :y8 
            GROUP BY month
            UNION ALL
            -- 9. Proyectos Completados (projects) - FILTRO POR RELACIÓN
            SELECT 
                DATE_FORMAT(p.updated_at, '%Y-%m') AS month, 0, 0, 0, 0, 0, 0, 0, 0, COUNT(*) AS proyectosCompletados 
            FROM projects p
            LEFT JOIN clients c ON p.client_id = c.id
            WHERE c.user_id = :u9 AND p.status = 'Archivado' AND YEAR(p.updated_at) = :y9 
            GROUP BY month
        ) AS combined_data
        GROUP BY month
        ORDER BY month ASC;
    ";

    $params = [
        ':u1' => $userId, ':y1' => $currentYear,
        ':u2' => $userId, ':y2' => $currentYear,
        ':u3a' => $userId, ':u3b' => $userId, ':y3' => $currentYear,
        ':u4a' => $userId, ':y4' => $currentYear,
        ':u5a' => $userId, ':u5b' => $userId, ':y5' => $currentYear,
        ':u6a' => $userId, ':u6b' => $userId, ':y6' => $currentYear,
        ':u7a' => $userId, ':u7b' => $userId, ':y7' => $currentYear,
        ':u8a' => $userId, ':u8b' => $userId, ':y8' => $currentYear,
        ':u9' => $userId, ':y9' => $currentYear,
    ];
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $formattedData = [];
    foreach ($results as $row) {
        $monthKey = $row['month'];
        $formattedData[$monthKey] = [
            'leads' => (int)$row['leads'],
            'clientes' => (int)$row['clientes'],
            'serviciosVendidos' => (int)$row['serviciosVendidos'],
            'facturacion' => (float)$row['facturacion'],
            'cotizacionesGeneradas' => (int)$row['cotizacionesGeneradas'],
            'facturasPagadas' => (int)$row['facturasPagadas'],
            'citasNuevas' => (int)$row['citasNuevas'],
            'citasRecurrentes' => (int)$row['citasRecurrentes'],
            'proyectosCompletados' => (int)$row['proyectosCompletados']
        ];
    }
    
    return $formattedData;
}
?>