import mysql.connector


def get_connection(host, db, user, password):
    try:
        connection = mysql.connector.connect(
            host=host,
            database=db,
            user=user,
            password=password,
            charset='utf8mb4'
        )
        if connection.is_connected():
            print("Connected to MySQL database on {}".format(host))
            return connection
    except Exception as e:
        print("Error: ", e)
        return None


def update_status_tweet_id(prompt_id, tweet_id, connection):
    if connection.is_connected():
        cursor = connection.cursor(dictionary=True)
        query = "UPDATE prompts set status = 1,tweet_id = {} where id = {}".format(tweet_id, prompt_id)
        print(query)
        cursor.execute(query)
        connection.commit()


def update_reddit_status(connection,id):
    if connection.is_connected():
        cursor = connection.cursor(dictionary=True)
        query = "UPDATE prompts set status = 5 where id = {}".format(id)
        print(query)
        cursor.execute(query)
        connection.commit()


def get_prompt(connection, status=0):
    try:
        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)

            # Assuming 'prompts' is the name of your table
            query = "SELECT * FROM prompts WHERE status = {} ORDER BY RAND() LIMIT 1".format(status)
            cursor.execute(query)

            record = cursor.fetchone()

            if record:
                return record
            else:
                return None

    except Exception as e:
        print("Error in get_prompt: {}".format(e))
        return None