"""
Scarica le statistiche giocatore da Understat (goals, xG, assist, xA, minuti)
e le carica nella tabella player_stats del DB. Servono al layer deterministico
dell'agente formazioni: la quota di contributo offensivo di un assente.

I nomi squadra vengono convertiti ai nomi football-data tramite la mappatura
già imparata (data/processed/team_mapping.csv).

Nota: se un giocatore ha cambiato squadra a gennaio, Understat riporta le
squadre separate da virgola; la riga viene attribuita all'ultima squadra
elencata (approssimazione accettata per il calcolo delle quote).

Uso:
    python scripts/download_players.py                 # 5 leghe, stagioni 2024 e 2025
    python scripts/download_players.py --seasons 2025
"""

import argparse
import json
import sys
import time
from pathlib import Path

import pandas as pd
import pymysql

import config
import requests

PROJECT_ROOT = Path(__file__).resolve().parent.parent
RAW_DIR = PROJECT_ROOT / "data" / "raw" / "understat"
MAP_PATH = PROJECT_ROOT / "data" / "processed" / "team_mapping.csv"

LEAGUES = {"EPL": "E0", "La_liga": "SP1", "Bundesliga": "D1",
           "Serie_A": "I1", "Ligue_1": "F1"}
DEFAULT_SEASONS = ["2024", "2025"]

HEADERS = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
                         "AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0 Safari/537.36"}

SCHEMA = """
CREATE TABLE IF NOT EXISTS player_stats (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    understat_id INT UNSIGNED NOT NULL,
    season       VARCHAR(7)   NOT NULL,
    league_code  VARCHAR(5)   NOT NULL,
    player       VARCHAR(80)  NOT NULL,
    team         VARCHAR(60)  NOT NULL,
    position     VARCHAR(10)  NULL,
    games        SMALLINT UNSIGNED NULL,
    minutes      INT UNSIGNED      NULL,
    goals        SMALLINT UNSIGNED NULL,
    assists      SMALLINT UNSIGNED NULL,
    xg           DECIMAL(7,3) NULL,
    xa           DECIMAL(7,3) NULL,
    npxg         DECIMAL(7,3) NULL,
    UNIQUE KEY uq_player_season (understat_id, season),
    KEY idx_team (league_code, season, team)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""


def fetch_players(league: str, year: str, force: bool = False) -> list | None:
    RAW_DIR.mkdir(parents=True, exist_ok=True)
    cache = RAW_DIR / f"players_{league}_{year}.json"
    if cache.exists() and not force:
        print(f"  [cache] {cache.name}")
        return json.loads(cache.read_text(encoding="utf-8"))

    url = f"https://understat.com/getLeagueData/{league}/{year}"
    headers = HEADERS | {"X-Requested-With": "XMLHttpRequest",
                         "Referer": f"https://understat.com/league/{league}/{year}"}
    for attempt in range(3):
        try:
            resp = requests.get(url, headers=headers, timeout=30)
            resp.raise_for_status()
            data = resp.json()["players"]
            cache.write_text(json.dumps(data), encoding="utf-8")
            print(f"  [ok]    {league}/{year}: {len(data)} giocatori")
            time.sleep(0.7)
            return data
        except requests.RequestException as exc:
            print(f"  [retry {attempt + 1}/3] {url}: {exc}")
            time.sleep(3 * (attempt + 1))
    print(f"  [FAIL]  {url}")
    return None


def main() -> int:
    parser = argparse.ArgumentParser(description="Scarica statistiche giocatore Understat")
    parser.add_argument("--seasons", nargs="+", default=DEFAULT_SEASONS)
    parser.add_argument("--force", action="store_true")
    config.add_db_args(parser)
    args = parser.parse_args()

    name_map = dict(pd.read_csv(MAP_PATH).itertuples(index=False, name=None))

    rows = []
    unmapped_teams = set()
    for league, code in LEAGUES.items():
        for year in args.seasons:
            data = fetch_players(league, year, force=args.force)
            if data is None:
                continue
            season = f"{year}/{str(int(year) + 1)[2:]}"
            for p in data:
                team_us = p["team_title"].split(",")[-1].strip()
                team = name_map.get(team_us)
                if team is None:
                    unmapped_teams.add(team_us)
                    continue
                rows.append((
                    int(p["id"]), season, code, p["player_name"], team,
                    p.get("position"), int(p["games"]), int(p["time"]),
                    int(p["goals"]), int(p["assists"]),
                    round(float(p["xG"]), 3), round(float(p["xA"]), 3),
                    round(float(p["npxG"]), 3),
                ))

    if unmapped_teams:
        print(f"  [ATTENZIONE] squadre senza mappatura, giocatori saltati: "
              f"{sorted(unmapped_teams)}")

    conn = pymysql.connect(host=args.host, port=args.port, user=args.user,
                           password=args.password, database="tigertips")
    cur = conn.cursor()
    cur.execute(SCHEMA)
    conn.commit()

    sql = """
        INSERT INTO player_stats (understat_id, season, league_code, player, team,
                                  position, games, minutes, goals, assists, xg, xa, npxg)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            league_code = VALUES(league_code), player = VALUES(player),
            team = VALUES(team), position = VALUES(position),
            games = VALUES(games), minutes = VALUES(minutes),
            goals = VALUES(goals), assists = VALUES(assists),
            xg = VALUES(xg), xa = VALUES(xa), npxg = VALUES(npxg)
    """
    for start in range(0, len(rows), 1000):
        cur.executemany(sql, rows[start:start + 1000])
        conn.commit()

    cur.execute("SELECT season, COUNT(*) FROM player_stats GROUP BY season")
    print("\nGiocatori in player_stats:")
    for season, count in cur.fetchall():
        print(f"  {season}: {count}")
    conn.close()
    return 0


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