50 lines
2.8 KiB
SQL
50 lines
2.8 KiB
SQL
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); |