"""
Migration : Sécurisation des images premium via API
Migre les chemins d'images des avatars pour utiliser l'API sécurisée avec vérification d'ownership.

Avant migration :
- /avatars/380_animal_avatard/X.svg
- /avatars/avatard_cool/XXX.png

Après migration :
- /api/avatars/X/image
- /api/avatars/cool_XXX/image

Usage:
    python migrate_secure_images.py
    python migrate_secure_images.py --dry-run    (affiche sans exécuter)
"""
import sys
import mysql.connector
from python.db_config import DB_CONFIG


def get_connection():
    """Connexion à la BDD OVH CloudDB."""
    return mysql.connector.connect(
        host=DB_CONFIG["host"],
        port=DB_CONFIG["port"],
        user=DB_CONFIG["user"],
        password=DB_CONFIG["password"],
        database=DB_CONFIG["database"],
    )


def run_migration(dry_run: bool = False):
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)

    print("=" * 70)
    print("MIGRATION DE SÉCURISATION DES IMAGES PREMIUM")
    print("=" * 70)
    print(f"Connecté à {DB_CONFIG['host']}:{DB_CONFIG['port']} / {DB_CONFIG['database']}")
    print(f"Mode: {'DRY-RUN (simulation)' if dry_run else 'EXECUTION RÉELLE'}")
    print("=" * 70)
    print()

    # ── ÉTAPE 1 : État initial ──
    print("1️⃣  ÉTAT INITIAL")
    print("-" * 70)
    
    cursor.execute("""
        SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN image_path LIKE '/avatars/380_animal_avatard/%' THEN 1 ELSE 0 END) as animal_public,
            SUM(CASE WHEN image_path LIKE '/avatars/avatard_cool/%' THEN 1 ELSE 0 END) as cool_public,
            SUM(CASE WHEN image_path LIKE '/api/avatars/%' THEN 1 ELSE 0 END) as secured,
            SUM(CASE WHEN image_path LIKE '/avatars/base/%' THEN 1 ELSE 0 END) as base_public
        FROM avatar_pack_images
    """)
    
    stats = cursor.fetchone()
    print(f"  Total d'images 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()

    # Afficher quelques exemples
    if stats['animal_public'] > 0:
        print("  Exemples d'avatars animaux à migrer:")
        cursor.execute("""
            SELECT image_path FROM avatar_pack_images 
            WHERE image_path LIKE '/avatars/380_animal_avatard/%'
            LIMIT 3
        """)
        for row in cursor.fetchall():
            print(f"    - {row['image_path']}")
    
    if stats['cool_public'] > 0:
        print("  Exemples d'avatars cool à migrer:")
        cursor.execute("""
            SELECT image_path FROM avatar_pack_images 
            WHERE image_path LIKE '/avatars/avatard_cool/%'
            LIMIT 3
        """)
        for row in cursor.fetchall():
            print(f"    - {row['image_path']}")
    print()

    # ── ÉTAPE 2 : Migration des avatars animaux ──
    print("2️⃣  MIGRATION DES AVATARS ANIMAUX")
    print("-" * 70)
    
    sql_animal = """
        UPDATE avatar_pack_images
        SET image_path = CONCAT('/api/avatars/', 
            SUBSTRING_INDEX(SUBSTRING_INDEX(image_path, '/', -1), '.', 1), 
            '/image')
        WHERE image_path LIKE '/avatars/380_animal_avatard/%'
    """
    
    if stats['animal_public'] > 0:
        print(f"  Migration de {stats['animal_public']} avatar(s) animal(aux)...")
        print("  Transformation : /avatars/380_animal_avatard/X.svg → /api/avatars/X/image")
        
        if not dry_run:
            cursor.execute(sql_animal)
            conn.commit()
            print(f"  ✅ {cursor.rowcount} ligne(s) mise(s) à jour")
        else:
            print(f"  [DRY-RUN] SQL: {sql_animal}")
    else:
        print("  ✅ Aucun avatar animal à migrer — skip")
    print()

    # ── ÉTAPE 3 : Migration des avatars cool ──
    print("3️⃣  MIGRATION DES AVATARS COOL")
    print("-" * 70)
    
    sql_cool = """
        UPDATE avatar_pack_images
        SET image_path = CONCAT('/api/avatars/cool_',
            SUBSTRING_INDEX(SUBSTRING_INDEX(image_path, '/', -1), '.', 1),
            '/image')
        WHERE image_path LIKE '/avatars/avatard_cool/%'
    """
    
    if stats['cool_public'] > 0:
        print(f"  Migration de {stats['cool_public']} avatar(s) cool...")
        print("  Transformation : /avatars/avatard_cool/XXX.png → /api/avatars/cool_XXX/image")
        
        if not dry_run:
            cursor.execute(sql_cool)
            conn.commit()
            print(f"  ✅ {cursor.rowcount} ligne(s) mise(s) à jour")
        else:
            print(f"  [DRY-RUN] SQL: {sql_cool}")
    else:
        print("  ✅ Aucun avatar cool à migrer — skip")
    print()

    # ── ÉTAPE 4 : Vérification des ball skins ──
    print("4️⃣  VÉRIFICATION DES BALL SKINS")
    print("-" * 70)
    
    cursor.execute("""
        SELECT 
            COUNT(*) as total_skins,
            SUM(CASE WHEN image_path LIKE '/api/ball-skins/%' THEN 1 ELSE 0 END) as secured_skins,
            SUM(CASE WHEN image_path LIKE '/images/billes/%' THEN 1 ELSE 0 END) as public_skins
        FROM ball_skin_packs
    """)
    
    ball_stats = cursor.fetchone()
    print(f"  Total de ball skins: {ball_stats['total_skins']}")
    print(f"  ├─ Ball skins sécurisés: {ball_stats['secured_skins']}")
    print(f"  └─ Ball skins publics: {ball_stats['public_skins']}")
    
    if ball_stats['public_skins'] > 0:
        print(f"  ⚠️  ATTENTION : {ball_stats['public_skins']} ball skin(s) ont encore un chemin public !")
    else:
        print("  ✅ Tous les ball skins sont déjà sécurisés")
    print()

    # ── ÉTAPE 5 : État final ──
    print("5️⃣  ÉTAT FINAL")
    print("-" * 70)
    
    cursor.execute("""
        SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN image_path LIKE '/avatars/380_animal_avatard/%' THEN 1 ELSE 0 END) as animal_public,
            SUM(CASE WHEN image_path LIKE '/avatars/avatard_cool/%' THEN 1 ELSE 0 END) as cool_public,
            SUM(CASE WHEN image_path LIKE '/api/avatars/%' THEN 1 ELSE 0 END) as secured,
            SUM(CASE WHEN image_path LIKE '/avatars/base/%' THEN 1 ELSE 0 END) as base_public
        FROM avatar_pack_images
    """)
    
    final_stats = cursor.fetchone()
    print(f"  Total d'images d'avatars: {final_stats['total']}")
    print(f"  ├─ Avatars animaux avec chemin public: {final_stats['animal_public']}")
    print(f"  ├─ Avatars cool avec chemin public: {final_stats['cool_public']}")
    print(f"  ├─ Avatars avec API sécurisée: {final_stats['secured']}")
    print(f"  └─ Avatars de base (publics OK): {final_stats['base_public']}")
    print()

    # Afficher quelques exemples de chemins migrés
    if final_stats['secured'] > 0:
        print("  Exemples de chemins sécurisés:")
        cursor.execute("""
            SELECT image_path FROM avatar_pack_images 
            WHERE image_path LIKE '/api/avatars/%'
            LIMIT 5
        """)
        for row in cursor.fetchall():
            print(f"    - {row['image_path']}")
    print()

    # ── RÉSUMÉ ──
    print("=" * 70)
    print("RÉSUMÉ DE LA MIGRATION")
    print("=" * 70)
    
    if final_stats['animal_public'] == 0 and final_stats['cool_public'] == 0:
        print("✅ SUCCÈS : Tous les avatars premium sont maintenant sécurisés !")
        print("   Les images ne sont accessibles que via l'API avec vérification d'ownership.")
    else:
        print("⚠️  ATTENTION : Certains avatars premium ont encore des chemins publics !")
        print(f"   - Avatars animaux publics: {final_stats['animal_public']}")
        print(f"   - Avatars cool publics: {final_stats['cool_public']}")
    
    if not dry_run:
        print("\n🔒 Migration appliquée avec succès en base de données.")
    else:
        print("\n⚠️  Mode DRY-RUN : Aucune modification n'a été appliquée.")
        print("   Relancez sans --dry-run pour appliquer les changements.")
    
    print("=" * 70)

    cursor.close()
    conn.close()


def main():
    dry_run = "--dry-run" in sys.argv
    
    try:
        run_migration(dry_run=dry_run)
    except mysql.connector.Error as err:
        print(f"\n❌ ERREUR MySQL : {err}")
        sys.exit(1)
    except Exception as e:
        print(f"\n❌ ERREUR : {e}")
        sys.exit(1)


if __name__ == "__main__":
    main()