<?php
/**
 * API pour les données du Tableau de Bord
 * À installer sur le serveur cPanel dans le dossier api/apiggr/
 */

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: GET, POST, OPTIONS");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
    http_response_code(200);
    exit;
}

require_once __DIR__ . '/../config/database.php';

$action = $_REQUEST['action'] ?? '';

try {
    $db = Database::getInstance();

    switch ($action) {
        case 'get_dashboard_stats':
            $exerciseYear = isset($_REQUEST['exercise']) ? (int)$_REQUEST['exercise'] : date('Y');
            $month = isset($_REQUEST['month']) ? (int)$_REQUEST['month'] : date('n');
            
            // Optimisation 1: Plage de dates au lieu de YEAR() / MONTH() (utilise les index)
            $dateDebutAnnee = $exerciseYear . '-01-01';
            $dateFinAnnee = $exerciseYear . '-12-31';
            $dateDebutMois = $exerciseYear . '-' . str_pad($month, 2, '0', STR_PAD_LEFT) . '-01';
            $dateFinMois = date('Y-m-t', strtotime($dateDebutMois));

            $exercise = $db->selectOne("SELECT id FROM exercices WHERE annee = ?", [$exerciseYear]);

            if (!$exercise) {
                echo json_encode(['success' => false, 'message' => 'Exercice non trouvé']);
                exit;
            }
            $exerciseId = $exercise['id'];

            // Optimisation 2: Une seule requête pour tous les agrégats factures
            $facturesStats = $db->selectOne("
                SELECT 
                    COUNT(*) as total_factures,
                    SUM(CASE WHEN statut = 'payee' THEN 1 ELSE 0 END) as factures_payees,
                    SUM(CASE WHEN statut IN ('validee', 'brouillon') THEN 1 ELSE 0 END) as factures_en_attente,
                    COALESCE(SUM(CASE WHEN statut = 'payee' THEN montant_ttc ELSE 0 END), 0) as chiffre_affaires,
                    COALESCE(SUM(CASE WHEN statut = 'payee' AND date_facture BETWEEN ? AND ? THEN montant_ttc ELSE 0 END), 0) as ca_mensuel
                FROM factures 
                WHERE date_facture BETWEEN ? AND ?
            ", [$dateDebutMois, $dateFinMois, $dateDebutAnnee, $dateFinAnnee]);

            // Optimisation 3: Compte clients en une requête
            $totalClients = $db->selectOne("SELECT COUNT(*) as total FROM clients WHERE created_at BETWEEN ? AND ?", [$dateDebutAnnee, $dateFinAnnee])['total'];

            // Optimisation 4: Ventes groupées avec filtre plage dates
            $ventesData = $db->select(
                "SELECT v.type_prod, v.site, SUM(v.total_ttc) AS total
                 FROM ventes v
                 WHERE v.exercice_id = ? AND v.date_fact BETWEEN ? AND ?
                 GROUP BY v.type_prod, v.site
                 ORDER BY v.type_prod, v.site",
                 [$exerciseId, $dateDebutMois, $dateFinMois]
            );

            $stats = [
                'total_clients' => $totalClients,
                'total_factures' => $facturesStats['total_factures'],
                'factures_payees' => $facturesStats['factures_payees'],
                'factures_en_attente' => $facturesStats['factures_en_attente'],
                'chiffre_affaires' => $facturesStats['chiffre_affaires'],
                'ca_mensuel' => $facturesStats['ca_mensuel'],
                'ventes_data' => $ventesData
            ];

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

        case 'get_recent_activities':
            $activities = [];

            // Optimisation: Suppression de la boucle N+1 avec un JOIN direct
            $activities['factures'] = $db->select("
                SELECT f.id, f.numero_facture, f.date_facture, f.montant_ttc, f.statut, f.client_id,
                       COALESCE(c.nom, 'Inconnu') as nom, COALESCE(c.prenom, '') as prenom
                FROM factures f
                LEFT JOIN clients c ON f.client_id = c.id
                ORDER BY f.date_facture DESC LIMIT 5
            ");

            $activities['paiements'] = $db->select("SELECT p.*, f.numero_facture, c.nom, c.prenom FROM paiements p JOIN factures f ON p.facture_id = f.id JOIN clients c ON f.client_id = c.id ORDER BY p.date_paiement DESC LIMIT 5");
            $activities['clients'] = $db->select("SELECT id, code_client, nom, prenom, created_at FROM clients ORDER BY created_at DESC LIMIT 5");

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

        case 'get_exercises':
            $exercises = $db->select("SELECT * FROM exercices ORDER BY annee DESC");
            echo json_encode(['success' => true, 'data' => $exercises]);
            break;
            
        case 'get_exercise_by_year':
            $year = isset($_REQUEST['year']) ? (int)$_REQUEST['year'] : date('Y');
            $exercise = $db->selectOne("SELECT * FROM exercices WHERE annee = ?", [$year]);
            echo json_encode(['success' => true, 'data' => $exercise]);
            break;

        case 'get_sites':
            $sites = $db->select('SELECT SiteID, Nom_Site FROM Sites WHERE SiteID != "SG" ORDER BY SiteID');
            echo json_encode(['success' => true, 'data' => $sites]);
            break;

        default:
            echo json_encode(['success' => false, 'message' => 'Action inconnue']);
            break;
    }

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