SET statement_timeout = 0;

-- =========================================================
-- FACTORIES
-- =========================================================

INSERT INTO car_dealership.factory (id, name)
VALUES (1, 'NovaForge'),
       (2, 'IronAxis'),
       (3, 'VelocityWorks'),
       (4, 'ApexDrive'),
       (5, 'TitanAssembly'),
       (6, 'QuantumMotors'),
       (7, 'BlueCore Industries'),
       (8, 'FusionLine'),
       (9, 'PrimeTorque'),
       (10, 'Vertex Automotive');


-- =========================================================
-- BRANDS
-- =========================================================

INSERT INTO car_dealership.brand (id, brand)
VALUES (1, 'BMW'),
       (2, 'Mercedes-Benz'),
       (3, 'Audi'),
       (4, 'Volkswagen'),
       (5, 'Porsche'),
       (6, 'Peugeot'),
       (7, 'Renault'),
       (8, 'Škoda'),
       (9, 'Volvo'),
       (10, 'Toyota'),
       (11, 'Honda'),
       (12, 'Nissan'),
       (13, 'Mazda'),
       (14, 'Subaru'),
       (15, 'Mitsubishi'),
       (16, 'Suzuki'),
       (17, 'Geely'),
       (18, 'BYD'),
       (19, 'Cherry'),
       (20, 'GWM');


-- =========================================================
-- ENGINE TYPES
-- =========================================================

INSERT INTO car_dealership.enginetype
VALUES (1, 'Petrol'),
       (2, 'Diesel'),
       (3, 'Hybrid'),
       (4, 'Electric');


-- =========================================================
-- VEHICLE TYPES
-- =========================================================

INSERT INTO car_dealership.vehicletype (id, type)
VALUES (1, 'Sedan'),
       (2, 'Hatchback'),
       (3, 'SUV'),
       (4, 'Coupe'),
       (5, 'Convertible'),
       (6, 'Wagon'),
       (7, 'Pickup'),
       (8, 'Van'),
       (9, 'Minivan'),
       (10, 'Sports Car'),
       (11, 'Crossover'),
       (12, 'Electric'),
       (13, 'Hybrid'),
       (14, 'Off-Road');


-- =========================================================
-- VEHICLE STATUSES
-- =========================================================

INSERT INTO car_dealership.status
VALUES (1, 'In Stock'),
       (2, 'Ordered'),
       (3, 'In Production'),
       (4, 'Reserved'),
       (5, 'Sold'),
       (6, 'In Transit');


-- =========================================================
-- EQUIPMENT TYPES
-- =========================================================

INSERT INTO car_dealership.equipmenttype (id, name)
VALUES (1, 'Safety'),
       (2, 'Comfort'),
       (3, 'Infotainment'),
       (4, 'Performance'),
       (5, 'Driver Assistance'),
       (6, 'Security');


-- =========================================================
-- MODELS
-- =========================================================

