"""
Importa il nome dell'arbitro nella tabella matches, dalle colonne Referee dei
CSV football-data già scaricati (disponibile solo per la Premier League).

Aggiunge la colonna matches.referee se manca. Idempotente.

Uso:
    python scripts/import_referees.py
"""

import argparse
import sys
from pathlib import Path

import pandas as pd
import pymysql

import config

PROJECT_ROOT = Path(__file__).resolve().parent.parent
RAW_DIR = PROJECT_ROOT / "data" / "raw" / "football-data"


def load_referee_rows() -> pd.DataFrame:
    frames = []
    for path in sorted(RAW_DIR.glob("*_E0.csv")):
        encoding = "utf-8-sig" if path.read_bytes()[:3] == b"\xef\xbb\xbf" else "latin-1"
        df = pd.read_csv(path, encoding=encoding, on_bad_lines="skip")
        df.columns = df.columns.str.strip()
        if "Referee" not in df.columns:
            print(f"  [salto] {path.name}: nessuna colonna Referee")
            continue
        df = df.dropna(subset=["Date", "HomeTeam", "AwayTeam", "Referee"])
        out = df[["Date", "HomeTeam", "AwayTeam", "Referee"]].copy()
        out["Date"] = pd.to_datetime(out["Date"], dayfirst=True,
                                     format="mixed", errors="coerce")
        out = out.dropna(subset=["Date"])
        # normalizzazione: spazi multipli/finali ("A Taylor " -> "A Taylor")
        out["Referee"] = out["Referee"].str.replace(r"\s+", " ", regex=True).str.strip()
        frames.append(out)
        print(f"  [ok] {path.name}: {len(out)} partite con arbitro")
    return pd.concat(frames, ignore_index=True)


def main() -> int:
    parser = argparse.ArgumentParser(description="Importa arbitri (Premier League) nel DB")
    config.add_db_args(parser)
    args = parser.parse_args()

    refs = load_referee_rows()
    print(f"Totale: {len(refs)} partite, {refs['Referee'].nunique()} arbitri distinti")

    conn = pymysql.connect(host=args.host, port=args.port, user=args.user,
                           password=args.password, database="tigertips")
    cur = conn.cursor()
    cur.execute("""
        SELECT COUNT(*) FROM information_schema.columns
        WHERE table_schema = 'tigertips' AND table_name = 'matches'
          AND column_name = 'referee'
    """)
    if cur.fetchone()[0] == 0:
        cur.execute("ALTER TABLE matches ADD COLUMN referee VARCHAR(50) NULL AFTER away_xg")
        conn.commit()
        print("Colonna matches.referee aggiunta")

    cur.execute("""
        SELECT m.id, m.match_date, h.name, a.name
        FROM matches m
        JOIN leagues l ON l.id = m.league_id
        JOIN teams h ON h.id = m.home_team_id
        JOIN teams a ON a.id = m.away_team_id
        WHERE l.code = 'E0'
    """)
    match_ids = {(d, hn, an): mid for mid, d, hn, an in cur.fetchall()}

    rows, unmatched = [], 0
    for r in refs.itertuples(index=False):
        mid = match_ids.get((r.Date.date(), r.HomeTeam, r.AwayTeam))
        if mid is None:
            unmatched += 1
            continue
        rows.append((r.Referee, mid))

    for start in range(0, len(rows), 1000):
        cur.executemany("UPDATE matches SET referee = %s WHERE id = %s",
                        rows[start:start + 1000])
        conn.commit()

    cur.execute("SELECT COUNT(*) FROM matches WHERE referee IS NOT NULL")
    print(f"Partite con arbitro nel DB: {cur.fetchone()[0]} "
          f"(non abbinate: {unmatched})")
    conn.close()
    return 0


if __name__ == "__main__":
    sys.exit(main())