<?php
namespace App\Controllers;

use App\Core\Auth;
use App\Core\Controller;
use App\Core\Database;
use PDO;

class CartographyController extends Controller
{
    private \PDO $db;
    private const ACTIVE_TECHNICIAN_WINDOW_SECONDS = 600;

    private function normalizeAssignedCity($value): ?string
    {
        return normalize_assigned_city_value($value);
    }

    private function currentUserAssignedCity(?array $user = null): ?string
    {
        return $this->normalizeAssignedCity(($user ?? Auth::user())['assigned_city'] ?? null);
    }

    private function userAssignedCityCondition(string $alias = 'u'): string
    {
        $prefix = trim($alias);
        if ($prefix !== '' && !str_ends_with($prefix, '.')) {
            $prefix .= '.';
        }

        return "LOWER(TRIM(COALESCE({$prefix}assigned_city, ''))) = ?";
    }

    private function tableHasColumn(string $table, string $column): bool
    {
        try {
            $st = $this->db->prepare(sprintf('SHOW COLUMNS FROM `%s` LIKE ?', str_replace('`', '``', $table)));
            $st->execute([$column]);
            return (bool)$st->fetch(PDO::FETCH_ASSOC);
        } catch (\Throwable $e) {
            return false;
        }
    }

    private function ensurePrivilegedAccess(): ?array
    {
        if (!Auth::check()) {
            return null;
        }

        $user = Auth::user();
        $role = strtolower((string)($user['role_key'] ?? ''));
        if (!in_array($role, $this->getPrivilegedRoles(), true)) {
            http_response_code(403);
            return null;
        }

        return $user;
    }

    private function getTrackableRoles(): array
    {
        return ['technicien', 'technician', 'tech', 'agent'];
    }

    private function getPrivilegedRoles(): array
    {
        return ['admin', 'superadmin', 'manager', 'superviseur', 'supervisor', 'dispatch', 'dispatcher'];
    }

    private function getBearerToken(): ?string
    {
        $hdr = $_SERVER['HTTP_AUTHORIZATION'] ?? $_SERVER['REDIRECT_HTTP_AUTHORIZATION'] ?? '';
        if (!$hdr && function_exists('apache_request_headers')) {
            $apacheHeaders = apache_request_headers();
            $hdr = $apacheHeaders['Authorization'] ?? $apacheHeaders['authorization'] ?? '';
        }
        if (!$hdr) {
            return null;
        }
        if (preg_match('/^Bearer\s+(.+)$/i', trim($hdr), $m)) {
            return trim($m[1]);
        }
        return null;
    }

