-- GeneaCase Sprint 18: cronología inteligente e integración temporal.
-- La detección genera alertas revisables; nunca corrige fechas ni relaciones automáticamente.

CREATE TABLE IF NOT EXISTS gene_temporal_alertas (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  uuid_publico CHAR(36) NOT NULL,
  codigo VARCHAR(80) NOT NULL,
  severidad ENUM('informativa','aviso','critica') NOT NULL DEFAULT 'aviso',
  estado ENUM('abierta','revisada','descartada','resuelta') NOT NULL DEFAULT 'abierta',
  id_evento_a BIGINT UNSIGNED NULL,
  id_evento_b BIGINT UNSIGNED NULL,
  id_persona BIGINT UNSIGNED NULL,
  titulo VARCHAR(220) NOT NULL,
  detalle TEXT NOT NULL,
  recomendacion TEXT NOT NULL,
  fingerprint CHAR(64) NOT NULL,
  metadata_json JSON NULL,
  detectada_por BIGINT UNSIGNED NOT NULL,
  detectada_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  detectada_ultima_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revisada_por BIGINT UNSIGNED NULL,
  revisada_en DATETIME NULL,
  nota_revision TEXT NULL,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_temporal_alert_uuid (uuid_publico),
  UNIQUE KEY uq_temporal_alert_fingerprint (id_empresa,id_caso,fingerprint),
  KEY idx_temporal_alert_case (id_empresa,id_caso,estado,severidad),
  KEY idx_temporal_alert_person (id_empresa,id_caso,id_persona),
  KEY idx_temporal_alert_event_a (id_evento_a),
  KEY idx_temporal_alert_event_b (id_evento_b),
  CONSTRAINT fk_temporal_alert_company FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  CONSTRAINT fk_temporal_alert_case FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  CONSTRAINT fk_temporal_alert_event_a FOREIGN KEY (id_evento_a) REFERENCES gene_eventos(id) ON DELETE SET NULL,
  CONSTRAINT fk_temporal_alert_event_b FOREIGN KEY (id_evento_b) REFERENCES gene_eventos(id) ON DELETE SET NULL,
  CONSTRAINT fk_temporal_alert_person FOREIGN KEY (id_persona) REFERENCES gene_personas(id) ON DELETE SET NULL,
  CONSTRAINT fk_temporal_alert_detected_user FOREIGN KEY (detectada_por) REFERENCES org_usuarios(id),
  CONSTRAINT fk_temporal_alert_reviewed_user FOREIGN KEY (revisada_por) REFERENCES org_usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;