import sqlite3
import gradio as gr

# Connect to SQLite database (or create it)
conn = sqlite3.connect("users.db", check_same_thread=False)
cursor = conn.cursor()

# Create table if not exists
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
)
''')
conn.commit()

# CRUD functions

def create_user(name, email):
    try:
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
        conn.commit()
        return "User created successfully!"
    except sqlite3.IntegrityError:
        return "Email already exists."

def read_users():
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    if not rows:
        return "No users found."
    result = ""
    for row in rows:
        result += f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}\n"
    return result

def update_user(user_id, name, email):
    cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
    if cursor.fetchone() is None:
        return "User ID not found."
    try:
        cursor.execute("UPDATE users SET name = ?, email = ? WHERE id = ?", (name, email, user_id))
        conn.commit()
        return "User updated successfully!"
    except sqlite3.IntegrityError:
        return "Email already exists."

def delete_user(user_id):
    cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
    if cursor.fetchone() is None:
        return "User ID not found."
    cursor.execute("DELETE FROM users WHERE id = ?", (user_id,))
    conn.commit()
    return "User deleted successfully!"

# Gradio interface

with gr.Blocks() as demo:
    gr.Markdown("# Simple SQLite3 CRUD with Gradio")

    with gr.Tab("Create User"):
        name_in = gr.Textbox(label="Name")
        email_in = gr.Textbox(label="Email")
        create_btn = gr.Button("Create User")
        create_out = gr.Textbox(label="Output")
        create_btn.click(create_user, inputs=[name_in, email_in], outputs=create_out)

    with gr.Tab("Read Users"):
        read_btn = gr.Button("Show All Users")
        read_out = gr.Textbox(label="Users", lines=10)
        read_btn.click(read_users, outputs=read_out)

    with gr.Tab("Update User"):
        update_id = gr.Number(label="User ID")
        update_name = gr.Textbox(label="New Name")
        update_email = gr.Textbox(label="New Email")
        update_btn = gr.Button("Update User")
        update_out = gr.Textbox(label="Output")
        update_btn.click(update_user, inputs=[update_id, update_name, update_email], outputs=update_out)

    with gr.Tab("Delete User"):
        delete_id = gr.Number(label="User ID")
        delete_btn = gr.Button("Delete User")
        delete_out = gr.Textbox(label="Output")
        delete_btn.click(delete_user, inputs=delete_id, outputs=delete_out)

demo.launch(debug=True)