# -*- coding: utf-8 -*-
"""
O2 raw (o2_raw_all.txt) -> SQLite (umaren confirmed odds).

Usage: python o2_to_sqlite.py <input.txt> <output.db>
Example: python o2_to_sqlite.py o2_raw_all.txt odds_o2.db

Layout (jrvltsql o2_parser.py):
  header 40 bytes
  from offset 40: kumi(4) + odds(6) + ninki(3) = 13 bytes per combo
  last 13 bytes: Vote
"""
from __future__ import annotations

import sqlite3
import sys
from collections import Counter

HDR = 40
BPC = 13
VOTE_TAIL = 13


def _parse_combos(raw: str) -> list[tuple[str, float, int | None]]:
    if len(raw) < HDR + BPC + VOTE_TAIL:
        return []
    body_end = len(raw) - VOTE_TAIL
    out: list[tuple[str, float, int | None]] = []
    for pos in range(HDR, body_end, BPC):
        item = raw[pos : pos + BPC]
        if len(item) < BPC:
            break
        kumi = item[0:4].strip()
        odds_s = item[4:10].strip()
        ninki_s = item[10:13].strip()
        if not kumi or kumi.strip("0") == "":
            continue
        if not odds_s or odds_s.strip("0") == "" or "*" in odds_s or "-" in odds_s:
            continue
        if not odds_s.isdigit():
            continue
        v = int(odds_s) / 10.0
        if v <= 0:
            continue
        nk = int(ninki_s) if ninki_s.isdigit() else None
        out.append((kumi, v, nk))
    return out


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, raw_len INTEGER);
    CREATE TABLE IF NOT EXISTS odds_umaren (
      race_key TEXT, kumi TEXT, odds REAL, ninki INTEGER,
      PRIMARY KEY (race_key, kumi));
    """
    )

    n_rec = 0
    n_skip_len = 0
    len_ctr: Counter[int] = Counter()
    with open(src, encoding="ascii", errors="replace") as f:
        for line in f:
            r = line.rstrip("\r\n")
            if not r.startswith("O2"):
                continue
            len_ctr[len(r)] += 1
            combos = _parse_combos(r)
            if not combos and len(r) < HDR + BPC:
                n_skip_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,
                    len(r),
                ),
            )
            for kumi, odds, nk in combos:
                c.execute(
                    "INSERT OR REPLACE INTO odds_umaren VALUES (?,?,?,?)",
                    (key, kumi, odds, nk),
                )
            n_rec += 1

    con.commit()
    races_um = c.execute("SELECT COUNT(DISTINCT race_key) FROM odds_umaren").fetchone()[0]
    print(f"records: {n_rec}")
    print(f"skipped short: {n_skip_len}")
    if len_ctr:
        print("raw lengths (top3):", len_ctr.most_common(3))
    print(f"  odds_races: {c.execute('SELECT COUNT(*) FROM odds_races').fetchone()[0]} rows")
    print(f"  odds_umaren: {c.execute('SELECT COUNT(*) FROM odds_umaren').fetchone()[0]} rows")
    print(f"  races_with_umaren: {races_um}")
    con.close()


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