-- v1.13.3: statement_timeout is set at database level via:
--   ALTER DATABASE boochat SET statement_timeout = '30s';
-- ALTER DATABASE can't run inside a DO block, so this is an operational
-- step rather than schema. Re-apply after a volume reset (the setting
-- lives in pg_db which survives `docker compose up --build` but NOT a
-- `docker volume rm boocode_pgdata`).

CREATE TABLE IF NOT EXISTS projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  path TEXT NOT NULL UNIQUE,
  added_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  last_session_id UUID
);

CREATE TABLE IF NOT EXISTS sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  model TEXT NOT NULL,
  system_prompt TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_sessions_project ON sessions(project_id, updated_at DESC);

CREATE TABLE IF NOT EXISTS messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  role TEXT NOT NULL,
  content TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'complete',
  last_seq INT NOT NULL DEFAULT 0,
  cache_tokens INTEGER,
  reasoning_tokens INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id, created_at);

-- vDeepSeek: add cache/reasoning token columns early so messages_with_parts
-- view (defined below) can reference them. IF NOT EXISTS guards re-runs.
ALTER TABLE messages ADD COLUMN IF NOT EXISTS cache_tokens INTEGER;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS reasoning_tokens INTEGER;

-- v1.13.0: granular message parts table. v1.13.20: legacy tool_calls/
-- tool_results columns dropped; message_parts is now the sole source of
-- truth for tool calls, tool results, and reasoning. ON DELETE CASCADE
-- means removing a message removes its parts in one go.
CREATE TABLE IF NOT EXISTS message_parts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  message_id uuid NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
  sequence int NOT NULL,
  kind text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  CONSTRAINT message_parts_kind_chk CHECK (kind IN ('text', 'tool_call', 'tool_result', 'reasoning', 'step_start', 'synthesis', 'html_artifact')),
  CONSTRAINT message_parts_seq_uniq UNIQUE (message_id, sequence)
);
CREATE INDEX IF NOT EXISTS message_parts_msg_seq_idx ON message_parts (message_id, sequence);

-- v1.13.4: prune support. hidden_at marks parts that have been pruned out
-- of the model payload by the two-tier compaction prune (services/inference/
-- prune.ts). Rows stay in the DB so frontend can still display them with a
-- "hidden" indicator (out of scope this dispatch). messages_with_parts
-- view filters these out — see below. Partial index speeds the common
-- "visible parts only" filter.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'message_parts' AND column_name = 'hidden_at'
  ) THEN
    ALTER TABLE message_parts ADD COLUMN hidden_at timestamptz NULL;
  END IF;
END $$;
CREATE INDEX IF NOT EXISTS message_parts_hidden_idx
  ON message_parts (message_id) WHERE hidden_at IS NULL;

-- v2.x-workflow-sdk: add retry_count for future tool retry observability.
-- Idempotent: information_schema guard skips on re-run. Existing rows
-- receive 0 via DEFAULT; no existing retry logic — column is plumbing only.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'message_parts' AND column_name = 'retry_count'
  ) THEN
    ALTER TABLE message_parts ADD COLUMN retry_count int NOT NULL DEFAULT 0;
  END IF;
END $$;

-- v1.13.13: extend message_parts.kind to allow 'synthesis'. Existing DBs were
-- created with the pre-v1.13.13 CHECK constraint that did NOT include
-- 'synthesis'; drop + re-add the constraint with the extended enum. Fresh
-- installs hit the inline constraint above (already updated) and skip this
-- block via the pg_constraint guard.
-- v1.14.x-html-artifact-panes: extend the same constraint with 'html_artifact'.
-- DROP IF EXISTS + DO $$ pg_constraint $$ guard remains idempotent across
-- both v1.13.13 and v1.14.x boots; the IN list below is the union of every
-- kind ever shipped.
ALTER TABLE message_parts DROP CONSTRAINT IF EXISTS message_parts_kind_chk;
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'message_parts_kind_chk'
  ) THEN
    ALTER TABLE message_parts
      ADD CONSTRAINT message_parts_kind_chk
      CHECK (kind IN ('text', 'tool_call', 'tool_result', 'reasoning', 'step_start', 'synthesis', 'html_artifact'));
  END IF;
END $$;

