<?php
namespace App\Core;

use PDO;

class RaccordementOtbService
{
    private const DEFAULT_ALERT_THRESHOLD_PCT = 80;

    public static function ensureSchema(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS raccordement_zone_settings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            zone_key VARCHAR(120) NOT NULL,
            zone_label VARCHAR(190) NOT NULL,
            plaque VARCHAR(120) NULL,
            hub VARCHAR(120) NULL,
            default_radius_meters INT NOT NULL DEFAULT 1000,
            technician_ids JSON NULL,
            supervisor_ids JSON NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_raccordement_zone_key (zone_key)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS raccordement_otb_boxes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            box_code VARCHAR(120) NOT NULL,
            city_name VARCHAR(120) NULL,
            olt_name VARCHAR(190) NULL,
            slot_port VARCHAR(120) NULL,
            sector_code VARCHAR(120) NULL,
            plaque VARCHAR(120) NULL,
            hub VARCHAR(120) NULL,
            zone_key VARCHAR(120) NULL,
            zone_label VARCHAR(190) NULL,
            location_details TEXT NULL,
            latitude DECIMAL(10,7) NOT NULL,
            longitude DECIMAL(10,7) NOT NULL,
            capacity_ports INT NOT NULL DEFAULT 8,
            occupied_ports INT NOT NULL DEFAULT 0,
            deployment_date DATE NULL,
            saturation_label VARCHAR(80) NULL,
            service_provider VARCHAR(120) NULL,
            source_file_name VARCHAR(190) NULL,
            source_imported_at DATETIME NULL,
            alert_threshold_pct INT NOT NULL DEFAULT 80,
            utilization_alert_state ENUM('normal','alert') NOT NULL DEFAULT 'normal',
            utilization_alert_sent_at DATETIME NULL,
            status ENUM('active','maintenance','inactive') NOT NULL DEFAULT 'active',
            notes TEXT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_raccordement_otb_code (box_code),
            KEY idx_raccordement_otb_zone (zone_key),
            KEY idx_raccordement_otb_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        foreach ([
            "ALTER TABLE raccordement_zone_settings ADD COLUMN plaque VARCHAR(120) NULL AFTER zone_label",
            "ALTER TABLE raccordement_zone_settings ADD COLUMN hub VARCHAR(120) NULL AFTER plaque",
            "ALTER TABLE raccordement_zone_settings ADD COLUMN default_radius_meters INT NOT NULL DEFAULT 1000 AFTER hub",
            "ALTER TABLE raccordement_zone_settings ADD COLUMN technician_ids JSON NULL AFTER default_radius_meters",
            "ALTER TABLE raccordement_zone_settings ADD COLUMN supervisor_ids JSON NULL AFTER technician_ids",
            "ALTER TABLE raccordement_zone_settings ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER supervisor_ids",
            "ALTER TABLE raccordement_zone_settings ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at",
            "ALTER TABLE raccordement_zone_settings ADD UNIQUE KEY uq_raccordement_zone_key (zone_key)",
            "ALTER TABLE maintenance_ftth_tickets ADD COLUMN dispatch_zone_key VARCHAR(120) NULL AFTER company_name",
            "ALTER TABLE maintenance_ftth_tickets ADD COLUMN dispatch_zone_label VARCHAR(190) NULL AFTER dispatch_zone_key",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN selected_otb_id INT NULL AFTER materials_used",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN selected_otb_label VARCHAR(190) NULL AFTER selected_otb_id",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN recommended_otb_id INT NULL AFTER selected_otb_label",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN recommended_otb_label VARCHAR(190) NULL AFTER recommended_otb_id",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN otb_distance_meters DECIMAL(10,2) NULL AFTER recommended_otb_label",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN otb_selection_mode ENUM('auto','manual','none') NOT NULL DEFAULT 'none' AFTER otb_distance_meters",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN otb_recommendation_payload JSON NULL AFTER otb_selection_mode",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN otb_occupancy_synced TINYINT(1) NOT NULL DEFAULT 0 AFTER otb_recommendation_payload",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN submit_gps_accuracy_meters DECIMAL(10,2) NULL AFTER submit_gps_lng",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN workflow_phase ENUM('study','installation') NOT NULL DEFAULT 'study' AFTER status",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN study_technician_id INT NULL AFTER workflow_phase",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN installation_technician_id INT NULL AFTER study_technician_id",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN study_submitted_at DATETIME NULL AFTER installation_technician_id",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN installation_sent_at DATETIME NULL AFTER study_submitted_at",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN study_gps_lat DECIMAL(10,7) NULL AFTER installation_sent_at",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN study_gps_lng DECIMAL(10,7) NULL AFTER study_gps_lat",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN study_gps_accuracy_meters DECIMAL(10,2) NULL AFTER study_gps_lng",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN selected_otb_port_number INT NULL AFTER selected_otb_id",
            "ALTER TABLE maintenance_ftth_reports ADD COLUMN selected_otb_port_status ENUM('none','reserved','occupied') NOT NULL DEFAULT 'none' AFTER selected_otb_port_number",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN city_name VARCHAR(120) NULL AFTER box_code",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN olt_name VARCHAR(190) NULL AFTER city_name",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN slot_port VARCHAR(120) NULL AFTER olt_name",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN sector_code VARCHAR(120) NULL AFTER slot_port",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN location_details TEXT NULL AFTER zone_label",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN deployment_date DATE NULL AFTER occupied_ports",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN saturation_label VARCHAR(80) NULL AFTER deployment_date",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN service_provider VARCHAR(120) NULL AFTER saturation_label",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN source_file_name VARCHAR(190) NULL AFTER service_provider",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN source_imported_at DATETIME NULL AFTER source_file_name",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN alert_threshold_pct INT NOT NULL DEFAULT 80 AFTER occupied_ports",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN utilization_alert_state ENUM('normal','alert') NOT NULL DEFAULT 'normal' AFTER alert_threshold_pct",
            "ALTER TABLE raccordement_otb_boxes ADD COLUMN utilization_alert_sent_at DATETIME NULL AFTER utilization_alert_state",
            "ALTER TABLE raccordement_otb_boxes ADD INDEX idx_raccordement_otb_zone_label_code (zone_label, box_code)",
            "ALTER TABLE maintenance_ftth_reports ADD INDEX idx_mfr_otb_status (selected_otb_id, selected_otb_port_status)",
            "ALTER TABLE maintenance_ftth_reports ADD INDEX idx_mfr_otb_port (selected_otb_id, selected_otb_port_number, selected_otb_port_status)"
        ] as $sql) {
            try {
                $pdo->exec($sql);
            } catch (\Throwable $e) {
            }
        }
    }