INSERT INTO car_dealership.model (id, model, brand_id, year)
VALUES (1, '3 Series', 1, 1995),
       (2, '3 Series', 1, 2000),
       (3, '3 Series', 1, 2005),
       (4, '3 Series', 1, 2012),
       (5, '3 Series', 1, 2019),
       (6, '3 Series', 1, 2023),
       (7, '5 Series', 1, 1995),
       (8, '5 Series', 1, 2004),
       (9, '5 Series', 1, 2010),
       (10, '5 Series', 1, 2017),
       (11, '5 Series', 1, 2024),
       (12, 'X3', 1, 2004),
       (13, 'X3', 1, 2011),
       (14, 'X3', 1, 2018),
       (15, 'X3', 1, 2024),
       (16, 'X5', 1, 1999),
       (17, 'X5', 1, 2007),
       (18, 'X5', 1, 2014),
       (19, 'X5', 1, 2023),

       (20, 'A-Class', 2, 1997),
       (21, 'A-Class', 2, 2005),
       (22, 'A-Class', 2, 2013),
       (23, 'A-Class', 2, 2019),
       (24, 'C-Class', 2, 1995),
       (25, 'C-Class', 2, 2001),
       (26, 'C-Class', 2, 2008),
       (27, 'C-Class', 2, 2015),
       (28, 'C-Class', 2, 2022),
       (29, 'E-Class', 2, 1995),
       (30, 'E-Class', 2, 2003),
       (31, 'E-Class', 2, 2010),
       (32, 'E-Class', 2, 2017),
       (33, 'E-Class', 2, 2024),
       (34, 'GLE', 2, 2016),
       (35, 'GLE', 2, 2020),
       (36, 'GLE', 2, 2024),

       (37, 'A3', 3, 1997),
       (38, 'A3', 3, 2004),
       (39, 'A3', 3, 2013),
       (40, 'A3', 3, 2021),
       (41, 'A4', 3, 1995),
       (42, 'A4', 3, 2001),
       (43, 'A4', 3, 2008),
       (44, 'A4', 3, 2016),
       (45, 'A4', 3, 2024),
       (46, 'A6', 3, 1995),
       (47, 'A6', 3, 2005),
       (48, 'A6', 3, 2012),
       (49, 'A6', 3, 2019),
       (50, 'Q5', 3, 2009),
       (51, 'Q5', 3, 2017),
       (52, 'Q5', 3, 2024),

       (53, 'Golf', 4, 1995),
       (54, 'Golf', 4, 2004),
       (55, 'Golf', 4, 2009),
       (56, 'Golf', 4, 2013),
       (57, 'Golf', 4, 2020),
       (58, 'Passat', 4, 1997),
       (59, 'Passat', 4, 2006),
       (60, 'Passat', 4, 2015),
       (61, 'Passat', 4, 2024),
       (62, 'Tiguan', 4, 2008),
       (63, 'Tiguan', 4, 2016),
       (64, 'Tiguan', 4, 2024),
       (65, 'Polo', 4, 1995),
       (66, 'Polo', 4, 2002),
       (67, 'Polo', 4, 2010),
       (68, 'Polo', 4, 2018),

       (69, '911', 5, 1995),
       (70, '911', 5, 2005),
       (71, '911', 5, 2012),
       (72, '911', 5, 2019),
       (73, 'Cayenne', 5, 2003),
       (74, 'Cayenne', 5, 2011),
       (75, 'Cayenne', 5, 2018),
       (76, 'Macan', 5, 2015),
       (77, 'Macan', 5, 2022),
       (78, 'Panamera', 5, 2010),
       (79, 'Panamera', 5, 2017),
       (80, 'Panamera', 5, 2024),

       (81, '206', 6, 1998),
       (82, '206', 6, 2003),
       (83, '207', 6, 2006),
       (84, '208', 6, 2012),
       (85, '208', 6, 2020),
       (86, '308', 6, 2008),
       (87, '308', 6, 2014),
       (88, '308', 6, 2022),
       (89, '3008', 6, 2009),
       (90, '3008', 6, 2017),
       (91, '3008', 6, 2024),
       (92, '508', 6, 2011),
       (93, '508', 6, 2019),

       (94, 'Clio', 7, 1998),
       (95, 'Clio', 7, 2006),
       (96, 'Clio', 7, 2013),
       (97, 'Clio', 7, 2020),
       (98, 'Megane', 7, 1996),
       (99, 'Megane', 7, 2003),
       (100, 'Megane', 7, 2009),
       (101, 'Megane', 7, 2016),
       (102, 'Captur', 7, 2013),
       (103, 'Captur', 7, 2020),
       (104, 'Kadjar', 7, 2015),
       (105, 'Kadjar', 7, 2022),

       (106, 'Fabia', 8, 1999),
       (107, 'Fabia', 8, 2007),
       (108, 'Fabia', 8, 2015),
       (109, 'Fabia', 8, 2022),
       (110, 'Octavia', 8, 1997),
       (111, 'Octavia', 8, 2004),
       (112, 'Octavia', 8, 2013),
       (113, 'Octavia', 8, 2021),
       (114, 'Superb', 8, 2002),
       (115, 'Superb', 8, 2009),
       (116, 'Superb', 8, 2016),
       (117, 'Superb', 8, 2024),
       (118, 'Kodiaq', 8, 2017),
       (119, 'Kodiaq', 8, 2024),

       (120, 'S60', 9, 2001),
       (121, 'S60', 9, 2011),
       (122, 'S60', 9, 2019),
       (123, 'S90', 9, 1997),
       (124, 'S90', 9, 2017),
       (125, 'S90', 9, 2024),
       (126, 'XC40', 9, 2018),
       (127, 'XC40', 9, 2023),
       (128, 'XC60', 9, 2009),
       (129, 'XC60', 9, 2018),
       (130, 'XC60', 9, 2024),

       (131, 'Corolla', 10, 1995),
       (132, 'Corolla', 10, 2002),
       (133, 'Corolla', 10, 2007),
       (134, 'Corolla', 10, 2014),
       (135, 'Corolla', 10, 2019),
       (136, 'Camry', 10, 1997),
       (137, 'Camry', 10, 2007),
       (138, 'Camry', 10, 2012),
       (139, 'Camry', 10, 2018),
       (140, 'RAV4', 10, 1996),
       (141, 'RAV4', 10, 2006),
       (142, 'RAV4', 10, 2013),
       (143, 'RAV4', 10, 2019),
       (144, 'Yaris', 10, 1999),
       (145, 'Yaris', 10, 2006),
       (146, 'Yaris', 10, 2012),
       (147, 'Yaris', 10, 2021),

       (148, 'Civic', 11, 1995),
       (149, 'Civic', 11, 2001),
       (150, 'Civic', 11, 2006),
       (151, 'Civic', 11, 2012),
       (152, 'Civic', 11, 2022),
       (153, 'Accord', 11, 1998),
       (154, 'Accord', 11, 2004),
       (155, 'Accord', 11, 2013),
       (156, 'Accord', 11, 2018),
       (157, 'CR-V', 11, 1997),
       (158, 'CR-V', 11, 2007),
       (159, 'CR-V', 11, 2013),
       (160, 'CR-V', 11, 2023),
       (161, 'Jazz', 11, 2002),
       (162, 'Jazz', 11, 2009),
       (163, 'Jazz', 11, 2015),
       (164, 'Jazz', 11, 2020),

       (165, 'Micra', 12, 1995),
       (166, 'Micra', 12, 2003),
       (167, 'Micra', 12, 2010),
       (168, 'Micra', 12, 2017),
       (169, 'Qashqai', 12, 2007),
       (170, 'Qashqai', 12, 2014),
       (171, 'Qashqai', 12, 2021),
       (172, 'X-Trail', 12, 2001),
       (173, 'X-Trail', 12, 2008),
       (174, 'X-Trail', 12, 2014),
       (175, 'X-Trail', 12, 2022),
       (176, 'Juke', 12, 2010),
       (177, 'Juke', 12, 2020),

       (178, 'Mazda3', 13, 2004),
       (179, 'Mazda3', 13, 2009),
       (180, 'Mazda3', 13, 2014),
       (181, 'Mazda3', 13, 2019),
       (182, 'Mazda6', 13, 2002),
       (183, 'Mazda6', 13, 2008),
       (184, 'Mazda6', 13, 2013),
       (185, 'Mazda6', 13, 2018),
       (186, 'CX-5', 13, 2012),
       (187, 'CX-5', 13, 2017),
       (188, 'CX-5', 13, 2022),
       (189, 'CX-30', 13, 2019),
       (190, 'CX-30', 13, 2024),

       (191, 'Impreza', 14, 1995),
       (192, 'Impreza', 14, 2001),
       (193, 'Impreza', 14, 2008),
       (194, 'Impreza', 14, 2017),
       (195, 'Outback', 14, 1996),
       (196, 'Outback', 14, 2004),
       (197, 'Outback', 14, 2015),
       (198, 'Outback', 14, 2021),
       (199, 'Forester', 14, 1997),
       (200, 'Forester', 14, 2008),
       (201, 'Forester', 14, 2014),
       (202, 'Forester', 14, 2019),
       (203, 'BRZ', 14, 2012),
       (204, 'BRZ', 14, 2022),

       (205, 'Lancer', 15, 1995),
       (206, 'Lancer', 15, 2004),
       (207, 'Lancer', 15, 2008),
       (208, 'Outlander', 15, 2003),
       (209, 'Outlander', 15, 2013),
       (210, 'Outlander', 15, 2022),
       (211, 'ASX', 15, 2010),
       (212, 'ASX', 15, 2017),
       (213, 'ASX', 15, 2023),
       (214, 'Eclipse Cross', 15, 2018),
       (215, 'Eclipse Cross', 15, 2022),

       (216, 'Swift', 16, 1995),
       (217, 'Swift', 16, 2005),
       (218, 'Swift', 16, 2011),
       (219, 'Swift', 16, 2017),
       (220, 'Swift', 16, 2024),
       (221, 'Vitara', 16, 1995),
       (222, 'Vitara', 16, 2006),
       (223, 'Vitara', 16, 2015),
       (224, 'Vitara', 16, 2022),
       (225, 'SX4', 16, 2006),
       (226, 'SX4', 16, 2014),
       (227, 'SX4', 16, 2021),
       (228, 'Ignis', 16, 2017),
       (229, 'Ignis', 16, 2020),

       (230, 'Emgrand', 17, 2009),
       (231, 'Emgrand', 17, 2016),
       (232, 'Emgrand', 17, 2022),
       (233, 'Coolray', 17, 2019),
       (234, 'Coolray', 17, 2023),
       (235, 'Atlas', 17, 2017),
       (236, 'Atlas', 17, 2022),
       (237, 'Tugella', 17, 2020),
       (238, 'Tugella', 17, 2024),

       (239, 'Han', 18, 2020),
       (240, 'Han', 18, 2023),
       (241, 'Tang', 18, 2018),
       (242, 'Tang', 18, 2022),
       (243, 'Seal', 18, 2022),
       (244, 'Seal', 18, 2025),
       (245, 'Dolphin', 18, 2021),
       (246, 'Dolphin', 18, 2024),

       (247, 'Tiggo 4', 19, 2014),
       (248, 'Tiggo 4', 19, 2021),
       (249, 'Tiggo 7', 19, 2016),
       (250, 'Tiggo 7', 19, 2022),
       (251, 'Tiggo 8', 19, 2018),
       (252, 'Tiggo 8', 19, 2023),
       (253, 'Arrizo 5', 19, 2014),
       (254, 'Arrizo 5', 19, 2021),

       (255, 'Haval H6', 20, 2011),
       (256, 'Haval H6', 20, 2017),
       (257, 'Haval H6', 20, 2021),
       (258, 'Haval Jolion', 20, 2021),
       (259, 'Haval Jolion', 20, 2024),
       (260, 'Poer', 20, 2019),
       (261, 'Poer', 20, 2023),
       (262, 'Tank 300', 20, 2021),
       (263, 'Tank 300', 20, 2024);


