DatabaseCreation: ddl (2).sql

File ddl (2).sql, 19.1 KB (added by 231169, 4 days ago)
Line 
1-- 1. ОСНОВНИ ШИФРАРНИЦИ (Табели без надворешни зависности)
2CREATE TABLE ROLES (
3 role_id BIGSERIAL PRIMARY KEY,
4 role_name varchar(255) NOT NULL UNIQUE,
5 description varchar(255)
6);
7
8CREATE TABLE CATEGORIES (
9 category_id BIGSERIAL PRIMARY KEY,
10 category_name varchar(255) NOT NULL,
11 parent_category_id int8,
12 CONSTRAINT PARENT_OF FOREIGN KEY (parent_category_id) REFERENCES CATEGORIES (category_id) ON UPDATE Cascade ON DELETE Restrict
13);
14
15CREATE TABLE PRODUCT_ATTRIBUTES (
16 attribute_id BIGSERIAL PRIMARY KEY,
17 attribute_name varchar(255) NOT NULL
18);
19
20CREATE TABLE SHIPPING_METHODS (
21 method_id BIGSERIAL PRIMARY KEY,
22 method_name varchar(255) NOT NULL,
23 cost numeric(12, 2) NOT NULL CHECK (cost >= 0),
24 estimated_days int4 NOT NULL CHECK (estimated_days >= 0)
25);
26
27CREATE TABLE COUPONS (
28 coupon_id BIGSERIAL PRIMARY KEY,
29 code varchar(255) NOT NULL UNIQUE,
30 discount_value numeric(12, 2) NOT NULL CHECK (discount_value > 0),
31 discount_type varchar(255) NOT NULL,
32 valid_from timestamp with time zone NOT NULL,
33 valid_to timestamp with time zone NOT NULL,
34 min_order_value numeric(10, 2),
35 CONSTRAINT chk_coupons_valid_dates
36CHECK (valid_to >= valid_from)
37);
38
39CREATE TABLE MANUFACTURERS (
40 manufacturer_id BIGSERIAL PRIMARY KEY,
41 name varchar(100) NOT NULL,
42 contact_info text
43);
44
45-- 2. КОРИСНИЧКИ МОДУЛ
46CREATE TABLE USERS (
47 user_id BIGSERIAL PRIMARY KEY,
48 email varchar(255) NOT NULL UNIQUE CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
49 password_hash varchar(255) NOT NULL,
50 status varchar(255) NOT NULL,
51 created_at timestamp with time zone NOT NULL,
52 CONSTRAINT chk_users_status
53 CHECK (status IN (
54 'ACTIVE',
55 'INACTIVE',
56 'SUSPENDED',
57 'DELETED'
58 ))
59);
60
61CREATE TABLE USER_PROFILES (
62 profile_id BIGSERIAL PRIMARY KEY,
63 user_id int8 NOT NULL UNIQUE,
64 first_name varchar(255) NOT NULL,
65 last_name varchar(255) NOT NULL,
66 phone_number varchar(255) CHECK (phone_number IS NULL OR phone_number ~ '^\+?[0-9]{7,15}$'),
67 profile_picture_url varchar(255),
68 CONSTRAINT HAS_PROFILE FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
69);
70
71CREATE TABLE ADDRESSES (
72 address_id BIGSERIAL PRIMARY KEY,
73 user_id int8 NOT NULL,
74 address_type varchar(255),
75 street_address varchar(255) NOT NULL,
76 city varchar(255) NOT NULL,
77 country varchar(255) NOT NULL,
78 is_default bool NOT NULL,
79 CONSTRAINT HAS_ADDRESS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
80);
81
82CREATE TABLE USER_ROLES (
83 user_id int8 NOT NULL,
84 role_id int8 NOT NULL,
85 assigned_at date NOT NULL,
86 PRIMARY KEY (user_id, role_id),
87 CONSTRAINT HAS_ROLE_ASSIGNMENT FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade,
88 CONSTRAINT IS_ASSIGNED_TO_USER FOREIGN KEY (role_id) REFERENCES ROLES (role_id) ON UPDATE Cascade ON DELETE Restrict
89);
90
91-- 3. КАТАЛОГ И ИНВЕНТАР
92CREATE TABLE PRODUCTS (
93 product_id BIGSERIAL PRIMARY KEY,
94 category_id int8 NOT NULL,
95 manufacturer_id int8 NOT NULL,
96 name varchar(255) NOT NULL,
97 brand varchar(255),
98 description text,
99 CONSTRAINT CONTAINS_PRODUCT FOREIGN KEY (category_id) REFERENCES CATEGORIES (category_id) ON UPDATE Cascade ON DELETE Restrict,
100 CONSTRAINT PRODUCED_BY FOREIGN KEY (manufacturer_id) REFERENCES MANUFACTURERS (manufacturer_id) ON UPDATE Cascade ON DELETE Restrict
101);
102
103CREATE TABLE PRODUCT_VARIANTS (
104 variant_id BIGSERIAL PRIMARY KEY,
105 product_id int8 NOT NULL,
106 manufacturer_id int8 NOT NULL,
107 sku varchar(100) NOT NULL UNIQUE,
108 product_variant_picture_url varchar(255),
109 price numeric(12, 2) NOT NULL CHECK (price >= 0),
110 stock_total int4 NOT NULL CHECK (stock_total >= 0),
111 CONSTRAINT HAS_VARIANT FOREIGN KEY (product_id) REFERENCES PRODUCTS (product_id) ON UPDATE Cascade ON DELETE Restrict,
112 CONSTRAINT V_PRODUCED_BY FOREIGN KEY (manufacturer_id) REFERENCES MANUFACTURERS (manufacturer_id) ON UPDATE Cascade ON DELETE Restrict
113);
114
115CREATE TABLE WAREHOUSES (
116 warehouse_id BIGSERIAL PRIMARY KEY,
117 warehouse_name varchar(255) NOT NULL,
118 location varchar(255),
119 user_id int8 NOT NULL,
120 CONSTRAINT OWNED_BY_SELLER FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict
121);
122
123CREATE TABLE INVENTORY_ITEMS (
124 inventory_id BIGSERIAL PRIMARY KEY,
125 warehouse_id int8 NOT NULL,
126 variant_id int8 NOT NULL,
127 quantity int8 NOT NULL CHECK (quantity >= 0),
128 CONSTRAINT MANAGES_STOCK FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (warehouse_id) ON UPDATE Cascade ON DELETE Restrict,
129 CONSTRAINT STOCKED_AS FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict
130);
131
132CREATE TABLE PRODUCT_INSTANCES (
133 instance_id BIGSERIAL PRIMARY KEY,
134 variant_id int8 NOT NULL,
135 warehouse_id int8 NOT NULL,
136 serial_number varchar(255) NOT NULL UNIQUE,
137 status varchar(20) NOT NULL,
138 CONSTRAINT LOCATED_IN FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (warehouse_id) ON UPDATE Cascade ON DELETE Restrict,
139 CONSTRAINT HAS_INSTANCE FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict,
140 CONSTRAINT chk_product_instances_status
141 CHECK (status IN (
142 'AVAILABLE',
143 'RESERVED',
144 'SHIPPED',
145 'DELIVERED',
146 'RETURNED',
147 'DEFECTIVE'
148 ))
149);
150
151CREATE TABLE WARRANTIES (
152 warranty_id BIGSERIAL PRIMARY KEY,
153 instance_id int8 NOT NULL UNIQUE,
154 duration_months int8 NOT NULL,
155 terms_conditions text,
156 CONSTRAINT PROVIDES_WARANTY FOREIGN KEY (instance_id) REFERENCES PRODUCT_INSTANCES (instance_id) ON UPDATE Cascade ON DELETE Restrict,
157 CONSTRAINT chk_warranties_duration
158CHECK (duration_months > 0)
159);
160
161CREATE TABLE PRODUCT_ATTRIBUTE_VALUES (
162 variant_id int8 NOT NULL,
163 attribute_id int8 NOT NULL,
164 attr_value varchar(255) NOT NULL,
165 PRIMARY KEY (variant_id, attribute_id),
166 CONSTRAINT DEFINES_VALUE FOREIGN KEY (attribute_id) REFERENCES PRODUCT_ATTRIBUTES (attribute_id) ON UPDATE Cascade ON DELETE Restrict,
167 CONSTRAINT HAS_ATTRIBUTE_VALUES FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade
168);
169
170CREATE TABLE PRODUCT_PRICE_HISTORY (
171 history_id BIGSERIAL PRIMARY KEY,
172 variant_id int8 NOT NULL,
173 old_price numeric(12, 2) NOT NULL CHECK (old_price >= 0),
174 new_price numeric(12, 2) NOT NULL CHECK (new_price >= 0),
175 change_date timestamp with time zone NOT NULL,
176 CONSTRAINT HAS_PRICE_HISTORY FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict
177);
178
179-- 4. ПРОДАЖБА И ТРАНСАКЦИИ (Виртуелна кошничка логика)
180CREATE TABLE ORDERS (
181 order_id BIGSERIAL PRIMARY KEY,
182 user_id int8 NOT NULL,
183 shipping_method_id int8,
184 order_date timestamp with time zone,
185 total_amount numeric(12, 2) CHECK (total_amount >= 0),
186 status varchar(255) NOT NULL,
187 CONSTRAINT USED_FOR_ORDER FOREIGN KEY (shipping_method_id) REFERENCES SHIPPING_METHODS (method_id) ON UPDATE Cascade ON DELETE Restrict,
188 CONSTRAINT PLACES_ORDER FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict,
189 CONSTRAINT chk_orders_status
190 CHECK (status IN (
191 'CART',
192 'PLACED',
193 'PAID',
194 'SHIPPED',
195 'COMPLETED',
196 'CANCELLED'
197 )),
198
199 CONSTRAINT chk_orders_cart_logic
200CHECK (
201 (status = 'CART' AND order_date IS NULL AND total_amount IS NULL AND shipping_method_id IS NULL)
202 OR
203 (status <> 'CART' AND order_date IS NOT NULL AND total_amount IS NOT NULL and shipping_method_id IS NOT NULL)
204 )
205);
206
207CREATE TABLE ORDER_ITEMS (
208 order_item_id BIGSERIAL PRIMARY KEY,
209 order_id int8 NOT NULL,
210 price_history_id int8 NOT NULL,
211 variant_id int8 NOT NULL,
212 quantity int4 NOT NULL CHECK (quantity > 0),
213 unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
214 CONSTRAINT CONTAINS_ORDER_ITEM FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Cascade,
215 CONSTRAINT IS_ORDERED_AS_ITEM FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict,
216 CONSTRAINT DEFINES_ORDER_ITEM_PRICE FOREIGN KEY (price_history_id) REFERENCES PRODUCT_PRICE_HISTORY (history_id) ON UPDATE Cascade ON DELETE Restrict
217);
218
219CREATE TABLE PAYMENTS (
220 payment_id BIGSERIAL PRIMARY KEY,
221 order_id int8 NOT NULL,
222 payment_method varchar(255) NOT NULL,
223 amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
224 payment_status varchar(255) NOT NULL,
225 transaction_id varchar(255) NOT NULL UNIQUE,
226 payment_date timestamp with time zone NOT NULL,
227 CONSTRAINT HAS_PAYMENT_ATTEMPT FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
228 CONSTRAINT chk_payments_status
229 CHECK (payment_status IN (
230 'PENDING',
231 'SUCCESS',
232 'FAILED',
233 'REFUNDED'
234 ))
235);
236
237CREATE TABLE SHIPMENTS (
238 shipment_id BIGSERIAL PRIMARY KEY,
239 order_id int8 NOT NULL,
240 address_id int8 NOT NULL,
241 tracking_number varchar(255) UNIQUE,
242 shipped_date timestamp with time zone ,
243 estimated_arrival date ,
244 delivered_date timestamp with time zone,
245 courier_id int8 NOT NULL,
246 status VARCHAR(20) NOT NULL,
247 CONSTRAINT chk_shipments_status
248 CHECK (status IN (
249 'PENDING',
250 'PROCESSING',
251 'SHIPPED',
252 'IN_TRANSIT',
253 'DELIVERED',
254 'FAILED',
255 'RETURNED'
256 )),
257 CONSTRAINT chk_shipments_estimated_after_shipped
258 CHECK (
259 estimated_arrival IS NULL
260 OR shipped_date IS NULL
261 OR estimated_arrival >= shipped_date::date
262 ),
263 CONSTRAINT GENERATES_SHIPMENTS FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
264 CONSTRAINT IS_SHIPPED_TO FOREIGN KEY (address_id) REFERENCES ADDRESSES (address_id) ON UPDATE Cascade ON DELETE Restrict,
265 CONSTRAINT RESPONSIBLE_FOR_SHIPMENT FOREIGN KEY (courier_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict,
266 CONSTRAINT chk_shipments_dates_tracking_by_status
267CHECK (
268 (
269 status IN ('PENDING', 'PROCESSING')
270 AND shipped_date IS NULL
271 AND estimated_arrival IS NULL
272 AND delivered_date IS NULL
273 )
274 OR
275 (
276 status IN ('SHIPPED', 'IN_TRANSIT')
277 AND tracking_number IS NOT NULL
278 AND shipped_date IS NOT NULL
279 AND estimated_arrival IS NOT NULL
280 AND delivered_date IS NULL
281 )
282 OR
283 (
284 status = 'DELIVERED'
285 AND tracking_number IS NOT NULL
286 AND shipped_date IS NOT NULL
287 AND estimated_arrival IS NOT NULL
288 AND delivered_date IS NOT NULL
289 )
290 OR
291 (
292 status IN ('FAILED', 'RETURNED')
293 AND tracking_number IS NOT NULL
294 AND shipped_date IS NOT NULL
295 AND estimated_arrival IS NOT NULL
296 )
297)
298);
299
300CREATE TABLE SHIPMENT_ITEMS (
301 shipment_item_id BIGSERIAL PRIMARY KEY,
302 shipment_id int8 NOT NULL,
303 order_item_id int8 NOT NULL,
304 instance_id int8 NOT NULL,
305 quantity_shipped int4 NOT NULL DEFAULT 1,
306
307 CONSTRAINT chk_shipment_items_quantity
308 CHECK (quantity_shipped = 1),
309
310 CONSTRAINT CONTAINS_SHIPMENT_ITEM
311 FOREIGN KEY (shipment_id) REFERENCES SHIPMENTS (shipment_id)
312 ON UPDATE CASCADE ON DELETE CASCADE,
313
314 CONSTRAINT IS_FULFILLED_IN_SHIPMENT
315 FOREIGN KEY (order_item_id) REFERENCES ORDER_ITEMS (order_item_id)
316 ON UPDATE CASCADE ON DELETE RESTRICT,
317
318 CONSTRAINT IS_SHIPPED_AS
319 FOREIGN KEY (instance_id) REFERENCES PRODUCT_INSTANCES (instance_id)
320 ON UPDATE CASCADE ON DELETE RESTRICT,
321
322 CONSTRAINT uq_shipment_items_instance
323 UNIQUE (instance_id)
324);
325
326CREATE TABLE ORDER_STATUS_HISTORY (
327 history_id BIGSERIAL PRIMARY KEY,
328 order_id int8 NOT NULL,
329 old_status varchar(255) NOT NULL,
330 new_status varchar(255) NOT NULL,
331 change_date timestamp with time zone NOT NULL,
332 CONSTRAINT HAS_STATUS_CHANGE FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
333 CONSTRAINT chk_order_status_history_old_status
334 CHECK (old_status IN (
335 'CART',
336 'PLACED',
337 'PAID',
338 'SHIPPED',
339 'COMPLETED',
340 'CANCELLED'
341 )),
342 CONSTRAINT chk_order_status_history_new_status
343 CHECK (new_status IN (
344 'CART',
345 'PLACED',
346 'PAID',
347 'SHIPPED',
348 'COMPLETED',
349 'CANCELLED'
350 ))
351);
352
353-- 5. МАРКЕТИНГ И ИНТЕРАКЦИЈА
354CREATE TABLE REVIEWS (
355 review_id BIGSERIAL PRIMARY KEY,
356 user_id int8 NOT NULL,
357 product_id int8 NOT NULL,
358 rating int4 NOT NULL CHECK (rating >= 1 AND rating <= 5),
359 comment_text text,
360 created_at timestamp with time zone NOT NULL,
361 CONSTRAINT HAS_REVIEW FOREIGN KEY (product_id) REFERENCES PRODUCTS (product_id) ON UPDATE Cascade ON DELETE Restrict,
362 CONSTRAINT WRITES_REVIEW FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict
363);
364
365CREATE TABLE REVIEW_HELPFULNESS (
366 user_id int8 NOT NULL,
367 review_id int8 NOT NULL,
368 vote_type varchar(255) NOT NULL,
369 voted_at timestamp with time zone NOT NULL,
370 PRIMARY KEY (user_id, review_id),
371 CONSTRAINT MARKS_HELPFULL FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade,
372 CONSTRAINT RECIEVES_HELPFULL_VOTE FOREIGN KEY (review_id) REFERENCES REVIEWS (review_id) ON UPDATE Cascade ON DELETE Cascade,
373 CONSTRAINT chk_review_helpfulness_vote
374 CHECK (vote_type IN (
375 'HELPFUL',
376 'NOT_HELPFUL'
377 ))
378);
379
380CREATE TABLE LOYALTY_ACCOUNTS (
381 loyalty_account_id BIGSERIAL PRIMARY KEY,
382 user_id int8 NOT NULL UNIQUE,
383 current_balance int4 NOT NULL CHECK (current_balance >= 0),
384 last_updated timestamp with time zone NOT NULL,
385 CONSTRAINT HAS_LOYALTY_POINTS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
386);
387
388CREATE TABLE LOYALTY_HISTORY (
389 history_id BIGSERIAL PRIMARY KEY,
390 loyalty_account_id int8 NOT NULL,
391 order_id int8 NOT NULL,
392 payment_id int8 NOT NULL,
393 points_change int4 NOT NULL,
394 transaction_type varchar(50) NOT NULL,
395 description text,
396 created_at timestamp with time zone NOT NULL,
397 CONSTRAINT LOGS_BALANCE_CHANGE FOREIGN KEY (loyalty_account_id) REFERENCES LOYALTY_ACCOUNTS (loyalty_account_id) ON UPDATE Cascade ON DELETE Restrict,
398 CONSTRAINT REDEEMS_POINTS_ON_ORDER FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
399 CONSTRAINT EARNS_POINTS_BY_PAYMENT FOREIGN KEY (payment_id) REFERENCES PAYMENTS (payment_id) ON UPDATE Cascade ON DELETE Restrict,
400 CONSTRAINT chk_loyalty_transaction_type
401 CHECK (transaction_type IN (
402 'EARN',
403 'REDEEM',
404 'REFUND',
405 'ADJUSTMENT'
406 ))
407);
408
409CREATE TABLE WISHLISTS (
410 wishlist_id BIGSERIAL PRIMARY KEY,
411 user_id int8 NOT NULL UNIQUE,
412 created_at timestamp with time zone NOT NULL,
413 CONSTRAINT HAS_WISHLIST FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
414);
415
416CREATE TABLE WISHLIST_ITEMS (
417 wishlist_id int8 NOT NULL,
418 variant_id int8 NOT NULL,
419 added_at timestamp with time zone NOT NULL,
420 PRIMARY KEY (wishlist_id, variant_id),
421 CONSTRAINT CONTAINS_WISHLIST_ITEM FOREIGN KEY (wishlist_id) REFERENCES WISHLISTS (wishlist_id) ON UPDATE Cascade ON DELETE Cascade,
422 CONSTRAINT IS_IN_WISHLIST FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade
423);
424
425CREATE TABLE PRODUCT_WAITLISTS (
426 waitlist_id BIGSERIAL PRIMARY KEY,
427 user_id int8 NOT NULL,
428 variant_id int8 NOT NULL,
429 added_at timestamp with time zone NOT NULL,
430 status varchar(255) NOT NULL,
431 CONSTRAINT uq_product_waitlist_user_variant UNIQUE (user_id, variant_id),
432 CONSTRAINT JOINS_WAITLIST FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade,
433 CONSTRAINT HAS_WAITLIST_ENTRY FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade,
434 CONSTRAINT chk_product_waitlists_status
435 CHECK (status IN (
436 'ACTIVE',
437 'NOTIFIED',
438 'REMOVED',
439 'FULFILLED'
440 ))
441
442);
443
444CREATE TABLE ORDER_DISCOUNTS (
445 order_id int8 NOT NULL,
446 coupon_id int8 NOT NULL,
447 applied_at timestamp with time zone NOT NULL,
448 amount_saved numeric(12, 2) NOT NULL CHECK (amount_saved >= 0),
449 PRIMARY KEY (order_id, coupon_id),
450 CONSTRAINT APPLIES_COUPON FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
451 CONSTRAINT IS_APPLIED_IN_ORDER FOREIGN KEY (coupon_id) REFERENCES COUPONS (coupon_id) ON UPDATE Cascade ON DELETE Restrict
452);
453
454-- 6. ОСТАНАТИ МОДУЛИ
455CREATE TABLE AUTH_LOGS (
456 log_id BIGSERIAL PRIMARY KEY,
457 user_id int8 NOT NULL,
458 login_timestamp timestamp with time zone NOT NULL,
459 ip_address varchar(45),
460 device_info varchar(255),
461 status varchar(255) NOT NULL,
462 CONSTRAINT GENERATES_AUTH_LOGS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict,
463 CONSTRAINT chk_auth_logs_status
464 CHECK (status IN (
465 'SUCCESS',
466 'FAILED'
467 ))
468);
469
470CREATE TABLE USER_SESSIONS (
471 session_id BIGSERIAL PRIMARY KEY,
472 user_id int8 NOT NULL,
473 session_token varchar(255) NOT NULL UNIQUE,
474 login_time timestamp with time zone NOT NULL,
475 expiry_time timestamp with time zone NOT NULL,
476 CONSTRAINT HAS_SESSION FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict,
477 CONSTRAINT chk_user_sessions_expiry
478 CHECK (expiry_time > login_time)
479);
480
481CREATE TABLE USER_NOTIFICATIONS (
482 notification_id BIGSERIAL PRIMARY KEY,
483 user_id int8 NOT NULL,
484 type varchar(255) NOT NULL,
485 message varchar(255) NOT NULL,
486 sent_at timestamp with time zone NOT NULL,
487 CONSTRAINT RECIEVES_NOTIFICATIONS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
488);
489
490CREATE TABLE SUPPORT_TICKETS (
491 ticket_id BIGSERIAL PRIMARY KEY,
492 user_id int8,
493 subject varchar(255) NOT NULL,
494 message_body text NOT NULL,
495 status varchar(255) NOT NULL,
496 priority varchar(255) NOT NULL,
497 created_at timestamp with time zone NOT NULL,
498 resolved_at timestamp with time zone,
499
500 CONSTRAINT CREATES_TICKET FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE SET NULL,
501
502 CONSTRAINT chk_support_tickets_status
503 CHECK (status IN (
504 'OPEN',
505 'IN_PROGRESS',
506 'RESOLVED',
507 'CLOSED'
508 )),
509
510 CONSTRAINT chk_support_tickets_priority
511 CHECK (priority IN (
512 'LOW',
513 'MEDIUM',
514 'HIGH'
515 )),
516
517 CONSTRAINT chk_support_tickets_resolved_after_created
518 CHECK (
519 resolved_at IS NULL
520 OR resolved_at >= created_at
521 )
522);