-- Daily Query Cache System — Database Schema
-- Run: psql -h 127.0.0.1 -p 5433 -U eventheodds -d eventheodds_sports -f src/db/cache-schema.sql

-- Enable pg_trgm for fuzzy similarity matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 1. Main cache store
CREATE TABLE IF NOT EXISTS query_cache (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  cache_key         VARCHAR(64) NOT NULL,
  normalized_query  TEXT NOT NULL,
  original_query    TEXT NOT NULL,
  agent             VARCHAR(50) NOT NULL,
  query_category    VARCHAR(50),
  response_payload  TEXT NOT NULL,
  response_tokens   INT,
  league            VARCHAR(20),
  teams             TEXT[],
  game_date         DATE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at        TIMESTAMPTZ NOT NULL,
  last_hit_at       TIMESTAMPTZ,
  hit_count         INT NOT NULL DEFAULT 0,
  is_cacheable      BOOLEAN NOT NULL DEFAULT true,
  safety_flags      JSONB DEFAULT '{}',
  model_used        VARCHAR(100),
  original_tokens_in  INT,
  original_tokens_out INT,
  source            VARCHAR(20) DEFAULT 'live',
  CONSTRAINT uq_cache_key_agent UNIQUE (cache_key, agent)
);

CREATE INDEX IF NOT EXISTS idx_qc_cache_key_agent ON query_cache (cache_key, agent);
CREATE INDEX IF NOT EXISTS idx_qc_expires_at ON query_cache (expires_at);
CREATE INDEX IF NOT EXISTS idx_qc_league_game_date ON query_cache (league, game_date);
CREATE INDEX IF NOT EXISTS idx_qc_normalized_query_trgm ON query_cache USING gin (normalized_query gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_qc_created_at ON query_cache (created_at DESC);

-- 2. Event log (hits/misses)
CREATE TABLE IF NOT EXISTS cache_events (
  id                BIGSERIAL PRIMARY KEY,
  cache_entry_id    UUID REFERENCES query_cache(id) ON DELETE SET NULL,
  event_type        VARCHAR(20) NOT NULL,
  agent             VARCHAR(50) NOT NULL,
  normalized_query  TEXT,
  similarity_score  REAL,
  tokens_saved      INT DEFAULT 0,
  latency_ms        INT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ce_created_at ON cache_events (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ce_event_type ON cache_events (event_type);
CREATE INDEX IF NOT EXISTS idx_ce_agent ON cache_events (agent);

-- 3. Daily rollup for dashboards
CREATE TABLE IF NOT EXISTS cache_metrics_daily (
  id              SERIAL PRIMARY KEY,
  date            DATE NOT NULL,
  agent           VARCHAR(50) NOT NULL,
  total_queries   INT DEFAULT 0,
  cache_hits      INT DEFAULT 0,
  cache_misses    INT DEFAULT 0,
  tokens_saved    BIGINT DEFAULT 0,
  unique_queries  INT DEFAULT 0,
  top_queries     JSONB DEFAULT '[]',
  CONSTRAINT uq_cache_metrics_daily UNIQUE (date, agent)
);
