DatabaseCreation: DML.sql

File DML.sql, 52.4 KB (added by 231004, 3 weeks ago)
Line 
1SET search_path TO car_dealership;
2TRUNCATE TABLE car_dealership.discount CASCADE;
3TRUNCATE TABLE car_dealership.payment CASCADE;
4TRUNCATE TABLE car_dealership.sale CASCADE;
5TRUNCATE TABLE car_dealership.contract CASCADE;
6TRUNCATE TABLE car_dealership."Order" CASCADE;
7TRUNCATE TABLE car_dealership.testdrive CASCADE;
8TRUNCATE TABLE car_dealership.production CASCADE;
9TRUNCATE TABLE car_dealership.vehicle CASCADE;
10TRUNCATE TABLE car_dealership.engine CASCADE;
11TRUNCATE TABLE car_dealership.configuration CASCADE;
12TRUNCATE TABLE car_dealership.customer CASCADE;
13TRUNCATE TABLE car_dealership.employee CASCADE;
14TRUNCATE TABLE car_dealership.model CASCADE;
15TRUNCATE TABLE car_dealership.factory CASCADE;
16TRUNCATE TABLE car_dealership.brand CASCADE;
17TRUNCATE TABLE car_dealership.enginetype CASCADE;
18TRUNCATE TABLE car_dealership.vehicletype CASCADE;
19TRUNCATE TABLE car_dealership.status CASCADE;
20TRUNCATE TABLE car_dealership.equipmentpackage CASCADE;
21TRUNCATE TABLE car_dealership.equipmenttype CASCADE;
22INSERT INTO car_dealership.factory (id, name)
23VALUES (1, 'NovaForge'),
24 (2, 'IronAxis'),
25 (3, 'VelocityWorks'),
26 (4, 'ApexDrive'),
27 (5, 'TitanAssembly'),
28 (6, 'QuantumMotors'),
29 (7, 'BlueCore Industries'),
30 (8, 'FusionLine'),
31 (9, 'PrimeTorque'),
32 (10, 'Vertex Automotive');
33
34INSERT INTO car_dealership.brand (id, brand)
35VALUES (1, 'BMW'),
36 (2, 'Mercedes-Benz'),
37 (3, 'Audi'),
38 (4, 'Volkswagen'),
39 (5, 'Porsche'),
40 (6, 'Peugeot'),
41 (7, 'Renault'),
42 (8, 'Škoda'),
43 (9, 'Volvo'),
44 (10, 'Toyota'),
45 (11, 'Honda'),
46 (12, 'Nissan'),
47 (13, 'Mazda'),
48 (14, 'Subaru'),
49 (15, 'Mitsubishi'),
50 (16, 'Suzuki'),
51 (17, 'Geely'),
52 (18, 'BYD'),
53 (19, 'Cherry'),
54 (20, 'GWM');
55
56INSERT INTO car_dealership.enginetype
57VALUES (1, 'Petrol'),
58 (2, 'Diesel'),
59 (3, 'Hybrid'),
60 (4, 'Electric');
61
62INSERT INTO car_dealership.vehicletype(id, type)
63VALUES (1, 'Sedan'),
64 (2, 'Hatchback'),
65 (3, 'SUV'),
66 (4, 'Coupe'),
67 (5, 'Convertible'),
68 (6, 'Wagon'),
69 (7, 'Pickup'),
70 (8, 'Van'),
71 (9, 'Minivan'),
72 (10, 'Sports Car'),
73 (11, 'Crossover'),
74 (12, 'Electric'),
75 (13, 'Hybrid'),
76 (14, 'Off-Road');
77
78INSERT INTO car_dealership.status
79VALUES (1, 'In Stock'),
80 (2, 'Ordered'),
81 (3, 'In Production'),
82 (4, 'Reserved'),
83 (5, 'Sold'),
84 (6, 'In Transit');
85
86INSERT INTO car_dealership.equipmenttype (id, name)
87VALUES (1, 'Safety'),
88 (2, 'Comfort'),
89 (3, 'Infotainment'),
90 (4, 'Performance'),
91 (5, 'Driver Assistance'),
92 (6, 'Security');
93
94
95INSERT INTO car_dealership.model (id, model, brand_id, year)
96VALUES
97 -- BMW
98 (1, '3 Series', 1, 1995),
99 (2, '3 Series', 1, 2000),
100 (3, '3 Series', 1, 2005),
101 (4, '3 Series', 1, 2012),
102 (5, '3 Series', 1, 2019),
103 (6, '3 Series', 1, 2023),
104 (7, '5 Series', 1, 1995),
105 (8, '5 Series', 1, 2004),
106 (9, '5 Series', 1, 2010),
107 (10, '5 Series', 1, 2017),
108 (11, '5 Series', 1, 2024),
109 (12, 'X3', 1, 2004),
110 (13, 'X3', 1, 2011),
111 (14, 'X3', 1, 2018),
112 (15, 'X3', 1, 2024),
113 (16, 'X5', 1, 1999),
114 (17, 'X5', 1, 2007),
115 (18, 'X5', 1, 2014),
116 (19, 'X5', 1, 2023),
117
118 -- Mercedes-Benz
119 (20, 'A-Class', 2, 1997),
120 (21, 'A-Class', 2, 2005),
121 (22, 'A-Class', 2, 2013),
122 (23, 'A-Class', 2, 2019),
123 (24, 'C-Class', 2, 1995),
124 (25, 'C-Class', 2, 2001),
125 (26, 'C-Class', 2, 2008),
126 (27, 'C-Class', 2, 2015),
127 (28, 'C-Class', 2, 2022),
128 (29, 'E-Class', 2, 1995),
129 (30, 'E-Class', 2, 2003),
130 (31, 'E-Class', 2, 2010),
131 (32, 'E-Class', 2, 2017),
132 (33, 'E-Class', 2, 2024),
133 (34, 'GLE', 2, 2016),
134 (35, 'GLE', 2, 2020),
135 (36, 'GLE', 2, 2024),
136
137 -- Audi
138 (37, 'A3', 3, 1997),
139 (38, 'A3', 3, 2004),
140 (39, 'A3', 3, 2013),
141 (40, 'A3', 3, 2021),
142 (41, 'A4', 3, 1995),
143 (42, 'A4', 3, 2001),
144 (43, 'A4', 3, 2008),
145 (44, 'A4', 3, 2016),
146 (45, 'A4', 3, 2024),
147 (46, 'A6', 3, 1995),
148 (47, 'A6', 3, 2005),
149 (48, 'A6', 3, 2012),
150 (49, 'A6', 3, 2019),
151 (50, 'Q5', 3, 2009),
152 (51, 'Q5', 3, 2017),
153 (52, 'Q5', 3, 2024),
154
155 -- Volkswagen
156 (53, 'Golf', 4, 1995),
157 (54, 'Golf', 4, 2004),
158 (55, 'Golf', 4, 2009),
159 (56, 'Golf', 4, 2013),
160 (57, 'Golf', 4, 2020),
161 (58, 'Passat', 4, 1997),
162 (59, 'Passat', 4, 2006),
163 (60, 'Passat', 4, 2015),
164 (61, 'Passat', 4, 2024),
165 (62, 'Tiguan', 4, 2008),
166 (63, 'Tiguan', 4, 2016),
167 (64, 'Tiguan', 4, 2024),
168 (65, 'Polo', 4, 1995),
169 (66, 'Polo', 4, 2002),
170 (67, 'Polo', 4, 2010),
171 (68, 'Polo', 4, 2018),
172
173 -- Porsche
174 (69, '911', 5, 1995),
175 (70, '911', 5, 2005),
176 (71, '911', 5, 2012),
177 (72, '911', 5, 2019),
178 (73, 'Cayenne', 5, 2003),
179 (74, 'Cayenne', 5, 2011),
180 (75, 'Cayenne', 5, 2018),
181 (76, 'Macan', 5, 2015),
182 (77, 'Macan', 5, 2022),
183 (78, 'Panamera', 5, 2010),
184 (79, 'Panamera', 5, 2017),
185 (80, 'Panamera', 5, 2024),
186
187 -- Peugeot
188 (81, '206', 6, 1998),
189 (82, '206', 6, 2003),
190 (83, '207', 6, 2006),
191 (84, '208', 6, 2012),
192 (85, '208', 6, 2020),
193 (86, '308', 6, 2008),
194 (87, '308', 6, 2014),
195 (88, '308', 6, 2022),
196 (89, '3008', 6, 2009),
197 (90, '3008', 6, 2017),
198 (91, '3008', 6, 2024),
199 (92, '508', 6, 2011),
200 (93, '508', 6, 2019),
201
202 -- Renault
203 (94, 'Clio', 7, 1998),
204 (95, 'Clio', 7, 2006),
205 (96, 'Clio', 7, 2013),
206 (97, 'Clio', 7, 2020),
207 (98, 'Megane', 7, 1996),
208 (99, 'Megane', 7, 2003),
209 (100, 'Megane', 7, 2009),
210 (101, 'Megane', 7, 2016),
211 (102, 'Captur', 7, 2013),
212 (103, 'Captur', 7, 2020),
213 (104, 'Kadjar', 7, 2015),
214 (105, 'Kadjar', 7, 2022),
215
216 -- Škoda
217 (106, 'Fabia', 8, 1999),
218 (107, 'Fabia', 8, 2007),
219 (108, 'Fabia', 8, 2015),
220 (109, 'Fabia', 8, 2022),
221 (110, 'Octavia', 8, 1997),
222 (111, 'Octavia', 8, 2004),
223 (112, 'Octavia', 8, 2013),
224 (113, 'Octavia', 8, 2021),
225 (114, 'Superb', 8, 2002),
226 (115, 'Superb', 8, 2009),
227 (116, 'Superb', 8, 2016),
228 (117, 'Superb', 8, 2024),
229 (118, 'Kodiaq', 8, 2017),
230 (119, 'Kodiaq', 8, 2024),
231
232 -- Volvo
233 (120, 'S60', 9, 2001),
234 (121, 'S60', 9, 2011),
235 (122, 'S60', 9, 2019),
236 (123, 'S90', 9, 1997),
237 (124, 'S90', 9, 2017),
238 (125, 'S90', 9, 2024),
239 (126, 'XC40', 9, 2018),
240 (127, 'XC40', 9, 2023),
241 (128, 'XC60', 9, 2009),
242 (129, 'XC60', 9, 2018),
243 (130, 'XC60', 9, 2024),
244
245 -- Toyota
246 (131, 'Corolla', 10, 1995),
247 (132, 'Corolla', 10, 2002),
248 (133, 'Corolla', 10, 2007),
249 (134, 'Corolla', 10, 2014),
250 (135, 'Corolla', 10, 2019),
251 (136, 'Camry', 10, 1997),
252 (137, 'Camry', 10, 2007),
253 (138, 'Camry', 10, 2012),
254 (139, 'Camry', 10, 2018),
255 (140, 'RAV4', 10, 1996),
256 (141, 'RAV4', 10, 2006),
257 (142, 'RAV4', 10, 2013),
258 (143, 'RAV4', 10, 2019),
259 (144, 'Yaris', 10, 1999),
260 (145, 'Yaris', 10, 2006),
261 (146, 'Yaris', 10, 2012),
262 (147, 'Yaris', 10, 2021),
263
264 -- Honda
265 (148, 'Civic', 11, 1995),
266 (149, 'Civic', 11, 2001),
267 (150, 'Civic', 11, 2006),
268 (151, 'Civic', 11, 2012),
269 (152, 'Civic', 11, 2022),
270 (153, 'Accord', 11, 1998),
271 (154, 'Accord', 11, 2004),
272 (155, 'Accord', 11, 2013),
273 (156, 'Accord', 11, 2018),
274 (157, 'CR-V', 11, 1997),
275 (158, 'CR-V', 11, 2007),
276 (159, 'CR-V', 11, 2013),
277 (160, 'CR-V', 11, 2023),
278 (161, 'Jazz', 11, 2002),
279 (162, 'Jazz', 11, 2009),
280 (163, 'Jazz', 11, 2015),
281 (164, 'Jazz', 11, 2020),
282
283 -- Nissan
284 (165, 'Micra', 12, 1995),
285 (166, 'Micra', 12, 2003),
286 (167, 'Micra', 12, 2010),
287 (168, 'Micra', 12, 2017),
288 (169, 'Qashqai', 12, 2007),
289 (170, 'Qashqai', 12, 2014),
290 (171, 'Qashqai', 12, 2021),
291 (172, 'X-Trail', 12, 2001),
292 (173, 'X-Trail', 12, 2008),
293 (174, 'X-Trail', 12, 2014),
294 (175, 'X-Trail', 12, 2022),
295 (176, 'Juke', 12, 2010),
296 (177, 'Juke', 12, 2020),
297
298 -- Mazda
299 (178, 'Mazda3', 13, 2004),
300 (179, 'Mazda3', 13, 2009),
301 (180, 'Mazda3', 13, 2014),
302 (181, 'Mazda3', 13, 2019),
303 (182, 'Mazda6', 13, 2002),
304 (183, 'Mazda6', 13, 2008),
305 (184, 'Mazda6', 13, 2013),
306 (185, 'Mazda6', 13, 2018),
307 (186, 'CX-5', 13, 2012),
308 (187, 'CX-5', 13, 2017),
309 (188, 'CX-5', 13, 2022),
310 (189, 'CX-30', 13, 2019),
311 (190, 'CX-30', 13, 2024),
312
313 -- Subaru
314 (191, 'Impreza', 14, 1995),
315 (192, 'Impreza', 14, 2001),
316 (193, 'Impreza', 14, 2008),
317 (194, 'Impreza', 14, 2017),
318 (195, 'Outback', 14, 1996),
319 (196, 'Outback', 14, 2004),
320 (197, 'Outback', 14, 2015),
321 (198, 'Outback', 14, 2021),
322 (199, 'Forester', 14, 1997),
323 (200, 'Forester', 14, 2008),
324 (201, 'Forester', 14, 2014),
325 (202, 'Forester', 14, 2019),
326 (203, 'BRZ', 14, 2012),
327 (204, 'BRZ', 14, 2022),
328
329 -- Mitsubishi
330 (205, 'Lancer', 15, 1995),
331 (206, 'Lancer', 15, 2004),
332 (207, 'Lancer', 15, 2008),
333 (208, 'Outlander', 15, 2003),
334 (209, 'Outlander', 15, 2013),
335 (210, 'Outlander', 15, 2022),
336 (211, 'ASX', 15, 2010),
337 (212, 'ASX', 15, 2017),
338 (213, 'ASX', 15, 2023),
339 (214, 'Eclipse Cross', 15, 2018),
340 (215, 'Eclipse Cross', 15, 2022),
341
342 -- Suzuki
343 (216, 'Swift', 16, 1995),
344 (217, 'Swift', 16, 2005),
345 (218, 'Swift', 16, 2011),
346 (219, 'Swift', 16, 2017),
347 (220, 'Swift', 16, 2024),
348 (221, 'Vitara', 16, 1995),
349 (222, 'Vitara', 16, 2006),
350 (223, 'Vitara', 16, 2015),
351 (224, 'Vitara', 16, 2022),
352 (225, 'SX4', 16, 2006),
353 (226, 'SX4', 16, 2014),
354 (227, 'SX4', 16, 2021),
355 (228, 'Ignis', 16, 2017),
356 (229, 'Ignis', 16, 2020),
357
358 -- Geely (only entered market significantly around 2015+)
359 (230, 'Emgrand', 17, 2009),
360 (231, 'Emgrand', 17, 2016),
361 (232, 'Emgrand', 17, 2022),
362 (233, 'Coolray', 17, 2019),
363 (234, 'Coolray', 17, 2023),
364 (235, 'Atlas', 17, 2017),
365 (236, 'Atlas', 17, 2022),
366 (237, 'Tugella', 17, 2020),
367 (238, 'Tugella', 17, 2024),
368
369 -- BYD (significant from 2015+, mostly electric)
370 (239, 'Han', 18, 2020),
371 (240, 'Han', 18, 2023),
372 (241, 'Tang', 18, 2018),
373 (242, 'Tang', 18, 2022),
374 (243, 'Seal', 18, 2022),
375 (244, 'Seal', 18, 2025),
376 (245, 'Dolphin', 18, 2021),
377 (246, 'Dolphin', 18, 2024),
378
379 -- Chery
380 (247, 'Tiggo 4', 19, 2014),
381 (248, 'Tiggo 4', 19, 2021),
382 (249, 'Tiggo 7', 19, 2016),
383 (250, 'Tiggo 7', 19, 2022),
384 (251, 'Tiggo 8', 19, 2018),
385 (252, 'Tiggo 8', 19, 2023),
386 (253, 'Arrizo 5', 19, 2014),
387 (254, 'Arrizo 5', 19, 2021),
388
389 -- GWM
390 (255, 'Haval H6', 20, 2011),
391 (256, 'Haval H6', 20, 2017),
392 (257, 'Haval H6', 20, 2021),
393 (258, 'Haval Jolion', 20, 2021),
394 (259, 'Haval Jolion', 20, 2024),
395 (260, 'Poer', 20, 2019),
396 (261, 'Poer', 20, 2023),
397 (262, 'Tank 300', 20, 2021),
398 (263, 'Tank 300', 20, 2024);
399
400WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
401 FROM unnest(ARRAY [
402 'John','Emma','Liam','Olivia','Noah','Ava','Ethan','Sophia',
403 'Mia','Lucas','Isabella','Mason','Amelia','Logan','Harper',
404 'James','Evelyn','Benjamin','Ella','Alexander',
405 'Daniel','David','Elena','Ivana','Stefan','Marko','Petar',
406 'Ana','Marija','Filip','Nikola','Sara','Teodora','Kristijan'
407 ]) AS t(val)),
408 last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
409 FROM unnest(ARRAY [
410 'Smith','Brown','Johnson','Wilson','Taylor','Lee','Martin','Anderson',
411 'Thomas','Moore','Jackson','White','Harris','Clark','Lewis',
412 'Walker','Hall','Allen','Young','King',
413 'Petrovski','Stojanov','Trajkovski','Nikolov','Jovanov',
414 'Iliev','Ristov','Georgiev','Kostov','Angelov','Mitrevski'
415 ]) AS t(val)),
416 fn_cnt AS MATERIALIZED (SELECT count(*) c FROM first_names),
417 ln_cnt AS MATERIALIZED (SELECT count(*) c FROM last_names)
418
419INSERT
420INTO car_dealership.customer (id,
421 first_name,
422 last_name,
423 email,
424 phone,
425 created_at)
426SELECT i,
427 fn.val AS first_name,
428 ln.val AS last_name,
429 lower(fn.val || '.' || ln.val || i || '@' ||
430 (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] || '.com') AS email,
431 '+3897' || (i % 8) || lpad((i % 1000000)::text, 6, '0') AS phone,
432 CURRENT_DATE - (i % 5000)
433FROM generate_series(1, 1000000) i
434 JOIN first_names fn ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
435 JOIN last_names ln ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;
436
437
438-- Customer addresses with fixed postal codes per city
439WITH cities AS MATERIALIZED (SELECT *
440 FROM (VALUES (0, 'Skopje', 1000),
441 (1, 'Bitola', 7000),
442 (2, 'Kumanovo', 1300),
443 (3, 'Prilep', 7500),
444 (4, 'Tetovo', 1200),
445 (5, 'Ohrid', 6000),
446 (6, 'Veles', 1400),
447 (7, 'Stip', 2000),
448 (8, 'Strumica', 2400),
449 (9, 'Kavadarci', 1430),
450 (10, 'Gostivar', 1230),
451 (11, 'Kocani', 2300),
452 (12, 'Gevgelija', 1480),
453 (13, 'Debar', 1250),
454 (14, 'Kriva Palanka', 1330),
455 (15, 'Struga', 6330)) AS t(idx, city, postal_code)),
456 streets AS MATERIALIZED (SELECT *
457 FROM (VALUES (0, 'Partizanska'),
458 (1, 'Ilindenska'),
459 (2, 'Goce Delchev'),
460 (3, 'Dame Gruev'),
461 (4, 'Marsal Tito'),
462 (5, 'Boris Trajkovski'),
463 (6, 'Jane Sandanski'),
464 (7, 'Nikola Karev'),
465 (8, '11 Oktomvri'),
466 (9, 'Dimitar Vlahov'),
467 (10, 'Makedonija'),
468 (11, 'Vasil Glavinov'),
469 (12, 'Kuzman Josifovski Pitu'),
470 (13, 'Mirche Acev'),
471 (14, 'Pitu Guli'),
472 (15, 'Blagoja Toska'),
473 (16, 'Ruzveltova'),
474 (17, 'Orce Nikolov'),
475 (18, 'Vodno'),
476 (19, 'Pelagonija')) AS t(idx, street))
477INSERT
478INTO car_dealership.address (id,
479 country,
480 city,
481 postal_code,
482 street,
483 building_number,
484 entry_number,
485 apartment_number,
486 customer_id,
487 factory_id)
488SELECT i AS id,
489 'Macedonia' AS country,
490 c.city,
491 c.postal_code,
492 s.street,
493 (i % 200) + 1 AS building_number,
494 (i % 10) + 1 AS entry_number,
495 (i % 50) + 1 AS apartment_number,
496 i AS customer_id,
497 NULL AS factory_id
498FROM generate_series(1, 1000000) i
499 JOIN cities c ON c.idx = (i % 16)
500 JOIN streets s ON s.idx = (i % 20);
501
502
503-- Factory addresses with fixed postal codes per city
504WITH cities AS MATERIALIZED (SELECT *
505 FROM (VALUES (0, 'Skopje', 1000),
506 (1, 'Bitola', 7000),
507 (2, 'Kumanovo', 1300),
508 (3, 'Prilep', 7500),
509 (4, 'Tetovo', 1200),
510 (5, 'Ohrid', 6000),
511 (6, 'Veles', 1400),
512 (7, 'Stip', 2000),
513 (8, 'Strumica', 2400),
514 (9, 'Kavadarci', 1430),
515 (10, 'Gostivar', 1230),
516 (11, 'Kocani', 2300),
517 (12, 'Gevgelija', 1480),
518 (13, 'Debar', 1250),
519 (14, 'Kriva Palanka', 1330),
520 (15, 'Struga', 6330)) AS t(idx, city, postal_code)),
521 streets AS MATERIALIZED (SELECT *
522 FROM (VALUES (0, 'Partizanska'),
523 (1, 'Ilindenska'),
524 (2, 'Goce Delchev'),
525 (3, 'Dame Gruev'),
526 (4, 'Marsal Tito'),
527 (5, 'Boris Trajkovski'),
528 (6, 'Jane Sandanski'),
529 (7, 'Nikola Karev'),
530 (8, '11 Oktomvri'),
531 (9, 'Dimitar Vlahov'),
532 (10, 'Makedonija'),
533 (11, 'Vasil Glavinov'),
534 (12, 'Kuzman Josifovski Pitu'),
535 (13, 'Mirche Acev'),
536 (14, 'Pitu Guli'),
537 (15, 'Blagoja Toska'),
538 (16, 'Ruzveltova'),
539 (17, 'Orce Nikolov'),
540 (18, 'Vodno'),
541 (19, 'Pelagonija')) AS t(idx, street))
542INSERT
543INTO car_dealership.address (id,
544 country,
545 city,
546 postal_code,
547 street,
548 building_number,
549 entry_number,
550 apartment_number,
551 customer_id,
552 factory_id)
553SELECT 1000000 + i AS id,
554 'Macedonia' AS country,
555 c.city,
556 c.postal_code,
557 s.street,
558 (i % 200) + 1 AS building_number,
559 NULL AS entry_number,
560 NULL AS apartment_number,
561 NULL AS customer_id,
562 i AS factory_id
563FROM generate_series(1, 10) i
564 JOIN cities c ON c.idx = (i % 16)
565 JOIN streets s ON s.idx = (i % 20);
566
567
568INSERT INTO car_dealership.equipmentpackage
569 (id, name, price, description, is_default, equipment_type_id)
570VALUES (1, 'Base', 0,
571 'Standard equipment included in every vehicle',
572 true, 1),
573
574 (2, 'Winter', 1200,
575 'Elements that support harsh and cold winter weather, heating, tires',
576 false, 2),
577
578 (3, 'Sport', 2500,
579 'Performance upgrades including suspension and exhaust enhancements',
580 false, 4),
581
582 (4, 'Luxury', 4000,
583 'Premium materials, leather interior, and high-end comfort features',
584 false, 2),
585
586 (5, 'Safety Plus', 1800,
587 'Advanced driver assistance and safety systems',
588 false, 5),
589
590 (6, 'Tech Pack', 2200,
591 'Infotainment, navigation, and smart connectivity features',
592 false, 3),
593
594 (7, 'Comfort Pack', 2000,
595 'Heated seats, climate control upgrades, and interior comfort enhancements',
596 false, 2);
597
598INSERT INTO car_dealership.packageelement (id, package_id, name, price)
599VALUES (1, 1, 'Air Conditioning', 0),
600 (2, 1, 'ABS Braking System', 0),
601 (3, 1, 'Front Airbags', 0),
602 (4, 1, 'Power Steering', 0),
603
604 (5, 2, 'Heated Front Seats', 300),
605 (6, 2, 'Heated Steering Wheel', 250),
606 (7, 2, 'Winter Tires Set', 700),
607 (8, 2, 'Windshield Defrost System', 200),
608
609 (9, 3, 'Sport Suspension', 900),
610 (10, 3, 'Performance Exhaust System', 1200),
611 (11, 3, 'Sport Steering Mode', 400),
612 (12, 3, 'Paddle Shifters', 350),
613
614 (13, 4, 'Leather Interior', 1500),
615 (14, 4, 'Premium Sound System', 1200),
616 (15, 4, 'Ambient Interior Lighting', 600),
617 (16, 4, 'Panoramic Sunroof', 1800),
618
619 (17, 5, 'Lane Assist System', 800),
620 (18, 5, 'Adaptive Cruise Control', 1000),
621 (19, 5, 'Blind Spot Monitoring', 700),
622 (20, 5, 'Automatic Emergency Braking', 900),
623
624 (21, 6, 'Digital Dashboard', 900),
625 (22, 6, 'Built-in Navigation System', 600),
626 (23, 6, 'Wireless Charging', 400),
627 (24, 6, 'Smartphone Integration', 500),
628
629 (25, 7, 'Dual Zone Climate Control', 500),
630 (26, 7, 'Massage Seats', 1100),
631 (27, 7, 'Rear Seat Heating', 350),
632 (28, 7, 'Electric Seat Adjustment', 450);
633
634
635WITH engine_types AS MATERIALIZED (SELECT MAX(CASE WHEN type = 'Petrol' THEN id END) AS petrol_id,
636 MAX(CASE WHEN type = 'Diesel' THEN id END) AS diesel_id,
637 MAX(CASE WHEN type = 'Hybrid' THEN id END) AS hybrid_id,
638 MAX(CASE WHEN type = 'Electric' THEN id END) AS electric_id
639 FROM car_dealership.enginetype)
640INSERT
641INTO car_dealership.engine (enginenumber, horsepower, enginetypeid)
642SELECT UPPER(
643 CASE
644 WHEN i % 100 < 60 THEN 'PTR'
645 WHEN i % 100 < 88 THEN 'DSL'
646 WHEN i % 100 < 96 THEN 'HYB'
647 ELSE 'ELC'
648 END
649 || CASE
650 WHEN i % 100 < 60 THEN (1000 + (i * 17 % 4001))::text
651 WHEN i % 100 < 88 THEN (1600 + (i * 13 % 1901))::text
652 WHEN i % 100 < 96 THEN (1500 + (i * 11 % 1501))::text
653 ELSE '0000'
654 END
655 || CHR(65 + (i % 26))
656 || lpad(i::text, 7, '0')
657 ) AS enginenumber,
658
659 CASE
660 WHEN i % 100 < 60 THEN 60 + (i * 13 % 441)
661 WHEN i % 100 < 88 THEN 60 + (i * 11 % 341)
662 WHEN i % 100 < 96 THEN 80 + (i * 9 % 321)
663 ELSE 100 + (i * 7 % 501)
664 END::int AS horsepower,
665
666 CASE
667 WHEN i % 100 < 60 THEN et.petrol_id
668 WHEN i % 100 < 88 THEN et.diesel_id
669 WHEN i % 100 < 96 THEN et.hybrid_id
670 ELSE et.electric_id
671 END AS enginetypeid
672
673FROM generate_series(1, 10000000) i
674 CROSS JOIN engine_types et;
675
676
677
678WITH models AS MATERIALIZED (SELECT id,
679 brand_id,
680 model,
681 year,
682 row_number() OVER (ORDER BY id) AS rn
683 FROM car_dealership.model),
684 statuses AS MATERIALIZED (SELECT id,
685 row_number() OVER (ORDER BY id) AS rn
686 FROM car_dealership.status),
687 vehicle_types AS MATERIALIZED (SELECT id, type
688 FROM car_dealership.vehicletype),
689 engines AS MATERIALIZED (SELECT enginenumber,
690 CASE
691 WHEN enginenumber LIKE 'PTR%' THEN 'P'
692 WHEN enginenumber LIKE 'DSL%' THEN 'D'
693 WHEN enginenumber LIKE 'HYB%' THEN 'H'
694 ELSE 'E'
695 END AS engine_code,
696 row_number() OVER (ORDER BY enginenumber) AS rn
697 FROM car_dealership.engine),
698 model_cnt AS MATERIALIZED (SELECT count(*) c FROM models),
699 status_cnt AS MATERIALIZED (SELECT count(*) c FROM statuses),
700 brand_wmi AS MATERIALIZED (SELECT *
701 FROM (VALUES (1, 'WBA'),
702 (2, 'WDB'),
703 (3, 'WAU'),
704 (4, 'WVW'),
705 (5, 'WP0'),
706 (6, 'VF3'),
707 (7, 'VF1'),
708 (8, 'TMB'),
709 (9, 'YV1'),
710 (10, 'JT2'),
711 (11, 'JHM'),
712 (12, 'JN1'),
713 (13, 'JM1'),
714 (14, 'JF1'),
715 (15, 'JA3'),
716 (16, 'JS2'),
717 (17, 'LGX'),
718 (18, 'LBV'),
719 (19, 'LVV'),
720 (20, 'LHG')) AS t(brand_id, wmi)),
721 model_specs AS MATERIALIZED (SELECT *
722 FROM (VALUES ('3 Series', 'Sedan', 25000, 55000),
723 ('5 Series', 'Sedan', 35000, 75000),
724 ('X3', 'SUV', 35000, 70000),
725 ('X5', 'SUV', 50000, 95000),
726 ('A-Class', 'Hatchback', 22000, 45000),
727 ('C-Class', 'Sedan', 28000, 60000),
728 ('E-Class', 'Sedan', 40000, 80000),
729 ('GLE', 'SUV', 55000, 95000),
730 ('A3', 'Hatchback', 22000, 45000),
731 ('A4', 'Sedan', 28000, 58000),
732 ('A6', 'Sedan', 40000, 75000),
733 ('Q5', 'SUV', 40000, 72000),
734 ('Golf', 'Hatchback', 18000, 40000),
735 ('Passat', 'Sedan', 25000, 50000),
736 ('Tiguan', 'SUV', 28000, 52000),
737 ('Polo', 'Hatchback', 13000, 28000),
738 ('911', 'Sports Car', 80000, 180000),
739 ('Cayenne', 'SUV', 70000, 150000),
740 ('Macan', 'SUV', 50000, 90000),
741 ('Panamera', 'Sedan', 85000,
742 160000)) AS t(model_name, vehicle_type, price_min, price_max)),
743 year_codes AS MATERIALIZED (SELECT *
744 FROM (VALUES (1995, 'S'),
745 (1996, 'T'),
746 (1997, 'V'),
747 (1998, 'W'),
748 (1999, 'X'),
749 (2000, 'Y'),
750 (2001, '1'),
751 (2002, '2'),
752 (2003, '3'),
753 (2004, '4'),
754 (2005, '5'),
755 (2006, '6'),
756 (2007, '7'),
757 (2008, '8'),
758 (2009, '9'),
759 (2010, 'A'),
760 (2011, 'B'),
761 (2012, 'C'),
762 (2013, 'D'),
763 (2014, 'E'),
764 (2015, 'F'),
765 (2016, 'G'),
766 (2017, 'H'),
767 (2018, 'J'),
768 (2019, 'K'),
769 (2020, 'L'),
770 (2021, 'M'),
771 (2022, 'N'),
772 (2023, 'P'),
773 (2024, 'R'),
774 (2025, 'S')) AS t(yr, code)),
775 model_full AS MATERIALIZED (SELECT m.rn,
776 m.id AS model_id,
777 m.year,
778 m.brand_id,
779 bw.wmi,
780 yc.code AS year_code,
781 vt.id AS vehicle_type_id,
782 CASE vt.type
783 WHEN 'Sedan' THEN 'AA'
784 WHEN 'Hatchback' THEN 'BB'
785 WHEN 'SUV' THEN 'CC'
786 WHEN 'Coupe' THEN 'DD'
787 WHEN 'Convertible' THEN 'EE'
788 WHEN 'Wagon' THEN 'FF'
789 WHEN 'Pickup' THEN 'GG'
790 WHEN 'Sports Car' THEN 'HH'
791 WHEN 'Crossover' THEN 'JJ'
792 ELSE 'XX'
793 END AS body_code,
794 COALESCE(ms.price_min, 15000) AS price_min,
795 COALESCE(ms.price_max, 50000) AS price_max,
796 (COALESCE(ms.price_min, 15000) + (
797 (COALESCE(ms.price_max, 50000) - COALESCE(ms.price_min, 15000)) *
798 (m.year - 1995)::numeric / 31
799 ))::numeric(10, 2) AS base_price
800 FROM models m
801 LEFT JOIN model_specs ms ON ms.model_name = model
802 JOIN vehicle_types vt ON vt.type = COALESCE(ms.vehicle_type, 'Sedan')
803 JOIN year_codes yc ON yc.yr = m.year
804 JOIN brand_wmi bw ON bw.brand_id = m.brand_id)
805
806INSERT
807INTO car_dealership.vehicle (vin, model_id, status_id, vehicle_type_id,
808 color, price, production_year, engine_number)
809SELECT mf.wmi
810 || mf.body_code
811 || e.engine_code
812 || lpad((mf.model_id % 1000)::text, 3, '0')
813 || mf.year_code
814 || CHR(65 + ((i - 1) / 1000000 % 26))
815 || lpad(((i - 1) % 1000000)::text, 6, '0') AS vin,
816 mf.model_id,
817 s.id AS status_id,
818 mf.vehicle_type_id,
819 (ARRAY ['Black','White','Grey','Silver','Red','Blue'])[(i % 6) + 1],
820 mf.base_price,
821 mf.year AS production_year,
822 e.enginenumber
823FROM generate_series(1, 10000000) i
824 JOIN model_full mf ON mf.rn = (i % (SELECT c FROM model_cnt)) + 1
825 JOIN statuses s ON s.rn = (i % (SELECT c FROM status_cnt)) + 1
826 JOIN engines e ON e.rn = i;
827
828
829
830WITH customers AS MATERIALIZED (SELECT id,
831 row_number() OVER (ORDER BY id) AS rn
832 FROM car_dealership.customer),
833
834 vehicles AS MATERIALIZED (SELECT vin,
835 row_number() OVER (ORDER BY vin) AS rn
836 FROM car_dealership.vehicle),
837
838 vehicle_cnt AS MATERIALIZED (SELECT COUNT(*) AS c FROM vehicles),
839
840 customer_slots AS MATERIALIZED (SELECT id AS customer_id,
841 rn AS customer_rn,
842 CASE
843 WHEN rn % 20 = 0 THEN 1
844 WHEN rn % 20 = 1 THEN 2
845 WHEN rn % 20 < 5 THEN 3
846 WHEN rn % 20 < 9 THEN 4
847 WHEN rn % 20 < 12 THEN 5
848 WHEN rn % 20 < 15 THEN 6
849 WHEN rn % 20 < 18 THEN 7
850 WHEN rn % 20 = 18 THEN 8
851 ELSE 9
852 END AS slot_count
853 FROM customers),
854
855 expanded AS MATERIALIZED (SELECT cs.customer_id,
856 cs.customer_rn,
857 gs.slot,
858 (SUM(cs.slot_count) OVER (ORDER BY cs.customer_rn
859 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
860 - cs.slot_count + gs.slot)::int AS global_pos
861 FROM customer_slots cs
862 CROSS JOIN LATERAL generate_series(1, cs.slot_count) AS gs(slot))
863
864INSERT
865INTO car_dealership.configuration
866 (vin, description, total_price, created_at, customer_id)
867SELECT v.vin,
868
869 (ARRAY [
870 'Standard configuration with base equipment',
871 'Sport-oriented setup with performance packages',
872 'Comfort-focused build for long-distance driving',
873 'City commuter spec with economy features',
874 'Family configuration with safety and comfort packs',
875 'Executive spec with full luxury options',
876 'Off-road ready with enhanced suspension package',
877 'Hybrid-optimised configuration for fuel efficiency',
878 'Tech-forward setup with full infotainment suite',
879 'Winter spec with cold-weather equipment'
880 ])[(e.global_pos % 10) + 1] AS description,
881
882 ROUND(
883 (10000 + (e.global_pos * 7 % 75001))::numeric(10, 2),
884 2) AS total_price,
885
886 CURRENT_DATE - (e.global_pos % 2920) AS created_at,
887
888 e.customer_id
889
890FROM expanded e
891 JOIN vehicles v
892 ON v.rn = ((e.global_pos - 1) % (SELECT c FROM vehicle_cnt)) + 1;
893
894
895-- Step 1: Insert managers (i = 1..5000), manager_id is NULL
896WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
897 FROM unnest(ARRAY [
898 'Anna','Mark','Ivan','Christian','Nicole','Andrew','Peter','Matthew',
899 'Richard','Elena','Stefan','Maria','Daniel','Laura','John','Emma',
900 'Petar','Nikola','Sara','Filip'
901 ]) AS t(val)),
902 last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
903 FROM unnest(ARRAY [
904 'Smith','Brown','Lee','Johnson','White','Peshevski',
905 'Stojanov','Ivanov','Petrov','Georgiev','Trajkovski',
906 'Kostov','Mitrevski','Ristov','Iliev'
907 ]) AS t(val)),
908 fn_cnt AS MATERIALIZED (SELECT count(*) c FROM first_names),
909 ln_cnt AS MATERIALIZED (SELECT count(*) c FROM last_names)
910
911INSERT
912INTO car_dealership.employee (id, position, first_name, last_name, email, phone, hire_date, manager_id)
913SELECT i,
914 (ARRAY ['Salesperson','Manager','Finance','Admin'])[(i % 4) + 1],
915 fn.val,
916 ln.val,
917 lower(fn.val || '.' || ln.val || i || '@' ||
918 (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] || '.com'),
919 '+3897' || (i % 8) || lpad((i % 1000000)::text, 6, '0'),
920 CURRENT_DATE - (i % 7000),
921 NULL
922FROM generate_series(1, 5000) i
923 JOIN first_names fn ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
924 JOIN last_names ln ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;
925
926
927-- Step 2: Insert remaining employees (i = 5001..50000), manager_id references Step 1
928WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
929 FROM unnest(ARRAY [
930 'Anna','Mark','Ivan','Christian','Nicole','Andrew','Peter','Matthew',
931 'Richard','Elena','Stefan','Maria','Daniel','Laura','John','Emma',
932 'Petar','Nikola','Sara','Filip'
933 ]) AS t(val)),
934 last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
935 FROM unnest(ARRAY [
936 'Smith','Brown','Lee','Johnson','White','Peshevski',
937 'Stojanov','Ivanov','Petrov','Georgiev','Trajkovski',
938 'Kostov','Mitrevski','Ristov','Iliev'
939 ]) AS t(val)),
940 fn_cnt AS MATERIALIZED (SELECT count(*) c FROM first_names),
941 ln_cnt AS MATERIALIZED (SELECT count(*) c FROM last_names)
942
943INSERT
944INTO car_dealership.employee (id, position, first_name, last_name, email, phone, hire_date, manager_id)
945SELECT i,
946 (ARRAY ['Salesperson','Manager','Finance','Admin'])[(i % 4) + 1],
947 fn.val,
948 ln.val,
949 lower(fn.val || '.' || ln.val || i || '@' ||
950 (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] || '.com'),
951 '+3897' || (i % 8) || lpad((i % 1000000)::text, 6, '0'),
952 CURRENT_DATE - (i % 7000),
953 (i % 5000) + 1
954FROM generate_series(5001, 50000) i
955 JOIN first_names fn ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
956 JOIN last_names ln ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;
957
958
959
960WITH brand_factory(brand_id, factory_id, volume_weight) AS MATERIALIZED (VALUES (1, 1, 1.6),
961 (2, 1,
962 1.4), -- BMW, Mercedes → Factory 1
963 (3, 2, 1.9),
964 (4, 2,
965 2.1), -- Audi, VW → Factory 2
966 (5, 3, 1.0),
967 (6, 3,
968 1.2), -- Porsche, Peug. → Factory 3
969 (7, 4, 1.1),
970 (8, 4,
971 0.9), -- Renault, Škoda → Factory 4
972 (9, 5, 2.0),
973 (10, 5,
974 2.2), -- Volvo, Toyota → Factory 5
975 (11, 6, 1.8),
976 (12, 6,
977 1.6), -- Honda, Nissan → Factory 6
978 (13, 7, 0.8),
979 (14, 7,
980 0.7), -- Mazda, Subaru → Factory 7
981 (15, 8, 0.6),
982 (16, 8,
983 0.7), -- Mitsub., Suzu. → Factory 8
984 (17, 9, 1.3),
985 (18, 9,
986 1.5), -- Geely, BYD → Factory 9
987 (19, 10, 0.8),
988 (20, 10,
989 0.7) -- Chery, GWM → Factory 10
990),
991
992 vehicle_factory AS MATERIALIZED (SELECT v.vin,
993 bf.factory_id,
994 bf.volume_weight
995 FROM car_dealership.vehicle v
996 JOIN car_dealership.model m ON m.id = v.model_id
997 JOIN brand_factory bf ON bf.brand_id = m.brand_id)
998
999INSERT
1000INTO car_dealership.production (factory_id, vin, status)
1001SELECT vf.factory_id,
1002 vf.vin,
1003 CASE
1004 WHEN r < 0.10 THEN 'Scheduled'
1005 WHEN r < 0.10 + 0.25 / vf.volume_weight THEN 'In Progress'
1006 WHEN r < 0.10 + 0.25 / vf.volume_weight + 0.15 / vf.volume_weight THEN 'Quality Check'
1007 ELSE 'Completed'
1008 END
1009FROM vehicle_factory vf
1010 CROSS JOIN LATERAL (
1011 SELECT (hashtext(vf.vin) # vf.factory_id)::float8 / 2147483647.0 AS r
1012 ) rng;
1013
1014
1015TRUNCATE TABLE car_dealership."Order" CASCADE;
1016
1017WITH configs AS MATERIALIZED (SELECT DISTINCT ON (vin) id,
1018 customer_id,
1019 row_number() OVER (ORDER BY id)::int AS rn
1020 FROM car_dealership.configuration
1021 ORDER BY vin, id),
1022
1023 employees AS MATERIALIZED (SELECT id,
1024 row_number() OVER (ORDER BY id)::int AS rn
1025 FROM car_dealership.employee),
1026
1027 config_cnt AS (SELECT COUNT(*)::int AS c FROM configs),
1028 employee_cnt AS (SELECT COUNT(*)::int AS c FROM employees)
1029
1030INSERT
1031INTO car_dealership."Order"
1032 (date, status, customer_id, configuration_id, employee_id)
1033SELECT CURRENT_DATE - (c.rn % 3000) AS date,
1034
1035 (ARRAY ['Confirmed','Pending','Canceled','Completed'])[(c.rn % 4) + 1] AS status,
1036
1037 c.customer_id,
1038
1039 c.id AS configuration_id,
1040
1041 e.id AS employee_id
1042
1043FROM configs c
1044 JOIN employees e ON e.rn = ((c.rn - 1) % (SELECT c FROM employee_cnt)) + 1;
1045
1046WITH orders AS MATERIALIZED (SELECT DISTINCT ON (cfg.vin) o.id AS order_id,
1047 o.customer_id,
1048 o.employee_id,
1049 cfg.vin,
1050 row_number() OVER (ORDER BY o.id)::int AS rn
1051 FROM car_dealership."Order" o
1052 JOIN car_dealership.configuration cfg ON cfg.id = o.configuration_id
1053 ORDER BY cfg.vin, o.id),
1054
1055 employees AS MATERIALIZED (SELECT id,
1056 row_number() OVER (ORDER BY id)::int AS rn
1057 FROM car_dealership.employee),
1058
1059 employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c FROM employees),
1060 customer_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c FROM (SELECT id FROM car_dealership.customer) x)
1061
1062INSERT
1063INTO car_dealership.contract
1064 (employee_id, notes, date, type, order_id, customer_id, vin)
1065SELECT e.id AS employee_id,
1066
1067 'Contract ' || o.rn AS notes,
1068
1069 CURRENT_DATE - (o.rn % 3650) AS date,
1070
1071 (ARRAY ['Standard','Finance','Fleet'])[(o.rn % 3) + 1] AS type,
1072
1073 o.order_id,
1074
1075 CASE
1076 WHEN o.rn % 20 = 0
1077 THEN (o.rn % (SELECT c FROM customer_cnt)) + 1
1078 ELSE o.customer_id
1079 END AS customer_id,
1080
1081 o.vin
1082
1083FROM orders o
1084 JOIN employees e ON e.rn = ((o.rn - 1) % (SELECT c FROM employee_cnt)) + 1;
1085
1086
1087WITH customers AS MATERIALIZED (SELECT id,
1088 row_number() OVER (ORDER BY id)::int AS rn
1089 FROM car_dealership.customer),
1090
1091 vehicles AS MATERIALIZED (SELECT vin,
1092 row_number() OVER (ORDER BY vin)::int AS rn
1093 FROM car_dealership.vehicle),
1094
1095 test_vehicles AS MATERIALIZED (SELECT vin, rn
1096 FROM vehicles
1097 WHERE rn <= 1000000),
1098
1099 tv_cnt AS (SELECT COUNT(*)::int AS c FROM test_vehicles)
1100
1101INSERT
1102INTO car_dealership.testdrive
1103 (date, time_start, time_end, customer_id, vin, result)
1104SELECT CURRENT_DATE - (i % 2000) AS date,
1105
1106 ts,
1107
1108 ts + ((30 + (i % 11)) || ' minutes')::interval AS time_end,
1109
1110 c.id AS customer_id,
1111
1112 v.vin,
1113
1114 (ARRAY ['Interested','Not Interested','Follow-Up'])[(i % 3) + 1] AS result
1115
1116FROM generate_series(1, 10000000) i
1117
1118 CROSS JOIN LATERAL (
1119 SELECT TIMESTAMP '2024-01-01'
1120 + (i % 365) * INTERVAL '1 day'
1121 + (i % 86400) * INTERVAL '1 second' AS ts
1122 ) t
1123
1124 JOIN customers c ON c.rn = ((i - 1) % 1000000) + 1
1125 JOIN test_vehicles v ON v.rn = ((i - 1) % (SELECT c FROM tv_cnt)) + 1;
1126
1127WITH configs AS MATERIALIZED (SELECT id, row_number() OVER () rn
1128 FROM car_dealership.configuration),
1129 packages AS MATERIALIZED (SELECT id, row_number() OVER () rn
1130 FROM car_dealership.equipmentpackage),
1131 package_cnt AS MATERIALIZED (SELECT count(*) c
1132 FROM packages)
1133
1134INSERT
1135INTO car_dealership.configurationpackage (id,
1136 configuration_id,
1137 package_id)
1138SELECT (c.rn - 1) * 3 + p_idx AS id,
1139
1140 c.id,
1141
1142 p.id
1143
1144FROM configs c
1145
1146 CROSS JOIN generate_series(1, 3) AS p_idx
1147
1148 JOIN packages p
1149 ON p.rn = ((c.rn + p_idx - 2) % (SELECT c FROM package_cnt)) + 1;
1150
1151
1152
1153WITH cp AS MATERIALIZED (SELECT id, row_number() OVER () rn
1154 FROM car_dealership.configurationpackage),
1155 elements AS MATERIALIZED (SELECT id, row_number() OVER () rn
1156 FROM car_dealership.packageelement),
1157 element_cnt AS (SELECT count(*) c
1158 FROM elements)
1159
1160INSERT
1161INTO car_dealership.configurationelement (id,
1162 configuration_package_id,
1163 package_element_id)
1164SELECT (cp.rn - 1) * 2 + e_idx AS id,
1165
1166 cp.id,
1167
1168 e.id
1169
1170FROM cp
1171
1172 CROSS JOIN generate_series(1, 2) AS e_idx
1173
1174 JOIN elements e
1175 ON e.rn = ((cp.rn + e_idx - 2) % (SELECT c FROM element_cnt)) + 1;
1176
1177WITH contracts AS MATERIALIZED (SELECT id,
1178 customer_id,
1179 row_number() OVER (ORDER BY id)::int AS rn
1180 FROM car_dealership.contract),
1181
1182 employees AS MATERIALIZED (SELECT id,
1183 row_number() OVER (ORDER BY id)::int AS rn
1184 FROM car_dealership.employee),
1185
1186 employee_cnt AS (SELECT COUNT(*)::int AS c FROM employees)
1187
1188INSERT
1189INTO car_dealership.sale
1190 (date, contract_id, customer_id, employee_id)
1191SELECT CURRENT_DATE - (c.rn % 2000) AS date,
1192
1193 c.id AS contract_id,
1194
1195 c.customer_id AS customer_id,
1196
1197 e.id AS employee_id
1198
1199FROM contracts c
1200 JOIN employees e ON e.rn = ((c.rn - 1) % (SELECT c FROM employee_cnt)) + 1;
1201
1202WITH sales AS MATERIALIZED (SELECT s.id,
1203 row_number() OVER () AS rn,
1204 conf.total_price
1205 FROM car_dealership.sale s
1206 JOIN car_dealership.contract ct ON ct.id = s.contract_id
1207 JOIN car_dealership."Order" o ON o.id = ct.order_id
1208 JOIN car_dealership.configuration conf ON conf.id = o.configuration_id)
1209INSERT
1210INTO car_dealership.payment (id, type, amount, sale_id)
1211SELECT rn,
1212 (ARRAY ['Cash','Installment','Leasing'])[(rn % 3) + 1],
1213 total_price,
1214 id
1215FROM sales;
1216
1217WITH payments AS MATERIALIZED (SELECT id,
1218 row_number() OVER (ORDER BY id)::int AS rn
1219 FROM car_dealership.payment),
1220
1221 employees AS MATERIALIZED (SELECT id,
1222 row_number() OVER (ORDER BY id)::int AS rn
1223 FROM car_dealership.employee),
1224
1225 employee_cnt AS (SELECT COUNT(*)::int AS c FROM employees)
1226
1227INSERT
1228INTO car_dealership.discount
1229 (percentage, payment_id, employee_id)
1230SELECT
1231 -- Discount between 5% and 25%
1232 (5 + (p.rn % 21))::smallint AS percentage,
1233
1234 p.id AS payment_id,
1235
1236 e.id AS employee_id
1237
1238FROM payments p
1239 JOIN employees e ON e.rn = ((p.rn - 1) % (SELECT c FROM employee_cnt)) + 1
1240
1241-- Only ~30% of payments receive a discount.
1242-- rn % 10 < 3 means positions 0,1,2 out of every 10 → 30%
1243WHERE p.rn % 10 < 3;
1244
1245