#!/usr/bin/env python3
"""
Script de migration pour améliorer le suivi des avatars
- Ajoute une colonne 'source' à user_avatars pour tracer l'origine
- Agrandit obtained_from dans user_weekly_avatars
- Génère les weekly_rewards manquants depuis janvier 2026
- Attribue les avatars du pack défis à ceux qui l'ont acheté
"""

import mysql.connector
import random
from datetime import datetime, timedelta
from python.db_config import DB_CONFIG

def get_connection():
    return mysql.connector.connect(**DB_CONFIG)

def migrate_user_avatars_table():
    """Améliore la table user_avatars avec une colonne source"""
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    print("=" * 70)
    print("1. MIGRATION DE user_avatars")
    print("=" * 70)
    
    # Vérifier si la colonne source existe déjà
    cursor.execute("DESCRIBE user_avatars")
    columns = [r['Field'] for r in cursor.fetchall()]
    
    if 'source' not in columns:
        print("  ➕ Ajout de la colonne 'source' (VARCHAR(50))...")
        cursor.execute("""
            ALTER TABLE user_avatars 
            ADD COLUMN source VARCHAR(50) DEFAULT 'unknown' AFTER avatar_id
        """)
        print("  ✓ Colonne 'source' ajoutée")
    else:
        print("  ✓ Colonne 'source' existe déjà")
    
    # Vérifier si la colonne metadata existe
    if 'metadata' not in columns:
        print("  ➕ Ajout de la colonne 'metadata' (JSON) pour infos supplémentaires...")
        cursor.execute("""
            ALTER TABLE user_avatars 
            ADD COLUMN metadata JSON DEFAULT NULL AFTER source
        """)
        print("  ✓ Colonne 'metadata' ajoutée")
    else:
        print("  ✓ Colonne 'metadata' existe déjà")
    
    conn.commit()
    conn.close()
    print()

def migrate_user_weekly_avatars_table():
    """Agrandit la colonne obtained_from dans user_weekly_avatars"""
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    print("=" * 70)
    print("2. MIGRATION DE user_weekly_avatars")
    print("=" * 70)
    
    # Vérifier la taille actuelle de obtained_from
    cursor.execute("DESCRIBE user_weekly_avatars")
    for r in cursor.fetchall():
        if r['Field'] == 'obtained_from':
            if 'varchar(10)' in r['Type'].lower():
                print("  ➕ Agrandissement de 'obtained_from' de VARCHAR(10) à VARCHAR(50)...")
                cursor.execute("""
                    ALTER TABLE user_weekly_avatars 
                    MODIFY COLUMN obtained_from VARCHAR(50)
                """)
                print("  ✓ Colonne agrandie")
            else:
                print(f"  ✓ Colonne 'obtained_from' déjà correcte: {r['Type']}")
    
    # Ajouter week_key si n'existe pas
    cursor.execute("DESCRIBE user_weekly_avatars")
    columns = [r['Field'] for r in cursor.fetchall()]
    
    if 'week_key' not in columns:
        print("  ➕ Ajout de la colonne 'week_key' pour référencer la semaine...")
        cursor.execute("""
            ALTER TABLE user_weekly_avatars 
            ADD COLUMN week_key VARCHAR(10) AFTER avatar_id
        """)
        print("  ✓ Colonne 'week_key' ajoutée")
    else:
        print("  ✓ Colonne 'week_key' existe déjà")
    
    conn.commit()
    conn.close()
    print()

def generate_missing_weekly_rewards():
    """Génère les weekly_rewards pour toutes les semaines depuis janvier 2026"""
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    print("=" * 70)
    print("3. GÉNÉRATION DES WEEKLY_REWARDS MANQUANTS")
    print("=" * 70)
    
    # Récupérer les avatars disponibles (category = 'recompense')
    cursor.execute("""
        SELECT id, code FROM avatars 
        WHERE category = 'recompense' AND id NOT IN (SELECT avatar_id FROM used_weekly_avatars)
    """)
    available_avatars = cursor.fetchall()
    
    if not available_avatars:
        # Fallback: utiliser tous les avatars non-base
        cursor.execute("""
            SELECT id, code FROM avatars WHERE category != 'base'
        """)
        available_avatars = cursor.fetchall()
    
    if not available_avatars:
        # Dernier recours: tous les avatars
        cursor.execute("SELECT id, code FROM avatars")
        available_avatars = cursor.fetchall()
    
    print(f"  Avatars disponibles pour récompenses: {len(available_avatars)}")
    for a in available_avatars:
        print(f"    - {a['id']}: {a['code']}")
    
    # Calculer toutes les semaines depuis le 1er janvier 2026 jusqu'à aujourd'hui
    start_date = datetime(2026, 1, 1)
    today = datetime.now()
    
    weeks = []
    current = start_date
    while current <= today:
        iso = current.isocalendar()
        week_key = f"{iso[0]}-W{iso[1]:02d}"
        if week_key not in [w[0] for w in weeks]:
            weeks.append((week_key, current))
        current += timedelta(days=7)
    
    print(f"\n  Semaines calculées (depuis 1er janvier 2026): {len(weeks)}")
    for wk, dt in weeks:
        print(f"    - {wk} (début: {dt.strftime('%d/%m/%Y')})")
    
    # Vérifier quelles semaines n'ont pas d'avatar
    cursor.execute("SELECT week_key FROM weekly_rewards")
    existing_weeks = [r['week_key'] for r in cursor.fetchall()]
    print(f"\n  Semaines existantes: {existing_weeks}")
    
    created = 0
    for week_key, week_date in weeks:
        if week_key not in existing_weeks:
            # Choisir un avatar aléatoire
            if available_avatars:
                avatar = random.choice(available_avatars)
                avatar_id = avatar['id']
                
                cursor.execute("""
                    INSERT INTO weekly_rewards (week_key, avatar_id, created_at)
                    VALUES (%s, %s, NOW())
                """, (week_key, avatar_id))
                
                # Marquer comme utilisé
                cursor.execute("""
                    INSERT IGNORE INTO used_weekly_avatars (avatar_id, used_at)
                    VALUES (%s, NOW())
                """, (avatar_id,))
                
                print(f"  ✓ Créé: {week_key} -> Avatar {avatar_id} ({avatar['code']})")
                created += 1
                
                # Retirer de la liste pour éviter les doublons
                available_avatars = [a for a in available_avatars if a['id'] != avatar_id]
    
    if created > 0:
        conn.commit()
        print(f"\n  ✓ {created} nouveaux weekly_rewards créés!")
    else:
        print("\n  Tous les weekly_rewards existent déjà.")
    
    conn.close()
    print()

