package store

import (
	"context"
	"fmt"
)

// migrations are applied in order. Each entry runs in a single transaction.
// Never edit an applied migration in-place — add a new one. The schema_version
// table records the highest applied index.
//
// Migration ordering matters: v1 is the initial schema, v2 adds runtime_mode
// so the "rerun a past run" feature can faithfully reproduce its parameters.
var migrations = []string{
	// v1 — core tables. Matches the harness plan §6 schema sketch.
	`
CREATE TABLE schema_version (
  version INTEGER PRIMARY KEY,
  applied_at TEXT NOT NULL
);

CREATE TABLE orgs (
  id           TEXT PRIMARY KEY,
  name         TEXT NOT NULL,
  version      INTEGER NOT NULL,
  yaml_path    TEXT NOT NULL,
  validated_at TEXT
);
CREATE UNIQUE INDEX idx_orgs_name_version ON orgs(name, version);

CREATE TABLE sub_orgs (
  id              TEXT PRIMARY KEY,
  org_id          TEXT NOT NULL REFERENCES orgs(id),
  name            TEXT NOT NULL,
  interface_path  TEXT,
  self_doc_path   TEXT
);
CREATE INDEX idx_sub_orgs_org ON sub_orgs(org_id);

CREATE TABLE teams (
  id              TEXT PRIMARY KEY,
  name            TEXT NOT NULL,
  philosophy      TEXT,
  topology        TEXT,           -- 'flat' | 'top-down'
  parent_sub_org  TEXT REFERENCES sub_orgs(id),
  memory_path     TEXT,
  status          TEXT NOT NULL DEFAULT 'idle'
);

CREATE TABLE teams_templates (
  id             TEXT PRIMARY KEY,
  source_team_id TEXT REFERENCES teams(id),
  snapshot_path  TEXT NOT NULL,
  score          REAL,
  created_at     TEXT NOT NULL
);

CREATE TABLE roles (
  id                  TEXT PRIMARY KEY,
  name                TEXT NOT NULL,
  definition_path     TEXT NOT NULL,
  provider_default    TEXT,
  tool_allowlist_json TEXT NOT NULL DEFAULT '[]'
);

CREATE TABLE runs (
  id               TEXT PRIMARY KEY,
  org_id           TEXT,           -- nullable: pre-org-load runs (Phase A)
  org_version      INTEGER,
  role_set_version TEXT,
  root_task_id     TEXT,
  started_at       TEXT NOT NULL,
  ended_at         TEXT,
  user_request     TEXT,
  status           TEXT NOT NULL DEFAULT 'running',
  tokens_total     INTEGER NOT NULL DEFAULT 0,
  cost_usd_total   REAL NOT NULL DEFAULT 0,
  kill_reason      TEXT
);

CREATE TABLE agents (
  id              TEXT PRIMARY KEY,
  role            TEXT,
  team_id         TEXT REFERENCES teams(id),
  sub_org_id      TEXT REFERENCES sub_orgs(id),
  org_id          TEXT REFERENCES orgs(id),
  run_id          TEXT REFERENCES runs(id),
  provider        TEXT,
  status          TEXT NOT NULL DEFAULT 'spawning',
  zone_scope_json TEXT NOT NULL DEFAULT '[]',
  spawned_at      TEXT NOT NULL,
  terminated_at   TEXT,
  parent_agent_id TEXT REFERENCES agents(id),
  heartbeat_at    TEXT,
  tokens_total    INTEGER NOT NULL DEFAULT 0,
  cost_usd_total  REAL NOT NULL DEFAULT 0,
  parse_failures  INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX idx_agents_status ON agents(status);
CREATE INDEX idx_agents_run    ON agents(run_id);

CREATE TABLE zones (
  id              TEXT PRIMARY KEY,
  name            TEXT NOT NULL,
  path_globs_json TEXT NOT NULL,
  owner_agent_id  TEXT REFERENCES agents(id),
  owner_team_id   TEXT REFERENCES teams(id),
  doc_path        TEXT
);

CREATE TABLE prompts (
  id              TEXT PRIMARY KEY,
  run_id          TEXT REFERENCES runs(id),
  source          TEXT NOT NULL,            -- 'user' | 'agent' | 'system'
  text            TEXT NOT NULL,
  classified_kind TEXT,
  classified_at   TEXT
);

CREATE TABLE tasks (
  id              TEXT PRIMARY KEY,
  run_id          TEXT NOT NULL REFERENCES runs(id),
  parent_task_id  TEXT REFERENCES tasks(id),
  owner_agent_id  TEXT REFERENCES agents(id),
  prompt_id       TEXT REFERENCES prompts(id),
  title           TEXT NOT NULL,
  body_path       TEXT,
  state           TEXT NOT NULL,
  deadline        TEXT,
  attempts        INTEGER NOT NULL DEFAULT 0,
  created_at      TEXT NOT NULL,
  updated_at      TEXT NOT NULL
);
CREATE INDEX idx_tasks_state    ON tasks(state);
CREATE INDEX idx_tasks_deadline ON tasks(deadline);
CREATE INDEX idx_tasks_run      ON tasks(run_id);

CREATE TABLE messages (
  id              TEXT PRIMARY KEY,
  run_id          TEXT NOT NULL,
  from_agent      TEXT NOT NULL,
  to_agent        TEXT NOT NULL,
  type            TEXT NOT NULL,
  payload_json    TEXT NOT NULL,
  in_reply_to     TEXT,
  task_id         TEXT,
  status          TEXT NOT NULL DEFAULT 'queued',  -- queued|delivered|acked|failed
  attempts        INTEGER NOT NULL DEFAULT 0,
  ttl_ms          INTEGER NOT NULL DEFAULT 60000,
  priority        INTEGER NOT NULL DEFAULT 0,      -- higher = sooner (interrupts)
  next_visible_at TEXT NOT NULL,
  created_at      TEXT NOT NULL,
  delivered_at    TEXT,
  acked_at        TEXT
);
CREATE INDEX idx_messages_dispatch ON messages(to_agent, status, priority DESC, next_visible_at);
CREATE INDEX idx_messages_run      ON messages(run_id);

CREATE TABLE action_drafts (
  id           TEXT PRIMARY KEY,
  task_id      TEXT REFERENCES tasks(id),
  agent_id     TEXT REFERENCES agents(id),
  kind         TEXT NOT NULL,
  payload_path TEXT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'pending', -- pending|approved|rejected|expired
  signed_by    TEXT,
  signed_at    TEXT,
  created_at   TEXT NOT NULL
);

CREATE TABLE handoffs (
  id          TEXT PRIMARY KEY,
  task_id     TEXT REFERENCES tasks(id),
  from_agent  TEXT,
  to_agent    TEXT,
  reason      TEXT,
  ts          TEXT NOT NULL
);

CREATE TABLE steering (
  id                  TEXT PRIMARY KEY,
  original_prompt_id  TEXT,
  target_task_id      TEXT,
  forwarded_to        TEXT,
  ack_at              TEXT,
  created_at          TEXT NOT NULL
);

CREATE TABLE evaluators (
  id            TEXT PRIMARY KEY,
  kind          TEXT NOT NULL,    -- 'user-rating' | 'llm-judge' | 'heuristic' | 'focused-llm-judge' | 'a-b-blind'
  criteria_path TEXT,
  model         TEXT,
  enabled       INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE evaluations (
  id                TEXT PRIMARY KEY,
  target_kind       TEXT NOT NULL,    -- 'task' | 'run' | 'artifact'
  target_id         TEXT NOT NULL,
  evaluator_id      TEXT NOT NULL REFERENCES evaluators(id),
  scores_json       TEXT NOT NULL,
  rationale_path    TEXT,
  agreed_with_user  INTEGER,           -- nullable until we have a user rating to compare
  created_at        TEXT NOT NULL
);
CREATE INDEX idx_evaluations_target ON evaluations(target_kind, target_id);

CREATE TABLE events (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id          TEXT,
  agent_id        TEXT,
  task_id         TEXT,
  kind            TEXT NOT NULL,
  classification  TEXT,
  payload_json    TEXT NOT NULL DEFAULT '{}',
  ts              TEXT NOT NULL
);
CREATE INDEX idx_events_run  ON events(run_id);
CREATE INDEX idx_events_kind ON events(kind);
CREATE INDEX idx_events_ts   ON events(ts);

CREATE TABLE artifacts (
  id          TEXT PRIMARY KEY,
  task_id     TEXT REFERENCES tasks(id),
  kind        TEXT NOT NULL,
  path        TEXT NOT NULL,
  sha256      TEXT,
  tags_json   TEXT NOT NULL DEFAULT '[]',
  created_at  TEXT NOT NULL
);
CREATE INDEX idx_artifacts_task ON artifacts(task_id);

CREATE TABLE user_notifications (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id      TEXT,
  task_id     TEXT,
  kind        TEXT NOT NULL,
  payload     TEXT NOT NULL,
  delivered   INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT NOT NULL,
  delivered_at TEXT
);
`,
	// v2 — rerun support. Capture runtime_mode + max_wall_secs so re-running
	// a past run faithfully reproduces its parameters from the runs table.
	`
ALTER TABLE runs ADD COLUMN runtime_mode TEXT NOT NULL DEFAULT '';
ALTER TABLE runs ADD COLUMN max_wall_secs INTEGER NOT NULL DEFAULT 0;
ALTER TABLE runs ADD COLUMN max_turns INTEGER NOT NULL DEFAULT 0;
`,
	// v3 — also capture the *resolved* runtime mode (e.g. "auto" → "scripted")
	// so the dashboard can show ground truth alongside the requested mode.
	`
ALTER TABLE runs ADD COLUMN runtime_mode_resolved TEXT NOT NULL DEFAULT '';
`,
	// v4 — schema-level enum enforcement for the status columns the rest of
	// the harness keys analytics off. Implemented as BEFORE INSERT/UPDATE
	// triggers (CHECK constraints would require a full table rebuild on
	// SQLite). Same correctness guarantee: any out-of-set value causes the
	// write to ABORT, which surfaces as a Go error at the call site.
	//
	// Lists must stay in sync with the Go enums:
	//   runs.status      → orchestrator.runStatus enum
	//   tasks.state      → orchestrator.TaskState enum
	//   agents.status    → orchestrator agent lifecycle states
	//   messages.status  → transport.queue states
	//
	// Whenever a new value is added in Go, add a v_N migration here too.
	`
CREATE TRIGGER trg_runs_status_insert
  BEFORE INSERT ON runs
  WHEN NEW.status NOT IN ('running','completed','killed','failed','unrouted','awaiting_user')
  BEGIN SELECT RAISE(ABORT, 'runs.status not in enum'); END;
CREATE TRIGGER trg_runs_status_update
  BEFORE UPDATE OF status ON runs
  WHEN NEW.status NOT IN ('running','completed','killed','failed','unrouted','awaiting_user')
  BEGIN SELECT RAISE(ABORT, 'runs.status not in enum'); END;

CREATE TRIGGER trg_tasks_state_insert
  BEFORE INSERT ON tasks
  WHEN NEW.state NOT IN ('created','assigned','in_progress','awaiting_handoff','awaiting_clarification','awaiting_subtask','awaiting_draft_signoff','awaiting_steering_ack','completed','failed','requeued','escalated','abandoned')
  BEGIN SELECT RAISE(ABORT, 'tasks.state not in enum'); END;
CREATE TRIGGER trg_tasks_state_update
  BEFORE UPDATE OF state ON tasks
  WHEN NEW.state NOT IN ('created','assigned','in_progress','awaiting_handoff','awaiting_clarification','awaiting_subtask','awaiting_draft_signoff','awaiting_steering_ack','completed','failed','requeued','escalated','abandoned')
  BEGIN SELECT RAISE(ABORT, 'tasks.state not in enum'); END;

CREATE TRIGGER trg_agents_status_insert
  BEFORE INSERT ON agents
  WHEN NEW.status NOT IN ('spawning','running','terminated','failed','cleaned_up')
  BEGIN SELECT RAISE(ABORT, 'agents.status not in enum'); END;
CREATE TRIGGER trg_agents_status_update
  BEFORE UPDATE OF status ON agents
  WHEN NEW.status NOT IN ('spawning','running','terminated','failed','cleaned_up')
  BEGIN SELECT RAISE(ABORT, 'agents.status not in enum'); END;

CREATE TRIGGER trg_messages_status_insert
  BEFORE INSERT ON messages
  WHEN NEW.status NOT IN ('queued','delivered','acked','failed')
  BEGIN SELECT RAISE(ABORT, 'messages.status not in enum'); END;
CREATE TRIGGER trg_messages_status_update
  BEFORE UPDATE OF status ON messages
  WHEN NEW.status NOT IN ('queued','delivered','acked','failed')
  BEGIN SELECT RAISE(ABORT, 'messages.status not in enum'); END;
`,
	// v5 — run/root-task invariant. The original sin in the harness was
	// allowing runs.status='completed' with root_task_id=NULL — the system
	// reported success for runs where nothing was dispatched. These triggers
	// make that combination unrepresentable in the DB. They also enforce a
	// synthetic FK on root_task_id (SQLite cannot ALTER TABLE ADD FOREIGN
	// KEY) so a non-null root_task_id must reference an existing tasks.id.
	//
	// Per status:
	//   running         root_task_id may be NULL (pre-dispatch) or set
	//   completed       requires root_task_id set, and the referenced task
	//                   must itself be in state='completed'
	//   failed          requires root_task_id set
	//   unrouted        forbids root_task_id (nothing was dispatched)
	//   killed          either (could be killed before or after dispatch)
	//   awaiting_user   either
	//
	// We use BEFORE INSERT and BEFORE UPDATE (no OF column) so any write
	// that lands the row in a violating state aborts, regardless of which
	// column the SET clause names. This closes the side-door where setting
	// root_task_id=NULL would bypass a "BEFORE UPDATE OF status" trigger.
	`
CREATE TRIGGER trg_runs_root_fk_insert
  BEFORE INSERT ON runs
  WHEN NEW.root_task_id IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM tasks WHERE id = NEW.root_task_id)
  BEGIN SELECT RAISE(ABORT, 'runs.root_task_id references missing task'); END;

CREATE TRIGGER trg_runs_root_fk_update
  BEFORE UPDATE ON runs
  WHEN NEW.root_task_id IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM tasks WHERE id = NEW.root_task_id)
  BEGIN SELECT RAISE(ABORT, 'runs.root_task_id references missing task'); END;

CREATE TRIGGER trg_runs_completed_needs_root_insert
  BEFORE INSERT ON runs
  WHEN NEW.status = 'completed' AND NEW.root_task_id IS NULL
  BEGIN SELECT RAISE(ABORT, 'runs.status=completed requires root_task_id'); END;

CREATE TRIGGER trg_runs_completed_needs_root_update
  BEFORE UPDATE ON runs
  WHEN NEW.status = 'completed' AND NEW.root_task_id IS NULL
  BEGIN SELECT RAISE(ABORT, 'runs.status=completed requires root_task_id'); END;

CREATE TRIGGER trg_runs_completed_needs_root_task_done
  BEFORE UPDATE ON runs
  WHEN NEW.status = 'completed'
   AND NEW.root_task_id IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM tasks WHERE id = NEW.root_task_id AND state = 'completed')
  BEGIN SELECT RAISE(ABORT, 'runs.status=completed requires root task in state=completed'); END;

CREATE TRIGGER trg_runs_failed_needs_root_insert
  BEFORE INSERT ON runs
  WHEN NEW.status = 'failed' AND NEW.root_task_id IS NULL
  BEGIN SELECT RAISE(ABORT, 'runs.status=failed requires root_task_id'); END;

CREATE TRIGGER trg_runs_failed_needs_root_update
  BEFORE UPDATE ON runs
  WHEN NEW.status = 'failed' AND NEW.root_task_id IS NULL
  BEGIN SELECT RAISE(ABORT, 'runs.status=failed requires root_task_id'); END;

CREATE TRIGGER trg_runs_unrouted_forbids_root_insert
  BEFORE INSERT ON runs
  WHEN NEW.status = 'unrouted' AND NEW.root_task_id IS NOT NULL
  BEGIN SELECT RAISE(ABORT, 'runs.status=unrouted forbids root_task_id'); END;

CREATE TRIGGER trg_runs_unrouted_forbids_root_update
  BEFORE UPDATE ON runs
  WHEN NEW.status = 'unrouted' AND NEW.root_task_id IS NOT NULL
  BEGIN SELECT RAISE(ABORT, 'runs.status=unrouted forbids root_task_id'); END;
`,
	// v6 — typed failure taxonomy. kill_reason is a free-form bag of strings
	// that's been used as both "is this run dead?" boolean and "why did it
	// die?" detail. Split into:
	//   failure_category — enum: 'unrouted','stalled','max_turns','max_wall',
	//                            'ctx_canceled','spawn_failed','task_failed',
	//                            'cost_ceiling','manual'
	//   failure_detail   — free-text (preserves the old kill_reason content)
	//
	// Existing rows: we leave kill_reason populated for backwards-compat
	// reads, but new writes set failure_category + failure_detail and
	// optionally still copy detail into kill_reason for a transition window.
	`
ALTER TABLE runs ADD COLUMN failure_category TEXT NOT NULL DEFAULT '';
ALTER TABLE runs ADD COLUMN failure_detail   TEXT NOT NULL DEFAULT '';

CREATE TRIGGER trg_runs_failure_category_insert
  BEFORE INSERT ON runs
  WHEN NEW.failure_category NOT IN ('','unrouted','stalled','max_turns','max_wall','ctx_canceled','spawn_failed','task_failed','cost_ceiling','manual')
  BEGIN SELECT RAISE(ABORT, 'runs.failure_category not in enum'); END;
CREATE TRIGGER trg_runs_failure_category_update
  BEFORE UPDATE OF failure_category ON runs
  WHEN NEW.failure_category NOT IN ('','unrouted','stalled','max_turns','max_wall','ctx_canceled','spawn_failed','task_failed','cost_ceiling','manual')
  BEGIN SELECT RAISE(ABORT, 'runs.failure_category not in enum'); END;

-- Enforce the cross-column invariant: any terminal-failure status MUST
-- have a category set. 'completed' MUST NOT have a category.
CREATE TRIGGER trg_runs_terminal_needs_category
  BEFORE UPDATE ON runs
  WHEN NEW.status IN ('killed','failed','unrouted') AND NEW.failure_category = ''
  BEGIN SELECT RAISE(ABORT, 'terminal-failure status requires failure_category'); END;
CREATE TRIGGER trg_runs_completed_no_category
  BEFORE UPDATE ON runs
  WHEN NEW.status = 'completed' AND NEW.failure_category <> ''
  BEGIN SELECT RAISE(ABORT, 'runs.status=completed forbids failure_category'); END;
`,
	// v7 — backfill the historical lie. Pre-fix data has runs with
	// status='completed' AND zero tasks: those were silent no-ops where the
	// classifier rejected the prompt but the run was reported successful.
	// We relabel them as the new 'unrouted' terminal state so the research
	// data stops claiming success that never happened.
	//
	// For genuinely-completed runs (tasks exist + a parent-less completed
	// task can be identified), we backfill runs.root_task_id so the v5
	// invariant holds for the historical row as well as future writes.
	//
	// Existing 'killed' runs are left as-is — they already had a
	// non-empty kill_reason in pre-fix data, and we don't have enough
	// signal to assign a typed failure_category retroactively. They'll
	// just have failure_category='' (legal for legacy 'killed' since the
	// v6 trigger only enforces it on UPDATE, not INSERT/state-as-is).
	`
-- Backfill #1: legacy completed-with-no-tasks → unrouted.
UPDATE runs
   SET status = 'unrouted',
       failure_category = 'unrouted',
       failure_detail = 'backfilled v7: legacy completed run had zero tasks'
 WHERE status = 'completed'
   AND root_task_id IS NULL
   AND id NOT IN (SELECT run_id FROM tasks);

-- Backfill #2: legacy completed runs that DO have a parent-less completed
-- task — set root_task_id to point at it so the schema invariant holds.
UPDATE runs
   SET root_task_id = (
     SELECT id FROM tasks
      WHERE run_id = runs.id AND parent_task_id IS NULL AND state = 'completed'
      ORDER BY created_at ASC LIMIT 1
   )
 WHERE status = 'completed' AND root_task_id IS NULL;

-- Backfill #3: any 'completed' row still violating the invariant (had
-- tasks but no parent-less completed task) is reclassified as 'killed'
-- with category=task_failed. We use 'killed' rather than 'failed' because
-- the v5 invariant requires 'failed' to have a root_task_id, and we have
-- no provable root for these rows. 'killed' permits NULL root_task_id.
UPDATE runs
   SET status = 'killed',
       failure_category = 'task_failed',
       failure_detail = 'backfilled v7: legacy completed had tasks but no parent-less completed root'
 WHERE status = 'completed' AND root_task_id IS NULL;
`,
	// v8 — hoist ad-hoc tables into versioned migrations (V27). Two tables
	// were being created via CREATE TABLE IF NOT EXISTS inside business
	// logic (orchestrator/orgbuilder.go for proposals, runtime/agent.go for
	// dynamic_spawn_requests). That hides schema from the migration system
	// and means different process startup orders could see different table
	// shapes. The brief's "100% transparency / research data integrity"
	// principle requires all state shapes to be in one auditable place.
	//
	// We CREATE TABLE IF NOT EXISTS here so deployments that already had
	// the ad-hoc tables don't blow up — they're equivalent. The ad-hoc
	// DDL in the business logic remains for a transition window (to avoid
	// a multi-file commit fan-out) but is now redundant.
	`
CREATE TABLE IF NOT EXISTS proposals (
  id                   TEXT PRIMARY KEY,
  baseline_org_id      TEXT,
  baseline_version     INTEGER,
  proposed_path        TEXT,
  rationale            TEXT,
  created_at           TEXT,
  status               TEXT NOT NULL DEFAULT 'open',
  falsifier_rationale  TEXT
);

CREATE TABLE IF NOT EXISTS dynamic_spawn_requests (
  id                  TEXT PRIMARY KEY,
  run_id              TEXT NOT NULL,
  requested_by        TEXT NOT NULL,
  role                TEXT NOT NULL,
  provider            TEXT,
  args_json           TEXT,
  status              TEXT NOT NULL DEFAULT 'pending',
  created_at          TEXT NOT NULL,
  fulfilled_agent_id  TEXT
);
CREATE INDEX IF NOT EXISTS idx_dyn_spawn_status ON dynamic_spawn_requests(status);
CREATE INDEX IF NOT EXISTS idx_dyn_spawn_run    ON dynamic_spawn_requests(run_id);

CREATE TRIGGER trg_dyn_spawn_status_insert
  BEFORE INSERT ON dynamic_spawn_requests
  WHEN NEW.status NOT IN ('pending','fulfilled','rejected','expired')
  BEGIN SELECT RAISE(ABORT, 'dynamic_spawn_requests.status not in enum'); END;
CREATE TRIGGER trg_dyn_spawn_status_update
  BEFORE UPDATE OF status ON dynamic_spawn_requests
  WHEN NEW.status NOT IN ('pending','fulfilled','rejected','expired')
  BEGIN SELECT RAISE(ABORT, 'dynamic_spawn_requests.status not in enum'); END;

-- proposals.status: 'pending' is the initial state set on insert; the
-- falsifier flips it to 'accepted' or 'rejected'. Future states may add
-- 'superseded' for chains of replacement proposals.
CREATE TRIGGER trg_proposals_status_insert
  BEFORE INSERT ON proposals
  WHEN NEW.status NOT IN ('pending','accepted','rejected','superseded')
  BEGIN SELECT RAISE(ABORT, 'proposals.status not in enum'); END;
CREATE TRIGGER trg_proposals_status_update
  BEFORE UPDATE OF status ON proposals
  WHEN NEW.status NOT IN ('pending','accepted','rejected','superseded')
  BEGIN SELECT RAISE(ABORT, 'proposals.status not in enum'); END;
`,
	// v9 — action_drafts.status enum (V105). The action_drafts table was
	// in v1 but its status column never got an enum guard; with v4-v8
	// covering everything else this was the last gap.
	`
CREATE TRIGGER trg_action_drafts_status_insert
  BEFORE INSERT ON action_drafts
  WHEN NEW.status NOT IN ('pending','approved','rejected','expired')
  BEGIN SELECT RAISE(ABORT, 'action_drafts.status not in enum'); END;
CREATE TRIGGER trg_action_drafts_status_update
  BEFORE UPDATE OF status ON action_drafts
  WHEN NEW.status NOT IN ('pending','approved','rejected','expired')
  BEGIN SELECT RAISE(ABORT, 'action_drafts.status not in enum'); END;

-- V106: user_notifications.kind enum (matches the four kinds the master
-- ever writes: completed, clarification, delegate_failed, unrouted).
CREATE TRIGGER trg_user_notifications_kind_insert
  BEFORE INSERT ON user_notifications
  WHEN NEW.kind NOT IN ('completed','clarification','delegate_failed','unrouted')
  BEGIN SELECT RAISE(ABORT, 'user_notifications.kind not in enum'); END;
CREATE TRIGGER trg_user_notifications_kind_update
  BEFORE UPDATE OF kind ON user_notifications
  WHEN NEW.kind NOT IN ('completed','clarification','delegate_failed','unrouted')
  BEGIN SELECT RAISE(ABORT, 'user_notifications.kind not in enum'); END;
`,
	// v10 — classifier observability. The Classifier returns
	// {kind, confidence, rationale}, but until now only `kind` was
	// persisted. The new fields let the dashboard show *why* a prompt was
	// routed (or refused), and let post-hoc analysis distinguish
	// rules-vs-llm performance.
	`
ALTER TABLE prompts ADD COLUMN classified_confidence REAL;
ALTER TABLE prompts ADD COLUMN classified_rationale  TEXT;
ALTER TABLE prompts ADD COLUMN classifier_impl       TEXT;
`,
}

