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