-- HIST / backtest store (Wave2C) — HARDENED v0.3.1 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.
--
-- Revision history (preserved files):
--   schema/d1_backtest_v0.2.sql  — prior hardened revision (Codex e0e72f9a response)
--   schema/d1_backtest_v0.3.sql  — preserved v0.3 (sha256 1a545b46…; Codex 59cfcc38 / Claude ITEM B)
--   schema/d1_backtest.sql        — THIS file (v0.3.1 closes Codex 2886717e pin REPLACE + idempotency proof)
--
-- D1-runtime note (Codex 59cfcc38 / 2886717e):
--   SQLite/D1 default recursive_triggers=OFF means DELETE triggers do NOT fire during
--   INSERT OR REPLACE. Do NOT rely on delete-triggers alone. Identity-lock tables +
--   BEFORE INSERT EXISTS…RAISE(ABORT) cover ALL advertised immutable natural-key tables
--   (scores, pins, authorization decisions, runs, input manifests) — not scores alone.
--   schema_apply also sets recursive_triggers=ON locally. Verify D1 session pragmas
--   before production migration.
--
-- Idempotency note (Codex 2886717e):
--   Raw INSERT … ON CONFLICT DO NOTHING on score_events is NOT inherently idempotent:
--   the BEFORE INSERT lock trigger aborts before conflict handling when the lock exists.
--   Idempotency is a WRITER property: srp.backtest.writer.insert_score_event pre-checks
--   payload, and on lock/conflict IntegrityError re-reads and returns
--   idempotent_same_payload ONLY when the stored row matches; changed payloads still
--   fail closed. Never INSERT OR REPLACE on immutable tables. Do NOT weaken locks.

-- ---------------------------------------------------------------------------
-- 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) + verified delegated
-- decision row in backtest_authorization_decisions — never from pin.authorized
-- or a nonempty decision string 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,            -- must exist in backtest_authorization_decisions for auth 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;

