-- ============================================================
-- SIGMA-COE — Esquema Consolidado de Base de Datos (FASE 1-17)
-- Motor: InnoDB | Charset: utf8mb4_unicode_ci
-- Generado para despliegue en producción
-- ============================================================

CREATE DATABASE IF NOT EXISTS sigma_coe_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE sigma_coe_db;

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- FASE 1A — Núcleo Central
-- ============================================================

CREATE TABLE IF NOT EXISTS `instituciones` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(60) NOT NULL,
  `nombre` varchar(180) NOT NULL,
  `siglas` varchar(50) DEFAULT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `instituciones_codigo_key` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `usuarios` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `institucion_id` bigint(20) unsigned DEFAULT NULL,
  `correo` varchar(150) NOT NULL,
  `nombres` varchar(120) NOT NULL,
  `apellidos` varchar(120) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `estado` enum('ACTIVE','INACTIVE','BLOCKED') NOT NULL DEFAULT 'ACTIVE',
  `intentos_fallidos` int(11) NOT NULL DEFAULT 0,
  `bloqueado_hasta` datetime(3) DEFAULT NULL,
  `ultimo_acceso_at` datetime(3) DEFAULT NULL,
  `ultimo_cambio_clave_at` datetime(3) DEFAULT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `usuarios_correo_key` (`correo`),
  KEY `usuarios_institucion_id_fkey` (`institucion_id`),
  CONSTRAINT `usuarios_institucion_id_fkey` FOREIGN KEY (`institucion_id`) REFERENCES `instituciones` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(120) NOT NULL,
  `descripcion` varchar(255) DEFAULT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_nombre_key` (`nombre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `permisos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(150) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `descripcion` varchar(255) DEFAULT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permisos_codigo_key` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `modulos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(80) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `descripcion` varchar(255) DEFAULT NULL,
  `icono` varchar(50) DEFAULT NULL,
  `ruta` varchar(100) DEFAULT NULL,
  `orden` int(10) unsigned NOT NULL DEFAULT 0,
  `estado` enum('ACTIVE','INACTIVE','INTEGRABLE') NOT NULL DEFAULT 'ACTIVE',
  `es_funcional` tinyint(1) NOT NULL DEFAULT 0,
  `es_integrable` tinyint(1) NOT NULL DEFAULT 0,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `modulos_codigo_key` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `usuarios_roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `usuario_id` bigint(20) unsigned NOT NULL,
  `rol_id` bigint(20) unsigned NOT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `usuarios_roles_usuario_id_rol_id_key` (`usuario_id`,`rol_id`),
  KEY `usuarios_roles_rol_id_fkey` (`rol_id`),
  CONSTRAINT `usuarios_roles_rol_id_fkey` FOREIGN KEY (`rol_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `usuarios_roles_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `usuarios_modulos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `usuario_id` bigint(20) unsigned NOT NULL,
  `modulo_id` bigint(20) unsigned NOT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `usuarios_modulos_usuario_id_modulo_id_key` (`usuario_id`,`modulo_id`),
  KEY `usuarios_modulos_modulo_id_fkey` (`modulo_id`),
  CONSTRAINT `usuarios_modulos_modulo_id_fkey` FOREIGN KEY (`modulo_id`) REFERENCES `modulos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `usuarios_modulos_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `roles_permisos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `rol_id` bigint(20) unsigned NOT NULL,
  `permiso_id` bigint(20) unsigned NOT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_permisos_rol_id_permiso_id_key` (`rol_id`,`permiso_id`),
  KEY `roles_permisos_permiso_id_fkey` (`permiso_id`),
  CONSTRAINT `roles_permisos_permiso_id_fkey` FOREIGN KEY (`permiso_id`) REFERENCES `permisos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `roles_permisos_rol_id_fkey` FOREIGN KEY (`rol_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `parametros` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `categoria` varchar(80) NOT NULL,
  `clave` varchar(120) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `valor` text NOT NULL,
  `tipo_dato` varchar(40) NOT NULL DEFAULT 'string',
  `descripcion` varchar(255) DEFAULT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `parametros_clave_key` (`clave`),
  KEY `parametros_categoria_idx` (`categoria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `configuracion_sistema` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `clave` varchar(120) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `valor` text NOT NULL,
  `tipo_dato` varchar(40) NOT NULL DEFAULT 'string',
  `descripcion` varchar(255) DEFAULT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `configuracion_sistema_clave_key` (`clave`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sesiones` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `usuario_id` bigint(20) unsigned NOT NULL,
  `session_id` varchar(500) NOT NULL,
  `refresh_hint` varchar(500) DEFAULT NULL,
  `ip` varchar(80) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `status` enum('ACTIVE','CLOSED','EXPIRED','REVOKED') NOT NULL DEFAULT 'ACTIVE',
  `ultimo_acceso_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `expira_at` datetime(3) NOT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `cerrado_at` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sesiones_session_id_key` (`session_id`),
  KEY `sesiones_usuario_id_status_idx` (`usuario_id`,`status`),
  CONSTRAINT `sesiones_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `intentos_login` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `usuario_id` bigint(20) unsigned DEFAULT NULL,
  `correo` varchar(150) NOT NULL,
  `ip` varchar(80) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `estado` enum('SUCCESS','FAILED','BLOCKED') NOT NULL,
  `mensaje` varchar(255) DEFAULT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  PRIMARY KEY (`id`),
  KEY `intentos_login_correo_creado_at_idx` (`correo`,`creado_at`),
  KEY `intentos_login_ip_creado_at_idx` (`ip`,`creado_at`),
  KEY `intentos_login_usuario_id_fkey` (`usuario_id`),
  CONSTRAINT `intentos_login_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `auditoria_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `usuario_id` bigint(20) unsigned DEFAULT NULL,
  `modulo_id` bigint(20) unsigned DEFAULT NULL,
  `accion` varchar(120) NOT NULL,
  `entidad` varchar(120) NOT NULL,
  `entidad_id` varchar(120) DEFAULT NULL,
  `detalle` varchar(255) DEFAULT NULL,
  `ip` varchar(80) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `ruta` varchar(255) DEFAULT NULL,
  `metodo` varchar(10) DEFAULT NULL,
  `resultado` varchar(50) DEFAULT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  PRIMARY KEY (`id`),
  KEY `auditoria_logs_accion_creado_at_idx` (`accion`,`creado_at`),
  KEY `auditoria_logs_entidad_creado_at_idx` (`entidad`,`creado_at`),
  KEY `auditoria_logs_usuario_id_fkey` (`usuario_id`),
  KEY `auditoria_logs_modulo_id_fkey` (`modulo_id`),
  CONSTRAINT `auditoria_logs_modulo_id_fkey` FOREIGN KEY (`modulo_id`) REFERENCES `modulos` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `auditoria_logs_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `usuarios_seguridad` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `usuario_id` bigint(20) unsigned NOT NULL,
  `mfa_habilitado` tinyint(1) NOT NULL DEFAULT 0,
  `passkeys_habilitado` tinyint(1) NOT NULL DEFAULT 0,
  `webauthn_user_handle` varchar(191) DEFAULT NULL,
  `webauthnCredentials` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`webauthnCredentials`)),
  `recovery_email` varchar(150) DEFAULT NULL,
  `alertas_seguridad` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `usuarios_seguridad_usuario_id_key` (`usuario_id`),
  CONSTRAINT `usuarios_seguridad_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `app_versions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `version` varchar(80) NOT NULL,
  `build` varchar(80) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `released_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `created_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updated_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `app_versions_version_key` (`version`),
  KEY `app_versions_is_active_released_at_idx` (`is_active`,`released_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rate_limits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `clave` varchar(64) NOT NULL,
  `ventana` int(10) unsigned NOT NULL,
  `contador` int(10) unsigned NOT NULL DEFAULT 1,
  `expira_at` datetime(3) NOT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `rate_limits_clave_ventana_key` (`clave`,`ventana`),
  KEY `rate_limits_expira_at_idx` (`expira_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 1B — Territorial
-- ============================================================

CREATE TABLE IF NOT EXISTS `administraciones_zonales` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(60) NOT NULL,
  `nombre` varchar(180) NOT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `administraciones_zonales_codigo_key` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `parroquias` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `administracion_zonal_id` bigint(20) unsigned NOT NULL,
  `codigo` varchar(60) NOT NULL,
  `nombre` varchar(180) NOT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `parroquias_codigo_key` (`codigo`),
  KEY `parroquias_administracion_zonal_id_fkey` (`administracion_zonal_id`),
  CONSTRAINT `parroquias_administracion_zonal_id_fkey` FOREIGN KEY (`administracion_zonal_id`) REFERENCES `administraciones_zonales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `barrios` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `parroquia_id` bigint(20) unsigned NOT NULL,
  `codigo` varchar(60) NOT NULL,
  `nombre` varchar(180) NOT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `barrios_codigo_key` (`codigo`),
  KEY `barrios_parroquia_id_fkey` (`parroquia_id`),
  CONSTRAINT `barrios_parroquia_id_fkey` FOREIGN KEY (`parroquia_id`) REFERENCES `parroquias` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `usuarios_territorio` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `usuario_id` bigint(20) unsigned NOT NULL,
  `barrio_id` bigint(20) unsigned DEFAULT NULL,
  `estado` tinyint(1) DEFAULT 1,
  `creado_at` datetime(3) DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) DEFAULT NULL ON UPDATE current_timestamp(3),
  PRIMARY KEY (`id`),
  KEY `usuario_id` (`usuario_id`),
  KEY `barrio_id` (`barrio_id`),
  CONSTRAINT `fk_usu_territorio_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_usu_territorio_barrio` FOREIGN KEY (`barrio_id`) REFERENCES `barrios` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `configuracion_modulos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `modulo_id` bigint(20) unsigned NOT NULL,
  `nombre_visible` varchar(150) NOT NULL,
  `logo_url` varchar(255) DEFAULT NULL,
  `color_primario` varchar(20) DEFAULT NULL,
  `color_secundario` varchar(20) DEFAULT NULL,
  `url_futura` varchar(255) DEFAULT NULL,
  `estado_visible` enum('ACTIVE','INACTIVE','INTEGRABLE') NOT NULL DEFAULT 'ACTIVE',
  `configuracion` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`configuracion`)),
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `configuracion_modulos_modulo_id_key` (`modulo_id`),
  CONSTRAINT `configuracion_modulos_modulo_id_fkey` FOREIGN KEY (`modulo_id`) REFERENCES `modulos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `plantillas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(80) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `canal` enum('WHATSAPP','EMAIL','PDF','REPORT') NOT NULL,
  `asunto` varchar(150) DEFAULT NULL,
  `contenido` text NOT NULL,
  `variables` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`variables`)),
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plantillas_codigo_key` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `grupos_notificacion` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(80) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `descripcion` varchar(255) DEFAULT NULL,
  `canales` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`canales`)),
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `grupos_notificacion_codigo_key` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 2 — Ficha Única y Motor Base de Eventos
-- ============================================================

CREATE TABLE IF NOT EXISTS `ficha_prioridades` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(100) NOT NULL,
  `nivel` int(10) unsigned NOT NULL DEFAULT 1,
  `color` varchar(20) DEFAULT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ficha_prioridades_nombre_key` (`nombre`),
  KEY `ficha_prioridades_nivel_idx` (`nivel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ficha_estados` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(40) NOT NULL,
  `nombre` varchar(100) NOT NULL,
  `descripcion` varchar(255) DEFAULT NULL,
  `color` varchar(20) DEFAULT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ficha_estados_codigo_key` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ficha_tipos_evento` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(40) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `descripcion` varchar(255) DEFAULT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ficha_tipos_evento_codigo_key` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ficha_subtipos_evento` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tipo_evento_id` bigint(20) unsigned NOT NULL,
  `codigo` varchar(40) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `descripcion` varchar(255) DEFAULT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ficha_subtipos_evento_codigo_key` (`codigo`),
  KEY `ficha_subtipos_evento_tipo_evento_id_idx` (`tipo_evento_id`),
  CONSTRAINT `ficha_subtipos_evento_tipo_evento_id_fkey` FOREIGN KEY (`tipo_evento_id`) REFERENCES `ficha_tipos_evento` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fichas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `codigo_ficha` varchar(30) NOT NULL,
  `numero_ecu911` varchar(60) DEFAULT NULL,
  `fecha` date NOT NULL,
  `hora` time NOT NULL,
  `prioridad_id` bigint(20) unsigned NOT NULL,
  `estado_id` bigint(20) unsigned NOT NULL,
  `tipo_evento_id` bigint(20) unsigned NOT NULL,
  `subtipo_evento_id` bigint(20) unsigned DEFAULT NULL,
  `administracion_zonal_id` bigint(20) unsigned NOT NULL,
  `parroquia_id` bigint(20) unsigned DEFAULT NULL,
  `barrio_id` bigint(20) unsigned DEFAULT NULL,
  `direccion` varchar(300) NOT NULL,
  `latitud` decimal(10,7) DEFAULT NULL,
  `longitud` decimal(10,7) DEFAULT NULL,
  `descripcion` text NOT NULL,
  `institucion_id` bigint(20) unsigned NOT NULL,
  `creador_id` bigint(20) unsigned NOT NULL,
  `modulo_origen_id` bigint(20) unsigned NOT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fichas_codigo_ficha_key` (`codigo_ficha`),
  KEY `fichas_numero_ecu911_idx` (`numero_ecu911`),
  KEY `fichas_fecha_idx` (`fecha`),
  KEY `fichas_estado_id_idx` (`estado_id`),
  KEY `fichas_prioridad_id_idx` (`prioridad_id`),
  KEY `fichas_tipo_evento_id_idx` (`tipo_evento_id`),
  KEY `fichas_administracion_zonal_id_idx` (`administracion_zonal_id`),
  KEY `fichas_creador_id_idx` (`creador_id`),
  CONSTRAINT `fichas_prioridad_id_fkey` FOREIGN KEY (`prioridad_id`) REFERENCES `ficha_prioridades` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_estado_id_fkey` FOREIGN KEY (`estado_id`) REFERENCES `ficha_estados` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_tipo_evento_id_fkey` FOREIGN KEY (`tipo_evento_id`) REFERENCES `ficha_tipos_evento` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_subtipo_evento_id_fkey` FOREIGN KEY (`subtipo_evento_id`) REFERENCES `ficha_subtipos_evento` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_administracion_zonal_id_fkey` FOREIGN KEY (`administracion_zonal_id`) REFERENCES `administraciones_zonales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_parroquia_id_fkey` FOREIGN KEY (`parroquia_id`) REFERENCES `parroquias` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_barrio_id_fkey` FOREIGN KEY (`barrio_id`) REFERENCES `barrios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_institucion_id_fkey` FOREIGN KEY (`institucion_id`) REFERENCES `instituciones` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_creador_id_fkey` FOREIGN KEY (`creador_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fichas_modulo_origen_id_fkey` FOREIGN KEY (`modulo_origen_id`) REFERENCES `modulos` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ficha_linea_tiempo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ficha_id` bigint(20) unsigned NOT NULL,
  `accion` varchar(120) NOT NULL,
  `descripcion` text DEFAULT NULL,
  `usuario_id` bigint(20) unsigned NOT NULL,
  `modulo_origen_id` bigint(20) unsigned DEFAULT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  PRIMARY KEY (`id`),
  KEY `ficha_linea_tiempo_ficha_id_idx` (`ficha_id`),
  KEY `ficha_linea_tiempo_creado_at_idx` (`ficha_id`,`creado_at`),
  CONSTRAINT `ficha_linea_tiempo_ficha_id_fkey` FOREIGN KEY (`ficha_id`) REFERENCES `fichas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ficha_linea_tiempo_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ficha_historial_cambios` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ficha_id` bigint(20) unsigned NOT NULL,
  `campo` varchar(80) NOT NULL,
  `valor_anterior` text DEFAULT NULL,
  `valor_nuevo` text NOT NULL,
  `usuario_id` bigint(20) unsigned NOT NULL,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  PRIMARY KEY (`id`),
  KEY `ficha_historial_cambios_ficha_id_idx` (`ficha_id`),
  CONSTRAINT `ficha_historial_cambios_ficha_id_fkey` FOREIGN KEY (`ficha_id`) REFERENCES `fichas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ficha_historial_cambios_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ficha_evidencias` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ficha_id` bigint(20) unsigned NOT NULL,
  `nombre_archivo` varchar(255) NOT NULL,
  `tipo_archivo` varchar(80) NOT NULL,
  `tamano` int(10) unsigned NOT NULL DEFAULT 0,
  `ruta` varchar(500) NOT NULL,
  `usuario_id` bigint(20) unsigned NOT NULL,
  `estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `actualizado_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ficha_evidencias_ficha_id_idx` (`ficha_id`),
  CONSTRAINT `ficha_evidencias_ficha_id_fkey` FOREIGN KEY (`ficha_id`) REFERENCES `fichas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ficha_evidencias_usuario_id_fkey` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 3 — Módulo Cabina Operativa
-- ============================================================

CREATE TABLE IF NOT EXISTS `cabina_despachos` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `institucion_id` BIGINT UNSIGNED NOT NULL,
  `recurso` VARCHAR(255) DEFAULT NULL,
  `responsable` VARCHAR(255) DEFAULT NULL,
  `observacion` TEXT DEFAULT NULL,
  `estado_despacho` VARCHAR(60) NOT NULL DEFAULT 'pendiente',
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `fecha_despacho` DATE DEFAULT NULL,
  `hora_despacho` TIME DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_cabina_despachos_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cabina_despachos_institucion` FOREIGN KEY (`institucion_id`) REFERENCES `instituciones`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cabina_despachos_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_cabina_despachos_ficha` (`ficha_id`),
  INDEX `idx_cabina_despachos_estado` (`estado_despacho`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cabina_coordinaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `institucion_id` BIGINT UNSIGNED NOT NULL,
  `descripcion` TEXT NOT NULL,
  `respuesta` TEXT DEFAULT NULL,
  `estado_coordinacion` VARCHAR(60) NOT NULL DEFAULT 'pendiente',
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_cabina_coordinaciones_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cabina_coordinaciones_institucion` FOREIGN KEY (`institucion_id`) REFERENCES `instituciones`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cabina_coordinaciones_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_cabina_coordinaciones_ficha` (`ficha_id`),
  INDEX `idx_cabina_coordinaciones_estado` (`estado_coordinacion`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cabina_tiempos` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `tipo_tiempo` VARCHAR(60) NOT NULL,
  `fecha_hora` DATETIME(3) NOT NULL,
  `observacion` TEXT DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_cabina_tiempos_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cabina_tiempos_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_cabina_tiempos_ficha` (`ficha_id`),
  INDEX `idx_cabina_tiempos_tipo` (`tipo_tiempo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cabina_activaciones_zonales` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `administracion_zonal_id` BIGINT UNSIGNED NOT NULL,
  `responsable` VARCHAR(255) DEFAULT NULL,
  `estado_activacion` VARCHAR(60) NOT NULL DEFAULT 'pendiente',
  `observacion` TEXT DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_cabina_act_zonales_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cabina_act_zonales_zonal` FOREIGN KEY (`administracion_zonal_id`) REFERENCES `administraciones_zonales`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cabina_act_zonales_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_cabina_act_zonales_ficha` (`ficha_id`),
  INDEX `idx_cabina_act_zonales_estado` (`estado_activacion`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 4 — Evento Macro y Gestión Multi-Ficha
-- ============================================================

CREATE TABLE IF NOT EXISTS `macro_eventos` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `codigo_macro` VARCHAR(30) NOT NULL UNIQUE,
  `nombre` VARCHAR(255) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `tipo_macro` VARCHAR(80) NOT NULL,
  `prioridad_id` BIGINT UNSIGNED NOT NULL,
  `estado_id` BIGINT UNSIGNED NOT NULL,
  `fecha_inicio` DATETIME(3) NOT NULL,
  `fecha_fin` DATETIME(3) DEFAULT NULL,
  `responsable_id` BIGINT UNSIGNED DEFAULT NULL,
  `observacion_cierre` TEXT DEFAULT NULL,
  `estado` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_macro_prioridad` FOREIGN KEY (`prioridad_id`) REFERENCES `ficha_prioridades`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_macro_estado` FOREIGN KEY (`estado_id`) REFERENCES `ficha_estados`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_macro_responsable` FOREIGN KEY (`responsable_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_macro_codigo` (`codigo_macro`),
  INDEX `idx_macro_tipo` (`tipo_macro`),
  INDEX `idx_macro_estado` (`estado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `macro_evento_fichas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `macro_id` BIGINT UNSIGNED NOT NULL,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_mef_macro` FOREIGN KEY (`macro_id`) REFERENCES `macro_eventos`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mef_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_macro_ficha` (`macro_id`, `ficha_id`),
  INDEX `idx_mef_macro` (`macro_id`),
  INDEX `idx_mef_ficha` (`ficha_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `macro_evento_responsables` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `macro_id` BIGINT UNSIGNED NOT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `rol` VARCHAR(80) DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_mer_macro` FOREIGN KEY (`macro_id`) REFERENCES `macro_eventos`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mer_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_mer_macro` (`macro_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `macro_evento_zonales` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `macro_id` BIGINT UNSIGNED NOT NULL,
  `administracion_zonal_id` BIGINT UNSIGNED NOT NULL,
  `estado` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_mez_macro` FOREIGN KEY (`macro_id`) REFERENCES `macro_eventos`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mez_zonal` FOREIGN KEY (`administracion_zonal_id`) REFERENCES `administraciones_zonales`(`id`) ON DELETE SET NULL,
  INDEX `idx_mez_macro` (`macro_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `macro_evento_instituciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `macro_id` BIGINT UNSIGNED NOT NULL,
  `institucion_id` BIGINT UNSIGNED NOT NULL,
  `estado` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_mei_macro` FOREIGN KEY (`macro_id`) REFERENCES `macro_eventos`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mei_institucion` FOREIGN KEY (`institucion_id`) REFERENCES `instituciones`(`id`) ON DELETE SET NULL,
  INDEX `idx_mei_macro` (`macro_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 5 — Módulo EVIN
-- ============================================================

CREATE TABLE IF NOT EXISTS `evin_asignaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `estado_asignacion` VARCHAR(60) NOT NULL DEFAULT 'asignado',
  `observacion` TEXT DEFAULT NULL,
  `llegada_at` DATETIME(3) DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evin_asig_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_evin_asig_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_evin_asig_ficha` (`ficha_id`),
  INDEX `idx_evin_asig_estado` (`estado_asignacion`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evin_ubicaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `latitud` DECIMAL(10,7) NOT NULL,
  `longitud` DECIMAL(10,7) NOT NULL,
  `direccion_confirmada` VARCHAR(300) DEFAULT NULL,
  `referencia` VARCHAR(255) DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evin_ubic_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_evin_ubic_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_evin_ubic_ficha` (`ficha_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evin_evaluaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `tipo_evaluacion` VARCHAR(60) NOT NULL,
  `descripcion` TEXT NOT NULL,
  `afectados` INT UNSIGNED DEFAULT 0,
  `fallecidos` INT UNSIGNED DEFAULT 0,
  `heridos` INT UNSIGNED DEFAULT 0,
  `damnificados` INT UNSIGNED DEFAULT 0,
  `nivel_riesgo` VARCHAR(60) DEFAULT NULL,
  `requiere_recursos` TINYINT(1) NOT NULL DEFAULT 0,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evin_eval_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_evin_eval_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_evin_eval_ficha` (`ficha_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evin_recursos` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `tipo_recurso` VARCHAR(120) NOT NULL,
  `cantidad` INT UNSIGNED NOT NULL DEFAULT 1,
  `descripcion` TEXT DEFAULT NULL,
  `estado_solicitud` VARCHAR(60) NOT NULL DEFAULT 'solicitado',
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evin_rec_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_evin_rec_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_evin_rec_ficha` (`ficha_id`),
  INDEX `idx_evin_rec_estado` (`estado_solicitud`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 6 — Módulo EVAD (Evaluación de Daños)
-- ============================================================

CREATE TABLE IF NOT EXISTS `evad_evaluaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `evin_evaluacion_id` BIGINT UNSIGNED DEFAULT NULL,
  `tipo_evad` VARCHAR(20) NOT NULL COMMENT 'inicial|rapido|detallado',
  `evaluador_id` BIGINT UNSIGNED NOT NULL,
  `fecha_evaluacion` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `resumen` TEXT DEFAULT NULL,
  `nivel_afectacion` VARCHAR(20) NOT NULL COMMENT 'bajo|medio|alto|critico',
  `estado_evad` VARCHAR(20) NOT NULL DEFAULT 'borrador' COMMENT 'borrador|enviado|validado|cerrado',
  `observacion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evad_eval_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_evad_eval_evin` FOREIGN KEY (`evin_evaluacion_id`) REFERENCES `evin_evaluaciones`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_evad_eval_usuario` FOREIGN KEY (`evaluador_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_evad_eval_ficha` (`ficha_id`),
  INDEX `idx_evad_eval_tipo` (`tipo_evad`),
  INDEX `idx_evad_eval_estado` (`estado_evad`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evad_danios` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `evad_id` BIGINT UNSIGNED NOT NULL,
  `categoria` VARCHAR(120) NOT NULL,
  `descripcion` TEXT NOT NULL,
  `severidad` VARCHAR(20) DEFAULT NULL COMMENT 'leve|moderado|severo|critico',
  `cantidad` INT UNSIGNED DEFAULT 1,
  `unidad` VARCHAR(60) DEFAULT NULL,
  `observacion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evad_danios_evad` FOREIGN KEY (`evad_id`) REFERENCES `evad_evaluaciones`(`id`) ON DELETE CASCADE,
  INDEX `idx_evad_danios_evad` (`evad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evad_afectaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `evad_id` BIGINT UNSIGNED NOT NULL,
  `tipo_afectacion` VARCHAR(120) NOT NULL,
  `personas_afectadas` INT UNSIGNED DEFAULT 0,
  `familias_afectadas` INT UNSIGNED DEFAULT 0,
  `viviendas_afectadas` INT UNSIGNED DEFAULT 0,
  `infraestructura_afectada` TEXT DEFAULT NULL,
  `observacion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evad_afect_evad` FOREIGN KEY (`evad_id`) REFERENCES `evad_evaluaciones`(`id`) ON DELETE CASCADE,
  INDEX `idx_evad_afect_evad` (`evad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evad_necesidades` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `evad_id` BIGINT UNSIGNED NOT NULL,
  `necesidad` VARCHAR(255) NOT NULL,
  `cantidad` INT UNSIGNED NOT NULL DEFAULT 1,
  `unidad` VARCHAR(60) DEFAULT NULL,
  `prioridad` VARCHAR(20) NOT NULL DEFAULT 'media' COMMENT 'baja|media|alta|critica',
  `observacion` TEXT DEFAULT NULL,
  `estado` VARCHAR(20) NOT NULL DEFAULT 'pendiente' COMMENT 'pendiente|aprobada|en_proceso|satisfecha|cancelada',
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evad_neces_evad` FOREIGN KEY (`evad_id`) REFERENCES `evad_evaluaciones`(`id`) ON DELETE CASCADE,
  INDEX `idx_evad_neces_evad` (`evad_id`),
  INDEX `idx_evad_neces_prioridad` (`prioridad`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evad_evidencias` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `evad_id` BIGINT UNSIGNED NOT NULL,
  `archivo` VARCHAR(500) NOT NULL,
  `tipo_archivo` VARCHAR(60) DEFAULT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evad_evid_evad` FOREIGN KEY (`evad_id`) REFERENCES `evad_evaluaciones`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_evad_evid_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_evad_evid_evad` (`evad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 7 — EVIN Familiar
-- ============================================================

CREATE TABLE IF NOT EXISTS `evin_familiar` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `ficha_id` bigint unsigned DEFAULT NULL,
  `evin_id` bigint unsigned DEFAULT NULL,
  `evad_id` bigint unsigned DEFAULT NULL,
  `macro_id` bigint unsigned DEFAULT NULL,
  `codigo_familiar` varchar(30) DEFAULT NULL,
  `jefe_hogar_nombres` varchar(200) NOT NULL,
  `jefe_hogar_documento` varchar(60) DEFAULT NULL,
  `telefono` varchar(40) DEFAULT NULL,
  `direccion` varchar(300) DEFAULT NULL,
  `latitud` decimal(10,7) DEFAULT NULL,
  `longitud` decimal(10,7) DEFAULT NULL,
  `numero_integrantes` int unsigned DEFAULT 0,
  `estado` varchar(20) DEFAULT 'activo',
  `observacion` text,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_evinfam_ficha` (`ficha_id`),
  KEY `idx_evinfam_codigo` (`codigo_familiar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evin_familiar_integrantes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `evin_familiar_id` bigint unsigned NOT NULL,
  `nombres` varchar(200) NOT NULL,
  `identificacion` varchar(60) DEFAULT NULL,
  `edad` int unsigned DEFAULT NULL,
  `parentesco` varchar(60) DEFAULT NULL,
  `telefono` varchar(40) DEFAULT NULL,
  `observacion` text,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `fk_evinfam_int_evinfam` (`evin_familiar_id`),
  CONSTRAINT `fk_evinfam_int_evinfam` FOREIGN KEY (`evin_familiar_id`) REFERENCES `evin_familiar`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `evin_familiar`
  ADD COLUMN IF NOT EXISTS `ficha_id` BIGINT UNSIGNED DEFAULT NULL AFTER `id`,
  ADD COLUMN IF NOT EXISTS `evin_id` BIGINT UNSIGNED DEFAULT NULL AFTER `ficha_id`,
  ADD COLUMN IF NOT EXISTS `evad_id` BIGINT UNSIGNED DEFAULT NULL AFTER `evin_id`,
  ADD COLUMN IF NOT EXISTS `macro_id` BIGINT UNSIGNED DEFAULT NULL AFTER `evad_id`,
  ADD COLUMN IF NOT EXISTS `numero_integrantes` INT UNSIGNED DEFAULT 0 AFTER `direccion`,
  ADD COLUMN IF NOT EXISTS `telefono` VARCHAR(40) DEFAULT NULL AFTER `jefe_hogar_documento`;

CREATE TABLE IF NOT EXISTS `evin_familiar_necesidades` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `evin_familiar_id` BIGINT UNSIGNED NOT NULL,
  `necesidad` VARCHAR(255) NOT NULL,
  `cantidad` INT UNSIGNED NOT NULL DEFAULT 1,
  `prioridad` VARCHAR(20) NOT NULL DEFAULT 'media',
  `observacion` TEXT DEFAULT NULL,
  `estado` VARCHAR(20) NOT NULL DEFAULT 'pendiente',
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evinfam_neces_evinfam` FOREIGN KEY (`evin_familiar_id`) REFERENCES `evin_familiar`(`id`) ON DELETE CASCADE,
  INDEX `idx_evinfam_neces_fam` (`evin_familiar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `evin_familiar_evidencias` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `evin_familiar_id` BIGINT UNSIGNED NOT NULL,
  `archivo` VARCHAR(500) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_evinfam_evid_evinfam` FOREIGN KEY (`evin_familiar_id`) REFERENCES `evin_familiar`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_evinfam_evid_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_evinfam_evid_fam` (`evin_familiar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 8 — Ayuda Humanitaria
-- ============================================================

CREATE TABLE IF NOT EXISTS `ayuda_solicitudes` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `evin_familiar_id` BIGINT UNSIGNED DEFAULT NULL,
  `evad_id` BIGINT UNSIGNED DEFAULT NULL,
  `macro_id` BIGINT UNSIGNED DEFAULT NULL,
  `solicitante_id` BIGINT UNSIGNED NOT NULL,
  `tipo_ayuda` VARCHAR(120) NOT NULL,
  `prioridad` VARCHAR(20) NOT NULL DEFAULT 'media',
  `justificacion` TEXT NOT NULL,
  `estado_solicitud` VARCHAR(20) NOT NULL DEFAULT 'solicitada',
  `observacion_revision` TEXT DEFAULT NULL,
  `aprobado_por` BIGINT UNSIGNED DEFAULT NULL,
  `fecha_aprobacion` DATETIME(3) DEFAULT NULL,
  `cerrado_por` BIGINT UNSIGNED DEFAULT NULL,
  `fecha_cierre` DATETIME(3) DEFAULT NULL,
  `observacion_cierre` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_ayuda_sol_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ayuda_sol_solicitante` FOREIGN KEY (`solicitante_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_ayuda_sol_ficha` (`ficha_id`),
  INDEX `idx_ayuda_sol_estado` (`estado_solicitud`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ayuda_beneficiarios` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `solicitud_id` BIGINT UNSIGNED NOT NULL,
  `evin_familiar_integrante_id` BIGINT UNSIGNED DEFAULT NULL,
  `nombres` VARCHAR(200) NOT NULL,
  `identificacion` VARCHAR(80) DEFAULT NULL,
  `edad` INT UNSIGNED DEFAULT NULL,
  `telefono` VARCHAR(40) DEFAULT NULL,
  `observacion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_ayuda_benef_sol` FOREIGN KEY (`solicitud_id`) REFERENCES `ayuda_solicitudes`(`id`) ON DELETE CASCADE,
  INDEX `idx_ayuda_benef_sol` (`solicitud_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ayuda_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `solicitud_id` BIGINT UNSIGNED NOT NULL,
  `item` VARCHAR(255) NOT NULL,
  `cantidad` INT UNSIGNED NOT NULL DEFAULT 1,
  `unidad` VARCHAR(60) DEFAULT NULL,
  `prioridad` VARCHAR(20) NOT NULL DEFAULT 'media',
  `estado` VARCHAR(20) NOT NULL DEFAULT 'pendiente',
  `observacion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_ayuda_items_sol` FOREIGN KEY (`solicitud_id`) REFERENCES `ayuda_solicitudes`(`id`) ON DELETE CASCADE,
  INDEX `idx_ayuda_items_sol` (`solicitud_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ayuda_entregas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `solicitud_id` BIGINT UNSIGNED NOT NULL,
  `entregado_por` BIGINT UNSIGNED NOT NULL,
  `recibido_por` VARCHAR(200) DEFAULT NULL,
  `fecha_entrega` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `lugar_entrega` VARCHAR(300) DEFAULT NULL,
  `observacion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_ayuda_ent_sol` FOREIGN KEY (`solicitud_id`) REFERENCES `ayuda_solicitudes`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ayuda_entregador` FOREIGN KEY (`entregado_por`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_ayuda_ent_sol` (`solicitud_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ayuda_evidencias` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `solicitud_id` BIGINT UNSIGNED NOT NULL,
  `entrega_id` BIGINT UNSIGNED DEFAULT NULL,
  `archivo` VARCHAR(500) NOT NULL,
  `tipo_archivo` VARCHAR(60) DEFAULT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_ayuda_evid_sol` FOREIGN KEY (`solicitud_id`) REFERENCES `ayuda_solicitudes`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ayuda_evid_entrega` FOREIGN KEY (`entrega_id`) REFERENCES `ayuda_entregas`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ayuda_evid_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_ayuda_evid_sol` (`solicitud_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 9 — Requerimientos EVIN ↔ Cabina
-- ============================================================

CREATE TABLE IF NOT EXISTS `requerimientos_evin_cabina` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED NOT NULL,
  `solicitante_id` BIGINT UNSIGNED NOT NULL,
  `tipo_requerimiento` VARCHAR(60) NOT NULL,
  `prioridad` VARCHAR(20) NOT NULL DEFAULT 'media',
  `descripcion` TEXT NOT NULL,
  `estado` VARCHAR(20) NOT NULL DEFAULT 'solicitado',
  `coordinador_id` BIGINT UNSIGNED DEFAULT NULL,
  `observacion_coordinacion` TEXT DEFAULT NULL,
  `fecha_coordinacion` DATETIME(3) DEFAULT NULL,
  `respondedor_id` BIGINT UNSIGNED DEFAULT NULL,
  `respuesta` TEXT DEFAULT NULL,
  `fecha_respuesta` DATETIME(3) DEFAULT NULL,
  `receptor_id` BIGINT UNSIGNED DEFAULT NULL,
  `fecha_recepcion` DATETIME(3) DEFAULT NULL,
  `cerrado_por` BIGINT UNSIGNED DEFAULT NULL,
  `observacion_cierre` TEXT DEFAULT NULL,
  `fecha_cierre` DATETIME(3) DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_req_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_req_solicitante` FOREIGN KEY (`solicitante_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_req_ficha` (`ficha_id`),
  INDEX `idx_req_estado` (`estado`),
  INDEX `idx_req_tipo` (`tipo_requerimiento`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `requerimientos_respuestas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `requerimiento_id` BIGINT UNSIGNED NOT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `respuesta` TEXT NOT NULL,
  `es_coordinacion` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_req_resp_req` FOREIGN KEY (`requerimiento_id`) REFERENCES `requerimientos_evin_cabina`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_req_resp_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_req_resp_req` (`requerimiento_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `requerimientos_evidencias` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `requerimiento_id` BIGINT UNSIGNED NOT NULL,
  `archivo` VARCHAR(500) NOT NULL,
  `tipo_archivo` VARCHAR(60) DEFAULT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_req_evid_req` FOREIGN KEY (`requerimiento_id`) REFERENCES `requerimientos_evin_cabina`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_req_evid_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_req_evid_req` (`requerimiento_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 10 — Sala Situacional
-- ============================================================

CREATE TABLE IF NOT EXISTS `sala_reportes_guardados` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `nombre` VARCHAR(200) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `filtros_json` JSON DEFAULT NULL,
  `tipo_reporte` VARCHAR(60) NOT NULL DEFAULT 'dashboard',
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_sala_reporte_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  INDEX `idx_sala_reporte_usuario` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 11 — Integraciones Sistemas y PMO
-- ============================================================

CREATE TABLE IF NOT EXISTS `integraciones_sistemas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `codigo` VARCHAR(60) NOT NULL UNIQUE,
  `nombre` VARCHAR(200) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `tipo` VARCHAR(60) NOT NULL DEFAULT 'externo',
  `estado` ENUM('activo','inactivo','mantenimiento') NOT NULL DEFAULT 'activo',
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `integraciones_configuracion` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sistema_id` BIGINT UNSIGNED NOT NULL,
  `base_url` VARCHAR(500) DEFAULT NULL,
  `api_key_encrypted` TEXT DEFAULT NULL,
  `token_encrypted` TEXT DEFAULT NULL,
  `headers_json` JSON DEFAULT NULL,
  `parametros_json` JSON DEFAULT NULL,
  `ambiente` VARCHAR(60) NOT NULL DEFAULT 'desarrollo',
  `activo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_integ_config_sistema` FOREIGN KEY (`sistema_id`) REFERENCES `integraciones_sistemas`(`id`) ON DELETE CASCADE,
  INDEX `idx_integ_config_sistema` (`sistema_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `integraciones_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sistema_id` BIGINT UNSIGNED NOT NULL,
  `direccion` ENUM('entrada','salida') NOT NULL DEFAULT 'salida',
  `endpoint` VARCHAR(500) DEFAULT NULL,
  `metodo` VARCHAR(20) DEFAULT NULL,
  `estado` ENUM('exitoso','error','pendiente','cancelado') NOT NULL DEFAULT 'pendiente',
  `codigo_respuesta` INT DEFAULT NULL,
  `mensaje` TEXT DEFAULT NULL,
  `payload_resumen` VARCHAR(500) DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_integ_log_sistema` FOREIGN KEY (`sistema_id`) REFERENCES `integraciones_sistemas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_integ_log_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_integ_log_sistema` (`sistema_id`),
  INDEX `idx_integ_log_estado` (`estado`),
  INDEX `idx_integ_log_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `integraciones_cola` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sistema_id` BIGINT UNSIGNED NOT NULL,
  `ficha_id` BIGINT UNSIGNED DEFAULT NULL,
  `macro_id` BIGINT UNSIGNED DEFAULT NULL,
  `tipo_evento` VARCHAR(120) NOT NULL,
  `payload_json` JSON DEFAULT NULL,
  `estado` ENUM('pendiente','procesando','enviado','error','cancelado') NOT NULL DEFAULT 'pendiente',
  `intentos` INT NOT NULL DEFAULT 0,
  `ultimo_error` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_integ_cola_sistema` FOREIGN KEY (`sistema_id`) REFERENCES `integraciones_sistemas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_integ_cola_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_integ_cola_macro` FOREIGN KEY (`macro_id`) REFERENCES `macro_eventos`(`id`) ON DELETE SET NULL,
  INDEX `idx_integ_cola_sistema` (`sistema_id`),
  INDEX `idx_integ_cola_estado` (`estado`),
  INDEX `idx_integ_cola_ficha` (`ficha_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `integraciones_payloads` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sistema_id` BIGINT UNSIGNED NOT NULL,
  `ficha_id` BIGINT UNSIGNED DEFAULT NULL,
  `macro_id` BIGINT UNSIGNED DEFAULT NULL,
  `origen` VARCHAR(60) NOT NULL DEFAULT 'manual',
  `tipo_payload` VARCHAR(120) NOT NULL,
  `payload_json` JSON NOT NULL,
  `estado` ENUM('recibido','procesado','error') NOT NULL DEFAULT 'recibido',
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_integ_payload_sistema` FOREIGN KEY (`sistema_id`) REFERENCES `integraciones_sistemas`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_integ_payload_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_integ_payload_macro` FOREIGN KEY (`macro_id`) REFERENCES `macro_eventos`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_integ_payload_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_integ_payload_sistema` (`sistema_id`),
  INDEX `idx_integ_payload_ficha` (`ficha_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 12 — Comunicaciones
-- ============================================================

CREATE TABLE IF NOT EXISTS `comunicaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ficha_id` BIGINT UNSIGNED DEFAULT NULL,
  `macro_id` BIGINT UNSIGNED DEFAULT NULL,
  `tipo` VARCHAR(60) NOT NULL,
  `asunto` VARCHAR(300) NOT NULL,
  `contenido` LONGTEXT NOT NULL,
  `estado` ENUM('borrador','publicado','cerrado') NOT NULL DEFAULT 'borrador',
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `observacion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_comunicacion_ficha` FOREIGN KEY (`ficha_id`) REFERENCES `fichas`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_comunicacion_macro` FOREIGN KEY (`macro_id`) REFERENCES `macro_eventos`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_comunicacion_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  INDEX `idx_com_ficha` (`ficha_id`),
  INDEX `idx_com_macro` (`macro_id`),
  INDEX `idx_com_tipo` (`tipo`),
  INDEX `idx_com_estado` (`estado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `comunicaciones_plantillas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nombre` VARCHAR(200) NOT NULL,
  `tipo` VARCHAR(60) NOT NULL,
  `contenido` LONGTEXT NOT NULL,
  `activo` TINYINT(1) NOT NULL DEFAULT 1,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_plantilla_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  INDEX `idx_plantilla_tipo` (`tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `comunicaciones_destinatarios` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `comunicacion_id` BIGINT UNSIGNED NOT NULL,
  `nombre` VARCHAR(200) NOT NULL,
  `institucion` VARCHAR(200) DEFAULT NULL,
  `medio` ENUM('whatsapp','correo','institucional','interno') NOT NULL DEFAULT 'interno',
  `observacion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_destinatario_comunicacion` FOREIGN KEY (`comunicacion_id`) REFERENCES `comunicaciones`(`id`) ON DELETE CASCADE,
  INDEX `idx_dest_comunicacion` (`comunicacion_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `comunicaciones_adjuntos` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `comunicacion_id` BIGINT UNSIGNED NOT NULL,
  `archivo` VARCHAR(500) NOT NULL,
  `tipo_archivo` VARCHAR(100) DEFAULT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_adjunto_comunicacion` FOREIGN KEY (`comunicacion_id`) REFERENCES `comunicaciones`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_adjunto_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_adj_comunicacion` (`comunicacion_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 13 — PWA y Operación Offline
-- ============================================================

CREATE TABLE IF NOT EXISTS `pwa_dispositivos` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nombre` VARCHAR(200) DEFAULT NULL,
  `tipo` ENUM('browser','mobile','tablet','desktop') NOT NULL DEFAULT 'browser',
  `uuid` VARCHAR(100) NOT NULL UNIQUE,
  `sistema_operativo` VARCHAR(100) DEFAULT NULL,
  `navegador` VARCHAR(100) DEFAULT NULL,
  `ultimo_acceso` DATETIME(3) DEFAULT NULL,
  `activo` TINYINT(1) NOT NULL DEFAULT 1,
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_pwa_dispositivo_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_pwa_dispositivo_uuid` (`uuid`),
  INDEX `idx_pwa_dispositivo_usuario` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pwa_sync_queue` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `dispositivo_id` BIGINT UNSIGNED DEFAULT NULL,
  `operacion` VARCHAR(20) NOT NULL COMMENT 'CREATE|UPDATE',
  `entidad` VARCHAR(60) NOT NULL,
  `entidad_id` BIGINT UNSIGNED DEFAULT NULL,
  `payload` JSON NOT NULL,
  `estado` ENUM('pendiente','sincronizado','error') NOT NULL DEFAULT 'pendiente',
  `error_detalle` TEXT DEFAULT NULL,
  `creado_localmente` DATETIME(3) DEFAULT NULL,
  `sincronizado_at` DATETIME(3) DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_pwa_sync_dispositivo` FOREIGN KEY (`dispositivo_id`) REFERENCES `pwa_dispositivos`(`id`) ON DELETE SET NULL,
  INDEX `idx_pwa_sync_estado` (`estado`),
  INDEX `idx_pwa_sync_entidad` (`entidad`, `entidad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pwa_sync_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `dispositivo_id` BIGINT UNSIGNED DEFAULT NULL,
  `resultado` ENUM('success','error') NOT NULL DEFAULT 'success',
  `detalle` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_pwa_log_dispositivo` FOREIGN KEY (`dispositivo_id`) REFERENCES `pwa_dispositivos`(`id`) ON DELETE SET NULL,
  INDEX `idx_pwa_log_resultado` (`resultado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 14 — Hardening y Producción
-- ============================================================

CREATE TABLE IF NOT EXISTS `auth_refresh_tokens` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL,
  `expires_at` DATETIME(3) NOT NULL,
  `revoked` TINYINT(1) NOT NULL DEFAULT 0,
  `revoked_at` DATETIME(3) DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_auth_refresh_user` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  INDEX `idx_refresh_token_hash` (`token_hash`),
  INDEX `idx_refresh_user` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `usuarios_password` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED NOT NULL UNIQUE,
  `ultimo_cambio` DATETIME(3) DEFAULT NULL,
  `requiere_cambio` TINYINT(1) NOT NULL DEFAULT 0,
  `intentos` INT NOT NULL DEFAULT 0,
  `bloqueado_hasta` DATETIME(3) DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_password_user` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rate_limits_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ip` VARCHAR(45) NOT NULL,
  `tipo` VARCHAR(20) NOT NULL COMMENT 'login|api|pwa|sync',
  `endpoint` VARCHAR(255) DEFAULT NULL,
  `metodo` VARCHAR(10) DEFAULT NULL,
  `bloqueado` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX `idx_rate_ip` (`ip`),
  INDEX `idx_rate_tipo` (`tipo`),
  INDEX `idx_rate_bloqueado` (`bloqueado`),
  INDEX `idx_rate_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `auditoria_seguridad` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `accion` VARCHAR(60) NOT NULL,
  `entidad` VARCHAR(60) DEFAULT NULL,
  `entidad_id` BIGINT UNSIGNED DEFAULT NULL,
  `detalle` JSON DEFAULT NULL,
  `ip` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX `idx_audseg_accion` (`accion`),
  INDEX `idx_audseg_usuario` (`usuario_id`),
  INDEX `idx_audseg_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 15 — Centro de Monitoreo Operacional
-- ============================================================

CREATE TABLE IF NOT EXISTS `centro_paneles` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `codigo` VARCHAR(30) NOT NULL COMMENT 'muro|dashboard|sala|mapa|monitoreo|panel',
  `config` JSON DEFAULT NULL,
  `activo` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_centro_panel_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uk_centro_panel` (`usuario_id`, `codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `centro_alarmas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `titulo` VARCHAR(200) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `nivel` ENUM('info','warning','critical','emergency') NOT NULL DEFAULT 'info',
  `origen` VARCHAR(60) DEFAULT NULL COMMENT 'fichas|evin|macro|requerimientos|integraciones',
  `origen_id` BIGINT UNSIGNED DEFAULT NULL,
  `leida` TINYINT(1) NOT NULL DEFAULT 0,
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX `idx_centro_alarma_nivel` (`nivel`),
  INDEX `idx_centro_alarma_leida` (`leida`),
  INDEX `idx_centro_alarma_origen` (`origen`, `origen_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `centro_notificaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `titulo` VARCHAR(200) NOT NULL,
  `mensaje` TEXT DEFAULT NULL,
  `tipo` ENUM('info','alerta','exito','error') NOT NULL DEFAULT 'info',
  `leida` TINYINT(1) NOT NULL DEFAULT 0,
  `enlace` VARCHAR(500) DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_centro_notif_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  INDEX `idx_centro_notif_usuario` (`usuario_id`, `leida`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `centro_sala_eventos` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `titulo` VARCHAR(200) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `tipo` ENUM('incidente','actualizacion','decision','alerta','log') NOT NULL DEFAULT 'incidente',
  `nivel` ENUM('bajo','medio','alto','critico') NOT NULL DEFAULT 'medio',
  `origen_entidad` VARCHAR(60) DEFAULT NULL,
  `origen_id` BIGINT UNSIGNED DEFAULT NULL,
  `datos` JSON DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX `idx_centro_sala_tipo` (`tipo`),
  INDEX `idx_centro_sala_nivel` (`nivel`),
  INDEX `idx_centro_sala_fecha` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 16 — BI / Inteligencia de Negocios
-- ============================================================

CREATE TABLE IF NOT EXISTS `bi_reportes_guardados` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `nombre` VARCHAR(200) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `filtros_json` JSON DEFAULT NULL,
  `tipo_reporte` VARCHAR(60) NOT NULL COMMENT 'dashboard|kpis|series|productividad|tiempos|mapa_calor|recursos|ayuda|territorial',
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_bi_reporte_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  INDEX `idx_bi_reporte_usuario` (`usuario_id`),
  INDEX `idx_bi_reporte_tipo` (`tipo_reporte`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bi_kpi_configuracion` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `codigo` VARCHAR(60) NOT NULL UNIQUE,
  `nombre` VARCHAR(200) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `modulo_origen` VARCHAR(60) DEFAULT NULL COMMENT 'fichas|evin|evad|ayuda|macro|cabina|comunicaciones|requerimientos',
  `formula_sql` TEXT DEFAULT NULL COMMENT 'SQL de ejemplo/documentación',
  `activo` TINYINT(1) NOT NULL DEFAULT 1,
  `orden` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  INDEX `idx_bi_kpi_activo` (`activo`),
  INDEX `idx_bi_kpi_modulo` (`modulo_origen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 17 — Administración General
-- ============================================================

CREATE TABLE IF NOT EXISTS `admin_documentos` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `nombre_original` VARCHAR(255) NOT NULL,
  `nombre_archivo` VARCHAR(255) NOT NULL,
  `ruta` VARCHAR(500) NOT NULL,
  `tipo_mime` VARCHAR(120) NOT NULL,
  `tamano` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `extension` VARCHAR(20) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_admin_doc_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  INDEX `idx_admin_doc_tipo` (`tipo_mime`),
  INDEX `idx_admin_doc_extension` (`extension`),
  INDEX `idx_admin_doc_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_documentos_relaciones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `documento_id` BIGINT UNSIGNED NOT NULL,
  `entidad` VARCHAR(60) NOT NULL COMMENT 'ficha|macro|evin|evad|ayuda|requerimiento|comunicacion',
  `entidad_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_admin_doc_rel_doc` FOREIGN KEY (`documento_id`) REFERENCES `admin_documentos`(`id`) ON DELETE CASCADE,
  INDEX `idx_admin_doc_rel_entidad` (`entidad`, `entidad_id`),
  UNIQUE KEY `uk_admin_doc_rel` (`documento_id`, `entidad`, `entidad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_versiones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `version` VARCHAR(30) NOT NULL,
  `titulo` VARCHAR(200) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `fases` VARCHAR(200) DEFAULT NULL COMMENT 'fases incluidas ej: 1,2,3-16',
  `archivo_sql` VARCHAR(500) DEFAULT NULL,
  `aplicado` TINYINT(1) NOT NULL DEFAULT 0,
  `aplicado_at` DATETIME(3) DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX `idx_admin_vers_aplicado` (`aplicado`),
  INDEX `idx_admin_vers_version` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_sistema` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `clave` VARCHAR(120) NOT NULL UNIQUE,
  `valor` TEXT DEFAULT NULL,
  `tipo` ENUM('string','int','bool','json') NOT NULL DEFAULT 'string',
  `descripcion` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nivel` ENUM('info','warning','error','security') NOT NULL DEFAULT 'info',
  `modulo` VARCHAR(60) DEFAULT NULL,
  `accion` VARCHAR(120) DEFAULT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `ip` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `usuario_id` BIGINT UNSIGNED DEFAULT NULL,
  `datos_json` JSON DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX `idx_admin_log_nivel` (`nivel`),
  INDEX `idx_admin_log_modulo` (`modulo`),
  INDEX `idx_admin_log_created` (`created_at`),
  INDEX `idx_admin_log_usuario` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_sesiones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL,
  `ip` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `dispositivo` VARCHAR(200) DEFAULT NULL,
  `ultima_actividad` DATETIME(3) DEFAULT NULL,
  `activa` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT `fk_admin_ses_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  INDEX `idx_admin_ses_usuario` (`usuario_id`, `activa`),
  INDEX `idx_admin_ses_token` (`token_hash`),
  INDEX `idx_admin_ses_actividad` (`ultima_actividad`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;