#!/usr/bin/env python3
"""
Build Player ID Mapping Table
Maps NBA.com IDs to SGO IDs via player names
Enables joining usage/advanced 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: LEBRON_JAMES_1_NBA -> lebron james
    name = re.sub(r'_\d+_(NBA|NFL|NHL|MLB|WNBA)$', '', 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 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()
    
    # Create mapping table
    print('Creating PlayerMapping table...')
    cur.execute('''
        CREATE TABLE IF NOT EXISTS "PlayerMapping" (
            id BIGSERIAL PRIMARY KEY,
            league TEXT NOT NULL,
            "playerName" TEXT NOT NULL,
            "normalizedName" TEXT NOT NULL,
            "nbaComId" TEXT,
            "sgoId" TEXT,
            "espnId" TEXT,
            "bdlId" TEXT,
            "createdAt" TIMESTAMPTZ DEFAULT NOW(),
            "updatedAt" TIMESTAMPTZ DEFAULT NOW(),
            UNIQUE(league, "normalizedName")
        )
    ''')
    conn.commit()
    
    # Get unique players from PlayerGameMetric (NBA.com IDs)
    print('Fetching players from PlayerGameMetric...')
    cur.execute('''
        SELECT DISTINCT "playerExternalId", league
        FROM "PlayerGameMetric"
        WHERE "playerExternalId" IS NOT NULL
        AND "playerExternalId" ~ '^[0-9]+$'
    ''')
    nbacom_players = cur.fetchall()
    print(f'  Found {len(nbacom_players)} NBA.com style IDs')
    
    # Get unique players from PlayerPropLine (SGO IDs)
    print('Fetching players from PlayerPropLine...')
    cur.execute('''
        SELECT DISTINCT "playerExternalId", league
        FROM "PlayerPropLine"
        WHERE "playerExternalId" IS NOT NULL
        AND "playerExternalId" LIKE '%_%'
    ''')
    sgo_players = cur.fetchall()
    print(f'  Found {len(sgo_players)} SGO style IDs')
    
    # Build mapping from SGO IDs (they contain names)
    print('Building mappings from SGO IDs...')
    sgo_mapping = {}
    for sgo_id, league in sgo_players:
        # Extract name from SGO ID: LEBRON_JAMES_1_NBA -> LeBron James
        name_match = re.match(r'^([A-Z_]+)_\d+_(NBA|NFL|NHL|MLB|WNBA)$', sgo_id, re.IGNORECASE)
        if name_match:
            name_parts = name_match.group(1).split('_')
            # Capitalize each part
            player_name = ' '.join(p.capitalize() for p in name_parts)
            normalized = normalize_name(player_name)
            
            key = (league.lower(), normalized)
            if key not in sgo_mapping:
                sgo_mapping[key] = {
                    'playerName': player_name,
                    'normalizedName': normalized,
                    'sgoId': sgo_id,
                    'league': league.lower()
                }
    
    print(f'  Extracted {len(sgo_mapping)} unique player names from SGO')
    
    # Try to fetch NBA.com player names from nba_api data
    # We'll use a known mapping for top players
    print('Adding known NBA.com ID mappings...')
    
    # Known mappings for top NBA players (NBA.com ID -> Name)
    known_nba_ids = {
        '2544': 'LeBron James',
        '201566': 'Russell Westbrook', 
        '203507': 'Giannis Antetokounmpo',
        '1629029': 'Luka Doncic',
        '203954': 'Joel Embiid',
        '1628369': 'Jayson Tatum',
        '1629627': 'Zion Williamson',
        '1630163': 'James Wiseman',
        '1630164': 'Anthony Edwards',
        '1628983': 'Shai Gilgeous-Alexander',
        '201142': 'Kevin Durant',
        '201939': 'Stephen Curry',
        '203076': 'Anthony Davis',
        '101108': 'Chris Paul',
        '1629630': 'Ja Morant',
        '1630595': 'Cade Cunningham',
        '1630224': 'LaMelo Ball',
        '1630169': 'Tyrese Haliburton',
        '203999': 'Nikola Jokic',
        '1628378': 'Donovan Mitchell',
        '1629636': 'Tyler Herro',
        '1629684': 'RJ Barrett',
        '203081': 'Damian Lillard',
        '201935': 'James Harden',
        '202681': 'Kyrie Irving',
        '202695': 'Kawhi Leonard',
        '203114': 'Khris Middleton',
        '201950': 'Jrue Holiday',
        '1629028': 'Trae Young',
        '1628384': 'OG Anunoby',
        '1629639': 'De\'Aaron Fox',
        '203497': 'Rudy Gobert',
        '1629631': 'Darius Garland',
        '1630162': 'Obi Toppin',
        '1629652': 'Cam Reddish',
        '1629651': 'Coby White',
        '203944': 'Julius Randle',
        '202696': 'Nikola Vucevic',
        '1628389': 'Bam Adebayo',
        '1627759': 'Jaylen Brown',
        '1628398': 'Jaren Jackson Jr',
        '1630532': 'Paolo Banchero',
        '1630559': 'Jalen Green',
        '1631094': 'Victor Wembanyama',
        '1630560': 'Scottie Barnes',
        '1631095': 'Chet Holmgren',
        '203935': 'Marcus Smart',
        '201566': 'Derrick Rose',
        '1629057': 'Deandre Ayton',
        '1628977': 'Mikal Bridges',
    }
    
    # Insert mappings
    insert_sql = '''
        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(EXCLUDED."sgoId", "PlayerMapping"."sgoId"),
            "updatedAt" = NOW()
    '''
    
    inserted = 0
    
    # Insert from SGO mappings
    for key, data in sgo_mapping.items():
        league, normalized = key
        try:
            cur.execute(insert_sql, (
                data['league'],
                data['playerName'],
                data['normalizedName'],
                None,  # nbaComId - will be filled from known mappings
                data['sgoId']
            ))
            inserted += 1
        except Exception as e:
            conn.rollback()
    
    conn.commit()
    print(f'  Inserted {inserted} SGO mappings')
    
    # Update with known NBA.com IDs
    updated = 0
    for nba_id, name in known_nba_ids.items():
        normalized = normalize_name(name)
        try:
            cur.execute('''
                UPDATE "PlayerMapping"
                SET "nbaComId" = %s, "updatedAt" = NOW()
                WHERE "normalizedName" = %s AND league = 'nba'
            ''', (nba_id, normalized))
            if cur.rowcount > 0:
                updated += 1
            else:
                # Insert if not exists
                cur.execute(insert_sql, ('nba', name, normalized, nba_id, None))
                inserted += 1
        except Exception as e:
            conn.rollback()
    
    conn.commit()
    print(f'  Updated {updated} with NBA.com IDs, inserted {inserted} new')
    
    # Summary
    cur.execute('''
        SELECT 
            COUNT(*) as total,
            COUNT("nbaComId") as with_nba_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 = 'nba'
    ''')
    stats = cur.fetchone()
    
    print(f'\n✅ Player Mapping Complete:')
    print(f'   Total: {stats[0]}')
    print(f'   With NBA.com ID: {stats[1]}')
    print(f'   With SGO ID: {stats[2]}')
    print(f'   Fully Mapped (both IDs): {stats[3]}')
    
    cur.close()
    conn.close()
    
    return {'success': True, 'total': stats[0], 'fully_mapped': stats[3]}

if __name__ == '__main__':
    build_mapping()