#!/usr/bin/env python3
"""
Apply fix to ingest_upcoming_schedules.py to prevent duplicates.
This creates a patched version that uses full team names and checks for existing games.
"""
import os

SCRIPT_PATH = '/var/www/html/eventheodds/scripts/ingest_upcoming_schedules.py'
BACKUP_PATH = '/var/www/html/eventheodds/scripts/ingest_upcoming_schedules.py.backup'

# The new normalize function that returns FULL NAMES instead of abbreviations
NEW_NORMALIZE_FUNCTION = '''
def normalize_team_to_full_name(team_name, league):
    """Convert any team name/abbreviation to its full canonical name."""
    if not team_name:
        return ''
    
    # Import from team_normalizer if available
    try:
        from team_normalizer import normalize_team_name
        return normalize_team_name(team_name, league)
    except ImportError:
        pass
    
    # Fallback mappings (abbreviation -> full name)
    team_full_names = {
        # NBA
        'ATL': 'Atlanta Hawks', 'BOS': 'Boston Celtics', 'BKN': 'Brooklyn Nets',
        'CHA': 'Charlotte Hornets', 'CHI': 'Chicago Bulls', 'CLE': 'Cleveland Cavaliers',
        'DAL': 'Dallas Mavericks', 'DEN': 'Denver Nuggets', 'DET': 'Detroit Pistons',
        'GSW': 'Golden State Warriors', 'HOU': 'Houston Rockets', 'IND': 'Indiana Pacers',
        'LAC': 'Los Angeles Clippers', 'LAL': 'Los Angeles Lakers', 'MEM': 'Memphis Grizzlies',
        'MIA': 'Miami Heat', 'MIL': 'Milwaukee Bucks', 'MIN': 'Minnesota Timberwolves',
        'NOP': 'New Orleans Pelicans', 'NYK': 'New York Knicks', 'OKC': 'Oklahoma City Thunder',
        'ORL': 'Orlando Magic', 'PHI': 'Philadelphia 76ers', 'PHX': 'Phoenix Suns',
        'POR': 'Portland Trail Blazers', 'SAC': 'Sacramento Kings', 'SAS': 'San Antonio Spurs',
        'TOR': 'Toronto Raptors', 'UTA': 'Utah Jazz', 'WAS': 'Washington Wizards',
        # NHL
        'ANA': 'Anaheim Ducks', 'ARI': 'Arizona Coyotes', 'BOS': 'Boston Bruins',
        'BUF': 'Buffalo Sabres', 'CGY': 'Calgary Flames', 'CAR': 'Carolina Hurricanes',
        'CHI': 'Chicago Blackhawks', 'COL': 'Colorado Avalanche', 'CBJ': 'Columbus Blue Jackets',
        'DAL': 'Dallas Stars', 'DET': 'Detroit Red Wings', 'EDM': 'Edmonton Oilers',
        'FLA': 'Florida Panthers', 'LAK': 'Los Angeles Kings', 'MIN': 'Minnesota Wild',
        'MTL': 'Montreal Canadiens', 'NSH': 'Nashville Predators', 'NJD': 'New Jersey Devils',
        'NYI': 'New York Islanders', 'NYR': 'New York Rangers', 'OTT': 'Ottawa Senators',
        'PHI': 'Philadelphia Flyers', 'PIT': 'Pittsburgh Penguins', 'SJS': 'San Jose Sharks',
        'SEA': 'Seattle Kraken', 'STL': 'St. Louis Blues', 'TBL': 'Tampa Bay Lightning',
        'TOR': 'Toronto Maple Leafs', 'VAN': 'Vancouver Canucks', 'VGK': 'Vegas Golden Knights',
        'WSH': 'Washington Capitals', 'WPG': 'Winnipeg Jets',
        # NFL
        'ARI': 'Arizona Cardinals', 'ATL': 'Atlanta Falcons', 'BAL': 'Baltimore Ravens',
        'BUF': 'Buffalo Bills', 'CAR': 'Carolina Panthers', 'CHI': 'Chicago Bears',
        'CIN': 'Cincinnati Bengals', 'CLE': 'Cleveland Browns', 'DAL': 'Dallas Cowboys',
        'DEN': 'Denver Broncos', 'DET': 'Detroit Lions', 'GB': 'Green Bay Packers',
        'HOU': 'Houston Texans', 'IND': 'Indianapolis Colts', 'JAX': 'Jacksonville Jaguars',
        'KC': 'Kansas City Chiefs', 'LV': 'Las Vegas Raiders', 'LAC': 'Los Angeles Chargers',
        'LAR': 'Los Angeles Rams', 'MIA': 'Miami Dolphins', 'MIN': 'Minnesota Vikings',
        'NE': 'New England Patriots', 'NO': 'New Orleans Saints', 'NYG': 'New York Giants',
        'NYJ': 'New York Jets', 'PHI': 'Philadelphia Eagles', 'PIT': 'Pittsburgh Steelers',
        'SF': 'San Francisco 49ers', 'SEA': 'Seattle Seahawks', 'TB': 'Tampa Bay Buccaneers',
        'TEN': 'Tennessee Titans', 'WAS': 'Washington Commanders',
    }
    
    # Reverse mapping (full name -> full name for validation)
    full_name_set = set(team_full_names.values())
    
    name_upper = team_name.upper().strip()
    name_title = team_name.strip()
    
    # Already a full name?
    if name_title in full_name_set:
        return name_title
    
    # Is it an abbreviation?
    if name_upper in team_full_names:
        return team_full_names[name_upper]
    
    # Try lowercase lookup
    name_lower = team_name.lower().strip()
    for full_name in full_name_set:
        if full_name.lower() == name_lower:
            return full_name
    
    # Return as-is if no match (for college teams, etc.)
    return team_name


'''

