-- --------------------------------------------------------
-- Hôte:                         127.0.0.1
-- Version du serveur:           8.4.3 - MySQL Community Server - GPL
-- SE du serveur:                Win64
-- HeidiSQL Version:             12.8.0.6908
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Listage de la structure de la base pour c2685816c_maintenance_back
CREATE DATABASE IF NOT EXISTS `c2685816c_maintenance_back` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `c2685816c_maintenance_back`;

-- Listage de la structure de table c2685816c_maintenance_back. clients
CREATE TABLE IF NOT EXISTS `clients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `contact_email` varchar(150) DEFAULT NULL,
  `contact_phone` varchar(50) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.clients : ~5 rows (environ)
INSERT INTO `clients` (`id`, `name`, `contact_email`, `contact_phone`, `active`) VALUES
	(1, 'Orange C??te d\'Ivoire', 'support@orange.ci', '+225 05 05 05 05', 1),
	(2, 'MTN C??te d\'Ivoire', 'tech@mtn.ci', '+225 07 07 07 07', 1),
	(3, 'Moov Africa', 'incident@moov.ci', '+225 01 01 01 01', 1),
	(4, 'Telecel Faso', 'ops@telecel.bf', '+226 70 00 00 00', 1),
	(5, 'Soci??t?? G??n??rale', 'it@sgbci.com', '+225 20 20 20 20', 1);

-- Listage de la structure de table c2685816c_maintenance_back. cost_categories
CREATE TABLE IF NOT EXISTS `cost_categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.cost_categories : ~0 rows (environ)

-- Listage de la structure de table c2685816c_maintenance_back. cost_items
CREATE TABLE IF NOT EXISTS `cost_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `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.00',
  `currency` varchar(3) NOT NULL DEFAULT 'XOF',
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `cost_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `cost_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.cost_items : ~0 rows (environ)

-- Listage de la structure de table c2685816c_maintenance_back. incidents
CREATE TABLE IF NOT EXISTS `incidents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(40) NOT NULL,
  `client_id` int NOT NULL,
  `location_id` int NOT NULL,
  `cause_id` int DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `description` text,
  `priority` enum('Basse','Moyenne','Haute','Urgent') NOT NULL DEFAULT 'Moyenne',
  `status_id` int NOT NULL,
  `declared_by` int NOT NULL,
  `declared_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expected_response_at` timestamp NULL DEFAULT NULL,
  `expected_resolution_at` timestamp NULL DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ticket_id` (`ticket_id`),
  KEY `client_id` (`client_id`),
  KEY `location_id` (`location_id`),
  KEY `cause_id` (`cause_id`),
  KEY `status_id` (`status_id`),
  KEY `declared_by` (`declared_by`),
  CONSTRAINT `incidents_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `incidents_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`),
  CONSTRAINT `incidents_ibfk_3` FOREIGN KEY (`cause_id`) REFERENCES `incident_causes` (`id`),
  CONSTRAINT `incidents_ibfk_4` FOREIGN KEY (`status_id`) REFERENCES `incident_statuses` (`id`),
  CONSTRAINT `incidents_ibfk_5` FOREIGN KEY (`declared_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incidents : ~3 rows (environ)
INSERT INTO `incidents` (`id`, `ticket_id`, `client_id`, `location_id`, `cause_id`, `title`, `description`, `priority`, `status_id`, `declared_by`, `declared_at`, `expected_response_at`, `expected_resolution_at`, `resolved_at`, `created_at`, `updated_at`) VALUES
	(1, 'INC-2025-001', 1, 1, 1, 'Coupure liaison Plateau-Cocody', 'Interruption totale du lien fibre entre les sites Plateau et Cocody depuis 14h30', 'Urgent', 8, 1, '2025-11-05 14:35:00', '2025-11-05 15:05:00', '2025-11-05 16:35:00', '2025-11-09 16:39:43', '2025-11-05 08:59:33', '2025-11-09 17:02:23'),
	(2, 'INC-2025-002', 2, 3, 4, 'Panne g??n??ratrice Treichville', 'Arr??t du groupe ??lectrog??ne principal, basculement sur onduleur', 'Haute', 2, 1, '2025-11-05 09:15:00', '2025-11-05 10:15:00', '2025-11-05 13:15:00', NULL, '2025-11-05 08:59:33', '2025-11-09 12:13:39'),
	(3, 'INC-2025-003', 3, 2, 3, 'D??gradation performance Cocody', 'Ralentissement constat?? sur les services data depuis ce matin', 'Moyenne', 3, 1, '2025-11-05 08:45:00', NULL, NULL, NULL, '2025-11-05 08:59:33', '2025-11-05 08:59:33');

