#!/usr/bin/env python3
"""
Fetch live scores from TheSportsDB v2 API for all active sports.
Stores raw data in ExternalFeedRecord (deduplicated by contentHash)
and upserts into LiveGameState for real-time tracking.

Cron: */3 * * * * (every 3 minutes)
"""
import json
import hashlib
import requests
import psycopg2
from psycopg2.extras import execute_values
from datetime import datetime, timezone

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

# Sports with meaningful live data
SPORTS = ["soccer", "basketball", "ice_hockey", "baseball", "american_football",
          "rugby", "cricket", "tennis", "volleyball", "fighting", "mma", "boxing",
          "motorsport", "hockey"]

# Map TheSportsDB sport names to our league codes where possible
LEAGUE_MAP = {
    "NBA": "nba",
    "National Basketball Association": "nba",
    "NHL": "nhl",
    "National Hockey League": "nhl",
    "NFL": "nfl",
    "National Football League": "nfl",
    "MLB": "mlb",
    "Major League Baseball": "mlb",
    "English Premier League": "epl",
    "Spanish La Liga": "laliga",
    "German Bundesliga": "bundesliga",
    "Italian Serie A": "seriea",
    "French Ligue 1": "ligue1",
    "Ultimate Fighting Championship": "ufc",
    "NCAA Football": "ncaaf",
    "NCAA Basketball": "ncaab",
}


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 sha256(data):
    return hashlib.sha256(json.dumps(data, sort_keys=True).encode()).hexdigest()


def fetch_sport(sport):
    try:
        resp = requests.get(f"{BASE}/livescore/{sport}", headers=HEADERS, timeout=15)
        if resp.status_code != 200:
            return []
        data = resp.json()
        return data.get("livescore") or []
    except Exception as e:
        print(f"  Error fetching {sport}: {e}")
        return []


def map_league(event):
    league_name = event.get("strLeague", "")
    return LEAGUE_MAP.get(league_name, league_name[:20].lower().replace(" ", "_"))


def ingest_events(conn, events, sport):
    if not events:
        return 0, 0

    now = datetime.now(timezone.utc)
    feed_rows = []
    live_rows = []

    for ev in events:
        event_id = ev.get("idEvent", "")
        content_hash = sha256(ev)
        league = map_league(ev)

        # ExternalFeedRecord row
        feed_rows.append((
            "thesportsdb",          # source
            "livescore",            # kind
            league,                 # league
            None,                   # season
            content_hash,           # contentHash
            now,                    # fetchedAt
            json.dumps(ev),         # raw
            now,                    # createdAt
        ))

        # LiveGameState row (only for in-progress games)
        status = ev.get("strStatus", "")
        if status not in ("FT", "NS", "PST", "CANC", "ABD", "AWD", "WO", ""):
            home_score = _int(ev.get("intHomeScore"))
            away_score = _int(ev.get("intAwayScore"))
            diff = (home_score - away_score) if home_score is not None and away_score is not None else None
            progress = _int(ev.get("strProgress"))

            live_rows.append((
                f"tsdb_{event_id}",     # gameExternalId
                league,                  # league
                ev.get("dateEvent"),     # gameDate
                progress,                # period (minute/progress)
                status,                  # timeRemaining (status text)
                home_score,
                away_score,
                diff,
                now,                     # recordedAt
            ))

    # Bulk insert ExternalFeedRecord (skip duplicates via contentHash)
    feed_inserted = 0
    if feed_rows:
        cur = conn.cursor()
        cur.execute("""
            INSERT INTO "ExternalFeedRecord" (source, kind, league, season, "contentHash", "fetchedAt", raw, "createdAt")
            SELECT * FROM unnest(
                %s::text[], %s::text[], %s::text[], %s::text[],
                %s::text[], %s::timestamp[], %s::jsonb[], %s::timestamp[]
            )
            ON CONFLICT (source, "contentHash") DO NOTHING
        """, (
            [r[0] for r in feed_rows],
            [r[1] for r in feed_rows],
            [r[2] for r in feed_rows],
            [r[3] for r in feed_rows],
            [r[4] for r in feed_rows],
            [r[5] for r in feed_rows],
            [r[6] for r in feed_rows],
            [r[7] for r in feed_rows],
        ))
        feed_inserted = cur.rowcount
        conn.commit()

    # Upsert LiveGameState
    live_inserted = 0
    if live_rows:
        cur = conn.cursor()
        for row in live_rows:
            cur.execute("""
                INSERT INTO "LiveGameState" ("gameExternalId", league, "gameDate", period,
                    "timeRemaining", "homeScore", "awayScore", "scoreDifferential", "recordedAt")
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT ("gameExternalId", "recordedAt") DO NOTHING
            """, row)
        live_inserted = cur.rowcount
        conn.commit()

    return feed_inserted, live_inserted


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


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

    conn = psycopg2.connect(db_url)
    total_events = 0
    total_feed = 0
    total_live = 0

    for sport in SPORTS:
        events = fetch_sport(sport)
        if events:
            feed_ins, live_ins = ingest_events(conn, events, sport)
            print(f"  {sport}: {len(events)} events, {feed_ins} new feed records, {live_ins} live states")
            total_events += len(events)
            total_feed += feed_ins
            total_live += live_ins

    conn.close()
    print(f"TOTAL: {total_events} events, {total_feed} new feed records, {total_live} live states")


if __name__ == "__main__":
    main()