<?php
namespace App\Controllers;

use App\Core\Auth;
use App\Core\Controller;
use App\Core\Database;
use App\Core\ModuleManager;
use App\Core\RaccordementOtbService;
use App\Core\XlsxReader;
use DateTimeImmutable;
use PDO;

class OtbController extends Controller
{
    private PDO $pdo;

    public function __construct()
    {
        $this->pdo = Database::pdo();

        try {
            ModuleManager::ensureSchema($this->pdo);
        } catch (\Throwable $e) {
            error_log('[OTB] ModuleManager::ensureSchema failed: ' . $e->getMessage());
        }

        try {
            RaccordementOtbService::ensureSchema($this->pdo);
        } catch (\Throwable $e) {
            error_log('[OTB] RaccordementOtbService::ensureSchema failed: ' . $e->getMessage());
        }

        if (Auth::check()) {
            try {
                $module = ModuleManager::find($this->pdo, 'otb_management');
                if (!empty($module) && empty($module['accessible'])) {
                    http_response_code(403);
                    $this->view('settings/module_blocked', ModuleManager::buildBlockedViewData($module));
                    exit;
                }
            } catch (\Throwable $e) {
                error_log('[OTB] ModuleManager::find failed: ' . $e->getMessage());
            }
        }
    }

    private function tableExists(string $tableName): bool
    {
        try {
            $stmt = $this->pdo->prepare('SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? LIMIT 1');
            $stmt->execute([$tableName]);
            return (bool)$stmt->fetchColumn();
        } catch (\Throwable $e) {
            return false;
        }
    }

    private function columnExists(string $tableName, string $columnName): bool
    {
        try {
            $stmt = $this->pdo->prepare('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1');
            $stmt->execute([$tableName, $columnName]);
            return (bool)$stmt->fetchColumn();
        } catch (\Throwable $e) {
            return false;
        }
    }

    private function defaultSummary(): array
    {
        return [
            'total_boxes' => 0,
            'active_boxes' => 0,
            'total_capacity' => 0,
            'occupancy_rate' => 0,
            'alert_boxes' => 0,
            'saturated_boxes' => 0,
            'avg_alert_threshold_pct' => RaccordementOtbService::defaultAlertThresholdPct(),
        ];
    }

    private function safeFetchAll(string $sql, array $params = []): array
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    public function index(): void
    {
        if (!Auth::check()) {
            $this->redirect('/login');
        }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);

        $search = trim((string)($_GET['q'] ?? ''));
        $selectedZones = array_values(array_unique(array_filter(array_map(static fn($value): string => trim((string)$value), (array)($_GET['zone'] ?? [])), static fn(string $value): bool => $value !== '')));
        $perPageOptions = [25, 50, 100, 200];
        $perPage = (int)($_GET['per_page'] ?? 50);
        if (!in_array($perPage, $perPageOptions, true)) {
            $perPage = 50;
        }

        $hasBoxesTable = $this->tableExists('raccordement_otb_boxes');
        $hasZonesTable = $this->tableExists('raccordement_zone_settings');
        $hasUsersTechnicianType = $this->columnExists('users', 'technician_type');

        [$whereSql, $whereParams] = $this->buildBoxFilterSql($search, $selectedZones);

        $totalItems = 0;
        if ($hasBoxesTable) {
            try {
                $countStmt = $this->pdo->prepare('SELECT COUNT(*) FROM raccordement_otb_boxes' . $whereSql);
                $countStmt->execute($whereParams);
                $totalItems = (int)$countStmt->fetchColumn();
            } catch (\Throwable $e) {
                $totalItems = 0;
            }
        }
        $totalPages = max(1, (int)ceil($totalItems / $perPage));
        $currentPage = max(1, (int)($_GET['page'] ?? 1));
        if ($currentPage > $totalPages) {
            $currentPage = $totalPages;
        }
        $offset = max(0, ($currentPage - 1) * $perPage);

