-- =======================================================
-- USER TABLE
-- =======================================================
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(256) NOT NULL,
    email VARCHAR(100)
);

-- =======================================================
-- MENU ITEM TABLE
-- =======================================================
CREATE TABLE IF NOT EXISTS menu_items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    category VARCHAR(50)
);

-- =======================================================
-- ORDER TABLE
-- =======================================================
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_price DECIMAL(10, 2) NOT NULL CHECK (total_price >= 0),
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- =======================================================
-- ORDER DETAIL TABLE
-- =======================================================
CREATE TABLE IF NOT EXISTS 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 DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_menu_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE RESTRICT
);

-- =======================================================
-- INITIAL MENU DATA
-- =======================================================
INSERT INTO menu_items (name, description, price, category) VALUES
('Margherita Pizza', 'Classic tomato sauce, fresh mozzarella, and basil', 10.00, 'Pizza'),
('Cheeseburger', 'Beef patty, cheddar cheese, lettuce, tomato, and burger sauce', 8.00, 'Burger'),
('Fettuccine Alfredo', 'Rich and creamy parmesan sauce over pasta', 12.00, 'Pasta'),
('Coca Cola', 'Chilled carbonated soft drink', 2.50, 'Drink')
ON CONFLICT DO NOTHING;