#!/usr/bin/env python3
"""
Script específico para corregir problemas de charset y collation 
en las tablas LOG_ de gps_reportes.
"""

import mysql.connector
import logging
import sys

# Configuración de conexiones
SOURCE_CONFIG = {
    'host': '10.2.12.220',
    'user': 'gps',
    'password': 'q1w2e3r4',
    'database': 'gps_reportes',
    'charset': 'utf8mb4',
    'collation': 'utf8mb4_unicode_ci',
    'use_pure': True,
}

DEST_CONFIG = {
    'host': 'base220.c8lcuo0a2bu6.us-east-1.rds.amazonaws.com',
    'user': 'gps',
    'password': 'q1w2e3r4',
    'database': 'gps_reportes',
    'charset': 'utf8mb4',
    'collation': 'utf8mb4_unicode_ci',
    'use_pure': True,
}

LOG_FORMAT = '%(asctime)s %(levelname)s: %(message)s'
logging.basicConfig(level=logging.INFO, format=LOG_FORMAT, stream=sys.stdout)
logger = logging.getLogger('charset_fixer')

def configure_connection_charset(conn, server_name):
    """Configura el charset de la conexión."""
    cursor = conn.cursor()
    try:
        cursor.execute("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci")
        cursor.execute("SET character_set_connection=utf8mb4")
        cursor.execute("SET collation_connection=utf8mb4_unicode_ci")
        cursor.execute("SET character_set_results=utf8mb4")
        cursor.execute("SET character_set_client=utf8mb4")
        logger.info(f"✅ Charset configurado para {server_name}")
    except mysql.connector.Error as err:
        logger.error(f"❌ Error configurando charset para {server_name}: {err}")
        raise
    finally:
        cursor.close()

def check_table_charset(conn, table_name):
    """Verifica el charset actual de una tabla."""
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT 
                TABLE_COLLATION,
                TABLE_SCHEMA
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
        """, (conn.database, table_name))
        
        result = cursor.fetchone()
        if result:
            return result[0]  # TABLE_COLLATION
        return None
    finally:
        cursor.close()

def fix_table_charset(conn, table_name, server_name):
    """Corrige el charset de una tabla específica."""
    cursor = conn.cursor()
    try:
        current_collation = check_table_charset(conn, table_name)
        logger.info(f"{server_name} - Tabla {table_name}: {current_collation}")
        
        if current_collation != 'utf8mb4_unicode_ci':
            logger.info(f"🔧 Convirtiendo {table_name} en {server_name}...")
            
            # Convertir tabla completa
            alter_sql = f"""
                ALTER TABLE `{table_name}` 
                CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
            """
            cursor.execute(alter_sql)
            conn.commit()
            
            # Verificar resultado
            new_collation = check_table_charset(conn, table_name)
            logger.info(f"✅ {table_name} convertida: {current_collation} → {new_collation}")
        else:
            logger.info(f"✅ {table_name} ya tiene collation correcta")
            
    except mysql.connector.Error as err:
        logger.error(f"❌ Error convirtiendo {table_name} en {server_name}: {err}")
        # No lanzar excepción para continuar con otras tablas
    finally:
        cursor.close()

def get_log_tables(conn):
    """Obtiene todas las tablas LOG_ de la base de datos."""
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT TABLE_NAME 
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = %s 
            AND TABLE_NAME LIKE 'LOG_%'
            ORDER BY TABLE_NAME
        """, (conn.database,))
        
        tables = [row[0] for row in cursor.fetchall()]
        return tables
    finally:
        cursor.close()

def fix_all_tables(conn, server_name):
    """Corrige el charset de todas las tablas LOG_."""
    logger.info(f"=== CORRIGIENDO CHARSETS EN {server_name.upper()} ===")
    
    tables = get_log_tables(conn)
    logger.info(f"Encontradas {len(tables)} tablas LOG_ en {server_name}")
    
    for table_name in tables:
        fix_table_charset(conn, table_name, server_name)
    
    logger.info(f"=== CHARSET CORRECTION COMPLETADA EN {server_name.upper()} ===")

def main():
    """Función principal."""
    logger.info("=== CORRECTOR DE CHARSET/COLLATION PARA TABLAS LOG_ ===")
    
    try:
        # Conectar a origen
        logger.info("Conectando a servidor origen...")
        src_conn = mysql.connector.connect(**SOURCE_CONFIG)
        configure_connection_charset(src_conn, "ORIGEN")
        
        # Conectar a destino
        logger.info("Conectando a servidor destino...")
        dest_conn = mysql.connector.connect(**DEST_CONFIG)
        configure_connection_charset(dest_conn, "DESTINO")
        
        # Corregir charsets
        fix_all_tables(src_conn, "ORIGEN")
       ##fix_all_tables(dest_conn, "DESTINO")
        
        logger.info("🎉 PROCESO COMPLETADO EXITOSAMENTE 🎉")
        
    except mysql.connector.Error as err:
        if err.errno == mysql.connector.errorcode.ER_ACCESS_DENIED_ERROR:
            logger.error("❌ Error de autenticación")
        elif err.errno == mysql.connector.errorcode.ER_BAD_DB_ERROR:
            logger.error("❌ Base de datos no existe")
        else:
            logger.error(f"❌ Error MySQL: {err}")
        sys.exit(1)
        
    except Exception as e:
        logger.error(f"❌ Error inesperado: {e}")
        sys.exit(1)
        
    finally:
        # Cerrar conexiones
        if 'src_conn' in locals() and src_conn.is_connected():
            src_conn.close()
            logger.info("Conexión origen cerrada")
        if 'dest_conn' in locals() and dest_conn.is_connected():
            dest_conn.close()
            logger.info("Conexión destino cerrada")

if __name__ == '__main__':
    main()