Eventix/Faza2A: ddlEventix.sql

File ddlEventix.sql, 12.9 KB (added by 231070, 4 days ago)
Line 
1CREATE TABLE APP_USER (
2 id BIGSERIAL NOT NULL,
3 first_name VARCHAR(20) NOT NULL,
4 last_name VARCHAR(20) NOT NULL,
5 email VARCHAR(50) NOT NULL UNIQUE,
6 phone VARCHAR(20) NOT NULL,
7 created_at DATE NOT NULL,
8 password_hash 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 VARCHAR(20) NOT NULL,
19 PRIMARY KEY (id),
20 CONSTRAINT CHK_role_title
21 CHECK (role 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 name VARCHAR(20) NOT NULL,
39 PRIMARY KEY (id),
40 CONSTRAINT CHK_category_values
41 CHECK (name IN ('Concerts', 'Theatre', 'Cinema', 'Festivals', 'Exhibitions', 'Seminars'))
42);
43
44CREATE TABLE SUBCATEGORY (
45 id BIGSERIAL NOT NULL,
46 name VARCHAR(20) 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(50) NOT NULL,
81 description VARCHAR(255),
82 start_date DATE NOT NULL,
83 end_date DATE NOT NULL,
84 CATEGORIZATIONid BIGINT ,
85 PRIMARY KEY (id),
86 CONSTRAINT CHK_event_dates
87 CHECK (end_date >= start_date),
88 CONSTRAINT FK_event_categorization
89 FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
90 ON UPDATE CASCADE ON DELETE SET NULL
91);
92
93CREATE TABLE EVENT_IMAGE (
94 id BIGSERIAL NOT NULL,
95 image_url VARCHAR(255) NOT NULL,
96 EVENTid BIGINT NOT NULL,
97 PRIMARY KEY (id),
98 CONSTRAINT FK_event_image_event
99 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
100 ON UPDATE CASCADE ON DELETE CASCADE
101);
102
103CREATE TABLE EVENT_ROLE (
104 id BIGSERIAL NOT NULL,
105 role VARCHAR(20) NOT NULL,
106 PRIMARY KEY (id),
107 CONSTRAINT CHK_event_role_values
108 CHECK (role IN ('EVENT_ADMIN', 'SALES_MANAGER', 'INFO_ADMIN', 'CONTENT_MANAGER'))
109);
110
111CREATE TABLE USER_EVENT (
112 APP_USERid BIGINT NOT NULL,
113 EVENTid BIGINT NOT NULL,
114 EVENT_ROLEid BIGINT NOT NULL,
115 PRIMARY KEY (APP_USERid, EVENTid),
116 CONSTRAINT FK_user_event_user
117 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
118 ON UPDATE CASCADE ON DELETE RESTRICT,
119 CONSTRAINT FK_user_event_event
120 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
121 ON UPDATE CASCADE ON DELETE RESTRICT,
122 CONSTRAINT FK_user_event_role
123 FOREIGN KEY (EVENT_ROLEid) REFERENCES EVENT_ROLE (id)
124 ON UPDATE CASCADE ON DELETE RESTRICT
125);
126
127CREATE TABLE VENUE (
128 id BIGSERIAL NOT NULL,
129 name VARCHAR(100) NOT NULL,
130 city VARCHAR(20),
131 address VARCHAR(100) NOT NULL,
132 PRIMARY KEY (id)
133);
134
135CREATE TABLE HALL (
136 id BIGSERIAL NOT NULL,
137 name VARCHAR(20) NOT NULL,
138 capacity INT NOT NULL,
139 VENUEid BIGINT NOT NULL,
140 PRIMARY KEY (id),
141 CONSTRAINT CHK_capacity_positive_number
142 CHECK (capacity > 0),
143 CONSTRAINT FK_hall_venue
144 FOREIGN KEY (VENUEid) REFERENCES VENUE (id)
145 ON UPDATE CASCADE ON DELETE RESTRICT
146);
147
148CREATE TABLE EVENT_HALL (
149 EVENTid BIGINT NOT NULL,
150 HALLid BIGINT NOT NULL,
151 allowed_access BOOLEAN NOT NULL,
152 PRIMARY KEY (EVENTid, HALLid),
153 CONSTRAINT FK_event_hall_event
154 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
155 ON UPDATE CASCADE ON DELETE RESTRICT,
156 CONSTRAINT FK_event_hall_hall
157 FOREIGN KEY (HALLid) REFERENCES HALL (id)
158 ON UPDATE CASCADE ON DELETE RESTRICT
159);
160
161CREATE TABLE TICKET_TYPE (
162 id BIGSERIAL NOT NULL,
163 name VARCHAR(50) NOT NULL,
164 PRIMARY KEY (id),
165 CONSTRAINT CHK_ticket_type_values
166 CHECK (name IN ('GENERAL_ADMISSION', 'STANDARD', 'PARTER', 'VIP'))
167);
168
169CREATE TABLE SEAT (
170 id BIGSERIAL NOT NULL,
171 number INT NOT NULL,
172 HALLid BIGINT NOT NULL,
173 TICKET_TYPEid BIGINT NOT NULL,
174 PRIMARY KEY (id),
175 CONSTRAINT FK_seat_hall
176 FOREIGN KEY (HALLid) REFERENCES HALL (id)
177 ON UPDATE CASCADE ON DELETE RESTRICT,
178 CONSTRAINT FK_seat_ticket_type
179 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
180 ON UPDATE CASCADE ON DELETE RESTRICT
181);
182
183CREATE TABLE EVENT_TICKET_TYPE (
184 EVENTid BIGINT NOT NULL,
185 TICKET_TYPEid BIGINT NOT NULL,
186 price INT NOT NULL,
187 quantity_available INT NOT NULL,
188 PRIMARY KEY (EVENTid, TICKET_TYPEid),
189 CONSTRAINT CHK_ticket_price_positive_num
190 CHECK (price > 0),
191 CONSTRAINT CHK_ticket_quantity_positive_num
192 CHECK (quantity_available >= 0),
193 CONSTRAINT FK_event_ticket_type_event
194 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
195 ON UPDATE CASCADE ON DELETE RESTRICT,
196 CONSTRAINT FK_event_ticket_type_ticket_type
197 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
198 ON UPDATE CASCADE ON DELETE RESTRICT
199);
200
201CREATE TABLE STATUS (
202 id BIGSERIAL NOT NULL,
203 name VARCHAR(20) NOT NULL,
204 description VARCHAR(255),
205 PRIMARY KEY (id),
206 CONSTRAINT CHK_order_status_values
207 CHECK (name IN ('CREATED', 'PAID', 'CANCELLED'))
208);
209
210CREATE TABLE PROMO_CODE (
211 id BIGSERIAL NOT NULL,
212 code VARCHAR(20) NOT NULL UNIQUE,
213 discount_percent INT NOT NULL,
214 expiration_date DATE NOT NULL,
215 APP_USERid BIGINT DEFAULT 1,
216 PRIMARY KEY (id),
217 CONSTRAINT CHK_discount_percent
218 CHECK (discount_percent > 0 AND discount_percent < 100),
219 CONSTRAINT FK_promo_code_user
220 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
221 ON UPDATE CASCADE ON DELETE SET DEFAULT
222);
223
224CREATE TABLE USER_ORDER (
225 id BIGSERIAL NOT NULL,
226 order_date DATE NOT NULL,
227 total_amount INT NOT NULL,
228 APP_USERid BIGINT NOT NULL,
229 STATUSid BIGINT NOT NULL,
230 PROMO_CODEid BIGINT,
231 PRIMARY KEY (id),
232 CONSTRAINT CHK_order_amount_positive_num
233 CHECK (total_amount > 0),
234 CONSTRAINT FK_user_order_user
235 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
236 ON UPDATE CASCADE ON DELETE RESTRICT,
237 CONSTRAINT FK_user_order_status
238 FOREIGN KEY (STATUSid) REFERENCES STATUS (id)
239 ON UPDATE CASCADE ON DELETE RESTRICT,
240 CONSTRAINT FK_user_order_promo_code
241 FOREIGN KEY (PROMO_CODEid) REFERENCES PROMO_CODE (id)
242 ON UPDATE CASCADE ON DELETE SET NULL
243);
244
245CREATE TABLE TICKET (
246 id BIGSERIAL NOT NULL,
247 code VARCHAR(50) NOT NULL UNIQUE,
248 status VARCHAR(20) NOT NULL,
249 TICKET_TYPEid BIGINT NOT NULL,
250 USER_ORDERid BIGINT NOT NULL,
251 SEATid BIGINT,
252 APP_USERid BIGINT NOT NULL,
253 EVENTid BIGINT NOT NULL,
254 HALLid BIGINT NOT NULL,
255 PRIMARY KEY (id),
256 CONSTRAINT CHK_ticket_status_values
257 CHECK (status IN ('ACTIVE', 'USED', 'CANCELLED')),
258 CONSTRAINT FK_ticket_ticket_type
259 FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
260 ON UPDATE CASCADE ON DELETE RESTRICT,
261 CONSTRAINT FK_ticket_user_order
262 FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id)
263 ON UPDATE CASCADE ON DELETE RESTRICT,
264 CONSTRAINT FK_ticket_seat
265 FOREIGN KEY (SEATid) REFERENCES SEAT (id)
266 ON UPDATE CASCADE ON DELETE RESTRICT,
267 CONSTRAINT FK_ticket_user
268 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
269 ON UPDATE CASCADE ON DELETE RESTRICT,
270 CONSTRAINT FK_ticket_event
271 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
272 ON UPDATE CASCADE ON DELETE RESTRICT,
273 CONSTRAINT FK_ticket_hall
274 FOREIGN KEY (HALLid) REFERENCES HALL (id)
275 ON UPDATE CASCADE ON DELETE RESTRICT
276);
277
278CREATE TABLE PAYMENT_METHOD (
279 id BIGSERIAL NOT NULL,
280 name VARCHAR(20) NOT NULL,
281 PRIMARY KEY (id),
282 CONSTRAINT CHK_payment_method_name
283 CHECK (name IN ('CASH', 'CARD'))
284);
285
286CREATE TABLE PAYMENT (
287 id BIGSERIAL NOT NULL,
288 amount INT NOT NULL,
289 status VARCHAR(20) NOT NULL,
290 payment_date DATE NOT NULL,
291 USER_ORDERid BIGINT NOT NULL,
292 PAYMENT_METHODid BIGINT NOT NULL,
293 PRIMARY KEY (id),
294 CONSTRAINT CHK_payment_status_values
295 CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED')),
296 CONSTRAINT CHK_amount_positive_number
297 CHECK (amount > 0),
298 CONSTRAINT FK_payment_user_order
299 FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id)
300 ON UPDATE CASCADE ON DELETE RESTRICT,
301 CONSTRAINT FK_payment_payment_method
302 FOREIGN KEY (PAYMENT_METHODid) REFERENCES PAYMENT_METHOD (id)
303 ON UPDATE CASCADE ON DELETE RESTRICT
304);
305
306CREATE TABLE REFUND (
307 id BIGSERIAL NOT NULL,
308 amount INT NOT NULL,
309 reason VARCHAR(255) NOT NULL,
310 status VARCHAR(20) NOT NULL,
311 PAYMENTid BIGINT NOT NULL,
312 PRIMARY KEY (id),
313 CONSTRAINT CHK_refund_amount_positive_num
314 CHECK (amount > 0),
315 CONSTRAINT CHK_refund_status_values
316 CHECK (status IN ('REQUESTED', 'APPROVED', 'REJECTED')),
317 CONSTRAINT FK_refund_payment
318 FOREIGN KEY (PAYMENTid) REFERENCES PAYMENT (id)
319 ON UPDATE CASCADE ON DELETE RESTRICT
320);
321
322CREATE TABLE REVIEW (
323 id BIGSERIAL NOT NULL,
324 rating INT NOT NULL,
325 comment VARCHAR(255),
326 APP_USERid BIGINT DEFAULT 1,
327 EVENTid BIGINT NOT NULL,
328 PRIMARY KEY (id),
329 CONSTRAINT CHK_rating_range
330 CHECK (rating BETWEEN 1 AND 5),
331 CONSTRAINT FK_review_user
332 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
333 ON UPDATE CASCADE ON DELETE SET DEFAULT,
334 CONSTRAINT FK_review_event
335 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
336 ON UPDATE CASCADE ON DELETE CASCADE
337);
338
339CREATE TABLE NOTIFICATION (
340 id BIGSERIAL NOT NULL,
341 message VARCHAR(150) NOT NULL,
342 created_at DATE NOT NULL,
343 APP_USERid BIGINT NOT NULL DEFAULT 1,
344 PRIMARY KEY (id),
345 CONSTRAINT FK_notification_user
346 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
347 ON UPDATE CASCADE ON DELETE SET DEFAULT
348);
349
350CREATE TABLE WAITLIST (
351 id BIGSERIAL NOT NULL,
352 created_at DATE NOT NULL,
353 status VARCHAR(20) NOT NULL,
354 EVENTid BIGINT NOT NULL,
355 PRIMARY KEY (id),
356 CONSTRAINT CHK_waitlist_status_values
357 CHECK (status IN ('PENDING', 'CONFIRMED', 'CANCELLED')),
358 CONSTRAINT FK_waitlist_event
359 FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
360 ON UPDATE CASCADE ON DELETE CASCADE
361);
362
363CREATE TABLE USER_WAITLIST (
364 APP_USERid BIGINT NOT NULL,
365 WAITLISTid BIGINT NOT NULL,
366 PRIMARY KEY (APP_USERid, WAITLISTid),
367 CONSTRAINT FK_user_waitlist_user
368 FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
369 ON UPDATE CASCADE ON DELETE RESTRICT,
370 CONSTRAINT FK_user_waitlist_waitlist
371 FOREIGN KEY (WAITLISTid) REFERENCES WAITLIST (id)
372 ON UPDATE CASCADE ON DELETE RESTRICT
373);