= Database Creation = === Имплементациски детали === Во овој проект, преку DDL наредби се креирани сите потребни табели за системот за управување со такси апликација, како и нивните меѓусебни релации. Во скриптите се опфатени: * примарни клучеви (PRIMARY KEY) * надворешни клучеви (FOREIGN KEY) * ограничувања за интегритет (CHECK, UNIQUE, NOT NULL) * правила за ажурирање и бришење (ON DELETE, ON UPDATE) Со овие DDL наредби се обезбедува конзистентност, интегритет и правилна организација на податоците во базата. === DDL Скрипти === {{{#!sql CREATE TABLE Status ( status_id SERIAL PRIMARY KEY, status_tip VARCHAR(255) NOT NULL, status_text VARCHAR(255) NOT NULL ); CREATE TABLE Vehicles_model ( Vehicles_model_id SERIAL PRIMARY KEY, Model VARCHAR(255) NOT NULL ); CREATE TABLE Locations ( location_id SERIAL PRIMARY KEY, latitude NUMERIC(9,6) NOT NULL, longitude NUMERIC(9,6) NOT NULL, grad VARCHAR(255) NOT NULL, naselba VARCHAR(255) NOT NULL, ulica VARCHAR(255) NOT NULL, broj VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL ); CREATE TABLE Users ( User_id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, username VARCHAR(255) NOT NULL UNIQUE, phone VARCHAR(255) NOT NULL UNIQUE, email VARCHAR(255) NOT NULL UNIQUE, password_hash TEXT NOT NULL, created_at TIMESTAMP NOT NULL, status_id INT NOT NULL, CONSTRAINT chk_users_phone CHECK (phone ~ '^[0-9+]+$'), CONSTRAINT chk_users_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'), CONSTRAINT FK_Users_Status FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE Drivers ( driver_id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, phone VARCHAR(255) NOT NULL UNIQUE, license_number VARCHAR(255) NOT NULL UNIQUE, registration_date DATE NOT NULL, rating NUMERIC(3,2) NOT NULL, status_id INT NOT NULL, CONSTRAINT chk_drivers_phone CHECK (phone ~ '^[0-9+]+$'), CONSTRAINT chk_drivers_rating CHECK (rating BETWEEN 0 AND 5), CONSTRAINT FK_Drivers_Status FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE Admins ( admins_id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, username VARCHAR(255) NOT NULL UNIQUE, phone VARCHAR(255) NOT NULL UNIQUE, email VARCHAR(255) NOT NULL UNIQUE, password_hash TEXT NOT NULL, created_at TIMESTAMP NOT NULL, status_id INT NOT NULL, CONSTRAINT chk_admins_phone CHECK (phone ~ '^[0-9+]+$'), CONSTRAINT chk_admins_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'), CONSTRAINT FK_Admins_Status FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE Vehicle_types ( vehicle_type_id SERIAL PRIMARY KEY, type_name VARCHAR(255) NOT NULL, description VARCHAR(255) NOT NULL, Vehicles_model_id INT NOT NULL, CONSTRAINT FK_Vehicle_types_Model FOREIGN KEY (Vehicles_model_id) REFERENCES Vehicles_model(Vehicles_model_id) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE Vehicle_ownership ( Vehicle_ownership_id SERIAL PRIMARY KEY, Plate VARCHAR(255) NOT NULL UNIQUE, Traffic_permit VARCHAR(255) NOT NULL UNIQUE, Color VARCHAR(255) NOT NULL, year INT NOT NULL, vehicle_type_id INT NOT NULL, CONSTRAINT chk_vehicle_year CHECK (year >= 1900), CONSTRAINT FK_Vehicle_ownership_Type FOREIGN KEY (vehicle_type_id) REFERENCES Vehicle_types(vehicle_type_id) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE Pricing_rules ( tariff_id SERIAL PRIMARY KEY, base_price NUMERIC(10,2) NOT NULL, price_per_km NUMERIC(10,2) NOT NULL, vehicle_type_id INT NOT NULL, CONSTRAINT chk_price CHECK (base_price >= 0 AND price_per_km >= 0), CONSTRAINT FK_Pricing_rules_Vehicle_types FOREIGN KEY (vehicle_type_id) REFERENCES Vehicle_types(vehicle_type_id) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE Drivers_Vehicle_ownership ( driver_id INT NOT NULL, Vehicle_ownership_id INT NOT NULL, PRIMARY KEY (driver_id, Vehicle_ownership_id), CONSTRAINT FK_DVO_Drivers FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE CASCADE, CONSTRAINT FK_DVO_Vehicles FOREIGN KEY (Vehicle_ownership_id) REFERENCES Vehicle_ownership(Vehicle_ownership_id) ON DELETE CASCADE ); CREATE TABLE Active_drivers ( driver_id INT PRIMARY KEY, current_location_id INT NOT NULL, is_available BOOLEAN NOT NULL, working_start TIMESTAMP NOT NULL, working_end TIMESTAMP NOT NULL, vehicle_ownership_id INT NOT NULL, CONSTRAINT FK_Active_drivers_Drivers FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE CASCADE, CONSTRAINT FK_Active_drivers_Locations FOREIGN KEY (current_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT, CONSTRAINT FK_Active_drivers_vehicle_ownership_id FOREIGN KEY (vehicle_ownership_id) REFERENCES Vehicle_ownership(Vehicle_ownership_id) ON DELETE RESTRICT ); CREATE TABLE Rides ( ride_id SERIAL PRIMARY KEY, user_id INT NOT NULL, driver_id INT NOT NULL, vehicle_ownership_id INT NOT NULL, pickup_location_id INT NOT NULL, dropoff_location_id INT NOT NULL, status_id INT NOT NULL, request_time TIMESTAMP NOT NULL, pickup_time TIMESTAMP NOT NULL, start_time TIMESTAMP NOT NULL, end_time TIMESTAMP NOT NULL, distance_km NUMERIC(6,2) NOT NULL, final_price NUMERIC(10,2) NOT NULL, discount_percentage NUMERIC(5,2) NOT NULL, CONSTRAINT chk_distance CHECK (distance_km >= 0), CONSTRAINT chk_discount CHECK (discount_percentage BETWEEN 0 AND 100), CONSTRAINT FK_Rides_Users FOREIGN KEY (user_id) REFERENCES Users(User_id) ON DELETE RESTRICT, CONSTRAINT FK_Rides_vehicle_ownership_id FOREIGN KEY (vehicle_ownership_id) REFERENCES Vehicle_ownership(Vehicle_ownership_id) ON DELETE RESTRICT, CONSTRAINT FK_Rides_Drivers FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE RESTRICT, CONSTRAINT FK_Rides_Pickup_Location FOREIGN KEY (pickup_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT, CONSTRAINT FK_Rides_Dropoff_Location FOREIGN KEY (dropoff_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT, CONSTRAINT FK_Rides_Status FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT ); CREATE TABLE Payment_methods( payment_method_id SERIAL PRIMARY KEY, payment_tip VARCHAR(255) NOT NULL ); CREATE TABLE Payments ( payment_id SERIAL PRIMARY KEY, amount NUMERIC(10,2) NOT NULL, payment_method_id INT NOT NULL, payment_time TIMESTAMP NOT NULL, ride_id INT NOT NULL, status_id INT NOT NULL, CONSTRAINT chk_amount CHECK (amount >= 0), CONSTRAINT FK_Payments_Rides FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE, CONSTRAINT FK_Payments_Status FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT, CONSTRAINT FK_Payments_Method FOREIGN KEY (payment_method_id) REFERENCES Payment_methods(payment_method_id) ON DELETE RESTRICT ); CREATE TABLE Ratings ( rating_id SERIAL PRIMARY KEY, score INT NOT NULL, comment VARCHAR(255), created_at TIMESTAMP NOT NULL, ride_id INT NOT NULL, User_id INT NOT NULL, driver_id INT NOT NULL, CONSTRAINT chk_score CHECK (score BETWEEN 1 AND 5), CONSTRAINT FK_Ratings_Rides FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE, CONSTRAINT FK_Ratings_Users FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE, CONSTRAINT FK_Ratings_Drivers FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE CASCADE ); CREATE TABLE Cancellations ( cancellation_id SERIAL PRIMARY KEY, cancelled_by_type VARCHAR(255) NOT NULL, cancelled_by_id INT NOT NULL, reason VARCHAR(255) NOT NULL, cancellation_fee NUMERIC(10,2) NOT NULL, created_at TIMESTAMP NOT NULL, ride_id INT NOT NULL, CONSTRAINT chk_fee CHECK (cancellation_fee >= 0), CONSTRAINT chk_cancelled_by_type CHECK (cancelled_by_type IN ('USER', 'DRIVER', 'ADMIN')), CONSTRAINT FK_Cancellations_Rides FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE ); CREATE TABLE Messages( message_id SERIAL PRIMARY KEY, message_text VARCHAR(255) NOT NULL ); CREATE TABLE Notifications ( notification_id SERIAL PRIMARY KEY, message_id INT NOT NULL, sent_time TIMESTAMP NOT NULL, User_id INT NOT NULL, ride_id INT NOT NULL, status_id INT NOT NULL, CONSTRAINT FK_Notifications_Users FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE, CONSTRAINT FK_Notifications_Rides FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE, CONSTRAINT FK_Notifications_Status FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT, CONSTRAINT FK_Notifications_Messages FOREIGN KEY (message_id) REFERENCES Messages(message_id) ON DELETE CASCADE ); }}} === DML Имплементација === DML скриптите се користат за внесување, генерирање и управување со податоците во базата на податоци. Во овој проект, преку DML наредби се пополнуваат табелите со иницијални и тест податоци потребни за функционирање и симулација на ride-sharing системот. Во скриптите се опфатени: внесување на почетни податоци (INSERT INTO) генерирање на големи количини тест податоци користење на SELECT, CASE, WITH и JOIN изрази за автоматско креирање податоци симулација на реални сценарија за возења, плаќања, откажувања и нотификации batch обработка и оптимизација за работа со милионски datasets === DML Скрипти === {{{#!sql INSERT INTO Status (status_tip, status_text) VALUES ('driver', 'active'), ('driver', 'inactive'); INSERT INTO Status (status_tip, status_text) VALUES ('user', 'active'), ('user', 'inactive'), ('user', 'blocked'), ('user', 'deleted'); INSERT INTO Status (status_tip, status_text) VALUES ('admin', 'active'), ('admin', 'inactive'), ('admin', 'blocked'); INSERT INTO Status (status_tip, status_text) VALUES ('ride', 'requested'), ('ride', 'accepted'), ('ride', 'in_progress'), ('ride', 'completed'), ('ride', 'cancelled'); INSERT INTO Status (status_tip, status_text) VALUES ('payment', 'pending'), ('payment', 'completed'), ('payment', 'failed'), ('payment', 'refunded'); INSERT INTO Payment_methods (payment_tip) VALUES ('CASH'), ('CARD'); INSERT INTO Messages (message_text) VALUES ('Vaseto baranje e uspesno isprateno'), ('Vozacot go prifati vaseto baranje'), ('Vozacot go odbi vaseto baranje'), ('Vozacot pristigna na lokacijata za podiganje'), ('Vozenjeto e otkazano od strana na vozacot'), ('Vozenjeto e otkazano od vasa strana'), ('Plakjanjeto e uspesno'), ('Plakjanjeto ne uspea'), ('Nemate dostapni vozaci vo momentot'); INSERT INTO Vehicles_model (Model) VALUES ('Volkswagen'), ('Skoda'), ('Toyota'), ('Opel'), ('Mercedes-Benz'), ('BMW'), ('Audi'), ('Hyundai'), ('Kia'), ('Renault'); INSERT INTO Vehicle_types (type_name, description, Vehicles_model_id) SELECT m.model_name, CASE WHEN m.model_name IN ('Golf','Octavia','Corolla','Astra','Polo','Fabia','Yaris','Corsa','Elantra','Clio', 'i30','Rio','Megane','Passat','Superb','Camry','Insignia','Tucson','Ceed','Captur') THEN 'Economy class' ELSE 'Luxury class' END as description, m.brand_id FROM ( SELECT vm.Vehicles_model_id as brand_id, unnest(models) as model_name FROM Vehicles_model vm JOIN (VALUES ('Volkswagen', ARRAY['Golf','Passat','Tiguan','Polo','Arteon']), ('Skoda', ARRAY['Octavia','Superb','Kodiaq','Fabia','Karoq']), ('Toyota', ARRAY['Corolla','Yaris','RAV4','Camry','C-HR']), ('Opel', ARRAY['Astra','Insignia','Corsa','Mokka','Grandland']), ('Mercedes-Benz', ARRAY['E-Class','C-Class','S-Class','GLE','CLA']), ('BMW', ARRAY['3 Series','5 Series','X5','X3','1 Series']), ('Audi', ARRAY['A4','A6','Q5','A3','Q7']), ('Hyundai', ARRAY['Elantra','Tucson','Santa Fe','i30','Kona']), ('Kia', ARRAY['Sportage','Ceed','Rio','Sorento','Niro']), ('Renault', ARRAY['Clio','Megane','Captur','Kadjar','Talisman']) ) AS t(brand, models) ON vm.Model = t.brand ) m; INSERT INTO Pricing_rules (base_price, price_per_km, vehicle_type_id) SELECT CASE WHEN description = 'Economy class' THEN 80.00 ELSE 100.00 END as base_price, CASE WHEN description = 'Economy class' THEN 40.00 ELSE 60.00 END as price_per_km, vehicle_type_id FROM Vehicle_types; INSERT INTO Locations (latitude, longitude, grad, naselba, ulica, broj, created_at) WITH street_data AS ( SELECT * FROM (VALUES ('Centar', 42.1322, 21.7144, ARRAY[ '11 Oktomvri', 'Goce Delchev', 'Tane Georgiev', 'Gorce Petrov', 'Plostad Marsal Tito', 'Done Bozhinov', 'Ljubo Atanasov', 'Ivo Lola Ribar', 'Leninova', 'Narodna Revolucija', 'Svetozar Markovic', 'Krste Misirkov', 'Jane Sandanski', 'Dragan Stoparevic', 'Bratstvo Edinstvo', 'Marsal Tito', 'JNA', 'Stiv Naumov', 'Guro Pucar Stari', 'Hristijan Karpos' ]), ('Zelen Rid', 42.1400, 21.7000, ARRAY[ 'Nikola Tesla', 'Zheleznichka', 'Vidoe Smilevski Bato', 'Vasil Glavinov', 'Sremska', 'Kozjak', 'Osogovska', 'Shar Planina', 'Belasica', 'Ruen', 'Prilepska', 'Bitolska', 'Ohridska', 'Strumicka', 'Vardar', 'Pelister', 'Skopska', 'Tetovska', 'Gevgeliska', 'Veleska' ]), ('Goce Delchev', 42.1250, 21.7250, ARRAY[ 'Treta Makedonska Udarna Brigada', 'Vera Kotorka', 'Boro Mikic', 'Filip Vtori', 'Srbo Tomovik', 'Zikica Jovanovic Spanac', 'Boro Menkov', 'Milan Zecar', 'Dositej Obradovic', 'Kuzman Josifovski Pitu', 'Tomaki Dimitrovski', 'Todor Velkov', 'Vasko Karangelevski', 'Niksicka', 'Pero Nakov', 'Bajram Shabani', 'Mite Bogoevski', 'Slavko Janevski', 'Gjorce Petrov', 'Kiro Fetak' ]), ('Pero Chicho', 42.1150, 21.7300, ARRAY[ 'Pero Chicho', '11 Noemvri', 'Boro Mendkov', 'Josip Pancic', 'Banatska', 'Moravska', 'Drinska', 'Sredorek', 'Vojvodina', 'Sumadinska', 'Toplicki Odred', 'Zajecarska', 'Niska', 'Vranjska', 'Leskovacka', 'Sremska', 'Prizrenska', 'Dakovicka', 'Pecka', 'Kosovska' ]), ('Karposh', 42.1200, 21.7500, ARRAY[ 'Oktomvriska Revolucija', 'Srecko Puzalka', 'Mito Hadzivasilev Jasmin', 'Sava Kovacevic', 'Karposova', 'Metodija Andonov Cento', 'ASNOM', 'Dame Gruev', 'Pitu Guli', 'Partizanska', 'Kiro Burnaz', 'Pere Tosev', 'Gjorce Petrov', 'Hristijan Todorovski', 'Miroslav Krleza', 'Blagoja Stevkovski', 'Kosta Racin', 'Koco Racin', 'Mirce Acev', 'Vasil Antevski' ]), ('Bedinje', 42.1500, 21.7100, ARRAY[ 'Srbo Tomovikj', 'Zhivko Tomovski', 'Guro Djakovic', 'Karaorman', 'Njegoseva', 'Ivan Milutinovic', 'Mojsa Pijade', 'Zheleznichka', 'Revolucionerna', 'Gjorce Petrov', 'Cede Filipovski', 'Bratstvo', 'Edinstvo', 'Prvomajska', 'Sloboda', 'Proleterska', 'Makedonska', 'Ilindenska', 'Vidoe Smilevski', 'Josip Pancic' ]), ('Ajducka Cheshma', 42.1480, 21.6950, ARRAY[ 'Bajram Shabani', 'Prilepska', 'Blagoja Stevkovski', 'Romanovska', 'Kumanovska', 'Debarska', 'Krusevska', 'Dojranska', 'Prespanska', 'Gevgeliska', 'Negotinska', 'Kavadarska', 'Radoviska', 'Berovska', 'Delcevska', 'Vinicka', 'Kratovska', 'Zletovska', 'Probistipska', 'Svetinikolska' ]) ) AS t(naselba, lat_base, lon_base, ulici) ), expanded AS ( SELECT naselba, lat_base, lon_base, unnest(ulici) as ulica FROM street_data ), numbered AS ( SELECT naselba, lat_base, lon_base, ulica, row_number() OVER (PARTITION BY naselba ORDER BY ulica) as street_num FROM expanded ) SELECT (lat_base + (street_num * 0.0008) + (n.num * 0.000045) + (random() * 0.00005 - 0.000025) )::NUMERIC(9,6) AS latitude, (lon_base + (street_num * 0.0010) + (n.num * 0.000060) + (random() * 0.00005 - 0.000025) )::NUMERIC(9,6) AS longitude, 'Kumanovo' AS grad, nd.naselba AS naselba, nd.ulica AS ulica, n.num::VARCHAR AS broj, (NOW() - interval '5 years' - (random() * interval '365 days'))::TIMESTAMP AS created_at FROM numbered nd CROSS JOIN generate_series(1, 100) AS n(num); INSERT INTO Users ( name, username, phone, email, password_hash, created_at, status_id ) WITH data_pools AS ( SELECT ARRAY['Marija','Ana','Ivana','Elena','Sara','Katerina','Jana','Mila','Teodora','Kristina','Sofija','Jovana','Tamara','Simona','Biljana','Vesna','Diana','Lidija','Monika','Angela','Ema','Tijana','Marina','Sonja','Nina','Anita','Valentina','Dragana','Ivona','Aleksandra','Natasha','Katarina','Emilija','Andrea','Gabriela','Tea','Danica','Zvezdana','Slobodanka','Gordana','Zorica','Ljubica','Rada','Mirjana','Despina','Kalina','Dafina','Hristina','Anastasija','Snezhana','Zlatka','Stojanka','Stana','Milka','Roksana','Romina','Karolina','Alina','Elina','Selina','Adelina','Darija','Dorotea','Nela','Neda','Zana','Hana','Ines','Iva','Irina','Arina','Lara','Tara','Dara','Kira','Lila','Tina','Jasmina','Bilja','Vanja','Maja','Bojana','Milena','Stefanija','Viktorija','Tatjana','Slavica','Ruzica'] AS f_names, ARRAY['Petar','Marko','Nikola','Stefan','Daniel','Goran','Bojan','Andrej','Filip','Viktor','Aleksandar','Dejan','Zoran','Ilija','Dimitar','Kiril','Hristijan','Vladimir','Vasko','Riste','Martin','Igor','Kristijan','Nenad','Damjan','Darko','Oliver','Simeon','Luka','Emil','Alen','Stojan','Metodija','Vlatko','Boris','Davor','Radoslav','Kosta','Mladen','Mile','Petre','Gjorgi','Trajan','Jovan','Ivan','Spas','Sasho','Zlatko','Bojcho','Risto','Bogdan','Tome','Kole','Vangel','Naum','Gligor','Stanko','Rade','Lazo','Arsen','Milko','Pavle','Dragan','Zdravko','Ilko','Vuk','Dame','Gorancho','Stevo','Miro'] AS m_names, ARRAY['Arsova','Dimitrova','Stojanova','Petrovska','Ilievska','Trajkova','Georgieva','Kostova','Maneva','Ristova','Popova','Jovanova','Nikolova','Mitrevska','Ivanova','Sokolova','Markova','Spasova','Angelova','Vasilevska','Pavlova','Gruevska','Nikolovska','Filipova','Stefanova','Andonova','Zafirova','Koleva','Stamenova','Bojkova','Todorova','Petkova','Hristova','Kitanova','Ilieva','Ruseva','Dimovska','Miloshevska','Petreska','Arnaudova','Georgievska','Markoska','Velkova','Stojkovska','Kirovska','Zlateva','Milevska','Naceva','Spiridonova','Lazarevska','Doneva','Atanasova','Bogdanova','Simeonova','Rangelova','Gligorova','Tasevska','Pancheva','Jankova','Bogoeva'] AS f_surnames, ARRAY['Arsov','Dimitrov','Stojanov','Petrovski','Iliev','Trajkov','Georgiev','Kostov','Manev','Ristov','Popov','Jovanov','Nikolov','Mitrev','Ivanov','Sokolov','Markov','Spasov','Angelov','Vasilev','Pavlov','Gruev','Nikolovski','Filipov','Stefanov','Andonov','Zafirov','Kolev','Stamenov','Bojkov','Todorov','Petkov','Hristov','Kitanov','Rusev','Dimovski','Miloshevski','Petreski','Arnaudov','Georgievski','Markoski','Velkov','Stojkovski','Kirovski','Zlatev','Milevski','Nacev','Spiridonov','Lazarevski','Donev','Atanasov','Bogdanov','Simeonov','Rangelov','Gligorov','Tasev','Panchev','Jankov','Bogoev','Karov','Trifunov','Gavrilov','Savov','Ilkov','Vukov','Zdravkov','Stojanovski','Danev'] AS m_surnames ) SELECT p.fname || ' ' || p.lname AS name, LOWER(p.fname || '.' || p.lname || i) AS username, '+3897' || (1000000 + i) AS phone, LOWER(p.fname || '.' || p.lname || i || '@gmail.com') AS email, md5(random()::text) AS password_hash, NOW() - (random() * interval '3 years') AS created_at, (SELECT status_id FROM Status WHERE status_tip = 'user' AND status_text = 'active' LIMIT 1) AS status_id FROM generate_series(1, 70000) i CROSS JOIN data_pools d CROSS JOIN LATERAL ( SELECT CASE WHEN i % 2 = 0 THEN d.f_names[ (i % cardinality(d.f_names)) + 1] ELSE d.m_names[ (i % cardinality(d.m_names)) + 1] END AS fname, CASE WHEN i % 2 = 0 THEN d.f_surnames[(i % cardinality(d.f_surnames)) + 1] ELSE d.m_surnames[(i % cardinality(d.m_surnames)) + 1] END AS lname ) p; INSERT INTO Drivers (name, phone, license_number, registration_date, rating, status_id) WITH data_pools AS ( SELECT ARRAY['Petar','Marko','Nikola','Stefan','Daniel','Goran','Bojan','Andrej','Filip','Viktor', 'Aleksandar','Dejan','Zoran','Ilija','Dimitar','Kiril','Hristijan','Vladimir','Vasko','Riste', 'Martin','Igor','Damjan','Darko','Oliver','Luka','Emil','Stojan','Vlatko','Boris', 'Radoslav','Kosta','Mladen','Mile','Petre','Gjorgi','Jovan','Ivan','Sasho','Zlatko', 'Bogdan','Tome','Naum','Gligor','Stanko','Rade','Lazo','Arsen','Pavle','Dragan'] AS m_names, ARRAY['Arsov','Dimitrov','Stojanov','Petrovski','Iliev','Trajkov','Georgiev','Kostov','Manev', 'Ristov','Popov','Jovanov','Nikolov','Mitrev','Ivanov','Sokolov','Markov','Spasov','Angelov', 'Vasilev','Pavlov','Gruev','Nikolovski','Filipov','Stefanov','Andonov','Zafirov','Kolev', 'Stamenov','Bojkov','Todorov','Petkov','Hristov','Kitanov','Rusev','Dimovski','Miloshevski', 'Petreski','Arnaudov','Georgievski','Markoski','Velkov','Stojkovski','Kirovski','Zlatev', 'Milevski','Spiridonov','Lazarevski','Atanasov','Bogdanov'] AS m_surnames ) SELECT p.fname || ' ' || p.lname AS name, '+3897' || (2000000 + i) AS phone, CHR(65 + (i % 26)) || LPAD((floor(random() * 9000000) + 1000000)::int::text, 7, '0') AS license_number, (NOW() - interval '3 years' - (random() * interval '2 years'))::DATE AS registration_date, ROUND((3.5 + random() * 1.5)::numeric, 2) AS rating, (SELECT status_id FROM Status WHERE status_tip = 'driver' AND status_text = 'active' LIMIT 1) AS status_id FROM generate_series(1, 200) i CROSS JOIN data_pools d CROSS JOIN LATERAL ( SELECT d.m_names[(i % cardinality(d.m_names)) + 1] AS fname, d.m_surnames[(i % cardinality(d.m_surnames)) + 1] AS lname ) p; INSERT INTO Admins (name, username, phone, email, password_hash, created_at, status_id) VALUES ('Aleksandar Dimitrov', 'admin.aleksandar', '+38971100001', 'aleksandar.dimitrov@taxikumanovo.mk', md5(random()::text), NOW() - interval '4 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)), ('Marija Petrovska', 'admin.marija', '+38971100002', 'marija.petrovska@taxikumanovo.mk', md5(random()::text), NOW() - interval '3 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)), ('Nikola Trajkov', 'admin.nikola', '+38971100003', 'nikola.trajkov@taxikumanovo.mk', md5(random()::text), NOW() - interval '3 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)), ('Elena Georgieva', 'admin.elena', '+38971100004', 'elena.georgieva@taxikumanovo.mk', md5(random()::text), NOW() - interval '2 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)), ('Stefan Iliev', 'admin.stefan', '+38971100005', 'stefan.iliev@taxikumanovo.mk', md5(random()::text), NOW() - interval '2 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)), ('Ana Nikolova', 'admin.ana', '+38971100006', 'ana.nikolova@taxikumanovo.mk', md5(random()::text), NOW() - interval '1 year', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)); INSERT INTO Vehicle_ownership (Plate, Traffic_permit, Color, year, vehicle_type_id) WITH colors AS ( SELECT ARRAY['Crna','Bela','Siva','Srebrena','Crvena','Sina','Zelena','Beza','Kafena','Temnosina'] AS cols ) SELECT -- Табличка: 2 букви + 3 цифри + 2 букви, пр: SK123AB 'KU' || LPAD((100 + i)::text, 4, '0') || CHR(65 + ((i * 5) % 26)) || CHR(65 + ((i * 7) % 26)) AS Plate, -- Сообраќајна: KU + 6 цифри CHR(65 + (i % 26)) || CHR(65 + ((i * 3) % 26)) || LPAD((100000 + i)::text, 6, '0') AS Traffic_permit, -- Боја c.cols[(i % cardinality(c.cols)) + 1] AS Color, -- Година: возилата се од 2010 до 2023 2010 + (i % 14) AS year, -- vehicle_type_id: рандом од 1 до 50 (1 + (i % 50)) AS vehicle_type_id FROM generate_series(1, 300) i CROSS JOIN colors c; INSERT INTO Active_drivers (driver_id, current_location_id, is_available, working_start, working_end,vehicle_ownership_id) SELECT d.driver_id, -- Random location_id од постоечките локации (1 + (d.driver_id % 14000)) AS current_location_id, -- 70% се available, 30% се зафатени CASE WHEN d.driver_id % 10 < 7 THEN true ELSE false END AS is_available, -- Работната смена почнала пред 1-8 часа (NOW() - (random() * interval '8 hours'))::TIMESTAMP AS working_start, -- Работната смена завршува за 2-10 часа (NOW() + (random() * interval '10 hours'))::TIMESTAMP AS working_end, -- Да се знае кое авто моментлано го вози CASE WHEN d.driver_id < 100 THEN d.driver_id + (random(0,1) * 200) ELSE d.driver_id END AS vehicle_ownership_id FROM Drivers d -- Земаме само 150 од 200 возачи (75%) WHERE d.driver_id % 4 != 0 LIMIT 150; INSERT INTO Drivers_Vehicle_ownership (driver_id, Vehicle_ownership_id) -- Sekoj vozac dobiva svoe primарно vozilo (1:1) SELECT d.driver_id, -- Voziloto se mapira deterministicki: vozac 1→vozilo 1, vozac 2→vozilo 2 ... d.driver_id AS Vehicle_ownership_id FROM Drivers d WHERE d.driver_id <= 200 -- site 200 vozaci UNION ALL -- Prvite 100 vozaci dobivaat i vtoro vozilo (od 201 do 300) SELECT d.driver_id, d.driver_id + 200 AS Vehicle_ownership_id FROM Drivers d WHERE d.driver_id <= 100; --Rides DO $$ DECLARE batch_size INT := 1000000; total_rows INT := 10000000; batches INT := total_rows / batch_size; b INT; max_user INT; max_driver INT; max_location INT; sid_requested INT; sid_accepted INT; sid_in_progress INT; sid_completed INT; sid_cancelled INT; BEGIN SELECT MAX(user_id) INTO max_user FROM Users; SELECT MAX(driver_id) INTO max_driver FROM Drivers; SELECT MAX(location_id) INTO max_location FROM Locations; SELECT status_id INTO sid_requested FROM Status WHERE status_tip='ride' AND status_text='requested' LIMIT 1; SELECT status_id INTO sid_accepted FROM Status WHERE status_tip='ride' AND status_text='accepted' LIMIT 1; SELECT status_id INTO sid_in_progress FROM Status WHERE status_tip='ride' AND status_text='in_progress' LIMIT 1; SELECT status_id INTO sid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1; SELECT status_id INTO sid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1; RAISE NOTICE 'max_user=%, max_driver=%, max_location=%', max_user, max_driver, max_location; FOR b IN 1..batches LOOP INSERT INTO Rides ( user_id, driver_id, pickup_location_id, dropoff_location_id, status_id, request_time, pickup_time, start_time, end_time, distance_km, final_price, discount_percentage, vehicle_ownership_id ) WITH -- -------------------------------------------------------- -- CTE 1: Realna cena po vozac spored tipot na voziloto -- Economy: base=80, per_km=40 -- Luxury: base=100, per_km=60 -- Ako ima 2 vozila → go zemame luksoznoto (MAX cena) -- -------------------------------------------------------- driver_pricing AS ( SELECT dvo.driver_id, MAX(pr.base_price) AS base_price, MAX(pr.price_per_km) AS price_per_km, MAX(vt.description) AS vehicle_class FROM Drivers_Vehicle_ownership dvo JOIN Vehicle_ownership vo ON dvo.Vehicle_ownership_id = vo.Vehicle_ownership_id JOIN Vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id JOIN Pricing_rules pr ON vt.vehicle_type_id = pr.vehicle_type_id GROUP BY dvo.driver_id ), -- -------------------------------------------------------- -- CTE 2: Base pool so site ogranichuvanja primeneti -- -------------------------------------------------------- base_pool AS ( SELECT s.i, -- USER / DRIVER (realni, razlichni) 1 + (s.i % max_user) AS user_id, 1 + ((s.i + floor(random() * 200000)+ 37)::INTEGER % max_driver) AS driver_id, -- LOKACII 1 + (s.i + floor(random() * 200000))::INTEGER % max_location AS pickup_location_id, 1 + ((s.i + floor(random() * 200000)+ (14000 / 2))::INTEGER % max_location) AS dropoff_location_id, -- STATUS CASE WHEN (s.i % 20) = 0 THEN sid_requested WHEN (s.i % 20) IN (1, 2) THEN sid_accepted WHEN (s.i % 20) IN (3, 4) THEN sid_in_progress WHEN (s.i % 20) IN (18, 19) THEN sid_cancelled ELSE sid_completed END AS status_id, -- BASE TIME: random den (poslednite 2 godini), cas 06:00-23:00 date_trunc('day', now() - (floor(random() * 730) * interval '1 day')) + (21600 + floor(random() * 61200)) * interval '1 second' AS request_time, -- DISTANCA: 0.5 .. 10.0 km (OGRANICHENO NA MAX 10km) ROUND((0.5 + random() * 9.5)::numeric, 2) AS distance_km FROM generate_series( (b - 1) * batch_size + 1, b * batch_size ) s(i) ), -- -------------------------------------------------------- -- CTE 3: Presmetaj popust i vreminja vrz osnova na distanca -- -------------------------------------------------------- enriched AS ( SELECT bp.*, -- POPUST: -- >= 9km → 10% -- < 9km → 0% CASE WHEN bp.distance_km >= 9 THEN 10.0 ELSE 0.0 END AS discount_pct, -- VREMINJA (site od ista request_time baza): -- pickup = request + 2..10 min bp.request_time + (2 + floor(random() * 9)) * interval '1 minute' AS pickup_time, -- start = request + 3..13 min (sekogash posle pickup) bp.request_time + (3 + floor(random() * 11)) * interval '1 minute' AS start_time, -- end = start + 5..15 min (OGRANICHENO: min 5, max 15) bp.request_time + (3 + floor(random() * 11)) * interval '1 minute' -- ista start baza + (5 + floor(random() * 11)) * interval '1 minute' -- + 5..15 min AS end_time FROM base_pool bp ) -- -------------------------------------------------------- -- FINALEN SELECT: JOIN so realni ceni od vozacovoto vozilo -- -------------------------------------------------------- SELECT en.user_id, en.driver_id, en.pickup_location_id, en.dropoff_location_id, en.status_id, en.request_time, en.pickup_time, en.start_time, en.end_time, en.distance_km, -- CENA spored tipot na voziloto: -- Economy → (80 + km * 40) * (1 - popust%) -- Luxury → (100 + km * 60) * (1 - popust%) -- Ako vozacot nema vozilo → fallback na Economy cena ROUND( (COALESCE(dp.base_price, 80) + en.distance_km * COALESCE(dp.price_per_km, 40)) * (1.0 - en.discount_pct / 100.0) ::numeric, 2) AS final_price, en.discount_pct AS discount_percentage, CASE WHEN en.driver_id < 100 THEN en.driver_id + (random(0,1) * 200) ELSE en.driver_id END AS vehicle_ownership_id FROM enriched en LEFT JOIN driver_pricing dp ON en.driver_id = dp.driver_id; RAISE NOTICE 'Batch % / % zavrshen — % redovi vkupno', b, batches, b * batch_size; END LOOP; update rides set pickup_location_id = 1 + (Rides.ride_id + floor(random() * 200000))::INTEGER% max_location where dropoff_location_id=pickup_location_id; RAISE NOTICE '*** GOTOVO: 10 000 000 redovi vo Rides ***'; END $$; --Payments DO $$ DECLARE batch_size INT := 1000000; total_rows INT := 10000000; batches INT := total_rows / batch_size; b INT; -- Payment method IDs pm_cash INT; pm_card INT; -- Payment status IDs sid_pending INT; sid_completed INT; sid_failed INT; sid_refunded INT; -- Ride status IDs (za da odredime payment status) rid_completed INT; rid_cancelled INT; BEGIN -- Payment methods SELECT payment_method_id INTO pm_cash FROM Payment_methods WHERE payment_tip = 'CASH' LIMIT 1; SELECT payment_method_id INTO pm_card FROM Payment_methods WHERE payment_tip = 'CARD' LIMIT 1; -- Payment statusi SELECT status_id INTO sid_pending FROM Status WHERE status_tip='payment' AND status_text='pending' LIMIT 1; SELECT status_id INTO sid_completed FROM Status WHERE status_tip='payment' AND status_text='completed' LIMIT 1; SELECT status_id INTO sid_failed FROM Status WHERE status_tip='payment' AND status_text='failed' LIMIT 1; SELECT status_id INTO sid_refunded FROM Status WHERE status_tip='payment' AND status_text='refunded' LIMIT 1; -- Ride statusi SELECT status_id INTO rid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1; SELECT status_id INTO rid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1; RAISE NOTICE 'pm_cash=%, pm_card=%', pm_cash, pm_card; RAISE NOTICE 'payment statusi: pending=%, completed=%, failed=%, refunded=%', sid_pending, sid_completed, sid_failed, sid_refunded; FOR b IN 1..batches LOOP INSERT INTO Payments ( amount, payment_method_id, payment_time, ride_id, status_id ) SELECT -- Amount = tocnata cena od ridot r.final_price AS amount, -- Payment method: 60% CASH, 40% CARD CASE WHEN (r.ride_id % 10) < 6 THEN pm_cash ELSE pm_card END AS payment_method_id, -- Payment time = end_time + 1..5 min r.end_time + (1 + floor(random() * 5)) * interval '1 minute' AS payment_time, r.ride_id AS ride_id, -- Payment status spored status na ridot: -- completed → completed -- cancelled → 50% failed, 50% refunded -- site drugi → pending CASE WHEN r.status_id = rid_completed THEN sid_completed WHEN r.status_id = rid_cancelled THEN CASE WHEN r.ride_id % 2 = 0 THEN sid_failed ELSE sid_refunded END ELSE sid_pending END AS status_id FROM Rides r WHERE r.ride_id BETWEEN (b - 1) * batch_size + 1 AND b * batch_size; RAISE NOTICE 'Batch % / % zavrshen — % payments vkupno', b, batches, b * batch_size; END LOOP; RAISE NOTICE '*** GOTOVO: 10 000 000 redovi vo Payments ***'; END $$; INSERT INTO Cancellations ( cancelled_by_type, cancelled_by_id, reason, cancellation_fee, created_at, ride_id ) WITH -- Ride status za cancelled cancelled_status AS ( SELECT status_id FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1 ), -- Realna base_price po vozac (Economy=80, Luxury=100) driver_pricing AS ( SELECT dvo.driver_id, MAX(pr.base_price) AS base_price FROM Drivers_Vehicle_ownership dvo JOIN Vehicle_ownership vo ON dvo.Vehicle_ownership_id = vo.Vehicle_ownership_id JOIN Vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id JOIN Pricing_rules pr ON vt.vehicle_type_id = pr.vehicle_type_id GROUP BY dvo.driver_id ), -- Samo cancelled rides + row_number za ramномерна distribucija cancelled_rides AS ( SELECT r.ride_id, r.user_id, r.driver_id, r.request_time, COALESCE(dp.base_price, 80) AS base_price, -- row_number() garantira 0..9 ramномерно bez zavisnost od ride_id vrednostite (row_number() OVER (ORDER BY r.ride_id) - 1) % 10 AS rn FROM Rides r JOIN cancelled_status cs ON r.status_id = cs.status_id LEFT JOIN driver_pricing dp ON r.driver_id = dp.driver_id ) SELECT -- cancelled_by_type: 50% USER (0-4), 30% DRIVER (5-7), 20% ADMIN (8-9) CASE WHEN rn IN (0,1,2,3,4) THEN 'USER' WHEN rn IN (5,6,7) THEN 'DRIVER' ELSE 'ADMIN' END AS cancelled_by_type, -- cancelled_by_id: realen ID CASE WHEN rn IN (0,1,2,3,4) THEN user_id WHEN rn IN (5,6,7) THEN driver_id ELSE 1 + (ride_id % 6) END AS cancelled_by_id, -- Reason spored tipot CASE WHEN rn IN (0,1,2,3,4) THEN CASE (ride_id % 3) WHEN 0 THEN 'Променив планови и повеќе не ми треба превоз' WHEN 1 THEN 'Чекањето беше предолго' ELSE 'Внесов погрешна локација за подигање' END WHEN rn IN (5,6,7) THEN CASE (ride_id % 3) WHEN 0 THEN 'Не можам да ја пронајдам точната локација на патникот' WHEN 1 THEN 'Патникот не се појави на местото за подигање' ELSE 'Технички проблем со возилото' END ELSE CASE (ride_id % 3) WHEN 0 THEN 'Вожњата е откажана поради сомнителна активност' WHEN 1 THEN 'Откажано поради дупликат барање' ELSE 'Откажано поради системска грешка' END END AS reason, -- Fee: samo USER plaka, Driver i Admin = 0 CASE WHEN rn IN (0,1,2,3,4) THEN base_price ELSE 0.00 END AS cancellation_fee, -- created_at = request_time + 1..30 min request_time + (1 + floor(random() * 30)) * interval '1 minute' AS created_at, ride_id FROM cancelled_rides; DO $$ DECLARE batch_size INT := 500000; b INT; max_ride INT; batches INT; mid_1 INT; mid_2 INT; mid_3 INT; mid_4 INT; mid_5 INT; mid_6 INT; mid_7 INT; mid_8 INT; mid_9 INT; sid_notif_sent INT; sid_notif_fail INT; rid_completed INT; rid_cancelled INT; rid_in_progress INT; rid_accepted INT; rid_requested INT; pay_completed INT; pay_failed INT; BEGIN -- Message IDs SELECT message_id INTO mid_1 FROM Messages WHERE message_text LIKE 'Vaseto baranje e uspesno%' LIMIT 1; SELECT message_id INTO mid_2 FROM Messages WHERE message_text LIKE 'Vozacot go prifati%' LIMIT 1; SELECT message_id INTO mid_3 FROM Messages WHERE message_text LIKE 'Vozacot go odbi%' LIMIT 1; SELECT message_id INTO mid_4 FROM Messages WHERE message_text LIKE 'Vozacot pristigna%' LIMIT 1; SELECT message_id INTO mid_5 FROM Messages WHERE message_text LIKE 'Vozenjeto e otkazano od strana na vozac%' LIMIT 1; SELECT message_id INTO mid_6 FROM Messages WHERE message_text LIKE 'Vozenjeto e otkazano od vasa%' LIMIT 1; SELECT message_id INTO mid_7 FROM Messages WHERE message_text LIKE 'Plakjanjeto e uspesno%' LIMIT 1; SELECT message_id INTO mid_8 FROM Messages WHERE message_text LIKE 'Plakjanjeto ne uspea%' LIMIT 1; SELECT message_id INTO mid_9 FROM Messages WHERE message_text LIKE 'Nemate dostapni%' LIMIT 1; -- Notification statusi (koristime ride status kako proxy za sent/fail) SELECT status_id INTO sid_notif_sent FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1; SELECT status_id INTO sid_notif_fail FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1; -- Ride statusi SELECT status_id INTO rid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1; SELECT status_id INTO rid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1; SELECT status_id INTO rid_in_progress FROM Status WHERE status_tip='ride' AND status_text='in_progress' LIMIT 1; SELECT status_id INTO rid_accepted FROM Status WHERE status_tip='ride' AND status_text='accepted' LIMIT 1; SELECT status_id INTO rid_requested FROM Status WHERE status_tip='ride' AND status_text='requested' LIMIT 1; -- Payment statusi SELECT status_id INTO pay_completed FROM Status WHERE status_tip='payment' AND status_text='completed' LIMIT 1; SELECT status_id INTO pay_failed FROM Status WHERE status_tip='payment' AND status_text='failed' LIMIT 1; SELECT MAX(ride_id) INTO max_ride FROM Rides; batches := CEIL(max_ride::numeric / batch_size); RAISE NOTICE 'Pocnuvame so % batches po % rides', batches, batch_size; -- =========================================================== -- FLOW 1: COMPLETED rides -- Poraki: 1→2→4→7 -- Vreminja: request→pickup→start→end+2min -- =========================================================== FOR b IN 1..batches LOOP INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id) SELECT * FROM ( -- Poraka 1: Baranjeto e uspesno isprateno → request_time SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent FROM Rides r WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_completed UNION ALL -- Poraka 2: Vozacot go prifati → pickup_time SELECT mid_2, r.pickup_time, r.user_id, r.ride_id, sid_notif_sent FROM Rides r WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_completed UNION ALL -- Poraka 4: Vozacot pristigna → start_time SELECT mid_4, r.start_time, r.user_id, r.ride_id, sid_notif_sent FROM Rides r WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_completed UNION ALL -- Poraka 7: Plakjanjeto uspesno → end_time + 2..5 min SELECT mid_7, r.end_time + (2 + floor(random()*4)) * interval '1 minute', r.user_id, r.ride_id, sid_notif_sent FROM Rides r JOIN Payments p ON r.ride_id = p.ride_id AND p.status_id = pay_completed WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_completed ) flows; RAISE NOTICE '[COMPLETED] Batch % / %', b, batches; END LOOP; -- =========================================================== -- FLOW 2: CANCELLED BY USER -- Poraki: 1→6→8 -- Vreminja: request→request+1..10min→end+2min -- =========================================================== FOR b IN 1..batches LOOP INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id) SELECT * FROM ( -- Poraka 1: Baranjeto isprateno → request_time SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent FROM Rides r JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER' WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_cancelled UNION ALL -- Poraka 6: Otkazano od vasa strana → request_time + 1..10 min SELECT mid_6, r.request_time + (1 + floor(random()*10)) * interval '1 minute', r.user_id, r.ride_id, sid_notif_fail FROM Rides r JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER' WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_cancelled UNION ALL -- Poraka 8: Plakjanjeto ne uspea → end_time + 2..5 min SELECT mid_8, r.end_time + (2 + floor(random()*4)) * interval '1 minute', r.user_id, r.ride_id, sid_notif_fail FROM Rides r JOIN Payments p ON r.ride_id = p.ride_id AND p.status_id = pay_failed JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER' WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_cancelled ) flows; RAISE NOTICE '[CANCELLED USER] Batch % / %', b, batches; END LOOP; -- =========================================================== -- FLOW 3: CANCELLED BY DRIVER -- Poraki: 1→2→5 -- Vreminja: request→pickup→request+5..15min -- =========================================================== FOR b IN 1..batches LOOP INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id) SELECT * FROM ( -- Poraka 1: Baranjeto isprateno → request_time SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent FROM Rides r JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER' WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_cancelled UNION ALL -- Poraka 2: Vozacot prifati → pickup_time SELECT mid_2, r.pickup_time, r.user_id, r.ride_id, sid_notif_sent FROM Rides r JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER' WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_cancelled UNION ALL -- Poraka 5: Otkazano od vozac → request_time + 5..15 min SELECT mid_5, r.request_time + (5 + floor(random()*11)) * interval '1 minute', r.user_id, r.ride_id, sid_notif_fail FROM Rides r JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER' WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_cancelled ) flows; RAISE NOTICE '[CANCELLED DRIVER] Batch % / %', b, batches; END LOOP; -- =========================================================== -- FLOW 4: CANCELLED BY ADMIN -- Poraki: 1→9 -- Vreminja: request→request (momentalno) -- =========================================================== FOR b IN 1..batches LOOP INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id) SELECT * FROM ( -- Poraka 1: Baranjeto isprateno → request_time SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent FROM Rides r JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'ADMIN' WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_cancelled UNION ALL -- Poraka 9: Nema dostapni vozaci → request_time (vednas) SELECT mid_9, r.request_time, r.user_id, r.ride_id, sid_notif_fail FROM Rides r JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'ADMIN' WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size AND r.status_id = rid_cancelled ) flows; RAISE NOTICE '[CANCELLED ADMIN] Batch % / %', b, batches; END LOOP; RAISE NOTICE '*** GOTOVO: SITE NOTIFICATIONS SE GENERIRANI ***'; END $$; }}}