RelationalDesign: schema_creation.sql

File schema_creation.sql, 9.3 KB (added by 201178, 2 weeks ago)
Line 
1DROP SCHEMA IF EXISTS project CASCADE;
2CREATE SCHEMA project;
3
4SET search_path TO project;
5
6-- =====================================================
7-- 1. ROLE
8-- =====================================================
9CREATE TABLE role (
10 role_id SERIAL PRIMARY KEY,
11 role_name VARCHAR(50) NOT NULL,
12 description TEXT,
13 CONSTRAINT unique_role_name UNIQUE (role_name)
14);
15
16-- =====================================================
17-- 2. APP_USER
18-- =====================================================
19CREATE TABLE app_user (
20 user_id SERIAL PRIMARY KEY,
21 role_id INTEGER NOT NULL,
22 username VARCHAR(50),
23 password_hash TEXT NOT NULL,
24 first_name VARCHAR(50),
25 last_name VARCHAR(50),
26 email VARCHAR(100),
27 active BOOLEAN DEFAULT TRUE,
28 CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES role(role_id),
29 CONSTRAINT app_user_username_key UNIQUE (username)
30);
31
32-- =====================================================
33-- 3. SESSION
34-- =====================================================
35CREATE TABLE session (
36 token UUID PRIMARY KEY DEFAULT gen_random_uuid(),
37 user_id INTEGER NOT NULL,
38 created_at TIMESTAMP NOT NULL DEFAULT now(),
39 expires_at TIMESTAMP NOT NULL DEFAULT (now() + '12:00:00'::interval),
40 CONSTRAINT session_user_id_fkey FOREIGN KEY (user_id) REFERENCES app_user(user_id)
41);
42
43-- =====================================================
44-- 4. AUDIT_LOG
45-- =====================================================
46CREATE TABLE audit_log (
47 log_id SERIAL PRIMARY KEY,
48 user_id INTEGER NOT NULL,
49 action TEXT NOT NULL,
50 entity_name VARCHAR(50),
51 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
52 CONSTRAINT audit_log_user_id_fkey FOREIGN KEY (user_id) REFERENCES app_user(user_id)
53);
54
55-- =====================================================
56-- 5. SHIFT
57-- =====================================================
58CREATE TABLE shift (
59 shift_id SERIAL PRIMARY KEY,
60 user_id INTEGER NOT NULL,
61 start_time TIMESTAMP NOT NULL,
62 end_time TIMESTAMP,
63 CONSTRAINT shift_user_id_fkey FOREIGN KEY (user_id) REFERENCES app_user(user_id)
64);
65
66-- =====================================================
67-- 6. SHIFT_CLOSE
68-- =====================================================
69CREATE TABLE shift_close (
70 shift_id SERIAL PRIMARY KEY,
71 closed_at TIMESTAMP NOT NULL DEFAULT now(),
72 closed_by INTEGER,
73 total NUMERIC NOT NULL,
74 order_count INTEGER NOT NULL,
75 CONSTRAINT shift_close_closed_by_fkey FOREIGN KEY (closed_by) REFERENCES app_user(user_id)
76);
77
78-- =====================================================
79-- 7. CATEGORY
80-- =====================================================
81CREATE TABLE category (
82 category_id SERIAL PRIMARY KEY,
83 name VARCHAR(100) NOT NULL,
84 description TEXT,
85 active BOOLEAN DEFAULT TRUE
86);
87
88-- =====================================================
89-- 8. PRODUCT
90-- =====================================================
91CREATE TABLE product (
92 product_id SERIAL PRIMARY KEY,
93 category_id INTEGER NOT NULL,
94 name VARCHAR(100) NOT NULL,
95 description TEXT,
96 price NUMERIC NOT NULL,
97 active BOOLEAN DEFAULT TRUE,
98 min_stock INTEGER NOT NULL DEFAULT 0,
99 CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES category(category_id)
100);
101
102-- =====================================================
103-- 9. RECIPE
104-- =====================================================
105CREATE TABLE recipe (
106 recipe_id SERIAL PRIMARY KEY,
107 product_id INTEGER,
108 CONSTRAINT recipe_product_id_fkey FOREIGN KEY (product_id) REFERENCES product(product_id),
109 CONSTRAINT recipe_product_id_key UNIQUE (product_id)
110);
111
112-- =====================================================
113-- 10. RECIPE_ITEM
114-- =====================================================
115CREATE TABLE recipe_item (
116 recipe_id INTEGER NOT NULL,
117 ingredient_id INTEGER NOT NULL,
118 quantity_needed NUMERIC NOT NULL,
119 CONSTRAINT recipe_item_pkey PRIMARY KEY (recipe_id, ingredient_id),
120 CONSTRAINT recipe_item_recipe_id_fkey FOREIGN KEY (recipe_id) REFERENCES recipe(recipe_id),
121 CONSTRAINT recipe_item_ingredient_id_fkey FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id)
122);
123
124-- =====================================================
125-- 11. INGREDIENT
126-- =====================================================
127CREATE TABLE ingredient (
128 ingredient_id SERIAL PRIMARY KEY,
129 name VARCHAR(200) NOT NULL,
130 unit VARCHAR(20) NOT NULL,
131 min_stock NUMERIC NOT NULL DEFAULT 0,
132 active BOOLEAN NOT NULL DEFAULT TRUE
133);
134
135-- =====================================================
136-- 12. INGREDIENT_INVENTORY
137-- =====================================================
138CREATE TABLE ingredient_inventory (
139 id SERIAL PRIMARY KEY,
140 ingredient_id INTEGER NOT NULL,
141 quantity_change NUMERIC NOT NULL,
142 operation_type VARCHAR(40) NOT NULL,
143 created_at TIMESTAMP NOT NULL DEFAULT now(),
144 CONSTRAINT ingredient_inventory_ingredient_id_fkey FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id)
145);
146
147-- =====================================================
148-- 13. INVENTORY
149-- =====================================================
150CREATE TABLE inventory (
151 inventory_id SERIAL PRIMARY KEY,
152 product_id INTEGER NOT NULL,
153 quantity_change INTEGER NOT NULL,
154 operation_type VARCHAR(30),
155 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
156 CONSTRAINT inventory_product_id_fkey FOREIGN KEY (product_id) REFERENCES product(product_id)
157);
158
159-- =====================================================
160-- 14. RESTAURANT_TABLE
161-- =====================================================
162CREATE TABLE restaurant_table (
163 table_id SERIAL PRIMARY KEY,
164 table_number VARCHAR(20) NOT NULL,
165 capacity INTEGER NOT NULL,
166 status VARCHAR(20) NOT NULL,
167 CONSTRAINT restaurant_table_table_number_key UNIQUE (table_number)
168);
169
170-- =====================================================
171-- 15. ORDERS
172-- =====================================================
173CREATE TABLE orders (
174 order_id SERIAL PRIMARY KEY,
175 user_id INTEGER NOT NULL,
176 table_id INTEGER NOT NULL,
177 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
178 status VARCHAR(20) NOT NULL,
179 CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES app_user(user_id),
180 CONSTRAINT orders_table_id_fkey FOREIGN KEY (table_id) REFERENCES restaurant_table(table_id)
181);
182
183-- =====================================================
184-- 16. ORDER_ITEM
185-- =====================================================
186CREATE TABLE order_item (
187 order_id INTEGER NOT NULL,
188 item_number INTEGER NOT NULL,
189 product_id INTEGER NOT NULL,
190 quantity INTEGER NOT NULL,
191 unit_price NUMERIC NOT NULL,
192 status VARCHAR(20) NOT NULL DEFAULT 'ПОРАЧАНО',
193 CONSTRAINT order_item_pkey PRIMARY KEY (order_id, item_number),
194 CONSTRAINT order_item_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders(order_id),
195 CONSTRAINT order_item_product_id_fkey FOREIGN KEY (product_id) REFERENCES product(product_id)
196);
197
198-- =====================================================
199-- 17. PAYMENT
200-- =====================================================
201CREATE TABLE payment (
202 payment_id SERIAL PRIMARY KEY,
203 order_id INTEGER NOT NULL,
204 amount NUMERIC NOT NULL,
205 method VARCHAR(20) NOT NULL,
206 payment_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
207 CONSTRAINT payment_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders(order_id),
208 CONSTRAINT payment_order_id_key UNIQUE (order_id)
209);
210
211-- =====================================================
212-- 18. INVOICE
213-- =====================================================
214CREATE TABLE invoice (
215 invoice_id SERIAL PRIMARY KEY,
216 payment_id INTEGER NOT NULL,
217 invoice_number VARCHAR(50) NOT NULL,
218 issued_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
219 CONSTRAINT invoice_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES payment(payment_id),
220 CONSTRAINT invoice_payment_id_key UNIQUE (payment_id),
221 CONSTRAINT invoice_invoice_number_key UNIQUE (invoice_number)
222);
223
224-- =====================================================
225-- 19. APP_SETTINGS
226-- =====================================================
227CREATE TABLE app_settings (
228 id INTEGER PRIMARY KEY DEFAULT 1,
229 restaurant_name VARCHAR(100) NOT NULL DEFAULT 'Мој Ресторан',
230 vat_rate NUMERIC NOT NULL DEFAULT 18.00,
231 currency VARCHAR(10) NOT NULL DEFAULT 'ден',
232 printer_config VARCHAR(255) NOT NULL DEFAULT 'default'
233);
234
235-- =====================================================
236-- 20. SETTINGS
237-- =====================================================
238CREATE TABLE settings (
239 id INTEGER PRIMARY KEY DEFAULT 1,
240 restaurant_name VARCHAR(200) NOT NULL DEFAULT 'EMPORIO CAFFE',
241 vat_percent NUMERIC NOT NULL DEFAULT 18,
242 currency VARCHAR(10) NOT NULL DEFAULT 'ден',
243 printer_ip VARCHAR(64),
244 printer_port INTEGER NOT NULL DEFAULT 9100
245);