import sqlite3
import huggingface_hub
import gradio as gr
import pandas as pd
import shutil
import os
import datetime
from apscheduler.schedulers.background import BackgroundScheduler


DB_FILE = "./results.db"
FROM_DB_FILE = "./data/results.db"

TOKEN = os.environ.get('HUB_TOKEN')
EMAIL = os.environ.get('HUB_EMAIL')
huggingface_hub.login(token=TOKEN)
repo = huggingface_hub.Repository(
    local_dir="data",
    repo_type="dataset",
    clone_from="eliot-christon/french_chatbot_arena_results",
    git_email=EMAIL,

)
repo.git_pull()

# Set db to latest
shutil.copyfile(FROM_DB_FILE, DB_FILE)


# Create table if it doesn't already exist

db = sqlite3.connect(DB_FILE)
try:
    db.execute("SELECT * FROM results").fetchall()
    db.close()
except sqlite3.OperationalError:
    db.execute("""
            CREATE TABLE results (
                date DATE,
                choice TEXT,
                model_1 TEXT,
                model_2 TEXT
            )
        """)
    db.commit()
    db.close()


def get_latest_reviews(db: sqlite3.Connection):
    results = db.execute("SELECT * FROM results limit 10").fetchall()
    total_reviews = db.execute("Select COUNT(choice) from results").fetchone()[0]
    results = pd.DataFrame(results, columns=["date", "choice", "model_1", "model_2"])
    return results, total_reviews


def add_review(choice: str, model_1: str, model_2: str):
    db = sqlite3.connect(DB_FILE)
    cursor = db.cursor()
    cursor.execute("INSERT INTO results(date, choice, model_1, model_2) VALUES(?,?,?,?)", [datetime.datetime.now(), choice, model_1, model_2])
    db.commit()
    results, total_reviews = get_latest_reviews(db)
    db.close()
    return results, total_reviews

def load_data():
    db = sqlite3.connect(DB_FILE)
    results, total_reviews = get_latest_reviews(db)
    db.close()
    return results, total_reviews


with gr.Blocks() as demo:
    with gr.Row():
        with gr.Column():
            choice = gr.Textbox(label="Choice", placeholder="Enter your choice")
            model_1 = gr.Textbox(label="First Model", placeholder="Enter the name of the first model")
            model_2 = gr.Textbox(label="Second Model", placeholder="Enter the name of the second model")
            submit = gr.Button(value="Submit Result", variant="primary")
        with gr.Column():
            data = gr.Dataframe()
            count = gr.Number(label="Total number of results")
    submit.click(add_review, [choice, model_1, model_2], [data, count])
    demo.load(load_data, None, [data, count])


def backup_db():
    shutil.copyfile(DB_FILE, FROM_DB_FILE)
    db = sqlite3.connect(DB_FILE)
    results = db.execute("SELECT * FROM results").fetchall()
    pd.DataFrame(results, columns=["date", "choice", "model_1", "model_2"]).to_csv("./data/results.csv", index=False)
    print("updating db")
    repo.push_to_hub(blocking=False, commit_message=f"Updating data at {datetime.datetime.now()}")


scheduler = BackgroundScheduler()
scheduler.add_job(func=backup_db, trigger="interval", seconds=20)
scheduler.start()


demo.launch()