"""Fetch team match statistics for the ML experiment (2026-09-29).

RUN WITH THE ISOLATED ENV, never the live one:
    E:\\soccer\\ml_env\\Scripts\\python.exe research\\ml_fetch_team_stats.py

The live monitor (real xlow orders) runs on the Python39 install; soccerdata
and its dependencies live only in ml_env so nothing here can upgrade a package
underneath it.

Sources (soccerdata caches raw pages under ~/soccerdata/data, so re-runs are
cheap):
  * Understat -- per team, per match: xG, npxG, xGA, npxGA, PPDA, deep
    completions, goals, expected points. Top 5 leagues, 2014/15 onward.
  * ClubElo   -- full rating history per club, joined to each match date.
    OPTIONAL: api.clubelo.com returned 502 for every club on 2026-09-29, so
    the experiment computes its own Elo instead (research/ml_elo.py). Pass
    --clubelo to try it anyway.

Writes research/ml_data/:
  understat_team_matches.csv   one row per team per match (raw)
  clubelo_history.csv          one row per club per rating interval (raw)
"""
from pathlib import Path

import pandas as pd
import soccerdata as sd

OUT = Path(r'E:\soccer\research\ml_data')
OUT.mkdir(parents=True, exist_ok=True)

LEAGUES = ['ENG-Premier League', 'ESP-La Liga', 'GER-Bundesliga',
           'ITA-Serie A', 'FRA-Ligue 1']
SEASONS = [f'{y % 100:02d}{(y + 1) % 100:02d}' for y in range(2014, 2027)]


def fetch_understat():
    us = sd.Understat(leagues=LEAGUES, seasons=SEASONS)
    df = us.read_team_match_stats().reset_index()
    df.to_csv(OUT / 'understat_team_matches.csv', index=False)
    print(f'understat: {len(df)} team-match rows, '
          f'{df["game"].nunique() if "game" in df else "?"} games, '
          f'columns: {list(df.columns)}')
    return df


def fetch_clubelo(team_names):
    ce = sd.ClubElo()
    frames, missing = [], []
    for t in sorted(team_names):
        try:
            h = ce.read_team_history(t).reset_index()
            h['query_team'] = t
            frames.append(h)
        except Exception as e:                       # name not known to ClubElo
            missing.append((t, type(e).__name__))
    if not frames:
        # Every lookup failed (server down, not bad names). Writing an empty
        # CSV here left a 2-byte file that read like "no clubs matched".
        raise SystemExit(f'clubelo: all {len(missing)} lookups failed '
                         f'({missing[0][1]}...) -- server down? nothing written')
    df = pd.concat(frames, ignore_index=True)
    df.to_csv(OUT / 'clubelo_history.csv', index=False)
    print(f'clubelo: {len(df)} rows for {len(frames)} clubs; '
          f'{len(missing)} not found: {missing[:20]}')
    return df


if __name__ == '__main__':
    us = fetch_understat()
    teams = set(us['home_team']) | set(us['away_team']) if 'home_team' in us \
        else set(us['team'])
    fetch_clubelo(teams)