-- earth-now.co
-- Author:  Simon-Pierre Boucher
-- Contact: contact@spboucher.ai
-- File:    infra/migrations/001_initial.sql
-- Purpose: Initial TimescaleDB schema — observations (hypertable), counter_models, registry sync, share tokens, ingest audit

CREATE EXTENSION IF NOT EXISTS timescaledb;

-- ---------------------------------------------------------------------------
-- observations — APPEND-ONLY.
-- Normalized ingested data points. Rows are NEVER updated or deleted: a
-- correction from a source is a new row (later ingested_at wins at read time).
-- This is what makes every counter value traceable and re-fittable forever.
-- ---------------------------------------------------------------------------
CREATE TABLE observations (
    id            bigserial,
    metric_id     text        NOT NULL,
    source_id     text        NOT NULL,
    observed_time timestamptz NOT NULL,
    value         double precision NOT NULL,
    ingested_at   timestamptz NOT NULL DEFAULT now(),
    raw_ref       text,
    -- Hypertables require the partitioning column in every unique constraint.
    PRIMARY KEY (id, observed_time)
);

SELECT create_hypertable('observations', 'observed_time');

CREATE INDEX observations_metric_time_idx
    ON observations (metric_id, observed_time DESC);
CREATE INDEX observations_source_time_idx
    ON observations (source_id, observed_time DESC);

-- ---------------------------------------------------------------------------
-- counter_models — versioned, NEVER UPDATEd.
-- Every re-fit inserts a NEW row; `deployed` marks the row currently served.
-- Deploying a model = insert new row with deployed = true (the previous
-- deployed row is superseded by fitted_at ordering, not mutated).
-- ---------------------------------------------------------------------------
CREATE TABLE counter_models (
    id            bigserial PRIMARY KEY,
    metric_id     text        NOT NULL,
    model_version text        NOT NULL,
    model         jsonb       NOT NULL,
    fitted_at     timestamptz NOT NULL DEFAULT now(),
    deployed      boolean     NOT NULL DEFAULT false
);

CREATE INDEX counter_models_metric_fitted_idx
    ON counter_models (metric_id, fitted_at DESC);
CREATE INDEX counter_models_deployed_idx
    ON counter_models (metric_id) WHERE deployed;

-- ---------------------------------------------------------------------------
-- metrics_registry_sync — last synced state of packages/registry per metric.
-- ---------------------------------------------------------------------------
CREATE TABLE metrics_registry_sync (
    metric_id text        PRIMARY KEY,
    yaml_hash text        NOT NULL,
    synced_at timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- share_tokens — revocable tokens for /m/:token pages (per-metric visibility).
-- Revocation = set revoked_at (kept for audit, never hard-deleted).
-- ---------------------------------------------------------------------------
CREATE TABLE share_tokens (
    token      text        PRIMARY KEY,
    metric_ids text[]      NOT NULL,
    revoked_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- ingest_runs — full auditability of every ingestion run (success or failure).
-- ---------------------------------------------------------------------------
CREATE TABLE ingest_runs (
    id          bigserial   PRIMARY KEY,
    source_id   text        NOT NULL,
    started_at  timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz,
    status      text        NOT NULL DEFAULT 'running',
    error       text,
    raw_ref     text,
    checksum    text
);

CREATE INDEX ingest_runs_source_started_idx
    ON ingest_runs (source_id, started_at DESC);