#!/usr/bin/env python3
"""
Critical Data Gap Backfill Script
Addresses:
1. NHL Goalie Stats (from NHL API skaters/goalies endpoint)
2. Soccer Half Scoring (from ESPN API)
3. Injury Timestamps (add tracking to existing injury records)
"""
import requests
import psycopg2
import json
import os
import time
from datetime import datetime, timezone, timedelta

REQUEST_DELAY = 0.5

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 get_db_connection():
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')
    return psycopg2.connect(db_url)


# =============================================================================
# NHL GOALIE STATS
# =============================================================================
def fetch_nhl_all_goalies(season='20252026'):
    """Fetch all NHL goalies from the stats endpoint"""
    url = f'https://api-web.nhle.com/v1/goalie-stats-leaders/{season}/2?limit=100'

    all_goalies = {}

    for category in ['wins', 'savePctg', 'goalsAgainstAverage', 'shutouts']:
        cat_url = f'https://api-web.nhle.com/v1/goalie-stats-leaders/{season}/2?categories={category}&limit=100'
        try:
            resp = requests.get(cat_url, timeout=30)
            if resp.status_code == 200:
                data = resp.json()
                if category in data:
                    for goalie in data[category]:
                        pid = goalie.get('playerId')
                        if pid not in all_goalies:
                            all_goalies[pid] = {
                                'playerId': pid,
                                'firstName': goalie.get('firstName', {}).get('default', ''),
                                'lastName': goalie.get('lastName', {}).get('default', ''),
                                'teamAbbrev': goalie.get('teamAbbrevs', ''),
                                'gamesPlayed': goalie.get('gamesPlayed'),
                                'wins': goalie.get('wins'),
                                'losses': goalie.get('losses'),
                                'otLosses': goalie.get('otLosses'),
                                'savePctg': goalie.get('savePctg'),
                                'goalsAgainstAvg': goalie.get('goalsAgainstAverage'),
                                'shutouts': goalie.get('shutouts'),
                                'shotsAgainst': goalie.get('shotsAgainst'),
                                'saves': goalie.get('saves'),
                            }
                        else:
                            # Merge additional stats
                            for k in ['wins', 'losses', 'otLosses', 'savePctg', 'goalsAgainstAvg',
                                     'shutouts', 'gamesPlayed', 'shotsAgainst', 'saves']:
                                if goalie.get(k) is not None:
                                    all_goalies[pid][k] = goalie.get(k)
            time.sleep(REQUEST_DELAY)
        except Exception as e:
            print(f"  Error fetching {category}: {e}")

    return list(all_goalies.values())


