import tkinter as tk
from tkinter import messagebox
from tkinter import ttk
import mysql.connector

# ------------------ DATABASE CONNECTION ------------------ #
def get_connection():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="",      # put your MySQL password
        database="testdb"
    )

# ------------------ INSERT ------------------ #
def insert_student():
    name = entry_name.get()
    marks = entry_marks.get()

    if name == "" or marks == "":
        messagebox.showerror("Error", "All fields required")
        return

    conn = get_connection()
    cur = conn.cursor()
    cur.execute("INSERT INTO student (name, marks) VALUES (%s, %s)", (name, marks))
    conn.commit()
    conn.close()

    messagebox.showinfo("Success", "Student Added")
    clear_fields()
    fetch_students()

# ------------------ FETCH ------------------ #
def fetch_students():
    for row in table.get_children():
        table.delete(row)

    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM student")
    rows = cur.fetchall()

    for row in rows:
        table.insert("", tk.END, values=row)

    conn.close()

# ------------------ SELECT ------------------ #
def select_student(event):
    selected = table.focus()
    data = table.item(selected, "values")

    if data:
        entry_id.delete(0, tk.END)
        entry_name.delete(0, tk.END)
        entry_marks.delete(0, tk.END)

        entry_id.insert(0, data[0])
        entry_name.insert(0, data[1])
        entry_marks.insert(0, data[2])

# ------------------ UPDATE ------------------ #
def update_student():
    id = entry_id.get()
    name = entry_name.get()
    marks = entry_marks.get()

    if id == "":
        messagebox.showerror("Error", "Select student")
        return

    conn = get_connection()
    cur = conn.cursor()
    cur.execute(
        "UPDATE student SET name=%s, marks=%s WHERE id=%s",
        (name, marks, id)
    )
    conn.commit()
    conn.close()

    messagebox.showinfo("Success", "Student Updated")
    clear_fields()
    fetch_students()

# ------------------ DELETE ------------------ #
def delete_student():
    id = entry_id.get()

    if id == "":
        messagebox.showerror("Error", "Select student")
        return

    conn = get_connection()
    cur = conn.cursor()
    cur.execute("DELETE FROM student WHERE id=%s", (id,))
    conn.commit()
    conn.close()

    messagebox.showinfo("Success", "Student Deleted")
    clear_fields()
    fetch_students()

# ------------------ CLEAR ------------------ #
def clear_fields():
    entry_id.delete(0, tk.END)
    entry_name.delete(0, tk.END)
    entry_marks.delete(0, tk.END)

# ------------------ TKINTER UI ------------------ #
root = tk.Tk()
root.title("Student CRUD - MySQL")
root.geometry("600x500")

# Labels & Entries
tk.Label(root, text="ID").grid(row=0, column=0, padx=10, pady=5)
entry_id = tk.Entry(root)
entry_id.grid(row=0, column=1)

tk.Label(root, text="Name").grid(row=1, column=0, padx=10, pady=5)
entry_name = tk.Entry(root)
entry_name.grid(row=1, column=1)

tk.Label(root, text="Marks").grid(row=2, column=0, padx=10, pady=5)
entry_marks = tk.Entry(root)
entry_marks.grid(row=2, column=1)

# Buttons
tk.Button(root, text="Insert", width=12, command=insert_student).grid(row=3, column=0, pady=10)
tk.Button(root, text="Update", width=12, command=update_student).grid(row=3, column=1)
tk.Button(root, text="Delete", width=12, command=delete_student).grid(row=4, column=0)
tk.Button(root, text="Clear", width=12, command=clear_fields).grid(row=4, column=1)

# Table
table = ttk.Treeview(root, columns=("ID", "Name", "Marks"), show="headings")
table.heading("ID", text="ID")
table.heading("Name", text="Name")
table.heading("Marks", text="Marks")
table.grid(row=5, column=0, columnspan=2, pady=20)

table.bind("<ButtonRelease-1>", select_student)

fetch_students()

root.mainloop()