#!/usr/bin/env python3
"""
Script pour examiner et améliorer la structure de la base de données
pour le suivi des avatars
"""

import mysql.connector
from python.db_config import DB_CONFIG

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

def show_all_tables():
    """Liste toutes les tables de la base"""
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    cursor.execute('SHOW TABLES')
    tables = [list(r.values())[0] for r in cursor.fetchall()]
    print("=" * 70)
    print("TOUTES LES TABLES DE LA BASE")
    print("=" * 70)
    for t in sorted(tables):
        cursor.execute(f'SELECT COUNT(*) as c FROM `{t}`')
        count = cursor.fetchone()['c']
        print(f"  {t:40} ({count} lignes)")
    
    conn.close()
    return tables

def describe_table(table_name):
    """Affiche la structure d'une table"""
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    print()
    print(f"=== Structure de {table_name} ===")
    cursor.execute(f'DESCRIBE `{table_name}`')
    for r in cursor.fetchall():
        default = r.get('Default', '') or ''
        print(f"  {r['Field']:25} {r['Type']:25} {'NULL' if r['Null']=='YES' else 'NOT NULL':10} {r['Key']:5} {default}")
    
    # Afficher quelques exemples
    cursor.execute(f'SELECT * FROM `{table_name}` LIMIT 5')
    rows = cursor.fetchall()
    if rows:
        print(f"\n  Exemples ({len(rows)} lignes):")
        for r in rows:
            print(f"    {r}")
    
    conn.close()

def check_avatar_tables():
    """Examine les tables liées aux avatars"""
    tables_to_check = [
        'avatars',
        'user_avatars', 
        'user_weekly_avatars',
        'weekly_rewards',
        'user_challenge_pack',
        'daily_challenges'
    ]
    
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    cursor.execute('SHOW TABLES')
    existing_tables = [list(r.values())[0] for r in cursor.fetchall()]
    conn.close()
    
    for table in tables_to_check:
        if table in existing_tables:
            describe_table(table)
        else:
            print(f"\n⚠️ Table '{table}' n'existe pas!")

def show_bruno_admin_data():
    """Affiche les données de bruno-admin"""
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    
    print()
    print("=" * 70)
    print("DONNÉES DE BRUNO-ADMIN (user_id=1)")
    print("=" * 70)
    
    # Avatars possédés
    cursor.execute('SELECT * FROM user_avatars WHERE user_id = 1')
    print(f"\nuser_avatars:")
    for r in cursor.fetchall():
        print(f"  {r}")
    
    # Avatars hebdo
    cursor.execute('SELECT * FROM user_weekly_avatars WHERE user_id = 1')
    print(f"\nuser_weekly_avatars:")
    for r in cursor.fetchall():
        print(f"  {r}")
    
    # Pack défis
    cursor.execute('SELECT * FROM user_challenge_pack WHERE user_id = 1')
    print(f"\nuser_challenge_pack:")
    for r in cursor.fetchall():
        print(f"  {r}")
    
    conn.close()

if __name__ == "__main__":
    print("🔍 ANALYSE DE LA STRUCTURE DE LA BASE DE DONNÉES")
    print("=" * 70)
    
    # 1. Lister toutes les tables
    all_tables = show_all_tables()
    
    # 2. Examiner les tables avatars
    check_avatar_tables()
    
    # 3. Données de bruno-admin
    show_bruno_admin_data()