-- tb-compound-holdout.sql — TOTAL BASES ONLY, direction-aligned.
--
-- TWO guards this query exists to enforce:
--   1. TB ROWS ONLY. Averaging into other stats would hide the effect, since
--      total_bases is 49 of 437 settled rows.
--   2. DIRECTION-ALIGNED. p_win is P(GRADED SIDE); proj_p_over_line and
--      proj_tb_p_over are P(OVER). 31.4% of rows are under-graded, and comparing
--      raw P(over) against an under-side outcome measures the model BACKWARDS —
--      that artifact alone accounted for 41% of the ladder's apparent loss.
--
-- Promote tb-v1 ONLY if it materially improves TB resolution toward/past the
-- champion. If it does NOT, the family-mismatch hypothesis is WRONG and the
-- mean-weakness / similarity branch REOPENS. Record which.

--
-- 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 tb as (
  select
    game_date, id, lower(side) side, (outcome='hit')::int won,
    p_win::numeric champ,
    case when lower(side)='under' then 1 - proj_p_over_line::numeric
         else proj_p_over_line::numeric end ladder_al,
    case when lower(side)='under' then 1 - proj_tb_p_over::numeric
         else proj_tb_p_over::numeric end tbv1_al
  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 stat = 'total_bases'
    and outcome in ('hit','miss')
    and p_win is not null
    and proj_p_over_line is not null
    and proj_tb_p_over is not null          -- matched rows: all three present
)
select
  count(*)                                            n,
  count(*) filter (where side='under')                under_rows,
  round(avg(won::numeric),3)                          base_rate,
  round(corr(champ,     won::numeric)::numeric,4)     res_champion,
  round(corr(ladder_al, won::numeric)::numeric,4)     res_ladder_v11,
  round(corr(tbv1_al,   won::numeric)::numeric,4)     res_tb_v1,
  round(stddev(ladder_al)::numeric,4)                 sd_ladder,
  round(stddev(tbv1_al)::numeric,4)                   sd_tb_v1,
  round(avg(ladder_al)::numeric,4)                    mean_ladder,
  round(avg(tbv1_al)::numeric,4)                      mean_tb_v1
from tb;