import subprocess
import re
import sys

dbs = ['educa25_docente', 'educa25_tecnico', 'educa25_alp_tulua', 'educa25_girardot', 'educa25_belisario', 'educa25_gimnasio', 'educa25_zprueba']
mysql_bin = r'D:\wamp64\bin\mysql\mysql5.7.19\bin\mysql.exe'

def fix_views():
    for db in dbs:
        print(f"--- Reparando vistas en {db} ---")
        try:
            # Listar vistas
            cmd_list = [mysql_bin, '-u', 'root', '-pvertrigo', '-s', '-N', '-e', f"SHOW FULL TABLES FROM {db} WHERE Table_type = 'VIEW';"]
            res = subprocess.check_output(cmd_list, stderr=subprocess.STDOUT).decode('latin1')
            views = [line.split('\t')[0] for line in res.splitlines() if line and 'Warning' not in line]
            
            for view in views:
                print(f"  Procesando: {view}")
                # Obtener definicion
                cmd_create = [mysql_bin, '-u', 'root', '-pvertrigo', '-s', '-N', '-e', f"SHOW CREATE VIEW {db}.{view};"]
                create_res = subprocess.check_output(cmd_create, stderr=subprocess.STDOUT).decode('latin1')
                
                # SHOW CREATE VIEW devuelve una fila con columnas: View | Create View | character_set_client | collation_connection
                parts = create_res.split('\t')
                if len(parts) >= 2:
                    sql_original = parts[1]
                    # Limpiar DEFINER
                    sql_fixed = re.sub(r'DEFINER=`.*?`@`.*?`', 'DEFINER=CURRENT_USER', sql_original)
                    
                    # Ejecutar el DROP y CREATE
                    drop_cmd = f"DROP VIEW IF EXISTS {db}.{view};"
                    create_cmd = sql_fixed
                    
                    full_cmd = f"USE {db}; {drop_cmd} {create_cmd}"
                    
                    # Escribir a un archivo temporal para ejecutar (evita problemas de escape en CLI)
                    with open('temp_view.sql', 'w', encoding='latin1') as f:
                        f.write(full_cmd)
                    
                    subprocess.call(f'"{mysql_bin}" -u root -pvertrigo < temp_view.sql', shell=True)
        except Exception as e:
            print(f"Error en base de datos {db}: {e}")

if __name__ == "__main__":
    fix_views()