-- P1: BooControl schema -- read-only fleet cockpit tables.
-- Applied on startup by apps/control/src/db.ts:applySchema().
-- Lives in the same 'boochat' database as BooChat's tables.

-- Host registry: one row per enabled llama-swap instance.
CREATE TABLE IF NOT EXISTS control_hosts (
  provider_id TEXT PRIMARY KEY,
  ssh_host TEXT,
  ssh_user TEXT,
  ssh_key_path TEXT,
  config_path TEXT,
  restart_cmd TEXT,
  os TEXT,
  gpu_label TEXT,
  enabled BOOLEAN NOT NULL DEFAULT true
);

-- P9 verb-mode: per-host SSH command mode. 'shell' = raw commands (default,
-- backward compatible); 'wrapper' = fixed verbs for a forced-command-locked key.
ALTER TABLE control_hosts ADD COLUMN IF NOT EXISTS ssh_mode TEXT NOT NULL DEFAULT 'shell';

-- Seed display metadata; SSH/config columns are NULL until P9.
INSERT INTO control_hosts (provider_id, os, gpu_label)
VALUES
  ('sam-desktop', 'Windows', 'RTX 5090 32GB'),
  ('embedding', 'Linux', 'P104-100 8GB')
ON CONFLICT (provider_id) DO NOTHING;

-- Request log: ingested from llama-swap /api/metrics ring.
CREATE TABLE IF NOT EXISTS control_requests (
  id BIGSERIAL PRIMARY KEY,
  provider_id TEXT NOT NULL,
  swap_entry_id INT NOT NULL,
  ts TIMESTAMPTZ NOT NULL,
  model TEXT,
  req_path TEXT,
  status_code INT,
  duration_ms INT,
  cache_tokens INT,
  input_tokens INT,
  output_tokens INT,
  prompt_tps REAL,
  gen_tps REAL,
  has_capture BOOLEAN NOT NULL DEFAULT false,
  capture JSONB,
  UNIQUE (provider_id, swap_entry_id, ts)
);

-- P4: Per-consumer attribution column. Added via idempotent ALTER so existing
-- DBs pick it up on next restart. See design §7 "Implementation notes" for the
-- llama-swap ActivityLogEntry discrepancy.
ALTER TABLE control_requests ADD COLUMN IF NOT EXISTS source TEXT;

CREATE INDEX IF NOT EXISTS idx_control_requests_provider_ts
  ON control_requests (provider_id, ts DESC);

-- Raw performance samples from llama-swap /api/performance.
CREATE TABLE IF NOT EXISTS control_perf_samples (
  provider_id TEXT NOT NULL,
  ts TIMESTAMPTZ NOT NULL,
  gpu JSONB,
  sys JSONB,
  UNIQUE (provider_id, ts)
);

CREATE INDEX IF NOT EXISTS idx_control_perf_samples_provider_ts
  ON control_perf_samples (provider_id, ts DESC);

-- 5-minute rollup aggregates.
CREATE TABLE IF NOT EXISTS control_perf_rollup_5m (
  provider_id TEXT NOT NULL,
  bucket TIMESTAMPTZ NOT NULL,
  gpu_agg JSONB,
  sys_agg JSONB,
  UNIQUE (provider_id, bucket)
);

-- Model state transitions + gap events.
CREATE TABLE IF NOT EXISTS control_model_events (
  provider_id TEXT NOT NULL,
  model TEXT NOT NULL,
  state TEXT NOT NULL,
  ts TIMESTAMPTZ NOT NULL,
  detail JSONB,
  UNIQUE (provider_id, model, state, ts)
);

CREATE INDEX IF NOT EXISTS idx_control_model_events_provider_ts
  ON control_model_events (provider_id, ts DESC);

-- P3: Bench engine tables -- additive schema change.