    public static function defaultAlertThresholdPct(): int
    {
        return self::DEFAULT_ALERT_THRESHOLD_PCT;
    }

    public static function normalizeZoneKey(?string $value): string
    {
        $value = trim((string)$value);
        if ($value === '') {
            return '';
        }

        $ascii = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
        if ($ascii !== false) {
            $value = $ascii;
        }

        $value = strtolower($value);
        $value = preg_replace('/[^a-z0-9]+/', '_', $value) ?? $value;

        return trim($value, '_');
    }

    public static function decodeIdList($raw): array
    {
        if (is_string($raw)) {
            $raw = json_decode($raw, true);
        }

        if (!is_array($raw)) {
            return [];
        }

        $ids = [];
        foreach ($raw as $value) {
            $id = (int)$value;
            if ($id > 0) {
                $ids[$id] = $id;
            }
        }

        return array_values($ids);
    }

    public static function getZoneSetting(PDO $pdo, ?string $zoneKey): ?array
    {
        $normalized = self::normalizeZoneKey($zoneKey);
        if ($normalized === '') {
            return null;
        }

        $stmt = $pdo->prepare('SELECT * FROM raccordement_zone_settings WHERE zone_key = ? LIMIT 1');
        $stmt->execute([$normalized]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) {
            return null;
        }

        $row['technician_ids'] = self::decodeIdList($row['technician_ids'] ?? null);
        $row['supervisor_ids'] = self::decodeIdList($row['supervisor_ids'] ?? null);

        return $row;
    }

    public static function resolveRadiusMeters(PDO $pdo, ?string $zoneKey, int $fallback = 1000): int
    {
        $setting = self::getZoneSetting($pdo, $zoneKey);
        $radius = (int)($setting['default_radius_meters'] ?? 0);

        return $radius > 0 ? $radius : $fallback;
    }

