DatabaseCreation: ddl.sql

File ddl.sql, 21.7 KB (added by 231138, 2 weeks ago)
Line 
1-- КОРИСНИЦИ
2
3CREATE TABLE users (
4 id SERIAL PRIMARY KEY,
5 name VARCHAR(100) NOT NULL,
6 email VARCHAR(150) NOT NULL UNIQUE,
7 phone VARCHAR(20) NOT NULL UNIQUE,
8 is_verified BOOLEAN NOT NULL DEFAULT FALSE,
9 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
10 deleted_at TIMESTAMP NULL,
11 CONSTRAINT chk_users_email CHECK (email LIKE '%@%.%'),
12 CONSTRAINT chk_users_name CHECK (LENGTH(TRIM(name)) > 0),
13 CONSTRAINT chk_users_phone CHECK (phone ~ '^\+389[2-9]\d{6,7}$'),
14 CONSTRAINT chk_users_deleted_at CHECK (deleted_at IS NULL OR deleted_at > created_at)
15);
16
17INSERT INTO users (id, name, email, phone, is_verified)
18VALUES (0, 'Deleted User', 'deleted@drivenet.internal', '+38921000000', FALSE);
19
20SELECT setval('users_id_seq', 1, FALSE);
21
22
23CREATE TABLE passengers (
24 id SERIAL PRIMARY KEY,
25 user_id INT NOT NULL UNIQUE,
26 smoking BOOLEAN NOT NULL DEFAULT FALSE,
27 pets BOOLEAN NOT NULL DEFAULT FALSE,
28 max_detour_percent DECIMAL(5,2) NULL,
29 is_active BOOLEAN NOT NULL DEFAULT TRUE,
30 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
31 CONSTRAINT chk_passengers_max_detour CHECK (
32 max_detour_percent IS NULL
33 OR (max_detour_percent >= 0 AND max_detour_percent <= 100)
34 ),
35 FOREIGN KEY (user_id) REFERENCES users(id)
36 ON DELETE RESTRICT
37);
38
39CREATE TABLE drivers (
40 id SERIAL PRIMARY KEY,
41 user_id INT NOT NULL UNIQUE,
42 license_number VARCHAR(50) NOT NULL UNIQUE,
43 license_class VARCHAR(10) NOT NULL,
44 license_expiry DATE NOT NULL,
45 status VARCHAR(20) NOT NULL DEFAULT 'pending',
46 verified_at TIMESTAMP NULL,
47 is_active BOOLEAN NOT NULL DEFAULT TRUE,
48 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
49 CONSTRAINT chk_drivers_status CHECK (status IN ('pending', 'active', 'suspended', 'banned')),
50 CONSTRAINT chk_drivers_expiry CHECK (license_expiry > '2000-01-01'),
51 CONSTRAINT chk_drivers_verified_at CHECK (
52 verified_at IS NULL OR status IN ('active', 'suspended', 'banned')
53 ),
54 FOREIGN KEY (user_id) REFERENCES users(id)
55 ON DELETE RESTRICT
56);
57
58CREATE TABLE user_addresses (
59 id SERIAL PRIMARY KEY,
60 user_id INT NOT NULL DEFAULT 0,
61 label VARCHAR(50) NOT NULL,
62 address VARCHAR(200) NOT NULL,
63 lat DECIMAL(10,7) NOT NULL,
64 lng DECIMAL(10,7) NOT NULL,
65 CONSTRAINT chk_user_addresses_lat CHECK (lat BETWEEN -90 AND 90),
66 CONSTRAINT chk_user_addresses_lng CHECK (lng BETWEEN -180 AND 180),
67 CONSTRAINT chk_user_addresses_label CHECK (LENGTH(TRIM(label)) > 0),
68 FOREIGN KEY (user_id) REFERENCES users(id)
69 ON DELETE SET DEFAULT
70);
71
72
73--ВОЗИЛА
74
75CREATE TABLE manufacturers (
76 id SERIAL PRIMARY KEY,
77 name VARCHAR(100) NOT NULL UNIQUE,
78 country VARCHAR(100) NOT NULL,
79 CONSTRAINT chk_manufacturers_name CHECK (LENGTH(TRIM(name)) > 0),
80 CONSTRAINT chk_manufacturers_country CHECK (LENGTH(TRIM(country)) > 0)
81);
82
83CREATE TABLE vehicle_models (
84 id SERIAL PRIMARY KEY,
85 manufacturer_id INT NOT NULL,
86 model_name VARCHAR(100) NOT NULL,
87 body_type VARCHAR(50) NULL,
88 fuel_type VARCHAR(50) NULL,
89 CONSTRAINT chk_vehicle_models_fuel CHECK (
90 fuel_type IS NULL
91 OR fuel_type IN ('petrol', 'diesel', 'electric', 'hybrid', 'lpg')
92 ),
93 CONSTRAINT chk_vehicle_models_body CHECK (
94 body_type IS NULL
95 OR body_type IN ('sedan', 'hatchback', 'suv', 'van', 'minivan', 'coupe', 'estate')
96 ),
97 CONSTRAINT chk_vehicle_models_name CHECK (LENGTH(TRIM(model_name)) > 0),
98 CONSTRAINT uq_vehicle_models_manufacturer UNIQUE (manufacturer_id, model_name),
99 FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id)
100 ON DELETE RESTRICT
101);
102
103CREATE TABLE vehicles (
104 id SERIAL PRIMARY KEY,
105 model_id INT NOT NULL,
106 year INT NOT NULL,
107 seats INT NOT NULL,
108 license_plate VARCHAR(20) NOT NULL UNIQUE,
109 vehicle_type VARCHAR(50) NULL,
110 color VARCHAR(50) NULL,
111 is_active BOOLEAN NOT NULL DEFAULT TRUE,
112 CONSTRAINT chk_vehicles_year CHECK (
113 year >= 1980
114 AND year <= EXTRACT(YEAR FROM CURRENT_DATE) + 1
115 ),
116 CONSTRAINT chk_vehicles_seats CHECK (seats >= 2 AND seats <= 9),
117 CONSTRAINT chk_vehicles_type CHECK (
118 vehicle_type IS NULL
119 OR vehicle_type IN ('car', 'van', 'minibus')
120 ),
121 CONSTRAINT chk_vehicles_plate CHECK (LENGTH(TRIM(license_plate)) > 0),
122 FOREIGN KEY (model_id) REFERENCES vehicle_models(id)
123 ON DELETE RESTRICT
124);
125
126CREATE TABLE vehicle_ownership (
127 id SERIAL PRIMARY KEY,
128 driver_id INT NOT NULL,
129 vehicle_id INT NOT NULL,
130 owned_from DATE NOT NULL,
131 owned_to DATE NULL,
132 is_active BOOLEAN NOT NULL DEFAULT TRUE,
133 CONSTRAINT chk_vehicle_ownership_dates CHECK (owned_to IS NULL OR owned_to > owned_from),
134 FOREIGN KEY (driver_id) REFERENCES drivers(id)
135 ON DELETE RESTRICT,
136 FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
137 ON DELETE RESTRICT
138);
139
140CREATE UNIQUE INDEX uq_vehicle_ownership_active
141 ON vehicle_ownership (vehicle_id)
142 WHERE is_active = TRUE;
143
144
145-- ЛОКАЦИИ И РУТИ
146
147CREATE TABLE cities (
148 id SERIAL PRIMARY KEY,
149 name VARCHAR(100) NOT NULL,
150 country VARCHAR(100) NOT NULL,
151 lat DECIMAL(10,7) NOT NULL,
152 lng DECIMAL(10,7) NOT NULL,
153 CONSTRAINT uq_cities_name_country CHECK (LENGTH(TRIM(name)) > 0),
154 CONSTRAINT chk_cities_lat CHECK (lat BETWEEN -90 AND 90),
155 CONSTRAINT chk_cities_lng CHECK (lng BETWEEN -180 AND 180),
156 UNIQUE (name, country)
157);
158
159CREATE TABLE locations (
160 id SERIAL PRIMARY KEY,
161 city_id INT NOT NULL,
162 name VARCHAR(150) NOT NULL,
163 address VARCHAR(250) NULL,
164 lat DECIMAL(10,7) NOT NULL,
165 lng DECIMAL(10,7) NOT NULL,
166 CONSTRAINT chk_locations_lat CHECK (lat BETWEEN -90 AND 90),
167 CONSTRAINT chk_locations_lng CHECK (lng BETWEEN -180 AND 180),
168 CONSTRAINT chk_locations_name CHECK (LENGTH(TRIM(name)) > 0),
169 FOREIGN KEY (city_id) REFERENCES cities(id)
170 ON DELETE RESTRICT
171);
172
173CREATE TABLE routes (
174 id SERIAL PRIMARY KEY,
175 origin_id INT NOT NULL,
176 destination_id INT NOT NULL,
177 distance_km DECIMAL(8,2) NOT NULL,
178 estimated_duration INT NOT NULL,
179 CONSTRAINT chk_routes_distance CHECK (distance_km > 0),
180 CONSTRAINT chk_routes_duration CHECK (estimated_duration > 0),
181 CONSTRAINT chk_routes_different CHECK (origin_id != destination_id),
182 FOREIGN KEY (origin_id) REFERENCES locations(id)
183 ON DELETE RESTRICT,
184 FOREIGN KEY (destination_id) REFERENCES locations(id)
185 ON DELETE RESTRICT
186);
187
188CREATE TABLE route_stops (
189 id SERIAL PRIMARY KEY,
190 route_id INT NOT NULL,
191 location_id INT NOT NULL,
192 stop_order INT NOT NULL,
193 estimated_time TIME NULL,
194 CONSTRAINT chk_route_stops_order CHECK (stop_order >= 0),
195 CONSTRAINT uq_route_stops_order UNIQUE (route_id, stop_order),
196
197 FOREIGN KEY (route_id) REFERENCES routes(id)
198 ON DELETE RESTRICT,
199 FOREIGN KEY (location_id) REFERENCES locations(id)
200 ON DELETE RESTRICT
201);
202
203
204--ВОЗЕЊА
205
206CREATE TABLE rides (
207 id SERIAL PRIMARY KEY,
208 driver_id INT NOT NULL,
209 vehicle_id INT NOT NULL,
210 route_id INT NOT NULL,
211 departure_time TIMESTAMP NOT NULL,
212 status VARCHAR(20) NOT NULL DEFAULT 'scheduled',
213 price_per_km DECIMAL(8,2) NOT NULL,
214 seats_available INT NOT NULL,
215 is_recurring BOOLEAN NOT NULL DEFAULT FALSE,
216 recurrence_days VARCHAR(50) NULL,
217 recurrence_end_date DATE NULL,
218 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
219 CONSTRAINT chk_rides_status CHECK (
220 status IN ('scheduled', 'in_progress', 'completed', 'cancelled')
221 ),
222 CONSTRAINT chk_rides_price CHECK (price_per_km > 0),
223 CONSTRAINT chk_rides_seats CHECK (seats_available >= 0),
224 CONSTRAINT chk_rides_recurrence CHECK (
225 is_recurring = FALSE
226 OR (
227 is_recurring = TRUE
228 AND recurrence_days IS NOT NULL
229 AND recurrence_end_date IS NOT NULL
230 )
231 ),
232 CONSTRAINT chk_rides_recurrence_end CHECK (
233 recurrence_end_date IS NULL
234 OR recurrence_end_date > departure_time::DATE
235 ),
236 CONSTRAINT chk_rides_created_before_departure CHECK (created_at <= departure_time),
237 FOREIGN KEY (driver_id) REFERENCES drivers(id)
238 ON DELETE RESTRICT,
239 FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
240 ON DELETE RESTRICT,
241 FOREIGN KEY (route_id) REFERENCES routes(id)
242 ON DELETE RESTRICT
243);
244
245CREATE TABLE route_segments (
246 id SERIAL PRIMARY KEY,
247 ride_id INT NOT NULL,
248 from_stop_id INT NOT NULL,
249 to_stop_id INT NOT NULL,
250 distance_km DECIMAL(8,2) NOT NULL,
251 segment_order INT NOT NULL,
252 CONSTRAINT chk_route_segments_distance CHECK (distance_km > 0),
253 CONSTRAINT chk_route_segments_order CHECK (segment_order >= 0),
254 CONSTRAINT chk_route_segments_stops CHECK (from_stop_id != to_stop_id),
255 CONSTRAINT uq_route_segments_order UNIQUE (ride_id, segment_order),
256 FOREIGN KEY (ride_id) REFERENCES rides(id)
257 ON DELETE RESTRICT,
258 FOREIGN KEY (from_stop_id) REFERENCES route_stops(id)
259 ON DELETE RESTRICT,
260 FOREIGN KEY (to_stop_id) REFERENCES route_stops(id)
261 ON DELETE RESTRICT
262);
263
264CREATE TABLE ride_status_history (
265 id SERIAL PRIMARY KEY,
266 ride_id INT NOT NULL,
267 status VARCHAR(20) NOT NULL,
268 changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
269 lat DECIMAL(10,7) NULL,
270 lng DECIMAL(10,7) NULL,
271 CONSTRAINT chk_ride_status_history_status CHECK (
272 status IN ('scheduled', 'in_progress', 'completed', 'cancelled')
273 ),
274 CONSTRAINT chk_ride_status_history_lat CHECK (lat IS NULL OR lat BETWEEN -90 AND 90),
275 CONSTRAINT chk_ride_status_history_lng CHECK (lng IS NULL OR lng BETWEEN -180 AND 180),
276 FOREIGN KEY (ride_id) REFERENCES rides(id)
277 ON DELETE RESTRICT
278);
279
280
281-- РЕЗЕРВАЦИИ
282
283CREATE TABLE bookings (
284 id SERIAL PRIMARY KEY,
285 ride_id INT NOT NULL,
286 passenger_id INT NOT NULL,
287 pickup_stop_id INT NOT NULL,
288 dropoff_stop_id INT NOT NULL,
289 status VARCHAR(20) NOT NULL DEFAULT 'pending',
290 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
291 pickup_confirmed_at TIMESTAMP NULL,
292 dropoff_confirmed_at TIMESTAMP NULL,
293 CONSTRAINT chk_bookings_status CHECK (
294 status IN ('pending', 'confirmed', 'picked_up', 'completed', 'cancelled')
295 ),
296 CONSTRAINT chk_bookings_stops CHECK (pickup_stop_id != dropoff_stop_id),
297 CONSTRAINT chk_bookings_dropoff_after CHECK (
298 dropoff_confirmed_at IS NULL
299 OR pickup_confirmed_at IS NULL
300 OR dropoff_confirmed_at > pickup_confirmed_at
301 ),
302 CONSTRAINT chk_bookings_pickup_after_created CHECK (
303 pickup_confirmed_at IS NULL
304 OR pickup_confirmed_at >= created_at
305 ),
306 CONSTRAINT uq_bookings_passenger_ride UNIQUE (ride_id, passenger_id),
307 FOREIGN KEY (ride_id) REFERENCES rides(id)
308 ON DELETE RESTRICT,
309 FOREIGN KEY (passenger_id) REFERENCES passengers(id)
310 ON DELETE RESTRICT,
311 FOREIGN KEY (pickup_stop_id) REFERENCES route_stops(id)
312 ON DELETE RESTRICT,
313 FOREIGN KEY (dropoff_stop_id) REFERENCES route_stops(id)
314 ON DELETE RESTRICT
315);
316
317CREATE TABLE booking_status_history (
318 id SERIAL PRIMARY KEY,
319 booking_id INT NOT NULL,
320 status VARCHAR(20) NOT NULL,
321 changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
322 reason VARCHAR(300) NULL,
323 CONSTRAINT chk_booking_status_history_status CHECK (
324 status IN ('pending', 'confirmed', 'picked_up', 'completed', 'cancelled')
325 ),
326 FOREIGN KEY (booking_id) REFERENCES bookings(id)
327 ON DELETE RESTRICT
328);
329
330
331-- ЦЕНА И ПАТАРИНИ
332
333CREATE TABLE passenger_segments (
334 id SERIAL PRIMARY KEY,
335 booking_id INT NOT NULL,
336 segment_id INT NOT NULL,
337 is_present BOOLEAN NOT NULL DEFAULT TRUE,
338 segment_cost DECIMAL(10,2) NOT NULL,
339 passengers_on_segment INT NOT NULL,
340 amount_charged DECIMAL(10,2) NOT NULL,
341 CONSTRAINT chk_passenger_segments_cost CHECK (segment_cost >= 0),
342 CONSTRAINT chk_passenger_segments_passengers CHECK (passengers_on_segment >= 1),
343 CONSTRAINT chk_passenger_segments_charged CHECK (amount_charged >= 0),
344 CONSTRAINT chk_passenger_segments_charged_le CHECK (amount_charged <= segment_cost),
345 CONSTRAINT uq_passenger_segments UNIQUE (booking_id, segment_id),
346 FOREIGN KEY (booking_id) REFERENCES bookings(id)
347 ON DELETE RESTRICT,
348 FOREIGN KEY (segment_id) REFERENCES route_segments(id)
349 ON DELETE RESTRICT
350);
351
352CREATE TABLE booking_final_fare (
353 id SERIAL PRIMARY KEY,
354 booking_id INT NOT NULL UNIQUE,
355 total_amount DECIMAL(10,2) NOT NULL,
356 calculated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
357 CONSTRAINT chk_booking_final_fare_amount CHECK (total_amount >= 0),
358 FOREIGN KEY (booking_id) REFERENCES bookings(id)
359 ON DELETE RESTRICT
360);
361
362CREATE TABLE toll_points (
363 id SERIAL PRIMARY KEY,
364 name VARCHAR(150) NOT NULL UNIQUE,
365 lat DECIMAL(10,7) NOT NULL,
366 lng DECIMAL(10,7) NOT NULL,
367 road_name VARCHAR(150) NULL,
368 price_car DECIMAL(8,2) NOT NULL,
369 price_van DECIMAL(8,2) NOT NULL,
370 CONSTRAINT chk_toll_points_lat CHECK (lat BETWEEN -90 AND 90),
371 CONSTRAINT chk_toll_points_lng CHECK (lng BETWEEN -180 AND 180),
372 CONSTRAINT chk_toll_points_price_car CHECK (price_car >= 0),
373 CONSTRAINT chk_toll_points_price_van CHECK (price_van >= 0),
374 CONSTRAINT chk_toll_points_name CHECK (LENGTH(TRIM(name)) > 0)
375);
376
377CREATE TABLE ride_tolls (
378 id SERIAL PRIMARY KEY,
379 ride_id INT NOT NULL,
380 toll_point_id INT NOT NULL,
381 actual_amount_paid DECIMAL(8,2) NOT NULL,
382 passed_at TIMESTAMP NOT NULL,
383 CONSTRAINT chk_ride_tolls_amount CHECK (actual_amount_paid >= 0),
384 CONSTRAINT uq_ride_tolls UNIQUE (ride_id, toll_point_id),
385 FOREIGN KEY (ride_id) REFERENCES rides(id)
386 ON DELETE RESTRICT,
387 FOREIGN KEY (toll_point_id) REFERENCES toll_points(id)
388 ON DELETE RESTRICT
389);
390
391CREATE TABLE toll_passenger_split (
392 id SERIAL PRIMARY KEY,
393 ride_toll_id INT NOT NULL,
394 booking_id INT NOT NULL,
395 amount_due DECIMAL(8,2) NOT NULL,
396 CONSTRAINT chk_toll_passenger_split_amount CHECK (amount_due >= 0),
397 CONSTRAINT uq_toll_passenger_split UNIQUE (ride_toll_id, booking_id),
398 FOREIGN KEY (ride_toll_id) REFERENCES ride_tolls(id)
399 ON DELETE RESTRICT,
400 FOREIGN KEY (booking_id) REFERENCES bookings(id)
401 ON DELETE RESTRICT
402);
403
404
405--ОЦЕНУВАЊЕ, ЧАТ И ИНЦИДЕНТИ
406
407CREATE TABLE ratings (
408 id SERIAL PRIMARY KEY,
409 reviewer_user_id INT NOT NULL DEFAULT 0,
410 reviewee_user_id INT NOT NULL DEFAULT 0,
411 ride_id INT NOT NULL,
412 score INT NOT NULL,
413 comment TEXT NULL,
414 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
415 CONSTRAINT chk_ratings_score CHECK (score BETWEEN 1 AND 5),
416 CONSTRAINT chk_ratings_no_self CHECK (reviewer_user_id != reviewee_user_id),
417 CONSTRAINT uq_ratings_per_ride UNIQUE (reviewer_user_id, reviewee_user_id, ride_id),
418 FOREIGN KEY (reviewer_user_id) REFERENCES users(id)
419 ON DELETE SET DEFAULT,
420 FOREIGN KEY (reviewee_user_id) REFERENCES users(id)
421 ON DELETE SET DEFAULT,
422 FOREIGN KEY (ride_id) REFERENCES rides(id)
423 ON DELETE RESTRICT
424);
425
426-- VIEW за пресметка на просечна оцена на возачи
427CREATE VIEW driver_ratings AS
428SELECT
429 d.id AS driver_id,
430 u.name AS driver_name,
431 ROUND(AVG(r.score)::NUMERIC, 2) AS avg_rating,
432 COUNT(r.id) AS total_ratings
433FROM drivers d
434JOIN users u ON u.id = d.user_id
435LEFT JOIN ratings r ON r.reviewee_user_id = d.user_id
436GROUP BY d.id, u.name;
437
438-- VIEW за пресметка на просечна оцена на патници
439CREATE VIEW passenger_ratings AS
440SELECT
441 p.id AS passenger_id,
442 u.name AS passenger_name,
443 ROUND(AVG(r.score)::NUMERIC, 2) AS avg_rating,
444 COUNT(r.id) AS total_ratings
445FROM passengers p
446JOIN users u ON u.id = p.user_id
447LEFT JOIN ratings r ON r.reviewee_user_id = p.user_id
448GROUP BY p.id, u.name;
449
450
451CREATE TABLE chat_threads (
452 id SERIAL PRIMARY KEY,
453 ride_id INT NOT NULL,
454 participant_1_id INT NOT NULL DEFAULT 0,
455 participant_2_id INT NOT NULL DEFAULT 0,
456 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
457 CONSTRAINT chk_chat_threads_no_self CHECK (participant_1_id != participant_2_id),
458 CONSTRAINT uq_chat_threads UNIQUE (ride_id, participant_1_id, participant_2_id),
459 FOREIGN KEY (ride_id) REFERENCES rides(id)
460 ON DELETE RESTRICT,
461 FOREIGN KEY (participant_1_id) REFERENCES users(id)
462 ON DELETE SET DEFAULT,
463 FOREIGN KEY (participant_2_id) REFERENCES users(id)
464 ON DELETE SET DEFAULT
465);
466
467CREATE TABLE chat_messages (
468 id SERIAL PRIMARY KEY,
469 thread_id INT NOT NULL,
470 sender_id INT NOT NULL DEFAULT 0,
471 content TEXT NOT NULL,
472 sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
473 is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
474 CONSTRAINT chk_chat_messages_content CHECK (LENGTH(TRIM(content)) > 0),
475 FOREIGN KEY (thread_id) REFERENCES chat_threads(id)
476 ON DELETE RESTRICT,
477 FOREIGN KEY (sender_id) REFERENCES users(id)
478 ON DELETE SET DEFAULT
479);
480
481CREATE TABLE incident_reports (
482 id SERIAL PRIMARY KEY,
483 ride_id INT NOT NULL,
484 reporter_id INT NOT NULL DEFAULT 0,
485 type VARCHAR(50) NOT NULL,
486 description TEXT NOT NULL,
487 reported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
488 CONSTRAINT chk_incident_reports_type CHECK (
489 type IN ('accident', 'reckless_driving', 'harassment', 'no_show', 'fraud', 'other')
490 ),
491 CONSTRAINT chk_incident_reports_description CHECK (LENGTH(TRIM(description)) > 0),
492 FOREIGN KEY (ride_id) REFERENCES rides(id)
493 ON DELETE RESTRICT,
494 FOREIGN KEY (reporter_id) REFERENCES users(id)
495 ON DELETE SET DEFAULT
496);
497
498
499-- LIVE СЛЕДЕЊЕ
500
501CREATE TABLE location_pings (
502 id SERIAL PRIMARY KEY,
503 ride_id INT NOT NULL,
504 user_id INT NOT NULL DEFAULT 0,
505 lat DECIMAL(10,7) NOT NULL,
506 lng DECIMAL(10,7) NOT NULL,
507 pinged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
508 speed DECIMAL(6,2) NULL,
509 CONSTRAINT chk_location_pings_lat CHECK (lat BETWEEN -90 AND 90),
510 CONSTRAINT chk_location_pings_lng CHECK (lng BETWEEN -180 AND 180),
511 CONSTRAINT chk_location_pings_speed CHECK (speed IS NULL OR speed >= 0),
512 FOREIGN KEY (ride_id) REFERENCES rides(id)
513 ON DELETE RESTRICT,
514 FOREIGN KEY (user_id) REFERENCES users(id)
515 ON DELETE SET DEFAULT
516);
517
518
519-- СИСТЕМ
520
521CREATE TABLE notifications (
522 id SERIAL PRIMARY KEY,
523 user_id INT NOT NULL DEFAULT 0,
524 type VARCHAR(50) NOT NULL,
525 title VARCHAR(200) NOT NULL,
526 body TEXT NOT NULL,
527 read_at TIMESTAMP NULL,
528 sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
529 CONSTRAINT chk_notifications_type CHECK (
530 type IN ('booking', 'ride', 'chat', 'rating', 'system', 'incident')
531 ),
532 CONSTRAINT chk_notifications_read_at CHECK (read_at IS NULL OR read_at >= sent_at),
533 CONSTRAINT chk_notifications_title CHECK (LENGTH(TRIM(title)) > 0),
534 FOREIGN KEY (user_id) REFERENCES users(id)
535 ON DELETE SET DEFAULT
536);
537
538CREATE TABLE audit_logs (
539 id SERIAL PRIMARY KEY,
540 table_name VARCHAR(100) NOT NULL,
541 record_id INT NOT NULL,
542 operation VARCHAR(10) NOT NULL,
543 old_data TEXT NULL,
544 new_data TEXT NULL,
545 changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
546 changed_by INT NULL DEFAULT 0,
547 CONSTRAINT chk_audit_logs_operation CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
548 CONSTRAINT chk_audit_logs_table_name CHECK (LENGTH(TRIM(table_name)) > 0),
549 FOREIGN KEY (changed_by) REFERENCES users(id)
550 ON DELETE SET DEFAULT
551);