SET search_path TO car_dealership;
TRUNCATE TABLE car_dealership.discount CASCADE;
TRUNCATE TABLE car_dealership.payment CASCADE;
TRUNCATE TABLE car_dealership.sale CASCADE;
TRUNCATE TABLE car_dealership.contract CASCADE;
TRUNCATE TABLE car_dealership."Order" CASCADE;
TRUNCATE TABLE car_dealership.testdrive CASCADE;
TRUNCATE TABLE car_dealership.production CASCADE;
TRUNCATE TABLE car_dealership.vehicle CASCADE;
TRUNCATE TABLE car_dealership.engine CASCADE;
TRUNCATE TABLE car_dealership.configuration CASCADE;
TRUNCATE TABLE car_dealership.customer CASCADE;
TRUNCATE TABLE car_dealership.employee CASCADE;
TRUNCATE TABLE car_dealership.model CASCADE;
TRUNCATE TABLE car_dealership.factory CASCADE;
TRUNCATE TABLE car_dealership.brand CASCADE;
TRUNCATE TABLE car_dealership.enginetype CASCADE;
TRUNCATE TABLE car_dealership.vehicletype CASCADE;
TRUNCATE TABLE car_dealership.status CASCADE;
TRUNCATE TABLE car_dealership.equipmentpackage CASCADE;
TRUNCATE TABLE car_dealership.equipmenttype CASCADE;
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');

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');

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

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');

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

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


INSERT INTO car_dealership.model (id, model, brand_id, year)
VALUES
    -- BMW
    (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),

    -- Mercedes-Benz
    (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),

    -- Audi
    (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),

    -- Volkswagen
    (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),

    -- Porsche
    (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),

    -- Peugeot
    (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),

    -- Renault
    (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),

    -- Škoda
    (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),

    -- Volvo
    (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),

    -- Toyota
    (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),

    -- Honda
    (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),

    -- Nissan
    (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),

    -- Mazda
    (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),

    -- Subaru
    (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),

    -- Mitsubishi
    (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),

    -- Suzuki
    (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),

    -- Geely (only entered market significantly around 2015+)
    (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),

    -- BYD (significant from 2015+, mostly electric)
    (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),

    -- Chery
    (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),

    -- GWM
    (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);

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                                                            AS first_name,
       ln.val                                                            AS last_name,
       lower(fn.val || '.' || ln.val || i || '@' ||
             (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] || '.com') AS email,
       '+3897' || (i % 8) || lpad((i % 1000000)::text, 6, '0')           AS phone,
       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 fixed postal codes per city
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),
                                          (15, 'Struga', 6330)) 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                 AS id,
       'Macedonia' AS country,
       c.city,
       c.postal_code,
       s.street,
       (i % 200) + 1     AS building_number,
       (i % 10) + 1      AS entry_number,
       (i % 50) + 1      AS apartment_number,
       i                 AS customer_id,
       NULL              AS factory_id
FROM generate_series(1, 1000000) i
         JOIN cities c ON c.idx = (i % 16)
         JOIN streets s ON s.idx = (i % 20);


-- Factory addresses with fixed postal codes per city
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),
                                          (15, 'Struga', 6330)) 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       AS id,
       'Macedonia' AS country,
       c.city,
       c.postal_code,
       s.street,
       (i % 200) + 1     AS building_number,
       NULL              AS entry_number,
       NULL              AS apartment_number,
       NULL              AS customer_id,
       i                 AS factory_id
FROM generate_series(1, 10) i
         JOIN cities c ON c.idx = (i % 16)
         JOIN streets s ON s.idx = (i % 20);


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);


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')
       )            AS enginenumber,

       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 AS horsepower,

       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      AS enginetypeid

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



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 = 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') AS vin,
       mf.model_id,
       s.id                                           AS status_id,
       mf.vehicle_type_id,
       (ARRAY ['Black','White','Grey','Silver','Red','Blue'])[(i % 6) + 1],
       mf.base_price,
       mf.year                                        AS production_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;



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]      AS description,

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

       CURRENT_DATE - (e.global_pos % 2920) AS created_at,

       e.customer_id

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


-- Step 1: Insert managers (i = 1..5000), manager_id is NULL
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),
       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;


-- Step 2: Insert remaining employees (i = 5001..50000), manager_id references Step 1
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;



