-- Restaurant Database Management System
--
-- This script creates the whole schema from scratch and inserts some
-- initial data. It can be run from start to finish without errors.
--
-- Run with:  psql -U postgres -d restaurant_db -f database.sql


-- Drop old tables first so the script is re-runnable.
-- Order matters because of the foreign keys (drop children before parents).
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.
-- Passwords are stored as a hash (never plain text) by the Java app.
CREATE TABLE users (
    id       SERIAL       PRIMARY KEY,
    username VARCHAR(50)  NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,          -- SHA-256 hex hash
    email    VARCHAR(255)                    -- optional
);


-- =======================================================
-- MENU ITEM TABLE
-- =======================================================
-- Represents available food and drink items.
CREATE TABLE menu_items (
    id          SERIAL         PRIMARY KEY,
    name        VARCHAR(100)   NOT NULL,
    description TEXT,                                 -- optional
    price       NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    category    VARCHAR(50)                           -- optional
);


-- =======================================================
-- ORDER TABLE  ("order" is a reserved word, so we use "orders")
-- =======================================================
-- Each order belongs to exactly one user (One-to-Many: user -> orders).
CREATE TABLE orders (
    id          SERIAL         PRIMARY KEY,
    user_id     INT            NOT NULL REFERENCES users(id),
    created_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_price NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (total_price >= 0)
);


-- =======================================================
-- ORDER DETAIL TABLE
-- =======================================================
-- One line inside an order (One-to-Many: order -> order_details).
-- "price" is the item price at the moment of purchase, so history stays
-- correct even if the menu price changes later.
CREATE TABLE order_details (
    id           SERIAL         PRIMARY KEY,
    order_id     INT            NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    menu_item_id INT            NOT NULL REFERENCES menu_items(id),
    quantity     INT            NOT NULL CHECK (quantity > 0),
    price        NUMERIC(10, 2) NOT NULL CHECK (price > 0)
);


-- =======================================================
-- INITIAL MENU DATA (at least 3 items)
-- =======================================================
INSERT INTO menu_items (name, description, price, category) VALUES
    ('Margherita Pizza', 'Classic pizza with tomato, mozzarella and basil', 10.00, 'Pizza'),
    ('Cheeseburger',     'Beef patty with cheddar, lettuce and tomato',     8.00,  'Burger'),
    ('Pasta Bolognese',  'Spaghetti with rich beef and tomato sauce',       12.00, 'Pasta'),
    ('Caesar Salad',     'Romaine, croutons, parmesan and Caesar dressing', 6.50,  'Salad'),
    ('Coca-Cola',        'Chilled 330ml can',                               2.50,  'Drink');


-- =======================================================
-- OPTIONAL TEST DATA
-- =======================================================
-- Sample user. The password below is the SHA-256 hash of the text "1234"
-- so you can log in with username "admin" / password "1234" for testing.
INSERT INTO users (username, password, email) VALUES
    ('admin', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'admin@pizzeria.com');


-- =======================================================
-- VERIFICATION QUERIES (optional, uncomment to check)
-- =======================================================
-- SELECT * FROM users;
-- SELECT * FROM menu_items;
-- SELECT * FROM orders;
-- SELECT * FROM order_details;