-- Identity locks: close INSERT OR REPLACE on pins under recursive_triggers=OFF
-- (Codex 2886717e). EXISTS+RAISE — not UNIQUE-fail (nested INSERT inherits OR REPLACE).
CREATE TABLE IF NOT EXISTS backtest_formula_pin_identity_locks (
  pin_id TEXT PRIMARY KEY,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

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

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

CREATE TRIGGER IF NOT EXISTS trg_backtest_formula_pins_before_insert
BEFORE INSERT ON backtest_formula_pins
BEGIN
  SELECT RAISE(ABORT, 'pin identity already locked; refusing INSERT OR REPLACE bypass')
  WHERE EXISTS (
    SELECT 1 FROM backtest_formula_pin_identity_locks WHERE pin_id = NEW.pin_id
  );
  INSERT INTO backtest_formula_pin_identity_locks (pin_id) VALUES (NEW.pin_id);
END;

-- ---------------------------------------------------------------------------
-- Verified delegated authorization decisions (append-only).
-- Nonempty decision strings are NOT sufficient — resolve_authorization requires
-- a row here whose rule_id/construct_id match the pin/registry.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backtest_authorization_decisions (
  decision_id TEXT PRIMARY KEY,
  rule_id TEXT NOT NULL,
  construct_id TEXT NOT NULL,
  decided_by TEXT NOT NULL,
  decision TEXT NOT NULL
    CHECK (decision IN ('authorize_backtest', 'deny')),
  evidence_json 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_authorization_decisions_no_update
BEFORE UPDATE ON backtest_authorization_decisions
BEGIN
  SELECT RAISE(ABORT, 'backtest_authorization_decisions is append-only');
END;

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

-- Identity locks for authorization decisions (Codex 2886717e)
CREATE TABLE IF NOT EXISTS backtest_authorization_decision_identity_locks (
  decision_id TEXT PRIMARY KEY,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

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

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

CREATE TRIGGER IF NOT EXISTS trg_backtest_authorization_decisions_before_insert
BEFORE INSERT ON backtest_authorization_decisions
BEGIN
  SELECT RAISE(ABORT, 'authorization decision identity already locked; refusing INSERT OR REPLACE bypass')
  WHERE EXISTS (
    SELECT 1 FROM backtest_authorization_decision_identity_locks
    WHERE decision_id = NEW.decision_id
  );
  INSERT INTO backtest_authorization_decision_identity_locks (decision_id)
  VALUES (NEW.decision_id);
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;

-- Identity locks for manifest_id (Codex 2886717e — all advertised immutable tables)
CREATE TABLE IF NOT EXISTS backtest_input_manifest_identity_locks (
  manifest_id TEXT PRIMARY KEY,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

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

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

CREATE TRIGGER IF NOT EXISTS trg_backtest_input_manifests_before_insert
BEFORE INSERT ON backtest_input_manifests
BEGIN
  SELECT RAISE(ABORT, 'manifest identity already locked; refusing INSERT OR REPLACE bypass')
  WHERE EXISTS (
    SELECT 1 FROM backtest_input_manifest_identity_locks
    WHERE manifest_id = NEW.manifest_id
  );
  INSERT INTO backtest_input_manifest_identity_locks (manifest_id)
  VALUES (NEW.manifest_id);
END;

-- ---------------------------------------------------------------------------
-- One auditable experiment / rerun (append-only identity; status via events)
-- git_or_worker_rev is FROZEN after insert (Claude B3). To record a change,
-- append backtest_runtime_provenance_events — do not mutate the run row.
-- ---------------------------------------------------------------------------
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 + git_or_worker_rev immutable; status transitions via 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
  OR OLD.git_or_worker_rev IS NOT NEW.git_or_worker_rev
BEGIN
  SELECT RAISE(ABORT, 'backtest_runs identity/policy/git_or_worker_rev fields are immutable; append runtime_provenance_events');
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;

-- Identity locks for run_id (Codex 2886717e) — prevents REPLACE of run identity/policy
CREATE TABLE IF NOT EXISTS backtest_run_identity_locks (
  run_id TEXT PRIMARY KEY,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

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

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

CREATE TRIGGER IF NOT EXISTS trg_backtest_runs_before_insert
BEFORE INSERT ON backtest_runs
BEGIN
  SELECT RAISE(ABORT, 'run identity already locked; refusing INSERT OR REPLACE bypass')
  WHERE EXISTS (
    SELECT 1 FROM backtest_run_identity_locks WHERE run_id = NEW.run_id
  );
  INSERT INTO backtest_run_identity_locks (run_id) VALUES (NEW.run_id);
END;

-- Append-only runtime provenance changes (when execution rev must be recorded post-create)
CREATE TABLE IF NOT EXISTS backtest_runtime_provenance_events (
  event_id INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id TEXT NOT NULL,
  prior_git_or_worker_rev TEXT,
  new_git_or_worker_rev TEXT NOT NULL,
  reason TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (run_id) REFERENCES backtest_runs(run_id)
);

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

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

-- ---------------------------------------------------------------------------
-- Identity locks: close INSERT OR REPLACE bypass under recursive_triggers=OFF.
-- REPLACE deletes the score row then inserts; locks are NOT deleted, so the
-- subsequent INSERT hits EXISTS…RAISE — silent overwrite is impossible.
-- Idempotent same-payload is NOT inherent to ON CONFLICT DO NOTHING (BEFORE INSERT
-- aborts first). Writer insert_score_event implements same-payload SUCCESS vs
-- changed-payload FAILURE (incl. concurrent races) without weakening locks.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backtest_score_identity_locks (
  run_id TEXT NOT NULL,
  construct_id TEXT NOT NULL,
  score_date TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (run_id, construct_id, score_date),
  FOREIGN KEY (run_id) REFERENCES backtest_runs(run_id)
);

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

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

-- ---------------------------------------------------------------------------
-- Append-only score EVENTS (immutable). Stable identity uniqueness enforced.
-- status_policy_version: required to allow non-null score with UNKNOWN label.
-- Construct must match run→pin→registry construct (Claude B2).
-- ---------------------------------------------------------------------------
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);

-- Bind score construct to run→pin construct; claim identity lock (anti-REPLACE).
-- IMPORTANT: nested INSERT inside a trigger inherits the outer OR REPLACE conflict
-- algorithm, so a UNIQUE fail on locks would itself REPLACE the lock. Use EXISTS+RAISE
-- (not constraint failure) to abort bypass under recursive_triggers=OFF.
CREATE TRIGGER IF NOT EXISTS trg_backtest_score_events_before_insert
BEFORE INSERT ON backtest_score_events
BEGIN
  SELECT RAISE(ABORT, 'score construct_id must match run→pin construct_id')
  WHERE NEW.construct_id IS NOT (
    SELECT p.construct_id
    FROM backtest_runs r
    JOIN backtest_formula_pins p ON p.pin_id = r.pin_id
    WHERE r.run_id = NEW.run_id
  );

  SELECT RAISE(ABORT, 'score identity already locked; refusing INSERT OR REPLACE bypass')
  WHERE EXISTS (
    SELECT 1 FROM backtest_score_identity_locks
    WHERE run_id = NEW.run_id
      AND construct_id = NEW.construct_id
      AND score_date = NEW.score_date
  );

  INSERT INTO backtest_score_identity_locks (run_id, construct_id, score_date)
  VALUES (NEW.run_id, NEW.construct_id, NEW.score_date);
END;

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; use authorized correction path (append-only events)');
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;

-- ---------------------------------------------------------------------------
-- Append-only exposure / variant events (Claude B4). Mutable projection JSON
-- on backtest_run_projections is a derived cache — NOT the research-history ledger.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS backtest_exposure_variant_events (
  event_id INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id TEXT NOT NULL,
  event_kind TEXT NOT NULL
    CHECK (event_kind IN ('exposure', 'variant', 'decision_note')),
  payload_json TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (run_id) REFERENCES backtest_runs(run_id)
);

CREATE INDEX IF NOT EXISTS idx_backtest_exposure_variant_events_run
  ON backtest_exposure_variant_events (run_id, event_id);

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

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

-- ---------------------------------------------------------------------------
-- Mutable PROJECTIONS (summaries / derived views) — clearly separated from events
-- exposure_variant_ledger_json is a mutable cache rebuilt from
-- backtest_exposure_variant_events — never the sole history.
-- ---------------------------------------------------------------------------
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;