"""
Script complet de migration du système de packs d'avatars
Ce script effectue toutes les opérations nécessaires pour configurer pack_avatars

Ce qu'il fait:
1. Vérifie/crée les 19 avatars du pack 'cool'  
2. Crée la table pack_avatars
3. Lie les avatars au pack
4. Valide la configuration complète

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

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


def setup_avatar_packs():
    """Configuration complète du système de packs d'avatars"""
    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

    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("ÉTAPE 1/4: Vérification du pack 'cool' dans avatar_packs")
        print("="*70)
        
        cursor.execute("""
            SELECT pack_id, name, avatar_count, price
            FROM avatar_packs 
            WHERE pack_id = 'cool'
        """)
        cool_pack = cursor.fetchone()

        if not cool_pack:
            print("❌ Le pack 'cool' n'existe pas dans avatar_packs")
            print("\n💡 Solution: Exécutez d'abord:")
            print("   cd rollerlogic-api && npm run db:init")
            print("   ou exécutez: sql/migrations/add_avatar_packs.sql")
            return False

        print(f"✅ Pack trouvé: {cool_pack['name']}")
        print(f"   Prix: {cool_pack['price']}€")
        print(f"   Avatars annoncés: {cool_pack['avatar_count']}")

        print(f"\n{'='*70}")
        print("ÉTAPE 2/4: Création/Vérification des avatars 'cool'")
        print("="*70)

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

        print(f"📊 {len(existing_cool)} avatar(s) 'cool' trouvé(s)")

        if len(existing_cool) < 19:
            print(f"⚠️  Il manque {19 - len(existing_cool)} avatar(s)")
            print("📝 Création des avatars manquants...")
            
            created = 0
            for i in range(1, 20):
                code = f"cool_{i:02d}"
                
                # Vérifier si existe
                if any(a['code'] == code for a in existing_cool):
                    continue
                
                file_path = f"avatars/cool/cool_{i:02d}.png"
                
                try:
                    cursor.execute("""
                        INSERT INTO avatars (code, category, file_path, price, display_order)
                        VALUES (%s, 'achat', %s, 0, %s)
                    """, (code, file_path, 500 + i))
                    created += 1
                    print(f"   ✅ {code} créé")
                except Error as e:
                    print(f"   ❌ Erreur pour {code}: {e}")
            
            connection.commit()
            print(f"✅ {created} avatar(s) créé(s)")
            
            # Recharger la liste
            cursor.execute("""
                SELECT id, code FROM avatars 
                WHERE code LIKE 'cool_%'
                ORDER BY code
            """)
            existing_cool = cursor.fetchall()

        print(f"✅ {len(existing_cool)} avatars 'cool' disponibles")

        print(f"\n{'='*70}")
        print("ÉTAPE 3/4: Création de la table pack_avatars")
        print("="*70)

        # Vérifier si la table existe
        cursor.execute("""
            SELECT COUNT(*) as count 
            FROM information_schema.tables 
            WHERE table_schema = %s 
            AND table_name = 'pack_avatars'
        """, (DB_CONFIG['database'],))
        
        table_exists = cursor.fetchone()['count'] > 0

        if table_exists:
            cursor.execute("SELECT COUNT(*) as count FROM pack_avatars WHERE pack_id = 'cool'")
            existing_links = cursor.fetchone()['count']
            print(f"✅ Table 'pack_avatars' existe ({existing_links} liens pour 'cool')")
            
            if existing_links > 0:
                response = input("   Voulez-vous réinitialiser les liens ? (o/N): ")
                if response.lower() == 'o':
                    cursor.execute("DELETE FROM pack_avatars WHERE pack_id = 'cool'")
                    connection.commit()
                    print("   ✅ Liens réinitialisés")
                else:
                    print("   ℹ️  Conservation des liens existants")
                    # Passer à la vérification finale
                    existing_links = 0
            
        else:
            print("📝 Création de la table 'pack_avatars'...")
            cursor.execute("""
                CREATE TABLE `pack_avatars` (
                  `pack_id` varchar(50) NOT NULL,
                  `avatar_id` int NOT NULL,
                  `display_order` int DEFAULT 0,
                  PRIMARY KEY (`pack_id`, `avatar_id`),
                  KEY `idx_pack_avatars_pack` (`pack_id`),
                  KEY `idx_pack_avatars_avatar` (`avatar_id`),
                  CONSTRAINT `pack_avatars_ibfk_1` FOREIGN KEY (`pack_id`) 
                    REFERENCES `avatar_packs` (`pack_id`) ON DELETE CASCADE,
                  CONSTRAINT `pack_avatars_ibfk_2` FOREIGN KEY (`avatar_id`) 
                    REFERENCES `avatars` (`id`) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
            """)
            connection.commit()
            print("✅ Table 'pack_avatars' créée")

        print(f"\n{'='*70}")
        print("ÉTAPE 4/4: Liaison des avatars au pack 'cool'")
        print("="*70)

        # Vérifier si des liens existent déjà
        cursor.execute("SELECT COUNT(*) as count FROM pack_avatars WHERE pack_id = 'cool'")
        if cursor.fetchone()['count'] == 0:
            print(f"📝 Insertion de {len(existing_cool)} avatars dans pack_avatars...")
            
            inserted = 0
            for i, avatar in enumerate(existing_cool):
                try:
                    cursor.execute("""
                        INSERT INTO pack_avatars (pack_id, avatar_id, display_order)
                        VALUES ('cool', %s, %s)
                    """, (avatar['id'], i + 1))
                    inserted += 1
                except Error as e:
                    print(f"   ⚠️  Erreur pour {avatar['code']}: {e}")
            
            connection.commit()
            print(f"✅ {inserted} avatar(s) lié(s) au pack")
        else:
            print("ℹ️  Les avatars sont déjà liés au pack")

        # Mise à jour du compteur dans avatar_packs
        cursor.execute("""
            UPDATE avatar_packs 
            SET avatar_count = (
                SELECT COUNT(*) FROM pack_avatars WHERE pack_id = 'cool'
            )
            WHERE pack_id = 'cool'
        """)
        connection.commit()

        print(f"\n{'='*70}")
        print("VÉRIFICATION FINALE")
        print("="*70)

        # Statistiques finales
        cursor.execute("""
            SELECT 
                ap.pack_id, 
                ap.name, 
                ap.price,
                ap.avatar_count as announced,
                COUNT(pa.avatar_id) as actual
            FROM avatar_packs ap
            LEFT JOIN pack_avatars pa ON ap.pack_id = pa.pack_id
            WHERE ap.pack_id = 'cool'
            GROUP BY ap.pack_id, ap.name, ap.price, ap.avatar_count
        """)
        stats = cursor.fetchone()

        print(f"Pack: {stats['name']}")
        print(f"Prix: {stats['price']}€")
        print(f"Avatars configurés: {stats['actual']}")
        print(f"Avatars annoncés: {stats['announced']}")

        if stats['actual'] == stats['announced']:
            print("✅ Configuration 100% cohérente")
        else:
            print(f"⚠️  Incohérence: {stats['actual']} vs {stats['announced']}")

        # Test de la requête du webhook
        print(f"\n🔍 Test de la requête webhook...")
        cursor.execute("""
            SELECT avatar_id FROM pack_avatars 
            WHERE pack_id = 'cool' 
            ORDER BY display_order ASC
        """)
        webhook_avatars = cursor.fetchall()
        
        print(f"✅ Le webhook récupérera {len(webhook_avatars)} avatar(s)")
        print(f"   IDs: {', '.join(str(a['avatar_id']) for a in webhook_avatars[:5])}")
        if len(webhook_avatars) > 5:
            print(f"   ... et {len(webhook_avatars) - 5} autres")

        print(f"\n{'='*70}")
        print("✅ MIGRATION COMPLÈTE RÉUSSIE")
        print("="*70)
        print("Le système de packs d'avatars est opérationnel!")
        print("Le webhook Stripe peut maintenant débloquer les avatars.")
        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("MIGRATION COMPLÈTE: Système de packs d'avatars")
    print("="*70)
    print()
    
    success = setup_avatar_packs()
    
    if not success:
        print("\n❌ Migration échouée")
        sys.exit(1)
    else:
        print("\n🎉 Migration réussie! Le pack 'cool' est prêt.")
        sys.exit(0)