import gradio as gr
import time
import random
import json
import mysql.connector
import os
import csv
from datetime import datetime
from huggingface_hub import Repository, hf_hub_download

def fetch_personalized_data(session_index):
    # Connect to the database
    conn = mysql.connector.connect(
        host="18.153.94.89",
        user="root",
        password="N12RXMKtKxRj",
        database="lionessdb"
    )

    # Create a cursor object
    cursor = conn.cursor()

    # Replace the placeholders with your actual database and table names
    core_table = "e5390g37096_core"
    decisions_table = "e5390g37096_decisions"

    # Query to fetch relevant data from both tables based on session_index
    query = f"""
        SELECT e5390g37096_core.playerNr, 
               e5390g37096_core.groupNr, 
               e5390g37096_core.subjectNr 
        FROM e5390g37096_core
        JOIN e5390g37096_decisions ON 
            e5390g37096_core.playerNr = e5390g37096_decisions.playerNr
        WHERE e5390g37096_decisions.session_index = '{session_index}'
    """

    try:
        cursor.execute(query)

        # Fetch all rows as lists of tuples
        rows = cursor.fetchall()

        # Close the database connection
        conn.close()

        # return [[str(row[0]), str(row[1]), str(row[2])] for row in rows]  # Convert each row to a list
        # Convert the rows to a list of dictionaries
        data = [{'playerNr': row[0], 'groupNr': row[1], 'subjectNr': row[2]} for row in rows]
        return data

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

def get_window_url_params():
    return """
        function() {
            const params = new URLSearchParams(window.location.search);
            const url_params = Object.fromEntries(params);
            return url_params;
            }
        """
    
with gr.Blocks() as demo:
    gr.Markdown("""## Gradio send queryparam to chatbot 
    type `hi`
    """)
    url_params = gr.JSON({}, visible=False, label="URL Params")
    chatbot = gr.Chatbot().style(height=500)
    msg = gr.Textbox()
    clear = gr.Button("Clear")

    def user(user_message, url_params, history):
        return "", history + [[user_message, None]]
    
    def bot(history, url_params):
        if "hi" in history[-1][0]:
            session_index = url_params.get('session_index')
            if session_index:  # Check if session_index is not None or empty
                #print(session_index)
                bot_message = f"""
                here your URL params:
                {json.dumps(url_params, indent=4)}
                """
                # Fetch personalized data
                personalized_data = fetch_personalized_data(session_index)
                print(personalized_data)
            else:
                bot_message = "Session index is missing."
        else:
            bot_message = "There was an error. Please try again."
        history[-1][1] = bot_message
        time.sleep(1)
        return history
    
    
    msg.submit(user, inputs=[msg, url_params, chatbot], outputs=[msg, chatbot], queue=False).then(
        fn=bot, inputs=[chatbot, url_params], outputs=[chatbot]
    )
    clear.click(lambda: None, None, chatbot, queue=False)
    demo.load(
        fn=lambda x: x,
        inputs=[url_params],
        outputs=[url_params],
        _js=get_window_url_params(),
        queue=False
    )
    
demo.launch()