import gradio as gr
from bs4 import BeautifulSoup
import requests
from acogsphere import acf
from bcogsphere import bcf
import math

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

import random
import time

DB_FILE = "./reviews0.db"

TOKEN = os.environ.get('HF_KEY')

repo = huggingface_hub.Repository(
    local_dir="data",
    repo_type="dataset",
    clone_from="CogSphere/dataset1",
    use_auth_token=TOKEN
)
repo.git_pull()

# Set db to latest
#shutil.copyfile("./data/reviews.db", DB_FILE)

# Create table if it doesn't already exist

db = sqlite3.connect(DB_FILE)
try:
    db.execute("SELECT * FROM reviews").fetchall()
    db.close()
except sqlite3.OperationalError:
    db.execute(
        '''
        CREATE TABLE reviews (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                              created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                              name TEXT, review INTEGER, comments TEXT)
        ''')
    db.commit()
    db.close()

def get_latest_reviews(db: sqlite3.Connection):
    reviews = db.execute("SELECT * FROM reviews ORDER BY id DESC limit 100").fetchall()
    total_reviews = db.execute("Select COUNT(id) from reviews").fetchone()[0]
    reviews = pd.DataFrame(reviews, columns=["id", "date_created", "name", "review", "comments"])
    return reviews, total_reviews


def ccogsphere(name: str, review: int, comments: str):
    db = sqlite3.connect(DB_FILE)
    cursor = db.cursor()
    cursor.execute("INSERT INTO reviews(name, review, comments) VALUES(?,?,?)", [name, review, comments])
    db.commit()
    reviews, total_reviews = get_latest_reviews(db)
    db.close()
    #demo.load()
    return reviews, total_reviews

def load_data():
    db = sqlite3.connect(DB_FILE)
    reviews, total_reviews = get_latest_reviews(db)
    db.close()
    return reviews, total_reviews
css="footer {visibility: hidden}"

with gr.Blocks(css=css,api_name=["/ccogsphere"]) as demo:
    with gr.Row():
        with gr.Column():
            #with gr.Box():
                #gr.Markdown("Based on dataset [here](https://huggingface.co/datasets/freddyaboulton/gradio-reviews)")
            data = gr.Dataframe()
            count = gr.Number(label="Rates!")
    with gr.Row():
        with gr.Column():
            name = gr.Textbox(label="a") #, placeholder="What is your name?")
            review =  gr.Textbox(label="b") #, placeholder="What is your name?") #gr.Radio(label="How satisfied are you with using gradio?", choices=[1, 2, 3, 4, 5])
            comments = gr.Textbox(label="c") #, lines=10, placeholder="Do you have any feedback on gradio?")
            submit = gr.Button(value=".")

                        
            submit.click(ccogsphere, [name, review, comments], [data, count])
            demo.load(load_data, None, [data, count])
            @name.change(inputs=name, outputs=comments,_js="window.location.reload()")
            def secwork(name):
                #if name=="abc":
                load_data()
                #return "Hello " + name + "!"
    
def backup_db():
    shutil.copyfile(DB_FILE, "./reviews0.db")
    db = sqlite3.connect(DB_FILE)
    reviews = db.execute("SELECT * FROM reviews").fetchall()
    pd.DataFrame(reviews).to_csv("./reviews1.csv", index=False)
    print("updating db")
    repo.push_to_hub(blocking=False, commit_message=f"Updating data at {datetime.datetime.now()}")

def load_data2():
    db = sqlite3.connect(DB_FILE)
    reviews, total_reviews = get_latest_reviews(db)
    #db.close()
    return reviews, total_reviews
    
scheduler = BackgroundScheduler()
scheduler.add_job(func=backup_db, trigger="interval", seconds=10)
scheduler.start()

scheduler2 = BackgroundScheduler()
scheduler2.add_job(func=load_data2, trigger="interval", seconds=10)
scheduler2.start()
demo.launch()