<?php
// API DecisionnelAssist - Transub
// Agent IA pour requêtes bases de données

header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, OPTIONS');
header('Access-Control-Allow-Headers: Authorization, Content-Type');

$config = [
    'ggr' => ['host' => 'localhost', 'user' => 'transubd_transub_user', 'pass' => 'Tr@nsub2@24', 'name' => 'transubd_GGR'],
    'parc' => ['host' => 'localhost', 'user' => 'transubd_transub_user', 'pass' => 'Tr@nsub2@24', 'name' => 'transubd_GestionParc'],
    'grh' => ['host' => 'localhost', 'user' => 'transubd_transub_user', 'pass' => 'Tr@nsub2@24', 'name' => 'transubd_GRH']
];

$admin_user = ['email' => 'dg@transub.dz', 'password' => 'Tr@nsub2@26', 'nom' => 'PDG Transub'];

function getDB($db = 'ggr') {
    global $config;
    $c = $config[$db];
    $pdo = new PDO("mysql:host={$c['host']};dbname={$c['name']}", $c['user'], $c['pass']);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $pdo;
}

function checkAuth() {
    // Try to get headers from various sources
    $token = '';
    
    // Method 1: Check $_SERVER variables (works on most setups)
    if (isset($_SERVER['HTTP_AUTHORIZATION'])) {
        $token = $_SERVER['HTTP_AUTHORIZATION'];
    } elseif (isset($_SERVER['AUTHORIZATION'])) {
        $token = $_SERVER['AUTHORIZATION'];
    } elseif (isset($_SERVER['HTTP_AUTHORIZATION_BEARER'])) {
        $token = $_SERVER['HTTP_AUTHORIZATION_BEARER'];
    }
    
    // Method 2: Try apache_request_headers if available
    if (empty($token) && function_exists('apache_request_headers')) {
        $headers = apache_request_headers();
        if (isset($headers['Authorization'])) {
            $token = $headers['Authorization'];
        }
    }
    
    // Method 3: Try getallheaders
    if (empty($token) && function_exists('getallheaders')) {
        $headers = getallheaders();
        foreach ($headers as $key => $value) {
            if (strtolower($key) === 'authorization') {
                $token = $value;
                break;
            }
        }
    }
    
    // Method 4: Check GET parameter as fallback
    if (empty($token) && isset($_GET['api_token'])) {
        $token = $_GET['api_token'];
    }
    
    // Method 5: Check POST body - some servers strip headers
    if (empty($token)) {
        $input = json_decode(file_get_contents('php://input'), true);
        if (isset($input['token'])) {
            $token = $input['token'];
        }
    }
    
    // Clean the token
    $token = str_replace('Bearer ', '', $token);
    $token = str_replace('bearer ', '', $token);
    
    return $token === 'PDG2024';
}

// Schéma des bases pour l'agent
$schema = [
    'ggr' => [
        'tickets' => ['cols' => ['TicketID', 'DateAchat', 'Prix', 'RedevancePassager', 'PrixTotal', 'Site', 'LigneID', 'OperateurID', 'ArretDepart', 'ArretArriver', 'Etat'], 'date_col' => 'DateAchat'],
        'feuilles' => ['cols' => ['FeuilleID', 'DateCreation', 'Site', 'BusID', 'OperateurID', 'RecetteTotal', 'NbBillets'], 'date_col' => 'DateCreation'],
        'depart' => ['cols' => ['DepartID', 'DateDepart', 'Site', 'Ligne', 'BusID'], 'date_col' => 'DateDepart'],
        'sites' => ['cols' => ['SiteID', 'NomSite', 'Gare', 'Ville'], 'date_col' => null],
        'lignes' => ['cols' => ['LigneID', 'NomLigne', 'Site', 'TypeLigne'], 'date_col' => null],
        'busgares' => ['cols' => ['BusGareID', 'Matricule', 'Site', 'Statut'], 'date_col' => null]
    ],
    'parc' => [
        'bus' => ['cols' => ['BusID', 'Matricule', 'Marque', 'Modele', 'Statut', 'DateMiseCirculation', 'Kilometrage'], 'date_col' => 'DateMiseCirculation'],
        'interventions' => ['cols' => ['InterventionID', 'BusID', 'DateIntervention', 'TypeIntervention', 'Description', 'Cout', 'Technicien'], 'date_col' => 'DateIntervention'],
        'pannes' => ['cols' => ['PanneID', 'BusID', 'DatePanne', 'Description', 'StatutPanne', 'Gravite'], 'date_col' => 'DatePanne'],
        'affectations' => ['cols' => ['AffectationID', 'BusID', 'ChauffeurID', 'DateAffectation', 'Ligne'], 'date_col' => 'DateAffectation']
    ],
    'grh' => [
        'salaries' => ['cols' => ['id', 'nom', 'prenom', 'matricule', 'service', 'fonction', 'date_embauche', 'situation_familiale', 'cin', 'telephone'], 'date_col' => 'date_embauche'],
        'pointage' => ['cols' => ['id', 'matricule', 'date_pointage', 'heure_arrivee', 'heure_depart', 'retard', 'absence'], 'date_col' => 'date_pointage'],
        'conge' => ['cols' => ['id', 'matricule', 'date_debut', 'date_fin', 'type_conge', 'motif', 'statut'], 'date_col' => 'date_debut'],
        'absences' => ['cols' => ['id', 'matricule', 'date_absence', 'motif', 'justifie'], 'date_col' => 'date_absence']
    ]
];

