F2: ddl.sql

File ddl.sql, 10.2 KB (added by 231507, 2 weeks ago)
Line 
1CREATE TABLE country (
2 country_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 country_name VARCHAR(100) NOT NULL UNIQUE
4);
5
6CREATE TABLE city (
7 city_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
8 city_name VARCHAR(100) NOT NULL,
9 postal_code VARCHAR(20) NOT NULL,
10 country_id BIGINT NOT NULL,
11 UNIQUE (city_name, country_id),
12 FOREIGN KEY (country_id)
13 REFERENCES country(country_id)
14 ON DELETE RESTRICT
15 ON UPDATE CASCADE
16);
17
18CREATE TABLE venue (
19 venue_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
20 venue_title VARCHAR(100) NOT NULL,
21 street_address VARCHAR(100) NOT NULL,
22 capacity INTEGER NOT NULL CHECK (capacity > 0),
23 city_id BIGINT NOT NULL,
24 UNIQUE (venue_title, street_address, city_id), -- еден објект може да има повеке сали/простории
25 FOREIGN KEY (city_id)
26 REFERENCES city(city_id)
27 ON DELETE RESTRICT
28 ON UPDATE CASCADE
29);
30
31CREATE TABLE admin (
32 admin_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
33 email VARCHAR(255) NOT NULL UNIQUE,
34 first_name VARCHAR(255) NOT NULL,
35 last_name VARCHAR(255) NOT NULL,
36 password_hash VARCHAR(255) NOT NULL,
37 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
38);
39
40CREATE TABLE customer (
41 customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
42 email VARCHAR(100) NOT NULL UNIQUE,
43 first_name VARCHAR(100) NOT NULL,
44 last_name VARCHAR(100) NOT NULL,
45 hash_password VARCHAR(100) NOT NULL,
46 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
47);
48
49CREATE TABLE category (
50 category_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
51 category_name VARCHAR(100) NOT NULL UNIQUE,
52 category_description VARCHAR(255) NOT NULL,
53 parent_category_id BIGINT,
54 FOREIGN KEY (parent_category_id)
55 REFERENCES category(category_id)
56 ON DELETE SET NULL
57 ON UPDATE CASCADE
58);
59
60CREATE TABLE sponsor_type (
61 sponsor_type_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
62 sponsor_type_name VARCHAR(100) NOT NULL UNIQUE,
63 description VARCHAR(255)
64);
65
66CREATE TABLE ticket_type (
67 ticket_type_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
68 type_name VARCHAR(255) NOT NULL UNIQUE
69);
70
71CREATE TABLE sponsor (
72 sponsor_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
73 sponsor_name VARCHAR(100) NOT NULL UNIQUE,
74 contact_email VARCHAR(100) NOT NULL,
75 website VARCHAR(100),
76 sponsor_type_id BIGINT NOT NULL,
77 FOREIGN KEY (sponsor_type_id)
78 REFERENCES sponsor_type(sponsor_type_id)
79 ON DELETE RESTRICT
80 ON UPDATE CASCADE,
81 CHECK (contact_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
82);
83
84CREATE TABLE event (
85 event_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
86 name VARCHAR(255) NOT NULL,
87 description VARCHAR(255),
88 start_datetime TIMESTAMP NOT NULL,
89 end_datetime TIMESTAMP NOT NULL,
90 status VARCHAR(50) NOT NULL,
91 venue_id BIGINT NOT NULL,
92 category_id BIGINT NOT NULL,
93 CHECK (end_datetime > start_datetime),
94 CHECK (status IN ('DRAFT', 'PUBLISHED', 'ONGOING', 'COMPLETED', 'CANCELLED')),
95 UNIQUE (name, start_datetime, venue_id),
96 FOREIGN KEY (venue_id)
97 REFERENCES venue(venue_id)
98 ON DELETE RESTRICT
99 ON UPDATE CASCADE,
100 FOREIGN KEY (category_id)
101 REFERENCES category(category_id)
102 ON DELETE RESTRICT
103 ON UPDATE CASCADE
104);
105
106CREATE TABLE seat (
107 seat_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
108 seat_number INTEGER NOT NULL,
109 row_number INTEGER NOT NULL,
110 section INTEGER NOT NULL,
111 section_type VARCHAR(100) NOT NULL,
112 venue_id BIGINT NOT NULL,
113 UNIQUE (venue_id, section, row_number, seat_number),
114 FOREIGN KEY (venue_id)
115 REFERENCES venue(venue_id)
116 ON DELETE CASCADE
117 ON UPDATE CASCADE -- seat е зависен од venue
118);
119
120CREATE TABLE event_admin (
121 admin_id BIGINT PRIMARY KEY,
122 FOREIGN KEY (admin_id)
123 REFERENCES admin(admin_id)
124 ON DELETE CASCADE
125 ON UPDATE CASCADE
126);
127
128CREATE TABLE ticket_admin (
129 admin_id BIGINT PRIMARY KEY,
130 FOREIGN KEY (admin_id)
131 REFERENCES admin(admin_id)
132 ON DELETE CASCADE
133 ON UPDATE CASCADE
134);
135
136CREATE TABLE discount (
137 discount_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
138 discount_code VARCHAR(50) NOT NULL UNIQUE,
139 discount_percent INTEGER NOT NULL CHECK (discount_percent BETWEEN 0 AND 100),
140 event_admin_id BIGINT NOT NULL,
141 FOREIGN KEY (event_admin_id)
142 REFERENCES event_admin(admin_id)
143 ON DELETE RESTRICT
144 ON UPDATE CASCADE
145);
146
147CREATE TABLE payment (
148 payment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
149 amount NUMERIC(10,2) NOT NULL CHECK (amount >= 0),
150 payment_method VARCHAR(100) NOT NULL,
151 payment_status VARCHAR(50) NOT NULL CHECK (payment_status IN ('PENDING','COMPLETED','FAILED','REFUNDED')),
152 payment_date TIMESTAMP NOT NULL,
153 customer_id BIGINT NOT NULL,
154 discount_id BIGINT,
155 CHECK (payment_date <= CURRENT_TIMESTAMP),
156 CHECK (payment_method IN ('CASH','CARD','ONLINE','TRANSFER')),
157 FOREIGN KEY (customer_id)
158 REFERENCES customer(customer_id)
159 ON DELETE RESTRICT
160 ON UPDATE CASCADE,
161 FOREIGN KEY (discount_id)
162 REFERENCES discount(discount_id)
163 ON DELETE SET NULL
164 ON UPDATE CASCADE
165);
166
167CREATE TABLE ticket (
168 ticket_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
169 status VARCHAR(50) NOT NULL DEFAULT 'AVAILABLE' CHECK (status IN ('AVAILABLE','RESERVED','PURCHASED','SCANNED','CANCELLED')),
170 reserved_at TIMESTAMP,
171 expires_at TIMESTAMP,
172 purchased_at TIMESTAMP,
173 ticket_price NUMERIC(10,2) NOT NULL CHECK (ticket_price >= 0),
174 qr_code VARCHAR(255) NOT NULL UNIQUE,
175 customer_id BIGINT,
176 seat_id BIGINT NOT NULL,
177 event_id BIGINT NOT NULL,
178 ticket_type_id BIGINT NOT NULL,
179 payment_id BIGINT,
180 UNIQUE (seat_id, event_id),
181 CHECK (purchased_at IS NULL OR reserved_at IS NULL OR purchased_at >= reserved_at),
182 CHECK (expires_at IS NULL OR reserved_at IS NULL OR expires_at >= reserved_at),
183 CHECK (purchased_at IS NULL OR expires_at IS NULL OR purchased_at <= expires_at),
184 CHECK (
185 (status = 'AVAILABLE' AND reserved_at IS NULL AND purchased_at IS NULL)
186 OR
187 (status = 'RESERVED' AND reserved_at IS NOT NULL AND purchased_at IS NULL)
188 OR
189 (status = 'PURCHASED' AND purchased_at IS NOT NULL)
190 OR
191 (status = 'SCANNED' AND purchased_at IS NOT NULL)
192 OR
193 (status = 'CANCELLED')),
194 FOREIGN KEY (customer_id)
195 REFERENCES customer(customer_id)
196 ON DELETE SET NULL
197 ON UPDATE CASCADE,
198 FOREIGN KEY (seat_id)
199 REFERENCES seat(seat_id)
200 ON DELETE RESTRICT
201 ON UPDATE CASCADE,
202 FOREIGN KEY (event_id)
203 REFERENCES event(event_id)
204 ON DELETE RESTRICT
205 ON UPDATE CASCADE,
206 FOREIGN KEY (ticket_type_id)
207 REFERENCES ticket_type(ticket_type_id)
208 ON DELETE RESTRICT
209 ON UPDATE CASCADE,
210 FOREIGN KEY (payment_id)
211 REFERENCES payment(payment_id)
212 ON DELETE SET NULL
213 ON UPDATE CASCADE
214);
215
216CREATE TABLE event_media (
217 media_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
218 url VARCHAR(255) NOT NULL,
219 event_id BIGINT NOT NULL,
220 FOREIGN KEY (event_id)
221 REFERENCES event(event_id)
222 ON DELETE CASCADE -- медија нема смисла без event
223 ON UPDATE CASCADE
224);
225
226CREATE TABLE review (
227 review_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
228 comment VARCHAR(255),
229 rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
230 customer_id BIGINT NOT NULL,
231 event_id BIGINT NOT NULL,
232 UNIQUE (customer_id, event_id),
233 FOREIGN KEY (customer_id)
234 REFERENCES customer(customer_id)
235 ON DELETE CASCADE
236 ON UPDATE CASCADE,
237 FOREIGN KEY (event_id)
238 REFERENCES event(event_id)
239 ON DELETE CASCADE
240 ON UPDATE CASCADE
241);
242
243CREATE TABLE notification (
244 notification_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
245 title VARCHAR(255) NOT NULL,
246 message VARCHAR(255) NOT NULL,
247 created_at TIMESTAMP NOT NULL,
248 is_read BOOLEAN DEFAULT FALSE NOT NULL,
249 customer_id BIGINT,
250 event_id BIGINT,
251 CHECK (created_at <= CURRENT_TIMESTAMP),
252 FOREIGN KEY (customer_id)
253 REFERENCES customer(customer_id)
254 ON DELETE SET NULL
255 ON UPDATE CASCADE,
256 FOREIGN KEY (event_id)
257 REFERENCES event(event_id)
258 ON DELETE SET NULL
259 ON UPDATE CASCADE
260);
261
262CREATE TABLE seat_reservation (
263 reservation_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
264 reserved_at TIMESTAMP NOT NULL,
265 status VARCHAR(50) NOT NULL,
266 seat_id BIGINT NOT NULL,
267 ticket_id BIGINT NOT NULL,
268 CHECK (status IN ('ACTIVE','CANCELLED','EXPIRED')),
269 FOREIGN KEY (seat_id)
270 REFERENCES seat(seat_id)
271 ON DELETE RESTRICT
272 ON UPDATE CASCADE,
273 FOREIGN KEY (ticket_id)
274 REFERENCES ticket(ticket_id)
275 ON DELETE RESTRICT
276 ON UPDATE CASCADE
277);
278
279CREATE TABLE event_sponsorship (
280 sponsor_id BIGINT,
281 event_id BIGINT,
282 sponsorship_amount NUMERIC(12,2) NOT NULL CHECK (sponsorship_amount >= 0),
283 contract_date TIMESTAMP NOT NULL,
284 PRIMARY KEY (sponsor_id, event_id),
285 CHECK (contract_date <= CURRENT_TIMESTAMP),
286 FOREIGN KEY (sponsor_id)
287 REFERENCES sponsor(sponsor_id)
288 ON DELETE CASCADE -- bridge table
289 ON UPDATE CASCADE,
290 FOREIGN KEY (event_id)
291 REFERENCES event(event_id)
292 ON DELETE CASCADE -- bridge table
293 ON UPDATE CASCADE
294);
295
296CREATE TABLE event_manages (
297 event_id BIGINT,
298 event_admin_id BIGINT,
299 PRIMARY KEY (event_id, event_admin_id),
300 FOREIGN KEY (event_id)
301 REFERENCES event(event_id)
302 ON DELETE CASCADE -- bridge table
303 ON UPDATE CASCADE,
304 FOREIGN KEY (event_admin_id)
305 REFERENCES event_admin(admin_id)
306 ON DELETE CASCADE -- bridge table
307 ON UPDATE CASCADE
308);