def ensure_goalie_table(cur):
    """Create NHL goalie stats table"""
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "NHLGoalieStats" (
            id BIGSERIAL PRIMARY KEY,
            season VARCHAR(20),
            "playerId" INT,
            "firstName" VARCHAR(100),
            "lastName" VARCHAR(100),
            "teamAbbrev" VARCHAR(10),
            "gamesPlayed" INT,
            wins INT,
            losses INT,
            "otLosses" INT,
            "savePctg" FLOAT,
            "goalsAgainstAvg" FLOAT,
            shutouts INT,
            "shotsAgainst" INT,
            saves INT,
            "updatedAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(season, "playerId")
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "NHLGoalieStats_team_idx" ON "NHLGoalieStats" ("teamAbbrev", season)')


def backfill_nhl_goalies():
    """Backfill NHL goalie stats to database"""
    print("\n" + "="*60)
    print("NHL GOALIE STATS BACKFILL")
    print("="*60)

    conn = get_db_connection()
    cur = conn.cursor()

    ensure_goalie_table(cur)
    conn.commit()

    total = 0
    for season in ['20252026', '20242025']:
        print(f"\nFetching goalies for season {season}...")
        goalies = fetch_nhl_all_goalies(season)
        print(f"  Found {len(goalies)} goalies")

        for goalie in goalies:
            try:
                cur.execute('''
                    INSERT INTO "NHLGoalieStats" (
                        season, "playerId", "firstName", "lastName", "teamAbbrev",
                        "gamesPlayed", wins, losses, "otLosses", "savePctg",
                        "goalsAgainstAvg", shutouts, "shotsAgainst", saves
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    ON CONFLICT (season, "playerId") DO UPDATE SET
                        "gamesPlayed" = EXCLUDED."gamesPlayed",
                        wins = EXCLUDED.wins,
                        losses = EXCLUDED.losses,
                        "otLosses" = EXCLUDED."otLosses",
                        "savePctg" = EXCLUDED."savePctg",
                        "goalsAgainstAvg" = EXCLUDED."goalsAgainstAvg",
                        shutouts = EXCLUDED.shutouts,
                        "shotsAgainst" = EXCLUDED."shotsAgainst",
                        saves = EXCLUDED.saves,
                        "updatedAt" = NOW()
                ''', (
                    season, goalie['playerId'], goalie['firstName'], goalie['lastName'],
                    goalie['teamAbbrev'], goalie.get('gamesPlayed'), goalie.get('wins'),
                    goalie.get('losses'), goalie.get('otLosses'), goalie.get('savePctg'),
                    goalie.get('goalsAgainstAvg'), goalie.get('shutouts'),
                    goalie.get('shotsAgainst'), goalie.get('saves')
                ))
                total += 1
            except Exception as e:
                print(f"    Error inserting goalie {goalie.get('lastName')}: {e}")

        conn.commit()

    cur.close()
    conn.close()

    print(f"\n✅ Inserted/updated {total} goalie records")
    return total


# =============================================================================
# SOCCER HALF SCORING
# =============================================================================
ESPN_BASE = 'https://site.api.espn.com/apis/site/v2/sports/soccer'

def fetch_soccer_match_details(league, match_id):
    """Fetch match details including half scores from ESPN"""
    league_map = {
        'epl': 'eng.1',
        'eng.1': 'eng.1',
        'laliga': 'esp.1',
        'esp.1': 'esp.1',
        'seriea': 'ita.1',
        'ita.1': 'ita.1',
        'bundesliga': 'ger.1',
        'ger.1': 'ger.1',
        'ligue1': 'fra.1',
        'fra.1': 'fra.1',
    }
    espn_league = league_map.get(league.lower(), league)

    url = f'{ESPN_BASE}/{espn_league}/summary?event={match_id}'

    try:
        resp = requests.get(url, timeout=30)
        if resp.status_code != 200:
            return None

        data = resp.json()
        boxscore = data.get('boxscore', {})
        teams = boxscore.get('teams', [])

        result = {'match_id': match_id}

        for i, team in enumerate(teams):
            prefix = 'home' if i == 0 else 'away'
            stats = team.get('statistics', [])

            # Look for half-time scores in line scores
            if 'linescores' in data:
                linescores = data.get('linescores', {})

            # Parse period scores from header
            header = data.get('header', {})
            competitions = header.get('competitions', [])
            if competitions:
                competitors = competitions[0].get('competitors', [])
                for comp in competitors:
                    if comp.get('homeAway') == prefix:
                        linescores_data = comp.get('linescores', [])
                        if len(linescores_data) >= 2:
                            result[f'{prefix}_1h'] = linescores_data[0].get('value', 0)
                            result[f'{prefix}_2h'] = linescores_data[1].get('value', 0)

        return result if 'home_1h' in result else None

    except Exception as e:
        return None


def ensure_soccer_period_table(cur):
    """Add period scoring columns to existing structure or create tracking table"""
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "SoccerPeriodScoring" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "gameId" BIGINT,
            "externalGameId" VARCHAR(100),
            "gameDate" TIMESTAMP,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            "home1H" INT,
            "home2H" INT,
            "away1H" INT,
            "away2H" INT,
            "homeTotal" INT,
            "awayTotal" INT,
            "createdAt" TIMESTAMP DEFAULT NOW(),
            UNIQUE(league, "gameId")
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "SoccerPeriodScoring_league_idx" ON "SoccerPeriodScoring" (league, "gameDate")')


def backfill_soccer_half_scoring():
    """Backfill soccer half scoring from ESPN"""
    print("\n" + "="*60)
    print("SOCCER HALF SCORING BACKFILL")
    print("="*60)

    conn = get_db_connection()
    cur = conn.cursor()

    ensure_soccer_period_table(cur)
    conn.commit()

    # Get recent EPL games that don't have period scoring yet
    cur.execute('''
        SELECT id, "externalGameId", "gameDate", "homeTeam", "awayTeam", "homeScore", "awayScore"
        FROM "SportsGame"
        WHERE league = 'epl'
          AND "homeScore" IS NOT NULL
          AND "gameDate" > NOW() - INTERVAL '90 days'
        ORDER BY "gameDate" DESC
        LIMIT 200
    ''')

    games = cur.fetchall()
    print(f"Found {len(games)} EPL games to process")

    total = 0
    for game_id, ext_id, game_date, home, away, home_score, away_score in games:
        if not ext_id:
            continue

        # Try to get period scoring
        time.sleep(REQUEST_DELAY)
        details = fetch_soccer_match_details('epl', ext_id)

        if details and 'home_1h' in details:
            try:
                cur.execute('''
                    INSERT INTO "SoccerPeriodScoring" (
                        league, "gameId", "externalGameId", "gameDate",
                        "homeTeam", "awayTeam", "home1H", "home2H", "away1H", "away2H",
                        "homeTotal", "awayTotal"
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    ON CONFLICT (league, "gameId") DO UPDATE SET
                        "home1H" = EXCLUDED."home1H",
                        "home2H" = EXCLUDED."home2H",
                        "away1H" = EXCLUDED."away1H",
                        "away2H" = EXCLUDED."away2H"
                ''', (
                    'epl', game_id, ext_id, game_date, home, away,
                    details.get('home_1h'), details.get('home_2h'),
                    details.get('away_1h'), details.get('away_2h'),
                    home_score, away_score
                ))
                total += 1
                if total % 20 == 0:
                    print(f"  Processed {total} games with half scoring...")
                    conn.commit()
            except Exception as e:
                print(f"  Error: {e}")

    conn.commit()
    cur.close()
    conn.close()

    print(f"\n✅ Added half scoring for {total} soccer matches")
    return total


# =============================================================================
# INJURY TIMESTAMP TRACKING
# =============================================================================
def ensure_injury_history_table(cur):
    """Create injury status history table"""
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "InjuryStatusHistory" (
            id BIGSERIAL PRIMARY KEY,
            league VARCHAR(20),
            "playerId" VARCHAR(100),
            "playerName" VARCHAR(200),
            team VARCHAR(50),
            status VARCHAR(50),
            "previousStatus" VARCHAR(50),
            injury VARCHAR(200),
            "statusChangedAt" TIMESTAMP,
            "gameId" BIGINT,
            "gameDate" TIMESTAMP,
            "hoursBeforeGame" FLOAT,
            "createdAt" TIMESTAMP DEFAULT NOW()
        )
    ''')
    cur.execute('CREATE INDEX IF NOT EXISTS "InjuryStatusHistory_player_idx" ON "InjuryStatusHistory" ("playerId", "statusChangedAt")')
    cur.execute('CREATE INDEX IF NOT EXISTS "InjuryStatusHistory_game_idx" ON "InjuryStatusHistory" ("gameId")')


def backfill_injury_timestamps():
    """Add timestamp tracking to existing injury records"""
    print("\n" + "="*60)
    print("INJURY TIMESTAMP TRACKING")
    print("="*60)

    conn = get_db_connection()
    cur = conn.cursor()

    ensure_injury_history_table(cur)
    conn.commit()

    # Add statusChangedAt column to PlayerInjury if not exists
    try:
        cur.execute('''
            ALTER TABLE "PlayerInjury"
            ADD COLUMN IF NOT EXISTS "statusChangedAt" TIMESTAMP,
            ADD COLUMN IF NOT EXISTS "previousStatus" VARCHAR(50)
        ''')
        conn.commit()
        print("  Added timestamp columns to PlayerInjury table")
    except Exception as e:
        print(f"  Column may already exist: {e}")
        conn.rollback()

    # Get current injuries and set initial timestamps
    cur.execute('''
        UPDATE "PlayerInjury"
        SET "statusChangedAt" = COALESCE("statusChangedAt", "updatedAt", "createdAt", NOW())
        WHERE "statusChangedAt" IS NULL
    ''')
    updated = cur.rowcount
    conn.commit()

    print(f"  Set timestamps for {updated} existing injury records")

    # Create snapshot of current injury statuses for future tracking
    cur.execute('''
        INSERT INTO "InjuryStatusHistory" (
            league, "playerId", "playerName", team, status, injury, "statusChangedAt"
        )
        SELECT
            league,
            "playerExternalId",
            "playerName",
            team,
            status,
            injury,
            COALESCE("statusChangedAt", "updatedAt", NOW())
        FROM "PlayerInjury"
        WHERE status IS NOT NULL
        ON CONFLICT DO NOTHING
    ''')
    snapshot_count = cur.rowcount
    conn.commit()

    cur.close()
    conn.close()

    print(f"✅ Created {snapshot_count} injury status history records")
    return snapshot_count


# =============================================================================
# NBA LINEUP FIX
# =============================================================================
def fix_nba_lineup_tables():
    """Fix NBA lineup table structure"""
    print("\n" + "="*60)
    print("NBA LINEUP TABLE FIX")
    print("="*60)

    conn = get_db_connection()
    cur = conn.cursor()

    # Drop and recreate tables with proper structure
    cur.execute('DROP TABLE IF EXISTS "NBALineup" CASCADE')
    cur.execute('DROP TABLE IF EXISTS "NBAPlayerOnOff" CASCADE')

    cur.execute('''
        CREATE TABLE "NBALineup" (
            id BIGSERIAL PRIMARY KEY,
            season VARCHAR(20),
            team_id INT,
            team VARCHAR(50),
            lineup_id VARCHAR(100),
            player1 VARCHAR(100),
            player2 VARCHAR(100),
            player3 VARCHAR(100),
            player4 VARCHAR(100),
            player5 VARCHAR(100),
            minutes FLOAT,
            plus_minus FLOAT,
            off_rating FLOAT,
            def_rating FLOAT,
            net_rating FLOAT,
            pace FLOAT,
            gp INT,
            wins INT,
            losses INT,
            updated_at TIMESTAMP DEFAULT NOW(),
            UNIQUE(season, team_id, lineup_id)
        )
    ''')

    cur.execute('''
        CREATE TABLE "NBAPlayerOnOff" (
            id BIGSERIAL PRIMARY KEY,
            season VARCHAR(20),
            player_id INT,
            player_name VARCHAR(100),
            team_id INT,
            team VARCHAR(50),
            on_court_min FLOAT,
            on_court_plus_minus FLOAT,
            on_court_off_rtg FLOAT,
            on_court_def_rtg FLOAT,
            on_court_net_rtg FLOAT,
            off_court_min FLOAT,
            off_court_plus_minus FLOAT,
            off_court_off_rtg FLOAT,
            off_court_def_rtg FLOAT,
            off_court_net_rtg FLOAT,
            on_off_diff FLOAT,
            updated_at TIMESTAMP DEFAULT NOW(),
            UNIQUE(season, player_id)
        )
    ''')

    cur.execute('CREATE INDEX idx_nbalineup_team ON "NBALineup" (team_id, season)')
    cur.execute('CREATE INDEX idx_nbalineup_netrating ON "NBALineup" (net_rating DESC)')
    cur.execute('CREATE INDEX idx_nbaplayeronoff_team ON "NBAPlayerOnOff" (team_id, season)')
    cur.execute('CREATE INDEX idx_nbaplayeronoff_diff ON "NBAPlayerOnOff" (on_off_diff DESC)')

    conn.commit()
    cur.close()
    conn.close()

    print("✅ NBA lineup tables recreated with fixed structure")


def fetch_nba_lineups_fixed():
    """Fetch NBA lineup data with fixed table structure"""
    print("\n" + "="*60)
    print("NBA LINEUP DATA FETCH")
    print("="*60)

    NBA_STATS_BASE = 'https://stats.nba.com/stats'
    NBA_HEADERS = {
        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36',
        'Accept': 'application/json',
        'Accept-Language': 'en-US,en;q=0.9',
        'Origin': 'https://www.nba.com',
        'Referer': 'https://www.nba.com/',
        'x-nba-stats-origin': 'stats',
        'x-nba-stats-token': 'true',
    }

    teams = {
        1610612737: 'ATL', 1610612738: 'BOS', 1610612751: 'BKN', 1610612766: 'CHA',
        1610612741: 'CHI', 1610612739: 'CLE', 1610612742: 'DAL', 1610612743: 'DEN',
        1610612765: 'DET', 1610612744: 'GSW', 1610612745: 'HOU', 1610612754: 'IND',
        1610612746: 'LAC', 1610612747: 'LAL', 1610612763: 'MEM', 1610612748: 'MIA',
        1610612749: 'MIL', 1610612750: 'MIN', 1610612740: 'NOP', 1610612752: 'NYK',
        1610612760: 'OKC', 1610612753: 'ORL', 1610612755: 'PHI', 1610612756: 'PHX',
        1610612757: 'POR', 1610612758: 'SAC', 1610612759: 'SAS', 1610612761: 'TOR',
        1610612762: 'UTA', 1610612764: 'WAS',
    }

    conn = get_db_connection()
    cur = conn.cursor()

    season = '2024-25'
    total_lineups = 0
    total_on_off = 0
    blocked = False

    for team_id, team_abbr in teams.items():
        if blocked:
            break

        print(f"\n{team_abbr}: Fetching...")
        time.sleep(1.5)  # Longer delay to avoid blocking

        # Fetch lineups
        try:
            url = f'{NBA_STATS_BASE}/teamdashlineups'
            params = {
                'TeamID': team_id,
                'Season': season,
                'SeasonType': 'Regular Season',
                'MeasureType': 'Advanced',
                'PerMode': 'PerGame',
                'GroupQuantity': 5,
            }

            resp = requests.get(url, headers=NBA_HEADERS, params=params, timeout=30)

            if resp.status_code == 403:
                print(f"  Access denied - NBA.com blocking requests")
                blocked = True
                break

            if resp.status_code == 200:
                data = resp.json()
                for rs in data.get('resultSets', []):
                    if rs.get('name') == 'Lineups':
                        headers = rs.get('headers', [])
                        for row in rs.get('rowSet', []):
                            row_dict = dict(zip(headers, row))
                            players = row_dict.get('GROUP_NAME', '').split(' - ') if row_dict.get('GROUP_NAME') else []

                            cur.execute('''
                                INSERT INTO "NBALineup" (
                                    season, team_id, team, lineup_id,
                                    player1, player2, player3, player4, player5,
                                    minutes, plus_minus, off_rating, def_rating, net_rating,
                                    pace, gp, wins, losses
                                )
                                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                                ON CONFLICT (season, team_id, lineup_id) DO UPDATE SET
                                    minutes = EXCLUDED.minutes,
                                    plus_minus = EXCLUDED.plus_minus,
                                    off_rating = EXCLUDED.off_rating,
                                    def_rating = EXCLUDED.def_rating,
                                    net_rating = EXCLUDED.net_rating,
                                    updated_at = NOW()
                            ''', (
                                season, team_id, team_abbr, row_dict.get('GROUP_ID'),
                                players[0] if len(players) > 0 else None,
                                players[1] if len(players) > 1 else None,
                                players[2] if len(players) > 2 else None,
                                players[3] if len(players) > 3 else None,
                                players[4] if len(players) > 4 else None,
                                row_dict.get('MIN'), row_dict.get('PLUS_MINUS'),
                                row_dict.get('OFF_RATING'), row_dict.get('DEF_RATING'),
                                row_dict.get('NET_RATING'), row_dict.get('PACE'),
                                row_dict.get('GP'), row_dict.get('W'), row_dict.get('L'),
                            ))
                            total_lineups += 1
                        print(f"  {len(rs.get('rowSet', []))} lineups")
        except Exception as e:
            print(f"  Error: {e}")

        # Fetch on/off data
        time.sleep(1)
        try:
            url = f'{NBA_STATS_BASE}/teamplayeronoffdetails'
            params = {
                'TeamID': team_id,
                'Season': season,
                'SeasonType': 'Regular Season',
                'MeasureType': 'Advanced',
                'PerMode': 'PerGame',
            }

            resp = requests.get(url, headers=NBA_HEADERS, params=params, timeout=30)

            if resp.status_code == 200:
                data = resp.json()
                on_court_data = {}
                off_court_data = {}

                for rs in data.get('resultSets', []):
                    headers = rs.get('headers', [])
                    if rs.get('name') == 'PlayersOnCourtTeamPlayerOnOffDetails':
                        for row in rs.get('rowSet', []):
                            row_dict = dict(zip(headers, row))
                            pid = row_dict.get('VS_PLAYER_ID')
                            on_court_data[pid] = {
                                'player_id': pid,
                                'player_name': row_dict.get('VS_PLAYER_NAME'),
                                'min': row_dict.get('MIN'),
                                'plus_minus': row_dict.get('PLUS_MINUS'),
                                'off_rating': row_dict.get('OFF_RATING'),
                                'def_rating': row_dict.get('DEF_RATING'),
                                'net_rating': row_dict.get('NET_RATING'),
                            }
                    elif rs.get('name') == 'PlayersOffCourtTeamPlayerOnOffDetails':
                        for row in rs.get('rowSet', []):
                            row_dict = dict(zip(headers, row))
                            pid = row_dict.get('VS_PLAYER_ID')
                            off_court_data[pid] = {
                                'min': row_dict.get('MIN'),
                                'plus_minus': row_dict.get('PLUS_MINUS'),
                                'off_rating': row_dict.get('OFF_RATING'),
                                'def_rating': row_dict.get('DEF_RATING'),
                                'net_rating': row_dict.get('NET_RATING'),
                            }

                for pid, on_data in on_court_data.items():
                    off_data = off_court_data.get(pid, {})
                    on_net = on_data.get('net_rating') or 0
                    off_net = off_data.get('net_rating') or 0

                    cur.execute('''
                        INSERT INTO "NBAPlayerOnOff" (
                            season, player_id, player_name, team_id, team,
                            on_court_min, on_court_plus_minus, on_court_off_rtg, on_court_def_rtg, on_court_net_rtg,
                            off_court_min, off_court_plus_minus, off_court_off_rtg, off_court_def_rtg, off_court_net_rtg,
                            on_off_diff
                        )
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        ON CONFLICT (season, player_id) DO UPDATE SET
                            on_court_min = EXCLUDED.on_court_min,
                            on_court_plus_minus = EXCLUDED.on_court_plus_minus,
                            on_court_net_rtg = EXCLUDED.on_court_net_rtg,
                            off_court_net_rtg = EXCLUDED.off_court_net_rtg,
                            on_off_diff = EXCLUDED.on_off_diff,
                            updated_at = NOW()
                    ''', (
                        season, on_data['player_id'], on_data.get('player_name'),
                        team_id, team_abbr,
                        on_data.get('min'), on_data.get('plus_minus'),
                        on_data.get('off_rating'), on_data.get('def_rating'), on_data.get('net_rating'),
                        off_data.get('min'), off_data.get('plus_minus'),
                        off_data.get('off_rating'), off_data.get('def_rating'), off_data.get('net_rating'),
                        on_net - off_net if on_net and off_net else None,
                    ))
                    total_on_off += 1

                print(f"  {len(on_court_data)} on/off records")
        except Exception as e:
            print(f"  On/off error: {e}")

        conn.commit()

    cur.close()
    conn.close()

    print(f"\n{'='*60}")
    print(f"TOTAL: {total_lineups} lineups, {total_on_off} on/off records")
    if blocked:
        print("Note: NBA.com blocked further requests")
    print("="*60)

    return {'lineups': total_lineups, 'on_off': total_on_off}


# =============================================================================
# MAIN
# =============================================================================
def main():
    print("="*60)
    print("CRITICAL DATA GAP BACKFILL")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("="*60)

    results = {}

    # 1. NHL Goalie Stats
    try:
        results['nhl_goalies'] = backfill_nhl_goalies()
    except Exception as e:
        print(f"NHL Goalie backfill failed: {e}")
        results['nhl_goalies'] = 0

    # 2. Fix NBA lineup tables and fetch data
    try:
        fix_nba_lineup_tables()
        results['nba_lineups'] = fetch_nba_lineups_fixed()
    except Exception as e:
        print(f"NBA Lineup backfill failed: {e}")
        import traceback
        traceback.print_exc()
        results['nba_lineups'] = {'lineups': 0, 'on_off': 0}

    # 3. Soccer Half Scoring
    try:
        results['soccer_halves'] = backfill_soccer_half_scoring()
    except Exception as e:
        print(f"Soccer half scoring backfill failed: {e}")
        results['soccer_halves'] = 0

    # 4. Injury Timestamps
    try:
        results['injury_timestamps'] = backfill_injury_timestamps()
    except Exception as e:
        print(f"Injury timestamp backfill failed: {e}")
        results['injury_timestamps'] = 0

    print("\n" + "="*60)
    print("BACKFILL COMPLETE")
    print("="*60)
    print(f"NHL Goalies: {results.get('nhl_goalies', 0)}")
    print(f"NBA Lineups: {results.get('nba_lineups', {}).get('lineups', 0)}")
    print(f"NBA On/Off: {results.get('nba_lineups', {}).get('on_off', 0)}")
    print(f"Soccer Half Scores: {results.get('soccer_halves', 0)}")
    print(f"Injury Timestamps: {results.get('injury_timestamps', 0)}")
    print("="*60)


if __name__ == '__main__':
    main()