        $pageRows = [];
        if ($hasBoxesTable) {
            try {
                $pageStmt = $this->pdo->prepare('SELECT * FROM raccordement_otb_boxes' . $whereSql . ' ORDER BY zone_label ASC, box_code ASC LIMIT ' . $perPage . ' OFFSET ' . $offset);
                $pageStmt->execute($whereParams);
                $pageRows = $pageStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            } catch (\Throwable $e) {
                $pageRows = [];
            }
        }

        try {
            $boxes = $hasBoxesTable ? RaccordementOtbService::decorateBoxesWithRealtimeStats($this->pdo, $pageRows) : [];
        } catch (\Throwable $e) {
            $boxes = $pageRows;
        }

        $zones = $hasZonesTable
            ? $this->safeFetchAll('SELECT * FROM raccordement_zone_settings ORDER BY zone_label ASC')
            : [];
        $technicians = $this->safeFetchAll("SELECT id, name, email FROM users WHERE active = 1 AND role_key IN ('technicien','technician') ORDER BY name ASC");

        $supervisorSql = "SELECT id, name, email FROM users WHERE active = 1 AND role_key IN ('agent','superviseur','supervisor')";
        if ($hasUsersTechnicianType) {
            $supervisorSql .= " AND technician_type = 'etude_raccordement'";
        }
        $supervisorSql .= ' ORDER BY name ASC';
        $supervisors = $this->safeFetchAll($supervisorSql);

        try {
            $summary = $hasBoxesTable ? RaccordementOtbService::summarize($this->pdo) : $this->defaultSummary();
        } catch (\Throwable $e) {
            $summary = $this->defaultSummary();
        }

        $selectedBoxId = (int)($_GET['view_box'] ?? 0);
        $stageBoxId = $selectedBoxId;
        if ($stageBoxId <= 0) {
            $stageBoxId = (int)($boxes[0]['id'] ?? 0);
        }
        try {
            $stageBox = ($hasBoxesTable && $stageBoxId > 0) ? RaccordementOtbService::getBoxRealtimeSnapshot($this->pdo, $stageBoxId) : null;
        } catch (\Throwable $e) {
            $stageBox = null;
        }

        foreach ($zones as &$zone) {
            $zone['technician_ids'] = RaccordementOtbService::decodeIdList($zone['technician_ids'] ?? null);
            $zone['supervisor_ids'] = RaccordementOtbService::decodeIdList($zone['supervisor_ids'] ?? null);
        }
        unset($zone);

        try {
            $zonePreviewStats = ($hasBoxesTable && $hasZonesTable) ? $this->buildZonePreviewStats() : [];
        } catch (\Throwable $e) {
            error_log('[OTB] buildZonePreviewStats failed: ' . $e->getMessage());
            $zonePreviewStats = [];
        }

