-- =========================================================
-- WS 10 - Restaurant Database Management System
-- database.sql
-- =========================================================

-- Drop tables if they already exist (useful when re-running the script)
DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS users;

-- =========================================================
-- 1. USERS  (Customer)
-- =========================================================
CREATE TABLE users (
                       id            BIGSERIAL NOT NULL PRIMARY KEY,
                       username      VARCHAR(50)  NOT NULL UNIQUE,
                       password_hash VARCHAR(255) NOT NULL,
                       email         VARCHAR(100)
);

-- =========================================================
-- 2. MENU_ITEMS
-- =========================================================
CREATE TABLE menu_items (
                            id          BIGSERIAL NOT NULL PRIMARY KEY,
                            name        VARCHAR(100)   NOT NULL,
                            description VARCHAR(255),
                            price       NUMERIC(10, 2) NOT NULL CHECK (price > 0),
                            category    VARCHAR(50)
);

-- =========================================================
-- 3. ORDERS
-- =========================================================
CREATE TABLE orders (
                        id          BIGSERIAL NOT NULL PRIMARY KEY,
                        user_id     BIGINT NOT NULL REFERENCES users (id),
                        created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        total_price NUMERIC(10, 2) NOT NULL CHECK (total_price >= 0)
);

-- =========================================================
-- 4. ORDER_DETAILS  (items inside an order)
-- =========================================================
CREATE TABLE order_details (
                               id           BIGSERIAL NOT NULL PRIMARY KEY,
                               order_id     BIGINT NOT NULL REFERENCES orders (id),
                               menu_item_id BIGINT NOT NULL REFERENCES menu_items (id),
                               quantity     INT NOT NULL CHECK (quantity > 0),
                               unit_price   NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0)
);

-- =========================================================
-- Initial mock data
-- =========================================================

-- Sample users
-- NOTE: these password_hash values are just placeholders.
-- Your Java app must hash real passwords before inserting (e.g. with BCrypt).
INSERT INTO users (username, password_hash, email) VALUES
                                                       ('john_doe', '$2a$10$placeholderHashValue1234567890', 'john@example.com'),
                                                       ('jane_smith', '$2a$10$placeholderHashValue1234567891', 'jane@example.com');

-- Sample menu items (at least 3 required)
INSERT INTO menu_items (name, description, price, category) VALUES
                                                                ('Pizza Margherita', 'Classic pizza with tomato, mozzarella and basil', 10.00, 'Main'),
                                                                ('Cheeseburger', 'Beef patty with cheddar cheese and house sauce', 8.00, 'Main'),
                                                                ('Pasta Carbonara', 'Pasta with egg, pancetta and parmesan', 12.00, 'Main'),
                                                                ('Tiramisu', 'Traditional Italian coffee-flavored dessert', 6.50, 'Dessert'),
                                                                ('Cola', 'Soft drink, 330ml can', 2.50, 'Drink');-- =========================================================
-- WS 10 - Restaurant Database Management System
-- database.sql
-- =========================================================

-- Drop tables if they already exist (useful when re-running the script)
DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS users;

-- =========================================================
-- 1. USERS  (Customer)
-- =========================================================
CREATE TABLE users (
                       id            BIGSERIAL NOT NULL PRIMARY KEY,
                       username      VARCHAR(50)  NOT NULL UNIQUE,
                       password_hash VARCHAR(255) NOT NULL,
                       email         VARCHAR(100)
);

-- =========================================================
-- 2. MENU_ITEMS
-- =========================================================
CREATE TABLE menu_items (
                            id          BIGSERIAL NOT NULL PRIMARY KEY,
                            name        VARCHAR(100)   NOT NULL,
                            description VARCHAR(255),
                            price       NUMERIC(10, 2) NOT NULL CHECK (price > 0),
                            category    VARCHAR(50)
);

-- =========================================================
-- 3. ORDERS
-- =========================================================
CREATE TABLE orders (
                        id          BIGSERIAL NOT NULL PRIMARY KEY,
                        user_id     BIGINT NOT NULL REFERENCES users (id),
                        created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        total_price NUMERIC(10, 2) NOT NULL CHECK (total_price >= 0)
);

-- =========================================================
-- 4. ORDER_DETAILS  (items inside an order)
-- =========================================================
CREATE TABLE order_details (
                               id           BIGSERIAL NOT NULL PRIMARY KEY,
                               order_id     BIGINT NOT NULL REFERENCES orders (id),
                               menu_item_id BIGINT NOT NULL REFERENCES menu_items (id),
                               quantity     INT NOT NULL CHECK (quantity > 0),
                               unit_price   NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0)
);

-- =========================================================
-- Initial mock data
-- =========================================================

-- Sample users
-- NOTE: these password_hash values are just placeholders.
-- Your Java app must hash real passwords before inserting (e.g. with BCrypt).
INSERT INTO users (username, password_hash, email) VALUES
                                                       ('john_doe', '$2a$10$placeholderHashValue1234567890', 'john@example.com'),
                                                       ('jane_smith', '$2a$10$placeholderHashValue1234567891', 'jane@example.com');

-- Sample menu items (at least 3 required)
INSERT INTO menu_items (name, description, price, category) VALUES
                                                                ('Pizza Margherita', 'Classic pizza with tomato, mozzarella and basil', 10.00, 'Main'),
                                                                ('Cheeseburger', 'Beef patty with cheddar cheese and house sauce', 8.00, 'Main'),
                                                                ('Pasta Carbonara', 'Pasta with egg, pancetta and parmesan', 12.00, 'Main'),
                                                                ('Tiramisu', 'Traditional Italian coffee-flavored dessert', 6.50, 'Dessert'),
                                                                ('Cola', 'Soft drink, 330ml can', 2.50, 'Drink');