import duckdb
import pandas as pd
from fastapi import FastAPI
import gradio as gr

con = duckdb.connect(database="./workspace/mydatabase.duckdb")
con.execute("CREATE TABLE IF NOT EXISTS items (id INTEGER, name VARCHAR);")

def setup_database_routes(app: FastAPI):
    def create_item(name):
        con.execute("INSERT INTO items (name) VALUES (?);", (name,))
        con.commit()
        return "Item created successfully!"

    def read_items():
        cursor = con.cursor()
        cursor.execute("SELECT * FROM items;")
        items = cursor.fetchall()
        df = pd.DataFrame(items, columns=["ID", "Name"])
        return df

    def update_item(id, name):
        con.execute("UPDATE items SET name = ? WHERE id = ?;", (name, id))
        con.commit()
        return "Item updated successfully!"

    def delete_item(id):
        con.execute("DELETE FROM items WHERE id = ?;", (id,))
        con.commit()
        return "Item deleted successfully!"

    with gr.Blocks() as appdb:
        gr.Markdown("CRUD Application")
        with gr.Row():
            with gr.Column():
                create_name = gr.Textbox(label="Create Item")
                create_btn = gr.Button("Create")
            with gr.Column():
                read_btn = gr.Button("Read Items")
        with gr.Row():
            with gr.Column():
                update_id = gr.Textbox(label="Update Item ID")
                update_name = gr.Textbox(label="Update Item Name")
                update_btn = gr.Button("Update")
            with gr.Column():
                delete_id = gr.Textbox(label="Delete Item ID")
                delete_btn = gr.Button("Delete")
        output_text = gr.Textbox(label="Output")
        output_table = gr.DataFrame(label="Items")

        create_btn.click(fn=create_item, inputs=create_name, outputs=output_text)
        read_btn.click(fn=read_items, outputs=output_table)
        update_btn.click(fn=update_item, inputs=[update_id, update_name], outputs=output_text)
        delete_btn.click(fn=delete_item, inputs=delete_id, outputs=output_text)

    app.mount("/db", appdb, name="database_app")