// Liste des gares/sites
$knownSites = ['GR05', 'GR01', 'GR02', 'GR03', 'PA01', 'ALGER', 'ORAN', 'CONSTANTINE', 'ANNABA', 'BEJAIA', 'BATNA', 'SIDI CHEIKH', 'SIDI CHEIKH (GR05)'];
$knownGares = ['Sidi Cheikh', 'Alger', 'Oran', 'Constantine', 'Annaba', 'Béjaïa', 'Batna', 'Tébessa', 'Skikda', 'Biskra', 'El Oued', 'Ghardaïa'];

function detectPeriode($q) {
    $periode = 'today';  // Default: today
    if (preg_match('/hier/i', $q)) $periode = 'yesterday';
    elseif (preg_match('/semaine dernière|semaine der/i', $q)) $periode = 'lastWeek';
    elseif (preg_match('/ce mois|mois en cours/i', $q)) $periode = 'month';
    elseif (preg_match('/mois dernier/i', $q)) $periode = 'lastMonth';
    elseif (preg_match('/annee|annee dernière|toute/i', $q)) $periode = 'year';
    return $periode;
}

function getPeriodeDates($periode) {
    $now = new DateTime();
    $start = clone $now;
    $end = clone $now;
    
    switch ($periode) {
        case 'today':
            $start->setTime(0,0,0);
            $end->setTime(23,59,59);
            break;
        case 'yesterday':
            $start->modify('-1 day')->setTime(0,0,0);
            $end->modify('-1 day')->setTime(23,59,59);
            break;
        case 'lastWeek':
            $start->modify('-1 week')->modify('Monday')->setTime(0,0,0);
            $end->modify('Sunday')->setTime(23,59,59);
            break;
        case 'month':
            $start->modify('first day of this month')->setTime(0,0,0);
            $end->modify('last day of this month')->setTime(23,59,59);
            break;
        case 'lastMonth':
            $start->modify('first day of last month')->setTime(0,0,0);
            $end->modify('last day of last month')->setTime(23,59,59);
            break;
        case 'year':
            $start->modify('first day of January')->setTime(0,0,0);
            $end->modify('last day of December')->setTime(23,59,59);
            break;
        default:
            // Default: today
            $start->setTime(0,0,0);
            $end->setTime(23,59,59);
    }
    
    return ['start' => $start->format('Y-m-d H:i:s'), 'end' => $end->format('Y-m-d H:i:s')];
}

function detectGare($q) {
    global $knownGares;
    $q = strtolower($q);
    foreach ($knownGares as $gare) {
        if (stripos($q, strtolower($gare)) !== false) {
            return $gare;
        }
    }
    return null;
}

function detectSite($q) {
    global $knownSites;
    $q = strtoupper($q);
    foreach ($knownSites as $site) {
        if (strpos($q, $site) !== false) {
            return $site;
        }
    }
    return null;
}

function buildSQL($parsed) {
    $db = $parsed['db'];
    $table = $parsed['table'];
    $select = $parsed['select'];
    $where = $parsed['where'];
    $group = $parsed['group'] ?? '';
    $order = $parsed['order'] ?? '';
    
    $sql = "SELECT $select FROM $table";
    if (!empty($where)) $sql .= " WHERE $where";
    if (!empty($group)) $sql .= " GROUP BY $group";
    if (!empty($order)) $sql .= " ORDER BY $order";
    
    return $sql;
}

