CREATE TABLE IF NOT EXISTS research_instituciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  nombre VARCHAR(300) NOT NULL,
  sigla VARCHAR(80) NULL,
  pais_codigo CHAR(2) NULL,
  jurisdiccion VARCHAR(220) NULL,
  tipo ENUM('archivo','registro_civil','parroquia','biblioteca','militar','migracion','universidad','asociacion','portal','otro') NOT NULL,
  sitio_web VARCHAR(1200) NULL,
  email VARCHAR(254) NULL,
  telefono VARCHAR(80) NULL,
  direccion TEXT NULL,
  verificado_en DATETIME NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (pais_codigo) REFERENCES sys_paises(codigo),
  UNIQUE KEY uq_institucion_nombre_pais (nombre, pais_codigo),
  INDEX idx_institucion_pais_tipo (pais_codigo, tipo),
  FULLTEXT KEY ft_institucion_nombre (nombre, sigla, jurisdiccion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS research_fuentes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  id_institucion BIGINT UNSIGNED NULL,
  nombre VARCHAR(300) NOT NULL,
  tipo ENUM('portal','catalogo','base_datos','pdf','csv','api','correo','formulario','presencial','investigador_local','otro') NOT NULL,
  nivel_acceso ENUM('publico','registro','suscripcion','restringido','institucional','presencial') NOT NULL DEFAULT 'publico',
  url VARCHAR(1200) NULL,
  terminos_url VARCHAR(1200) NULL,
  robots_url VARCHAR(1200) NULL,
  permite_automatizacion ENUM('si','no','parcial','desconocido') NOT NULL DEFAULT 'desconocido',
  requiere_captcha TINYINT(1) NOT NULL DEFAULT 0,
  requiere_login TINYINT(1) NOT NULL DEFAULT 0,
  cobertura_geografica_json JSON NULL,
  cobertura_periodo_desde SMALLINT NULL,
  cobertura_periodo_hasta SMALLINT NULL,
  tipos_registro_json JSON NULL,
  instrucciones TEXT NULL,
  verificado_en DATETIME NULL,
  estado ENUM('activo','degradado','no_disponible','retirado') NOT NULL DEFAULT 'activo',
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (id_institucion) REFERENCES research_instituciones(id),
  INDEX idx_research_fuente_tipo_estado (tipo, estado),
  FULLTEXT KEY ft_research_fuente_nombre (nombre, instrucciones)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS research_fuente_canales (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_fuente BIGINT UNSIGNED NOT NULL,
  canal ENUM('web','email','formulario','api','telefono','correo_postal','presencial','investigador_local') NOT NULL,
  valor VARCHAR(1200) NULL,
  prioridad TINYINT UNSIGNED NOT NULL DEFAULT 1,
  requisitos TEXT NULL,
  costo_desde DECIMAL(12,2) NULL,
  moneda CHAR(3) NULL,
  plazo_dias_estimado INT UNSIGNED NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (id_fuente) REFERENCES research_fuentes(id) ON DELETE CASCADE,
  INDEX idx_canal_fuente_activo (id_fuente, activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS research_fuente_cambios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_fuente BIGINT UNSIGNED NOT NULL,
  tipo ENUM('url','estructura','filtros','acceso','terminos','disponibilidad','otro') NOT NULL,
  descripcion TEXT NOT NULL,
  severidad ENUM('baja','media','alta','bloqueante') NOT NULL DEFAULT 'media',
  detectado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resuelto_en DATETIME NULL,
  FOREIGN KEY (id_fuente) REFERENCES research_fuentes(id) ON DELETE CASCADE,
  INDEX idx_fuente_cambio_pendiente (id_fuente, resuelto_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_tareas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_pregunta BIGINT UNSIGNED NULL,
  titulo VARCHAR(300) NOT NULL,
  descripcion TEXT NULL,
  tipo VARCHAR(100) NOT NULL,
  prioridad ENUM('baja','media','alta','critica') NOT NULL DEFAULT 'media',
  estado ENUM('pendiente','en_curso','bloqueada','esperando','completada','descartada') NOT NULL DEFAULT 'pendiente',
  responsable_id BIGINT UNSIGNED NULL,
  fecha_limite DATE NULL,
  costo_estimado DECIMAL(12,2) NULL,
  moneda CHAR(3) NULL,
  impacto_estimado DECIMAL(5,2) NULL,
  probabilidad_exito DECIMAL(5,2) NULL,
  resultado TEXT NULL,
  creado_por BIGINT UNSIGNED NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  completada_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_pregunta) REFERENCES gene_preguntas(id),
  FOREIGN KEY (responsable_id) REFERENCES org_usuarios(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_tarea_caso_estado (id_empresa, id_caso, estado),
  INDEX idx_tarea_responsable (id_empresa, responsable_id, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_tarea_dependencias (
  id_tarea BIGINT UNSIGNED NOT NULL,
  depende_de_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id_tarea, depende_de_id),
  FOREIGN KEY (id_tarea) REFERENCES gene_tareas(id) ON DELETE CASCADE,
  FOREIGN KEY (depende_de_id) REFERENCES gene_tareas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_busqueda_planes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_pregunta BIGINT UNSIGNED NOT NULL,
  titulo VARCHAR(300) NOT NULL,
  objetivo TEXT NOT NULL,
  condicion_cierre TEXT NULL,
  estado ENUM('borrador','activo','pausado','completado','archivado') NOT NULL DEFAULT 'borrador',
  cobertura_porcentaje DECIMAL(5,2) NOT NULL DEFAULT 0,
  creado_por BIGINT UNSIGNED NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_pregunta) REFERENCES gene_preguntas(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_plan_caso_estado (id_empresa, id_caso, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_busqueda_plan_fuentes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_plan BIGINT UNSIGNED NOT NULL,
  id_fuente BIGINT UNSIGNED NOT NULL,
  orden INT UNSIGNED NOT NULL DEFAULT 1,
  prioridad ENUM('baja','media','alta','critica') NOT NULL DEFAULT 'media',
  motivo TEXT NOT NULL,
  criterios_sugeridos_json JSON NULL,
  condicion_salto TEXT NULL,
  estado ENUM('pendiente','en_curso','cubierta','omitida','bloqueada') NOT NULL DEFAULT 'pendiente',
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_plan) REFERENCES gene_busqueda_planes(id) ON DELETE CASCADE,
  FOREIGN KEY (id_fuente) REFERENCES research_fuentes(id),
  UNIQUE KEY uq_plan_fuente (id_plan, id_fuente),
  INDEX idx_plan_fuente_orden (id_plan, orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_busqueda_variantes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_plan BIGINT UNSIGNED NOT NULL,
  categoria ENUM('nombre','apellido','fecha','lugar','padre','madre','profesion','unidad','otro') NOT NULL,
  valor VARCHAR(500) NOT NULL,
  normalizado VARCHAR(500) NULL,
  origen ENUM('usuario','documento','ia','receta','error_ocr') NOT NULL,
  prioridad INT NOT NULL DEFAULT 0,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_plan) REFERENCES gene_busqueda_planes(id) ON DELETE CASCADE,
  INDEX idx_variante_plan_categoria (id_plan, categoria, activo),
  FULLTEXT KEY ft_variante_valor (valor, normalizado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_busqueda_ejecuciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_plan BIGINT UNSIGNED NULL,
  id_fuente BIGINT UNSIGNED NOT NULL,
  id_tarea BIGINT UNSIGNED NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  modo ENUM('manual','asistido','api','importacion','correo','formulario','presencial','investigador_local') NOT NULL,
  consulta_exacta TEXT NOT NULL,
  filtros_json JSON NULL,
  combinacion_hash CHAR(64) NOT NULL,
  url_consultada VARCHAR(1800) NULL,
  version_fuente VARCHAR(120) NULL,
  estado ENUM('hallazgo_confirmado','hallazgo_posible','parcial','sin_resultado','falso_positivo','homonimo','bloqueado','restringido','error','pendiente_respuesta','pendiente_pago','pendiente_visita') NOT NULL,
  comentario_resultado TEXT NOT NULL,
  resultados_cantidad INT UNSIGNED NULL,
  tiempo_segundos INT UNSIGNED NULL,
  costo DECIMAL(12,2) NULL,
  moneda CHAR(3) NULL,
  ejecutado_por BIGINT UNSIGNED NOT NULL,
  ejecutado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_plan) REFERENCES gene_busqueda_planes(id),
  FOREIGN KEY (id_fuente) REFERENCES research_fuentes(id),
  FOREIGN KEY (id_tarea) REFERENCES gene_tareas(id),
  FOREIGN KEY (ejecutado_por) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_busqueda_reproducible (id_empresa, id_caso, id_fuente, combinacion_hash),
  INDEX idx_busqueda_caso_estado (id_empresa, id_caso, estado),
  INDEX idx_busqueda_fuente_fecha (id_fuente, ejecutado_en),
  FULLTEXT KEY ft_busqueda_consulta_resultado (consulta_exacta, comentario_resultado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_busqueda_resultados (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_ejecucion BIGINT UNSIGNED NOT NULL,
  tipo ENUM('registro','persona','documento','referencia','respuesta','error','otro') NOT NULL,
  titulo VARCHAR(500) NOT NULL,
  resumen TEXT NULL,
  url VARCHAR(1800) NULL,
  pagina VARCHAR(120) NULL,
  fragmento TEXT NULL,
  clasificacion ENUM('relevante','posible','falso_positivo','homonimo','irrelevante','pendiente') NOT NULL DEFAULT 'pendiente',
  persona_candidata_id BIGINT UNSIGNED NULL,
  metadata_json JSON NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_ejecucion) REFERENCES gene_busqueda_ejecuciones(id) ON DELETE CASCADE,
  FOREIGN KEY (persona_candidata_id) REFERENCES gene_personas(id),
  INDEX idx_resultado_ejecucion_clase (id_ejecucion, clasificacion),
  FULLTEXT KEY ft_resultado_titulo_resumen (titulo, resumen, fragmento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_busqueda_evidencias (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_ejecucion BIGINT UNSIGNED NOT NULL,
  id_resultado BIGINT UNSIGNED NULL,
  tipo ENUM('captura','pdf','csv','html','correo','archivo','nota','otro') NOT NULL,
  id_objeto BIGINT UNSIGNED NULL,
  url VARCHAR(1800) NULL,
  descripcion VARCHAR(500) NOT NULL,
  derechos_nota VARCHAR(500) NULL,
  creado_por BIGINT UNSIGNED NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_ejecucion) REFERENCES gene_busqueda_ejecuciones(id) ON DELETE CASCADE,
  FOREIGN KEY (id_resultado) REFERENCES gene_busqueda_resultados(id) ON DELETE SET NULL,
  FOREIGN KEY (id_objeto) REFERENCES storage_objetos(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_busqueda_evidencia_ejecucion (id_ejecucion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_busqueda_asignaciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_plan_fuente BIGINT UNSIGNED NOT NULL,
  responsable_id BIGINT UNSIGNED NOT NULL,
  estado ENUM('asignada','aceptada','en_progreso','entregada','rechazada','cancelada') NOT NULL DEFAULT 'asignada',
  tiempo_minutos INT UNSIGNED NULL,
  costo DECIMAL(12,2) NULL,
  moneda CHAR(3) NULL,
  instrucciones TEXT NULL,
  resultado TEXT NULL,
  asignada_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  entregada_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_plan_fuente) REFERENCES gene_busqueda_plan_fuentes(id),
  FOREIGN KEY (responsable_id) REFERENCES org_usuarios(id),
  INDEX idx_asignacion_responsable_estado (id_empresa, responsable_id, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;