-- ---------------------------------------------------------------------------
-- PPM Atacama - base de precipitacion mensual del desierto de Atacama
-- Esquema para SQLite / Cloudflare D1.
--
-- Un investigador que quiera replicar el articulo necesita tres cosas, y las
-- tres estan aqui:
--   1. la MEDICION con su procedencia y sus reparos
--      (estacion, posicion_estacion, observacion, marca_calidad, accion_limpieza)
--   2. el RESULTADO de la homogeneizacion Y COMO SE OBTUVO
--      (segmento_regimen, corrida_climatol, salto_climatol)
--      -- los valores sin los parametros no son replicables
--   3. lo necesario para REHACER las figuras
--      (metrica_validacion, panel_figura, geometria)
--
-- Se genera con  python scripts/construir_bd.py  y no se edita a mano.
-- ---------------------------------------------------------------------------

PRAGMA foreign_keys = ON;

-- --- catalogos -------------------------------------------------------------

CREATE TABLE institucion (
  id     INTEGER PRIMARY KEY,
  nombre TEXT UNIQUE NOT NULL,          -- DGA, DMC, CEAZA, INIA, SENAMHI...
  pais   TEXT
);

CREATE TABLE fuente (
  id            INTEGER PRIMARY KEY,
  nombre        TEXT UNIQUE NOT NULL,   -- portal SNIA, CR2, catalogo DMC
  -- primaria = quien OPERA el instrumento; redistribuidor = quien lo republica.
  -- La distincion no es burocratica: cuando la base fue corregida contra el
  -- portal de la DGA, CR2 conservo la coordenada vieja, y sin este campo esas
  -- cuatro correcciones logradas se leen como cuatro errores.
  tipo          TEXT NOT NULL CHECK(tipo IN ('primaria','redistribuidor')),
  aporta        TEXT,
  url           TEXT,
  acceso        TEXT,
  consultada_en TEXT
);

-- Una fila por SERIE PUNTUAL. La mayoria son pluviometros, pero climatol tomo
-- ademas 118 pixeles de ERA5-Land como series de referencia, y sin ellos la
-- corrida no se puede repetir: la entrada del regimen A no eran 151 series
-- sino 269. Viven aqui, marcados con es_referencia = 1, y todas las vistas los
-- excluyen. Meterlos aparte habria obligado a duplicar el camino de JOIN;
-- dejarlos fuera habria hecho irreproducible la homogeneizacion.
CREATE TABLE estacion (
  codigo          TEXT PRIMARY KEY,     -- 1000005, BL0420, E5_-0176-00694
  nombre          TEXT NOT NULL,
  institucion_id  INTEGER NOT NULL REFERENCES institucion(id),
  pais            TEXT,
  nombre_climatol TEXT,                 -- sin tildes ni espacios; clave en los .est
  origen          TEXT,
  es_referencia   INTEGER NOT NULL DEFAULT 0,
  nota            TEXT
);
CREATE INDEX ix_estacion_inst ON estacion(institucion_id);

-- La POSICION es un hecho fechado y con fuente, no un atributo de la estacion.
-- Una estacion puede tener varias filas: la vigente en la base, y la que
-- declara cada fuente. Asi la discrepancia se guarda en vez de perderse, y una
-- estacion que se traslada agrega fila en lugar de sobrescribir su pasado.
CREATE TABLE posicion_estacion (
  id                INTEGER PRIMARY KEY,
  estacion          TEXT NOT NULL REFERENCES estacion(codigo),
  lat               REAL NOT NULL CHECK(lat BETWEEN -56 AND -15),
  lon               REAL NOT NULL CHECK(lon BETWEEN -76 AND -66),
  altitud_m         REAL,
  dem_m             REAL,
  fuente_id         INTEGER NOT NULL REFERENCES fuente(id),
  es_vigente        INTEGER NOT NULL DEFAULT 0,
  dist_a_vigente_km REAL,
  UNIQUE (estacion, fuente_id)
);
CREATE INDEX ix_pos_est ON posicion_estacion(estacion);

-- escala: cuantas unidades enteras hay en 1 mm. El valor se guarda como
-- ENTERO (valor = mm x escala) y se reconstruye dividiendo. Es por producto
-- porque los cuatro no tienen la misma resolucion util:
--   observado      100    el pluviometro resuelve 0,1 mm y ninguna serie baja
--                         de 0,01; ademas 2 decimales limpian la basura de
--                         float32 heredada (70.19999694824219 -> 70,20)
--   homogeneizado  100    climatol escribe con ndec=1
--   chirps       10000    su minimo positivo es 3,3e-16 mm, que es ruido de
--                         remuestreo, no lluvia; con 2 decimales 2.152 celdas
--                         positivas caerian a cero, y son justo las del
--                         regimen hiperarido, donde vive el predictor del GWR
--   era5         10000    con 2 decimales el error relativo llega al 33 %
CREATE TABLE producto (
  id          INTEGER PRIMARY KEY,
  codigo      TEXT UNIQUE NOT NULL,     -- observado|homogeneizado|chirps|era5
  nombre      TEXT NOT NULL,
  unidad      TEXT NOT NULL DEFAULT 'mm/mes',
  escala      INTEGER NOT NULL DEFAULT 100 CHECK(escala > 0),
  es_medicion INTEGER NOT NULL          -- 1 solo para observado
);