function parseAndExecute($question) {
    $q = strtolower($question);
    $periode = detectPeriode($q);
    $dates = getPeriodeDates($periode);
    $gare = detectGare($question);
    $site = detectSite($question);
    
    // Types de requêtes
    $types = [
        'recettes' => ['recette', 'revenu', 'argent', 'gain', 'billet', 'vente', 'chiffre'],
        'passagers' => ['passager', 'voyageur', 'trafic', 'frequentation', 'nb voyageurs', 'nombre voyageurs'],
        'retards' => ['retard', 'retardé', 'en retard'],
        'flotte' => ['bus', 'flotte', 'vehicule', 'parking', 'disponible'],
        'personnel' => ['personnel', 'employe', 'salarié', 'effectif', 'presence', 'employés'],
        'interventions' => ['intervention', 'maintenance', 'reparation', 'panne'],
        'departs' => ['depart', 'voyage', 'trajet', 'ligne'],
        'site' => ['site', 'gare']
    ];
    
    $detectedType = null;
    foreach ($types as $type => $keywords) {
        foreach ($keywords as $kw) {
            if (strpos($q, $kw) !== false) {
                $detectedType = $type;
                break 2;
            }
        }
    }
    
    if (!$detectedType) {
        // Essayer de deviner basé sur les mots présents
        if (preg_match('/(combien|total|montant|somme)/', $q)) {
            if (preg_match('/(bus|vehicule)/', $q)) $detectedType = 'flotte';
            elseif (preg_match('/(employe|personnel|salarié)/', $q)) $detectedType = 'personnel';
            else $detectedType = 'recettes';
        }
    }
    
    $result = ['success' => false, 'text' => '', 'data' => [], 'periode' => $periode];
    
    try {
        switch ($detectedType) {
            case 'recettes':
                $result = handleRecettes($dates, $gare, $site, $q);
                break;
            case 'passagers':
                $result = handlePassagers($dates, $gare, $site, $q);
                break;
            case 'flotte':
                $result = handleFlotte($q);
                break;
            case 'personnel':
                $result = handlePersonnel($dates, $q);
                break;
            case 'retards':
                $result = handleRetards($dates, $q);
                break;
            case 'interventions':
                $result = handleInterventions($dates, $q);
                break;
            case 'departs':
                $result = handleDeparts($dates, $gare, $site);
                break;
            case 'site':
                $result = handleSites();
                break;
            default:
                $result = handleGeneral($question);
        }
    } catch (Exception $e) {
        $result = ['success' => false, 'text' => "Erreur: " . $e->getMessage()];
    }
    
    return $result;
}

function handleRecettes($dates, $gare, $site, $q) {
    $pdo = getDB('ggr');
    $text = "";
    $data = [];
    
    // Debug: Check table exists and has data
    $check = $pdo->query("SELECT COUNT(*) as cnt FROM tickets LIMIT 1")->fetch(PDO::FETCH_ASSOC);
    if ($check['cnt'] == 0) {
        $text = "⚠️ La table tickets est vide.\n";
        return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
    }
    
    // Recettes par tickets - using DateAchat
    $sql = "SELECT COALESCE(SUM(PrixTotal), 0) as total_recettes, 
            COUNT(*) as nb_tickets,
            COALESCE(SUM(RedevancePassager), 0) as total_redevance
            FROM tickets 
            WHERE DateAchat BETWEEN ? AND ?";
    $params = [$dates['start'], $dates['end']];
    
    if ($site) {
        $sql .= " AND Site = ?";
        $params[] = $site;
    }
    
    if ($gare) {
        $sql .= " AND (ArretDepart LIKE ? OR ArretArriver LIKE ?)";
        $params[] = "%$gare%";
        $params[] = "%$gare%";
    }
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    $text .= "💰 **RECETTES**\n";
    $text .= "Période: Aujourd'hui\n\n";
    $text .= "• Total recettes: **" . number_format($row['total_recettes'], 0, ',', ' ') . " DA**\n";
    $text .= "• Nombre de billets: **" . number_format($row['nb_tickets'], 0, ',', ' ') . "**\n";
    $text .= "• Redevance passagers: **" . number_format($row['total_redevance'], 0, ',', ' ') . " DA**\n";
    
    if ($gare || $site) {
        $text .= "\n📍 Filtre: " . ($gare ? "Gare $gare" : "Site $site");
    }
    
    $data[] = ['type' => 'recettes', 'total' => $row['total_recettes'], 'nb_tickets' => $row['nb_tickets']];
    
    // Recettes par site si pas de filtre
    if (!$gare && !$site) {
        $sql2 = "SELECT Site, SUM(PrixTotal) as total, COUNT(*) as nb 
                FROM tickets WHERE DateAchat BETWEEN ? AND ? 
                GROUP BY Site ORDER BY total DESC LIMIT 5";
        $stmt2 = $pdo->prepare($sql2);
        $stmt2->execute([$dates['start'], $dates['end']]);
        $rows = $stmt2->fetchAll(PDO::FETCH_ASSOC);
        
        $text .= "\n\n📊 **Top Sites:**\n";
        foreach ($rows as $r) {
            if ($r['Site']) {
                $text .= "• {$r['Site']}: " . number_format($r['total'], 0, ',', ' ') . " DA ({$r['nb']} billets)\n";
            }
        }
    }
    
    return ['success' => true, 'text' => $text, 'data' => json_encode($data), 'periode' => 'today'];
}

