#!/usr/bin/env python3
"""Met à jour les pseudos trop longs et génère des pseudos uniques pour les comptes"""

import mysql.connector
from python.db_config import DB_CONFIG

conn = mysql.connector.connect(**DB_CONFIG)
cursor = conn.cursor(dictionary=True)

print("=" * 70)
print("MISE À JOUR DES PSEUDOS")
print("=" * 70)

# 1. Trouver les pseudos trop longs (> 10 caractères)
cursor.execute("""
    SELECT id, email, display_name, CHAR_LENGTH(display_name) as length
    FROM users
    WHERE CHAR_LENGTH(display_name) > 10
    ORDER BY id
""")
long_names = cursor.fetchall()

print(f"\n📊 Pseudos trop longs trouvés : {len(long_names)}")

if long_names:
    print("\n" + "=" * 70)
    print("RACCOURCISSEMENT DES PSEUDOS")
    print("=" * 70)
    
    for user in long_names:
        user_id = user['id']
        old_name = user['display_name']
        email = user['email']
        length = user['length']
        
        # Générer nouveau pseudo basé sur l'ID
        new_name = f"Joueur{user_id}"
        
        print(f"\n--- ID {user_id} ({email}) ---")
        print(f"  ❌ Ancien pseudo : '{old_name}' ({length} caractères)")
        print(f"  ✅ Nouveau pseudo : '{new_name}'")
        
        cursor.execute("""
            UPDATE users 
            SET display_name = %s
            WHERE id = %s
        """, (new_name, user_id))

# 2. Trouver les doublons de pseudos
cursor.execute("""
    SELECT LOWER(display_name) as lower_name, COUNT(*) as count
    FROM users
    WHERE guest = 0
    GROUP BY LOWER(display_name)
    HAVING COUNT(*) > 1
""")
duplicates = cursor.fetchall()

if duplicates:
    print("\n" + "=" * 70)
    print("RÉSOLUTION DES DOUBLONS")
    print("=" * 70)
    print(f"\n⚠️  {len(duplicates)} pseudo(s) en doublon détecté(s)")
    
    for dup in duplicates:
        lower_name = dup['lower_name']
        count = dup['count']
        
        print(f"\n--- Doublon : '{lower_name}' ({count} utilisateurs) ---")
        
        # Récupérer tous les utilisateurs avec ce pseudo
        cursor.execute("""
            SELECT id, email, display_name, created_at
            FROM users
            WHERE LOWER(display_name) = %s
            ORDER BY created_at ASC, id ASC
        """, (lower_name,))
        users_with_name = cursor.fetchall()
        
        # Garder le premier, renommer les autres
        for i, user in enumerate(users_with_name):
            if i == 0:
                print(f"  ✅ {user['email']} (ID: {user['id']}) garde '{user['display_name']}'")
            else:
                new_name = f"Joueur{user['id']}"
                print(f"  🔄 {user['email']} (ID: {user['id']}) renommé en '{new_name}'")
                cursor.execute("""
                    UPDATE users 
                    SET display_name = %s
                    WHERE id = %s
                """, (new_name, user['id']))

# Commit les changements
conn.commit()

# 3. Résumé final
cursor.execute("SELECT COUNT(*) as total FROM users WHERE CHAR_LENGTH(display_name) > 10")
remaining_long = cursor.fetchone()['total']

# Compter les doublons restants simplement
cursor.execute("""
    SELECT LOWER(display_name) as lower_name, COUNT(*) as cnt
    FROM users
    WHERE guest = 0
    GROUP BY LOWER(display_name)
    HAVING COUNT(*) > 1
""")
remaining_dupes = len(cursor.fetchall())

print("\n" + "=" * 70)
print("RÉSUMÉ")
print("=" * 70)
print(f"✅ Pseudos trop longs traités : {len(long_names)}")
print(f"✅ Doublons résolus : {len(duplicates)}")
print(f"📊 Pseudos > 10 caractères restants : {remaining_long}")
print(f"📊 Doublons restants : {remaining_dupes}")

cursor.close()
conn.close()

print("\n✨ Migration terminée avec succès !")