CREATE TABLE IF NOT EXISTS storage_objetos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  driver ENUM('local','s3','compatible') NOT NULL DEFAULT 'local',
  bucket VARCHAR(190) NULL,
  ruta VARCHAR(700) NOT NULL,
  nombre_original VARCHAR(500) NOT NULL,
  mime_type VARCHAR(190) NOT NULL,
  extension VARCHAR(20) NULL,
  tamano_bytes BIGINT UNSIGNED NOT NULL,
  sha256 CHAR(64) NOT NULL,
  cifrado TINYINT(1) NOT NULL DEFAULT 0,
  escaneo_estado ENUM('pendiente','limpio','infectado','error') NOT NULL DEFAULT 'pendiente',
  creado_por BIGINT UNSIGNED NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  eliminado_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_storage_empresa_hash (id_empresa, sha256),
  INDEX idx_storage_empresa_fecha (id_empresa, creado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_clientes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  id_usuario_portal BIGINT UNSIGNED NULL,
  nombre VARCHAR(190) NOT NULL,
  email VARCHAR(254) NULL,
  telefono VARCHAR(60) NULL,
  tipo ENUM('persona','familia','organizacion') NOT NULL DEFAULT 'persona',
  estado ENUM('prospecto','activo','inactivo','archivado') NOT NULL DEFAULT 'activo',
  notas_privadas 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,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_usuario_portal) REFERENCES org_usuarios(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_cliente_empresa_estado (id_empresa, estado),
  INDEX idx_cliente_empresa_email (id_empresa, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_casos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  id_cliente BIGINT UNSIGNED NULL,
  codigo VARCHAR(50) NOT NULL,
  titulo VARCHAR(220) NOT NULL,
  objetivo TEXT NOT NULL,
  alcance TEXT NULL,
  exclusiones TEXT NULL,
  finalidad ENUM('family','professional','citizenship','legal','academic','other') NOT NULL DEFAULT 'family',
  estado ENUM('borrador','activo','esperando','revision','concluido','archivado') NOT NULL DEFAULT 'borrador',
  privacidad ENUM('privado','empresa','invitados') NOT NULL DEFAULT 'privado',
  periodo_desde SMALLINT NULL,
  periodo_hasta SMALLINT NULL,
  paises_json JSON NULL,
  idioma CHAR(2) NOT NULL DEFAULT 'es',
  responsable_id BIGINT UNSIGNED NOT 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,
  archivado_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_cliente) REFERENCES gene_clientes(id),
  FOREIGN KEY (idioma) REFERENCES sys_idiomas(codigo),
  FOREIGN KEY (responsable_id) REFERENCES org_usuarios(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_caso_empresa_codigo (id_empresa, codigo),
  INDEX idx_caso_empresa_estado (id_empresa, estado),
  INDEX idx_caso_cliente (id_empresa, id_cliente),
  INDEX idx_caso_responsable (id_empresa, responsable_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_caso_miembros (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_usuario BIGINT UNSIGNED NOT NULL,
  rol_caso ENUM('responsable','investigador','revisor','colaborador','cliente','consulta') NOT NULL,
  puede_ver_notas_internas TINYINT(1) NOT NULL DEFAULT 0,
  estado ENUM('activo','revocado') NOT NULL DEFAULT 'activo',
  vence_en DATETIME NULL,
  creado_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_usuario) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_caso_usuario (id_caso, id_usuario),
  INDEX idx_caso_miembro_empresa (id_empresa, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_preguntas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  pregunta TEXT NOT NULL,
  criterio_exito TEXT NULL,
  prioridad ENUM('baja','media','alta','critica') NOT NULL DEFAULT 'media',
  estado ENUM('abierta','en_progreso','parcial','resuelta','sin_resultado','descartada') NOT NULL DEFAULT 'abierta',
  respuesta_resumen TEXT NULL,
  creada_por BIGINT UNSIGNED NOT NULL,
  resuelta_por BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resuelta_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (creada_por) REFERENCES org_usuarios(id),
  FOREIGN KEY (resuelta_por) REFERENCES org_usuarios(id),
  INDEX idx_pregunta_caso_estado (id_empresa, id_caso, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS geo_lugares (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  padre_id BIGINT UNSIGNED NULL,
  tipo ENUM('mundo','pais','region','provincia','municipio','distrito','parroquia','localidad','direccion','otro') NOT NULL,
  nombre_actual VARCHAR(220) NOT NULL,
  pais_codigo CHAR(2) NULL,
  latitud DECIMAL(10,7) NULL,
  longitud DECIMAL(10,7) NULL,
  metadata_json JSON NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (padre_id) REFERENCES geo_lugares(id),
  FOREIGN KEY (pais_codigo) REFERENCES sys_paises(codigo),
  INDEX idx_lugar_padre (padre_id),
  INDEX idx_lugar_nombre (nombre_actual),
  INDEX idx_lugar_pais_tipo (pais_codigo, tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS geo_lugar_nombres (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_lugar BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(220) NOT NULL,
  idioma CHAR(2) NULL,
  desde_ano SMALLINT NULL,
  hasta_ano SMALLINT NULL,
  tipo ENUM('historico','alternativo','traduccion','error','oficial') NOT NULL DEFAULT 'alternativo',
  FOREIGN KEY (id_lugar) REFERENCES geo_lugares(id) ON DELETE CASCADE,
  FOREIGN KEY (idioma) REFERENCES sys_idiomas(codigo),
  INDEX idx_lugar_nombre_variante (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_personas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  nombre_preferido VARCHAR(220) NOT NULL,
  sexo_documental ENUM('masculino','femenino','intersex','desconocido','no_aplica') NOT NULL DEFAULT 'desconocido',
  estado_vital ENUM('vivo','fallecido','desconocido') NOT NULL DEFAULT 'desconocido',
  privacidad ENUM('normal','restringida','oculta') NOT NULL DEFAULT 'normal',
  es_persona_objetivo TINYINT(1) NOT NULL DEFAULT 0,
  notas 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,
  fusionado_en BIGINT UNSIGNED NULL,
  eliminado_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  FOREIGN KEY (fusionado_en) REFERENCES gene_personas(id),
  INDEX idx_persona_caso_nombre (id_empresa, id_caso, nombre_preferido),
  INDEX idx_persona_objetivo (id_empresa, id_caso, es_persona_objetivo),
  FULLTEXT KEY ft_persona_nombre_notas (nombre_preferido, notas)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_persona_nombres (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_persona BIGINT UNSIGNED NOT NULL,
  nombre_completo VARCHAR(220) NOT NULL,
  nombres VARCHAR(160) NULL,
  apellido_1 VARCHAR(120) NULL,
  apellido_2 VARCHAR(120) NULL,
  tipo ENUM('principal','alternativo','religioso','casado','alias','error','transliteracion','desconocido') NOT NULL DEFAULT 'alternativo',
  idioma CHAR(2) NULL,
  desde_ano SMALLINT NULL,
  hasta_ano SMALLINT NULL,
  id_cita BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_persona) REFERENCES gene_personas(id) ON DELETE CASCADE,
  FOREIGN KEY (idioma) REFERENCES sys_idiomas(codigo),
  INDEX idx_persona_nombre (id_empresa, nombre_completo),
  INDEX idx_persona_apellidos (id_empresa, apellido_1, apellido_2),
  FULLTEXT KEY ft_persona_variantes (nombre_completo, nombres, apellido_1, apellido_2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_eventos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  tipo VARCHAR(100) NOT NULL,
  titulo VARCHAR(220) NOT NULL,
  fecha_tipo ENUM('exacta','aproximada','rango','antes','despues','desconocida') NOT NULL DEFAULT 'desconocida',
  fecha_desde DATE NULL,
  fecha_hasta DATE NULL,
  ano_desde SMALLINT NULL,
  ano_hasta SMALLINT NULL,
  id_lugar BIGINT UNSIGNED NULL,
  certeza ENUM('confirmado','muy_probable','posible','refutado','desconocido','contexto') NOT NULL DEFAULT 'desconocido',
  descripcion 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,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_lugar) REFERENCES geo_lugares(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_evento_caso_fecha (id_empresa, id_caso, ano_desde, fecha_desde),
  INDEX idx_evento_lugar_tipo (id_lugar, tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_evento_participantes (
  id_evento BIGINT UNSIGNED NOT NULL,
  id_persona BIGINT UNSIGNED NOT NULL,
  rol VARCHAR(100) NOT NULL DEFAULT 'participante',
  PRIMARY KEY (id_evento, id_persona, rol),
  FOREIGN KEY (id_evento) REFERENCES gene_eventos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_persona) REFERENCES gene_personas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_relaciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  persona_origen_id BIGINT UNSIGNED NOT NULL,
  persona_destino_id BIGINT UNSIGNED NOT NULL,
  tipo VARCHAR(100) NOT NULL,
  estado ENUM('confirmado','muy_probable','posible','refutado','desconocido') NOT NULL DEFAULT 'desconocido',
  desde_ano SMALLINT NULL,
  hasta_ano SMALLINT NULL,
  notas 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,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (persona_origen_id) REFERENCES gene_personas(id),
  FOREIGN KEY (persona_destino_id) REFERENCES gene_personas(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_relacion_caso (id_caso, persona_origen_id, persona_destino_id, tipo),
  INDEX idx_relacion_persona_origen (id_empresa, persona_origen_id),
  INDEX idx_relacion_persona_destino (id_empresa, persona_destino_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_fuentes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  tipo VARCHAR(120) NOT NULL,
  titulo VARCHAR(500) NOT NULL,
  repositorio VARCHAR(220) NULL,
  jurisdiccion VARCHAR(220) NULL,
  fondo VARCHAR(220) NULL,
  serie VARCHAR(220) NULL,
  caja VARCHAR(120) NULL,
  expediente VARCHAR(120) NULL,
  libro VARCHAR(120) NULL,
  folio VARCHAR(120) NULL,
  url VARCHAR(1200) NULL,
  fecha_documento DATE NULL,
  autenticidad ENUM('original','copia_certificada','copia','transcripcion','indice','desconocida') NOT NULL DEFAULT 'desconocida',
  naturaleza ENUM('primaria','secundaria','derivada','desconocida') NOT NULL DEFAULT 'desconocida',
  informante_tipo ENUM('directo','indirecto','desconocido') NOT NULL DEFAULT 'desconocido',
  legibilidad ENUM('alta','media','baja','ilegible','no_aplica') NOT NULL DEFAULT 'no_aplica',
  metadata_json JSON 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,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_fuente_caso_tipo (id_empresa, id_caso, tipo),
  -- Los prefijos mantienen el índice por signatura dentro del límite InnoDB de 3072 bytes con utf8mb4.
  INDEX idx_fuente_signatura (repositorio(100), fondo(100), serie(100), caja(80), expediente(80)),
  FULLTEXT KEY ft_fuente_titulo_repo (titulo, repositorio, fondo, serie)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_documentos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_fuente BIGINT UNSIGNED NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  titulo VARCHAR(500) NOT NULL,
  tipo VARCHAR(120) NOT NULL,
  emisor VARCHAR(220) NULL,
  fecha_documento DATE NULL,
  idioma CHAR(2) NULL,
  estado ENUM('cargado','procesando','revisado','archivado','error') NOT NULL DEFAULT 'cargado',
  privacidad ENUM('privado','equipo','cliente','compartido') NOT NULL DEFAULT 'privado',
  objeto_original_id BIGINT UNSIGNED NOT NULL,
  metadata_json JSON 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,
  archivado_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_fuente) REFERENCES gene_fuentes(id),
  FOREIGN KEY (idioma) REFERENCES sys_idiomas(codigo),
  FOREIGN KEY (objeto_original_id) REFERENCES storage_objetos(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_documento_caso_estado (id_empresa, id_caso, estado),
  INDEX idx_documento_fuente (id_empresa, id_fuente),
  FULLTEXT KEY ft_documento_titulo_emisor (titulo, emisor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_documento_versiones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_documento BIGINT UNSIGNED NOT NULL,
  numero_version INT UNSIGNED NOT NULL,
  tipo ENUM('original','ocr','traduccion','normalizado','redactado','derivado') NOT NULL,
  id_objeto BIGINT UNSIGNED NULL,
  contenido_texto LONGTEXT NULL,
  idioma CHAR(2) NULL,
  proveedor VARCHAR(120) NULL,
  modelo VARCHAR(190) NULL,
  sha256 CHAR(64) NULL,
  creado_por BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_documento) REFERENCES gene_documentos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_objeto) REFERENCES storage_objetos(id),
  FOREIGN KEY (idioma) REFERENCES sys_idiomas(codigo),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_documento_version (id_documento, numero_version, tipo),
  FULLTEXT KEY ft_documento_version_texto (contenido_texto)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_documento_paginas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_documento BIGINT UNSIGNED NOT NULL,
  numero_pagina INT UNSIGNED NOT NULL,
  texto LONGTEXT NULL,
  ancho_px INT UNSIGNED NULL,
  alto_px INT UNSIGNED NULL,
  id_objeto_imagen BIGINT UNSIGNED NULL,
  metadata_json JSON NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_documento) REFERENCES gene_documentos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_objeto_imagen) REFERENCES storage_objetos(id),
  UNIQUE KEY uq_documento_pagina (id_documento, numero_pagina),
  FULLTEXT KEY ft_pagina_texto (texto)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_citas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_fuente BIGINT UNSIGNED NOT NULL,
  id_documento BIGINT UNSIGNED NULL,
  id_pagina BIGINT UNSIGNED NULL,
  fragmento TEXT NOT NULL,
  ubicacion VARCHAR(300) NULL,
  coordenadas_json JSON NULL,
  transcripcion_literal TINYINT(1) NOT NULL DEFAULT 1,
  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_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_fuente) REFERENCES gene_fuentes(id),
  FOREIGN KEY (id_documento) REFERENCES gene_documentos(id),
  FOREIGN KEY (id_pagina) REFERENCES gene_documento_paginas(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_cita_caso_fuente (id_empresa, id_caso, id_fuente)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_afirmaciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  sujeto_tipo ENUM('persona','relacion','evento','documento','caso','otro') NOT NULL,
  sujeto_id BIGINT UNSIGNED NOT NULL,
  predicado VARCHAR(140) NOT NULL,
  valor_tipo ENUM('texto','numero','fecha','persona','lugar','booleano','json') NOT NULL,
  valor_texto TEXT NULL,
  valor_numero DECIMAL(20,6) NULL,
  valor_fecha DATE NULL,
  valor_referencia_id BIGINT UNSIGNED NULL,
  valor_json JSON NULL,
  fecha_tipo ENUM('exacta','aproximada','rango','antes','despues','desconocida') NOT NULL DEFAULT 'desconocida',
  estado ENUM('propuesta','aceptada','refutada','reemplazada','archivada') NOT NULL DEFAULT 'propuesta',
  certeza ENUM('confirmado','muy_probable','posible','homonimo_probable','descartado','desconocido') NOT NULL DEFAULT 'desconocido',
  origen ENUM('usuario','ia','importacion','investigador','archivo') NOT NULL,
  id_cita_principal BIGINT UNSIGNED NULL,
  informante VARCHAR(220) NULL,
  razonamiento TEXT NULL,
  creado_por BIGINT UNSIGNED NOT NULL,
  revisado_por BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revisado_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_cita_principal) REFERENCES gene_citas(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  FOREIGN KEY (revisado_por) REFERENCES org_usuarios(id),
  INDEX idx_afirmacion_caso_predicado (id_empresa, id_caso, predicado),
  INDEX idx_afirmacion_sujeto (id_empresa, sujeto_tipo, sujeto_id),
  INDEX idx_afirmacion_estado_certeza (id_empresa, estado, certeza),
  FULLTEXT KEY ft_afirmacion_valor_razonamiento (valor_texto, razonamiento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_evidencias (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_afirmacion BIGINT UNSIGNED NOT NULL,
  id_cita BIGINT UNSIGNED NOT NULL,
  posicion ENUM('a_favor','en_contra','contexto') NOT NULL,
  calidad ENUM('alta','media','baja','indeterminada') NOT NULL DEFAULT 'indeterminada',
  peso_orientativo DECIMAL(5,2) NULL,
  explicacion TEXT NULL,
  creada_por BIGINT UNSIGNED NOT NULL,
  creado_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_afirmacion) REFERENCES gene_afirmaciones(id) ON DELETE CASCADE,
  FOREIGN KEY (id_cita) REFERENCES gene_citas(id),
  FOREIGN KEY (creada_por) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_evidencia_afirmacion_cita_posicion (id_afirmacion, id_cita, posicion),
  INDEX idx_evidencia_caso_posicion (id_empresa, id_caso, posicion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_contradicciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  titulo VARCHAR(300) NOT NULL,
  descripcion TEXT NULL,
  estado ENUM('abierta','en_revision','resuelta','reabierta','archivada') NOT NULL DEFAULT 'abierta',
  resolucion TEXT NULL,
  resuelta_por BIGINT UNSIGNED NULL,
  creada_por BIGINT UNSIGNED NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resuelta_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (resuelta_por) REFERENCES org_usuarios(id),
  FOREIGN KEY (creada_por) REFERENCES org_usuarios(id),
  INDEX idx_contradiccion_caso_estado (id_empresa, id_caso, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_contradiccion_items (
  id_contradiccion BIGINT UNSIGNED NOT NULL,
  id_afirmacion BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id_contradiccion, id_afirmacion),
  FOREIGN KEY (id_contradiccion) REFERENCES gene_contradicciones(id) ON DELETE CASCADE,
  FOREIGN KEY (id_afirmacion) REFERENCES gene_afirmaciones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_hipotesis (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  titulo VARCHAR(300) NOT NULL,
  descripcion TEXT NOT NULL,
  estado ENUM('propuesta','activa','apoyada','debilitada','refutada','convertida_conclusion','archivada') NOT NULL DEFAULT 'propuesta',
  probabilidad_orientativa DECIMAL(5,2) NULL,
  advertencia VARCHAR(500) NULL,
  creada_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 (creada_por) REFERENCES org_usuarios(id),
  INDEX idx_hipotesis_caso_estado (id_empresa, id_caso, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_hipotesis_pruebas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_hipotesis BIGINT UNSIGNED NOT NULL,
  descripcion TEXT NOT NULL,
  tipo ENUM('prediccion','documento_necesario','consulta','experimento','otro') NOT NULL,
  estado ENUM('pendiente','en_progreso','cumplida','no_cumplida','inconclusa') NOT NULL DEFAULT 'pendiente',
  resultado TEXT NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_hipotesis) REFERENCES gene_hipotesis(id) ON DELETE CASCADE,
  INDEX idx_prueba_hipotesis_estado (id_hipotesis, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_conclusiones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  titulo VARCHAR(300) NOT NULL,
  conclusion LONGTEXT NOT NULL,
  alcance TEXT NULL,
  certeza ENUM('confirmado','muy_probable','posible','inconcluso') NOT NULL,
  estado ENUM('borrador','aprobada','reemplazada','reabierta') NOT NULL DEFAULT 'borrador',
  aprobada_por BIGINT UNSIGNED NULL,
  creada_por BIGINT UNSIGNED NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  aprobada_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (aprobada_por) REFERENCES org_usuarios(id),
  FOREIGN KEY (creada_por) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_conclusion_caso_version (id_caso, version),
  INDEX idx_conclusion_estado (id_empresa, id_caso, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_conclusion_evidencias (
  id_conclusion BIGINT UNSIGNED NOT NULL,
  id_evidencia BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id_conclusion, id_evidencia),
  FOREIGN KEY (id_conclusion) REFERENCES gene_conclusiones(id) ON DELETE CASCADE,
  FOREIGN KEY (id_evidencia) REFERENCES gene_evidencias(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_candidatos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  persona_objetivo_id BIGINT UNSIGNED NOT NULL,
  persona_candidata_id BIGINT UNSIGNED NOT NULL,
  clasificacion ENUM('confirmado','muy_probable','posible','homonimo_probable','descartado') NOT NULL DEFAULT 'posible',
  puntuacion_orientativa DECIMAL(5,2) NULL,
  explicacion TEXT NULL,
  motivo_descarte 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,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  FOREIGN KEY (persona_objetivo_id) REFERENCES gene_personas(id),
  FOREIGN KEY (persona_candidata_id) REFERENCES gene_personas(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_candidato_par (id_caso, persona_objetivo_id, persona_candidata_id),
  INDEX idx_candidato_clasificacion (id_empresa, id_caso, clasificacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_candidato_criterios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_candidato BIGINT UNSIGNED NOT NULL,
  criterio ENUM('nombre','fecha','lugar','padres','domicilio','profesion','unidad','trayectoria','otro') NOT NULL,
  resultado ENUM('coincide','parcial','contradice','desconocido') NOT NULL,
  peso DECIMAL(5,2) NOT NULL DEFAULT 1,
  detalle TEXT NULL,
  id_evidencia BIGINT UNSIGNED NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_candidato) REFERENCES gene_candidatos(id) ON DELETE CASCADE,
  FOREIGN KEY (id_evidencia) REFERENCES gene_evidencias(id),
  UNIQUE KEY uq_candidato_criterio (id_candidato, criterio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;