# -*- coding: utf-8 -*-
"""
O1 raw (o1_raw_all.txt format) -> SQLite.

Usage: python o1_to_sqlite.py <input.txt> <output.db>
Example: python o1_to_sqlite.py o1_raw_all.txt odds_o1_2026.db

Works with normal Python (64-bit OK). No pywin32.
Reviewed against jrvltsql o1_parser offsets (2026-06).
"""
from __future__ import annotations

import sqlite3
import sys

O1_LEN = 960


def num(s: str, div: int = 10) -> float | None:
    s = s.strip()
    return int(s) / div if s.isdigit() and int(s) > 0 else None


def main(src: str, dst: str) -> None:
    con = sqlite3.connect(dst)
    c = con.cursor()
    c.executescript(
        """
    CREATE TABLE IF NOT EXISTS odds_races (
      race_key TEXT PRIMARY KEY, year TEXT, monthday TEXT, jyo_cd TEXT,
      kai TEXT, nichi TEXT, race_num TEXT, data_kubun TEXT,
      toroku_tosu INTEGER, shusso_tosu INTEGER);
    CREATE TABLE IF NOT EXISTS odds_tansho (
      race_key TEXT, umaban INTEGER, odds REAL, ninki INTEGER,
      PRIMARY KEY (race_key, umaban));
    CREATE TABLE IF NOT EXISTS odds_fukusho (
      race_key TEXT, umaban INTEGER, odds_low REAL, odds_high REAL, ninki INTEGER,
      PRIMARY KEY (race_key, umaban));
    CREATE TABLE IF NOT EXISTS odds_wakuren (
      race_key TEXT, kumi TEXT, odds REAL, ninki INTEGER,
      PRIMARY KEY (race_key, kumi));
    """
    )

    n = 0
    skipped_len = 0
    with open(src, encoding="ascii", errors="replace") as f:
        for line in f:
            r = line.rstrip("\r\n")
            if not r.startswith("O1") or len(r) != O1_LEN:
                if r.startswith("O1") and len(r) != O1_LEN:
                    skipped_len += 1
                continue
            key = r[11:27]
            c.execute(
                "INSERT OR REPLACE INTO odds_races VALUES (?,?,?,?,?,?,?,?,?,?)",
                (
                    key,
                    r[11:15],
                    r[15:19],
                    r[19:21],
                    r[21:23],
                    r[23:25],
                    r[25:27],
                    r[2:3],
                    int(r[35:37]) if r[35:37].strip().isdigit() else None,
                    int(r[37:39]) if r[37:39].strip().isdigit() else None,
                ),
            )
            for h in range(28):
                o = 43 + h * 8
                ub, od, nk = r[o : o + 2], r[o + 2 : o + 6], r[o + 6 : o + 8]
                ub, od, nk = ub.strip(), od.strip(), nk.strip()
                if ub.isdigit() and int(ub) > 0 and od.isdigit() and int(od) > 0:
                    c.execute(
                        "INSERT OR REPLACE INTO odds_tansho VALUES (?,?,?,?)",
                        (key, int(ub), int(od) / 10, int(nk) if nk.isdigit() else None),
                    )
            for h in range(28):
                o = 267 + h * 12
                ub, lo, hi, nk = r[o : o + 2], r[o + 2 : o + 6], r[o + 6 : o + 10], r[o + 10 : o + 12]
                ub, lo, hi, nk = ub.strip(), lo.strip(), hi.strip(), nk.strip()
                if ub.isdigit() and int(ub) > 0 and lo.isdigit() and int(lo) > 0:
                    c.execute(
                        "INSERT OR REPLACE INTO odds_fukusho VALUES (?,?,?,?,?)",
                        (key, int(ub), int(lo) / 10, num(hi), int(nk) if nk.isdigit() else None),
                    )
            for k in range(36):
                o = 603 + k * 9
                km, od, nk = r[o : o + 2], r[o + 2 : o + 7], r[o + 7 : o + 9]
                km, od, nk = km.strip(), od.strip(), nk.strip()
                if km.isdigit() and int(km) > 0 and od.isdigit() and int(od) > 0:
                    c.execute(
                        "INSERT OR REPLACE INTO odds_wakuren VALUES (?,?,?,?)",
                        (key, km, int(od) / 10, int(nk) if nk.isdigit() else None),
                    )
            n += 1
    con.commit()
    print(f"records: {n}")
    if skipped_len:
        print(f"skipped (len!={O1_LEN}): {skipped_len}")
    for t in ("odds_races", "odds_tansho", "odds_fukusho", "odds_wakuren"):
        print(f"  {t}: {c.execute('SELECT COUNT(*) FROM ' + t).fetchone()[0]} rows")
    races_tan = c.execute("SELECT COUNT(DISTINCT race_key) FROM odds_tansho").fetchone()[0]
    print(f"  races_with_tansho: {races_tan}")
    con.close()


if __name__ == "__main__":
    if len(sys.argv) != 3:
        print(__doc__)
        sys.exit(1)
    main(sys.argv[1], sys.argv[2])