import tkinter as tk
from tkinter import ttk, simpledialog, messagebox
from datetime import datetime, timedelta
import mysql.connector

# ===================== DATABASE CONNECTION =====================
def get_db_connection():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="root",  # Change to your MySQL password
        database="library"  # Change to your database name
    )

# ===================== ADMIN FUNCTIONS =====================
def view_books_admin():
    admin_books_win = tk.Toplevel()
    admin_books_win.title("View All Books (Admin)")
    admin_books_win.geometry("1000x650")
    admin_books_win.configure(bg="#ecf0f1")

    tree = ttk.Treeview(admin_books_win, columns=("ID", "Title", "Author", "Quantity", "Category"), show="headings")
    tree.heading("ID", text="ID")
    tree.heading("Title", text="Title")
    tree.heading("Author", text="Author")
    tree.heading("Quantity", text="Quantity")
    tree.heading("Category", text="Category")
    tree.pack(fill="both", expand=True, padx=20, pady=20)

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM books")
    rows = cursor.fetchall()
    db.close()

    for row in rows:
        tree.insert("", "end", values=row)

def add_book():
    title = simpledialog.askstring("Add Book", "Enter book title:")
    author = simpledialog.askstring("Add Book", "Enter author name:")
    quantity = simpledialog.askinteger("Add Book", "Enter quantity:")
    category = simpledialog.askstring("Add Book", "Enter category:")

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("INSERT INTO books (title, author, quantity, category) VALUES (%s, %s, %s, %s)",
                   (title, author, quantity, category))
    db.commit()
    db.close()

    messagebox.showinfo("Success", "Book added successfully!")

def update_book():
    book_id = simpledialog.askinteger("Update Book", "Enter the Book ID to update:")
    if book_id:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM books WHERE id = %s", (book_id,))
        book = cursor.fetchone()

        if book:
            new_title = simpledialog.askstring("Update Book", f"Enter new title for {book[1]}:")
            new_author = simpledialog.askstring("Update Book", f"Enter new author for {book[2]}:")
            new_quantity = simpledialog.askinteger("Update Book", f"Enter new quantity for {book[3]}:")
            new_category = simpledialog.askstring("Update Book", f"Enter new category for {book[4]}:")

            cursor.execute("""UPDATE books SET title=%s, author=%s, quantity=%s, category=%s WHERE id=%s""",
                           (new_title, new_author, new_quantity, new_category, book_id))
            db.commit()
            db.close()

            messagebox.showinfo("Success", f"Book with ID {book_id} updated successfully!")
        else:
            messagebox.showerror("Error", "Book not found.")

def delete_book():
    book_id = simpledialog.askinteger("Delete Book", "Enter the Book ID to delete:")
    if book_id:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("DELETE FROM books WHERE id = %s", (book_id,))
        db.commit()
        db.close()
        messagebox.showinfo("Success", f"Book with ID {book_id} deleted successfully!")

def issue_book():
    book_id = simpledialog.askinteger("Issue Book", "Enter the Book ID to issue:")
    student_username = simpledialog.askstring("Issue Book", "Enter the Student Username:")

    if not book_id or not student_username:
        messagebox.showerror("Error", "Book ID and Student Username are required.")
        return

    db = get_db_connection()
    cursor = db.cursor()

    # Validate student exists
    cursor.execute("SELECT id FROM users WHERE username = %s AND role = 'student'", (student_username,))
    user = cursor.fetchone()

    if not user:
        messagebox.showerror("Error", "Student not found. Please enter a valid registered student username.")
        db.close()
        return

    # Check if book is available
    cursor.execute("SELECT quantity FROM books WHERE id = %s", (book_id,))
    result = cursor.fetchone()

    if result and result[0] > 0:
        issue_date = datetime.now().date()
        due_date = issue_date + timedelta(days=14)

        cursor.execute(
            "INSERT INTO issued_books (book_id, student_username, issue_date, due_date) VALUES (%s, %s, %s, %s)",
            (book_id, student_username, issue_date, due_date)
        )
        cursor.execute("UPDATE books SET quantity = quantity - 1 WHERE id = %s", (book_id,))
        db.commit()
        db.close()
        messagebox.showinfo("Success", "Book issued successfully!")
    else:
        db.close()
        messagebox.showerror("Error", "Book not available.")

