-- Regime gate schema (plan 011) — a FREE-data daily signal (session-level
-- metalabel) that decides whether the ORB trades AT ALL today, separate from
-- which NAME to trade (strategy/population.py). Applied by
-- research.regime.apply_schema on first run (same idiom as
-- storage/experiments_schema.sql). Idempotent: safe to run repeatedly.

-- One row per labelable session_date: PRE-OPEN/prior-close-lagged features +
-- the session's OWN outcome label. label_* are OUTCOME columns computed from
-- the session's setup fires — NEVER read back as model features (see
-- research.regime.FEATURE_COLS / test_regime_features_no_label_leak).
CREATE TABLE IF NOT EXISTS regime_features (
    session_date           DATE PRIMARY KEY,
    breadth_gap            DOUBLE PRECISION,   -- fraction of session's setups with gap_pct>0
    breadth_rvol           DOUBLE PRECISION,   -- median relative_volume across session's setups
    tape_regime            DOUBLE PRECISION,   -- ordinal: trend_down=-1, chop=0, trend_up=1
    spy_mom5                DOUBLE PRECISION,  -- SPY 5-session momentum as of PRIOR close
    spy_mom20               DOUBLE PRECISION,  -- SPY 20-session momentum as of PRIOR close
    spy_vol20                DOUBLE PRECISION, -- SPY 20-session realized vol as of PRIOR close
    bot_trailing_expr_10d   DOUBLE PRECISION,  -- mean label_expR over the PRIOR 10 labelable sessions
    label_expr              DOUBLE PRECISION,  -- OUTCOME: mean _sim_baseline_r over the session's setup fires
    label_pos               BOOLEAN,           -- OUTCOME: label_expr > 0
    computed_at              TIMESTAMP
);

-- One row per (model, session): the walk-forward gate decision consumed by
-- research.experiments.replay's iv_regime_gate.
CREATE TABLE IF NOT EXISTS regime_scores (
    model_version TEXT NOT NULL,
    session_date  DATE NOT NULL,
    p_profit      DOUBLE PRECISION,
    favorable     BOOLEAN NOT NULL,
    computed_at   TIMESTAMP NOT NULL,
    PRIMARY KEY (model_version, session_date)
);