#!/usr/bin/env python3
"""Import odds_o1_*.db / odds_o2_*.db (o1_to_sqlite / o2_to_sqlite output) into MySQL."""
from __future__ import annotations

import argparse
import os
import re
import sqlite3
import sys
from datetime import date

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

from collectors.db_util import connect  # noqa: E402

JRA_JYO = {"01", "02", "03", "04", "05", "06", "07", "08", "09", "10"}
COMMIT_EVERY = 2000


def _parse_race_date(year_s: str, md_s: str) -> date | None:
    y = str(year_s).strip()
    md = re.sub(r"\D", "", str(md_s).strip()).zfill(4)
    if len(y) != 4 or len(md) != 4:
        return None
    try:
        return date(int(y), int(md[:2]), int(md[2:]))
    except ValueError:
        return None


def _source_cname(rd: date, jyo: str, kai: int | None, nichi: int | None, race_number: int) -> str:
    return f"jrvltsql:{rd.isoformat()}:{jyo}:{kai}:{nichi}:{race_number}"


def _find_race_id(cur, rd: date, jyo: str, kai: int | None, nichi: int | None, race_number: int) -> int | None:
    cur.execute("SELECT id FROM races WHERE source_cname=%s LIMIT 1", (_source_cname(rd, jyo, kai, nichi, race_number),))
    row = cur.fetchone()
    if row:
        return int(row["id"])
    cur.execute(
        """
        SELECT r.id FROM races r
        JOIN tracks t ON t.id = r.track_id
        WHERE r.circuit='JRA' AND r.race_date=%s AND t.code=%s AND r.race_number=%s
        LIMIT 1
        """,
        (rd, jyo, race_number),
    )
    row = cur.fetchone()
    return int(row["id"]) if row else None


def _kumi_to_combo(kumi: str) -> str | None:
    k = str(kumi).strip()
    if len(k) != 4 or not k.isdigit():
        return None
    a, b = int(k[:2]), int(k[2:])
    if a <= 0 or b <= 0:
        return None
    return f"{min(a, b)}-{max(a, b)}"


def _race_row(cur_sqlite: sqlite3.Connection, race_key: str) -> sqlite3.Row | None:
    return cur_sqlite.execute(
        "SELECT year, monthday, jyo_cd, kai, nichi, race_num FROM odds_races WHERE race_key=?",
        (race_key,),
    ).fetchone()


def import_o1(path: str, mysql_cur, *, from_year: int, to_year: int, stats: dict) -> None:
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    conn.row_factory = sqlite3.Row
    cleared: set[int] = set()

    for row in conn.execute(
        """
        SELECT t.race_key, t.umaban, t.odds, t.ninki,
               r.year, r.monthday, r.jyo_cd, r.kai, r.nichi, r.race_num
        FROM odds_tansho t
        JOIN odds_races r ON r.race_key = t.race_key
        WHERE CAST(r.year AS INTEGER) >= ? AND CAST(r.year AS INTEGER) <= ?
        ORDER BY r.year, r.monthday, r.jyo_cd, r.race_num, t.umaban
        """,
        (from_year, to_year),
    ):
        stats["o1_rows"] += 1
        y = int(row["year"])
        jyo = str(row["jyo_cd"]).zfill(2)
        if jyo not in JRA_JYO:
            stats["o1_skip"] += 1
            continue
        rd = _parse_race_date(row["year"], row["monthday"])
        if not rd:
            stats["o1_skip"] += 1
            continue
        try:
            race_number = int(row["race_num"])
            umaban = int(row["umaban"])
        except (TypeError, ValueError):
            stats["o1_skip"] += 1
            continue
        kai = int(row["kai"]) if row["kai"] not in (None, "") else None
        nichi = int(row["nichi"]) if row["nichi"] not in (None, "") else None
        race_id = _find_race_id(mysql_cur, rd, jyo, kai, nichi, race_number)
        if not race_id:
            stats["o1_no_race"] += 1
            continue
        odds = float(row["odds"])
        if odds <= 0:
            stats["o1_skip"] += 1
            continue
        pop = int(row["ninki"]) if row["ninki"] is not None else None

        if race_id not in cleared:
            mysql_cur.execute(
                "DELETE FROM odds_lines WHERE race_id=%s AND odds_type IN ('tansho','umaren')",
                (race_id,),
            )
            cleared.add(race_id)
            stats["o1_races"] += 1

        mysql_cur.execute(
            """
            INSERT INTO odds_lines (race_id, odds_type, combination, min_odds, max_odds, popularity, source_record_type)
            VALUES (%s,'tansho',%s,%s,%s,%s,'O1')
            ON DUPLICATE KEY UPDATE
              min_odds=VALUES(min_odds), max_odds=VALUES(max_odds),
              popularity=VALUES(popularity), source_record_type=VALUES(source_record_type)
            """,
            (race_id, str(umaban), odds, odds, pop),
        )
        stats["o1_upsert"] += 1
        mysql_cur.execute(
            "UPDATE race_entries SET odds_win=%s WHERE race_id=%s AND horse_number=%s",
            (odds, race_id, umaban),
        )
        if stats["o1_rows"] % COMMIT_EVERY == 0:
            mysql_cur.connection.commit()

    conn.close()