def return_book():
    issue_id = simpledialog.askinteger("Return Book", "Enter the Issue ID to return:")
    return_date = datetime.now().date()

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("SELECT due_date FROM issued_books WHERE id = %s AND return_date IS NULL", (issue_id,))
    result = cursor.fetchone()

    if result:
        due_date = result[0]
        fine = max(0, (return_date - due_date).days)
        cursor.execute("UPDATE issued_books SET return_date = %s, fine = %s WHERE id = %s",
                       (return_date, fine, issue_id))
        cursor.execute(
            "UPDATE books SET quantity = quantity + 1 WHERE id = (SELECT book_id FROM issued_books WHERE id = %s)",
            (issue_id,))
        db.commit()
        db.close()
        messagebox.showinfo("Success", f"Book returned successfully! Fine: ${fine:.2f}")
    else:
        messagebox.showerror("Error", "Invalid issue ID or book already returned.")

def view_issued_books_admin():
    win = tk.Toplevel()
    win.title("Issued Books")
    win.geometry("1000x650")
    win.configure(bg="#ecf0f1")

    tree = ttk.Treeview(win, columns=("ID", "Title", "Student Username", "Issue", "Due", "Return", "Fine"), show="headings")
    for col in tree["columns"]:
        tree.heading(col, text=col)
    tree.pack(fill="both", expand=True, padx=20, pady=20)

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("""SELECT ib.id, b.title, ib.student_username, ib.issue_date, ib.due_date, ib.return_date, ib.fine 
                      FROM issued_books ib JOIN books b ON ib.book_id = b.id""")
    for row in cursor.fetchall():
        tree.insert("", "end", values=row)
    db.close()

# ===================== STUDENT FUNCTIONS =====================
def view_books_student():
    win = tk.Toplevel()
    win.title("Available Books")
    win.geometry("1000x650")
    win.configure(bg="#ecf0f1")

    tree = ttk.Treeview(win, columns=("ID", "Title", "Author", "Quantity", "Category"), show="headings")
    for col in tree["columns"]:
        tree.heading(col, text=col)
    tree.pack(fill="both", expand=True, padx=20, pady=20)

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM books WHERE quantity > 0")
    for row in cursor.fetchall():
        tree.insert("", "end", values=row)
    db.close()

def search_books_student():
    query = simpledialog.askstring("Search Books", "Enter title, author, or category:")
    if query:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("""SELECT * FROM books 
                          WHERE title LIKE %s OR author LIKE %s OR category LIKE %s""",
                       (f'%{query}%', f'%{query}%', f'%{query}%'))
        rows = cursor.fetchall()
        db.close()

        if rows:
            win = tk.Toplevel()
            win.title("Search Results")
            win.geometry("1000x650")
            win.configure(bg="#ecf0f1")
            tree = ttk.Treeview(win, columns=("ID", "Title", "Author", "Quantity", "Category"), show="headings")
            for col in tree["columns"]:
                tree.heading(col, text=col)
            tree.pack(fill="both", expand=True, padx=20, pady=20)
            for row in rows:
                tree.insert("", "end", values=row)
        else:
            messagebox.showinfo("No Results", "No books found.")

def reserve_book_student():
    book_id = simpledialog.askinteger("Reserve Book", "Enter the Book ID to reserve:")
    student_username = simpledialog.askstring("Reserve Book", "Enter your Student Username:")

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("SELECT quantity FROM books WHERE id = %s", (book_id,))
    result = cursor.fetchone()

    if result and result[0] == 0:
        cursor.execute("INSERT INTO reserved_books (book_id, student_username) VALUES (%s, %s)", (book_id, student_username))
        db.commit()
        db.close()
        messagebox.showinfo("Reservation Success", "Book reserved successfully!")
    else:
        messagebox.showerror("Error", "Book is available, no need to reserve.")

def view_fine_student(username):
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("SELECT SUM(fine) FROM issued_books WHERE student_username = %s AND return_date IS NULL", (username,))
    result = cursor.fetchone()
    db.close()
    fine_amount = result[0] if result[0] else 0
    messagebox.showinfo("Fine", f"Your current fine: ${fine_amount:.2f}")

def view_issued_books_student(username):
    win = tk.Toplevel()
    win.title("Issued Books")
    win.geometry("1000x650")
    win.configure(bg="#ecf0f1")

    tree = ttk.Treeview(win, columns=("ID", "Title", "Issue Date", "Due Date", "Return Date", "Fine"), show="headings")
    for col in tree["columns"]:
        tree.heading(col, text=col)
    tree.pack(fill="both", expand=True, padx=20, pady=20)

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("""SELECT b.title, ib.issue_date, ib.due_date, ib.return_date, ib.fine 
                      FROM issued_books ib 
                      JOIN books b ON ib.book_id = b.id 
                      WHERE ib.student_username = %s""", (username,))
    rows = cursor.fetchall()
    db.close()

    for row in rows:
        tree.insert("", "end", values=row)

