database.sql

-- BookHaven Library Management System Database
-- Run this SQL script to create the database and tables

CREATE DATABASE IF NOT EXISTS bookhaven CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bookhaven;

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('USER', 'ADMIN') DEFAULT 'USER',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Books table
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(150) NOT NULL,
    category VARCHAR(100) NOT NULL,
    isbn VARCHAR(20) NULL,
    description TEXT,
    total_copies INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Reservations table
CREATE TABLE reservations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    status ENUM('ACTIVE', 'CANCELLED', 'REJECTED') DEFAULT 'ACTIVE',
    reason TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    UNIQUE KEY unique_active_reservation (user_id, book_id, status)
);

-- Book requests table
CREATE TABLE book_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(150) NOT NULL,
    isbn VARCHAR(20) NULL,
    reason TEXT NOT NULL,
    status ENUM('PENDING', 'APPROVED', 'REJECTED') DEFAULT 'PENDING',
    admin_reason TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Contact messages table
CREATE TABLE contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Membership inquiries table
CREATE TABLE membership_inquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default admin user
-- Email: admin@library.com
-- Password: password123
INSERT INTO users (name, email, password_hash, role) VALUES 
('Admin User', 'admin@library.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ADMIN');

-- Insert sample books for testing
INSERT INTO books (title, author, category, isbn, description, total_copies) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', '9780743273565', 'A classic American novel set in the Jazz Age.', 3),
('To Kill a Mockingbird', 'Harper Lee', 'Fiction', '9780061120084', 'A gripping tale of racial injustice and childhood.', 2),
('1984', 'George Orwell', 'Dystopian', '9780451524935', 'A dystopian social science fiction novel.', 4),
('Pride and Prejudice', 'Jane Austen', 'Romance', '9780141439518', 'A romantic novel of manners.', 2),
('The Catcher in the Rye', 'J.D. Salinger', 'Fiction', '9780316769488', 'A controversial novel about teenage rebellion.', 3),
('Harry Potter and the Sorcerer''s Stone', 'J.K. Rowling', 'Fantasy', '9780439708180', 'The first book in the Harry Potter series.', 5);

-- Insert sample reservations
INSERT INTO reservations (user_id, book_id, status) VALUES
(1, 1, 'ACTIVE'),
(1, 3, 'ACTIVE');