"""
Weekly API-Football refresh — discovers new finished fixtures from the past 7 days,
appends them to fixtures_index.csv, and pulls match stats for any new ones.
Scheduled Wednesdays via Task Scheduler.
"""
import requests, pandas as pd, time, sys
from pathlib import Path
from datetime import datetime, timedelta

# The .bat redirects stdout to a log, so Python picks cp1252 rather than the
# console's encoding -- and the arrow in the progress banner has no cp1252
# mapping, which killed the whole weekly run (2026-08-05, 2026-08-09).
try:
    sys.stdout.reconfigure(encoding='utf-8')
    sys.stderr.reconfigure(encoding='utf-8')
except AttributeError:
    pass

API_KEY  = "81b487be73030f5dec980d2a1ef2c406"
HEADERS  = {"x-apisports-key": API_KEY}
AF_BASE  = "https://v3.football.api-sports.io"

OUT_DIR      = Path(r"E:\soccer\raw_api")
FIXTURES_CSV = OUT_DIR / "fixtures_index.csv"
STATS_CSV    = OUT_DIR / "match_stats.csv"
DONE_FILE    = OUT_DIR / "done_fixtures.txt"
FEATURES_CSV = OUT_DIR / "match_features.csv"

# Fallback only. The real season is resolved per league from API-Football via
# maps.current_seasons() -- a league's season year is not reliably the calendar
# year (J1 moved to autumn-spring and is labelled 2027), and a stale constant
# here does not error, it just silently returns zero fixtures. This was pinned at
# 2025 through the 2026/27 kickoff, so every EFL fixture from 2026-08-08 onward
# was invisible to the weekly refresh.
AF_SEASON = 2025

LEAGUE_MAP = {
    'E0': 39,  'E1': 40,  'E2': 41,  'E3': 42,  'EC': 43,
    'SP1': 140,'SP2': 141,'I1': 135, 'I2': 136,
    'D1': 78,  'D2': 79,  'F1': 61,  'F2': 62,
    'N1': 88,  'B1': 144, 'P1': 94,  'T1': 203,
    'G1': 197, 'SC0': 179,'SC1': 180,'SC2': 183,'SC3': 184,
}

DAILY_LIMIT = 7400

class QuotaExhausted(RuntimeError):
    """API-Football refused the call. Not a 'no results' condition."""


def _api_errors(payload):
    """API-Football answers 200 with an `errors` body -- quota, bad key, bad
    params. It is a dict when populated and an empty LIST when clean, so a
    truthiness test is the only safe check."""
    e = payload.get('errors')
    return e if isinstance(e, dict) and e else None


def get_requests_used():
    """(current, limit_day), or raise.

    This used to be `except: return 0, 7500`, which is the worst possible
    answer to "how much quota is left": when the account was actually OUT, the
    status call failed, the handler reported full quota available, and the run
    proceeded to make calls that every one came back empty. The log then said
    'API quota: 0/7500 used' and 'New fixtures found: 0' and exited 0. Six days
    of fixtures went missing while the task reported success. Never invent an
    optimistic number for a limit you could not read.
    """
    r = requests.get(f"{AF_BASE}/status", headers=HEADERS, timeout=10)
    d = r.json()
    err = _api_errors(d)
    if err:
        raise QuotaExhausted(f"API-Football refused /status: {err}")
    req = (d.get('response') or {}).get('requests') or {}
    if 'current' not in req or 'limit_day' not in req:
        raise QuotaExhausted(f"/status returned no request counts: {d}")
    return req['current'], req['limit_day']

def fetch_fixtures_range(league_id, date_from, date_to, season):
    """Finished fixtures in a window, or raise if the API refused.

    An exhausted quota comes back 200 with results:0 and an `errors` body, which
    is indistinguishable from a genuine "no matches that week" unless the errors
    field is read. It was not read, so a dead account looked exactly like a
    quiet week -- 22 leagues scanned, 0 fixtures found, exit 0.
    """
    r = requests.get(f"{AF_BASE}/fixtures", headers=HEADERS, params={
        'league': league_id, 'season': season,
        'from': date_from, 'to': date_to, 'status': 'FT',
    }, timeout=15)
    d = r.json()
    err = _api_errors(d)
    if err:
        raise QuotaExhausted(f"API-Football refused /fixtures: {err}")
    return d.get('response', [])


def resolve_seasons():
    """{div: season} for the divs we pull, from API-Football's own `current` flag.

    Cached 7d by maps. Any league the resolver cannot answer for falls back to
    AF_SEASON, so a dead API degrades to the old behaviour rather than skipping.
    """
    try:
        import maps
        resolved = maps.current_seasons(HEADERS)
    except Exception as e:
        print(f"  season resolver unavailable ({e}) -- falling back to {AF_SEASON}")
        resolved = {}
    return {div: (resolved.get(div) or AF_SEASON) for div in LEAGUE_MAP}

