DatabaseCreation: dml.sql

File dml.sql, 50.2 KB (added by 231138, 10 days ago)
Line 
1-- ── MANUFACTURERS (20) ───────────────────────────────────────
2INSERT INTO manufacturers (name, country) VALUES
3('Toyota', 'Japan'),
4('Volkswagen', 'Germany'),
5('BMW', 'Germany'),
6('Mercedes-Benz', 'Germany'),
7('Audi', 'Germany'),
8('Ford', 'USA'),
9('Opel', 'Germany'),
10('Renault', 'France'),
11('Peugeot', 'France'),
12('Citroën', 'France'),
13('Fiat', 'Italy'),
14('Alfa Romeo', 'Italy'),
15('Škoda', 'Czech Republic'),
16('SEAT', 'Spain'),
17('Hyundai', 'South Korea'),
18('Kia', 'South Korea'),
19('Nissan', 'Japan'),
20('Honda', 'Japan'),
21('Mazda', 'Japan'),
22('Volvo', 'Sweden');
23
24-- ── CITIES (20 — North Macedonia, real coordinates) ──────────
25INSERT INTO cities (name, country, lat, lng) VALUES
26('Skopje', 'North Macedonia', 41.9973, 21.4280),
27('Bitola', 'North Macedonia', 41.0297, 21.3340),
28('Kumanovo', 'North Macedonia', 42.1322, 21.7144),
29('Prilep', 'North Macedonia', 41.3453, 21.5547),
30('Tetovo', 'North Macedonia', 42.0094, 20.9716),
31('Ohrid', 'North Macedonia', 41.1231, 20.8020),
32('Veles', 'North Macedonia', 41.7153, 21.7745),
33('Štip', 'North Macedonia', 41.7452, 22.1952),
34('Gostivar', 'North Macedonia', 41.7992, 20.9086),
35('Strumica', 'North Macedonia', 41.4378, 22.6429),
36('Kavadarci', 'North Macedonia', 41.4328, 22.0115),
37('Debar', 'North Macedonia', 41.5233, 20.5244),
38('Radoviš', 'North Macedonia', 41.6381, 22.4674),
39('Gevgelija', 'North Macedonia', 41.1428, 22.5036),
40('Kočani', 'North Macedonia', 41.9167, 22.4167),
41('Negotino', 'North Macedonia', 41.4839, 22.0906),
42('Delčevo', 'North Macedonia', 41.9678, 22.7728),
43('Vinica', 'North Macedonia', 41.8797, 22.5097),
44('Kičevo', 'North Macedonia', 41.5147, 20.9575),
45('Struga', 'North Macedonia', 41.1775, 20.6778);
46
47-- ── TOLL POINTS (15 — A1 / A2 / A3 corridors) ────────────────
48INSERT INTO toll_points (name, lat, lng, road_name, price_car, price_van) VALUES
49('Tabanovce', 42.1050, 21.7300, 'A1', 40.00, 70.00),
50('Petrovec', 41.9400, 21.6200, 'A1', 40.00, 70.00),
51('Veles North', 41.7800, 21.7500, 'A1', 40.00, 70.00),
52('Negotino', 41.5000, 22.0700, 'A1', 40.00, 70.00),
53('Demir Kapija', 41.4100, 22.2400, 'A1', 40.00, 70.00),
54('Udovo', 41.2500, 22.3800, 'A1', 40.00, 70.00),
55('Bogorodica', 41.1700, 22.4900, 'A1', 40.00, 70.00),
56('Blace', 42.0200, 21.2900, 'A2', 30.00, 55.00),
57('Brodec', 41.9700, 21.1500, 'A2', 30.00, 55.00),
58('Kičevo', 41.5200, 20.9600, 'A2', 30.00, 55.00),
59('Struga', 41.1800, 20.6800, 'A2', 30.00, 55.00),
60('Miladinovci', 41.9800, 21.5800, 'A3', 25.00, 45.00),
61('Sveti Nikole', 41.8700, 21.9400, 'A3', 25.00, 45.00),
62('Štip', 41.7400, 22.1900, 'A3', 25.00, 45.00),
63('Vinica', 41.8800, 22.5100, 'A3', 25.00, 45.00);
64
65
66-- ============================================================
67-- PHASE 2 — USER DOMAIN
68-- users (50K), passengers (35K), drivers (15K),
69-- user_addresses (80K)
70-- ============================================================
71
72ALTER TABLE users DISABLE TRIGGER ALL;
73ALTER TABLE passengers DISABLE TRIGGER ALL;
74ALTER TABLE drivers DISABLE TRIGGER ALL;
75ALTER TABLE user_addresses DISABLE TRIGGER ALL;
76
77-- ── USERS (50,000) ──────────────────────────────────────────
78INSERT INTO users (name, email, phone, is_verified, created_at, deleted_at)
79WITH
80first_names(n) AS (VALUES (ARRAY[
81 'Aleksandar','Stefan','Nikola','Ivan','Bojan','Darko','Goran','Petar','Zoran','Filip',
82 'Tome','Andrej','Martin','Kire','Dragan','Risto','Marko','Viktor','Dimitar','Gorjan',
83 'Angel','Blagoj','Damjan','Emil','Georgi','Igor','Jovan','Lazar','Mile','Naum',
84 'Oliver','Panche','Radoslav','Saše','Toni','Vasko','Zlatan','Boris','Dejan','Elvin',
85 'Gligor','Hristo','Ilija','Jasmin','Kosta','Ljubčo','Metodij','Orce','Pavle','Robert',
86 'Simon','Trajče','Velko','Zdravko','Dušan','Hristijan','Ivo','Krste','Luka','Mite',
87 'Predrag','Radi','Slave','Trifun','Agon','Blerim','Emir','Fatmir','Gjorgi','Hasan',
88 'Jakub','Bajram','Abdula','Cvetan','Dragi','Niko','Aco','Brane','Done','Erik',
89 'Fidan','Gane','Hari','Ace','Janche','Kole','Lazo','Miro','Nase','Ogi',
90 'Pero','Rade','Sale','Tase','Vane','Artan','Besnik','Drin','Enis','Florent',
91 'Marija','Ana','Elena','Sara','Irena','Maja','Sonja','Kristina','Vesna','Jana',
92 'Lena','Dina','Biljana','Snezana','Angela','Bojana','Danijela','Emilija','Frosina','Gordana',
93 'Hristina','Ivana','Jasna','Katerina','Lidija','Milica','Neda','Olivera','Petra','Radmila',
94 'Sanja','Tatjana','Valentina','Zaneta','Adela','Besa','Dragana','Eleonora','Fatima','Gorica',
95 'Hana','Iskra','Jovana','Kalina','Ljupka','Meri','Nataša','Olga','Rozeta','Sofija',
96 'Tamara','Vera','Zorica','Aneta','Cveta','Dušanka','Evgenija','Grozdana','Ilinka','Jelena',
97 'Kosana','Lepa','Mencija','Nada','Violeta','Teuta','Silvija','Renata','Rada','Nadežda',
98 'Metka','Lila','Keti','Jasmina','Isidora','Hamide','Gena','Florina','Ema','Dafina',
99 'Branka','Amira','Alma','Aferdita','Zora','Mirjana','Sandra','Karolina','Nina','Tijana',
100 'Vlatka','Anda','Ceca','Deja','Roza','Tanja','Urška','Vikica','Žana','Mila'
101])),
102last_names(n) AS (VALUES (ARRAY[
103 'Petrov','Nikolov','Stojanoski','Jovanov','Iliev','Ristov','Gjorgjiev','Dimov','Janev','Kocev',
104 'Trajanov','Blazevski','Cvetkov','Andonov','Micev','Todorov','Stefanov','Georgiev','Atanasov','Velkov',
105 'Apostolов','Bojadziev','Cvetanovski','Dojcinov','Efremov','Filipov','Gruevski','Hristov','Ivanovski','Jovanovski',
106 'Kitanovski','Lazarov','Manchev','Naumov','Pavlovski','Risteski','Sazdov','Tasev','Ugrinov','Vasilevski',
107 'Zdravev','Arsov','Babunski','Damjanovski','Elenov','Fazliovski','Gligorov','Hadžiev','Isakov','Jakimovski',
108 'Kostovski','Lazareski','Mihajlov','Nedelkovski','Petreski','Radeski','Spasov','Tanev','Uzunov','Veličkovski',
109 'Zafirov','Acevski','Brankov','Doncev','Ferizi','Gorgioski','Halili','Ibrahimović','Jasarević','Krasniqi',
110 'Limani','Mustafov','Nuredinov','Osmani','Popov','Rašidov','Selimi','Trpeski','Veljanoski','Zejneli',
111 'Apostoloski','Belev','Crvenkovski','Dukoski','Ejupov','Frchkovski','Grcev','Hristoski','Ilievski','Jovanoski',
112 'Kunovski','Lozanovski','Mirčevski','Nacevski','Ognenovski','Pendovski','Rizov','Stavrev','Trajanoski','Yordanov',
113 'Zlatanoski','Andonovski','Canevski','Daskalovski','Efimov','Filiposki','Galevski','Hajdarovski','Ivanoski','Jakimov',
114 'Kitanov','Lazaroski','Naumoski','Petrevski','Ristevski','Simov','Tasevski','Velickov','Zdraveski','Acoski',
115 'Bundalevski','Čemerski','Dončev','Fejzulai','Gjorgjieski','Hadžieva','Ismaili','Jančevski','Korubin','Lazarević',
116 'Mitev','Nikoloski','Ognjanov','Penov','Radev','Sazdoski','Teodosievski','Urosevski','Vanevski','Zografski',
117 'Adamovski','Belčev','Cuckovski','Damevski','Etemi','Fildišev','Gordanoski','Hajrizi','Idrizi','Jankulovski',
118 'Kolevski','Lirovski','Milosavljeski','Necev','Omerov','Pavlov','Stojanov','Trajkoski','Vitanov','Andreevski',
119 'Bogevski','Dimitrovski','Ferati','Gligoroski','Hajredini','Ibrahimi','Jakovleski','Koleski','Markovska','Nikolovski',
120 'Orovčanski','Popovski','Ristovski','Stanojeski','Taleski','Ugrinoska','Veselinoski','Zdravkoski','Aleksoski','Bogdanovski',
121 'Čočkoski','Dabeski','Eftimoski','Fidanoski','Gavrovski','Hristomanov','Ilčevski','Josifoski','Kanevski','Lovreski',
122 'Micevski','Nečev','Ovaneski','Pesevski','Runtevski','Spasevski','Trajčevski','Stojkovski','Todoroski','Veljanoski2'
123]))
124SELECT
125 (SELECT n FROM first_names)[ ((i-1) % 200) + 1 ]
126 || ' ' ||
127 (SELECT n FROM last_names) [ ((i-1)/200 + ((i-1) % 200) * 11) % 200 + 1 ] AS name,
128 'user' || i || '@drivenet.mk' AS email,
129 '+3892' || LPAD(i::text, 7, '0') AS phone,
130 (hashint8(i::bigint * 3) & 2147483647) % 10 < 7 AS is_verified,
131 '2022-01-01'::timestamp
132 + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 1096) AS created_at,
133 CASE WHEN (hashint8(i::bigint * 13) & 2147483647) % 20 = 0
134 THEN '2022-01-01'::timestamp
135 + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 1096)
136 + make_interval(days => 1 + (hashint8(i::bigint * 17) & 2147483647) % 365)
137 ELSE NULL
138 END AS deleted_at
139FROM generate_series(1, 50000) gs(i);
140
141-- ── PASSENGERS (users 1–35,000) ─────────────────────────────
142INSERT INTO passengers (user_id, smoking, pets, max_detour_percent, is_active, created_at)
143SELECT
144 i,
145 (hashint8(i::bigint * 5) & 2147483647) % 5 = 0 AS smoking,
146 (hashint8(i::bigint * 11) & 2147483647) % 7 = 0 AS pets,
147 CASE WHEN (hashint8(i::bigint * 17) & 2147483647) % 10 < 4
148 THEN ROUND((5 + (hashint8(i::bigint * 23) & 2147483647) % 46)::numeric, 2)
149 ELSE NULL
150 END AS max_detour_percent,
151 (hashint8(i::bigint * 29) & 2147483647) % 20 > 0 AS is_active,
152 '2022-01-01'::timestamp
153 + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 1096) AS created_at
154FROM generate_series(1, 35000) gs(i);
155
156-- ── DRIVERS (users 35,001–50,000) ───────────────────────────
157INSERT INTO drivers
158 (user_id, license_number, license_class, license_expiry,
159 status, verified_at, is_active, created_at)
160WITH base AS (
161 SELECT
162 i,
163 35000 + i AS user_id,
164 'MK-LIC-' || LPAD(i::text, 6, '0') AS license_number,
165 (ARRAY['B','B','B','B+E','C','C+E','D'])
166 [(hashint8(i::bigint * 3) & 2147483647) % 7 + 1] AS license_class,
167 '2025-01-01'::date
168 + make_interval(days => (hashint8(i::bigint * 5) & 2147483647) % 2190)
169 AS license_expiry,
170 CASE
171 WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 75 THEN 'active'
172 WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 87 THEN 'pending'
173 WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 97 THEN 'suspended'
174 ELSE 'banned'
175 END AS status,
176 '2022-01-01'::timestamp
177 + make_interval(days => (hashint8(i::bigint * 11) & 2147483647) % 1096)
178 AS created_at,
179 (hashint8(i::bigint * 13) & 2147483647) % 10 AS rnd
180 FROM generate_series(1, 15000) gs(i)
181)
182SELECT
183 user_id, license_number, license_class, license_expiry, status,
184 CASE WHEN status <> 'pending'
185 THEN created_at + make_interval(days => (hashint8(i::bigint * 17) & 2147483647) % 180 + 1)
186 ELSE NULL
187 END AS verified_at,
188 rnd > 0 AS is_active,
189 created_at
190FROM base;
191
192-- ── USER_ADDRESSES (80,000) ──────────────────────────────────
193INSERT INTO user_addresses (user_id, label, address, lat, lng)
194SELECT
195 i, 'Home',
196 'ul. ' ||
197 (ARRAY['Partizanska','Makedonija','Ilindenska','Vasil Gjorgov','Dame Gruev',
198 'Borka Taleski','Orce Nikolov','Jane Sandanski','Kliment Ohridski'])[(i % 9) + 1]
199 || ' br.' || (1 + (hashint8(i::bigint * 3) & 2147483647) % 99)::text,
200 40.85 + ((hashint8(i::bigint * 5) & 2147483647) % 15200) / 10000.0,
201 20.45 + ((hashint8(i::bigint * 7) & 2147483647) % 25800) / 10000.0
202FROM generate_series(1, 50000) gs(i)
203UNION ALL
204SELECT
205 i, 'Work',
206 'ul. ' ||
207 (ARRAY['Industriska','Tehnička','Komercijalna','Stara Čaršija','Centar','Novo Maalo'])[(i % 6) + 1]
208 || ' br.' || (1 + (hashint8(i::bigint * 11) & 2147483647) % 99)::text,
209 40.85 + ((hashint8(i::bigint * 13) & 2147483647) % 15200) / 10000.0,
210 20.45 + ((hashint8(i::bigint * 17) & 2147483647) % 25800) / 10000.0
211FROM generate_series(1, 30000) gs(i);
212
213ALTER TABLE users ENABLE TRIGGER ALL;
214ALTER TABLE passengers ENABLE TRIGGER ALL;
215ALTER TABLE drivers ENABLE TRIGGER ALL;
216ALTER TABLE user_addresses ENABLE TRIGGER ALL;
217
218ANALYZE users; ANALYZE passengers; ANALYZE drivers; ANALYZE user_addresses;
219
220
221-- ============================================================
222-- PHASE 3 — VEHICLES + ROUTES DOMAIN
223-- vehicle_models (120), locations (160), vehicles (18K),
224-- vehicle_ownership (18K), routes (400), route_stops (1 600)
225-- ============================================================
226
227ALTER TABLE vehicle_models DISABLE TRIGGER ALL;
228ALTER TABLE vehicles DISABLE TRIGGER ALL;
229ALTER TABLE vehicle_ownership DISABLE TRIGGER ALL;
230ALTER TABLE locations DISABLE TRIGGER ALL;
231ALTER TABLE routes DISABLE TRIGGER ALL;
232ALTER TABLE route_stops DISABLE TRIGGER ALL;
233
234-- ── VEHICLE MODELS (120 = 20 manufacturers × 6 models) ──────
235INSERT INTO vehicle_models (manufacturer_id, model_name, body_type, fuel_type) VALUES
236(1,'Corolla','sedan','petrol'),(1,'Yaris','hatchback','petrol'),
237(1,'RAV4','suv','hybrid'),(1,'Land Cruiser','suv','diesel'),
238(1,'Camry','sedan','hybrid'),(1,'Avensis','sedan','diesel'),
239(2,'Golf','hatchback','petrol'),(2,'Passat','sedan','diesel'),
240(2,'Tiguan','suv','petrol'),(2,'Polo','hatchback','petrol'),
241(2,'Transporter','van','diesel'),(2,'Caddy','van','diesel'),
242(3,'3 Series','sedan','petrol'),(3,'5 Series','sedan','diesel'),
243(3,'X5','suv','diesel'),(3,'1 Series','hatchback','petrol'),
244(3,'X3','suv','petrol'),(3,'M3','coupe','petrol'),
245(4,'C-Class','sedan','petrol'),(4,'E-Class','sedan','diesel'),
246(4,'GLC','suv','diesel'),(4,'A-Class','hatchback','petrol'),
247(4,'Sprinter','van','diesel'),(4,'Vito','van','diesel'),
248(5,'A3','sedan','petrol'),(5,'A4','sedan','diesel'),
249(5,'Q5','suv','diesel'),(5,'A1','hatchback','petrol'),
250(5,'Q3','suv','petrol'),(5,'A6','sedan','diesel'),
251(6,'Focus','hatchback','petrol'),(6,'Mondeo','sedan','diesel'),
252(6,'Kuga','suv','petrol'),(6,'Fiesta','hatchback','petrol'),
253(6,'Transit','van','diesel'),(6,'Galaxy','minivan','diesel'),
254(7,'Astra','hatchback','petrol'),(7,'Vectra','sedan','diesel'),
255(7,'Mokka','suv','petrol'),(7,'Corsa','hatchback','petrol'),
256(7,'Vivaro','van','diesel'),(7,'Zafira','minivan','petrol'),
257(8,'Clio','hatchback','petrol'),(8,'Megane','sedan','petrol'),
258(8,'Kadjar','suv','diesel'),(8,'Twingo','hatchback','petrol'),
259(8,'Trafic','van','diesel'),(8,'Scenic','minivan','diesel'),
260(9,'208','hatchback','petrol'),(9,'308','hatchback','diesel'),
261(9,'3008','suv','diesel'),(9,'107','hatchback','petrol'),
262(9,'Partner','van','diesel'),(9,'5008','minivan','diesel'),
263(10,'C3','hatchback','petrol'),(10,'C4','sedan','petrol'),
264(10,'C5 Aircross','suv','diesel'),(10,'C1','hatchback','petrol'),
265(10,'Berlingo','van','diesel'),(10,'C4 Picasso','minivan','petrol'),
266(11,'Punto','hatchback','petrol'),(11,'500','hatchback','petrol'),
267(11,'Tipo','sedan','diesel'),(11,'Bravo','hatchback','diesel'),
268(11,'Ducato','van','diesel'),(11,'Doblo','minivan','diesel'),
269(12,'Giulia','sedan','petrol'),(12,'Stelvio','suv','diesel'),
270(12,'Giulietta','hatchback','petrol'),(12,'156','sedan','petrol'),
271(12,'147','hatchback','diesel'),(12,'Tonale','suv','hybrid'),
272(13,'Octavia','sedan','diesel'),(13,'Fabia','hatchback','petrol'),
273(13,'Superb','sedan','diesel'),(13,'Karoq','suv','petrol'),
274(13,'Kodiaq','suv','diesel'),(13,'Rapid','sedan','petrol'),
275(14,'Ibiza','hatchback','petrol'),(14,'Leon','hatchback','petrol'),
276(14,'Ateca','suv','diesel'),(14,'Arona','suv','petrol'),
277(14,'Tarraco','suv','diesel'),(14,'Toledo','sedan','petrol'),
278(15,'i20','hatchback','petrol'),(15,'i30','hatchback','diesel'),
279(15,'Tucson','suv','petrol'),(15,'i10','hatchback','petrol'),
280(15,'Santa Fe','suv','diesel'),(15,'Ioniq','hatchback','electric'),
281(16,'Rio','hatchback','petrol'),(16,'Ceed','hatchback','diesel'),
282(16,'Sportage','suv','petrol'),(16,'Picanto','hatchback','petrol'),
283(16,'Sorento','suv','diesel'),(16,'Niro','suv','hybrid'),
284(17,'Micra','hatchback','petrol'),(17,'Juke','suv','petrol'),
285(17,'Qashqai','suv','diesel'),(17,'Note','hatchback','petrol'),
286(17,'X-Trail','suv','diesel'),(17,'Leaf','hatchback','electric'),
287(18,'Jazz','hatchback','petrol'),(18,'Civic','sedan','petrol'),
288(18,'CR-V','suv','hybrid'),(18,'HR-V','suv','petrol'),
289(18,'e','hatchback','electric'),(18,'Accord','sedan','petrol'),
290(19,'Mazda2','hatchback','petrol'),(19,'Mazda3','sedan','petrol'),
291(19,'CX-5','suv','diesel'),(19,'Mazda6','sedan','diesel'),
292(19,'CX-3','suv','petrol'),(19,'MX-5','coupe','petrol'),
293(20,'V40','hatchback','diesel'),(20,'S60','sedan','petrol'),
294(20,'XC40','suv','diesel'),(20,'XC60','suv','petrol'),
295(20,'V60','estate','diesel'),(20,'S90','sedan','hybrid');
296
297-- ── LOCATIONS (160 = 20 cities × 8 types) ───────────────────
298INSERT INTO locations (city_id, name, address, lat, lng)
299SELECT
300 c.id,
301 (ARRAY['Bus Station','Train Station','City Center','Hospital',
302 'University','Airport','Shopping Mall','Market Square'])[p.pos]
303 || ' ' || c.name,
304 NULL,
305 ROUND((c.lat + (p.pos - 4.5) * 0.018)::numeric, 7),
306 ROUND((c.lng + (p.pos - 4.5) * 0.022)::numeric, 7)
307FROM cities c
308CROSS JOIN generate_series(1, 8) p(pos)
309ORDER BY c.id, p.pos;
310
311-- ── VEHICLES (18,000) ────────────────────────────────────────
312INSERT INTO vehicles (model_id, year, seats, license_plate, vehicle_type, color, is_active)
313SELECT
314 m.id,
315 1995 + (hashint8(i::bigint * 5) & 2147483647) % 30,
316 CASE
317 WHEN m.body_type IN ('van','minivan') THEN 5 + (hashint8(i::bigint * 7) & 2147483647) % 3
318 ELSE 4 + (hashint8(i::bigint * 7) & 2147483647) % 2
319 END,
320 (ARRAY['SK','BT','KU','PP','TT','OH','VE','ST','GS','SR',
321 'KV','DC','RA','GV','KO','NE','DE','VI'])
322 [((i-1) % 18) + 1]
323 || LPAD(((i-1) / 18 + 1)::text, 4, '0')
324 || 'MK',
325 CASE WHEN m.body_type IN ('van','minivan') THEN 'van' ELSE 'car' END,
326 (ARRAY['Black','White','Silver','Gray','Blue','Red',
327 'Green','Brown','Yellow','Orange'])
328 [(hashint8(i::bigint * 11) & 2147483647) % 10 + 1],
329 (hashint8(i::bigint * 13) & 2147483647) % 20 > 0
330FROM generate_series(1, 18000) gs(i)
331JOIN vehicle_models m ON m.id = ((i-1) % 120) + 1;
332
333-- ── VEHICLE OWNERSHIP (18,000) ───────────────────────────────
334INSERT INTO vehicle_ownership (driver_id, vehicle_id, owned_from, owned_to, is_active)
335SELECT
336 ((i-1) % 15000) + 1,
337 i,
338 '2019-01-01'::date
339 + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 2028),
340 NULL,
341 TRUE
342FROM generate_series(1, 18000) gs(i);
343
344-- ── ROUTES (400) ─────────────────────────────────────────────
345INSERT INTO routes (origin_id, destination_id, distance_km, estimated_duration)
346WITH city_pairs AS (
347 SELECT
348 c1.id AS c1_id, c2.id AS c2_id,
349 (c1.id-1)*8+1 AS orig_loc,
350 (c2.id-1)*8+1 AS dest_loc,
351 GREATEST(5.00,
352 ROUND(SQRT(POWER((c2.lat-c1.lat)*111, 2) +
353 POWER((c2.lng-c1.lng)*83, 2))::numeric, 2)
354 ) AS dist_km
355 FROM cities c1
356 CROSS JOIN cities c2
357 WHERE c1.id < c2.id
358)
359SELECT orig_loc, dest_loc, dist_km, GREATEST(10, ROUND(dist_km * 60.0 / 80)::int) FROM city_pairs
360UNION ALL
361SELECT dest_loc, orig_loc, dist_km, GREATEST(10, ROUND(dist_km * 60.0 / 80)::int) FROM city_pairs
362UNION ALL
363SELECT (c.id-1)*8+1, (c.id-1)*8+3, 4.00, 12 FROM cities c;
364
365-- ── ROUTE STOPS (1,600) ──────────────────────────────────────
366INSERT INTO route_stops (route_id, location_id, stop_order, estimated_time)
367SELECT
368 r.id,
369 CASE s.ord
370 WHEN 0 THEN r.origin_id
371 WHEN 1 THEN ((r.origin_id + r.id * 3) % 160) + 1
372 WHEN 2 THEN ((r.destination_id + r.id * 5) % 160) + 1
373 WHEN 3 THEN r.destination_id
374 END,
375 s.ord,
376 CASE s.ord
377 WHEN 0 THEN '00:00:00'::time
378 WHEN 1 THEN '00:25:00'::time
379 WHEN 2 THEN '00:50:00'::time
380 WHEN 3 THEN '01:15:00'::time
381 END
382FROM routes r
383CROSS JOIN generate_series(0, 3) s(ord);
384
385ALTER TABLE vehicle_models ENABLE TRIGGER ALL;
386ALTER TABLE vehicles ENABLE TRIGGER ALL;
387ALTER TABLE vehicle_ownership ENABLE TRIGGER ALL;
388ALTER TABLE locations ENABLE TRIGGER ALL;
389ALTER TABLE routes ENABLE TRIGGER ALL;
390ALTER TABLE route_stops ENABLE TRIGGER ALL;
391
392ANALYZE vehicle_models; ANALYZE vehicles; ANALYZE vehicle_ownership;
393ANALYZE locations; ANALYZE routes; ANALYZE route_stops;
394
395
396-- ============================================================
397-- PHASE 4 — RIDES DOMAIN
398-- rides (5M), route_segments (15M), ride_status_history (~13.6M)
399-- ============================================================
400
401ALTER TABLE rides DISABLE TRIGGER ALL;
402ALTER TABLE route_segments DISABLE TRIGGER ALL;
403ALTER TABLE ride_status_history DISABLE TRIGGER ALL;
404
405CREATE TEMP TABLE _route_seg_tmpl AS
406SELECT
407 rs1.route_id,
408 rs1.stop_order AS segment_order,
409 rs1.id AS from_stop_id,
410 rs2.id AS to_stop_id,
411 GREATEST(1.00, ROUND((r.distance_km / 3.0)::numeric, 2)) AS seg_dist
412FROM route_stops rs1
413JOIN route_stops rs2
414 ON rs2.route_id = rs1.route_id
415 AND rs2.stop_order = rs1.stop_order + 1
416JOIN routes r ON r.id = rs1.route_id;
417
418CREATE INDEX idx_rst_route_id ON _route_seg_tmpl(route_id);
419
420-- rides — 10 × 500K batches
421INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
422WITH 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))
423SELECT ((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;
424
425INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
426WITH 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))
427SELECT ((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;
428
429INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
430WITH 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))
431SELECT ((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;
432
433INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
434WITH 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))
435SELECT ((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;
436
437INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
438WITH 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))
439SELECT ((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;
440
441INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
442WITH 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))
443SELECT ((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;
444
445INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
446WITH 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))
447SELECT ((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;
448
449INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
450WITH 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))
451SELECT ((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;
452
453INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
454WITH 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))
455SELECT ((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;
456
457INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
458WITH 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))
459SELECT ((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;
460
461ANALYZE rides;
462
463-- route_segments — 10 × 1.5M batches
464INSERT 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;
465INSERT 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;
466INSERT 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;
467INSERT 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;
468INSERT 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;
469INSERT 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;
470INSERT 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;
471INSERT 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;
472INSERT 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;
473INSERT 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;
474
475ANALYZE route_segments;
476
477-- ride_status_history — 3 passes
478INSERT INTO ride_status_history (ride_id, status, changed_at) SELECT id,'scheduled',created_at FROM rides;
479INSERT 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);
480INSERT 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');
481
482ALTER TABLE rides ENABLE TRIGGER ALL;
483ALTER TABLE route_segments ENABLE TRIGGER ALL;
484ALTER TABLE ride_status_history ENABLE TRIGGER ALL;
485
486ANALYZE rides; ANALYZE route_segments; ANALYZE ride_status_history;
487
488
489-- ============================================================
490-- PHASE 5 — BOOKINGS DOMAIN
491-- bookings (12M), booking_status_history (~15M),
492-- passenger_segments (36M), booking_final_fare (12M)
493-- ============================================================
494
495ALTER TABLE bookings DISABLE TRIGGER ALL;
496ALTER TABLE booking_status_history DISABLE TRIGGER ALL;
497ALTER TABLE passenger_segments DISABLE TRIGGER ALL;
498ALTER TABLE booking_final_fare DISABLE TRIGGER ALL;
499
500DROP TABLE IF EXISTS _route_stops_map;
501CREATE TEMP TABLE _route_stops_map AS
502SELECT route_id,
503 MAX(CASE WHEN stop_order=0 THEN id END) AS s0,
504 MAX(CASE WHEN stop_order=1 THEN id END) AS s1,
505 MAX(CASE WHEN stop_order=2 THEN id END) AS s2,
506 MAX(CASE WHEN stop_order=3 THEN id END) AS s3
507FROM route_stops GROUP BY route_id;
508CREATE INDEX idx_rsm_route ON _route_stops_map(route_id);
509
510-- bookings wave 1 / 3
511INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at)
512WITH base AS (
513 SELECT r.id AS ride_id, ((r.id-1)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff,
514 CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*7)&2147483647)%20<16 THEN 'completed'
515 WHEN r.status='completed' AND (hashint8(r.id::bigint*7)&2147483647)%20<19 THEN 'cancelled'
516 WHEN r.status='completed' THEN 'picked_up'
517 WHEN r.status='cancelled' AND (hashint8(r.id::bigint*7)&2147483647)%10<6 THEN 'cancelled'
518 WHEN r.status='cancelled' THEN 'pending'
519 WHEN r.status='in_progress' AND (hashint8(r.id::bigint*7)&2147483647)%10<7 THEN 'picked_up'
520 WHEN r.status='in_progress' THEN 'confirmed'
521 ELSE 'confirmed' END AS bk_status,
522 r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*11)&2147483647)%14) AS bk_created,
523 r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*13)&2147483647)%30) AS pup_at
524 FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id
525)
526SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created,
527 CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END,
528 CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*17)&2147483647)%91) END
529FROM base;
530
531-- bookings wave 2 / 3
532INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at)
533WITH base AS (
534 SELECT r.id AS ride_id, ((r.id-1+7001)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff,
535 CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*23)&2147483647)%20<16 THEN 'completed'
536 WHEN r.status='completed' AND (hashint8(r.id::bigint*23)&2147483647)%20<19 THEN 'cancelled'
537 WHEN r.status='completed' THEN 'picked_up'
538 WHEN r.status='cancelled' AND (hashint8(r.id::bigint*23)&2147483647)%10<6 THEN 'cancelled'
539 WHEN r.status='cancelled' THEN 'pending'
540 WHEN r.status='in_progress' AND (hashint8(r.id::bigint*23)&2147483647)%10<7 THEN 'picked_up'
541 WHEN r.status='in_progress' THEN 'confirmed'
542 ELSE 'confirmed' END AS bk_status,
543 r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*29)&2147483647)%14) AS bk_created,
544 r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*31)&2147483647)%30) AS pup_at
545 FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id
546)
547SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created,
548 CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END,
549 CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*37)&2147483647)%91) END
550FROM base;
551
552-- bookings wave 3 / 3 (rides 1-2M only)
553INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at)
554WITH base AS (
555 SELECT r.id AS ride_id, ((r.id-1+14002)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff,
556 CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*41)&2147483647)%20<16 THEN 'completed'
557 WHEN r.status='completed' AND (hashint8(r.id::bigint*41)&2147483647)%20<19 THEN 'cancelled'
558 WHEN r.status='completed' THEN 'picked_up'
559 WHEN r.status='cancelled' AND (hashint8(r.id::bigint*41)&2147483647)%10<6 THEN 'cancelled'
560 WHEN r.status='cancelled' THEN 'pending'
561 WHEN r.status='in_progress' AND (hashint8(r.id::bigint*41)&2147483647)%10<7 THEN 'picked_up'
562 WHEN r.status='in_progress' THEN 'confirmed'
563 ELSE 'confirmed' END AS bk_status,
564 r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*43)&2147483647)%14) AS bk_created,
565 r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*47)&2147483647)%30) AS pup_at
566 FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id WHERE r.id BETWEEN 1 AND 2000000
567)
568SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created,
569 CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END,
570 CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*53)&2147483647)%91) END
571FROM base;
572
573ANALYZE bookings;
574
575-- booking_status_history — 5 passes
576INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 1 AND 3000000;
577INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 3000001 AND 6000000;
578INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 6000001 AND 9000000;
579INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id > 9000000;
580INSERT INTO booking_status_history (booking_id, status, changed_at)
581SELECT id, CASE WHEN status='completed' THEN 'completed' WHEN status='cancelled' THEN 'cancelled' WHEN status='picked_up' THEN 'picked_up' ELSE 'confirmed' END,
582 created_at+make_interval(hours=>2+(hashint8(id::bigint*5)&2147483647)%22)
583FROM bookings WHERE (hashint8(id::bigint*37)&2147483647)%4=0;
584
585ANALYZE booking_status_history;
586
587-- passenger_segments — 6 × 2M batches
588INSERT 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;
589INSERT 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;
590INSERT 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;
591INSERT 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;
592INSERT 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;
593INSERT 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;
594
595ANALYZE passenger_segments;
596
597-- booking_final_fare — 3 batches
598INSERT 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;
599INSERT 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;
600INSERT 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;
601
602ALTER TABLE bookings ENABLE TRIGGER ALL;
603ALTER TABLE booking_status_history ENABLE TRIGGER ALL;
604ALTER TABLE passenger_segments ENABLE TRIGGER ALL;
605ALTER TABLE booking_final_fare ENABLE TRIGGER ALL;
606
607ANALYZE bookings; ANALYZE booking_status_history; ANALYZE passenger_segments; ANALYZE booking_final_fare;
608
609
610-- ============================================================
611-- PHASE 6 — TOLLS
612-- ride_tolls (~600K), toll_passenger_split (~1.4M)
613-- ============================================================
614
615ALTER TABLE ride_tolls DISABLE TRIGGER ALL;
616ALTER TABLE toll_passenger_split DISABLE TRIGGER ALL;
617
618INSERT INTO ride_tolls (ride_id, toll_point_id, actual_amount_paid, passed_at)
619SELECT r.id, ((hashint8(r.id::bigint*3)&2147483647)%15)+1,
620 ROUND((tp.price_car+((hashint8(r.id::bigint*5)&2147483647)%201)/100.0)::numeric,2),
621 r.departure_time+make_interval(mins=>30+(hashint8(r.id::bigint*7)&2147483647)%91)
622FROM rides r
623JOIN toll_points tp ON tp.id=((hashint8(r.id::bigint*3)&2147483647)%15)+1
624WHERE (hashint8(r.id::bigint*11)&2147483647)%100<12;
625
626ANALYZE ride_tolls;
627
628INSERT INTO toll_passenger_split (ride_toll_id, booking_id, amount_due)
629SELECT rt.id, b.id, ROUND(rt.actual_amount_paid/COUNT(b.id) OVER (PARTITION BY rt.id),2)
630FROM ride_tolls rt JOIN bookings b ON b.ride_id=rt.ride_id;
631
632ALTER TABLE ride_tolls ENABLE TRIGGER ALL;
633ALTER TABLE toll_passenger_split ENABLE TRIGGER ALL;
634
635ANALYZE ride_tolls; ANALYZE toll_passenger_split;
636
637
638-- ============================================================
639-- PHASE 7 — SOCIAL & SYSTEM
640-- ratings (~2.1M), chat_threads (~500K), chat_messages (~5M),
641-- incident_reports (~50K), location_pings (~10M),
642-- notifications (~10M), audit_logs (~200K)
643-- ============================================================
644
645ALTER TABLE ratings DISABLE TRIGGER ALL;
646ALTER TABLE chat_threads DISABLE TRIGGER ALL;
647ALTER TABLE chat_messages DISABLE TRIGGER ALL;
648ALTER TABLE incident_reports DISABLE TRIGGER ALL;
649ALTER TABLE location_pings DISABLE TRIGGER ALL;
650ALTER TABLE notifications DISABLE TRIGGER ALL;
651ALTER TABLE audit_logs DISABLE TRIGGER ALL;
652
653INSERT INTO ratings (reviewer_user_id, reviewee_user_id, ride_id, score, comment, created_at)
654SELECT b.passenger_id, r.driver_id+35000, b.ride_id,
655 (hashint8(b.id::bigint*7)&2147483647)%5+1, NULL,
656 r.departure_time+make_interval(hours=>3+(hashint8(b.id::bigint*5)&2147483647)%24)
657FROM bookings b JOIN rides r ON r.id=b.ride_id
658WHERE b.status='completed' AND (hashint8(b.id::bigint*13)&2147483647)%10<3;
659
660ANALYZE ratings;
661
662INSERT INTO chat_threads (ride_id, participant_1_id, participant_2_id, created_at)
663SELECT r.id, r.driver_id+35000, ((r.id-1)%35000)+1, r.departure_time-make_interval(days=>1)
664FROM rides r WHERE (hashint8(r.id::bigint*7)&2147483647)%10=0;
665
666ANALYZE chat_threads;
667
668INSERT INTO chat_messages (thread_id, sender_id, content, sent_at, is_deleted)
669SELECT ct.id,
670 CASE WHEN s.msg%2=0 THEN ct.participant_1_id ELSE ct.participant_2_id END,
671 (ARRAY['Ok!','Sounds good','When exactly?','See you there','Got it',
672 'On my way','Thanks a lot','Perfect!','Sure thing','Got it, thanks'])[s.msg],
673 ct.created_at+make_interval(mins=>s.msg*15), FALSE
674FROM chat_threads ct CROSS JOIN generate_series(1,10) s(msg);
675
676ANALYZE chat_messages;
677
678INSERT INTO incident_reports (ride_id, reporter_id, type, description, reported_at)
679SELECT r.id, ((r.id-1)%35000)+1,
680 (ARRAY['accident','reckless_driving','harassment','no_show','fraud','other'])[(hashint8(r.id::bigint*3)&2147483647)%6+1],
681 (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],
682 r.departure_time+make_interval(hours=>1)
683FROM rides r WHERE (hashint8(r.id::bigint*7)&2147483647)%100=0;
684
685ANALYZE incident_reports;
686
687-- location_pings — 2 × 2.5M ride batches
688INSERT INTO location_pings (ride_id,user_id,lat,lng,pinged_at,speed)
689SELECT r.id, r.driver_id+35000,
690 ROUND((41.00+((hashint8(r.id::bigint*p.n*5)&2147483647)%12001)/10000.0)::numeric,7),
691 ROUND((20.50+((hashint8(r.id::bigint*p.n*7)&2147483647)%25001)/10000.0)::numeric,7),
692 r.departure_time+make_interval(mins=>p.n*30),
693 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
694FROM rides r CROSS JOIN generate_series(1,2) p(n) WHERE r.id BETWEEN 1 AND 2500000;
695
696INSERT INTO location_pings (ride_id,user_id,lat,lng,pinged_at,speed)
697SELECT r.id, r.driver_id+35000,
698 ROUND((41.00+((hashint8(r.id::bigint*p.n*5)&2147483647)%12001)/10000.0)::numeric,7),
699 ROUND((20.50+((hashint8(r.id::bigint*p.n*7)&2147483647)%25001)/10000.0)::numeric,7),
700 r.departure_time+make_interval(mins=>p.n*30),
701 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
702FROM rides r CROSS JOIN generate_series(1,2) p(n) WHERE r.id BETWEEN 2500001 AND 5000000;
703
704ANALYZE location_pings;
705
706-- notifications — ride reminders (5M) + booking confirmations (5M)
707INSERT INTO notifications (user_id,type,title,body,sent_at,read_at)
708SELECT r.driver_id+35000,'ride','Ride Departing Soon',
709 'You have a ride scheduled for '||TO_CHAR(r.departure_time,'DD Mon HH24:MI'),
710 r.departure_time-make_interval(hours=>2),
711 CASE WHEN (hashint8(r.id::bigint*3)&2147483647)%10<3 THEN r.departure_time-make_interval(hours=>1) ELSE NULL END
712FROM rides r;
713
714INSERT INTO notifications (user_id,type,title,body,sent_at,read_at)
715SELECT b.passenger_id,'booking','Booking Confirmed',
716 'Your booking for ride #'||b.ride_id||' is '||b.status,
717 b.created_at+make_interval(hours=>1),
718 CASE WHEN (hashint8(b.id::bigint*7)&2147483647)%10<4 THEN b.created_at+make_interval(hours=>3) ELSE NULL END
719FROM bookings b WHERE b.id<=5000000;
720
721ANALYZE notifications;
722
723INSERT INTO audit_logs (table_name,record_id,operation,old_data,new_data,changed_at,changed_by)
724SELECT 'rides',id,'INSERT',NULL,'{"status":"'||status||'","driver_id":'||driver_id||'}',created_at,driver_id+35000
725FROM rides WHERE id BETWEEN 4800001 AND 5000000;
726
727ALTER TABLE ratings ENABLE TRIGGER ALL;
728ALTER TABLE chat_threads ENABLE TRIGGER ALL;
729ALTER TABLE chat_messages ENABLE TRIGGER ALL;
730ALTER TABLE incident_reports ENABLE TRIGGER ALL;
731ALTER TABLE location_pings ENABLE TRIGGER ALL;
732ALTER TABLE notifications ENABLE TRIGGER ALL;
733ALTER TABLE audit_logs ENABLE TRIGGER ALL;
734
735ANALYZE ratings; ANALYZE chat_threads; ANALYZE chat_messages; ANALYZE incident_reports;
736ANALYZE location_pings; ANALYZE notifications; ANALYZE audit_logs;
737