Eventix/Faza2A: ddl.sql

File ddl.sql, 13.2 KB (added by 231070, 3 days ago)
Line 
1CREATE TABLE APP_USER (
2 id BIGSERIAL NOT NULL,
3 first_name VARCHAR(50) NOT NULL,
4 last_name VARCHAR(50) NOT NULL,
5 email VARCHAR(50) NOT NULL UNIQUE,
6 phone VARCHAR(20) NOT NULL,
7 created_at DATE NOT NULL,
8 user_password VARCHAR(255) NOT NULL,
9 PRIMARY KEY (id),
10 CONSTRAINT CHK_email_format
11 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
12 CONSTRAINT CHK_phone_number_format
13 CHECK (phone ~ '^[0-9]{7,15}$')
14);
15
16CREATE TABLE ROLES (
17 id BIGSERIAL NOT NULL,
18 role_name VARCHAR(20) NOT null unique,
19 PRIMARY KEY (id),
20 CONSTRAINT CHK_role_title
21 CHECK (role_name IN ('ADMINISTRATOR', 'USER'))
22);
23
24CREATE TABLE USER_ROLES (
25 APP_USERid BIGINT NOT NULL,
26 ROLESid BIGINT NOT NULL,
27 PRIMARY KEY (APP_USERid, ROLESid),
28 CONSTRAINT FK_user_roles_user
29 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
30 ON UPDATE CASCADE ON DELETE RESTRICT,
31 CONSTRAINT FK_user_roles_role
32 FOREIGN KEY (ROLESid) REFERENCES ROLES (id)
33 ON UPDATE CASCADE ON DELETE RESTRICT
34);
35
36CREATE TABLE CATEGORIZATION (
37 id BIGSERIAL NOT NULL,
38 category_name VARCHAR(20) NOT NULL,
39 PRIMARY KEY (id),
40 CONSTRAINT CHK_category_values
41 CHECK (category_name IN ('Uncategorized', 'Concerts', 'Theatre', 'Cinema', 'Festivals', 'Exhibitions', 'Seminars'))
42);
43
44CREATE TABLE SUBCATEGORY (
45 id BIGSERIAL NOT NULL,
46 subcategory_name VARCHAR(50) NOT NULL,
47 CATEGORIZATIONid BIGINT NOT NULL,
48 PRIMARY KEY (id),
49 CONSTRAINT FK_subcategory_categorization
50 FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
51 ON UPDATE CASCADE ON DELETE RESTRICT
52);
53
54CREATE TABLE USER_CATEGORY_SUBSCRIPTION (
55 APP_USERid BIGINT NOT NULL DEFAULT 1,
56 CATEGORIZATIONid BIGINT NOT NULL DEFAULT 1,
57 PRIMARY KEY (APP_USERid, CATEGORIZATIONid),
58 CONSTRAINT FK_user_category_sub_user
59 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
60 ON UPDATE CASCADE ON DELETE SET DEFAULT,
61 CONSTRAINT FK_user_category_sub_categorization
62 FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
63 ON UPDATE CASCADE ON DELETE SET DEFAULT
64);
65
66CREATE TABLE USER_SUBCATEGORY_SUBSCRIPTION (
67 APP_USERid BIGINT NOT NULL DEFAULT 1,
68 SUBCATEGORYid BIGINT NOT NULL DEFAULT 1,
69 PRIMARY KEY (APP_USERid, SUBCATEGORYid),
70 CONSTRAINT FK_user_subcategory_sub_user
71 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
72 ON UPDATE CASCADE ON DELETE SET DEFAULT,
73 CONSTRAINT FK_user_subcategory_sub_subcategory
74 FOREIGN KEY (SUBCATEGORYid) REFERENCES SUBCATEGORY (id)
75 ON UPDATE CASCADE ON DELETE SET DEFAULT
76);
77
78CREATE TABLE EVENT (
79 id BIGSERIAL NOT NULL,
80 title VARCHAR(100) NOT NULL,
81 description VARCHAR(255),
82 start_date DATE NOT NULL,
83 end_date DATE NOT NULL,
84 CATEGORIZATIONid BIGINT NOT NULL DEFAULT 1,
85 SUBCATEGORYid BIGINT ,
86 PRIMARY KEY (id),
87 CONSTRAINT CHK_event_dates
88 CHECK (end_date >= start_date),
89 CONSTRAINT FK_event_categorization
90 FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
91 ON UPDATE CASCADE ON DELETE SET DEFAULT,
92 CONSTRAINT FK_event_subcategory
93 FOREIGN KEY (SUBCATEGORYid) REFERENCES SUBCATEGORY (id)
94 ON UPDATE CASCADE ON DELETE SET NULL
95);
96
97CREATE TABLE EVENT_IMAGE (
98 id BIGSERIAL NOT NULL,
99 image_url VARCHAR(255) NOT NULL,
100 EVENTid BIGINT NOT NULL,
101 PRIMARY KEY (id),
102 CONSTRAINT FK_event_image_event
103 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
104 ON UPDATE CASCADE ON DELETE CASCADE
105);
106
107CREATE TABLE EVENT_ROLE (
108 id BIGSERIAL NOT NULL,
109 role_name VARCHAR(20) NOT null unique,
110 PRIMARY KEY (id),
111 CONSTRAINT CHK_event_role_values
112 CHECK (role_name IN ('EVENT_ADMIN', 'SALES_MANAGER', 'INFO_ADMIN', 'CONTENT_MANAGER'))
113);
114
115CREATE TABLE USER_EVENT (
116 APP_USERid BIGINT NOT NULL,
117 EVENTid BIGINT NOT NULL,
118 EVENT_ROLEid BIGINT NOT NULL,
119 PRIMARY KEY (APP_USERid, EVENTid),
120 CONSTRAINT FK_user_event_user
121 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
122 ON UPDATE CASCADE ON DELETE RESTRICT,
123 CONSTRAINT FK_user_event_event
124 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
125 ON UPDATE CASCADE ON DELETE RESTRICT,
126 CONSTRAINT FK_user_event_role
127 FOREIGN KEY (EVENT_ROLEid) REFERENCES EVENT_ROLE (id)
128 ON UPDATE CASCADE ON DELETE RESTRICT
129);
130
131CREATE TABLE VENUE (
132 id BIGSERIAL NOT NULL,
133 venue_name VARCHAR(100) NOT NULL,
134 city VARCHAR(50),
135 address VARCHAR(100) NOT NULL,
136 PRIMARY KEY (id)
137);
138
139CREATE TABLE HALL (
140 id BIGSERIAL NOT NULL,
141 hall_name VARCHAR(50) NOT NULL,
142 capacity INT NOT NULL,
143 VENUEid BIGINT NOT NULL,
144 PRIMARY KEY (id),
145 CONSTRAINT CHK_capacity_positive_number
146 CHECK (capacity > 0),
147 CONSTRAINT FK_hall_venue
148 FOREIGN KEY (VENUEid) REFERENCES VENUE (id)
149 ON UPDATE CASCADE ON DELETE RESTRICT
150);
151
152CREATE TABLE EVENT_HALL (
153 EVENTid BIGINT NOT NULL,
154 HALLid BIGINT NOT NULL,
155 allowed_access BOOLEAN NOT NULL,
156 PRIMARY KEY (EVENTid, HALLid),
157 CONSTRAINT FK_event_hall_event
158 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
159 ON UPDATE CASCADE ON DELETE RESTRICT,
160 CONSTRAINT FK_event_hall_hall
161 FOREIGN KEY (HALLid) REFERENCES HALL (id)
162 ON UPDATE CASCADE ON DELETE RESTRICT
163);
164
165CREATE TABLE TICKET_TYPE (
166 id BIGSERIAL NOT NULL,
167 type_name VARCHAR(50) NOT NULL,
168 PRIMARY KEY (id),
169 CONSTRAINT CHK_ticket_type_values
170 CHECK (type_name IN ('GENERAL_ADMISSION', 'STANDARD', 'PARTER', 'VIP'))
171);
172
173CREATE TABLE SEAT (
174 id BIGSERIAL NOT NULL,
175 seat_number INT NOT NULL,
176 HALLid BIGINT NOT NULL,
177 TICKET_TYPEid BIGINT NOT NULL,
178 PRIMARY KEY (id),
179 CONSTRAINT FK_seat_hall
180 FOREIGN KEY (HALLid) REFERENCES HALL (id)
181 ON UPDATE CASCADE ON DELETE RESTRICT,
182 CONSTRAINT FK_seat_ticket_type
183 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
184 ON UPDATE CASCADE ON DELETE RESTRICT
185);
186
187CREATE TABLE EVENT_TICKET_TYPE (
188 EVENTid BIGINT NOT NULL,
189 TICKET_TYPEid BIGINT NOT NULL,
190 price INT NOT NULL,
191 quantity_available INT NOT NULL,
192 PRIMARY KEY (EVENTid, TICKET_TYPEid),
193 CONSTRAINT CHK_ticket_price_positive_num
194 CHECK (price > 0),
195 CONSTRAINT CHK_ticket_quantity_positive_num
196 CHECK (quantity_available >= 0),
197 CONSTRAINT FK_event_ticket_type_event
198 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
199 ON UPDATE CASCADE ON DELETE RESTRICT,
200 CONSTRAINT FK_event_ticket_type_ticket_type
201 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
202 ON UPDATE CASCADE ON DELETE RESTRICT
203);
204
205CREATE TABLE STATUS (
206 id BIGSERIAL NOT NULL,
207 status_name VARCHAR(20) NOT NULL,
208 description VARCHAR(255),
209 PRIMARY KEY (id),
210 CONSTRAINT CHK_order_status_values
211 CHECK (status_name IN ('CREATED', 'PAID', 'CANCELLED'))
212);
213
214CREATE TABLE PROMO_CODE (
215 id BIGSERIAL NOT NULL,
216 code VARCHAR(50) NOT NULL UNIQUE,
217 discount_percent INT NOT NULL,
218 expiration_date DATE NOT NULL,
219 APP_USERid BIGINT DEFAULT 1,
220 PRIMARY KEY (id),
221 CONSTRAINT CHK_discount_percent
222 CHECK (discount_percent > 0 AND discount_percent < 100),
223 CONSTRAINT FK_promo_code_user
224 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
225 ON UPDATE CASCADE ON DELETE SET DEFAULT
226);
227
228CREATE TABLE USER_ORDER (
229 id BIGSERIAL NOT NULL,
230 order_date DATE NOT NULL,
231 total_amount INT NOT NULL,
232 APP_USERid BIGINT NOT NULL,
233 STATUSid BIGINT NOT NULL,
234 PROMO_CODEid BIGINT,
235 PRIMARY KEY (id),
236 CONSTRAINT CHK_order_amount_positive_num
237 CHECK (total_amount > 0),
238 CONSTRAINT FK_user_order_user
239 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
240 ON UPDATE CASCADE ON DELETE RESTRICT,
241 CONSTRAINT FK_user_order_status
242 FOREIGN KEY (STATUSid) REFERENCES STATUS (id)
243 ON UPDATE CASCADE ON DELETE RESTRICT,
244 CONSTRAINT FK_user_order_promo_code
245 FOREIGN KEY (PROMO_CODEid) REFERENCES PROMO_CODE (id)
246 ON UPDATE CASCADE ON DELETE SET NULL
247);
248
249CREATE TABLE TICKET (
250 id BIGSERIAL NOT NULL,
251 code VARCHAR(50) NOT NULL UNIQUE,
252 status VARCHAR(20) NOT NULL,
253 TICKET_TYPEid BIGINT NOT NULL,
254 USER_ORDERid BIGINT NOT NULL,
255 SEATid BIGINT,
256 APP_USERid BIGINT NOT NULL,
257 EVENTid BIGINT NOT NULL,
258 HALLid BIGINT NOT NULL,
259 PRIMARY KEY (id),
260 CONSTRAINT CHK_ticket_status_values
261 CHECK (status IN ('ACTIVE', 'USED', 'CANCELLED')),
262 CONSTRAINT FK_ticket_ticket_type
263 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
264 ON UPDATE CASCADE ON DELETE RESTRICT,
265 CONSTRAINT FK_ticket_user_order
266 FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id)
267 ON UPDATE CASCADE ON DELETE RESTRICT,
268 CONSTRAINT FK_ticket_seat
269 FOREIGN KEY (SEATid) REFERENCES SEAT (id)
270 ON UPDATE CASCADE ON DELETE RESTRICT,
271 CONSTRAINT FK_ticket_user
272 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
273 ON UPDATE CASCADE ON DELETE RESTRICT,
274 CONSTRAINT FK_ticket_event
275 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
276 ON UPDATE CASCADE ON DELETE RESTRICT,
277 CONSTRAINT FK_ticket_hall
278 FOREIGN KEY (HALLid) REFERENCES HALL (id)
279 ON UPDATE CASCADE ON DELETE RESTRICT
280);
281
282CREATE TABLE PAYMENT_METHOD (
283 id BIGSERIAL NOT NULL,
284 method_name VARCHAR(20) NOT NULL,
285 PRIMARY KEY (id),
286 CONSTRAINT CHK_payment_method_name
287 CHECK (method_name IN ('CASH', 'CARD'))
288);
289
290CREATE TABLE PAYMENT (
291 id BIGSERIAL NOT NULL,
292 amount INT NOT NULL,
293 status VARCHAR(20) NOT NULL,
294 payment_date DATE NOT NULL,
295 USER_ORDERid BIGINT NOT NULL,
296 PAYMENT_METHODid BIGINT NOT NULL,
297 PRIMARY KEY (id),
298 CONSTRAINT CHK_payment_status_values
299 CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED')),
300 CONSTRAINT CHK_amount_positive_number
301 CHECK (amount > 0),
302 CONSTRAINT FK_payment_user_order
303 FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id)
304 ON UPDATE CASCADE ON DELETE RESTRICT,
305 CONSTRAINT FK_payment_payment_method
306 FOREIGN KEY (PAYMENT_METHODid) REFERENCES PAYMENT_METHOD (id)
307 ON UPDATE CASCADE ON DELETE RESTRICT
308);
309
310CREATE TABLE REFUND (
311 id BIGSERIAL NOT NULL,
312 amount INT NOT NULL,
313 reason VARCHAR(255) NOT NULL,
314 status VARCHAR(20) NOT NULL,
315 PAYMENTid BIGINT NOT NULL,
316 PRIMARY KEY (id),
317 CONSTRAINT CHK_refund_amount_positive_num
318 CHECK (amount > 0),
319 CONSTRAINT CHK_refund_status_values
320 CHECK (status IN ('REQUESTED', 'APPROVED', 'REJECTED')),
321 CONSTRAINT FK_refund_payment
322 FOREIGN KEY (PAYMENTid) REFERENCES PAYMENT (id)
323 ON UPDATE CASCADE ON DELETE RESTRICT
324);
325
326CREATE TABLE REVIEW (
327 id BIGSERIAL NOT NULL,
328 rating INT NOT NULL,
329 review_comment VARCHAR(255),
330 APP_USERid BIGINT DEFAULT 1,
331 EVENTid BIGINT NOT NULL,
332 PRIMARY KEY (id),
333 CONSTRAINT CHK_rating_range
334 CHECK (rating BETWEEN 1 AND 5),
335 CONSTRAINT FK_review_user
336 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
337 ON UPDATE CASCADE ON DELETE SET DEFAULT,
338 CONSTRAINT FK_review_event
339 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
340 ON UPDATE CASCADE ON DELETE CASCADE
341);
342
343CREATE TABLE NOTIFICATION (
344 id BIGSERIAL NOT NULL,
345 message VARCHAR(150) NOT NULL,
346 created_at DATE NOT NULL,
347 APP_USERid BIGINT NOT NULL DEFAULT 1,
348 PRIMARY KEY (id),
349 CONSTRAINT FK_notification_user
350 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
351 ON UPDATE CASCADE ON DELETE SET DEFAULT
352);
353
354CREATE TABLE WAITLIST (
355 id BIGSERIAL NOT NULL,
356 created_at DATE NOT NULL,
357 status VARCHAR(20) NOT NULL,
358 EVENTid BIGINT NOT NULL,
359 PRIMARY KEY (id),
360 CONSTRAINT CHK_waitlist_status_values
361 CHECK (status IN ('PENDING', 'CONFIRMED', 'CANCELLED')),
362 CONSTRAINT FK_waitlist_event
363 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
364 ON UPDATE CASCADE ON DELETE CASCADE
365);
366
367CREATE TABLE USER_WAITLIST (
368 APP_USERid BIGINT NOT NULL,
369 WAITLISTid BIGINT NOT NULL,
370 PRIMARY KEY (APP_USERid, WAITLISTid),
371 CONSTRAINT FK_user_waitlist_user
372 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
373 ON UPDATE CASCADE ON DELETE RESTRICT,
374 CONSTRAINT FK_user_waitlist_waitlist
375 FOREIGN KEY (WAITLISTid) REFERENCES WAITLIST (id)
376 ON UPDATE CASCADE ON DELETE RESTRICT
377);