# To test open trust-game-llama2-7b in terminal,
# open conda environment via conda activate  
# and run this file via python3 data_fetcher.py

# Example from here: https://www.techgropse.com/blog/how-to-return-sql-data-in-json-format-python/
# Use the built-in json module and the pymysql package if using MySQL as database: 
import mysql.connector
import json
import requests 
import urllib.parse
import urllib.request

import gradio as gr
import time
import random
import json

# fetch query strings from URL  
get_window_url_params = """
    function() {
        const params = new URLSearchParams(window.location.search);
        const url_params = Object.fromEntries(params);
        return url_params;
        }
    """

# fetch data from the decisions database 
def fetch_data_as_json():
    # 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()

    # Execute the SQL query
    query = "SELECT playerNr, subjectNr, initialCredit, transfer1, tripledAmount1, keptForSelf1, returned1, " \
            "newCreditRound2, transfer2, tripledAmount2, keptForSelf2, returned2, results2rounds, " \
            "newCreditRound3, transfer3, tripledAmount3, keptForSelf3, returned3, results3rounds FROM e5390g37096_decisions"
    cursor.execute(query)

    # Fetch all rows and convert to a list of dictionaries
    rows = cursor.fetchall()
    result = []
    for row in rows:
        d = {}
        for i, col in enumerate(cursor.description):
            d[col[0]] = row[i]
        result.append(d)

    # Convert the list of dictionaries to JSON
    json_result = json.dumps(result)

    # Close the database connection
    conn.close()

    # Return the JSON result
    return json_result

# Call the function to fetch data as JSON
json_result = fetch_data_as_json()

# Print or use the json_result variable as needed
print(json_result)


# In the above code, we first connect to the database using pymysql, then create a cursor object to execute the SQL query. 
# We then fetch all rows from the query result and convert them to a list of dictionaries, 
# where each dictionary represents a row in the table with column names as keys and their values as values. 
# Finally, we convert the list of dictionaries to JSON format using the json.dumps() method and print it.


#In general: Track the page that participants are currently on - via the core table from the database using the variable onPage 
#Once multiple groups: Track the participants’ group number - via the core table from the database using the variable groupNr 

# fetch data from the core database 
def fetch_data_core():
    # 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()

    # Execute the SQL query
    query = "SELECT playerNr, subjectNr, groupNrStart, onPage FROM e5390g37096_core WHERE subjectNr = 1" 
    cursor.execute(query)

    # Fetch all rows and convert to a list of dictionaries
    rows = cursor.fetchall()
    result = []
    for row in rows:
        d = {}
        for i, col in enumerate(cursor.description):
            d[col[0]] = row[i]
        result.append(d)

    # Convert the list of dictionaries to JSON
    core_result = json.dumps(result)

    # Close the database connection
    conn.close()

    # Return the JSON result
    return core_result

# Call the function to fetch data as JSON
core_result = fetch_data_core()

# Print or use the json_result variable as needed
print(core_result)