-- HIST / backtest store (Wave2C) — HARDENED before migration
-- Apply AFTER schema/d1_schema.sql with PRAGMA foreign_keys=ON.
-- Research-only scaffolding. Does NOT authorize new construct scores.
-- Append-only events vs mutable projections are explicitly separated.

-- ---------------------------------------------------------------------------
-- Frozen formula pin (immutable once inserted; formula_sha256 cannot mutate)
-- Authorization cache on the pin is DENORMALIZED ONLY — runners MUST resolve
-- authorization from measurement_rules (FROZEN+authorized) + delegated decision,
-- never from pin.authorized / caller claims alone.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backtest_formula_pins (
  pin_id TEXT PRIMARY KEY,
  construct_id TEXT NOT NULL,
  rule_id TEXT NOT NULL,                      -- FK → measurement_rules.rule_id
  concept_model_version TEXT NOT NULL,
  measurement_spec_version TEXT NOT NULL,
  formula_sha256 TEXT NOT NULL
    CHECK (length(formula_sha256) = 64),
  runtime_pin_json TEXT NOT NULL,             -- exact runtime/code pin (worker rev, module path, deps)
  authorized INTEGER NOT NULL DEFAULT 0
    CHECK (authorized IN (0, 1)),            -- denormalized cache; not sole auth source
  authorization_decision_id TEXT,            -- delegated decision record id (required for auth=1 runs)
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (rule_id) REFERENCES measurement_rules(rule_id)
);

CREATE TRIGGER IF NOT EXISTS trg_backtest_formula_pins_no_update
BEFORE UPDATE ON backtest_formula_pins
BEGIN
  SELECT RAISE(ABORT, 'backtest_formula_pins is append-only; create a new pin_id');
END;

CREATE TRIGGER IF NOT EXISTS trg_backtest_formula_pins_no_delete
BEFORE DELETE ON backtest_formula_pins
BEGIN
  SELECT RAISE(ABORT, 'backtest_formula_pins is append-only');
END;

-- ---------------------------------------------------------------------------
-- Input revision / hash manifest (pinned raw observation revisions + artifacts)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backtest_input_manifests (
  manifest_id TEXT PRIMARY KEY,
  pin_id TEXT NOT NULL,
  artifact_kind TEXT NOT NULL,               -- raw_observation_revision | production_report | audit_json | other
  artifact_path TEXT NOT NULL,
  artifact_sha256 TEXT NOT NULL
    CHECK (length(artifact_sha256) = 64),
  raw_observation_ids_json TEXT,             -- optional list of raw_observations.id
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (pin_id) REFERENCES backtest_formula_pins(pin_id),
  UNIQUE (pin_id, artifact_kind, artifact_path, artifact_sha256)
);

CREATE TRIGGER IF NOT EXISTS trg_backtest_input_manifests_no_update
BEFORE UPDATE ON backtest_input_manifests
BEGIN
  SELECT RAISE(ABORT, 'backtest_input_manifests is append-only');
END;

CREATE TRIGGER IF NOT EXISTS trg_backtest_input_manifests_no_delete
BEFORE DELETE ON backtest_input_manifests
BEGIN
  SELECT RAISE(ABORT, 'backtest_input_manifests is append-only');
END;

-- ---------------------------------------------------------------------------
-- One auditable experiment / rerun (append-only identity; status via events)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backtest_runs (
  run_id TEXT PRIMARY KEY,
  pin_id TEXT NOT NULL,
  country TEXT NOT NULL DEFAULT 'US',
  window_start TEXT NOT NULL,
  window_end TEXT NOT NULL,
  era_split_json TEXT,
  holdout_policy TEXT NOT NULL,
  holdout_frozen_before_outcomes INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL,
  git_or_worker_rev TEXT,
  authorization_source TEXT NOT NULL DEFAULT 'registry+delegated_decision',
  started_at TEXT,
  finished_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (pin_id) REFERENCES backtest_formula_pins(pin_id),
  CHECK (window_start <= window_end),
  CHECK (holdout_frozen_before_outcomes IN (0, 1)),
  CHECK (status IN ('planned', 'running', 'ok', 'failed', 'cancelled')),
  CHECK (authorization_source = 'registry+delegated_decision')
);

