<?php
// Requêtes complexes et spécifiques pour le projet VB.NET
// GET  /apiggr/tables  (action=select, table=..., columns=..., where=..., orderby=..., params=...)
// POST /apiggr/queries  Body: {"action": "...", "params": {...}}
// POST /apiggr/tables  Body: {"action": "insert|update|delete", ...}

header('Content-Type: application/json; charset=utf-8');
require_once __DIR__ . '/../../config/database.php';

$method = $_SERVER['REQUEST_METHOD'];

// Pour GET, lire les paramètres de la query string
// Pour POST, lire le body JSON
if ($method === 'GET') {
    $action = $_GET['action'] ?? '';
    $body = $_GET;
} else {
    $body = json_decode(file_get_contents('php://input'), true) ?? [];
    $action = $body['action'] ?? '';
}

try {
    $db = Database::getInstance()->getConnection();

    switch ($action) {

        // === CONNEXION / AUTH ===
        case 'login':
            $username = $body['username'] ?? '';
            $password = $body['password'] ?? '';
            $sql = "SELECT code_utilisateur, NomPrenom, NomPrenomAR, role, SiteID, statut 
                    FROM Utilisateurs 
                    WHERE BINARY code_utilisateur = :username 
                    AND BINARY mot_de_passe = :password 
                    AND statut = 'ACTIF'";
            $stmt = $db->prepare($sql);
            $stmt->execute([':username' => $username, ':password' => $password]);
            $user = $stmt->fetch();

            if (!$user) {
                http_response_code(401);
                echo json_encode(['success' => false, 'message' => 'Identifiants invalides']);
                exit;
            }

            $stmtSite = $db->prepare("SELECT Nom_Site, Nom_SiteAR FROM Sites WHERE SiteID = :id");
            $stmtSite->execute([':id' => $user['SiteID']]);
            $site = $stmtSite->fetch();

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

        // === SITES ===
        case 'get_last_site_id':
            $sql = "SELECT SiteID FROM Sites WHERE Nom_Site <> 'Siège' ORDER BY SiteID DESC LIMIT 1";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === DEPARTS ===
        case 'get_departs_ouverts':
            $sql = "SELECT * FROM Departs WHERE Etat_Depart = 'O' AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        case 'get_departs_fermes':
            $sql = "SELECT * FROM Departs WHERE Etat_Depart = 'F' AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        case 'count_departs_bus':
            $sql = "SELECT COUNT(*) as cnt FROM Departs WHERE BusID = :bus AND Site = :site 
                    AND DepartManuel = 'Non' AND (Etat_Depart IS NULL OR Etat_Depart = '' OR Etat_Depart = 'O')";
            $stmt = $db->prepare($sql);
            $stmt->execute([':bus' => $body['bus'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_tickets_stats_depart':
            $sql = "SELECT COUNT(*) AS NbreTickets, SUM(RedevancePassager) AS TotalRedevance, 
                    SUM(PrixTotal) AS TotalPrix, SUM(Prix) As PrixTickets 
                    FROM Tickets WHERE DepartID = :depart AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':depart' => $body['depart'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'count_feuilles_manuelles':
            $sql = "SELECT COUNT(*) AS Total FROM Feuilles WHERE Site = :site AND FeuilleManuelle = 'Non'";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_sum_prix_tickets':
            $sql = "SELECT SUM(PrixTotal) AS mtTickets FROM Tickets WHERE DepartID = :depart";
            $stmt = $db->prepare($sql);
            $stmt->execute([':depart' => $body['depart'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_last_ticket_id':
            $sql = "SELECT TicketID FROM Tickets WHERE Site = :site ORDER BY TicketID DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_all_feuille_ids':
            $sql = "SELECT FeuilleID FROM Feuilles";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        case 'get_all_ticket_ids':
            $sql = "SELECT TicketID FROM Tickets";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        case 'get_all_depart_ids':
            $sql = "SELECT DepartID FROM Departs";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        case 'update_tickets_feuille':
            $sql = "UPDATE Tickets SET FeuilleId = :feuilleId WHERE DepartID = :depart AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':feuilleId' => $body['feuilleId'] ?? '',
                ':depart' => $body['depart'] ?? '',
                ':site' => $body['site'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => ['affected' => $stmt->rowCount()]]);
            break;

        // === BUSGARES ===
        case 'get_bus_actifs':
            $sql = "SELECT Modele_Bus FROM BusGares WHERE Site = :site AND Etat = 'Actif' ORDER BY Modele_Bus";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        case 'get_bus_info':
            $sql = "SELECT Id_Ligne, Operateur_Bus, Capacite_Bus FROM BusGares 
                    WHERE Modele_Bus = :modele AND Etat = 'Actif' AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':modele' => $body['modele'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_matricule_bus':
            $sql = "SELECT Matricule_Bus FROM BusGares WHERE Modele_Bus = :modele AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':modele' => $body['modele'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'count_bus_by_operateur':
            $sql = "SELECT COUNT(*) AS NbreBus FROM BusGares WHERE Operateur_bus = :opr AND Etat = 'Actif'";
            $stmt = $db->prepare($sql);
            $stmt->execute([':opr' => $body['opr'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === JOURNALISATION ===
        case 'get_bus_by_operateur_dates':
            $sql = "SELECT BusID, COUNT(*) AS TotalOccurrences FROM Departs 
                    WHERE OperateurID = :opr AND Site = :site AND Etat_depart <> 'O' 
                    AND Date_Fermeture BETWEEN :dateDeb AND :dateFin GROUP BY BusID";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':opr' => $body['operateur'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        case 'get_feuilles_by_bus':
            $sql = "SELECT F.FeuilleID AS feuille, F.DateFeuille AS dateFeuille, F.Etat_Jrl AS etatJrl, 
                    D.Nbre_Tickets AS nbreTickets, D.Mt_Tickets AS mtTickets, D.MT_Redevance AS redevance 
                    FROM Feuilles F INNER JOIN Departs D ON D.DepartID = F.IdDepart 
                    WHERE D.BusID = :bus AND D.Site = :site AND F.DateFeuille BETWEEN :dateDeb AND :dateFin";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':bus' => $body['bus'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        case 'get_commission_dst':
            $sql = "SELECT TL.Commission, TL.DST, O.Mode_Paiement 
                    FROM (BusGares B INNER JOIN TypesLignes TL ON B.Type_Ligne = TL.Libelle_Type) 
                    INNER JOIN Operateurs O ON B.Operateur_Bus = O.NomOperateur 
                    WHERE B.Modele_Bus = :bus";
            $stmt = $db->prepare($sql);
            $stmt->execute([':bus' => $body['bus'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_last_journal_num':
            $sql = "SELECT Num_Journal FROM Journalisation WHERE Site = :site ORDER BY Num_Journal DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_all_journal_nums':
            $sql = "SELECT Num_Journal FROM Journalisation";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        // === OPERATEURS ===
        case 'get_max_operateur_id':
            $sql = "SELECT MAX(OperateurID) AS MaxID FROM Operateurs WHERE Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === LIGNES ===
        case 'get_max_ligne_id':
            $sql = "SELECT MAX(LigneID) AS MaxID FROM Lignes WHERE Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_last_ligne_id':
            $sql = "SELECT LigneID FROM Lignes";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        case 'count_ligne_nom':
            $sql = "SELECT COUNT(*) AS RecCount FROM Lignes WHERE NomLigne = :nom AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':nom' => $body['nom'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'count_ligne_nom_update':
            $sql = "SELECT COUNT(*) AS RecCount FROM Lignes WHERE NomLigne = :nom AND LigneID <> :id AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':nom' => $body['nom'] ?? '', ':id' => $body['id'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'count_departs_ligne':
            $sql = "SELECT COUNT(*) FROM Departs WHERE LigneID = :ligne AND Etat_Depart = 'F'";
            $stmt = $db->prepare($sql);
            $stmt->execute([':ligne' => $body['ligne'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === BUS ===
        case 'get_max_bus_id':
            $sql = "SELECT MAX(BusID) AS MaxID FROM BusGares";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_all_bus_ids':
            $sql = "SELECT BusID FROM BusGares";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        case 'count_departs_bus_etat':
            $sql = "SELECT COUNT(*) FROM Departs WHERE BusID = :bus AND Etat_Depart = 'F'";
            $stmt = $db->prepare($sql);
            $stmt->execute([':bus' => $body['bus'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'count_modele_bus':
            $sql = "SELECT COUNT(*) AS CountModel FROM BusGares WHERE Modele_Bus = :modele";
            $stmt = $db->prepare($sql);
            $stmt->execute([':modele' => $body['modele'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'count_modele_bus_update':
            $sql = "SELECT COUNT(*) AS RecCount FROM BusGares WHERE Modele_Bus = :modele AND BusID <> :id";
            $stmt = $db->prepare($sql);
            $stmt->execute([':modele' => $body['modele'] ?? '', ':id' => $body['id'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'update_nbre_bus_operateur':
            $sql = "UPDATE Operateurs SET NbreBus = :nbre WHERE NomOperateur = :opr";
            $stmt = $db->prepare($sql);
            $stmt->execute([':nbre' => $body['nbre'] ?? 0, ':opr' => $body['opr'] ?? '']);
            echo json_encode(['success' => true, 'data' => ['affected' => $stmt->rowCount()]]);
            break;

        // === TARIFS ===
        case 'get_max_tarif_id':
            $sql = "SELECT MAX(Id) AS MaxID FROM Tarifications WHERE Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_all_tarif_ids':
            $sql = "SELECT ID FROM Tarifications";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        // === TYPESLIGNES ===
        case 'count_types_lignes':
            $sql = "SELECT COUNT(*) AS NombreLignes FROM TypesLignes";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_all_type_ligne_ids':
            $sql = "SELECT Type_LigneID FROM TypesLignes";
            $stmt = $db->query($sql);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        // === ARRETS ===
        case 'count_arret_nom':
            $sql = "SELECT COUNT(*) FROM Arrets WHERE Nom_Arret = :nom";
            $stmt = $db->prepare($sql);
            $stmt->execute([':nom' => $body['nom'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'count_arret_tickets':
            $sql = "SELECT COUNT(*) FROM Tickets WHERE ArretDepart = :arret OR ArretArriver = :arret2";
            $stmt = $db->prepare($sql);
            $stmt->execute([':arret' => $body['arret'] ?? '', ':arret2' => $body['arret'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === UTILISATEURS ===
        case 'get_user_by_nom':
            $sql = "SELECT code_utilisateur FROM Utilisateurs WHERE NomPrenom = :nom AND SiteID = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':nom' => $body['nom'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_last_user_id':
            $sql = "SELECT code_utilisateur FROM Utilisateurs WHERE SiteID = :site ORDER BY code_utilisateur DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === CONSIGNES BAGAGES ===
        case 'get_last_consigne_id':
            $sql = "SELECT ConsigneID FROM ConsignesBagages WHERE Site = :site ORDER BY ConsigneID DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === AUTRES RECETTES ===
        case 'get_last_autres_recettes_num':
            $sql = "SELECT Num FROM AutresRecettes WHERE Site = :site ORDER BY Num DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === EXCEPTIONS ===
        case 'get_last_exception_num':
            $sql = "SELECT Num FROM Exceptions WHERE Site = :site ORDER BY Num DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === REMBOURSEMENT ===
        case 'get_last_ref_remb':
            $sql = "SELECT ReferenceRemb FROM Tickets WHERE Etat = 'Rembourser' AND Site = :site ORDER BY ReferenceRemb DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === EDITIONS / RAPPORTS ===
        case 'get_journal_by_num_bus':
            $sql = "SELECT * FROM Journalisation WHERE Num_Journal = :num AND Bus = :bus AND Site = :site AND Date_Du >= :dateDeb AND Date_Au <= :dateFin";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':num' => $body['journal'] ?? '',
                ':bus' => $body['bus'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        case 'get_distinct_dst_ht':
            $sql = "SELECT DISTINCT DST_HT FROM Journalisation WHERE Num_Journal = :num AND Site = :site AND Date_Du >= :dateDeb AND Date_Au <= :dateFin";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':num' => $body['journal'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        case 'get_journal_dst_grouped':
            $sql = "SELECT Operateur, Num_DST, COUNT(*) AS nbreFeuille, SUM(DST_HT) AS montant_HT, 
                    SUM(TVA_DST) AS taxes, SUM(Mt_Timbre) AS DroitTimbre, SUM(Mt_TTC) AS mt_TTC 
                    FROM Journalisation WHERE Num_Journal = :num AND DST_HT = :dst AND Site = :site 
                    AND Date_Du >= :dateDeb AND Date_Au <= :dateFin GROUP BY Operateur, Num_DST";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':num' => $body['journal'] ?? '',
                ':dst' => $body['dst'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_journal_com_grouped':
            $sql = "SELECT Num_Det, Num_Com, COUNT(*) AS nbreFeuille, SUM(Total_Montant) AS RecetteTTC, 
                    SUM(Recette_HT) AS recetteHT, Taux_Com, SUM(Mt_Taxe) AS mtHT, SUM(TVA_Com) AS totalTVA, 
                    SUM(Mt_TTC) AS totalTTC, SUM(Total_Com) As totalCom FROM Journalisation 
                    WHERE Num_Journal = :num AND Bus = :bus AND Site = :site AND Date_Du >= :dateDeb 
                    AND Date_Au <= :dateFin GROUP BY Num_Det, Num_Com, Taux_Com";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':num' => $body['journal'] ?? '',
                ':bus' => $body['bus'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === PENALITES / ANNULATIONS / REMBOURSEMENTS ===
        case 'get_penalites_sum':
            $sql = "SELECT SUM(Redevance) AS PenaliteRedevance, SUM(Commission) AS PenaliteCommission, 
                    SUM(DST) AS PenaliteDST FROM Penalites WHERE Operateur = :opr AND Site = :site 
                    AND DatePenalite >= :dateDeb AND DatePenalite <= :dateFin";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':opr' => $body['operateur'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_annulations_sum':
            $sql = "SELECT SUM(MtTickets) AS AnnulationTickets, SUM(Redevance) AS AnnulationRedevance 
                    FROM Annulations_Feuilles WHERE Operateur = :opr AND Site = :site 
                    AND DateAnnulation >= :dateDeb AND DateAnnulation <= :dateFin";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':opr' => $body['operateur'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_remboursements_sum':
            $sql = "SELECT SUM(Prix) AS mtTickets, SUM(RedevanceRemb) AS mtRembRedevance, 
                    SUM(CommissionRemb) AS commissionRemb FROM Tickets 
                    WHERE OperateurID = :opr AND Etat = 'Rembourser' AND Site = :site 
                    AND DateRemb >= :dateDeb AND DateRemb <= :dateFin";
            $stmt = $db->prepare($sql);
            $stmt->execute([
                ':opr' => $body['operateur'] ?? '',
                ':site' => $body['site'] ?? '',
                ':dateDeb' => $body['dateDeb'] ?? '',
                ':dateFin' => $body['dateFin'] ?? ''
            ]);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === ROLES ===
        case 'get_role_by_designation':
            $sql = "SELECT * FROM Roles WHERE UCASE(LTrim(RTrim(designation))) = :desig";
            $stmt = $db->prepare($sql);
            $stmt->execute([':desig' => strtoupper(trim($body['designation'] ?? ''))]);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_role_like':
            $sql = "SELECT * FROM Roles WHERE UCASE(designation) LIKE :desig";
            $stmt = $db->prepare($sql);
            $stmt->execute([':desig' => '%' . strtoupper($body['designation'] ?? '') . '%']);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        // === STATISTIQUES ===
        case 'get_tickets_with_site_filter':
            $where = "WHERE 1=1";
            $params = [];
            if (!empty($body['site'])) {
                $where .= " AND Site = :site";
                $params[':site'] = $body['site'];
            }
            if (!empty($body['user'])) {
                $where .= " AND UserID = :user";
                $params[':user'] = $body['user'];
            }
            if (!empty($body['dateDeb']) && !empty($body['dateFin'])) {
                $where .= " AND DateAchat BETWEEN :dateDeb AND :dateFin";
                $params[':dateDeb'] = $body['dateDeb'];
                $params[':dateFin'] = $body['dateFin'];
            }
            $sql = "SELECT TicketID, Prix, RedevancePassager, PrixTotal, MtRembVoyageur, DateAchat, UserID, Site FROM Tickets {$where}";
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        // === MDP ===
        case 'get_password':
            $sql = "SELECT mot_de_passe FROM Utilisateurs WHERE NomPrenom = :nom";
            $stmt = $db->prepare($sql);
            $stmt->execute([':nom' => $body['nom'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'update_password':
            $sql = "UPDATE Utilisateurs SET mot_de_passe = :mdp WHERE NomPrenom = :nom";
            $stmt = $db->prepare($sql);
            $stmt->execute([':mdp' => $body['mdp'] ?? '', ':nom' => $body['nom'] ?? '']);
            echo json_encode(['success' => true, 'data' => ['affected' => $stmt->rowCount()]]);
            break;

        // === CONSULTATION DEPARS ANNULEE ===
        case 'get_annulation_feuille':
            $sql = "SELECT D.DepartID, D.DateDepart, D.OperateurID, D.BusID, D.LigneID, D.Nbre_Tickets, 
                    D.Mt_Tickets, D.MT_Redevance, D.MtTotalDepart, F.FeuilleID, F.DateFeuille 
                    FROM Departs D INNER JOIN Feuilles F ON D.DepartID = F.IdDepart 
                    WHERE D.Site = :site AND (TRIM(F.Etat_Jrl) = '' OR F.Etat_Jrl IS NULL) 
                    AND D.Etat_Depart = 'F' AND D.DepartManuel = 'Non'";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        // === FEUILLES MANUELLES ===
        case 'get_last_depart_manuel':
            $sql = "SELECT DepartID FROM Departs WHERE Site = :site AND DepartManuel = 'Oui' ORDER BY DepartID DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_last_feuille_manuelle':
            $sql = "SELECT FeuilleID FROM Feuilles WHERE Site = :site AND FeuilleManuelle = 'Oui' ORDER BY FeuilleID DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'get_last_depart_non_manuel':
            $sql = "SELECT DepartID FROM Departs WHERE Site = :site AND DepartManuel = 'Non' ORDER BY DepartID DESC LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === TRAJETS ===
        case 'get_prix_redevance_trajet':
            $sql = "SELECT Prix, Redevance, PrixTotal FROM Trajets WHERE LigneID = :ligne AND ArretArriveeId = :arret AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':ligne' => $body['ligne'] ?? '', ':arret' => $body['arret'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        case 'count_trajets_ligne':
            $sql = "SELECT COUNT(*) FROM Trajets WHERE LigneID = :ligne AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':ligne' => $body['ligne'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === EDITION FEUILLE DE ROUTE ===
        case 'get_feuille_route':
            $sql = "SELECT F.FeuilleID, F.DateFeuille, F.EtatFeuille, F.FeuilleManuelle, D.DepartID, D.BusID, 
                    D.Nbre_Tickets, D.Mt_Tickets, D.MT_Redevance, D.MtTotalDepart 
                    FROM Feuilles F INNER JOIN Departs D ON F.IdDepart = D.DepartID 
                    WHERE D.Etat_Depart IN ('F', 'A') AND F.Site = :site AND F.DateFeuille >= :dateDeb AND F.DateFeuille < :dateFin";
            $stmt = $db->prepare($sql);
            $stmt->execute([':site' => $body['site'] ?? '', ':dateDeb' => $body['dateDeb'] ?? '', ':dateFin' => $body['dateFin'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        // === AFFECTATIONS ===
        case 'get_arrets_arrivee':
            $sql = "SELECT ArretArriveeID FROM Trajets WHERE LigneID = :ligne AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':ligne' => $body['ligne'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        // === TRANSFERT FEUILLE ===
        case 'get_bus_by_ligne':
            $sql = "SELECT Modele_Bus FROM BusGares WHERE Id_Ligne = :ligne AND Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':ligne' => $body['ligne'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll(PDO::FETCH_COLUMN)]);
            break;

        case 'get_ligne_info_by_modele':
            $sql = "SELECT L.NomLigne, L.LigneID, B.Operateur_Bus FROM Lignes L 
                    INNER JOIN BusGares B ON L.LigneID = B.Id_Ligne WHERE B.Modele_Bus = :modele AND B.Site = :site";
            $stmt = $db->prepare($sql);
            $stmt->execute([':modele' => $body['modele'] ?? '', ':site' => $body['site'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === TICKETS PARKING ===
        case 'count_ticket_parking':
            $sql = "SELECT COUNT(*) FROM Tickets_Parking WHERE numero_ticket = :num";
            $stmt = $db->prepare($sql);
            $stmt->execute([':num' => $body['num'] ?? '']);
            echo json_encode(['success' => true, 'data' => $stmt->fetch()]);
            break;

        // === CRUD GENERIQUE (SELECT/INSERT/UPDATE/DELETE) ===
        case 'select':
            $table = $body['table'] ?? '';
            $columns = $body['columns'] ?? '*';
            $where = $body['where'] ?? '';
            $orderby = $body['orderby'] ?? '';
            $params = $body['params'] ?? [];

            // Si params est une string JSON (GET), la décoder
            if (is_string($params)) {
                $params = json_decode($params, true) ?? [];
            }

            $sql = "SELECT {$columns} FROM {$table}";
            if (!empty($where)) $sql .= " WHERE {$where}";
            if (!empty($orderby)) $sql .= " ORDER BY {$orderby}";

            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        case 'insert':
            $table = $body['table'] ?? '';
            $data = $body['data'] ?? [];
            if (is_string($data)) $data = json_decode($data, true) ?? [];
            if (empty($table) || empty($data)) {
                http_response_code(400);
                echo json_encode(['success' => false, 'message' => 'Table ou données manquantes']);
                exit;
            }
            $columns = array_keys($data);
            $placeholders = array_map(function($c) { return ':' . $c; }, $columns);
            $sql = "INSERT INTO {$table} (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . ")";
            $stmt = $db->prepare($sql);
            $stmt->execute($data);
            echo json_encode(['success' => true, 'data' => ['affected_rows' => $stmt->rowCount(), 'last_insert_id' => $db->lastInsertId()]]);
            break;

        case 'update':
            $table = $body['table'] ?? '';
            $data = $body['data'] ?? [];
            $where = $body['where'] ?? [];
            if (is_string($data)) $data = json_decode($data, true) ?? [];
            if (is_string($where)) $where = json_decode($where, true) ?? [];
            if (empty($table) || empty($data) || empty($where)) {
                http_response_code(400);
                echo json_encode(['success' => false, 'message' => 'Données incomplètes']);
                exit;
            }
            $setClauses = [];
            $params = [];
            foreach ($data as $col => $val) {
                $setClauses[] = "{$col} = :set_{$col}";
                $params[":set_{$col}"] = $val;
            }
            $whereClauses = [];
            foreach ($where as $col => $val) {
                $whereClauses[] = "{$col} = :where_{$col}";
                $params[":where_{$col}"] = $val;
            }
            $sql = "UPDATE {$table} SET " . implode(', ', $setClauses) . " WHERE " . implode(' AND ', $whereClauses);
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            echo json_encode(['success' => true, 'data' => ['affected_rows' => $stmt->rowCount()]]);
            break;

        case 'delete':
            $table = $body['table'] ?? '';
            $where = $body['where'] ?? [];
            if (is_string($where)) $where = json_decode($where, true) ?? [];
            if (empty($table) || empty($where)) {
                http_response_code(400);
                echo json_encode(['success' => false, 'message' => 'Données incomplètes']);
                exit;
            }
            $whereClauses = [];
            $params = [];
            foreach ($where as $col => $val) {
                $whereClauses[] = "{$col} = :where_{$col}";
                $params[":where_{$col}"] = $val;
            }
            $sql = "DELETE FROM {$table} WHERE " . implode(' AND ', $whereClauses);
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            echo json_encode(['success' => true, 'data' => ['affected_rows' => $stmt->rowCount()]]);
            break;

        case 'raw_query':
            $sql = $body['sql'] ?? '';
            $params = $body['params'] ?? [];
            if (is_string($params)) {
                $params = json_decode($params, true) ?? [];
            }
            if (empty($sql)) {
                http_response_code(400);
                echo json_encode(['success' => false, 'message' => 'SQL manquant']);
                exit;
            }
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            echo json_encode(['success' => true, 'data' => $stmt->fetchAll()]);
            break;

        default:
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Action inconnue: ' . $action]);
    }

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