CREATE TABLE users (
                       id          SERIAL PRIMARY KEY,
                       username    VARCHAR(50) UNIQUE NOT NULL,
                       password    VARCHAR(255) NOT NULL,   -- هش شده با SHA-256 + Base64
                       email       VARCHAR(100)
);

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)
);

CREATE TABLE orders (
                        id          SERIAL PRIMARY KEY,
                        user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                        created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        total_price DECIMAL(10,2) NOT NULL CHECK (total_price >= 0)
);

CREATE TABLE order_details (
                               id            SERIAL PRIMARY KEY,
                               order_id      INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
                               menu_item_id  INTEGER NOT NULL REFERENCES menu_items(id),
                               quantity      INTEGER NOT NULL CHECK (quantity > 0),
                               price         DECIMAL(10,2) NOT NULL CHECK (price >= 0)   -- قیمت در زمان خرید
);

INSERT INTO menu_items (name, description, price, category) VALUES
                                                                ('Pizza', 'Classic Margherita with mozzarella and basil', 10.00, 'Main'),
                                                                ('Burger', 'Juicy beef burger with lettuce and cheese', 8.00, 'Main'),
                                                                ('Pasta', 'Creamy Alfredo pasta with chicken', 12.00, 'Main'),
                                                                ('Salad', 'Fresh garden salad with vinaigrette', 6.50, 'Appetizer'),
                                                                ('Soda', 'Carbonated soft drink', 2.50, 'Beverage');

INSERT INTO users (username, password, email) VALUES
    ('john_doe', 'XohImNooBHFR0OVvjcYpJ3NgPQ1qq73WKhHvch0VQtg=', 'john@example.com');

INSERT INTO orders (user_id, total_price) VALUES
    ((SELECT id FROM users WHERE username = 'john_doe'), 18.00);


INSERT INTO order_details (order_id, menu_item_id, quantity, price)
VALUES
    ((SELECT id FROM orders WHERE user_id = (SELECT id FROM users WHERE username = 'john_doe') ORDER BY id LIMIT 1),
    (SELECT id FROM menu_items WHERE name = 'Pizza'), 2, 10.00),
    ((SELECT id FROM orders WHERE user_id = (SELECT id FROM users WHERE username = 'john_doe') ORDER BY id LIMIT 1),
     (SELECT id FROM menu_items WHERE name = 'Burger'), 1, 8.00);