-- v1.13.1-B: read-path view. Read sites SELECT FROM messages_with_parts
-- instead of messages so tool_calls / tool_results / reasoning_parts come
-- from the granular message_parts table.
-- v1.13.20: post column-drop. The legacy COALESCE fallback over
-- messages.tool_calls / messages.tool_results was removed because those
-- columns no longer exist on the table (see the ALTER TABLE DROP COLUMN
-- statements below). Writes continue to target `messages` directly — the
-- view is read-only. Shapes match the in-memory ToolCall / ToolResult
-- types: tool_calls is a jsonb array of {id, name, args}, tool_results is
-- a single jsonb object {tool_call_id, output, truncated, error?}.
-- reasoning_parts is consumed by the inference history fetch (payload.ts)
-- for v1.13.1-C reasoning round-tripping. Not surfaced in external APIs.
-- model-attribution: which model produced an assistant message (NULL for
-- user/system rows and pre-existing messages). Stamped at finalize (BooChat /
-- native coder) and at assistant-row creation (external coder dispatcher).
ALTER TABLE messages ADD COLUMN IF NOT EXISTS model TEXT;

CREATE OR REPLACE VIEW messages_with_parts AS
SELECT
  m.id, m.session_id, m.chat_id, m.role, m.content, m.kind, m.status,
  m.last_seq, m.tokens_used, m.ctx_used, m.ctx_max,
  m.started_at, m.finished_at, m.created_at, m.metadata,
  m.summary, m.tail_start_id, m.compacted_at,
  (SELECT jsonb_agg(p.payload ORDER BY p.sequence)
     FROM message_parts p
    WHERE p.message_id = m.id AND p.kind = 'tool_call' AND p.hidden_at IS NULL) AS tool_calls,
  (SELECT p.payload
     FROM message_parts p
    WHERE p.message_id = m.id AND p.kind = 'tool_result' AND p.hidden_at IS NULL
    ORDER BY p.sequence LIMIT 1) AS tool_results,
  (SELECT jsonb_agg(p.payload ORDER BY p.sequence)
     FROM message_parts p
    WHERE p.message_id = m.id AND p.kind = 'reasoning' AND p.hidden_at IS NULL) AS reasoning_parts,
  -- NEW columns MUST be appended at the end: CREATE OR REPLACE VIEW can't
  -- reorder/rename existing columns (42P16). cache_tokens and reasoning_tokens added last.
  m.model, m.cache_tokens, m.reasoning_tokens
FROM messages m;

-- v1.13.20: drop legacy tool_calls/tool_results columns. Reads have routed
-- through messages_with_parts since v1.13.1-B; dual-writes removed in this
-- batch. The view above was simplified to remove COALESCE fallbacks before
-- this drop (Postgres rejects column-drop on view-referenced columns).
-- Idempotent via IF EXISTS.
ALTER TABLE messages DROP COLUMN IF EXISTS tool_calls;
ALTER TABLE messages DROP COLUMN IF EXISTS tool_results;

