<?php

namespace App\Controllers\Concerns;

use App\Core\Notifier;
use App\Core\PDF;
use App\Core\RaccordementOtbService;
use App\Core\XlsxReader;
use PDO;

trait HandlesRaccordementClients
{
    protected function logRaccordementRuntime(string $event, array $context = []): void
    {
        try {
            error_log('[raccordement-runtime] ' . $event . ' ' . json_encode($context, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
        } catch (\Throwable $exception) {
            error_log('[raccordement-runtime] ' . $event);
        }
    }

    protected function ensureRaccordementCompatibilityView(): void
    {
        try {
            $this->pdo->exec(
                'CREATE OR REPLACE VIEW raccordement_clients AS
                 SELECT t.*
                 FROM maintenance_ftth_tickets t
                 WHERE ' . $this->raccordementModuleWhereClause('t')
            );
        } catch (\Throwable $e) {
            // L''application continue de fonctionner avec maintenance_ftth_tickets
            // si la base en ligne ne permet pas CREATE VIEW.
        }
    }

    protected function raccordementModuleBasePath(): string
    {
        return '/raccordement-clients';
    }

    protected function raccordementModuleWhereClause(string $alias = ''): 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\"%"
        )';
    }

    protected function ticketMatchesRaccordementModule(array $ticket): bool
    {
        $extraFields = [];
        $rawExtraFields = (string)($ticket['extra_fields'] ?? '');
        if (!empty($ticket['extra_fields'])) {
            $decoded = json_decode((string)$ticket['extra_fields'], true);
            if (is_array($decoded)) {
                $extraFields = $decoded;
            }
        }

        $nature = mb_strtolower(trim((string)($ticket['nature_intervention'] ?? '')));
        if ($nature !== '' && str_contains($nature, 'raccordement')) {
            return true;
        }

        $normalizedExtraFieldJson = mb_strtolower($rawExtraFields);
        foreach (['numeroabonnement', 'equipe_raccordement', 'date_enregistrement_instances', 'date_envoi_instances_prestataires', 'snont', '"module":"raccordement-clients"'] as $marker) {
            if ($normalizedExtraFieldJson !== '' && str_contains($normalizedExtraFieldJson, $marker)) {
                return true;
            }
        }

        foreach (['NumeroAbonnement', 'Equipe_raccordement', 'Date_Enregistrement_Instances', 'Date_Envoi_Instances_Prestataires', 'SNONT', 'module'] as $key) {
            if (!array_key_exists($key, $extraFields)) {
                continue;
            }

            $value = mb_strtolower(trim((string)$extraFields[$key]));
            if ($key === 'module') {
                if ($value === 'raccordement-clients') {
                    return true;
                }
                continue;
            }

            if ($value !== '') {
                return true;
            }
        }

        foreach ($extraFields as $key => $value) {
            $normalizedKey = mb_strtolower(trim((string)$key));
            if (in_array($normalizedKey, ['numeroabonnement', 'equipe_raccordement', 'date_enregistrement_instances', 'date_envoi_instances_prestataires', 'snont'], true)
                && trim((string)$value) !== '') {
                return true;
            }
            if ($normalizedKey === 'module' && mb_strtolower(trim((string)$value)) === 'raccordement-clients') {
                return true;
            }
        }

        return false;
    }

    protected function getRaccordementExpectedImportHeaders(): array
    {
        return [
            'Ville',
            'Commune',
            'NumeroAbonnement',
            'NomClient',
            'Localisation',
            'Contact1',
            'Contact2',
            'Latitude_Terrai',
            'Longitude_Terrain',
            'SNONT',
            'ND',
            'Date_Enregistrement_Instances',
            'Duree_attente_j',
            'Date_Envoi_Instances_Prestataires',
            'EntrepriseFTTH',
            'Etude_Technique',
            'Commentaires',
            'Problèmes',
            'Equipe_raccordement',
        ];
    }

    protected function getRaccordementExpectedImportFileLabel(): string
    {
        return 'Instanceprestataire_template_wandoo.xlsx (onglet RACCORDES)';
    }

    protected function raccordementTableExists(string $tableName): bool
    {
        try {
            $statement = $this->pdo->prepare('SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? LIMIT 1');
            $statement->execute([$tableName]);
            return (bool)$statement->fetchColumn();
        } catch (\Throwable $e) {
            return false;
        }
    }

    protected function raccordementColumnExists(string $tableName, string $columnName): bool
    {
        try {
            $statement = $this->pdo->prepare('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1');
            $statement->execute([$tableName, $columnName]);
            return (bool)$statement->fetchColumn();
        } catch (\Throwable $e) {
            return false;
        }
    }

    protected function buildRaccordementListOtbSelectSql(): string
    {
        $hasReportsSelectedOtb = $this->raccordementColumnExists('maintenance_ftth_reports', 'selected_otb_id');
        $hasReportsSelectedLabel = $this->raccordementColumnExists('maintenance_ftth_reports', 'selected_otb_label');
        $hasOtbBoxes = $this->raccordementTableExists('raccordement_otb_boxes');

        if (!$hasReportsSelectedOtb) {
            return '
                    NULL AS selected_otb_id,
                    NULL AS selected_otb_label,
                    NULL AS selected_box_plaque,
                    NULL AS selected_box_hub,
                    NULL AS selected_box_code,';
        }

        $selectedOtbLabelSql = $hasReportsSelectedLabel
            ? "(SELECT r.selected_otb_label
                     FROM maintenance_ftth_reports r
                     WHERE r.ticket_id = t.id
                     ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC
                     LIMIT 1) AS selected_otb_label,"
            : 'NULL AS selected_otb_label,';

        if (!$hasOtbBoxes) {
            return '
                    (SELECT r.selected_otb_id
                     FROM maintenance_ftth_reports r
                     WHERE r.ticket_id = t.id
                     ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC
                     LIMIT 1) AS selected_otb_id,
                    ' . $selectedOtbLabelSql . '
                    NULL AS selected_box_plaque,
                    NULL AS selected_box_hub,
                    NULL AS selected_box_code,';
        }

