CREATE TABLE IF NOT EXISTS comm_plantillas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NULL,
  empresa_scope BIGINT UNSIGNED AS (IFNULL(id_empresa,0)) PERSISTENT,
  codigo VARCHAR(100) NOT NULL,
  nombre VARCHAR(220) NOT NULL,
  canal ENUM('email','carta','formulario','mensaje') NOT NULL,
  idioma CHAR(2) NOT NULL DEFAULT 'es',
  asunto VARCHAR(500) NULL,
  cuerpo LONGTEXT NOT NULL,
  variables_json JSON NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_por BIGINT UNSIGNED 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 (idioma) REFERENCES sys_idiomas(codigo),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  UNIQUE KEY uq_plantilla_empresa_codigo_idioma (empresa_scope, codigo, idioma)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comm_solicitudes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_tarea BIGINT UNSIGNED NULL,
  id_fuente BIGINT UNSIGNED NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  tipo ENUM('correo','carta','formulario','telefono','presencial') NOT NULL,
  destinatario_nombre VARCHAR(300) NULL,
  destinatario_direccion VARCHAR(500) NULL,
  asunto VARCHAR(500) NULL,
  cuerpo LONGTEXT NOT NULL,
  referencia_interna VARCHAR(120) NULL,
  estado ENUM('borrador','programada','enviada','entregada','respondida','sin_respuesta','fallida','cancelada') NOT NULL DEFAULT 'borrador',
  enviada_por BIGINT UNSIGNED NULL,
  programada_en DATETIME NULL,
  enviada_en DATETIME NULL,
  respondida_en DATETIME 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_tarea) REFERENCES gene_tareas(id),
  FOREIGN KEY (id_fuente) REFERENCES research_fuentes(id),
  FOREIGN KEY (enviada_por) REFERENCES org_usuarios(id),
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_solicitud_caso_estado (id_empresa, id_caso, estado),
  INDEX idx_solicitud_programada (estado, programada_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comm_mensajes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_solicitud BIGINT UNSIGNED NOT NULL,
  direccion ENUM('saliente','entrante','nota_interna') NOT NULL,
  message_id VARCHAR(500) NULL,
  remitente VARCHAR(500) NULL,
  destinatarios TEXT NULL,
  asunto VARCHAR(500) NULL,
  cuerpo_texto LONGTEXT NULL,
  cuerpo_html LONGTEXT NULL,
  recibido_en DATETIME NULL,
  enviado_en DATETIME 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_solicitud) REFERENCES comm_solicitudes(id) ON DELETE CASCADE,
  FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_mensaje_solicitud_fecha (id_solicitud, creado_en),
  INDEX idx_mensaje_message_id (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comm_adjuntos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_solicitud BIGINT UNSIGNED NULL,
  id_mensaje BIGINT UNSIGNED NULL,
  id_objeto BIGINT UNSIGNED NOT NULL,
  nombre_visible VARCHAR(500) NOT NULL,
  compartido_con_destinatario TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_solicitud) REFERENCES comm_solicitudes(id) ON DELETE CASCADE,
  FOREIGN KEY (id_mensaje) REFERENCES comm_mensajes(id) ON DELETE CASCADE,
  FOREIGN KEY (id_objeto) REFERENCES storage_objetos(id),
  INDEX idx_adjunto_solicitud (id_solicitud),
  INDEX idx_adjunto_mensaje (id_mensaje)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comm_recordatorios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_solicitud BIGINT UNSIGNED NOT NULL,
  programado_en DATETIME NOT NULL,
  tipo ENUM('seguimiento','vencimiento','pago','respuesta','otro') NOT NULL,
  estado ENUM('pendiente','enviado','cancelado','omitido') NOT NULL DEFAULT 'pendiente',
  nota VARCHAR(500) NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_solicitud) REFERENCES comm_solicitudes(id) ON DELETE CASCADE,
  INDEX idx_recordatorio_pendiente (estado, programado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ia_proveedores (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(80) NOT NULL UNIQUE,
  nombre VARCHAR(160) NOT NULL,
  tipo ENUM('llm','ocr','embedding','translation','antivirus') NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  configuracion_publica_json JSON NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ia_modelos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_proveedor BIGINT UNSIGNED NOT NULL,
  codigo VARCHAR(190) NOT NULL,
  nombre VARCHAR(190) NOT NULL,
  version VARCHAR(120) NULL,
  entrada_costo_millon DECIMAL(12,6) NULL,
  salida_costo_millon DECIMAL(12,6) NULL,
  moneda CHAR(3) NOT NULL DEFAULT 'USD',
  capacidades_json JSON NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_proveedor) REFERENCES ia_proveedores(id),
  UNIQUE KEY uq_modelo_proveedor_codigo (id_proveedor, codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ia_ejecuciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NULL,
  id_documento BIGINT UNSIGNED NULL,
  id_modelo BIGINT UNSIGNED NULL,
  uuid_publico CHAR(36) NOT NULL UNIQUE,
  tarea VARCHAR(120) NOT NULL,
  prompt_version VARCHAR(80) NOT NULL,
  prompt_hash CHAR(64) NOT NULL,
  estado ENUM('pendiente','procesando','completada','fallida','cancelada') NOT NULL DEFAULT 'pendiente',
  tokens_entrada INT UNSIGNED NULL,
  tokens_salida INT UNSIGNED NULL,
  costo DECIMAL(12,6) NULL,
  latencia_ms INT UNSIGNED NULL,
  entrada_redactada_json JSON NULL,
  salida_json JSON NULL,
  error TEXT NULL,
  solicitada_por BIGINT UNSIGNED NOT NULL,
  iniciada_en DATETIME NULL,
  completada_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 SET NULL,
  FOREIGN KEY (id_documento) REFERENCES gene_documentos(id) ON DELETE SET NULL,
  FOREIGN KEY (id_modelo) REFERENCES ia_modelos(id),
  FOREIGN KEY (solicitada_por) REFERENCES org_usuarios(id),
  INDEX idx_ia_empresa_estado (id_empresa, estado, creado_en),
  INDEX idx_ia_documento (id_documento, tarea)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ia_resultados (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_ejecucion BIGINT UNSIGNED NOT NULL,
  tipo ENUM('persona','fecha','lugar','relacion','afirmacion','referencia','texto','otro') NOT NULL,
  valor_json JSON NOT NULL,
  pagina INT UNSIGNED NULL,
  fragmento TEXT NULL,
  confianza DECIMAL(5,2) NULL,
  estado_revision ENUM('propuesto','aceptado','corregido','rechazado') NOT NULL DEFAULT 'propuesto',
  revisado_por BIGINT UNSIGNED NULL,
  revisado_en DATETIME NULL,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_ejecucion) REFERENCES ia_ejecuciones(id) ON DELETE CASCADE,
  FOREIGN KEY (revisado_por) REFERENCES org_usuarios(id),
  INDEX idx_ia_resultado_revision (id_empresa, estado_revision)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS queue_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cola VARCHAR(100) NOT NULL DEFAULT 'default',
  tipo VARCHAR(160) NOT NULL,
  payload_json JSON NOT NULL,
  prioridad INT NOT NULL DEFAULT 0,
  intentos INT UNSIGNED NOT NULL DEFAULT 0,
  max_intentos INT UNSIGNED NOT NULL DEFAULT 3,
  disponible_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reservado_en DATETIME NULL,
  reservado_por VARCHAR(190) NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_queue_disponible (cola, reservado_en, disponible_en, prioridad)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS queue_failed_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  job_id BIGINT UNSIGNED NULL,
  cola VARCHAR(100) NOT NULL,
  tipo VARCHAR(160) NOT NULL,
  payload_json JSON NOT NULL,
  error LONGTEXT NOT NULL,
  fallido_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_failed_fecha (fallido_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_usuario BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(190) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  permisos_json JSON NOT NULL,
  ultima_actividad_en DATETIME NULL,
  vence_en DATETIME NULL,
  revocado_en DATETIME NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_usuario) REFERENCES org_usuarios(id),
  INDEX idx_api_token_empresa (id_empresa, revocado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_webhooks_salientes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(190) NOT NULL,
  url VARCHAR(1200) NOT NULL,
  secreto_cifrado TEXT NOT NULL,
  eventos_json JSON NOT NULL,
  activo 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 (creado_por) REFERENCES org_usuarios(id),
  INDEX idx_webhook_empresa_activo (id_empresa, activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_webhook_entregas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_webhook BIGINT UNSIGNED NOT NULL,
  evento VARCHAR(160) NOT NULL,
  payload_json JSON NOT NULL,
  intento INT UNSIGNED NOT NULL DEFAULT 1,
  http_status SMALLINT UNSIGNED NULL,
  respuesta TEXT NULL,
  estado ENUM('pendiente','enviado','fallido','descartado') NOT NULL DEFAULT 'pendiente',
  siguiente_intento_en DATETIME NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  enviado_en DATETIME NULL,
  FOREIGN KEY (id_webhook) REFERENCES api_webhooks_salientes(id) ON DELETE CASCADE,
  INDEX idx_webhook_entrega_pendiente (estado, siguiente_intento_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sys_notificaciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_usuario BIGINT UNSIGNED NOT NULL,
  tipo VARCHAR(120) NOT NULL,
  titulo VARCHAR(300) NOT NULL,
  mensaje TEXT NOT NULL,
  url VARCHAR(1200) NULL,
  leida_en DATETIME NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  FOREIGN KEY (id_usuario) REFERENCES org_usuarios(id),
  INDEX idx_notificacion_usuario (id_usuario, leida_en, creado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sys_preferencias_usuario (
  id_usuario BIGINT UNSIGNED PRIMARY KEY,
  tema ENUM('claro','oscuro','sistema') NOT NULL DEFAULT 'sistema',
  idioma CHAR(2) NOT NULL DEFAULT 'es',
  zona_horaria VARCHAR(80) NOT NULL DEFAULT 'UTC',
  formato_fecha VARCHAR(30) NOT NULL DEFAULT 'DD/MM/YYYY',
  notificaciones_json JSON NULL,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (id_usuario) REFERENCES org_usuarios(id) ON DELETE CASCADE,
  FOREIGN KEY (idioma) REFERENCES sys_idiomas(codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;