-- Restaurant Database Management System
--
-- Instructions:
-- 1. Create all required tables.
-- 2. Design appropriate PRIMARY KEY and FOREIGN KEY relationships.
-- 3. Add suitable constraints based on the requirements.
-- 4. Insert initial mock data.
-- 5. Insert at least 3 menu items.
-- 6. The script should be executable from start to finish without errors.



-- =======================================================
-- USER TABLE
-- =======================================================
--
-- Represents customers using the system.
--
-- Required information:
-- - Unique identifier
-- - Username
-- - Password
-- - Email (optional)
--
-- Requirements:
-- - Each user must have a unique identifier.
-- - Usernames must be unique.
-- - Username and password are required.
-- - Passwords should not be stored in plain text.
--
-- CREATE TABLE ...



-- =======================================================
-- MENU ITEM TABLE
-- =======================================================
--
-- Represents available food and drink items.
--
-- Required information:
-- - Unique identifier
-- - Name
-- - Description (optional)
-- - Price
-- - Category (optional)
--
-- Requirements:
-- - Each menu item must have a unique identifier.
-- - Name is required.
-- - Price must always be positive.
--
-- CREATE TABLE ...



-- =======================================================
-- ORDER TABLE
-- =======================================================
--
-- Represents orders placed by customers.
--
-- Required information:
-- - Unique identifier
-- - Reference to customer
-- - Creation date and time
-- - Total price
--
-- Requirements:
-- - Each order must belong to exactly one user.
-- - A user can have multiple orders.
-- - The relationship between User and Order must be implemented.
--
-- Note:
-- Avoid using reserved SQL keywords as table names.
-- Consider using a name such as "orders" or "customer_orders".
--
-- CREATE TABLE ...



-- =======================================================
-- ORDER DETAIL TABLE
-- =======================================================
--
-- Represents items inside an order.
--
-- Required information:
-- - Unique identifier
-- - Reference to an order
-- - Reference to a menu item
-- - Quantity
-- - Item price at purchase time
--
-- Requirements:
-- - Each detail record must belong to one order.
-- - Each detail record must reference one menu item.
-- - Quantity must always be greater than zero.
-- - Store the item's price at the moment of purchase.
--
-- CREATE TABLE ...



-- =======================================================
-- INITIAL MENU DATA
-- =======================================================
--
-- Insert at least 3 food or drink items.
--
-- Example categories:
-- - Pizza
-- - Burger
-- - Pasta
-- - Drink
--
-- INSERT INTO ...



-- =======================================================
-- OPTIONAL TEST DATA
-- =======================================================
--
-- You may insert sample users and orders for testing.
-- This section is optional.
--
-- INSERT INTO ...



-- =======================================================
-- VERIFICATION QUERIES
-- =======================================================
--
-- Uncomment these queries to verify your database.
--
-- SELECT * FROM ...;
-- SELECT * FROM ...;
-- SELECT * FROM ...;
-- SELECT * FROM ...;


-- =======================================================
-- Restaurant Database Management System
-- PostgreSQL Schema
-- =======================================================

-- -------------------------------------------------------
-- OPTIONAL CLEANUP
-- Uncomment if you want to reset the database completely
-- -------------------------------------------------------
-- DROP TABLE IF EXISTS order_details CASCADE;
-- DROP TABLE IF EXISTS orders CASCADE;
-- DROP TABLE IF EXISTS menu_items CASCADE;
-- DROP TABLE IF EXISTS users CASCADE;

-- =======================================================
-- USER TABLE
-- =======================================================
-- Represents customers using the system.
-- Requirements:
-- - Unique identifier
-- - Unique username
-- - Required username and password
-- - Passwords should not be stored in plain text
CREATE TABLE users (
                       id SERIAL PRIMARY KEY,
                       username VARCHAR(100) NOT NULL UNIQUE,
                       password_hash VARCHAR(255) NOT NULL,
                       email VARCHAR(255)
);