    private function ensureApiTokensTable(): void
    {
        $this->db->exec("CREATE TABLE IF NOT EXISTS api_tokens (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            token_hash CHAR(64) NOT NULL,
            name VARCHAR(60) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_used_at DATETIME NULL,
            expires_at DATETIME NULL,
            revoked_at DATETIME NULL,
            UNIQUE KEY uniq_token_hash (token_hash),
            INDEX(user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    private function resolveAuthenticatedUser(bool $allowBearerToken = false): ?array
    {
        if (Auth::check()) {
            return Auth::user();
        }

        if (!$allowBearerToken) {
            return null;
        }

        $token = $this->getBearerToken();
        if (!$token) {
            return null;
        }

        try {
            $this->ensureApiTokensTable();
            $hash = hash('sha256', $token);
            $st = $this->db->prepare('SELECT user_id FROM api_tokens WHERE token_hash = ? AND revoked_at IS NULL AND (expires_at IS NULL OR expires_at > NOW()) LIMIT 1');
            $st->execute([$hash]);
            $userId = (int)($st->fetchColumn() ?: 0);
            if ($userId <= 0) {
                return null;
            }

            $this->db->prepare('UPDATE api_tokens SET last_used_at = NOW() WHERE token_hash = ?')->execute([$hash]);

            $user = $this->db->prepare('SELECT id, name, email, role_key, active, assigned_city FROM users WHERE id = ? AND active = 1 LIMIT 1');
            $user->execute([$userId]);
            $row = $user->fetch(PDO::FETCH_ASSOC);
            return $row ?: null;
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function getTrackingAssignmentStats(int $userId): array
    {
        $stats = [
            'incident_assignments' => 0,
            'ftth_assignments' => 0,
        ];

        try {
            $hasIncidentAssignments = $this->db->query("SHOW TABLES LIKE 'incident_assignments'")->fetchColumn();
            if ($hasIncidentAssignments) {
                $st = $this->db->prepare('SELECT COUNT(DISTINCT incident_id) FROM incident_assignments WHERE user_id = ? AND (active IS NULL OR active = 1)');
                $st->execute([$userId]);
                $stats['incident_assignments'] = (int)($st->fetchColumn() ?: 0);
            }
        } catch (\Throwable $e) {
            $stats['incident_assignments'] = 0;
        }

        try {
            $hasFtthAssignments = $this->db->query("SHOW TABLES LIKE 'maintenance_ftth_assignments'")->fetchColumn();
            $hasFtthTickets = $this->db->query("SHOW TABLES LIKE 'maintenance_ftth_tickets'")->fetchColumn();
            if ($hasFtthAssignments && $hasFtthTickets) {
                $assignmentUserColumn = $this->tableHasColumn('maintenance_ftth_assignments', 'technician_id')
                    ? 'technician_id'
                    : ($this->tableHasColumn('maintenance_ftth_assignments', 'user_id') ? 'user_id' : null);

                if ($assignmentUserColumn !== null) {
                    $hasActiveColumn = $this->tableHasColumn('maintenance_ftth_assignments', 'active');
                    $activeSql = $hasActiveColumn ? 'AND (a.active IS NULL OR a.active = 1)' : '';

                    $st = $this->db->prepare("SELECT COUNT(DISTINCT a.ticket_id)
                    FROM maintenance_ftth_assignments a
                    JOIN maintenance_ftth_tickets t ON t.id = a.ticket_id
                    WHERE a.{$assignmentUserColumn} = ?
                      {$activeSql}
                      AND LOWER(COALESCE(t.status, '')) NOT LIKE '%valid%'
                      AND LOWER(COALESCE(t.status, '')) NOT LIKE '%clôt%'
                      AND LOWER(COALESCE(t.status, '')) NOT LIKE '%clot%'
                      AND LOWER(COALESCE(t.status, '')) NOT LIKE '%clos%'
                      AND LOWER(COALESCE(t.status, '')) NOT LIKE '%resolu%'
                      AND LOWER(COALESCE(t.status, '')) NOT LIKE '%résolu%'");
                    $st->execute([$userId]);
                    $stats['ftth_assignments'] = (int)($st->fetchColumn() ?: 0);
                }
            }
        } catch (\Throwable $e) {
            $stats['ftth_assignments'] = 0;
        }

        return $stats;
    }

    private function buildTrackingContext(array $user): array
    {
        $role = strtolower((string)($user['role_key'] ?? ''));
        $canTrack = in_array($role, $this->getTrackableRoles(), true);
        $stats = $this->getTrackingAssignmentStats((int)($user['id'] ?? 0));
        $hasAssignments = ($stats['incident_assignments'] + $stats['ftth_assignments']) > 0;

        return [
            'ok' => true,
            'user_id' => (int)($user['id'] ?? 0),
            'role' => $role,
            'can_track' => $canTrack,
            'should_track' => $canTrack && $hasAssignments,
            'has_assignments' => $hasAssignments,
            'incident_assignments' => (int)$stats['incident_assignments'],
            'ftth_assignments' => (int)$stats['ftth_assignments'],
            'tracking_visibility_window_minutes' => 1440,
            'tracking_refresh_seconds' => 120,
        ];
    }

    private function normalizeName(string $name): string
    {
        $n = mb_strtoupper(trim($name));
        $n = str_replace(["\xC2\xA0"], ' ', $n);
        $n = preg_replace('/[\s\t\r\n]+/u', ' ', $n) ?? $n;
        $n = str_replace(['_', '-'], ' ', $n);
        $n = preg_replace('/\s+/u', ' ', $n) ?? $n;
        $n = preg_replace('/[\.,;:!?()\[\]{}"\'`]/u', '', $n) ?? $n;
        return trim($n);
    }

    public function __construct()
    {
    $this->db = Database::pdo();
    }

    public function index(): void
    {
        $currentUser = $this->ensurePrivilegedAccess();
        if (!$currentUser) {
            if (!Auth::check()) { $this->redirect('/login'); }
            $this->redirect('/dashboard');
            return;
        }
        $this->view('cartography.index', [
            'title' => 'Cartographie',
            'currentUser' => $currentUser,
            'assignedCity' => $currentUser['assigned_city'] ?? null,
        ]);
    }

    public function debug(): void
    {
        $currentUser = $this->ensurePrivilegedAccess();
        if (!$currentUser) {
            if (!Auth::check()) { $this->redirect('/login'); }
            $this->redirect('/dashboard');
            return;
        }
        $this->view('cartography.debug', [
            'title' => 'Cartographie Debug',
            'currentUser' => $currentUser,
            'assignedCity' => $currentUser['assigned_city'] ?? null,
        ]);
    }

    public function supervision(): void
    {
        $currentUser = $this->ensurePrivilegedAccess();
        if (!$currentUser) {
            if (!Auth::check()) { $this->redirect('/login'); }
            $this->redirect('/dashboard');
            return;
        }
        $this->view('cartography.supervision', [
            'title' => 'Supervision Live',
            '_layout' => 'none',
            'currentUser' => $currentUser,
            'assignedCity' => $currentUser['assigned_city'] ?? null,
        ]);
    }

    public function data(): void
    {
        $currentUser = $this->ensurePrivilegedAccess();
        if (!$currentUser) { http_response_code(Auth::check() ? 403 : 401); echo json_encode(['error'=>'unauthorized']); return; }
        header('Content-Type: application/json; charset=utf-8');
        $assignedCity = $this->currentUserAssignedCity($currentUser);

        // Mode debug via paramètre GET
        $debug = isset($_GET['debug']);

        try {
            if ($debug) {
                $debugInfo = ['debug' => true, 'steps' => []];
            }
            
            // Vérifier la présence des tables de base
            $hasIncidents = $this->db->query("SHOW TABLES LIKE 'incidents'")->fetchColumn();
            if (!$hasIncidents) { 
                if ($debug) { echo json_encode(['error' => 'Table incidents not found']); return; }
                echo json_encode([]); 
                return; 
            }

            $hasStatuses  = $this->db->query("SHOW TABLES LIKE 'incident_statuses'")->fetchColumn();
            $hasLocations = $this->db->query("SHOW TABLES LIKE 'locations'")->fetchColumn();
            $hasReports   = $this->db->query("SHOW TABLES LIKE 'incident_reports'")->fetchColumn();
            $hasUsers     = $this->db->query("SHOW TABLES LIKE 'users'")->fetchColumn();

            if ($debug) {
                $debugInfo['steps'][] = 'Tables check completed';
                $debugInfo['tables'] = [
                    'incidents' => (bool)$hasIncidents,
                    'statuses' => (bool)$hasStatuses,
                    'locations' => (bool)$hasLocations,
                    'reports' => (bool)$hasReports,
                    'users' => (bool)$hasUsers
                ];
            }

            if (!$hasLocations && !$hasReports) { 
                if ($debug) { echo json_encode(['error' => 'No location or report tables']); return; }
                echo json_encode([]); 
                return; 
            }

            // Vérifier si la colonne ticket_id ou reference existe
            $hasTicketId = false;
            $hasReference = false;
            try {
                $cols = $this->db->query("SHOW COLUMNS FROM incidents")->fetchAll(PDO::FETCH_ASSOC);
                foreach ($cols as $col) {
                    if ($col['Field'] === 'ticket_id') $hasTicketId = true;
                    if ($col['Field'] === 'reference') $hasReference = true;
                }
            } catch (\Throwable $e) {
                // Ignore error
            }
            
            if ($debug) {
                $debugInfo['steps'][] = 'Checked ticket columns';
                $debugInfo['has_ticket_id'] = $hasTicketId;
                $debugInfo['has_reference'] = $hasReference;
            }

            // Utiliser ticket_id, reference ou générer depuis l'id
            $ticketField = $hasTicketId ? "i.ticket_id" : ($hasReference ? "i.reference" : "CONCAT('INC-', i.id)");

            // Vérifier si assigned_to existe
            $hasAssignedTo = false;
            try {
                $cols = $this->db->query("SHOW COLUMNS FROM incidents LIKE 'assigned_to'")->fetchAll();
                $hasAssignedTo = !empty($cols);
            } catch (\Throwable $e) {
                // Ignore
            }
            
            if ($debug) {
                $debugInfo['has_assigned_to'] = $hasAssignedTo;
            }

            $parts = [];

            // 1) Incidents avec coordonnées issues des rapports (dernier GPS connu)
            if ($hasReports) {
                $userJoin = "";
                if ($hasUsers) {
                    if ($hasAssignedTo) {
                        $userJoin = "LEFT JOIN users u ON u.id = i.assigned_to";
                    } else {
                        $userJoin = "LEFT JOIN incident_assignments ia ON ia.incident_id = i.id AND ia.active = 1
                            LEFT JOIN users u ON u.id = ia.user_id";
                    }
                }
                
                $parts[] = "SELECT i.id, $ticketField AS ticket_id, i.title, i.priority,
                                   i.created_at, i.resolved_at,
                                   " . ($hasStatuses ? "s.label" : "NULL") . " AS status_label,
                                   " . ($hasStatuses ? "s.color" : "'#0d6efd'") . " AS status_color,
                                   " . ($hasLocations ? "l.name" : "''") . " AS location_name,
                                   " . ($hasUsers ? "COALESCE(u.name, u.email)" : "NULL") . " AS technician_name,
                                   ir2.gps_lat AS latitude,
                                   ir2.gps_lng AS longitude
                            FROM incidents i
                            JOIN (
                                SELECT incident_id, MAX(id) AS max_id
                                FROM incident_reports
                                WHERE gps_lat IS NOT NULL AND gps_lng IS NOT NULL
                                GROUP BY incident_id
                            ) last ON last.incident_id = i.id
                            JOIN incident_reports ir2 ON ir2.id = last.max_id
                            " . ($hasStatuses ? "LEFT JOIN incident_statuses s ON s.id = i.status_id" : "") . "
                            " . ($hasLocations ? "LEFT JOIN locations l ON l.id = i.location_id" : "") . "
                            " . $userJoin
                            . ($assignedCity !== null && $hasUsers ? " WHERE (u.id IS NULL OR " . $this->userAssignedCityCondition('u') . ")" : "");
            }

            // 2) Incidents sans rapport GPS mais avec coordonnées de site
            if ($hasLocations) {
                $userJoin = "";
                if ($hasUsers) {
                    if ($hasAssignedTo) {
                        $userJoin = "LEFT JOIN users u ON u.id = i.assigned_to";
                    } else {
                        $userJoin = "LEFT JOIN incident_assignments ia ON ia.incident_id = i.id AND ia.active = 1
                            LEFT JOIN users u ON u.id = ia.user_id";
                    }
                }
                
                $parts[] = "SELECT i.id, $ticketField AS ticket_id, i.title, i.priority,
                                       i.created_at, i.resolved_at,
                                       " . ($hasStatuses ? "s.label" : "NULL") . " AS status_label,
                                       " . ($hasStatuses ? "s.color" : "'#0d6efd'") . " AS status_color,
                                       l.name AS location_name,
                                       " . ($hasUsers ? "COALESCE(u.name, u.email)" : "NULL") . " AS technician_name,
                                       l.latitude AS latitude,
                                       l.longitude AS longitude
                                FROM incidents i
                                JOIN locations l ON l.id = i.location_id
                                " . ($hasStatuses ? "LEFT JOIN incident_statuses s ON s.id = i.status_id" : "") . "
                                " . $userJoin . "
                                WHERE l.latitude IS NOT NULL AND l.longitude IS NOT NULL
                                                                " . ($assignedCity !== null && $hasUsers ? "AND (u.id IS NULL OR " . $this->userAssignedCityCondition('u') . ")\n                                " : "")
                                                                . ($hasReports ? "AND NOT EXISTS (
                                        SELECT 1 FROM incident_reports r
                                        WHERE r.incident_id = i.id
                                          AND r.gps_lat IS NOT NULL AND r.gps_lng IS NOT NULL
                                      )" : "");
            }

            if (empty($parts)) { 
                if ($debug) { echo json_encode(['error' => 'No query parts generated', 'info' => $debugInfo]); return; }
                echo json_encode([]); 
                return; 
            }

            $sql = implode(" UNION ALL ", $parts);
            
            if ($debug) {
                $debugInfo['steps'][] = 'SQL query built';
                $debugInfo['sql'] = $sql;
            }

            $stmt = $this->db->prepare($sql);
            $queryParams = [];
            if ($assignedCity !== null && $hasUsers) {
                foreach ($parts as $_part) {
                    if (str_contains($_part, $this->userAssignedCityCondition('u'))) {
                        $queryParams[] = $assignedCity;
                    }
                }
            }
            $stmt->execute($queryParams);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            
            if ($debug) {
                $debugInfo['steps'][] = 'Query executed';
                $debugInfo['rows_count'] = count($rows);
                $debugInfo['raw_rows'] = $rows;
            }

            $markers = array_map(function($r){
                $titleTicket = isset($r['ticket_id']) ? ($r['ticket_id'] . ' - ') : '';
                $statusLabel = $r['status_label'] ?? '—';
                $color = !empty($r['status_color']) ? $r['status_color'] : '#0d6efd';
                return [
                    'id' => (int)($r['id'] ?? 0),
                    'lat' => isset($r['latitude']) ? (float)$r['latitude'] : 0.0,
                    'lng' => isset($r['longitude']) ? (float)$r['longitude'] : 0.0,
                    'title' => $titleTicket . ($r['title'] ?? 'Incident'),
                    'status' => $statusLabel,
                    'color' => $color,
                    'priority' => $r['priority'] ?? 'Moyenne',
                    'location' => $r['location_name'] ?? '',
                    'technician' => $r['technician_name'] ?? null,
                    'created_at' => $r['created_at'] ?? null,
                    'resolved_at' => $r['resolved_at'] ?? null
                ];
            }, $rows);
            
            // Enrichir avec les images et commentaires
            foreach ($markers as &$marker) {
                $marker['images'] = $this->getIncidentImages($marker['id']);
                $marker['comments'] = $this->getIncidentComments($marker['id']);
            }

            if ($debug) {
                $debugInfo['steps'][] = 'Markers formatted';
                $debugInfo['markers'] = $markers;
                echo json_encode($debugInfo, JSON_PRETTY_PRINT);
                return;
            }

            echo json_encode($markers);
        } catch (\Throwable $e) {
            // En cas d'erreur SQL ou autre
            if ($debug) {
                echo json_encode([
                    'error' => $e->getMessage(),
                    'file' => $e->getFile(),
                    'line' => $e->getLine(),
                    'trace' => $e->getTraceAsString()
                ], JSON_PRETTY_PRINT);
                return;
            }
            error_log('CartographyController::data error: ' . $e->getMessage());
            echo json_encode([]);
        }
    }

    public function trackingContext(): void
    {
        $user = $this->resolveAuthenticatedUser(true);
        if (!$user) {
            http_response_code(401);
            header('Content-Type: application/json; charset=utf-8');
            echo json_encode(['ok' => false, 'error' => 'unauthorized']);
            return;
        }

        header('Content-Type: application/json; charset=utf-8');
        echo json_encode($this->buildTrackingContext($user));
    }

    /**
     * Retourne la position actuelle des techniciens (si table disponible)
     */
    public function technicians(): void
    {
        $currentUser = $this->ensurePrivilegedAccess();
        if (!$currentUser) { http_response_code(Auth::check() ? 403 : 401); echo json_encode(['error'=>'unauthorized']); return; }
        header('Content-Type: application/json; charset=utf-8');
        $assignedCity = $this->currentUserAssignedCity($currentUser);

        try {
            // Vérifie si la table existe, sinon retourne un tableau vide pour éviter l'erreur SQL
            $hasTable = $this->db->query("SHOW TABLES LIKE 'technician_locations'")->fetchColumn();
            if (!$hasTable) { echo json_encode([]); return; }

            // Filtrage par fraicheur (minutes), défaut 24h
            $maxAge = isset($_GET['max_age']) ? max(1, (int)$_GET['max_age']) : 1440;
            $since = date('Y-m-d H:i:s', time() - $maxAge * 60);

                        $sql = "SELECT tl.user_id,
                                       u.name,
                                       u.email,
                                       u.role_key,
                                       u.assigned_city,
                                       tl.latitude,
                                       tl.longitude,
                                       tl.heading,
                                       tl.speed,
                                       tl.accuracy,
                                       tl.updated_at,
                                       GREATEST(0, TIMESTAMPDIFF(SECOND, tl.updated_at, NOW())) AS age_seconds
                                        FROM technician_locations tl
                                        JOIN users u ON u.id = tl.user_id
                                        WHERE tl.latitude IS NOT NULL AND tl.longitude IS NOT NULL
                                            AND tl.updated_at >= ?";

                        if ($assignedCity !== null) {
                            $sql .= " AND " . $this->userAssignedCityCondition('u');
                        }

                        $params = [$since];
                        if ($assignedCity !== null) {
                            $params[] = $assignedCity;
                        }

                        $sql .= " ORDER BY tl.updated_at DESC";
                        $stmt = $this->db->prepare($sql);
                        $stmt->execute($params);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            $items = array_map(function($r){
                $ageSec = isset($r['age_seconds']) ? (int)$r['age_seconds'] : null;
                $isActive = $ageSec !== null ? ($ageSec <= self::ACTIVE_TECHNICIAN_WINDOW_SECONDS) : false;
                return [
                    'id' => (int)$r['user_id'],
                    'name' => $r['name'] ?: ($r['email'] ?? 'Technicien'),
                    'role' => $r['role_key'] ?? 'tech',
                    'lat' => isset($r['latitude']) ? (float)$r['latitude'] : null,
                    'lng' => isset($r['longitude']) ? (float)$r['longitude'] : null,
                    'heading' => isset($r['heading']) ? (float)$r['heading'] : null,
                    'speed' => isset($r['speed']) ? (float)$r['speed'] : null,
                    'accuracy' => isset($r['accuracy']) ? (float)$r['accuracy'] : null,
                    'updated_at' => $r['updated_at'] ?? null,
                    'age_seconds' => $ageSec,
                    'is_active' => $isActive,
                    'active_window_seconds' => self::ACTIVE_TECHNICIAN_WINDOW_SECONDS,
                ];
            }, $rows);

            echo json_encode($items);
        } catch (\Throwable $e) {
            http_response_code(200);
            echo json_encode([]);
        }
    }

    /**
     * Mise à jour de la position du technicien connecté
     * Attendu: POST lat, lng, accuracy?, heading?, speed?
     */
    public function updateLocation(): void
    {
        $user = $this->resolveAuthenticatedUser(true);
        if (!$user) { http_response_code(401); echo json_encode(['error'=>'unauthorized']); return; }
        
        // Seuls les techniciens et agents peuvent partager leur position
        // Admin/manager peuvent voir mais ne partagent pas automatiquement
        $allowedRoles = $this->getTrackableRoles();
        $userRole = strtolower($user['role_key'] ?? '');
        if (!in_array($userRole, $allowedRoles)) {
            // Permettre quand même l'envoi manuel (bouton "Me localiser")
            // mais ne pas forcer pour admin/manager
        }

        header('Content-Type: application/json; charset=utf-8');

        $lat = isset($_POST['lat']) ? (float)$_POST['lat'] : null;
        $lng = isset($_POST['lng']) ? (float)$_POST['lng'] : null;
        $accuracy = isset($_POST['accuracy']) ? (float)$_POST['accuracy'] : null;
        $heading = isset($_POST['heading']) ? (float)$_POST['heading'] : null;
        $speed = isset($_POST['speed']) ? (float)$_POST['speed'] : null;

        if ($lat === null || $lng === null) { http_response_code(400); echo json_encode(['error'=>'invalid']); return; }

        try {
            // Version ultra-simple sans création de table automatique
            // (la table sera créée manuellement si nécessaire)
            
            // Test de connexion BDD d'abord
            $this->db->query("SELECT 1")->fetchColumn();
            
            // Créer la table seulement si elle n'existe pas
            $tableExists = $this->db->query("SHOW TABLES LIKE 'technician_locations'")->fetchColumn();
            if (!$tableExists) {
                $this->db->exec("CREATE TABLE technician_locations (
                    user_id INT NOT NULL,
                    latitude DECIMAL(10,8) NOT NULL,
                    longitude DECIMAL(11,8) NOT NULL,
                    accuracy FLOAT NULL,
                    heading FLOAT NULL,
                    speed FLOAT NULL,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (user_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            }
            
            // Créer la table d'historique pour tracer les déplacements
            $historyExists = $this->db->query("SHOW TABLES LIKE 'technician_location_history'")->fetchColumn();
            if (!$historyExists) {
                $this->db->exec("CREATE TABLE technician_location_history (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    latitude DECIMAL(10,8) NOT NULL,
                    longitude DECIMAL(11,8) NOT NULL,
                    accuracy FLOAT NULL,
                    heading FLOAT NULL,
                    speed FLOAT NULL,
                    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_user_time (user_id, recorded_at),
                    INDEX idx_recorded_at (recorded_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            }

            // Test simple avec INSERT/UPDATE séparé
            $exists = $this->db->prepare("SELECT COUNT(*) FROM technician_locations WHERE user_id = ?");
            $exists->execute([$user['id']]);

            // Règle qualité: ne pas remplacer une position récente plus précise par une moins précise
            // (utile quand le navigateur repasse sur une localisation réseau/Wi-Fi puis revient au GPS)
            $shouldUpdate = true;
            $current = null;
            try {
                $cur = $this->db->prepare("SELECT latitude, longitude, accuracy, updated_at FROM technician_locations WHERE user_id = ? LIMIT 1");
                $cur->execute([$user['id']]);
                $current = $cur->fetch(\PDO::FETCH_ASSOC) ?: null;
            } catch (\Throwable $e) {
                $current = null;
            }

            if ($current && $accuracy !== null && isset($current['accuracy']) && $current['accuracy'] !== null) {
                $oldAcc = (float)$current['accuracy'];
                $newAcc = (float)$accuracy;
                $oldUpdated = isset($current['updated_at']) ? strtotime((string)$current['updated_at']) : null;
                $ageSec = $oldUpdated ? max(0, time() - $oldUpdated) : null;

                // Si l'ancienne position est très récente (<= 10 min) et nettement plus précise,
                // on refuse d'écraser avec une précision plus mauvaise.
                if ($ageSec !== null && $ageSec <= 600) {
                    if ($oldAcc > 0 && $newAcc > 0) {
                        $isNewClearlyWorse = ($newAcc >= ($oldAcc + 15)) && ($newAcc > 25);
                        if ($isNewClearlyWorse) {
                            $shouldUpdate = false;
                        }
                    }
                }
            }
            
            if ($exists->fetchColumn() > 0) {
                if ($shouldUpdate) {
                    // UPDATE
                    $update = $this->db->prepare("UPDATE technician_locations 
                                                SET latitude = ?, longitude = ?, accuracy = ?, heading = ?, speed = ?, updated_at = NOW() 
                                                WHERE user_id = ?");
                    $update->execute([$lat, $lng, $accuracy, $heading, $speed, $user['id']]);
                }
            } else {
                // INSERT
                $insert = $this->db->prepare("INSERT INTO technician_locations 
                                            (user_id, latitude, longitude, accuracy, heading, speed, updated_at) 
                                            VALUES (?, ?, ?, ?, ?, ?, NOW())");
                $insert->execute([$user['id'], $lat, $lng, $accuracy, $heading, $speed]);
            }
            
            // Ajouter à l'historique (seulement si mouvement significatif détecté > 10m ou premier point)
            $needsHistory = true;
            $lastPos = $this->db->prepare("SELECT latitude, longitude FROM technician_location_history WHERE user_id = ? ORDER BY recorded_at DESC LIMIT 1");
            $lastPos->execute([$user['id']]);
            $last = $lastPos->fetch(\PDO::FETCH_ASSOC);
            
            if ($last) {
                // Calculer distance en mètres (formule haversine simplifiée)
                $earthRadius = 6371000; // mètres
                $dLat = deg2rad((float)$last['latitude'] - $lat);
                $dLng = deg2rad((float)$last['longitude'] - $lng);
                $a = sin($dLat/2) * sin($dLat/2) + cos(deg2rad($lat)) * cos(deg2rad((float)$last['latitude'])) * sin($dLng/2) * sin($dLng/2);
                $c = 2 * atan2(sqrt($a), sqrt(1-$a));
                $distance = $earthRadius * $c;
                
                // Ne stocker que si déplacement > 10 mètres
                $needsHistory = $distance > 10;
            }
            
            if ($needsHistory) {
                $history = $this->db->prepare("INSERT INTO technician_location_history 
                                              (user_id, latitude, longitude, accuracy, heading, speed, recorded_at) 
                                              VALUES (?, ?, ?, ?, ?, ?, NOW())");
                $history->execute([$user['id'], $lat, $lng, $accuracy, $heading, $speed]);
            }

            echo json_encode(['ok' => true, 'debug' => 'Position saved successfully', 'history_added' => $needsHistory, 'updated' => $shouldUpdate]);
        } catch (\Throwable $e) {
            // Log l'erreur complète pour débogage
            error_log("updateLocation error: " . $e->getMessage() . " in " . $e->getFile() . " line " . $e->getLine());
            http_response_code(500);
            echo json_encode([
                'error' => 'server_error', 
                'message' => $e->getMessage(),
                'file' => $e->getFile(),
                'line' => $e->getLine()
            ]);
        }
    }
    
    /**
     * Retourne l'historique des trajectoires des techniciens
     * Paramètres: user_id (optionnel), hours (défaut: 2)
     */
    public function trajectories(): void
    {
        $currentUser = $this->ensurePrivilegedAccess();
        if (!$currentUser) { http_response_code(Auth::check() ? 403 : 401); echo json_encode(['error'=>'unauthorized']); return; }
        header('Content-Type: application/json; charset=utf-8');
        $assignedCity = $this->currentUserAssignedCity($currentUser);
        
        try {
            $hasTable = $this->db->query("SHOW TABLES LIKE 'technician_location_history'")->fetchColumn();
            if (!$hasTable) { echo json_encode([]); return; }
            
            $userId = isset($_GET['user_id']) ? (int)$_GET['user_id'] : null;

            // Support période explicite: from/to (YYYY-MM-DD ou ISO), sinon fallback hours
            $from = isset($_GET['from']) ? trim((string)$_GET['from']) : '';
            $to = isset($_GET['to']) ? trim((string)$_GET['to']) : '';
            $since = null;
            $until = null;
            if ($from !== '') {
                $ts = strtotime($from);
                if ($ts !== false) { $since = date('Y-m-d H:i:s', $ts); }
            }
            if ($to !== '') {
                $ts = strtotime($to);
                if ($ts !== false) { $until = date('Y-m-d H:i:s', $ts); }
            }
            if (!$since && !$until) {
                $hours = isset($_GET['hours']) ? max(1, (int)$_GET['hours']) : 24;
                $since = date('Y-m-d H:i:s', time() - $hours * 3600);
            }
            if (!$since) { $since = date('Y-m-d H:i:s', time() - 24 * 3600); }
            
            if ($userId) {
                $sql = "SELECT h.user_id, u.name, h.latitude, h.longitude, h.speed, h.heading, h.recorded_at
                        FROM technician_location_history h
                        JOIN users u ON u.id = h.user_id
                        WHERE h.user_id = ? AND h.recorded_at >= ?";
                $params = [$userId, $since];
                if ($assignedCity !== null) {
                    $sql .= " AND " . $this->userAssignedCityCondition('u');
                    $params[] = $assignedCity;
                }
                if ($until) {
                    $sql .= " AND h.recorded_at <= ?";
                    $params[] = $until;
                }
                $sql .= "
                        ORDER BY h.recorded_at ASC";
                $stmt = $this->db->prepare($sql);
                $stmt->execute($params);
            } else {
                $sql = "SELECT h.user_id, u.name, h.latitude, h.longitude, h.speed, h.heading, h.recorded_at
                        FROM technician_location_history h
                        JOIN users u ON u.id = h.user_id
                        WHERE h.recorded_at >= ?";
                $params = [$since];
                if ($assignedCity !== null) {
                    $sql .= " AND " . $this->userAssignedCityCondition('u');
                    $params[] = $assignedCity;
                }
                if ($until) {
                    $sql .= " AND h.recorded_at <= ?";
                    $params[] = $until;
                }
                $sql .= "
                        ORDER BY h.user_id, h.recorded_at ASC";
                $stmt = $this->db->prepare($sql);
                $stmt->execute($params);
            }
            
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            
            // Grouper par utilisateur
            $trajectories = [];
            foreach ($rows as $row) {
                $uid = (int)$row['user_id'];
                if (!isset($trajectories[$uid])) {
                    $trajectories[$uid] = [
                        'user_id' => $uid,
                        'name' => $row['name'],
                        'points' => [],
                        'stats' => null,
                    ];
                }
                $trajectories[$uid]['points'][] = [
                    'lat' => (float)$row['latitude'],
                    'lng' => (float)$row['longitude'],
                    'speed' => isset($row['speed']) ? (float)$row['speed'] : null,
                    'heading' => isset($row['heading']) ? (float)$row['heading'] : null,
                    'time' => $row['recorded_at']
                ];
            }

            // Stats incidents déclarés sur période (si table/colonnes disponibles)
            try {
                $hasInc = $this->db->query("SHOW TABLES LIKE 'incidents'")->fetchColumn();
                if ($hasInc) {
                    // Vérifier colonne declared_by
                    $hasDeclaredBy = false;
                    $cols = $this->db->query("SHOW COLUMNS FROM incidents")->fetchAll(\PDO::FETCH_ASSOC);
                    foreach ($cols as $c) { if (($c['Field'] ?? '') === 'declared_by') { $hasDeclaredBy = true; break; } }
                    if ($hasDeclaredBy) {
                        foreach ($trajectories as $uid => &$t) {
                            $q = "SELECT COUNT(*) FROM incidents WHERE declared_by = :uid AND declared_at >= :since";
                            $p = [':uid' => $uid, ':since' => $since];
                            if ($until) { $q .= " AND declared_at <= :until"; $p[':until'] = $until; }
                            $st = $this->db->prepare($q);
                            $st->execute($p);
                            $t['stats'] = [
                                'incidents_declared' => (int)$st->fetchColumn(),
                                'from' => $since,
                                'to' => $until,
                            ];
                        }
                        unset($t);
                    }
                }
            } catch (\Throwable $e) { /* ignore stats errors */ }
            
            echo json_encode(array_values($trajectories));
        } catch (\Throwable $e) {
            error_log("trajectories error: " . $e->getMessage());
            echo json_encode([]);
        }
    }

    /**
     * Liste de techniciens (pour les filtres). Retourne les utilisateurs role_key technicien/agent.
     */
    public function techniciansList(): void
    {
        $currentUser = $this->ensurePrivilegedAccess();
        if (!$currentUser) { http_response_code(Auth::check() ? 403 : 401); echo json_encode(['error'=>'unauthorized']); return; }
        header('Content-Type: application/json; charset=utf-8');
        $assignedCity = $this->currentUserAssignedCity($currentUser);

        try {
            $hasUsers = $this->db->query("SHOW TABLES LIKE 'users'")->fetchColumn();
            if (!$hasUsers) { echo json_encode([]); return; }
            $sql = "SELECT id, COALESCE(name, email) AS name, email, role_key, active, assigned_city FROM users WHERE role_key IN ('technicien','technician','tech','agent')";
            $params = [];
            if ($assignedCity !== null) {
                $sql .= " AND " . $this->userAssignedCityCondition('users');
                $params[] = $assignedCity;
            }
            $sql .= " ORDER BY name";
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            $items = array_map(fn($r) => [
                'id' => (int)($r['id'] ?? 0),
                'name' => $r['name'] ?? ($r['email'] ?? 'Utilisateur'),
                'role' => $r['role_key'] ?? null,
                'active' => isset($r['active']) ? (int)$r['active'] : 1,
            ], $rows);
            echo json_encode($items);
        } catch (\Throwable $e) {
            echo json_encode([]);
        }
    }

    /**
     * Données liaisons: pylônes (Site A/B) + statut agrégé depuis incidents.
     */
    public function liaisons(): void
    {
        if (!$this->ensurePrivilegedAccess()) { http_response_code(Auth::check() ? 403 : 401); echo json_encode(['error'=>'unauthorized']); return; }
        header('Content-Type: application/json; charset=utf-8');

        $debug = isset($_GET['debug']) && (string)$_GET['debug'] !== '0';

        // CACHE SIMPLE: évite de recalculer toutes les 8 secondes (front throttle à 30s)
        // Valide 30 secondes pour alléger la charge serveur
        static $cachedResult = null;
        static $cacheTime = 0;
        $now = time();
        
        if (!$debug && $cachedResult !== null && ($now - $cacheTime) < 30) {
            echo $cachedResult;
            return;
        }

        try {
            $hasLiaisons = $this->db->query("SHOW TABLES LIKE 'liaisons'")->fetchColumn();
            $hasLocations = $this->db->query("SHOW TABLES LIKE 'locations'")->fetchColumn();
            if (!$hasLiaisons || !$hasLocations) { echo json_encode([]); return; }

            // Colonnes disponibles (schémas variables selon dump)
            $locationCols = [];
            try {
                $locationCols = array_map(fn($r) => (string)($r['Field'] ?? ''), $this->db->query('SHOW COLUMNS FROM locations')->fetchAll(\PDO::FETCH_ASSOC));
            } catch (\Throwable $e) { $locationCols = []; }
            $liaisonCols = [];
            try {
                $liaisonCols = array_map(fn($r) => (string)($r['Field'] ?? ''), $this->db->query('SHOW COLUMNS FROM liaisons')->fetchAll(\PDO::FETCH_ASSOC));
            } catch (\Throwable $e) { $liaisonCols = []; }

            $pickCol = function(array $cols, array $candidates): ?string {
                foreach ($candidates as $c) {
                    if (in_array($c, $cols, true)) return $c;
                }
                return null;
            };

            $locLatCol = $pickCol($locationCols, ['latitude','lat']);
            $locLngCol = $pickCol($locationCols, ['longitude','lng','lon']);

            $liaA_lat = $pickCol($liaisonCols, ['site_a_latitude','site_a_lat']);
            $liaA_lng = $pickCol($liaisonCols, ['site_a_longitude','site_a_lng','site_a_lon']);
            $liaB_lat = $pickCol($liaisonCols, ['site_b_latitude','site_b_lat']);
            $liaB_lng = $pickCol($liaisonCols, ['site_b_longitude','site_b_lng','site_b_lon']);

            // Index locations par nom normalisé
            $locations = [];
            if ($locLatCol && $locLngCol) {
                $sqlLoc = "SELECT name, `{$locLatCol}` AS latitude, `{$locLngCol}` AS longitude FROM locations WHERE `{$locLatCol}` IS NOT NULL AND `{$locLngCol}` IS NOT NULL";
                $locations = $this->db->query($sqlLoc)->fetchAll(\PDO::FETCH_ASSOC);
            }
            $locIndex = [];
            $locNamesByKey = [];
            foreach ($locations as $loc) {
                $key = $this->normalizeName((string)($loc['name'] ?? ''));
                if ($key === '') continue;
                $lat = trim((string)($loc['latitude'] ?? ''));
                $lng = trim((string)($loc['longitude'] ?? ''));
                if ($lat === '' || $lng === '') continue;
                $locIndex[$key] = ['lat' => (float)$lat, 'lng' => (float)$lng];
                $locNamesByKey[$key] = (string)($loc['name'] ?? '');
            }

            $selectCols = ['id','name','active','site_a_name','site_b_name'];
            if ($liaA_lat) { $selectCols[] = $liaA_lat . ' AS site_a_lat'; }
            if ($liaA_lng) { $selectCols[] = $liaA_lng . ' AS site_a_lng'; }
            if ($liaB_lat) { $selectCols[] = $liaB_lat . ' AS site_b_lat'; }
            if ($liaB_lng) { $selectCols[] = $liaB_lng . ' AS site_b_lng'; }
            $sqlLia = 'SELECT ' . implode(', ', array_map(function($c){
                // Protéger uniquement les colonnes simples; les alias sont déjà sûrs
                return (strpos($c, ' AS ') !== false) ? $c : ('`' . str_replace('`','',$c) . '`');
            }, $selectCols)) . ' FROM liaisons ORDER BY id DESC';
            $liaisons = $this->db->query($sqlLia)->fetchAll(\PDO::FETCH_ASSOC);

            // En debug, limiter la volumétrie analysée pour éviter de saturer le serveur
            $debugMaxLiaisons = 300;
            if ($debug && count($liaisons) > $debugMaxLiaisons) {
                $liaisons = array_slice($liaisons, 0, $debugMaxLiaisons);
            }

            // Statuts incidents par liaison (si incidents existent)
            $liaisonStatus = [];
            $hasIncidents = $this->db->query("SHOW TABLES LIKE 'incidents'")->fetchColumn();
            $hasStatuses = $this->db->query("SHOW TABLES LIKE 'incident_statuses'")->fetchColumn();
            if ($hasIncidents) {
                // Vérifier colonne liaison_id
                $hasLiaisonId = false;
                $cols = $this->db->query("SHOW COLUMNS FROM incidents LIKE 'liaison_id'")->fetchAll();
                $hasLiaisonId = !empty($cols);
                if ($hasLiaisonId) {
                    // Récupérer TOUS les incidents (pas seulement récents) pour chaque liaison
                    $sql = "SELECT i.id AS incident_id, i.liaison_id, " . ($hasStatuses ? "s.key_name" : "NULL") . " AS status_key, " . ($hasStatuses ? "s.label" : "NULL") . " AS status_label
                            FROM incidents i " . ($hasStatuses ? "LEFT JOIN incident_statuses s ON s.id = i.status_id" : "") . "
                            WHERE i.liaison_id IS NOT NULL
                            ORDER BY i.liaison_id, i.id DESC";
                    $rows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC);

                    $rankOf = function(?string $statusKey, ?string $statusLabel): array {
                        $k = mb_strtolower((string)($statusKey ?? ''));
                        $l = mb_strtolower((string)($statusLabel ?? ''));
                        $txt = $k . ' ' . $l;
                        
                        // Ordre de priorité (rank le plus élevé = plus urgent):
                        // 4 = critical/urgent (rouge vif)
                        // 3 = open/new/pending (rouge)
                        // 2 = in_progress/assigned (orange)
                        // 1 = treated/resolved/awaiting (bleu)
                        // 0 = closed/terminated (vert)
                        
                        // Fermé/terminé/résolu définitivement
                        $closed = str_contains($txt, 'closed') || str_contains($txt, 'clot') || str_contains($txt, 'clôt') 
                            || str_contains($txt, 'ferme') || str_contains($txt, 'termin') || str_contains($txt, 'archiv')
                            || str_contains($txt, 'completed') || str_contains($txt, 'done');
                        if ($closed) return ['bucket' => 'closed', 'rank' => 0];
                        
                        // Traité/résolu temporairement (attente validation)
                        $treated = str_contains($txt, 'treated') || str_contains($txt, 'trait') 
                            || str_contains($txt, 'resolu') || str_contains($txt, 'résolu')
                            || str_contains($txt, 'resolved') || str_contains($txt, 'await')
                            || str_contains($txt, 'validation') || str_contains($txt, 'pending_close');
                        if ($treated) return ['bucket' => 'treated', 'rank' => 1];
                        
                        // En cours/attribué/pris en charge
                        $inProgress = str_contains($txt, 'progress') || str_contains($txt, 'cours') 
                            || str_contains($txt, 'attrib') || str_contains($txt, 'assign')
                            || str_contains($txt, 'working') || str_contains($txt, 'processing')
                            || str_contains($txt, 'ongoing');
                        if ($inProgress) return ['bucket' => 'in_progress', 'rank' => 2];
                        
                        // Critique/urgent (priorité max)
                        $critical = str_contains($txt, 'critical') || str_contains($txt, 'urgent') 
                            || str_contains($txt, 'emergency') || str_contains($txt, 'critiq')
                            || str_contains($txt, 'urgence');
                        if ($critical) return ['bucket' => 'critical', 'rank' => 4];
                        
                        // Par défaut: ouvert/nouveau/en attente (rouge standard)
                        return ['bucket' => 'open', 'rank' => 3];
                    };

                    $incidentsByLiaison = [];
                    foreach ($rows as $r) {
                        $lid = (int)($r['liaison_id'] ?? 0);
                        if ($lid <= 0) continue;
                        $info = $rankOf($r['status_key'] ?? null, $r['status_label'] ?? null);
                        
                        // Garder trace de tous les incidents pour debug
                        if (!isset($incidentsByLiaison[$lid])) {
                            $incidentsByLiaison[$lid] = [];
                        }
                        $incidentsByLiaison[$lid][] = [
                            'id' => (int)($r['incident_id'] ?? 0),
                            'status_key' => $r['status_key'] ?? '',
                            'status_label' => $r['status_label'] ?? '',
                            'bucket' => $info['bucket'],
                            'rank' => $info['rank'],
                        ];
                        
                        // Garder le statut le plus prioritaire (rank le plus élevé = plus urgent)
                        if (!isset($liaisonStatus[$lid]) || $info['rank'] > $liaisonStatus[$lid]['rank']) {
                            $liaisonStatus[$lid] = [
                                'bucket' => $info['bucket'],
                                'rank' => $info['rank'],
                                'status_label' => $r['status_label'] ?? '',
                            ];
                        }
                    }
                    
                    if ($debug) {
                        $debugInfo['incidents_by_liaison'] = $incidentsByLiaison;
                        $debugInfo['liaison_status_summary'] = $liaisonStatus;
                    }
                }
            }

            $items = [];
            $missing = [];

            $suggestClosest = function(string $needleKey) use ($locIndex, $locNamesByKey, $debug): ?array {
                if ($needleKey === '' || empty($locIndex)) return null;

                // Si énormément de sites, le levenshtein devient coûteux.
                // En debug, on préfère renvoyer la clé brute plutôt que de bloquer le serveur.
                if ($debug && count($locIndex) > 5000) {
                    return null;
                }

                $best = null;
                $bestDist = null;
                // Limite simple: si énormément de locations, on garde quand même O(n) mais ça reste ok en pratique.
                foreach ($locIndex as $k => $coords) {
                    $d = levenshtein($needleKey, (string)$k);
                    if ($bestDist === null || $d < $bestDist) {
                        $bestDist = $d;
                        $best = [
                            'key' => (string)$k,
                            'name' => $locNamesByKey[$k] ?? $k,
                            'distance' => $d,
                            'coords' => $coords,
                        ];
                    }
                }
                return $best;
            };

            foreach ($liaisons as $l) {
                $id = (int)($l['id'] ?? 0);
                $name = (string)($l['name'] ?? '');
                $siteA = trim((string)($l['site_a_name'] ?? ''));
                $siteB = trim((string)($l['site_b_name'] ?? ''));
                // fallback parse [A vers B]
                if (($siteA === '' || $siteB === '') && $name !== '' && preg_match('/\[(.*?)\]/', $name, $m)) {
                    $inside = $m[1] ?? '';
                    $parts = preg_split('/\s+(?:vers|to)\s+/iu', (string)$inside);
                    if (is_array($parts) && count($parts) >= 2) {
                        if ($siteA === '') { $siteA = trim((string)$parts[0]); }
                        if ($siteB === '') { $siteB = trim((string)$parts[1]); }
                    }
                }

                $aKey = $this->normalizeName($siteA);
                $bKey = $this->normalizeName($siteB);
                if ($aKey === '' || $bKey === '') {
                    if ($debug) {
                        $missing[] = [
                            'id' => $id,
                            'name' => $name,
                            'site_a' => $siteA,
                            'site_b' => $siteB,
                            'reason' => 'missing_site_names',
                        ];
                    }
                    continue;
                }

                // Résolution coords A/B: priorité aux colonnes coordonnées de la liaison, puis fallback locations.
                $coordsA = null;
                $coordsB = null;
                $aLat = $l['site_a_lat'] ?? null;
                $aLng = $l['site_a_lng'] ?? null;
                $bLat = $l['site_b_lat'] ?? null;
                $bLng = $l['site_b_lng'] ?? null;
                if ($aLat !== null && $aLng !== null && $aLat !== '' && $aLng !== '') {
                    $coordsA = ['lat' => (float)$aLat, 'lng' => (float)$aLng];
                }
                if ($bLat !== null && $bLng !== null && $bLat !== '' && $bLng !== '') {
                    $coordsB = ['lat' => (float)$bLat, 'lng' => (float)$bLng];
                }
                if ($coordsA === null && isset($locIndex[$aKey])) { $coordsA = $locIndex[$aKey]; }
                if ($coordsB === null && isset($locIndex[$bKey])) { $coordsB = $locIndex[$bKey]; }

                if ($coordsA === null || $coordsB === null) {
                    if ($debug) {
                        // Plafonner les entrées debug pour éviter de grossir la réponse
                        if (count($missing) >= 200) {
                            continue;
                        }
                        $missing[] = [
                            'id' => $id,
                            'name' => $name,
                            'site_a' => $siteA,
                            'site_b' => $siteB,
                            'site_a_key' => $aKey,
                            'site_b_key' => $bKey,
                            'missing_a' => ($coordsA === null),
                            'missing_b' => ($coordsB === null),
                            'suggest_a' => ($coordsA === null) ? $suggestClosest($aKey) : null,
                            'suggest_b' => ($coordsB === null) ? $suggestClosest($bKey) : null,
                            'reason' => ($coordsA === null || $coordsB === null) ? 'coords_not_found' : 'unknown',
                        ];
                    }
                    continue;
                }

                // Statut: prioriser le bucket déterminé par incidents, sinon 'unknown' (pas de suivi)
                $statusInfo = $liaisonStatus[$id] ?? null;
                $statusBucket = $statusInfo['bucket'] ?? 'unknown';
                $statusLabel = $statusInfo['status_label'] ?? '';
                
                $item = [
                    'id' => $id,
                    'name' => $name,
                    'site_a' => $siteA,
                    'site_b' => $siteB,
                    'a' => $coordsA,
                    'b' => $coordsB,
                    'status_bucket' => $statusBucket,
                ];
                
                if ($debug && $statusLabel) {
                    $item['status_label_debug'] = $statusLabel;
                }
                
                $items[] = $item;
            }

            if ($debug) {
                echo json_encode([
                    'matched' => $items,
                    'missing' => $missing,
                    'stats' => [
                        'liaisons_total' => count($liaisons),
                        'locations_with_coords' => count($locIndex),
                        'locations_has_latlng_cols' => (bool)($locLatCol && $locLngCol),
                        'liaisons_has_coord_cols' => (bool)(($liaA_lat && $liaA_lng && $liaB_lat && $liaB_lng)),
                        'matched_count' => count($items),
                        'missing_count' => count($missing),
                    ],
                    'hint' => 'Vérifiez les noms: on match sur locations.name vs liaisons.site_a_name/site_b_name (ou parsing [A vers B] dans liaisons.name).',
                ]);
                return;
            }

            $result = json_encode($items);
            
            // Mise en cache du résultat (30s) si pas en mode debug
            if (!$debug) {
                $cachedResult = $result;
                $cacheTime = $now;
            }
            
            echo $result;
        } catch (\Throwable $e) {
            echo json_encode([]);
        }
    }

    /**
     * Récupérer les images d'un incident
     */
    private function getIncidentImages(int $incidentId): array
    {
        try {
            // Vérifier si la table incident_attachments existe
            $hasAttachments = $this->db->query("SHOW TABLES LIKE 'incident_attachments'")->fetchColumn();
            if (!$hasAttachments) return [];
            
            // Récupérer d'abord le report_id de l'incident
            $reportStmt = $this->db->prepare("SELECT id FROM incident_reports WHERE incident_id = :id ORDER BY created_at DESC LIMIT 1");
            $reportStmt->execute([':id' => $incidentId]);
            $report = $reportStmt->fetch(\PDO::FETCH_ASSOC);
            if (!$report) return [];

            $stmt = $this->db->prepare("
                SELECT path, observation, lat, lng, taken_at, uploaded_at 
                FROM incident_attachments 
                WHERE report_id = :report_id 
                  AND type = 'photo'
                ORDER BY uploaded_at DESC
                LIMIT 10
            ");
            $stmt->execute([':report_id' => $report['id']]);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            
            return array_map(function($row) {
                return [
                    'path' => $row['path'],
                    'comment' => $row['observation'] ?? '',
                    'date' => $row['taken_at'] ?? $row['uploaded_at'],
                    'lat' => $row['lat'] ?? null,
                    'lng' => $row['lng'] ?? null
                ];
            }, $rows);
        } catch (\Throwable $e) {
            error_log("getIncidentImages error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Récupérer les commentaires d'un incident
     */
    private function getIncidentComments(int $incidentId): array
    {
        try {
            // Vérifier si la table incident_reports existe
            $hasReports = $this->db->query("SHOW TABLES LIKE 'incident_reports'")->fetchColumn();
            if (!$hasReports) return [];

            $stmt = $this->db->prepare("
                SELECT ir.work_comments, ir.supervisor_comments, ir.created_at,
                       COALESCE(u.full_name, u.name, u.username) as author_name
                FROM incident_reports ir
                LEFT JOIN users u ON u.id = ir.technician_id
                WHERE ir.incident_id = :id 
                  AND (ir.work_comments IS NOT NULL OR ir.supervisor_comments IS NOT NULL)
                ORDER BY ir.created_at DESC
                LIMIT 10
            ");
            $stmt->execute([':id' => $incidentId]);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            
            return array_map(function($row) {
                $text = '';
                if (!empty($row['work_comments'])) {
                    $text = $row['work_comments'];
                }
                if (!empty($row['supervisor_comments'])) {
                    $text .= ($text ? "\n\nNotes superviseur: " : '') . $row['supervisor_comments'];
                }
                return [
                    'text' => $text,
                    'author' => $row['author_name'] ?? 'Système',
                    'date' => $row['created_at']
                ];
            }, $rows);
        } catch (\Throwable $e) {
            error_log("getIncidentComments error: " . $e->getMessage());
            return [];
        }
    }
}