import mysql.connector
from mysql.connector import Error

def connect_to_database():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='',
            database='gestion_mei_1'
        )
        if connection.is_connected():
            print(f"Connecté à la base de données gestion_mei_1")
            return connection
    except Error as e:
        print(f"Erreur lors de la connexion à MySQL: {e}")
        return None

def get_all_tables(connection):
    cursor = connection.cursor()
    cursor.execute("SHOW TABLES")
    tables = [table[0] for table in cursor.fetchall()]
    cursor.close()
    return tables

def get_table_structure(connection, table_name):
    cursor = connection.cursor(dictionary=True)
    cursor.execute(f"DESCRIBE {table_name}")
    columns = cursor.fetchall()
    
    # Vérifier le nombre d'enregistrements
    cursor.execute(f"SELECT COUNT(*) as count FROM {table_name}")
    count = cursor.fetchone()['count']
    
    cursor.close()
    return columns, count

def main():
    connection = connect_to_database()
    if not connection:
        return
    
    # Tables déjà implémentées dans l'application
    implemented_tables = [
        'utilisateurs', 'user', 'mei', 'dr', 'famille', 'rapport_ctrl', 
        'fournisseur', 'commande', 'cde', 'constat', 'bl', 'pr'
    ]
    
    tables = get_all_tables(connection)
    
    print("\nTables disponibles dans la base de données:")
    for i, table in enumerate(tables, 1):
        print(f"{i}. {table}")
    
    print("\nTables non encore implémentées dans l'application:")
    not_implemented = [table for table in tables if table.lower() not in [t.lower() for t in implemented_tables]]
    
    for i, table in enumerate(not_implemented, 1):
        columns, count = get_table_structure(connection, table)
        print(f"\n{i}. Table: {table} ({count} enregistrements)")
        
        print("   Colonnes:")
        for column in columns:
            nullable = "NULL" if column['Null'] == "YES" else "NOT NULL"
            key = f", {column['Key']}" if column['Key'] else ""
            print(f"   - {column['Field']} ({column['Type']}, {nullable}{key})")
    
    connection.close()
    print("\nAnalyse terminée.")

if __name__ == "__main__":
    main()