import gradio as gr
import sqlite3
import time
import json
import pandas as pd

# Example score_file function (simulate long running task)
def score_file(csv_file, model_name):
    # Simulate a delay (replace with your actual processing)
    time.sleep(5)
    # Dummy output; replace with your actual scoring logic.
    result = {
        'individual_scores': {
            'ab': 100.0, 'advanced_geometry': 100.0, 'aiw': 100.0,
            'base_conversion': 100.0, 'basic_arithmetic': 100.0, 'bf': 100.0,
            'binary_alternation': 100.0, 'binary_matrix': 100.0, 'bitwise_arithmetic': 100.0,
            'boxnet': 1.0, 'caesar_cipher': 100.0, 'calendar_arithmetic': 100.0,
            'chain_sum': 100.0, 'circuit_logic': 100.0, 'color_cube_rotation': 100.0,
            'complex_arithmetic': 100.0, 'count_bits': 100.0, 'count_primes': 100.0,
            'course_schedule': 100.0, 'cryptarithm': 100.0, 'decimal_arithmetic': 100.0,
            'decimal_chain_sum': 100.0, 'dice': 100.0, 'emoji_mystery': 100.0,
            'family_relationships': 100.0, 'figlet_font': 100.0, 'fraction_simplification': 100.0,
            'game_of_life': 100.0, 'game_of_life_halting': 100.0, 'graph_color': 0.0,
            'group_anagrams': 100.0, 'intermediate_integration': 100.0, 'isomorphic_strings': 100.0,
            'jugs': 100.0, 'largest_island': 100.0, 'lcm': 100.0, 'leg_counting': 100.0,
            'letter_counting': 100.0, 'letter_jumble': 100.0, 'mahjong_puzzle': 100.0,
            'manipulate_matrix': 100.0, 'maze': 100.0, 'mini_sudoku': 100.0, 'modulo_grid': 100.0,
            'n_queens': 100.0, 'needle_haystack': 100.0, 'number_filtering': 100.0,
            'number_format': 100.0, 'number_sequence': 100.0, 'number_sorting': 100.0,
            'palindrome_generation': 100.0, 'palindrome_partitioning': 100.0,
            'polynomial_equations': 100.0, 'polynomial_multiplication': 0.0, 'pool_matrix': 100.0,
            'power_function': 100.0, 'prime_factorization': 100.0, 'products': 100.0,
            'propositional_logic': 0.0, 'quantum_lock': 100.0, 'ransom_note': 100.0,
            'rectangle_count': 100.0, 'rotate_matrix': 100.0, 'rotten_oranges': 100.0,
            'rush_hour': 0.0, 'self_reference': 100.0, 'sentence_reordering': 100.0,
            'shortest_path': 100.0, 'simple_equations': 100.0, 'simple_geometry': 100.0,
            'simple_integration': 100.0, 'sokoban': 100.0, 'spell_backward': 100.0,
            'spiral_matrix': 100.0, 'string_insertion': 100.0, 'string_manipulation': 100.0,
            'string_splitting': 100.0, 'string_synthesis': 100.0, 'sudoku': 100.0,
            'time_intervals': 100.0, 'tsumego': 100.0, 'word_sequence_reversal': 100.0,
            'zebra_puzzles': 100.0
        },
        'total_score': 93.98795180722891
    }
    return result

# Initialize or connect to a SQLite database
conn = sqlite3.connect("results.db", check_same_thread=False)
c = conn.cursor()
c.execute("""
    CREATE TABLE IF NOT EXISTS results (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        model_name TEXT,
        total_score REAL,
        result_json TEXT,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )
""")
conn.commit()

def process_file(file_obj, model_name):
    # Run the scoring function
    result = score_file(file_obj, model_name)

    # Save the result to the database
    result_json = json.dumps(result)
    total_score = result.get("total_score", 0)
    c.execute("INSERT INTO results (model_name, total_score, result_json) VALUES (?, ?, ?)",
              (model_name, total_score, result_json))
    conn.commit()

    # Retrieve updated records, sorted by total_score descending
    df = get_saved_results()
    
    # Return the individual run result and the table of saved results
    return f"Current Run Result:\n{json.dumps(result, indent=2)}", df

def get_saved_results():
    c.execute("SELECT id, model_name, total_score, timestamp FROM results ORDER BY total_score DESC")
    records = c.fetchall()
    # Create a pandas DataFrame for a nicer display
    df = pd.DataFrame(records, columns=["ID", "Model Name", "Total Score", "Timestamp"])
    return df

with gr.Blocks() as demo:
    gr.Markdown("# RGBench [WIP]")
    gr.Markdown("Upload a CSV file with your completed results and enter the name of your model. RGBench will score and save your results.")

    with gr.Row():
        file_input = gr.File(label="Upload CSV File", file_types=['.csv'])
        model_input = gr.Textbox(label="Model Name")

    run_button = gr.Button("Run Scoring")

    result_output = gr.Textbox(label="Score File Result", lines=15)
    table_output = gr.Dataframe(label="All Saved Results (sorted by Total Score)")

    run_button.click(fn=process_file, inputs=[file_input, model_input], outputs=[result_output, table_output])
    
    # Load the table data on page load
    demo.load(fn=get_saved_results, inputs=[], outputs=table_output)

demo.launch()