#!/usr/bin/env python3
"""
Enrich Games with Broadcast/TV Data
Extracts broadcast network info from ESPN API for TV analysis.
Unlocks: Q13 (nationally televised game pricing)

Run: Daily for upcoming games, weekly backfill for historical
"""
import psycopg2
import requests
import os
from datetime import datetime, timezone, timedelta
import time

HEADERS = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'}
ESPN_BASE = 'https://site.api.espn.com/apis/site/v2/sports'

LEAGUE_CONFIG = {
    'nba': ('basketball', 'nba'),
    'nfl': ('football', 'nfl'),
    'nhl': ('hockey', 'nhl'),
    'mlb': ('baseball', 'mlb'),
    'ncaab': ('basketball', 'mens-college-basketball'),
    'ncaaf': ('football', 'college-football'),
}

# National networks (for "nationally televised" flag)
NATIONAL_NETWORKS = {
    'ESPN', 'ESPN2', 'ABC', 'TNT', 'TBS', 'NBC', 'CBS', 'FOX',
    'FS1', 'NBCSN', 'USA', 'NFL Network', 'NBA TV', 'NHL Network',
    'MLB Network', 'ESPN+', 'Peacock', 'Prime Video', 'Apple TV+'
}

def load_db_url():
    env_path = '/var/www/html/eventheodds/.env'
    try:
        with open(env_path, 'r') as f:
            for line in f:
                if line.startswith('SPORTS_DATABASE_URL='):
                    return line.split('=', 1)[1].strip().split('?')[0]
    except FileNotFoundError:
        pass
    return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0]


def ensure_columns(cur):
    """Add broadcast columns to SportsGame if needed"""
    columns = [
        ('broadcastNetworks', 'TEXT[]'),
        ('primaryBroadcast', 'TEXT'),
        ('isNationalTV', 'BOOLEAN'),
        ('broadcastType', 'TEXT'),  # TV, Streaming, Regional
    ]
    for col_name, col_type in columns:
        try:
            cur.execute(f'ALTER TABLE "SportsGame" ADD COLUMN IF NOT EXISTS "{col_name}" {col_type}')
        except:
            pass


def fetch_broadcasts_for_date(league, date_str):
    """Fetch broadcast data from ESPN for a specific date"""
    if league not in LEAGUE_CONFIG:
        return []

    sport, espn_league = LEAGUE_CONFIG[league]
    url = f'{ESPN_BASE}/{sport}/{espn_league}/scoreboard?dates={date_str}'

    broadcasts = []

    try:
        resp = requests.get(url, headers=HEADERS, timeout=15)
        if resp.status_code != 200:
            return broadcasts

        data = resp.json()

        for event in data.get('events', []):
            event_id = event.get('id')
            competition = event.get('competitions', [{}])[0]

            # Get teams
            home_team = None
            away_team = None
            for comp in competition.get('competitors', []):
                abbr = comp.get('team', {}).get('abbreviation')
                if comp.get('homeAway') == 'home':
                    home_team = abbr
                else:
                    away_team = abbr

            # Get broadcasts
            broadcast_list = competition.get('broadcasts', [])
            networks = []
            primary = None
            broadcast_type = 'Regional'

            for bc in broadcast_list:
                for name in bc.get('names', []):
                    networks.append(name)
                    if name in NATIONAL_NETWORKS:
                        broadcast_type = 'National'
                        if not primary:
                            primary = name

            if not primary and networks:
                primary = networks[0]

            # Check geoBroadcasts for streaming
            geo_broadcasts = competition.get('geoBroadcasts', [])
            for gb in geo_broadcasts:
                media = gb.get('media', {}).get('shortName', '')
                if media:
                    networks.append(media)
                    if media in {'ESPN+', 'Peacock', 'Prime Video', 'Apple TV+'}:
                        broadcast_type = 'Streaming'

            if home_team and away_team:
                broadcasts.append({
                    'event_id': event_id,
                    'home_team': home_team,
                    'away_team': away_team,
                    'game_date': event.get('date'),
                    'networks': list(set(networks)),
                    'primary': primary,
                    'type': broadcast_type,
                    'is_national': broadcast_type == 'National'
                })

    except Exception as e:
        print(f"  Error: {e}")

    return broadcasts


def update_games(conn, league, broadcasts):
    """Update SportsGame records with broadcast data"""
    cur = conn.cursor()
    updated = 0

    for bc in broadcasts:
        # Find matching game
        cur.execute('''
            UPDATE "SportsGame"
            SET "broadcastNetworks" = %s,
                "primaryBroadcast" = %s,
                "isNationalTV" = %s,
                "broadcastType" = %s
            WHERE league = %s
              AND "homeTeam" = %s
              AND "awayTeam" = %s
              AND ABS(EXTRACT(EPOCH FROM "gameDate" - %s::timestamp)) < 86400
              AND "broadcastNetworks" IS NULL
            RETURNING id
        ''', (
            bc['networks'],
            bc['primary'],
            bc['is_national'],
            bc['type'],
            league,
            bc['home_team'],
            bc['away_team'],
            bc['game_date']
        ))

        if cur.fetchone():
            updated += 1

    conn.commit()
    cur.close()
    return updated


def main():
    print("=" * 60)
    print("ENRICH BROADCAST DATA")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    db_url = load_db_url()
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    ensure_columns(cur)
    conn.commit()
    cur.close()

    total_updated = 0

    # Process each league
    for league in ['nba', 'nfl', 'nhl', 'mlb']:
        print(f"\n{league.upper()}:")

        # Fetch for past 30 days and next 7 days
        now = datetime.now(timezone.utc)
        dates_to_check = []

        for i in range(-30, 8):
            date = now + timedelta(days=i)
            dates_to_check.append(date.strftime('%Y%m%d'))

        league_updated = 0
        for date_str in dates_to_check:
            broadcasts = fetch_broadcasts_for_date(league, date_str)
            if broadcasts:
                updated = update_games(conn, league, broadcasts)
                league_updated += updated

            time.sleep(0.3)  # Rate limiting

        print(f"  Updated: {league_updated} games")
        total_updated += league_updated

    # Summary report
    cur = conn.cursor()
    cur.execute('''
        SELECT
            league,
            COUNT(*) as total_games,
            COUNT(*) FILTER (WHERE "isNationalTV" = true) as national_tv,
            COUNT(*) FILTER (WHERE "broadcastNetworks" IS NOT NULL) as with_broadcast
        FROM "SportsGame"
        WHERE league IN ('nba', 'nfl', 'nhl', 'mlb')
          AND "gameDate" > NOW() - INTERVAL '90 days'
        GROUP BY league
        ORDER BY league
    ''')

    print("\nBroadcast Coverage (last 90 days):")
    for row in cur.fetchall():
        league, total, national, with_bc = row
        print(f"  {league}: {with_bc}/{total} with broadcast, {national} national TV")

    cur.close()
    conn.close()

    print("\n" + "=" * 60)
    print(f"Total games updated: {total_updated}")
    print("=" * 60)


if __name__ == '__main__':
    main()