-- Cache Backtests table for The Pool
-- Run: psql -h 127.0.0.1 -p 5433 -U eventheodds -d eventheodds_sports -f pool-schema.sql

CREATE TABLE IF NOT EXISTS cache_backtests (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          VARCHAR(200) NOT NULL,
  status        VARCHAR(20) NOT NULL DEFAULT 'pending',
  config        JSONB NOT NULL,
  results       JSONB,
  detail_rows   JSONB DEFAULT '[]',
  queries_total INT DEFAULT 0,
  queries_done  INT DEFAULT 0,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at  TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_cache_backtests_status ON cache_backtests(status);
CREATE INDEX IF NOT EXISTS idx_cache_backtests_created ON cache_backtests(created_at DESC);
