-- LEGACY: This file is superseded by db_setup_postgres.sql and db_setup_sqlite.sql.
-- Kept for historical reference only.
--
-- hazo_llm_api_log: per-call observability log.
-- Written by the default afterResponse hook (on by default; disable via api_log.enabled=false).
-- Cost values are frozen per-row for accurate historical audit.
--
-- SQLite version (commented):
-- CREATE TABLE IF NOT EXISTS hazo_llm_api_log (
--   id                        TEXT PRIMARY KEY,
--   created_at                TEXT NOT NULL DEFAULT (datetime('now')),
--   service_type              TEXT NOT NULL,
--   provider                  TEXT NOT NULL,
--   model                     TEXT,
--   success                   INTEGER NOT NULL,
--   tokens_input              INTEGER,
--   tokens_output             INTEGER,
--   tokens_cached_input       INTEGER,
--   tokens_total              INTEGER,
--   cost_usd                  REAL,
--   pricing_kind              TEXT,
--   pricing_input_per_1m_usd  REAL,
--   pricing_output_per_1m_usd REAL,
--   pricing_per_image_usd     REAL,
--   latency_ms                INTEGER,
--   finish_reason             TEXT,
--   error_code                TEXT,
--   error_message             TEXT,
--   prompt_area               TEXT,
--   prompt_key                TEXT,
--   prompt_id                 TEXT,
--   session_id                TEXT,
--   reference                 TEXT,
--   attempts_count            INTEGER DEFAULT 1,
--   context_json              TEXT DEFAULT '{}'
-- );
--
-- PostgreSQL version (active):
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);
