#!/usr/bin/env python3
"""
Build CFB (NCAAF) Player ID Mapping Table
Maps ESPN IDs to SGO IDs via player names
"""
import psycopg2
import re

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: TRAVIS_HUNTER_1_NCAAF -> travis hunter
    name = re.sub(r'_\d+_NCAAF$', '', name, flags=re.IGNORECASE)
    name = name.replace('_', ' ').lower().strip()
    name = re.sub(r'\s+(jr\.?|sr\.?|ii|iii|iv)$', '', name, flags=re.IGNORECASE)
    name = re.sub(r"['\-]", '', name)
    return name

def build_mapping():
    """Build the CFB 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()
    
    # Extract SGO IDs from raw JSON
    print('Extracting CFB players from PlayerPropLine raw JSON...')
    cur.execute('''
        SELECT DISTINCT raw->'sportsgameodds'->>'playerID' as sgo_id
        FROM "PlayerPropLine"
        WHERE league = 'ncaaf'
        AND raw->'sportsgameodds'->>'playerID' LIKE '%_NCAAF'
    ''')
    sgo_players = cur.fetchall()
    print(f'  Found {len(sgo_players)} SGO name-style CFB player IDs')
    
    # Build SGO mapping
    sgo_mapping = {}
    for (sgo_id,) in sgo_players:
        if not sgo_id:
            continue
        name_match = re.match(r'^([A-Z_]+)_\d+_NCAAF$', 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_mapping[normalized] = {
                'playerName': player_name,
                'normalizedName': normalized,
                'sgoId': sgo_id,
            }
    print(f'  Extracted {len(sgo_mapping)} unique player names from SGO')
    
    # Get player names from PlayerGameMetric
    print('Fetching CFB players from PlayerGameMetric...')
    cur.execute('''
        SELECT DISTINCT "playerExternalId", "playerName"
        FROM "PlayerGameMetric"
        WHERE league = 'ncaaf'
        AND "playerName" IS NOT NULL
    ''')
    cfb_players = cur.fetchall()
    print(f'  Found {len(cfb_players)} CFB players with stats')
    
    # Build ESPN mapping
    espn_mapping = {}
    for espn_id, player_name in cfb_players:
        if not player_name:
            continue
        normalized = normalize_name(player_name)
        if normalized not in espn_mapping:
            espn_mapping[normalized] = {
                'espnId': espn_id,
                'playerName': player_name,
                'normalizedName': normalized,
            }
    print(f'  Extracted {len(espn_mapping)} unique player names from ESPN data')
    
    # 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
            "sgoId" = COALESCE(EXCLUDED."sgoId", "PlayerMapping"."sgoId"),
            "espnId" = COALESCE(EXCLUDED."espnId", "PlayerMapping"."espnId"),
            "updatedAt" = NOW()
    '''
    
    inserted = 0
    matched = 0
    
    for normalized, data in sgo_mapping.items():
        espn_id = None
        if normalized in espn_mapping:
            espn_id = espn_mapping[normalized]['espnId']
            matched += 1
        
        try:
            cur.execute(insert_sql, (
                'ncaaf',
                data['playerName'],
                data['normalizedName'],
                None,
                data['sgoId'],
                espn_id,
            ))
            inserted += 1
        except Exception as e:
            print(f'  Error inserting {data["playerName"]}: {e}')
            conn.rollback()
    
    conn.commit()
    print(f'  Inserted {inserted} CFB mappings, {matched} matched with ESPN IDs')
    
    # Add ESPN-only players
    additional = 0
    for normalized, data in espn_mapping.items():
        if normalized not in sgo_mapping:
            try:
                cur.execute(insert_sql, (
                    'ncaaf',
                    data['playerName'],
                    data['normalizedName'],
                    None,
                    None,
                    data['espnId'],
                ))
                additional += 1
            except:
                conn.rollback()
    
    conn.commit()
    print(f'  Added {additional} additional ESPN-only players')
    
    # Summary
    cur.execute('''
        SELECT 
            COUNT(*) as total,
            COUNT("espnId") as with_espn_id,
            COUNT("sgoId") as with_sgo_id,
            COUNT(CASE WHEN "espnId" IS NOT NULL AND "sgoId" IS NOT NULL THEN 1 END) as fully_mapped
        FROM "PlayerMapping"
        WHERE league = 'ncaaf'
    ''')
    stats = cur.fetchone()
    
    print(f'\n✅ CFB Player Mapping Complete:')
    print(f'   Total: {stats[0]}')
    print(f'   With ESPN 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()