-- ---------------------------------------------------------------
-- 018 — Engine 2 + learning loop tables (Session 6c).
--
--   grade_history retrofit  : Engine 2 analysis columns
--   engine1_weights         : versioned factor weights for Engine 1
--   accuracy_tracking       : per-grade-tier hit rates with baseline lock
--
-- All three are service-role-only. The Ledger surface reads grade_history
-- directly (it already had RLS configured in earlier migrations); the new
-- engine2_* columns inherit that policy.
-- ---------------------------------------------------------------

ALTER TABLE public.grade_history
  ADD COLUMN IF NOT EXISTS engine2_grade            text,
  ADD COLUMN IF NOT EXISTS engine2_confidence       numeric,
  ADD COLUMN IF NOT EXISTS engine2_narrative        text,
  ADD COLUMN IF NOT EXISTS engine2_agrees           boolean,
  ADD COLUMN IF NOT EXISTS engine2_key_factor       text,
  ADD COLUMN IF NOT EXISTS engine2_trap_concern     text,
  ADD COLUMN IF NOT EXISTS engine2_model            text,
  ADD COLUMN IF NOT EXISTS engine2_latency_ms       integer,
  ADD COLUMN IF NOT EXISTS engine2_analyzed_at      timestamptz;

CREATE INDEX IF NOT EXISTS idx_grade_history_engine2_pending
  ON public.grade_history (id) WHERE engine2_analyzed_at IS NULL;

-- Session 7a additions: store Engine 1 factors (for the learning loop's
-- weight adjuster) and the quantile-based P(Over) (for the UI + Engine 2).
ALTER TABLE public.grade_history
  ADD COLUMN IF NOT EXISTS factors  jsonb,
  ADD COLUMN IF NOT EXISTS p_over   numeric;

-- ---------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.engine1_weights (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sport               text NOT NULL,
  stat_type           text NOT NULL,
  factor_name         text NOT NULL,
  weight              numeric NOT NULL,
  previous_weight     numeric,
  adjustment_reason   text,
  resolved_grade_id   uuid,
  version             integer NOT NULL DEFAULT 1,
  created_at          timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_weights_sport_stat
  ON public.engine1_weights (sport, stat_type);
CREATE INDEX IF NOT EXISTS idx_weights_version
  ON public.engine1_weights (sport, stat_type, factor_name, version DESC);

ALTER TABLE public.engine1_weights ENABLE ROW LEVEL SECURITY;

-- ---------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.accuracy_tracking (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sport               text NOT NULL,
  grade               text NOT NULL,
  period              text NOT NULL DEFAULT 'all_time',
  total_graded        integer NOT NULL DEFAULT 0,
  total_hit           integer NOT NULL DEFAULT 0,
  total_miss          integer NOT NULL DEFAULT 0,
  total_push          integer NOT NULL DEFAULT 0,
  total_void          integer NOT NULL DEFAULT 0,
  hit_rate            numeric,
  baseline_hit_rate   numeric,
  baseline_locked     boolean NOT NULL DEFAULT false,
  clv_avg             numeric,
  engine2_agree_rate  numeric,
  last_updated        timestamptz NOT NULL DEFAULT now(),
  UNIQUE (sport, grade, period)
);

ALTER TABLE public.accuracy_tracking ENABLE ROW LEVEL SECURITY;