import mysql.connector
from mysql.connector import Error
import time

def main():
    try:
        # Establish the connection to the MySQL database
        connection = mysql.connector.connect(
            host="dbswf33330813.syss0001.stage1.skysql.net",
            user="dbswf33330813",
            password="Adm1n777#",
            port=5001,
            database="travel"
        )

        # Check if the connection is established
        if connection.is_connected():
            print("Connected to the MySQL Server.")

            # Start the transaction
            cursor = connection.cursor(buffered=True)
            connection.start_transaction()

            # Select dep_delay from the flights table where carrier='UA' and flight_id like '69%'
            select_query = """SELECT dep_delay FROM flights WHERE carrier = 'UA' AND flight_id LIKE '69%';"""
            cursor.execute(select_query)

            # Sleep for 300 seconds
            print("Sleeping for 300 seconds...")
            time.sleep(300)
            print("Getting connection again flights")
            connection2 = mysql.connector.connect(
            host="dbswf33330813.syss0001.stage1.skysql.net",
            user="dbswf33330813",
            password="Adm1n777#",
            port=5001,
            database="travel"
            )
            cursor2 = connection2.cursor(buffered=True)
            print("Updating flights")

            # Update dep_delay in the flights table where carrier='UA' and flight_id like '69%'
            update_query = """UPDATE flights SET dep_delay = dep_delay + 1 WHERE carrier = 'UA' AND flight_id LIKE '69%';"""
            cursor2.execute(update_query)
            
            print("Committing connection")

            # Commit the transaction
            connection2.commit()
            print("Transaction committed.")

    except Error as e:
        print("Error occurred:", e)
        # Rollback the transaction in case of an error
        connection.rollback()
        print("Transaction rolled back.")
    
    finally:
        # Close the cursor and connection
        if cursor:
            cursor.close()
        if connection:
            connection.close()
            print("MySQL connection closed.")

if __name__ == "__main__":
    main()