def grant_pack_avatars():
    """Attribue les avatars du pack défis à tous les acheteurs"""
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    print("=" * 70)
    print("4. ATTRIBUTION DES AVATARS DU PACK DÉFIS")
    print("=" * 70)
    
    # Récupérer tous les acheteurs du pack
    cursor.execute("""
        SELECT ucp.user_id, u.display_name, ucp.purchased_at
        FROM user_challenge_pack ucp
        JOIN users u ON u.id = ucp.user_id
    """)
    buyers = cursor.fetchall()
    
    print(f"  Acheteurs du pack: {len(buyers)}")
    for b in buyers:
        print(f"    - {b['display_name']} (ID: {b['user_id']}) - acheté le {b['purchased_at']}")
    
    # Récupérer tous les weekly_rewards
    cursor.execute("SELECT week_key, avatar_id FROM weekly_rewards")
    weekly_rewards = cursor.fetchall()
    
    print(f"\n  Weekly rewards disponibles: {len(weekly_rewards)}")
    
    total_granted = 0
    
    for buyer in buyers:
        user_id = buyer['user_id']
        user_name = buyer['display_name']
        granted_for_user = 0
        
        print(f"\n  === Attribution pour {user_name} (ID: {user_id}) ===")
        
        for reward in weekly_rewards:
            week_key = reward['week_key']
            avatar_id = reward['avatar_id']
            
            # 1. Ajouter dans user_weekly_avatars si pas déjà présent
            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))
                print(f"    ✓ user_weekly_avatars: Semaine {week_key} -> Avatar {avatar_id}")
            else:
                # Mettre à jour le week_key si manquant
                cursor.execute("""
                    UPDATE user_weekly_avatars 
                    SET week_key = %s, obtained_from = 'challenge_pack'
                    WHERE user_id = %s AND avatar_id = %s AND (week_key IS NULL OR week_key = '')
                """, (week_key, user_id, avatar_id))
            
            # 2. Ajouter dans user_avatars si pas déjà présent
            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}"}}'))
                print(f"    ✓ user_avatars: Avatar {avatar_id} (source: challenge_pack, semaine: {week_key})")
                granted_for_user += 1
        
        print(f"    -> {granted_for_user} nouveaux avatars attribués à {user_name}")
        total_granted += granted_for_user
    
    conn.commit()
    print(f"\n  ✓ TOTAL: {total_granted} avatars attribués")
    conn.close()
    print()

def show_final_state():
    """Affiche l'état final après migration"""
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    print("=" * 70)
    print("5. ÉTAT FINAL APRÈS MIGRATION")
    print("=" * 70)
    
    # Structure mise à jour de user_avatars
    print("\n  Structure de user_avatars:")
    cursor.execute("DESCRIBE user_avatars")
    for r in cursor.fetchall():
        print(f"    {r['Field']:20} {r['Type']}")
    
    # Structure mise à jour de user_weekly_avatars
    print("\n  Structure de user_weekly_avatars:")
    cursor.execute("DESCRIBE user_weekly_avatars")
    for r in cursor.fetchall():
        print(f"    {r['Field']:20} {r['Type']}")
    
    # Weekly rewards
    print("\n  Weekly rewards:")
    cursor.execute("SELECT * FROM weekly_rewards ORDER BY week_key")
    for r in cursor.fetchall():
        print(f"    {r['week_key']}: Avatar {r['avatar_id']}")
    
    # Avatars de bruno-admin
    print("\n  Avatars de Bruno-admin (ID: 1):")
    cursor.execute("""
        SELECT ua.avatar_id, ua.source, ua.metadata, ua.unlocked_at, a.code
        FROM user_avatars ua
        JOIN avatars a ON a.id = ua.avatar_id
        WHERE ua.user_id = 1
    """)
    for r in cursor.fetchall():
        print(f"    - {r['code']} (ID: {r['avatar_id']}) - source: {r['source']}, metadata: {r['metadata']}")
    
    conn.close()

if __name__ == "__main__":
    print("🔧 MIGRATION DE LA BASE DE DONNÉES POUR LE SUIVI DES AVATARS")
    print("=" * 70)
    print()
    
    # 1. Migrer user_avatars
    migrate_user_avatars_table()
    
    # 2. Migrer user_weekly_avatars
    migrate_user_weekly_avatars_table()
    
    # 3. Générer les weekly_rewards manquants
    generate_missing_weekly_rewards()
    
    # 4. Attribuer les avatars du pack
    grant_pack_avatars()
    
    # 5. Afficher l'état final
    show_final_state()
    
    print("\n✅ MIGRATION TERMINÉE!")