# ===================== REGISTRATION FUNCTION =====================
def register():
    reg_win = tk.Toplevel()
    reg_win.title("Register")
    reg_win.geometry("500x550")
    reg_win.configure(bg="#ecf0f1")

    tk.Label(reg_win, text="Register", font=("Helvetica", 16), bg="#ecf0f1").pack(pady=10)

    tk.Label(reg_win, text="Username:", bg="#ecf0f1").pack()
    reg_username = tk.Entry(reg_win)
    reg_username.pack(pady=5)

    tk.Label(reg_win, text="Password:", bg="#ecf0f1").pack()
    reg_password = tk.Entry(reg_win, show="*")
    reg_password.pack(pady=5)

    tk.Label(reg_win, text="Email:", bg="#ecf0f1").pack()
    reg_email = tk.Entry(reg_win)
    reg_email.pack(pady=5)

    tk.Label(reg_win, text="Role:", bg="#ecf0f1").pack()
    reg_role = ttk.Combobox(reg_win, values=["admin", "student"], state="readonly")
    reg_role.pack(pady=5)

    def submit_registration():
        username = reg_username.get()
        password = reg_password.get()
        email = reg_email.get()
        role = reg_role.get()

        if not all([username, password, email, role]):
            messagebox.showerror("Error", "All fields are required.")
            return

        db = get_db_connection()
        cursor = db.cursor()
        try:
            cursor.execute("INSERT INTO users (username, password, email, role) VALUES (%s, %s, %s, %s)",
                           (username, password, email, role))
            db.commit()
            messagebox.showinfo("Success", "Registration successful!")
            reg_win.destroy()
        except mysql.connector.errors.IntegrityError:
            messagebox.showerror("Error", "Username already exists.")
        finally:
            db.close()

    tk.Button(reg_win, text="Submit", command=submit_registration, bg="#2ecc71", fg="white",
              font=("Helvetica", 12)).pack(pady=20)

# ===================== LOGIN & DASHBOARDS =====================
def login():
    login_win = tk.Toplevel()
    login_win.title("Login")
    login_win.geometry("500x400")
    login_win.configure(bg="#ecf0f1")

    tk.Label(login_win, text="Login", font=("Helvetica", 16, "bold"), bg="#ecf0f1").pack(pady=20)

    tk.Label(login_win, text="Username:", bg="#ecf0f1", font=("Helvetica", 12)).pack()
    login_username = tk.Entry(login_win, font=("Helvetica", 12))
    login_username.pack(pady=5)

    tk.Label(login_win, text="Password:", bg="#ecf0f1", font=("Helvetica", 12)).pack()
    login_password = tk.Entry(login_win, show="*", font=("Helvetica", 12))
    login_password.pack(pady=5)

    def submit_login():
        username = login_username.get()
        password = login_password.get()

        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))
        user = cursor.fetchone()
        db.close()

        if user:
            messagebox.showinfo("Login Successful", f"Welcome, {user[1]}!")
            login_win.destroy()

            if user[4] == "admin":
                admin_dashboard()
            else:
                student_dashboard(user[1])
        else:
            messagebox.showerror("Error", "Invalid username or password.")

    tk.Button(login_win, text="Login", command=submit_login, bg="#3498db", fg="white", font=("Helvetica", 12, "bold")).pack(pady=20)

def admin_dashboard():
    win = tk.Toplevel()
    win.title("Admin Dashboard")
    win.geometry("600x500")
    win.configure(bg="#ecf0f1")

    tk.Label(win, text="Admin Dashboard", font=("Helvetica", 16, "bold"), bg="#ecf0f1").pack(pady=20)

    tk.Button(win, text="View Books", command=view_books_admin, font=("Helvetica", 12), bg="#2980b9", fg="white").pack(pady=5)
    tk.Button(win, text="Add Book", command=add_book, font=("Helvetica", 12), bg="#2980b9", fg="white").pack(pady=5)
    tk.Button(win, text="Update Book", command=update_book, font=("Helvetica", 12), bg="#2980b9", fg="white").pack(pady=5)
    tk.Button(win, text="Delete Book", command=delete_book, font=("Helvetica", 12), bg="#2980b9", fg="white").pack(pady=5)
    tk.Button(win, text="View Issued Books", command=view_issued_books_admin, font=("Helvetica", 12), bg="#2980b9", fg="white").pack(pady=5)
    tk.Button(win, text="Logout", command=win.destroy, font=("Helvetica", 12), bg="#f1c40f", fg="black").pack(pady=15)
    tk.Button(win, text="Exit", command=root.quit, font=("Helvetica", 12), bg="#e74c3c", fg="white").pack(pady=5)

