#!/usr/bin/env python3
"""
Generate detailed list of players that need new SbaPlayer records for manual review
"""
import json
import csv
import logging
from dataclasses import dataclass
from typing import Dict, List, Set, Optional, Tuple
from collections import defaultdict

# Set up logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger('NewSbaPlayersList')

@dataclass
class PlayerRecord:
    id: int
    name: str
    season: int
    bbref_id: Optional[str] = None
    sbaplayer_id: Optional[int] = None

def load_cached_data():
    """Load cached data"""
    with open('/tmp/sbaplayers.json', 'r') as f:
        sbaplayer_data = json.load(f)
    
    all_players = []
    for season in range(1, 13):
        with open(f"/tmp/players_season_{season}.json", 'r') as f:
            season_data = json.load(f)
        
        for data in season_data:
            all_players.append(PlayerRecord(**data))
    
    return all_players, sbaplayer_data

def normalize_name(name: str) -> str:
    """Normalize name for matching"""
    if not name:
        return ""
    return name.lower().strip().replace("'", "").replace(".", "").replace("-", " ")

def find_unmatched_players():
    """Find players that would need new SbaPlayer records"""
    all_players, sbaplayer_data = load_cached_data()
    
    # Create SbaPlayer lookup maps
    sbaplayers_by_bbref = {}
    sbaplayers_by_name = {}
    
    for sba in sbaplayer_data:
        if sba.get('key_bbref'):
            sbaplayers_by_bbref[sba['key_bbref']] = sba
        
        sba_name = f"{sba['first_name']} {sba['last_name']}"
        normalized_sba = normalize_name(sba_name)
        if normalized_sba not in sbaplayers_by_name:
            sbaplayers_by_name[normalized_sba] = []
        sbaplayers_by_name[normalized_sba].append(sba)
    
    # Find unmatched players
    unmatched_groups = {}
    
    for player in all_players:
        matched = False
        
        # Try bbref_id match
        if player.bbref_id and player.bbref_id in sbaplayers_by_bbref:
            matched = True
        
        # Try name match
        if not matched:
            normalized_player = normalize_name(player.name)
            if normalized_player in sbaplayers_by_name:
                if len(sbaplayers_by_name[normalized_player]) == 1:
                    matched = True
        
        # If not matched, add to unmatched groups
        if not matched:
            if player.bbref_id:
                key = f"bbref:{player.bbref_id}"
            else:
                key = f"name:{normalize_name(player.name)}"
            
            if key not in unmatched_groups:
                unmatched_groups[key] = []
            unmatched_groups[key].append(player)
    
    return unmatched_groups, sbaplayers_by_name, sbaplayer_data

def generate_review_csv():
    """Generate CSV file for manual review"""
    unmatched_groups, sbaplayers_by_name, sbaplayer_data = find_unmatched_players()
    
    output_file = '/mnt/NV2/Development/major-domo/database/player-to-sbaplayer-matching/new_sbaplayers_for_review.csv'
    
    with open(output_file, 'w', newline='') as csvfile:
        writer = csv.writer(csvfile)
        writer.writerow([
            'group_key', 'player_name', 'bbref_id', 'seasons_appeared', 'sample_player_ids',
            'potential_existing_sbaplayer_id', 'potential_existing_sbaplayer_name', 'potential_match_reason',
            'your_decision_sbaplayer_id', 'your_decision_notes'
        ])
        
        for key, players in unmatched_groups.items():
            # Use first player as representative
            representative = players[0]
            seasons = sorted(list(set(p.season for p in players)))
            sample_ids = [str(p.id) for p in players[:3]]  # Show first 3 player IDs
            
            # Look for potential matches
            potential_id = ""
            potential_name = ""
            potential_reason = ""
            
            normalized_name = normalize_name(representative.name)
            
            # Check for similar names in existing SbaPlayers
            for sba in sbaplayer_data:
                sba_name = f"{sba['first_name']} {sba['last_name']}"
                sba_normalized = normalize_name(sba_name)
                
                # Exact match that was somehow missed
                if sba_normalized == normalized_name:
                    potential_id = str(sba['id'])
                    potential_name = sba_name
                    potential_reason = "Exact name match - check why not matched automatically"
                    break
                
                # Check for common variations
                player_parts = normalized_name.split()
                sba_parts = sba_normalized.split()
                
                if len(player_parts) >= 2 and len(sba_parts) >= 2:
                    # Same last name, similar first name
                    if (player_parts[-1] == sba_parts[-1] and 
                        (player_parts[0] in sba_parts[0] or sba_parts[0] in player_parts[0])):
                        if not potential_id:  # Only suggest first match
                            potential_id = f"MAYBE:{sba['id']}"
                            potential_name = sba_name
                            potential_reason = f"Similar name: {representative.name} vs {sba_name}"
            
            if not potential_id:
                potential_reason = "No obvious existing match - likely needs new SbaPlayer record"
            
            writer.writerow([
                key,
                representative.name,
                representative.bbref_id or '',
                ','.join(map(str, seasons)),
                ','.join(sample_ids),
                potential_id,
                potential_name,
                potential_reason,
                '',  # your_decision_sbaplayer_id (empty for manual filling)
                ''   # your_decision_notes (empty for manual filling)
            ])
    
    logger.info(f"Generated review CSV: {output_file}")
    logger.info(f"Found {len(unmatched_groups)} unique unmatched players for review")
    
    # Also generate a summary
    with open('/mnt/NV2/Development/major-domo/database/player-to-sbaplayer-matching/new_sbaplayers_summary.txt', 'w') as f:
        f.write("NEW SBAPLAYERS REVIEW SUMMARY\n")
        f.write("=" * 40 + "\n\n")
        f.write(f"Total unique players needing review: {len(unmatched_groups)}\n\n")
        
        f.write("INSTRUCTIONS:\n")
        f.write("1. Review the CSV file: new_sbaplayers_for_review.csv\n")
        f.write("2. For each row, fill in 'your_decision_sbaplayer_id' column:\n")
        f.write("   - Enter existing SbaPlayer ID if you found a match\n")
        f.write("   - Leave blank if player needs a new SbaPlayer record\n")
        f.write("3. Use 'your_decision_notes' for any comments\n\n")
        
        f.write("SAMPLE ENTRIES:\n")
        for i, (key, players) in enumerate(list(unmatched_groups.items())[:5]):
            representative = players[0]
            seasons = sorted(list(set(p.season for p in players)))
            f.write(f"  {representative.name} (bbref: {representative.bbref_id or 'None'}) - seasons {seasons}\n")
        
        if len(unmatched_groups) > 5:
            f.write(f"  ... and {len(unmatched_groups) - 5} more\n")

if __name__ == "__main__":
    generate_review_csv()