import mysql.connector

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

def check_duplicates():
    try:
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT 
                TABLE_NAME, 
                INDEX_NAME, 
                GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as columns
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = 'educa25_docente'
            GROUP BY TABLE_NAME, INDEX_NAME
        """)
        indexes = cursor.fetchall()

        table_indexes = {}
        for idx in indexes:
            table = idx['TABLE_NAME']
            if table not in table_indexes:
                table_indexes[table] = []
            table_indexes[table].append({
                'name': idx['INDEX_NAME'],
                'columns': idx['columns']
            })

        print("=== ÍNDICES DUPLICADOS (Mismas columnas exactas) ===")
        found_exact = False
        for table, idx_list in table_indexes.items():
            col_dict = {}
            for idx in idx_list:
                cols = idx['columns']
                if cols not in col_dict:
                    col_dict[cols] = []
                col_dict[cols].append(idx['name'])
            
            for cols, names in col_dict.items():
                if len(names) > 1:
                    found_exact = True
                    print(f"Tabla: {table}")
                    print(f"  Columnas: {cols}")
                    print(f"  Índices redundantes: {', '.join(names)}\n")
        
        if not found_exact:
            print("No se encontraron índices duplicados exactos.\n")

        print("=== ÍNDICES REDUNDANTES (Prefijos) ===")
        found_prefix = False
        for table, idx_list in table_indexes.items():
            for i in range(len(idx_list)):
                for j in range(len(idx_list)):
                    if i != j:
                        idx1 = idx_list[i]
                        idx2 = idx_list[j]
                        cols1 = idx1['columns']
                        cols2 = idx2['columns']
                        
                        if cols1 and cols2 and cols1 != cols2:
                            if cols2.startswith(cols1 + ','):
                                # check if idx1 is PRIMARY, if so it might be needed for InnoDB clustered index, 
                                # but still good to know
                                found_prefix = True
                                print(f"Tabla: {table}")
                                print(f"  El índice '{idx1['name']}' ({cols1}) está incluido dentro de '{idx2['name']}' ({cols2})")
        
        if not found_prefix:
            print("No se encontraron índices prefijos/redundantes.\n")

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

if __name__ == "__main__":
    check_duplicates()