function handlePassagers($dates, $gare, $site, $q) {
    $pdo = getDB('ggr');
    $text = "🚶 **PASSAGERS**\n";
    $text .= "Période: 7 derniers jours\n\n";
    
    $sql = "SELECT COUNT(*) as nb_passagers FROM tickets 
            WHERE DateAchat BETWEEN ? AND ? AND Prix > 0";
    $params = [$dates['start'], $dates['end']];
    
    if ($site) {
        $sql .= " AND Site = ?";
        $params[] = $site;
    }
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    $text .= "• Total voyageurs: **" . number_format($row['nb_passagers'], 0, ',', ' ') . "**\n";
    
    // Répartition par ligne
    $sql2 = "SELECT LigneID, COUNT(*) as nb FROM tickets 
            WHERE DateAchat BETWEEN ? AND ? 
            GROUP BY LigneID ORDER BY nb DESC LIMIT 5";
    $stmt2 = $pdo->prepare($sql2);
    $stmt2->execute([$dates['start'], $dates['end']]);
    $rows = $stmt2->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "\n📍 **Top Lignes:**\n";
    foreach ($rows as $r) {
        if ($r['LigneID']) {
            $text .= "• " . substr($r['LigneID'], 0, 40) . ": {$r['nb']} voyageurs\n";
        }
    }
    
    return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
}

function handleFlotte($q) {
    $pdo = getDB('parc');
    $text = "🚌 **ÉTAT DE LA FLOTTE**\n\n";
    
    // Statut des bus (colonne EtAt)
    $sql = "SELECT `Etat`, COUNT(*) as nb FROM Bus GROUP BY `Etat`";
    $stmt = $pdo->query($sql);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "📊 **Par Statut:**\n";
    foreach ($rows as $r) {
        $text .= "• {$r['Etat']}: **{$r['nb']}** bus\n";
    }
    
    // Disponibles
    $sql2 = "SELECT COUNT(*) as nb FROM Bus WHERE `Etat` = 'En Marche'";
    $stmt2 = $pdo->query($sql2);
    $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);
    $text .= "\n✅ **En service:** {$row2['nb']} bus\n";
    
    // En réparation
    $sql3 = "SELECT COUNT(*) as nb FROM Bus WHERE `Etat` = 'En Réparation'";
    $stmt3 = $pdo->query($sql3);
    $row3 = $stmt3->fetch(PDO::FETCH_ASSOC);
    $text .= "🔧 **En réparation:** {$row3['nb']} bus\n";
    
    // Immobilisé
    $sql4 = "SELECT COUNT(*) as nb FROM Bus WHERE `Etat` = 'Immobilisé'";
    $stmt4 = $pdo->query($sql4);
    $row4 = $stmt4->fetch(PDO::FETCH_ASSOC);
    $text .= "⛔ **Immobilisés:** {$row4['nb']} bus\n";
    
    // Marque
    $sql5 = "SELECT Marque, COUNT(*) as nb FROM Bus WHERE Marque IS NOT NULL GROUP BY Marque ORDER BY nb DESC";
    $stmt5 = $pdo->query($sql5);
    $rows5 = $stmt5->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "\n🏷️ **Par Marque:**\n";
    foreach ($rows5 as $r) {
        if ($r['Marque']) $text .= "• {$r['Marque']}: {$r['nb']}\n";
    }
    
    // Type bus
    $sql6 = "SELECT type_bus, COUNT(*) as nb FROM Bus WHERE type_bus IS NOT NULL GROUP BY type_bus ORDER BY nb DESC";
    $stmt6 = $pdo->query($sql6);
    $rows6 = $stmt6->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "\n🚍 **Par Type:**\n";
    foreach ($rows6 as $r) {
        if ($r['type_bus']) $text .= "• {$r['type_bus']}: {$r['nb']}\n";
    }
    
    return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
}