-- =======================================================
-- MENU ITEM TABLE
-- =======================================================
-- Represents available food and drink items.
-- Requirements:
-- - Unique identifier
-- - Name required
-- - Price positive
CREATE TABLE menu_items (
                            id SERIAL PRIMARY KEY,
                            name VARCHAR(150) NOT NULL,
                            description TEXT,
                            price DOUBLE PRECISION NOT NULL CHECK (price > 0),
                            category VARCHAR(100)
);

-- =======================================================
-- ORDER TABLE
-- =======================================================
-- Represents orders placed by customers.
-- Requirements:
-- - Each order belongs to exactly one user
-- - A user can have multiple orders
-- - Use non-reserved table name: orders
CREATE TABLE orders (
                        id SERIAL PRIMARY KEY,
                        user_id INT NOT NULL,
                        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        total_price DOUBLE PRECISION NOT NULL DEFAULT 0 CHECK (total_price >= 0),
                        CONSTRAINT fk_orders_user
                            FOREIGN KEY (user_id)
                                REFERENCES users(id)
                                ON DELETE CASCADE
);

-- =======================================================
-- ORDER DETAIL TABLE
-- =======================================================
-- Represents items inside an order.
-- Requirements:
-- - Each detail belongs to one order
-- - Each detail references one menu item
-- - Quantity > 0
-- - Store item price at purchase time
CREATE TABLE order_details (
                               id SERIAL PRIMARY KEY,
                               order_id INT NOT NULL,
                               menu_item_id INT NOT NULL,
                               quantity INT NOT NULL CHECK (quantity > 0),
                               price DOUBLE PRECISION NOT NULL CHECK (price > 0),
                               CONSTRAINT fk_order_details_order
                                   FOREIGN KEY (order_id)
                                       REFERENCES orders(id)
                                       ON DELETE CASCADE,
                               CONSTRAINT fk_order_details_menu_item
                                   FOREIGN KEY (menu_item_id)
                                       REFERENCES menu_items(id)
);

-- =======================================================
-- INITIAL MENU DATA
-- =======================================================
-- Insert at least 3 food/drink items
INSERT INTO menu_items (name, description, price, category) VALUES
                                                                ('Margherita Pizza', 'Classic pizza with tomato sauce, mozzarella, and basil', 8.99, 'Pizza'),
                                                                ('Pepperoni Pizza', 'Pizza topped with pepperoni and cheese', 10.99, 'Pizza'),
                                                                ('Cheeseburger', 'Beef burger with cheddar cheese and lettuce', 7.50, 'Burger'),
                                                                ('Pasta Carbonara', 'Creamy pasta with bacon and parmesan', 11.50, 'Pasta'),
                                                                ('Coca Cola', 'Chilled soft drink', 1.50, 'Drink');

-- =======================================================
-- OPTIONAL TEST DATA
-- =======================================================
-- Sample user for testing (password is hashed placeholder)
INSERT INTO users (username, password_hash, email) VALUES
    ('testuser', '$2a$10$dummyhashforTestingOnly12345678901234567890', 'testuser@example.com');

-- Sample order for testing
INSERT INTO orders (user_id, created_at, total_price)
VALUES (1, CURRENT_TIMESTAMP, 0);

-- Sample order details for testing
INSERT INTO order_details (order_id, menu_item_id, quantity, price) VALUES
                                                                        (1, 1, 2, 8.99),
                                                                        (1, 5, 2, 1.50);

-- Update total price for the sample order
UPDATE orders
SET total_price = (
    SELECT SUM(quantity * price)
    FROM order_details
    WHERE order_id = 1
)
WHERE id = 1;

-- =======================================================
-- VERIFICATION QUERIES
-- =======================================================
SELECT * FROM users;
SELECT * FROM menu_items;
SELECT * FROM orders;
SELECT * FROM order_details;