-- =========================================================
-- CUSTOMERS
-- =========================================================

WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
                                  FROM unnest(ARRAY [
                                      'John','Emma','Liam','Olivia','Noah','Ava','Ethan','Sophia',
                                      'Mia','Lucas','Isabella','Mason','Amelia','Logan','Harper',
                                      'James','Evelyn','Benjamin','Ella','Alexander',
                                      'Daniel','David','Elena','Ivana','Stefan','Marko','Petar',
                                      'Ana','Marija','Filip','Nikola','Sara','Teodora','Kristijan'
                                      ]) AS t(val)),
     last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
                                 FROM unnest(ARRAY [
                                     'Smith','Brown','Johnson','Wilson','Taylor','Lee','Martin','Anderson',
                                     'Thomas','Moore','Jackson','White','Harris','Clark','Lewis',
                                     'Walker','Hall','Allen','Young','King',
                                     'Petrovski','Stojanov','Trajkovski','Nikolov','Jovanov',
                                     'Iliev','Ristov','Georgiev','Kostov','Angelov','Mitrevski'
                                     ]) AS t(val)),
     fn_cnt AS MATERIALIZED (SELECT count(*) c
                             FROM first_names),
     ln_cnt AS MATERIALIZED (SELECT count(*) c
                             FROM last_names)

INSERT
INTO car_dealership.customer
    (id, first_name, last_name, email, phone, created_at)
SELECT i,
       fn.val,
       ln.val,
       lower(
               fn.val || '.' || ln.val || i || '@' ||
               (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] || '.com'
       ),
       '+3897' || (i % 8) || lpad((i % 1000000)::text, 6, '0'),
       CURRENT_DATE - (i % 5000)
FROM generate_series(1, 1000000) i
         JOIN first_names fn
              ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
         JOIN last_names ln
              ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;


-- =========================================================
-- CUSTOMER ADDRESSES
-- =========================================================

WITH cities AS MATERIALIZED (SELECT *
                             FROM (VALUES (0, 'Skopje', 1000),
                                          (1, 'Bitola', 7000),
                                          (2, 'Kumanovo', 1300),
                                          (3, 'Prilep', 7500),
                                          (4, 'Tetovo', 1200),
                                          (5, 'Ohrid', 6000),
                                          (6, 'Veles', 1400),
                                          (7, 'Stip', 2000),
                                          (8, 'Strumica', 2400),
                                          (9, 'Kavadarci', 1430),
                                          (10, 'Gostivar', 1230),
                                          (11, 'Kocani', 2300),
                                          (12, 'Gevgelija', 1480),
                                          (13, 'Debar', 1250),
                                          (14, 'Kriva Palanka', 1330)) AS t(idx, city, postal_code)),
     streets AS MATERIALIZED (SELECT *
                              FROM (VALUES (0, 'Partizanska'),
                                           (1, 'Ilindenska'),
                                           (2, 'Goce Delchev'),
                                           (3, 'Dame Gruev'),
                                           (4, 'Marsal Tito'),
                                           (5, 'Boris Trajkovski'),
                                           (6, 'Jane Sandanski'),
                                           (7, 'Nikola Karev'),
                                           (8, '11 Oktomvri'),
                                           (9, 'Dimitar Vlahov'),
                                           (10, 'Makedonija'),
                                           (11, 'Vasil Glavinov'),
                                           (12, 'Kuzman Josifovski Pitu'),
                                           (13, 'Mirche Acev'),
                                           (14, 'Pitu Guli'),
                                           (15, 'Blagoja Toska'),
                                           (16, 'Ruzveltova'),
                                           (17, 'Orce Nikolov'),
                                           (18, 'Vodno'),
                                           (19, 'Pelagonija')) AS t(idx, street))

INSERT
INTO car_dealership.address
(id, country, city, postal_code, street,
 building_number, entry_number, apartment_number,
 customer_id, factory_id)
SELECT i,
       'North Macedonia',
       c.city,
       c.postal_code,
       s.street,
       (i % 200) + 1,
       (i % 10) + 1,
       (i % 50) + 1,
       i,
       NULL
FROM generate_series(1, 1000000) i
         JOIN cities c ON c.idx = (i % 15)
         JOIN streets s ON s.idx = (i % 20);


-- =========================================================
-- FACTORY ADDRESSES
-- =========================================================

