-- ruler-comparison.sql — Order: MLB CALIBRATION RE-RUN vs CONSENSUS RULER (2026-08-01)
-- MEASURE-ONLY. Run against prod (Supabase MCP execute_sql).
--
-- WHAT THIS DOES AND DOES NOT ANSWER
--
-- It does NOT re-fit the p_win calibration. It cannot: `estimateProbability`
-- takes {gameLogs, line, statType, features} and never sees a market price, and
-- the calibration query fits p_win against OUTCOMES. Reliability and resolution
-- are both p_win-vs-outcome measures, so the ruler cannot enter either. See
-- pwin-timeforward.sql for the (ruler-independent) calibration refresh.
--
-- What IS ruler-dependent is EDGE (p_win - fair_prob). This measures whether
-- swapping the incumbent single-book ruler for a median consensus rescues it.
--
-- TIMING IS HELD CONSTANT: both rulers are read at CLOSE. The lock-time
-- reconstruction is impossible at usable n (only 43 settled rows join
-- lock_lines with >=2 two-sided books), and mixing a lock-time incumbent with
-- a close-time consensus would confound WHEN with WHAT.
--
-- LIMITATION, load-bearing: closing_captures contains ONLY MODEL books
-- (draftkings/betmgm/betrivers/fanduel/pinnacle). Exchange quotes were never
-- stored, because normalizeProps discarded them until 2026-08-01. So this can
-- only test a US-books-median ruler, NOT the exchange-inclusive consensus. The
-- exchange ruler is untestable on existing data at any n.

--
-- CONTAMINATION EXCLUSION (2026-08-02, MANDATORY). Rows whose price/book/takeable
-- were stamped from a NON-TAKEABLE book (DFS / offshore / exchange) between
-- 2026-08-01 and the write-path fix are tagged `quarantine_reason LIKE
-- 'nontakeable_book%'`. They are EXCLUDED here and must never be pooled with
-- clean rows: their locked price -- and therefore the `takeable` flag computed
-- from it -- describes a market you could not have bet.

with imp as (
  select id, player_key, stat, game_date, line, side, p_win, book, (outcome='hit')::int won
  from public.ledger_entries
  where sport='mlb' and user_id is null
    and (quarantine_reason is null or quarantine_reason not like 'nontakeable_book%') and outcome in ('hit','miss') and p_win is not null),

-- latest CLOSE capture per (prop, book); two-sided only -- a one-sided quote
-- cannot be de-vigged, so it cannot price a ruler.
cap as (
  select distinct on (player_key,stat,game_date,line,book)
         player_key,stat,game_date,line,book,over_odds,under_odds
  from public.closing_captures
  where sport='mlb' and over_odds is not null and under_odds is not null
  order by player_key,stat,game_date,line,book,captured_at desc),

d as (select *,
        case when over_odds>0  then 100.0/(over_odds+100)  else (-over_odds)/((-over_odds)+100.0)  end po,
        case when under_odds>0 then 100.0/(under_odds+100) else (-under_odds)/((-under_odds)+100.0) end pu
      from cap),

-- multiplicative two-way de-vig, per book (matches src/utils/devig.js)
f as (select player_key,stat,game_date,line,book, po/(po+pu) fo, pu/(po+pu) fu
      from d where po+pu > 0),

j as (select i.*, f.book ref_book,
             case when lower(i.side)='under' then f.fu else f.fo end fair_side
      from imp i join f
        on f.player_key=i.player_key and f.stat=i.stat
       and f.game_date=i.game_date and f.line=i.line),

a as (select id, p_win, won, game_date,
             count(*) n_books,
             percentile_cont(0.5) within group (order by fair_side) cons,   -- v2: MEDIAN
             max(case when ref_book=book then fair_side end) own            -- v1: the locked book
      from j group by 1,2,3,4),

e as (select *, p_win-own edge_v1, p_win-cons edge_v2
      from a where n_books>=2 and own is not null)

select count(*) n,
       round(avg(won)::numeric,4)                          base_rate,
       round(avg(abs(cons-own))::numeric,4)                mean_abs_ruler_gap,
       round(avg(cons-own)::numeric,4)                     mean_signed_ruler_gap,
       round(corr(edge_v1, won::numeric)::numeric,4)       corr_edge_v1_won,
       round(corr(edge_v2, won::numeric)::numeric,4)       corr_edge_v2_won,
       round(corr(p_win,  won::numeric)::numeric,4)        corr_pwin_won,
       round(avg(edge_v1) filter (where won=1)::numeric,4) edge_v1_winners,
       round(avg(edge_v1) filter (where won=0)::numeric,4) edge_v1_losers,
       round(avg(edge_v2) filter (where won=1)::numeric,4) edge_v2_winners,
       round(avg(edge_v2) filter (where won=0)::numeric,4) edge_v2_losers
from e;