restaurant.sql

CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Staff (
    staff_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    role VARCHAR(50), -- e.g., Waiter, Chef, Manager
    phone VARCHAR(20),
    email VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'Active'
);

CREATE TABLE Tables (
    table_id SERIAL PRIMARY KEY,
    table_number INT UNIQUE NOT NULL,
    capacity INT,
    location VARCHAR(50), -- e.g., "Indoor", "Patio"
    status VARCHAR(20) DEFAULT 'Available' -- Available, Occupied, Reserved
);


CREATE TABLE MenuCategories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE MenuItems (
    item_id SERIAL PRIMARY KEY,
    category_id INT REFERENCES MenuCategories(category_id) ON DELETE SET NULL,
    item_name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    is_available BOOLEAN DEFAULT TRUE
);

CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES Customers(customer_id) ON DELETE SET NULL,
    staff_id INT REFERENCES Staff(staff_id) ON DELETE SET NULL,
    table_id INT REFERENCES Tables(table_id) ON DELETE SET NULL,
    order_type VARCHAR(20) NOT NULL, -- Dine-in, Takeaway, Delivery
    order_status VARCHAR(20) DEFAULT 'Pending', -- Pending, Preparing, Served, Completed, Cancelled
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) DEFAULT 0,
    notes TEXT
);

CREATE TABLE OrderItems (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES Orders(order_id) ON DELETE CASCADE,
    item_id INT REFERENCES MenuItems(item_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * price) STORED
);

CREATE TABLE Payments (
    payment_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES Orders(order_id) ON DELETE CASCADE,
    payment_method VARCHAR(50), -- Cash, Card, Mobile Payment
    payment_status VARCHAR(20) DEFAULT 'Unpaid', -- Unpaid, Paid, Refunded
    amount DECIMAL(10,2) NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE InventoryItems (
    inventory_id SERIAL PRIMARY KEY,
    item_name VARCHAR(150) NOT NULL,
    category VARCHAR(50), -- e.g., Meat, Vegetables, Beverages
    quantity DECIMAL(10,2) DEFAULT 0,
    unit VARCHAR(20), -- kg, liters, pcs, etc.
    reorder_level DECIMAL(10,2) DEFAULT 10,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE InventoryUsage (
    usage_id SERIAL PRIMARY KEY,
    inventory_id INT REFERENCES InventoryItems(inventory_id) ON DELETE CASCADE,
    menu_item_id INT REFERENCES MenuItems(item_id) ON DELETE CASCADE,
    quantity_used DECIMAL(10,2) NOT NULL
);

CREATE TABLE Reservations (
    reservation_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES Customers(customer_id) ON DELETE CASCADE,
    table_id INT REFERENCES Tables(table_id) ON DELETE SET NULL,
    reservation_date TIMESTAMP NOT NULL,
    number_of_guests INT,
    status VARCHAR(20) DEFAULT 'Booked', -- Booked, Completed, Cancelled
    notes TEXT
);

-- | Entity             | Description                                       |
-- | ------------------ | ------------------------------------------------- |
-- | **Customers**      | Information about guests placing orders.          |
-- | **Staff**          | Employees (waiters, chefs, managers, etc.).       |
-- | **Tables**         | Dine-in restaurant tables.                        |
-- | **MenuCategories** | Menu groupings (Appetizers, Drinks, Main Course). |
-- | **MenuItems**      | Individual food/drink items.                      |
-- | **Orders**         | Customer orders (dine-in, delivery, or takeaway). |
-- | **OrderItems**     | Details of items in each order.                   |
-- | **Payments**       | Payment and billing info.                         |
-- | **InventoryItems** | Stock of ingredients and supplies.                |
-- | **InventoryUsage** | Tracks ingredient usage for menu items.           |
-- | **Reservations**   | Table reservations.                               |


-- | Relationship                   | Type | Description                                     |
-- | ------------------------------ | ---- | ----------------------------------------------- |
-- | Customer → Orders              | 1:N  | One customer can place many orders.             |
-- | Staff → Orders                 | 1:N  | One staff member can handle multiple orders.    |
-- | Table → Orders                 | 1:N  | One table can have multiple orders over time.   |
-- | Order → OrderItems             | 1:N  | Each order has many ordered items.              |
-- | Order → Payment                | 1:1  | Each order has one payment record.              |
-- | MenuCategory → MenuItems       | 1:N  | Each category has multiple menu items.          |
-- | MenuItem → InventoryUsage      | 1:N  | Menu item uses multiple ingredients.            |
-- | InventoryItem → InventoryUsage | 1:N  | Each ingredient can be used by multiple dishes. |
-- | Customer → Reservations        | 1:N  | One customer can book many reservations.        |
-- | Table → Reservations           | 1:N  | One table can be reserved multiple times.       |