-- ── MANUFACTURERS (20) ─────────────────────────────────────── INSERT INTO manufacturers (name, country) VALUES ('Toyota', 'Japan'), ('Volkswagen', 'Germany'), ('BMW', 'Germany'), ('Mercedes-Benz', 'Germany'), ('Audi', 'Germany'), ('Ford', 'USA'), ('Opel', 'Germany'), ('Renault', 'France'), ('Peugeot', 'France'), ('Citroën', 'France'), ('Fiat', 'Italy'), ('Alfa Romeo', 'Italy'), ('Škoda', 'Czech Republic'), ('SEAT', 'Spain'), ('Hyundai', 'South Korea'), ('Kia', 'South Korea'), ('Nissan', 'Japan'), ('Honda', 'Japan'), ('Mazda', 'Japan'), ('Volvo', 'Sweden'); -- ── CITIES (20 — North Macedonia, real coordinates) ────────── INSERT INTO cities (name, country, lat, lng) VALUES ('Skopje', 'North Macedonia', 41.9973, 21.4280), ('Bitola', 'North Macedonia', 41.0297, 21.3340), ('Kumanovo', 'North Macedonia', 42.1322, 21.7144), ('Prilep', 'North Macedonia', 41.3453, 21.5547), ('Tetovo', 'North Macedonia', 42.0094, 20.9716), ('Ohrid', 'North Macedonia', 41.1231, 20.8020), ('Veles', 'North Macedonia', 41.7153, 21.7745), ('Štip', 'North Macedonia', 41.7452, 22.1952), ('Gostivar', 'North Macedonia', 41.7992, 20.9086), ('Strumica', 'North Macedonia', 41.4378, 22.6429), ('Kavadarci', 'North Macedonia', 41.4328, 22.0115), ('Debar', 'North Macedonia', 41.5233, 20.5244), ('Radoviš', 'North Macedonia', 41.6381, 22.4674), ('Gevgelija', 'North Macedonia', 41.1428, 22.5036), ('Kočani', 'North Macedonia', 41.9167, 22.4167), ('Negotino', 'North Macedonia', 41.4839, 22.0906), ('Delčevo', 'North Macedonia', 41.9678, 22.7728), ('Vinica', 'North Macedonia', 41.8797, 22.5097), ('Kičevo', 'North Macedonia', 41.5147, 20.9575), ('Struga', 'North Macedonia', 41.1775, 20.6778); -- ── TOLL POINTS (15 — A1 / A2 / A3 corridors) ──────────────── INSERT INTO toll_points (name, lat, lng, road_name, price_car, price_van) VALUES ('Tabanovce', 42.1050, 21.7300, 'A1', 40.00, 70.00), ('Petrovec', 41.9400, 21.6200, 'A1', 40.00, 70.00), ('Veles North', 41.7800, 21.7500, 'A1', 40.00, 70.00), ('Negotino', 41.5000, 22.0700, 'A1', 40.00, 70.00), ('Demir Kapija', 41.4100, 22.2400, 'A1', 40.00, 70.00), ('Udovo', 41.2500, 22.3800, 'A1', 40.00, 70.00), ('Bogorodica', 41.1700, 22.4900, 'A1', 40.00, 70.00), ('Blace', 42.0200, 21.2900, 'A2', 30.00, 55.00), ('Brodec', 41.9700, 21.1500, 'A2', 30.00, 55.00), ('Kičevo', 41.5200, 20.9600, 'A2', 30.00, 55.00), ('Struga', 41.1800, 20.6800, 'A2', 30.00, 55.00), ('Miladinovci', 41.9800, 21.5800, 'A3', 25.00, 45.00), ('Sveti Nikole', 41.8700, 21.9400, 'A3', 25.00, 45.00), ('Štip', 41.7400, 22.1900, 'A3', 25.00, 45.00), ('Vinica', 41.8800, 22.5100, 'A3', 25.00, 45.00); -- ============================================================ -- PHASE 2 — USER DOMAIN -- users (50K), passengers (35K), drivers (15K), -- user_addresses (80K) -- ============================================================ ALTER TABLE users DISABLE TRIGGER ALL; ALTER TABLE passengers DISABLE TRIGGER ALL; ALTER TABLE drivers DISABLE TRIGGER ALL; ALTER TABLE user_addresses DISABLE TRIGGER ALL; -- ── USERS (50,000) ────────────────────────────────────────── INSERT INTO users (name, email, phone, is_verified, created_at, deleted_at) WITH first_names(n) AS (VALUES (ARRAY[ 'Aleksandar','Stefan','Nikola','Ivan','Bojan','Darko','Goran','Petar','Zoran','Filip', 'Tome','Andrej','Martin','Kire','Dragan','Risto','Marko','Viktor','Dimitar','Gorjan', 'Angel','Blagoj','Damjan','Emil','Georgi','Igor','Jovan','Lazar','Mile','Naum', 'Oliver','Panche','Radoslav','Saše','Toni','Vasko','Zlatan','Boris','Dejan','Elvin', 'Gligor','Hristo','Ilija','Jasmin','Kosta','Ljubčo','Metodij','Orce','Pavle','Robert', 'Simon','Trajče','Velko','Zdravko','Dušan','Hristijan','Ivo','Krste','Luka','Mite', 'Predrag','Radi','Slave','Trifun','Agon','Blerim','Emir','Fatmir','Gjorgi','Hasan', 'Jakub','Bajram','Abdula','Cvetan','Dragi','Niko','Aco','Brane','Done','Erik', 'Fidan','Gane','Hari','Ace','Janche','Kole','Lazo','Miro','Nase','Ogi', 'Pero','Rade','Sale','Tase','Vane','Artan','Besnik','Drin','Enis','Florent', 'Marija','Ana','Elena','Sara','Irena','Maja','Sonja','Kristina','Vesna','Jana', 'Lena','Dina','Biljana','Snezana','Angela','Bojana','Danijela','Emilija','Frosina','Gordana', 'Hristina','Ivana','Jasna','Katerina','Lidija','Milica','Neda','Olivera','Petra','Radmila', 'Sanja','Tatjana','Valentina','Zaneta','Adela','Besa','Dragana','Eleonora','Fatima','Gorica', 'Hana','Iskra','Jovana','Kalina','Ljupka','Meri','Nataša','Olga','Rozeta','Sofija', 'Tamara','Vera','Zorica','Aneta','Cveta','Dušanka','Evgenija','Grozdana','Ilinka','Jelena', 'Kosana','Lepa','Mencija','Nada','Violeta','Teuta','Silvija','Renata','Rada','Nadežda', 'Metka','Lila','Keti','Jasmina','Isidora','Hamide','Gena','Florina','Ema','Dafina', 'Branka','Amira','Alma','Aferdita','Zora','Mirjana','Sandra','Karolina','Nina','Tijana', 'Vlatka','Anda','Ceca','Deja','Roza','Tanja','Urška','Vikica','Žana','Mila' ])), last_names(n) AS (VALUES (ARRAY[ 'Petrov','Nikolov','Stojanoski','Jovanov','Iliev','Ristov','Gjorgjiev','Dimov','Janev','Kocev', 'Trajanov','Blazevski','Cvetkov','Andonov','Micev','Todorov','Stefanov','Georgiev','Atanasov','Velkov', 'Apostolов','Bojadziev','Cvetanovski','Dojcinov','Efremov','Filipov','Gruevski','Hristov','Ivanovski','Jovanovski', 'Kitanovski','Lazarov','Manchev','Naumov','Pavlovski','Risteski','Sazdov','Tasev','Ugrinov','Vasilevski', 'Zdravev','Arsov','Babunski','Damjanovski','Elenov','Fazliovski','Gligorov','Hadžiev','Isakov','Jakimovski', 'Kostovski','Lazareski','Mihajlov','Nedelkovski','Petreski','Radeski','Spasov','Tanev','Uzunov','Veličkovski', 'Zafirov','Acevski','Brankov','Doncev','Ferizi','Gorgioski','Halili','Ibrahimović','Jasarević','Krasniqi', 'Limani','Mustafov','Nuredinov','Osmani','Popov','Rašidov','Selimi','Trpeski','Veljanoski','Zejneli', 'Apostoloski','Belev','Crvenkovski','Dukoski','Ejupov','Frchkovski','Grcev','Hristoski','Ilievski','Jovanoski', 'Kunovski','Lozanovski','Mirčevski','Nacevski','Ognenovski','Pendovski','Rizov','Stavrev','Trajanoski','Yordanov', 'Zlatanoski','Andonovski','Canevski','Daskalovski','Efimov','Filiposki','Galevski','Hajdarovski','Ivanoski','Jakimov', 'Kitanov','Lazaroski','Naumoski','Petrevski','Ristevski','Simov','Tasevski','Velickov','Zdraveski','Acoski', 'Bundalevski','Čemerski','Dončev','Fejzulai','Gjorgjieski','Hadžieva','Ismaili','Jančevski','Korubin','Lazarević', 'Mitev','Nikoloski','Ognjanov','Penov','Radev','Sazdoski','Teodosievski','Urosevski','Vanevski','Zografski', 'Adamovski','Belčev','Cuckovski','Damevski','Etemi','Fildišev','Gordanoski','Hajrizi','Idrizi','Jankulovski', 'Kolevski','Lirovski','Milosavljeski','Necev','Omerov','Pavlov','Stojanov','Trajkoski','Vitanov','Andreevski', 'Bogevski','Dimitrovski','Ferati','Gligoroski','Hajredini','Ibrahimi','Jakovleski','Koleski','Markovska','Nikolovski', 'Orovčanski','Popovski','Ristovski','Stanojeski','Taleski','Ugrinoska','Veselinoski','Zdravkoski','Aleksoski','Bogdanovski', 'Čočkoski','Dabeski','Eftimoski','Fidanoski','Gavrovski','Hristomanov','Ilčevski','Josifoski','Kanevski','Lovreski', 'Micevski','Nečev','Ovaneski','Pesevski','Runtevski','Spasevski','Trajčevski','Stojkovski','Todoroski','Veljanoski2' ])) SELECT (SELECT n FROM first_names)[ ((i-1) % 200) + 1 ] || ' ' || (SELECT n FROM last_names) [ ((i-1)/200 + ((i-1) % 200) * 11) % 200 + 1 ] AS name, 'user' || i || '@drivenet.mk' AS email, '+3892' || LPAD(i::text, 7, '0') AS phone, (hashint8(i::bigint * 3) & 2147483647) % 10 < 7 AS is_verified, '2022-01-01'::timestamp + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 1096) AS created_at, CASE WHEN (hashint8(i::bigint * 13) & 2147483647) % 20 = 0 THEN '2022-01-01'::timestamp + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 1096) + make_interval(days => 1 + (hashint8(i::bigint * 17) & 2147483647) % 365) ELSE NULL END AS deleted_at FROM generate_series(1, 50000) gs(i); -- ── PASSENGERS (users 1–35,000) ───────────────────────────── INSERT INTO passengers (user_id, smoking, pets, max_detour_percent, is_active, created_at) SELECT i, (hashint8(i::bigint * 5) & 2147483647) % 5 = 0 AS smoking, (hashint8(i::bigint * 11) & 2147483647) % 7 = 0 AS pets, CASE WHEN (hashint8(i::bigint * 17) & 2147483647) % 10 < 4 THEN ROUND((5 + (hashint8(i::bigint * 23) & 2147483647) % 46)::numeric, 2) ELSE NULL END AS max_detour_percent, (hashint8(i::bigint * 29) & 2147483647) % 20 > 0 AS is_active, '2022-01-01'::timestamp + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 1096) AS created_at FROM generate_series(1, 35000) gs(i); -- ── DRIVERS (users 35,001–50,000) ─────────────────────────── INSERT INTO drivers (user_id, license_number, license_class, license_expiry, status, verified_at, is_active, created_at) WITH base AS ( SELECT i, 35000 + i AS user_id, 'MK-LIC-' || LPAD(i::text, 6, '0') AS license_number, (ARRAY['B','B','B','B+E','C','C+E','D']) [(hashint8(i::bigint * 3) & 2147483647) % 7 + 1] AS license_class, '2025-01-01'::date + make_interval(days => (hashint8(i::bigint * 5) & 2147483647) % 2190) AS license_expiry, CASE WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 75 THEN 'active' WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 87 THEN 'pending' WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 97 THEN 'suspended' ELSE 'banned' END AS status, '2022-01-01'::timestamp + make_interval(days => (hashint8(i::bigint * 11) & 2147483647) % 1096) AS created_at, (hashint8(i::bigint * 13) & 2147483647) % 10 AS rnd FROM generate_series(1, 15000) gs(i) ) SELECT user_id, license_number, license_class, license_expiry, status, CASE WHEN status <> 'pending' THEN created_at + make_interval(days => (hashint8(i::bigint * 17) & 2147483647) % 180 + 1) ELSE NULL END AS verified_at, rnd > 0 AS is_active, created_at FROM base; -- ── USER_ADDRESSES (80,000) ────────────────────────────────── INSERT INTO user_addresses (user_id, label, address, lat, lng) SELECT i, 'Home', 'ul. ' || (ARRAY['Partizanska','Makedonija','Ilindenska','Vasil Gjorgov','Dame Gruev', 'Borka Taleski','Orce Nikolov','Jane Sandanski','Kliment Ohridski'])[(i % 9) + 1] || ' br.' || (1 + (hashint8(i::bigint * 3) & 2147483647) % 99)::text, 40.85 + ((hashint8(i::bigint * 5) & 2147483647) % 15200) / 10000.0, 20.45 + ((hashint8(i::bigint * 7) & 2147483647) % 25800) / 10000.0 FROM generate_series(1, 50000) gs(i) UNION ALL SELECT i, 'Work', 'ul. ' || (ARRAY['Industriska','Tehnička','Komercijalna','Stara Čaršija','Centar','Novo Maalo'])[(i % 6) + 1] || ' br.' || (1 + (hashint8(i::bigint * 11) & 2147483647) % 99)::text, 40.85 + ((hashint8(i::bigint * 13) & 2147483647) % 15200) / 10000.0, 20.45 + ((hashint8(i::bigint * 17) & 2147483647) % 25800) / 10000.0 FROM generate_series(1, 30000) gs(i); ALTER TABLE users ENABLE TRIGGER ALL; ALTER TABLE passengers ENABLE TRIGGER ALL; ALTER TABLE drivers ENABLE TRIGGER ALL; ALTER TABLE user_addresses ENABLE TRIGGER ALL; ANALYZE users; ANALYZE passengers; ANALYZE drivers; ANALYZE user_addresses; -- ============================================================ -- PHASE 3 — VEHICLES + ROUTES DOMAIN -- vehicle_models (120), locations (160), vehicles (18K), -- vehicle_ownership (18K), routes (400), route_stops (1 600) -- ============================================================ ALTER TABLE vehicle_models DISABLE TRIGGER ALL; ALTER TABLE vehicles DISABLE TRIGGER ALL; ALTER TABLE vehicle_ownership DISABLE TRIGGER ALL; ALTER TABLE locations DISABLE TRIGGER ALL; ALTER TABLE routes DISABLE TRIGGER ALL; ALTER TABLE route_stops DISABLE TRIGGER ALL; -- ── VEHICLE MODELS (120 = 20 manufacturers × 6 models) ────── INSERT INTO vehicle_models (manufacturer_id, model_name, body_type, fuel_type) VALUES (1,'Corolla','sedan','petrol'),(1,'Yaris','hatchback','petrol'), (1,'RAV4','suv','hybrid'),(1,'Land Cruiser','suv','diesel'), (1,'Camry','sedan','hybrid'),(1,'Avensis','sedan','diesel'), (2,'Golf','hatchback','petrol'),(2,'Passat','sedan','diesel'), (2,'Tiguan','suv','petrol'),(2,'Polo','hatchback','petrol'), (2,'Transporter','van','diesel'),(2,'Caddy','van','diesel'), (3,'3 Series','sedan','petrol'),(3,'5 Series','sedan','diesel'), (3,'X5','suv','diesel'),(3,'1 Series','hatchback','petrol'), (3,'X3','suv','petrol'),(3,'M3','coupe','petrol'), (4,'C-Class','sedan','petrol'),(4,'E-Class','sedan','diesel'), (4,'GLC','suv','diesel'),(4,'A-Class','hatchback','petrol'), (4,'Sprinter','van','diesel'),(4,'Vito','van','diesel'), (5,'A3','sedan','petrol'),(5,'A4','sedan','diesel'), (5,'Q5','suv','diesel'),(5,'A1','hatchback','petrol'), (5,'Q3','suv','petrol'),(5,'A6','sedan','diesel'), (6,'Focus','hatchback','petrol'),(6,'Mondeo','sedan','diesel'), (6,'Kuga','suv','petrol'),(6,'Fiesta','hatchback','petrol'), (6,'Transit','van','diesel'),(6,'Galaxy','minivan','diesel'), (7,'Astra','hatchback','petrol'),(7,'Vectra','sedan','diesel'), (7,'Mokka','suv','petrol'),(7,'Corsa','hatchback','petrol'), (7,'Vivaro','van','diesel'),(7,'Zafira','minivan','petrol'), (8,'Clio','hatchback','petrol'),(8,'Megane','sedan','petrol'), (8,'Kadjar','suv','diesel'),(8,'Twingo','hatchback','petrol'), (8,'Trafic','van','diesel'),(8,'Scenic','minivan','diesel'), (9,'208','hatchback','petrol'),(9,'308','hatchback','diesel'), (9,'3008','suv','diesel'),(9,'107','hatchback','petrol'), (9,'Partner','van','diesel'),(9,'5008','minivan','diesel'), (10,'C3','hatchback','petrol'),(10,'C4','sedan','petrol'), (10,'C5 Aircross','suv','diesel'),(10,'C1','hatchback','petrol'), (10,'Berlingo','van','diesel'),(10,'C4 Picasso','minivan','petrol'), (11,'Punto','hatchback','petrol'),(11,'500','hatchback','petrol'), (11,'Tipo','sedan','diesel'),(11,'Bravo','hatchback','diesel'), (11,'Ducato','van','diesel'),(11,'Doblo','minivan','diesel'), (12,'Giulia','sedan','petrol'),(12,'Stelvio','suv','diesel'), (12,'Giulietta','hatchback','petrol'),(12,'156','sedan','petrol'), (12,'147','hatchback','diesel'),(12,'Tonale','suv','hybrid'), (13,'Octavia','sedan','diesel'),(13,'Fabia','hatchback','petrol'), (13,'Superb','sedan','diesel'),(13,'Karoq','suv','petrol'), (13,'Kodiaq','suv','diesel'),(13,'Rapid','sedan','petrol'), (14,'Ibiza','hatchback','petrol'),(14,'Leon','hatchback','petrol'), (14,'Ateca','suv','diesel'),(14,'Arona','suv','petrol'), (14,'Tarraco','suv','diesel'),(14,'Toledo','sedan','petrol'), (15,'i20','hatchback','petrol'),(15,'i30','hatchback','diesel'), (15,'Tucson','suv','petrol'),(15,'i10','hatchback','petrol'), (15,'Santa Fe','suv','diesel'),(15,'Ioniq','hatchback','electric'), (16,'Rio','hatchback','petrol'),(16,'Ceed','hatchback','diesel'), (16,'Sportage','suv','petrol'),(16,'Picanto','hatchback','petrol'), (16,'Sorento','suv','diesel'),(16,'Niro','suv','hybrid'), (17,'Micra','hatchback','petrol'),(17,'Juke','suv','petrol'), (17,'Qashqai','suv','diesel'),(17,'Note','hatchback','petrol'), (17,'X-Trail','suv','diesel'),(17,'Leaf','hatchback','electric'), (18,'Jazz','hatchback','petrol'),(18,'Civic','sedan','petrol'), (18,'CR-V','suv','hybrid'),(18,'HR-V','suv','petrol'), (18,'e','hatchback','electric'),(18,'Accord','sedan','petrol'), (19,'Mazda2','hatchback','petrol'),(19,'Mazda3','sedan','petrol'), (19,'CX-5','suv','diesel'),(19,'Mazda6','sedan','diesel'), (19,'CX-3','suv','petrol'),(19,'MX-5','coupe','petrol'), (20,'V40','hatchback','diesel'),(20,'S60','sedan','petrol'), (20,'XC40','suv','diesel'),(20,'XC60','suv','petrol'), (20,'V60','estate','diesel'),(20,'S90','sedan','hybrid'); -- ── LOCATIONS (160 = 20 cities × 8 types) ─────────────────── INSERT INTO locations (city_id, name, address, lat, lng) SELECT c.id, (ARRAY['Bus Station','Train Station','City Center','Hospital', 'University','Airport','Shopping Mall','Market Square'])[p.pos] || ' ' || c.name, NULL, ROUND((c.lat + (p.pos - 4.5) * 0.018)::numeric, 7), ROUND((c.lng + (p.pos - 4.5) * 0.022)::numeric, 7) FROM cities c CROSS JOIN generate_series(1, 8) p(pos) ORDER BY c.id, p.pos; -- ── VEHICLES (18,000) ──────────────────────────────────────── INSERT INTO vehicles (model_id, year, seats, license_plate, vehicle_type, color, is_active) SELECT m.id, 1995 + (hashint8(i::bigint * 5) & 2147483647) % 30, CASE WHEN m.body_type IN ('van','minivan') THEN 5 + (hashint8(i::bigint * 7) & 2147483647) % 3 ELSE 4 + (hashint8(i::bigint * 7) & 2147483647) % 2 END, (ARRAY['SK','BT','KU','PP','TT','OH','VE','ST','GS','SR', 'KV','DC','RA','GV','KO','NE','DE','VI']) [((i-1) % 18) + 1] || LPAD(((i-1) / 18 + 1)::text, 4, '0') || 'MK', CASE WHEN m.body_type IN ('van','minivan') THEN 'van' ELSE 'car' END, (ARRAY['Black','White','Silver','Gray','Blue','Red', 'Green','Brown','Yellow','Orange']) [(hashint8(i::bigint * 11) & 2147483647) % 10 + 1], (hashint8(i::bigint * 13) & 2147483647) % 20 > 0 FROM generate_series(1, 18000) gs(i) JOIN vehicle_models m ON m.id = ((i-1) % 120) + 1; -- ── VEHICLE OWNERSHIP (18,000) ─────────────────────────────── INSERT INTO vehicle_ownership (driver_id, vehicle_id, owned_from, owned_to, is_active) SELECT ((i-1) % 15000) + 1, i, '2019-01-01'::date + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 2028), NULL, TRUE FROM generate_series(1, 18000) gs(i); -- ── ROUTES (400) ───────────────────────────────────────────── INSERT INTO routes (origin_id, destination_id, distance_km, estimated_duration) WITH city_pairs AS ( SELECT c1.id AS c1_id, c2.id AS c2_id, (c1.id-1)*8+1 AS orig_loc, (c2.id-1)*8+1 AS dest_loc, GREATEST(5.00, ROUND(SQRT(POWER((c2.lat-c1.lat)*111, 2) + POWER((c2.lng-c1.lng)*83, 2))::numeric, 2) ) AS dist_km FROM cities c1 CROSS JOIN cities c2 WHERE c1.id < c2.id ) SELECT orig_loc, dest_loc, dist_km, GREATEST(10, ROUND(dist_km * 60.0 / 80)::int) FROM city_pairs UNION ALL SELECT dest_loc, orig_loc, dist_km, GREATEST(10, ROUND(dist_km * 60.0 / 80)::int) FROM city_pairs UNION ALL SELECT (c.id-1)*8+1, (c.id-1)*8+3, 4.00, 12 FROM cities c; -- ── ROUTE STOPS (1,600) ────────────────────────────────────── INSERT INTO route_stops (route_id, location_id, stop_order, estimated_time) SELECT r.id, CASE s.ord WHEN 0 THEN r.origin_id WHEN 1 THEN ((r.origin_id + r.id * 3) % 160) + 1 WHEN 2 THEN ((r.destination_id + r.id * 5) % 160) + 1 WHEN 3 THEN r.destination_id END, s.ord, CASE s.ord WHEN 0 THEN '00:00:00'::time WHEN 1 THEN '00:25:00'::time WHEN 2 THEN '00:50:00'::time WHEN 3 THEN '01:15:00'::time END FROM routes r CROSS JOIN generate_series(0, 3) s(ord); ALTER TABLE vehicle_models ENABLE TRIGGER ALL; ALTER TABLE vehicles ENABLE TRIGGER ALL; ALTER TABLE vehicle_ownership ENABLE TRIGGER ALL; ALTER TABLE locations ENABLE TRIGGER ALL; ALTER TABLE routes ENABLE TRIGGER ALL; ALTER TABLE route_stops ENABLE TRIGGER ALL; ANALYZE vehicle_models; ANALYZE vehicles; ANALYZE vehicle_ownership; ANALYZE locations; ANALYZE routes; ANALYZE route_stops; -- ============================================================ -- PHASE 4 — RIDES DOMAIN -- rides (5M), route_segments (15M), ride_status_history (~13.6M) -- ============================================================ ALTER TABLE rides DISABLE TRIGGER ALL; ALTER TABLE route_segments DISABLE TRIGGER ALL; ALTER TABLE ride_status_history DISABLE TRIGGER ALL; CREATE TEMP TABLE _route_seg_tmpl AS SELECT rs1.route_id, rs1.stop_order AS segment_order, rs1.id AS from_stop_id, rs2.id AS to_stop_id, GREATEST(1.00, ROUND((r.distance_km / 3.0)::numeric, 2)) AS seg_dist FROM route_stops rs1 JOIN route_stops rs2 ON rs2.route_id = rs1.route_id AND rs2.stop_order = rs1.stop_order + 1 JOIN routes r ON r.id = rs1.route_id; CREATE INDEX idx_rst_route_id ON _route_seg_tmpl(route_id); -- rides — 10 × 500K batches INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(1,500000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(500001,1000000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(1000001,1500000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(1500001,2000000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(2000001,2500000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(2500001,3000000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(3000001,3500000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(3500001,4000000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(4000001,4500000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at) WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(4500001,5000000) gs(i)) SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen; ANALYZE rides; -- route_segments — 10 × 1.5M batches INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 1 AND 500000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 500001 AND 1000000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 1000001 AND 1500000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 1500001 AND 2000000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 2000001 AND 2500000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 2500001 AND 3000000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 3000001 AND 3500000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 3500001 AND 4000000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 4000001 AND 4500000; INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 4500001 AND 5000000; ANALYZE route_segments; -- ride_status_history — 3 passes INSERT INTO ride_status_history (ride_id, status, changed_at) SELECT id,'scheduled',created_at FROM rides; INSERT INTO ride_status_history (ride_id, status, changed_at) SELECT id,'in_progress',created_at+make_interval(mins=>10+(hashint8(id::bigint*3)&2147483647)%51) FROM rides WHERE status IN ('completed','in_progress') OR (status='cancelled' AND (hashint8(id::bigint*29)&2147483647)%2=0); INSERT INTO ride_status_history (ride_id, status, changed_at) SELECT id,status,CASE WHEN status='completed' THEN departure_time+make_interval(mins=>60+(hashint8(id::bigint*7)&2147483647)%121) ELSE created_at+make_interval(mins=>5+(hashint8(id::bigint*7)&2147483647)%26) END FROM rides WHERE status IN ('completed','cancelled'); ALTER TABLE rides ENABLE TRIGGER ALL; ALTER TABLE route_segments ENABLE TRIGGER ALL; ALTER TABLE ride_status_history ENABLE TRIGGER ALL; ANALYZE rides; ANALYZE route_segments; ANALYZE ride_status_history; -- ============================================================ -- PHASE 5 — BOOKINGS DOMAIN -- bookings (12M), booking_status_history (~15M), -- passenger_segments (36M), booking_final_fare (12M) -- ============================================================ ALTER TABLE bookings DISABLE TRIGGER ALL; ALTER TABLE booking_status_history DISABLE TRIGGER ALL; ALTER TABLE passenger_segments DISABLE TRIGGER ALL; ALTER TABLE booking_final_fare DISABLE TRIGGER ALL; DROP TABLE IF EXISTS _route_stops_map; CREATE TEMP TABLE _route_stops_map AS SELECT route_id, MAX(CASE WHEN stop_order=0 THEN id END) AS s0, MAX(CASE WHEN stop_order=1 THEN id END) AS s1, MAX(CASE WHEN stop_order=2 THEN id END) AS s2, MAX(CASE WHEN stop_order=3 THEN id END) AS s3 FROM route_stops GROUP BY route_id; CREATE INDEX idx_rsm_route ON _route_stops_map(route_id); -- bookings wave 1 / 3 INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at) WITH base AS ( SELECT r.id AS ride_id, ((r.id-1)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff, CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*7)&2147483647)%20<16 THEN 'completed' WHEN r.status='completed' AND (hashint8(r.id::bigint*7)&2147483647)%20<19 THEN 'cancelled' WHEN r.status='completed' THEN 'picked_up' WHEN r.status='cancelled' AND (hashint8(r.id::bigint*7)&2147483647)%10<6 THEN 'cancelled' WHEN r.status='cancelled' THEN 'pending' WHEN r.status='in_progress' AND (hashint8(r.id::bigint*7)&2147483647)%10<7 THEN 'picked_up' WHEN r.status='in_progress' THEN 'confirmed' ELSE 'confirmed' END AS bk_status, r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*11)&2147483647)%14) AS bk_created, r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*13)&2147483647)%30) AS pup_at FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id ) SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created, CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END, CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*17)&2147483647)%91) END FROM base; -- bookings wave 2 / 3 INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at) WITH base AS ( SELECT r.id AS ride_id, ((r.id-1+7001)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff, CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*23)&2147483647)%20<16 THEN 'completed' WHEN r.status='completed' AND (hashint8(r.id::bigint*23)&2147483647)%20<19 THEN 'cancelled' WHEN r.status='completed' THEN 'picked_up' WHEN r.status='cancelled' AND (hashint8(r.id::bigint*23)&2147483647)%10<6 THEN 'cancelled' WHEN r.status='cancelled' THEN 'pending' WHEN r.status='in_progress' AND (hashint8(r.id::bigint*23)&2147483647)%10<7 THEN 'picked_up' WHEN r.status='in_progress' THEN 'confirmed' ELSE 'confirmed' END AS bk_status, r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*29)&2147483647)%14) AS bk_created, r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*31)&2147483647)%30) AS pup_at FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id ) SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created, CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END, CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*37)&2147483647)%91) END FROM base; -- bookings wave 3 / 3 (rides 1-2M only) INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at) WITH base AS ( SELECT r.id AS ride_id, ((r.id-1+14002)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff, CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*41)&2147483647)%20<16 THEN 'completed' WHEN r.status='completed' AND (hashint8(r.id::bigint*41)&2147483647)%20<19 THEN 'cancelled' WHEN r.status='completed' THEN 'picked_up' WHEN r.status='cancelled' AND (hashint8(r.id::bigint*41)&2147483647)%10<6 THEN 'cancelled' WHEN r.status='cancelled' THEN 'pending' WHEN r.status='in_progress' AND (hashint8(r.id::bigint*41)&2147483647)%10<7 THEN 'picked_up' WHEN r.status='in_progress' THEN 'confirmed' ELSE 'confirmed' END AS bk_status, r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*43)&2147483647)%14) AS bk_created, r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*47)&2147483647)%30) AS pup_at FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id WHERE r.id BETWEEN 1 AND 2000000 ) SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created, CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END, CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*53)&2147483647)%91) END FROM base; ANALYZE bookings; -- booking_status_history — 5 passes INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 1 AND 3000000; INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 3000001 AND 6000000; INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 6000001 AND 9000000; INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id > 9000000; INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id, CASE WHEN status='completed' THEN 'completed' WHEN status='cancelled' THEN 'cancelled' WHEN status='picked_up' THEN 'picked_up' ELSE 'confirmed' END, created_at+make_interval(hours=>2+(hashint8(id::bigint*5)&2147483647)%22) FROM bookings WHERE (hashint8(id::bigint*37)&2147483647)%4=0; ANALYZE booking_status_history; -- passenger_segments — 6 × 2M batches INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 1 AND 2000000; INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 2000001 AND 4000000; INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 4000001 AND 6000000; INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 6000001 AND 8000000; INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 8000001 AND 10000000; INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id > 10000000; ANALYZE passenger_segments; -- booking_final_fare — 3 batches INSERT INTO booking_final_fare (booking_id,total_amount,calculated_at) SELECT id,ROUND((10.00+((hashint8(id::bigint*7)&2147483647)%4001)/100.0)::numeric,2),created_at+INTERVAL '1 day' FROM bookings WHERE id BETWEEN 1 AND 4000000; INSERT INTO booking_final_fare (booking_id,total_amount,calculated_at) SELECT id,ROUND((10.00+((hashint8(id::bigint*7)&2147483647)%4001)/100.0)::numeric,2),created_at+INTERVAL '1 day' FROM bookings WHERE id BETWEEN 4000001 AND 8000000; INSERT INTO booking_final_fare (booking_id,total_amount,calculated_at) SELECT id,ROUND((10.00+((hashint8(id::bigint*7)&2147483647)%4001)/100.0)::numeric,2),created_at+INTERVAL '1 day' FROM bookings WHERE id > 8000000; ALTER TABLE bookings ENABLE TRIGGER ALL; ALTER TABLE booking_status_history ENABLE TRIGGER ALL; ALTER TABLE passenger_segments ENABLE TRIGGER ALL; ALTER TABLE booking_final_fare ENABLE TRIGGER ALL; ANALYZE bookings; ANALYZE booking_status_history; ANALYZE passenger_segments; ANALYZE booking_final_fare; -- ============================================================ -- PHASE 6 — TOLLS -- ride_tolls (~600K), toll_passenger_split (~1.4M) -- ============================================================ ALTER TABLE ride_tolls DISABLE TRIGGER ALL; ALTER TABLE toll_passenger_split DISABLE TRIGGER ALL; INSERT INTO ride_tolls (ride_id, toll_point_id, actual_amount_paid, passed_at) SELECT r.id, ((hashint8(r.id::bigint*3)&2147483647)%15)+1, ROUND((tp.price_car+((hashint8(r.id::bigint*5)&2147483647)%201)/100.0)::numeric,2), r.departure_time+make_interval(mins=>30+(hashint8(r.id::bigint*7)&2147483647)%91) FROM rides r JOIN toll_points tp ON tp.id=((hashint8(r.id::bigint*3)&2147483647)%15)+1 WHERE (hashint8(r.id::bigint*11)&2147483647)%100<12; ANALYZE ride_tolls; INSERT INTO toll_passenger_split (ride_toll_id, booking_id, amount_due) SELECT rt.id, b.id, ROUND(rt.actual_amount_paid/COUNT(b.id) OVER (PARTITION BY rt.id),2) FROM ride_tolls rt JOIN bookings b ON b.ride_id=rt.ride_id; ALTER TABLE ride_tolls ENABLE TRIGGER ALL; ALTER TABLE toll_passenger_split ENABLE TRIGGER ALL; ANALYZE ride_tolls; ANALYZE toll_passenger_split; -- ============================================================ -- PHASE 7 — SOCIAL & SYSTEM -- ratings (~2.1M), chat_threads (~500K), chat_messages (~5M), -- incident_reports (~50K), location_pings (~10M), -- notifications (~10M), audit_logs (~200K) -- ============================================================ ALTER TABLE ratings DISABLE TRIGGER ALL; ALTER TABLE chat_threads DISABLE TRIGGER ALL; ALTER TABLE chat_messages DISABLE TRIGGER ALL; ALTER TABLE incident_reports DISABLE TRIGGER ALL; ALTER TABLE location_pings DISABLE TRIGGER ALL; ALTER TABLE notifications DISABLE TRIGGER ALL; ALTER TABLE audit_logs DISABLE TRIGGER ALL; INSERT INTO ratings (reviewer_user_id, reviewee_user_id, ride_id, score, comment, created_at) SELECT b.passenger_id, r.driver_id+35000, b.ride_id, (hashint8(b.id::bigint*7)&2147483647)%5+1, NULL, r.departure_time+make_interval(hours=>3+(hashint8(b.id::bigint*5)&2147483647)%24) FROM bookings b JOIN rides r ON r.id=b.ride_id WHERE b.status='completed' AND (hashint8(b.id::bigint*13)&2147483647)%10<3; ANALYZE ratings; INSERT INTO chat_threads (ride_id, participant_1_id, participant_2_id, created_at) SELECT r.id, r.driver_id+35000, ((r.id-1)%35000)+1, r.departure_time-make_interval(days=>1) FROM rides r WHERE (hashint8(r.id::bigint*7)&2147483647)%10=0; ANALYZE chat_threads; INSERT INTO chat_messages (thread_id, sender_id, content, sent_at, is_deleted) SELECT ct.id, CASE WHEN s.msg%2=0 THEN ct.participant_1_id ELSE ct.participant_2_id END, (ARRAY['Ok!','Sounds good','When exactly?','See you there','Got it', 'On my way','Thanks a lot','Perfect!','Sure thing','Got it, thanks'])[s.msg], ct.created_at+make_interval(mins=>s.msg*15), FALSE FROM chat_threads ct CROSS JOIN generate_series(1,10) s(msg); ANALYZE chat_messages; INSERT INTO incident_reports (ride_id, reporter_id, type, description, reported_at) SELECT r.id, ((r.id-1)%35000)+1, (ARRAY['accident','reckless_driving','harassment','no_show','fraud','other'])[(hashint8(r.id::bigint*3)&2147483647)%6+1], (ARRAY['Minor incident during the ride','Unsafe driving behavior observed','Passenger reported discomfort','Driver did not arrive at pickup','Suspicious payment activity','General issue reported by passenger'])[(hashint8(r.id::bigint*3)&2147483647)%6+1], r.departure_time+make_interval(hours=>1) FROM rides r WHERE (hashint8(r.id::bigint*7)&2147483647)%100=0; ANALYZE incident_reports; -- location_pings — 2 × 2.5M ride batches INSERT INTO location_pings (ride_id,user_id,lat,lng,pinged_at,speed) SELECT r.id, r.driver_id+35000, ROUND((41.00+((hashint8(r.id::bigint*p.n*5)&2147483647)%12001)/10000.0)::numeric,7), ROUND((20.50+((hashint8(r.id::bigint*p.n*7)&2147483647)%25001)/10000.0)::numeric,7), r.departure_time+make_interval(mins=>p.n*30), CASE WHEN (hashint8(r.id::bigint*p.n*11)&2147483647)%5=0 THEN NULL ELSE ROUND(((hashint8(r.id::bigint*p.n*13)&2147483647)%131)::numeric,1) END FROM rides r CROSS JOIN generate_series(1,2) p(n) WHERE r.id BETWEEN 1 AND 2500000; INSERT INTO location_pings (ride_id,user_id,lat,lng,pinged_at,speed) SELECT r.id, r.driver_id+35000, ROUND((41.00+((hashint8(r.id::bigint*p.n*5)&2147483647)%12001)/10000.0)::numeric,7), ROUND((20.50+((hashint8(r.id::bigint*p.n*7)&2147483647)%25001)/10000.0)::numeric,7), r.departure_time+make_interval(mins=>p.n*30), CASE WHEN (hashint8(r.id::bigint*p.n*11)&2147483647)%5=0 THEN NULL ELSE ROUND(((hashint8(r.id::bigint*p.n*13)&2147483647)%131)::numeric,1) END FROM rides r CROSS JOIN generate_series(1,2) p(n) WHERE r.id BETWEEN 2500001 AND 5000000; ANALYZE location_pings; -- notifications — ride reminders (5M) + booking confirmations (5M) INSERT INTO notifications (user_id,type,title,body,sent_at,read_at) SELECT r.driver_id+35000,'ride','Ride Departing Soon', 'You have a ride scheduled for '||TO_CHAR(r.departure_time,'DD Mon HH24:MI'), r.departure_time-make_interval(hours=>2), CASE WHEN (hashint8(r.id::bigint*3)&2147483647)%10<3 THEN r.departure_time-make_interval(hours=>1) ELSE NULL END FROM rides r; INSERT INTO notifications (user_id,type,title,body,sent_at,read_at) SELECT b.passenger_id,'booking','Booking Confirmed', 'Your booking for ride #'||b.ride_id||' is '||b.status, b.created_at+make_interval(hours=>1), CASE WHEN (hashint8(b.id::bigint*7)&2147483647)%10<4 THEN b.created_at+make_interval(hours=>3) ELSE NULL END FROM bookings b WHERE b.id<=5000000; ANALYZE notifications; INSERT INTO audit_logs (table_name,record_id,operation,old_data,new_data,changed_at,changed_by) SELECT 'rides',id,'INSERT',NULL,'{"status":"'||status||'","driver_id":'||driver_id||'}',created_at,driver_id+35000 FROM rides WHERE id BETWEEN 4800001 AND 5000000; ALTER TABLE ratings ENABLE TRIGGER ALL; ALTER TABLE chat_threads ENABLE TRIGGER ALL; ALTER TABLE chat_messages ENABLE TRIGGER ALL; ALTER TABLE incident_reports ENABLE TRIGGER ALL; ALTER TABLE location_pings ENABLE TRIGGER ALL; ALTER TABLE notifications ENABLE TRIGGER ALL; ALTER TABLE audit_logs ENABLE TRIGGER ALL; ANALYZE ratings; ANALYZE chat_threads; ANALYZE chat_messages; ANALYZE incident_reports; ANALYZE location_pings; ANALYZE notifications; ANALYZE audit_logs;