# -*- coding: utf-8 -*-
"""keiba.db NL_O1 / NL_O2 coverage. ASCII-only for cmd."""
from __future__ import annotations

import sqlite3
import sys

DB = sys.argv[1] if len(sys.argv) > 1 else r"data\keiba.db"
YEAR_FROM = sys.argv[2] if len(sys.argv) > 2 else "2020"

conn = sqlite3.connect(DB)
tables = {r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")}

print("=== keiba.db odds check ===")
print("db:", DB)
print("year_from:", YEAR_FROM)
print("")

if "NL_RA" in tables:
    ra = conn.execute(
        "SELECT Year, COUNT(*) FROM NL_RA WHERE Year >= ? GROUP BY Year ORDER BY Year",
        (YEAR_FROM,),
    ).fetchall()
    print("--- NL_RA (race count) ---")
    for y, n in ra:
        print(f"  {y}: {n}")
    print("")

rk = "Year || JyoCD || MonthDay || RaceNum"

if "NL_O1" in tables:
    print("--- NL_O1 (confirmed tansho / TanOdds) ---")
    for y, n_all, n_odds in conn.execute(
        f"""
        SELECT Year,
               COUNT(DISTINCT {rk}),
               COUNT(DISTINCT CASE WHEN TanOdds IS NOT NULL AND CAST(TanOdds AS REAL) > 0
                    THEN {rk} END)
        FROM NL_O1 WHERE Year >= ?
        GROUP BY Year ORDER BY Year
        """,
        (YEAR_FROM,),
    ):
        print(f"  {y}: races={n_all}  with_tanodds={n_odds}")
    tot = conn.execute(
        f"""
        SELECT COUNT(DISTINCT CASE WHEN TanOdds IS NOT NULL AND CAST(TanOdds AS REAL) > 0
               THEN {rk} END) FROM NL_O1 WHERE Year >= ?
        """,
        (YEAR_FROM,),
    ).fetchone()[0]
    print(f"  TOTAL with_tanodds: {tot}")
    print("")
else:
    print("NL_O1: not found")
    print("")

if "NL_O2" in tables:
    print("--- NL_O2 (confirmed umaren / Odds) ---")
    for y, n_all, n_odds in conn.execute(
        f"""
        SELECT Year,
               COUNT(DISTINCT {rk}),
               COUNT(DISTINCT CASE WHEN Odds IS NOT NULL AND CAST(Odds AS REAL) > 0
                    THEN {rk} END)
        FROM NL_O2 WHERE Year >= ?
        GROUP BY Year ORDER BY Year
        """,
        (YEAR_FROM,),
    ):
        print(f"  {y}: races={n_all}  with_umaren_odds={n_odds}")
    tot = conn.execute(
        f"""
        SELECT COUNT(DISTINCT CASE WHEN Odds IS NOT NULL AND CAST(Odds AS REAL) > 0
               THEN {rk} END) FROM NL_O2 WHERE Year >= ?
        """,
        (YEAR_FROM,),
    ).fetchone()[0]
    print(f"  TOTAL with_umaren_odds: {tot}")
else:
    print("NL_O2: not found")

conn.close()