-- Migration: V583 - Add cache token columns to llm_conversation_agent table
-- Purpose: Track cached and non-cached tokens separately for accurate cost calculation
-- Supports: Google AI (Gemini), Anthropic (Claude), and future providers with caching
-- Date: 2025-10-21

-- Add columns for cache token tracking
ALTER TABLE llm_conversation_agent
ADD COLUMN IF NOT EXISTS cached_input_tokens INTEGER DEFAULT 0 NOT NULL,
ADD COLUMN IF NOT EXISTS non_cached_input_tokens INTEGER DEFAULT 0 NOT NULL;

-- Add comments to explain all token-related columns for clarity
-- Works for Google AI (Gemini), Anthropic (Claude), and any provider with caching support
COMMENT ON COLUMN llm_conversation_agent.input_tokens IS 'Total input tokens (cached + non_cached) sent to LLM';
COMMENT ON COLUMN llm_conversation_agent.output_tokens IS 'Total output tokens generated by LLM';
COMMENT ON COLUMN llm_conversation_agent.cached_input_tokens IS 'Tokens served from cache (10x cheaper: $0.125/1M for Gemini, $0.30/1M for Claude)';
COMMENT ON COLUMN llm_conversation_agent.non_cached_input_tokens IS 'New input tokens not from cache (regular rate: $1.25/1M for Gemini, $3.00/1M for Claude)';

-- Create index for performance when querying by cache usage
CREATE INDEX IF NOT EXISTS idx_llm_conversation_agent_cache_tokens
ON llm_conversation_agent(cached_input_tokens, non_cached_input_tokens)
WHERE cached_input_tokens > 0;

-- Backfill existing data: assume all existing input tokens are non-cached
-- This is conservative and won't overestimate savings
UPDATE llm_conversation_agent
SET non_cached_input_tokens = input_tokens,
    cached_input_tokens = 0
WHERE non_cached_input_tokens = 0 AND input_tokens > 0;
