#!/usr/bin/env python3
"""
Fetch upcoming & recent schedules from TheSportsDB v2 for priority leagues.
Upserts into SportsGame (deduplicated by externalGameId).

Cron: 0 */2 * * * (every 2 hours)
"""
import json
import requests
import psycopg2
from datetime import datetime, timezone

API_KEY = "428892"
BASE = "https://www.thesportsdb.com/api/v2/json"
HEADERS = {"X-API-KEY": API_KEY}

PRIORITY_LEAGUES = {
    "4387": ("nba", "NBA"),
    "4391": ("nfl", "NFL"),
    "4380": ("nhl", "NHL"),
    "4424": ("mlb", "MLB"),
    "4607": ("ncaab", "NCAAB"),
    "4479": ("ncaaf", "NCAAF"),
    "4328": ("epl", "English Premier League"),
    "4335": ("laliga", "Spanish La Liga"),
    "4331": ("bundesliga", "German Bundesliga"),
    "4332": ("seriea", "Italian Serie A"),
    "4334": ("ligue1", "French Ligue 1"),
    "4346": ("mls", "American MLS"),
    "4480": ("ucl", "UEFA Champions League"),
    "4443": ("ufc", "UFC"),
}


def load_db_url():
    with open('/var/www/html/eventheodds/.env', 'r') as f:
        for line in f:
            if line.startswith('SPORTS_DATABASE_URL='):
                return line.split('=', 1)[1].strip().strip('"').split('?')[0]
    return ''


def fetch_schedule(league_id, direction):
    """Fetch next or previous schedule for a league."""
    url = f"{BASE}/schedule/{direction}/league/{league_id}"
    try:
        resp = requests.get(url, headers=HEADERS, timeout=15)
        if resp.status_code != 200:
            return []
        data = resp.json()
        return data.get("schedule") or data.get("events") or []
    except Exception as e:
        print(f"  Error fetching schedule/{direction}/{league_id}: {e}")
        return []


def _int(v):
    if v is None:
        return None
    try:
        return int(v)
    except (ValueError, TypeError):
        return None


def _float(v):
    if v is None:
        return None
    try:
        return float(v)
    except (ValueError, TypeError):
        return None


def parse_season(event):
    """Extract season year from event."""
    s = event.get("strSeason", "")
    if not s:
        date = event.get("dateEvent", "")
        if date:
            return int(date[:4])
        return datetime.now().year
    # Handle "2024-2025" format
    try:
        return int(s.split("-")[0])
    except (ValueError, IndexError):
        return datetime.now().year


def upsert_games(conn, events, league_code):
    if not events:
        return 0
    now = datetime.now(timezone.utc)
    inserted = 0
    cur = conn.cursor()

    for ev in events:
        event_id = ev.get("idEvent", "")
        if not event_id:
            continue

        ext_id = f"tsdb_{event_id}"
        date_str = ev.get("dateEvent", "")
        time_str = ev.get("strTime") or ev.get("strTimeLocal") or "00:00:00"
        if time_str and len(time_str) >= 5:
            game_date = f"{date_str} {time_str[:8]}"
        else:
            game_date = f"{date_str} 00:00:00"

        home = ev.get("strHomeTeam") or ev.get("strEvent", "Unknown")
        away = ev.get("strAwayTeam") or "TBD"
        home_score = _int(ev.get("intHomeScore"))
        away_score = _int(ev.get("intAwayScore"))
        status = ev.get("strStatus") or ev.get("strPostponed") or ""
        if home_score is not None and away_score is not None:
            status = "final"
        elif status in ("NS", ""):
            status = "scheduled"

        season = parse_season(ev)

        cur.execute("""
            INSERT INTO "SportsGame" (
                league, season, "gameDate", "homeTeam", "awayTeam",
                "externalGameId", "homeScore", "awayScore", status, raw,
                "createdAt", "updatedAt"
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (league, "externalGameId") DO UPDATE SET
                "homeScore" = COALESCE(EXCLUDED."homeScore", "SportsGame"."homeScore"),
                "awayScore" = COALESCE(EXCLUDED."awayScore", "SportsGame"."awayScore"),
                status = CASE WHEN EXCLUDED.status = 'final' THEN 'final' ELSE "SportsGame".status END,
                raw = EXCLUDED.raw,
                "updatedAt" = EXCLUDED."updatedAt"
            WHERE EXCLUDED."homeScore" IS NOT NULL OR "SportsGame"."homeScore" IS NULL
        """, (
            league_code, season, game_date, home, away,
            ext_id, home_score, away_score, status,
            json.dumps(ev), now, now,
        ))
        inserted += 1

    conn.commit()
    return inserted


def main():
    db_url = load_db_url()
    if not db_url:
        print("ERROR: No SPORTS_DATABASE_URL")
        return

    conn = psycopg2.connect(db_url)
    total = 0

    for league_id, (code, name) in PRIORITY_LEAGUES.items():
        for direction in ("next", "previous"):
            events = fetch_schedule(league_id, direction)
            if events:
                n = upsert_games(conn, events, code)
                print(f"  {name} ({direction}): {len(events)} events, {n} upserted")
                total += n

    conn.close()
    print(f"TOTAL: {total} games upserted")


if __name__ == "__main__":
    main()