-- Runs: identity immutable; status transitions go through projection + events.
CREATE TRIGGER IF NOT EXISTS trg_backtest_runs_no_identity_update
BEFORE UPDATE ON backtest_runs
WHEN
  OLD.run_id IS NOT NEW.run_id
  OR OLD.pin_id IS NOT NEW.pin_id
  OR OLD.country IS NOT NEW.country
  OR OLD.window_start IS NOT NEW.window_start
  OR OLD.window_end IS NOT NEW.window_end
  OR OLD.era_split_json IS NOT NEW.era_split_json
  OR OLD.holdout_policy IS NOT NEW.holdout_policy
  OR OLD.holdout_frozen_before_outcomes IS NOT NEW.holdout_frozen_before_outcomes
  OR OLD.authorization_source IS NOT NEW.authorization_source
BEGIN
  SELECT RAISE(ABORT, 'backtest_runs identity/policy fields are immutable');
END;

CREATE TRIGGER IF NOT EXISTS trg_backtest_runs_no_delete
BEFORE DELETE ON backtest_runs
BEGIN
  SELECT RAISE(ABORT, 'backtest_runs is append-only (cancel via status)');
END;

-- ---------------------------------------------------------------------------
-- Append-only score EVENTS (immutable). Stable identity uniqueness enforced.
-- status_policy_version: required to allow non-null score with UNKNOWN label.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backtest_score_events (
  event_id INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id TEXT NOT NULL,
  construct_id TEXT NOT NULL,
  score_date TEXT NOT NULL,
  score REAL,
  score_label TEXT NOT NULL DEFAULT 'UNKNOWN',
  status_policy_version TEXT,
  split_role TEXT NOT NULL,
  input_obs_ids_json TEXT,
  audit_json TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (run_id) REFERENCES backtest_runs(run_id),
  UNIQUE (run_id, construct_id, score_date),
  CHECK (
    split_role IN ('train', 'holdout', 'full')
    OR substr(split_role, 1, 4) = 'era:'
  ),
  CHECK (
    (
      status_policy_version IS NULL
      AND (
        (score IS NULL AND score_label = 'UNKNOWN')
        OR (score IS NOT NULL AND score_label <> 'UNKNOWN')
      )
    )
    OR (status_policy_version IS NOT NULL)
  ),
  CHECK (
    NOT (score IS NOT NULL AND score_label = 'UNKNOWN' AND status_policy_version IS NULL)
  )
);

CREATE INDEX IF NOT EXISTS idx_backtest_score_events_run
  ON backtest_score_events (run_id, construct_id, score_date);

CREATE TRIGGER IF NOT EXISTS trg_backtest_score_events_no_update
BEFORE UPDATE ON backtest_score_events
BEGIN
  SELECT RAISE(ABORT, 'backtest_score_events is append-only');
END;

CREATE TRIGGER IF NOT EXISTS trg_backtest_score_events_no_delete
BEFORE DELETE ON backtest_score_events
BEGIN
  SELECT RAISE(ABORT, 'backtest_score_events is append-only');
END;

-- ---------------------------------------------------------------------------
-- Mutable PROJECTIONS (summaries / derived views) — clearly separated from events
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backtest_run_projections (
  run_id TEXT PRIMARY KEY,
  status TEXT NOT NULL,
  summary_json TEXT,
  metrics_json TEXT,
  exposure_variant_ledger_json TEXT,
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (run_id) REFERENCES backtest_runs(run_id),
  CHECK (status IN ('planned', 'running', 'ok', 'failed', 'cancelled'))
);

-- Alias view name used in earlier memo drafts (read-only projection over events)
CREATE VIEW IF NOT EXISTS backtest_scores AS
  SELECT
    event_id AS id,
    run_id,
    construct_id,
    score_date,
    score,
    score_label,
    split_role,
    input_obs_ids_json,
    audit_json,
    created_at
  FROM backtest_score_events;