"""
Resolve match results in live_data CSVs using data already in the file.
Finds FT/AET/PEN rows, extracts the final score, and backfills
ftr / result_home / result_away for all rows of that game.

Runs nightly at 12:05 AM via Task Scheduler.
Safe to re-run — skips already-resolved games.

Usage: python resolve_live_data.py
"""
import glob, os, sys, json, time, requests, unicodedata
import pandas as pd
from datetime import datetime, date
from pathlib import Path

sys.stdout.reconfigure(encoding='utf-8', errors='replace')

DATA_DIR  = Path(r"E:\soccer\live_data")
TERMINAL  = {'FT', 'AET', 'PEN'}
API_KEY   = "81b487be73030f5dec980d2a1ef2c406"
AF_BASE   = "https://v3.football.api-sports.io"
HEADERS   = {"x-apisports-key": API_KEY}
CACHE_F   = Path(r"E:\soccer\data\_result_cache.json")

with open(r"E:\soccer\data\league_map.json") as f:
    _lm = json.load(f)
LEAGUE_IDS = {v["api_football_id"]: k for k, v in _lm.items() if v.get("api_football_id")}

def _strip_accents(s):
    return unicodedata.normalize('NFD', s).encode('ascii', 'ignore').decode()

# Known mismatches between our team names and API-Football names
TEAM_ALIASES = {
    'wolverhampton': 'wolves',
    'bilbao':        'athletic',
    'atletico':      'atletico madrid',
    'saint louis':   'saint louis city',
    'seattle':       'seattle sounders',
    'vancouver':     'vancouver whitecaps',
    'san jose':      'san jose earthquakes',
    'dallas':        'fc dallas',
    'colorado':      'colorado rapids',
    'psg':           'paris saint-germain',
    'nuremberg':     'nurnberg',
    'larissa':       'larisa',
    'panaitolikos':  'panetolikos',
    'panaitolikos':  'panetolikos',
    'kocaeli':       'kocaelispor',
    'genclerbirligi': 'genclerbirligi s.k.',
    'vicente barcelos': 'gil vicente',
}

def load_cache():
    if CACHE_F.exists():
        try: return json.load(open(CACHE_F))
        except: pass
    return {}

def save_cache(cache):
    """Persist the result cache, MINUS today.

    Today must stay cached in memory -- one run resolves ~100 same-day games and
    without it each would re-fetch, which is ~200 API calls per dashboard
    refresh on a plan that already ran out today.

    It must NOT reach disk, though: a snapshot written at 3pm contains only the
    games finished by 3pm, and api_results_for_date returns any non-empty cache
    entry without re-fetching, so the evening's games would be missing from it
    permanently. Dropping today keeps the in-run saving and loses the trap.
    """
    today = date.today().isoformat()
    with open(CACHE_F, "w") as f:
        json.dump({k: v for k, v in cache.items() if k != today}, f)

def api_results_for_date(gd_str, cache):
    # Treat empty cached results for past dates as stale — re-fetch
    if gd_str in cache and cache[gd_str]:
        return cache[gd_str]
    results = {}
    try:
        r = requests.get(f"{AF_BASE}/fixtures", headers=HEADERS, params={"date": gd_str}, timeout=15)
        for fix in r.json().get("response", []):
            if fix["league"]["id"] not in LEAGUE_IDS: continue
            if fix["fixture"]["status"]["short"] not in TERMINAL: continue
            goals = fix["goals"]
            if goals["home"] is None: continue
            h, a = int(goals["home"]), int(goals["away"])
            ftr = "H" if h > a else ("A" if h < a else "D")
            hn = _strip_accents(fix['teams']['home']['name']).lower().strip()
            an = _strip_accents(fix['teams']['away']['name']).lower().strip()
            key = f"{hn}|{an}"
            results[key] = {"ftr": ftr, "result_home": h, "result_away": a}
        cache[gd_str] = results
        time.sleep(0.3)
    except Exception as e:
        print(f"  API error for {gd_str}: {e}")
    return results

def _finished_today(grp):
    """Has this same-day game actually ended?

    Only matters because live_monitor's clock-based fallback writes status=FT
    with NO GOALS when it cannot get a score -- which is exactly the case that
    needs the API. So a terminal status counts as 'ended' even with no score
    attached, and 115+ minutes on the clock counts as well for a game whose
    status never advanced at all.

    The point of the original 'skip today' rule was not to ask about a game
    still in play. This keeps that, without waiting until tomorrow.
    """
    try:
        if grp['status'].isin(TERMINAL).any():
            return True
    except Exception:
        pass
    try:
        return float(grp['elapsed'].max()) >= 115
    except Exception:
        return False


def _resolve_alias(name):
    """Apply known team name aliases before matching. Always works on accent-stripped lowercase."""
    n = _strip_accents(name).lower().strip()
    return TEAM_ALIASES.get(n, n)

def api_results_for_date_combined(gd_str, cache):
    """Fetch results for gd_str and gd_str+1 day (catches late games stored under next UTC date)."""
    from datetime import timedelta
    results = dict(api_results_for_date(gd_str, cache))
    try:
        next_day = (date.fromisoformat(gd_str) + timedelta(days=1)).isoformat()
        results.update(api_results_for_date(next_day, cache))
    except Exception:
        pass
    return results

