"""Backfill + forward-fill enrichment runner.

Reads targets from tvviewers (SELECT only), fetches Wikipedia, parses with
Claude, and writes fixtures + per-row enrichment into the `crystal` database.

Usage:
    python3 worker/run_enrichment.py --limit 10          # pilot
    python3 worker/run_enrichment.py --limit 500         # full backfill
    python3 worker/run_enrichment.py --limit 50 --min-year 2024
"""
from __future__ import annotations

import argparse
import json
import sys
from pathlib import Path

import pymysql

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))
from engine.wiki_enrich import enrich_event_season  # noqa: E402

MARKETS = ("India", "United Kingdom", "Germany", "Italy",
           "South Africa", "United States")

# Placeholder "events" that are not matches -- Wikipedia cannot help these.
PLACEHOLDER = "NON_EVENT|UNKNOWN|UNCLASSIFIED|MAGAZINE|STUDIO|ARCHIVE|WRESTLING"

TARGETS_SQL = f"""
SELECT event_name, YEAR(prog_date) AS season_year,
       COUNT(*) AS rows_total,
       SUM(sports_teams IS NULL) AS rows_missing_teams,
       ROUND(SUM(ama_000)) AS total_ama
FROM global_sports
WHERE ama_000 > 0
  AND event_name IS NOT NULL AND TRIM(event_name) <> ''
  AND event_name NOT REGEXP %s
  AND country IN ({','.join(['%s'] * len(MARKETS))})
  AND YEAR(prog_date) >= %s
GROUP BY event_name, YEAR(prog_date)
HAVING rows_missing_teams > 0
ORDER BY total_ama DESC
LIMIT %s
"""


def ref_conn(cfg):
    """Read-only connection to tvviewers."""
    return pymysql.connect(
        host=cfg.get("ref_db_host") or cfg["db_host"],
        port=int(cfg.get("ref_db_port") or 3306),
        user=cfg.get("ref_db_user") or cfg["db_user"],
        password=cfg.get("ref_db_pass") or cfg["db_pass"],
        database="tvviewers", charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor)


def app_conn(cfg):
    """Read-write connection to the crystal application schema.

    Local MySQL resolves a TCP connection from 127.0.0.1 to `user@localhost`,
    which is a different (less privileged) account than the `user@%` the socket
    resolves to. Prefer the socket for local connections so the grants match.
    """
    kw = dict(user=cfg["db_user"], password=cfg["db_pass"],
              database=cfg.get("db_name", "crystal"), charset="utf8mb4",
              autocommit=True, cursorclass=pymysql.cursors.DictCursor)
    sock = cfg.get("db_socket", "/var/run/mysqld/mysqld.sock")
    if cfg["db_host"] in ("127.0.0.1", "localhost") and Path(sock).exists():
        return pymysql.connect(unix_socket=sock, **kw)
    return pymysql.connect(host=cfg["db_host"], port=int(cfg.get("db_port", 3306)), **kw)


