"""Diagnose why a tournament bracket is "stuck" on the website.

The website reads tss_tournaments.db live, so a bracket that never updates means
the *scanner* stopped writing fresh rows for that tournament. This script proves
where the freeze is by comparing, for each tournament:

  - what is STORED in tss_tournaments.db (and how stale it is),
  - whether needs_scan() will ever re-scan it (and why not),
  - what a FRESH live scan from the TSS API would produce right now,
  - the diff (new battles / changed match statuses the DB is missing).

If the live scan has battles/matches the DB lacks AND needs_scan() is False, the
freeze is in the scanner (a prematurely-stored "finished" status). If the live
scan matches the DB, the data source is current and the staleness is downstream
(web cache / frontend not refreshing).

Usage:
  # Auto-detect frozen tournaments: games arrived after the last scan.
  python TSSBOT/scripts/diagnose_tournament_freeze.py

  # Diagnose specific tournament ids.
  python TSSBOT/scripts/diagnose_tournament_freeze.py 25003 25034

  # Tune how many auto-detected tournaments to deep-scan (default 5).
  python TSSBOT/scripts/diagnose_tournament_freeze.py --top 10
"""
import argparse
import asyncio
import pathlib
import sqlite3
import sys
import time
from typing import Dict, List, Optional, Tuple

ROOT = pathlib.Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT))
sys.path.insert(0, str(ROOT.parent / "SHARED"))

# Match start_bot.py/tss_ws.py: load TSSBOT/.env before importing storage, which
# resolves STORAGE_VOL_PATH at import time.
try:
    from dotenv import load_dotenv
    load_dotenv(dotenv_path=ROOT / ".env")
except Exception:
    pass

from BOT.storage import TSS_BATTLES_DB_PATH  # noqa: E402
from BOT.tss_tournaments import (  # noqa: E402
    TSS_TOURNAMENTS_DB_PATH,
    RESCAN_INTERVAL_SECONDS,
    RESCAN_END_BUFFER_SECONDS,
    build_scan_sync,
    needs_scan,
)


def _age(unix: Optional[int], now: int) -> str:
    if not unix:
        return "never"
    secs = max(0, now - int(unix))
    if secs < 90:
        return f"{secs}s ago"
    if secs < 5400:
        return f"{secs // 60}m ago"
    if secs < 172800:
        return f"{secs // 3600}h ago"
    return f"{secs // 86400}d ago"


def stored_state(tid: int) -> Optional[Dict]:
    """Stored tournaments row + child-row counts, or None if never scanned."""
    if not TSS_TOURNAMENTS_DB_PATH.exists():
        return None
    with sqlite3.connect(f"file:{TSS_TOURNAMENTS_DB_PATH}?mode=ro", uri=True) as conn:
        row = conn.execute(
            "SELECT name, status, date_end, scanned_unix, match_count "
            "FROM tournaments WHERE tournament_id = ?",
            (tid,),
        ).fetchone()
        if row is None:
            return None
        status_counts = dict(
            conn.execute(
                "SELECT status, COUNT(*) FROM tournament_matches "
                "WHERE tournament_id = ? GROUP BY status",
                (tid,),
            ).fetchall()
        )
        battle_count = conn.execute(
            "SELECT COUNT(*) FROM tournament_battles WHERE tournament_id = ?",
            (tid,),
        ).fetchone()[0]
        battle_hexes = {
            r[0]
            for r in conn.execute(
                "SELECT session_hex FROM tournament_battles WHERE tournament_id = ?",
                (tid,),
            ).fetchall()
        }
    return {
        "name": row[0],
        "status": row[1],
        "date_end": row[2],
        "scanned_unix": row[3],
        "match_count": row[4],
        "status_counts": status_counts,
        "battle_count": battle_count,
        "battle_hexes": battle_hexes,
    }


def newest_game_unix(tid: int) -> Optional[int]:
    """endtime_unix of the most recent game we ingested for this tournament."""
    with sqlite3.connect(f"file:{TSS_BATTLES_DB_PATH}?mode=ro", uri=True) as conn:
        row = conn.execute(
            "SELECT MAX(endtime_unix) FROM match_summary WHERE tournament_id = ?",
            (tid,),
        ).fetchone()
    return int(row[0]) if row and row[0] else None


def frozen_candidates() -> List[Tuple[int, Optional[str], int, Optional[int]]]:
    """Tournaments with a game ingested AFTER their last scan — the freeze tell.

    Returns (tid, name, newest_game_unix, scanned_unix) sorted by newest game.
    """
    games = {}
    names = {}
    with sqlite3.connect(f"file:{TSS_BATTLES_DB_PATH}?mode=ro", uri=True) as conn:
        for tid, newest, name in conn.execute(
            "SELECT tournament_id, MAX(endtime_unix), NULLIF(MAX(tournament_name), '') "
            "FROM match_summary WHERE tournament_id IS NOT NULL AND tournament_id > 0 "
            "GROUP BY tournament_id"
        ).fetchall():
            games[int(tid)] = int(newest) if newest else 0
            names[int(tid)] = name

    scanned: Dict[int, int] = {}
    if TSS_TOURNAMENTS_DB_PATH.exists():
        with sqlite3.connect(f"file:{TSS_TOURNAMENTS_DB_PATH}?mode=ro", uri=True) as conn:
            for tid, sunix in conn.execute(
                "SELECT tournament_id, scanned_unix FROM tournaments"
            ).fetchall():
                scanned[int(tid)] = int(sunix) if sunix else 0

    out = []
    for tid, newest in games.items():
        sunix = scanned.get(tid, 0)
        # A game landed after the last scan → the bracket on the site is stale.
        if newest and newest > sunix:
            out.append((tid, names.get(tid), newest, scanned.get(tid)))
    out.sort(key=lambda r: r[2], reverse=True)
    return out