func (s *Store) migrate(ctx context.Context) error {
	// schema_version table: bootstrap by checking sqlite_master.
	row := s.db.QueryRowContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name='schema_version'`)
	var name string
	hasVersionTable := row.Scan(&name) == nil

	current := 0
	if hasVersionTable {
		row := s.db.QueryRowContext(ctx, `SELECT COALESCE(MAX(version),0) FROM schema_version`)
		if err := row.Scan(&current); err != nil {
			return fmt.Errorf("read schema_version: %w", err)
		}
	}

	for i := current; i < len(migrations); i++ {
		v := i + 1
		stmt := migrations[i]
		// V77: re-check schema_version INSIDE the tx so two processes
		// concurrently opening the DB can't both try to apply the same
		// migration. Whichever wins the BEGIN IMMEDIATE writes the row;
		// the loser sees version >= v and skips.
		err := s.TxImmediate(ctx, func(q Querier) error {
			// TxImmediate acquires the writer lock up-front so two
			// processes can't both read version=N and try to apply N+1.
			var inTxCurrent int
			_ = q.QueryRow(`SELECT COALESCE(MAX(version),0) FROM schema_version`).Scan(&inTxCurrent)
			if inTxCurrent >= v {
				return nil // already applied by a concurrent process
			}
			if _, err := q.Exec(stmt); err != nil {
				return fmt.Errorf("migration v%d: %w", v, err)
			}
			if _, err := q.Exec(`INSERT INTO schema_version(version, applied_at) VALUES(?, ?)`, v, FmtTime(Now())); err != nil {
				return fmt.Errorf("migration v%d record: %w", v, err)
			}
			return nil
		})
		if err != nil {
			return err
		}
	}
	return nil
}

// timeFmt is the canonical ISO-8601-UTC text format used in all TEXT timestamp
// columns. Always UTC.
const timeFmt = "2006-01-02T15:04:05.000Z"