# The new INSERT logic with duplicate checking
NEW_INSERT_LOGIC = '''
            # Check if game already exists (by teams and date, not just externalGameId)
            cur.execute("""
                SELECT id FROM "SportsGame" 
                WHERE league = %s 
                  AND "gameDate"::date = %s::date
                  AND (
                      ("homeTeam" = %s AND "awayTeam" = %s)
                      OR ("homeTeam" ILIKE %s AND "awayTeam" ILIKE %s)
                  )
                LIMIT 1
            """, (league, game_date, home_team_full, away_team_full, 
                   f"%{home_team_full.split()[-1]}%", f"%{away_team_full.split()[-1]}%"))
            
            existing = cur.fetchone()
            if existing:
                # Update existing record
                cur.execute("""
                    UPDATE "SportsGame" 
                    SET "externalGameId" = COALESCE("externalGameId", %s),
                        "updatedAt" = NOW()
                    WHERE id = %s
                """, (event_id, existing[0]))
                total_updated += 1
                continue

            try:
                # Insert new game with full team names
                cur.execute(\'\'\'
                    INSERT INTO "SportsGame" (
                        league, season, "externalGameId", "gameDate", "homeTeam", "awayTeam",
                        status, "createdAt", "updatedAt"
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, \'scheduled\', NOW(), NOW())
                    ON CONFLICT (league, "externalGameId")
                    DO UPDATE SET
                        "gameDate" = EXCLUDED."gameDate",
                        "updatedAt" = NOW()
                    RETURNING (xmax = 0) AS inserted
                \'\'\', (league, season_year, event_id, game_date, home_team_full, away_team_full))

                result = cur.fetchone()
                if result and result[0]:
                    total_added += 1
                else:
                    total_updated += 1
'''

def apply_patch():
    print("Applying patch to ingest_upcoming_schedules.py...")
    
    # Read original file
    with open(SCRIPT_PATH, 'r') as f:
        content = f.read()
    
    # Create backup
    with open(BACKUP_PATH, 'w') as f:
        f.write(content)
    print(f"Backup created: {BACKUP_PATH}")
    
    # Replace normalize_team_abbrev with normalize_team_to_full_name
    content = content.replace(
        'home_abbrev = normalize_team_abbrev(home_team, league)',
        'home_team_full = normalize_team_to_full_name(home_team, league)'
    )
    content = content.replace(
        'away_abbrev = normalize_team_abbrev(away_team, league)',
        'away_team_full = normalize_team_to_full_name(away_team, league)'
    )
    content = content.replace('home_abbrev', 'home_team_full')
    content = content.replace('away_abbrev', 'away_team_full')
    
    # Add the new function after the imports
    if 'def normalize_team_to_full_name' not in content:
        # Find where to insert (after normalize_team_abbrev function)
        insert_pos = content.find('def ingest_schedules(')
        if insert_pos > 0:
            content = content[:insert_pos] + NEW_NORMALIZE_FUNCTION + '\n\n' + content[insert_pos:]
    
    # Write patched file
    with open(SCRIPT_PATH, 'w') as f:
        f.write(content)
    
    print("Patch applied successfully!")
    print("Changes made:")
    print("  1. Added normalize_team_to_full_name() function")
    print("  2. Updated references to use full team names")
    print("")
    print("To revert: cp {} {}".format(BACKUP_PATH, SCRIPT_PATH))

if __name__ == '__main__':
    apply_patch()