#!/usr/bin/env python3
"""
Fetch TV broadcast schedules from TheSportsDB v2.
Upserts into GameBroadcast table.

Cron: 0 8 * * * (daily at 8 AM UTC)
"""
import json
import requests
import psycopg2
from datetime import datetime, timezone, timedelta

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

# National US networks
NATIONAL_NETWORKS = {"ESPN", "ABC", "CBS", "FOX", "NBC", "TNT", "TBS", "FS1",
                     "ESPN2", "ESPNU", "NFL Network", "MLB Network", "NBA TV",
                     "NHL Network", "Fox Sports 1", "CBS Sports Network"}


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_tv_day(date_str):
    """Fetch TV listings for a specific date (YYYY-MM-DD)."""
    url = f"{BASE}/filter/tv/day/{date_str}"
    try:
        resp = requests.get(url, headers=HEADERS, timeout=15)
        if resp.status_code != 200:
            return []
        data = resp.json()
        return data.get("tvevents") or data.get("tv") or []
    except Exception as e:
        print(f"  Error fetching TV for {date_str}: {e}")
        return []


def upsert_broadcasts(conn, tv_events):
    if not tv_events:
        return 0
    now = datetime.now(timezone.utc)
    cur = conn.cursor()
    inserted = 0

    for tv in tv_events:
        event_name = tv.get("strEvent", "")
        channel = tv.get("strChannel", "")
        date_str = tv.get("dateEvent", "")
        time_str = tv.get("strTimeStart") or "00:00"
        sport = tv.get("strSport", "")

        # Try to parse home/away from event name
        parts = event_name.split(" vs ")
        if len(parts) == 2:
            home = parts[0].strip()
            away = parts[1].strip()
        else:
            home = event_name
            away = ""

        game_date = f"{date_str} {time_str[:5]}:00" if date_str else None
        if not game_date:
            continue

        is_national = channel.upper() in {n.upper() for n in NATIONAL_NETWORKS}
        league = tv.get("strLeague", sport)[:20].lower().replace(" ", "_")

        # Parse day of week and hour
        try:
            dt = datetime.strptime(game_date, "%Y-%m-%d %H:%M:%S")
            dow = dt.weekday()
            hour = dt.hour
        except ValueError:
            dow = None
            hour = None

        try:
            cur.execute("""
                INSERT INTO "GameBroadcast"
                    (league, "gameDate", "homeTeam", "awayTeam", network,
                     "isNationalTV", "dayOfWeek", "startHour", "createdAt")
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (league, "gameId") DO NOTHING
            """, (
                league, game_date, home, away, channel,
                is_national, dow, hour, now,
            ))
            inserted += cur.rowcount
        except Exception:
            conn.rollback()
            continue

    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

    # Fetch today and next 7 days
    for i in range(8):
        date = datetime.now(timezone.utc) + timedelta(days=i)
        date_str = date.strftime("%Y-%m-%d")
        events = fetch_tv_day(date_str)
        if events:
            n = upsert_broadcasts(conn, events)
            print(f"  {date_str}: {len(events)} TV events, {n} inserted")
            total += n

    conn.close()
    print(f"TOTAL: {total} broadcast records")


if __name__ == "__main__":
    main()