    public static function findBoxById(PDO $pdo, int $boxId): ?array
    {
        if ($boxId <= 0) {
            return null;
        }

        $stmt = $pdo->prepare('SELECT * FROM raccordement_otb_boxes WHERE id = ? LIMIT 1');
        $stmt->execute([$boxId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return is_array($row) ? self::decorateBox($row) : null;
    }

    public static function getBoxRealtimeSnapshot(PDO $pdo, int $boxId): ?array
    {
        if ($boxId <= 0) {
            return null;
        }

        $stmt = $pdo->prepare('SELECT * FROM raccordement_otb_boxes WHERE id = ? LIMIT 1');
        $stmt->execute([$boxId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) {
            return null;
        }

        $row['occupied_ports'] = self::resolveEffectiveBaseOccupiedPorts($pdo, $row);
        $snapshot = self::decorateBoxWithRealtimeStats($pdo, $row);
        $snapshot['ports'] = self::getAvailablePorts($pdo, $boxId);

        return $snapshot;
    }

    public static function decorateBoxesWithRealtimeStats(PDO $pdo, array $boxes): array
    {
        if ($boxes === []) {
            return [];
        }

        $boxIds = array_values(array_filter(array_map(static fn(array $box): int => (int)($box['id'] ?? 0), $boxes), static fn(int $boxId): bool => $boxId > 0));
        if ($boxIds === []) {
            return array_map(static fn(array $box): array => self::decorateBox($box), $boxes);
        }

        $realtimeStats = self::getRealtimePortStatsMap($pdo, $boxIds);
        $baseOccupiedMap = self::resolveEffectiveBaseOccupiedPortsMap($pdo, $boxes);

        return array_map(function (array $box) use ($realtimeStats, $baseOccupiedMap): array {
            $boxId = (int)($box['id'] ?? 0);
            if ($boxId > 0 && array_key_exists($boxId, $baseOccupiedMap)) {
                $box['occupied_ports'] = $baseOccupiedMap[$boxId];
            }

            return self::applyRealtimeUsage($box, $realtimeStats[$boxId] ?? null);
        }, $boxes);
    }

    private static function resolveEffectiveBaseOccupiedPorts(PDO $pdo, array $box): int
    {
        $boxId = (int)($box['id'] ?? 0);
        $capacity = max(0, (int)($box['capacity_ports'] ?? 0));
        if ($boxId <= 0 || $capacity <= 0) {
            return 0;
        }

        $normalizedBoxCode = self::normalizePcoIdentifier((string)($box['box_code'] ?? ''));
        $params = [$boxId];
        $pcoClause = '';
        if ($normalizedBoxCode !== '') {
            $pcoClause = ' OR UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(t.pco_client, "")), " ", ""), "_", ""), "-", ""), "/", ""), ".", "")) = ?
                           OR UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(r.selected_otb_label, "")), " ", ""), "_", ""), "-", ""), "/", ""), ".", "")) = ?';
            $params[] = $normalizedBoxCode;
            $params[] = $normalizedBoxCode;
        }

        $stmt = $pdo->prepare(
            'SELECT
                COUNT(DISTINCT t.id) AS linked_ticket_count,
                COUNT(DISTINCT CASE
                    WHEN (
                        (
                            COALESCE(r.client_visit_status, "") = "intervention_effectuee"
                            AND COALESCE(r.status, "") IN ("soumis", "validé")
                        )
                        OR COALESCE(r.selected_otb_port_status, "") = "occupied"
                    )
                    AND (r.id IS NULL OR r.selected_otb_port_number IS NULL OR r.selected_otb_port_number <= 0)
                    THEN t.id
                    ELSE NULL
                END) AS validated_anonymous_count
             FROM maintenance_ftth_tickets t
             LEFT JOIN maintenance_ftth_reports r ON r.ticket_id = t.id
             WHERE (r.selected_otb_id = ?' . $pcoClause . ')
               AND ' . self::raccordementTicketSqlClause('t')
        );
        $stmt->execute($params);
        $stats = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $linkedTicketCount = (int)($stats['linked_ticket_count'] ?? 0);
        if ($linkedTicketCount <= 0) {
            return 0;
        }

        return min($capacity, max(0, (int)($stats['validated_anonymous_count'] ?? 0)));
    }

    private static function resolveEffectiveBaseOccupiedPortsMap(PDO $pdo, array $boxes): array
    {
        if ($boxes === []) {
            return [];
        }

        $boxMeta = [];
        $normalizedCodes = [];
        foreach ($boxes as $box) {
            $boxId = (int)($box['id'] ?? 0);
            $capacity = max(0, (int)($box['capacity_ports'] ?? 0));
            if ($boxId <= 0 || $capacity <= 0) {
                continue;
            }

            $normalizedCode = self::normalizePcoIdentifier((string)($box['box_code'] ?? ''));
            $boxMeta[$boxId] = [
                'capacity' => $capacity,
                'normalized_code' => $normalizedCode,
            ];

            if ($normalizedCode !== '') {
                $normalizedCodes[$normalizedCode] = $boxId;
            }
        }

        if ($boxMeta === []) {
            return [];
        }

        $boxIds = array_keys($boxMeta);
        $conditions = [];
        $params = [];
        $boxIdPlaceholders = implode(',', array_fill(0, count($boxIds), '?'));
        $conditions[] = 'r.selected_otb_id IN (' . $boxIdPlaceholders . ')';
        $params = array_merge($params, $boxIds);

        if ($normalizedCodes !== []) {
            $codePlaceholders = implode(',', array_fill(0, count($normalizedCodes), '?'));
            $normalizedPcoSql = 'UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(t.pco_client, "")), " ", ""), "_", ""), "-", ""), "/", ""), ".", ""))';
            $normalizedLabelSql = 'UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(r.selected_otb_label, "")), " ", ""), "_", ""), "-", ""), "/", ""), ".", ""))';
            $conditions[] = $normalizedPcoSql . ' IN (' . $codePlaceholders . ')';
            $conditions[] = $normalizedLabelSql . ' IN (' . $codePlaceholders . ')';
            $params = array_merge($params, array_keys($normalizedCodes), array_keys($normalizedCodes));
        } else {
            $normalizedPcoSql = '""';
            $normalizedLabelSql = '""';
        }

        $stmt = $pdo->prepare(
            'SELECT
                t.id AS ticket_id,
                r.selected_otb_id,
                r.selected_otb_port_status,
                r.selected_otb_port_number,
                COALESCE(r.client_visit_status, "") AS client_visit_status,
                COALESCE(r.status, "") AS report_status,
                ' . $normalizedPcoSql . ' AS normalized_ticket_pco,
                ' . $normalizedLabelSql . ' AS normalized_selected_otb_label
             FROM maintenance_ftth_tickets t
             LEFT JOIN maintenance_ftth_reports r ON r.ticket_id = t.id
             WHERE (' . implode(' OR ', $conditions) . ')
               AND ' . self::raccordementTicketSqlClause('t')
        );
        $stmt->execute($params);

        $linkedTicketsByBox = [];
        $validatedTicketsByBox = [];
        foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) {
            $ticketId = (int)($row['ticket_id'] ?? 0);
            if ($ticketId <= 0) {
                continue;
            }

            $matchedBoxIds = [];

            $selectedOtbId = (int)($row['selected_otb_id'] ?? 0);
            if ($selectedOtbId > 0 && isset($boxMeta[$selectedOtbId])) {
                $matchedBoxIds[$selectedOtbId] = true;
            }

            $normalizedTicketPco = trim((string)($row['normalized_ticket_pco'] ?? ''));
            if ($normalizedTicketPco !== '' && isset($normalizedCodes[$normalizedTicketPco])) {
                $matchedBoxIds[$normalizedCodes[$normalizedTicketPco]] = true;
            }

            $normalizedSelectedOtbLabel = trim((string)($row['normalized_selected_otb_label'] ?? ''));
            if ($normalizedSelectedOtbLabel !== '' && isset($normalizedCodes[$normalizedSelectedOtbLabel])) {
                $matchedBoxIds[$normalizedCodes[$normalizedSelectedOtbLabel]] = true;
            }

            if ($matchedBoxIds === []) {
                continue;
            }

            $isValidatedAnonymous = (
                (
                    (string)($row['client_visit_status'] ?? '') === 'intervention_effectuee'
                    && in_array((string)($row['report_status'] ?? ''), ['soumis', 'validé'], true)
                )
                || (string)($row['selected_otb_port_status'] ?? '') === 'occupied'
            ) && (int)($row['selected_otb_port_number'] ?? 0) <= 0;

            foreach (array_keys($matchedBoxIds) as $boxId) {
                $linkedTicketsByBox[$boxId][$ticketId] = true;
                if ($isValidatedAnonymous) {
                    $validatedTicketsByBox[$boxId][$ticketId] = true;
                }
            }
        }

        $result = [];
        foreach ($boxMeta as $boxId => $meta) {
            if (empty($linkedTicketsByBox[$boxId])) {
                $result[$boxId] = 0;
                continue;
            }

            $result[$boxId] = min($meta['capacity'], count($validatedTicketsByBox[$boxId] ?? []));
        }

        return $result;
    }

    private static function getRealtimePortStatsMap(PDO $pdo, array $boxIds): array
    {
        $boxIds = array_values(array_filter(array_map('intval', $boxIds), static fn(int $boxId): bool => $boxId > 0));
        if ($boxIds === []) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($boxIds), '?'));
        $stmt = $pdo->prepare(
            "SELECT selected_otb_id,
                    CASE
                        WHEN selected_otb_port_status = 'occupied' THEN 'occupied'
                        WHEN selected_otb_port_status = 'reserved' AND client_visit_status = 'intervention_effectuee' AND status IN ('soumis','validé') THEN 'occupied'
                        WHEN selected_otb_port_status = 'reserved' THEN 'reserved'
                        ELSE 'none'
                    END AS effective_port_status,
                    COUNT(*) AS port_count
             FROM maintenance_ftth_reports
             WHERE selected_otb_id IN ($placeholders)
               AND selected_otb_port_status IN ('reserved','occupied')
             GROUP BY selected_otb_id, effective_port_status"
        );
        $stmt->execute($boxIds);

        $realtimeStats = [];
        foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) {
            $boxId = (int)($row['selected_otb_id'] ?? 0);
            $status = (string)($row['effective_port_status'] ?? 'reserved');
            $count = (int)($row['port_count'] ?? 0);
            if ($boxId <= 0 || $count <= 0 || !in_array($status, ['reserved', 'occupied'], true)) {
                continue;
            }

            if (!isset($realtimeStats[$boxId])) {
                $realtimeStats[$boxId] = ['reserved' => 0, 'occupied' => 0];
            }
            if ($status === 'occupied') {
                $realtimeStats[$boxId]['occupied'] += $count;
            } elseif ($status === 'reserved') {
                $realtimeStats[$boxId]['reserved'] += $count;
            }
        }

        return $realtimeStats;
    }

    private static function applyRealtimeUsage(array $box, ?array $realtimeStats = null): array
    {
        $box = self::decorateBox($box);
        $capacity = max(0, (int)($box['capacity_ports'] ?? 0));
        $baseOccupiedPorts = min($capacity, max(0, (int)($box['occupied_ports'] ?? 0)));
        $workflowOccupiedPorts = max(0, (int)($realtimeStats['occupied'] ?? 0));
        $effectiveOccupiedPorts = min($capacity, max($baseOccupiedPorts, $workflowOccupiedPorts));
        $reservedPorts = min(max(0, $capacity - $effectiveOccupiedPorts), max(0, (int)($realtimeStats['reserved'] ?? 0)));
        $usedPorts = min($capacity, $effectiveOccupiedPorts + $reservedPorts);

        $box['occupied_ports'] = $effectiveOccupiedPorts;
        $box['reserved_ports'] = $reservedPorts;
        $box['used_ports'] = $usedPorts;
        $box['ports_available'] = max(0, $capacity - $usedPorts);
        $box['occupancy_rate'] = $capacity > 0 ? round(($usedPorts / $capacity) * 100, 1) : 0.0;
        $box['is_alert'] = (string)($box['status'] ?? 'active') === 'active'
            && $capacity > 0
            && (float)($box['occupancy_rate'] ?? 0) >= (int)($box['alert_threshold_pct'] ?? self::DEFAULT_ALERT_THRESHOLD_PCT);

        return $box;
    }

    public static function buildRecommendation(PDO $pdo, ?float $clientLat, ?float $clientLng, ?string $zoneKey = null): array
    {
        $radiusMeters = self::resolveRadiusMeters($pdo, $zoneKey, 1000);
        $stmt = $pdo->query("SELECT * FROM raccordement_otb_boxes WHERE status = 'active' ORDER BY box_code ASC");
        $rows = $stmt ? self::decorateBoxesWithRealtimeStats($pdo, $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) : [];
        $zoneKey = self::normalizeZoneKey($zoneKey);
        $candidates = [];

        if ($clientLat === null || $clientLng === null) {
            foreach ($rows as $row) {
                $box = $row;
                if (($box['ports_available'] ?? 0) <= 0) {
                    continue;
                }

                $box['distance_meters'] = null;
                $box['distance_label'] = 'Distance indisponible';
                $box['within_radius'] = false;
                $box['same_zone'] = $zoneKey !== '' && self::normalizeZoneKey((string)($box['zone_key'] ?? '')) === $zoneKey;
                $candidates[] = $box;
            }

            usort($candidates, static function (array $left, array $right): int {
                $zoneCompare = (($right['same_zone'] ? 1 : 0) <=> ($left['same_zone'] ? 1 : 0));
                if ($zoneCompare !== 0) {
                    return $zoneCompare;
                }

                $portsCompare = (($right['ports_available'] ?? 0) <=> ($left['ports_available'] ?? 0));
                if ($portsCompare !== 0) {
                    return $portsCompare;
                }

                return strcmp((string)($left['box_code'] ?? ''), (string)($right['box_code'] ?? ''));
            });

            $sameZoneCandidates = array_values(array_filter($candidates, static fn(array $candidate): bool => !empty($candidate['same_zone'])));
            $recommended = $sameZoneCandidates[0] ?? null;
            $alert = $recommended
                ? 'Coordonnées GPS client indisponibles. La recommandation OTB est basée sur la zone du ticket.'
                : 'Coordonnées GPS client indisponibles. Les boîtiers actifs restent disponibles en sélection manuelle.';

            return [
                'radius_meters' => $radiusMeters,
                'recommended' => $recommended,
                'candidates' => array_slice($candidates, 0, 20),
                'out_of_radius' => [],
                'alert' => $alert,
            ];
        }

        foreach ($rows as $row) {
            $box = $row;
            if (($box['ports_available'] ?? 0) <= 0) {
                continue;
            }

            $distance = self::haversineMeters($clientLat, $clientLng, (float)$box['latitude'], (float)$box['longitude']);
            $box['distance_meters'] = round($distance, 2);
            $box['distance_label'] = self::formatDistance($distance);
            $box['within_radius'] = $distance <= $radiusMeters;
            $box['same_zone'] = $zoneKey !== '' && self::normalizeZoneKey((string)($box['zone_key'] ?? '')) === $zoneKey;
            $candidates[] = $box;
        }

        usort($candidates, static function (array $left, array $right): int {
            $distanceCompare = ($left['distance_meters'] <=> $right['distance_meters']);
            if ($distanceCompare !== 0) {
                return $distanceCompare;
            }

            $portsCompare = (($right['ports_available'] ?? 0) <=> ($left['ports_available'] ?? 0));
            if ($portsCompare !== 0) {
                return $portsCompare;
            }

            return strcmp((string)($left['box_code'] ?? ''), (string)($right['box_code'] ?? ''));
        });

        $withinRadius = array_values(array_filter($candidates, static fn(array $candidate): bool => !empty($candidate['within_radius'])));
        $recommended = $withinRadius[0] ?? null;
        $outOfRadius = [];
        $alert = null;

        if ($recommended === null) {
            $outOfRadius = array_slice($candidates, 0, 3);
            $alert = 'Aucun boîtier OTB disponible dans le rayon configuré pour cette zone. Escalade superviseur recommandée.';
        }

        return [
            'radius_meters' => $radiusMeters,
            'recommended' => $recommended,
            'candidates' => array_slice($withinRadius, 0, 20),
            'out_of_radius' => $outOfRadius,
            'alert' => $alert,
        ];
    }

    public static function syncOccupancyFromReport(PDO $pdo, int $reportId): void
    {
        if ($reportId <= 0) {
            return;
        }

        $stmt = $pdo->prepare('SELECT id, selected_otb_id, selected_otb_port_status, otb_occupancy_synced, status FROM maintenance_ftth_reports WHERE id = ? LIMIT 1');
        $stmt->execute([$reportId]);
        $report = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($report)) {
            return;
        }

        $selectedOtbId = (int)($report['selected_otb_id'] ?? 0);
        if ($selectedOtbId <= 0 || (int)($report['otb_occupancy_synced'] ?? 0) === 1 || (string)($report['status'] ?? '') !== 'validé') {
            return;
        }

        $pdo->beginTransaction();
        try {
            $pdo->prepare('UPDATE maintenance_ftth_reports SET selected_otb_port_status = ? WHERE id = ?')
                ->execute(['occupied', $reportId]);
            self::recomputeBoxOccupancy($pdo, $selectedOtbId);
            $pdo->prepare('UPDATE maintenance_ftth_reports SET otb_occupancy_synced = 1 WHERE id = ?')->execute([$reportId]);
            $pdo->commit();
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
        }
    }

    public static function summarize(PDO $pdo): array
    {
        $rows = $pdo->query('SELECT id, box_code, status, capacity_ports, occupied_ports, alert_threshold_pct, saturation_label FROM raccordement_otb_boxes')->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $summary = [
            'total_boxes' => 0,
            'total_capacity' => 0,
            'total_occupied' => 0,
            'total_reserved' => 0,
            'total_used' => 0,
            'total_available' => 0,
            'active_boxes' => 0,
            'alert_boxes' => 0,
            'saturated_boxes' => 0,
            'avg_alert_threshold_pct' => 0.0,
        ];

        $rows = self::decorateBoxesWithRealtimeStats($pdo, $rows);
        $thresholdAccumulator = 0;
        $thresholdCount = 0;

        foreach ($rows as $row) {
            $box = $row;

            $reserved = (int)($box['reserved_ports'] ?? 0);
            $occupied = (int)($box['occupied_ports'] ?? 0);
            $used = (int)($box['used_ports'] ?? 0);
            $available = (int)($box['ports_available'] ?? 0);
            $capacity = (int)($box['capacity_ports'] ?? 0);
            $threshold = (int)($box['alert_threshold_pct'] ?? self::DEFAULT_ALERT_THRESHOLD_PCT);
            $threshold = max(1, min(100, $threshold));
            $status = (string)($box['status'] ?? 'active');
            $summary['total_boxes']++;
            $summary['total_capacity'] += $capacity;
            $summary['total_occupied'] += $occupied;
            $summary['total_reserved'] += $reserved;
            $summary['total_used'] += $used;
            $summary['total_available'] += $available;
            $thresholdAccumulator += $threshold;
            $thresholdCount++;

            if ($status === 'active') {
                $summary['active_boxes']++;
            }

            if ($status === 'active' && !empty($box['is_alert'])) {
                $summary['alert_boxes']++;
            }
            if ($status === 'active' && $capacity > 0 && $available <= 0) {
                $summary['saturated_boxes']++;
            }
        }

        if ($summary['total_capacity'] > 0) {
            $summary['occupancy_rate'] = round(($summary['total_used'] / $summary['total_capacity']) * 100, 1);
        }
        $summary['avg_alert_threshold_pct'] = $thresholdCount > 0
            ? round($thresholdAccumulator / $thresholdCount, 1)
            : (float)self::DEFAULT_ALERT_THRESHOLD_PCT;

        return $summary;
    }

    public static function evaluateUtilizationAlert(PDO $pdo, int $boxId): void
    {
        $box = self::getBoxRealtimeSnapshot($pdo, $boxId);
        if (!$box) {
            return;
        }

        $threshold = max(1, min(100, (int)($box['alert_threshold_pct'] ?? self::DEFAULT_ALERT_THRESHOLD_PCT)));
        $isAlert = (string)($box['status'] ?? 'active') === 'active'
            && (int)($box['capacity_ports'] ?? 0) > 0
            && (float)($box['occupancy_rate'] ?? 0) >= $threshold;
        $currentState = (string)($box['utilization_alert_state'] ?? 'normal');

        if (!$isAlert) {
            if ($currentState !== 'normal') {
                $pdo->prepare('UPDATE raccordement_otb_boxes SET utilization_alert_state = ?, utilization_alert_sent_at = NULL WHERE id = ?')
                    ->execute(['normal', $boxId]);
            }
            return;
        }

        if ($currentState === 'alert') {
            return;
        }

        $pdo->prepare('UPDATE raccordement_otb_boxes SET utilization_alert_state = ?, utilization_alert_sent_at = NOW() WHERE id = ?')
            ->execute(['alert', $boxId]);

        $recipientIds = self::resolveAlertRecipientIds($pdo, (string)($box['zone_key'] ?? ''));
        if ($recipientIds === []) {
            return;
        }

        $usedPorts = (int)($box['used_ports'] ?? 0);
        $capacity = (int)($box['capacity_ports'] ?? 0);
        $available = (int)($box['ports_available'] ?? 0);
        $rate = number_format((float)($box['occupancy_rate'] ?? 0), 1, ',', ' ');
        $zoneLabel = trim((string)($box['zone_label'] ?? ''));
        $title = '[OTB] Saturation à surveiller';
        $body = 'Le boîtier ' . ((string)($box['box_code'] ?? ('#' . $boxId)))
            . ' atteint ' . $rate . '% d\'utilisation (' . $usedPorts . '/' . $capacity . ' ports utilisés, ' . $available . ' libre(s)).'
            . ($zoneLabel !== '' ? ' Zone: ' . $zoneLabel . '.' : '')
            . ' Un nouveau boîtier doit être envisagé pour éviter la saturation.';
        $url = route_url('/otb-management');

        Notifier::notifyUsers($pdo, $recipientIds, $title, $body, $url);
        Notifier::emailUsers($pdo, $recipientIds, $title, nl2br(htmlspecialchars($body)) . '<br><a href="' . htmlspecialchars($url, ENT_QUOTES, 'UTF-8') . '">Ouvrir la gestion OTB</a>');
        Notifier::pushUsers($pdo, $recipientIds, $title, $body, $url);
    }

    public static function getAvailablePorts(PDO $pdo, int $boxId, ?int $currentReportId = null): array
    {
        $box = self::findBoxById($pdo, $boxId);
        if (!$box) {
            return [];
        }

        $capacity = max(0, (int)($box['capacity_ports'] ?? 0));
        $baseOccupiedPorts = self::resolveEffectiveBaseOccupiedPorts($pdo, $box);
        if ($capacity <= 0) {
            return [];
        }

         $stmt = $pdo->prepare(
             "SELECT r.id,
                  r.ticket_id,
                  r.selected_otb_port_number,
                  CASE
                      WHEN r.selected_otb_port_status = 'occupied' THEN 'occupied'
                      WHEN r.selected_otb_port_status = 'reserved' AND r.client_visit_status = 'intervention_effectuee' AND r.status IN ('soumis','validé') THEN 'occupied'
                      WHEN r.selected_otb_port_status = 'reserved' THEN 'reserved'
                      ELSE 'none'
                  END AS effective_port_status,
                  r.status AS report_status,
                  r.client_name_onsite,
                  t.client_name,
                  t.company_name,
                  t.ref_code
              FROM maintenance_ftth_reports r
              LEFT JOIN maintenance_ftth_tickets t ON t.id = r.ticket_id
              WHERE r.selected_otb_id = ?
             AND r.selected_otb_port_number IS NOT NULL
             AND r.selected_otb_port_status IN ('reserved','occupied')"
         );
        $stmt->execute([$boxId]);
        $usedRows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $usedPorts = [];
        foreach ($usedRows as $row) {
            if ($currentReportId !== null && (int)($row['id'] ?? 0) === $currentReportId) {
                continue;
            }
            $portNumber = (int)($row['selected_otb_port_number'] ?? 0);
            if ($portNumber > 0) {
                $clientName = trim((string)(($row['client_name_onsite'] ?? '') ?: ($row['client_name'] ?? '')));
                $companyName = trim((string)($row['company_name'] ?? ''));
                $usedPorts[$portNumber] = [
                    'status' => (string)($row['effective_port_status'] ?? 'reserved'),
                    'report_id' => (int)($row['id'] ?? 0),
                    'ticket_id' => (int)($row['ticket_id'] ?? 0),
                    'client_name' => $clientName,
                    'company_name' => $companyName,
                    'ref_code' => trim((string)($row['ref_code'] ?? '')),
                ];
            }
        }

        $explicitOccupiedPorts = 0;
        foreach ($usedPorts as $portUsage) {
            if (($portUsage['status'] ?? 'reserved') === 'occupied') {
                $explicitOccupiedPorts++;
            }
        }

        $anonymousImportedOccupiedPorts = max(0, $baseOccupiedPorts - $explicitOccupiedPorts);
        $fallbackFtthClients = $anonymousImportedOccupiedPorts > 0
            ? self::findFallbackFtthClientsForBox($pdo, $boxId, (string)($box['box_code'] ?? ''), $currentReportId)
            : [];

        $ports = [];
        for ($portNumber = 1; $portNumber <= $capacity; $portNumber++) {
            $portUsage = $usedPorts[$portNumber] ?? null;
            $status = is_array($portUsage) ? (string)($portUsage['status'] ?? 'active') : 'active';
            $clientName = is_array($portUsage) ? (string)($portUsage['client_name'] ?? '') : '';
            $companyName = is_array($portUsage) ? (string)($portUsage['company_name'] ?? '') : '';
            $refCode = is_array($portUsage) ? (string)($portUsage['ref_code'] ?? '') : '';
            $ticketId = is_array($portUsage) ? (int)($portUsage['ticket_id'] ?? 0) : 0;
            $reportId = is_array($portUsage) ? (int)($portUsage['report_id'] ?? 0) : 0;

            if (!is_array($portUsage) && $anonymousImportedOccupiedPorts > 0) {
                $fallbackClient = array_shift($fallbackFtthClients) ?: null;
                $status = 'occupied';
                $clientName = trim((string)($fallbackClient['client_name'] ?? ''));
                $companyName = trim((string)($fallbackClient['company_name'] ?? ''));
                $refCode = trim((string)($fallbackClient['ref_code'] ?? ''));
                $ticketId = (int)($fallbackClient['ticket_id'] ?? 0);
                $reportId = (int)($fallbackClient['report_id'] ?? 0);
                if ($clientName === '') {
                    $clientName = 'Brin occupe (referentiel FTTH)';
                }
                if ($refCode === '') {
                    $refCode = 'Maintenance FTTH';
                }
                $anonymousImportedOccupiedPorts--;
            }

            $ports[] = [
                'number' => $portNumber,
                'status' => $status,
                'label' => 'Port ' . $portNumber,
                'available' => $status === 'active',
                'client_name' => $clientName,
                'company_name' => $companyName,
                'ref_code' => $refCode,
                'ticket_id' => $ticketId,
                'report_id' => $reportId,
            ];
        }

        return $ports;
    }

    private static function findFallbackFtthClientsForBox(PDO $pdo, int $boxId, string $boxCode, ?int $currentReportId = null): array
    {
        $boxCode = trim($boxCode);
        $normalizedBoxCode = self::normalizePcoIdentifier($boxCode);
        $raccordementClause = self::raccordementTicketSqlClause('t');
        $params = [$boxId];
        $pcoClause = '';
        if ($normalizedBoxCode !== '') {
            $pcoClause = ' OR UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(t.pco_client, "")), " ", ""), "_", ""), "-", ""), "/", ""), ".", "")) = ?
                           OR UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(r.selected_otb_label, "")), " ", ""), "_", ""), "-", ""), "/", ""), ".", "")) = ?';
            $params[] = $normalizedBoxCode;
            $params[] = $normalizedBoxCode;
        }

        $stmt = $pdo->prepare(
            'SELECT
                r.id AS report_id,
                r.ticket_id,
                TRIM(COALESCE(NULLIF(r.client_name_onsite, ""), NULLIF(t.client_name, ""), "")) AS client_name,
                TRIM(COALESCE(t.company_name, "")) AS company_name,
                TRIM(COALESCE(t.ref_code, "")) AS ref_code
             FROM maintenance_ftth_reports r
             LEFT JOIN maintenance_ftth_tickets t ON t.id = r.ticket_id
             WHERE (r.selected_otb_id = ?' . $pcoClause . ')
                AND ' . $raccordementClause . '
                AND (
                                        (
                                                COALESCE(r.client_visit_status, "") = "intervention_effectuee"
                                                AND COALESCE(r.status, "") IN ("soumis", "validé")
                                        )
                                        OR COALESCE(r.selected_otb_port_status, "") = "occupied"
                )
               AND (r.selected_otb_port_number IS NULL OR r.selected_otb_port_number <= 0)
             ORDER BY r.updated_at DESC, r.id DESC'
        );
        $stmt->execute($params);

        $clients = [];
        $seenKeys = [];
        foreach (($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) {
            if ($currentReportId !== null && (int)($row['report_id'] ?? 0) === $currentReportId) {
                continue;
            }

            $clientName = trim((string)($row['client_name'] ?? ''));
            $companyName = trim((string)($row['company_name'] ?? ''));
            $refCode = trim((string)($row['ref_code'] ?? ''));
            $ticketId = (int)($row['ticket_id'] ?? 0);
            $reportId = (int)($row['report_id'] ?? 0);
            $dedupeKey = $ticketId . '|' . $reportId . '|' . $refCode . '|' . $clientName;
            if (isset($seenKeys[$dedupeKey])) {
                continue;
            }
            $seenKeys[$dedupeKey] = true;

            if ($clientName === '' && $companyName === '' && $refCode === '') {
                continue;
            }

            $clients[] = [
                'client_name' => $clientName,
                'company_name' => $companyName,
                'ref_code' => $refCode,
                'ticket_id' => $ticketId,
                'report_id' => $reportId,
            ];
        }

        if ($normalizedBoxCode !== '') {
            $ticketStmt = $pdo->prepare(
                'SELECT
                    0 AS report_id,
                    t.id AS ticket_id,
                    TRIM(COALESCE(NULLIF(t.client_name, ""), "")) AS client_name,
                    TRIM(COALESCE(t.company_name, "")) AS company_name,
                    TRIM(COALESCE(t.ref_code, "")) AS ref_code
                 FROM maintenance_ftth_tickets t
                 WHERE UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(t.pco_client, "")), " ", ""), "_", ""), "-", ""), "/", ""), ".", "")) = ?
                                     AND ' . $raccordementClause . '
                                     AND t.status IN (' . $pdo->quote('traité') . ', ' . $pdo->quote('validé') . ')
                 ORDER BY t.id DESC'
            );
            $ticketStmt->execute([$normalizedBoxCode]);

            foreach (($ticketStmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) {
                $clientName = trim((string)($row['client_name'] ?? ''));
                $companyName = trim((string)($row['company_name'] ?? ''));
                $refCode = trim((string)($row['ref_code'] ?? ''));
                $ticketId = (int)($row['ticket_id'] ?? 0);
                $reportId = 0;
                $dedupeKey = $ticketId . '|' . $reportId . '|' . $refCode . '|' . $clientName;
                if (isset($seenKeys[$dedupeKey])) {
                    continue;
                }
                $seenKeys[$dedupeKey] = true;

                if ($clientName === '' && $companyName === '' && $refCode === '') {
                    continue;
                }

                $clients[] = [
                    'client_name' => $clientName,
                    'company_name' => $companyName,
                    'ref_code' => $refCode,
                    'ticket_id' => $ticketId,
                    'report_id' => $reportId,
                ];
            }
        }

        return $clients;
    }

    private static function normalizePcoIdentifier(?string $value): string
    {
        $value = strtoupper(trim((string)$value));
        if ($value === '') {
            return '';
        }

        return str_replace([' ', '_', '-', '/', '.'], '', $value);
    }

    private static function raccordementTicketSqlClause(string $alias = 't'): string
    {
        $prefix = $alias !== '' ? $alias . '.' : '';

        return '(
            LOWER(COALESCE(' . $prefix . 'nature_intervention, "")) LIKE "%raccordement%"
            OR LOWER(COALESCE(' . $prefix . 'extra_fields, "")) LIKE "%numeroabonnement%"
            OR LOWER(COALESCE(' . $prefix . 'extra_fields, "")) LIKE "%equipe_raccordement%"
            OR LOWER(COALESCE(' . $prefix . 'extra_fields, "")) LIKE "%date_enregistrement_instances%"
            OR LOWER(COALESCE(' . $prefix . 'extra_fields, "")) LIKE "%date_envoi_instances_prestataires%"
            OR LOWER(COALESCE(' . $prefix . 'extra_fields, "")) LIKE "%snont%"
            OR LOWER(COALESCE(' . $prefix . 'extra_fields, "")) LIKE "%\"module\":\"raccordement-clients\"%"
        )';
    }

    public static function recomputeBoxOccupancy(PDO $pdo, int $boxId): void
    {
        if ($boxId <= 0) {
            return;
        }

        $stmt = $pdo->prepare(
              "SELECT COUNT(*)
             FROM maintenance_ftth_reports
             WHERE selected_otb_id = ?
                AND (
                    selected_otb_port_status = 'occupied'
                    OR (selected_otb_port_status = 'reserved' AND client_visit_status = 'intervention_effectuee' AND status IN ('soumis','validé'))
                )"
        );
        $stmt->execute([$boxId]);
        $occupiedPorts = (int)($stmt->fetchColumn() ?: 0);

        $boxStmt = $pdo->prepare('SELECT id, box_code, capacity_ports, occupied_ports FROM raccordement_otb_boxes WHERE id = ? LIMIT 1');
        $boxStmt->execute([$boxId]);
        $boxRow = $boxStmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $capacity = max(0, (int)($boxRow['capacity_ports'] ?? 0));
        $baseOccupiedPorts = self::resolveEffectiveBaseOccupiedPorts($pdo, $boxRow);
        $occupiedPorts = min($capacity, max($baseOccupiedPorts, max(0, $occupiedPorts)));

        $pdo->prepare('UPDATE raccordement_otb_boxes SET occupied_ports = ? WHERE id = ?')->execute([$occupiedPorts, $boxId]);
        self::evaluateUtilizationAlert($pdo, $boxId);
    }

    public static function resolveAlertRecipientIds(PDO $pdo, ?string $zoneKey = null): array
    {
        $recipientIds = [];

        $setting = self::getZoneSetting($pdo, $zoneKey);
        foreach (self::decodeIdList($setting['supervisor_ids'] ?? null) as $userId) {
            if ($userId > 0) {
                $recipientIds[$userId] = $userId;
            }
        }

        try {
            $stmt = $pdo->query("SELECT id
                FROM users
                WHERE active = 1
                  AND role_key IN ('agent','superviseur','supervisor')
                  AND technician_type = 'etude_raccordement'");
            foreach (($stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
                $userId = (int)($row['id'] ?? 0);
                if ($userId > 0) {
                    $recipientIds[$userId] = $userId;
                }
            }
        } catch (\Throwable $e) {
        }

        return array_values($recipientIds);
    }

    private static function decorateBox(array $box): array
    {
        $capacity = max(0, (int)($box['capacity_ports'] ?? 0));
        $occupied = max(0, (int)($box['occupied_ports'] ?? 0));
        $portsAvailable = max(0, $capacity - $occupied);
        $threshold = max(1, min(100, (int)($box['alert_threshold_pct'] ?? self::DEFAULT_ALERT_THRESHOLD_PCT)));

        $box['zone_key'] = self::normalizeZoneKey((string)($box['zone_key'] ?? ''));
        $box['capacity_ports'] = $capacity;
        $box['occupied_ports'] = $occupied;
        $box['ports_available'] = $portsAvailable;
        $box['occupancy_rate'] = $capacity > 0 ? round(($occupied / $capacity) * 100, 1) : 0.0;
        $box['alert_threshold_pct'] = $threshold;
        $box['utilization_alert_state'] = (string)($box['utilization_alert_state'] ?? 'normal');
        $box['is_alert'] = $capacity > 0 && (float)($box['occupancy_rate'] ?? 0) >= $threshold;

        return $box;
    }

    private static function decorateBoxWithRealtimeStats(PDO $pdo, array $box): array
    {
        $box = self::decorateBox($box);
        $boxId = (int)($box['id'] ?? 0);
        if ($boxId <= 0) {
            return $box;
        }

        $ports = self::getAvailablePorts($pdo, $boxId);
        if ($ports === []) {
            $box['reserved_ports'] = 0;
            $box['used_ports'] = (int)($box['occupied_ports'] ?? 0);
            return $box;
        }

        $reservedPorts = 0;
        $occupiedPorts = 0;
        foreach ($ports as $port) {
            $status = (string)($port['status'] ?? 'active');
            if ($status === 'reserved') {
                $reservedPorts++;
                continue;
            }
            if ($status === 'occupied') {
                $occupiedPorts++;
            }
        }

        $capacity = max(0, (int)($box['capacity_ports'] ?? 0));
        $usedPorts = $reservedPorts + $occupiedPorts;
        $box['reserved_ports'] = $reservedPorts;
        $box['occupied_ports'] = $occupiedPorts;
        $box['used_ports'] = $usedPorts;
        $box['ports_available'] = max(0, $capacity - $usedPorts);
        $box['occupancy_rate'] = $capacity > 0 ? round(($usedPorts / $capacity) * 100, 1) : 0.0;
        $box['is_alert'] = $capacity > 0 && (float)($box['occupancy_rate'] ?? 0) >= (int)($box['alert_threshold_pct'] ?? self::DEFAULT_ALERT_THRESHOLD_PCT);

        return $box;
    }

    private static function haversineMeters(float $lat1, float $lng1, float $lat2, float $lng2): float
    {
        $earthRadius = 6371000.0;
        $dLat = deg2rad($lat2 - $lat1);
        $dLng = deg2rad($lng2 - $lng1);

        $a = sin($dLat / 2) ** 2
            + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * sin($dLng / 2) ** 2;

        return 2 * $earthRadius * asin(min(1, sqrt($a)));
    }

    private static function formatDistance(float $distanceMeters): string
    {
        if ($distanceMeters >= 1000) {
            return number_format($distanceMeters / 1000, 2, ',', ' ') . ' km';
        }

        return number_format($distanceMeters, 0, ',', ' ') . ' m';
    }

    /**
     * Retourne toutes les boîtes actives, triées par distance croissante si coordonnées fournies.
     * Utilisé pour la recherche manuelle en phase étude mobile.
     *
     * @param PDO    $pdo
     * @param float|null $clientLat  Latitude du client (optionnelle)
     * @param float|null $clientLng  Longitude du client (optionnelle)
     * @param int    $limit          Nombre maximum de résultats
     *
     * @return array
     */
    public static function getAllBoxesSortedByDistance(PDO $pdo, ?float $clientLat, ?float $clientLng, int $limit = 60): array
    {
        $stmt = $pdo->query("SELECT id, box_code, latitude, longitude, capacity_ports, zone_key FROM raccordement_otb_boxes WHERE status = 'active' ORDER BY box_code ASC");
        $rows = $stmt ? self::decorateBoxesWithRealtimeStats($pdo, $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) : [];

        foreach ($rows as &$row) {
            if ($clientLat !== null && $clientLng !== null && is_numeric($row['latitude'] ?? null) && is_numeric($row['longitude'] ?? null)) {
                $dist = self::haversineMeters($clientLat, $clientLng, (float)$row['latitude'], (float)$row['longitude']);
                $row['distance_meters'] = round($dist, 2);
                $row['distance_label']  = self::formatDistance($dist);
            } else {
                $row['distance_meters'] = null;
                $row['distance_label']  = 'Distance inconnue';
            }
        }
        unset($row);

        usort($rows, static function (array $a, array $b): int {
            $da = $a['distance_meters'] ?? PHP_INT_MAX;
            $db = $b['distance_meters'] ?? PHP_INT_MAX;
            return $da <=> $db;
        });

        return array_slice($rows, 0, $limit);
    }
}