#!/usr/bin/env python3
"""Vérification finale du système d'avatars"""

import mysql.connector
from python.db_config import DB_CONFIG

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

print("=" * 70)
print("VÉRIFICATION FINALE DU SYSTÈME D'AVATARS")
print("=" * 70)

# 1. Structure des tables
print("\n1. STRUCTURE DES TABLES")
print("-" * 40)

for table in ['user_avatars', 'user_weekly_avatars']:
    print(f"\n{table}:")
    cursor.execute(f'DESCRIBE {table}')
    for r in cursor.fetchall():
        print(f"  {r['Field']:20} {r['Type']}")

# 2. Weekly rewards
print("\n2. WEEKLY REWARDS (avatars par semaine)")
print("-" * 40)
cursor.execute('''
    SELECT wr.week_key, wr.avatar_id, a.code, a.file_path
    FROM weekly_rewards wr
    LEFT JOIN avatars a ON a.id = wr.avatar_id
    ORDER BY wr.week_key
''')
for r in cursor.fetchall():
    print(f"  {r['week_key']}: Avatar {r['avatar_id']} ({r['code']}) -> {r['file_path']}")

# 3. Avatars de Bruno-admin
print("\n3. AVATARS DE BRUNO-ADMIN (ID: 1)")
print("-" * 40)

print("\nDans user_avatars:")
cursor.execute('''
    SELECT ua.avatar_id, ua.source, ua.metadata, a.code, a.file_path
    FROM user_avatars ua
    LEFT JOIN avatars a ON a.id = ua.avatar_id
    WHERE ua.user_id = 1
    ORDER BY ua.avatar_id
''')
avatars_ua = cursor.fetchall()
print(f"  Total: {len(avatars_ua)}")
for a in avatars_ua:
    print(f"  - {a['code']} (ID: {a['avatar_id']}) - {a['file_path']} - source: {a['source']}")

print("\nDans user_weekly_avatars:")
cursor.execute('''
    SELECT uwa.avatar_id, uwa.week_key, uwa.obtained_from, a.code
    FROM user_weekly_avatars uwa
    LEFT JOIN avatars a ON a.id = uwa.avatar_id
    WHERE uwa.user_id = 1
    ORDER BY uwa.week_key
''')
avatars_uwa = cursor.fetchall()
print(f"  Total: {len(avatars_uwa)}")
for a in avatars_uwa:
    print(f"  - Semaine {a['week_key']}: Avatar {a['avatar_id']} ({a['code']}) - source: {a['obtained_from']}")

# 4. Ce que l'API devrait renvoyer
print("\n4. CE QUE L'API DEVRAIT RENVOYER")
print("-" * 40)
cursor.execute('''
    SELECT DISTINCT 
       COALESCE(ua.avatar_id, uwa.avatar_id) as avatar_id,
       COALESCE(ua.unlocked_at, uwa.obtained_at) as obtained_at,
       COALESCE(ua.source, uwa.obtained_from, 'unknown') as obtained_from,
       a.file_path,
       a.code,
       a.category
     FROM user_avatars ua
     LEFT JOIN avatars a ON a.id = ua.avatar_id
     WHERE ua.user_id = 1
     
     UNION
     
     SELECT DISTINCT 
       uwa.avatar_id,
       uwa.obtained_at,
       COALESCE(uwa.obtained_from, 'weekly') as obtained_from,
       a.file_path,
       a.code,
       a.category
     FROM user_weekly_avatars uwa
     LEFT JOIN avatars a ON a.id = uwa.avatar_id
     WHERE uwa.user_id = 1
       AND uwa.avatar_id NOT IN (SELECT avatar_id FROM user_avatars WHERE user_id = 1)
''')
api_result = cursor.fetchall()
print(f"  Total avatars à afficher: {len(api_result)}")
for a in api_result:
    path = f"/avatars/{a['file_path']}" if a['file_path'] else f"/avatars/380_animal_avatard/{a['avatar_id']}.svg"
    print(f"  - {a['code']} (ID: {a['avatar_id']}) -> {path}")

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