# -*- coding: utf-8 -*-
"""NL_O1 schema + sample rows (TanOdds filled vs empty). ASCII-safe prints."""
from __future__ import annotations

import sqlite3
import sys

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

conn = sqlite3.connect(DB)
conn.row_factory = sqlite3.Row

print("=== NL_O1 inspect ===")
print("db:", DB)
print("year:", YEAR)
print("")

cols = [r[1] for r in conn.execute("PRAGMA table_info(NL_O1)")]
print("columns (%d):" % len(cols))
for c in cols:
    print(" ", c)
print("")

rk = "Year||JyoCD||MonthDay||RaceNum"
total = conn.execute("SELECT COUNT(*) FROM NL_O1 WHERE Year=?", (YEAR,)).fetchone()[0]
races = conn.execute(
    f"SELECT COUNT(DISTINCT {rk}) FROM NL_O1 WHERE Year=?", (YEAR,)
).fetchone()[0]
races_odds = conn.execute(
    f"SELECT COUNT(DISTINCT {rk}) FROM NL_O1 WHERE Year=? "
    "AND TanOdds IS NOT NULL AND CAST(TanOdds AS REAL)>0",
    (YEAR,),
).fetchone()[0]
rows_odds = conn.execute(
    "SELECT COUNT(*) FROM NL_O1 WHERE Year=? "
    "AND TanOdds IS NOT NULL AND CAST(TanOdds AS REAL)>0",
    (YEAR,),
).fetchone()[0]
print("rows:", total)
print("races_distinct:", races)
print("races_with_tanodds:", races_odds)
print("rows_with_tanodds:", rows_odds)
print("")

print("DataKubun breakdown:")
for kubun, n in conn.execute(
    "SELECT DataKubun, COUNT(*) FROM NL_O1 WHERE Year=? GROUP BY DataKubun ORDER BY DataKubun",
    (YEAR,),
):
    ro = conn.execute(
        f"SELECT COUNT(DISTINCT {rk}) FROM NL_O1 WHERE Year=? AND DataKubun=? "
        "AND TanOdds IS NOT NULL AND CAST(TanOdds AS REAL)>0",
        (YEAR, kubun),
    ).fetchone()[0]
    print(f"  kubun={kubun!s:>3} rows={n:>8}  races_with_odds={ro}")
print("")

# sample: race WITH tanodds
print("--- sample race WITH TanOdds (1 race, horses) ---")
row = conn.execute(
    f"SELECT Year,JyoCD,MonthDay,RaceNum FROM NL_O1 WHERE Year=? "
    "AND TanOdds IS NOT NULL AND CAST(TanOdds AS REAL)>0 LIMIT 1",
    (YEAR,),
).fetchone()
if row:
    y, j, md, rn = row
    for r in conn.execute(
        "SELECT Umaban,DataKubun,TanOdds,TanNinki,FukuOddsLow,FukuOddsHigh "
        "FROM NL_O1 WHERE Year=? AND JyoCD=? AND MonthDay=? AND RaceNum=? ORDER BY Umaban",
        (y, j, md, rn),
    ):
        print(dict(r))
else:
    print("(none)")
print("")

# sample: race WITHOUT tanodds (any horse)
print("--- sample race WITHOUT TanOdds (1 race, horses) ---")
row = conn.execute(
    f"SELECT Year,JyoCD,MonthDay,RaceNum FROM NL_O1 WHERE Year=? "
    f"GROUP BY Year,JyoCD,MonthDay,RaceNum "
    "HAVING MAX(CASE WHEN TanOdds IS NOT NULL AND CAST(TanOdds AS REAL)>0 THEN 1 ELSE 0 END)=0 "
    "LIMIT 1",
    (YEAR,),
).fetchone()
if row:
    y, j, md, rn = row
    print("race:", y, j, md, rn)
    for r in conn.execute(
        "SELECT Umaban,DataKubun,TanOdds,TanNinki,FukuOddsLow,FukuOddsHigh "
        "FROM NL_O1 WHERE Year=? AND JyoCD=? AND MonthDay=? AND RaceNum=? ORDER BY Umaban LIMIT 15",
        (y, j, md, rn),
    ):
        print(dict(r))
else:
    print("(none)")

conn.close()