-- Mobile Checker (mobile-checker)
-- SQL idempotente para el checador operativo de choferes.

CREATE TABLE IF NOT EXISTS `checadas` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `chofer_id` INT UNSIGNED NOT NULL,
  `fecha` DATE NOT NULL,
  `hora_entrada` DATETIME NOT NULL,
  `hora_salida` DATETIME NULL,
  `latitud` VARCHAR(40) NULL,
  `longitud` VARCHAR(40) NULL,
  `latitud_salida` VARCHAR(40) NULL,
  `longitud_salida` VARCHAR(40) NULL,
  `ip` VARCHAR(45) NULL,
  `user_agent` VARCHAR(500) NULL,
  `estado` ENUM('activo','cerrado') NOT NULL DEFAULT 'activo',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_checadas_chofer_fecha` (`chofer_id`, `fecha`),
  INDEX `idx_checadas_estado_fecha` (`estado`, `fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `reportes_servicio` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `servicio_id` INT UNSIGNED NOT NULL,
  `chofer_id` INT UNSIGNED NOT NULL,
  `tipo` ENUM('inicio','fin') NOT NULL,
  `latitud` VARCHAR(40) NULL,
  `longitud` VARCHAR(40) NULL,
  `hora` DATETIME NOT NULL,
  `ip` VARCHAR(45) NULL,
  `user_agent` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_reportes_servicio_tipo` (`servicio_id`, `tipo`),
  INDEX `idx_reportes_servicio_chofer` (`chofer_id`),
  INDEX `idx_reportes_servicio_hora` (`hora`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `incidentes` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `servicio_id` INT UNSIGNED NOT NULL,
  `chofer_id` INT UNSIGNED NOT NULL,
  `tipo` VARCHAR(80) NOT NULL,
  `descripcion` TEXT NOT NULL,
  `severidad` VARCHAR(40) NULL,
  `lat` DECIMAL(10,7) NULL,
  `lng` DECIMAL(10,7) NULL,
  `ocurrido_en` DATETIME NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_incidentes_servicio` (`servicio_id`),
  INDEX `idx_incidentes_chofer` (`chofer_id`),
  INDEX `idx_incidentes_creado_en` (`creado_en`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `incidentes_fotos` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `incidente_id` INT UNSIGNED NOT NULL,
  `ruta` VARCHAR(500) NOT NULL,
  `ancho` INT UNSIGNED NULL,
  `alto` INT UNSIGNED NULL,
  `bytes` INT UNSIGNED NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_incidentes_fotos_incidente` (`incidente_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mobile_checker_settings` (
  `setting_key` VARCHAR(80) NOT NULL PRIMARY KEY,
  `setting_value` TEXT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