def student_dashboard(username):
    win = tk.Toplevel()
    win.title("Student Dashboard")
    win.geometry("600x500")
    win.configure(bg="#ecf0f1")

    tk.Label(win, text="Student Dashboard", font=("Helvetica", 16, "bold"), bg="#ecf0f1").pack(pady=20)

    tk.Button(win, text="View Available Books", command=view_books_student, font=("Helvetica", 12), bg="#27ae60", fg="white").pack(pady=5)
    tk.Button(win, text="Search Books", command=search_books_student, font=("Helvetica", 12), bg="#27ae60", fg="white").pack(pady=5)
    tk.Button(win, text="View Issued Books", command=lambda: view_issued_books_student(username), font=("Helvetica", 12), bg="#27ae60", fg="white").pack(pady=5)
    tk.Button(win, text="View Fine", command=lambda: view_fine_student(username), font=("Helvetica", 12), bg="#27ae60", fg="white").pack(pady=5)
    tk.Button(win, text="Reserve Book", command=reserve_book_student, font=("Helvetica", 12), bg="#27ae60", fg="white").pack(pady=5)
    tk.Button(win, text="Logout", command=win.destroy, font=("Helvetica", 12), bg="#f1c40f", fg="black").pack(pady=15)
    tk.Button(win, text="Exit", command=root.quit, font=("Helvetica", 12), bg="#e74c3c", fg="white").pack(pady=5)

# ===================== MAIN WINDOW =====================
root = tk.Tk()
root.title("Library Management System")
root.geometry("600x450")
root.configure(bg="#ecf0f1")

tk.Label(root, text="Library Management System", font=("Helvetica", 18, "bold"), bg="#ecf0f1").pack(pady=40)

tk.Button(root, text="Login", command=login, font=("Helvetica", 14), bg="#3498db", fg="white", width=20).pack(pady=10)
tk.Button(root, text="Register", command=register, font=("Helvetica", 14), bg="#2ecc71", fg="white", width=20).pack(pady=10)
tk.Button(root, text="Exit", command=root.quit, font=("Helvetica", 14), bg="#e74c3c", fg="white", width=20).pack(pady=10)

root.mainloop()

-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS library;
USE library;

-- 1. Users Table (Admins and Students)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    role ENUM('admin', 'student') NOT NULL
);

-- 2. Books Table
CREATE TABLE IF NOT EXISTS books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255),
    quantity INT DEFAULT 0,
    available BOOLEAN DEFAULT TRUE,  -- Tracks availability (default TRUE)
    category VARCHAR(100)
);

-- 3. Issued Books Table (using student_username instead of student_id)
CREATE TABLE IF NOT EXISTS issued_books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    student_username VARCHAR(50),  -- Now matches 'username' in users table
    issue_date DATE,
    due_date DATE,
    return_date DATE DEFAULT NULL,
    fine DECIMAL(6,2) DEFAULT 0,

    FOREIGN KEY (book_id) REFERENCES books(id)
        ON DELETE CASCADE,
    FOREIGN KEY (student_username) REFERENCES users(username)
        ON DELETE CASCADE
);

-- Insert admin user
INSERT INTO users (username, password, email, role)
VALUES ('admin', 'admin123', 'admin@example.com', 'admin');

-- Insert a student user
INSERT INTO users (username, password, email, role)
VALUES ('student1', 'studpass', 'student1@example.com', 'student');

-- Insert books
INSERT INTO books (title, author, quantity, available, category) VALUES
('To Kill a Mockingbird', 'Harper Lee', 5, TRUE, 'Fiction'),
('1984', 'George Orwell', 4, TRUE, 'Dystopian'),
('The Great Gatsby', 'F. Scott Fitzgerald', 3, TRUE, 'Classic'),
('The Catcher in the Rye', 'J.D. Salinger', 4, TRUE, 'Fiction'),
('Pride and Prejudice', 'Jane Austen', 6, TRUE, 'Romance'),
('The Hobbit', 'J.R.R. Tolkien', 5, TRUE, 'Fantasy'),
('Harry Potter and the Sorcerer\'s Stone', 'J.K. Rowling', 7, TRUE, 'Fantasy'),
('The Alchemist', 'Paulo Coelho', 6, TRUE, 'Philosophical'),
('The Da Vinci Code', 'Dan Brown', 5, TRUE, 'Thriller'),
('A Brief History of Time', 'Stephen Hawking', 2, TRUE, 'Science');

-- Check the books table
SELECT * FROM books;
SELECT * FROM users;
 check this too and if any error will occurs or not