-- A. All-three-zero — historical fingerprint of #496 mislabel.
--    Investigate spike vs. weekly baseline.
SELECT count() FROM sequency.debate_recommendation_outcomes
WHERE confluence_version = 'v5'
  AND confluence_bull_v5 = 0
  AND confluence_bear_v5 = 0
  AND confluence_net_v5  = 0
  AND debate_timestamp > now() - INTERVAL 1 DAY;

-- B. Asymmetric-zero — one v5 side zero while the other is non-zero.
--    Useful for diagnosing partial-population leakage.
SELECT count() FROM sequency.debate_recommendation_outcomes
WHERE confluence_version = 'v5'
  AND ((confluence_bull_v5 = 0) != (confluence_bear_v5 = 0))
  AND debate_timestamp > now() - INTERVAL 1 DAY;

-- C. Arithmetic-inconsistency (HARD GATE — investigate any non-zero count).
--    Net non-zero with both bull and bear at zero is structurally impossible
--    when bull/bear are sourced and net = bull − bear. Indicates a writer
--    or scoring bug.
SELECT count() FROM sequency.debate_recommendation_outcomes
WHERE confluence_version = 'v5'
  AND abs(confluence_net_v5) > 0
  AND abs(confluence_bull_v5) + abs(confluence_bear_v5) = 0
  AND debate_timestamp > now() - INTERVAL 1 DAY;

SELECT * FROM sequency.debate_recommendation_outcomes
WHERE confluence_version = 'v5'
  AND debate_timestamp >= toDateTime('2026-04-26 00:00:00');  -- post-#497 only

SELECT * FROM sequency.debate_recommendation_outcomes
WHERE confluence_version != '';

-- INVALID: reconstructed rows have look-ahead bias from the score_history
-- rebuild, so pooling them with live-emission v5 inflates apparent
-- accuracy. Forward-return calibration on this cohort is unreliable.
WHERE confluence_version IN ('v5', 'reconstructed')

-- INVALID for the same reason regardless of source-prefixed reconstructed
-- variants:
WHERE confluence_version IN ('v5', 'v5_reconstructed')

-- INVALID: pools two distinct generations. Use only when the analysis
-- is explicitly cross-generation comparison (e.g. v5 vs v4.3.1
-- per-symbol accuracy) and even then, GROUP BY confluence_version.
WHERE confluence_version IN ('v5', 'v4.3.1')

WHERE confluence_version IN ('v5', 'v5_partial')

-- Correct: SQL-level pattern match catches every reconstructed shape,
-- including future source-prefixes added under #491.
WHERE confluence_version NOT LIKE '%reconstructed%'

SELECT
  toStartOfWeek(debate_timestamp) AS week,
  countIf(confluence_version = 'v4.3.1') / count() AS v43_ratio,
  countIf(confluence_version = 'v4.2')   / count() AS v4_ratio
FROM sequency.debate_recommendation_outcomes
WHERE debate_timestamp > now() - INTERVAL 30 DAY
GROUP BY week
ORDER BY week DESC;

sum by (error_type) (rate(debate_outcome_write_error_total[5m])) > 0