def import_o2(path: str, mysql_cur, *, from_year: int, to_year: int, stats: dict) -> None:
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    conn.row_factory = sqlite3.Row
    touched: set[int] = set()

    for row in conn.execute(
        """
        SELECT u.race_key, u.kumi, u.odds, u.ninki,
               r.year, r.monthday, r.jyo_cd, r.kai, r.nichi, r.race_num
        FROM odds_umaren u
        JOIN odds_races r ON r.race_key = u.race_key
        WHERE CAST(r.year AS INTEGER) >= ? AND CAST(r.year AS INTEGER) <= ?
        ORDER BY r.year, r.monthday, r.jyo_cd, r.race_num, u.kumi
        """,
        (from_year, to_year),
    ):
        stats["o2_rows"] += 1
        jyo = str(row["jyo_cd"]).zfill(2)
        if jyo not in JRA_JYO:
            stats["o2_skip"] += 1
            continue
        rd = _parse_race_date(row["year"], row["monthday"])
        if not rd:
            stats["o2_skip"] += 1
            continue
        try:
            race_number = int(row["race_num"])
        except (TypeError, ValueError):
            stats["o2_skip"] += 1
            continue
        combo = _kumi_to_combo(row["kumi"])
        if not combo:
            stats["o2_skip"] += 1
            continue
        kai = int(row["kai"]) if row["kai"] not in (None, "") else None
        nichi = int(row["nichi"]) if row["nichi"] not in (None, "") else None
        race_id = _find_race_id(mysql_cur, rd, jyo, kai, nichi, race_number)
        if not race_id:
            stats["o2_no_race"] += 1
            continue
        odds = float(row["odds"])
        if odds <= 0:
            stats["o2_skip"] += 1
            continue
        pop = int(row["ninki"]) if row["ninki"] is not None else None

        mysql_cur.execute(
            """
            INSERT INTO odds_lines (race_id, odds_type, combination, min_odds, max_odds, popularity, source_record_type)
            VALUES (%s,'umaren',%s,%s,%s,%s,'O2')
            ON DUPLICATE KEY UPDATE
              min_odds=VALUES(min_odds), max_odds=VALUES(max_odds),
              popularity=VALUES(popularity), source_record_type=VALUES(source_record_type)
            """,
            (race_id, combo, odds, odds, pop),
        )
        stats["o2_upsert"] += 1
        touched.add(race_id)
        if len(touched) % 500 == 0:
            stats["o2_races"] = len(touched)
        if stats["o2_rows"] % COMMIT_EVERY == 0:
            mysql_cur.connection.commit()

    stats["o2_races"] = len(touched)
    conn.close()


def main() -> None:
    ap = argparse.ArgumentParser()
    ap.add_argument("--o1", required=True, help="odds_o1_2020_2026.db path")
    ap.add_argument("--o2", required=True, help="odds_o2_2020_2026.db path")
    ap.add_argument("--from-year", type=int, default=2020)
    ap.add_argument("--to-year", type=int, default=2026)
    args = ap.parse_args()

    for p in (args.o1, args.o2):
        if not os.path.isfile(p):
            raise SystemExit(f"not found: {p}")

    conn = connect()
    conn.autocommit(False)
    cur = conn.cursor()

    stats: dict = {
        "o1_rows": 0,
        "o1_races": 0,
        "o1_upsert": 0,
        "o1_no_race": 0,
        "o1_skip": 0,
        "o2_rows": 0,
        "o2_races": 0,
        "o2_upsert": 0,
        "o2_no_race": 0,
        "o2_skip": 0,
    }

    print("=== import O1 tansho ===")
    import_o1(args.o1, cur, from_year=args.from_year, to_year=args.to_year, stats=stats)
    conn.commit()

    print("=== import O2 umaren ===")
    import_o2(args.o2, cur, from_year=args.from_year, to_year=args.to_year, stats=stats)
    conn.commit()
    conn.close()

    print("=== stats ===")
    for k, v in stats.items():
        print(f"  {k}: {v}")


if __name__ == "__main__":
    main()