#!/usr/bin/env python3
"""keiba.db の NL_O1（確定単勝オッズ）件数を年別に表示。sqlite3 不要。"""
from __future__ import annotations

import argparse
import os
import sqlite3
import sys

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


def main() -> None:
    ap = argparse.ArgumentParser()
    ap.add_argument("--sqlite", default="/root/keiba.db", help="keiba.db のパス")
    args = ap.parse_args()
    if not os.path.isfile(args.sqlite):
        raise SystemExit(f"not found: {args.sqlite}")

    conn = sqlite3.connect(f"file:{args.sqlite}?mode=ro", uri=True)
    tables = {r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    o1 = "NL_O1" if "NL_O1" in tables else ("RT_O1" if "RT_O1" in tables else None)
    if not o1:
        print("NL_O1 / RT_O1 がありません。tables:", sorted(tables)[:20])
        return

    print(f"=== {args.sqlite} / {o1} ===")
    total = conn.execute(f"SELECT COUNT(*) FROM {o1}").fetchone()[0]
    print(f"rows_total: {total}")

    rows = conn.execute(
        f"""
        SELECT Year,
               COUNT(DISTINCT JyoCD || '-' || MonthDay || '-' || RaceNum) AS races_all,
               COUNT(DISTINCT CASE WHEN TanOdds IS NOT NULL AND CAST(TanOdds AS REAL) > 0
                    THEN JyoCD || '-' || MonthDay || '-' || RaceNum END) AS races_with_odds
        FROM {o1}
        GROUP BY Year
        ORDER BY Year
        """
    ).fetchall()
    print(f"{'Year':>6}  {'races_all':>10}  {'with_odds':>10}")
    for y, all_r, with_o in rows:
        print(f"{y:>6}  {all_r:>10}  {with_o:>10}")

    ra = "NL_RA" if "NL_RA" in tables else None
    if ra:
        ra_rows = conn.execute(
            f"""
            SELECT Year, COUNT(*) FROM {ra}
            WHERE Year >= 2020 GROUP BY Year ORDER BY Year
            """
        ).fetchall()
        print("\n=== NL_RA (レース数・参考) ===")
        for y, c in ra_rows:
            print(f"{y}: {c}")

    conn.close()


if __name__ == "__main__":
    main()