-- hazo_llm_api — complete idempotent PostgreSQL schema
-- Run this once against a fresh database, or re-run safely on an existing one.
-- All statements use IF NOT EXISTS / IF EXISTS guards.

-- ---------------------------------------------------------------------------
-- prompts_library
-- Stores reusable prompt templates managed via the prompt-config UI.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS prompts_library (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prompt_area         TEXT NOT NULL,
  prompt_key          TEXT NOT NULL,
  prompt_name         TEXT NOT NULL,
  prompt_text_head    TEXT DEFAULT '',
  prompt_text_body    TEXT NOT NULL,
  prompt_text_tail    TEXT DEFAULT '',
  prompt_variables    TEXT DEFAULT '[]',
  prompt_notes        TEXT DEFAULT '',
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  changed_by          TEXT DEFAULT NULL,
  UNIQUE(prompt_area, prompt_key)
);

CREATE INDEX IF NOT EXISTS idx_prompts_area_key ON prompts_library(prompt_area, prompt_key);

-- ---------------------------------------------------------------------------
-- hazo_llm_api_log
-- Per-call observability log written by the default afterResponse hook.
-- Cost values are frozen per-row for accurate historical audit.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS hazo_llm_api_log (
  id                        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at                TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  service_type              TEXT NOT NULL,
  provider                  TEXT NOT NULL,
  model                     TEXT,
  success                   BOOLEAN NOT NULL,
  tokens_input              INTEGER,
  tokens_output             INTEGER,
  tokens_cached_input       INTEGER,
  tokens_total              INTEGER,
  cost_usd                  DOUBLE PRECISION,
  pricing_kind              TEXT,
  pricing_input_per_1m_usd  DOUBLE PRECISION,
  pricing_output_per_1m_usd DOUBLE PRECISION,
  pricing_per_image_usd     DOUBLE PRECISION,
  latency_ms                INTEGER,
  finish_reason             TEXT,
  error_code                TEXT,
  error_message             TEXT,
  prompt_area               TEXT,
  prompt_key                TEXT,
  prompt_id                 UUID,
  session_id                TEXT,
  reference                 TEXT,
  attempts_count            INTEGER DEFAULT 1,
  context_json              JSONB DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_llm_log_created         ON hazo_llm_api_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_llm_log_provider        ON hazo_llm_api_log(provider, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_llm_log_success         ON hazo_llm_api_log(success, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_llm_log_session         ON hazo_llm_api_log(session_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_llm_log_service         ON hazo_llm_api_log(service_type, created_at DESC);
-- Cost-cap index (session window queries, no DESC — matches cost_cap.ts usage)
CREATE INDEX IF NOT EXISTS idx_llm_log_session_created ON hazo_llm_api_log(session_id, created_at);

NOTIFY pgrst, 'reload schema';