function handlePersonnel($dates, $q) {
    $pdo = getDB('grh');
    $text = "👥 **PERSONNEL**\n\n";
    
    // Effectif total - uniquement actifs
    $sql = "SELECT COUNT(*) as total FROM salaries WHERE `EtatSal` = 'Actif'";
    $stmt = $pdo->query($sql);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $text .= "• Effectif total (Actifs): **{$row['total']}** employés\n";
    
    // Par Affectation (service) - uniquement actifs
    $sql2 = "SELECT Affectation, COUNT(*) as nb FROM salaries WHERE Affectation IS NOT NULL AND Affectation != '' AND `EtatSal` = 'Actif' GROUP BY Affectation ORDER BY nb DESC";
    $stmt2 = $pdo->query($sql2);
    $rows2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "\n📋 **Par Affectation:**\n";
    foreach ($rows2 as $r) {
        $text .= "• {$r['Affectation']}: **{$r['nb']}**\n";
    }
    
    // Par Direction - uniquement actifs
    $sql3 = "SELECT Direction, COUNT(*) as nb FROM salaries WHERE Direction IS NOT NULL AND Direction != '' AND `EtatSal` = 'Actif' GROUP BY Direction ORDER BY nb DESC";
    $stmt3 = $pdo->query($sql3);
    $rows3 = $stmt3->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "\n🏢 **Par Direction:**\n";
    foreach ($rows3 as $r) {
        $text .= "• {$r['Direction']}: **{$r['nb']}**\n";
    }
    
    // Par Fonction - uniquement actifs
    $sql4 = "SELECT Fonction, COUNT(*) as nb FROM salaries WHERE Fonction IS NOT NULL AND Fonction != '' AND `EtatSal` = 'Actif' GROUP BY Fonction ORDER BY nb DESC LIMIT 10";
    $stmt4 = $pdo->query($sql4);
    $rows4 = $stmt4->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "\n💼 **Top Fonctions:**\n";
    foreach ($rows4 as $r) {
        $text .= "• {$r['Fonction']}: **{$r['nb']}**\n";
    }
    
    // Par statut
    $sql5 = "SELECT `EtatSal`, COUNT(*) as nb FROM salaries WHERE `EtatSal` IS NOT NULL GROUP BY `EtatSal`";
    $stmt5 = $pdo->query($sql5);
    $rows5 = $stmt5->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "\n📊 **Par Statut:**\n";
    foreach ($rows5 as $r) {
        $text .= "• {$r['EtatSal']}: **{$r['nb']}**\n";
    }
    
    return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
}

function handleRetards($dates, $q) {
    $pdo = getDB('ggr');
    $text = "⏰ **RETARDS**\n";
    $text .= "Période: 7 derniers jours\n\n";
    
    // Chercher dans les departure times
    $sql = "SELECT d.DepartID, d.HeureDepartPrevue, d.HeureDepartReelle, d.Ligne, d.BusID,
            TIMESTAMPDIFF(MINUTE, d.HeureDepartPrevue, d.HeureDepartReelle) as minutes_retard
            FROM depart d
            WHERE d.DateDepart BETWEEN ? AND ? 
            AND d.HeureDepartReelle IS NOT NULL
            AND d.HeureDepartPrevue IS NOT NULL
            AND d.HeureDepartReelle > d.HeureDepartPrevue
            ORDER BY minutes_retard DESC
            LIMIT 10";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$dates['start'], $dates['end']]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (count($rows) > 0) {
        $text .= "⚠️ **Retards constatés:**\n";
        $totalRetard = 0;
        foreach ($rows as $r) {
            $min = $r['minutes_retard'] ?: 0;
            $totalRetard += $min;
            $text .= "• {$r['Ligne']}: +{$min} min\n";
        }
        $text .= "\n📊 Retard moyen: " . round($totalRetard / count($rows)) . " minutes\n";
    } else {
        $text .= "✅ Aucun retard enregistré sur cette période\n";
    }
    
    return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
}

function handleInterventions($dates, $q) {
    $pdo = getDB('parc');
    $text = "🔧 **INTERVENTIONS**\n";
    $text .= "Période: 7 derniers jours\n\n";
    
    $sql = "SELECT Type_Maintenance, COUNT(*) as nb
            FROM Interventions
            WHERE Date_Entree BETWEEN ? AND ?
            GROUP BY Type_Maintenance";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$dates['start'], $dates['end']]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (count($rows) > 0) {
        $text .= "📊 **Par Type:**\n";
        foreach ($rows as $r) {
            $text .= "• {$r['Type_Maintenance']}: {$r['nb']} interventions\n";
        }
    } else {
        $text .= "Aucune intervention enregistrée\n";
    }
    
    // Bus avec le plus d'interventions
    $sql2 = "SELECT b.Matricule, COUNT(*) as nb
            FROM Interventions i
            JOIN Bus b ON i.Num_Parc = b.Num_Parc
            WHERE i.Date_Entree BETWEEN ? AND ?
            GROUP BY b.Matricule
            ORDER BY nb DESC
            LIMIT 5";
    
    $stmt2 = $pdo->prepare($sql2);
    $stmt2->execute([$dates['start'], $dates['end']]);
    $rows2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
    
    if (count($rows2) > 0) {
        $text .= "\n🚌 **Bus avec interventions:**\n";
        foreach ($rows2 as $r) {
            $text .= "• {$r['Matricule']}: {$r['nb']} interventions\n";
        }
    }
    
    return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
}