WITH cities AS MATERIALIZED (SELECT *
                             FROM (VALUES (0, 'Skopje', 1000),
                                          (1, 'Bitola', 7000),
                                          (2, 'Kumanovo', 1300),
                                          (3, 'Prilep', 7500),
                                          (4, 'Tetovo', 1200),
                                          (5, 'Ohrid', 6000),
                                          (6, 'Veles', 1400),
                                          (7, 'Stip', 2000),
                                          (8, 'Strumica', 2400),
                                          (9, 'Kavadarci', 1430),
                                          (10, 'Gostivar', 1230),
                                          (11, 'Kocani', 2300),
                                          (12, 'Gevgelija', 1480),
                                          (13, 'Debar', 1250),
                                          (14, 'Kriva Palanka', 1330)) AS t(idx, city, postal_code)),
     streets AS MATERIALIZED (SELECT *
                              FROM (VALUES (0, 'Partizanska'),
                                           (1, 'Ilindenska'),
                                           (2, 'Goce Delchev'),
                                           (3, 'Dame Gruev'),
                                           (4, 'Marsal Tito'),
                                           (5, 'Boris Trajkovski'),
                                           (6, 'Jane Sandanski'),
                                           (7, 'Nikola Karev'),
                                           (8, '11 Oktomvri'),
                                           (9, 'Dimitar Vlahov'),
                                           (10, 'Makedonija'),
                                           (11, 'Vasil Glavinov'),
                                           (12, 'Kuzman Josifovski Pitu'),
                                           (13, 'Mirche Acev'),
                                           (14, 'Pitu Guli'),
                                           (15, 'Blagoja Toska'),
                                           (16, 'Ruzveltova'),
                                           (17, 'Orce Nikolov'),
                                           (18, 'Vodno'),
                                           (19, 'Pelagonija')) AS t(idx, street))

INSERT
INTO car_dealership.address
(id, country, city, postal_code, street,
 building_number, entry_number, apartment_number,
 customer_id, factory_id)
SELECT 1000000 + i,
       'North Macedonia',
       c.city,
       c.postal_code,
       s.street,
       (i % 200) + 1,
       NULL,
       NULL,
       NULL,
       i
FROM generate_series(1, 10) i
         JOIN cities c ON c.idx = (i % 15)
         JOIN streets s ON s.idx = (i % 20);


-- =========================================================
-- EQUIPMENT PACKAGES
-- =========================================================

INSERT INTO car_dealership.equipmentpackage
    (id, name, price, description, is_default, equipment_type_id)
VALUES (1, 'Base', 0,
        'Standard equipment included in every vehicle', true, 1),

       (2, 'Winter', 1200,
        'Elements that support harsh and cold winter weather, heating, tires', false, 2),

       (3, 'Sport', 2500,
        'Performance upgrades including suspension and exhaust enhancements', false, 4),

       (4, 'Luxury', 4000,
        'Premium materials, leather interior, and high-end comfort features', false, 2),

       (5, 'Safety Plus', 1800,
        'Advanced driver assistance and safety systems', false, 5),

       (6, 'Tech Pack', 2200,
        'Infotainment, navigation, and smart connectivity features', false, 3),

       (7, 'Comfort Pack', 2000,
        'Heated seats, climate control upgrades, and interior comfort enhancements', false, 2);


INSERT INTO car_dealership.packageelement
    (id, package_id, name, price)
VALUES (1, 1, 'Air Conditioning', 0),
       (2, 1, 'ABS Braking System', 0),
       (3, 1, 'Front Airbags', 0),
       (4, 1, 'Power Steering', 0),

       (5, 2, 'Heated Front Seats', 300),
       (6, 2, 'Heated Steering Wheel', 250),
       (7, 2, 'Winter Tires Set', 700),
       (8, 2, 'Windshield Defrost System', 200),

       (9, 3, 'Sport Suspension', 900),
       (10, 3, 'Performance Exhaust System', 1200),
       (11, 3, 'Sport Steering Mode', 400),
       (12, 3, 'Paddle Shifters', 350),

       (13, 4, 'Leather Interior', 1500),
       (14, 4, 'Premium Sound System', 1200),
       (15, 4, 'Ambient Interior Lighting', 600),
       (16, 4, 'Panoramic Sunroof', 1800),

       (17, 5, 'Lane Assist System', 800),
       (18, 5, 'Adaptive Cruise Control', 1000),
       (19, 5, 'Blind Spot Monitoring', 700),
       (20, 5, 'Automatic Emergency Braking', 900),

       (21, 6, 'Digital Dashboard', 900),
       (22, 6, 'Built-in Navigation System', 600),
       (23, 6, 'Wireless Charging', 400),
       (24, 6, 'Smartphone Integration', 500),

       (25, 7, 'Dual Zone Climate Control', 500),
       (26, 7, 'Massage Seats', 1100),
       (27, 7, 'Rear Seat Heating', 350),
       (28, 7, 'Electric Seat Adjustment', 450);


-- =========================================================
-- ENGINES
-- =========================================================

WITH engine_types AS MATERIALIZED (SELECT MAX(CASE WHEN type = 'Petrol' THEN id END)   AS petrol_id,
                                          MAX(CASE WHEN type = 'Diesel' THEN id END)   AS diesel_id,
                                          MAX(CASE WHEN type = 'Hybrid' THEN id END)   AS hybrid_id,
                                          MAX(CASE WHEN type = 'Electric' THEN id END) AS electric_id
                                   FROM car_dealership.enginetype)

INSERT
INTO car_dealership.engine
    (enginenumber, horsepower, enginetypeid)
SELECT UPPER(
               CASE
                   WHEN i % 100 < 60 THEN 'PTR'
                   WHEN i % 100 < 88 THEN 'DSL'
                   WHEN i % 100 < 96 THEN 'HYB'
                   ELSE 'ELC'
                   END
                   ||
               CASE
                   WHEN i % 100 < 60 THEN (1000 + (i * 17 % 4001))::text
                   WHEN i % 100 < 88 THEN (1600 + (i * 13 % 1901))::text
                   WHEN i % 100 < 96 THEN (1500 + (i * 11 % 1501))::text
                   ELSE '0000'
                   END
                   || CHR(65 + (i % 26))
                   || lpad(i::text, 7, '0')
       ),

       CASE
           WHEN i % 100 < 60 THEN 60 + (i * 13 % 441)
           WHEN i % 100 < 88 THEN 60 + (i * 11 % 341)
           WHEN i % 100 < 96 THEN 80 + (i * 9 % 321)
           ELSE 100 + (i * 7 % 501)
           END::int,

       CASE
           WHEN i % 100 < 60 THEN et.petrol_id
           WHEN i % 100 < 88 THEN et.diesel_id
           WHEN i % 100 < 96 THEN et.hybrid_id
           ELSE et.electric_id
           END

