-- =====================================================================
-- agent_telemetry table: stores full AI turn telemetry for diagnostics
-- and as training data for fine-tuning.
-- =====================================================================
--
-- Each row captures one model turn: the exact context the model saw
-- (system prompt + chat history) and the exact output it produced
-- (text, thoughts, tool calls), plus token/latency metrics.
--
-- JSONB columns let you export clean fine-tuning datasets later, e.g.
--   SELECT input_context, target_output FROM agent_telemetry;

CREATE TABLE IF NOT EXISTS agent_telemetry (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  -- 'llm_call'  = one row per model call (training data)
  -- 'turn_summary' = one row per user turn (orchestration diagnostics)
  record_type TEXT NOT NULL DEFAULT 'llm_call',
  project_id VARCHAR(255),
  session_id TEXT,            -- chat thread id; groups all calls of a conversation
  user_message TEXT,         -- the user message that triggered the turn
  model_used VARCHAR(255) NOT NULL,
  system_prompt TEXT NOT NULL,
  chat_history JSONB NOT NULL,
  response_text TEXT,
  response_thoughts TEXT,
  tool_calls JSONB,
  tool_results JSONB,        -- redacted tool outputs (turn_summary)
  -- Orchestration / governor diagnostics (turn_summary rows)
  stop_reason TEXT,          -- completed | round_cap | loop_detected:* | tool_failure | silent_rounds | user_aborted | empty_no_tools
  rounds INTEGER,            -- how many tool-loop rounds the turn ran
  tool_call_count INTEGER,   -- total tool calls executed in the turn
  turn_intent TEXT,          -- conversational vs action intent classification
  chat_mode TEXT,            -- vibe | collaborate | delegate
  prompt_tokens INTEGER,
  completion_tokens INTEGER,
  total_tokens INTEGER,
  duration_ms INTEGER NOT NULL
);

-- Index for fast querying by project
CREATE INDEX IF NOT EXISTS idx_agent_telemetry_project ON agent_telemetry(project_id);
-- Index for chronological sorting
CREATE INDEX IF NOT EXISTS idx_agent_telemetry_created_at ON agent_telemetry(created_at DESC);
-- Diagnostic indexes
CREATE INDEX IF NOT EXISTS idx_agent_telemetry_session ON agent_telemetry(session_id);
CREATE INDEX IF NOT EXISTS idx_agent_telemetry_stop ON agent_telemetry(stop_reason);
CREATE INDEX IF NOT EXISTS idx_agent_telemetry_record_type ON agent_telemetry(record_type);

-- =====================================================================
-- Example diagnostic queries (the whole point of this instrumentation)
-- =====================================================================
--
-- Distribution of how turns end (find premature-stop problems):
--   SELECT stop_reason, COUNT(*), ROUND(AVG(rounds),1) AS avg_rounds
--   FROM agent_telemetry WHERE record_type='turn_summary'
--   GROUP BY stop_reason ORDER BY 2 DESC;
--
-- Turns the governor cut off early (rounds < 3 but tools were running):
--   SELECT created_at, project_id, user_message, stop_reason, rounds, tool_call_count
--   FROM agent_telemetry
--   WHERE record_type='turn_summary' AND stop_reason <> 'completed' AND rounds < 3
--   ORDER BY created_at DESC;
--
-- Replay the full trajectory of one turn:
--   SELECT created_at, record_type, response_text, tool_calls
--   FROM agent_telemetry WHERE session_id = '<thread_id>' ORDER BY created_at ASC;