-- v1.13.10: per-tool token cost rolling window. Derives from
-- messages_with_parts (the v1.13.1-B view; v1.13.20 removed the legacy
-- JSON-column COALESCE fallback — parts are sole source). No new write
-- site — all source data already lands via tool-phase.ts:94-95 UPDATE.
--
-- Attribution model: equal split. A turn emitting N tool calls divides its
-- prompt/completion tokens by N before attribution. See v1.13.10 dispatch
-- brief for rationale + rejected alternatives.
--
-- Column mapping: messages.ctx_used = prompt (input), messages.tokens_used
-- = completion (output). Non-obvious naming; pinned via canonical writes at
-- tool-phase.ts:94-95 et al.
--
-- Filtering rationale:
--   status='complete'                — exclude failed/cancelled (defense in
--                                      depth; failed-path doesn't write
--                                      tokens_used so they're filtered
--                                      indirectly too).
--   metadata->>'kind' exclusions     — exclude cap_hit / doom_loop sentinels
--                                      (defense in depth; sentinels are
--                                      role='system' with tool_calls=NULL
--                                      so they're filtered indirectly too).
--   experimental_repairToolCall      — no special handling; retries flow
--                                      as normal next-turn tool_result
--                                      errors and count naturally.
--
-- Rolling window: last 100 calls per tool_name, ordered by created_at DESC.
-- Aggregate-on-read is microseconds at BooCode scale (single user, ~30
-- tools, < 100 calls each). DROP VIEW + recreate to change window size.
CREATE OR REPLACE VIEW tool_cost_stats AS
WITH per_call AS (
  SELECT
    (tc->>'name')::text AS tool_name,
    (m.ctx_used::float / NULLIF(jsonb_array_length(m.tool_calls), 0)) AS prompt_tokens,
    (m.tokens_used::float / NULLIF(jsonb_array_length(m.tool_calls), 0)) AS completion_tokens,
    m.created_at,
    ROW_NUMBER() OVER (
      PARTITION BY (tc->>'name')::text
      ORDER BY m.created_at DESC
    ) AS rn
  FROM messages_with_parts m,
    LATERAL jsonb_array_elements(m.tool_calls) AS tc
  WHERE m.tool_calls IS NOT NULL
    AND jsonb_array_length(m.tool_calls) > 0
    AND m.tokens_used IS NOT NULL
    AND m.ctx_used IS NOT NULL
    AND m.status = 'complete'
    AND (m.metadata IS NULL
         OR m.metadata->>'kind' IS NULL
         OR m.metadata->>'kind' NOT IN ('cap_hit', 'doom_loop'))
)
SELECT
  tool_name,
  ROUND(SUM(prompt_tokens))::int AS prompt_tokens_sum,
  ROUND(SUM(completion_tokens))::int AS completion_tokens_sum,
  COUNT(*)::int AS n_calls,
  MAX(created_at) AS updated_at
FROM per_call
WHERE rn <= 100
GROUP BY tool_name;

ALTER TABLE messages ADD COLUMN IF NOT EXISTS tokens_used INTEGER;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS ctx_used INTEGER;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS ctx_max INTEGER;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS started_at TIMESTAMPTZ;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS finished_at TIMESTAMPTZ;

ALTER TABLE sessions ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp();

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

INSERT INTO settings (key, value) VALUES ('default_model', '"sam-desktop/qwen3.6-35b-a3b"') ON CONFLICT (key) DO NOTHING;

-- v1.12.1: deprecated session_panes table removed. Workspace pane state now
-- lives in sessions.workspace_panes (jsonb), see below.
DROP TABLE IF EXISTS session_panes;

-- v1.12.1: server-side workspace pane layout, replaces localStorage so every
-- device sees the same panes for a given session. Shape matches
-- WorkspacePane[] from apps/server/src/types/api.ts.
ALTER TABLE sessions ADD COLUMN IF NOT EXISTS workspace_panes JSONB NOT NULL DEFAULT '[]'::jsonb;

-- v1.2: sessions.status (open | archived)
ALTER TABLE sessions ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'open';

-- v1.2: chats table
-- per-chat-model-switching v2.x: ALTER below adds the model override column.
CREATE TABLE IF NOT EXISTS chats (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id   UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  name         TEXT,
  status       TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'archived')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);
CREATE INDEX IF NOT EXISTS idx_chats_session_status ON chats (session_id, status, updated_at DESC);

-- v2.7.x: per-chat model override. NULL = inherit from session.model.
ALTER TABLE chats ADD COLUMN IF NOT EXISTS model TEXT;

-- v1.2: messages.chat_id + messages.kind
ALTER TABLE messages ADD COLUMN IF NOT EXISTS chat_id UUID REFERENCES chats(id) ON DELETE CASCADE;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS kind TEXT NOT NULL DEFAULT 'message';
CREATE INDEX IF NOT EXISTS idx_messages_chat ON messages (chat_id, created_at);

-- Backfill: one chat per existing session that has none yet
INSERT INTO chats (session_id, name, status, created_at, updated_at)
SELECT s.id, s.name, 'open', s.created_at, s.updated_at
FROM sessions s
WHERE NOT EXISTS (
  SELECT 1 FROM chats c WHERE c.session_id = s.id
);

-- Backfill: link orphaned messages to their session's first chat
UPDATE messages SET chat_id = (
  SELECT c.id FROM chats c WHERE c.session_id = messages.session_id ORDER BY c.created_at ASC LIMIT 1
)
WHERE chat_id IS NULL;

-- Enforce NOT NULL on chat_id once all rows are backfilled
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'messages' AND column_name = 'chat_id' AND is_nullable = 'YES'
  ) AND NOT EXISTS (
    SELECT 1 FROM messages WHERE chat_id IS NULL
  ) THEN
    ALTER TABLE messages ALTER COLUMN chat_id SET NOT NULL;
  END IF;
END $$;

-- v1.2.1: CHECK constraints for sessions.status and messages (role, status)
-- KEEP IN SYNC: apps/server/src/types/api.ts (MESSAGE_ROLES, MESSAGE_STATUSES, SessionStatus)
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'sessions_status_chk') THEN
    ALTER TABLE sessions ADD CONSTRAINT sessions_status_chk
      CHECK (status IN ('open', 'archived'));
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'messages_role_chk') THEN
    ALTER TABLE messages ADD CONSTRAINT messages_role_chk
      CHECK (role IN ('user', 'assistant', 'system', 'tool'));
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'messages_status_chk') THEN
    ALTER TABLE messages ADD CONSTRAINT messages_status_chk
      CHECK (status IN ('streaming', 'complete', 'failed', 'cancelled'));
  END IF;
