#!/usr/bin/env python3
"""
JRA データの揃い具合を年別に集計する（races / 結果 / 払戻 / オッズ / predictions / p6 比較表）。

例:
  python3 scripts/audit_data_coverage.py
  python3 scripts/audit_data_coverage.py --from-year 2021 --to-year 2026
"""
from __future__ import annotations

import argparse
import os
import sys

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from collectors.db_util import connect  # noqa: E402

# 中央競馬の目安（フルシーズンおおよそ）
FULL_YEAR_RACES_HINT = 3200


def main() -> None:
    ap = argparse.ArgumentParser(description="JRA データカバレッジ監査")
    ap.add_argument("--from-year", type=int, default=2019)
    ap.add_argument("--to-year", type=int, default=2026)
    args = ap.parse_args()

    conn = connect()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT
                  YEAR(r.race_date) AS y,
                  COUNT(DISTINCT r.id) AS races,
                  COUNT(DISTINCT re.race_id) AS with_entries,
                  COUNT(DISTINCT rr1.race_id) AS with_winner,
                  COUNT(DISTINCT pt.race_id) AS with_tansho_pay,
                  COUNT(DISTINCT ol.race_id) AS with_tansho_odds,
                  COUNT(DISTINCT pr.race_id) AS with_predictions,
                  COUNT(DISTINCT c.race_id) AS p6_compare_ok
                FROM races r
                LEFT JOIN race_entries re ON re.race_id = r.id
                LEFT JOIN (
                  SELECT race_id FROM race_results WHERE finish_position = 1 GROUP BY race_id
                ) rr1 ON rr1.race_id = r.id
                LEFT JOIN (
                  SELECT race_id FROM payouts WHERE bet_type = 'tansho' GROUP BY race_id
                ) pt ON pt.race_id = r.id
                LEFT JOIN (
                  SELECT race_id FROM odds_lines WHERE odds_type = 'tansho' GROUP BY race_id
                ) ol ON ol.race_id = r.id
                LEFT JOIN (
                  SELECT DISTINCT race_id FROM predictions
                  WHERE prediction_type = 'stage1_screening_accuracy'
                ) pr ON pr.race_id = r.id
                LEFT JOIN (
                  SELECT race_id FROM all_race_ai_profile_compare
                  WHERE profile_key = 'p6_latest' AND status = 'ok'
                ) c ON c.race_id = r.id
                WHERE r.circuit = 'JRA'
                  AND YEAR(r.race_date) BETWEEN %s AND %s
                GROUP BY YEAR(r.race_date)
                ORDER BY y
                """,
                (args.from_year, args.to_year),
            )
            rows = cur.fetchall()

            cur.execute("SELECT COUNT(*) AS n FROM jv_raw_records")
            jv_raw = int((cur.fetchone() or {}).get("n") or 0)
            cur.execute(
                """
                SELECT MAX(race_date) AS last FROM races WHERE circuit = 'JRA'
                """
            )
            last_date = (cur.fetchone() or {}).get("last")
    finally:
        conn.close()

    print(f"jv_raw_records: {jv_raw:,} 件")
    print(f"races 最終開催日: {last_date}")
    print()
    hdr = (
        f"{'年':>4}  {'レース':>6}  {'出走表':>6}  {'1着確定':>7}  {'払戻':>6}  "
        f"{'単勝ｵｯｽﾞ':>8}  {'predict':>7}  {'p6比較':>6}  目安({FULL_YEAR_RACES_HINT})"
    )
    print(hdr)
    print("-" * len(hdr))

    gaps: list[str] = []
    for r in rows:
        y = int(r["y"])
        races = int(r["races"] or 0)
        hint = FULL_YEAR_RACES_HINT if y < args.to_year else "途中"
        print(
            f"{y:4d}  {races:6d}  {int(r['with_entries'] or 0):6d}  "
            f"{int(r['with_winner'] or 0):7d}  {int(r['with_tansho_pay'] or 0):6d}  "
            f"{int(r['with_tansho_odds'] or 0):8d}  {int(r['with_predictions'] or 0):7d}  "
            f"{int(r['p6_compare_ok'] or 0):6d}  {hint}"
        )
        if races < FULL_YEAR_RACES_HINT * 0.85 and y < args.to_year:
            gaps.append(f"{y}年: レース行が不足（{races} / 目安{FULL_YEAR_RACES_HINT}）")
        w = int(r["with_winner"] or 0)
        if w < races * 0.85:
            gaps.append(f"{y}年: 1着確定が不足（{w} / {races} レース行）")
        if int(r["with_tansho_pay"] or 0) < w * 0.9 and w > 0:
            gaps.append(
                f"{y}年: 払戻(tansho)不足（{int(r['with_tansho_pay'] or 0)} / 1着確定{w}）"
            )

    print()
    if gaps:
        print("【不足の要点】")
        for g in gaps:
            print(f"  - {g}")
    else:
        print("【要点】年別の大きな不足は検出されませんでした。")

    print()
    print("次の手順: docs/ops_data_backfill_and_repredict.md")


if __name__ == "__main__":
    main()