#!/usr/bin/env bash
# p6_latest hit stats from all_race_ai_profile_compare
set -euo pipefail
mysql keiba_ai -e "
SELECT '=== p6_latest overall ===' AS '';
SELECT
  COUNT(*) AS total,
  SUM(status='ok') AS ok_rows,
  SUM(winner_umaban IS NOT NULL) AS with_winner,
  SUM(match_honmei_winner=1) AS honmei_hit,
  SUM(match_taikou_winner=1) AS taikou_hit,
  SUM(match_honmei_or_taikou_winner=1) AS top2_hit,
  ROUND(100.0*SUM(match_honmei_winner=1)/NULLIF(SUM(winner_umaban IS NOT NULL),0),2) AS honmei_pct
FROM all_race_ai_profile_compare
WHERE profile_key='p6_latest';

SELECT '=== by year ===' AS '';
SELECT
  YEAR(race_date) AS y,
  COUNT(*) AS n,
  SUM(match_honmei_winner=1) AS honmei_hit,
  ROUND(100.0*SUM(match_honmei_winner=1)/COUNT(*),2) AS honmei_pct
FROM all_race_ai_profile_compare
WHERE profile_key='p6_latest' AND status='ok' AND winner_umaban IS NOT NULL
GROUP BY YEAR(race_date)
ORDER BY y;

SELECT '=== sample hits (latest 5) ===' AS '';
SELECT race_date, track_code, race_number, ai_honmei_umaban, winner_umaban, match_honmei_winner
FROM all_race_ai_profile_compare
WHERE profile_key='p6_latest' AND match_honmei_winner=1
ORDER BY race_date DESC
LIMIT 5;
"