-- Listage de la structure de table c2685816c_maintenance_back. incident_assignments
CREATE TABLE IF NOT EXISTS `incident_assignments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `user_id` int NOT NULL,
  `assigned_by` int NOT NULL,
  `assigned_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `user_id` (`user_id`),
  KEY `assigned_by` (`assigned_by`),
  CONSTRAINT `incident_assignments_ibfk_1` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `incident_assignments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `incident_assignments_ibfk_3` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_assignments : ~0 rows (environ)
INSERT INTO `incident_assignments` (`id`, `incident_id`, `user_id`, `assigned_by`, `assigned_at`, `active`) VALUES
	(1, 1, 2, 1, '2025-11-06 12:03:36', 1),
	(2, 2, 2, 1, '2025-11-09 12:19:10', 1);

-- Listage de la structure de table c2685816c_maintenance_back. incident_attachments
CREATE TABLE IF NOT EXISTS `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,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_attachments : ~0 rows (environ)
INSERT INTO `incident_attachments` (`id`, `report_id`, `type`, `path`, `uploaded_at`) VALUES
	(1, 1, 'photo', '/storage/uploads/incidents/1/photos_690cd0dd8ff76.jpg', '2025-11-06 16:46:21'),
	(2, 1, 'photo', 'incidents/1/photos_6910b17c17e94.png', '2025-11-09 15:21:32');

-- Listage de la structure de table c2685816c_maintenance_back. incident_causes
CREATE TABLE IF NOT EXISTS `incident_causes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `category` varchar(80) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_causes : ~12 rows (environ)
INSERT INTO `incident_causes` (`id`, `name`, `category`, `active`) VALUES
	(1, 'Coupure fibre optique', 'Infrastructure', 1),
	(2, 'Panne ??quipement r??seau', 'Mat??riel', 1),
	(3, 'Surcharge trafic', 'Capacit??', 1),
	(4, 'Coupure ??lectrique', '??nergie', 1),
	(5, 'Probl??me climatisation', 'Environnement', 1),
	(6, 'Erreur configuration', 'Logiciel', 1),
	(7, 'Intrusion s??curitaire', 'S??curit??', 1),
	(8, 'Maintenance pr??ventive', 'Planifi??', 1),
	(9, 'Vandalisme ??quipement', 'Externe', 1),
	(10, 'D??faillance transmission', 'Transport', 1),
	(11, 'Panne serveur', 'IT', 1),
	(12, 'Probl??me synchronisation', 'Temps', 1);

-- Listage de la structure de table c2685816c_maintenance_back. incident_comments
CREATE TABLE IF NOT EXISTS `incident_comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `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 NULL DEFAULT CURRENT_TIMESTAMP,
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `incident_comments_ibfk_1` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `incident_comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_comments : ~1 rows (environ)
INSERT INTO `incident_comments` (`id`, `incident_id`, `user_id`, `comment`, `is_internal`, `created_at`, `parent_id`) VALUES
	(1, 1, 2, 'Test', 0, '2025-11-06 17:16:07', NULL);

-- Listage de la structure de table c2685816c_maintenance_back. incident_comment_attachments
CREATE TABLE IF NOT EXISTS `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 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_comment_attachments : ~0 rows (environ)

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

-- Listage des données de la table c2685816c_maintenance_back.incident_final_reports : ~1 rows (environ)
INSERT INTO `incident_final_reports` (`id`, `incident_id`, `supervisor_id`, `restoration_at`, `comments`, `created_at`, `updated_at`, `client_supervisor`) VALUES
	(1, 1, 2, '2025-11-09 16:39:00', 'TEST FINAL', '2025-11-09 16:17:05', '2025-11-09 16:39:42', 'YEO YVAN');

-- Listage de la structure de table c2685816c_maintenance_back. incident_final_report_attachments
CREATE TABLE IF NOT EXISTS `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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_final_report_attachments : ~0 rows (environ)
INSERT INTO `incident_final_report_attachments` (`id`, `final_report_id`, `path`, `uploaded_at`) VALUES
	(1, 1, 'incidents/final/final/final_attachments_6910c3ce43db9.png', '2025-11-09 16:39:42');

