import mysql.connector

db_config = {
    "host": "localhost",
    "user": "root",
    "password": "vertrigo",
    "database": "educa25_docente"
}

def audit():
    try:
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        print("=== AUDITORÍA GLOBAL DE INFRAESTRUCTURA ===\n")
        
        # 1. Consistencia de tipos para 'numidentif'
        print("1. Verificando consistencia de 'numidentif'...")
        cursor.execute("""
            SELECT TABLE_NAME, COLUMN_TYPE 
            FROM information_schema.columns 
            WHERE table_schema = 'educa25_docente' 
            AND COLUMN_NAME = 'numidentif'
        """)
        cols = cursor.fetchall()
        types = set(c['COLUMN_TYPE'] for c in cols)
        if len(types) > 1:
            print(f"  [ALERTA] 'numidentif' tiene tipos mezclados: {types}")
            for c in cols:
                print(f"    - {c['TABLE_NAME']}: {c['COLUMN_TYPE']}")
        else:
            print(f"  [OK] 'numidentif' es consistente ({list(types)[0]}).")

        # 2. Tablas grandes sin índices suficientes
        print("\n2. Analizando tablas pesadas (>5MB)...")
        cursor.execute("""
            SELECT table_name, 
                   round(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
            FROM information_schema.TABLES 
            WHERE table_schema = 'educa25_docente'
            AND (data_length + index_length) > 5 * 1024 * 1024
            ORDER BY size_mb DESC
        """)
        heavy_tables = cursor.fetchall()
        for t in heavy_tables:
            cursor.execute(f"SHOW INDEX FROM `{t['table_name']}`")
            indexes = cursor.fetchall()
            print(f"  - {t['table_name']}: {t['size_mb']} MB, {len(set(i['Key_name'] for i in indexes))} índices.")

        # 3. Columnas candidatas a índice (IDs no indexados)
        print("\n3. Buscando columnas relacionales sin índice...")
        cursor.execute("""
            SELECT table_name, column_name 
            FROM information_schema.columns 
            WHERE table_schema = 'educa25_docente' 
            AND (column_name LIKE 'id_%' OR column_name LIKE 'cod_%' OR column_name LIKE 'num_%')
            AND column_name NOT IN (
                SELECT column_name 
                FROM information_schema.statistics 
                WHERE table_schema = 'educa25_docente'
            )
            LIMIT 15
        """)
        candidates = cursor.fetchall()
        if candidates:
            print("  [MEJORA] Las siguientes columnas podrían necesitar índices para JOINs:")
            for c in candidates:
                print(f"    - {c['table_name']}.{c['column_name']}")

        cursor.close()
        conn.close()
    except Exception as e:
        print(f"Error: {e}")

audit()