<?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;

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

        switch ($card_type) {
            case 'recette_bus':
                $title = 'Recette Bus';
                $columns = ['N° Versement', 'Date Versement', 'Période', 'Matricule', 'Opérateur', 'Montant TTC (DA)'];
                $rows = $db->select("
                    SELECT num_vers, DATE_FORMAT(date_vers,'%d/%m/%Y') as date_vers,
                           periode_p, matricule, operateur,
                           FORMAT(mt_ttc, 2) as mt_ttc
                    FROM bons_vers
                    WHERE type_vehicule = 'Bus'
                      AND periode_p >= ? AND periode_p <= ?
                    ORDER BY date_vers DESC
                ", [$m1, $m2]);
                break;

            case 'bus_pointe_paye':
                $title = 'Bus Pointés et Payés';
                $columns = ['N° Versement', 'Période', 'Matricule', 'Opérateur', 'Montant TTC (DA)'];
                $rows = $db->select("
                    SELECT bv.num_vers, bv.periode_p, bv.matricule, bv.operateur,
                           FORMAT(bv.mt_ttc, 2) as mt_ttc
                    FROM bons_vers bv
                    INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = 'Bus'
                    WHERE bv.type_vehicule = 'Bus'
                      AND bv.periode_p >= ? AND bv.periode_p <= ?
                      AND EXISTS (
                          SELECT 1 FROM checkins c
                          WHERE c.bus_id = b.id
                            AND c.checkin_date BETWEEN ? AND ?
                      )
                    ORDER BY bv.matricule
                ", [$m1, $m2, $d1, $d2]);
                break;

            case 'bus_pointe_non_paye':
                $title = 'Bus Pointés Non Payés';
                $columns = ['Code Bus', 'Matricule', 'Nb Pointages'];
                $rows = $db->select("
                    SELECT b.code_bus, b.matricule,
                           COUNT(c.id) as nb_pointages
                    FROM checkins c
                    INNER JOIN buses b ON b.id = c.bus_id AND b.type = 'Bus'
                    WHERE c.checkin_date BETWEEN ? AND ?
                      AND NOT EXISTS (
                          SELECT 1 FROM bons_vers bv
                          WHERE bv.matricule = b.matricule
                            AND bv.type_vehicule = 'Bus'
                            AND bv.periode_p >= ? AND bv.periode_p <= ?
                      )
                    GROUP BY b.id, b.code_bus, b.matricule
                    ORDER BY b.code_bus
                ", [$d1, $d2, $m1, $m2]);
                break;

            case 'bus_paye_non_pointe':
                $title = 'Bus Payés Non Pointés';
                $columns = ['N° Versement', 'Période', 'Matricule', 'Opérateur', 'Montant TTC (DA)'];
                $rows = $db->select("
                    SELECT bv.num_vers, bv.periode_p, bv.matricule, bv.operateur,
                           FORMAT(bv.mt_ttc, 2) as mt_ttc
                    FROM bons_vers bv
                    INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = 'Bus'
                    WHERE bv.type_vehicule = 'Bus'
                      AND bv.periode_p >= ? AND bv.periode_p <= ?
                      AND NOT EXISTS (
                          SELECT 1 FROM checkins c
                          WHERE c.bus_id = b.id
                            AND c.checkin_date BETWEEN ? AND ?
                      )
                    ORDER BY bv.matricule
                ", [$m1, $m2, $d1, $d2]);
                break;

            case 'bus_non_pointe_non_paye':
                $title = 'Bus Non Pointés et Non Payés';
                $columns = ['Code Bus', 'Matricule', 'Modèle', 'Capacité'];
                $rows = $db->select("
                    SELECT b.code_bus, b.matricule, b.modele, b.capacite
                    FROM buses b
                    WHERE b.type = 'Bus' AND b.actif = 1
                      AND NOT EXISTS (
                          SELECT 1 FROM bons_vers bv
                          WHERE bv.matricule = b.matricule
                            AND bv.type_vehicule = 'Bus'
                            AND bv.periode_p >= ? AND bv.periode_p <= ?
                      )
                      AND NOT EXISTS (
                          SELECT 1 FROM checkins c
                          WHERE c.bus_id = b.id
                            AND c.checkin_date BETWEEN ? AND ?
                      )
                    ORDER BY b.code_bus
                ", [$m1, $m2, $d1, $d2]);
                break;

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

            case 'taxi_pointe_paye':
                $title = 'Taxi Pointés et Payés';
                $columns = ['N° Versement', 'Période', 'Matricule', 'Opérateur', 'Montant TTC (DA)'];
                $rows = $db->select("
                    SELECT bv.num_vers, bv.periode_p, bv.matricule, bv.operateur,
                           FORMAT(bv.mt_ttc, 2) as mt_ttc
                    FROM bons_vers bv
                    INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = 'Taxi'
                    WHERE bv.type_vehicule = 'Taxi'
                      AND bv.periode_p >= ? AND bv.periode_p <= ?
                      AND EXISTS (
                          SELECT 1 FROM checkins c
                          WHERE c.bus_id = b.id
                            AND c.checkin_date BETWEEN ? AND ?
                      )
                    ORDER BY bv.matricule
                ", [$m1, $m2, $d1, $d2]);
                break;

            case 'taxi_pointe_non_paye':
                $title = 'Taxi Pointés Non Payés';
                $columns = ['Code Bus', 'Matricule', 'Nb Pointages'];
                $rows = $db->select("
                    SELECT b.code_bus, b.matricule,
                           COUNT(c.id) as nb_pointages
                    FROM checkins c
                    INNER JOIN buses b ON b.id = c.bus_id AND b.type = 'Taxi'
                    WHERE 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 bv.periode_p >= ? AND bv.periode_p <= ?
                      )
                    GROUP BY b.id, b.code_bus, b.matricule
                    ORDER BY b.code_bus
                ", [$d1, $d2, $m1, $m2]);
                break;

            case 'taxi_paye_non_pointe':
                $title = 'Taxi Payés Non Pointés';
                $columns = ['N° Versement', 'Période', 'Matricule', 'Opérateur', 'Montant TTC (DA)'];
                $rows = $db->select("
                    SELECT bv.num_vers, bv.periode_p, bv.matricule, bv.operateur,
                           FORMAT(bv.mt_ttc, 2) as mt_ttc
                    FROM bons_vers bv
                    INNER JOIN buses b ON b.matricule = bv.matricule AND b.type = 'Taxi'
                    WHERE bv.type_vehicule = 'Taxi'
                      AND bv.periode_p >= ? AND bv.periode_p <= ?
                      AND NOT EXISTS (
                          SELECT 1 FROM checkins c
                          WHERE c.bus_id = b.id
                            AND c.checkin_date BETWEEN ? AND ?
                      )
                    ORDER BY bv.matricule
                ", [$m1, $m2, $d1, $d2]);
                break;

            case 'taxi_non_pointe_non_paye':
                $title = 'Taxi Non Pointés et Non Payés';
                $columns = ['Code Bus', 'Matricule', 'Modèle', 'Capacité'];
                $rows = $db->select("
                    SELECT b.code_bus, b.matricule, b.modele, b.capacite
                    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 bv.periode_p >= ? AND bv.periode_p <= ?
                      )
                      AND NOT EXISTS (
                          SELECT 1 FROM checkins c
                          WHERE c.bus_id = b.id
                            AND c.checkin_date BETWEEN ? AND ?
                      )
                    ORDER BY b.code_bus
                ", [$m1, $m2, $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 = 'Bus'
          AND periode_p >= ? AND periode_p <= ?
    ", [$m1, $m2]);
    $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 = 'Bus'
        WHERE bv.type_vehicule = 'Bus'
          AND bv.periode_p >= ? AND bv.periode_p <= ?
          AND EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND c.checkin_date BETWEEN ? AND ?
          )
    ", [$m1, $m2, $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 = 'Bus'
        WHERE c.checkin_date BETWEEN ? AND ?
          AND NOT EXISTS (
              SELECT 1 FROM bons_vers bv
              WHERE bv.matricule = b.matricule
                AND bv.type_vehicule = 'Bus'
                AND bv.periode_p >= ? AND bv.periode_p <= ?
          )
    ", [$d1, $d2, $m1, $m2]);
    $bus_pointe_non_paye = (int)($row['cnt'] ?? 0);

    // Bus 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 = 'Bus'
        WHERE bv.type_vehicule = 'Bus'
          AND bv.periode_p >= ? AND bv.periode_p <= ?
          AND NOT EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND c.checkin_date BETWEEN ? AND ?
          )
    ", [$m1, $m2, $d1, $d2]);
    $bus_paye_non_pointe = (int)($row['cnt'] ?? 0);

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

    /* ======================== 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 periode_p >= ? AND periode_p <= ?
    ", [$m1, $m2]);
    $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 bv.periode_p >= ? AND bv.periode_p <= ?
          AND EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND c.checkin_date BETWEEN ? AND ?
          )
    ", [$m1, $m2, $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 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 bv.periode_p >= ? AND bv.periode_p <= ?
          )
    ", [$d1, $d2, $m1, $m2]);
    $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 bv.periode_p >= ? AND bv.periode_p <= ?
          AND NOT EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND c.checkin_date BETWEEN ? AND ?
          )
    ", [$m1, $m2, $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 bv.periode_p >= ? AND bv.periode_p <= ?
          )
          AND NOT EXISTS (
              SELECT 1 FROM checkins c
              WHERE c.bus_id = b.id
                AND c.checkin_date BETWEEN ? AND ?
          )
    ", [$m1, $m2, $d1, $d2]);
    $taxi_non_pointe_non_paye = (int)($row['cnt'] ?? 0);

    echo json_encode([
        'success' => true,
        'data' => [
            '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,
            'bus_paye_non_pointe'     => $bus_paye_non_pointe,
            'bus_non_pointe_non_paye' => $bus_non_pointe_non_paye,
            '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,
            'taxi_paye_non_pointe'    => $taxi_paye_non_pointe,
            'taxi_non_pointe_non_paye'=> $taxi_non_pointe_non_paye,
        ],
        'params' => [
            'date_debut' => $date_debut,
            'date_fin'   => $date_fin,
            'mois_debut' => $mois_debut,
            'mois_fin'   => $mois_fin,
        ]
    ]);

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