| | 312 | |
| | 313 | === DML Имплементација === |
| | 314 | |
| | 315 | DML скриптите се користат за внесување, генерирање и управување со податоците во базата на податоци. Во овој проект, преку DML наредби се пополнуваат табелите со иницијални и тест податоци потребни за функционирање и симулација на ride-sharing системот. |
| | 316 | |
| | 317 | Во скриптите се опфатени: |
| | 318 | |
| | 319 | внесување на почетни податоци (INSERT INTO) |
| | 320 | |
| | 321 | генерирање на големи количини тест податоци |
| | 322 | |
| | 323 | користење на SELECT, CASE, WITH и JOIN изрази за автоматско креирање податоци |
| | 324 | |
| | 325 | симулација на реални сценарија за возења, плаќања, откажувања и нотификации |
| | 326 | |
| | 327 | batch обработка и оптимизација за работа со милионски datasets |
| | 328 | |
| | 329 | користење на PostgreSQL DO $$ блокови и procedural логика |
| | 330 | |
| | 331 | Со овие DML скрипти се обезбедува реалистичен dataset кој овозможува тестирање, анализа и евалуација на перформансите на системот. |
| | 332 | |
| | 333 | === DML Скрипти === |
| | 334 | ``` |
| | 335 | INSERT INTO Status (status_tip, status_text) VALUES |
| | 336 | ('driver', 'active'), |
| | 337 | ('driver', 'inactive'); |
| | 338 | INSERT INTO Status (status_tip, status_text) VALUES |
| | 339 | ('user', 'active'), |
| | 340 | ('user', 'inactive'), |
| | 341 | ('user', 'blocked'), |
| | 342 | ('user', 'deleted'); |
| | 343 | INSERT INTO Status (status_tip, status_text) VALUES |
| | 344 | ('admin', 'active'), |
| | 345 | ('admin', 'inactive'), |
| | 346 | ('admin', 'blocked'); |
| | 347 | INSERT INTO Status (status_tip, status_text) VALUES |
| | 348 | ('ride', 'requested'), |
| | 349 | ('ride', 'accepted'), |
| | 350 | ('ride', 'in_progress'), |
| | 351 | ('ride', 'completed'), |
| | 352 | ('ride', 'cancelled'); |
| | 353 | INSERT INTO Status (status_tip, status_text) VALUES |
| | 354 | ('payment', 'pending'), |
| | 355 | ('payment', 'completed'), |
| | 356 | ('payment', 'failed'), |
| | 357 | ('payment', 'refunded'); |
| | 358 | |
| | 359 | INSERT INTO Payment_methods (payment_tip) VALUES |
| | 360 | ('CASH'), |
| | 361 | ('CARD'); |
| | 362 | |
| | 363 | INSERT INTO Messages (message_text) VALUES |
| | 364 | ('Vaseto baranje e uspesno isprateno'), |
| | 365 | ('Vozacot go prifati vaseto baranje'), |
| | 366 | ('Vozacot go odbi vaseto baranje'), |
| | 367 | ('Vozacot pristigna na lokacijata za podiganje'), |
| | 368 | ('Vozenjeto e otkazano od strana na vozacot'), |
| | 369 | ('Vozenjeto e otkazano od vasa strana'), |
| | 370 | ('Plakjanjeto e uspesno'), |
| | 371 | ('Plakjanjeto ne uspea'), |
| | 372 | ('Nemate dostapni vozaci vo momentot'); |
| | 373 | |
| | 374 | INSERT INTO Vehicles_model (Model) VALUES |
| | 375 | ('Volkswagen'), |
| | 376 | ('Skoda'), |
| | 377 | ('Toyota'), |
| | 378 | ('Opel'), |
| | 379 | ('Mercedes-Benz'), |
| | 380 | ('BMW'), |
| | 381 | ('Audi'), |
| | 382 | ('Hyundai'), |
| | 383 | ('Kia'), |
| | 384 | ('Renault'); |
| | 385 | |
| | 386 | INSERT INTO Vehicle_types (type_name, description, Vehicles_model_id) |
| | 387 | SELECT |
| | 388 | m.model_name, |
| | 389 | CASE |
| | 390 | WHEN m.model_name IN ('Golf','Octavia','Corolla','Astra','Polo','Fabia','Yaris','Corsa','Elantra','Clio', |
| | 391 | 'i30','Rio','Megane','Passat','Superb','Camry','Insignia','Tucson','Ceed','Captur') |
| | 392 | THEN 'Economy class' |
| | 393 | ELSE 'Luxury class' |
| | 394 | END as description, |
| | 395 | m.brand_id |
| | 396 | FROM ( |
| | 397 | SELECT vm.Vehicles_model_id as brand_id, unnest(models) as model_name |
| | 398 | FROM Vehicles_model vm |
| | 399 | JOIN (VALUES |
| | 400 | ('Volkswagen', ARRAY['Golf','Passat','Tiguan','Polo','Arteon']), |
| | 401 | ('Skoda', ARRAY['Octavia','Superb','Kodiaq','Fabia','Karoq']), |
| | 402 | ('Toyota', ARRAY['Corolla','Yaris','RAV4','Camry','C-HR']), |
| | 403 | ('Opel', ARRAY['Astra','Insignia','Corsa','Mokka','Grandland']), |
| | 404 | ('Mercedes-Benz', ARRAY['E-Class','C-Class','S-Class','GLE','CLA']), |
| | 405 | ('BMW', ARRAY['3 Series','5 Series','X5','X3','1 Series']), |
| | 406 | ('Audi', ARRAY['A4','A6','Q5','A3','Q7']), |
| | 407 | ('Hyundai', ARRAY['Elantra','Tucson','Santa Fe','i30','Kona']), |
| | 408 | ('Kia', ARRAY['Sportage','Ceed','Rio','Sorento','Niro']), |
| | 409 | ('Renault', ARRAY['Clio','Megane','Captur','Kadjar','Talisman']) |
| | 410 | ) AS t(brand, models) ON vm.Model = t.brand |
| | 411 | ) m; |
| | 412 | |
| | 413 | INSERT INTO Pricing_rules (base_price, price_per_km, vehicle_type_id) |
| | 414 | SELECT |
| | 415 | CASE |
| | 416 | WHEN description = 'Economy class' THEN 80.00 |
| | 417 | ELSE 100.00 |
| | 418 | END as base_price, |
| | 419 | CASE |
| | 420 | WHEN description = 'Economy class' THEN 40.00 |
| | 421 | ELSE 60.00 |
| | 422 | END as price_per_km, |
| | 423 | vehicle_type_id |
| | 424 | FROM Vehicle_types; |
| | 425 | |
| | 426 | INSERT INTO Locations (latitude, longitude, grad, naselba, ulica, broj, created_at) |
| | 427 | WITH street_data AS ( |
| | 428 | SELECT * FROM (VALUES |
| | 429 | ('Centar', 42.1322, 21.7144, ARRAY[ |
| | 430 | '11 Oktomvri', 'Goce Delchev', 'Tane Georgiev', 'Gorce Petrov', 'Plostad Marsal Tito', |
| | 431 | 'Done Bozhinov', 'Ljubo Atanasov', 'Ivo Lola Ribar', 'Leninova', 'Narodna Revolucija', |
| | 432 | 'Svetozar Markovic', 'Krste Misirkov', 'Jane Sandanski', 'Dragan Stoparevic', 'Bratstvo Edinstvo', |
| | 433 | 'Marsal Tito', 'JNA', 'Stiv Naumov', 'Guro Pucar Stari', 'Hristijan Karpos' |
| | 434 | ]), |
| | 435 | ('Zelen Rid', 42.1400, 21.7000, ARRAY[ |
| | 436 | 'Nikola Tesla', 'Zheleznichka', 'Vidoe Smilevski Bato', 'Vasil Glavinov', 'Sremska', |
| | 437 | 'Kozjak', 'Osogovska', 'Shar Planina', 'Belasica', 'Ruen', |
| | 438 | 'Prilepska', 'Bitolska', 'Ohridska', 'Strumicka', 'Vardar', |
| | 439 | 'Pelister', 'Skopska', 'Tetovska', 'Gevgeliska', 'Veleska' |
| | 440 | ]), |
| | 441 | ('Goce Delchev', 42.1250, 21.7250, ARRAY[ |
| | 442 | 'Treta Makedonska Udarna Brigada', 'Vera Kotorka', 'Boro Mikic', 'Filip Vtori', 'Srbo Tomovik', |
| | 443 | 'Zikica Jovanovic Spanac', 'Boro Menkov', 'Milan Zecar', 'Dositej Obradovic', 'Kuzman Josifovski Pitu', |
| | 444 | 'Tomaki Dimitrovski', 'Todor Velkov', 'Vasko Karangelevski', 'Niksicka', 'Pero Nakov', |
| | 445 | 'Bajram Shabani', 'Mite Bogoevski', 'Slavko Janevski', 'Gjorce Petrov', 'Kiro Fetak' |
| | 446 | ]), |
| | 447 | ('Pero Chicho', 42.1150, 21.7300, ARRAY[ |
| | 448 | 'Pero Chicho', '11 Noemvri', 'Boro Mendkov', 'Josip Pancic', 'Banatska', |
| | 449 | 'Moravska', 'Drinska', 'Sredorek', 'Vojvodina', 'Sumadinska', |
| | 450 | 'Toplicki Odred', 'Zajecarska', 'Niska', 'Vranjska', 'Leskovacka', |
| | 451 | 'Sremska', 'Prizrenska', 'Dakovicka', 'Pecka', 'Kosovska' |
| | 452 | ]), |
| | 453 | ('Karposh', 42.1200, 21.7500, ARRAY[ |
| | 454 | 'Oktomvriska Revolucija', 'Srecko Puzalka', 'Mito Hadzivasilev Jasmin', 'Sava Kovacevic', 'Karposova', |
| | 455 | 'Metodija Andonov Cento', 'ASNOM', 'Dame Gruev', 'Pitu Guli', 'Partizanska', |
| | 456 | 'Kiro Burnaz', 'Pere Tosev', 'Gjorce Petrov', 'Hristijan Todorovski', 'Miroslav Krleza', |
| | 457 | 'Blagoja Stevkovski', 'Kosta Racin', 'Koco Racin', 'Mirce Acev', 'Vasil Antevski' |
| | 458 | ]), |
| | 459 | ('Bedinje', 42.1500, 21.7100, ARRAY[ |
| | 460 | 'Srbo Tomovikj', 'Zhivko Tomovski', 'Guro Djakovic', 'Karaorman', 'Njegoseva', |
| | 461 | 'Ivan Milutinovic', 'Mojsa Pijade', 'Zheleznichka', 'Revolucionerna', 'Gjorce Petrov', |
| | 462 | 'Cede Filipovski', 'Bratstvo', 'Edinstvo', 'Prvomajska', 'Sloboda', |
| | 463 | 'Proleterska', 'Makedonska', 'Ilindenska', 'Vidoe Smilevski', 'Josip Pancic' |
| | 464 | ]), |
| | 465 | ('Ajducka Cheshma', 42.1480, 21.6950, ARRAY[ |
| | 466 | 'Bajram Shabani', 'Prilepska', 'Blagoja Stevkovski', 'Romanovska', 'Kumanovska', |
| | 467 | 'Debarska', 'Krusevska', 'Dojranska', 'Prespanska', 'Gevgeliska', |
| | 468 | 'Negotinska', 'Kavadarska', 'Radoviska', 'Berovska', 'Delcevska', |
| | 469 | 'Vinicka', 'Kratovska', 'Zletovska', 'Probistipska', 'Svetinikolska' |
| | 470 | ]) |
| | 471 | ) AS t(naselba, lat_base, lon_base, ulici) |
| | 472 | ), |
| | 473 | expanded AS ( |
| | 474 | SELECT |
| | 475 | naselba, |
| | 476 | lat_base, |
| | 477 | lon_base, |
| | 478 | unnest(ulici) as ulica |
| | 479 | FROM street_data |
| | 480 | ), |
| | 481 | numbered AS ( |
| | 482 | SELECT |
| | 483 | naselba, |
| | 484 | lat_base, |
| | 485 | lon_base, |
| | 486 | ulica, |
| | 487 | row_number() OVER (PARTITION BY naselba ORDER BY ulica) as street_num |
| | 488 | FROM expanded |
| | 489 | ) |
| | 490 | SELECT |
| | 491 | (lat_base |
| | 492 | + (street_num * 0.0008) |
| | 493 | + (n.num * 0.000045) |
| | 494 | + (random() * 0.00005 - 0.000025) |
| | 495 | )::NUMERIC(9,6) AS latitude, |
| | 496 | |
| | 497 | (lon_base |
| | 498 | + (street_num * 0.0010) |
| | 499 | + (n.num * 0.000060) |
| | 500 | + (random() * 0.00005 - 0.000025) |
| | 501 | )::NUMERIC(9,6) AS longitude, |
| | 502 | |
| | 503 | 'Kumanovo' AS grad, |
| | 504 | nd.naselba AS naselba, |
| | 505 | nd.ulica AS ulica, |
| | 506 | n.num::VARCHAR AS број, |
| | 507 | |
| | 508 | (NOW() - interval '5 years' - (random() * interval '365 days'))::TIMESTAMP AS created_at |
| | 509 | |
| | 510 | FROM numbered nd |
| | 511 | CROSS JOIN generate_series(1, 100) AS n(num); |
| | 512 | |
| | 513 | |
| | 514 | INSERT INTO Users ( |
| | 515 | name, |
| | 516 | username, |
| | 517 | phone, |
| | 518 | email, |
| | 519 | password_hash, |
| | 520 | created_at, |
| | 521 | status_id |
| | 522 | ) |
| | 523 | WITH data_pools AS ( |
| | 524 | SELECT |
| | 525 | 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, |
| | 526 | 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, |
| | 527 | 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, |
| | 528 | 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 |
| | 529 | ) |
| | 530 | SELECT |
| | 531 | p.fname || ' ' || p.lname AS name, |
| | 532 | LOWER(p.fname || '.' || p.lname || i) AS username, |
| | 533 | '+3897' || (1000000 + i) AS phone, |
| | 534 | LOWER(p.fname || '.' || p.lname || i || '@gmail.com') AS email, |
| | 535 | md5(random()::text) AS password_hash, |
| | 536 | NOW() - (random() * interval '3 years') AS created_at, |
| | 537 | (SELECT status_id FROM Status WHERE status_tip = 'user' AND status_text = 'active' LIMIT 1) AS status_id |
| | 538 | FROM generate_series(1, 70000) i |
| | 539 | CROSS JOIN data_pools d |
| | 540 | CROSS JOIN LATERAL ( |
| | 541 | SELECT |
| | 542 | CASE WHEN i % 2 = 0 |
| | 543 | THEN d.f_names[ (i % cardinality(d.f_names)) + 1] |
| | 544 | ELSE d.m_names[ (i % cardinality(d.m_names)) + 1] |
| | 545 | END AS fname, |
| | 546 | CASE WHEN i % 2 = 0 |
| | 547 | THEN d.f_surnames[(i % cardinality(d.f_surnames)) + 1] |
| | 548 | ELSE d.m_surnames[(i % cardinality(d.m_surnames)) + 1] |
| | 549 | END AS lname |
| | 550 | ) p; |
| | 551 | |
| | 552 | INSERT INTO Drivers (name, phone, license_number, registration_date, rating, status_id) |
| | 553 | WITH data_pools AS ( |
| | 554 | SELECT |
| | 555 | ARRAY['Petar','Marko','Nikola','Stefan','Daniel','Goran','Bojan','Andrej','Filip','Viktor', |
| | 556 | 'Aleksandar','Dejan','Zoran','Ilija','Dimitar','Kiril','Hristijan','Vladimir','Vasko','Riste', |
| | 557 | 'Martin','Igor','Damjan','Darko','Oliver','Luka','Emil','Stojan','Vlatko','Boris', |
| | 558 | 'Radoslav','Kosta','Mladen','Mile','Petre','Gjorgi','Jovan','Ivan','Sasho','Zlatko', |
| | 559 | 'Bogdan','Tome','Naum','Gligor','Stanko','Rade','Lazo','Arsen','Pavle','Dragan'] AS m_names, |
| | 560 | ARRAY['Arsov','Dimitrov','Stojanov','Petrovski','Iliev','Trajkov','Georgiev','Kostov','Manev', |
| | 561 | 'Ristov','Popov','Jovanov','Nikolov','Mitrev','Ivanov','Sokolov','Markov','Spasov','Angelov', |
| | 562 | 'Vasilev','Pavlov','Gruev','Nikolovski','Filipov','Stefanov','Andonov','Zafirov','Kolev', |
| | 563 | 'Stamenov','Bojkov','Todorov','Petkov','Hristov','Kitanov','Rusev','Dimovski','Miloshevski', |
| | 564 | 'Petreski','Arnaudov','Georgievski','Markoski','Velkov','Stojkovski','Kirovski','Zlatev', |
| | 565 | 'Milevski','Spiridonov','Lazarevski','Atanasov','Bogdanov'] AS m_surnames |
| | 566 | ) |
| | 567 | SELECT |
| | 568 | p.fname || ' ' || p.lname AS name, |
| | 569 | '+3897' || (2000000 + i) AS phone, |
| | 570 | CHR(65 + (i % 26)) || LPAD((floor(random() * 9000000) + 1000000)::int::text, 7, '0') AS license_number, |
| | 571 | (NOW() - interval '3 years' - (random() * interval '2 years'))::DATE AS registration_date, |
| | 572 | ROUND((3.5 + random() * 1.5)::numeric, 2) AS rating, |
| | 573 | (SELECT status_id FROM Status WHERE status_tip = 'driver' AND status_text = 'active' LIMIT 1) AS status_id |
| | 574 | FROM generate_series(1, 200) i |
| | 575 | CROSS JOIN data_pools d |
| | 576 | CROSS JOIN LATERAL ( |
| | 577 | SELECT |
| | 578 | d.m_names[(i % cardinality(d.m_names)) + 1] AS fname, |
| | 579 | d.m_surnames[(i % cardinality(d.m_surnames)) + 1] AS lname |
| | 580 | ) p; |
| | 581 | |
| | 582 | |
| | 583 | INSERT INTO Admins (name, username, phone, email, password_hash, created_at, status_id) |
| | 584 | VALUES |
| | 585 | ('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)), |
| | 586 | ('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)), |
| | 587 | ('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)), |
| | 588 | ('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)), |
| | 589 | ('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)), |
| | 590 | ('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)); |
| | 591 | |
| | 592 | |
| | 593 | INSERT INTO Vehicle_ownership (Plate, Traffic_permit, Color, year, vehicle_type_id) |
| | 594 | WITH colors AS ( |
| | 595 | SELECT ARRAY['Crna','Bela','Siva','Srebrena','Crvena','Sina','Zelena','Beza','Kafena','Temnosina'] AS cols |
| | 596 | ) |
| | 597 | SELECT |
| | 598 | -- Табличка: 2 букви + 3 цифри + 2 букви, пр: SK123AB |
| | 599 | 'KU' || |
| | 600 | LPAD((100 + i)::text, 4, '0') || |
| | 601 | CHR(65 + ((i * 5) % 26)) || CHR(65 + ((i * 7) % 26)) AS Plate, |
| | 602 | |
| | 603 | -- Сообраќајна: KU + 6 цифри |
| | 604 | CHR(65 + (i % 26)) || CHR(65 + ((i * 3) % 26)) || LPAD((100000 + i)::text, 6, '0') AS Traffic_permit, |
| | 605 | |
| | 606 | -- Боја |
| | 607 | c.cols[(i % cardinality(c.cols)) + 1] AS Color, |
| | 608 | |
| | 609 | -- Година: возилата се од 2010 до 2023 |
| | 610 | 2010 + (i % 14) AS year, |
| | 611 | |
| | 612 | -- vehicle_type_id: рандом од 1 до 50 |
| | 613 | (1 + (i % 50)) AS vehicle_type_id |
| | 614 | |
| | 615 | FROM generate_series(1, 300) i |
| | 616 | CROSS JOIN colors c; |
| | 617 | |
| | 618 | INSERT INTO Active_drivers (driver_id, current_location_id, is_available, working_start, working_end,vehicle_ownership_id) |
| | 619 | SELECT |
| | 620 | d.driver_id, |
| | 621 | -- Random location_id од постоечките локации |
| | 622 | (1 + (d.driver_id % 14000)) AS current_location_id, |
| | 623 | -- 70% се available, 30% се зафатени |
| | 624 | CASE WHEN d.driver_id % 10 < 7 THEN true ELSE false END AS is_available, |
| | 625 | -- Работната смена почнала пред 1-8 часа |
| | 626 | (NOW() - (random() * interval '8 hours'))::TIMESTAMP AS working_start, |
| | 627 | -- Работната смена завршува за 2-10 часа |
| | 628 | (NOW() + (random() * interval '10 hours'))::TIMESTAMP AS working_end, |
| | 629 | -- Да се знае кое авто моментлано го вози |
| | 630 | CASE |
| | 631 | WHEN d.driver_id < 100 |
| | 632 | THEN d.driver_id + (random(0,1) * 200) |
| | 633 | ELSE d.driver_id |
| | 634 | END AS vehicle_ownership_id |
| | 635 | FROM Drivers d |
| | 636 | -- Земаме само 150 од 200 возачи (75%) |
| | 637 | WHERE d.driver_id % 4 != 0 |
| | 638 | LIMIT 150; |
| | 639 | |
| | 640 | |
| | 641 | INSERT INTO Drivers_Vehicle_ownership (driver_id, Vehicle_ownership_id) |
| | 642 | |
| | 643 | -- Sekoj vozac dobiva svoe primарно vozilo (1:1) |
| | 644 | SELECT |
| | 645 | d.driver_id, |
| | 646 | -- Voziloto se mapira deterministicki: vozac 1→vozilo 1, vozac 2→vozilo 2 ... |
| | 647 | d.driver_id AS Vehicle_ownership_id |
| | 648 | FROM Drivers d |
| | 649 | WHERE d.driver_id <= 200 -- site 200 vozaci |
| | 650 | |
| | 651 | UNION ALL |
| | 652 | |
| | 653 | -- Prvite 100 vozaci dobivaat i vtoro vozilo (od 201 do 300) |
| | 654 | SELECT |
| | 655 | d.driver_id, |
| | 656 | d.driver_id + 200 AS Vehicle_ownership_id |
| | 657 | FROM Drivers d |
| | 658 | WHERE d.driver_id <= 100; |
| | 659 | |
| | 660 | --Rides |
| | 661 | DO $$ |
| | 662 | DECLARE |
| | 663 | batch_size INT := 1000000; |
| | 664 | total_rows INT := 10000000; |
| | 665 | batches INT := total_rows / batch_size; |
| | 666 | b INT; |
| | 667 | |
| | 668 | max_user INT; |
| | 669 | max_driver INT; |
| | 670 | max_location INT; |
| | 671 | |
| | 672 | sid_requested INT; |
| | 673 | sid_accepted INT; |
| | 674 | sid_in_progress INT; |
| | 675 | sid_completed INT; |
| | 676 | sid_cancelled INT; |
| | 677 | BEGIN |
| | 678 | SELECT MAX(user_id) INTO max_user FROM Users; |
| | 679 | SELECT MAX(driver_id) INTO max_driver FROM Drivers; |
| | 680 | SELECT MAX(location_id) INTO max_location FROM Locations; |
| | 681 | |
| | 682 | SELECT status_id INTO sid_requested FROM Status WHERE status_tip='ride' AND status_text='requested' LIMIT 1; |
| | 683 | SELECT status_id INTO sid_accepted FROM Status WHERE status_tip='ride' AND status_text='accepted' LIMIT 1; |
| | 684 | SELECT status_id INTO sid_in_progress FROM Status WHERE status_tip='ride' AND status_text='in_progress' LIMIT 1; |
| | 685 | SELECT status_id INTO sid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1; |
| | 686 | SELECT status_id INTO sid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1; |
| | 687 | |
| | 688 | RAISE NOTICE 'max_user=%, max_driver=%, max_location=%', max_user, max_driver, max_location; |
| | 689 | |
| | 690 | FOR b IN 1..batches LOOP |
| | 691 | |
| | 692 | INSERT INTO Rides ( |
| | 693 | user_id, |
| | 694 | driver_id, |
| | 695 | pickup_location_id, |
| | 696 | dropoff_location_id, |
| | 697 | status_id, |
| | 698 | request_time, |
| | 699 | pickup_time, |
| | 700 | start_time, |
| | 701 | end_time, |
| | 702 | distance_km, |
| | 703 | final_price, |
| | 704 | discount_percentage, |
| | 705 | vehicle_ownership_id |
| | 706 | ) |
| | 707 | WITH |
| | 708 | |
| | 709 | -- -------------------------------------------------------- |
| | 710 | -- CTE 1: Realna cena po vozac spored tipot na voziloto |
| | 711 | -- Economy: base=80, per_km=40 |
| | 712 | -- Luxury: base=100, per_km=60 |
| | 713 | -- Ako ima 2 vozila → go zemame luksoznoto (MAX cena) |
| | 714 | -- -------------------------------------------------------- |
| | 715 | driver_pricing AS ( |
| | 716 | SELECT |
| | 717 | dvo.driver_id, |
| | 718 | MAX(pr.base_price) AS base_price, |
| | 719 | MAX(pr.price_per_km) AS price_per_km, |
| | 720 | MAX(vt.description) AS vehicle_class |
| | 721 | FROM Drivers_Vehicle_ownership dvo |
| | 722 | JOIN Vehicle_ownership vo ON dvo.Vehicle_ownership_id = vo.Vehicle_ownership_id |
| | 723 | JOIN Vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id |
| | 724 | JOIN Pricing_rules pr ON vt.vehicle_type_id = pr.vehicle_type_id |
| | 725 | GROUP BY dvo.driver_id |
| | 726 | ), |
| | 727 | |
| | 728 | -- -------------------------------------------------------- |
| | 729 | -- CTE 2: Base pool so site ogranichuvanja primeneti |
| | 730 | -- -------------------------------------------------------- |
| | 731 | base_pool AS ( |
| | 732 | SELECT |
| | 733 | s.i, |
| | 734 | |
| | 735 | -- USER / DRIVER (realni, razlichni) |
| | 736 | 1 + (s.i % max_user) AS user_id, |
| | 737 | 1 + ((s.i + floor(random() * 200000)+ 37)::INTEGER % max_driver) AS driver_id, |
| | 738 | |
| | 739 | -- LOKACII |
| | 740 | 1 + (s.i + floor(random() * 200000))::INTEGER % max_location AS pickup_location_id, |
| | 741 | 1 + ((s.i + floor(random() * 200000)+ (14000 / 2))::INTEGER % max_location) AS dropoff_location_id, |
| | 742 | |
| | 743 | -- STATUS |
| | 744 | CASE |
| | 745 | WHEN (s.i % 20) = 0 THEN sid_requested |
| | 746 | WHEN (s.i % 20) IN (1, 2) THEN sid_accepted |
| | 747 | WHEN (s.i % 20) IN (3, 4) THEN sid_in_progress |
| | 748 | WHEN (s.i % 20) IN (18, 19) THEN sid_cancelled |
| | 749 | ELSE sid_completed |
| | 750 | END AS status_id, |
| | 751 | |
| | 752 | -- BASE TIME: random den (poslednite 2 godini), cas 06:00-23:00 |
| | 753 | date_trunc('day', now() - (floor(random() * 730) * interval '1 day')) |
| | 754 | + (21600 + floor(random() * 61200)) * interval '1 second' |
| | 755 | AS request_time, |
| | 756 | |
| | 757 | -- DISTANCA: 0.5 .. 10.0 km (OGRANICHENO NA MAX 10km) |
| | 758 | ROUND((0.5 + random() * 9.5)::numeric, 2) AS distance_km |
| | 759 | |
| | 760 | FROM generate_series( |
| | 761 | (b - 1) * batch_size + 1, |
| | 762 | b * batch_size |
| | 763 | ) s(i) |
| | 764 | ), |
| | 765 | |
| | 766 | -- -------------------------------------------------------- |
| | 767 | -- CTE 3: Presmetaj popust i vreminja vrz osnova na distanca |
| | 768 | -- -------------------------------------------------------- |
| | 769 | enriched AS ( |
| | 770 | SELECT |
| | 771 | bp.*, |
| | 772 | |
| | 773 | -- POPUST: |
| | 774 | -- >= 9km → 10% |
| | 775 | -- < 9km → 0% |
| | 776 | CASE |
| | 777 | WHEN bp.distance_km >= 9 THEN 10.0 |
| | 778 | ELSE 0.0 |
| | 779 | END AS discount_pct, |
| | 780 | |
| | 781 | -- VREMINJA (site od ista request_time baza): |
| | 782 | -- pickup = request + 2..10 min |
| | 783 | bp.request_time |
| | 784 | + (2 + floor(random() * 9)) * interval '1 minute' |
| | 785 | AS pickup_time, |
| | 786 | |
| | 787 | -- start = request + 3..13 min (sekogash posle pickup) |
| | 788 | bp.request_time |
| | 789 | + (3 + floor(random() * 11)) * interval '1 minute' |
| | 790 | AS start_time, |
| | 791 | |
| | 792 | -- end = start + 5..15 min (OGRANICHENO: min 5, max 15) |
| | 793 | bp.request_time |
| | 794 | + (3 + floor(random() * 11)) * interval '1 minute' -- ista start baza |
| | 795 | + (5 + floor(random() * 11)) * interval '1 minute' -- + 5..15 min |
| | 796 | AS end_time |
| | 797 | |
| | 798 | FROM base_pool bp |
| | 799 | ) |
| | 800 | |
| | 801 | -- -------------------------------------------------------- |
| | 802 | -- FINALEN SELECT: JOIN so realni ceni od vozacovoto vozilo |
| | 803 | -- -------------------------------------------------------- |
| | 804 | SELECT |
| | 805 | en.user_id, |
| | 806 | en.driver_id, |
| | 807 | en.pickup_location_id, |
| | 808 | en.dropoff_location_id, |
| | 809 | en.status_id, |
| | 810 | |
| | 811 | en.request_time, |
| | 812 | en.pickup_time, |
| | 813 | en.start_time, |
| | 814 | en.end_time, |
| | 815 | |
| | 816 | en.distance_km, |
| | 817 | |
| | 818 | -- CENA spored tipot na voziloto: |
| | 819 | -- Economy → (80 + km * 40) * (1 - popust%) |
| | 820 | -- Luxury → (100 + km * 60) * (1 - popust%) |
| | 821 | -- Ako vozacot nema vozilo → fallback na Economy cena |
| | 822 | ROUND( |
| | 823 | (COALESCE(dp.base_price, 80) + en.distance_km * COALESCE(dp.price_per_km, 40)) |
| | 824 | * (1.0 - en.discount_pct / 100.0) |
| | 825 | ::numeric, 2) AS final_price, |
| | 826 | |
| | 827 | en.discount_pct AS discount_percentage, |
| | 828 | CASE |
| | 829 | WHEN en.driver_id < 100 |
| | 830 | THEN en.driver_id + (random(0,1) * 200) |
| | 831 | ELSE |
| | 832 | en.driver_id |
| | 833 | END AS vehicle_ownership_id |
| | 834 | |
| | 835 | |
| | 836 | FROM enriched en |
| | 837 | LEFT JOIN driver_pricing dp ON en.driver_id = dp.driver_id; |
| | 838 | |
| | 839 | RAISE NOTICE 'Batch % / % zavrshen — % redovi vkupno', b, batches, b * batch_size; |
| | 840 | |
| | 841 | END LOOP; |
| | 842 | |
| | 843 | update rides set pickup_location_id = 1 + (Rides.ride_id + floor(random() * 200000))::INTEGER% max_location |
| | 844 | where dropoff_location_id=pickup_location_id; |
| | 845 | |
| | 846 | RAISE NOTICE '*** GOTOVO: 10 000 000 redovi vo Rides ***'; |
| | 847 | END $$; |
| | 848 | |
| | 849 | --Payments |
| | 850 | DO $$ |
| | 851 | DECLARE |
| | 852 | batch_size INT := 1000000; |
| | 853 | total_rows INT := 10000000; |
| | 854 | batches INT := total_rows / batch_size; |
| | 855 | b INT; |
| | 856 | |
| | 857 | -- Payment method IDs |
| | 858 | pm_cash INT; |
| | 859 | pm_card INT; |
| | 860 | |
| | 861 | -- Payment status IDs |
| | 862 | sid_pending INT; |
| | 863 | sid_completed INT; |
| | 864 | sid_failed INT; |
| | 865 | sid_refunded INT; |
| | 866 | |
| | 867 | -- Ride status IDs (za da odredime payment status) |
| | 868 | rid_completed INT; |
| | 869 | rid_cancelled INT; |
| | 870 | |
| | 871 | BEGIN |
| | 872 | -- Payment methods |
| | 873 | SELECT payment_method_id INTO pm_cash FROM Payment_methods WHERE payment_tip = 'CASH' LIMIT 1; |
| | 874 | SELECT payment_method_id INTO pm_card FROM Payment_methods WHERE payment_tip = 'CARD' LIMIT 1; |
| | 875 | |
| | 876 | -- Payment statusi |
| | 877 | SELECT status_id INTO sid_pending FROM Status WHERE status_tip='payment' AND status_text='pending' LIMIT 1; |
| | 878 | SELECT status_id INTO sid_completed FROM Status WHERE status_tip='payment' AND status_text='completed' LIMIT 1; |
| | 879 | SELECT status_id INTO sid_failed FROM Status WHERE status_tip='payment' AND status_text='failed' LIMIT 1; |
| | 880 | SELECT status_id INTO sid_refunded FROM Status WHERE status_tip='payment' AND status_text='refunded' LIMIT 1; |
| | 881 | |
| | 882 | -- Ride statusi |
| | 883 | SELECT status_id INTO rid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1; |
| | 884 | SELECT status_id INTO rid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1; |
| | 885 | |
| | 886 | RAISE NOTICE 'pm_cash=%, pm_card=%', pm_cash, pm_card; |
| | 887 | RAISE NOTICE 'payment statusi: pending=%, completed=%, failed=%, refunded=%', |
| | 888 | sid_pending, sid_completed, sid_failed, sid_refunded; |
| | 889 | |
| | 890 | FOR b IN 1..batches LOOP |
| | 891 | |
| | 892 | INSERT INTO Payments ( |
| | 893 | amount, |
| | 894 | payment_method_id, |
| | 895 | payment_time, |
| | 896 | ride_id, |
| | 897 | status_id |
| | 898 | ) |
| | 899 | SELECT |
| | 900 | -- Amount = tocnata cena od ridot |
| | 901 | r.final_price AS amount, |
| | 902 | |
| | 903 | -- Payment method: 60% CASH, 40% CARD |
| | 904 | CASE |
| | 905 | WHEN (r.ride_id % 10) < 6 THEN pm_cash |
| | 906 | ELSE pm_card |
| | 907 | END AS payment_method_id, |
| | 908 | |
| | 909 | -- Payment time = end_time + 1..5 min |
| | 910 | r.end_time + (1 + floor(random() * 5)) * interval '1 minute' |
| | 911 | AS payment_time, |
| | 912 | |
| | 913 | r.ride_id AS ride_id, |
| | 914 | |
| | 915 | -- Payment status spored status na ridot: |
| | 916 | -- completed → completed |
| | 917 | -- cancelled → 50% failed, 50% refunded |
| | 918 | -- site drugi → pending |
| | 919 | CASE |
| | 920 | WHEN r.status_id = rid_completed THEN sid_completed |
| | 921 | WHEN r.status_id = rid_cancelled THEN |
| | 922 | CASE WHEN r.ride_id % 2 = 0 THEN sid_failed |
| | 923 | ELSE sid_refunded |
| | 924 | END |
| | 925 | ELSE sid_pending |
| | 926 | END AS status_id |
| | 927 | |
| | 928 | FROM Rides r |
| | 929 | WHERE r.ride_id BETWEEN (b - 1) * batch_size + 1 |
| | 930 | AND b * batch_size; |
| | 931 | |
| | 932 | RAISE NOTICE 'Batch % / % zavrshen — % payments vkupno', b, batches, b * batch_size; |
| | 933 | |
| | 934 | END LOOP; |
| | 935 | |
| | 936 | RAISE NOTICE '*** GOTOVO: 10 000 000 redovi vo Payments ***'; |
| | 937 | END $$; |
| | 938 | |
| | 939 | INSERT INTO Cancellations ( |
| | 940 | cancelled_by_type, |
| | 941 | cancelled_by_id, |
| | 942 | reason, |
| | 943 | cancellation_fee, |
| | 944 | created_at, |
| | 945 | ride_id |
| | 946 | ) |
| | 947 | WITH |
| | 948 | |
| | 949 | -- Ride status za cancelled |
| | 950 | cancelled_status AS ( |
| | 951 | SELECT status_id FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1 |
| | 952 | ), |
| | 953 | |
| | 954 | -- Realna base_price po vozac (Economy=80, Luxury=100) |
| | 955 | driver_pricing AS ( |
| | 956 | SELECT |
| | 957 | dvo.driver_id, |
| | 958 | MAX(pr.base_price) AS base_price |
| | 959 | FROM Drivers_Vehicle_ownership dvo |
| | 960 | JOIN Vehicle_ownership vo ON dvo.Vehicle_ownership_id = vo.Vehicle_ownership_id |
| | 961 | JOIN Vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id |
| | 962 | JOIN Pricing_rules pr ON vt.vehicle_type_id = pr.vehicle_type_id |
| | 963 | GROUP BY dvo.driver_id |
| | 964 | ), |
| | 965 | |
| | 966 | -- Samo cancelled rides + row_number za ramномерна distribucija |
| | 967 | cancelled_rides AS ( |
| | 968 | SELECT |
| | 969 | r.ride_id, |
| | 970 | r.user_id, |
| | 971 | r.driver_id, |
| | 972 | r.request_time, |
| | 973 | COALESCE(dp.base_price, 80) AS base_price, |
| | 974 | -- row_number() garantira 0..9 ramномерно bez zavisnost od ride_id vrednostite |
| | 975 | (row_number() OVER (ORDER BY r.ride_id) - 1) % 10 AS rn |
| | 976 | FROM Rides r |
| | 977 | JOIN cancelled_status cs ON r.status_id = cs.status_id |
| | 978 | LEFT JOIN driver_pricing dp ON r.driver_id = dp.driver_id |
| | 979 | ) |
| | 980 | |
| | 981 | SELECT |
| | 982 | -- cancelled_by_type: 50% USER (0-4), 30% DRIVER (5-7), 20% ADMIN (8-9) |
| | 983 | CASE |
| | 984 | WHEN rn IN (0,1,2,3,4) THEN 'USER' |
| | 985 | WHEN rn IN (5,6,7) THEN 'DRIVER' |
| | 986 | ELSE 'ADMIN' |
| | 987 | END AS cancelled_by_type, |
| | 988 | |
| | 989 | -- cancelled_by_id: realen ID |
| | 990 | CASE |
| | 991 | WHEN rn IN (0,1,2,3,4) THEN user_id |
| | 992 | WHEN rn IN (5,6,7) THEN driver_id |
| | 993 | ELSE 1 + (ride_id % 6) |
| | 994 | END AS cancelled_by_id, |
| | 995 | |
| | 996 | -- Reason spored tipot |
| | 997 | CASE |
| | 998 | WHEN rn IN (0,1,2,3,4) THEN |
| | 999 | CASE (ride_id % 3) |
| | 1000 | WHEN 0 THEN 'Променив планови и повеќе не ми треба превоз' |
| | 1001 | WHEN 1 THEN 'Чекањето беше предолго' |
| | 1002 | ELSE 'Внесов погрешна локација за подигање' |
| | 1003 | END |
| | 1004 | WHEN rn IN (5,6,7) THEN |
| | 1005 | CASE (ride_id % 3) |
| | 1006 | WHEN 0 THEN 'Не можам да ја пронајдам точната локација на патникот' |
| | 1007 | WHEN 1 THEN 'Патникот не се појави на местото за подигање' |
| | 1008 | ELSE 'Технички проблем со возилото' |
| | 1009 | END |
| | 1010 | ELSE |
| | 1011 | CASE (ride_id % 3) |
| | 1012 | WHEN 0 THEN 'Вожњата е откажана поради сомнителна активност' |
| | 1013 | WHEN 1 THEN 'Откажано поради дупликат барање' |
| | 1014 | ELSE 'Откажано поради системска грешка' |
| | 1015 | END |
| | 1016 | END AS reason, |
| | 1017 | |
| | 1018 | -- Fee: samo USER plaka, Driver i Admin = 0 |
| | 1019 | CASE |
| | 1020 | WHEN rn IN (0,1,2,3,4) THEN base_price |
| | 1021 | ELSE 0.00 |
| | 1022 | END AS cancellation_fee, |
| | 1023 | |
| | 1024 | -- created_at = request_time + 1..30 min |
| | 1025 | request_time + (1 + floor(random() * 30)) * interval '1 minute' |
| | 1026 | AS created_at, |
| | 1027 | |
| | 1028 | ride_id |
| | 1029 | FROM cancelled_rides; |
| | 1030 | |
| | 1031 | |
| | 1032 | |
| | 1033 | DO $$ |
| | 1034 | DECLARE |
| | 1035 | batch_size INT := 500000; |
| | 1036 | b INT; |
| | 1037 | max_ride INT; |
| | 1038 | batches INT; |
| | 1039 | |
| | 1040 | mid_1 INT; mid_2 INT; mid_3 INT; mid_4 INT; |
| | 1041 | mid_5 INT; mid_6 INT; mid_7 INT; mid_8 INT; mid_9 INT; |
| | 1042 | |
| | 1043 | sid_notif_sent INT; |
| | 1044 | sid_notif_fail INT; |
| | 1045 | |
| | 1046 | rid_completed INT; |
| | 1047 | rid_cancelled INT; |
| | 1048 | rid_in_progress INT; |
| | 1049 | rid_accepted INT; |
| | 1050 | rid_requested INT; |
| | 1051 | |
| | 1052 | pay_completed INT; |
| | 1053 | pay_failed INT; |
| | 1054 | BEGIN |
| | 1055 | -- Message IDs |
| | 1056 | SELECT message_id INTO mid_1 FROM Messages WHERE message_text LIKE 'Vaseto baranje e uspesno%' LIMIT 1; |
| | 1057 | SELECT message_id INTO mid_2 FROM Messages WHERE message_text LIKE 'Vozacot go prifati%' LIMIT 1; |
| | 1058 | SELECT message_id INTO mid_3 FROM Messages WHERE message_text LIKE 'Vozacot go odbi%' LIMIT 1; |
| | 1059 | SELECT message_id INTO mid_4 FROM Messages WHERE message_text LIKE 'Vozacot pristigna%' LIMIT 1; |
| | 1060 | SELECT message_id INTO mid_5 FROM Messages WHERE message_text LIKE 'Vozenjeto e otkazano od strana na vozac%' LIMIT 1; |
| | 1061 | SELECT message_id INTO mid_6 FROM Messages WHERE message_text LIKE 'Vozenjeto e otkazano od vasa%' LIMIT 1; |
| | 1062 | SELECT message_id INTO mid_7 FROM Messages WHERE message_text LIKE 'Plakjanjeto e uspesno%' LIMIT 1; |
| | 1063 | SELECT message_id INTO mid_8 FROM Messages WHERE message_text LIKE 'Plakjanjeto ne uspea%' LIMIT 1; |
| | 1064 | SELECT message_id INTO mid_9 FROM Messages WHERE message_text LIKE 'Nemate dostapni%' LIMIT 1; |
| | 1065 | |
| | 1066 | -- Notification statusi (koristime ride status kako proxy za sent/fail) |
| | 1067 | SELECT status_id INTO sid_notif_sent FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1; |
| | 1068 | SELECT status_id INTO sid_notif_fail FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1; |
| | 1069 | |
| | 1070 | -- Ride statusi |
| | 1071 | SELECT status_id INTO rid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1; |
| | 1072 | SELECT status_id INTO rid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1; |
| | 1073 | SELECT status_id INTO rid_in_progress FROM Status WHERE status_tip='ride' AND status_text='in_progress' LIMIT 1; |
| | 1074 | SELECT status_id INTO rid_accepted FROM Status WHERE status_tip='ride' AND status_text='accepted' LIMIT 1; |
| | 1075 | SELECT status_id INTO rid_requested FROM Status WHERE status_tip='ride' AND status_text='requested' LIMIT 1; |
| | 1076 | |
| | 1077 | -- Payment statusi |
| | 1078 | SELECT status_id INTO pay_completed FROM Status WHERE status_tip='payment' AND status_text='completed' LIMIT 1; |
| | 1079 | SELECT status_id INTO pay_failed FROM Status WHERE status_tip='payment' AND status_text='failed' LIMIT 1; |
| | 1080 | |
| | 1081 | SELECT MAX(ride_id) INTO max_ride FROM Rides; |
| | 1082 | batches := CEIL(max_ride::numeric / batch_size); |
| | 1083 | |
| | 1084 | RAISE NOTICE 'Pocnuvame so % batches po % rides', batches, batch_size; |
| | 1085 | |
| | 1086 | -- =========================================================== |
| | 1087 | -- FLOW 1: COMPLETED rides |
| | 1088 | -- Poraki: 1→2→4→7 |
| | 1089 | -- Vreminja: request→pickup→start→end+2min |
| | 1090 | -- =========================================================== |
| | 1091 | FOR b IN 1..batches LOOP |
| | 1092 | INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id) |
| | 1093 | SELECT * FROM ( |
| | 1094 | |
| | 1095 | -- Poraka 1: Baranjeto e uspesno isprateno → request_time |
| | 1096 | SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent |
| | 1097 | FROM Rides r |
| | 1098 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1099 | AND r.status_id = rid_completed |
| | 1100 | |
| | 1101 | UNION ALL |
| | 1102 | |
| | 1103 | -- Poraka 2: Vozacot go prifati → pickup_time |
| | 1104 | SELECT mid_2, r.pickup_time, r.user_id, r.ride_id, sid_notif_sent |
| | 1105 | FROM Rides r |
| | 1106 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1107 | AND r.status_id = rid_completed |
| | 1108 | |
| | 1109 | UNION ALL |
| | 1110 | |
| | 1111 | -- Poraka 4: Vozacot pristigna → start_time |
| | 1112 | SELECT mid_4, r.start_time, r.user_id, r.ride_id, sid_notif_sent |
| | 1113 | FROM Rides r |
| | 1114 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1115 | AND r.status_id = rid_completed |
| | 1116 | |
| | 1117 | UNION ALL |
| | 1118 | |
| | 1119 | -- Poraka 7: Plakjanjeto uspesno → end_time + 2..5 min |
| | 1120 | SELECT mid_7, |
| | 1121 | r.end_time + (2 + floor(random()*4)) * interval '1 minute', |
| | 1122 | r.user_id, r.ride_id, sid_notif_sent |
| | 1123 | FROM Rides r |
| | 1124 | JOIN Payments p ON r.ride_id = p.ride_id AND p.status_id = pay_completed |
| | 1125 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1126 | AND r.status_id = rid_completed |
| | 1127 | |
| | 1128 | ) flows; |
| | 1129 | |
| | 1130 | RAISE NOTICE '[COMPLETED] Batch % / %', b, batches; |
| | 1131 | END LOOP; |
| | 1132 | |
| | 1133 | -- =========================================================== |
| | 1134 | -- FLOW 2: CANCELLED BY USER |
| | 1135 | -- Poraki: 1→6→8 |
| | 1136 | -- Vreminja: request→request+1..10min→end+2min |
| | 1137 | -- =========================================================== |
| | 1138 | FOR b IN 1..batches LOOP |
| | 1139 | INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id) |
| | 1140 | SELECT * FROM ( |
| | 1141 | |
| | 1142 | -- Poraka 1: Baranjeto isprateno → request_time |
| | 1143 | SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent |
| | 1144 | FROM Rides r |
| | 1145 | JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER' |
| | 1146 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1147 | AND r.status_id = rid_cancelled |
| | 1148 | |
| | 1149 | UNION ALL |
| | 1150 | |
| | 1151 | -- Poraka 6: Otkazano od vasa strana → request_time + 1..10 min |
| | 1152 | SELECT mid_6, |
| | 1153 | r.request_time + (1 + floor(random()*10)) * interval '1 minute', |
| | 1154 | r.user_id, r.ride_id, sid_notif_fail |
| | 1155 | FROM Rides r |
| | 1156 | JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER' |
| | 1157 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1158 | AND r.status_id = rid_cancelled |
| | 1159 | |
| | 1160 | UNION ALL |
| | 1161 | |
| | 1162 | -- Poraka 8: Plakjanjeto ne uspea → end_time + 2..5 min |
| | 1163 | SELECT mid_8, |
| | 1164 | r.end_time + (2 + floor(random()*4)) * interval '1 minute', |
| | 1165 | r.user_id, r.ride_id, sid_notif_fail |
| | 1166 | FROM Rides r |
| | 1167 | JOIN Payments p ON r.ride_id = p.ride_id AND p.status_id = pay_failed |
| | 1168 | JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER' |
| | 1169 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1170 | AND r.status_id = rid_cancelled |
| | 1171 | |
| | 1172 | ) flows; |
| | 1173 | |
| | 1174 | RAISE NOTICE '[CANCELLED USER] Batch % / %', b, batches; |
| | 1175 | END LOOP; |
| | 1176 | |
| | 1177 | -- =========================================================== |
| | 1178 | -- FLOW 3: CANCELLED BY DRIVER |
| | 1179 | -- Poraki: 1→2→5 |
| | 1180 | -- Vreminja: request→pickup→request+5..15min |
| | 1181 | -- =========================================================== |
| | 1182 | FOR b IN 1..batches LOOP |
| | 1183 | INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id) |
| | 1184 | SELECT * FROM ( |
| | 1185 | |
| | 1186 | -- Poraka 1: Baranjeto isprateno → request_time |
| | 1187 | SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent |
| | 1188 | FROM Rides r |
| | 1189 | JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER' |
| | 1190 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1191 | AND r.status_id = rid_cancelled |
| | 1192 | |
| | 1193 | UNION ALL |
| | 1194 | |
| | 1195 | -- Poraka 2: Vozacot prifati → pickup_time |
| | 1196 | SELECT mid_2, r.pickup_time, r.user_id, r.ride_id, sid_notif_sent |
| | 1197 | FROM Rides r |
| | 1198 | JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER' |
| | 1199 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1200 | AND r.status_id = rid_cancelled |
| | 1201 | |
| | 1202 | UNION ALL |
| | 1203 | |
| | 1204 | -- Poraka 5: Otkazano od vozac → request_time + 5..15 min |
| | 1205 | SELECT mid_5, |
| | 1206 | r.request_time + (5 + floor(random()*11)) * interval '1 minute', |
| | 1207 | r.user_id, r.ride_id, sid_notif_fail |
| | 1208 | FROM Rides r |
| | 1209 | JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER' |
| | 1210 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1211 | AND r.status_id = rid_cancelled |
| | 1212 | |
| | 1213 | ) flows; |
| | 1214 | |
| | 1215 | RAISE NOTICE '[CANCELLED DRIVER] Batch % / %', b, batches; |
| | 1216 | END LOOP; |
| | 1217 | |
| | 1218 | -- =========================================================== |
| | 1219 | -- FLOW 4: CANCELLED BY ADMIN |
| | 1220 | -- Poraki: 1→9 |
| | 1221 | -- Vreminja: request→request (momentalno) |
| | 1222 | -- =========================================================== |
| | 1223 | FOR b IN 1..batches LOOP |
| | 1224 | INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id) |
| | 1225 | SELECT * FROM ( |
| | 1226 | |
| | 1227 | -- Poraka 1: Baranjeto isprateno → request_time |
| | 1228 | SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent |
| | 1229 | FROM Rides r |
| | 1230 | JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'ADMIN' |
| | 1231 | WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size |
| | 1232 | AND r.status_id = rid_cancelled |
| | 1233 | |
| | 1234 | UNION ALL |
| | 1235 | |
| | 1236 | -- Poraka 9: Nema dostapni vozaci → request_time (vednas) |
| | 1237 | SELECT mid_9, r.request_time, r.user_id, r.ride_id, sid_notif_fail |
| | 1238 | FROM Rides r |
| | 1239 | |
| | 1240 | ``` |
