#!/usr/bin/env python3
"""
Corrige les weekly_rewards pour utiliser les avatars animal (1-380)
au lieu des avatars base de la table avatars
"""

import mysql.connector
import random
from python.db_config import DB_CONFIG

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

print("=" * 70)
print("CORRECTION DES WEEKLY_REWARDS")
print("Les avatars doivent être dans 380_animal_avatard (1-380)")
print("=" * 70)

# 1. Voir l'état actuel
print("\n1. État actuel des weekly_rewards:")
cursor.execute("SELECT * FROM weekly_rewards ORDER BY week_key")
current_rewards = cursor.fetchall()
for r in current_rewards:
    print(f"   {r['week_key']}: Avatar {r['avatar_id']}")

# 2. Voir les avatars déjà utilisés
print("\n2. Avatars déjà utilisés (used_weekly_avatars):")
cursor.execute("SELECT avatar_id FROM used_weekly_avatars")
used = [r['avatar_id'] for r in cursor.fetchall()]
print(f"   {used}")

# 3. Supprimer les mauvais weekly_rewards (ceux avec avatar_id <= 20)
print("\n3. Suppression des weekly_rewards incorrects...")
cursor.execute("DELETE FROM weekly_rewards WHERE avatar_id <= 20")
deleted = cursor.rowcount
print(f"   {deleted} lignes supprimées")

# 4. Supprimer les avatars attribués aux utilisateurs (ceux <= 20)
print("\n4. Nettoyage de user_avatars et user_weekly_avatars...")
cursor.execute("DELETE FROM user_avatars WHERE avatar_id <= 20 AND source = 'challenge_pack'")
print(f"   user_avatars: {cursor.rowcount} lignes supprimées")
cursor.execute("DELETE FROM user_weekly_avatars WHERE avatar_id <= 20")
print(f"   user_weekly_avatars: {cursor.rowcount} lignes supprimées")

conn.commit()

# 5. Générer de nouveaux weekly_rewards avec des avatars animal (21-380)
print("\n5. Génération de nouveaux weekly_rewards avec avatars animal...")

# Semaines à créer (janvier 2026 = W01 à W05)
weeks_to_create = ['2026-W01', '2026-W02', '2026-W03', '2026-W04', '2026-W05']

# Avatars disponibles (1-380, mais éviter ceux déjà utilisés)
all_animal_avatars = list(range(1, 381))  # 1 à 380
available_avatars = [a for a in all_animal_avatars if a not in used]

print(f"   Avatars animal disponibles: {len(available_avatars)}")

created = 0
new_rewards = []
for week_key in weeks_to_create:
    # Vérifier si existe déjà
    cursor.execute("SELECT avatar_id FROM weekly_rewards WHERE week_key = %s", (week_key,))
    if cursor.fetchone():
        print(f"   {week_key}: existe déjà, ignoré")
        continue
    
    # Choisir un avatar aléatoire
    if available_avatars:
        avatar_id = random.choice(available_avatars)
        available_avatars.remove(avatar_id)
        
        cursor.execute(
            "INSERT INTO weekly_rewards (week_key, avatar_id, created_at) VALUES (%s, %s, NOW())",
            (week_key, avatar_id)
        )
        cursor.execute(
            "INSERT IGNORE INTO used_weekly_avatars (avatar_id, used_at) VALUES (%s, NOW())",
            (avatar_id,)
        )
        
        new_rewards.append((week_key, avatar_id))
        print(f"   ✓ {week_key}: Avatar {avatar_id} -> /avatars/380_animal_avatard/{avatar_id}.svg")
        created += 1

conn.commit()
print(f"\n   {created} nouveaux weekly_rewards créés")

# 6. Attribuer les avatars aux acheteurs du pack
print("\n6. Attribution des avatars aux acheteurs du pack...")

cursor.execute("""
    SELECT ucp.user_id, u.display_name 
    FROM user_challenge_pack ucp
    JOIN users u ON u.id = ucp.user_id
""")
buyers = cursor.fetchall()
print(f"   {len(buyers)} acheteurs trouvés")

# Récupérer tous les weekly_rewards actuels
cursor.execute("SELECT week_key, avatar_id FROM weekly_rewards ORDER BY week_key")
all_rewards = cursor.fetchall()
print(f"   {len(all_rewards)} weekly_rewards à attribuer")

for buyer in buyers:
    user_id = buyer['user_id']
    user_name = buyer['display_name']
    granted = 0
    
    for reward in all_rewards:
        week_key = reward['week_key']
        avatar_id = reward['avatar_id']
        
        # user_weekly_avatars
        cursor.execute(
            "SELECT 1 FROM user_weekly_avatars WHERE user_id = %s AND avatar_id = %s",
            (user_id, avatar_id)
        )
        if not cursor.fetchone():
            cursor.execute(
                """INSERT INTO user_weekly_avatars (user_id, avatar_id, week_key, obtained_at, obtained_from)
                   VALUES (%s, %s, %s, NOW(), 'challenge_pack')""",
                (user_id, avatar_id, week_key)
            )
        
        # user_avatars (pour la traçabilité)
        cursor.execute(
            "SELECT 1 FROM user_avatars WHERE user_id = %s AND avatar_id = %s",
            (user_id, avatar_id)
        )
        if not cursor.fetchone():
            cursor.execute(
                """INSERT INTO user_avatars (user_id, avatar_id, source, metadata, unlocked_at)
                   VALUES (%s, %s, 'challenge_pack', %s, NOW())""",
                (user_id, avatar_id, f'{{"week_key": "{week_key}"}}')
            )
            granted += 1
    
    print(f"   {user_name}: {granted} avatars attribués")

conn.commit()

# 7. Vérification finale
print("\n" + "=" * 70)
print("VÉRIFICATION FINALE")
print("=" * 70)

print("\nWeekly rewards:")
cursor.execute("SELECT week_key, avatar_id FROM weekly_rewards ORDER BY week_key")
for r in cursor.fetchall():
    print(f"   {r['week_key']}: Avatar {r['avatar_id']} -> /avatars/380_animal_avatard/{r['avatar_id']}.svg")

print("\nAvatars de Bruno-admin (ID: 1):")
cursor.execute("""
    SELECT ua.avatar_id, ua.source
    FROM user_avatars ua
    WHERE ua.user_id = 1
    ORDER BY ua.avatar_id
""")
for r in cursor.fetchall():
    path = f"/avatars/380_animal_avatard/{r['avatar_id']}.svg"
    print(f"   Avatar {r['avatar_id']} -> {path}")

conn.close()
print("\n✅ Correction terminée!")