-- Engagement server schema v1
-- Managed by CC (create-claude-cabinet). Do not edit manually.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS engagements (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  auth_mode TEXT NOT NULL DEFAULT 'local' CHECK(auth_mode IN ('local', 'external')),
  auth_config TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  engagement_id TEXT NOT NULL REFERENCES engagements(id),
  name TEXT NOT NULL,
  email TEXT,
  role TEXT NOT NULL CHECK(role IN ('consultant', 'client')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_users_engagement ON users(engagement_id);

CREATE TABLE IF NOT EXISTS api_tokens (
  token_hash TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  label TEXT,
  revoked_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_tokens_user ON api_tokens(user_id);

CREATE TABLE IF NOT EXISTS messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  engagement_id TEXT NOT NULL REFERENCES engagements(id),
  from_user_id TEXT NOT NULL REFERENCES users(id),
  type TEXT NOT NULL,
  context_type TEXT CHECK(context_type IS NULL OR context_type IN ('item', 'message')),
  context_ref TEXT,
  payload TEXT NOT NULL CHECK(length(payload) <= 5000000),
  expires_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_msg_engagement ON messages(engagement_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_msg_context ON messages(engagement_id, context_type, context_ref);
