<?php
namespace App\Controllers;

use App\Core\Controller;
use App\Core\Auth;
use App\Core\Database;

class DashboardController extends Controller
{
    public function index(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        $pdo = Database::pdo();
        
        // Récupérer l'utilisateur connecté et son rôle
        $currentUser = Auth::user();
        $userRole = $currentUser['role_key'] ?? '';
        $userId = (int)($currentUser['id'] ?? 0);
        $assignedCity = normalize_assigned_city_value($currentUser['assigned_city'] ?? null);
        $ftthCityCondition = function (string $alias = 't') use ($assignedCity): string {
            return $assignedCity !== null ? ' AND ' . assigned_city_sql_condition($alias) : '';
        };
        $ftthCityParams = function () use ($assignedCity): array {
            return $assignedCity !== null ? assigned_city_sql_params($assignedCity) : [];
        };
        $incidentListScope = Auth::permissionScope('incidents', 'list');
        $hasAssignments = false;
        try {
            $hasAssignments = (bool)$pdo->query("SHOW TABLES LIKE 'incident_assignments'")->fetchColumn();
        } catch (\Throwable $e) {
            $hasAssignments = false;
        }
        
        // Définir les filtres selon le rôle
        $whereClause = '';
        $joinClause = '';
        $params = [];
        
        if ($incidentListScope === 'own') {
            if ($userRole === 'technicien' && $hasAssignments) {
                $whereClause = 'WHERE (i.declared_by = ? OR EXISTS (SELECT 1 FROM incident_assignments ia WHERE ia.incident_id = i.id AND ia.user_id = ? AND (ia.active IS NULL OR ia.active = 1)))';
                $params = [$userId, $userId];
            } else {
                $whereClause = 'WHERE i.declared_by = ?';
                $params = [$userId];
            }
        } elseif ($userRole === 'technicien') {
            if ($hasAssignments) {
                $whereClause = 'WHERE EXISTS (SELECT 1 FROM incident_assignments ia WHERE ia.incident_id = i.id AND ia.user_id = ? AND (ia.active IS NULL OR ia.active = 1))';
                $params = [$userId];
            }
        } elseif ($userRole === 'superviseur' || $userRole === 'supervisor') {
            // Superviseur: voir les incidents de ses techniciens ou de sa région
            // Pour l'instant, on montre tous les incidents (à adapter selon l'organisation)
            $whereClause = '';
        } elseif ($userRole === 'client') {
            // Client: voir uniquement ses propres incidents
            $whereClause = 'WHERE i.client_id = ?';
            $params = [$currentUser['client_id'] ?? $userId];
        }
        // Admin et agent voient tout (pas de filtre)

        // Période du dashboard
        $allowedPeriods = [7 => '7 jours', 30 => '30 jours', 90 => '3 mois', 180 => '6 mois', 365 => '1 an'];
        $rawPeriod      = $_GET['period'] ?? '30';
        $isCustom       = ($rawPeriod === 'custom');
        $chartPeriod    = $isCustom ? 'custom' : (int)$rawPeriod;
        if (!$isCustom && !array_key_exists($chartPeriod, $allowedPeriods)) {
            $chartPeriod = 30;
        }

        $dateTo   = date('Y-m-d');
        $dateFrom = date('Y-m-d', strtotime('-30 days'));

        if ($isCustom
            && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['date_from'] ?? '')
            && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['date_to'] ?? '')) {
            $dateFrom = $_GET['date_from'];
            $dateTo   = min($_GET['date_to'], date('Y-m-d'));
        } elseif (!$isCustom) {
            $dateFrom = date('Y-m-d', strtotime("-$chartPeriod days"));
        }

        $dtFrom = $dateFrom . ' 00:00:00';
        $dtTo   = $dateTo . ' 23:59:59';

        $appendCondition = static function (string $clause, string $condition): string {
            return $clause . ($clause ? ' AND ' : ' WHERE ') . $condition;
        };

        $incidentDeclaredWhere = $appendCondition($whereClause, 'i.declared_at BETWEEN ? AND ?');
        $incidentResolvedWhere = $appendCondition($whereClause, 'i.resolved_at BETWEEN ? AND ?');
        $incidentUpdatedWhere  = $appendCondition($whereClause, 'COALESCE(i.updated_at, i.declared_at) BETWEEN ? AND ?');
        $incidentExpectedWhere = $appendCondition($whereClause, 'i.expected_resolution_at BETWEEN ? AND ?');
        $scopedParamsWithPeriod = array_merge($params, [$dtFrom, $dtTo]);
        
        // Préparer des métriques basiques avec garde-fou
        $stats = [
            'open' => 0,
            'in_progress' => 0,
            'resolved' => 0,
            'planned_tasks' => 0,
            'today_tickets' => 0,
            'sla_breached' => 0,
            'active_techs' => 0,
            'impacted_sites' => 0,
        ];
        $recentIncidents = [];
        $ftthStats = [
            'assigned' => 0,
            'in_progress' => 0,
            'to_fill' => 0,
            'awaiting_validation' => 0,
        ];
        $b2bMetrics = [
            'total_tickets' => 0,
            'unassigned' => 0,
            'in_progress' => 0,
            'awaiting_validation' => 0,
            'urgent_open' => 0,
            'validated_today' => 0,
            'service_restored_rate' => 0,
            'avg_assignment_delay_hours' => 0,
        ];
        $eInterventionMetrics = [
            'total_requests' => 0,
            'requests_today' => 0,
            'pending_intake' => 0,
            'assigned_field' => 0,
            'resolved' => 0,
        ];
        $topTechnicians = [];
        $myTechnicianPerformance = [
            'backbone_total' => 0,
            'backbone_in_progress' => 0,
            'backbone_closed' => 0,
            'ftth_total' => 0,
            'ftth_in_progress' => 0,
            'ftth_closed' => 0,
            'score' => 0,
        ];
        $technicianPerformanceChart = [
            'labels' => [],
            'incident_scores' => [],
            'ftth_scores' => [],
            'total_scores' => [],
        ];
        $b2bDistributions = [
            'priority' => [],
            'status' => [],
            'nature' => [],
            'clients' => [],
        ];
        $moduleMetrics = [
            'raccordement_total' => 0,
            'raccordement_pending_study' => 0,
            'raccordement_pending_installation' => 0,
            'raccordement_waiting_validation' => 0,
            'raccordement_validated' => 0,
            'otb_total_boxes' => 0,
            'otb_active_boxes' => 0,
            'otb_alert_boxes' => 0,
            'otb_saturated_boxes' => 0,
            'otb_occupancy_rate' => 0.0,
        ];
        $ftthTickets = [];
        $raccordementWhereSql = "(
            LOWER(COALESCE(t.nature_intervention, '')) LIKE '%raccordement%'
            OR LOWER(COALESCE(t.description, '')) LIKE '%numeroabonnement%'
            OR LOWER(COALESCE(t.extra_fields, '')) LIKE '%equipe_raccordement%'
            OR LOWER(COALESCE(t.extra_fields, '')) LIKE '%\"module\":\"raccordement-clients\"%'
        )";
        try {
            // Incidents ouverts (non résolus) - filtré selon l'utilisateur
             $sql = "SELECT COUNT(*) FROM incidents i $joinClause $incidentDeclaredWhere" .
                 " AND i.resolved_at IS NULL";
            $stmt = $pdo->prepare($sql);
             $stmt->execute($scopedParamsWithPeriod);
            $stats['open'] = (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {}
        try {
            // Tentative via statut clé (in_progress) - filtré selon l'utilisateur
            $statusJoin = 'JOIN incident_statuses s ON i.status_id=s.id';
             $sql = "SELECT COUNT(*) FROM incidents i $joinClause $statusJoin $incidentDeclaredWhere" .
                 " AND s.key_name = 'in_progress'";
            $stmt = $pdo->prepare($sql);
             $stmt->execute($scopedParamsWithPeriod);
            $stats['in_progress'] = (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {}
        try {
            // Incidents résolus - filtré selon l'utilisateur
             $sql = "SELECT COUNT(*) FROM incidents i $joinClause $incidentResolvedWhere" .
                 " AND i.resolved_at IS NOT NULL";
            $stmt = $pdo->prepare($sql);
             $stmt->execute($scopedParamsWithPeriod);
            $stats['resolved'] = (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {}
        try {
            // Tâches planifiées (si la table existe)
            $stats['planned_tasks'] = (int)$pdo->query("SELECT COUNT(*) FROM planning_events WHERE start_datetime >= CURDATE()")->fetchColumn();
        } catch (\Throwable $e) {
            try {
                // Fallback avec d'autres noms possibles
                $stats['planned_tasks'] = (int)$pdo->query("SELECT COUNT(*) FROM planning WHERE start_date >= CURDATE()")->fetchColumn();
            } catch (\Throwable $e2) {}
        }
        try {
            // Incidents d'aujourd'hui - filtré selon l'utilisateur
             $sql = "SELECT COUNT(*) FROM incidents i $joinClause $incidentDeclaredWhere" .
                 " AND DATE(i.declared_at)=CURDATE()";
            $stmt = $pdo->prepare($sql);
             $stmt->execute($scopedParamsWithPeriod);
            $stats['today_tickets'] = (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {}
        try {
            // SLA breach - incidents avec délai dépassé - filtré selon l'utilisateur
             $sql = "SELECT COUNT(*) FROM incidents i $joinClause $incidentExpectedWhere" .
                 " AND i.expected_resolution_at IS NOT NULL AND i.expected_resolution_at < NOW() AND i.resolved_at IS NULL";
            $stmt = $pdo->prepare($sql);
             $stmt->execute($scopedParamsWithPeriod);
            $stats['sla_breached'] = (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {}
        try {
            // Techniciens actifs - selon le contexte utilisateur
            if ($userRole === 'technicien') {
                // Pour un technicien, afficher seulement s'il est actif
                $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE id = ? AND active = 1");
                $stmt->execute([$userId]);
                $stats['active_techs'] = (int)$stmt->fetchColumn();
            } else {
                // Pour les autres rôles, afficher tous les techniciens actifs
                try {
                    $stmt = $pdo->query("SELECT COUNT(*) FROM users u JOIN roles r ON u.role_id = r.id WHERE r.key_name = 'technicien' AND u.active = 1");
                    $stats['active_techs'] = (int)$stmt->fetchColumn();
                } catch (\Throwable $e2) {
                    $stats['active_techs'] = (int)$pdo->query("SELECT COUNT(*) FROM users WHERE role_key = 'technicien' AND active = 1")->fetchColumn();
                }
            }
        } catch (\Throwable $e) {}
        try {
            // Sites impactés distincts - filtré selon l'utilisateur
             $sql = "SELECT COUNT(DISTINCT i.location_id) FROM incidents i $joinClause $incidentDeclaredWhere" .
                 " AND i.resolved_at IS NULL AND i.location_id IS NOT NULL";
            $stmt = $pdo->prepare($sql);
             $stmt->execute($scopedParamsWithPeriod);
            $stats['impacted_sites'] = (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {}
        try {
            // Incidents récents backbone - filtré selon l'utilisateur
             $sql = "SELECT i.id, i.ticket_id, i.title, i.client_id, i.priority, i.status_id, i.updated_at, i.declared_at, 'backbone' AS _source " .
                 "FROM incidents i $joinClause $incidentUpdatedWhere " .
                   "ORDER BY COALESCE(i.updated_at, i.declared_at) DESC LIMIT 5";
            $stmt = $pdo->prepare($sql);
             $stmt->execute($scopedParamsWithPeriod);
            $recentIncidents = $stmt->fetchAll();
        } catch (\Throwable $e) {}

        // Fusionner les tickets FTTH récents
        try {
            if ($userRole === 'technicien') {
                $ftthRecentSql = "SELECT t.id,
                    t.ref_code AS ticket_id,
                    COALESCE(NULLIF(TRIM(t.site_name),''), t.nature_intervention, 'Ticket FTTH') AS title,
                    NULL AS client_id, t.priority, NULL AS status_id,
                    COALESCE(t.updated_at, t.created_at) AS updated_at,
                    t.created_at AS declared_at,
                    'ftth' AS _source
                FROM maintenance_ftth_tickets t
                JOIN maintenance_ftth_assignments a ON a.ticket_id = t.id AND a.technician_id = ?
                WHERE COALESCE(t.updated_at, t.created_at) BETWEEN ? AND ?
                  " . $ftthCityCondition('t') . "
                ORDER BY COALESCE(t.updated_at, t.created_at) DESC LIMIT 5";
                $stmtFtthR = $pdo->prepare($ftthRecentSql);
                $stmtFtthR->execute(array_merge([$userId, $dtFrom, $dtTo], $ftthCityParams()));
            } else {
                $ftthRecentSql = "SELECT t.id,
                    t.ref_code AS ticket_id,
                    COALESCE(NULLIF(TRIM(t.site_name),''), t.nature_intervention, 'Ticket FTTH') AS title,
                    NULL AS client_id, t.priority, NULL AS status_id,
                    COALESCE(t.updated_at, t.created_at) AS updated_at,
                    t.created_at AS declared_at,
                    'ftth' AS _source,
                    t.status AS ftth_status
                FROM maintenance_ftth_tickets t
                WHERE COALESCE(t.updated_at, t.created_at) BETWEEN ? AND ?
                  " . $ftthCityCondition('t') . "
                ORDER BY COALESCE(t.updated_at, t.created_at) DESC LIMIT 5";
                $stmtFtthR = $pdo->prepare($ftthRecentSql);
                $stmtFtthR->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
            }
            $recentFtth = $stmtFtthR->fetchAll();

            // Ajouter le statut FTTH comme champ pour l'affichage
            $ftthLabelMap = [
                'nouveau'            => 'Nouveau',
                'assigné'            => 'Assigné',
                'en_cours'           => 'En cours',
                'traité'             => 'Traité',
                'attente_validation' => 'En attente valid.',
                'validé'             => 'Validé',
                'clôturé'            => 'Clôturé',
            ];
            foreach ($recentFtth as &$ft) {
                $ft['_source']      = 'ftth';
                $ft['status_label'] = $ftthLabelMap[$ft['ftth_status'] ?? ''] ?? ($ft['ftth_status'] ?? '');
            }
            unset($ft);

            // Fusionner et trier par date
            $merged = array_merge($recentIncidents, $recentFtth);
            usort($merged, static function (array $a, array $b): int {
                $ta = strtotime((string)($a['updated_at'] ?? $a['declared_at'] ?? ''));
                $tb = strtotime((string)($b['updated_at'] ?? $b['declared_at'] ?? ''));
                return $tb <=> $ta;
            });
            $recentIncidents = array_slice($merged, 0, 10);
        } catch (\Throwable $e) {}

                if ($userRole === 'technicien') {
                        try {
                                $ftthSql = "SELECT t.id, t.ref_code, t.client_name, t.client_phone, t.client_address,
                                                                     t.priority, t.status, t.site_name, t.nature_intervention,
                                                                     a.assigned_at, a.notes AS assign_notes,
                                                                     r.status AS report_status, r.submitted_at
                                                        FROM maintenance_ftth_assignments a
                                                        JOIN maintenance_ftth_tickets t ON t.id = a.ticket_id
                                                        LEFT JOIN maintenance_ftth_reports r ON r.ticket_id = t.id
                                                        WHERE a.technician_id = ?
                                                            AND t.status NOT IN ('validé', 'clôturé')
                                                            " . $ftthCityCondition('t') . "
                                                        ORDER BY
                                                            CASE t.priority
                                                                WHEN 'Urgent' THEN 0
                                                                WHEN 'Haute' THEN 1
                                                                WHEN 'Moyenne' THEN 2
                                                                ELSE 3
                                                            END,
                                                            CASE t.status
                                                                WHEN 'en_cours' THEN 0
                                                                WHEN 'assigné' THEN 1
                                                                WHEN 'traité' THEN 2
                                                                ELSE 3
                                                            END,
                                                            a.assigned_at DESC
                                                        LIMIT 6";
                                $stmt = $pdo->prepare($ftthSql);
                                $stmt->execute(array_merge([$userId], $ftthCityParams()));
                                $ftthTickets = $stmt->fetchAll() ?: [];

                                foreach ($ftthTickets as $ftthTicket) {
                                        $ftthStats['assigned']++;

                                        if (($ftthTicket['status'] ?? '') === 'en_cours') {
                                                $ftthStats['in_progress']++;
                                        }

                                        $reportStatus = $ftthTicket['report_status'] ?? null;
                                        if ($reportStatus === 'soumis') {
                                                $ftthStats['awaiting_validation']++;
                                        }

                                        if ($reportStatus === null || in_array($reportStatus, ['brouillon', 'rejeté'], true)) {
                                                $ftthStats['to_fill']++;
                                        }
                                }
                        } catch (\Throwable $e) {
                                $ftthTickets = [];
                        }
                }

                $showGlobalB2BInsights = !in_array($userRole, ['technicien', 'client'], true);
                if ($showGlobalB2BInsights) {
                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_tickets t
                             WHERE {$raccordementWhereSql}
                               AND t.created_at BETWEEN ? AND ?"
                            . $ftthCityCondition('t')
                        );
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $moduleMetrics['raccordement_total'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_tickets t
                             LEFT JOIN maintenance_ftth_reports r ON r.ticket_id = t.id
                             WHERE {$raccordementWhereSql}
                               AND t.status NOT IN ('validé', 'clôturé')
                               AND (r.id IS NULL OR COALESCE(r.workflow_phase, 'study') = 'study')"
                            . $ftthCityCondition('t')
                        );
                        $stmt->execute($ftthCityParams());
                        $moduleMetrics['raccordement_pending_study'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_tickets t
                             JOIN maintenance_ftth_reports r ON r.ticket_id = t.id
                             WHERE {$raccordementWhereSql}
                               AND t.status NOT IN ('validé', 'clôturé')
                               AND r.workflow_phase = 'installation'"
                            . $ftthCityCondition('t')
                        );
                        $stmt->execute($ftthCityParams());
                        $moduleMetrics['raccordement_pending_installation'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_tickets t
                             JOIN maintenance_ftth_reports r ON r.ticket_id = t.id
                             WHERE {$raccordementWhereSql}
                               AND r.status = 'soumis'"
                            . $ftthCityCondition('t')
                        );
                        $stmt->execute($ftthCityParams());
                        $moduleMetrics['raccordement_waiting_validation'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_tickets t
                             JOIN maintenance_ftth_reports r ON r.ticket_id = t.id
                             WHERE {$raccordementWhereSql}
                               AND r.status = 'validé'
                               AND DATE(COALESCE(r.validated_at, r.updated_at, r.created_at)) BETWEEN ? AND ?"
                            . $ftthCityCondition('t')
                        );
                        $stmt->execute(array_merge([$dateFrom, $dateTo], $ftthCityParams()));
                        $moduleMetrics['raccordement_validated'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $otbSummary = \App\Core\RaccordementOtbService::summarize($pdo);
                        $moduleMetrics['otb_total_boxes'] = (int)($otbSummary['total_boxes'] ?? 0);
                        $moduleMetrics['otb_active_boxes'] = (int)($otbSummary['active_boxes'] ?? 0);
                        $moduleMetrics['otb_alert_boxes'] = (int)($otbSummary['alert_boxes'] ?? 0);
                        $moduleMetrics['otb_saturated_boxes'] = (int)($otbSummary['saturated_boxes'] ?? 0);
                        $moduleMetrics['otb_occupancy_rate'] = (float)($otbSummary['occupancy_rate'] ?? 0.0);
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->query(
                            "SELECT
                                COUNT(*) AS total_requests,
                                SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) AS requests_today,
                                SUM(CASE WHEN status IN ('soumis', 'prise_en_charge') THEN 1 ELSE 0 END) AS pending_intake,
                                SUM(CASE WHEN status IN ('assigne_technicien', 'en_cours') THEN 1 ELSE 0 END) AS assigned_field,
                                SUM(CASE WHEN status IN ('resolu', 'ferme') THEN 1 ELSE 0 END) AS resolved
                             FROM public_mobile_incidents
                             WHERE created_at BETWEEN " . $pdo->quote($dtFrom) . " AND " . $pdo->quote($dtTo)
                        );
                        $row = $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
                        foreach ($eInterventionMetrics as $metricKey => $metricDefault) {
                            $eInterventionMetrics[$metricKey] = (int)($row[$metricKey] ?? $metricDefault);
                        }
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare("SELECT COUNT(*) FROM maintenance_ftth_tickets WHERE created_at BETWEEN ? AND ?" . $ftthCityCondition('maintenance_ftth_tickets'));
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bMetrics['total_tickets'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_tickets t
                             LEFT JOIN maintenance_ftth_assignments a ON a.ticket_id = t.id
                             WHERE a.id IS NULL
                               AND t.created_at BETWEEN ? AND ?
                               AND t.status NOT IN ('validé', 'clôturé')"
                             . $ftthCityCondition('t')
                        );
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bMetrics['unassigned'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare("SELECT COUNT(*) FROM maintenance_ftth_tickets WHERE status = 'en_cours' AND created_at BETWEEN ? AND ?" . $ftthCityCondition('maintenance_ftth_tickets'));
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bMetrics['in_progress'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_reports r
                             JOIN maintenance_ftth_tickets t ON t.id = r.ticket_id
                             WHERE r.status = 'soumis'
                               AND r.submitted_at BETWEEN ? AND ?"
                            . $ftthCityCondition('t')
                        );
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bMetrics['awaiting_validation'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_tickets
                             WHERE priority = 'Urgent'
                               AND created_at BETWEEN ? AND ?
                               AND status NOT IN ('validé', 'clôturé')"
                            . $ftthCityCondition('maintenance_ftth_tickets')
                        );
                           $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bMetrics['urgent_open'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COUNT(*)
                             FROM maintenance_ftth_reports r
                             JOIN maintenance_ftth_tickets t ON t.id = r.ticket_id
                             WHERE r.status = 'validé'
                               AND DATE(r.validated_at) = CURDATE()"
                            . $ftthCityCondition('t')
                        );
                        $stmt->execute($ftthCityParams());
                        $b2bMetrics['validated_today'] = (int)$stmt->fetchColumn();
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT ROUND(AVG(CASE WHEN service_restored = 1 THEN 100 ELSE 0 END), 0)
                             FROM maintenance_ftth_reports r
                             JOIN maintenance_ftth_tickets t ON t.id = r.ticket_id
                             WHERE r.status IN ('soumis', 'validé')"
                                . " AND r.submitted_at BETWEEN ? AND ?"
                                . $ftthCityCondition('t')
                        );
                            $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bMetrics['service_restored_rate'] = (int)($stmt->fetchColumn() ?: 0);
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT ROUND(AVG(TIMESTAMPDIFF(MINUTE, t.created_at, fa.first_assigned_at)) / 60, 1)
                             FROM maintenance_ftth_tickets t
                             JOIN (
                                SELECT ticket_id, MIN(assigned_at) AS first_assigned_at
                                FROM maintenance_ftth_assignments
                                GROUP BY ticket_id
                                ) fa ON fa.ticket_id = t.id
                                WHERE t.created_at BETWEEN ? AND ?"
                            . $ftthCityCondition('t')
                        );
                            $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bMetrics['avg_assignment_delay_hours'] = (float)($stmt->fetchColumn() ?: 0);
                    } catch (\Throwable $e) {}

                    try {
                              $topTechSql = "SELECT u.id,
                                              u.name,
                                              u.email,
                                              COALESCE(incident_stats.total_incidents_count, 0)       AS total_incidents_count,
                                              COALESCE(incident_stats.in_progress_incidents_count, 0) AS in_progress_incidents_count,
                                              COALESCE(incident_stats.closed_incidents_count, 0)      AS closed_incidents_count,
                                              COALESCE(incident_stats.total_incidents_count, 0)       AS assigned_incidents_count,
                                              COALESCE(incident_stats.in_progress_incidents_count, 0) AS active_incidents_count,
                                              COALESCE(incident_stats.closed_incidents_count, 0)      AS resolved_incidents_count,
                                              COALESCE(assign_stats.assigned_count, 0)    AS assigned_count,
                                              COALESCE(assign_stats.in_progress_count, 0) AS in_progress_count,
                                              COALESCE(assign_stats.closed_count, 0)      AS closed_count,
                                              COALESCE(report_stats.submitted_count, 0)   AS submitted_count,
                                              COALESCE(report_stats.validated_count, 0)   AS validated_count,
                                              COALESCE(report_stats.restored_count, 0)    AS restored_count,
                                              COALESCE(report_stats.avg_turnaround_hours, 0) AS avg_turnaround_hours
                                       FROM users u
                                       LEFT JOIN (
                                           SELECT ia.user_id,
                                                  COUNT(DISTINCT ia.incident_id) AS total_incidents_count,
                                                  COUNT(DISTINCT CASE
                                                      WHEN i.resolved_at IS NULL
                                                       AND (s.key_name IS NULL OR s.key_name NOT IN ('resolved_temp','resolved_final','resolved','closed','cancelled'))
                                                      THEN ia.incident_id
                                                  END) AS in_progress_incidents_count,
                                                  COUNT(DISTINCT CASE
                                                      WHEN i.resolved_at IS NOT NULL
                                                        OR s.key_name IN ('resolved_temp','resolved_final','resolved','closed')
                                                      THEN ia.incident_id
                                                  END) AS closed_incidents_count
                                           FROM incident_assignments ia
                                           JOIN incidents i ON i.id = ia.incident_id
                                           LEFT JOIN incident_statuses s ON s.id = i.status_id
                                           WHERE ia.active IS NULL OR ia.active = 1
                                           GROUP BY ia.user_id
                                       ) incident_stats ON incident_stats.user_id = u.id
                                       LEFT JOIN (
                                           SELECT a.technician_id,
                                                  COUNT(DISTINCT a.ticket_id) AS assigned_count,
                                                  COUNT(DISTINCT CASE WHEN t.status = 'en_cours' THEN a.ticket_id END) AS in_progress_count,
                                                  COUNT(DISTINCT CASE WHEN t.status IN ('traité','validé','clôturé') THEN a.ticket_id END) AS closed_count
                                           FROM maintenance_ftth_assignments a
                                           JOIN maintenance_ftth_tickets t ON t.id = a.ticket_id
                                           WHERE 1=1" . $ftthCityCondition('t') . "
                                           GROUP BY a.technician_id
                                       ) assign_stats ON assign_stats.technician_id = u.id
                                       LEFT JOIN (
                                           SELECT rpt.technician_id,
                                                  SUM(CASE WHEN rpt.status = 'soumis' THEN 1 ELSE 0 END)  AS submitted_count,
                                                  SUM(CASE WHEN rpt.status = 'validé' THEN 1 ELSE 0 END)  AS validated_count,
                                                  SUM(CASE WHEN rpt.service_restored = 1 THEN 1 ELSE 0 END) AS restored_count,
                                                  ROUND(AVG(CASE
                                                      WHEN fa.first_assigned_at IS NOT NULL AND rpt.submitted_at IS NOT NULL
                                                      THEN TIMESTAMPDIFF(MINUTE, fa.first_assigned_at, rpt.submitted_at) / 60
                                                      ELSE NULL
                                                  END), 1) AS avg_turnaround_hours
                                           FROM maintenance_ftth_reports rpt
                                           JOIN maintenance_ftth_tickets rt ON rt.id = rpt.ticket_id
                                           LEFT JOIN (
                                               SELECT technician_id, ticket_id, MIN(assigned_at) AS first_assigned_at
                                               FROM maintenance_ftth_assignments
                                               GROUP BY technician_id, ticket_id
                                           ) fa ON fa.technician_id = rpt.technician_id AND fa.ticket_id = rpt.ticket_id
                                           WHERE 1=1" . $ftthCityCondition('rt') . "
                                           GROUP BY rpt.technician_id
                                       ) report_stats ON report_stats.technician_id = u.id
                                       WHERE u.active = 1
                                         AND u.role_key IN ('technicien','technician')";
                        if ($assignedCity !== null) {
                            $topTechSql .= " AND LOWER(TRIM(COALESCE(u.assigned_city, ''))) = ?";
                        }
                        $stmt = $pdo->prepare($topTechSql);
                        $stmt->execute(array_merge($ftthCityParams(), $ftthCityParams(), $assignedCity !== null ? [$assignedCity] : []));
                        $topTechnicians = $stmt->fetchAll() ?: [];

                        foreach ($topTechnicians as &$technician) {
                            $incidentAssignedCount = (int)($technician['total_incidents_count'] ?? $technician['assigned_incidents_count'] ?? 0);
                            $incidentActiveCount = (int)($technician['in_progress_incidents_count'] ?? $technician['active_incidents_count'] ?? 0);
                            $incidentResolvedCount = (int)($technician['closed_incidents_count'] ?? $technician['resolved_incidents_count'] ?? 0);
                            $assignedCount = (int)($technician['assigned_count'] ?? 0);
                            $inProgressCount = (int)($technician['in_progress_count'] ?? 0);
                            $closedCount = (int)($technician['closed_count'] ?? 0);
                            $turnaround = (float)($technician['avg_turnaround_hours'] ?? 0);
                            $technician['incident_score'] = ($incidentResolvedCount * 6) + ($incidentActiveCount * 3) + $incidentAssignedCount;
                            $technician['ftth_score'] = ($closedCount * 6) + ($inProgressCount * 3) + $assignedCount;
                            $technician['score'] = (int)$technician['incident_score'] + (int)$technician['ftth_score'];
                            $technician['avg_turnaround_hours'] = $turnaround > 0 ? $turnaround : null;
                        }
                        unset($technician);

                        usort($topTechnicians, static function (array $left, array $right): int {
                            return [$right['score'], $right['closed_incidents_count'], $right['closed_count'], $right['in_progress_count'], $right['assigned_count']]
                                <=> [$left['score'], $left['closed_incidents_count'], $left['closed_count'], $left['in_progress_count'], $left['assigned_count']];
                        });

                        $topTechnicians = array_slice($topTechnicians, 0, 10);
                        $technicianPerformanceChart = [
                            'labels' => array_map(static function (array $technician): string {
                                $parts = preg_split('/\s+/', trim((string)($technician['name'] ?? 'Technicien'))) ?: [];
                                return (string)($parts[0] ?? 'Technicien');
                            }, array_slice($topTechnicians, 0, 7)),
                            'incident_scores' => array_map(static fn(array $technician): int => (int)($technician['incident_score'] ?? 0), array_slice($topTechnicians, 0, 7)),
                            'ftth_scores' => array_map(static fn(array $technician): int => (int)($technician['ftth_score'] ?? 0), array_slice($topTechnicians, 0, 7)),
                            'total_scores' => array_map(static fn(array $technician): int => (int)($technician['score'] ?? 0), array_slice($topTechnicians, 0, 7)),
                        ];
                    } catch (\Throwable $e) {
                        $topTechnicians = [];
                        $technicianPerformanceChart = [
                            'labels' => [],
                            'incident_scores' => [],
                            'ftth_scores' => [],
                            'total_scores' => [],
                        ];
                    }

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT priority AS label, COUNT(*) AS total
                                FROM maintenance_ftth_tickets
                                WHERE created_at BETWEEN ? AND ?"
                            . $ftthCityCondition('maintenance_ftth_tickets') .
                            " GROUP BY priority
                             ORDER BY FIELD(priority, 'Urgent', 'Haute', 'Moyenne', 'Basse')"
                        );
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bDistributions['priority'] = $stmt->fetchAll() ?: [];
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT status AS label, COUNT(*) AS total
                                FROM maintenance_ftth_tickets
                                WHERE created_at BETWEEN ? AND ?"
                            . $ftthCityCondition('maintenance_ftth_tickets') .
                            " GROUP BY status
                             ORDER BY FIELD(status, 'nouveau', 'assigné', 'en_cours', 'traité', 'validé', 'clôturé')"
                        );
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bDistributions['status'] = $stmt->fetchAll() ?: [];
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COALESCE(NULLIF(TRIM(nature_intervention), ''), 'Non renseignée') AS label,
                                    COUNT(*) AS total,
                                    SUM(CASE WHEN status NOT IN ('validé', 'clôturé') THEN 1 ELSE 0 END) AS active_total
                                FROM maintenance_ftth_tickets
                                WHERE created_at BETWEEN ? AND ?"
                            . $ftthCityCondition('maintenance_ftth_tickets') .
                            " GROUP BY COALESCE(NULLIF(TRIM(nature_intervention), ''), 'Non renseignée')
                             ORDER BY active_total DESC, total DESC
                             LIMIT 5"
                        );
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bDistributions['nature'] = $stmt->fetchAll() ?: [];
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT COALESCE(NULLIF(TRIM(client_name), ''), 'Client non renseigné') AS label,
                                    COUNT(*) AS total,
                                    SUM(CASE WHEN status NOT IN ('validé', 'clôturé') THEN 1 ELSE 0 END) AS active_total
                                FROM maintenance_ftth_tickets
                                WHERE created_at BETWEEN ? AND ?"
                            . $ftthCityCondition('maintenance_ftth_tickets') .
                            " GROUP BY COALESCE(NULLIF(TRIM(client_name), ''), 'Client non renseigné')
                             ORDER BY active_total DESC, total DESC
                             LIMIT 5"
                        );
                        $stmt->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
                        $b2bDistributions['clients'] = $stmt->fetchAll() ?: [];
                    } catch (\Throwable $e) {}
                }

                if (in_array($userRole, ['technicien', 'technician'], true)) {
                    try {
                        $stmt = $pdo->prepare(
                            "SELECT
                                COUNT(DISTINCT ia.incident_id) AS backbone_total,
                                COUNT(DISTINCT CASE
                                    WHEN i.resolved_at IS NULL
                                     AND (
                                        s.key_name IS NULL
                                        OR s.key_name NOT IN ('resolved_temp', 'resolved_final', 'resolved', 'closed', 'cancelled')
                                     )
                                    THEN ia.incident_id
                                END) AS backbone_in_progress,
                                COUNT(DISTINCT CASE
                                    WHEN i.resolved_at IS NOT NULL
                                      OR s.key_name IN ('resolved_temp', 'resolved_final', 'resolved', 'closed')
                                    THEN ia.incident_id
                                END) AS backbone_closed
                             FROM incident_assignments ia
                             JOIN incidents i ON i.id = ia.incident_id
                             LEFT JOIN incident_statuses s ON s.id = i.status_id
                             WHERE ia.user_id = ?
                               AND i.declared_at BETWEEN ? AND ?
                               AND (ia.active IS NULL OR ia.active = 1)"
                        );
                           $stmt->execute([$userId, $dtFrom, $dtTo]);
                        $row = $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
                        $myTechnicianPerformance['backbone_total'] = (int)($row['backbone_total'] ?? 0);
                        $myTechnicianPerformance['backbone_in_progress'] = (int)($row['backbone_in_progress'] ?? 0);
                        $myTechnicianPerformance['backbone_closed'] = (int)($row['backbone_closed'] ?? 0);
                    } catch (\Throwable $e) {}

                    try {
                        $stmt = $pdo->prepare(
                            "SELECT
                                COUNT(DISTINCT a.ticket_id) AS ftth_total,
                                COUNT(DISTINCT CASE WHEN t.status = 'en_cours' THEN a.ticket_id END) AS ftth_in_progress,
                                COUNT(DISTINCT CASE WHEN t.status IN ('traité', 'validé', 'clôturé') THEN a.ticket_id END) AS ftth_closed
                             FROM maintenance_ftth_assignments a
                             JOIN maintenance_ftth_tickets t ON t.id = a.ticket_id
                                WHERE a.technician_id = ?
                                 AND t.created_at BETWEEN ? AND ?"
                            . $ftthCityCondition('t')
                        );
                            $stmt->execute(array_merge([$userId, $dtFrom, $dtTo], $ftthCityParams()));
                        $row = $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
                        $myTechnicianPerformance['ftth_total'] = (int)($row['ftth_total'] ?? 0);
                        $myTechnicianPerformance['ftth_in_progress'] = (int)($row['ftth_in_progress'] ?? 0);
                        $myTechnicianPerformance['ftth_closed'] = (int)($row['ftth_closed'] ?? 0);
                    } catch (\Throwable $e) {}

                    $myTechnicianPerformance['score'] =
                        ($myTechnicianPerformance['backbone_closed'] * 6)
                        + ($myTechnicianPerformance['backbone_in_progress'] * 3)
                        + $myTechnicianPerformance['backbone_total']
                        + ($myTechnicianPerformance['ftth_closed'] * 6)
                        + ($myTechnicianPerformance['ftth_in_progress'] * 3)
                        + $myTechnicianPerformance['ftth_total'];
                }

        $daysDiff = max(1, (int)((strtotime($dateTo . ' 23:59:59') - strtotime($dateFrom . ' 00:00:00')) / 86400));
        // Bucketing : ≤30j → quotidien | ≤180j → semaine | >180j → mois
        $bucket = $daysDiff <= 30 ? 'day' : ($daysDiff <= 180 ? 'week' : 'month');

        // ── Générer les points du graphique ────────────────────────────────
        $chartPoints = [];
        if ($bucket === 'day') {
            for ($d = $daysDiff; $d >= 0; $d--) {
                $day = date('Y-m-d', strtotime("$dateTo -$d days"));
                $chartPoints[] = ['from' => $day, 'to' => $day,
                    'label' => $d === 0 ? "Auj." : date('d/m', strtotime($day))];
            }
        } elseif ($bucket === 'week') {
            $cur = strtotime('Monday this week', strtotime($dateFrom));
            if ($cur > strtotime($dateFrom)) { $cur = strtotime('Monday last week', strtotime($dateFrom)); }
            while ($cur <= strtotime($dateTo)) {
                $wEnd = strtotime('+6 days', $cur);
                $chartPoints[] = ['from' => date('Y-m-d', $cur), 'to' => date('Y-m-d', min($wEnd, strtotime($dateTo))),
                    'label' => 'S' . date('W', $cur)];
                $cur = strtotime('+7 days', $cur);
            }
        } else { // month
            $cur = strtotime(date('Y-m-01', strtotime($dateFrom)));
            $end = strtotime($dateTo);
            while ($cur <= $end) {
                $mEnd = strtotime(date('Y-m-t', $cur));
                $chartPoints[] = ['from' => date('Y-m-d', $cur), 'to' => date('Y-m-d', min($mEnd, $end)),
                    'label' => ucfirst(strftime('%b %y', $cur) ?: date("M 'y", $cur))];
                $cur = strtotime('+1 month', $cur);
            }
        }

        // ── Requêtes agrégées (4 requêtes au total) ─────────────────────────
        $openedByDay = $resolvedByDay = $ftthOpenedByDay = $ftthResolvedByDay = [];
        try {
            $sqlO = "SELECT DATE(declared_at) AS d, COUNT(*) AS n FROM incidents i $joinClause $whereClause" .
                    ($whereClause ? ' AND' : ' WHERE') . " declared_at BETWEEN ? AND ? GROUP BY DATE(declared_at)";
            $st = $pdo->prepare($sqlO); $st->execute(array_merge($params, [$dtFrom, $dtTo]));
            $openedByDay = array_column($st->fetchAll(), 'n', 'd');
        } catch (\Throwable $e) {}
        try {
            $sqlR = "SELECT DATE(resolved_at) AS d, COUNT(*) AS n FROM incidents i $joinClause $whereClause" .
                    ($whereClause ? ' AND' : ' WHERE') . " resolved_at BETWEEN ? AND ? GROUP BY DATE(resolved_at)";
            $st = $pdo->prepare($sqlR); $st->execute(array_merge($params, [$dtFrom, $dtTo]));
            $resolvedByDay = array_column($st->fetchAll(), 'n', 'd');
        } catch (\Throwable $e) {}
        try {
            $sqlFO = $userRole === 'technicien'
                ? "SELECT DATE(t.created_at) AS d, COUNT(*) AS n FROM maintenance_ftth_tickets t JOIN maintenance_ftth_assignments a ON a.ticket_id=t.id AND a.technician_id=$userId WHERE t.created_at BETWEEN ? AND ?" . $ftthCityCondition('t') . " GROUP BY DATE(t.created_at)"
                : "SELECT DATE(created_at) AS d, COUNT(*) AS n FROM maintenance_ftth_tickets WHERE created_at BETWEEN ? AND ?" . $ftthCityCondition('maintenance_ftth_tickets') . " GROUP BY DATE(created_at)";
            $st = $pdo->prepare($sqlFO); $st->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
            $ftthOpenedByDay = array_column($st->fetchAll(), 'n', 'd');
        } catch (\Throwable $e) {}
        try {
            $sqlFR = $userRole === 'technicien'
                ? "SELECT DATE(t.updated_at) AS d, COUNT(*) AS n FROM maintenance_ftth_tickets t JOIN maintenance_ftth_assignments a ON a.ticket_id=t.id AND a.technician_id=$userId WHERE t.status IN ('validé','clôturé') AND t.updated_at BETWEEN ? AND ?" . $ftthCityCondition('t') . " GROUP BY DATE(t.updated_at)"
                : "SELECT DATE(updated_at) AS d, COUNT(*) AS n FROM maintenance_ftth_tickets WHERE status IN ('validé','clôturé') AND updated_at BETWEEN ? AND ?" . $ftthCityCondition('maintenance_ftth_tickets') . " GROUP BY DATE(updated_at)";
            $st = $pdo->prepare($sqlFR); $st->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
            $ftthResolvedByDay = array_column($st->fetchAll(), 'n', 'd');
        } catch (\Throwable $e) {}

        // ── Agréger par bucket ──────────────────────────────────────────────
        $chartData = ['labels' => [], 'opened' => [], 'resolved' => [], 'ftth_opened' => [], 'ftth_resolved' => []];
        foreach ($chartPoints as $pt) {
            $o = $r = $fo = $fr = 0;
            for ($cur = strtotime($pt['from']), $ptEnd = strtotime($pt['to']); $cur <= $ptEnd; $cur = strtotime('+1 day', $cur)) {
                $d = date('Y-m-d', $cur);
                $o  += (int)($openedByDay[$d]      ?? 0);
                $r  += (int)($resolvedByDay[$d]    ?? 0);
                $fo += (int)($ftthOpenedByDay[$d]  ?? 0);
                $fr += (int)($ftthResolvedByDay[$d]?? 0);
            }
            $chartData['labels'][]        = $pt['label'];
            $chartData['opened'][]        = $o;
            $chartData['resolved'][]      = $r;
            $chartData['ftth_opened'][]   = $fo;
            $chartData['ftth_resolved'][] = $fr;
        }

        // Répartition par statut pour le graphique en donut (backbone + FTTH)
        $statusData = [
            'labels' => [],
            'data'   => [],
            'colors' => [],
        ];

        // Backbone : incidents par statut
        try {
            $sql = "SELECT s.key_name, s.label, s.color, COUNT(*) AS count " .
                   "FROM incidents i $joinClause " .
                   "JOIN incident_statuses s ON i.status_id = s.id " .
                   "$incidentDeclaredWhere " .
                   "GROUP BY s.id, s.key_name, s.label, s.color " .
                   "ORDER BY count DESC";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($scopedParamsWithPeriod);
            foreach ($stmt->fetchAll() as $row) {
                $statusData['labels'][] = $row['label'];
                $statusData['data'][]   = (int)$row['count'];
                $statusData['colors'][] = incident_status_color([
                    'status_key'   => $row['key_name'] ?? '',
                    'status_label' => $row['label'] ?? '',
                    'status_color' => $row['color'] ?? '',
                ]);
            }
        } catch (\Throwable $e) {}

        // FTTH : tickets par statut — on normalise les libellés et on fusionne
        $ftthStatusLabels = [
            'nouveau'            => 'Nouveau',
            'assigné'            => 'Assigné',
            'en_cours'           => 'En cours',
            'traité'             => 'Traité',
            'attente_validation' => 'Validation N+1',
            'validé'             => 'Validé',
            'clôturé'            => 'Clôturé',
        ];
        $ftthStatusColors = [
            'Nouveau'        => '#ef4444',
            'Assigné'        => '#f59e0b',
            'En cours'       => '#3b82f6',
            'Traité'         => '#8b5cf6',
            'Validation N+1' => '#f97316',
            'Validé'         => '#10b981',
            'Clôturé'        => '#6b7280',
        ];
        try {
            $ftthStatusSql = "SELECT status, COUNT(*) AS cnt FROM maintenance_ftth_tickets WHERE created_at BETWEEN ? AND ?" . $ftthCityCondition('maintenance_ftth_tickets') . " GROUP BY status";
            if ($userRole === 'technicien') {
                $ftthStatusSql = "SELECT t.status, COUNT(*) AS cnt
                                  FROM maintenance_ftth_tickets t
                                  JOIN maintenance_ftth_assignments a ON a.ticket_id = t.id AND a.technician_id = $userId
                                  WHERE t.created_at BETWEEN ? AND ?
                                  " . $ftthCityCondition('t') . "
                                  GROUP BY t.status";
            }
            $stmtFs = $pdo->prepare($ftthStatusSql);
            $stmtFs->execute(array_merge([$dtFrom, $dtTo], $ftthCityParams()));
            foreach ($stmtFs->fetchAll() as $ftRow) {
                $lbl = $ftthStatusLabels[$ftRow['status']] ?? ucfirst((string)$ftRow['status']);
                $cnt = (int)$ftRow['cnt'];
                $idx = array_search($lbl, $statusData['labels'], true);
                if ($idx !== false) {
                    $statusData['data'][$idx] += $cnt;   // fusion avec backbone si même libellé
                } else {
                    $statusData['labels'][] = $lbl;
                    $statusData['data'][]   = $cnt;
                    $statusData['colors'][] = $ftthStatusColors[$lbl] ?? '#6b7280';
                }
            }
        } catch (\Throwable $e) {}

        // Fallback si aucune donnée
        if (empty($statusData['labels'])) {
            $statusData = [
                'labels' => ['Ouvert', 'En cours', 'Traité', 'Clôturé'],
                'data'   => [0, 0, 0, 0],
                'colors' => ['#dc3545', '#fd7e14', '#0d6efd', '#198754'],
            ];
        }

        // Calcul des objectifs d'intervention (SLA/Performance)
        $interventionMetrics = [
            'open_to_assigned' => 0,
            'assigned_to_intervention' => 0,
            'intervention_to_resolution' => 0
        ];

        try {
            // 1. Ouverture → Assignation : % d'incidents assignés dans les délais
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM incidents WHERE declared_at BETWEEN ? AND ?");
            $stmt->execute([$dtFrom, $dtTo]);
            $totalIncidents = (int)$stmt->fetchColumn();
            if ($totalIncidents > 0) {
                // Incidents avec assignation dans les 4h (SLA standard)
                $stmt = $pdo->prepare("
                    SELECT COUNT(*) FROM incident_assignments ia 
                    JOIN incidents i ON i.id = ia.incident_id 
                    WHERE TIMESTAMPDIFF(HOUR, i.declared_at, ia.assigned_at) <= 4
                      AND i.declared_at BETWEEN ? AND ?
                ");
                $stmt->execute([$dtFrom, $dtTo]);
                $assignedInTime = (int)$stmt->fetchColumn();
                $interventionMetrics['open_to_assigned'] = min(100, round(($assignedInTime / $totalIncidents) * 100));
            }
        } catch (\Throwable $e) {}

        try {
            // 2. Assignation → Intervention : % d'interventions démarrées dans les délais
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM incident_assignments WHERE active = 1 AND assigned_at BETWEEN ? AND ?");
            $stmt->execute([$dtFrom, $dtTo]);
            $totalAssigned = (int)$stmt->fetchColumn();
            if ($totalAssigned > 0) {
                // Interventions démarrées dans les 2h après assignation
                    // Utiliser la table réelle des événements d'intervention
                    $stmt = $pdo->prepare("
                        SELECT COUNT(*) FROM incident_intervention_events ie 
                        JOIN incident_assignments ia ON ia.incident_id = ie.incident_id 
                        WHERE ie.action = 'start' 
                          AND (ia.active = 1 OR ia.active IS NULL) 
                          AND TIMESTAMPDIFF(HOUR, ia.assigned_at, ie.happened_at) <= 2
                          AND ia.assigned_at BETWEEN ? AND ?
                    ");
                $stmt->execute([$dtFrom, $dtTo]);
                $interventionStarted = (int)$stmt->fetchColumn();
                $interventionMetrics['assigned_to_intervention'] = min(100, round(($interventionStarted / $totalAssigned) * 100));
            }
        } catch (\Throwable $e) {}

        try {
            // 3. Intervention → Résolution : % d'incidents résolus dans le SLA
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM incidents WHERE expected_resolution_at IS NOT NULL AND expected_resolution_at BETWEEN ? AND ?");
            $stmt->execute([$dtFrom, $dtTo]);
            $totalWithSLA = (int)$stmt->fetchColumn();
            if ($totalWithSLA > 0) {
                $stmt = $pdo->prepare("
                    SELECT COUNT(*) FROM incidents 
                    WHERE expected_resolution_at IS NOT NULL 
                    AND resolved_at IS NOT NULL 
                    AND resolved_at <= expected_resolution_at
                    AND expected_resolution_at BETWEEN ? AND ?
                ");
                $stmt->execute([$dtFrom, $dtTo]);
                $resolvedInTime = (int)$stmt->fetchColumn();
                $interventionMetrics['intervention_to_resolution'] = min(100, round(($resolvedInTime / $totalWithSLA) * 100));
            }
        } catch (\Throwable $e) {}

        // Valeurs par défaut si aucune donnée
        if ($interventionMetrics['open_to_assigned'] === 0 && $interventionMetrics['assigned_to_intervention'] === 0 && $interventionMetrics['intervention_to_resolution'] === 0) {
            $interventionMetrics = [
                'open_to_assigned' => 0,
                'assigned_to_intervention' => 0,
                'intervention_to_resolution' => 0
            ];
        }

        $this->view('dashboard/index', [
            'title'                     => 'Tableau de Bord',
            'stats'                     => $stats,
            'ftthStats'                 => $ftthStats,
            'ftthTickets'               => $ftthTickets,
            'eInterventionMetrics'      => $eInterventionMetrics,
            'b2bMetrics'                => $b2bMetrics,
            'moduleMetrics'             => $moduleMetrics,
            'topTechnicians'            => $topTechnicians,
            'myTechnicianPerformance'   => $myTechnicianPerformance,
            'technicianPerformanceChart'=> $technicianPerformanceChart,
            'b2bDistributions'          => $b2bDistributions,
            'recentIncidents'           => $recentIncidents,
            'chartData'                 => $chartData,
            'chartPeriod'               => $chartPeriod,
            'chartDateFrom'             => $dateFrom,
            'chartDateTo'               => $dateTo,
            'chartIsCustom'             => $isCustom,
            'chartAllowedPeriods'       => $allowedPeriods,
            'statusData'                => $statusData,
            'interventionMetrics'       => $interventionMetrics,
            'currentUser'               => $currentUser,
            'userRole'                  => $userRole,
        ]);
    }
}