#este script compara tablas de una base y las crea en otras junto a los datos

import mysql.connector
import traceback
from datetime import datetime

# Configuración de conexión
source_config = {
    'host': '10.2.12.220',
    'port': 3306,
    'user': 'gps',
    'password': 'q1w2e3r4',
    'database': 'gps_reportes'
}

dest_config = {
    'host': 'base220.c8lcuo0a2bu6.us-east-1.rds.amazonaws.com',
    'port': 3306,
    'user': 'gps',
    'password': 'q1w2e3r4',
    'database': 'gps_reportes'
}

# Log
log_file = 'sync_log.txt'

def log(msg):
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with open(log_file, 'a') as f:
        f.write(f"[{timestamp}] {msg}\n")

def main():
    try:
        src = mysql.connector.connect(**source_config)
        dst = mysql.connector.connect(**dest_config)

        src_cursor = src.cursor()
        dst_cursor = dst.cursor()

        # Obtener todas las tablas de la base local
        src_cursor.execute("SHOW TABLES")
        tablas = [t[0] for t in src_cursor.fetchall()]

        for tabla in tablas:
            try:
                print(f"Procesando tabla: {tabla}")
                # Verificar si existe en destino
                dst_cursor.execute(f"SHOW TABLES LIKE '{tabla}'")
                existe = dst_cursor.fetchone()

                if not existe:
                    # Crear estructura
                    src_cursor.execute(f"SHOW CREATE TABLE {tabla}")
                    create_stmt = src_cursor.fetchone()[1]
                    dst_cursor.execute(create_stmt)
                    dst.commit()
                    print(f"Tabla {tabla} creada.")

                    # Copiar datos
                    src_cursor.execute(f"SELECT * FROM {tabla}")
                    rows = src_cursor.fetchall()
                    if rows:
                        cols = ', '.join([desc[0] for desc in src_cursor.description])
                        placeholders = ', '.join(['%s'] * len(src_cursor.description))
                        insert_stmt = f"INSERT INTO {tabla} ({cols}) VALUES ({placeholders})"
                        dst_cursor.executemany(insert_stmt, rows)
                        dst.commit()
                        print(f"{len(rows)} registros copiados a {tabla}")
                else:
                    print(f"Tabla {tabla} ya existe, se omite.")

            except Exception as e:
                log(f"Error con tabla {tabla}: {e}")
                log(traceback.format_exc())
                continue

        src_cursor.close()
        dst_cursor.close()
        src.close()
        dst.close()
        print("Proceso finalizado.")

    except Exception as e:
        log(f"Fallo general: {e}")
        log(traceback.format_exc())

if __name__ == "__main__":
    main()