FROM generate_series(1, 10000000) i
         CROSS JOIN engine_types et;


-- =========================================================
-- VEHICLES
-- =========================================================

WITH models AS MATERIALIZED (SELECT id,
                                    brand_id,
                                    model,
                                    year,
                                    row_number() OVER (ORDER BY id) AS rn
                             FROM car_dealership.model),

     statuses AS MATERIALIZED (SELECT id,
                                      row_number() OVER (ORDER BY id) AS rn
                               FROM car_dealership.status),

     vehicle_types AS MATERIALIZED (SELECT id, type
                                    FROM car_dealership.vehicletype),

     engines AS MATERIALIZED (SELECT enginenumber,
                                     CASE
                                         WHEN enginenumber LIKE 'PTR%' THEN 'P'
                                         WHEN enginenumber LIKE 'DSL%' THEN 'D'
                                         WHEN enginenumber LIKE 'HYB%' THEN 'H'
                                         ELSE 'E'
                                         END                                   AS engine_code,
                                     row_number() OVER (ORDER BY enginenumber) AS rn
                              FROM car_dealership.engine),

     model_cnt AS MATERIALIZED (SELECT count(*) c
                                FROM models),

     status_cnt AS MATERIALIZED (SELECT count(*) c
                                 FROM statuses),

     brand_wmi AS MATERIALIZED (SELECT *
                                FROM (VALUES (1, 'WBA'),
                                             (2, 'WDB'),
                                             (3, 'WAU'),
                                             (4, 'WVW'),
                                             (5, 'WP0'),
                                             (6, 'VF3'),
                                             (7, 'VF1'),
                                             (8, 'TMB'),
                                             (9, 'YV1'),
                                             (10, 'JT2'),
                                             (11, 'JHM'),
                                             (12, 'JN1'),
                                             (13, 'JM1'),
                                             (14, 'JF1'),
                                             (15, 'JA3'),
                                             (16, 'JS2'),
                                             (17, 'LGX'),
                                             (18, 'LBV'),
                                             (19, 'LVV'),
                                             (20, 'LHG')) AS t(brand_id, wmi)),

     model_specs AS MATERIALIZED (SELECT *
                                  FROM (VALUES ('3 Series', 'Sedan', 25000, 55000),
                                               ('5 Series', 'Sedan', 35000, 75000),
                                               ('X3', 'SUV', 35000, 70000),
                                               ('X5', 'SUV', 50000, 95000),
                                               ('A-Class', 'Hatchback', 22000, 45000),
                                               ('C-Class', 'Sedan', 28000, 60000),
                                               ('E-Class', 'Sedan', 40000, 80000),
                                               ('GLE', 'SUV', 55000, 95000),
                                               ('A3', 'Hatchback', 22000, 45000),
                                               ('A4', 'Sedan', 28000, 58000),
                                               ('A6', 'Sedan', 40000, 75000),
                                               ('Q5', 'SUV', 40000, 72000),
                                               ('Golf', 'Hatchback', 18000, 40000),
                                               ('Passat', 'Sedan', 25000, 50000),
                                               ('Tiguan', 'SUV', 28000, 52000),
                                               ('Polo', 'Hatchback', 13000, 28000),
                                               ('911', 'Sports Car', 80000, 180000),
                                               ('Cayenne', 'SUV', 70000, 150000),
                                               ('Macan', 'SUV', 50000, 90000),
                                               ('Panamera', 'Sedan', 85000,
                                                160000)) AS t(model_name, vehicle_type, price_min, price_max)),

     year_codes AS MATERIALIZED (SELECT *
                                 FROM (VALUES (1995, 'S'),
                                              (1996, 'T'),
                                              (1997, 'V'),
                                              (1998, 'W'),
                                              (1999, 'X'),
                                              (2000, 'Y'),
                                              (2001, '1'),
                                              (2002, '2'),
                                              (2003, '3'),
                                              (2004, '4'),
                                              (2005, '5'),
                                              (2006, '6'),
                                              (2007, '7'),
                                              (2008, '8'),
                                              (2009, '9'),
                                              (2010, 'A'),
                                              (2011, 'B'),
                                              (2012, 'C'),
                                              (2013, 'D'),
                                              (2014, 'E'),
                                              (2015, 'F'),
                                              (2016, 'G'),
                                              (2017, 'H'),
                                              (2018, 'J'),
                                              (2019, 'K'),
                                              (2020, 'L'),
                                              (2021, 'M'),
                                              (2022, 'N'),
                                              (2023, 'P'),
                                              (2024, 'R'),
                                              (2025, 'S')) AS t(yr, code)),

     model_full AS MATERIALIZED (SELECT m.rn,
                                        m.id                          AS model_id,
                                        m.year,
                                        m.brand_id,
                                        bw.wmi,
                                        yc.code                       AS year_code,
                                        vt.id                         AS vehicle_type_id,

                                        CASE vt.type
                                            WHEN 'Sedan' THEN 'AA'
                                            WHEN 'Hatchback' THEN 'BB'
                                            WHEN 'SUV' THEN 'CC'
                                            WHEN 'Coupe' THEN 'DD'
                                            WHEN 'Convertible' THEN 'EE'
                                            WHEN 'Wagon' THEN 'FF'
                                            WHEN 'Pickup' THEN 'GG'
                                            WHEN 'Sports Car' THEN 'HH'
                                            WHEN 'Crossover' THEN 'JJ'
                                            ELSE 'XX'
                                            END                       AS body_code,

                                        COALESCE(ms.price_min, 15000) AS price_min,
                                        COALESCE(ms.price_max, 50000) AS price_max,

                                        (
                                            COALESCE(ms.price_min, 15000)
                                                +
                                            (
                                                (COALESCE(ms.price_max, 50000) -
                                                 COALESCE(ms.price_min, 15000))
                                                    *
                                                (m.year - 1995)::numeric / 31
                                                )
                                            )::numeric(10, 2)         AS base_price

                                 FROM models m
                                          LEFT JOIN model_specs ms
                                                    ON ms.model_name = m.model
                                          JOIN vehicle_types vt
                                               ON vt.type = COALESCE(ms.vehicle_type, 'Sedan')
                                          JOIN year_codes yc
                                               ON yc.yr = m.year
                                          JOIN brand_wmi bw
                                               ON bw.brand_id = m.brand_id)