WITH brand_factory(brand_id, factory_id, volume_weight) AS MATERIALIZED (VALUES (1, 1, 1.6),
                                                                                (2, 1,
                                                                                 1.4), -- BMW, Mercedes  → Factory 1
                                                                                (3, 2, 1.9),
                                                                                (4, 2,
                                                                                 2.1), -- Audi, VW       → Factory 2
                                                                                (5, 3, 1.0),
                                                                                (6, 3,
                                                                                 1.2), -- Porsche, Peug. → Factory 3
                                                                                (7, 4, 1.1),
                                                                                (8, 4,
                                                                                 0.9), -- Renault, Škoda → Factory 4
                                                                                (9, 5, 2.0),
                                                                                (10, 5,
                                                                                 2.2), -- Volvo, Toyota  → Factory 5
                                                                                (11, 6, 1.8),
                                                                                (12, 6,
                                                                                 1.6), -- Honda, Nissan  → Factory 6
                                                                                (13, 7, 0.8),
                                                                                (14, 7,
                                                                                 0.7), -- Mazda, Subaru  → Factory 7
                                                                                (15, 8, 0.6),
                                                                                (16, 8,
                                                                                 0.7), -- Mitsub., Suzu. → Factory 8
                                                                                (17, 9, 1.3),
                                                                                (18, 9,
                                                                                 1.5), -- Geely, BYD     → Factory 9
                                                                                (19, 10, 0.8),
                                                                                (20, 10,
                                                                                 0.7) -- Chery, GWM     → Factory 10
),

     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 (hashtext(vf.vin) # vf.factory_id)::float8 / 2147483647.0 AS r
    ) rng;


TRUNCATE TABLE car_dealership."Order" CASCADE;

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),

     config_cnt AS (SELECT COUNT(*)::int AS c FROM configs),
     employee_cnt AS (SELECT COUNT(*)::int AS c FROM employees)

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

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

       c.customer_id,

       c.id                                                                   AS configuration_id,

       e.id                                                                   AS employee_id

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

WITH orders AS MATERIALIZED (SELECT DISTINCT ON (cfg.vin) o.id                                   AS order_id,
                                                          o.customer_id,
                                                          o.employee_id,
                                                          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
                             ORDER BY cfg.vin, o.id),

     employees AS MATERIALIZED (SELECT id,
                                       row_number() OVER (ORDER BY id)::int AS rn
                                FROM car_dealership.employee),

     employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c FROM employees),
     customer_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c FROM (SELECT id FROM car_dealership.customer) x)

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

       'Contract ' || o.rn                                    AS notes,

       CURRENT_DATE - (o.rn % 3650)                           AS date,

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

       o.order_id,

       CASE
           WHEN o.rn % 20 = 0
               THEN (o.rn % (SELECT c FROM customer_cnt)) + 1
           ELSE o.customer_id
           END                                                AS customer_id,

       o.vin

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


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 (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)                                        AS date,

       ts,

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

       c.id                                                             AS customer_id,

       v.vin,

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

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;

WITH configs AS MATERIALIZED (SELECT id, row_number() OVER () rn
                              FROM car_dealership.configuration),
     packages AS MATERIALIZED (SELECT id, row_number() OVER () 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 AS id,

       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;



WITH cp AS MATERIALIZED (SELECT id, row_number() OVER () rn
                         FROM car_dealership.configurationpackage),
     elements AS MATERIALIZED (SELECT id, row_number() OVER () rn
                               FROM car_dealership.packageelement),
     element_cnt AS (SELECT count(*) c
                     FROM elements)

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

       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;

WITH contracts AS MATERIALIZED (SELECT id,
                                       customer_id,
                                       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),

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

INSERT
INTO car_dealership.sale
    (date, contract_id, customer_id, employee_id)
SELECT CURRENT_DATE - (c.rn % 2000) AS date,

       c.id                         AS contract_id,

       c.customer_id                AS customer_id,

       e.id                         AS employee_id

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

WITH sales AS MATERIALIZED (SELECT s.id,
                                   row_number() OVER () 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;

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),

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

INSERT
INTO car_dealership.discount
    (percentage, payment_id, employee_id)
SELECT
    -- Discount between 5% and 25%
    (5 + (p.rn % 21))::smallint AS percentage,

    p.id                        AS payment_id,

    e.id                        AS employee_id

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

-- Only ~30% of payments receive a discount.
-- rn % 10 < 3 means positions 0,1,2 out of every 10 → 30%
WHERE p.rn % 10 < 3;