-- Export de la base de données: c2685816c_maintenance_back
-- Date: 2025-12-18 11:47:39
-- Généré par: export_database.php

SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET time_zone = '+00:00';

-- --------------------------------------------------------
-- Structure de la table `clients`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `id` int 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  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  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 AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4  COMMENT='Table des clients';

-- Données de la table `clients`

INSERT INTO `clients` (`id`, `name`, `email`, `phone`, `address`, `city`, `country`, `contact_person`, `contact_phone`, `notes`, `active`, `created_at`, `updated_at`) 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', '2025-12-15 12:11:38', NULL);
INSERT INTO `clients` (`id`, `name`, `email`, `phone`, `address`, `city`, `country`, `contact_person`, `contact_phone`, `notes`, `active`, `created_at`, `updated_at`) VALUES ('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', '2025-12-15 12:11:38', NULL);
INSERT INTO `clients` (`id`, `name`, `email`, `phone`, `address`, `city`, `country`, `contact_person`, `contact_phone`, `notes`, `active`, `created_at`, `updated_at`) VALUES ('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', '2025-12-15 12:11:38', NULL);

-- --------------------------------------------------------
-- Structure de la table `equipments`
-- --------------------------------------------------------

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

-- Données de la table `equipments`

INSERT INTO `equipments` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('1', 'Serveur principal', 'Serveur', 'Serveur de production principal', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `equipments` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('2', 'Switch r├®seau', 'R├®seau', 'Commutateur r├®seau principal', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `equipments` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('3', 'Routeur', 'R├®seau', 'Routeur de connexion internet', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `equipments` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('4', 'Firewall', 'S├®curit├®', 'Pare-feu de s├®curit├®', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `equipments` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('5', 'Fibre optique', 'Fibre', 'Infrastructure fibre optique', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `equipments` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('6', 'UPS', 'Infrastructure', 'Système d\'alimentation sans interruption', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');

-- --------------------------------------------------------
-- Structure de la table `incident_assignments`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_assignments`;
CREATE TABLE `incident_assignments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `user_id` int NOT NULL,
  `assigned_by` int DEFAULT NULL,
  `assigned_at` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `idx_incident` (`incident_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_active` (`active`),
  KEY `fk_incident_assignments_assigned_by` (`assigned_by`),
  CONSTRAINT `fk_incident_assignments_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_incident_assignments_incident` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_incident_assignments_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `incident_assignments`

INSERT INTO `incident_assignments` (`id`, `incident_id`, `user_id`, `assigned_by`, `assigned_at`, `active`) VALUES ('1', '3', '3', '1', '2025-12-17 11:36:23', '0');
INSERT INTO `incident_assignments` (`id`, `incident_id`, `user_id`, `assigned_by`, `assigned_at`, `active`) VALUES ('2', '3', '3', '1', '2025-12-17 11:41:07', '1');
INSERT INTO `incident_assignments` (`id`, `incident_id`, `user_id`, `assigned_by`, `assigned_at`, `active`) VALUES ('3', '1', '3', '1', '2025-12-17 12:34:58', '1');

-- --------------------------------------------------------
-- Structure de la table `incident_attachments`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_attachments`;
CREATE TABLE `incident_attachments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `report_id` int NOT NULL,
  `type` varchar(20) NOT NULL,
  `path` varchar(255) NOT NULL,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `observation` text,
  `lat` decimal(10,7) DEFAULT NULL,
  `lng` decimal(11,7) DEFAULT NULL,
  `taken_by` varchar(255) DEFAULT NULL,
  `taken_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `incident_attachments`

INSERT INTO `incident_attachments` (`id`, `report_id`, `type`, `path`, `uploaded_at`, `observation`, `lat`, `lng`, `taken_by`, `taken_at`) VALUES ('1', '1', 'intervention_photo', 'incidents/1/interv_photo_6942d5811e04f.jpeg', '2025-12-17 16:08:33', 'Test OK', '5.3680920', '-3.9900960', 'Admin', '2025-12-17 16:07:00');

-- --------------------------------------------------------
-- Structure de la table `incident_causes`
-- --------------------------------------------------------

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

-- Données de la table `incident_causes`

INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('1', 'Panne de courant', 'Environnementale', 'Coupure d\'├®lectricit├®', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('2', 'D├®faillance mat├®rielle', 'Technique', 'Panne d\'├®quipement', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('3', 'Erreur humaine', 'Humaine', 'Erreur de manipulation', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('4', 'Probl├¿me r├®seau', 'R├®seau', 'Dysfonctionnement du r├®seau', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('5', 'Surcharge syst├¿me', 'Syst├¿me', 'Syst├¿me surcharg├®', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('6', 'Faille de s├®curit├®', 'S├®curit├®', 'Probl├¿me de s├®curit├® informatique', '1', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('7', 'Coupure fibre optique', 'Infrastructure', NULL, '1', '2025-12-17 09:50:35', '2025-12-17 09:50:35');
INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('8', 'Panne équipement réseau', 'Matériel', NULL, '1', '2025-12-17 09:50:35', '2025-12-17 09:50:35');
INSERT INTO `incident_causes` (`id`, `name`, `category`, `description`, `active`, `created_at`, `updated_at`) VALUES ('9', 'Surcharge trafic', 'Capacité', NULL, '1', '2025-12-17 09:50:35', '2025-12-17 09:50:35');

-- --------------------------------------------------------
-- Structure de la table `incident_comment_attachments`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_comment_attachments`;
CREATE TABLE `incident_comment_attachments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `comment_id` int NOT NULL,
  `path` varchar(255) NOT NULL,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `comment_id` (`comment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- --------------------------------------------------------
-- Structure de la table `incident_comments`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_comments`;
CREATE TABLE `incident_comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `user_id` int NOT NULL,
  `content` text NOT NULL,
  `parent_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `user_id` (`user_id`),
  KEY `parent_id` (`parent_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- --------------------------------------------------------
-- Structure de la table `incident_final_report_attachments`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_final_report_attachments`;
CREATE TABLE `incident_final_report_attachments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `final_report_id` int NOT NULL,
  `path` varchar(255) NOT NULL,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `final_report_id` (`final_report_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- --------------------------------------------------------
-- Structure de la table `incident_final_reports`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_final_reports`;
CREATE TABLE `incident_final_reports` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `supervisor_id` int DEFAULT NULL,
  `restoration_at` datetime DEFAULT NULL,
  `client_supervisor` varchar(255) DEFAULT NULL,
  `comments` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `incident_id` (`incident_id`),
  KEY `incident_id_2` (`incident_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `incident_final_reports`

INSERT INTO `incident_final_reports` (`id`, `incident_id`, `supervisor_id`, `restoration_at`, `client_supervisor`, `comments`, `created_at`, `updated_at`) VALUES ('1', '1', '1', NULL, NULL, NULL, '2025-12-17 16:08:51', '2025-12-17 16:08:51');

-- --------------------------------------------------------
-- Structure de la table `incident_history`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_history`;
CREATE TABLE `incident_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `old_value` text,
  `new_value` text,
  `changed_by` int DEFAULT NULL,
  `changed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `changed_by` (`changed_by`),
  KEY `changed_at` (`changed_at`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `incident_history`

INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('1', '1', 'Intervention', NULL, 'Intervention démarrée', '1', '2025-12-17 11:29:43');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('2', '1', 'Intervention', NULL, 'Intervention en pause — Motif: Test', '1', '2025-12-17 11:32:40');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('3', '3', 'Assignment', NULL, 'Assigné à tech', '1', '2025-12-17 11:36:23');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('4', '3', 'Assignment', NULL, 'Désassigné: tech', '1', '2025-12-17 11:41:02');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('5', '3', 'Assignment', NULL, 'Assigné à tech', '1', '2025-12-17 11:41:07');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('6', '3', 'Intervention', NULL, 'Intervention démarrée', '1', '2025-12-17 11:42:06');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('7', '3', 'Intervention', NULL, 'Intervention en pause — Motif: Rest', '1', '2025-12-17 11:42:17');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('8', '1', 'Assignment', NULL, 'Assigné à tech', '1', '2025-12-17 12:34:58');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('9', '1', 'Status', 'Nouveau', 'Traité', '1', '2025-12-17 16:08:51');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('10', '1', 'Action', NULL, 'Compléter le rapport final (date de remontée, commentaire, PJ)', '1', '2025-12-17 16:08:51');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('11', '1', 'Intervention', NULL, 'Intervention reprise', '1', '2025-12-17 16:14:43');
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES ('12', '1', 'Status', 'Traité', 'Clôturé (rapport final)', '1', '2025-12-17 18:31:47');

-- --------------------------------------------------------
-- Structure de la table `incident_intervention_events`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_intervention_events`;
CREATE TABLE `incident_intervention_events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `user_id` int NOT NULL,
  `action` varchar(10) NOT NULL,
  `reason` text,
  `happened_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `user_id` (`user_id`),
  KEY `happened_at` (`happened_at`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `incident_intervention_events`

INSERT INTO `incident_intervention_events` (`id`, `incident_id`, `user_id`, `action`, `reason`, `happened_at`) VALUES ('1', '1', '1', 'start', NULL, '2025-12-17 11:29:43');
INSERT INTO `incident_intervention_events` (`id`, `incident_id`, `user_id`, `action`, `reason`, `happened_at`) VALUES ('2', '1', '1', 'pause', 'Test', '2025-12-17 11:32:40');
INSERT INTO `incident_intervention_events` (`id`, `incident_id`, `user_id`, `action`, `reason`, `happened_at`) VALUES ('3', '3', '1', 'start', NULL, '2025-12-17 11:42:06');
INSERT INTO `incident_intervention_events` (`id`, `incident_id`, `user_id`, `action`, `reason`, `happened_at`) VALUES ('4', '3', '1', 'pause', 'Rest', '2025-12-17 11:42:17');
INSERT INTO `incident_intervention_events` (`id`, `incident_id`, `user_id`, `action`, `reason`, `happened_at`) VALUES ('5', '1', '1', 'resume', NULL, '2025-12-17 16:14:43');

-- --------------------------------------------------------
-- Structure de la table `incident_materials`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_materials`;
CREATE TABLE `incident_materials` (
  `id` int NOT NULL AUTO_INCREMENT,
  `report_id` int NOT NULL,
  `designation` varchar(255) NOT NULL,
  `qty` decimal(10,2) DEFAULT '0.00',
  `reference` varchar(255) DEFAULT NULL,
  `unit_cost` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- --------------------------------------------------------
-- Structure de la table `incident_reports`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_reports`;
CREATE TABLE `incident_reports` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `technician_id` int NOT NULL,
  `team_name` varchar(255) DEFAULT NULL,
  `team_contact` varchar(255) DEFAULT NULL,
  `type_of_incident` varchar(50) DEFAULT NULL,
  `detection_mode` varchar(50) DEFAULT NULL,
  `cut_distance_km` decimal(10,2) DEFAULT NULL,
  `gps_lat` decimal(10,8) DEFAULT NULL,
  `gps_lng` decimal(11,8) DEFAULT NULL,
  `fault_nature` text,
  `impacted_equipment` text,
  `takeover_at` datetime DEFAULT NULL,
  `intervention_start` datetime DEFAULT NULL,
  `intervention_end` datetime DEFAULT NULL,
  `duration_hours` decimal(10,2) DEFAULT NULL,
  `repair_type` varchar(100) DEFAULT NULL,
  `work_comments` text,
  `otdr_before_path` varchar(255) DEFAULT NULL,
  `otdr_after_path` varchar(255) DEFAULT NULL,
  `otdr_result` varchar(20) DEFAULT NULL,
  `conformity_status` varchar(20) DEFAULT NULL,
  `resolution_status` varchar(20) DEFAULT NULL,
  `need_additional` tinyint(1) DEFAULT '0',
  `quote_file_path` varchar(255) DEFAULT NULL,
  `supervisor_comments` text,
  `signature_path` varchar(255) DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `validated_at` datetime DEFAULT NULL,
  `validated_by` int DEFAULT NULL,
  `rejected_at` datetime DEFAULT NULL,
  `rejected_by` int DEFAULT NULL,
  `rejection_reason` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `closure_report_path` varchar(255) DEFAULT NULL,
  `closure_notes` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `incident_reports`

INSERT INTO `incident_reports` (`id`, `incident_id`, `technician_id`, `team_name`, `team_contact`, `type_of_incident`, `detection_mode`, `cut_distance_km`, `gps_lat`, `gps_lng`, `fault_nature`, `impacted_equipment`, `takeover_at`, `intervention_start`, `intervention_end`, `duration_hours`, `repair_type`, `work_comments`, `otdr_before_path`, `otdr_after_path`, `otdr_result`, `conformity_status`, `resolution_status`, `need_additional`, `quote_file_path`, `supervisor_comments`, `signature_path`, `submitted_at`, `validated_at`, `validated_by`, `rejected_at`, `rejected_by`, `rejection_reason`, `created_at`, `updated_at`, `closure_report_path`, `closure_notes`) VALUES ('1', '1', '1', 'Admin', 'admin, admin@novalink.ci', 'Travaux tiers', 'Appel', '0.00', '5.36807900', '-3.99011300', '', '[\"ODF\",\"Câble 96FO\"]', '2025-12-17 16:05:00', '2025-12-17 16:06:00', '2025-12-19 05:30:00', '37.40', 'Remplacement câble', '', 'incidents/1/otdr_before_6942d581158cd.jpg', 'incidents/1/otdr_after_6942d58115df4.png', '', NULL, '', '0', NULL, 'OK', 'incidents/1/signature_6942d58116392.png', '2025-12-17 16:08:33', '2025-12-17 16:08:51', '1', NULL, NULL, NULL, '2025-12-17 16:06:02', '2025-12-17 18:31:47', 'incidents/1/closure_report_6942f71331ddd.pdf', '');

-- --------------------------------------------------------
-- Structure de la table `incident_statuses`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incident_statuses`;
CREATE TABLE `incident_statuses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key_name` varchar(50)  NOT NULL,
  `label` varchar(100)  NOT NULL,
  `color` varchar(7)  DEFAULT '#6c757d',
  `active` tinyint(1) DEFAULT '1',
  `sort_order` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key` (`key_name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `incident_statuses`

INSERT INTO `incident_statuses` (`id`, `key_name`, `label`, `color`, `active`, `sort_order`) VALUES ('1', 'nouveau', 'Nouveau', '#dc3545', '1', '1');
INSERT INTO `incident_statuses` (`id`, `key_name`, `label`, `color`, `active`, `sort_order`) VALUES ('2', 'ouvert', 'Ouvert', '#fd7e14', '1', '2');
INSERT INTO `incident_statuses` (`id`, `key_name`, `label`, `color`, `active`, `sort_order`) VALUES ('3', 'en_cours', 'En cours', '#ffc107', '1', '3');
INSERT INTO `incident_statuses` (`id`, `key_name`, `label`, `color`, `active`, `sort_order`) VALUES ('4', 'resolu', 'Résolu', '#20c997', '1', '4');
INSERT INTO `incident_statuses` (`id`, `key_name`, `label`, `color`, `active`, `sort_order`) VALUES ('5', 'clos', 'Clos', '#198754', '1', '5');
INSERT INTO `incident_statuses` (`id`, `key_name`, `label`, `color`, `active`, `sort_order`) VALUES ('6', 'treated', 'Traité', '#0d6efd', '1', '60');
INSERT INTO `incident_statuses` (`id`, `key_name`, `label`, `color`, `active`, `sort_order`) VALUES ('7', 'closed', 'Clôturé', '#6c757d', '1', '100');

-- --------------------------------------------------------
-- Structure de la table `incidents`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `incidents`;
CREATE TABLE `incidents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reference` varchar(50)  DEFAULT NULL,
  `client_id` int NOT NULL,
  `location_id` int DEFAULT NULL,
  `site_id` int DEFAULT NULL,
  `liaison_id` int DEFAULT NULL,
  `cause_id` int DEFAULT NULL,
  `title` varchar(255)  NOT NULL,
  `description` text ,
  `priority` enum('low','medium','high','critical')  DEFAULT 'medium',
  `status_id` int DEFAULT '1',
  `assigned_to` int DEFAULT NULL,
  `declared_by` int DEFAULT NULL,
  `declared_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`),
  KEY `idx_client` (`client_id`),
  KEY `idx_location` (`location_id`),
  KEY `idx_status` (`status_id`),
  KEY `idx_assigned` (`assigned_to`),
  KEY `idx_reference` (`reference`),
  KEY `idx_cause` (`cause_id`),
  KEY `idx_site` (`site_id`),
  KEY `idx_liaison` (`liaison_id`),
  CONSTRAINT `fk_incidents_cause` FOREIGN KEY (`cause_id`) REFERENCES `incident_causes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_incidents_liaison` FOREIGN KEY (`liaison_id`) REFERENCES `liaisons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_incidents_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `incidents`

INSERT INTO `incidents` (`id`, `reference`, `client_id`, `location_id`, `site_id`, `liaison_id`, `cause_id`, `title`, `description`, `priority`, `status_id`, `assigned_to`, `declared_by`, `declared_at`, `resolved_at`, `created_at`, `updated_at`) VALUES ('1', 'INC-2025-001', '1', '1', NULL, NULL, NULL, 'Panne réseau fibre', 'Interruption du service internet sur le site principal', 'medium', '7', NULL, NULL, '2025-12-15 12:14:37', NULL, '2025-12-15 12:14:37', '2025-12-17 18:31:47');
INSERT INTO `incidents` (`id`, `reference`, `client_id`, `location_id`, `site_id`, `liaison_id`, `cause_id`, `title`, `description`, `priority`, `status_id`, `assigned_to`, `declared_by`, `declared_at`, `resolved_at`, `created_at`, `updated_at`) VALUES ('2', 'INC-2025-002', '2', '3', NULL, NULL, NULL, 'Problème téléphonie', 'Pas de tonalité sur les postes fixes', 'medium', '2', NULL, NULL, '2025-12-15 12:14:37', NULL, '2025-12-15 12:14:37', NULL);
INSERT INTO `incidents` (`id`, `reference`, `client_id`, `location_id`, `site_id`, `liaison_id`, `cause_id`, `title`, `description`, `priority`, `status_id`, `assigned_to`, `declared_by`, `declared_at`, `resolved_at`, `created_at`, `updated_at`) VALUES ('3', 'INC-2025-003', '3', '4', NULL, NULL, NULL, 'Accès système bancaire', 'Impossible d\'accéder au système de gestion', 'medium', '1', NULL, NULL, '2025-12-15 12:14:37', NULL, '2025-12-15 12:14:37', NULL);

-- --------------------------------------------------------
-- Structure de la table `liaisons`
-- --------------------------------------------------------

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

-- --------------------------------------------------------
-- Structure de la table `locations`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int NOT NULL,
  `name` varchar(255)  NOT NULL,
  `address` text ,
  `city` varchar(100)  DEFAULT NULL,
  `postal_code` varchar(20)  DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client` (`client_id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `locations`

INSERT INTO `locations` (`id`, `client_id`, `name`, `address`, `city`, `postal_code`, `latitude`, `longitude`, `active`, `created_at`, `updated_at`) VALUES ('1', '1', 'Orange Siège Social', 'Boulevard Lagunaire', 'Abidjan', NULL, NULL, NULL, '1', '2025-12-15 12:14:37', NULL);
INSERT INTO `locations` (`id`, `client_id`, `name`, `address`, `city`, `postal_code`, `latitude`, `longitude`, `active`, `created_at`, `updated_at`) VALUES ('2', '1', 'Orange Plateau', 'Avenue Chardy', 'Abidjan', NULL, NULL, NULL, '1', '2025-12-15 12:14:37', NULL);
INSERT INTO `locations` (`id`, `client_id`, `name`, `address`, `city`, `postal_code`, `latitude`, `longitude`, `active`, `created_at`, `updated_at`) VALUES ('3', '2', 'MTN Siège', 'Avenue Terrasson de Fougères', 'Abidjan', NULL, NULL, NULL, '1', '2025-12-15 12:14:37', NULL);
INSERT INTO `locations` (`id`, `client_id`, `name`, `address`, `city`, `postal_code`, `latitude`, `longitude`, `active`, `created_at`, `updated_at`) VALUES ('4', '3', 'Banque Atlantique Plateau', 'Boulevard Carde', 'Abidjan', NULL, NULL, NULL, '1', '2025-12-15 12:14:37', NULL);

-- --------------------------------------------------------
-- Structure de la table `maintenance_types`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `maintenance_types`;
CREATE TABLE `maintenance_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100)  NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `maintenance_types`

INSERT INTO `maintenance_types` (`id`, `name`, `active`, `created_at`, `updated_at`) VALUES ('1', 'Maintenance corrective', '1', '2025-12-17 10:59:32', NULL);
INSERT INTO `maintenance_types` (`id`, `name`, `active`, `created_at`, `updated_at`) VALUES ('2', 'Maintenance préventive', '1', '2025-12-17 10:59:32', NULL);

-- --------------------------------------------------------
-- Structure de la table `notifications`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `title` varchar(200) NOT NULL,
  `body` text,
  `url` varchar(300) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `notifications`

INSERT INTO `notifications` (`id`, `user_id`, `title`, `body`, `url`, `is_read`, `created_at`) VALUES ('1', '1', 'Prise en charge technicien', 'Admin s\'est mis en prise en charge à 12:00', 'http://localhost/Insuite_backbones/public/cartographie', '1', '2025-12-17 12:00:50');
INSERT INTO `notifications` (`id`, `user_id`, `title`, `body`, `url`, `is_read`, `created_at`) VALUES ('2', '1', 'Devis en attente de validation', 'Le devis DEV-251217-142403-188 pour MTN CI est en attente.Montant: 0,00 XOF', '/Insuite_backbones/public/quotes/show?id=1', '1', '2025-12-17 14:55:36');
INSERT INTO `notifications` (`id`, `user_id`, `title`, `body`, `url`, `is_read`, `created_at`) VALUES ('3', '1', 'Devis validé', 'Le devis DEV-251217-142403-188 pour MTN CI a été validé.Montant: 0,00 XOF', '/Insuite_backbones/public/quotes/show?id=1', '1', '2025-12-17 15:22:52');

-- --------------------------------------------------------
-- Structure de la table `planning_tasks`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `planning_tasks`;
CREATE TABLE `planning_tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `type` enum('préventive','corrective') NOT NULL DEFAULT 'corrective',
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime DEFAULT NULL,
  `status` enum('Planifié','En cours','Terminé','Annulé') NOT NULL DEFAULT 'Planifié',
  `technician_id` int DEFAULT NULL,
  `incident_id` int DEFAULT NULL,
  `location_id` int DEFAULT NULL,
  `notes` text,
  `color` varchar(7) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- --------------------------------------------------------
-- Structure de la table `priority_levels`
-- --------------------------------------------------------

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

-- Données de la table `priority_levels`

INSERT INTO `priority_levels` (`id`, `name`, `sort_order`, `color`, `description`, `created_at`, `updated_at`) VALUES ('1', 'Critique', '1', '#dc3545', 'Incident critique n├®cessitant une intervention imm├®diate', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `priority_levels` (`id`, `name`, `sort_order`, `color`, `description`, `created_at`, `updated_at`) VALUES ('2', 'Haute', '2', '#fd7e14', 'Incident ├á haute priorit├®', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `priority_levels` (`id`, `name`, `sort_order`, `color`, `description`, `created_at`, `updated_at`) VALUES ('3', 'Normale', '3', '#ffc107', 'Incident ├á priorit├® normale', '2025-12-17 09:50:05', '2025-12-17 09:50:05');
INSERT INTO `priority_levels` (`id`, `name`, `sort_order`, `color`, `description`, `created_at`, `updated_at`) VALUES ('4', 'Basse', '4', '#28a745', 'Incident ├á basse priorit├®', '2025-12-17 09:50:05', '2025-12-17 09:50:05');

-- --------------------------------------------------------
-- Structure de la table `quote_history`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `quote_history`;
CREATE TABLE `quote_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quote_id` int NOT NULL,
  `changed_by` int DEFAULT NULL,
  `from_status` varchar(64) DEFAULT NULL,
  `to_status` varchar(64) NOT NULL,
  `reason` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `quote_id` (`quote_id`),
  CONSTRAINT `quote_history_ibfk_1` FOREIGN KEY (`quote_id`) REFERENCES `quotes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `quote_history`

INSERT INTO `quote_history` (`id`, `quote_id`, `changed_by`, `from_status`, `to_status`, `reason`, `created_at`) VALUES ('1', '1', '1', 'Rejeté', 'Validé', NULL, '2025-12-17 15:22:52');

-- --------------------------------------------------------
-- Structure de la table `quote_items`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `quote_items`;
CREATE TABLE `quote_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quote_id` int NOT NULL,
  `designation` varchar(255) NOT NULL,
  `unit` varchar(16) NOT NULL DEFAULT 'u',
  `quantity` decimal(12,2) NOT NULL DEFAULT '0.00',
  `unit_price` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total_line` decimal(12,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `quote_id` (`quote_id`),
  CONSTRAINT `quote_items_ibfk_1` FOREIGN KEY (`quote_id`) REFERENCES `quotes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- --------------------------------------------------------
-- Structure de la table `quotes`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `quotes`;
CREATE TABLE `quotes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quote_no` varchar(64) NOT NULL,
  `client_name` varchar(255) NOT NULL,
  `related_type` varchar(64) DEFAULT NULL,
  `related_ref` varchar(128) DEFAULT NULL,
  `quote_date` date NOT NULL,
  `status` varchar(64) NOT NULL DEFAULT 'En attente',
  `total_ht` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total_tva` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total_ttc` decimal(12,2) NOT NULL DEFAULT '0.00',
  `currency` varchar(8) NOT NULL DEFAULT 'XOF',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status_changed_at` datetime DEFAULT NULL,
  `reject_reason` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `quotes`

INSERT INTO `quotes` (`id`, `quote_no`, `client_name`, `related_type`, `related_ref`, `quote_date`, `status`, `total_ht`, `total_tva`, `total_ttc`, `currency`, `created_at`, `updated_at`, `status_changed_at`, `reject_reason`) VALUES ('1', 'DEV-251217-142403-188', 'MTN CI', 'incident', '', '2025-12-17', 'Validé', '0.00', '0.00', '0.00', 'XOF', '2025-12-17 14:24:03', '2025-12-17 15:22:52', '2025-12-17 15:22:52', NULL);

-- --------------------------------------------------------
-- Structure de la table `roles`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key_name` varchar(50)  NOT NULL,
  `label` varchar(100)  NOT NULL,
  `description` text ,
  `permissions` json DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_name` (`key_name`),
  UNIQUE KEY `idx_key` (`key_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `roles`

INSERT INTO `roles` (`id`, `key_name`, `label`, `description`, `permissions`, `active`) VALUES ('1', 'admin', 'Administrateur', 'Accès complet au système', NULL, '1');
INSERT INTO `roles` (`id`, `key_name`, `label`, `description`, `permissions`, `active`) VALUES ('2', 'manager', 'Manager', 'Gestion des équipes et incidents', NULL, '1');
INSERT INTO `roles` (`id`, `key_name`, `label`, `description`, `permissions`, `active`) VALUES ('3', 'superviseur', 'Superviseur', 'Supervision des interventions', NULL, '1');
INSERT INTO `roles` (`id`, `key_name`, `label`, `description`, `permissions`, `active`) VALUES ('4', 'agent', 'Agent Commercial', 'Gestion des clients et devis', NULL, '1');
INSERT INTO `roles` (`id`, `key_name`, `label`, `description`, `permissions`, `active`) VALUES ('5', 'technicien', 'Technicien', 'Intervention terrain', NULL, '1');

-- --------------------------------------------------------
-- Structure de la table `settings`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key_name` varchar(100)  NOT NULL,
  `value` text ,
  `description` text ,
  `type` varchar(20)  DEFAULT 'string',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_name` (`key_name`),
  UNIQUE KEY `idx_key` (`key_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `settings`

INSERT INTO `settings` (`id`, `key_name`, `value`, `description`, `type`, `created_at`, `updated_at`) VALUES ('1', 'app_name', 'NOVALINK', 'Nom de l\'application', 'string', '2025-12-15 12:15:23', NULL);
INSERT INTO `settings` (`id`, `key_name`, `value`, `description`, `type`, `created_at`, `updated_at`) VALUES ('2', 'timezone', 'Africa/Abidjan', 'Fuseau horaire par défaut', 'string', '2025-12-15 12:15:23', NULL);
INSERT INTO `settings` (`id`, `key_name`, `value`, `description`, `type`, `created_at`, `updated_at`) VALUES ('3', 'locale', 'fr_FR', 'Langue par défaut', 'string', '2025-12-15 12:15:23', NULL);
INSERT INTO `settings` (`id`, `key_name`, `value`, `description`, `type`, `created_at`, `updated_at`) VALUES ('4', 'currency', 'XOF', 'Devise par défaut', 'string', '2025-12-15 12:15:23', NULL);
INSERT INTO `settings` (`id`, `key_name`, `value`, `description`, `type`, `created_at`, `updated_at`) VALUES ('5', 'company_name', 'NOVALINK SARL', 'Nom de l\'entreprise', 'string', '2025-12-15 12:15:23', NULL);

-- --------------------------------------------------------
-- Structure de la table `sites`
-- --------------------------------------------------------

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

-- --------------------------------------------------------
-- Structure de la table `sla_policies`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `sla_policies`;
CREATE TABLE `sla_policies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `maintenance_type_id` int NOT NULL,
  `priority` enum('Basse','Moyenne','Haute','Urgent')  NOT NULL,
  `response_minutes` int NOT NULL,
  `resolution_minutes` int NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_sla` (`maintenance_type_id`,`priority`),
  CONSTRAINT `fk_sla_mt` FOREIGN KEY (`maintenance_type_id`) REFERENCES `maintenance_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `sla_policies`

INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`, `active`, `created_at`, `updated_at`) VALUES ('1', '1', 'Basse', '120', '1440', '1', '2025-12-17 10:59:32', NULL);
INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`, `active`, `created_at`, `updated_at`) VALUES ('2', '1', 'Moyenne', '60', '480', '1', '2025-12-17 10:59:32', NULL);
INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`, `active`, `created_at`, `updated_at`) VALUES ('3', '1', 'Haute', '30', '240', '1', '2025-12-17 10:59:32', NULL);
INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`, `active`, `created_at`, `updated_at`) VALUES ('4', '1', 'Urgent', '15', '120', '1', '2025-12-17 10:59:32', NULL);
INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`, `active`, `created_at`, `updated_at`) VALUES ('5', '2', 'Basse', '240', '2880', '1', '2025-12-17 10:59:32', NULL);
INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`, `active`, `created_at`, `updated_at`) VALUES ('6', '2', 'Moyenne', '120', '1440', '1', '2025-12-17 10:59:32', NULL);
INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`, `active`, `created_at`, `updated_at`) VALUES ('7', '2', 'Haute', '60', '720', '1', '2025-12-17 10:59:32', NULL);
INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`, `active`, `created_at`, `updated_at`) VALUES ('8', '2', 'Urgent', '30', '480', '1', '2025-12-17 10:59:32', NULL);

-- --------------------------------------------------------
-- Structure de la table `technician_location_history`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `technician_location_history`;
CREATE TABLE `technician_location_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `accuracy` float DEFAULT NULL,
  `heading` float DEFAULT NULL,
  `speed` float DEFAULT NULL,
  `recorded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_time` (`user_id`,`recorded_at`),
  KEY `idx_recorded_at` (`recorded_at`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `technician_location_history`

INSERT INTO `technician_location_history` (`id`, `user_id`, `latitude`, `longitude`, `accuracy`, `heading`, `speed`, `recorded_at`) VALUES ('1', '1', '5.36836590', '-3.99022660', '15.843', NULL, NULL, '2025-12-15 12:35:20');
INSERT INTO `technician_location_history` (`id`, `user_id`, `latitude`, `longitude`, `accuracy`, `heading`, `speed`, `recorded_at`) VALUES ('2', '1', '5.35101440', '-4.01735680', '213527', NULL, NULL, '2025-12-15 12:35:45');
INSERT INTO `technician_location_history` (`id`, `user_id`, `latitude`, `longitude`, `accuracy`, `heading`, `speed`, `recorded_at`) VALUES ('3', '1', '5.36803498', '-3.99014161', '95', NULL, NULL, '2025-12-17 11:26:29');
INSERT INTO `technician_location_history` (`id`, `user_id`, `latitude`, `longitude`, `accuracy`, `heading`, `speed`, `recorded_at`) VALUES ('4', '1', '5.36812934', '-3.99008183', '88', NULL, NULL, '2025-12-17 19:30:00');
INSERT INTO `technician_location_history` (`id`, `user_id`, `latitude`, `longitude`, `accuracy`, `heading`, `speed`, `recorded_at`) VALUES ('5', '1', '5.36820661', '-3.98999443', '73', NULL, NULL, '2025-12-17 19:30:12');

-- --------------------------------------------------------
-- Structure de la table `technician_locations`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `technician_locations`;
CREATE TABLE `technician_locations` (
  `user_id` int NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `accuracy` float DEFAULT NULL,
  `heading` float DEFAULT NULL,
  `speed` float DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `technician_locations`

INSERT INTO `technician_locations` (`user_id`, `latitude`, `longitude`, `accuracy`, `heading`, `speed`, `updated_at`) VALUES ('1', '5.36821342', '-3.98998755', '72', NULL, NULL, '2025-12-17 19:31:49');

-- --------------------------------------------------------
-- Structure de la table `user_takeover_events`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `user_takeover_events`;
CREATE TABLE `user_takeover_events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `taken_at` datetime NOT NULL,
  `ip_address` varchar(64) DEFAULT NULL,
  `user_agent` text,
  `lat` decimal(10,7) DEFAULT NULL,
  `lng` decimal(10,7) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `user_takeover_events`

INSERT INTO `user_takeover_events` (`id`, `user_id`, `taken_at`, `ip_address`, `user_agent`, `lat`, `lng`) VALUES ('1', '1', '2025-12-17 12:00:50', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/140.0.0.0 Safari/537.36', '5.3680089', '-3.9901466');

-- --------------------------------------------------------
-- Structure de la table `users`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50)  NOT NULL,
  `email` varchar(100)  NOT NULL,
  `role_key` varchar(50)  NOT NULL DEFAULT 'agent',
  `password` varchar(255)  NOT NULL,
  `full_name` varchar(100)  DEFAULT NULL,
  `role` varchar(20)  DEFAULT 'technicien',
  `active` tinyint(1) DEFAULT '1',
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(100)  NOT NULL DEFAULT 'Utilisateur',
  `phone` varchar(30)  DEFAULT NULL,
  `takeover_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_username` (`username`),
  KEY `idx_email` (`email`),
  KEY `idx_role` (`role`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 ;

-- Données de la table `users`

INSERT INTO `users` (`id`, `username`, `email`, `role_key`, `password`, `full_name`, `role`, `active`, `last_login`, `created_at`, `updated_at`, `name`, `phone`, `takeover_at`) VALUES ('1', 'admin', 'admin@novalink.ci', 'admin', '$2y$10$ae/tLIidfceRCDygCr3PdO5Jq0MXHVEkJpouwFa71DB5XFNNrxYgO', 'Administrateur Système', 'admin', '1', NULL, '2025-12-15 12:15:23', '2025-12-17 12:00:50', 'admin', NULL, '2025-12-17 12:00:50');
INSERT INTO `users` (`id`, `username`, `email`, `role_key`, `password`, `full_name`, `role`, `active`, `last_login`, `created_at`, `updated_at`, `name`, `phone`, `takeover_at`) VALUES ('2', 'agent1', 'agent@novalink.ci', 'agent', '$2y$10$ae/tLIidfceRCDygCr3PdO5Jq0MXHVEkJpouwFa71DB5XFNNrxYgO', 'Agent Commercial', 'agent', '1', NULL, '2025-12-15 12:15:23', '2025-12-17 09:54:24', 'agent', NULL, NULL);
INSERT INTO `users` (`id`, `username`, `email`, `role_key`, `password`, `full_name`, `role`, `active`, `last_login`, `created_at`, `updated_at`, `name`, `phone`, `takeover_at`) VALUES ('3', 'technicien1', 'tech@novalink.ci', 'technicien', '$2y$10$ae/tLIidfceRCDygCr3PdO5Jq0MXHVEkJpouwFa71DB5XFNNrxYgO', 'Technicien Terrain', 'technicien', '1', NULL, '2025-12-15 12:15:23', '2025-12-17 10:32:07', 'tech', NULL, NULL);

SET FOREIGN_KEY_CHECKS=1;