def main() -> None:
    ap = argparse.ArgumentParser()
    ap.add_argument("--config", default=str(Path(__file__).resolve().parent / "config.json"))
    ap.add_argument("--limit", type=int, default=10)
    ap.add_argument("--min-year", type=int, default=2019)
    ap.add_argument("--redo", action="store_true", help="re-fetch already-logged event-seasons")
    args = ap.parse_args()

    cfg = json.loads(Path(args.config).read_text())
    import anthropic
    client = anthropic.Anthropic()

    ref, app = ref_conn(cfg), app_conn(cfg)
    with ref.cursor() as c:
        c.execute(TARGETS_SQL, (PLACEHOLDER, *MARKETS, args.min_year, args.limit))
        targets = c.fetchall()
    print(f"{len(targets)} event-seasons selected\n", flush=True)

    for i, t in enumerate(targets, 1):
        ev, yr = t["event_name"], t["season_year"]
        with app.cursor() as c:
            c.execute("SELECT status FROM wiki_fetch_log WHERE event_name=%s AND season_year=%s",
                      (ev, yr))
            if c.fetchone() and not args.redo:
                print(f"[{i}/{len(targets)}] skip (already logged): {ev} {yr}", flush=True)
                continue

        try:
            fixtures, status, note = enrich_event_season(client, ev, yr)
        except Exception as e:                       # noqa: BLE001
            fixtures, status, note = [], "error", f"{type(e).__name__}: {e}"[:480]

        with app.cursor() as c:
            c.execute("""INSERT INTO wiki_fetch_log
                         (event_name, season_year, url, status, fixtures_found, note)
                         VALUES (%s,%s,%s,%s,%s,%s)
                         ON DUPLICATE KEY UPDATE status=VALUES(status),
                           fixtures_found=VALUES(fixtures_found), note=VALUES(note)""",
                      (ev, yr, note[:480], status, len(fixtures), note[:480]))
            for f in fixtures:
                if not (f.get("team1") and f.get("team2")):
                    continue
                c.execute("""INSERT INTO wiki_fixtures
                             (event_name, season_year, match_date, team1, team2,
                              stage, match_no, source_url, confidence)
                             VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                          (ev, yr, f.get("match_date") or None, f["team1"], f["team2"],
                           f.get("stage") or "UNKNOWN", f.get("match_no") or None,
                           note[:480], 0.800))

        print(f"[{i}/{len(targets)}] {ev} {yr}: {status}, {len(fixtures)} fixtures "
              f"({t['rows_missing_teams']:,} rows need teams)", flush=True)

    join_back(ref, app)
    print("\nDONE", flush=True)


def join_back(ref, app) -> None:
    """Match global_sports rows to fixtures on (event_name, date). Read-only on
    tvviewers; writes only to crystal.global_sports_enrichment."""
    with app.cursor() as c:
        c.execute("SELECT id, event_name, season_year, match_date, team1, team2, stage "
                  "FROM wiki_fixtures WHERE match_date IS NOT NULL")
        fixtures = c.fetchall()
    if not fixtures:
        print("\nno dated fixtures to join"); return

    by_key: dict[tuple, list] = {}
    for f in fixtures:
        by_key.setdefault((f["event_name"], f["match_date"]), []).append(f)

    events = sorted({f["event_name"] for f in fixtures})
    ph = ",".join(["%s"] * len(events))
    with ref.cursor() as c:
        c.execute(f"""SELECT global_id, event_name, prog_date, country
                      FROM global_sports
                      WHERE ama_000 > 0 AND event_name IN ({ph})
                        AND (sports_teams IS NULL OR match_level IS NULL)""", events)
        rows = c.fetchall()

    written = 0
    with app.cursor() as c:
        for r in rows:
            cand = by_key.get((r["event_name"], r["prog_date"]))
            if not cand:
                continue
            # One fixture that day -> unambiguous. Several -> record the stage
            # only; the teams are genuinely ambiguous without kickoff times.
            if len(cand) == 1:
                f = cand[0]
                teams = f"{f['team1']}|{f['team2']}"
                conf = 0.850
            else:
                f = cand[0]
                teams, conf = None, 0.400
            c.execute("""INSERT INTO global_sports_enrichment
                         (global_id, event_name, prog_date, teams_enriched,
                          match_level_enriched, fixture_id, method, confidence)
                         VALUES (%s,%s,%s,%s,%s,%s,'wiki',%s)
                         ON DUPLICATE KEY UPDATE teams_enriched=VALUES(teams_enriched),
                           match_level_enriched=VALUES(match_level_enriched),
                           fixture_id=VALUES(fixture_id), confidence=VALUES(confidence)""",
                      (r["global_id"], r["event_name"], r["prog_date"], teams,
                       f["stage"], f["id"], conf))
            written += 1
    print(f"\njoined {written:,} global_sports rows "
          f"(from {len(rows):,} candidates, {len(fixtures):,} fixtures)")


if __name__ == "__main__":
    main()