INSERT
INTO car_dealership.vehicle
(vin, model_id, status_id, vehicle_type_id,
 color, price, production_year, engine_number)
SELECT mf.wmi
           || mf.body_code
           || e.engine_code
           || lpad((mf.model_id % 1000)::text, 3, '0')
           || mf.year_code
           || CHR(65 + ((i - 1) / 1000000 % 26))
           || lpad(((i - 1) % 1000000)::text, 6, '0'),

       mf.model_id,
       s.id,
       mf.vehicle_type_id,

       (ARRAY ['Black','White','Grey','Silver','Red','Blue'])[(i % 6) + 1],

       mf.base_price,
       mf.year,
       e.enginenumber

FROM generate_series(1, 10000000) i
         JOIN model_full mf
              ON mf.rn = (i % (SELECT c FROM model_cnt)) + 1
         JOIN statuses s
              ON s.rn = (i % (SELECT c FROM status_cnt)) + 1
         JOIN engines e
              ON e.rn = i;


-- =========================================================
-- EMPLOYEES: MANAGERS FIRST
-- =========================================================

WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
                                  FROM unnest(ARRAY [
                                      'Anna','Mark','Ivan','Christian','Nicole','Andrew','Peter','Matthew',
                                      'Richard','Elena','Stefan','Maria','Daniel','Laura','John','Emma',
                                      'Petar','Nikola','Sara','Filip'
                                      ]) AS t(val)),

     last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
                                 FROM unnest(ARRAY [
                                     'Smith','Brown','Lee','Johnson','White','Peshevski',
                                     'Stojanov','Ivanov','Petrov','Georgiev','Trajkovski',
                                     'Kostov','Mitrevski','Ristov','Iliev'
                                     ]) AS t(val)),

     fn_cnt AS MATERIALIZED (SELECT count(*) c
                             FROM first_names),

     ln_cnt AS MATERIALIZED (SELECT count(*) c
                             FROM last_names)

INSERT
INTO car_dealership.employee
(id, position, first_name, last_name,
 email, phone, hire_date, manager_id)
SELECT i,
       'Manager',
       fn.val,
       ln.val,

       lower(
               fn.val || '.' || ln.val || i || '@' ||
               (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] ||
               '.com'
       ),

       '+3897' || (i % 8) ||
       lpad((i % 1000000)::text, 6, '0'),

       CURRENT_DATE - (i % 7000),

       NULL

FROM generate_series(1, 5000) i
         JOIN first_names fn
              ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
         JOIN last_names ln
              ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;


-- =========================================================
-- EMPLOYEES: REMAINING 45,000
-- =========================================================

WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
                                  FROM unnest(ARRAY [
                                      'Anna','Mark','Ivan','Christian','Nicole','Andrew','Peter','Matthew',
                                      'Richard','Elena','Stefan','Maria','Daniel','Laura','John','Emma',
                                      'Petar','Nikola','Sara','Filip'
                                      ]) AS t(val)),

     last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
                                 FROM unnest(ARRAY [
                                     'Smith','Brown','Lee','Johnson','White','Peshevski',
                                     'Stojanov','Ivanov','Petrov','Georgiev','Trajkovski',
                                     'Kostov','Mitrevski','Ristov','Iliev'
                                     ]) AS t(val)),

     fn_cnt AS MATERIALIZED (SELECT count(*) c
                             FROM first_names),

     ln_cnt AS MATERIALIZED (SELECT count(*) c
                             FROM last_names)

INSERT
INTO car_dealership.employee
(id, position, first_name, last_name,
 email, phone, hire_date, manager_id)
SELECT i,

       (ARRAY [
           'Salesperson',
           'Manager',
           'Finance',
           'Admin'
           ])[(i % 4) + 1],

       fn.val,
       ln.val,

       lower(
               fn.val || '.' || ln.val || i || '@' ||
               (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] ||
               '.com'
       ),

       '+3897' || (i % 8) ||
       lpad((i % 1000000)::text, 6, '0'),

       CURRENT_DATE - (i % 7000),

       (i % 5000) + 1

FROM generate_series(5001, 50000) i
         JOIN first_names fn
              ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
         JOIN last_names ln
              ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;


-- =========================================================
-- CONFIGURATIONS
-- =========================================================

WITH customers AS MATERIALIZED (SELECT id,
                                       row_number() OVER (ORDER BY id) AS rn
                                FROM car_dealership.customer),

     vehicles AS MATERIALIZED (SELECT vin,
                                      row_number() OVER (ORDER BY vin) AS rn
                               FROM car_dealership.vehicle),

     vehicle_cnt AS MATERIALIZED (SELECT COUNT(*) AS c
                                  FROM vehicles),

     customer_slots AS MATERIALIZED (SELECT id      AS customer_id,
                                            rn      AS customer_rn,

                                            CASE
                                                WHEN rn % 20 = 0 THEN 1
                                                WHEN rn % 20 = 1 THEN 2
                                                WHEN rn % 20 < 5 THEN 3
                                                WHEN rn % 20 < 9 THEN 4
                                                WHEN rn % 20 < 12 THEN 5
                                                WHEN rn % 20 < 15 THEN 6
                                                WHEN rn % 20 < 18 THEN 7
                                                WHEN rn % 20 = 18 THEN 8
                                                ELSE 9
                                                END AS slot_count

                                     FROM customers),

     expanded AS MATERIALIZED (SELECT cs.customer_id,
                                      cs.customer_rn,
                                      gs.slot,

                                      (
                                                  SUM(cs.slot_count) OVER (
                                              ORDER BY cs.customer_rn
                                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                                              )
                                              - cs.slot_count
                                              + gs.slot
                                          )::int AS global_pos

                               FROM customer_slots cs
                                        CROSS JOIN LATERAL
                                   generate_series(1, cs.slot_count) AS gs(slot))

INSERT
INTO car_dealership.configuration
    (vin, description, total_price, created_at, customer_id)