        return '
                    (SELECT r.selected_otb_id
                     FROM maintenance_ftth_reports r
                     WHERE r.ticket_id = t.id
                     ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC
                     LIMIT 1) AS selected_otb_id,
                    ' . $selectedOtbLabelSql . '
                    (SELECT b.plaque
                     FROM maintenance_ftth_reports r
                     LEFT JOIN raccordement_otb_boxes b ON b.id = r.selected_otb_id
                     WHERE r.ticket_id = t.id
                     ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC
                     LIMIT 1) AS selected_box_plaque,
                    (SELECT b.hub
                     FROM maintenance_ftth_reports r
                     LEFT JOIN raccordement_otb_boxes b ON b.id = r.selected_otb_id
                     WHERE r.ticket_id = t.id
                     ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC
                     LIMIT 1) AS selected_box_hub,
                    (SELECT b.box_code
                     FROM maintenance_ftth_reports r
                     LEFT JOIN raccordement_otb_boxes b ON b.id = r.selected_otb_id
                     WHERE r.ticket_id = t.id
                     ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC
                     LIMIT 1) AS selected_box_code,';
    }

    protected function raccordementImportTicketExists(?string $refCode, ?string $clientCode, string $clientName): bool
    {
        $where = [$this->raccordementModuleWhereClause('t')];
        $params = [];

        $refCode = trim((string)$refCode);
        $clientCode = trim((string)$clientCode);
        $clientName = trim((string)$clientName);

        if ($refCode !== '') {
            $where[] = 't.ref_code = ?';
            $params[] = $refCode;
        } elseif ($clientCode !== '') {
            $where[] = 't.client_code = ?';
            $where[] = 't.client_name = ?';
            $params[] = $clientCode;
            $params[] = $clientName;
        } else {
            $where[] = 't.client_name = ?';
            $params[] = $clientName;
        }

        $statement = $this->pdo->prepare('SELECT 1 FROM maintenance_ftth_tickets t WHERE ' . implode(' AND ', $where) . ' LIMIT 1');
        $statement->execute($params);

        return (bool)$statement->fetchColumn();
    }

    protected function buildRaccordementListFilters(array $user, string $listScope): array
    {
        $status = trim((string)($_GET['status'] ?? ''));
        $search = trim((string)($_GET['q'] ?? ''));
        $sro = trim((string)($_GET['sro'] ?? ''));
        $company = trim((string)($_GET['company'] ?? ''));
        $progress = trim((string)($_GET['progress'] ?? ''));
        $source = trim((string)($_GET['source'] ?? ''));

        $where = ['1=1', $this->raccordementModuleWhereClause('t')];
        $params = [];

        if ($status !== '') {
            $where[] = 't.status = ?';
            $params[] = $status;
        }
        if ($search !== '') {
            $where[] = '(t.client_name LIKE ? OR t.ref_code LIKE ? OR t.nature_intervention LIKE ? OR t.client_code LIKE ? OR t.fixed_line LIKE ? OR t.company_name LIKE ?)';
            for ($index = 0; $index < 6; $index++) {
                $params[] = "%{$search}%";
            }
        }
        if ($sro !== '') {
            $where[] = 't.sro_client = ?';
            $params[] = $sro;
        }
        if ($company !== '') {
            $where[] = 't.company_name = ?';
            $params[] = $company;
        }
        if ($progress !== '') {
            $where[] = 't.avancement = ?';
            $params[] = $progress;
        }
        if ($source !== '') {
            if ($source === 'operator-direct') {
                $where[] = "(t.source_channel IS NULL OR t.source_channel = '' OR t.source_channel <> 'e-intervention')";
            } else {
                $where[] = 't.source_channel = ?';
                $params[] = $source;
            }
        }
        if ($listScope === 'own') {
            $where[] = $this->maintenanceOwnershipClause('t');
            $params[] = (int)$user['id'];
            $params[] = (int)$user['id'];
        }
        $this->appendAssignedCityFilter($where, $params, $user, 't');

        return [
            'status' => $status,
            'search' => $search,
            'sro' => $sro,
            'company' => $company,
            'progress' => $progress,
            'source' => $source,
            'where' => implode(' AND ', $where),
            'params' => $params,
        ];
    }

    protected function fetchRaccordementDistinctTicketColumnValues(string $column): array
    {
        $allowedColumns = ['sro_client', 'company_name', 'avancement'];
        if (!in_array($column, $allowedColumns, true)) {
            return [];
        }

        $statement = $this->pdo->query(
            "SELECT DISTINCT {$column} AS value
             FROM maintenance_ftth_tickets
             WHERE {$column} IS NOT NULL AND TRIM({$column}) <> '' AND {$this->raccordementModuleWhereClause()}
             ORDER BY {$column} ASC"
        );

        return array_values(array_filter(array_map(
            static fn($value) => trim((string)$value),
            $statement->fetchAll(PDO::FETCH_COLUMN) ?: []
        )));
    }

    protected function resolveRaccordementZoneTeamTechnicianIds(array $ticket): array
    {
        $zoneKey = trim((string)($ticket['dispatch_zone_key'] ?? ''));
        if ($zoneKey === '') {
            return [];
        }

        $setting = RaccordementOtbService::getZoneSetting($this->pdo, $zoneKey);
        return array_values(array_filter(array_map('intval', $setting['technician_ids'] ?? []), static fn(int $id): bool => $id > 0));
    }

    protected function fetchAssignedTechnicianIds(int $ticketId, ?int $excludeTechnicianId = null): array
    {
        $statement = $this->pdo->prepare('SELECT technician_id FROM maintenance_ftth_assignments WHERE ticket_id = ?');
        $statement->execute([$ticketId]);

        return array_values(array_filter(array_map('intval', $statement->fetchAll(PDO::FETCH_COLUMN) ?: []), static function (int $technicianId) use ($excludeTechnicianId): bool {
            return $technicianId > 0 && ($excludeTechnicianId === null || $technicianId !== $excludeTechnicianId);
        }));
    }

    protected function filterRaccordementTechnicianIds(array $technicianIds, ?int $excludeTechnicianId = null): array
    {
        $technicianIds = array_values(array_filter(array_map('intval', $technicianIds), static function (int $technicianId) use ($excludeTechnicianId): bool {
            return $technicianId > 0 && ($excludeTechnicianId === null || $technicianId !== $excludeTechnicianId);
        }));
        if (empty($technicianIds)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($technicianIds), '?'));
        $statement = $this->pdo->prepare(
            "SELECT id
             FROM users
             WHERE active = 1
               AND role_key IN ('technicien','technician')
               AND technician_type = 'raccordement'
               AND id IN ({$placeholders})"
        );
        $statement->execute($technicianIds);

        return array_values(array_map('intval', $statement->fetchAll(PDO::FETCH_COLUMN) ?: []));
    }

    protected function fetchAllRaccordementTechnicianIds(?int $excludeTechnicianId = null): array
    {
        $statement = $this->pdo->query(
            "SELECT id
             FROM users
             WHERE active = 1
               AND role_key IN ('technicien','technician')
               AND technician_type = 'raccordement'
             ORDER BY name ASC"
        );

        return array_values(array_filter(array_map('intval', $statement->fetchAll(PDO::FETCH_COLUMN) ?: []), static function (int $technicianId) use ($excludeTechnicianId): bool {
            return $technicianId > 0 && ($excludeTechnicianId === null || $technicianId !== $excludeTechnicianId);
        }));
    }

    protected function resolveRaccordementHandoffTechnicianIds(array $ticket, int $ticketId, int $currentUserId): array
    {
        $zoneTeamIds = $this->resolveRaccordementZoneTeamTechnicianIds($ticket);
        $zoneTeamIds = $this->filterRaccordementTechnicianIds($zoneTeamIds, $currentUserId > 0 ? $currentUserId : null);
        if (!empty($zoneTeamIds)) {
            return $zoneTeamIds;
        }

        $assignedIds = $this->fetchAssignedTechnicianIds($ticketId, $currentUserId > 0 ? $currentUserId : null);
        $assignedIds = $this->filterRaccordementTechnicianIds($assignedIds, $currentUserId > 0 ? $currentUserId : null);
        if (!empty($assignedIds)) {
            return $assignedIds;
        }

        return $this->fetchAllRaccordementTechnicianIds($currentUserId > 0 ? $currentUserId : null);
    }

    protected function resolveRaccordementHandoffTechnicians(array $ticket, int $ticketId, int $excludeTechnicianId = 0): array
    {
        $ids = $this->resolveRaccordementHandoffTechnicianIds($ticket, $ticketId, $excludeTechnicianId);
        $technicians = [];

        foreach ($ids as $technicianId) {
            if ($excludeTechnicianId > 0 && (int)$technicianId === $excludeTechnicianId) {
                continue;
            }

            $user = $this->findUserById((int)$technicianId);
            if ($user) {
                $technicians[] = $user;
            }
        }

        return $technicians;
    }

    protected function resolveRaccordementAssignableTechnicianType(?array $report): string
    {
        $workflowPhase = strtolower(trim((string)($report['workflow_phase'] ?? 'study')));
        if ($workflowPhase === 'installation') {
            return 'raccordement';
        }

        if (!empty($report['installation_sent_at']) || (int)($report['installation_technician_id'] ?? 0) > 0) {
            return 'raccordement';
        }

        return 'etude';
    }

    protected function fetchRaccordementAssignableTechnicians(?array $report): array
    {
        $statement = $this->pdo->prepare(
            "SELECT id, name, email
             FROM users
             WHERE active = 1
               AND role_key IN ('technicien','technician')
               AND technician_type = ?
             ORDER BY name ASC"
        );
        $statement->execute([$this->resolveRaccordementAssignableTechnicianType($report)]);

        return $statement->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    protected function filterRaccordementAssignableTechnicianIds(array $techIds, ?array $report): array
    {
        $techIds = array_values(array_unique(array_filter(array_map('intval', $techIds), static fn(int $id): bool => $id > 0)));
        if ($techIds === []) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($techIds), '?'));
        $statement = $this->pdo->prepare(
            "SELECT id
             FROM users
             WHERE active = 1
               AND role_key IN ('technicien','technician')
               AND technician_type = ?
               AND id IN ($placeholders)
             ORDER BY name ASC"
        );
        $statement->execute(array_merge([$this->resolveRaccordementAssignableTechnicianType($report)], $techIds));

        return array_values(array_map('intval', $statement->fetchAll(PDO::FETCH_COLUMN) ?: []));
    }

    protected function resolveRaccordementStudyTechnician(?array $report, array $assignments): ?array
    {
        $studyTechnicianId = (int)($report['study_technician_id'] ?? 0);
        if ($studyTechnicianId > 0) {
            return $this->findUserById($studyTechnicianId);
        }

        $reportTechnicianId = (int)($report['technician_id'] ?? 0);
        if ($reportTechnicianId > 0) {
            $reportTechnician = $this->findUserById($reportTechnicianId);
            if ($reportTechnician) {
                return $reportTechnician;
            }
        }

        foreach ($assignments as $assignment) {
            $technicianType = strtolower(trim((string)($assignment['tech_technician_type'] ?? '')));
            $technicianId = (int)($assignment['technician_id'] ?? 0);
            if ($technicianId > 0 && $technicianType === 'etude') {
                return $this->findUserById($technicianId);
            }
        }

        foreach ($assignments as $assignment) {
            $technicianId = (int)($assignment['technician_id'] ?? 0);
            if ($technicianId > 0) {
                return $this->findUserById($technicianId);
            }
        }

        return null;
    }

    protected function resolveRaccordementInstallationTechnician(?array $report, array $assignments): ?array
    {
        $installationTechnicianId = (int)($report['installation_technician_id'] ?? 0);
        if ($installationTechnicianId > 0) {
            return $this->findUserById($installationTechnicianId);
        }

        foreach ($assignments as $assignment) {
            $technicianType = strtolower(trim((string)($assignment['tech_technician_type'] ?? '')));
            $technicianId = (int)($assignment['technician_id'] ?? 0);
            if ($technicianId > 0 && $technicianType === 'raccordement') {
                return $this->findUserById($technicianId);
            }
        }

        return null;
    }

    protected function canEditRaccordementStudyPhase(array $user, ?array $report): bool
    {
        if (in_array($user['role_key'] ?? '', ['admin', 'agent', 'manager', 'superviseur', 'supervisor'], true)) {
            return true;
        }

        if (is_array($report) && (string)($report['workflow_phase'] ?? 'study') !== 'study') {
            return false;
        }

        $studyTechnicianId = (int)($report['study_technician_id'] ?? 0);
        if ($studyTechnicianId > 0) {
            return $studyTechnicianId === (int)($user['id'] ?? 0);
        }

        return true;
    }

    protected function canEditRaccordementInstallationPhase(array $user, ?array $report): bool
    {
        if (in_array($user['role_key'] ?? '', ['admin', 'agent', 'manager', 'superviseur', 'supervisor'], true)) {
            return true;
        }

        if (!is_array($report) || (string)($report['workflow_phase'] ?? 'study') === 'study') {
            return false;
        }

        return (int)($report['installation_technician_id'] ?? 0) === (int)($user['id'] ?? 0);
    }

    protected function resolveRaccordementStudyCoordinates(?array $report, array $ticket): array
    {
        $studyLat = $report['study_gps_lat'] ?? null;
        $studyLng = $report['study_gps_lng'] ?? null;
        if ($this->hasValidCoordinates($studyLat, $studyLng)) {
            return [
                'lat' => (float)str_replace(',', '.', (string)$studyLat),
                'lng' => (float)str_replace(',', '.', (string)$studyLng),
                'source' => 'study',
            ];
        }

        return $this->resolveTicketClientCoordinates($ticket);
    }

    public function index(): void
    {
        $user = $this->requireAuth();
        $listScope = $this->resolveMaintenanceScope('list', $user);
        if ($listScope === 'none') {
            http_response_code(403);
            exit('Accès refusé.');
        }

        $filters = $this->buildRaccordementListFilters($user, $listScope);
        $page = max(1, (int)($_GET['page'] ?? 1));
        $perPage = 20;
        $offset = ($page - 1) * $perPage;
        $otbSelectSql = $this->buildRaccordementListOtbSelectSql();

        $countStatement = $this->pdo->prepare("SELECT COUNT(*) FROM maintenance_ftth_tickets t WHERE {$filters['where']}");
        $countStatement->execute($filters['params']);
        $total = (int)$countStatement->fetchColumn();

        $listStatement = $this->pdo->prepare(
            "SELECT t.*, t.id AS ticket_id, u.name AS imported_by_name,
                    (SELECT GROUP_CONCAT(us.name SEPARATOR ', ')
                     FROM maintenance_ftth_assignments a
                     JOIN users us ON us.id = a.technician_id
                     WHERE a.ticket_id = t.id) AS assigned_to,
                    (SELECT r.status FROM maintenance_ftth_reports r WHERE r.ticket_id = t.id LIMIT 1) AS report_status,
                    (SELECT r.client_visit_status FROM maintenance_ftth_reports r WHERE r.ticket_id = t.id LIMIT 1) AS client_visit_status,
                    (SELECT r.planned_intervention_date FROM maintenance_ftth_reports r WHERE r.ticket_id = t.id LIMIT 1) AS planned_intervention_date,
                    (SELECT r.planned_intervention_time FROM maintenance_ftth_reports r WHERE r.ticket_id = t.id LIMIT 1) AS planned_intervention_time,
                    {$otbSelectSql}
                    (SELECT COALESCE(r.updated_at, r.created_at) FROM maintenance_ftth_reports r WHERE r.ticket_id = t.id ORDER BY COALESCE(r.updated_at, r.created_at) DESC LIMIT 1) AS report_updated_at
             FROM maintenance_ftth_tickets t
             LEFT JOIN users u ON u.id = t.imported_by
             WHERE {$filters['where']}
             ORDER BY t.created_at DESC
             LIMIT {$perPage} OFFSET {$offset}"
        );
        $listStatement->execute($filters['params']);
        $tickets = array_map(fn(array $ticket): array => $this->normalizeRaccordementTicketForView($this->hydrateTicketImportFallbacks($ticket)), $listStatement->fetchAll(PDO::FETCH_ASSOC));

        $statsStatement = $this->pdo->prepare("SELECT t.status, COUNT(*) AS cnt FROM maintenance_ftth_tickets t WHERE {$filters['where']} GROUP BY t.status");
        $statsStatement->execute($filters['params']);
        $stats = [];
        foreach ($statsStatement->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $stats[$row['status']] = (int)$row['cnt'];
        }

        $technicians = $this->fetchRaccordementAssignableTechnicians(null);

        $this->view('maintenance_ftth/index', [
            'title' => 'Raccordement Clients',
            'isRaccordementClients' => true,
            'moduleBasePath' => $this->raccordementModuleBasePath(),
            'tickets' => $tickets,
            'stats' => $stats,
            'status' => $filters['status'],
            'search' => $filters['search'],
            'sro' => $filters['sro'],
            'company' => $filters['company'],
            'progress' => $filters['progress'],
            'source' => $filters['source'],
            'page' => $page,
            'perPage' => $perPage,
            'total' => $total,
            'pages' => (int)ceil($total / $perPage),
            'technicians' => $technicians,
            'expectedHeaders' => $this->getRaccordementExpectedImportHeaders(),
            'expectedImportFileLabel' => $this->getRaccordementExpectedImportFileLabel(),
            'sroOptions' => $this->fetchRaccordementDistinctTicketColumnValues('sro_client'),
            'companyOptions' => $this->fetchRaccordementDistinctTicketColumnValues('company_name'),
            'progressOptions' => $this->fetchRaccordementDistinctTicketColumnValues('avancement'),
            'sourceOptions' => [
                'e-intervention' => 'e-Intervention',
                'operator-direct' => 'Opérateur direct',
            ],
            'user' => $user,
            'maintenanceScope' => $listScope,
        ]);
    }

    public function export(): void
    {
        $user = $this->requireAuth();
        $listScope = $this->resolveMaintenanceScope('list', $user);
        if ($listScope === 'none') {
            http_response_code(403);
            exit('Accès refusé.');
        }

        $filters = $this->buildRaccordementListFilters($user, $listScope);
        $rows = $this->fetchMaintenanceExportRows($filters['where'], $filters['params']);

        $filename = 'raccordement_clients_' . date('Ymd_His') . '.csv';
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        $output = fopen('php://output', 'w');
        fwrite($output, "\xEF\xBB\xBF");
        fputcsv($output, [
            'Reference', 'Ligne fixe', 'Code client', 'Client', 'Contact 1', 'Contact 2',
            'Localisation', 'SRO', 'JDV', 'PCO', 'Entreprise', 'Nature intervention',
            'Avancement', 'Statut', 'Priorite', 'Equipes', 'Assignes a', 'Statut rapport', 'Statut intervention',
            'Commentaire constat', 'Commentaire intervention',
            'Date cloture', 'Origine', 'Importe le', 'Importe par'
        ], ';');

        foreach ($rows as $row) {
            fputcsv($output, [
                $row['ref_code'] ?? '',
                $row['fixed_line'] ?? '',
                $row['client_code'] ?? '',
                $row['client_name'] ?? '',
                $row['client_phone'] ?? '',
                $row['client_phone2'] ?? '',
                $row['client_address'] ?? '',
                $row['sro_client'] ?? '',
                $row['jdv_client'] ?? '',
                $row['pco_client'] ?? '',
                $row['company_name'] ?? '',
                $row['nature_intervention'] ?? '',
                $row['avancement'] ?? '',
                $row['status'] ?? '',
                $row['priority'] ?? '',
                $row['equipes'] ?? '',
                $row['assigned_to'] ?? '',
                $row['report_status'] ?? '',
                $row['client_visit_status'] ?? '',
                trim((string)($row['constat_comments'] ?? '')),
                trim((string)($row['general_comments'] ?? '')),
                $row['date_cloture'] ?? '',
                (($row['source_channel'] ?? '') === 'e-intervention') ? 'e-Intervention' : 'Operateur direct',
                $row['imported_at'] ?? '',
                $row['imported_by_name'] ?? '',
            ], ';');
        }

        fclose($output);
        exit;
    }

    public function exportPdf(): void
    {
        $user = $this->requireAuth();
        $listScope = $this->resolveMaintenanceScope('list', $user);
        if ($listScope === 'none') {
            http_response_code(403);
            exit('Accès refusé.');
        }

        $filters = $this->buildRaccordementListFilters($user, $listScope);
        $rows = $this->fetchMaintenanceExportRows($filters['where'], $filters['params']);
        $stats = $this->buildMaintenanceExportStats($rows);
        $html = $this->renderViewToString('maintenance_ftth/export_pdf', [
            'rows' => $rows,
            'stats' => $stats,
            'filters' => $filters,
            'generatedBy' => $user,
            'generatedAt' => date('Y-m-d H:i:s'),
        ], 'none');

        PDF::download($html, 'raccordement_clients_' . date('Ymd_His') . '.pdf', [
            'isRemoteEnabled' => true,
        ]);
    }

    public function import(): void
    {
        $user = $this->requireRoles(['admin', 'agent', 'manager', 'superviseur', 'supervisor']);
        $moduleBasePath = $this->raccordementModuleBasePath();

        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect($moduleBasePath);
            return;
        }

        if (empty($_FILES['excel_file']['tmp_name']) || !is_uploaded_file($_FILES['excel_file']['tmp_name'])) {
            $this->redirect($moduleBasePath . '?err=nofile');
            return;
        }

        $tmpPath = $_FILES['excel_file']['tmp_name'];
        $originalName = trim((string)($_FILES['excel_file']['name'] ?? ''));
        $origExt = strtolower(pathinfo((string)($_FILES['excel_file']['name'] ?? ''), PATHINFO_EXTENSION));
        if (!in_array($origExt, ['xlsx', 'xls'], true)) {
            $this->redirect($moduleBasePath . '?err=badext');
            return;
        }

        try {
            $rows = XlsxReader::readSheet($tmpPath, 'RACCORDES');
        } catch (\Throwable $exception) {
            $this->redirect($moduleBasePath . '?err=' . urlencode($exception->getMessage()));
            return;
        }

        if (count($rows) < 2) {
            $this->redirect($moduleBasePath . '?err=empty');
            return;
        }

        $fileHash = @hash_file('sha256', $tmpPath) ?: '';
        $previousImport = $fileHash !== '' ? $this->findExistingImportByHash($fileHash) : null;
        $assocRows = XlsxReader::toAssoc($rows);
        $imported = 0;
        $skipped = 0;
        $resolvedSkipped = 0;
        $firstImportError = '';

        $statement = $this->pdo->prepare(
            'INSERT INTO maintenance_ftth_tickets
                (ref_code, fixed_line, client_name, client_code, client_phone, client_phone2,
                 client_email, client_address, client_lat, client_lng,
                 sro_client, jdv_client, pco_client, company_name,
                 site_name, nature_intervention, description, cause_comment,
                 priority, status, avancement, equipes, date_cloture,
                 extra_fields, imported_at, imported_by)
             VALUES (:ref_code, :fixed_line, :client_name, :client_code, :client_phone, :client_phone2,
                     :client_email, :client_address, :client_lat, :client_lng,
                     :sro_client, :jdv_client, :pco_client, :company_name,
                     :site_name, :nature_intervention, :description, :cause_comment,
                     :priority, :status, :avancement, :equipes, :date_cloture,
                     :extra_fields, NOW(), :imported_by)'
        );

        foreach ($assocRows as $rowIndex => $row) {
            $clientName = trim((string)($this->pickCol($row, [
                'NomClient', 'Nom du client', 'Noom du client', 'Nom client', 'Client', 'Nom / Prenoms client',
                'Numero Abonnement/Nom du client', 'Numero abonnement nom du client'
            ]) ?? ''));
            if ($clientName === '') {
                $skipped++;
                continue;
            }

            $ref = $this->pickCol($row, ['Numéro de Requête', 'Numero de Requete', 'Numero Requete', 'N Requete', 'N° Requête', 'Reference', 'Référence', 'Ref']);
            $fixedLine = $this->pickCol($row, ['Ligne fixe', 'Ligne Fixe', 'Ligne']);
            $phone = $this->pickCol($row, ['Contact1', 'CONTACT CLIENT 1', 'Contact client 1', 'Contact client', 'Telephone client 1', 'Téléphone client 1', 'Telephone', 'Téléphone', 'Phone']);
            $phone2 = $this->pickCol($row, ['Contact2', 'CONTACT CLIENT 2', 'Contact client 2', 'Telephone client 2', 'Téléphone client 2', 'Phone 2', 'Contact secondaire']);
            $address = $this->pickCol($row, ['LOCALISATION', 'Localisation', 'Adresse', 'Address', 'Contact client et localisation', 'Contact client localisation', 'Adresse intervention']);
            $clientCode = $this->pickCol($row, ['NumeroAbonnement', 'Numéro Abonnement', 'Numero Abonnement', 'N° Abonnement', 'Numero abonnement', 'Code client', 'Abonnement', 'Compte client']);
            $siteName = $this->pickCol($row, ['Commune', 'Ville', 'Site', 'Site concerne', 'Site concerné', 'Nom du site', 'Localisation site']);
            $sroClient = null;
            $jdvClient = null;
            $pcoClient = null;
            $companyName = $this->pickCol($row, ['EntrepriseFTTH', 'Entreprise/porteur d\'action', 'Entreprise porteur d action', 'Entreprise', 'Porteur d action']);
            $nature = $this->pickCol($row, ['Etude_Technique', 'Nature intervention', 'Nature de l intervention', 'Type intervention', 'Intervention', 'Type de maintenance']);
            $description = $this->pickCol($row, ['Commentaires', 'Problèmes', 'Commentaire', 'Commentaire client', 'Observation', 'Observations', 'Notes', 'Avertissement du client', 'Avertissements du client']);
            $causeComment = $this->pickCol($row, ['Problèmes', 'Commentaire_cause', 'Commentaire cause', 'Cause', 'Cause commentaire']);
            $avancement = $this->pickCol($row, ['Avancement', 'Progression']);
            $equipes = $this->pickCol($row, ['Equipe_raccordement', 'equipes', 'Équipes', 'Equipe', 'Interruption/equipes', 'Interruption equipes', 'Liste Tech', 'Liste techniciens', 'Technicien', 'Techniciens', 'Soudeur']);
            $equipesValue = trim((string)$equipes);
            $dateCloture = $this->pickCol($row, ['Date de Clôture', 'Date de Cloture', 'Date cloture', 'Cloture', 'Date fermeture']);
            $rawStatus = $this->pickCol($row, ['Statut', 'Status', 'Etat', 'État']);
            $priority = $this->normalizePriority((string)($this->pickCol($row, ['Priorite', 'Priorité', 'Urgence', 'Niveau de priorite', 'Niveau de priorité']) ?? 'Moyenne'));

            if ($this->raccordementImportTicketExists($ref, $clientCode, $clientName)) {
                $skipped++;
                continue;
            }

            if ($this->shouldSkipImportRowByStatus($rawStatus)) {
                $skipped++;
                $resolvedSkipped++;
                continue;
            }
            $status = $this->normalizeStatus((string)($rawStatus ?? ''));

            $lat = null;
            $lng = null;
            $gpsRaw = $this->pickCol($row, ['GPS', 'Coordonnees GPS', 'Coordonnées GPS', 'Coordonnees', 'Coordonnées', 'Coordinates', 'Latitude/Longitude']);
            if ($gpsRaw && preg_match('/(-?\d+(?:[\.,]\d+)?)\s*[,;\/]\s*(-?\d+(?:[\.,]\d+)?)/', (string)$gpsRaw, $matches)) {
                $lat = str_replace(',', '.', $matches[1]);
                $lng = str_replace(',', '.', $matches[2]);
            } else {
                $rawLat = $this->pickCol($row, ['Latitude_Terrai', 'Latitude_Terrain', 'ck_latitude']);
                $rawLng = $this->pickCol($row, ['Longitude_Terrain', 'Longitude_Terrai', 'ck_longitude']);
                if ($rawLat !== null && $rawLng !== null && $rawLat !== '' && $rawLng !== '') {
                    $lat = str_replace(',', '.', (string)$rawLat);
                    $lng = str_replace(',', '.', (string)$rawLng);
                }
            }

            if (trim((string)$nature) === '' && $this->pickCol($row, ['NumeroAbonnement']) !== null) {
                $nature = 'Raccordement client';
            }

            $zoneContext = $this->resolveImportZoneContext($row, $sroClient, $jdvClient, $pcoClient, $siteName);
            $extraFields = [];
            foreach ($row as $columnName => $columnValue) {
                $normalizedColumn = $this->normalizeImportHeader((string)$columnName);
                if (in_array($normalizedColumn, ['plaque', 'jdv', 'pco'], true)) {
                    continue;
                }
                if (trim((string)$columnValue) !== '') {
                    $extraFields[$columnName] = $columnValue;
                }
            }
            $extraFields['module'] = 'raccordement-clients';

            try {
                $statement->execute([
                    'ref_code' => $ref,
                    'fixed_line' => $fixedLine,
                    'client_name' => $clientName,
                    'client_code' => $clientCode,
                    'client_phone' => $phone,
                    'client_phone2' => $phone2,
                    'client_email' => null,
                    'client_address' => $address,
                    'client_lat' => $lat,
                    'client_lng' => $lng,
                    'sro_client' => $sroClient,
                    'jdv_client' => $jdvClient,
                    'pco_client' => $pcoClient,
                    'company_name' => $companyName,
                    'site_name' => $siteName,
                    'nature_intervention' => $nature,
                    'description' => $description,
                    'cause_comment' => $causeComment,
                    'priority' => $priority,
                    'status' => $status,
                    'avancement' => $avancement,
                    'equipes' => $equipesValue !== '' ? $equipesValue : null,
                    'date_cloture' => $dateCloture,
                    'extra_fields' => json_encode($extraFields, JSON_UNESCAPED_UNICODE),
                    'imported_by' => (int)$user['id'],
                ]);
                $imported++;

                try {
                    $ticketId = $this->resolveImportedTicketId($ref, $clientCode, $clientName);
                    if ($ticketId > 0) {
                        $this->saveTicketDispatchZone($ticketId, $zoneContext);
                    }
                } catch (\Throwable $zoneException) {
                    if ($firstImportError === '') {
                        $lineNumber = $rowIndex + 2;
                        $firstImportError = 'Ligne ' . $lineNumber . ' importee mais post-traitement incomplet: ' . $zoneException->getMessage();
                    }
                }
            } catch (\Throwable $exception) {
                $skipped++;
                if ($firstImportError === '') {
                    $lineNumber = $rowIndex + 2;
                    $firstImportError = 'Ligne ' . $lineNumber . ' ignoree: ' . $exception->getMessage();
                }
            }
        }

        if ($fileHash !== '') {
            $this->storeImportFingerprint($fileHash, $originalName, count($assocRows), (int)$user['id'], $imported, $skipped);
        }

        $params = [
            'imported' => $imported,
            'skipped' => $skipped,
            'resolved_skipped' => $resolvedSkipped,
        ];

        if ($previousImport) {
            $params['duplicate_import'] = 1;
            $params['duplicate_file'] = (string)($previousImport['original_name'] ?: $originalName);
            $params['duplicate_at'] = (string)($previousImport['last_imported_at'] ?: $previousImport['first_imported_at'] ?: '');
            $params['duplicate_count'] = (int)($previousImport['import_count'] ?? 1);
        }

        if ($firstImportError !== '') {
            $params['err'] = $firstImportError;
        }

        $this->redirect($moduleBasePath . '?' . http_build_query($params));
    }

    public function show(): void
    {
        $ticketId = (int)($_GET['id'] ?? 0);
        $user = $this->requireAuth();

        if (session_status() === PHP_SESSION_ACTIVE) {
            $_SESSION['last_raccordement_ticket_id'] = $ticketId;
        }

        if ($ticketId <= 0) {
            $this->redirect($this->currentModuleBasePath());
            return;
        }

        $viewScope = $this->resolveMaintenanceScope('view', $user);
        if ($viewScope === 'none') {
            http_response_code(403);
            exit('Accès refusé.');
        }

        if (!$this->canAccessMaintenanceTicket($ticketId, $user, 'view')) {
            http_response_code(403);
            exit('Accès refusé.');
        }

        $ticketStatement = $this->pdo->prepare(
            'SELECT t.*, u.name AS imported_by_name
             FROM maintenance_ftth_tickets t
             LEFT JOIN users u ON u.id = t.imported_by
             WHERE t.id = ? LIMIT 1'
        );
        $ticketStatement->execute([$ticketId]);
        $ticket = $ticketStatement->fetch(PDO::FETCH_ASSOC);
        if (!$ticket || !$this->ticketMatchesRaccordementModule($ticket)) {
            http_response_code(404);
            exit('Ticket non trouvé pour ce module.');
        }
        $ticket = $this->normalizeRaccordementTicketForView($this->hydrateTicketImportFallbacks($ticket));

        $photoColumn = $this->detectUserPhotoColumn();
        $selectPhoto = $photoColumn ? ', u.`' . str_replace('`', '', $photoColumn) . '` AS tech_photo_path' : ', NULL AS tech_photo_path';
        $assignmentStatement = $this->pdo->prepare(
            'SELECT a.*, u.name AS tech_name, u.email AS tech_email, u.phone AS tech_phone, u.role_key AS tech_role_key, u.technician_type AS tech_technician_type' . $selectPhoto . '
             FROM maintenance_ftth_assignments a
             JOIN users u ON u.id = a.technician_id
             WHERE a.ticket_id = ?
             ORDER BY a.assigned_at DESC'
        );
        $assignmentStatement->execute([$ticketId]);
        $assignments = array_map(function (array $assignment): array {
            $assignment['tech_avatar_url'] = $this->resolveUserPhotoUrl($assignment['tech_photo_path'] ?? null);
            $assignment['tech_initials'] = $this->buildInitials((string)($assignment['tech_name'] ?? ''));
            return $assignment;
        }, $assignmentStatement->fetchAll(PDO::FETCH_ASSOC));

        $reportStatement = $this->pdo->prepare(
            'SELECT r.*, r.id AS report_id, uv.name AS validated_by_name, ut.name AS technician_name
             FROM maintenance_ftth_reports r
             LEFT JOIN users uv ON uv.id = r.validated_by
             LEFT JOIN users ut ON ut.id = r.technician_id
             WHERE r.ticket_id = ? AND r.id > 0
             ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC
             LIMIT 1'
        );
        $reportStatement->execute([$ticketId]);
        $report = $reportStatement->fetch(PDO::FETCH_ASSOC) ?: null;

        if (!$report && (($ticket['status'] ?? '') === 'attente_planification' || isset($_GET['transmitted']) || isset($_GET['validated']))) {
            $this->logRaccordementRuntime('show_missing_report', [
                'ticket_id' => $ticketId,
                'ticket_status' => (string)($ticket['status'] ?? ''),
                'ticket_progress' => (string)($ticket['avancement'] ?? ''),
                'query' => [
                    'transmitted' => $_GET['transmitted'] ?? null,
                    'validated' => $_GET['validated'] ?? null,
                    'err' => $_GET['err'] ?? null,
                ],
                'assignment_ids' => array_values(array_map(static fn(array $assignment): int => (int)($assignment['technician_id'] ?? 0), $assignments)),
            ]);
        }

        $photos = [];
        if ($report) {
            $photoStatement = $this->pdo->prepare('SELECT * FROM maintenance_ftth_photos WHERE report_id=? ORDER BY tab_type, id');
            $photoStatement->execute([(int)($report['report_id'] ?? $report['id'] ?? 0)]);
            $photos = $photoStatement->fetchAll(PDO::FETCH_ASSOC);
        }

        $technicians = $this->fetchRaccordementAssignableTechnicians($report);
        $extraFields = [];
        if (!empty($ticket['extra_fields'])) {
            $decoded = json_decode((string)$ticket['extra_fields'], true);
            if (is_array($decoded)) {
                $extraFields = $decoded;
            }
        }

        $ticketCoordinates = $this->resolveTicketClientCoordinates($ticket);
        $recommendationCoordinates = $this->resolveRaccordementStudyCoordinates($report, $ticket);
        $zoneSetting = RaccordementOtbService::getZoneSetting($this->pdo, (string)($ticket['dispatch_zone_key'] ?? ''));
        $zoneTeamIds = $this->resolveRaccordementZoneTeamTechnicianIds($ticket);
        $otbRecommendation = RaccordementOtbService::buildRecommendation(
            $this->pdo,
            $recommendationCoordinates['lat'],
            $recommendationCoordinates['lng'],
            (string)($ticket['dispatch_zone_key'] ?? '')
        );
        $selectedBoxId = (int)($report['selected_otb_id'] ?? 0);
        $selectedBox = $selectedBoxId > 0 ? RaccordementOtbService::findBoxById($this->pdo, $selectedBoxId) : null;
        $portOptions = $selectedBoxId > 0 ? RaccordementOtbService::getAvailablePorts($this->pdo, $selectedBoxId, (int)($report['id'] ?? 0) ?: null) : [];
        $portsByBox = [];
        foreach (array_merge($selectedBox ? [$selectedBox] : [], $otbRecommendation['candidates'] ?? [], $otbRecommendation['out_of_radius'] ?? []) as $box) {
            $boxId = (int)($box['id'] ?? 0);
            if ($boxId <= 0 || isset($portsByBox[$boxId])) {
                continue;
            }
            $portsByBox[$boxId] = RaccordementOtbService::getAvailablePorts($this->pdo, $boxId, (int)($report['id'] ?? 0) ?: null);
        }

        $studyTechnician = $this->resolveRaccordementStudyTechnician($report, $assignments);
        $installationTechnician = $this->resolveRaccordementInstallationTechnician($report, $assignments);

        $raccordementWorkflow = [
            'phase' => (string)($report['workflow_phase'] ?? 'study'),
            'assignable_technician_type' => $this->resolveRaccordementAssignableTechnicianType($report),
            'zone_setting' => $zoneSetting,
            'zone_team_ids' => $zoneTeamIds,
            'handoff_technicians' => $this->resolveRaccordementHandoffTechnicians($ticket, $ticketId, (int)($studyTechnician['id'] ?? ($user['id'] ?? 0))),
            'otb' => $otbRecommendation,
            'selected_box' => $selectedBox,
            'port_options' => $portOptions,
            'ports_by_box' => $portsByBox,
            'study_technician' => $studyTechnician,
            'installation_technician' => $installationTechnician,
            'study_can_edit' => $this->canEditRaccordementStudyPhase($user, $report),
            'installation_can_edit' => $this->canEditRaccordementInstallationPhase($user, $report),
            'ticket_coordinates' => $ticketCoordinates,
            'recommendation_coordinates' => $recommendationCoordinates,
        ];

        $this->view('raccordement_clients/show', [
            'title' => 'Raccordement — ' . ($ticket['ref_code'] ?: 'Ticket #' . $ticket['id']),
            'isRaccordementClients' => true,
            'moduleBasePath' => $this->raccordementModuleBasePath(),
            'ticket' => $ticket,
            'extraFields' => $extraFields,
            'assignments' => $assignments,
            'report' => $report,
            'photos' => $photos,
            'ticketEvents' => $this->fetchTicketEvents($ticketId),
            'technicians' => $technicians,
            'raccordementWorkflow' => $raccordementWorkflow,
            'user' => $user,
        ]);
    }

    public function deleteTicket(): void
    {
        $moduleBasePath = $this->raccordementModuleBasePath();
        $ticketId = (int)($_POST['ticket_id'] ?? $_POST['id'] ?? $_GET['ticket_id'] ?? $_GET['id'] ?? 0);
        $user = $this->requireRoles(['admin', 'manager']);

        if ($ticketId <= 0) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Aucun ticket sélectionné pour la suppression.'));
            return;
        }

        if (!$this->canAccessMaintenanceTicket($ticketId, $user, 'delete')) {
            $this->redirect($moduleBasePath . '?err=forbidden');
            return;
        }

        $deletedCount = $this->deleteRaccordementTicketsByIds([$ticketId]);
        $this->redirect($moduleBasePath . '?' . ($deletedCount > 0 ? 'deleted=1' : 'err=' . urlencode('Ticket introuvable ou déjà supprimé.')));
    }

    public function bulkDelete(): void
    {
        $moduleBasePath = $this->raccordementModuleBasePath();
        $user = $this->requireRoles(['admin', 'manager']);

        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect($moduleBasePath);
            return;
        }

        $ticketIds = $this->parseSelectedRaccordementTicketIds($_POST['selected_ticket_ids'] ?? []);
        if (empty($ticketIds)) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Aucun ticket sélectionné pour la suppression.'));
            return;
        }

        $ticketIds = $this->filterAccessibleRaccordementTicketIds($ticketIds, $user, $this->resolveMaintenanceScope('delete', $user));
        if (empty($ticketIds)) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Aucun ticket supprimable dans votre ville de rattachement.'));
            return;
        }

        $deletedCount = $this->deleteRaccordementTicketsByIds($ticketIds);
        $this->redirect($moduleBasePath . '?bulk_deleted=' . $deletedCount);
    }

    private function normalizeRaccordementTicketForView(array $ticket): array
    {
        $ticketId = (int)($ticket['ticket_id'] ?? 0);
        if ($ticketId <= 0) {
            $ticketId = (int)($ticket['id'] ?? 0);
        }
        if ($ticketId <= 0) {
            $ticketId = $this->resolveImportedTicketId(
                isset($ticket['ref_code']) ? (string)$ticket['ref_code'] : null,
                isset($ticket['client_code']) ? (string)$ticket['client_code'] : null,
                trim((string)($ticket['client_name'] ?? ''))
            );
        }

        if ($ticketId > 0) {
            $ticket['ticket_id'] = $ticketId;
            $ticket['id'] = $ticketId;
        }

        return $ticket;
    }

    private function parseSelectedRaccordementTicketIds($rawIds): array
    {
        if (is_string($rawIds)) {
            $rawIds = preg_split('/[\s,;]+/', $rawIds) ?: [];
        }

        if (!is_array($rawIds)) {
            $rawIds = [$rawIds];
        }

        $ids = array_values(array_unique(array_filter(array_map('intval', $rawIds), static fn(int $id): bool => $id > 0)));
        return array_slice($ids, 0, 200);
    }

    private function filterAccessibleRaccordementTicketIds(array $ticketIds, array $user, string $scope): array
    {
        $normalizedIds = $this->parseSelectedRaccordementTicketIds($ticketIds);
        if (empty($normalizedIds) || !in_array($scope, ['all', 'own'], true)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($normalizedIds), '?'));
        $where = ['t.id IN (' . $placeholders . ')', $this->raccordementModuleWhereClause('t')];
        $params = $normalizedIds;

        if ($scope === 'own') {
            $where[] = $this->maintenanceOwnershipClause('t');
            $userId = (int)($user['id'] ?? 0);
            $params[] = $userId;
            $params[] = $userId;
        }

        $this->appendAssignedCityFilter($where, $params, $user, 't');

        $statement = $this->pdo->prepare('SELECT t.id FROM maintenance_ftth_tickets t WHERE ' . implode(' AND ', $where));
        $statement->execute($params);

        return array_map('intval', $statement->fetchAll(PDO::FETCH_COLUMN) ?: []);
    }

    private function deleteRaccordementTicketsByIds(array $ticketIds): int
    {
        $ticketIds = $this->parseSelectedRaccordementTicketIds($ticketIds);
        if (empty($ticketIds)) {
            return 0;
        }

        $placeholders = implode(',', array_fill(0, count($ticketIds), '?'));
        $existingStatement = $this->pdo->prepare("SELECT id FROM maintenance_ftth_tickets WHERE id IN ($placeholders)");
        $existingStatement->execute($ticketIds);
        $existingIds = array_map('intval', $existingStatement->fetchAll(PDO::FETCH_COLUMN) ?: []);

        if (empty($existingIds)) {
            return 0;
        }

        $ticketPlaceholders = implode(',', array_fill(0, count($existingIds), '?'));
        $reportStatement = $this->pdo->prepare("SELECT id FROM maintenance_ftth_reports WHERE ticket_id IN ($ticketPlaceholders)");
        $reportStatement->execute($existingIds);
        $reportIds = array_map('intval', $reportStatement->fetchAll(PDO::FETCH_COLUMN) ?: []);

        $this->pdo->beginTransaction();
        try {
            if (!empty($reportIds)) {
                $reportPlaceholders = implode(',', array_fill(0, count($reportIds), '?'));
                $this->pdo->prepare("DELETE FROM maintenance_ftth_photos WHERE report_id IN ($reportPlaceholders)")
                    ->execute($reportIds);
            }

            $this->pdo->prepare("DELETE FROM maintenance_ftth_reports WHERE ticket_id IN ($ticketPlaceholders)")
                ->execute($existingIds);
            $this->pdo->prepare("DELETE FROM maintenance_ftth_assignments WHERE ticket_id IN ($ticketPlaceholders)")
                ->execute($existingIds);
            $this->pdo->prepare("DELETE FROM maintenance_ftth_tickets WHERE id IN ($ticketPlaceholders)")
                ->execute($existingIds);

            $this->pdo->commit();
        } catch (\Throwable $exception) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $exception;
        }

        foreach ($existingIds as $ticketId) {
            $this->removeRaccordementDirectoryRecursive(__DIR__ . '/../../../public/storage/uploads/ftth/' . $ticketId);
        }

        return count($existingIds);
    }

    private function removeRaccordementDirectoryRecursive(string $dirPath): void
    {
        if (!is_dir($dirPath)) {
            return;
        }

        $items = scandir($dirPath);
        if ($items === false) {
            return;
        }

        foreach ($items as $item) {
            if ($item === '.' || $item === '..') {
                continue;
            }

            $fullPath = $dirPath . DIRECTORY_SEPARATOR . $item;
            if (is_dir($fullPath)) {
                $this->removeRaccordementDirectoryRecursive($fullPath);
                continue;
            }

            @unlink($fullPath);
        }

        @rmdir($dirPath);
    }

    public function assign(): void
    {
        $user = $this->requireRoles(['admin', 'agent', 'manager', 'superviseur', 'supervisor']);
        $moduleBasePath = $this->raccordementModuleBasePath();

        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect($moduleBasePath);
            return;
        }

        $ticketId = (int)($_POST['ticket_id'] ?? 0);
        $techIds = array_values(array_unique(array_filter(array_map('intval', (array)($_POST['technician_ids'] ?? [])))));
        $notes = trim((string)($_POST['notes'] ?? ''));

        if ($ticketId <= 0 || empty($techIds)) {
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=missing');
            return;
        }

        if (!$this->canAccessMaintenanceTicket($ticketId, $user, 'assign')) {
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=forbidden');
            return;
        }

        $reportStatement = $this->pdo->prepare(
            'SELECT id, workflow_phase, study_submitted_at, installation_sent_at, installation_technician_id
             FROM maintenance_ftth_reports
             WHERE ticket_id = ? AND id > 0
             ORDER BY COALESCE(updated_at, created_at) DESC, id DESC
             LIMIT 1'
        );
        $reportStatement->execute([$ticketId]);
        $report = $reportStatement->fetch(PDO::FETCH_ASSOC) ?: null;
        $assignableTechnicianType = $this->resolveRaccordementAssignableTechnicianType($report);
        $techIds = $this->filterRaccordementAssignableTechnicianIds([reset($techIds) ?: 0], $report);
        if (empty($techIds)) {
            $technicianLabel = $assignableTechnicianType === 'raccordement' ? 'raccordement' : 'étude';
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('Sélectionnez un technicien ' . $technicianLabel . '.'));
            return;
        }

        if ($assignableTechnicianType === 'raccordement') {
            $this->removeAssignmentsFromTicket($ticketId, null);
        }

        $this->assignTechniciansToTicket($ticketId, $techIds, (int)$user['id'], $notes);

        $reportId = (int)($report['id'] ?? 0);
        if ($assignableTechnicianType === 'raccordement') {
            if ($reportId > 0) {
                $this->pdo->prepare('UPDATE maintenance_ftth_reports SET workflow_phase = ?, installation_technician_id = ? WHERE id = ?')
                    ->execute(['installation', (int)$techIds[0], $reportId]);
            }
            $this->pdo->prepare('UPDATE maintenance_ftth_tickets SET status = ?, avancement = ? WHERE id = ?')
                ->execute(['en_cours', 'Installation assignée', $ticketId]);
            $this->logTicketEvent($ticketId, (int)$user['id'], 'installation_assigned', 'Technicien raccordement assigné', 'Le superviseur a assigné le technicien raccordement pour la phase installation.', 'info', 'user-check');
        } else {
            if ($reportId > 0) {
                $this->pdo->prepare('UPDATE maintenance_ftth_reports SET study_technician_id = COALESCE(study_technician_id, ?), workflow_phase = COALESCE(workflow_phase, "study") WHERE id = ?')
                    ->execute([(int)$techIds[0], $reportId]);
            }
            $this->pdo->prepare('UPDATE maintenance_ftth_tickets SET status = ?, avancement = ? WHERE id = ?')
                ->execute(['assigné', 'Étude assignée', $ticketId]);
            $this->logTicketEvent($ticketId, (int)$user['id'], 'study_assigned', 'Technicien étude assigné', 'Le dossier a été assigné au technicien étude pour préparation.', 'info', 'user-pen');
        }

        $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&assigned=1');
    }

    public function createManual(): void
    {
        $user = $this->requireRoles(['admin', 'agent', 'manager', 'superviseur', 'supervisor']);
        $moduleBasePath = $this->raccordementModuleBasePath();
        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect($moduleBasePath);
            return;
        }

        $refCode = trim((string)($_POST['ref_code'] ?? ''));
        $fixedLine = trim((string)($_POST['fixed_line'] ?? ''));
        $clientCode = trim((string)($_POST['client_code'] ?? ''));
        $clientName = trim((string)($_POST['client_name'] ?? ''));
        $clientPhone = trim((string)($_POST['client_phone'] ?? ''));
        $clientPhone2 = trim((string)($_POST['client_phone2'] ?? ''));
        $clientAddress = trim((string)($_POST['client_address'] ?? ''));
        $raccordementCity = trim((string)($_POST['raccordement_city'] ?? ''));
        $raccordementCommune = trim((string)($_POST['raccordement_commune'] ?? ''));
        $raccordementLatitude = trim((string)($_POST['raccordement_latitude'] ?? ''));
        $raccordementLongitude = trim((string)($_POST['raccordement_longitude'] ?? ''));
        $sroClient = trim((string)($_POST['sro_client'] ?? ''));
        $jdvClient = trim((string)($_POST['jdv_client'] ?? ''));
        $pcoClient = trim((string)($_POST['pco_client'] ?? ''));
        $companyName = trim((string)($_POST['company_name'] ?? ''));
        $raccordementSnont = trim((string)($_POST['raccordement_snont'] ?? ''));
        $raccordementNd = trim((string)($_POST['raccordement_nd'] ?? ''));
        $raccordementDateEnregistrement = trim((string)($_POST['raccordement_date_enregistrement'] ?? ''));
        $raccordementDateEnvoiPrestataires = trim((string)($_POST['raccordement_date_envoi_prestataires'] ?? ''));
        $raccordementDureeAttente = trim((string)($_POST['raccordement_duree_attente'] ?? ''));
        $raccordementTeam = trim((string)($_POST['raccordement_team'] ?? ''));
        $raccordementEtudeTechnique = trim((string)($_POST['raccordement_etude_technique'] ?? ''));
        $avancement = trim((string)($_POST['avancement'] ?? ''));
        $status = $this->normalizeStatus((string)($_POST['status'] ?? 'nouveau'));
        $priority = $this->normalizePriority((string)($_POST['priority'] ?? 'Moyenne'));
        $description = trim((string)($_POST['description'] ?? ''));
        $causeComment = trim((string)($_POST['cause_comment'] ?? ''));
        $dateCloture = trim((string)($_POST['date_cloture'] ?? ''));
        $techIds = $this->filterAssignableTechnicianIds((array)($_POST['technician_ids'] ?? []));
        $notes = trim((string)($_POST['assignment_notes'] ?? ''));

        if ($clientName === '') {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Le nom du client est obligatoire pour la déclaration manuelle.'));
            return;
        }

        if (!$this->requestMatchesAssignedCity($user, $raccordementCity, $raccordementCommune, $clientAddress)) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('La ville saisie ne correspond pas à votre ville de rattachement.'));
            return;
        }

        $clientLat = null;
        $clientLng = null;
        if ($raccordementLatitude !== '' && $raccordementLongitude !== '') {
            $clientLat = str_replace(',', '.', $raccordementLatitude);
            $clientLng = str_replace(',', '.', $raccordementLongitude);
        }

        if ($refCode === '') {
            $refCode = 'MAN-' . date('YmdHis');
        }

        $extraFields = array_filter([
            'source' => 'manual',
            'module' => 'raccordement-clients',
            'NumeroAbonnement' => $clientCode,
            'NomClient' => $clientName,
            'Ville' => $raccordementCity,
            'Commune' => $raccordementCommune,
            'Contact1' => $clientPhone,
            'Contact2' => $clientPhone2,
            'Localisation' => $clientAddress,
            'Latitude_Terrai' => $raccordementLatitude !== '' ? $raccordementLatitude : $clientLat,
            'Longitude_Terrain' => $raccordementLongitude !== '' ? $raccordementLongitude : $clientLng,
            'PLAQUE' => $sroClient,
            'JDV' => $jdvClient,
            'PCO' => $pcoClient,
            'SNONT' => $raccordementSnont,
            'ND' => $raccordementNd,
            'EntrepriseFTTH' => $companyName,
            'Etude_Technique' => $raccordementEtudeTechnique,
            'Commentaires' => $description,
            'Problèmes' => $causeComment,
            'Equipe_raccordement' => $raccordementTeam,
            'Date_Enregistrement_Instances' => $raccordementDateEnregistrement,
            'Date_Envoi_Instances_Prestataires' => $raccordementDateEnvoiPrestataires,
            'Duree_attente_j' => $raccordementDureeAttente,
        ], static fn($value) => $value !== null && $value !== '');

        $statement = $this->pdo->prepare(
            'INSERT INTO maintenance_ftth_tickets
             (ref_code, fixed_line, client_name, client_code, client_phone, client_phone2,
              client_address, client_lat, client_lng, sro_client, jdv_client, pco_client,
              company_name, nature_intervention, description, cause_comment, priority, status,
              avancement, equipes, date_cloture, extra_fields, imported_at, imported_by)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?)'
        );
        $statement->execute([
            $refCode,
            $fixedLine !== '' ? $fixedLine : null,
            $clientName,
            $clientCode !== '' ? $clientCode : null,
            $clientPhone !== '' ? $clientPhone : null,
            $clientPhone2 !== '' ? $clientPhone2 : null,
            $clientAddress !== '' ? $clientAddress : null,
            $clientLat,
            $clientLng,
            $sroClient !== '' ? $sroClient : null,
            $jdvClient !== '' ? $jdvClient : null,
            $pcoClient !== '' ? $pcoClient : null,
            $companyName !== '' ? $companyName : null,
            'Raccordement client',
            $description !== '' ? $description : null,
            $causeComment !== '' ? $causeComment : null,
            $priority,
            $status,
            $avancement !== '' ? $avancement : null,
            null,
            $dateCloture !== '' ? $dateCloture : null,
            json_encode($extraFields, JSON_UNESCAPED_UNICODE),
            (int)$user['id'],
        ]);

        $ticketId = (int)$this->pdo->lastInsertId();
        if (!empty($techIds)) {
            $this->assignTechniciansToTicket($ticketId, [(int)reset($techIds)], (int)$user['id'], $notes);
        }

        $this->redirect($moduleBasePath . '/detail?id=' . $ticketId . '&created=1');
    }

    public function updateManual(): void
    {
        $user = $this->requireRoles(['admin', 'agent', 'manager', 'superviseur', 'supervisor']);
        $moduleBasePath = $this->raccordementModuleBasePath();
        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect($moduleBasePath);
            return;
        }

        $ticketId = (int)($_POST['ticket_id'] ?? 0);
        if ($ticketId <= 0) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Raccordement introuvable pour modification.'));
            return;
        }

        if (!$this->canAccessMaintenanceTicket($ticketId, $user, 'edit')) {
            $this->redirect($moduleBasePath . '?err=forbidden');
            return;
        }

        $ticketStatement = $this->pdo->prepare('SELECT * FROM maintenance_ftth_tickets WHERE id=? LIMIT 1');
        $ticketStatement->execute([$ticketId]);
        $ticket = $ticketStatement->fetch(PDO::FETCH_ASSOC);
        if (!$ticket || !$this->ticketMatchesRaccordementModule($ticket)) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Raccordement introuvable pour modification.'));
            return;
        }

        $refCode = trim((string)($_POST['ref_code'] ?? ''));
        $clientCode = trim((string)($_POST['client_code'] ?? ''));
        $clientName = trim((string)($_POST['client_name'] ?? ''));
        $clientPhone = trim((string)($_POST['client_phone'] ?? ''));
        $clientPhone2 = trim((string)($_POST['client_phone2'] ?? ''));
        $clientAddress = trim((string)($_POST['client_address'] ?? ''));
        $raccordementCity = trim((string)($_POST['raccordement_city'] ?? ''));
        $raccordementCommune = trim((string)($_POST['raccordement_commune'] ?? ''));
        $raccordementLatitude = trim((string)($_POST['raccordement_latitude'] ?? ''));
        $raccordementLongitude = trim((string)($_POST['raccordement_longitude'] ?? ''));
        $sroClient = trim((string)($_POST['sro_client'] ?? ''));
        $jdvClient = trim((string)($_POST['jdv_client'] ?? ''));
        $pcoClient = trim((string)($_POST['pco_client'] ?? ''));
        $companyName = trim((string)($_POST['company_name'] ?? ''));
        $raccordementSnont = trim((string)($_POST['raccordement_snont'] ?? ''));
        $raccordementNd = trim((string)($_POST['raccordement_nd'] ?? ''));
        $raccordementDateEnregistrement = trim((string)($_POST['raccordement_date_enregistrement'] ?? ''));
        $raccordementDateEnvoiPrestataires = trim((string)($_POST['raccordement_date_envoi_prestataires'] ?? ''));
        $raccordementDureeAttente = trim((string)($_POST['raccordement_duree_attente'] ?? ''));
        $raccordementTeam = trim((string)($_POST['raccordement_team'] ?? ''));
        $raccordementEtudeTechnique = trim((string)($_POST['raccordement_etude_technique'] ?? ''));
        $avancement = trim((string)($_POST['avancement'] ?? ''));
        $priority = $this->normalizePriority((string)($_POST['priority'] ?? ($ticket['priority'] ?? 'Moyenne')));
        $status = $this->normalizeStatus((string)($_POST['status'] ?? ($ticket['status'] ?? 'nouveau')));
        $description = trim((string)($_POST['description'] ?? ''));
        $causeComment = trim((string)($_POST['cause_comment'] ?? ''));

        if ($clientName === '') {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Le nom du client est obligatoire pour modifier le raccordement.'));
            return;
        }

        if (!$this->requestMatchesAssignedCity($user, $raccordementCity, $raccordementCommune, $clientAddress)) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('La ville saisie ne correspond pas à votre ville de rattachement.'));
            return;
        }

        $clientLat = null;
        $clientLng = null;
        if ($raccordementLatitude !== '' && $raccordementLongitude !== '') {
            $clientLat = str_replace(',', '.', $raccordementLatitude);
            $clientLng = str_replace(',', '.', $raccordementLongitude);
        }

        $extraFields = [];
        if (!empty($ticket['extra_fields'])) {
            $decoded = json_decode((string)$ticket['extra_fields'], true);
            if (is_array($decoded)) {
                $extraFields = $decoded;
            }
        }

        $extraFields['module'] = 'raccordement-clients';
        $extraFields['NumeroAbonnement'] = $clientCode;
        $extraFields['NomClient'] = $clientName;
        $extraFields['Ville'] = $raccordementCity;
        $extraFields['Commune'] = $raccordementCommune;
        $extraFields['Contact1'] = $clientPhone;
        $extraFields['Contact2'] = $clientPhone2;
        $extraFields['Localisation'] = $clientAddress;
        $extraFields['Latitude_Terrai'] = $raccordementLatitude !== '' ? $raccordementLatitude : ($clientLat ?? '');
        $extraFields['Longitude_Terrain'] = $raccordementLongitude !== '' ? $raccordementLongitude : ($clientLng ?? '');
        $extraFields['PLAQUE'] = $sroClient;
        $extraFields['JDV'] = $jdvClient;
        $extraFields['PCO'] = $pcoClient;
        $extraFields['SNONT'] = $raccordementSnont;
        $extraFields['ND'] = $raccordementNd;
        $extraFields['EntrepriseFTTH'] = $companyName;
        $extraFields['Etude_Technique'] = $raccordementEtudeTechnique;
        $extraFields['Commentaires'] = $description;
        $extraFields['Problèmes'] = $causeComment;
        $extraFields['Equipe_raccordement'] = $raccordementTeam;
        $extraFields['Date_Enregistrement_Instances'] = $raccordementDateEnregistrement;
        $extraFields['Date_Envoi_Instances_Prestataires'] = $raccordementDateEnvoiPrestataires;
        $extraFields['Duree_attente_j'] = $raccordementDureeAttente;

        foreach ($extraFields as $key => $value) {
            if ($value === null || trim((string)$value) !== '') {
                continue;
            }
            unset($extraFields[$key]);
        }

        $this->pdo->prepare(
            'UPDATE maintenance_ftth_tickets
             SET ref_code=?, client_name=?, client_code=?, client_phone=?, client_phone2=?, client_address=?,
                 client_lat=?, client_lng=?, sro_client=?, jdv_client=?, pco_client=?, company_name=?,
                 description=?, cause_comment=?, priority=?, status=?, avancement=?, nature_intervention=?, extra_fields=?
             WHERE id=? LIMIT 1'
        )->execute([
            $refCode !== '' ? $refCode : ($ticket['ref_code'] ?: 'MAN-' . date('YmdHis')),
            $clientName,
            $clientCode !== '' ? $clientCode : null,
            $clientPhone !== '' ? $clientPhone : null,
            $clientPhone2 !== '' ? $clientPhone2 : null,
            $clientAddress !== '' ? $clientAddress : null,
            $this->hasValidCoordinates($clientLat, $clientLng) ? $clientLat : null,
            $this->hasValidCoordinates($clientLat, $clientLng) ? $clientLng : null,
            $sroClient !== '' ? $sroClient : null,
            $jdvClient !== '' ? $jdvClient : null,
            $pcoClient !== '' ? $pcoClient : null,
            $companyName !== '' ? $companyName : null,
            $description !== '' ? $description : null,
            $causeComment !== '' ? $causeComment : null,
            $priority,
            $status,
            $avancement !== '' ? $avancement : null,
            'Raccordement client',
            json_encode($extraFields, JSON_UNESCAPED_UNICODE),
            $ticketId,
        ]);

        $summary = array_values(array_filter([
            $clientName !== '' ? 'Client: ' . $clientName : null,
            $clientCode !== '' ? 'Abonnement: ' . $clientCode : null,
            $avancement !== '' ? 'Avancement: ' . $avancement : null,
            'Priorité: ' . $priority,
            'Statut: ' . $status,
        ]));
        $this->logTicketEvent($ticketId, (int)($user['id'] ?? 0), 'raccordement_updated', 'Raccordement modifié', implode(' • ', $summary), 'info', 'pen-to-square');

        $this->redirect($moduleBasePath . '?updated=1');
    }

    public function requalifyPriority(): void
    {
        $user = $this->requireRoles(['admin', 'agent', 'manager', 'superviseur', 'supervisor']);
        $moduleBasePath = $this->raccordementModuleBasePath();

        if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
            $this->redirect($moduleBasePath);
            return;
        }

        $ticketId = (int)($_POST['ticket_id'] ?? 0);
        $priority = $this->normalizePriority((string)($_POST['priority'] ?? 'Moyenne'));
        if ($ticketId <= 0) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Raccordement introuvable pour requalification.'));
            return;
        }

        if (!$this->canAccessMaintenanceTicket($ticketId, $user, 'edit')) {
            $this->redirect($moduleBasePath . '?err=forbidden');
            return;
        }

        $statement = $this->pdo->prepare('SELECT * FROM maintenance_ftth_tickets WHERE id=? LIMIT 1');
        $statement->execute([$ticketId]);
        $ticket = $statement->fetch(PDO::FETCH_ASSOC);
        if (!$ticket || !$this->ticketMatchesRaccordementModule($ticket)) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Raccordement introuvable pour requalification.'));
            return;
        }

        $this->pdo->prepare('UPDATE maintenance_ftth_tickets SET priority=? WHERE id=? LIMIT 1')->execute([$priority, $ticketId]);
        $this->logTicketEvent($ticketId, (int)($user['id'] ?? 0), 'priority_requalified', 'Criticité requalifiée', 'Nouvelle criticité : ' . $priority, 'danger', 'triangle-exclamation');

        $this->redirect($moduleBasePath . '?priority_updated=1');
    }

    public function webReportSave(): void
    {
        $user = $this->requireAuth();
        $moduleBasePath = $this->raccordementModuleBasePath();
        $ticketId = (int)($_POST['ticket_id'] ?? 0);
        $actionType = in_array($_POST['action_type'] ?? '', ['draft', 'submit', 'handoff'], true) ? $_POST['action_type'] : 'draft';

        if ($actionType === 'handoff') {
            $this->logRaccordementRuntime('handoff_request_received', [
                'ticket_id' => $ticketId,
                'user_id' => (int)($user['id'] ?? 0),
                'role_key' => (string)($user['role_key'] ?? ''),
                'selected_otb_id' => (int)($_POST['selected_otb_id'] ?? 0),
                'selected_otb_port_number' => (int)($_POST['selected_otb_port_number'] ?? 0),
                'study_gps_lat' => $_POST['study_gps_lat'] ?? null,
                'study_gps_lng' => $_POST['study_gps_lng'] ?? null,
            ]);
        }

        if ($ticketId <= 0) {
            $this->redirect($moduleBasePath);
            return;
        }

        if (!$this->canAccessMaintenanceTicket($ticketId, $user, 'view')) {
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=forbidden');
            return;
        }

        $isAdmin = in_array($user['role_key'] ?? '', ['admin', 'agent', 'manager', 'superviseur', 'supervisor'], true);
        if (!$isAdmin) {
            $check = $this->pdo->prepare('SELECT 1 FROM maintenance_ftth_assignments WHERE ticket_id=? AND technician_id=? LIMIT 1');
            $check->execute([$ticketId, (int)$user['id']]);
            if (!$check->fetchColumn()) {
                $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=forbidden');
                return;
            }
        }

        $ticketStatement = $this->pdo->prepare('SELECT * FROM maintenance_ftth_tickets WHERE id=? LIMIT 1');
        $ticketStatement->execute([$ticketId]);
        $ticket = $ticketStatement->fetch(PDO::FETCH_ASSOC) ?: null;
        if (!$ticket || !$this->ticketMatchesRaccordementModule($ticket)) {
            $this->redirect($moduleBasePath . '?err=' . urlencode('Dossier introuvable.'));
            return;
        }

        $existingStatement = $this->pdo->prepare(
            'SELECT id, intervention_date, intervention_start, intervention_end, workflow_phase, selected_otb_id, selected_otb_port_number, selected_otb_port_status, study_technician_id, installation_technician_id, study_submitted_at, installation_sent_at
             FROM maintenance_ftth_reports
             WHERE ticket_id = ? AND id > 0
             ORDER BY COALESCE(updated_at, created_at) DESC, id DESC
             LIMIT 1'
        );
        $existingStatement->execute([$ticketId]);
        $existingReport = $existingStatement->fetch(PDO::FETCH_ASSOC) ?: null;

        if ($actionType === 'handoff') {
            $this->logRaccordementRuntime('handoff_existing_report', [
                'ticket_id' => $ticketId,
                'existing_report_id' => (int)($existingReport['id'] ?? 0),
                'existing_status' => (string)($existingReport['status'] ?? ''),
                'existing_phase' => (string)($existingReport['workflow_phase'] ?? ''),
                'existing_study_submitted_at' => $existingReport['study_submitted_at'] ?? null,
                'existing_installation_sent_at' => $existingReport['installation_sent_at'] ?? null,
            ]);
        }

        if ($actionType === 'handoff' && !$this->canEditRaccordementStudyPhase($user, $existingReport)) {
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('Seul le technicien étude peut transmettre ce dossier.'));
            return;
        }
        if ((($existingReport['workflow_phase'] ?? 'study') !== 'study') && $actionType !== 'handoff' && !$this->canEditRaccordementInstallationPhase($user, $existingReport)) {
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('Cette phase est réservée au technicien terrain en charge du raccordement.'));
            return;
        }

        $destDir = $this->uploadDir($ticketId);
        $operationType = in_array($_POST['operation_type'] ?? '', ['maintenance', 'travaux', 'autres'], true) ? $_POST['operation_type'] : 'maintenance';
        $interventionDate = $_POST['intervention_date'] ?? null;
        $interventionStart = $_POST['intervention_start'] ?? null;
        $interventionEnd = $_POST['intervention_end'] ?? null;
        $constatComments = trim((string)($_POST['constat_comments'] ?? ''));
        $impactedEquipment = json_decode($_POST['impacted_equipment'] ?? '[]', true) ?: [];
        $materialsUsed = json_decode($_POST['materials_used'] ?? '[]', true) ?: [];
        $generalComments = trim((string)($_POST['general_comments'] ?? ''));
        $selectedOtbId = (int)($_POST['selected_otb_id'] ?? ($existingReport['selected_otb_id'] ?? 0));
        $selectedOtbPortNumber = (int)($_POST['selected_otb_port_number'] ?? ($existingReport['selected_otb_port_number'] ?? 0));
        $recommendedOtbId = (int)($_POST['recommended_otb_id'] ?? 0);
        $otbDistanceMeters = is_numeric($_POST['otb_distance_meters'] ?? null) ? round((float)$_POST['otb_distance_meters'], 2) : null;
        $otbSelectionMode = trim((string)($_POST['otb_selection_mode'] ?? ($selectedOtbId > 0 ? 'manual' : 'none')));
        if (!in_array($otbSelectionMode, ['auto', 'manual', 'none'], true)) {
            $otbSelectionMode = 'none';
        }
        $selectedOtb = $selectedOtbId > 0 ? RaccordementOtbService::findBoxById($this->pdo, $selectedOtbId) : null;
        $recommendedOtb = $recommendedOtbId > 0 ? RaccordementOtbService::findBoxById($this->pdo, $recommendedOtbId) : null;
        $selectedOtbLabel = $selectedOtb['box_code'] ?? null;
        $recommendedOtbLabel = $recommendedOtb['box_code'] ?? null;

        if ($otbSelectionMode === 'auto') {
            $autoRecommendation = null;
            if ($this->hasValidCoordinates($studyGpsLat, $studyGpsLng)) {
                $autoRecommendation = RaccordementOtbService::buildRecommendation(
                    $this->pdo,
                    $studyGpsLat,
                    $studyGpsLng,
                    (string)($ticket['dispatch_zone_key'] ?? '')
                );
            }

            $recommendedCandidate = is_array($autoRecommendation['recommended'] ?? null)
                ? $autoRecommendation['recommended']
                : $recommendedOtb;

            if (is_array($recommendedCandidate) && (int)($recommendedCandidate['id'] ?? 0) > 0) {
                $recommendedOtbId = (int)$recommendedCandidate['id'];
                $recommendedOtb = RaccordementOtbService::findBoxById($this->pdo, $recommendedOtbId);
                $recommendedOtbLabel = $recommendedOtb['box_code'] ?? ($recommendedCandidate['box_code'] ?? null);
                $otbDistanceMeters = is_numeric($recommendedCandidate['distance_meters'] ?? null)
                    ? round((float)$recommendedCandidate['distance_meters'], 2)
                    : $otbDistanceMeters;
                $selectedOtbId = $recommendedOtbId;
                $selectedOtb = $recommendedOtb;
                $selectedOtbLabel = $recommendedOtbLabel;
            }

            if ($selectedOtbId > 0) {
                $availablePorts = RaccordementOtbService::getAvailablePorts($this->pdo, $selectedOtbId, $existingId > 0 ? $existingId : null);
                $selectedPortStillAvailable = false;
                $firstAvailablePortNumber = 0;

                foreach ($availablePorts as $port) {
                    if (!empty($port['available']) && $firstAvailablePortNumber <= 0) {
                        $firstAvailablePortNumber = (int)($port['number'] ?? 0);
                    }
                    if ((int)($port['number'] ?? 0) === $selectedOtbPortNumber && !empty($port['available'])) {
                        $selectedPortStillAvailable = true;
                    }
                }

                if (!$selectedPortStillAvailable) {
                    $selectedOtbPortNumber = $firstAvailablePortNumber;
                }
            }
        }

        $allowedSatisfaction = ['très_satisfait', 'satisfait', 'peu_satisfait', 'non_satisfait'];
        $satisfactionService = in_array($_POST['satisfaction_service'] ?? '', $allowedSatisfaction, true) ? $_POST['satisfaction_service'] : null;
        $satisfactionDelay = in_array($_POST['satisfaction_delay'] ?? '', $allowedSatisfaction, true) ? $_POST['satisfaction_delay'] : null;
        $satisfactionQuality = in_array($_POST['satisfaction_quality'] ?? '', $allowedSatisfaction, true) ? $_POST['satisfaction_quality'] : null;
        $satisfactionBehavior = in_array($_POST['satisfaction_behavior'] ?? '', $allowedSatisfaction, true) ? $_POST['satisfaction_behavior'] : null;

        $clientNameOnsite = trim((string)($_POST['client_name_onsite'] ?? ''));
        $clientVisitStatus = $this->normalizeClientVisitStatus($_POST['client_visit_status'] ?? null);
        $plannedInterventionDate = $this->normalizeOptionalDateValue($_POST['planned_intervention_date'] ?? null);
        $plannedInterventionTime = $this->normalizeOptionalTimeValue($_POST['planned_intervention_time'] ?? null);
        $studyDispatchComment = trim((string)($_POST['study_dispatch_comment'] ?? ''));
        $planningComment = trim((string)($_POST['planning_comment'] ?? ''));
        $clientRemarks = trim((string)($_POST['client_remarks'] ?? ''));
        $serviceRestored = (int)($_POST['service_restored'] ?? 0);

        if ($clientVisitStatus === 'reprogrammation_demandee' && (!$plannedInterventionDate || !$plannedInterventionTime)) {
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('Veuillez renseigner la date et l\'heure de replanification.'));
            return;
        }

        $clientSignaturePath = null;
        $agentSignaturePath = null;
        if (!empty($_POST['client_signature_data'])) {
            $clientSignaturePath = $this->saveBase64Image((string)$_POST['client_signature_data'], $destDir, 'sig_client');
        }
        if (!empty($_POST['agent_signature_data'])) {
            $agentSignaturePath = $this->saveBase64Image((string)$_POST['agent_signature_data'], $destDir, 'sig_agent');
        }

        $gpsLat = $_POST['gps_lat'] ?? null;
        $gpsLng = $_POST['gps_lng'] ?? null;
        $gpsAccuracyMeters = is_numeric($_POST['gps_accuracy_meters'] ?? null) ? round((float)$_POST['gps_accuracy_meters'], 2) : null;
        $studyGpsLat = is_numeric($_POST['study_gps_lat'] ?? null) ? (float)$_POST['study_gps_lat'] : null;
        $studyGpsLng = is_numeric($_POST['study_gps_lng'] ?? null) ? (float)$_POST['study_gps_lng'] : null;
        $studyGpsAccuracyMeters = is_numeric($_POST['study_gps_accuracy_meters'] ?? null) ? round((float)$_POST['study_gps_accuracy_meters'], 2) : null;

        $workflow = $this->resolveFtthWorkflow($clientVisitStatus, $actionType);
        $statusNew = $workflow['report_status'];
        $existingId = (int)($existingReport['id'] ?? 0);
        $now = new \DateTimeImmutable('now');
        $interventionDate = trim((string)$interventionDate) !== '' ? $interventionDate : ($existingReport['intervention_date'] ?? $now->format('Y-m-d'));
        $interventionStart = trim((string)$interventionStart) !== '' ? $interventionStart : ($existingReport['intervention_start'] ?? $now->format('H:i:s'));
        if ($actionType === 'submit') {
            $interventionEnd = $now->format('H:i:s');
        } elseif (trim((string)$interventionEnd) === '' && !empty($existingReport['intervention_end'])) {
            $interventionEnd = $existingReport['intervention_end'];
        }

        $workflowPhase = (string)($existingReport['workflow_phase'] ?? 'study');
        $studyTechnicianId = (int)($existingReport['study_technician_id'] ?? 0);
        $installationTechnicianId = (int)($existingReport['installation_technician_id'] ?? 0);
        $studySubmittedAt = $existingReport['study_submitted_at'] ?? null;
        $installationSentAt = $existingReport['installation_sent_at'] ?? null;
        $selectedOtbPortStatus = (string)($existingReport['selected_otb_port_status'] ?? 'none');
        if ($actionType === 'handoff') {
            if ($studyDispatchComment !== '') {
                $planningComment = $studyDispatchComment;
            }
            if ($selectedOtbId <= 0 || !$selectedOtb) {
                $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('Sélectionnez un boîtier intelligent avant transmission.'));
                return;
            }
            if ($selectedOtbPortNumber <= 0) {
                $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('Réservez un port actif avant transmission.'));
                return;
            }
            if (!$this->hasValidCoordinates($studyGpsLat, $studyGpsLng)) {
                $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('La position étude doit être capturée avant transmission.'));
                return;
            }

            $availablePorts = RaccordementOtbService::getAvailablePorts($this->pdo, $selectedOtbId, $existingId > 0 ? $existingId : null);
            $selectedPort = null;
            foreach ($availablePorts as $port) {
                if ((int)($port['number'] ?? 0) === $selectedOtbPortNumber) {
                    $selectedPort = $port;
                    break;
                }
            }
            if (!$selectedPort || empty($selectedPort['available'])) {
                $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('Le port sélectionné n\'est plus disponible.'));
                return;
            }

            $supervisorIds = $this->resolveValidationRecipientIds($ticketId);
            if (empty($supervisorIds)) {
                $this->logRaccordementRuntime('handoff_no_supervisor', [
                    'ticket_id' => $ticketId,
                    'user_id' => (int)($user['id'] ?? 0),
                ]);
                $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('Aucun superviseur raccordement n\'est disponible pour recevoir cette transmission.'));
                return;
            }

            $workflowPhase = 'study';
            $studyTechnicianId = (int)($user['id'] ?? 0);
            $installationTechnicianId = 0;
            $studySubmittedAt = $now->format('Y-m-d H:i:s');
            $installationSentAt = null;
            $selectedOtbPortStatus = 'reserved';
        }

        $markSubmitted = $workflow['mark_submitted'] || $actionType === 'handoff';

        if ($existingId <= 0) {
            $existingId = $this->ensureWebReportDraftExists($ticketId, (int)($user['id'] ?? 0));
            if ($actionType === 'handoff') {
                $this->logRaccordementRuntime('handoff_draft_ensured', [
                    'ticket_id' => $ticketId,
                    'report_id' => $existingId,
                ]);
            }
        }

        $this->pdo->prepare(
            'UPDATE maintenance_ftth_reports SET
                operation_type=?, intervention_date=?, intervention_start=?, intervention_end=?,
                constat_comments=?, impacted_equipment=?, materials_used=?,
                selected_otb_id=?, selected_otb_port_number=?, selected_otb_port_status=?, selected_otb_label=?,
                recommended_otb_id=?, recommended_otb_label=?, otb_distance_meters=?, otb_selection_mode=?, general_comments=?,
                satisfaction_service=?, satisfaction_delay=?, satisfaction_quality=?, satisfaction_behavior=?,
                client_name_onsite=?, client_visit_status=?, planned_intervention_date=?, planned_intervention_time=?, planning_comment=?, client_remarks=?, service_restored=?,
                submit_gps_lat=?, submit_gps_lng=?, submit_gps_accuracy_meters=?, status=?, workflow_phase=?, study_technician_id=?, installation_technician_id=?, study_submitted_at=?, installation_sent_at=?, study_gps_lat=?, study_gps_lng=?, study_gps_accuracy_meters=?,
                      submitted_at=IF(?,NOW(),submitted_at), updated_at=NOW()
             WHERE id=?'
        )->execute([
            $operationType, $interventionDate ?: null, $interventionStart ?: null, $interventionEnd ?: null,
            $constatComments, json_encode($impactedEquipment, JSON_UNESCAPED_UNICODE), json_encode($materialsUsed, JSON_UNESCAPED_UNICODE),
            $selectedOtbId > 0 ? $selectedOtbId : null, $selectedOtbPortNumber > 0 ? $selectedOtbPortNumber : null, $selectedOtbPortStatus, $selectedOtbLabel,
            $recommendedOtbId > 0 ? $recommendedOtbId : null, $recommendedOtbLabel, $otbDistanceMeters, $otbSelectionMode, $generalComments,
            $satisfactionService, $satisfactionDelay, $satisfactionQuality, $satisfactionBehavior,
            $clientNameOnsite, $clientVisitStatus, $plannedInterventionDate, $plannedInterventionTime, $planningComment, $clientRemarks, $serviceRestored,
            $gpsLat ?: null, $gpsLng ?: null, $gpsAccuracyMeters, $statusNew, $workflowPhase, $studyTechnicianId > 0 ? $studyTechnicianId : null, $installationTechnicianId > 0 ? $installationTechnicianId : null, $studySubmittedAt, $installationSentAt, $studyGpsLat, $studyGpsLng, $studyGpsAccuracyMeters,
            $markSubmitted ? 1 : 0,
            $existingId,
        ]);
        if ($clientSignaturePath) {
            $this->pdo->prepare('UPDATE maintenance_ftth_reports SET client_signature_path=? WHERE id=?')->execute([$clientSignaturePath, $existingId]);
        }
        if ($agentSignaturePath) {
            $this->pdo->prepare('UPDATE maintenance_ftth_reports SET agent_signature_path=? WHERE id=?')->execute([$agentSignaturePath, $existingId]);
        }
        $reportId = $existingId;

        $persistedReportStatement = $this->pdo->prepare(
            'SELECT id, status, workflow_phase
             FROM maintenance_ftth_reports
             WHERE id = ? AND ticket_id = ?
             LIMIT 1'
        );
        $persistedReportStatement->execute([$reportId, $ticketId]);
        $persistedReport = $persistedReportStatement->fetch(PDO::FETCH_ASSOC) ?: null;
        if ($actionType === 'handoff') {
            $this->logRaccordementRuntime('handoff_persisted_report', [
                'ticket_id' => $ticketId,
                'report_id' => $reportId,
                'persisted' => $persistedReport,
            ]);
        }
        if (!$persistedReport) {
            error_log('[raccordement:webReportSave] Report persistence failed for ticket ' . $ticketId . ' after save');
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('La fiche n\'a pas pu être enregistrée.'));
            return;
        }

        if ($actionType === 'handoff'
            && (((string)($persistedReport['status'] ?? '')) !== 'soumis'
                || ((string)($persistedReport['workflow_phase'] ?? '')) !== 'study')) {
            error_log('[raccordement:webReportSave] Invalid handoff state for ticket ' . $ticketId . ' => status=' . (string)($persistedReport['status'] ?? '') . ', phase=' . (string)($persistedReport['workflow_phase'] ?? ''));
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&err=' . urlencode('La fiche étude n\'a pas atteint l\'état de validation attendu.'));
            return;
        }

        $this->saveReportPhotos($reportId, $ticketId, $destDir);

        if ($actionType === 'handoff') {
            $supervisorIds = $this->resolveValidationRecipientIds($ticketId);
            $this->removeAssignmentsFromTicket($ticketId, null);
            $this->assignTechniciansToTicket($ticketId, $supervisorIds, (int)$user['id'], 'Transmission étude vers supervision raccordement · Boîtier ' . ($selectedOtbLabel ?: ('#' . $selectedOtbId)) . ' · Port ' . $selectedOtbPortNumber);
            $this->pdo->prepare('UPDATE maintenance_ftth_tickets SET status=?, avancement=? WHERE id=?')
                ->execute(['attente_planification', 'Étude en attente de validation raccordement', $ticketId]);
            $this->logRaccordementRuntime('handoff_redirect_success', [
                'ticket_id' => $ticketId,
                'report_id' => $reportId,
                'supervisor_ids' => $supervisorIds,
                'redirect' => $this->detailPathForModule($ticketId, $moduleBasePath) . '&transmitted=1',
            ]);
            $this->logTicketEvent($ticketId, (int)($user['id'] ?? 0), 'study_transmitted', 'Étude transmise au superviseur raccordement', 'Boîtier ' . ($selectedOtbLabel ?: ('#' . $selectedOtbId)) . ' · Port réservé ' . $selectedOtbPortNumber, 'info', 'share-nodes');
            $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . '&transmitted=1');
            return;
        }

        if (in_array($clientVisitStatus, ['client_indisponible', 'reprogrammation_demandee'], true)) {
            $this->pdo->prepare('UPDATE maintenance_ftth_tickets SET status=? WHERE id=?')->execute([$workflow['ticket_status'], $ticketId]);
            $this->notifyAssignmentOwnersForPlanningUpdate($ticketId, $user, $clientVisitStatus, $plannedInterventionDate, $plannedInterventionTime, $planningComment);
        } elseif ($actionType === 'submit') {
            $progressValue = 'Installation soumise';
            if ($clientVisitStatus === 'reprogrammation_demandee') {
                $progressValue = 'Reprogrammation demandée';
            } elseif ($clientVisitStatus === 'client_indisponible') {
                $progressValue = 'Client indisponible';
            } elseif ($serviceRestored === 1) {
                $progressValue = 'Installation réalisée';
            } elseif ($serviceRestored === 0) {
                $progressValue = 'Installation non aboutie';
            }
            $this->pdo->prepare('UPDATE maintenance_ftth_tickets SET status=?, avancement=? WHERE id=?')->execute(['traité', $progressValue, $ticketId]);
            if ($selectedOtbId > 0) {
                RaccordementOtbService::recomputeBoxOccupancy($this->pdo, $selectedOtbId);
            }
            $supervisors = $this->resolveValidationRecipientIds($ticketId);
            if (!empty($supervisors)) {
                $clientNameStatement = $this->pdo->prepare('SELECT client_name FROM maintenance_ftth_tickets WHERE id=?');
                $clientNameStatement->execute([$ticketId]);
                $clientName = $clientNameStatement->fetchColumn();
                Notifier::notifyUsers(
                    $this->pdo,
                    array_map('intval', $supervisors),
                    '[Raccordement] Rapport soumis',
                    $user['name'] . ' a soumis le rapport pour le client ' . $clientName . '. En attente de validation.',
                    $this->detailPathForModule($ticketId, $moduleBasePath)
                );
            }
        } else {
            $this->pdo->prepare('UPDATE maintenance_ftth_tickets SET status=? WHERE id=?')->execute(['en_cours', $ticketId]);
        }

        $this->redirect($this->detailPathForModule($ticketId, $moduleBasePath) . ($actionType === 'submit' ? '&submitted=1' : '&saved=1'));
    }
}