_refetched_dates = set()   # dates whose cache was busted this run (once each)

def api_lookup(home, away, gd_str, cache):
    results = api_results_for_date_combined(gd_str, cache)
    # Exact match (accent-stripped)
    hn = _strip_accents(home).lower().strip()
    an = _strip_accents(away).lower().strip()
    key = f"{hn}|{an}"
    if key not in results and gd_str not in _refetched_dates:
        # A cached date snapshotted mid-evening (e.g. the 00:05 run while a late
        # game was still in play) is non-empty yet missing that game — and the
        # empty-only staleness rule trusted it forever (Seattle vs Portland
        # 2026-07-16 lost its win this way). Bust the cache once per date per
        # run when the match we need isn't in it.
        _refetched_dates.add(gd_str)
        cache.pop(gd_str, None)
        try:
            from datetime import timedelta
            cache.pop((date.fromisoformat(gd_str) + timedelta(days=1)).isoformat(), None)
        except Exception:
            pass
        results = api_results_for_date_combined(gd_str, cache)
    if key in results:
        return results[key]
    # Alias match
    ah, aa = _resolve_alias(home), _resolve_alias(away)
    alias_key = f"{ah}|{aa}"
    if alias_key in results:
        return results[alias_key]
    # Fuzzy: bidirectional word substring matching (accent-stripped throughout)
    skip = {'fc', 'ac', 'sc', 'af', 'real', 'royal', 'sporting', 'united', 'city', 'town'}
    def words(n):
        return [w for w in _strip_accents(n).lower().replace('-', ' ').split()
                if len(w) > 3 and w not in skip]
    def matches(our_name, cache_name):
        ow = list(dict.fromkeys(words(our_name) + words(_resolve_alias(our_name))))
        cw = words(cache_name)
        return (any(w in cache_name for w in ow) or
                any(w in _strip_accents(our_name).lower() or w in _resolve_alias(our_name) for w in cw))
    for k, v in results.items():
        kh, ka = k.split('|', 1)
        if matches(home, kh) and matches(away, ka):
            return v
    return None


def resolve_file(path, cache):
    df = pd.read_csv(path, encoding='utf-8')

    for col in ['ftr', 'result_home', 'result_away']:
        if col not in df.columns:
            df[col] = None

    df['home_goals'] = pd.to_numeric(df['home_goals'], errors='coerce')
    df['away_goals'] = pd.to_numeric(df['away_goals'], errors='coerce')

    # Derive game date from filename (YYYY-MM-DD.csv) — used for API fallback
    try:
        gd_str = path.stem  # e.g. "2026-04-25"
        gd = date.fromisoformat(gd_str)
    except Exception:
        gd_str = None
        gd = None

    # Past dates are always safe to look up. Today is allowed too, but only for
    # a game that has visibly ENDED -- see _finished_today. The old rule skipped
    # today outright, which meant a Saturday's results could not resolve until
    # Sunday no matter how many times the dashboard was refreshed.
    api_eligible = gd is not None and gd < date.today()
    is_today = gd == date.today()

    changed = False
    for (home, away), grp in df.groupby(['home', 'away']):
        # Skip if already resolved
        if df.loc[grp.index, 'ftr'].notna().any():
            continue

        # Find terminal rows with valid scores
        terminal = grp[grp['status'].isin(TERMINAL) & grp['home_goals'].notna()]
        if not terminal.empty:
            last = terminal.iloc[-1]
            h = int(last['home_goals'])
            a = int(last['away_goals'])
            ftr = 'H' if h > a else ('A' if h < a else 'D')
            df.loc[grp.index, 'ftr']         = ftr
            df.loc[grp.index, 'result_home'] = h
            df.loc[grp.index, 'result_away'] = a
            changed = True
            print(f"  [csv] {home} vs {away} -> {ftr} {h}-{a}")
            continue

        # API fallback: game has no usable terminal rows
        if not (api_eligible or (is_today and _finished_today(grp))):
            continue
        result = api_lookup(home, away, gd_str, cache)
        if result:
            df.loc[grp.index, 'ftr']         = result['ftr']
            df.loc[grp.index, 'result_home'] = result['result_home']
            df.loc[grp.index, 'result_away'] = result['result_away']
            changed = True
            print(f"  [api] {home} vs {away} -> {result['ftr']} {result['result_home']}-{result['result_away']}")

    if changed:
        df.to_csv(path, index=False, encoding='utf-8')

    return changed


def main():
    print(f"\n=== Resolve Live Data — {datetime.now().strftime('%Y-%m-%d %H:%M')} ===")
    files = sorted(glob.glob(str(DATA_DIR / "*.csv")))
    cache = load_cache()
    updated = 0
    for f in files:
        fname = os.path.basename(f)
        print(f"\n[{fname}]")
        if resolve_file(Path(f), cache):
            updated += 1
    save_cache(cache)
    print(f"\nDone — {updated} files updated.")


if __name__ == '__main__':
    main()