SELECT v.vin,

       (ARRAY [
           'Standard configuration with base equipment',
           'Sport-oriented setup with performance packages',
           'Comfort-focused build for long-distance driving',
           'City commuter spec with economy features',
           'Family configuration with safety and comfort packs',
           'Executive spec with full luxury options',
           'Off-road ready with enhanced suspension package',
           'Hybrid-optimised configuration for fuel efficiency',
           'Tech-forward setup with full infotainment suite',
           'Winter spec with cold-weather equipment'
           ])[(e.global_pos % 10) + 1],

       ROUND(
               (
                   10000 +
                   (e.global_pos * 7 % 75001)
                   )::numeric(10, 2),
               2
       ),

       CURRENT_DATE - (e.global_pos % 2920),

       e.customer_id

FROM expanded e
         JOIN vehicles v
              ON v.rn =
                 ((e.global_pos - 1) %
                  (SELECT c FROM vehicle_cnt)) + 1;


-- =========================================================
-- PRODUCTION
-- =========================================================

WITH brand_factory
         (brand_id, factory_id, volume_weight)
         AS MATERIALIZED (VALUES (1, 1, 1.6),
                                 (2, 1, 1.4),

                                 (3, 2, 1.9),
                                 (4, 2, 2.1),

                                 (5, 3, 1.0),
                                 (6, 3, 1.2),

                                 (7, 4, 1.1),
                                 (8, 4, 0.9),

                                 (9, 5, 2.0),
                                 (10, 5, 2.2),

                                 (11, 6, 1.8),
                                 (12, 6, 1.6),

                                 (13, 7, 0.8),
                                 (14, 7, 0.7),

                                 (15, 8, 0.6),
                                 (16, 8, 0.7),

                                 (17, 9, 1.3),
                                 (18, 9, 1.5),

                                 (19, 10, 0.8),
                                 (20, 10, 0.7)),

     vehicle_factory AS MATERIALIZED (SELECT v.vin,
                                             bf.factory_id,
                                             bf.volume_weight

                                      FROM car_dealership.vehicle v
                                               JOIN car_dealership.model m
                                                    ON m.id = v.model_id
                                               JOIN brand_factory bf
                                                    ON bf.brand_id = m.brand_id)

INSERT
INTO car_dealership.production
    (factory_id, vin, status)
SELECT vf.factory_id,
       vf.vin,

       CASE
           WHEN r < 0.10 THEN 'Scheduled'

           WHEN r <
                0.10 +
                0.25 / vf.volume_weight
               THEN 'In Progress'

           WHEN r <
                0.10 +
                0.25 / vf.volume_weight +
                0.15 / vf.volume_weight
               THEN 'Quality Check'

           ELSE 'Completed'
           END

FROM vehicle_factory vf

         CROSS JOIN LATERAL (
    SELECT ABS(
                   (hashtext(vf.vin) # vf.factory_id)::float8 /
                   2147483647.0
           ) AS r
    ) rng;


-- =========================================================
-- ORDERS
-- =========================================================

WITH configs AS MATERIALIZED (SELECT DISTINCT ON (vin) id,
                                                       customer_id,
                                                       row_number() OVER (ORDER BY id)::int AS rn

                              FROM car_dealership.configuration
                              ORDER BY vin, id),

     employees AS MATERIALIZED (SELECT id,
                                       row_number() OVER (ORDER BY id)::int AS rn
                                FROM car_dealership.employee
                                WHERE position = 'Salesperson'),

     employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
                                   FROM employees)

INSERT
INTO car_dealership."Order"
    (date, status, customer_id, configuration_id, employee_id)
SELECT CURRENT_DATE - (60 + (c.rn % 3000)),

       (ARRAY [
           'Confirmed',
           'Pending',
           'Canceled',
           'Completed'
           ])[(c.rn % 4) + 1],

       c.customer_id,
       c.id,
       e.id

FROM configs c
         JOIN employees e
              ON e.rn =
                 ((c.rn - 1) %
                  (SELECT c FROM employee_cnt)) + 1;


-- =========================================================
-- CONTRACTS
-- =========================================================

WITH orders AS MATERIALIZED (SELECT DISTINCT ON (cfg.vin) o.id                                   AS order_id,
                                                          o.customer_id,
                                                          o.employee_id,
                                                          o.date                                 AS order_date,
                                                          cfg.vin,
                                                          row_number() OVER (ORDER BY o.id)::int AS rn

                             FROM car_dealership."Order" o

                                      JOIN car_dealership.configuration cfg
                                           ON cfg.id = o.configuration_id

                             WHERE o.status IN ('Confirmed', 'Completed')

                             ORDER BY cfg.vin, o.id),

     employees AS MATERIALIZED (SELECT id,
                                       row_number() OVER (ORDER BY id)::int AS rn

                                FROM car_dealership.employee
                                WHERE position = 'Salesperson'),

     employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
                                   FROM employees)

INSERT
INTO car_dealership.contract
(employee_id, notes, date, type,
 order_id, customer_id, vin)
SELECT e.id,

       'Contract ' || o.rn,

       o.order_date + (o.rn % 30),

       (ARRAY [
           'Standard',
           'Finance',
           'Fleet'
           ])[(o.rn % 3) + 1],

       o.order_id,
       o.customer_id,
       o.vin

FROM orders o
         JOIN employees e
              ON e.rn =
                 ((o.rn - 1) %
                  (SELECT c FROM employee_cnt)) + 1;


-- =========================================================
-- TEST DRIVES
-- =========================================================

WITH customers AS MATERIALIZED (SELECT id,
                                       row_number() OVER (ORDER BY id)::int AS rn
                                FROM car_dealership.customer),

     vehicles AS MATERIALIZED (SELECT vin,
                                      row_number() OVER (ORDER BY vin)::int AS rn
                               FROM car_dealership.vehicle),

     test_vehicles AS MATERIALIZED (SELECT vin, rn
                                    FROM vehicles
                                    WHERE rn <= 1000000),

     tv_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
                             FROM test_vehicles)

INSERT
INTO car_dealership.testdrive
(date, time_start, time_end,
 customer_id, vin, result)
SELECT CURRENT_DATE - (i % 2000),

       ts,

       ts +
       ((30 + (i % 11)) || ' minutes')::interval,

       c.id,

       v.vin,

       (ARRAY [
           'Interested',
           'Not Interested',
           'Follow-Up'
           ])[(i % 3) + 1]

FROM generate_series(1, 10000000) i

         CROSS JOIN LATERAL (
    SELECT TIMESTAMP '2024-01-01'
               + (i % 365) * INTERVAL '1 day'
               + (i % 86400) * INTERVAL '1 second'
               AS ts
    ) t

         JOIN customers c
              ON c.rn = ((i - 1) % 1000000) + 1

         JOIN test_vehicles v
              ON v.rn =
                 ((i - 1) %
                  (SELECT c FROM tv_cnt)) + 1;


