-- 025 — MODEL SNAPSHOTS (retention store). Phase 2, priority zero.
--
-- Why this exists: as of 2026-07-20 the ONLY store of model history was
-- ledger_entries (640 rows, 6 game days) and it holds no model INPUTS. Every
-- other warehouse table in the schema is empty. That means we could score the
-- grades we emitted but could NOT replay a different model against the same
-- conditions — which is the only question a backtest exists to answer.
--
-- This table captures inputs AND outputs at lock time, immutably, so history
-- starts compounding tonight.
--
-- Append-only: ONE ROW PER GRADED PROP PER SNAPSHOT CYCLE. A re-grade at the
-- next cycle writes a NEW row on purpose — that is what lets us ask whether the
-- 14:00 read or the 22:00 read was sharper.

create table if not exists public.model_snapshots (
  id               bigserial primary key,

  -- provenance — never mix model eras (ledger_entries already has this
  -- contamination with no marker; everything from here is stamped)
  snapshot_id      uuid        not null,
  captured_at      timestamptz not null,
  cycle_hour_utc   smallint,
  model_version    text        not null,
  code_sha         text,

  -- identity (mirrors the ledger natural key so outcomes can be stamped)
  sport            text        not null,
  game_id          text        not null,
  game_date        date        not null,
  player_key       text        not null,
  player_name      text        not null,
  team             text,
  opponent         text,
  stat             text        not null,
  line             numeric     not null,
  side             text        not null,

  -- MARKET — real book numbers captured at a timestamp, never model output
  book             text,
  book_odds        integer,
  over_odds        integer,
  under_odds       integer,
  fair_odds        integer,
  fair_prob        numeric,
  overround        numeric,
  devig_method     text,

  -- MODEL OUTPUT
  grade            text,
  grade_11         text,
  confidence       numeric,
  confidence_basis text,
  p_win            numeric,
  ev_pct           numeric,
  projection       numeric,
  edge_pct         numeric,
  takeable         boolean,
  value            boolean,
  archetype        text,

  -- REFUSALS ARE TRAINING DATA. The ledger drops them entirely, so "was the
  -- gate right to refuse this?" is currently unanswerable. A too-aggressive
  -- gate costs real edge and is invisible without these rows.
  refused          boolean     not null default false,
  refusal_reason   text,

  -- THE COUNTERFACTUAL ENABLER. Schema-free so a new feature never needs a
  -- migration. Nulled after 90 days by the retention sweep; scalars stay forever.
  features         jsonb,

  -- OUTCOME — stamped later by the settle pass, per cycle row
  outcome          text,
  actual_value     numeric,
  settled_at       timestamptz,

  created_at       timestamptz not null default now()
);

-- One row per prop per cycle; a retry of the same cycle must not duplicate.
create unique index if not exists model_snapshots_cycle_prop_uniq
  on public.model_snapshots (snapshot_id, player_key, stat, line, side);

create index if not exists model_snapshots_date_sport_idx
  on public.model_snapshots (game_date, sport);
create index if not exists model_snapshots_player_idx
  on public.model_snapshots (player_key, stat, game_date);
create index if not exists model_snapshots_version_idx
  on public.model_snapshots (model_version, game_date);
-- Settle pass looks up unsettled rows by natural key.
create index if not exists model_snapshots_settle_idx
  on public.model_snapshots (game_date, player_key, stat, line, side)
  where outcome is null;

comment on table public.model_snapshots is
  'Append-only model history: inputs (features) + outputs per graded prop per snapshot cycle. Feeds the backtest harness, calibration, and metric validation. Features nulled after 90 days; scalars kept forever.';
comment on column public.model_snapshots.features is
  'Full feature vector at lock time. THE counterfactual enabler — without it a backtest can only grade our own homework. Nulled after 90 days by the retention sweep.';
comment on column public.model_snapshots.refused is
  'True when the engine refused to grade. Refusals are training data: the only way to detect a gate that is refusing props that would have won.';
comment on column public.model_snapshots.model_version is
  'Which engine produced this row. A backtest that mixes model eras is worthless. ledger_entries lacks this and is permanently contaminated across the 2026-07-19 fix boundary.';

-- RLS: service-role writes only, same posture as ledger_entries. This is
-- internal model telemetry — no client reads it.
alter table public.model_snapshots enable row level security;