function handleDeparts($dates, $gare, $site) {
    $pdo = getDB('ggr');
    $text = "🚌 **DÉPARTS**\n";
    $text .= "Période: 7 derniers jours\n\n";
    
    // Using Departs table
    $sql = "SELECT COUNT(*) as nb FROM Departs 
            WHERE DateDepart BETWEEN ? AND ?";
    $params = [$dates['start'], $dates['end']];
    
    if ($site) {
        $sql .= " AND Site = ?";
        $params[] = $site;
    }
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    $text .= "• Total départs: **{$row['nb']}** voyages\n";
    
    // Par ligne
    $sql2 = "SELECT LigneID, COUNT(*) as nb FROM Departs 
            WHERE DateDepart BETWEEN ? AND ?
            GROUP BY LigneID ORDER BY nb DESC LIMIT 5";
    $stmt2 = $pdo->prepare($sql2);
    $stmt2->execute([$dates['start'], $dates['end']]);
    $rows2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
    
    $text .= "\n📍 **Top Lignes:**\n";
    foreach ($rows2 as $r) {
        if ($r['LigneID']) {
            $text .= "• " . substr($r['LigneID'], 0, 40) . ": {$r['nb']} départs\n";
        }
    }
    
    return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
}

function handleSites() {
    $pdo = getDB('ggr');
    $text = "🏢 **SITES & GARES**\n\n";
    
    $sql = "SELECT `SiteID`, `Nom_Site`, `Localisation_Site` FROM Sites LIMIT 20";
    $stmt = $pdo->query($sql);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($rows as $r) {
        $text .= "• **{$r['SiteID']}** - {$r['Nom_Site']} ({$r['Localisation_Site']})\n";
    }
    
    return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
}

function handleGeneral($question) {
    $text = "📋 Question: \"$question\"\n\n";
    $text .= "Je peux répondre aux questions sur:\n";
    $text .= "• 💰 Recettes aujourd'hui\n";
    $text .= "• 🚶 Passagers / Trafic\n";
    $text .= "• ⏰ Retards\n";
    $text .= "• 🚌 Flotte (bus disponibles, maintenance)\n";
    $text .= "• 👥 Personnel (effectif, par service)\n";
    $text .= "• 🔧 Interventions\n";
    $text .= "• 🚌 Départs par ligne\n";
    $text .= "• 🏢 Sites et gares\n\n";
    $text .= "Essayez par exemple:\n";
    $text .= "\"Recettes aujourd'hui\"\n";
    $text .= "\"État de la flotte\"\n";
    $text .= "\"Personnel\"\n";
    $text .= "\"Nombre de bus\"\n";
    
    return ['success' => true, 'text' => $text, 'data' => '[]', 'periode' => 'today'];
}

// Routing
$method = $_SERVER['REQUEST_METHOD'];
$uri = isset($_SERVER['REQUEST_URI']) ? $_SERVER['REQUEST_URI'] : '/';
$path = parse_url($uri, PHP_URL_PATH);
$path = preg_replace('#^/apidecisionnelAssist#', '', $path);
$path = preg_replace('#^/api_php#', '', $path);
if (empty($path)) $path = '/';

// Health check
if ($path === '' || $path === '/' || $path === '/health') {
    echo json_encode(['status' => 'OK', 'version' => '2.0.1', 'agent' => 'Transub AI - MODIFIED']);
    exit;
}

// Debug endpoint - test database connection
if ($path === '/debug') {
    // Just echo and exit without auth
    echo json_encode(['status' => 'debug']);
    exit;
}
if ($path === '/test') {
    // Test endpoint with hardcoded response to verify routing works
    $input = json_decode(file_get_contents('php://input'), true);
    $question = $input['question'] ?? $_GET['question'] ?? '';
    $token = $input['token'] ?? $_GET['token'] ?? '';
    error_log("TEST ENDPOINT - Question: $question, Token: $token");
    echo json_encode(['success' => true, 'test' => 'ok', 'question' => $question, 'token' => $token]);
    exit;
}

// Login
if ($path === '/auth/login' && $method === 'POST') {
    $data = json_decode(file_get_contents('php://input'), true);
    $email = $data['email'] ?? '';
    $pass = $data['password'] ?? '';
    
    if ($email === $admin_user['email'] && $pass === $admin_user['password']) {
        echo json_encode(['success' => true, 'data' => ['token' => 'PDG2024', 'user' => $email]]);
    } else {
        http_response_code(401);
        echo json_encode(['success' => false, 'message' => 'Identifiants invalides']);
    }
    exit;
}