-- Listage de la structure de table c2685816c_maintenance_back. incident_history
CREATE TABLE IF NOT EXISTS `incident_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `field_name` varchar(50) NOT NULL,
  `old_value` text,
  `new_value` text,
  `changed_by` int NOT NULL,
  `changed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `changed_by` (`changed_by`),
  CONSTRAINT `incident_history_ibfk_1` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `incident_history_ibfk_2` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_history : ~9 rows (environ)
INSERT INTO `incident_history` (`id`, `incident_id`, `field_name`, `old_value`, `new_value`, `changed_by`, `changed_at`) VALUES
	(1, 1, 'Assignment', NULL, 'Assigné à ZAHUI CHRISTIAN WILFRIED', 1, '2025-11-06 12:03:36'),
	(2, 2, 'Assignment', NULL, 'Assigné à ZAHUI CHRISTIAN WILFRIED', 1, '2025-11-09 12:19:10'),
	(3, 2, 'Intervention', NULL, 'Intervention démarrée', 1, '2025-11-09 12:41:29'),
	(4, 2, 'Intervention', NULL, 'Intervention en pause', 1, '2025-11-09 12:41:57'),
	(5, 2, 'Intervention', NULL, 'Intervention reprise', 1, '2025-11-09 12:42:11'),
	(6, 2, 'Intervention', NULL, 'Intervention en pause', 1, '2025-11-09 12:45:19'),
	(7, 2, 'Intervention', NULL, 'Intervention reprise', 1, '2025-11-09 12:45:21'),
	(8, 2, 'Intervention', NULL, 'Intervention en pause — Motif: Test Motif', 1, '2025-11-09 12:55:38'),
	(9, 2, 'Intervention', NULL, 'Intervention reprise', 1, '2025-11-09 12:55:56'),
	(10, 1, 'Intervention', NULL, 'Intervention démarrée', 1, '2025-11-09 15:07:07'),
	(11, 1, 'Intervention', NULL, 'Intervention démarrée', 2, '2025-11-09 15:19:54'),
	(12, 1, 'Status', 'Ouvert', 'Traité', 1, '2025-11-09 16:36:25'),
	(13, 1, 'FinalReport', NULL, 'Rapport final mis à jour', 2, '2025-11-09 16:39:42'),
	(14, 1, 'Status', 'Traité', 'Clôturé', 2, '2025-11-09 16:39:43'),
	(15, 1, 'Status', 'Clôturé', 'R??solu provisoire', 2, '2025-11-09 17:02:08'),
	(16, 1, 'Status', 'R??solu provisoire', 'Traité', 2, '2025-11-09 17:02:08'),
	(17, 1, 'Status', 'Traité', 'Clôturé', 2, '2025-11-09 17:02:23');

-- Listage de la structure de table c2685816c_maintenance_back. incident_intervention_events
CREATE TABLE IF NOT EXISTS `incident_intervention_events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `incident_id` int NOT NULL,
  `user_id` int NOT NULL,
  `action` varchar(10) NOT NULL,
  `happened_at` datetime NOT NULL,
  `reason` text,
  PRIMARY KEY (`id`),
  KEY `incident_id` (`incident_id`),
  KEY `user_id` (`user_id`),
  KEY `happened_at` (`happened_at`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_intervention_events : ~4 rows (environ)
INSERT INTO `incident_intervention_events` (`id`, `incident_id`, `user_id`, `action`, `happened_at`, `reason`) VALUES
	(1, 2, 1, 'pause', '2025-11-09 12:45:19', NULL),
	(2, 2, 1, 'resume', '2025-11-09 12:45:21', NULL),
	(3, 2, 1, 'pause', '2025-11-09 12:55:38', 'Test Motif'),
	(4, 2, 1, 'resume', '2025-11-09 12:55:56', NULL),
	(5, 1, 1, 'start', '2025-11-09 15:07:07', NULL),
	(6, 1, 2, 'start', '2025-11-09 15:19:54', NULL);

-- Listage de la structure de table c2685816c_maintenance_back. incident_materials
CREATE TABLE IF NOT EXISTS `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=3 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_materials : ~0 rows (environ)
INSERT INTO `incident_materials` (`id`, `report_id`, `designation`, `qty`, `reference`, `unit_cost`) VALUES
	(2, 1, 'ODF', 1.00, '', 250000.00);

