-- =============================================================================
-- QWHPI — Quebec Weekly Housing Price Index
-- Author  : Simon-Pierre Boucher
-- Contact : contact@spboucher.ai
-- File    : db/schema.sql
-- Purpose : Relational schema serving the API (loaded by engine export.py).
-- =============================================================================

CREATE TABLE IF NOT EXISTS geographies (
    geography_id    TEXT PRIMARY KEY,
    geography_name  TEXT NOT NULL,
    geography_level TEXT NOT NULL CHECK (geography_level IN ('province','region','municipality')),
    parent_id       TEXT REFERENCES geographies(geography_id)
);

CREATE TABLE IF NOT EXISTS series (
    series_id       TEXT PRIMARY KEY,          -- '<geography_id>:<property_type>'
    geography_id    TEXT NOT NULL REFERENCES geographies(geography_id),
    property_type   TEXT NOT NULL CHECK (property_type IN ('all','unifamilial','condo','plex')),
    base_period     TEXT NOT NULL DEFAULT '2021 average = 100',
    UNIQUE (geography_id, property_type)
);

CREATE TABLE IF NOT EXISTS model_runs (
    run_id          SERIAL PRIMARY KEY,
    model_version   TEXT NOT NULL,
    data_vintage    DATE NOT NULL,
    input_row_count BIGINT,
    input_hash      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (model_version, data_vintage)
);

CREATE TABLE IF NOT EXISTS observations (
    series_id            TEXT NOT NULL REFERENCES series(series_id),
    week                 DATE NOT NULL,
    index                DOUBLE PRECISION,
    index_smoothed       DOUBLE PRECISION NOT NULL,
    representative_value DOUBLE PRECISION,
    transactions         INTEGER NOT NULL,
    effective_sample_size DOUBLE PRECISION,
    weekly_pct           DOUBLE PRECISION,
    four_week_pct        DOUBLE PRECISION,
    thirteen_week_pct    DOUBLE PRECISION,
    twenty_six_week_pct  DOUBLE PRECISION,
    yoy_pct              DOUBLE PRECISION,
    lower_95             DOUBLE PRECISION NOT NULL,
    upper_95             DOUBLE PRECISION NOT NULL,
    reliability_grade    CHAR(1) NOT NULL CHECK (reliability_grade IN ('A','B','C','D','E')),
    shrinkage_weight     DOUBLE PRECISION,
    is_partial_week      BOOLEAN NOT NULL DEFAULT FALSE,
    model_version        TEXT NOT NULL,
    data_vintage         DATE NOT NULL,
    PRIMARY KEY (series_id, week)
);
CREATE INDEX IF NOT EXISTS idx_obs_week ON observations (week);

CREATE TABLE IF NOT EXISTS liquidity (
    series_id    TEXT NOT NULL REFERENCES series(series_id),
    week         DATE NOT NULL,
    transactions INTEGER NOT NULL,
    PRIMARY KEY (series_id, week)
);

CREATE TABLE IF NOT EXISTS vintages (
    series_id                     TEXT NOT NULL REFERENCES series(series_id),
    week                          DATE NOT NULL,
    first_release_index           DOUBLE PRECISION,
    first_release_index_smoothed  DOUBLE PRECISION NOT NULL,
    first_release_vintage         DATE NOT NULL,
    PRIMARY KEY (series_id, week)
);

-- Author credit: Simon-Pierre Boucher — contact@spboucher.ai