<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=bd_insuite_backbones', 'root', '');

echo "=== Diagnostic Cartographie ===\n\n";

// 1. Compter les incidents
$incidents = $pdo->query("SELECT COUNT(*) FROM incidents")->fetchColumn();
echo "Total incidents: $incidents\n";

// 2. Compter les incidents avec GPS dans reports
$withGPS = $pdo->query("SELECT COUNT(DISTINCT incident_id) FROM incident_reports WHERE gps_lat IS NOT NULL AND gps_lng IS NOT NULL")->fetchColumn();
echo "Incidents avec GPS (reports): $withGPS\n";

// 3. Vérifier les locations
$hasLocations = $pdo->query("SHOW TABLES LIKE 'locations'")->fetchColumn();
echo "Table locations existe: " . ($hasLocations ? "OUI" : "NON") . "\n";

if ($hasLocations) {
    $locCount = $pdo->query("SELECT COUNT(*) FROM locations WHERE latitude IS NOT NULL AND longitude IS NOT NULL")->fetchColumn();
    echo "Locations avec GPS: $locCount\n";
    
    $incidentsWithLoc = $pdo->query("SELECT COUNT(*) FROM incidents WHERE location_id IS NOT NULL")->fetchColumn();
    echo "Incidents avec location_id: $incidentsWithLoc\n";
}

// 4. Exemples d'incidents avec GPS
echo "\n--- Exemples incidents avec GPS ---\n";
$examples = $pdo->query("
    SELECT i.id, i.ticket_id, i.title, ir.gps_lat, ir.gps_lng
    FROM incidents i
    JOIN incident_reports ir ON ir.incident_id = i.id
    WHERE ir.gps_lat IS NOT NULL AND ir.gps_lng IS NOT NULL
    LIMIT 3
")->fetchAll(PDO::FETCH_ASSOC);

foreach ($examples as $ex) {
    echo "- ID {$ex['id']}, Ticket: {$ex['ticket_id']}, GPS: {$ex['gps_lat']}, {$ex['gps_lng']}\n";
}

if (empty($examples)) {
    echo "Aucun incident avec GPS trouvé.\n";
}

// 5. Vérifier status_id
echo "\n--- Vérification statuts ---\n";
$withStatus = $pdo->query("SELECT COUNT(*) FROM incidents WHERE status_id IS NOT NULL")->fetchColumn();
echo "Incidents avec status_id: $withStatus\n";

$statuses = $pdo->query("SELECT id, label, color FROM incident_statuses LIMIT 5")->fetchAll(PDO::FETCH_ASSOC);
echo "Exemples de statuts:\n";
foreach ($statuses as $s) {
    echo "  - ID {$s['id']}: {$s['label']} ({$s['color']})\n";
}