DatabaseCreation: ddl.2.sql

File ddl.2.sql, 39.9 KB (added by 231138, 7 days 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);
552
553-- ============================================================
554-- DriveNet — APPLICATION VIEWS
555-- Target DB : drive_net / advdb_202526l_prj_drive_net
556-- Views : 9
557--
558-- 1. v_available_rides — ride-search results
559-- 2. v_driver_profile — driver card with stats & vehicle
560-- 3. v_passenger_trip_history — passenger's full booking history
561-- 4. v_ride_manifest — all passengers on a ride (driver view)
562-- 5. v_driver_earnings — monthly revenue breakdown per driver
563-- 6. v_route_popularity — most-booked routes ranking
564-- 7. v_booking_details — single-booking detail (passenger view)
565-- 8. v_unread_notifications — unread notification inbox per user
566-- 9. v_incident_summary — incident reports with full ride context
567-- ============================================================
568
569
570-- ============================================================
571-- 1. v_available_rides
572-- Purpose : Ride-search screen — returns every scheduled ride
573-- with driver info, vehicle, route, and price estimate.
574-- Used by : Passenger "Find a ride" feature.
575-- ============================================================
576CREATE OR REPLACE VIEW v_available_rides AS
577SELECT
578 r.id AS ride_id,
579 r.departure_time,
580 r.seats_available,
581 r.price_per_km,
582 r.is_recurring,
583 -- driver
584 d.id AS driver_id,
585 ud.name AS driver_name,
586 ROUND(AVG(rt.score)::numeric, 2) AS driver_avg_rating,
587 COUNT(rt.id) AS driver_rating_count,
588 -- vehicle
589 mfr.name || ' ' || vm.model_name AS vehicle,
590 v.year AS vehicle_year,
591 v.color,
592 v.seats AS vehicle_total_seats,
593 -- route
594 orig_city.name AS origin_city,
595 orig.name AS origin_location,
596 dest_city.name AS destination_city,
597 dest.name AS destination_location,
598 ro.distance_km,
599 ro.estimated_duration AS duration_mins,
600 ROUND((ro.distance_km * r.price_per_km)::numeric, 2) AS estimated_fare
601FROM rides r
602JOIN drivers d ON d.id = r.driver_id
603JOIN users ud ON ud.id = d.user_id
604JOIN routes ro ON ro.id = r.route_id
605JOIN locations orig ON orig.id = ro.origin_id
606JOIN cities orig_city ON orig_city.id = orig.city_id
607JOIN locations dest ON dest.id = ro.destination_id
608JOIN cities dest_city ON dest_city.id = dest.city_id
609JOIN vehicles v ON v.id = r.vehicle_id
610JOIN vehicle_models vm ON vm.id = v.model_id
611JOIN manufacturers mfr ON mfr.id = vm.manufacturer_id
612LEFT JOIN ratings rt ON rt.reviewee_user_id = ud.id
613WHERE r.status = 'scheduled'
614 AND ud.deleted_at IS NULL
615GROUP BY
616 r.id, r.departure_time, r.seats_available, r.price_per_km, r.is_recurring,
617 d.id, ud.name,
618 mfr.name, vm.model_name, v.year, v.color, v.seats,
619 orig_city.name, orig.name, dest_city.name, dest.name,
620 ro.distance_km, ro.estimated_duration;
621
622
623-- ============================================================
624-- 2. v_driver_profile
625-- Purpose : Driver profile card — identity, license, primary
626-- vehicle, ride counts and aggregate rating.
627-- Used by : Driver profile page; passenger "view driver" modal.
628-- ============================================================
629CREATE OR REPLACE VIEW v_driver_profile AS
630SELECT
631 d.id AS driver_id,
632 ud.id AS user_id,
633 ud.name,
634 ud.email,
635 ud.phone,
636 ud.is_verified,
637 d.license_number,
638 d.license_class,
639 d.license_expiry,
640 d.status AS driver_status,
641 d.verified_at,
642 -- primary active vehicle (lowest vehicle_id when multiple)
643 COALESCE(mfr.name || ' ' || vm.model_name, 'No vehicle') AS primary_vehicle,
644 v.year AS vehicle_year,
645 v.color,
646 v.license_plate,
647 -- ride stats
648 COUNT(DISTINCT r.id) AS total_rides,
649 COUNT(DISTINCT r.id) FILTER (WHERE r.status = 'completed') AS completed_rides,
650 COUNT(DISTINCT r.id) FILTER (WHERE r.status = 'cancelled') AS cancelled_rides,
651 -- rating stats
652 ROUND(AVG(rt.score)::numeric, 2) AS avg_rating,
653 COUNT(rt.id) AS rating_count
654FROM drivers d
655JOIN users ud ON ud.id = d.user_id
656-- one active vehicle per driver
657LEFT JOIN LATERAL (
658 SELECT vo2.vehicle_id
659 FROM vehicle_ownership vo2
660 WHERE vo2.driver_id = d.id
661 AND vo2.is_active = TRUE
662 ORDER BY vo2.vehicle_id
663 LIMIT 1
664) pv ON TRUE
665LEFT JOIN vehicles v ON v.id = pv.vehicle_id
666LEFT JOIN vehicle_models vm ON vm.id = v.model_id
667LEFT JOIN manufacturers mfr ON mfr.id = vm.manufacturer_id
668LEFT JOIN rides r ON r.driver_id = d.id
669LEFT JOIN ratings rt ON rt.reviewee_user_id = ud.id
670WHERE ud.deleted_at IS NULL
671GROUP BY
672 d.id, ud.id, ud.name, ud.email, ud.phone, ud.is_verified,
673 d.license_number, d.license_class, d.license_expiry, d.status, d.verified_at,
674 mfr.name, vm.model_name, v.year, v.color, v.license_plate;
675
676
677-- ============================================================
678-- 3. v_passenger_trip_history
679-- Purpose : Full booking history for every passenger — status,
680-- route, driver name and final fare paid.
681-- Used by : Passenger "My trips" screen.
682-- ============================================================
683CREATE OR REPLACE VIEW v_passenger_trip_history AS
684SELECT
685 b.id AS booking_id,
686 b.ride_id,
687 -- passenger
688 up.id AS passenger_user_id,
689 up.name AS passenger_name,
690 -- ride
691 r.departure_time,
692 r.status AS ride_status,
693 b.status AS booking_status,
694 -- route
695 orig_city.name AS origin_city,
696 dest_city.name AS destination_city,
697 ro.distance_km,
698 -- driver
699 ud.name AS driver_name,
700 ud.phone AS driver_phone,
701 -- timing
702 b.created_at AS booked_at,
703 b.pickup_confirmed_at,
704 b.dropoff_confirmed_at,
705 -- fare
706 bff.total_amount AS fare_paid,
707 -- rating given by this passenger for this ride
708 rt.score AS rating_given
709FROM bookings b
710JOIN passengers p ON p.id = b.passenger_id
711JOIN users up ON up.id = p.user_id
712JOIN rides r ON r.id = b.ride_id
713JOIN drivers d ON d.id = r.driver_id
714JOIN users ud ON ud.id = d.user_id
715JOIN routes ro ON ro.id = r.route_id
716JOIN locations orig ON orig.id = ro.origin_id
717JOIN cities orig_city ON orig_city.id = orig.city_id
718JOIN locations dest ON dest.id = ro.destination_id
719JOIN cities dest_city ON dest_city.id = dest.city_id
720LEFT JOIN booking_final_fare bff ON bff.booking_id = b.id
721LEFT JOIN ratings rt ON rt.reviewer_user_id = up.id
722 AND rt.ride_id = b.ride_id
723WHERE up.deleted_at IS NULL;
724
725
726-- ============================================================
727-- 4. v_ride_manifest
728-- Purpose : All active passengers on each ride with pickup /
729-- drop-off locations and contact details.
730-- Used by : Driver "My passengers" screen before/during a ride.
731-- ============================================================
732CREATE OR REPLACE VIEW v_ride_manifest AS
733SELECT
734 r.id AS ride_id,
735 r.departure_time,
736 r.status AS ride_status,
737 -- route
738 orig_city.name AS origin_city,
739 dest_city.name AS destination_city,
740 -- booking
741 b.id AS booking_id,
742 b.status AS booking_status,
743 -- passenger
744 up.id AS passenger_user_id,
745 up.name AS passenger_name,
746 up.phone AS passenger_phone,
747 -- stop names
748 pup_loc.name AS pickup_location,
749 pdrop_loc.name AS dropoff_location,
750 -- fare
751 bff.total_amount AS fare
752FROM rides r
753JOIN routes ro ON ro.id = r.route_id
754JOIN locations orig ON orig.id = ro.origin_id
755JOIN cities orig_city ON orig_city.id = orig.city_id
756JOIN locations dest ON dest.id = ro.destination_id
757JOIN cities dest_city ON dest_city.id = dest.city_id
758JOIN bookings b ON b.ride_id = r.id
759JOIN passengers p ON p.id = b.passenger_id
760JOIN users up ON up.id = p.user_id
761JOIN route_stops pup_stop ON pup_stop.id = b.pickup_stop_id
762JOIN locations pup_loc ON pup_loc.id = pup_stop.location_id
763JOIN route_stops pdrop_stop ON pdrop_stop.id = b.dropoff_stop_id
764JOIN locations pdrop_loc ON pdrop_loc.id = pdrop_stop.location_id
765LEFT JOIN booking_final_fare bff ON bff.booking_id = b.id
766WHERE b.status IN ('confirmed', 'picked_up', 'completed')
767 AND up.deleted_at IS NULL;
768
769
770-- ============================================================
771-- 5. v_driver_earnings
772-- Purpose : Monthly revenue per driver from completed bookings.
773-- Used by : Driver "Earnings" dashboard; finance reports.
774-- ============================================================
775CREATE OR REPLACE VIEW v_driver_earnings AS
776SELECT
777 d.id AS driver_id,
778 ud.name AS driver_name,
779 DATE_TRUNC('month', r.departure_time) AS month,
780 COUNT(DISTINCT r.id) AS completed_rides,
781 COUNT(b.id) AS paid_bookings,
782 ROUND(SUM(bff.total_amount)::numeric, 2) AS gross_revenue,
783 ROUND(AVG(bff.total_amount)::numeric, 2) AS avg_fare_per_booking,
784 ROUND(MAX(bff.total_amount)::numeric, 2) AS max_fare,
785 ROUND(MIN(bff.total_amount)::numeric, 2) AS min_fare
786FROM drivers d
787JOIN users ud ON ud.id = d.user_id
788JOIN rides r ON r.driver_id = d.id
789 AND r.status = 'completed'
790JOIN bookings b ON b.ride_id = r.id
791 AND b.status = 'completed'
792JOIN booking_final_fare bff ON bff.booking_id = b.id
793WHERE ud.deleted_at IS NULL
794GROUP BY
795 d.id, ud.name,
796 DATE_TRUNC('month', r.departure_time);
797
798
799-- ============================================================
800-- 6. v_route_popularity
801-- Purpose : Routes ranked by total bookings — useful for
802-- surfacing top-demand corridors and pricing hints.
803-- Used by : Home-screen "Popular routes" widget; analytics.
804-- ============================================================
805CREATE OR REPLACE VIEW v_route_popularity AS
806SELECT
807 ro.id AS route_id,
808 orig_city.name AS origin_city,
809 orig.name AS origin_location,
810 dest_city.name AS destination_city,
811 dest.name AS destination_location,
812 ro.distance_km,
813 ro.estimated_duration AS duration_mins,
814 COUNT(DISTINCT r.id) AS total_rides,
815 COUNT(b.id) AS total_bookings,
816 COUNT(b.id) FILTER (WHERE b.status = 'completed') AS completed_bookings,
817 ROUND(AVG(bff.total_amount)::numeric, 2) AS avg_fare,
818 ROUND(AVG(r.price_per_km)::numeric, 4) AS avg_price_per_km
819FROM routes ro
820JOIN locations orig ON orig.id = ro.origin_id
821JOIN cities orig_city ON orig_city.id = orig.city_id
822JOIN locations dest ON dest.id = ro.destination_id
823JOIN cities dest_city ON dest_city.id = dest.city_id
824LEFT JOIN rides r ON r.route_id = ro.id
825LEFT JOIN bookings b ON b.ride_id = r.id
826LEFT JOIN booking_final_fare bff ON bff.booking_id = b.id
827GROUP BY
828 ro.id, orig_city.name, orig.name, dest_city.name, dest.name,
829 ro.distance_km, ro.estimated_duration
830ORDER BY total_bookings DESC;
831
832
833-- ============================================================
834-- 7. v_booking_details
835-- Purpose : Complete single-booking record — passenger, driver,
836-- vehicle, pickup/drop-off stops, and fare.
837-- Used by : Booking confirmation screen; receipt / PDF export.
838-- ============================================================
839CREATE OR REPLACE VIEW v_booking_details AS
840SELECT
841 b.id AS booking_id,
842 b.status AS booking_status,
843 b.created_at AS booked_at,
844 b.pickup_confirmed_at,
845 b.dropoff_confirmed_at,
846 -- passenger
847 up.id AS passenger_user_id,
848 up.name AS passenger_name,
849 up.phone AS passenger_phone,
850 -- driver
851 ud.id AS driver_user_id,
852 ud.name AS driver_name,
853 ud.phone AS driver_phone,
854 -- ride
855 r.id AS ride_id,
856 r.departure_time,
857 r.status AS ride_status,
858 r.price_per_km,
859 -- vehicle
860 mfr.name || ' ' || vm.model_name AS vehicle,
861 v.license_plate,
862 v.color,
863 -- stops
864 pup_loc.name AS pickup_location,
865 orig_city.name AS pickup_city,
866 drop_loc.name AS dropoff_location,
867 dest_city.name AS dropoff_city,
868 ro.distance_km,
869 -- fare
870 bff.total_amount AS fare_paid,
871 bff.calculated_at AS fare_calculated_at
872FROM bookings b
873JOIN passengers p ON p.id = b.passenger_id
874JOIN users up ON up.id = p.user_id
875JOIN rides r ON r.id = b.ride_id
876JOIN drivers d ON d.id = r.driver_id
877JOIN users ud ON ud.id = d.user_id
878JOIN vehicles v ON v.id = r.vehicle_id
879JOIN vehicle_models vm ON vm.id = v.model_id
880JOIN manufacturers mfr ON mfr.id = vm.manufacturer_id
881JOIN routes ro ON ro.id = r.route_id
882JOIN locations orig ON orig.id = ro.origin_id
883JOIN cities orig_city ON orig_city.id = orig.city_id
884JOIN locations dest ON dest.id = ro.destination_id
885JOIN cities dest_city ON dest_city.id = dest.city_id
886JOIN route_stops pup_stop ON pup_stop.id = b.pickup_stop_id
887JOIN locations pup_loc ON pup_loc.id = pup_stop.location_id
888JOIN route_stops drop_stop ON drop_stop.id = b.dropoff_stop_id
889JOIN locations drop_loc ON drop_loc.id = drop_stop.location_id
890LEFT JOIN booking_final_fare bff ON bff.booking_id = b.id;
891
892
893-- ============================================================
894-- 8. v_unread_notifications
895-- Purpose : All unread notifications with user context.
896-- Used by : Notification bell / inbox in the mobile app.
897-- ============================================================
898CREATE OR REPLACE VIEW v_unread_notifications AS
899SELECT
900 n.id AS notification_id,
901 n.user_id,
902 u.name AS user_name,
903 n.type,
904 n.title,
905 n.body,
906 n.sent_at,
907 -- age in minutes so the app can show "5 min ago"
908 EXTRACT(EPOCH FROM (NOW() - n.sent_at)) / 60 AS age_mins
909FROM notifications n
910JOIN users u ON u.id = n.user_id
911WHERE n.read_at IS NULL
912 AND u.deleted_at IS NULL;
913
914
915-- ============================================================
916-- 9. v_incident_summary
917-- Purpose : All incident reports enriched with ride, reporter
918-- and driver details for the admin safety dashboard.
919-- Used by : Admin "Incidents" panel; safety team review.
920-- ============================================================
921CREATE OR REPLACE VIEW v_incident_summary AS
922SELECT
923 ir.id AS incident_id,
924 ir.type AS incident_type,
925 ir.description,
926 ir.reported_at,
927 -- reporter (passenger)
928 u_rep.id AS reporter_user_id,
929 u_rep.name AS reporter_name,
930 u_rep.phone AS reporter_phone,
931 -- ride
932 r.id AS ride_id,
933 r.departure_time,
934 r.status AS ride_status,
935 -- driver
936 u_drv.id AS driver_user_id,
937 u_drv.name AS driver_name,
938 u_drv.phone AS driver_phone,
939 d.license_number AS driver_license,
940 -- route
941 orig_city.name AS origin_city,
942 dest_city.name AS destination_city,
943 ro.distance_km
944FROM incident_reports ir
945JOIN users u_rep ON u_rep.id = ir.reporter_id
946JOIN rides r ON r.id = ir.ride_id
947JOIN drivers d ON d.id = r.driver_id
948JOIN users u_drv ON u_drv.id = d.user_id
949JOIN routes ro ON ro.id = r.route_id
950JOIN locations orig ON orig.id = ro.origin_id
951JOIN cities orig_city ON orig_city.id = orig.city_id
952JOIN locations dest ON dest.id = ro.destination_id
953JOIN cities dest_city ON dest_city.id = dest.city_id
954WHERE u_rep.deleted_at IS NULL;