        $this->view('otb/index', [
            'title' => 'Gestion des boîtiers OTB',
            'boxes' => $boxes,
            'zones' => $zones,
            'technicians' => $technicians,
            'supervisors' => $supervisors,
            'summary' => $summary,
            'zonePreviewStats' => $zonePreviewStats,
            'filters' => [
                'q' => $search,
                'zones' => $selectedZones,
                'per_page' => $perPage,
            ],
            'pagination' => [
                'current_page' => $currentPage,
                'per_page' => $perPage,
                'per_page_options' => $perPageOptions,
                'total_items' => $totalItems,
                'total_pages' => $totalPages,
                'offset' => $offset,
            ],
            'stageBox' => $stageBox,
        ]);
    }

    public function boxDetail(): void
    {
        if (!Auth::check()) {
            http_response_code(401);
            header('Content-Type: application/json; charset=utf-8');
            echo json_encode(['error' => 'unauthorized'], JSON_UNESCAPED_UNICODE);
            return;
        }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);

        $boxId = (int)($_GET['id'] ?? 0);
        try {
            $box = ($this->tableExists('raccordement_otb_boxes') && $boxId > 0)
                ? RaccordementOtbService::getBoxRealtimeSnapshot($this->pdo, $boxId)
                : null;
        } catch (\Throwable $e) {
            $box = null;
        }

        header('Content-Type: application/json; charset=utf-8');
        if ($box === null) {
            http_response_code(404);
            echo json_encode(['error' => 'not_found'], JSON_UNESCAPED_UNICODE);
            return;
        }

        echo json_encode(['box' => $box], JSON_UNESCAPED_UNICODE);
    }

    public function saveBox(): void
    {
        if (!Auth::check()) {
            $this->redirect('/login');
        }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);

        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect('/otb-management');
            return;
        }

        $boxId = (int)($_POST['box_id'] ?? 0);
        $boxCode = $this->normalizeBoxCode($_POST['box_code'] ?? '');
        $cityName = trim((string)($_POST['city_name'] ?? ''));
        $oltName = trim((string)($_POST['olt_name'] ?? ''));
        $slotPort = trim((string)($_POST['slot_port'] ?? ''));
        $sectorCode = trim((string)($_POST['sector_code'] ?? ''));
        $plaque = trim((string)($_POST['plaque'] ?? ''));
        $hub = trim((string)($_POST['hub'] ?? ''));
        $zoneLabel = $this->buildImportedZoneLabel($plaque, $hub, $cityName, $sectorCode);
        $zoneKey = RaccordementOtbService::normalizeZoneKey($zoneLabel);
        $locationDetails = trim((string)($_POST['location_details'] ?? ''));
        $latitude = is_numeric($_POST['latitude'] ?? null) ? (float)$_POST['latitude'] : null;
        $longitude = is_numeric($_POST['longitude'] ?? null) ? (float)$_POST['longitude'] : null;
        $occupied = max(0, (int)($_POST['occupied_ports'] ?? 0));
        $freePorts = max(0, (int)($_POST['free_ports'] ?? 0));
        $capacity = $occupied + $freePorts;
        $deploymentDate = $this->normalizeDateInput($_POST['deployment_date'] ?? null);
        $saturationLabel = trim((string)($_POST['saturation_label'] ?? ''));
        $serviceProvider = trim((string)($_POST['service_provider'] ?? ''));
        $status = stripos($saturationLabel, 'hors service') !== false ? 'inactive' : 'active';
        $notesParts = array_filter([
            $locationDetails !== '' ? 'Localisation: ' . $locationDetails : null,
            $cityName !== '' ? 'Ville: ' . $cityName : null,
            $oltName !== '' ? 'OLT: ' . $oltName : null,
            $slotPort !== '' ? 'SLOT/PORT: ' . $slotPort : null,
        ]);
        $notes = $notesParts !== [] ? implode(' | ', $notesParts) : '';

        if ($boxCode === '' || $latitude === null || $longitude === null) {
            $this->redirect('/otb-management?err=missing_box');
            return;
        }

        if ($capacity <= 0) {
            $capacity = 8;
        }
        $occupied = min($occupied, $capacity);
        $alertThreshold = RaccordementOtbService::defaultAlertThresholdPct();

        $existingBoxId = $this->findExistingBoxIdByCode($boxCode, $boxId > 0 ? $boxId : null);
        if ($boxId <= 0 && $existingBoxId !== null) {
            $boxId = $existingBoxId;
        }

        if ($boxId > 0) {
            $stmt = $this->pdo->prepare('UPDATE raccordement_otb_boxes
                SET box_code = ?, city_name = ?, olt_name = ?, slot_port = ?, sector_code = ?, plaque = ?, hub = ?, zone_key = ?, zone_label = ?, location_details = ?, latitude = ?, longitude = ?, capacity_ports = ?, occupied_ports = ?, deployment_date = ?, saturation_label = ?, service_provider = ?, alert_threshold_pct = ?, status = ?, notes = ?
                WHERE id = ?');
            $stmt->execute([$boxCode, $cityName ?: null, $oltName ?: null, $slotPort ?: null, $sectorCode ?: null, $plaque ?: null, $hub ?: null, $zoneKey ?: null, $zoneLabel ?: null, $locationDetails ?: null, $latitude, $longitude, $capacity, $occupied, $deploymentDate, $saturationLabel ?: null, $serviceProvider ?: null, $alertThreshold, $status, $notes ?: null, $boxId]);
        } else {
            $stmt = $this->pdo->prepare('INSERT INTO raccordement_otb_boxes
                (box_code, city_name, olt_name, slot_port, sector_code, plaque, hub, zone_key, zone_label, location_details, latitude, longitude, capacity_ports, occupied_ports, deployment_date, saturation_label, service_provider, alert_threshold_pct, status, notes)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
            $stmt->execute([$boxCode, $cityName ?: null, $oltName ?: null, $slotPort ?: null, $sectorCode ?: null, $plaque ?: null, $hub ?: null, $zoneKey ?: null, $zoneLabel ?: null, $locationDetails ?: null, $latitude, $longitude, $capacity, $occupied, $deploymentDate, $saturationLabel ?: null, $serviceProvider ?: null, $alertThreshold, $status, $notes ?: null]);
            $boxId = (int)$this->pdo->lastInsertId();
        }

        $this->upsertZoneSettingIfNeeded($zoneKey, $zoneLabel, $plaque, $hub);

        if ($boxId > 0) {
            RaccordementOtbService::evaluateUtilizationAlert($this->pdo, $boxId);
        }

        $this->redirect('/otb-management?saved_box=1');
    }

    public function importExcel(): void
    {
        if (!Auth::check()) {
            $this->redirect('/login');
        }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);
        @set_time_limit(0);

        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect('/otb-management');
            return;
        }

        $uploadedFile = $_FILES['excel_file'] ?? null;
        if (!is_array($uploadedFile) || (int)($uploadedFile['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
            $this->redirect('/otb-management?err=import_missing_file');
            return;
        }

        $originalName = trim((string)($uploadedFile['name'] ?? 'import_otb.xlsx'));
        $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
        if ($extension !== 'xlsx') {
            $this->redirect('/otb-management?err=import_invalid_format');
            return;
        }

        try {
            $rows = XlsxReader::readSheet((string)$uploadedFile['tmp_name']);
        } catch (\Throwable $e) {
            $this->redirect('/otb-management?err=import_parse');
            return;
        }

        if (count($rows) < 2) {
            $this->redirect('/otb-management?err=import_empty');
            return;
        }

        $existingLookup = $this->buildExistingBoxIdMap();

        $created = 0;
        $updated = 0;
        $skipped = 0;
        $importedCodes = [];
        $zoneCache = [];
        $fileSeenCodes = [];

        $updateStmt = $this->pdo->prepare('UPDATE raccordement_otb_boxes
            SET box_code = ?, city_name = ?, olt_name = ?, slot_port = ?, sector_code = ?, plaque = ?, hub = ?, zone_key = ?, zone_label = ?, location_details = ?, latitude = ?, longitude = ?, capacity_ports = ?, occupied_ports = ?, deployment_date = ?, saturation_label = ?, service_provider = ?, source_file_name = ?, source_imported_at = NOW(), alert_threshold_pct = ?, status = ?, notes = ?
            WHERE id = ?');

        $insertStmt = $this->pdo->prepare('INSERT INTO raccordement_otb_boxes
            (box_code, city_name, olt_name, slot_port, sector_code, plaque, hub, zone_key, zone_label, location_details, latitude, longitude, capacity_ports, occupied_ports, deployment_date, saturation_label, service_provider, source_file_name, source_imported_at, alert_threshold_pct, status, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?)');

        $this->pdo->beginTransaction();
        try {
            foreach (array_slice($rows, 1) as $row) {
                $payload = $this->mapImportedBoxRow(is_array($row) ? $row : [], $originalName);
                if ($payload === null) {
                    $skipped++;
                    continue;
                }

                $normalizedBoxCode = $this->normalizeBoxCode($payload['box_code'] ?? '');
                if ($normalizedBoxCode === '') {
                    $skipped++;
                    continue;
                }
                $payload['box_code'] = $normalizedBoxCode;

                $targetBoxId = $fileSeenCodes[$normalizedBoxCode] ?? $existingLookup[$normalizedBoxCode] ?? null;

                if ($targetBoxId !== null) {
                    $updateStmt->execute([
                        $payload['box_code'],
                        $payload['city_name'],
                        $payload['olt_name'],
                        $payload['slot_port'],
                        $payload['sector_code'],
                        $payload['plaque'],
                        $payload['hub'],
                        $payload['zone_key'],
                        $payload['zone_label'],
                        $payload['location_details'],
                        $payload['latitude'],
                        $payload['longitude'],
                        $payload['capacity_ports'],
                        $payload['occupied_ports'],
                        $payload['deployment_date'],
                        $payload['saturation_label'],
                        $payload['service_provider'],
                        $payload['source_file_name'],
                        $payload['alert_threshold_pct'],
                        $payload['status'],
                        $payload['notes'],
                        $targetBoxId,
                    ]);
                    $updated++;
                } else {
                    $insertStmt->execute([
                        $payload['box_code'],
                        $payload['city_name'],
                        $payload['olt_name'],
                        $payload['slot_port'],
                        $payload['sector_code'],
                        $payload['plaque'],
                        $payload['hub'],
                        $payload['zone_key'],
                        $payload['zone_label'],
                        $payload['location_details'],
                        $payload['latitude'],
                        $payload['longitude'],
                        $payload['capacity_ports'],
                        $payload['occupied_ports'],
                        $payload['deployment_date'],
                        $payload['saturation_label'],
                        $payload['service_provider'],
                        $payload['source_file_name'],
                        $payload['alert_threshold_pct'],
                        $payload['status'],
                        $payload['notes'],
                    ]);
                    $targetBoxId = (int)$this->pdo->lastInsertId();
                    $created++;
                }

                $existingLookup[$normalizedBoxCode] = $targetBoxId;
                $fileSeenCodes[$normalizedBoxCode] = $targetBoxId;
                $importedCodes[] = $normalizedBoxCode;
                $zoneSignature = $payload['zone_key'] . '|' . $payload['zone_label'];
                if ($payload['zone_key'] !== '' && !isset($zoneCache[$zoneSignature])) {
                    $this->upsertZoneSettingIfNeeded($payload['zone_key'], $payload['zone_label'], $payload['plaque'], $payload['hub']);
                    $zoneCache[$zoneSignature] = true;
                }
            }

            $this->pdo->commit();
            unset($_SESSION['otb_import_error']);
        } catch (\Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            $_SESSION['otb_import_error'] = $e->getMessage();
            error_log('[OTB import] ' . $e->getMessage());
            $this->redirect('/otb-management?err=import_failed');
            return;
        }

        $importedCodes = array_values(array_unique(array_filter($importedCodes)));
        if ($importedCodes !== []) {
            foreach (array_chunk($importedCodes, 500) as $codesBatch) {
                $placeholders = implode(',', array_fill(0, count($codesBatch), '?'));
                $stmt = $this->pdo->prepare("SELECT id FROM raccordement_otb_boxes WHERE box_code IN ($placeholders)");
                $stmt->execute($codesBatch);
                foreach (($stmt->fetchAll(PDO::FETCH_COLUMN) ?: []) as $boxId) {
                    RaccordementOtbService::evaluateUtilizationAlert($this->pdo, (int)$boxId);
                }
            }
        }

        $this->redirect('/otb-management?imported=1&created=' . $created . '&updated=' . $updated . '&skipped=' . $skipped);
    }

    public function deleteBox(): void
    {
        if (!Auth::check()) {
            $this->redirect('/login');
        }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);

        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect('/otb-management');
            return;
        }

        $boxId = (int)($_POST['box_id'] ?? 0);
        if ($boxId > 0) {
            $this->pdo->prepare('DELETE FROM raccordement_otb_boxes WHERE id = ?')->execute([$boxId]);
        }

        $this->redirect('/otb-management?deleted_box=1');
    }

    public function saveZone(): void
    {
        if (!Auth::check()) {
            $this->redirect('/login');
        }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);

        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect('/otb-management');
            return;
        }

        $zoneId = (int)($_POST['zone_id'] ?? 0);
        $zoneLabel = trim((string)($_POST['zone_label'] ?? ''));
        $zoneKey = RaccordementOtbService::normalizeZoneKey($_POST['zone_key'] ?? $zoneLabel);
        $plaque = trim((string)($_POST['plaque'] ?? ''));
        $hub = trim((string)($_POST['hub'] ?? ''));
        $radius = max(100, (int)($_POST['default_radius_meters'] ?? 1000));
        $technicianIds = json_encode(array_values(array_unique(array_map('intval', $_POST['technician_ids'] ?? []))), JSON_UNESCAPED_UNICODE);
        $supervisorIds = json_encode(array_values(array_unique(array_map('intval', $_POST['supervisor_ids'] ?? []))), JSON_UNESCAPED_UNICODE);

        if ($zoneLabel === '' || $zoneKey === '') {
            $this->redirect('/otb-management?err=missing_zone');
            return;
        }

        if ($zoneId > 0) {
            $stmt = $this->pdo->prepare('UPDATE raccordement_zone_settings
                SET zone_key = ?, zone_label = ?, plaque = ?, hub = ?, default_radius_meters = ?, technician_ids = ?, supervisor_ids = ?
                WHERE id = ?');
            $stmt->execute([$zoneKey, $zoneLabel, $plaque ?: null, $hub ?: null, $radius, $technicianIds, $supervisorIds, $zoneId]);
        } else {
            $stmt = $this->pdo->prepare('INSERT INTO raccordement_zone_settings
                (zone_key, zone_label, plaque, hub, default_radius_meters, technician_ids, supervisor_ids)
                VALUES (?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE zone_label = VALUES(zone_label), plaque = VALUES(plaque), hub = VALUES(hub), default_radius_meters = VALUES(default_radius_meters), technician_ids = VALUES(technician_ids), supervisor_ids = VALUES(supervisor_ids)');
            $stmt->execute([$zoneKey, $zoneLabel, $plaque ?: null, $hub ?: null, $radius, $technicianIds, $supervisorIds]);
        }

        $this->redirect('/otb-management?saved_zone=1');
    }

    private function mapImportedBoxRow(array $row, string $sourceFileName): ?array
    {
        $boxCode = $this->normalizeBoxCode($row[3] ?? '');
        $latitude = $this->toNullableFloat($row[8] ?? null);
        $longitude = $this->toNullableFloat($row[9] ?? null);
        if ($boxCode === '' || $latitude === null || $longitude === null) {
            return null;
        }

        $cityName = trim((string)($row[0] ?? ''));
        $oltName = trim((string)($row[1] ?? ''));
        $slotPort = trim((string)($row[2] ?? ''));
        $sectorCode = trim((string)($row[4] ?? ''));
        $plaque = trim((string)($row[5] ?? ''));
        $hub = trim((string)($row[6] ?? ''));
        $locationDetails = trim((string)($row[7] ?? ''));
        $occupiedPorts = max(0, $this->toInt($row[10] ?? null));
        $freePorts = max(0, $this->toInt($row[11] ?? null));
        $capacityPorts = $occupiedPorts + $freePorts;
        if ($capacityPorts <= 0) {
            $capacityPorts = 8;
        }
        $occupiedPorts = min($occupiedPorts, $capacityPorts);

        $zoneLabel = $this->buildImportedZoneLabel($plaque, $hub, $cityName, $sectorCode);
        $zoneKey = RaccordementOtbService::normalizeZoneKey($zoneLabel);
        $deploymentDate = $this->parseExcelDateValue($row[12] ?? null);
        $saturationLabel = trim((string)($row[13] ?? ''));
        $serviceProvider = trim((string)($row[14] ?? ''));
        $status = stripos($saturationLabel, 'hors service') !== false ? 'inactive' : 'active';
        $notesParts = array_filter([
            $locationDetails !== '' ? 'Localisation: ' . $locationDetails : null,
            $cityName !== '' ? 'Ville: ' . $cityName : null,
            $oltName !== '' ? 'OLT: ' . $oltName : null,
            $slotPort !== '' ? 'SLOT/PORT: ' . $slotPort : null,
        ]);

        return [
            'box_code' => $boxCode,
            'city_name' => $cityName !== '' ? $cityName : null,
            'olt_name' => $oltName !== '' ? $oltName : null,
            'slot_port' => $slotPort !== '' ? $slotPort : null,
            'sector_code' => $sectorCode !== '' ? $sectorCode : null,
            'plaque' => $plaque !== '' ? $plaque : null,
            'hub' => $hub !== '' ? $hub : null,
            'zone_key' => $zoneKey,
            'zone_label' => $zoneLabel !== '' ? $zoneLabel : $boxCode,
            'location_details' => $locationDetails !== '' ? $locationDetails : null,
            'latitude' => $latitude,
            'longitude' => $longitude,
            'capacity_ports' => $capacityPorts,
            'occupied_ports' => $occupiedPorts,
            'deployment_date' => $deploymentDate,
            'saturation_label' => $saturationLabel !== '' ? $saturationLabel : null,
            'service_provider' => $serviceProvider !== '' ? $serviceProvider : null,
            'source_file_name' => $sourceFileName,
            'alert_threshold_pct' => RaccordementOtbService::defaultAlertThresholdPct(),
            'status' => $status,
            'notes' => $notesParts !== [] ? implode(' | ', $notesParts) : null,
        ];
    }

    private function buildImportedZoneLabel(string $plaque, string $hub, string $cityName, string $sectorCode): string
    {
        $parts = array_values(array_filter([$plaque, $hub, $cityName !== '' && $plaque === '' ? $cityName : '', $sectorCode !== '' && $plaque === '' ? $sectorCode : ''], static fn(string $value): bool => trim($value) !== ''));
        return implode(' / ', $parts);
    }

    private function buildBoxFilterSql(string $search, array $selectedZones): array
    {
        $clauses = [];
        $params = [];

        if ($search !== '') {
            $clauses[] = '(box_code LIKE ? OR plaque LIKE ? OR hub LIKE ? OR city_name LIKE ? OR olt_name LIKE ? OR slot_port LIKE ? OR sector_code LIKE ?)';
            $searchTerm = '%' . $search . '%';
            for ($index = 0; $index < 7; $index++) {
                $params[] = $searchTerm;
            }
        }

        if ($selectedZones !== []) {
            $zoneClauses = [];
            foreach ($selectedZones as $zoneKey) {
                if ($zoneKey === 'hors_zone') {
                    $zoneClauses[] = "(TRIM(COALESCE(zone_key, '')) = '' AND TRIM(COALESCE(zone_label, '')) = '')";
                    continue;
                }

                $zoneClauses[] = "(zone_key = ? OR (TRIM(COALESCE(zone_key, '')) = '' AND COALESCE(NULLIF(TRIM(zone_label), ''), 'hors_zone') = ?))";
                $params[] = $zoneKey;
                $params[] = $zoneKey;
            }

            if ($zoneClauses !== []) {
                $clauses[] = '(' . implode(' OR ', $zoneClauses) . ')';
            }
        }

        return [$clauses !== [] ? ' WHERE ' . implode(' AND ', $clauses) : '', $params];
    }

    private function buildZonePreviewStats(): array
    {
        try {
            $rows = $this->pdo->query('SELECT b.zone_key, b.zone_label, COUNT(*) AS total_boxes, MAX(COALESCE(z.default_radius_meters, 0)) AS radius
                FROM raccordement_otb_boxes b
                LEFT JOIN raccordement_zone_settings z ON z.zone_key = b.zone_key
                GROUP BY b.zone_key, b.zone_label
                ORDER BY b.zone_label ASC, b.zone_key ASC')->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            return [];
        }

        $stats = [];
        foreach ($rows as $row) {
            $zoneKey = trim((string)($row['zone_key'] ?? ''));
            $zoneLabel = trim((string)($row['zone_label'] ?? ''));
            $indexKey = $zoneKey !== '' ? $zoneKey : ($zoneLabel !== '' ? $zoneLabel : 'hors_zone');
            if (!isset($stats[$indexKey])) {
                $stats[$indexKey] = [
                    'label' => $zoneLabel !== '' ? $zoneLabel : 'Zone libre',
                    'count' => 0,
                    'radius' => (int)($row['radius'] ?? 0),
                ];
            }
            $stats[$indexKey]['count'] += (int)($row['total_boxes'] ?? 0);
            $stats[$indexKey]['radius'] = max($stats[$indexKey]['radius'], (int)($row['radius'] ?? 0));
        }

        return $stats;
    }

    private function normalizeBoxCode($value): string
    {
        $normalized = trim((string)$value);
        if ($normalized === '') {
            return '';
        }

        $normalized = preg_replace('/\s+/', '', $normalized) ?? $normalized;
        $normalized = str_replace(['–', '—'], '-', $normalized);

        return strtoupper($normalized);
    }

    private function buildExistingBoxIdMap(): array
    {
        $rows = $this->pdo->query('SELECT id, box_code FROM raccordement_otb_boxes ORDER BY id ASC')->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $map = [];
        foreach ($rows as $row) {
            $boxId = (int)($row['id'] ?? 0);
            $normalized = $this->normalizeBoxCode($row['box_code'] ?? '');
            if ($boxId > 0 && $normalized !== '' && !isset($map[$normalized])) {
                $map[$normalized] = $boxId;
            }
        }

        return $map;
    }

    private function findExistingBoxIdByCode(string $boxCode, ?int $excludeId = null): ?int
    {
        $normalizedTarget = $this->normalizeBoxCode($boxCode);
        if ($normalizedTarget === '') {
            return null;
        }

        foreach ($this->buildExistingBoxIdMap() as $normalizedCode => $boxId) {
            if ($normalizedCode !== $normalizedTarget) {
                continue;
            }
            if ($excludeId !== null && $boxId === $excludeId) {
                continue;
            }
            return $boxId;
        }

        return null;
    }

    private function upsertZoneSettingIfNeeded(string $zoneKey, string $zoneLabel, ?string $plaque, ?string $hub): void
    {
        $plaque = trim((string)$plaque);
        $hub = trim((string)$hub);

        if ($zoneKey === '' || $zoneLabel === '') {
            return;
        }

        $stmt = $this->pdo->prepare('INSERT INTO raccordement_zone_settings (zone_key, zone_label, plaque, hub, default_radius_meters)
            VALUES (?, ?, ?, ?, 1000)
            ON DUPLICATE KEY UPDATE zone_label = VALUES(zone_label), plaque = VALUES(plaque), hub = VALUES(hub)');
        $stmt->execute([$zoneKey, $zoneLabel, $plaque !== '' ? $plaque : null, $hub !== '' ? $hub : null]);
    }

    private function toNullableFloat($value): ?float
    {
        $normalized = trim((string)$value);
        if ($normalized === '') {
            return null;
        }

        $normalized = str_replace(',', '.', $normalized);
        return is_numeric($normalized) ? (float)$normalized : null;
    }

    private function toInt($value): int
    {
        $normalized = trim((string)$value);
        if ($normalized === '') {
            return 0;
        }

        $normalized = str_replace(',', '.', $normalized);
        return is_numeric($normalized) ? (int)round((float)$normalized) : 0;
    }

    private function parseExcelDateValue($value): ?string
    {
        $normalized = trim((string)$value);
        if ($normalized === '') {
            return null;
        }

        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $normalized)) {
            return $normalized;
        }

        if (preg_match('/^\d{1,2}\/\d{1,2}\/\d{4}$/', $normalized)) {
            $date = DateTimeImmutable::createFromFormat('d/m/Y', $normalized);
            return $date ? $date->format('Y-m-d') : null;
        }

        if (is_numeric($normalized)) {
            $excelSerial = (int)round((float)$normalized);
            if ($excelSerial > 0) {
                $date = (new DateTimeImmutable('1899-12-30'))->modify('+' . $excelSerial . ' days');
                return $date->format('Y-m-d');
            }
        }

        return null;
    }

    private function normalizeDateInput($value): ?string
    {
        $normalized = trim((string)$value);
        if ($normalized === '') {
            return null;
        }

        return preg_match('/^\d{4}-\d{2}-\d{2}$/', $normalized) ? $normalized : null;
    }
}