-- Listage de la structure de table c2685816c_maintenance_back. incident_reports
CREATE TABLE IF NOT EXISTS `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,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `validated_at` datetime DEFAULT NULL,
  `validated_by` int DEFAULT NULL,
  `rejected_at` datetime DEFAULT NULL,
  `rejected_by` int DEFAULT NULL,
  `rejection_reason` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_reports : ~1 rows (environ)
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`, `created_at`, `updated_at`, `validated_at`, `validated_by`, `rejected_at`, `rejected_by`, `rejection_reason`) VALUES
	(1, 1, 2, 'Admin', 'Admin, admin@fiberops.local', 'Travaux tiers', 'OTDR', 1000.00, 5.31285300, -4.05659800, 'TEst', '["ODF","Boîtier de jonction","Coupe-circuit"]', '2025-11-06 16:43:00', '2025-11-07 16:45:00', '2025-11-08 16:45:00', 24.00, 'Remplacement câble', 'Etstststst', '/storage/uploads/incidents/1/otdr_before_690cd0dd8a79e.png', '/storage/uploads/incidents/1/otdr_after_690cd0dd8abc2.png', 'Conforme', 'Conforme', 'Provisoire', 0, NULL, 'Oui', NULL, '2025-11-09 15:21:32', '2025-11-06 16:46:21', '2025-11-09 15:57:55', '2025-11-09 15:57:55', 1, NULL, NULL, NULL);

-- Listage de la structure de table c2685816c_maintenance_back. incident_statuses
CREATE TABLE IF NOT EXISTS `incident_statuses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key_name` varchar(50) 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',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_name` (`key_name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.incident_statuses : ~6 rows (environ)
INSERT INTO `incident_statuses` (`id`, `key_name`, `label`, `is_system`, `color`, `sort_order`) VALUES
	(1, 'open', 'Ouvert', 1, '#dc3545', 10),
	(2, 'assigned', 'Assign??', 1, '#fd7e14', 20),
	(3, 'in_progress', 'En cours', 1, '#0d6efd', 30),
	(4, 'resolved_temp', 'R??solu provisoire', 1, '#198754', 40),
	(5, 'resolved_final', 'R??solu d??finitif', 1, '#20c997', 50),
	(6, 'cancelled', 'Annul??', 1, '#6c757d', 60),
	(7, 'treated', 'Traité', 0, '#0d6efd', 60),
	(8, 'closed', 'Clôturé', 0, '#198754', 90);

-- Listage de la structure de table c2685816c_maintenance_back. locations
CREATE TABLE IF NOT EXISTS `locations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `address` text,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.locations : ~7 rows (environ)
INSERT INTO `locations` (`id`, `name`, `address`, `latitude`, `longitude`, `active`) VALUES
	(1, 'Abidjan - Plateau', 'Boulevard de la R??publique, Plateau, Abidjan', 5.3166667, -4.0333333, 1),
	(2, 'Abidjan - Cocody', 'Riviera Golf, Cocody, Abidjan', 5.3677778, -3.9708333, 1),
	(3, 'Abidjan - Treichville', 'Zone Industrielle, Treichville', 5.2833333, -4.0166667, 1),
	(4, 'Bouak?? - Centre', 'Boulevard G??n??ral de Gaulle, Bouak??', 7.6833333, -5.0333333, 1),
	(5, 'Yamoussoukro - Politique', 'Boulevard Mamadou Coulibaly, Yamoussoukro', 6.8166667, -5.2833333, 1),
	(6, 'San Pedro - Port', 'Zone Portuaire, San Pedro', 4.7666667, -6.6333333, 1),
	(7, 'Korhogo - Nord', 'Avenue Loukou Laurent, Korhogo', 9.4580556, -5.6294444, 1);

-- Listage de la structure de table c2685816c_maintenance_back. maintenance_types
CREATE TABLE IF NOT EXISTS `maintenance_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.maintenance_types : ~5 rows (environ)
INSERT INTO `maintenance_types` (`id`, `name`) VALUES
	(5, 'Audit technique'),
	(3, 'Installation'),
	(1, 'Maintenance corrective'),
	(2, 'Maintenance pr??ventive'),
	(4, 'Migration');

