"""
Script pour vérifier la structure de la base de données concernant les avatars
"""
import mysql.connector
from python.db_config import DB_CONFIG

def check_structure():
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor(dictionary=True)
    
    print("=" * 70)
    print("VÉRIFICATION DE LA STRUCTURE BASE DE DONNÉES - AVATARS")
    print("=" * 70)
    print()
    
    # 1. Vérifier les tables liées aux avatars
    print("1️⃣  TABLES LIÉES AUX AVATARS")
    print("-" * 70)
    cursor.execute("SHOW TABLES LIKE '%avatar%'")
    tables = cursor.fetchall()
    for table in tables:
        table_name = list(table.values())[0]
        print(f"  ✓ {table_name}")
    print()
    
    # 2. Structure de la table avatars
    print("2️⃣  STRUCTURE DE LA TABLE 'avatars'")
    print("-" * 70)
    cursor.execute("DESCRIBE avatars")
    for col in cursor.fetchall():
        print(f"  {col['Field']:20} {col['Type']:20} {col['Null']:5} {col['Key']:5} {col['Default']}")
    print()
    
    # 3. Vérifier si avatar_pack_images existe
    cursor.execute("SHOW TABLES LIKE 'avatar_pack_images'")
    if cursor.fetchone():
        print("3️⃣  STRUCTURE DE LA TABLE 'avatar_pack_images'")
        print("-" * 70)
        cursor.execute("DESCRIBE avatar_pack_images")
        for col in cursor.fetchall():
            print(f"  {col['Field']:20} {col['Type']:20} {col['Null']:5} {col['Key']:5} {col['Default']}")
        print()
        
        print("4️⃣  EXEMPLES DE DONNÉES DANS 'avatar_pack_images'")
        print("-" * 70)
        cursor.execute("SELECT * FROM avatar_pack_images LIMIT 10")
        rows = cursor.fetchall()
        if rows:
            for row in rows:
                print(f"  ID: {row.get('id')}, Pack: {row.get('pack_id')}, Avatar: {row.get('avatar_id')}, Path: {row.get('image_path')}")
        else:
            print("  Aucune donnée trouvée")
        print()
    else:
        print("⚠️  La table 'avatar_pack_images' n'existe PAS")
        print()
    
    # 4. Vérifier les packs d'avatars
    cursor.execute("SHOW TABLES LIKE 'avatar_packs'")
    if cursor.fetchone():
        print("5️⃣  STRUCTURE DE LA TABLE 'avatar_packs'")
        print("-" * 70)
        cursor.execute("DESCRIBE avatar_packs")
        for col in cursor.fetchall():
            print(f"  {col['Field']:20} {col['Type']:20} {col['Null']:5} {col['Key']:5} {col['Default']}")
        print()
        
        print("6️⃣  LISTE DES PACKS D'AVATARS")
        print("-" * 70)
        cursor.execute("SELECT * FROM avatar_packs")
        packs = cursor.fetchall()
        for pack in packs:
            print(f"  ID: {pack.get('id')}, Name: {pack.get('name')}, Price: {pack.get('price_gems')}")
    print()
    
    # 5. Vérifier la table avatars et ses données
    print("7️⃣  EXEMPLES D'AVATARS DANS LA TABLE 'avatars'")
    print("-" * 70)
    cursor.execute("SELECT id, code, category, file_path FROM avatars ORDER BY id LIMIT 10")
    avatars = cursor.fetchall()
    for avatar in avatars:
        print(f"  ID: {avatar['id']:3}, Code: {avatar['code']:20}, Category: {avatar['category']:10}, Path: {avatar['file_path']}")
    print()
    
    # 6. Statistiques sur les chemins d'images
    print("8️⃣  STATISTIQUES SUR LES CHEMINS D'IMAGES")
    print("-" * 70)
    cursor.execute("""
        SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN file_path LIKE '/avatars/380_animal_avatard/%' THEN 1 ELSE 0 END) as animal_public,
            SUM(CASE WHEN file_path LIKE '/avatars/avatard_cool/%' THEN 1 ELSE 0 END) as cool_public,
            SUM(CASE WHEN file_path LIKE '/api/avatars/%' THEN 1 ELSE 0 END) as secured,
            SUM(CASE WHEN file_path LIKE '/avatars/base/%' THEN 1 ELSE 0 END) as base_public
        FROM avatars
    """)
    stats = cursor.fetchone()
    print(f"  Total d'avatars: {stats['total']}")
    print(f"  ├─ Avatars animaux avec chemin public: {stats['animal_public']}")
    print(f"  ├─ Avatars cool avec chemin public: {stats['cool_public']}")
    print(f"  ├─ Avatars avec API sécurisée: {stats['secured']}")
    print(f"  └─ Avatars de base (publics OK): {stats['base_public']}")
    print()
    
    # 7. Vérifier quelques exemples de chemins
    print("9️⃣  EXEMPLES DE CHEMINS D'IMAGES PAR CATÉGORIE")
    print("-" * 70)
    
    categories = ['base', 'recompense', 'achat']
    for cat in categories:
        cursor.execute(f"SELECT file_path FROM avatars WHERE category = %s LIMIT 3", (cat,))
        paths = cursor.fetchall()
        if paths:
            print(f"  Catégorie '{cat}':")
            for p in paths:
                print(f"    - {p['file_path']}")
    print()
    
    cursor.close()
    conn.close()

if __name__ == "__main__":
    try:
        check_structure()
    except mysql.connector.Error as err:
        print(f"\n❌ ERREUR MySQL : {err}")
    except Exception as e:
        print(f"\n❌ ERREUR : {e}")