-- GeneaCase Sprint 19: mapa geográfico y rutas migratorias.
-- Las ubicaciones y rutas comienzan como propuestas y requieren revisión humana.
-- Nunca se convierten automáticamente en hechos, parentescos o movimientos confirmados.

CREATE TABLE IF NOT EXISTS gene_mapa_ubicaciones (
  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,
  id_persona BIGINT UNSIGNED NULL,
  id_evento BIGINT UNSIGNED NULL,
  referencia_tipo ENUM('manual','evento','documento','evidencia','busqueda','hipotesis','fuente','otro') NOT NULL DEFAULT 'manual',
  referencia_id BIGINT UNSIGNED NULL,
  nombre_actual VARCHAR(220) NOT NULL,
  nombre_historico VARCHAR(220) NULL,
  pais_codigo CHAR(2) NULL,
  latitud DECIMAL(10,7) NOT NULL,
  longitud DECIMAL(10,7) 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,
  certeza ENUM('confirmado','muy_probable','posible','refutado','desconocido','contexto') NOT NULL DEFAULT 'posible',
  estado ENUM('propuesta','revisada','confirmada','refutada','archivada') NOT NULL DEFAULT 'propuesta',
  procedencia TEXT NOT NULL,
  notas TEXT NULL,
  fingerprint CHAR(64) NOT NULL,
  requiere_revision_humana TINYINT(1) NOT NULL DEFAULT 1,
  creado_por BIGINT UNSIGNED NOT NULL,
  revisado_por BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revisado_en DATETIME NULL,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_mapa_ubicacion_uuid (uuid_publico),
  UNIQUE KEY uq_mapa_ubicacion_fingerprint (id_empresa,id_caso,fingerprint),
  KEY idx_mapa_ubicacion_case (id_empresa,id_caso,estado,certeza),
  KEY idx_mapa_ubicacion_person (id_empresa,id_caso,id_persona),
  KEY idx_mapa_ubicacion_event (id_empresa,id_caso,id_evento),
  KEY idx_mapa_ubicacion_coords (latitud,longitud),
  CONSTRAINT fk_mapa_ubicacion_company FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  CONSTRAINT fk_mapa_ubicacion_case FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  CONSTRAINT fk_mapa_ubicacion_person FOREIGN KEY (id_persona) REFERENCES gene_personas(id) ON DELETE SET NULL,
  CONSTRAINT fk_mapa_ubicacion_event FOREIGN KEY (id_evento) REFERENCES gene_eventos(id) ON DELETE SET NULL,
  CONSTRAINT fk_mapa_ubicacion_country FOREIGN KEY (pais_codigo) REFERENCES sys_paises(codigo),
  CONSTRAINT fk_mapa_ubicacion_creator FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  CONSTRAINT fk_mapa_ubicacion_reviewer FOREIGN KEY (revisado_por) REFERENCES org_usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_mapa_rutas (
  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,
  id_persona BIGINT UNSIGNED NULL,
  nombre VARCHAR(260) NOT NULL,
  descripcion TEXT 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,
  certeza ENUM('confirmado','muy_probable','posible','refutado','desconocido','contexto') NOT NULL DEFAULT 'posible',
  estado ENUM('propuesta','revisada','confirmada','refutada','archivada') NOT NULL DEFAULT 'propuesta',
  procedencia TEXT NOT NULL,
  notas_revision TEXT NULL,
  fingerprint CHAR(64) NOT NULL,
  requiere_revision_humana TINYINT(1) NOT NULL DEFAULT 1,
  creado_por BIGINT UNSIGNED NOT NULL,
  revisado_por BIGINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revisado_en DATETIME NULL,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_mapa_ruta_uuid (uuid_publico),
  UNIQUE KEY uq_mapa_ruta_fingerprint (id_empresa,id_caso,fingerprint),
  KEY idx_mapa_ruta_case (id_empresa,id_caso,estado,certeza),
  KEY idx_mapa_ruta_person (id_empresa,id_caso,id_persona),
  CONSTRAINT fk_mapa_ruta_company FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  CONSTRAINT fk_mapa_ruta_case FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  CONSTRAINT fk_mapa_ruta_person FOREIGN KEY (id_persona) REFERENCES gene_personas(id) ON DELETE SET NULL,
  CONSTRAINT fk_mapa_ruta_creator FOREIGN KEY (creado_por) REFERENCES org_usuarios(id),
  CONSTRAINT fk_mapa_ruta_reviewer FOREIGN KEY (revisado_por) REFERENCES org_usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gene_mapa_ruta_puntos (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_empresa BIGINT UNSIGNED NOT NULL,
  id_caso BIGINT UNSIGNED NOT NULL,
  id_ruta BIGINT UNSIGNED NOT NULL,
  id_ubicacion BIGINT UNSIGNED NOT NULL,
  orden SMALLINT UNSIGNED NOT NULL,
  tramo_estado ENUM('confirmado','probable','alternativo','contexto') NOT NULL DEFAULT 'probable',
  medio_transporte VARCHAR(120) NULL,
  nota VARCHAR(800) NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_mapa_ruta_punto_orden (id_ruta,orden),
  KEY idx_mapa_ruta_punto_case (id_empresa,id_caso,id_ruta),
  KEY idx_mapa_ruta_punto_location (id_empresa,id_caso,id_ubicacion),
  CONSTRAINT fk_mapa_ruta_punto_company FOREIGN KEY (id_empresa) REFERENCES org_empresas(id),
  CONSTRAINT fk_mapa_ruta_punto_case FOREIGN KEY (id_caso) REFERENCES gene_casos(id) ON DELETE CASCADE,
  CONSTRAINT fk_mapa_ruta_punto_route FOREIGN KEY (id_ruta) REFERENCES gene_mapa_rutas(id) ON DELETE CASCADE,
  CONSTRAINT fk_mapa_ruta_punto_location FOREIGN KEY (id_ubicacion) REFERENCES gene_mapa_ubicaciones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;