import gradio as gr
import sqlite3

# Database setup
def init_db():
    conn = sqlite3.connect('inventory.db')
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS inventory (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT NOT NULL,
                        quantity INTEGER NOT NULL,
                        price REAL NOT NULL
                    )''')
    conn.commit()
    conn.close()

init_db()

# Functions to handle inventory
def view_inventory():
    conn = sqlite3.connect('inventory.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM inventory")
    items = cursor.fetchall()
    conn.close()
    return items

def add_item(name, quantity, price):
    conn = sqlite3.connect('inventory.db')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO inventory (name, quantity, price) VALUES (?, ?, ?)", (name, quantity, price))
    conn.commit()
    conn.close()
    return f"Added {name} to inventory."

def delete_item(item_id):
    conn = sqlite3.connect('inventory.db')
    cursor = conn.cursor()
    cursor.execute("DELETE FROM inventory WHERE id = ?", (item_id,))
    conn.commit()
    conn.close()
    return f"Deleted item with ID {item_id}."

def update_item(item_id, name, quantity, price):
    conn = sqlite3.connect('inventory.db')
    cursor = conn.cursor()
    cursor.execute("UPDATE inventory SET name = ?, quantity = ?, price = ? WHERE id = ?", (name, quantity, price, item_id))
    conn.commit()
    conn.close()
    return f"Updated item with ID {item_id}."

# Gradio UI
with gr.Blocks() as demo:
    gr.Markdown("# Inventory Management System")
    with gr.Row():
        with gr.Column():
            name = gr.Textbox(label="Item Name")
            quantity = gr.Number(label="Quantity")
            price = gr.Number(label="Price")
            add_btn = gr.Button("Add Item")
            add_output = gr.Textbox()
        with gr.Column():
            item_id_del = gr.Number(label="Item ID to Delete")
            delete_btn = gr.Button("Delete Item")
            delete_output = gr.Textbox()
        with gr.Column():
            item_id_upd = gr.Number(label="Item ID to Update")
            name_upd = gr.Textbox(label="New Name")
            quantity_upd = gr.Number(label="New Quantity")
            price_upd = gr.Number(label="New Price")
            update_btn = gr.Button("Update Item")
            update_output = gr.Textbox()
    
    inventory_list = gr.Dataframe(headers=["ID", "Name", "Quantity", "Price"])
    refresh_btn = gr.Button("Refresh Inventory")
    
    add_btn.click(add_item, inputs=[name, quantity, price], outputs=add_output)
    delete_btn.click(delete_item, inputs=item_id_del, outputs=delete_output)
    update_btn.click(update_item, inputs=[item_id_upd, name_upd, quantity_upd, price_upd], outputs=update_output)
    refresh_btn.click(view_inventory, outputs=inventory_list)

demo.launch()