def fetch_stats(fixture_id):
    r = requests.get(f"{AF_BASE}/fixtures/statistics", headers=HEADERS,
                     params={"fixture": fixture_id}, timeout=15)
    r.raise_for_status()
    return r.json().get("response", [])

def parse_stats(fixture_id, fixture_row, response):
    rows = []
    for team_data in response:
        team_name = team_data["team"]["name"]
        is_home = (team_name == fixture_row["home_team"])
        stats = {s["type"]: s["value"] for s in team_data["statistics"]}
        def get(key, default=None):
            v = stats.get(key, default)
            if v is None: return default
            if isinstance(v, str) and v.endswith("%"):
                try: return float(v.rstrip("%"))
                except: return default
            try: return float(v)
            except: return default
        rows.append({
            "fixture_id":      fixture_id,
            "date":            fixture_row["date"],
            "div":             fixture_row["div"],
            "season":          fixture_row["season"],
            "team":            team_name,
            "is_home":         int(is_home),
            "home_team":       fixture_row["home_team"],
            "away_team":       fixture_row["away_team"],
            "home_goals":      fixture_row["home_goals"],
            "away_goals":      fixture_row["away_goals"],
            "shots_on_goal":   get("Shots on Goal"),
            "shots_off_goal":  get("Shots off Goal"),
            "total_shots":     get("Total Shots"),
            "blocked_shots":   get("Blocked Shots"),
            "shots_inside":    get("Shots insidebox"),
            "shots_outside":   get("Shots outsidebox"),
            "fouls":           get("Fouls"),
            "corners":         get("Corner Kicks"),
            "offsides":        get("Offsides"),
            "possession":      get("Ball Possession"),
            "yellow_cards":    get("Yellow Cards"),
            "red_cards":       get("Red Cards"),
            "saves":           get("Goalkeeper Saves"),
            "total_passes":    get("Total passes"),
            "accurate_passes": get("Passes accurate"),
            "pass_pct":        get("Passes %"),
        })
    return rows

def build_features(df):
    df = df.copy()
    df["date"] = pd.to_datetime(df["date"])
    df = df.sort_values(["team", "date"]).reset_index(drop=True)
    stat_cols = ["shots_on_goal","shots_off_goal","total_shots","blocked_shots",
                 "shots_inside","shots_outside","fouls","corners","offsides",
                 "possession","yellow_cards","red_cards","saves",
                 "total_passes","accurate_passes","pass_pct"]
    df["pts"] = 0
    win_mask  = ((df["is_home"]==1) & (df["home_goals"] > df["away_goals"])) | \
                ((df["is_home"]==0) & (df["away_goals"] > df["home_goals"]))
    draw_mask = df["home_goals"] == df["away_goals"]
    df.loc[win_mask,  "pts"] = 3
    df.loc[draw_mask, "pts"] = 1
    df["gf"] = df.apply(lambda r: r["home_goals"] if r["is_home"] else r["away_goals"], axis=1)
    df["ga"] = df.apply(lambda r: r["away_goals"] if r["is_home"] else r["home_goals"], axis=1)
    roll_cols = ["pts","gf","ga"] + [c for c in stat_cols if c in df.columns]
    feat_rows = []
    for team, grp in df.groupby("team"):
        grp = grp.sort_values("date").reset_index(drop=True)
        for w in [5, 10]:
            for col in roll_cols:
                if col in grp.columns:
                    grp[f"api_{col}_w{w}"] = grp[col].shift(1).rolling(w, min_periods=1).mean()
        feat_rows.append(grp)
    feat_df = pd.concat(feat_rows, ignore_index=True)
    feat_cols = ["fixture_id","date","div","season","team","is_home",
                 "home_team","away_team","home_goals","away_goals"] + \
                [c for c in feat_df.columns if c.startswith("api_")]
    return feat_df[[c for c in feat_cols if c in feat_df.columns]]

# ── Main ──────────────────────────────────────────────────────────────────────

print(f"=== API-Football Weekly Refresh — {datetime.now().strftime('%Y-%m-%d %H:%M')} ===\n")

try:
    used, limit = get_requests_used()
except (QuotaExhausted, requests.RequestException) as e:
    # Exit LOUDLY. A weekly job that cannot reach the API has not "found no
    # fixtures", it has not run, and the scheduler must see a failure so the
    # missed week is visible instead of being logged as success.
    print(f"ABORT: cannot read API-Football quota -- {e}")
    print("Nothing was refreshed. Fixture data is now stale.")
    sys.exit(3)
print(f"API quota: {used}/{limit} used ({limit - used} remaining)\n")
if used >= limit:
    print(f"ABORT: daily quota already exhausted ({used}/{limit}).")
    sys.exit(3)

