<?php
/**
 * API Dashboard Stats - VERSEMENTS (GGR Web App)
 * Supporte action=stats (défaut) et action=detail&card_type=xxx
 */
header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');

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

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

$date_debut = isset($_GET['date_debut']) ? trim($_GET['date_debut']) : date('Y-m-01');
$date_fin   = isset($_GET['date_fin'])   ? trim($_GET['date_fin'])   : date('Y-m-t');
$action     = isset($_GET['action'])     ? trim($_GET['action'])     : 'stats';
$card_type  = isset($_GET['card_type'])  ? trim($_GET['card_type'])  : '';

if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_debut)) $date_debut = date('Y-m-01');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_fin))   $date_fin   = date('Y-m-t');

$mois_debut = substr($date_debut, 0, 7);
$mois_fin   = substr($date_fin,   0, 7);

try {
    $db = Database::getInstance('transubd_PointageBus');
    $d1 = $date_debut;
    $d2 = $date_fin;
    $m1 = $mois_debut;
    $m2 = $mois_fin;

// Récupérer les tarifs TTC depuis la table tarifs
    $tarifs_raw = $db->select("SELECT type, ttc FROM tarifs");
    $tarifs = [];
    foreach ($tarifs_raw as $t) {
        $tarifs[$t['type']] = (float)$t['ttc'];
    }
$tarif_bus = $tarifs['Bus'] ?? 0;
    $tarif_taxi = $tarifs['Taxi'] ?? 0;
    $tarif_busgl = $tarifs['BusGL'] ?? 0;

    if ($action === 'detail') {
        $rows = [];
        $columns = [];
        $title = '';

        switch ($card_type) {
            case 'recette_bus':
                $title = "Recette Bus";
                // Individuel : Opérateur, Matricule, N° Versement, Date Versement, Période, Montant
                $columns = ['Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                $rows = $db->select("
SELECT operateur, matricule, num_vers, DATE_FORMAT(date_vers,'%d/%m/%Y') as date_vers, COALESCE(periode_p, '') as periode_p,
           mt_ttc as montant
    FROM bons_vers
    WHERE type_vehicule IN ('Bus', 'BusGL')
      AND DATE(date_vers) BETWEEN ? AND ?
                    ORDER BY date_vers DESC
                ", [$d1, $d2]);
                break;

            case 'recette_taxi':
                $title = "Recette Taxi";
                // Individuel : Opérateur, Matricule, N° Versement, Date Versement, Période, Montant
                $columns = ['Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                $rows = $db->select("
SELECT operateur, matricule, num_vers, DATE_FORMAT(date_vers,'%d/%m/%Y') as date_vers, COALESCE(periode_p, '') as periode_p,
           mt_ttc as montant
    FROM bons_vers
    WHERE type_vehicule = 'Taxi'
      AND DATE(date_vers) BETWEEN ? AND ?
                    ORDER BY date_vers DESC
                ", [$d1, $d2]);
                break;

            case 'bus_pointe_paye':
            case 'taxi_pointe_paye':
            case 'total_pointe_paye':
                if ($card_type === 'bus_pointe_paye') { 
                    $type = 'Bus'; $title = 'Bus Pointés et Payés'; 
                    $columns = ['Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                    $select = "bv.operateur, bv.matricule, bv.num_vers, DATE_FORMAT(bv.date_vers,'%d/%m/%Y') as date_vers, COALESCE(bv.periode_p, '') as periode_p, bv.mt_ttc as montant";
                } elseif ($card_type === 'taxi_pointe_paye') { 
                    $type = 'Taxi'; $title = 'Taxi Pointés et Payés'; 
                    $columns = ['Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                    $select = "bv.operateur, bv.matricule, bv.num_vers, DATE_FORMAT(bv.date_vers,'%d/%m/%Y') as date_vers, COALESCE(bv.periode_p, '') as periode_p, bv.mt_ttc as montant";
                } else { 
                    $type = null; $title = 'Total Parc Pointé et Payé'; 
                    $columns = ['Type', 'Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                    $select = "bv.type_vehicule as type_v, bv.operateur, bv.matricule, bv.num_vers, DATE_FORMAT(bv.date_vers,'%d/%m/%Y') as date_vers, COALESCE(bv.periode_p, '') as periode_p, bv.mt_ttc as montant";
                }

                $where = "WHERE DATE(bv.date_vers) BETWEEN ? AND ?";
                if ($type) $where .= " AND bv.type_vehicule IN ('Bus', 'BusGL')";

                $rows = $db->select("
SELECT $select
    FROM bons_vers bv
    INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = bv.type_vehicule
    $where
      AND EXISTS (
          SELECT 1 FROM checkins c
          WHERE c.bus_id = b.id
            AND DATE(c.checkin_date) BETWEEN ? AND ?
      )
                    ORDER BY bv.date_vers DESC
                ", [$d1, $d2, $d1, $d2]);
                break;

            case 'bus_pointe_non_paye':
            case 'taxi_pointe_non_paye':
            case 'total_pointe_non_paye':
                if ($card_type === 'bus_pointe_non_paye') { $type = 'Bus'; $title = 'Bus Pointés Non Payés'; }
                elseif ($card_type === 'taxi_pointe_non_paye') { $type = 'Taxi'; $title = 'Taxi Pointés Non Payés'; }
                else { $type = null; $title = 'Total Parc Pointé Non Payé'; }

                $columns = ['Code Bus', 'Opérateur', 'Matricule', 'Montant (DA)'];
                $where = "WHERE DATE(c.checkin_date) BETWEEN ? AND ?";
                if ($type === 'Bus') $where .= " AND b.type IN ('Bus', 'BusGL')";
                elseif ($type === 'Taxi') $where .= " AND b.type = '$type'";

                // Récupération Tarifs dynamiques
                $tarifs_raw = $db->select("SELECT type, ttc FROM tarifs WHERE type IN ('Bus', 'Taxi', 'BusGL')");
                $tarifs = [];
                foreach ($tarifs_raw as $t) { $tarifs[$t['type']] = (float)$t['ttc']; }
                $trf_bus = $tarifs['Bus'] ?? 0;
                $trf_taxi = $tarifs['Taxi'] ?? 0;
                $trf_busgl = $tarifs['BusGL'] ?? 0;

                $raw_rows = $db->select("
                    SELECT b.type, b.code_bus, COALESCE(o.name, 'N/A') as operateur, b.matricule
                    FROM checkins c
                    INNER JOIN buses b ON b.id = c.bus_id
                    LEFT JOIN operators o ON o.id = b.id_operateur
                    $where
                      AND NOT EXISTS (
                          SELECT 1 FROM bons_vers bv
                          WHERE bv.matricule = b.matricule
                            AND bv.type_vehicule = b.type
                            AND DATE(bv.date_vers) BETWEEN ? AND ?
                      )
                    GROUP BY b.id, b.type, o.name, b.matricule, b.code_bus
                    ORDER BY b.code_bus
                ", [$d1, $d2, $d1, $d2]);

                // Debug: afficher les données brutes
                error_log("DEBUG card_type=$card_type raw_rows: " . print_r($raw_rows, true));

                $result = [];
                foreach ($raw_rows as $r) {
                    $busType = isset($r['type']) ? $r['type'] : '';
                    if ($busType === 'Bus') {
                        $mt = -$trf_bus;
                    } elseif ($busType === 'BusGL') {
                        $mt = -$trf_busgl;
                    } else {
                        $mt = -$trf_taxi;
                    }
                    $result[] = [
                        'Code Bus' => $r['code_bus'],
                        'Opérateur' => $r['operateur'],
                        'Matricule' => $r['matricule'],
                        'Montant (DA)' => $mt  // Retourner comme float, pas formaté
                    ];
                }
                $rows = $result;
                break;

            case 'bus_paye_non_pointe':
            case 'taxi_paye_non_pointe':
            case 'total_paye_non_pointe':
                if ($card_type === 'bus_paye_non_pointe') { 
                    $type = 'Bus'; $title = 'Bus Payés Non Pointés'; 
                    $columns = ['Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                    $select = "bv.operateur, bv.matricule, bv.num_vers, DATE_FORMAT(bv.date_vers,'%d/%m/%Y') as date_vers, COALESCE(bv.periode_p, '') as periode_p, bv.mt_ttc as montant";
                } elseif ($card_type === 'taxi_paye_non_pointe') { 
                    $type = 'Taxi'; $title = 'Taxi Payés Non Pointés'; 
                    $columns = ['Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                    $select = "bv.operateur, bv.matricule, bv.num_vers, DATE_FORMAT(bv.date_vers,'%d/%m/%Y') as date_vers, COALESCE(bv.periode_p, '') as periode_p, bv.mt_ttc as montant";
                } else { 
                    $type = null; $title = 'Total Parc Payé Non Pointé'; 
                    $columns = ['Type', 'Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                    $select = "bv.type_vehicule as type_v, bv.operateur, bv.matricule, bv.num_vers, DATE_FORMAT(bv.date_vers,'%d/%m/%Y') as date_vers, COALESCE(bv.periode_p, '') as periode_p, bv.mt_ttc as montant";
                }

                $where = "WHERE DATE(bv.date_vers) BETWEEN ? AND ?";
                if ($type === 'Bus') $where .= " AND bv.type_vehicule IN ('Bus', 'BusGL')";
                elseif ($type === 'Taxi') $where .= " AND bv.type_vehicule = 'Taxi'";

                $rows = $db->select("
SELECT $select
    FROM bons_vers bv
    INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = bv.type_vehicule
    $where
      AND NOT EXISTS (
          SELECT 1 FROM checkins c
          WHERE c.bus_id = b.id
            AND DATE(c.checkin_date) BETWEEN ? AND ?
      )
                    ORDER BY bv.date_vers DESC
                ", [$d1, $d2, $d1, $d2]);
                break;

            case 'bus_non_pointe_non_paye':
            case 'taxi_non_pointe_non_paye':
            case 'total_non_pointe_non_paye':
                if ($card_type === 'bus_non_pointe_non_paye') { $type = 'Bus'; $title = 'Bus Non Pointés et Non Payés'; }
                elseif ($card_type === 'taxi_non_pointe_non_paye') { $type = 'Taxi'; $title = 'Taxi Non Pointés et Non Payés'; }
                else { $type = null; $title = 'Total Parc Non Pointé et Non Payé'; }

                $columns = ['Code Bus', 'Opérateur', 'Matricule', 'Montant (DA)'];
                $where = "WHERE b.actif = 1";
                if ($type === 'Bus') $where .= " AND b.type IN ('Bus', 'BusGL')";
                elseif ($type === 'Taxi') $where .= " AND b.type = '$type'";

                // Récupération Tarifs dynamiques
                $tarifs_raw = $db->select("SELECT type, ttc FROM tarifs WHERE type IN ('Bus', 'Taxi', 'BusGL')");
                $tarifs = [];
                foreach ($tarifs_raw as $t) { $tarifs[$t['type']] = (float)$t['ttc']; }
                $trf_bus = $tarifs['Bus'] ?? 0;
                $trf_taxi = $tarifs['Taxi'] ?? 0;
                $trf_busgl = $tarifs['BusGL'] ?? 0;

                $raw_rows = $db->select("
SELECT b.type, b.code_bus, COALESCE(o.name, 'N/A') as operateur, b.matricule
    FROM buses b
    LEFT JOIN operators o ON o.id = b.id_operateur
    $where
      AND NOT EXISTS (
          SELECT 1 FROM bons_vers bv
          WHERE bv.matricule = b.matricule
            AND bv.type_vehicule = b.type
            AND DATE(bv.date_vers) BETWEEN ? AND ?
      )
      AND NOT EXISTS (
          SELECT 1 FROM checkins c
          WHERE c.bus_id = b.id
            AND DATE(c.checkin_date) BETWEEN ? AND ?
      )
                    ORDER BY b.code_bus
                ", [$d1, $d2, $d1, $d2]);

                $result = [];
                foreach ($raw_rows as $r) {
                    $busType = isset($r['type']) ? $r['type'] : '';
                    if ($busType === 'Bus') {
                        $mt = -$trf_bus;
                    } elseif ($busType === 'BusGL') {
                        $mt = -$trf_busgl;
                    } else {
                        $mt = -$trf_taxi;
                    }
                    $result[] = [
                        'Code Bus' => $r['code_bus'],
                        'Opérateur' => $r['operateur'],
                        'Matricule' => $r['matricule'],
                        'Montant (DA)' => $mt  // Retourner comme float, pas formaté
                    ];
                }
                $rows = $result;
                break;

            case 'total_recette':
                $title = 'Total Recette (Bus + Taxi)';
                // Synthèse : Type, Opérateur, Matricule, N° Versement, Date Versement, Période, Montant
                $columns = ['Type', 'Opérateur', 'Matricule', 'N° Versement', 'Date Versement', 'Période', 'Montant'];
                $rows = $db->select("
SELECT type_vehicule as type_v, operateur, matricule, num_vers, DATE_FORMAT(date_vers,'%d/%m/%Y') as date_vers, COALESCE(periode_p, '') as periode_p,
           mt_ttc as montant
    FROM bons_vers
    WHERE type_vehicule IN ('Bus', 'BusGL', 'Taxi')
      AND DATE(date_vers) BETWEEN ? AND ?
                    ORDER BY date_vers DESC
                ", [$d1, $d2]);
                break;

            default:
                echo json_encode(['success' => false, 'message' => 'Type de carte invalide: ' . $card_type]);
                exit;
        }

        echo json_encode([
            'success'    => true,
            'title'      => $title,
            'columns'    => $columns,
            'data'       => $rows,
            'date_debut' => $d1,
            'date_fin'   => $d2,
        ]);
        exit;
    }

    // === Action stats (défaut) ===

    /* ======================== BUS ======================== */

    // Recette Bus
    $row = $db->selectOne("
SELECT COALESCE(SUM(mt_ttc), 0) as total, COUNT(*) as cnt
        FROM bons_vers
WHERE type_vehicule IN ('Bus', 'BusGL')
          AND DATE(date_vers) BETWEEN ? AND ?
    ", [$d1, $d2]);
    $recette_bus = (float)($row['total'] ?? 0);
    $recette_bus_count = (int)($row['cnt'] ?? 0);

    // Bus Pointé Payé : versé ET pointé sur la même période
    $row = $db->selectOne("
        SELECT COALESCE(SUM(bv.mt_ttc), 0) as total, COUNT(*) as cnt
        FROM bons_vers bv
        INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = bv.type_vehicule
WHERE bv.type_vehicule IN ('Bus', 'BusGL')
          AND DATE(bv.date_vers) BETWEEN ? AND ? AND EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND DATE(c.checkin_date) BETWEEN ? AND ?
          )
    ", [$d1, $d2, $d1, $d2]);
    $bus_pointe_paye = (float)($row['total'] ?? 0);
    $bus_pointe_paye_count = (int)($row['cnt'] ?? 0);

    // Bus Pointé Non Payé
    $row = $db->selectOne("
SELECT COUNT(DISTINCT c.bus_id) as cnt
        FROM checkins c
        INNER JOIN buses b ON b.id = c.bus_id AND b.type IN ('Bus', 'BusGL')
        WHERE DATE(c.checkin_date) BETWEEN ? AND ?
          AND NOT EXISTS (
              SELECT 1 FROM bons_vers bv
              WHERE bv.matricule = b.matricule
                AND bv.type_vehicule IN ('Bus', 'BusGL')
                AND DATE(bv.date_vers) BETWEEN ? AND ?
          )
    ", [$d1, $d2, $d1, $d2]);
    $bus_pointe_non_paye = (int)($row['cnt'] ?? 0);

    // Calcul du montant théorique pour Bus Pointé Non Payé
    $bus_ppnp_rows = $db->select("
SELECT b.type
    FROM checkins c
    INNER JOIN buses b ON b.id = c.bus_id AND b.type IN ('Bus', 'BusGL')
    WHERE DATE(c.checkin_date) BETWEEN ? AND ?
      AND NOT EXISTS (
          SELECT 1 FROM bons_vers bv
          WHERE bv.matricule = b.matricule
            AND bv.type_vehicule IN ('Bus', 'BusGL')
            AND DATE(bv.date_vers) BETWEEN ? AND ?
      )
    GROUP BY b.id, b.type
    ", [$d1, $d2, $d1, $d2]);
    $bus_pointe_non_paye_mt = 0;
    foreach ($bus_ppnp_rows as $bpr) {
        if ($bpr['type'] === 'Bus') {
            $bus_pointe_non_paye_mt -= $tarif_bus;
        } elseif ($bpr['type'] === 'BusGL') {
            $bus_pointe_non_paye_mt -= $tarif_busgl;
        }
    }

    // Bus Payé Non Pointé
    $row = $db->selectOne("
SELECT COUNT(DISTINCT bv.matricule) as cnt, COALESCE(SUM(bv.mt_ttc), 0) as total
        FROM bons_vers bv
        INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = bv.type_vehicule
WHERE bv.type_vehicule IN ('Bus', 'BusGL')
          AND DATE(bv.date_vers) BETWEEN ? AND ?
          AND NOT EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND DATE(c.checkin_date) BETWEEN ? AND ?
          )
    ", [$d1, $d2, $d1, $d2]);
    $bus_paye_non_pointe = (int)($row['cnt'] ?? 0);
    $bus_paye_non_pointe_mt = (float)($row['total'] ?? 0);

    // Bus Non Pointé Non Payé
    $row = $db->selectOne("
SELECT COUNT(*) as cnt
        FROM buses b
        WHERE b.type IN ('Bus', 'BusGL') AND b.actif = 1
          AND NOT EXISTS (
              SELECT 1 FROM bons_vers bv
              WHERE bv.matricule = b.matricule
                AND bv.type_vehicule IN ('Bus', 'BusGL')
                AND DATE(bv.date_vers) BETWEEN ? AND ?
          )
          AND NOT EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND DATE(c.checkin_date) BETWEEN ? AND ?
          )
    ", [$d1, $d2, $d1, $d2]);
    $bus_non_pointe_non_paye = (int)($row['cnt'] ?? 0);

    // Calcul du montant théorique pour Bus Non Pointé Non Payé
    $bus_rows = $db->select("
SELECT b.type
    FROM buses b
    WHERE b.type IN ('Bus', 'BusGL') AND b.actif = 1
      AND NOT EXISTS (
          SELECT 1 FROM bons_vers bv
          WHERE bv.matricule = b.matricule
            AND bv.type_vehicule IN ('Bus', 'BusGL')
            AND DATE(bv.date_vers) BETWEEN ? AND ?
      )
      AND NOT EXISTS (
          SELECT 1 FROM checkins c
          WHERE c.bus_id = b.id
            AND DATE(c.checkin_date) BETWEEN ? AND ?
      )
    ", [$d1, $d2, $d1, $d2]);
    $bus_non_pointe_non_paye_mt = 0;
    foreach ($bus_rows as $br) {
        if ($br['type'] === 'Bus') {
            $bus_non_pointe_non_paye_mt -= $tarif_bus;
        } elseif ($br['type'] === 'BusGL') {
            $bus_non_pointe_non_paye_mt -= $tarif_busgl;
        }
    }

    /* ======================== TAXI ======================== */

// Recette Taxi
    $row = $db->selectOne("
SELECT COALESCE(SUM(mt_ttc), 0) as total, COUNT(*) as cnt
        FROM bons_vers
        WHERE type_vehicule = 'Taxi'
          AND DATE(date_vers) BETWEEN ? AND ?
    ", [$d1, $d2]);
    $recette_taxi = (float)($row['total'] ?? 0);
    $recette_taxi_count = (int)($row['cnt'] ?? 0);

    // Taxi Pointé Payé
    $row = $db->selectOne("
SELECT COALESCE(SUM(bv.mt_ttc), 0) as total, COUNT(*) as cnt
        FROM bons_vers bv
        INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = 'Taxi'
        WHERE bv.type_vehicule = 'Taxi'
          AND DATE(bv.date_vers) BETWEEN ? AND ?
          AND EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND DATE(c.checkin_date) BETWEEN ? AND ?
          )
    ", [$d1, $d2, $d1, $d2]);
    $taxi_pointe_paye = (float)($row['total'] ?? 0);
    $taxi_pointe_paye_count = (int)($row['cnt'] ?? 0);

    // Taxi Pointé Non Payé
    $row = $db->selectOne("
        SELECT COUNT(DISTINCT c.bus_id) as cnt
        FROM checkins c
        INNER JOIN buses b ON b.id = c.bus_id AND b.type = 'Taxi'
        WHERE DATE(c.checkin_date) BETWEEN ? AND ?
          AND NOT EXISTS (
              SELECT 1 FROM bons_vers bv
              WHERE bv.matricule = b.matricule
                AND bv.type_vehicule = 'Taxi'
                AND DATE(bv.date_vers) BETWEEN ? AND ?
          )
    ", [$d1, $d2, $d1, $d2]);
    $taxi_pointe_non_paye = (int)($row['cnt'] ?? 0);

    // Taxi Payé Non Pointé
    $row = $db->selectOne("
        SELECT COUNT(DISTINCT bv.matricule) as cnt
        FROM bons_vers bv
        INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = 'Taxi'
        WHERE bv.type_vehicule = 'Taxi'
          AND DATE(bv.date_vers) BETWEEN ? AND ?
          AND NOT EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND DATE(c.checkin_date) BETWEEN ? AND ?
          )
    ", [$d1, $d2, $d1, $d2]);
    $taxi_paye_non_pointe = (int)($row['cnt'] ?? 0);

    // Taxi Non Pointé Non Payé
    $row = $db->selectOne("
        SELECT COUNT(*) as cnt
        FROM buses b
        WHERE b.type = 'Taxi' AND b.actif = 1
          AND NOT EXISTS (
              SELECT 1 FROM bons_vers bv
              WHERE bv.matricule = b.matricule
                AND bv.type_vehicule = 'Taxi'
                AND DATE(bv.date_vers) BETWEEN ? AND ?
          )
          AND NOT EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND DATE(c.checkin_date) BETWEEN ? AND ?
          )
    ", [$d1, $d2, $d1, $d2]);
    $taxi_non_pointe_non_paye = (int)($row['cnt'] ?? 0);

    // Calcul du montant théorique pour Taxi Non Pointé Non Payé
    $taxi_npnp_rows = $db->select("
SELECT b.type
    FROM buses b
    WHERE b.type = 'Taxi' AND b.actif = 1
      AND NOT EXISTS (
          SELECT 1 FROM bons_vers bv
          WHERE bv.matricule = b.matricule
            AND bv.type_vehicule = 'Taxi'
            AND DATE(bv.date_vers) BETWEEN ? AND ?
      )
      AND NOT EXISTS (
          SELECT 1 FROM checkins c
          WHERE c.bus_id = b.id
            AND DATE(c.checkin_date) BETWEEN ? AND ?
      )
    ", [$d1, $d2, $d1, $d2]);
    $taxi_non_pointe_non_paye_mt = 0;
    foreach ($taxi_npnp_rows as $tnr) {
        if ($tnr['type'] === 'Taxi') {
            $taxi_non_pointe_non_paye_mt -= $tarif_taxi;
        }
    }

    echo json_encode([
        'success' => true,
        'data' => [
// === BUS ===
            'recette_bus'               => $recette_bus,
            'recette_bus_count'         => $recette_bus_count,

            'bus_pointe_paye'           => $bus_pointe_paye,
            'bus_pointe_paye_count'     => $bus_pointe_paye_count,

            'bus_pointe_non_paye'       => $bus_pointe_non_paye,
            'mt_bus_pointe_non_paye'    => $bus_pointe_non_paye_mt,

            'bus_paye_non_pointe'       => $bus_paye_non_pointe,
            'mt_bus_paye_non_pointe'    => $bus_paye_non_pointe_mt,

            'bus_non_pointe_non_paye'   => $bus_non_pointe_non_paye,
            'mt_bus_non_pointe_non_paye'=> $bus_non_pointe_non_paye_mt,

            // === TAXI ===
            'recette_taxi'              => $recette_taxi,
            'recette_taxi_count'        => $recette_taxi_count,

            'taxi_pointe_paye'          => $taxi_pointe_paye,
            'taxi_pointe_paye_count'    => $taxi_pointe_paye_count,

            'taxi_pointe_non_paye'      => $taxi_pointe_non_paye,
            'mt_taxi_pointe_non_paye'   => -($taxi_pointe_non_paye * $tarif_taxi),

            'taxi_paye_non_pointe'      => $taxi_paye_non_pointe,
            'mt_taxi_paye_non_pointe'   => ($taxi_paye_non_pointe * $tarif_taxi),

            'taxi_non_pointe_non_paye'  => $taxi_non_pointe_non_paye,
            'mt_taxi_non_pointe_non_paye'=> $taxi_non_pointe_non_paye_mt,

            // === SYNTHÈSE (GR09) ===
            'total_recette'             => $recette_bus + $recette_taxi,
            'total_recette_count'       => $recette_bus_count + $recette_taxi_count,

            'total_pointe_paye'         => $bus_pointe_paye + $taxi_pointe_paye,
            'total_pointe_paye_count'   => $bus_pointe_paye_count + $taxi_pointe_paye_count,

            'total_pointe_non_paye_count' => $bus_pointe_non_paye + $taxi_pointe_non_paye,
            'total_pointe_non_paye_mt'    => -($bus_pointe_non_paye * $tarif_bus + $taxi_pointe_non_paye * $tarif_taxi),

            'total_paye_non_pointe_count' => $bus_paye_non_pointe + $taxi_paye_non_pointe,
            'total_paye_non_pointe_mt'    => ($bus_paye_non_pointe * $tarif_bus + $taxi_paye_non_pointe * $tarif_taxi),

            'total_non_pointe_non_paye_count' => $bus_non_pointe_non_paye + $taxi_non_pointe_non_paye,
            'total_non_pointe_non_paye_mt'    => $bus_non_pointe_non_paye_mt + $taxi_non_pointe_non_paye_mt
        ],
'params' => [
            'date_debut' => $date_debut,
            'date_fin'   => $date_fin,
            'tarif_bus'  => $tarif_bus,
            'tarif_taxi' => $tarif_taxi,
            'tarif_busgl' => $tarif_busgl
        ]
    ]);

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