import mysql.connector
from datetime import datetime

DB_HOST = "10.2.12.220"
DB_USER = "gps"
DB_PASSWORD = "q1w2e3r4"
DB_NAME = "gps_reportes"

try:
    mydb = mysql.connector.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME
    )
    mycursor = mydb.cursor()

    # Modificar la consulta SELECT para traer solo las tablas NO optimizadas
    mycursor.execute(
        """
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = 'gps_reportes'
          
          AND TABLE_NAME NOT IN (SELECT nombre_tabla FROM tablas_optimizadas)
        ORDER BY TABLE_NAME DESC
        """
    )

    tables = mycursor.fetchall()

    print("\n--- Optimizing tables ---")  # Mensaje general de optimización

    for table_tuple in tables:
        table_name = table_tuple[0]
        print(f"  Optimizing table: {table_name}")
        optimize_query = f"OPTIMIZE TABLE `{table_name}`"
        mycursor.execute(optimize_query)
        result = mycursor.fetchall()
        for row in result:
            print(f"    {row}")

        # Insertar registro en la tabla tablas_optimizadas
        insert_query = "INSERT INTO tablas_optimizadas (nombre_tabla, fecha) VALUES (%s, %s)"
        now = datetime.now()
        mycursor.execute(insert_query, (table_name, now))
        mydb.commit()  # Confirmar la inserción

    print("\nOptimization process completed.")

except mysql.connector.Error as err:
    print(f"Error: {err}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()