-- Restaurant Database Management System
-- Workshop 10 - Database Schema & Initial Data

-- پاک کردن جدول‌های قدیمی در صورت وجود (به ترتیب برعکس وابستگی برای جلوگیری از ارور Foreign Key)
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
-- =======================================================
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL, -- ذخیره به صورت هش شده مطابق نیازمندی
    email VARCHAR(100)
);

-- =======================================================
-- MENU ITEM TABLE
-- =======================================================
CREATE TABLE 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
-- =======================================================
-- از نام جایگزین orders استفاده شده چون order کلمه کلیدی رزرو شده SQL است.
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- =======================================================
-- ORDER DETAIL TABLE
-- =======================================================
CREATE TABLE order_details (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    menu_item_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0), -- قید بزرگتر از صفر بودن تعداد
    unit_price DECIMAL(10, 2) NOT NULL, -- ذخیره قیمت زمان خرید (اگر بعداً قیمت منو عوض شد، فاکتورهای قبلی خراب نشوند)
    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
('Pizza', 'Delicious Pepperoni Pizza with mozzarella cheese', 10.00, 'Food'),
('Burger', 'Juicy Beef Burger with cheddar cheese and fries', 8.00, 'Food'),
('Pasta', 'Creamy Alfredo Pasta with grilled chicken', 12.00, 'Food'),
('Soda', 'Cold refreshing soft drink', 2.00, 'Drink');

-- =======================================================
-- VERIFICATION QUERIES (کدهای تایید صحت ساختار)
-- =======================================================
SELECT * FROM menu_items;