"""
Script pour corriger les chemins des avatars cool avec les vrais noms de fichiers
Les fichiers sont: "Fun Avatar Characters (Community) (1).png" etc.

Usage:
    python python/migrations/fix_cool_avatars_paths.py
"""
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))
from db_config import DB_CONFIG


def fix_cool_avatars_paths():
    """Corrige les chemins des avatars cool avec les vrais noms de fichiers"""
    try:
        import mysql.connector
        from mysql.connector import Error
    except ModuleNotFoundError:
        print("❌ Module mysql-connector-python requis")
        print("Installation: pip install mysql-connector-python")
        return False

    # Mapping des codes vers les vrais noms de fichiers
    # Fun Avatar Characters (Community).png = (1)
    # Fun Avatar Characters (Community) (1).png = le premier numéroté
    file_mapping = {
        'cool_01': 'avatars/cool/Fun Avatar Characters (Community).png',  # Sans numéro
        'cool_02': 'avatars/cool/Fun Avatar Characters (Community) (1).png',
        'cool_03': 'avatars/cool/Fun Avatar Characters (Community) (2).png',
        'cool_04': 'avatars/cool/Fun Avatar Characters (Community) (4).png',
        'cool_05': 'avatars/cool/Fun Avatar Characters (Community) (5).png',
        'cool_06': 'avatars/cool/Fun Avatar Characters (Community) (6).png',
        'cool_07': 'avatars/cool/Fun Avatar Characters (Community) (7).png',
        'cool_08': 'avatars/cool/Fun Avatar Characters (Community) (8).png',
        'cool_09': 'avatars/cool/Fun Avatar Characters (Community) (9).png',
        'cool_10': 'avatars/cool/Fun Avatar Characters (Community) (10).png',
        'cool_11': 'avatars/cool/Fun Avatar Characters (Community) (11).png',
        'cool_12': 'avatars/cool/Fun Avatar Characters (Community) (12).png',
        'cool_13': 'avatars/cool/Fun Avatar Characters (Community) (13).png',
        'cool_14': 'avatars/cool/Fun Avatar Characters (Community) (14).png',
        'cool_15': 'avatars/cool/Fun Avatar Characters (Community) (15).png',
        'cool_16': 'avatars/cool/Fun Avatar Characters (Community) (16).png',
        'cool_17': 'avatars/cool/Fun Avatar Characters (Community) (17).png',
        'cool_18': 'avatars/cool/Fun Avatar Characters (Community) (18).png',
        'cool_19': 'avatars/cool/Fun Avatar Characters (Community) (19).png',
    }

    connection = None
    try:
        print("🔗 Connexion à la base de données OVH...")
        connection = mysql.connector.connect(**DB_CONFIG)
        cursor = connection.cursor(dictionary=True)

        print(f"\n{'='*70}")
        print("CORRECTION: Chemins des avatars cool")
        print("="*70)

        # Vérifier les avatars cool existants
        cursor.execute("""
            SELECT id, code, file_path 
            FROM avatars 
            WHERE code LIKE 'cool_%'
            ORDER BY code
        """)
        existing_avatars = cursor.fetchall()

        if not existing_avatars:
            print("❌ Aucun avatar cool trouvé dans la base")
            print("   Exécutez d'abord: python/migrations/setup_avatar_packs_complete.py")
            return False

        print(f"📋 {len(existing_avatars)} avatars cool trouvés:")
        for avatar in existing_avatars:
            print(f"   {avatar['code']}: {avatar['file_path']}")

        print(f"\n📝 Mise à jour des chemins...")
        updated = 0
        not_found = 0

        for avatar in existing_avatars:
            code = avatar['code']
            if code in file_mapping:
                new_path = file_mapping[code]
                old_path = avatar['file_path']
                
                if old_path != new_path:
                    try:
                        cursor.execute("""
                            UPDATE avatars 
                            SET file_path = %s 
                            WHERE code = %s
                        """, (new_path, code))
                        updated += 1
                        print(f"   ✅ {code}: {new_path}")
                    except Error as e:
                        print(f"   ❌ Erreur pour {code}: {e}")
                else:
                    print(f"   ⏭️  {code}: déjà correct")
            else:
                not_found += 1
                print(f"   ⚠️  {code}: pas de mapping trouvé")

        connection.commit()

        print(f"\n{'='*70}")
        print(f"✅ Mise à jour terminée:")
        print(f"   - Mis à jour: {updated}")
        print(f"   - Déjà corrects: {len(existing_avatars) - updated - not_found}")
        print(f"   - Non trouvés: {not_found}")
        print(f"{'='*70}")

        # Vérification finale
        print(f"\n🔍 Vérification finale...")
        cursor.execute("""
            SELECT code, file_path 
            FROM avatars 
            WHERE code LIKE 'cool_%'
            ORDER BY code
        """)
        final_avatars = cursor.fetchall()

        print(f"\n📋 État final des avatars cool ({len(final_avatars)}):")
        for avatar in final_avatars[:5]:
            print(f"   {avatar['code']}: {avatar['file_path']}")
        if len(final_avatars) > 5:
            print(f"   ... et {len(final_avatars) - 5} autres")

        # Vérifier que tous les chemins sont corrects
        all_correct = all(
            avatar['file_path'].startswith('avatars/cool/Fun Avatar Characters')
            for avatar in final_avatars
        )

        if all_correct:
            print(f"\n✅ Tous les chemins sont corrects!")
        else:
            incorrect = [
                avatar['code'] for avatar in final_avatars
                if not avatar['file_path'].startswith('avatars/cool/Fun Avatar Characters')
            ]
            print(f"\n⚠️  Chemins incorrects restants: {', '.join(incorrect)}")

        print(f"\n{'='*70}")
        print("✅ CORRECTION RÉUSSIE")
        print("="*70)
        print("Les avatars cool pointent maintenant vers les vrais fichiers.")
        print("Le système de packs d'avatars est entièrement opérationnel!")
        print("="*70)

        return True

    except Error as e:
        print(f"\n❌ Erreur MySQL: {e}")
        if connection and connection.is_connected():
            connection.rollback()
        return False

    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("\n🔌 Connexion fermée")


if __name__ == "__main__":
    print("="*70)
    print("CORRECTION: Chemins des fichiers avatars cool")
    print("="*70)
    print()
    
    success = fix_cool_avatars_paths()
    
    if not success:
        print("\n❌ Correction échouée")
        sys.exit(1)
    else:
        print("\n✅ Correction terminée avec succès")
        sys.exit(0)