#!/usr/bin/env bash
# odds_lines tansho / umaren count by year (JRA 2020+)
set -euo pipefail
mysql keiba_ai -e "
SELECT YEAR(r.race_date) AS y,
       COUNT(DISTINCT CASE WHEN o.odds_type='tansho' THEN r.id END) AS tansho,
       COUNT(DISTINCT CASE WHEN o.odds_type='umaren' THEN r.id END) AS umaren
FROM races r
LEFT JOIN odds_lines o ON o.race_id = r.id
WHERE r.circuit='JRA' AND YEAR(r.race_date) >= 2020
GROUP BY y
ORDER BY y;
"