<?php
namespace App\Controllers;

use App\Core\Controller;
use App\Core\Auth;
use App\Core\Database;
use PDO;

class ReferentialController extends Controller
{
    private function ensureLocationsGeoColumns(PDO $pdo): void
    {
        // Best-effort: table peut déjà exister en prod
        try {
            $pdo->exec("CREATE TABLE IF NOT EXISTS locations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                client_id INT NULL,
                name VARCHAR(255) NOT NULL,
                address TEXT NULL,
                city VARCHAR(100) NULL,
                postal_code VARCHAR(20) NULL,
                active TINYINT(1) DEFAULT 1,
                latitude DECIMAL(10,8) NULL,
                longitude DECIMAL(11,8) NULL,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        } catch (\Throwable $e) {
        }

        $need = [
            'address' => "ALTER TABLE locations ADD COLUMN address TEXT NULL",
            'city' => "ALTER TABLE locations ADD COLUMN city VARCHAR(100) NULL",
            'postal_code' => "ALTER TABLE locations ADD COLUMN postal_code VARCHAR(20) NULL",
            'latitude' => "ALTER TABLE locations ADD COLUMN latitude DECIMAL(10,8) NULL",
            'longitude' => "ALTER TABLE locations ADD COLUMN longitude DECIMAL(11,8) NULL",
        ];
        foreach ($need as $col => $sql) {
            try {
                $st = $pdo->prepare('SHOW COLUMNS FROM locations LIKE ?');
                $st->execute([$col]);
                if (!$st->fetch()) {
                    $pdo->exec($sql);
                }
            } catch (\Throwable $e) {
            }
        }
    }

    private function upsertLocationCoords(PDO $pdo, string $siteName, ?string $lat, ?string $lng): void
    {
        $siteName = trim($siteName);
        if ($siteName === '') return;

        $lat = $this->normalizeDecimal($lat);
        $lng = $this->normalizeDecimal($lng);
        if ($lat === null || $lng === null) return;

        $this->ensureLocationsGeoColumns($pdo);

        // Update par name (pas d'UNIQUE en prod parfois)
        $upd = $pdo->prepare(
            'UPDATE locations SET latitude = COALESCE(?, latitude), longitude = COALESCE(?, longitude), active = 1 WHERE name = ?'
        );
        $upd->execute([$lat, $lng, $siteName]);

        if ($upd->rowCount() === 0) {
            $ins = $pdo->prepare('INSERT INTO locations (name, client_id, latitude, longitude, active) VALUES (?, ?, ?, ?, 1)');
            $ins->execute([$siteName, 1, $lat, $lng]);
        }
    }
    private function ensureLiaisonsGeoColumns(PDO $pdo): void
    {
        // Créer la table si absente (installation)
        $pdo->exec("CREATE TABLE IF NOT EXISTS liaisons (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT NULL,
            site_a_name VARCHAR(255) NULL,
            site_b_name VARCHAR(255) NULL,
            site_a_latitude DECIMAL(10,7) NULL,
            site_a_longitude DECIMAL(10,7) NULL,
            site_b_latitude DECIMAL(10,7) NULL,
            site_b_longitude DECIMAL(10,7) NULL,
            active TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY name (name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // Ajouter les colonnes manquantes sur une base existante
        $need = [
            'site_a_name' => "ALTER TABLE liaisons ADD COLUMN site_a_name VARCHAR(255) NULL AFTER description",
            'site_b_name' => "ALTER TABLE liaisons ADD COLUMN site_b_name VARCHAR(255) NULL AFTER site_a_name",
            'site_a_latitude' => "ALTER TABLE liaisons ADD COLUMN site_a_latitude DECIMAL(10,7) NULL AFTER site_b_name",
            'site_a_longitude' => "ALTER TABLE liaisons ADD COLUMN site_a_longitude DECIMAL(10,7) NULL AFTER site_a_latitude",
            'site_b_latitude' => "ALTER TABLE liaisons ADD COLUMN site_b_latitude DECIMAL(10,7) NULL AFTER site_a_longitude",
            'site_b_longitude' => "ALTER TABLE liaisons ADD COLUMN site_b_longitude DECIMAL(10,7) NULL AFTER site_b_latitude",
        ];

        foreach ($need as $col => $sql) {
            try {
                $st = $pdo->prepare('SHOW COLUMNS FROM liaisons LIKE ?');
                $st->execute([$col]);
                if (!$st->fetch()) {
                    $pdo->exec($sql);
                }
            } catch (\Throwable $e) {
                // Si l'utilisateur DB n'a pas les droits ALTER, on continue en mode dégradé.
            }
        }
    }

    private function parseSitesFromLiaisonName(string $liaisonName): array
    {
        $inside = '';
        if (preg_match('/\[(.*?)\]/u', $liaisonName, $m)) {
            $inside = trim((string)$m[1]);
        }
        if ($inside === '') {
            return [null, null];
        }

        $inside = preg_replace('/\s+/u', ' ', $inside);
        if (preg_match('/^(.*?)\s+(?:vers|to)\s+(.*)$/iu', $inside, $m2)) {
            $a = trim((string)$m2[1]);
            $b = trim((string)$m2[2]);
            return [$a !== '' ? $a : null, $b !== '' ? $b : null];
        }

        return [null, null];
    }

    private function normalizeDecimal(?string $value): ?string
    {
        $v = trim((string)($value ?? ''));
        if ($v === '') return null;
        $v = str_replace(',', '.', $v);
        if (!is_numeric($v)) return null;
        return $v;
    }

    private function lookupCoordsFromLocations(PDO $pdo, string $siteName): array
    {
        try {
            // Priorité: une ligne qui a bien des coordonnées
            $st = $pdo->prepare('SELECT latitude, longitude FROM locations WHERE name = ? AND latitude IS NOT NULL AND longitude IS NOT NULL ORDER BY id DESC LIMIT 1');
            $st->execute([$siteName]);
            $row = $st->fetch(PDO::FETCH_ASSOC);
            if ($row && ($row['latitude'] ?? '') !== '' && ($row['longitude'] ?? '') !== '') {
                return [
                    'lat' => (string)$row['latitude'],
                    'lng' => (string)$row['longitude'],
                ];
            }

            // Fallback: si aucune coordonnée trouvée, prendre quand même la dernière ligne
            $stAny = $pdo->prepare('SELECT latitude, longitude FROM locations WHERE name = ? ORDER BY id DESC LIMIT 1');
            $stAny->execute([$siteName]);
            $rowAny = $stAny->fetch(PDO::FETCH_ASSOC);
            if ($rowAny && ($rowAny['latitude'] ?? '') !== '' && ($rowAny['longitude'] ?? '') !== '') {
                return [
                    'lat' => (string)$rowAny['latitude'],
                    'lng' => (string)$rowAny['longitude'],
                ];
            }

            // Fallback: match "normalisé" (underscore/tiret -> espace, casse/espaces)
            // Objectif: MARC_ANADER == "MARC ANADER" et variantes.
            $st2 = $pdo->prepare(
                "SELECT latitude, longitude
                 FROM locations
                 WHERE UPPER(TRIM(REPLACE(REPLACE(name, '_', ' '), '-', ' '))) = UPPER(TRIM(REPLACE(REPLACE(?, '_', ' '), '-', ' ')))
                   AND latitude IS NOT NULL AND longitude IS NOT NULL
                 ORDER BY id DESC
                 LIMIT 1"
            );
            $st2->execute([$siteName]);
            $row2 = $st2->fetch(PDO::FETCH_ASSOC);
            if ($row2 && ($row2['latitude'] ?? '') !== '' && ($row2['longitude'] ?? '') !== '') {
                return [
                    'lat' => (string)$row2['latitude'],
                    'lng' => (string)$row2['longitude'],
                ];
            }

            // Dernier recours: match normalisé sans contrainte coords
            $st2Any = $pdo->prepare(
                "SELECT latitude, longitude
                 FROM locations
                 WHERE UPPER(TRIM(REPLACE(REPLACE(name, '_', ' '), '-', ' '))) = UPPER(TRIM(REPLACE(REPLACE(?, '_', ' '), '-', ' ')))
                 ORDER BY id DESC
                 LIMIT 1"
            );
            $st2Any->execute([$siteName]);
            $row2Any = $st2Any->fetch(PDO::FETCH_ASSOC);
            if ($row2Any && ($row2Any['latitude'] ?? '') !== '' && ($row2Any['longitude'] ?? '') !== '') {
                return [
                    'lat' => (string)$row2Any['latitude'],
                    'lng' => (string)$row2Any['longitude'],
                ];
            }
        } catch (\Throwable $e) {
        }
        return ['lat' => null, 'lng' => null];
    }

    public function index(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        Auth::requireRole(['admin']);
        
        $pdo = Database::pdo();
        
        // Charger tous les référentiels
        $referentials = [
            'maintenance_types' => [],
            'locations' => [],
            'liaisons' => [],
            'sites' => [],
            'incident_causes' => [],
            'priority_levels' => [],
            'equipments' => []
        ];
        
        try {
            // Types de maintenance
            $referentials['maintenance_types'] = $pdo->query('SELECT * FROM maintenance_types ORDER BY name')->fetchAll();
        } catch (\Throwable $e) {}
        
        try {
            // Localisations
            $referentials['locations'] = $pdo->query('SELECT * FROM locations ORDER BY name')->fetchAll();
        } catch (\Throwable $e) {}
        
        try {
            // Liaisons
            $this->ensureLiaisonsGeoColumns($pdo);
            $referentials['liaisons'] = $pdo->query('SELECT * FROM liaisons WHERE active = 1 ORDER BY name')->fetchAll();
        } catch (\Throwable $e) {}
        
        try {
            // Sites
            $referentials['sites'] = $pdo->query('SELECT * FROM sites WHERE active = 1 ORDER BY name')->fetchAll();
        } catch (\Throwable $e) {}
        
        try {
            // Causes probables
            $referentials['incident_causes'] = $pdo->query('SELECT * FROM incident_causes WHERE active = 1 ORDER BY category, name')->fetchAll();
        } catch (\Throwable $e) {}
        
        try {
            // Niveaux de priorité
            $referentials['priority_levels'] = $pdo->query('SELECT * FROM priority_levels ORDER BY sort_order, name')->fetchAll();
        } catch (\Throwable $e) {}
        
        try {
            // Équipements
            $referentials['equipments'] = $pdo->query('SELECT * FROM equipments WHERE active = 1 ORDER BY category, name')->fetchAll();
        } catch (\Throwable $e) {}
        
        $this->view('referentials/index', compact('referentials'));
    }

    public function store(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        Auth::requireRole(['admin']);
        
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') { $this->redirect('/referentials'); }
        
        $type = $_POST['type'] ?? '';
        $id = (int)($_POST['id'] ?? 0);
        $name = trim($_POST['name'] ?? '');
        $category = trim($_POST['category'] ?? '');
        $description = trim($_POST['description'] ?? '');
        $address = trim($_POST['address'] ?? '');
        $city = trim($_POST['city'] ?? '');
        $postalCode = trim($_POST['postal_code'] ?? '');
        $sortOrder = (int)($_POST['sort_order'] ?? 10);
        $color = trim($_POST['color'] ?? '#6c757d');
        $active = isset($_POST['active']) ? 1 : 0;
        
        if (!$name || !$type) { 
            $this->redirect('/referentials?error=missing_data'); 
            return;
        }
        
        $pdo = Database::pdo();
        
        try {
            // Mode modification ou création
            $isUpdate = $id > 0;
            // S'assurer que les tables existent selon le type
            switch ($type) {
                case 'maintenance_type':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS maintenance_types (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        active TINYINT(1) DEFAULT 1,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        UNIQUE KEY name (name)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    
                    if ($isUpdate) {
                        $stmt = $pdo->prepare('UPDATE maintenance_types SET name = ?, active = ? WHERE id = ?');
                        $stmt->execute([$name, $active, $id]);
                    } else {
                        $stmt = $pdo->prepare('INSERT INTO maintenance_types (name, active) VALUES (?, ?)');
                        $stmt->execute([$name, $active]);
                    }
                    break;
                    
                case 'location':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS locations (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        client_id INT NULL,
                        name VARCHAR(255) NOT NULL,
                        address TEXT NULL,
                        city VARCHAR(100) NULL,
                        postal_code VARCHAR(20) NULL,
                        active TINYINT(1) DEFAULT 1,
                        latitude DECIMAL(10,8) NULL,
                        longitude DECIMAL(11,8) NULL,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    
                    $clientId = (int)($_POST['client_id'] ?? 1);
                    if ($isUpdate) {
                        $stmt = $pdo->prepare('UPDATE locations SET name = ?, address = ?, city = ?, postal_code = ?, client_id = ?, active = ? WHERE id = ?');
                        $stmt->execute([$name, $address, $city, $postalCode, $clientId, $active, $id]);
                    } else {
                        $stmt = $pdo->prepare('INSERT INTO locations (name, address, city, postal_code, client_id, active) VALUES (?, ?, ?, ?, ?, ?)');
                        $stmt->execute([$name, $address, $city, $postalCode, $clientId, $active]);
                    }
                    break;
                    
                case 'liaison':
                    $this->ensureLiaisonsGeoColumns($pdo);
                    
                    if ($isUpdate) {
                        $stmt = $pdo->prepare('UPDATE liaisons SET name = ?, description = ?, active = ? WHERE id = ?');
                        $stmt->execute([$name, $description, $active, $id]);
                    } else {
                        $stmt = $pdo->prepare('INSERT INTO liaisons (name, description, active) VALUES (?, ?, ?)');
                        $stmt->execute([$name, $description, $active]);
                        $id = (int)$pdo->lastInsertId();
                    }

                    // Découpage Site A / Site B + coordonnés depuis locations (si existantes)
                    try {
                        [$siteA, $siteB] = $this->parseSitesFromLiaisonName($name);
                        if ($id > 0) {
                            // Les coordonnées sont déduites automatiquement depuis le référentiel "locations".
                            // On stocke uniquement Site A/B pour faciliter l'affichage et l'export.
                            $stUp = $pdo->prepare('UPDATE liaisons SET site_a_name = ?, site_b_name = ? WHERE id = ?');
                            $stUp->execute([$siteA, $siteB, $id]);
                        }
                    } catch (\Throwable $e) {
                    }
                    break;
                    
                case 'site':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS sites (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        description TEXT NULL,
                        active TINYINT(1) DEFAULT 1,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        UNIQUE KEY name (name)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    
                    if ($isUpdate) {
                        $stmt = $pdo->prepare('UPDATE sites SET name = ?, description = ?, active = ? WHERE id = ?');
                        $stmt->execute([$name, $description, $active, $id]);
                    } else {
                        $stmt = $pdo->prepare('INSERT INTO sites (name, description, active) VALUES (?, ?, ?)');
                        $stmt->execute([$name, $description, $active]);
                    }
                    break;
                    
                case 'incident_cause':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS incident_causes (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        category VARCHAR(100) NULL,
                        description TEXT NULL,
                        active TINYINT(1) DEFAULT 1,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    
                    if ($isUpdate) {
                        $stmt = $pdo->prepare('UPDATE incident_causes SET name = ?, category = ?, description = ?, active = ? WHERE id = ?');
                        $stmt->execute([$name, $category, $description, $active, $id]);
                    } else {
                        $stmt = $pdo->prepare('INSERT INTO incident_causes (name, category, description, active) VALUES (?, ?, ?, ?)');
                        $stmt->execute([$name, $category, $description, $active]);
                    }
                    break;
                    
                case 'priority_level':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS priority_levels (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(100) NOT NULL,
                        sort_order INT DEFAULT 10,
                        color VARCHAR(7) DEFAULT '#6c757d',
                        description TEXT NULL,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        UNIQUE KEY name (name)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    
                    if ($isUpdate) {
                        $stmt = $pdo->prepare('UPDATE priority_levels SET name = ?, sort_order = ?, color = ?, description = ? WHERE id = ?');
                        $stmt->execute([$name, $sortOrder, $color, $description, $id]);
                    } else {
                        $stmt = $pdo->prepare('INSERT INTO priority_levels (name, sort_order, color, description) VALUES (?, ?, ?, ?)');
                        $stmt->execute([$name, $sortOrder, $color, $description]);
                    }
                    break;
                    
                case 'equipment':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS equipments (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        category VARCHAR(100) NULL,
                        description TEXT NULL,
                        active TINYINT(1) DEFAULT 1,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    
                    if ($isUpdate) {
                        $stmt = $pdo->prepare('UPDATE equipments SET name = ?, category = ?, description = ?, active = ? WHERE id = ?');
                        $stmt->execute([$name, $category, $description, $active, $id]);
                    } else {
                        $stmt = $pdo->prepare('INSERT INTO equipments (name, category, description, active) VALUES (?, ?, ?, ?)');
                        $stmt->execute([$name, $category, $description, $active]);
                    }
                    break;
                    
                default:
                    $this->redirect('/referentials?error=invalid_type');
                    return;
            }
            
            $this->redirect('/referentials?success=1');
        } catch (\Throwable $e) {
            $this->redirect('/referentials?error=database');
        }
    }

    public function delete(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        Auth::requireRole(['admin']);
        
        $type = $_GET['type'] ?? '';
        $id = (int)($_GET['id'] ?? 0);
        
        if (!$type || !$id) { 
            $this->redirect('/referentials'); 
            return;
        }
        
        $pdo = Database::pdo();
        
        try {
            // S'assurer que les tables existent avant de tenter une suppression/désactivation
            switch ($type) {
                case 'maintenance_type':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS maintenance_types (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        active TINYINT(1) DEFAULT 1,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        UNIQUE KEY name (name)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    $stmt = $pdo->prepare('DELETE FROM maintenance_types WHERE id = ?');
                    break;
                case 'location':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS locations (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        client_id INT NULL,
                        name VARCHAR(255) NOT NULL,
                        address TEXT NULL,
                        city VARCHAR(100) NULL,
                        postal_code VARCHAR(20) NULL,
                        active TINYINT(1) DEFAULT 1,
                        latitude DECIMAL(10,8) NULL,
                        longitude DECIMAL(11,8) NULL,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    $stmt = $pdo->prepare('UPDATE locations SET active = 0 WHERE id = ?');
                    break;
                case 'liaison':
                    $this->ensureLiaisonsGeoColumns($pdo);
                    $stmt = $pdo->prepare('UPDATE liaisons SET active = 0 WHERE id = ?');
                    break;
                case 'site':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS sites (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        description TEXT NULL,
                        active TINYINT(1) DEFAULT 1,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        UNIQUE KEY name (name)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    $stmt = $pdo->prepare('UPDATE sites SET active = 0 WHERE id = ?');
                    break;
                case 'incident_cause':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS incident_causes (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        category VARCHAR(100) NULL,
                        description TEXT NULL,
                        active TINYINT(1) DEFAULT 1,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    $stmt = $pdo->prepare('UPDATE incident_causes SET active = 0 WHERE id = ?');
                    break;
                case 'priority_level':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS priority_levels (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(100) NOT NULL,
                        sort_order INT DEFAULT 10,
                        color VARCHAR(7) DEFAULT '#6c757d',
                        description TEXT NULL,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        UNIQUE KEY name (name)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    $stmt = $pdo->prepare('DELETE FROM priority_levels WHERE id = ?');
                    break;
                case 'equipment':
                    $pdo->exec("CREATE TABLE IF NOT EXISTS equipments (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        category VARCHAR(100) NULL,
                        description TEXT NULL,
                        active TINYINT(1) DEFAULT 1,
                        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                    $stmt = $pdo->prepare('UPDATE equipments SET active = 0 WHERE id = ?');
                    break;
                default:
                    $this->redirect('/referentials');
                    return;
            }
            
            $stmt->execute([$id]);
            $this->redirect('/referentials?deleted=1');
        } catch (\Throwable $e) {
            $this->redirect('/referentials?error=delete_failed');
        }
    }

    public function export(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        Auth::requireRole(['admin']);
        
        $type = $_GET['type'] ?? '';
        if (!$type) { $this->redirect('/referentials'); }
        
        $pdo = Database::pdo();
        
        try {
            $data = [];
            
            switch ($type) {
                case 'maintenance_types':
                    $data = $pdo->query('SELECT * FROM maintenance_types ORDER BY name')->fetchAll();
                    break;
                case 'locations':
                    $data = $pdo->query('SELECT * FROM locations ORDER BY name')->fetchAll();
                    break;
                case 'liaisons':
                    $this->ensureLiaisonsGeoColumns($pdo);
                    $rows = $pdo->query('SELECT * FROM liaisons ORDER BY name')->fetchAll(PDO::FETCH_ASSOC);

                    $headers = [
                        'name',
                        'description',
                        'site_a_name',
                        'site_b_name',
                        'site_a_latitude',
                        'site_a_longitude',
                        'site_b_latitude',
                        'site_b_longitude',
                        'active',
                    ];

                    $data = [];
                    foreach ($rows as $r) {
                        $siteA = $r['site_a_name'] ?? '';
                        $siteB = $r['site_b_name'] ?? '';
                        if ($siteA === '' || $siteB === '') {
                            [$pa, $pb] = $this->parseSitesFromLiaisonName((string)($r['name'] ?? ''));
                            if ($siteA === '' && $pa) $siteA = $pa;
                            if ($siteB === '' && $pb) $siteB = $pb;
                        }

                        // Coordonnées: toujours déduites depuis le référentiel des sites (locations) si possible.
                        // On garde un fallback sur les champs stockés dans liaisons pour compatibilité.
                        $latA = null; $lngA = null; $latB = null; $lngB = null;

                        if ($siteA !== '') {
                            $c = $this->lookupCoordsFromLocations($pdo, $siteA);
                            $latA = $c['lat'];
                            $lngA = $c['lng'];
                        }
                        if (($latA === null || $latA === '') && ($r['site_a_latitude'] ?? '') !== '') $latA = $r['site_a_latitude'];
                        if (($lngA === null || $lngA === '') && ($r['site_a_longitude'] ?? '') !== '') $lngA = $r['site_a_longitude'];

                        if ($siteB !== '') {
                            $c = $this->lookupCoordsFromLocations($pdo, $siteB);
                            $latB = $c['lat'];
                            $lngB = $c['lng'];
                        }
                        if (($latB === null || $latB === '') && ($r['site_b_latitude'] ?? '') !== '') $latB = $r['site_b_latitude'];
                        if (($lngB === null || $lngB === '') && ($r['site_b_longitude'] ?? '') !== '') $lngB = $r['site_b_longitude'];

                        $data[] = [
                            'name' => $r['name'] ?? '',
                            'description' => $r['description'] ?? '',
                            'site_a_name' => $siteA,
                            'site_b_name' => $siteB,
                            'site_a_latitude' => $latA,
                            'site_a_longitude' => $lngA,
                            'site_b_latitude' => $latB,
                            'site_b_longitude' => $lngB,
                            'active' => $r['active'] ?? 1,
                        ];
                    }
                    break;
                case 'sites':
                    $data = $pdo->query('SELECT * FROM sites ORDER BY name')->fetchAll();
                    break;
                case 'incident_causes':
                    $data = $pdo->query('SELECT * FROM incident_causes ORDER BY category, name')->fetchAll();
                    break;
                case 'priority_levels':
                    $data = $pdo->query('SELECT * FROM priority_levels ORDER BY sort_order')->fetchAll();
                    break;
                case 'equipments':
                    $data = $pdo->query('SELECT * FROM equipments ORDER BY category, name')->fetchAll();
                    break;
                default:
                    $this->redirect('/referentials?error=invalid_type');
                    return;
            }
            
            // Export en CSV
            $filename = $type . '_' . date('Y-m-d_H-i-s') . '.csv';
            
            header('Content-Type: text/csv; charset=utf-8');
            header('Content-Disposition: attachment; filename="' . $filename . '"');
            header('Pragma: no-cache');
            header('Expires: 0');
            
            $output = fopen('php://output', 'w');
            
            // En-têtes CSV
            if (!empty($data)) {
                if (isset($headers) && is_array($headers) && count($headers) > 0) {
                    fputcsv($output, $headers);
                    foreach ($data as $row) {
                        $line = [];
                        foreach ($headers as $h) {
                            $line[] = $row[$h] ?? '';
                        }
                        fputcsv($output, $line);
                    }
                } else {
                    fputcsv($output, array_keys($data[0]));
                    foreach ($data as $row) {
                        fputcsv($output, $row);
                    }
                }
            }
            
            fclose($output);
            exit;
            
        } catch (\Throwable $e) {
            $this->redirect('/referentials?error=export_failed');
        }
    }

    public function template(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        Auth::requireRole(['admin']);
        
        $type = $_GET['type'] ?? '';
        
        if (!$type) {
            die('Type manquant');
        }
        
        // Définir les en-têtes selon le type de table
        $headers = [];
        $exampleData = [];
        
        switch ($type) {
            case 'maintenance_types':
                $headers = ['name', 'active'];
                $exampleData = ['Maintenance préventive', '1'];
                break;
            case 'locations':
                $headers = ['name', 'client_id', 'address', 'city', 'postal_code', 'latitude', 'longitude', 'active'];
                $exampleData = ['MARC ANADER', '1', '', '', '', '5.3500000', '-3.9800000', '1'];
                break;
            case 'liaisons':
                $headers = ['name', 'description', 'site_a_name', 'site_b_name', 'site_a_latitude', 'site_a_longitude', 'site_b_latitude', 'site_b_longitude', 'active'];
                // Coordonnées: auto-déduites depuis "locations". Laissez vide dans le template.
                $exampleData = ['FO_AC002-TO-ET082 [MARC_ANADER vers ECG_BIETRY]', 'Description exemple', 'MARC_ANADER', 'ECG_BIETRY', '', '', '', '', '1'];
                break;
            case 'sites':
                $headers = ['name', 'description', 'active'];
                $exampleData = ['Site A', 'Description exemple', '1'];
                break;
            case 'incident_causes':
                $headers = ['name', 'category', 'description', 'active'];
                $exampleData = ['Panne matérielle', 'Technique', 'Description exemple', '1'];
                break;
            case 'priority_levels':
                $headers = ['name', 'sort_order', 'color', 'description'];
                $exampleData = ['Critique', '1', '#FF0000', 'Priorité maximale'];
                break;
            case 'equipments':
                $headers = ['name', 'category', 'description', 'active'];
                $exampleData = ['Routeur', 'Réseau', 'Description exemple', '1'];
                break;
            default:
                die('Type invalide');
        }
        
        // Générer le template CSV
        $filename = 'template_' . $type . '.csv';
        
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Pragma: no-cache');
        header('Expires: 0');
        
        $output = fopen('php://output', 'w');
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF)); // BOM UTF-8
        
        // En-têtes
        fputcsv($output, $headers);
        
        // Ligne d'exemple
        fputcsv($output, $exampleData);
        
        fclose($output);
        exit;
    }

    public function import(): void
    {
        if (!Auth::check()) {
            echo json_encode(['success' => false, 'error' => 'Non authentifié']);
            exit;
        }
        
        Auth::requireRole(['admin']);
        
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            echo json_encode(['success' => false, 'error' => 'Méthode non autorisée']);
            exit;
        }
        
        $type = $_POST['type'] ?? '';
        
        if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
            echo json_encode(['success' => false, 'error' => 'Fichier non uploadé']);
            exit;
        }
        
        $file = $_FILES['file'];
        $extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        
        if (!in_array($extension, ['csv', 'json'])) {
            echo json_encode(['success' => false, 'error' => 'Format non supporté (CSV ou JSON uniquement)']);
            exit;
        }
        
        $pdo = Database::pdo();
        $imported = 0;
        
        try {
            if ($extension === 'csv') {
                $handle = fopen($file['tmp_name'], 'r');
                $headers = fgetcsv($handle);
                
                // Nettoyer les en-têtes (enlever BOM, espaces, etc.)
                $headers = array_map(function($h) { return trim(str_replace("\xEF\xBB\xBF", '', $h)); }, $headers);
                
                while (($row = fgetcsv($handle)) !== false) {
                    // Skip les lignes vides ou incomplètes
                    if (empty($row) || count(array_filter($row)) === 0) continue;
                    
                    // S'assurer que le nombre de colonnes correspond
                    if (count($row) !== count($headers)) {
                        // Ajuster la taille du tableau
                        $row = array_pad($row, count($headers), '');
                    }
                    
                    $data = array_combine($headers, $row);
                    
                    // Validation: le champ 'name' est obligatoire
                    if (empty(trim($data['name'] ?? ''))) {
                        continue; // Skip cette ligne si pas de nom
                    }
                    
                    // Nettoyer et valider les données
                    $cleanData = array_map('trim', $data);
                    
                    switch ($type) {
                        case 'maintenance_types':
                            $stmt = $pdo->prepare('INSERT INTO maintenance_types (name, active) VALUES (?, ?) ON DUPLICATE KEY UPDATE active = VALUES(active)');
                            $name = $cleanData['name'] ?? '';
                            $active = isset($cleanData['active']) ? (int)$cleanData['active'] : 1;
                            if (empty($name)) continue 2; // Skip si nom vide
                            $stmt->execute([$name, $active]);
                            break;
                        case 'locations':
                            // S'assurer que les colonnes existent (compat prod)
                            $pdo->exec("CREATE TABLE IF NOT EXISTS locations (
                                id INT AUTO_INCREMENT PRIMARY KEY,
                                client_id INT NULL,
                                name VARCHAR(255) NOT NULL,
                                address TEXT NULL,
                                city VARCHAR(100) NULL,
                                postal_code VARCHAR(20) NULL,
                                active TINYINT(1) DEFAULT 1,
                                latitude DECIMAL(10,8) NULL,
                                longitude DECIMAL(11,8) NULL,
                                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                                updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

                            $name = $cleanData['name'] ?? '';
                            $clientId = isset($cleanData['client_id']) ? (int)$cleanData['client_id'] : 1;
                            $address = $cleanData['address'] ?? '';
                            $city = $cleanData['city'] ?? '';
                            $postal = $cleanData['postal_code'] ?? '';
                            $lat = $this->normalizeDecimal($cleanData['latitude'] ?? null);
                            $lng = $this->normalizeDecimal($cleanData['longitude'] ?? null);
                            $active = isset($cleanData['active']) ? (int)$cleanData['active'] : 1;
                            if (empty($name)) continue 2;

                            // IMPORTANT: sur certaines bases, locations.name n'est pas UNIQUE => ON DUPLICATE KEY ne s'applique pas.
                            // On fait donc UPDATE par name, puis INSERT si aucun enregistrement.
                            $upd = $pdo->prepare(
                                'UPDATE locations '
                                . 'SET client_id = ?, address = ?, city = ?, postal_code = ?, '
                                . 'latitude = COALESCE(?, latitude), longitude = COALESCE(?, longitude), active = ? '
                                . 'WHERE name = ?'
                            );
                            $upd->execute([$clientId, $address, $city, $postal, $lat, $lng, $active, $name]);

                            if ($upd->rowCount() === 0) {
                                $ins = $pdo->prepare(
                                    'INSERT INTO locations (name, client_id, address, city, postal_code, latitude, longitude, active) '
                                    . 'VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
                                );
                                $ins->execute([$name, $clientId, $address, $city, $postal, $lat, $lng, $active]);
                            }
                            break;
                        case 'liaisons':
                            $this->ensureLiaisonsGeoColumns($pdo);
                            $name = $cleanData['name'] ?? '';
                            $description = $cleanData['description'] ?? '';
                            $active = isset($cleanData['active']) ? (int)$cleanData['active'] : 1;
                            if (empty($name)) continue 2;

                            $siteA = $cleanData['site_a_name'] ?? '';
                            $siteB = $cleanData['site_b_name'] ?? '';
                            if ($siteA === '' || $siteB === '') {
                                [$pa, $pb] = $this->parseSitesFromLiaisonName($name);
                                if ($siteA === '' && $pa) $siteA = $pa;
                                if ($siteB === '' && $pb) $siteB = $pb;
                            }

                            // Si le CSV fournit des coordonnées, on crée/met à jour automatiquement les sites dans locations
                            $latA = $cleanData['site_a_latitude'] ?? null;
                            $lngA = $cleanData['site_a_longitude'] ?? null;
                            $latB = $cleanData['site_b_latitude'] ?? null;
                            $lngB = $cleanData['site_b_longitude'] ?? null;
                            try {
                                if ($siteA !== '') { $this->upsertLocationCoords($pdo, $siteA, $latA, $lngA); }
                                if ($siteB !== '') { $this->upsertLocationCoords($pdo, $siteB, $latB, $lngB); }
                            } catch (\Throwable $e) {
                            }

                            // On ne saisit pas les coordonnées au niveau de la liaison.
                            // Les coordonnées sont déduites automatiquement depuis le référentiel "locations".
                            $stmt = $pdo->prepare(
                                'INSERT INTO liaisons (name, description, site_a_name, site_b_name, active) '
                                . 'VALUES (?, ?, ?, ?, ?) '
                                . 'ON DUPLICATE KEY UPDATE '
                                . 'description = VALUES(description), '
                                . 'site_a_name = COALESCE(NULLIF(VALUES(site_a_name), \'\'), site_a_name), '
                                . 'site_b_name = COALESCE(NULLIF(VALUES(site_b_name), \'\'), site_b_name), '
                                . 'active = VALUES(active)'
                            );
                            $stmt->execute([$name, $description, $siteA, $siteB, $active]);
                            break;
                        case 'sites':
                            $stmt = $pdo->prepare('INSERT INTO sites (name, description, active) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE description = VALUES(description), active = VALUES(active)');
                            $name = $cleanData['name'] ?? '';
                            $description = $cleanData['description'] ?? '';
                            $active = isset($cleanData['active']) ? (int)$cleanData['active'] : 1;
                            if (empty($name)) continue 2;
                            $stmt->execute([$name, $description, $active]);
                            break;
                        case 'incident_causes':
                            $stmt = $pdo->prepare('INSERT INTO incident_causes (name, category, description, active) VALUES (?, ?, ?, ?)');
                            $name = $cleanData['name'] ?? '';
                            $category = $cleanData['category'] ?? '';
                            $description = $cleanData['description'] ?? '';
                            $active = isset($cleanData['active']) ? (int)$cleanData['active'] : 1;
                            if (empty($name)) continue 2;
                            $stmt->execute([$name, $category, $description, $active]);
                            break;
                        case 'priority_levels':
                            $stmt = $pdo->prepare('INSERT INTO priority_levels (name, sort_order, color, description) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order), color = VALUES(color), description = VALUES(description)');
                            $name = $cleanData['name'] ?? '';
                            $sortOrder = isset($cleanData['sort_order']) ? (int)$cleanData['sort_order'] : 10;
                            $color = $cleanData['color'] ?? '#6c757d';
                            $description = $cleanData['description'] ?? '';
                            if (empty($name)) continue 2;
                            $stmt->execute([$name, $sortOrder, $color, $description]);
                            break;
                        case 'equipments':
                            $stmt = $pdo->prepare('INSERT INTO equipments (name, category, description, active) VALUES (?, ?, ?, ?)');
                            $name = $cleanData['name'] ?? '';
                            $category = $cleanData['category'] ?? '';
                            $description = $cleanData['description'] ?? '';
                            $active = isset($cleanData['active']) ? (int)$cleanData['active'] : 1;
                            if (empty($name)) continue 2;
                            $stmt->execute([$name, $category, $description, $active]);
                            break;
                    }
                    
                    $imported++;
                }
                
                fclose($handle);
            }
            
            echo json_encode(['success' => true, 'imported' => $imported]);
            
        } catch (\Throwable $e) {
            echo json_encode(['success' => false, 'error' => $e->getMessage()]);
        }
        
        exit;
    }
}