-- 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.

CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    password_hash VARCHAR(50) NOT NULL,
    email VARCHAR(150)
);

CREATE TABLE menu_items (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name_ VARCHAR(70) NOT NULL,
    description_ TEXT,
    price NUMERIC(7, 2) NOT NULL CHECK(price > 0),
    category VARCHAR(50)
);

CREATE TABLE orders (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    total_price NUMERIC(7, 2) NOT NULL DEFAULT 0
);

CREATE TABLE order_details (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id),
    menu_item_id INT NOT NULL REFERENCES menu_items(id),
    quantity SMALLINT NOT NULL CHECK(quantity > 0),
    unit_price NUMERIC(7, 2) NOT NULL CHECK(unit_price > 0)
);


INSERT INTO menu_items(name_, description_, price, category) VALUES ('Bacon Pizza', 'Medium', 15.99, 'Pizza');
INSERT INTO menu_items(name_, description_, price, category) VALUES ('Vegan Pizza', 'Small', 10.99, 'Pizza');
INSERT INTO menu_items(name_, description_, price, category) VALUES ('Coke', 'Mini Can', 2.99, 'Drink');


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