-- Listage de la structure de table c2685816c_maintenance_back. notifications
CREATE TABLE IF NOT EXISTS `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=11 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.notifications : ~5 rows (environ)
INSERT INTO `notifications` (`id`, `user_id`, `title`, `body`, `url`, `is_read`, `created_at`) VALUES
	(1, 1, 'Prise en charge technicien', 'ZAHUI CHRISTIAN WILFRIED s\'est mis en prise en charge à 12:30', '/Insuite_backbones/public/cartographie', 1, '2025-11-09 12:30:14'),
	(2, 1, 'Intervention en pause: INC-2025-002', 'Admin a mis en pause l\'intervention sur INC-2025-002.', '/Insuite_backbones/public/incidents/detail?id=2', 1, '2025-11-09 12:45:19'),
	(3, 1, 'Intervention reprise: INC-2025-002', 'Admin a repris l\'intervention sur INC-2025-002.', '/Insuite_backbones/public/incidents/detail?id=2', 1, '2025-11-09 12:45:21'),
	(4, 1, 'Intervention en pause: INC-2025-002', 'Admin a mis en pause l\'intervention sur INC-2025-002. Motif: Test Motif', '/Insuite_backbones/public/incidents/detail?id=2', 1, '2025-11-09 12:55:38'),
	(5, 1, 'Intervention reprise: INC-2025-002', 'Admin a repris l\'intervention sur INC-2025-002.', '/Insuite_backbones/public/incidents/detail?id=2', 1, '2025-11-09 12:55:56'),
	(6, 1, 'Traitement soumis: INC-2025-001', 'ZAHUI CHRISTIAN WILFRIED a soumis un rapport de traitement pour l\'incident INC-2025-001.', 'http://localhost/Insuite_backbones/public/incidents/treatment?id=1&view=1', 1, '2025-11-09 15:21:32'),
	(7, 1, 'Traitement validé: INC-2025-001', 'Admin a validé le rapport de traitement de l\'incident INC-2025-001.', 'http://localhost/Insuite_backbones/public/incidents/treatment?id=1&view=1', 1, '2025-11-09 15:57:55'),
	(8, 2, 'Traitement validé: INC-2025-001', 'Admin a validé le rapport de traitement de l\'incident INC-2025-001.', 'http://localhost/Insuite_backbones/public/incidents/treatment?id=1&view=1', 1, '2025-11-09 15:57:55'),
	(9, 1, 'Rapport final complété: INC-2025-001', 'ZAHUI CHRISTIAN WILFRIED a complété le rapport final (remontée le 09/11/2025 16:39).', 'http://localhost/Insuite_backbones/public/incidents/detail?id=1', 1, '2025-11-09 16:39:42'),
	(10, 2, 'Rapport final complété: INC-2025-001', 'ZAHUI CHRISTIAN WILFRIED a complété le rapport final (remontée le 09/11/2025 16:39).', 'http://localhost/Insuite_backbones/public/incidents/detail?id=1', 1, '2025-11-09 16:39:42');

-- Listage de la structure de table c2685816c_maintenance_back. permissions
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key_name` varchar(100) NOT NULL,
  `label` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_name` (`key_name`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.permissions : ~24 rows (environ)
INSERT INTO `permissions` (`id`, `key_name`, `label`) VALUES
	(1, 'dashboard.view', 'Voir tableau de bord'),
	(2, 'users.list', 'Lister utilisateurs'),
	(3, 'users.create', 'Cr??er utilisateur'),
	(4, 'users.edit', 'Modifier utilisateur'),
	(5, 'users.delete', 'Supprimer utilisateur'),
	(6, 'users.toggle_2fa', 'Activer/D??sactiver 2FA'),
	(7, 'roles.list', 'Lister r??les'),
	(8, 'roles.create', 'Cr??er r??le'),
	(9, 'roles.edit', 'Modifier r??le'),
	(10, 'roles.delete', 'Supprimer r??le'),
	(11, 'settings.view', 'Voir param??tres'),
	(12, 'settings.edit', 'Modifier param??tres'),
	(13, 'catalog.view', 'Voir catalogue'),
	(14, 'catalog.create', 'Cr??er cat??gorie/article'),
	(15, 'catalog.edit', 'Modifier cat??gorie/article'),
	(16, 'catalog.delete', 'Supprimer cat??gorie/article'),
	(17, 'quotes.list', 'Lister devis'),
	(18, 'quotes.create', 'Cr??er devis'),
	(19, 'quotes.edit', 'Modifier devis'),
	(20, 'quotes.delete', 'Supprimer devis'),
	(21, 'sla.view', 'Voir SLA'),
	(22, 'sla.create', 'Cr??er SLA'),
	(23, 'sla.edit', 'Modifier SLA'),
	(24, 'sla.delete', 'Supprimer SLA');

-- Listage de la structure de table c2685816c_maintenance_back. planning_tasks
CREATE TABLE IF NOT EXISTS `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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.planning_tasks : ~0 rows (environ)
INSERT INTO `planning_tasks` (`id`, `title`, `type`, `start_datetime`, `end_datetime`, `status`, `technician_id`, `incident_id`, `location_id`, `notes`, `color`, `created_at`, `updated_at`) VALUES
	(1, 'Test', 'préventive', '2025-11-10 17:10:00', '2025-11-12 17:10:00', 'Planifié', 2, 1, 2, NULL, '#d0640b', '2025-11-09 17:11:19', '2025-11-09 17:11:19');