-- =========================================================
-- CONFIGURATION PACKAGES
-- =========================================================

WITH configs AS MATERIALIZED (SELECT id,
                                     row_number() OVER (ORDER BY id) rn
                              FROM car_dealership.configuration),

     packages AS MATERIALIZED (SELECT id,
                                      row_number() OVER (ORDER BY id) rn
                               FROM car_dealership.equipmentpackage),

     package_cnt AS MATERIALIZED (SELECT count(*) c
                                  FROM packages)

INSERT
INTO car_dealership.configurationpackage
    (id, configuration_id, package_id)
SELECT (c.rn - 1) * 3 + p_idx,

       c.id,

       p.id

FROM configs c

         CROSS JOIN generate_series(1, 3) AS p_idx

         JOIN packages p
              ON p.rn =
                 ((c.rn + p_idx - 2) %
                  (SELECT c FROM package_cnt)) + 1;


-- =========================================================
-- CONFIGURATION ELEMENTS
-- =========================================================

WITH cp AS MATERIALIZED (SELECT id,
                                row_number() OVER (ORDER BY id) rn
                         FROM car_dealership.configurationpackage),

     elements AS MATERIALIZED (SELECT id,
                                      row_number() OVER (ORDER BY id) rn
                               FROM car_dealership.packageelement),

     element_cnt AS MATERIALIZED (SELECT count(*) c
                                  FROM elements)

INSERT
INTO car_dealership.configurationelement
    (id, configuration_package_id, package_element_id)
SELECT (cp.rn - 1) * 2 + e_idx,

       cp.id,

       e.id

FROM cp

         CROSS JOIN generate_series(1, 2) AS e_idx

         JOIN elements e
              ON e.rn =
                 ((cp.rn + e_idx - 2) %
                  (SELECT c FROM element_cnt)) + 1;


-- =========================================================
-- SALES
-- =========================================================

WITH contracts AS MATERIALIZED (SELECT id,
                                       customer_id,
                                       date                                 AS contract_date,
                                       row_number() OVER (ORDER BY id)::int AS rn
                                FROM car_dealership.contract),

     employees AS MATERIALIZED (SELECT id,
                                       row_number() OVER (ORDER BY id)::int AS rn

                                FROM car_dealership.employee
                                WHERE position = 'Salesperson'),

     employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
                                   FROM employees)

INSERT
INTO car_dealership.sale
    (date, contract_id, customer_id, employee_id)
SELECT c.contract_date + (c.rn % 30),

       c.id,

       c.customer_id,

       e.id

FROM contracts c
         JOIN employees e
              ON e.rn =
                 ((c.rn - 1) %
                  (SELECT c FROM employee_cnt)) + 1;


-- =========================================================
-- MARK SOLD VEHICLES
-- =========================================================

UPDATE car_dealership.vehicle v
SET status_id = (SELECT id
                 FROM car_dealership.status
                 WHERE status = 'Sold')
WHERE EXISTS (SELECT 1

              FROM car_dealership.contract c
                       JOIN car_dealership.sale s
                            ON s.contract_id = c.id

              WHERE c.vin = v.vin);


-- =========================================================
-- COMPLETE PRODUCTION FOR SOLD VEHICLES
-- =========================================================

UPDATE car_dealership.production p
SET status = 'Completed'
WHERE EXISTS (SELECT 1

              FROM car_dealership.contract c
                       JOIN car_dealership.sale s
                            ON s.contract_id = c.id

              WHERE c.vin = p.vin);


-- =========================================================
-- PAYMENTS
-- =========================================================

WITH sales AS MATERIALIZED (SELECT s.id,
                                   row_number() OVER (ORDER BY s.id) AS rn,
                                   conf.total_price

                            FROM car_dealership.sale s

                                     JOIN car_dealership.contract ct
                                          ON ct.id = s.contract_id

                                     JOIN car_dealership."Order" o
                                          ON o.id = ct.order_id

                                     JOIN car_dealership.configuration conf
                                          ON conf.id = o.configuration_id)

INSERT
INTO car_dealership.payment
    (id, type, amount, sale_id)
SELECT rn,

       (ARRAY [
           'Cash',
           'Installment',
           'Leasing'
           ])[(rn % 3) + 1],

       total_price,

       id

FROM sales;


-- =========================================================
-- DISCOUNTS
-- =========================================================

WITH payments AS MATERIALIZED (SELECT id,
                                      row_number() OVER (ORDER BY id)::int AS rn
                               FROM car_dealership.payment),

     employees AS MATERIALIZED (SELECT id,
                                       row_number() OVER (ORDER BY id)::int AS rn

                                FROM car_dealership.employee

                                WHERE position IN ('Finance', 'Manager')),

     employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
                                   FROM employees)

INSERT
INTO car_dealership.discount
    (percentage, payment_id, employee_id)
SELECT (5 + (p.rn % 21))::smallint,

       p.id,

       e.id

FROM payments p

         JOIN employees e
              ON e.rn =
                 ((p.rn - 1) %
                  (SELECT c FROM employee_cnt)) + 1

WHERE p.rn % 10 < 3;


-- =========================================================
-- UPDATE STATISTICS AFTER BULK LOAD
-- =========================================================

/*ANALYZE car_dealership.factory;
ANALYZE car_dealership.brand;
ANALYZE car_dealership.model;
ANALYZE car_dealership.customer;
ANALYZE car_dealership.address;
ANALYZE car_dealership.engine;
ANALYZE car_dealership.vehicle;
ANALYZE car_dealership.employee;
ANALYZE car_dealership.configuration;
ANALYZE car_dealership.production;
ANALYZE car_dealership."Order";
ANALYZE car_dealership.contract;
ANALYZE car_dealership.testdrive;
ANALYZE car_dealership.configurationpackage;
ANALYZE car_dealership.configurationelement;
ANALYZE car_dealership.sale;
ANALYZE car_dealership.payment;
ANALYZE car_dealership.discount;*/

SELECT st.status, COUNT(*)
FROM car_dealership.Sale s
JOIN car_dealership.Contract c ON c.id = s.contract_id
JOIN car_dealership.Vehicle v ON v.vin = c.vin
JOIN car_dealership.Status st ON st.id = v.status_id
GROUP BY st.status;