#!/usr/bin/env python3
"""
Build NHL Player ID Mapping Table
Maps NHL API IDs to SGO IDs via player names
Enables joining TOI/shots stats with prop lines
"""
import psycopg2
import re
from datetime import datetime, timezone

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().split('?')[0]
    return ''

def normalize_name(name):
    """Normalize player name for matching"""
    if not name:
        return ''
    # Convert SGO format: CONNOR_MCDAVID_1_NHL -> connor mcdavid
    name = re.sub(r'_\d+_NHL$', '', name, flags=re.IGNORECASE)
    name = name.replace('_', ' ').lower().strip()
    # Remove suffixes like Jr., III, etc.
    name = re.sub(r'\s+(jr\.?|sr\.?|ii|iii|iv)$', '', name, flags=re.IGNORECASE)
    # Remove apostrophes and hyphens for matching
    name = re.sub(r"['\-]", '', name)
    return name

def build_mapping():
    """Build the NHL player ID mapping table"""
    db_url = load_db_url()
    if not db_url:
        print('Error: SPORTS_DATABASE_URL not found')
        return
    
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    
    # Get unique SGO IDs from PlayerPropLine (name-style)
    print('Fetching NHL players from PlayerPropLine (SGO name-style IDs)...')
    cur.execute('''
        SELECT DISTINCT "playerExternalId"
        FROM "PlayerPropLine"
        WHERE league = 'nhl'
        AND "playerExternalId" LIKE '%_NHL'
    ''')
    sgo_name_players = cur.fetchall()
    print(f'  Found {len(sgo_name_players)} SGO name-style NHL player IDs')
    
    # Get unique numeric IDs from PlayerPropLine
    print('Fetching NHL players from PlayerPropLine (numeric IDs)...')
    cur.execute('''
        SELECT DISTINCT "playerExternalId"
        FROM "PlayerPropLine"
        WHERE league = 'nhl'
        AND "playerExternalId" ~ '^[0-9]+$'
        AND LENGTH("playerExternalId") < 10
    ''')
    sgo_numeric_players = cur.fetchall()
    print(f'  Found {len(sgo_numeric_players)} SGO numeric NHL player IDs')
    
    # Get unique NHL API IDs from PlayerGameMetric (has player names)
    print('Fetching NHL players from PlayerGameMetric (NHL API IDs)...')
    cur.execute('''
        SELECT DISTINCT "playerExternalId", "playerName"
        FROM "PlayerGameMetric"
        WHERE league = 'nhl'
        AND "playerExternalId" ~ '^[0-9]+$'
        AND "playerName" IS NOT NULL
    ''')
    nhl_players = cur.fetchall()
    print(f'  Found {len(nhl_players)} NHL API style player IDs')
    
    # Build SGO name mapping: extract names from IDs
    sgo_name_mapping = {}
    for (sgo_id,) in sgo_name_players:
        # Extract name from SGO ID: CONNOR_MCDAVID_1_NHL -> Connor Mcdavid
        name_match = re.match(r'^([A-Z_]+)_\d+_NHL$', sgo_id, re.IGNORECASE)
        if name_match:
            name_parts = name_match.group(1).split('_')
            player_name = ' '.join(p.capitalize() for p in name_parts)
            normalized = normalize_name(player_name)
            
            sgo_name_mapping[normalized] = {
                'playerName': player_name,
                'normalizedName': normalized,
                'sgoId': sgo_id,
            }
    
    print(f'  Extracted {len(sgo_name_mapping)} unique player names from SGO')
    
    # Build NHL API mapping (numeric ID -> name)
    nhl_mapping = {}
    for nhl_id, player_name in nhl_players:
        if not player_name:
            continue
        normalized = normalize_name(player_name)
        
        nhl_mapping[normalized] = {
            'nhlApiId': nhl_id,
            'playerName': player_name,
            'normalizedName': normalized,
        }
        # Also store by ID for direct matching
        nhl_mapping[f'id_{nhl_id}'] = {
            'nhlApiId': nhl_id,
            'playerName': player_name,
            'normalizedName': normalized,
        }
    
    print(f'  Extracted {len(nhl_players)} unique player names from NHL API data')
    
    # Build numeric ID set for direct matching
    numeric_prop_ids = set(pid for (pid,) in sgo_numeric_players)
    
    # Insert mappings
    insert_sql = '''
        INSERT INTO "PlayerMapping"
        (league, "playerName", "normalizedName", "nbaComId", "sgoId", "espnId")
        VALUES (%s, %s, %s, %s, %s, %s)
        ON CONFLICT (league, "normalizedName")
        DO UPDATE SET
            "nbaComId" = COALESCE(EXCLUDED."nbaComId", "PlayerMapping"."nbaComId"),
            "sgoId" = COALESCE(EXCLUDED."sgoId", "PlayerMapping"."sgoId"),
            "updatedAt" = NOW()
    '''
    
    inserted = 0
    name_matched = 0
    numeric_matched = 0
    
    # First, insert SGO name-style players and try to match with NHL API
    for normalized, data in sgo_name_mapping.items():
        nhl_id = None
        # Try to find matching NHL API ID by name
        if normalized in nhl_mapping:
            nhl_id = nhl_mapping[normalized]['nhlApiId']
            name_matched += 1
        
        try:
            cur.execute(insert_sql, (
                'nhl',
                data['playerName'],
                data['normalizedName'],
                nhl_id,  # Using nbaComId column for NHL API ID
                data['sgoId'],
                None,
            ))
            inserted += 1
        except Exception as e:
            print(f'  Error inserting {data["playerName"]}: {e}')
            conn.rollback()
    
    conn.commit()
    print(f'  Inserted {inserted} NHL name-style mappings, {name_matched} matched by name')
    
    # Now handle numeric IDs that directly match
    for nhl_id, player_name in nhl_players:
        if nhl_id in numeric_prop_ids:
            normalized = normalize_name(player_name)
            try:
                # Update existing record if exists, or insert new
                cur.execute('''
                    INSERT INTO "PlayerMapping"
                    (league, "playerName", "normalizedName", "nbaComId", "sgoId")
                    VALUES (%s, %s, %s, %s, %s)
                    ON CONFLICT (league, "normalizedName")
                    DO UPDATE SET
                        "nbaComId" = COALESCE(EXCLUDED."nbaComId", "PlayerMapping"."nbaComId"),
                        "sgoId" = COALESCE(%s, "PlayerMapping"."sgoId"),
                        "updatedAt" = NOW()
                ''', (
                    'nhl',
                    player_name,
                    normalized,
                    nhl_id,
                    nhl_id,  # The numeric ID serves as both NHL API ID and SGO ID
                    nhl_id,
                ))
                numeric_matched += 1
            except Exception as e:
                conn.rollback()
    
    conn.commit()
    print(f'  Added {numeric_matched} numeric ID direct matches')
    
    # Also insert NHL API players that weren't in SGO
    additional = 0
    for normalized, data in nhl_mapping.items():
        if normalized.startswith('id_'):
            continue  # Skip ID entries
        if normalized not in sgo_name_mapping:
            try:
                cur.execute(insert_sql, (
                    'nhl',
                    data['playerName'],
                    data['normalizedName'],
                    data['nhlApiId'],
                    None,
                    None,
                ))
                additional += 1
            except Exception as e:
                conn.rollback()
    
    conn.commit()
    print(f'  Added {additional} additional NHL API-only players')
    
    # Summary
    cur.execute('''
        SELECT 
            COUNT(*) as total,
            COUNT("nbaComId") as with_nhl_id,
            COUNT("sgoId") as with_sgo_id,
            COUNT(CASE WHEN "nbaComId" IS NOT NULL AND "sgoId" IS NOT NULL THEN 1 END) as fully_mapped
        FROM "PlayerMapping"
        WHERE league = 'nhl'
    ''')
    stats = cur.fetchone()
    
    print(f'\n✅ NHL Player Mapping Complete:')
    print(f'   Total: {stats[0]}')
    print(f'   With NHL API ID: {stats[1]}')
    print(f'   With SGO ID: {stats[2]}')
    print(f'   Fully Mapped (both IDs): {stats[3]}')
    
    # Show some sample mappings
    print('\n📋 Sample Fully Mapped Players:')
    cur.execute('''
        SELECT "playerName", "nbaComId", "sgoId"
        FROM "PlayerMapping"
        WHERE league = 'nhl'
        AND "nbaComId" IS NOT NULL
        AND "sgoId" IS NOT NULL
        ORDER BY "playerName"
        LIMIT 10
    ''')
    for row in cur.fetchall():
        print(f'   {row[0]}: NHL API={row[1]}, SGO={row[2]}')
    
    cur.close()
    conn.close()
    
    return {'success': True, 'total': stats[0], 'fully_mapped': stats[3]}

if __name__ == '__main__':
    build_mapping()