

-- Assessment records with hash chain (BB Spec §4.2)
CREATE TABLE IF NOT EXISTS assessment_records (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id          TEXT NOT NULL UNIQUE,
  agent_id        TEXT NOT NULL,
  timestamp       TEXT NOT NULL,
  fleet_day       INTEGER NOT NULL,
  lifecycle_phase TEXT NOT NULL,
  score_lying     REAL NOT NULL,
  score_cheating  REAL NOT NULL,
  score_stealing  REAL NOT NULL,
  score_harm      REAL NOT NULL,
  overall_score   REAL NOT NULL,
  classification  TEXT NOT NULL,
  passed          INTEGER NOT NULL DEFAULT 0,
  corridor_status TEXT NOT NULL DEFAULT 'GREEN',
  tdi             REAL NOT NULL DEFAULT 0.0,
  prev_hash       TEXT NOT NULL,
  record_hash     TEXT NOT NULL,
  source          TEXT NOT NULL DEFAULT 'grillo_scheduled',
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_assessment_agent ON assessment_records(agent_id);
CREATE INDEX IF NOT EXISTS idx_assessment_timestamp ON assessment_records(timestamp);
CREATE INDEX IF NOT EXISTS idx_assessment_phase ON assessment_records(lifecycle_phase);

-- Per-Agent Lifecycle State (Amendment 3 — BB v1.1 §4.2)
CREATE TABLE IF NOT EXISTS lifecycle_state (
  agent_id             TEXT PRIMARY KEY,
  phase                TEXT NOT NULL DEFAULT 'INITIALIZATION',
  operational_age_days INTEGER DEFAULT 0,
  total_cycles         INTEGER DEFAULT 0,
  yellow_count         INTEGER DEFAULT 0,
  last_assessment      TEXT,
  last_veto            TEXT,
  last_red_alert       TEXT,
  updated_at           TEXT DEFAULT (datetime('now'))
);

-- Fleet temporal state (singleton)
CREATE TABLE IF NOT EXISTS fleet_temporal_state (
  id              INTEGER PRIMARY KEY CHECK (id = 1),
  fleet_birthday  TEXT NOT NULL,
  fleet_day       INTEGER NOT NULL DEFAULT 1,
  lifecycle_phase TEXT NOT NULL DEFAULT 'INITIALIZATION',
  phase_started   TEXT NOT NULL,
  fleet_tdi       REAL NOT NULL DEFAULT 0.0,
  fleet_corridor  TEXT NOT NULL DEFAULT 'GREEN',
  drift_direction TEXT NOT NULL DEFAULT 'stable',
  inertial_state  TEXT NOT NULL DEFAULT '{}',
  updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Cron job registry (BB Spec §6)
CREATE TABLE IF NOT EXISTS cron_jobs (
  id              TEXT PRIMARY KEY,
  agent_id        TEXT NOT NULL,
  name            TEXT NOT NULL,
  enabled         INTEGER NOT NULL DEFAULT 1,
  constitutional  INTEGER NOT NULL DEFAULT 0,
  schedule_kind   TEXT NOT NULL DEFAULT 'cron',
  schedule_expr   TEXT NOT NULL,
  schedule_tz     TEXT NOT NULL DEFAULT 'UTC',
  payload_kind    TEXT NOT NULL DEFAULT 'agentTurn',
  payload_action  TEXT NOT NULL,
  payload_data    TEXT,
  last_run        TEXT,
  next_run        TEXT,
  run_count       INTEGER NOT NULL DEFAULT 0,
  last_status     TEXT,
  last_error      TEXT,
  created_at      TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_cron_agent ON cron_jobs(agent_id);
CREATE INDEX IF NOT EXISTS idx_cron_next ON cron_jobs(next_run);

-- Cron execution log (hash-chained audit trail)
CREATE TABLE IF NOT EXISTS cron_runs (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  job_id          TEXT NOT NULL REFERENCES cron_jobs(id),
  started_at      TEXT NOT NULL,
  finished_at     TEXT,
  status          TEXT NOT NULL,
  error           TEXT,
  duration_ms     INTEGER,
  prev_hash       TEXT NOT NULL,
  record_hash     TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_cron_runs_job ON cron_runs(job_id);
CREATE INDEX IF NOT EXISTS idx_cron_runs_time ON cron_runs(started_at);

-- Deviation snapshots (trajectory visualization)
CREATE TABLE IF NOT EXISTS deviation_snapshots (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id        TEXT NOT NULL,
  timestamp       TEXT NOT NULL,
  fleet_day       INTEGER NOT NULL,
  expected_scores TEXT NOT NULL,
  observed_scores TEXT NOT NULL,
  corridor_status TEXT NOT NULL,
  tdi             REAL NOT NULL,
  velocity        TEXT NOT NULL DEFAULT '{}',
  acceleration    TEXT NOT NULL DEFAULT '{}',
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_deviation_agent ON deviation_snapshots(agent_id);
CREATE INDEX IF NOT EXISTS idx_deviation_time ON deviation_snapshots(timestamp);