-- Una version conoce a su padre, asi que la obsolescencia se propaga sola:
-- rehacer PPM_DB3 marca como caducos a todos sus derivados sin mirar fechas de
-- fichero. Se consulta con WITH RECURSIVE.
CREATE TABLE version_dataset (
  id           INTEGER PRIMARY KEY,
  etiqueta     TEXT UNIQUE NOT NULL,    -- PPM_DB3, homog_v4, chirps_v2
  producto_id  INTEGER NOT NULL REFERENCES producto(id),
  padre_id     INTEGER REFERENCES version_dataset(id),
  generada_en  TEXT NOT NULL,
  generada_por TEXT,                    -- el script que la produjo
  descripcion  TEXT,
  es_vigente   INTEGER NOT NULL DEFAULT 0
);

-- --- el hecho central ------------------------------------------------------

-- Formato LARGO. En ancho, agregar una estacion seria un ALTER TABLE.
-- bandera sigue la convencion de climatol: 0 observado, 1 rellenado,
-- 2 corregido. Para tendencias se filtra bandera = 0.
--
-- valor es ENTERO, en decimas o centesimas segun producto.escala. Tres motivos,
-- y el primero es el que importa en este dominio:
--   1. 0,1 mm no existe en binario. Guardado como REAL, comparar "= 0" o
--      "= 0.1" depende de como se escribio el numero. Aqui el cero medido es el
--      65 % de la base y tiene que comparar exacto SIEMPRE, no por suerte.
--   2. SQLite guarda el entero 0 en CERO bytes (tipo serial 8) y los pequenos
--      en 1-3; un REAL ocupa 8 siempre. Con 32.207 ceros medidos eso se nota.
--   3. Suma sin acumular error: totales anuales y climatologias salen exactos
--      y solo se divide al final.
-- Casi nadie deberia leer esta columna: para eso esta la vista v_observacion,
-- que devuelve valor_mm ya reconstruido.
CREATE TABLE observacion (
  estacion    TEXT NOT NULL REFERENCES estacion(codigo),
  mes         TEXT NOT NULL,            -- YYYY-MM, ordenable como texto
  producto_id INTEGER NOT NULL REFERENCES producto(id),
  version_id  INTEGER NOT NULL REFERENCES version_dataset(id),
  valor       INTEGER CHECK(valor IS NULL OR valor >= 0),
  bandera     INTEGER NOT NULL DEFAULT 0 CHECK(bandera IN (0,1,2)),
  PRIMARY KEY (estacion, mes, producto_id, version_id)
) WITHOUT ROWID;
CREATE INDEX ix_obs_mes  ON observacion(mes, producto_id);
CREATE INDEX ix_obs_prod ON observacion(producto_id, version_id);

-- Una marca NO destruye el valor. Rio Pulido arrastro durante dos versiones
-- una nota que decia "se recomienda excluir" y aun asi entro en CHIRPS, en el
-- GWR y en dos corridas de climatol. Con la marca en su propia tabla un JOIN
-- la trae siempre y un WHERE la excluye, pero el dato medido no se borra.
CREATE TABLE marca_calidad (
  id         INTEGER PRIMARY KEY,
  estacion   TEXT NOT NULL REFERENCES estacion(codigo),
  mes        TEXT,                      -- NULL = afecta a la serie entera
  tipo       TEXT NOT NULL CHECK(tipo IN
               ('implausible','excluida','cambio_variable','revisar')),
  motivo     TEXT NOT NULL,
  evidencia  TEXT,
  version_id INTEGER REFERENCES version_dataset(id)
);
CREATE INDEX ix_marca_est ON marca_calidad(estacion, mes);

CREATE TABLE accion_limpieza (
  id                   INTEGER PRIMARY KEY,
  version_id           INTEGER NOT NULL REFERENCES version_dataset(id),
  etapa                TEXT NOT NULL,
  operacion            TEXT NOT NULL,
  detalle              TEXT,
  estaciones_afectadas INTEGER,
  celdas_afectadas     INTEGER
);

-- --- la homogeneizacion: resultado Y procedimiento -------------------------

-- Por que se corre por regimen: una sola corrida sobre todo el dominio deja
-- que el altiplano monzonico sirva de referencia a la costa hiperarida, y esa
-- contaminacion marcaba 44 saltos que no existian.
CREATE TABLE segmento_regimen (
  estacion   TEXT NOT NULL REFERENCES estacion(codigo),
  version_id INTEGER NOT NULL REFERENCES version_dataset(id),
  regimen    TEXT NOT NULL CHECK(regimen IN ('A','B','C','D')),
  f_verano   REAL,                      -- fraccion de la lluvia caida en dic-mar
  mm_ano     REAL,
  n_meses    INTEGER,
  PRIMARY KEY (estacion, version_id)
);

