final_database_schema.sql

-- =====================================================
-- Dream Brew Coffee - Complete Fully Validated Database Schema
-- =====================================================

CREATE DATABASE IF NOT EXISTS dream_brew_coffee;
USE dream_brew_coffee;

-- =====================================================
-- 1. Users Table (Customers)
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- Storing plain text as per current app logic, but size allows hash
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    gender VARCHAR(10) NOT NULL,
    phone VARCHAR(11) NOT NULL, -- Enforcing 11 digit limit for "03..." validation
    avatar VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_phone_length CHECK (LENGTH(phone) = 11)
);

-- =====================================================
-- 2. Admins Table
-- =====================================================
CREATE TABLE IF NOT EXISTS admins (
    admin_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) DEFAULT 'Administrator',
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(11) DEFAULT '03000000000',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Seed Default Admin
INSERT IGNORE INTO admins (username, password, name, email, phone) 
VALUES ('admin', 'admin123', 'System Administrator', 'admin@dreambrew.com', '03001234567');

-- =====================================================
-- 3. Employees Table
-- =====================================================
CREATE TABLE IF NOT EXISTS employees (
    id VARCHAR(50) PRIMARY KEY, -- Custom ID like "CF-John-1234"
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(11) NOT NULL,
    address TEXT NOT NULL,
    category VARCHAR(50) NOT NULL, -- e.g., 'Server', 'Cook'
    blood_group VARCHAR(10),
    joining_date VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_emp_phone_length CHECK (LENGTH(phone) = 11)
);

-- =====================================================
-- 4. Categories Table
-- =====================================================
CREATE TABLE IF NOT EXISTS categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

INSERT IGNORE INTO categories (name, description) VALUES 
('Coffee', 'Freshly brewed coffee'),
('Desserts', 'Sweet treats'),
('Snacks', 'Light bites');

-- =====================================================
-- 5. Products Table
-- =====================================================
CREATE TABLE IF NOT EXISTS products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT,
    image VARCHAR(255), -- Renamed from image_path to match Java 'image'
    available BOOLEAN DEFAULT TRUE, -- Renamed from is_available to match Java 'available'
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL
);

-- Sample Products
INSERT IGNORE INTO products (name, description, price, category_id, image, available) VALUES
('Espresso', 'Standard Espresso', 120.00, 1, 'src/CoffeeShopManagementSystem/Image/coffee1.jpeg', TRUE),
('Cappuccino', 'Cappuccino with foam', 150.00, 1, 'src/CoffeeShopManagementSystem/Image/coffee2.jpeg', TRUE),
('Latte', 'Creamy Latte', 160.00, 1, 'src/CoffeeShopManagementSystem/Image/coffee3.png', TRUE),
('Mocha', 'Chocolate flavored coffee', 170.00, 1, 'src/CoffeeShopManagementSystem/Image/mocha coffee.jpg', TRUE),
('Americano', 'Black coffee', 130.00, 1, 'src/CoffeeShopManagementSystem/Image/coffee4.png', TRUE),
('Chocolate Muffin', 'Freshly baked muffin', 80.00, 3, 'src/CoffeeShopManagementSystem/Image/coffee7.jpeg', TRUE),
('Blueberry Muffin', 'Muffin with blueberries', 85.00, 3, 'src/CoffeeShopManagementSystem/Image/coffee7.jpeg', TRUE),
('Chicken Sandwich', 'Grilled chicken sandwich', 180.00, 3, 'src/CoffeeShopManagementSystem/Image/coffee8.jpg', TRUE),
('Veg Sandwich', 'Fresh vegetable sandwich', 150.00, 3, 'src/CoffeeShopManagementSystem/Image/coffee8.jpg', TRUE),
('Chocolate Cake', 'Rich dark chocolate cake', 450.00, 2, 'src/CoffeeShopManagementSystem/Image/coffee6.jpeg', TRUE),
('Club Sandwich', 'Chicken club sandwich', 550.00, 3, 'src/CoffeeShopManagementSystem/Image/coffee8.jpg', TRUE);

-- =====================================================
-- 6. Orders Table
-- =====================================================
CREATE TABLE IF NOT EXISTS orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    employee_id VARCHAR(50), -- Assigned Employee
    total_price DECIMAL(10, 2) NOT NULL,
    status ENUM('Pending', 'Processing', 'Preparing', 'Completed', 'Cancelled') DEFAULT 'Pending',
    address TEXT,
    payment_method VARCHAR(50) DEFAULT 'Cash on Delivery',
    delivery_type VARCHAR(50), 
    token_number INT, 
    timer_minutes INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL
);

-- =====================================================
-- 7. Order Items Table
-- =====================================================
CREATE TABLE IF NOT EXISTS order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL, -- Snapshot of name at time of order
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    image_path VARCHAR(255),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

-- =====================================================
-- 8. Feedback Table
-- =====================================================
CREATE TABLE IF NOT EXISTS feedback (
    feedback_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    message TEXT,
    admin_reply TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- =====================================================
-- 9a. Cart Table (Missing in previous version)
-- =====================================================
CREATE TABLE IF NOT EXISTS cart (
    cart_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

-- =====================================================
-- 9. FAQs Table
-- =====================================================
CREATE TABLE IF NOT EXISTS faqs (
    faq_id INT AUTO_INCREMENT PRIMARY KEY,
    question TEXT NOT NULL,
    answer TEXT NOT NULL
);

INSERT IGNORE INTO faqs (question, answer) VALUES
('How do I cancel my order?', 'You can request cancellation via the helpline or if the admin cancels it.'),
('What payment methods are accepted?', 'Currently we accept Cash on Delivery.');

-- =====================================================
-- 10. Banners (Optional for UI)
-- =====================================================
CREATE TABLE IF NOT EXISTS banners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    image_path VARCHAR(255) NOT NULL
);