async def diagnose(tid: int, name: Optional[str], now: int) -> None:
    print(f"\n{'=' * 72}\nTournament {tid}" + (f"  ({name})" if name else ""))
    print("=" * 72)

    stored = stored_state(tid)
    newest_game = newest_game_unix(tid)

    if stored is None:
        print("  STORED: never scanned (no row in tss_tournaments.db)")
    else:
        print(f"  STORED: status={stored['status']!r}  "
              f"last scan {_age(stored['scanned_unix'], now)}  "
              f"matches={stored['match_count']}  battles={stored['battle_count']}")
        print(f"          match statuses: {stored['status_counts']}")
        print(f"          date_end {_age(stored['date_end'], now)}")
    print(f"  NEWEST INGESTED GAME: {_age(newest_game, now)}")
    if stored and newest_game and stored["scanned_unix"]:
        if newest_game > stored["scanned_unix"]:
            print("  >> A game arrived AFTER the last scan — the stored bracket is stale.")

    will_rescan = await needs_scan(tid, now=now)
    reason = ""
    if stored is None:
        reason = "never scanned"
    elif stored["status"] == "finished":
        reason = "status=='finished' → needs_scan() is permanently False (FROZEN)"
    elif stored["date_end"] and now > stored["date_end"] + RESCAN_END_BUFFER_SECONDS:
        reason = "past date_end + buffer → re-scan disabled"
    else:
        nxt = (stored["scanned_unix"] or 0) + RESCAN_INTERVAL_SECONDS
        reason = f"interval-gated; next eligible {_age(nxt, now)} ({'due' if nxt <= now else 'waiting'})"
    print(f"  needs_scan(): {will_rescan}  — {reason}")

    print("  LIVE SCAN (fetching from TSS API, not stored)…", flush=True)
    try:
        scan = build_scan_sync(tid, fallback_name=name, now=now)
    except Exception as exc:
        print(f"  LIVE SCAN FAILED: {exc}")
        return

    live_hexes = {b["session_hex"] for b in scan["battles"]}
    live_status_counts: Dict[str, int] = {}
    for m in scan["matches"]:
        live_status_counts[m["status"]] = live_status_counts.get(m["status"], 0) + 1
    print(f"  LIVE:   status={scan['status']!r}  matches={scan['match_count']}  "
          f"battles={len(scan['battles'])}")
    print(f"          match statuses: {live_status_counts}")

    stored_hexes = stored["battle_hexes"] if stored else set()
    new_battles = live_hexes - stored_hexes
    print(f"  DIFF:   live has {len(new_battles)} battle(s) the DB is missing; "
          f"match_count {stored['match_count'] if stored else 0} → {scan['match_count']}")

    if new_battles and not will_rescan:
        print("  VERDICT: FROZEN SCANNER — live data is newer but needs_scan() is "
              "False, so the bracket will never update. Root cause is the scanner.")
    elif new_battles and will_rescan:
        print("  VERDICT: due to re-scan — data source is fine; staleness is timing "
              "or downstream (web cache / frontend not refreshing).")
    elif not new_battles:
        print("  VERDICT: DB already current with live — if the site still looks "
              "stale, the freeze is downstream (web cache / frontend polling).")


async def main() -> None:
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("tournament_ids", nargs="*", type=int,
                        help="tournament ids to diagnose (default: auto-detect frozen ones)")
    parser.add_argument("--top", type=int, default=5,
                        help="how many auto-detected tournaments to deep-scan (default 5)")
    args = parser.parse_args()
    now = int(time.time())

    print(f"battles db:    {TSS_BATTLES_DB_PATH}")
    print(f"tournament db: {TSS_TOURNAMENTS_DB_PATH}")

    if args.tournament_ids:
        targets = [(tid, None) for tid in args.tournament_ids]
    else:
        candidates = frozen_candidates()
        print(f"\nAuto-detected {len(candidates)} tournament(s) with a game ingested "
              f"after their last scan (stale brackets):")
        for tid, name, newest, sunix in candidates[: args.top * 4]:
            print(f"  {tid:>8}  game {_age(newest, now):>10}  "
                  f"scan {_age(sunix, now):>10}  {name or ''}")
        if not candidates:
            print("  (none — no tournament has games newer than its last scan)")
            return
        print(f"\nDeep-scanning the top {min(args.top, len(candidates))}…")
        targets = [(tid, name) for tid, name, _, _ in candidates[: args.top]]

    for tid, name in targets:
        await diagnose(tid, name, now)


if __name__ == "__main__":
    asyncio.run(main())