-- Listage de la structure de table c2685816c_maintenance_back. quotes
CREATE TABLE IF NOT EXISTS `quotes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quote_no` varchar(40) NOT NULL,
  `client_name` varchar(150) NOT NULL,
  `related_type` enum('incident','preventive','') NOT NULL DEFAULT '',
  `related_ref` varchar(64) DEFAULT 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.00',
  `total_tva` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total_ttc` decimal(12,2) NOT NULL DEFAULT '0.00',
  `currency` varchar(3) NOT NULL DEFAULT 'XOF',
  PRIMARY KEY (`id`),
  UNIQUE KEY `quote_no` (`quote_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.quotes : ~0 rows (environ)

-- Listage de la structure de table c2685816c_maintenance_back. quote_items
CREATE TABLE IF NOT EXISTS `quote_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `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.000',
  `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 ;

-- Listage des données de la table c2685816c_maintenance_back.quote_items : ~0 rows (environ)

-- Listage de la structure de table c2685816c_maintenance_back. roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key_name` varchar(50) NOT NULL,
  `label` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_name` (`key_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.roles : ~3 rows (environ)
INSERT INTO `roles` (`id`, `key_name`, `label`) VALUES
	(1, 'admin', 'Administrateur'),
	(2, 'agent', 'Agent'),
	(3, 'technicien', 'Technicien');