-- Sin los parametros los valores homogeneizados no son replicables: son el
-- resultado de una decision, no de un calculo canonico. parametros guarda el
-- JSON completo tal como se le paso a homogen().
CREATE TABLE corrida_climatol (
  id               INTEGER PRIMARY KEY,
  version_id       INTEGER NOT NULL REFERENCES version_dataset(id),
  regimen          TEXT NOT NULL,
  escala           TEXT NOT NULL CHECK(escala IN ('mensual','anual')),
  anyi             INTEGER NOT NULL,
  anyf             INTEGER NOT NULL,
  n_series         INTEGER,
  n_referencia     INTEGER,
  parametros       TEXT NOT NULL,       -- JSON: std, niqd, rlemin, dz.max...
  inht             TEXT,
  metad            INTEGER,
  version_climatol TEXT,
  script           TEXT,
  ejecutada_en     TEXT,
  UNIQUE (version_id, regimen, escala)
);

CREATE TABLE salto_climatol (
  id              INTEGER PRIMARY KEY,
  corrida_id      INTEGER NOT NULL REFERENCES corrida_climatol(id),
  estacion        TEXT REFERENCES estacion(codigo),
  codigo_climatol TEXT NOT NULL,
  mes             TEXT NOT NULL,
  snht            REAL
);
CREATE INDEX ix_salto_corrida ON salto_climatol(corrida_id);

-- --- lo que hace falta para rehacer las figuras ----------------------------

-- Las cifras de las tarjetas del articulo. Las que se pueden recalcular las
-- recalcula construir_bd.py desde observacion; las de la corrida superada
-- (110 meses borrados, 44 saltos, 3 regimenes) son constantes historicas y se
-- guardan marcadas como tales.
CREATE TABLE metrica_validacion (
  clave         TEXT PRIMARY KEY,
  titulo        TEXT NOT NULL,
  valor_antes   TEXT,
  valor_despues TEXT,
  unidad        TEXT,
  glosa         TEXT,
  favorable     INTEGER NOT NULL DEFAULT 1,
  origen        TEXT NOT NULL           -- calculada | historica
);

CREATE TABLE panel_figura (
  estacion  TEXT PRIMARY KEY REFERENCES estacion(codigo),
  localidad TEXT NOT NULL,
  orden     INTEGER NOT NULL
);

-- Linea de costa y toponimos del mapa: entrada editorial fija, no un dato
-- medido. Vive aqui para que la figura se arme con una sola conexion.
CREATE TABLE geometria (
  id     INTEGER PRIMARY KEY,
  tipo   TEXT NOT NULL CHECK(tipo IN ('costa','localidad')),
  nombre TEXT,
  lat    REAL NOT NULL,
  lon    REAL NOT NULL,
  orden  INTEGER
);

-- --- rasters: en D1 solo el puntero; el fichero vive en R2 -----------------
CREATE TABLE activo_raster (
  id           INTEGER PRIMARY KEY,
  producto_id  INTEGER REFERENCES producto(id),
  mes          TEXT,
  clave_r2     TEXT UNIQUE NOT NULL,
  crs          TEXT,
  resolucion_m REAL,
  bytes        INTEGER
);

-- --- vistas de conveniencia ------------------------------------------------

-- El puente entre el entero guardado y los mm. Es la tabla que se deberia
-- consultar por defecto; observacion.valor casi nunca se lee directamente.
CREATE VIEW v_observacion AS
SELECT o.estacion, o.mes, p.codigo AS producto, v.etiqueta AS version,
       o.valor * 1.0 / p.escala AS valor_mm, o.bandera,
       o.producto_id, o.version_id
FROM observacion o
JOIN producto p        ON p.id = o.producto_id
JOIN version_dataset v ON v.id = o.version_id;

-- La medicion limpia: sin relleno, sin correccion, sin celda marcada.
-- Es la que sostiene cualquier calculo de tendencia.
CREATE VIEW v_medicion_limpia AS
SELECT o.estacion, o.mes, o.valor * 1.0 / p.escala AS valor_mm
FROM observacion o
JOIN producto p        ON p.id = o.producto_id AND p.es_medicion = 1
JOIN version_dataset v ON v.id = o.version_id  AND v.es_vigente  = 1
JOIN estacion e        ON e.codigo = o.estacion AND e.es_referencia = 0
WHERE o.bandera = 0 AND o.valor IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM marca_calidad m
                  WHERE m.estacion = o.estacion
                    AND (m.mes IS NULL OR m.mes = o.mes));

CREATE VIEW v_estacion_vigente AS
SELECT e.codigo, e.nombre, i.nombre AS institucion, e.pais,
       p.lat, p.lon, p.altitud_m, s.regimen
FROM estacion e
JOIN institucion i        ON i.id = e.institucion_id
LEFT JOIN posicion_estacion p ON p.estacion = e.codigo AND p.es_vigente = 1
LEFT JOIN segmento_regimen  s ON s.estacion = e.codigo
WHERE e.es_referencia = 0;
