-- =============================================================
-- FiberOps GMAO - Dump SQL de production
-- Nom de base: c2685816c_maintenance_back
-- Objectif: Créer l'ensemble du schéma et des données de base
-- Date: 17 décembre 2025
-- =============================================================

-- Sécurité et encodage
SET NAMES utf8mb4;
SET SQL_MODE = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION";

-- Création base (si absente) et sélection
CREATE DATABASE IF NOT EXISTS c2685816c_maintenance_back
  CHARACTER SET utf8mb4 ;
USE c2685816c_maintenance_back;

-- =============================================================
-- 1. Sécurité / Utilisateurs / Paramétrage
-- =============================================================
CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(50) UNIQUE NOT NULL,
  label VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(100) UNIQUE NOT NULL,
  label VARCHAR(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role_key VARCHAR(50) NOT NULL DEFAULT 'agent',
  technician_type VARCHAR(50) NULL,
  totp_secret VARCHAR(64) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- 2. Référentiels métier de base (Clients / Sites / Liaisons / Équipements)
-- =============================================================
-- Table clients (version complète)
CREATE TABLE IF NOT EXISTS `clients` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL COMMENT 'Nom du client',
  `email` VARCHAR(255) DEFAULT NULL COMMENT 'Email du client',
  `phone` VARCHAR(50) DEFAULT NULL COMMENT 'Téléphone principal',
  `address` TEXT DEFAULT NULL COMMENT 'Adresse complète',
  `city` VARCHAR(100) DEFAULT NULL COMMENT 'Ville',
  `country` VARCHAR(100) DEFAULT 'Côte d\'Ivoire' COMMENT 'Pays',
  `contact_person` VARCHAR(255) DEFAULT NULL COMMENT 'Nom de la personne de contact',
  `contact_phone` VARCHAR(50) DEFAULT NULL COMMENT 'Téléphone de la personne de contact',
  `notes` TEXT DEFAULT NULL COMMENT 'Notes internes',
  `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Client actif (1) ou inactif (0)',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_active` (`active`),
  KEY `idx_email` (`email`),
  KEY `idx_city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COMMENT='Table des clients';

-- Table locations (liées à clients)
CREATE TABLE IF NOT EXISTS `locations` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `address` TEXT,
  `city` VARCHAR(100),
  `postal_code` VARCHAR(20),
  `latitude` DECIMAL(10,7),
  `longitude` DECIMAL(10,7),
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client` (`client_id`),
  KEY `idx_name` (`name`),
  CONSTRAINT `fk_locations_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COMMENT='Sites/implantations des clients';

-- Liaisons
CREATE TABLE IF NOT EXISTS `liaisons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `site_a_name` varchar(255) DEFAULT NULL,
  `site_b_name` varchar(255) DEFAULT NULL,
  `site_a_latitude` decimal(10,7) DEFAULT NULL,
  `site_a_longitude` decimal(10,7) DEFAULT NULL,
  `site_b_latitude` decimal(10,7) DEFAULT NULL,
  `site_b_longitude` decimal(10,7) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sites
CREATE TABLE IF NOT EXISTS `sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Équipements
CREATE TABLE IF NOT EXISTS `equipments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Priorités d'incidents
CREATE TABLE IF NOT EXISTS `priority_levels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sort_order` int(11) DEFAULT 10,
  `color` varchar(7) DEFAULT '#6c757d',
  `description` text DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- 3. Incidents
-- =============================================================
CREATE TABLE IF NOT EXISTS incident_causes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  category VARCHAR(80) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incident_statuses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(50) UNIQUE NOT NULL,
  label VARCHAR(100) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  color VARCHAR(7) DEFAULT '#6c757d',
  sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incidents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id VARCHAR(40) UNIQUE NOT NULL,
  client_id INT NOT NULL,
  location_id INT NOT NULL,
  cause_id INT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  priority ENUM('Basse','Moyenne','Haute','Urgent') NOT NULL DEFAULT 'Moyenne',
  status_id INT NOT NULL,
  declared_by INT NOT NULL,
  declared_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expected_response_at TIMESTAMP NULL,
  expected_resolution_at TIMESTAMP NULL,
  resolved_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id),
  FOREIGN KEY (location_id) REFERENCES locations(id),
  FOREIGN KEY (cause_id) REFERENCES incident_causes(id),
  FOREIGN KEY (status_id) REFERENCES incident_statuses(id),
  FOREIGN KEY (declared_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incident_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id INT NOT NULL,
  user_id INT NOT NULL,
  assigned_by INT NOT NULL,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incident_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id INT NOT NULL,
  field_name VARCHAR(50) NOT NULL,
  old_value TEXT NULL,
  new_value TEXT NULL,
  changed_by INT NOT NULL,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
  FOREIGN KEY (changed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incident_comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id INT NOT NULL,
  user_id INT NOT NULL,
  comment TEXT NOT NULL,
  is_internal TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- 4. Catalogue coûts & devis
-- =============================================================
CREATE TABLE IF NOT EXISTS cost_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cost_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  designation VARCHAR(200) NOT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'u',
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency VARCHAR(3) NOT NULL DEFAULT 'XOF',
  FOREIGN KEY (category_id) REFERENCES cost_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS quotes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quote_no VARCHAR(40) UNIQUE NOT NULL,
  client_name VARCHAR(150) NOT NULL,
  related_type ENUM('incident','preventive','') NOT NULL DEFAULT '',
  related_ref VARCHAR(64) NULL,
  quote_date DATE NOT NULL,
  status ENUM('En attente','Validé','Rejeté') NOT NULL DEFAULT 'En attente',
  total_ht DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_tva DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_ttc DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency VARCHAR(3) NOT NULL DEFAULT 'XOF'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS quote_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quote_id INT NOT NULL,
  designation VARCHAR(200) NOT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'u',
  quantity DECIMAL(12,3) NOT NULL DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_line DECIMAL(12,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- 5. SLA et planning
-- =============================================================
CREATE TABLE IF NOT EXISTS maintenance_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sla_policies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  maintenance_type_id INT NOT NULL,
  priority ENUM('Basse','Moyenne','Haute','Urgent') NOT NULL DEFAULT 'Moyenne',
  response_minutes INT NOT NULL DEFAULT 60,
  resolution_minutes INT NOT NULL DEFAULT 240,
  FOREIGN KEY (maintenance_type_id) REFERENCES maintenance_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS planning_tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  type ENUM('préventive','corrective') NOT NULL DEFAULT 'corrective',
  start_datetime DATETIME NOT NULL,
  end_datetime DATETIME NULL,
  status ENUM('Planifié','En cours','Terminé','Annulé') NOT NULL DEFAULT 'Planifié',
  technician_id INT NULL,
  incident_id INT NULL,
  location_id INT NULL,
  notes TEXT NULL,
  color VARCHAR(7) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (technician_id) REFERENCES users(id),
  FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE SET NULL,
  FOREIGN KEY (location_id) REFERENCES locations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- 6. SEEDS (Données initiales)
-- =============================================================
-- Rôles
INSERT IGNORE INTO roles (id, key_name, label) VALUES
  (1,'admin','Administrateur'),
  (2,'agent','Agent'),
  (3,'technicien','Technicien');

-- Utilisateur admin (mot de passe hashé: Admin@12345)
INSERT INTO users (name, email, password_hash, role_key, active)
VALUES ('Admin', 'admin@fiberops.local', '$2y$10$w2xB6m0nKcJrJ9C2k3XlRebmW0b6D4k9hA9YbZGmEo4YbE3XvAcpm', 'admin', 1)
ON DUPLICATE KEY UPDATE email=email;

-- Permissions
INSERT IGNORE INTO permissions (key_name, label) VALUES
('dashboard.view', 'Voir tableau de bord'),
('users.list', 'Lister utilisateurs'),
('users.create', 'Créer utilisateur'),
('users.edit', 'Modifier utilisateur'),
('users.delete', 'Supprimer utilisateur'),
('users.toggle_2fa', 'Activer/Désactiver 2FA'),
('roles.list', 'Lister rôles'),
('roles.create', 'Créer rôle'),
('roles.edit', 'Modifier rôle'),
('roles.delete', 'Supprimer rôle'),
('settings.view', 'Voir paramètres'),
('settings.edit', 'Modifier paramètres'),
('catalog.view', 'Voir catalogue'),
('catalog.create', 'Créer catégorie/article'),
('catalog.edit', 'Modifier catégorie/article'),
('catalog.delete', 'Supprimer catégorie/article'),
('quotes.list', 'Lister devis'),
('quotes.create', 'Créer devis'),
('quotes.edit', 'Modifier devis'),
('quotes.delete', 'Supprimer devis'),
('sla.view', 'Voir SLA'),
('sla.create', 'Créer SLA'),
('sla.edit', 'Modifier SLA'),
('sla.delete', 'Supprimer SLA');

-- Permissions admin (toutes)
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.key_name = 'admin';

-- Permissions agent
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.key_name = 'agent' AND p.key_name IN ('dashboard.view', 'quotes.list', 'quotes.create', 'quotes.edit');

-- Permissions technicien
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.key_name = 'technicien' AND p.key_name IN ('dashboard.view');

-- Statuts système incidents
INSERT IGNORE INTO incident_statuses (key_name, label, is_system, color, sort_order) VALUES
('open', 'Ouvert', 1, '#dc3545', 10),
('assigned', 'Assigné', 1, '#fd7e14', 20),
('in_progress', 'En cours', 1, '#0d6efd', 30),
('resolved_temp', 'Résolu provisoire', 1, '#198754', 40),
('resolved_final', 'Résolu définitif', 1, '#20c997', 50),
('cancelled', 'Annulé', 1, '#6c757d', 60);

-- Référentiels de base
INSERT IGNORE INTO `priority_levels` (`name`, `sort_order`, `color`, `description`) VALUES
('Critique', 1, '#dc3545', 'Incident critique nécessitant une intervention immédiate'),
('Haute', 2, '#fd7e14', 'Incident à haute priorité'),
('Normale', 3, '#ffc107', 'Incident à priorité normale'),
('Basse', 4, '#28a745', 'Incident à basse priorité');

INSERT IGNORE INTO `equipments` (`name`, `category`, `description`, `active`) VALUES
('Serveur principal', 'Serveur', 'Serveur de production principal', 1),
('Switch réseau', 'Réseau', 'Commutateur réseau principal', 1),
('Routeur', 'Réseau', 'Routeur de connexion internet', 1),
('Firewall', 'Sécurité', 'Pare-feu de sécurité', 1),
('Fibre optique', 'Fibre', 'Infrastructure fibre optique', 1),
('UPS', 'Infrastructure', 'Système d\'alimentation sans interruption', 1);

INSERT IGNORE INTO `liaisons` (`name`, `description`, `active`) VALUES
('Liaison principale', 'Liaison fibre principale', 1),
('Liaison de secours', 'Liaison de backup', 1),
('Liaison interne', 'Liaison réseau interne', 1);

INSERT IGNORE INTO `sites` (`name`, `description`, `active`) VALUES
('Abidjan Centre', 'Site principal d\'Abidjan', 1),
('Bouaké', 'Site de Bouaké', 1),
('San Pedro', 'Site de San Pedro', 1),
('Yamoussoukro', 'Site de Yamoussoukro', 1);

-- Causes d'incidents
INSERT IGNORE INTO incident_causes (name, category) VALUES
('Coupure fibre optique', 'Infrastructure'),
('Panne équipement réseau', 'Matériel'),
('Surcharge trafic', 'Capacité'),
('Coupure électrique', 'Énergie'),
('Problème climatisation', 'Environnement'),
('Erreur configuration', 'Logiciel'),
('Intrusion sécuritaire', 'Sécurité');

-- Clients de démonstration
INSERT IGNORE INTO `clients` (`id`, `name`, `email`, `phone`, `address`, `city`, `country`, `contact_person`, `contact_phone`, `notes`, `active`) VALUES
(1, 'Orange Côte d\'Ivoire', 'contact@orange.ci', '+225 27 20 10 00 00', 'Boulevard Lagunaire, Cocody', 'Abidjan', 'Côte d\'Ivoire', 'Jean KOUASSI', '+225 07 12 34 56 78', 'Client principal - Opérateur télécom', 1),
(2, 'MTN CI', 'info@mtn.ci', '+225 27 20 30 00 00', 'Avenue Terrasson de Fougères, Plateau', 'Abidjan', 'Côte d\'Ivoire', 'Marie DIABATE', '+225 05 23 45 67 89', 'Opérateur télécom majeur', 1),
(3, 'Banque Atlantique', 'contact@banqueatlantique.ci', '+225 27 21 15 00 00', 'Boulevard Carde, Plateau', 'Abidjan', 'Côte d\'Ivoire', 'Pierre TOURE', '+225 01 34 56 78 90', 'Secteur bancaire', 1);

-- Locations associées aux clients (exemple)
INSERT IGNORE INTO locations (client_id, name, address, city, latitude, longitude, active) VALUES
(1, 'Orange - Siège Cocody', 'Boulevard Lagunaire, Cocody', 'Abidjan', 5.3677778, -3.9708333, 1),
(1, 'Orange - Plateau', 'Avenue Chardy, Plateau', 'Abidjan', 5.3166667, -4.0333333, 1),
(2, 'MTN - Siège', 'Avenue Terrasson de Fougères', 'Abidjan', NULL, NULL, 1),
(3, 'Banque Atlantique - Plateau', 'Boulevard Carde', 'Abidjan', NULL, NULL, 1);

-- Types de maintenance
INSERT IGNORE INTO maintenance_types (name) VALUES
('Maintenance corrective'),
('Maintenance préventive'),
('Installation'),
('Migration'),
('Audit technique');

-- SLA par défaut
INSERT IGNORE INTO sla_policies (maintenance_type_id, priority, response_minutes, resolution_minutes)
SELECT mt.id, 'Basse', 480, 1440 FROM maintenance_types mt WHERE mt.name = 'Maintenance préventive'
UNION ALL SELECT mt.id, 'Moyenne', 240, 720 FROM maintenance_types mt WHERE mt.name = 'Maintenance préventive'
UNION ALL SELECT mt.id, 'Haute', 120, 360 FROM maintenance_types mt WHERE mt.name = 'Maintenance préventive'
UNION ALL SELECT mt.id, 'Urgent', 60, 180 FROM maintenance_types mt WHERE mt.name = 'Maintenance préventive'
UNION ALL SELECT mt.id, 'Basse', 240, 720 FROM maintenance_types mt WHERE mt.name = 'Maintenance corrective'
UNION ALL SELECT mt.id, 'Moyenne', 120, 480 FROM maintenance_types mt WHERE mt.name = 'Maintenance corrective'
UNION ALL SELECT mt.id, 'Haute', 60, 240 FROM maintenance_types mt WHERE mt.name = 'Maintenance corrective'
UNION ALL SELECT mt.id, 'Urgent', 30, 120 FROM maintenance_types mt WHERE mt.name = 'Maintenance corrective';

-- Exemple planning
INSERT IGNORE INTO planning_tasks (title, type, start_datetime, end_datetime, status, technician_id, incident_id, location_id, notes, color) VALUES
('Préventif - Inspection Cocody', 'préventive', '2025-11-06 09:00:00', '2025-11-06 12:00:00', 'Planifié', NULL, NULL, 1, 'Contrôle climatisation et filtres', '#0d6efd'),
('Correctif - Liaison Plateau', 'corrective', '2025-11-06 14:00:00', '2025-11-06 16:30:00', 'Planifié', NULL, 1, 1, 'Réparation fibre optique', '#dc3545');

-- =============================================================
-- 7. Vérifications rapides (facultatif)
-- =============================================================
-- SELECT COUNT(*) AS users_total FROM users;
-- SELECT COUNT(*) AS clients_total FROM clients;
-- SELECT COUNT(*) AS locations_total FROM locations;
-- SELECT COUNT(*) AS incidents_statuses_total FROM incident_statuses;
-- SELECT COUNT(*) AS catalog_items_total FROM cost_items;
-- SELECT COUNT(*) AS planning_total FROM planning_tasks;

-- Fin du script