#!/usr/bin/env bash
# p6 honmei odds band vs hit rate
set -euo pipefail
mysql keiba_ai -e "
SELECT '=== p6 honmei odds band (2025) ===' AS '';
SELECT
  CASE
    WHEN o.min_odds < 5 THEN 'under5_fav'
    WHEN o.min_odds < 10 THEN '5-10'
    WHEN o.min_odds < 20 THEN '10-20'
    ELSE '20plus_longshot'
  END AS band,
  COUNT(*) AS n,
  SUM(a.match_honmei_winner=1) AS hits,
  ROUND(100.0*SUM(a.match_honmei_winner=1)/COUNT(*),2) AS hit_pct,
  ROUND(AVG(o.min_odds),1) AS avg_odds
FROM all_race_ai_profile_compare a
JOIN odds_lines o
  ON o.race_id = a.race_id
 AND o.odds_type = 'tansho'
 AND o.combination = CAST(a.ai_honmei_umaban AS CHAR)
WHERE a.profile_key='p6_latest'
  AND a.status='ok'
  AND YEAR(a.race_date)=2025
  AND a.winner_umaban IS NOT NULL
GROUP BY band
ORDER BY avg_odds;

SELECT '=== 1st favorite hit rate (baseline) ===' AS '';
SELECT COUNT(*) AS races,
  SUM(w.umaban = a.winner_umaban) AS fav_wins,
  ROUND(100.0*SUM(w.umaban = a.winner_umaban)/COUNT(*),2) AS fav_hit_pct
FROM all_race_ai_profile_compare a
JOIN (
  SELECT race_id, MIN(min_odds) AS min_o
  FROM odds_lines WHERE odds_type='tansho' GROUP BY race_id
) m ON m.race_id = a.race_id
JOIN odds_lines w ON w.race_id = a.race_id AND w.odds_type='tansho' AND w.min_odds = m.min_o
WHERE a.profile_key='p6_latest' AND YEAR(a.race_date)=2025 AND a.status='ok';
"