-- Global database schema (admin.db)

CREATE TABLE IF NOT EXISTS projects (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  description TEXT,
  repo_name TEXT NOT NULL,
  repo_path TEXT NOT NULL,
  default_branch TEXT DEFAULT 'main',
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

-- Default settings
INSERT OR IGNORE INTO settings (key, value) VALUES
  ('anthropic_model_planning', 'claude-opus-4-20250514'),
  ('anthropic_model_review', 'claude-sonnet-4-20250514'),
  ('anthropic_model_implementation', 'claude-opus-4-20250514'),
  ('anthropic_model_testing', 'claude-sonnet-4-20250514');

-- Per-project database schema (applied to {project-name}.db)
-- This comment marks where project schema starts; code splits on this marker.
-- === PROJECT_SCHEMA ===

CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'todo',
  priority TEXT NOT NULL DEFAULT 'medium',
  level INTEGER NOT NULL DEFAULT 3,
  description TEXT,
  plan TEXT,
  decision_log TEXT,
  done_when TEXT,
  implementation_notes TEXT,
  tags TEXT DEFAULT '[]',
  plan_review_comments TEXT DEFAULT '[]',
  review_comments TEXT DEFAULT '[]',
  test_results TEXT DEFAULT '[]',
  agent_log TEXT DEFAULT '[]',
  current_agent TEXT,
  plan_review_count INTEGER DEFAULT 0,
  impl_review_count INTEGER DEFAULT 0,
  branch_name TEXT,
  gerrit_change_id TEXT,
  gerrit_change_number INTEGER,
  diff TEXT,
  rank REAL DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now')),
  started_at TEXT,
  planned_at TEXT,
  reviewed_at TEXT,
  tested_at TEXT,
  completed_at TEXT
);

CREATE TABLE IF NOT EXISTS pipeline_jobs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_id INTEGER NOT NULL REFERENCES tasks(id),
  agent TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  model TEXT NOT NULL,
  started_at TEXT,
  completed_at TEXT,
  error TEXT,
  tokens_used INTEGER DEFAULT 0
);