END $$;

-- v1.2-project-ux: projects.status + projects.gitea_remote
-- KEEP IN SYNC: apps/server/src/types/api.ts PROJECT_STATUSES
ALTER TABLE projects ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'open';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS gitea_remote TEXT;
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'projects_status_chk') THEN
    ALTER TABLE projects ADD CONSTRAINT projects_status_chk
      CHECK (status IN ('open', 'archived'));
  END IF;
END $$;

-- v1.3-tab-close-chat-archive: align chats.status vocabulary with projects ('archived' not 'closed')
-- KEEP IN SYNC: apps/server/src/types/api.ts CHAT_STATUSES
-- Order matters: (1) drop the OLD inline CHECK that only allowed ('open','closed');
-- (2) migrate existing rows; (3) add new named CHECK allowing ('open','archived').
ALTER TABLE chats DROP CONSTRAINT IF EXISTS chats_status_check;
UPDATE chats SET status = 'archived' WHERE status = 'closed';
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'chats_status_chk') THEN
    ALTER TABLE chats ADD CONSTRAINT chats_status_chk
      CHECK (status IN ('open', 'archived'));
  END IF;
END $$;

-- per-chat-model-switching: per-chat model override. NULL = inherit from session model.
ALTER TABLE chats ADD COLUMN IF NOT EXISTS model TEXT;

-- v1.x-batch9: per-session agent reference. Agent definitions are not stored in
-- the DB; they live in builtins (services/agents.ts) and a per-project AGENTS.md.
-- agent_id is the slugified agent name. NULL means "use BooCode defaults".
ALTER TABLE sessions ADD COLUMN IF NOT EXISTS agent_id TEXT;

-- v1.13.17-cross-repo-reads: session-scoped read grants for paths outside the
-- session's primary project root. Populated only by the request_read_access
-- tool's approve branch; revoked via PATCH /api/sessions/:id. Values are
-- absolute paths to project roots OR repo-shaped dirs under
-- PROJECT_ROOT_WHITELIST (default /opt). No CHECK constraint — validation
-- happens at write time in services/grant_resolver.ts. Cleared automatically
-- when the session row is deleted (no cascade needed; the column goes with it).
ALTER TABLE sessions
  ADD COLUMN IF NOT EXISTS allowed_read_paths TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[];

-- v1.8.2: per-message metadata for sentinels (cap-hit) and structured error
-- reasons. JSONB so future kinds can extend without further schema churn.
-- Shape for cap_hit:  { kind: 'cap_hit', used: number, limit: number,
--                       agent_name: string|null, can_continue: boolean }
-- Shape for errors:   { error_reason: 'llm_provider_error'|..., error_text: string }
ALTER TABLE messages ADD COLUMN IF NOT EXISTS metadata JSONB;

-- themes-v1: idempotent seeds for the two theme preference keys. The settings
-- table is a key/value store (see line 43) so theme prefs live as two rows,
-- not new columns. Defaults match docs/themes_v1.md: obsidian (dark).
INSERT INTO settings (key, value) VALUES ('theme_id', '"obsidian"') ON CONFLICT (key) DO NOTHING;
INSERT INTO settings (key, value) VALUES ('theme_mode', '"dark"') ON CONFLICT (key) DO NOTHING;

-- v1.9: per-project defaults that new sessions inherit, plus a per-session
-- web-search override. Empty string on either prompt column means "inherit"
-- (resolved in services/system-prompt.ts buildSystemPrompt). web_search_enabled is the
-- only tri-state field: null on session = inherit from project default.
ALTER TABLE projects ADD COLUMN IF NOT EXISTS default_system_prompt TEXT NOT NULL DEFAULT '';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS default_web_search_enabled BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE sessions ADD COLUMN IF NOT EXISTS web_search_enabled BOOLEAN;

-- v[state-graph]: optional declarative state-graph engine flag. Default OFF
-- (existing procedural while loop). When ON, runAssistantTurn routes
-- through runGraph in state-graph.ts for node-based execution.
ALTER TABLE sessions ADD COLUMN IF NOT EXISTS state_graph_enabled BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE sessions DROP COLUMN IF EXISTS tags;

