#!/usr/bin/env bash
# jv_raw_records を開催年(meet_year)別に件数表示
#
# 例:
#   ./scripts/jv_raw_year_counts.sh
#   ./scripts/jv_raw_year_counts.sh 2019 2026
#
set -euo pipefail

FROM_Y="${1:-2020}"
TO_Y="${2:-2026}"

mysql keiba_ai -N -e "
SELECT COALESCE(t.y, y.年) AS 開催年, COALESCE(t.c, 0) AS 件数
FROM (
  SELECT ${FROM_Y} + seq AS 年
  FROM (
    SELECT 0 seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
  ) s
  WHERE ${FROM_Y} + seq <= ${TO_Y}
) y
LEFT JOIN (
  SELECT
    JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.meet_year')) AS y,
    COUNT(*) AS c
  FROM jv_raw_records
  GROUP BY JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.meet_year'))
) t ON t.y = y.年
ORDER BY y.年;
SELECT COUNT(*) FROM jv_raw_records;
" 2>/dev/null | {
  total=""
  while read -r year count; do
    if [[ "$year" =~ ^[0-9]+$ ]] && [[ "$count" =~ ^[0-9]+$ ]] && [[ "$year" -ge 1900 ]] && [[ "$year" -le 2100 ]]; then
      printf "%s年: %s件取得済み\n" "$year" "$count"
    elif [[ "$count" =~ ^[0-9]+$ ]] && [[ -z "$total" ]] && [[ ! "$year" =~ ^[0-9]{4}$ ]]; then
      total="$count"
    fi
  done
  [[ -n "$total" ]] && echo "合計: ${total}件"
}