-- Esquema original, sin datos privados. MySQL 8.0+
CREATE TABLE `bitacora_flotilla` (
  `id` int NOT NULL,
  `vehiculo_id` int NOT NULL,
  `tipo_registro` enum('carga_combustible','mantenimiento_preventivo','reparacion') NOT NULL,
  `fecha` date NOT NULL,
  `costo` decimal(10,2) NOT NULL,
  `litros_cargados` decimal(8,2) DEFAULT NULL,
  `kilometraje_registro` int NOT NULL,
  `descripcion` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `choferes` (
  `id` int NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `telefono` varchar(20) DEFAULT NULL,
  `licencia` varchar(50) NOT NULL,
  `vigencia_licencia` date DEFAULT NULL,
  `estado` enum('disponible','en_ruta','descanso','incapacidad') DEFAULT 'disponible'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `comprobantes_entrega` (
  `id` int NOT NULL,
  `envio_id` int NOT NULL,
  `fecha_entrega` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `lat_entrega` decimal(10,8) DEFAULT NULL,
  `lng_entrega` decimal(11,8) DEFAULT NULL,
  `firma_base64` longtext,
  `foto_evidencia` varchar(255) DEFAULT NULL,
  `notas_chofer` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `envios` (
  `id` int NOT NULL,
  `guia_rastreo` varchar(50) NOT NULL,
  `ruta_id` int DEFAULT NULL,
  `cliente_destinatario` varchar(150) NOT NULL,
  `telefono_destinatario` varchar(20) DEFAULT NULL,
  `direccion_destino` text NOT NULL,
  `lat_destino` decimal(10,8) DEFAULT NULL,
  `lng_destino` decimal(11,8) DEFAULT NULL,
  `peso_kg` decimal(8,2) DEFAULT '0.00',
  `estado` enum('en_almacen','asignado_a_ruta','en_transito','entregado','fallido') DEFAULT 'en_almacen',
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `rutas` (
  `id` int NOT NULL,
  `folio` varchar(20) NOT NULL,
  `vehiculo_id` int NOT NULL,
  `chofer_id` int NOT NULL,
  `fecha_ruta` date NOT NULL,
  `zona_operativa` varchar(50) DEFAULT NULL,
  `estado` enum('planificada','en_curso','completada','cancelada') DEFAULT 'planificada',
  `hora_salida` datetime DEFAULT NULL,
  `hora_regreso` datetime DEFAULT NULL,
  `kilometraje_inicial` int DEFAULT NULL,
  `kilometraje_final` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `usuarios` (
  `id` int NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `rol` enum('admin','chofer') NOT NULL,
  `chofer_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `vehiculos` (
  `id` int NOT NULL,
  `placas` varchar(20) NOT NULL,
  `marca` varchar(50) NOT NULL,
  `modelo` varchar(50) DEFAULT NULL,
  `capacidad_kg` decimal(10,2) NOT NULL,
  `rendimiento_km_litro` decimal(5,2) DEFAULT NULL,
  `kilometraje_actual` int DEFAULT '0',
  `estado` enum('disponible','en_ruta','taller','baja') DEFAULT 'disponible',
  `gps_lat` decimal(10,8) DEFAULT NULL,
  `gps_lng` decimal(11,8) DEFAULT NULL,
  `ultima_conexion` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `bitacora_flotilla`
  ADD PRIMARY KEY (`id`),
  ADD KEY `vehiculo_id` (`vehiculo_id`);

ALTER TABLE `choferes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `licencia` (`licencia`);

ALTER TABLE `comprobantes_entrega`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `envio_id` (`envio_id`);

ALTER TABLE `envios`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `guia_rastreo` (`guia_rastreo`),
  ADD KEY `ruta_id` (`ruta_id`);

ALTER TABLE `rutas`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `folio` (`folio`),
  ADD KEY `vehiculo_id` (`vehiculo_id`),
  ADD KEY `chofer_id` (`chofer_id`);

ALTER TABLE `usuarios`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD KEY `chofer_id` (`chofer_id`);

ALTER TABLE `vehiculos`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `placas` (`placas`);

ALTER TABLE `bitacora_flotilla`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `choferes`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `comprobantes_entrega`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `envios`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE `rutas`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `usuarios`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `vehiculos`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `bitacora_flotilla`
  ADD CONSTRAINT `bitacora_flotilla_ibfk_1` FOREIGN KEY (`vehiculo_id`) REFERENCES `vehiculos` (`id`) ON DELETE CASCADE;

ALTER TABLE `comprobantes_entrega`
  ADD CONSTRAINT `comprobantes_entrega_ibfk_1` FOREIGN KEY (`envio_id`) REFERENCES `envios` (`id`) ON DELETE CASCADE;

ALTER TABLE `envios`
  ADD CONSTRAINT `envios_ibfk_1` FOREIGN KEY (`ruta_id`) REFERENCES `rutas` (`id`) ON DELETE SET NULL;

ALTER TABLE `rutas`
  ADD CONSTRAINT `rutas_ibfk_1` FOREIGN KEY (`vehiculo_id`) REFERENCES `vehiculos` (`id`),
  ADD CONSTRAINT `rutas_ibfk_2` FOREIGN KEY (`chofer_id`) REFERENCES `choferes` (`id`);

ALTER TABLE `usuarios`
  ADD CONSTRAINT `usuarios_ibfk_1` FOREIGN KEY (`chofer_id`) REFERENCES `choferes` (`id`) ON DELETE SET NULL;