-- v1.11: anchored rolling compaction.
--   compacted_at  — marks rows that are "behind the curtain" of the latest
--                   summary. Inference assembly filters compacted_at IS NULL;
--                   the API GET still returns all rows so the UI can show
--                   history with the summary card inline.
--   summary       — true on the assistant row that IS the anchored summary.
--                   Exactly one row per chat is the "current" summary
--                   (every prior summary row is itself compacted_at-stamped
--                   when superseded, leaving one live anchor).
--   tail_start_id — points at the first preserved message that the summary
--                   covers up to (exclusive). Lets the UI/debug reason about
--                   the boundary without re-deriving from compacted_at.
--   needs_compaction — flag on chats (not sessions) because chat history is
--                   per-chat; sessions have 1:N chats. Set true post-overflow,
--                   cleared by compaction.process at the start of the next
--                   inference turn.
ALTER TABLE messages ADD COLUMN IF NOT EXISTS compacted_at TIMESTAMPTZ;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS summary BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS tail_start_id UUID REFERENCES messages(id) ON DELETE SET NULL;
ALTER TABLE chats ADD COLUMN IF NOT EXISTS needs_compaction BOOLEAN NOT NULL DEFAULT FALSE;
CREATE INDEX IF NOT EXISTS idx_messages_chat_compacted ON messages (chat_id, compacted_at);

-- tasks table (provider dispatch)
CREATE TABLE IF NOT EXISTS tasks (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id        UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  session_id        UUID REFERENCES sessions(id) ON DELETE CASCADE,
  parent_task_id    UUID REFERENCES tasks(id),
  state             TEXT NOT NULL DEFAULT 'pending'
                    CHECK (state IN ('pending','running','completed','failed','blocked','cancelled')),
  input             TEXT NOT NULL,
  output_summary    TEXT,
  agent             TEXT,
  model             TEXT,
  mode_id           TEXT,
  thinking_option_id TEXT,
  execution_path    TEXT CHECK (execution_path IS NULL OR execution_path IN ('native','acp','pty','qwen')),
  cost_tokens       INTEGER,
  started_at        TIMESTAMPTZ,
  ended_at          TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- Fix tasks FK to cascade on session delete (existing tables without CASCADE)
DO $$ BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'tasks_session_id_fkey'
    AND confdeltype != 'c'
  ) THEN
    ALTER TABLE tasks DROP CONSTRAINT tasks_session_id_fkey;
    ALTER TABLE tasks ADD CONSTRAINT tasks_session_id_fkey
      FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE;
  END IF;
END $$;

-- Remove the v2.0.5 arena_id column (replaced by the new Arena feature).
ALTER TABLE tasks DROP COLUMN IF EXISTS arena_id;

-- v2.x-tool-traces: per-call tool execution records for observability.
CREATE TABLE IF NOT EXISTS tool_traces (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id       UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  chat_id          UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
  message_id       UUID REFERENCES messages(id) ON DELETE SET NULL,
  turn_number      INTEGER NOT NULL,
  tool_name        TEXT NOT NULL,
  tool_input       JSONB NOT NULL,
  tool_output      TEXT,
  started_at       TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  finished_at      TIMESTAMPTZ,
  latency_ms       INTEGER,
  tokens_used      INTEGER,
  cache_tokens     INTEGER,
  reasoning_tokens INTEGER,
  error            TEXT,
  outcome          TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_tool_traces_chat ON tool_traces(chat_id, created_at);

-- v2.x-tool-traces: active tool call state for in-flight instrumentation.
CREATE TABLE IF NOT EXISTS tool_trace_states (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id       UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  chat_id          UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
  message_id       UUID REFERENCES messages(id) ON DELETE SET NULL,
  turn_number      INTEGER NOT NULL,
  tool_name        TEXT NOT NULL,
  tool_input       JSONB NOT NULL,
  started_at       TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- agent_snapshots: persistent agent session state for cross-refresh resume.
CREATE TABLE IF NOT EXISTS agent_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
  model TEXT NOT NULL,
  agent TEXT,
  mode TEXT,
  turn_number INTEGER NOT NULL DEFAULT 0,
  messages JSONB NOT NULL DEFAULT '[]'::jsonb,
  tool_states JSONB NOT NULL DEFAULT '[]'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);
CREATE INDEX IF NOT EXISTS idx_agent_snapshots_chat ON agent_snapshots(chat_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_agent_snapshots_chat_unique ON agent_snapshots(chat_id);

-- memory-browser-ui: topic-based memory, daily log, dream diaries.
CREATE TABLE IF NOT EXISTS memory_entries (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  topic       TEXT NOT NULL,
  title       TEXT NOT NULL,
  content     TEXT NOT NULL DEFAULT '',
  tags        TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  date        DATE,
  mood        TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);
CREATE INDEX IF NOT EXISTS idx_memory_entries_project ON memory_entries(project_id, created_at DESC);