-- Suite definitions: grid of prompt_tokens x gen_tokens x concurrency x repetitions.
CREATE TABLE IF NOT EXISTS bench_suites (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  provider_id TEXT NOT NULL,
  model TEXT NOT NULL,
  prompt_tokens INT[] NOT NULL,
  gen_tokens INT[] NOT NULL,
  concurrency INT[] NOT NULL,
  repetitions INT NOT NULL DEFAULT 1,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- Individual bench runs (one per suite execution).
CREATE TABLE IF NOT EXISTS bench_runs (
  id TEXT PRIMARY KEY,
  suite_id TEXT NOT NULL REFERENCES bench_suites(id),
  job_type TEXT NOT NULL DEFAULT 'bench',
  status TEXT NOT NULL DEFAULT 'queued',
  started_at TIMESTAMPTZ,
  finished_at TIMESTAMPTZ,
  total_samples INT NOT NULL DEFAULT 0,
  completed_samples INT NOT NULL DEFAULT 0,
  concurrent_foreign_requests INT NOT NULL DEFAULT 0,
  temperature REAL,
  top_p REAL,
  aggregate JSONB,
  regression_flag TEXT,
  error TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_bench_runs_suite_id
  ON bench_runs (suite_id);

CREATE INDEX IF NOT EXISTS idx_bench_runs_status
  ON bench_runs (status);

-- Raw per-request samples from a bench run.
CREATE TABLE IF NOT EXISTS bench_samples (
  id BIGSERIAL PRIMARY KEY,
  run_id TEXT NOT NULL REFERENCES bench_runs(id),
  prompt_tokens INT NOT NULL,
  gen_tokens INT NOT NULL,
  concurrency INT NOT NULL,
  repetition INT NOT NULL,
  ttft_ms REAL,
  total_ms REAL,
  prompt_tps REAL,
  gen_tps REAL,
  cache_n INT,
  error TEXT
);

CREATE INDEX IF NOT EXISTS idx_bench_samples_run_id
  ON bench_samples (run_id);

-- P3: Baseline aggregates per (provider_id, model).
-- First completed run seeds the baseline; subsequent runs compare against it.
CREATE TABLE IF NOT EXISTS bench_baselines (
  provider_id TEXT NOT NULL,
  model TEXT NOT NULL,
  aggregate JSONB NOT NULL,
  run_id TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  PRIMARY KEY (provider_id, model)
);

-- P5: Quality evals + sandbox tables.

-- Eval suite definitions: kind (chat|code), tasks JSONB, judge_model.
CREATE TABLE IF NOT EXISTS eval_suites (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  kind TEXT NOT NULL,
  version INT NOT NULL DEFAULT 1,
  tasks JSONB NOT NULL,
  judge_model TEXT,
  judge_model_version TEXT,
  metadata JSONB,
  UNIQUE (name, version),
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_eval_suites_kind
  ON eval_suites (kind);

-- Individual eval runs (one per suite execution against a model).
CREATE TABLE IF NOT EXISTS eval_runs (
  id TEXT PRIMARY KEY,
  suite_id TEXT NOT NULL REFERENCES eval_suites(id),
  job_type TEXT NOT NULL DEFAULT 'eval',
  provider_id TEXT NOT NULL,
  model TEXT NOT NULL,
  quant TEXT,
  status TEXT NOT NULL DEFAULT 'queued',
  judge_model TEXT,
  judge_model_version TEXT,
  started_at TIMESTAMPTZ,
  finished_at TIMESTAMPTZ,
  total_tasks INT NOT NULL DEFAULT 0,
  completed_tasks INT NOT NULL DEFAULT 0,
  aggregate JSONB,
  error TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_eval_runs_suite_id
  ON eval_runs (suite_id);

CREATE INDEX IF NOT EXISTS idx_eval_runs_status
  ON eval_runs (status);

CREATE INDEX IF NOT EXISTS idx_eval_runs_provider_model
  ON eval_runs (provider_id, model);

-- Per-task eval results: score, judge rationale, sandbox exit info.
CREATE TABLE IF NOT EXISTS eval_results (
  id BIGSERIAL PRIMARY KEY,
  run_id TEXT NOT NULL REFERENCES eval_runs(id),
  task_id TEXT NOT NULL,
  task_index INT NOT NULL,
  score REAL,
  max_score REAL,
  rationale TEXT,
  sandbox_exit_code INT,
  sandbox_stderr TEXT,
  sandbox_stdout TEXT,
  execution_ms INT,
  error TEXT
);

CREATE INDEX IF NOT EXISTS idx_eval_results_run_id
  ON eval_results (run_id);

-- P6.2: Generated fleet reports (markdown digest + JSONB stats).
CREATE TABLE IF NOT EXISTS control_reports (
  id TEXT PRIMARY KEY,
  kind TEXT NOT NULL DEFAULT 'digest',
  interval TEXT NOT NULL DEFAULT 'daily',
  period_start TIMESTAMPTZ NOT NULL,
  period_end TIMESTAMPTZ NOT NULL,
  markdown TEXT NOT NULL,
  stats JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_control_reports_created
  ON control_reports (created_at DESC);

-- P6.2: Scheduler metadata for the in-process report timer. Single row keyed by
-- schedule name; last_run_at drives catch-up-on-boot (same pattern as retention).
CREATE TABLE IF NOT EXISTS control_schedule_meta (
  name TEXT PRIMARY KEY,
  interval TEXT NOT NULL DEFAULT 'daily',
  enabled BOOLEAN NOT NULL DEFAULT true,
  last_run_at TIMESTAMPTZ
);

INSERT INTO control_schedule_meta (name, interval, enabled)
VALUES ('report-digest', 'daily', true)
ON CONFLICT (name) DO NOTHING;

-- P7.1: Routing policies for the auto:* gateway. `match` selects which virtual
-- model a policy serves (e.g. 'auto:code'); `candidates` is an ordered list of
-- composite ids ('provider/model'); `fallback` is the last-resort composite id.
CREATE TABLE IF NOT EXISTS route_policies (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  virtual_model TEXT NOT NULL,
  candidates JSONB NOT NULL,
  fallback TEXT,
  enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  UNIQUE (virtual_model)
);

-- P7.1/P7.4: Per-dispatch log for the gateway. One row per resolved completion
-- routed through a virtual model, recording the chosen target + outcome.
CREATE TABLE IF NOT EXISTS route_dispatch_log (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  virtual_model TEXT NOT NULL,
  chosen_provider_id TEXT,
  chosen_model TEXT,
  candidates_tried JSONB,
  status TEXT NOT NULL,
  source TEXT,
  error TEXT,
  duration_ms INT
);

CREATE INDEX IF NOT EXISTS idx_route_dispatch_log_ts
  ON route_dispatch_log (ts DESC);

CREATE INDEX IF NOT EXISTS idx_route_dispatch_log_virtual
  ON route_dispatch_log (virtual_model, ts DESC);
