"""
Script pour extraire la structure de la base de données RollerLogic depuis OVH CloudDB
"""
import sys
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parent.parent))
from db_config import DB_CONFIG

def extract_database_structure():
    try:
        try:
            import mysql.connector  # type: ignore
            from mysql.connector import Error  # type: ignore
        except ModuleNotFoundError:
            print("❌ Dépendance manquante: mysql-connector-python")
            print()
            print("Option A (Python):")
            print("  pip install mysql-connector-python")
            print("  python3 python/db/extract_db_structure.py > db_structure_live.txt")
            print()
            print("Option B (Node, recommandé si mysql2 est dispo):")
            print("  cd rollerlogic-api && npm run db:schema:dump")
            return

        # Connexion à la base de données
        connection = mysql.connector.connect(**DB_CONFIG)

        if connection.is_connected():
            cursor = connection.cursor()
            
            print("=" * 80)
            print("STRUCTURE DE LA BASE DE DONNÉES: RollerLogic")
            print("=" * 80)
            print()
            
            # Récupérer la liste des tables
            cursor.execute("SHOW TABLES")
            tables = cursor.fetchall()
            
            if not tables:
                print("⚠️  Aucune table trouvée dans la base de données.")
                return
            
            print(f"📊 Nombre de tables: {len(tables)}\n")
            
            # Pour chaque table, extraire la structure
            for (table_name,) in tables:
                print("-" * 80)
                print(f"📋 TABLE: {table_name}")
                print("-" * 80)
                
                # Structure de la table
                cursor.execute(f"DESCRIBE {table_name}")
                columns = cursor.fetchall()
                
                print("\nColonnes:")
                print(f"{'Nom':<30} {'Type':<20} {'Null':<8} {'Key':<8} {'Default':<15} {'Extra':<20}")
                print("-" * 120)
                
                for col in columns:
                    field, type_, null, key, default, extra = col
                    default_str = str(default) if default is not None else 'NULL'
                    print(f"{field:<30} {type_:<20} {null:<8} {key:<8} {default_str:<15} {extra:<20}")
                
                # Compter les enregistrements
                cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
                count = cursor.fetchone()[0]
                print(f"\n📈 Nombre d'enregistrements: {count}")
                
                # Afficher le CREATE TABLE
                cursor.execute(f"SHOW CREATE TABLE {table_name}")
                create_table = cursor.fetchone()[1]
                print(f"\n💾 CREATE TABLE:\n{create_table}")
                print()
            
            print("=" * 80)
            print("✅ Extraction terminée")
            print("=" * 80)

    except Error as e:
        print(f"❌ Erreur de connexion MySQL: {e}")
    
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("\n🔌 Connexion fermée")

if __name__ == "__main__":
    extract_database_structure()