-- Listage de la structure de table c2685816c_maintenance_back. role_permissions
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id` int NOT NULL,
  `permission_id` int NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_permissions_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.role_permissions : ~29 rows (environ)
INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES
	(1, 1),
	(2, 1),
	(3, 1),
	(1, 2),
	(1, 3),
	(1, 4),
	(1, 5),
	(1, 6),
	(1, 7),
	(1, 8),
	(1, 9),
	(1, 10),
	(1, 11),
	(1, 12),
	(1, 13),
	(1, 14),
	(1, 15),
	(1, 16),
	(1, 17),
	(2, 17),
	(1, 18),
	(2, 18),
	(1, 19),
	(2, 19),
	(1, 20),
	(1, 21),
	(1, 22),
	(1, 23),
	(1, 24);

-- Listage de la structure de table c2685816c_maintenance_back. settings
CREATE TABLE IF NOT EXISTS `settings` (
  `key` varchar(100) NOT NULL,
  `value` text,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.settings : ~9 rows (environ)
INSERT INTO `settings` (`key`, `value`) VALUES
	('app_name', 'INSuite GMAO BACKBONES'),
	('default_currency', 'XOF'),
	('enforce_2fa', ''),
	('smtp_encryption', ''),
	('smtp_host', ''),
	('smtp_pass', ''),
	('smtp_port', ''),
	('smtp_user', ''),
	('vat_rate', '18');

-- Listage de la structure de table c2685816c_maintenance_back. sla_policies
CREATE TABLE IF NOT EXISTS `sla_policies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `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',
  PRIMARY KEY (`id`),
  KEY `maintenance_type_id` (`maintenance_type_id`),
  CONSTRAINT `sla_policies_ibfk_1` FOREIGN KEY (`maintenance_type_id`) REFERENCES `maintenance_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.sla_policies : ~8 rows (environ)
INSERT INTO `sla_policies` (`id`, `maintenance_type_id`, `priority`, `response_minutes`, `resolution_minutes`) VALUES
	(1, 2, 'Basse', 480, 1440),
	(2, 2, 'Moyenne', 240, 720),
	(3, 2, 'Haute', 120, 360),
	(4, 2, 'Urgent', 60, 180),
	(5, 1, 'Basse', 240, 720),
	(6, 1, 'Moyenne', 120, 480),
	(7, 1, 'Haute', 60, 240),
	(8, 1, 'Urgent', 30, 120);

-- Listage de la structure de table c2685816c_maintenance_back. technician_locations
CREATE TABLE IF NOT EXISTS `technician_locations` (
  `user_id` int NOT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT 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 ;

-- Listage des données de la table c2685816c_maintenance_back.technician_locations : ~2 rows (environ)
INSERT INTO `technician_locations` (`user_id`, `latitude`, `longitude`, `accuracy`, `heading`, `speed`, `updated_at`) VALUES
	(1, 5.34700000, -4.00700000, 15.2, 90, 0.5, '2025-11-05 19:22:42'),
	(18, 5.36842740, -3.99004050, 34.4, NULL, NULL, '2025-11-05 19:26:52');

-- Listage de la structure de table c2685816c_maintenance_back. users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role_key` varchar(50) NOT NULL DEFAULT 'agent',
  `totp_secret` varchar(64) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `phone` varchar(30) DEFAULT NULL,
  `takeover_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ;

-- Listage des données de la table c2685816c_maintenance_back.users : ~2 rows (environ)
INSERT INTO `users` (`id`, `name`, `email`, `password_hash`, `role_key`, `totp_secret`, `active`, `created_at`, `phone`, `takeover_at`) VALUES
	(1, 'Admin', 'admin@fiberops.local', '$2y$10$IsWBVMB/xKipMwFB/Au3BOaih0m7k97nhtM17.v/IJa9gGMKEHvz2', 'admin', NULL, 1, '2025-11-05 08:59:12', NULL, NULL),
	(2, 'ZAHUI CHRISTIAN WILFRIED', 'wil.zahui@gmail.com', '$2y$10$Qv8u8AiAfAXUkQVbKJOJde5At3cgEw3o8nBDVccZnx.l0y4Z7m/Pu', 'technicien', NULL, 1, '2025-11-06 12:03:11', NULL, '2025-11-09 12:30:14');

-- Listage de la structure de table c2685816c_maintenance_back. user_takeover_events
CREATE TABLE IF NOT EXISTS `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 ;

-- Listage des données de la table c2685816c_maintenance_back.user_takeover_events : ~0 rows (environ)
INSERT INTO `user_takeover_events` (`id`, `user_id`, `taken_at`, `ip_address`, `user_agent`, `lat`, `lng`) VALUES
	(1, 2, '2025-11-09 12:30:14', NULL, NULL, NULL, NULL);

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;