CREATE DATABASE restaurant_db;

DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS users;

CREATE TABLE users
(
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(64) NOT NULL,
    email VARCHAR(100)
);

CREATE TABLE menu_items
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price NUMERIC(10,2) NOT NULL CHECK(price > 0),
    category VARCHAR(50)
);

CREATE TABLE orders
(
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_price NUMERIC(10,2) NOT NULL CHECK(total_price >= 0),

    CONSTRAINT fk_orders_user
        FOREIGN KEY(user_id)
            REFERENCES users(id)
            ON DELETE CASCADE
);

CREATE TABLE order_details
(
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL,
    menu_item_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    price NUMERIC(10,2) NOT NULL CHECK(price > 0),

    CONSTRAINT fk_detail_order
        FOREIGN KEY(order_id)
            REFERENCES orders(id)
            ON DELETE CASCADE,

    CONSTRAINT fk_detail_menu
        FOREIGN KEY(menu_item_id)
            REFERENCES menu_items(id)
);

INSERT INTO menu_items(name,description,price,category)
VALUES
    ('Pizza Margherita','Classic pizza',10.00,'Pizza'),
    ('Cheese Burger','Beef burger',8.00,'Burger'),
    ('Chicken Pasta','Creamy pasta',12.00,'Pasta'),
    ('Cola','Cold drink',2.50,'Drink');