# Date range: last 8 days (covers Sat-Sun games from prior weekend)
date_to   = datetime.now().date()
date_from = date_to - timedelta(days=8)
print(f"Scanning finished fixtures: {date_from} → {date_to}\n")

# Load existing index
index_df = pd.read_csv(FIXTURES_CSV)
existing_ids = set(index_df['fixture_id'].astype(int))
print(f"Existing fixture index: {len(existing_ids):,} fixtures")

# Load done set
done = set()
if DONE_FILE.exists():
    with open(DONE_FILE) as f:
        done = set(int(x) for x in f.read().split() if x.strip())

# ── Step 1: Discover new fixtures ─────────────────────────────────────────────
print("\n--- Step 1: Discovering new fixtures ---")
new_index_rows = []
requests_used = used

seasons = resolve_seasons()
off_default = {d: s for d, s in seasons.items() if s != AF_SEASON}
if off_default:
    print(f"  seasons resolved; {len(off_default)} league(s) not on {AF_SEASON}: "
          + ", ".join(f"{d}={s}" for d, s in sorted(off_default.items())))

for div, league_id in LEAGUE_MAP.items():
    if requests_used >= DAILY_LIMIT:
        print("Daily limit reached during discovery — stopping.")
        break
    try:
        fixtures = fetch_fixtures_range(league_id, date_from.isoformat(),
                                        date_to.isoformat(), seasons[div])
    except QuotaExhausted as e:
        # Stop at the first refusal rather than grinding through the remaining
        # leagues collecting empty responses and calling the result "0 new".
        print(f"ABORT during discovery on {div}: {e}")
        print(f"  {len(new_index_rows)} fixture(s) found before the refusal "
              f"were NOT written -- rerun once quota resets.")
        sys.exit(3)
    requests_used += 1
    for fix in fixtures:
        fid = fix['fixture']['id']
        if fid in existing_ids:
            continue
        home_goals = fix['goals']['home']
        away_goals = fix['goals']['away']
        if home_goals is None or away_goals is None:
            continue
        new_index_rows.append({
            'fixture_id': fid,
            'date':       fix['fixture']['date'][:10],
            'div':        div,
            'league_id':  league_id,
            'season':     seasons[div],
            'home_team':  fix['teams']['home']['name'],
            'away_team':  fix['teams']['away']['name'],
            'home_goals': home_goals,
            'away_goals': away_goals,
        })
        existing_ids.add(fid)
    time.sleep(0.5)

print(f"New fixtures found: {len(new_index_rows)}")

if new_index_rows:
    new_df = pd.DataFrame(new_index_rows)
    index_df = pd.concat([index_df, new_df], ignore_index=True)
    index_df.to_csv(FIXTURES_CSV, index=False)
    print(f"fixtures_index.csv updated: {len(index_df):,} total fixtures")

# ── Step 2: Pull stats for new fixtures ───────────────────────────────────────
todo_ids = [r['fixture_id'] for r in new_index_rows if r['fixture_id'] not in done]
print(f"\n--- Step 2: Pulling stats for {len(todo_ids)} new fixtures ---")

if not todo_ids:
    print("Nothing new to pull.")
else:
    fixture_lookup = index_df.set_index('fixture_id').to_dict('index')
    existing_stats = pd.read_csv(STATS_CSV) if STATS_CSV.exists() else pd.DataFrame()
    new_rows = []

    for i, fid in enumerate(todo_ids):
        if requests_used >= DAILY_LIMIT:
            print(f"Daily limit reached — stopping after {i} fixtures.")
            break
        try:
            response = fetch_stats(fid)
            requests_used += 1
            if response and fid in fixture_lookup:
                parsed = parse_stats(fid, fixture_lookup[fid], response)
                new_rows.extend(parsed)
            with open(DONE_FILE, 'a') as f:
                f.write(f"{fid}\n")
            done.add(fid)
            time.sleep(1)
        except requests.exceptions.HTTPError as e:
            code = e.response.status_code if e.response else 0
            if code == 403:
                with open(DONE_FILE, 'a') as f:
                    f.write(f"{fid}\n")
                done.add(fid)
            else:
                print(f"  Error fixture {fid}: {e}")
            time.sleep(1)
        except Exception as e:
            print(f"  Error fixture {fid}: {e}")
            time.sleep(1)

    if new_rows:
        chunk = pd.DataFrame(new_rows)
        combined = pd.concat([existing_stats, chunk], ignore_index=True) if len(existing_stats) else chunk
        combined.to_csv(STATS_CSV, index=False)
        print(f"match_stats.csv updated: {len(combined):,} team-match rows")

        print("\n--- Step 3: Rebuilding rolling features ---")
        feat_df = build_features(combined)
        feat_df.to_csv(FEATURES_CSV, index=False)
        print(f"match_features.csv updated: {len(feat_df):,} rows")
    else:
        print("No new stats rows.")

print(f"\nDone. API quota used this run: {requests_used - used}")