// Query IA avec agent local
if ($path === '/ia/query' && $method === 'POST') {
    if (!checkAuth()) {
        http_response_code(401);
        echo json_encode(['success' => false, 'message' => 'Non autorisé']);
        exit;
    }
    
    $data = json_decode(file_get_contents('php://input'), true);
    $question = $data['question'] ?? '';
    
    if (empty($question)) {
        echo json_encode(['success' => false, 'message' => 'Question vide']);
        exit;
    }
    
    // Detect what's being asked
    $qLower = strtolower($question);
    
    // Clean the question - remove accents, handle typos
    $qClean = strtolower($question);
    $qClean = str_replace(['é', 'è', 'ê', 'ë'], 'e', $qClean);
    $qClean = str_replace(['à', 'â', 'ä'], 'a', $qClean);
    $qClean = str_replace(['î', 'ï'], 'i', $qClean);
    $qClean = str_replace(['ô', 'ö'], 'o', $qClean);
    $qClean = str_replace(['ù', 'û', 'ü'], 'u', $qClean);
    $qClean = str_replace(['ç'], 'c', $qClean);
    
    // Check for personnel-related keywords (with typo tolerance)
    $isPersonnel = false;
    $personnelKeywords = ['personnel', 'employe', 'salarie', 'effectif', 'effect', 'efect', 'efec', 'ef', 'eff', 'presence', 'agent', 'employee', 'employe'];
    $foundKeyword = '';
    foreach ($personnelKeywords as $kw) {
        if (stripos($qClean, $kw) !== false || stripos($question, $kw) !== false) {
            $isPersonnel = true;
            $foundKeyword = $kw;
            error_log("Personnel keyword found: " . $kw . " in question: " . $question);
            break;
        }
    }
    
    // Also check if question mentions breakdown fields directly (Direction, Affectation, Fonction, etc.)
    if (!$isPersonnel && (stripos($qClean, 'direction') !== false || stripos($qClean, 'affectation') !== false)) {
        $isPersonnel = true;
        error_log("Personnel detected via breakdown keyword for question: " . $question);
    }
    
    if (!$isPersonnel) {
        error_log("NO personnel keyword found for question: " . $question);
    }
    
    // Handle similar but not exact matches - ask for confirmation
    if (!$isPersonnel && strlen($qClean) >= 3 && (stripos($qClean, 'effect') !== false || stripos($qClean, 'efect') !== false || stripos($qClean, 'efec') !== false)) {
        // Check if it's not "effectif" but something else like "effectuer"
        if (stripos($qClean, 'effectuer') === false && stripos($qClean, 'effectue') === false && stripos($qClean, 'efficace') === false) {
            $text = "🤔 Voulez-vous dire \"effectif\" ?\n\n";
            $text .= "Tapez \"oui\" pour voir l'effectif du personnel.\n";
            $text .= "Ou reformulez votre question.";
            echo json_encode(['success' => true, 'data' => ['text' => $text, 'confirm' => 'effectif']]);
            exit;
        }
    }
    
    // Handle personnel queries
    if ($isPersonnel) {
        // TEMPORARY DEBUG - Remove after testing
        error_log("=== ENTERED PERSONNEL HANDLER ===");
        error_log("Question: " . $question);
        error_log("isPersonnel: " . ($isPersonnel ? "true" : "false"));
        
        try {
            $pdo = getDB('grh');
            
            // Detect which field to group by - check for each keyword
            $field = null;
            $fieldToShow = strtolower($question);
            
            // Log for debugging
            error_log("Processing question: " . $question);
            
            // Check for specific keywords in order
            // Remove "par" and "par " from the string to make matching easier
            $searchStr = strtolower($question);
            $searchStr = str_replace('par ', '', $searchStr);
            $searchStr = str_replace('par', '', $searchStr);
            
            // Also try without any cleanup for backup detection
            $searchStrRaw = strtolower($question);
            
            // Debug: log what's happening
            error_log("=== FIELD DETECTION ===");
            error_log("Question: [" . $question . "]");
            error_log("searchStr: [" . $searchStr . "]");
            error_log("searchStrRaw: [" . $searchStrRaw . "]");
            
            // Check each keyword individually
            $hasAffectation = (strpos($searchStr, 'affectation') !== false);
            $hasDirection = (strpos($searchStr, 'direction') !== false);
            $hasFonction = (strpos($searchStr, 'fonction') !== false);
            
            error_log("hasAffectation: " . ($hasAffectation ? "true" : "false"));
            error_log("hasDirection: " . ($hasDirection ? "true" : "false"));
            error_log("hasFonction: " . ($hasFonction ? "true" : "false"));
            
            // Force affectation if detected
            if ($hasAffectation) {
                $field = 'affectation';
                error_log("Setting field to affectation");
            } elseif ($hasDirection) {
                $field = 'direction';
                error_log("Setting field to direction");
            } elseif (strpos($searchStr, 'fonction') !== false || strpos($searchStr, 'poste') !== false) {
                $field = 'fonction';
            } elseif (strpos($searchStr, 'contrat') !== false || strpos($searchStr, 'cdi') !== false) {
                $field = 'contrat';
            } elseif (strpos($searchStr, 'categorie') !== false || strpos($searchStr, 'categor') !== false) {
                $field = 'categorie';
            } elseif (strpos($searchStr, 'statut') !== false) {
                $field = 'statut';
            }
            
            error_log("Detected field: " . ($field ?: "none"));
            
            // Force output something for debugging
            error_log("=== END FIELD DETECTION ===");
            
            // If no field detected, return error message
            if ($field === null) {
                $text = "🤔 Je n'ai pas compris le type de répartition.\n\n";
                $text .= "Essayez:\n";
                $text .= "• \"effectif direction\"\n";
                $text .= "• \"effectif affectation\"\n";
                $text .= "• \"effectif fonction\"\n";
                $text .= "• \"effectif contrat\"\n";
                $text .= "• \"effectif categorie\"\n";
                echo json_encode(['success' => true, 'data' => ['text' => $text]]);
                exit;
            }
            
            // If field detected, show breakdown
            if ($field !== null) {
                // Get total
                $totalStmt = $pdo->query("SELECT COUNT(*) as cnt FROM salaries WHERE `EtatSal` = 'Actif'");
                $totalRow = $totalStmt->fetch(PDO::FETCH_ASSOC);
                
                // Get breakdown
                $fieldColumns = [
                    'direction' => 'Direction',
                    'affectation' => 'Affectation',
                    'fonction' => 'Fonction',
                    'contrat' => 'Type_Contrat',
                    'categorie' => 'Categorie',
                    'statut' => 'EtatSal'
                ];
                
                $col = $fieldColumns[$field];
                $sql = "SELECT `$col`, COUNT(*) as nb FROM salaries WHERE `EtatSal` = 'Actif' AND `$col` IS NOT NULL AND `$col` != '' GROUP BY `$col` ORDER BY nb DESC";
                $stmt = $pdo->query($sql);
                $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
                
                $text = "👥 EFFECTIF - PAR " . strtoupper($field) . "\n\n";
                $text .= "Total: " . $totalRow['cnt'] . " employés\n\n";
                $text .= "Répartition:\n";
                
                foreach ($rows as $r) {
                    $text .= "• " . ($r[$col] ?: 'Non défini') . ": " . $r['nb'] . "\n";
                }
                
                echo json_encode(['success' => true, 'data' => ['text' => $text]]);
                exit;
            }
            
            // No specific field - just show total
            $totalStmt = $pdo->query("SELECT COUNT(*) as cnt FROM salaries WHERE `EtatSal` = 'Actif'");
            $totalRow = $totalStmt->fetch(PDO::FETCH_ASSOC);
            
            $text = "👥 EFFECTIF PERSONNEL\n\n";
            $text .= "Total employés actifs: " . $totalRow['cnt'] . "\n\n";
            $text .= "Pour la répartition:\n";
            $text .= "• \"effectif direction\"\n";
            $text .= "• \"effectif affectation\"\n";
            $text .= "• \"effectif fonction\"\n";
            $text .= "• \"effectif contrat\"\n";
            $text .= "• \"effectif categorie\"\n";
            
            echo json_encode(['success' => true, 'data' => ['text' => $text]]);
            exit;
            
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'error' => $e->getMessage()]);
            exit;
        }
    }
    
    // Debug: log isPersonnel detection
    error_log("isPersonnel detected: " . ($isPersonnel ? "true" : "false") . " for question: " . $question);
    
    // Handle other queries via parseAndExecute but remove fallback text
    $result = parseAndExecute($question);
    
    // If result is empty or fallback, return a smarter message
    if (empty($result['text']) || stripos($result['text'], 'Je peux répondre') !== false) {
        // Just ask what they want - don't suggest specific topics
        $text = "🤔 Je n'ai pas compris. Pouvez-vous reformuler ?\n";
        
        echo json_encode(['success' => true, 'data' => ['text' => $text]]);
        exit;
    }
    
    echo json_encode([
        'success' => $result['success'],
        'data' => [
            'text' => $result['text'],
            'metadata' => ['periode' => $result['periode']]
        ]
    ]);
    exit;
}

// Suggestions
if ($path === '/ia/suggestions') {
    echo json_encode([
        'success' => true,
        'suggestions' => [
            ['category' => 'Recettes', 'questions' => [
                'Combien de recettes cette semaine ?',
                'Recettes du mois dernier',
                'Recettes site GR05 ce mois'
            ]],
            ['category' => 'Passagers', 'questions' => [
                'Combien de voyageurs cette semaine ?',
                'Trafic aujourd\'hui'
            ]],
            ['category' => 'Flotte', 'questions' => [
                'État de la flotte',
                'Bus en maintenance',
                'Nombre de bus disponibles'
            ]],
            ['category' => 'Personnel', 'questions' => [
                'Effectif total',
                'Présence aujourd\'hui',
                'Personnel par service'
            ]],
            ['category' => 'Retards', 'questions' => [
                'Les retards de cette semaine',
                'Retards hier'
            ]],
            ['category' => 'Interventions', 'questions' => [
                'Interventions ce mois',
                'Bus avec le plus d\'interventions'
            ]]
        ]
    ]);
    exit;
}

// Default
echo json_encode(['success' => true, 'message' => 'API Transub - DecisionnelAssist v2.0', 'agent' => 'ok']);