DatabaseCreation: dml.sql

create table if not exists _temp_male_names
(
    id   bigserial primary key,
    name text
);

create table if not exists _temp_female_names
(
    id   bigserial primary key,
    name text
);

create table if not exists _temp_surnames
(
    id      bigserial primary key,
    surname text
);

select count(*)
from _temp_female_names;
select count(*)
from _temp_male_names;
select count(*)
from _temp_surnames;

with male_names as (select name, row_number() over () as rn from _temp_male_names order by random()),
     surnames as (select surname, row_number() over () as rn from _temp_surnames order by random()),
     combined as (select mn.name              as first_name,
                         s.surname            as last_name,
                         regexp_replace(lower(mn.name || s.surname), '[^a-zA-Z0-9]', '', 'g') ||
                         row_number() over () as username
                  from male_names mn
                           cross join surnames s
                  limit 500000)
insert
into appuser (first_name, last_name, username, password_hash, date_of_birth, phone_number)
select first_name,
       last_name,
       username,
       substring(md5(random()::text) from 1 for 12),
       '2020-01-01',
       '+38975000000'
from combined
on conflict (username) do nothing;

with female_names as (select name, row_number() over () as rn from _temp_female_names order by random()),
     surnames as (select surname, row_number() over () as rn from _temp_surnames order by random()),
     combined as (select fn.name              as first_name,
                         s.surname            as last_name,
                         regexp_replace(lower(fn.name || s.surname), '[^a-zA-Z0-9]', '', 'g') ||
                         row_number() over () as username
                  from female_names fn
                           cross join surnames s
                  limit 500000)
insert
into appuser (first_name, last_name, username, password_hash, date_of_birth, phone_number)
select first_name,
       last_name,
       username,
       substring(md5(random()::text) from 1 for 12),
       '2020-01-01',
       '+38975000000'
from combined
on conflict (username) do nothing;
-- update appuser
-- set password_hash = crypt(password_hash, gen_salt('bf')); -- encrypt password
update appuser
set email = lower(
        username || '@' ||
        (array ['gmail.com', 'yahoo.com', 'outlook.com', 'protonmail.com', 'icloud.com'])[floor(random() * 5 + 1)]
            );

update appuser
set phone_number = '+3897'
                       || (array ['0', '1', '2', '5', '7', '8'])[floor(random() * 6 + 1)]
    || floor(random() * (999999 - 100000 + 1) + 100000)::text;

update appuser
set date_of_birth = (now() - interval '70 years' * random())::date;


insert into customer
select id
from appuser
order by random()
limit 800000;

insert into admin
select id
from appuser
order by random()
limit 100;

insert into dispatcher
select id
from appuser
order by random()
limit 5000;

-- DRIVER License
create temp table if not exists seed_params as
select 20000 as total_drivers;
insert into driverlicense (issue_date, expire_date, license_id)
select '2024-01-01'::date + (random() * 500)::int  as issue_date,
       '2030-01-01'::date + (random() * 2000)::int as expire_date,
       'U' || lpad(s.i::text, 7, '0')              as license_id
from generate_series(1, (select total_drivers from seed_params)) as s(i);

with recursive
    target_pool as (select license_id,
                           issue_date as next_start_date,
                           1          as depth
                    from driverlicense
                    order by random()
                    limit (select 0.3 * total_drivers from seed_params)),
    license_chain as (select license_id,
                             next_start_date,
                             depth
                      from target_pool

                      union all

                      select license_id,
                             (next_start_date - 3655 - (random() * 100)::int)::date,
                             depth + 1
                      from license_chain
                      where depth < 3
                        and random() > 0.4)
insert
into driverlicense (issue_date, expire_date, license_id)
select (next_start_date - 3650)::date                        as issue_date,
       (next_start_date - (10 + (random() * 30)::int))::date as expire_date,
       license_id
from license_chain;

insert into driverlicense_driverlicensecategory (driver_license_id, driver_license_category_id)
select dl.id, cat.id
from driverlicense dl
         cross join driverlicensecategory cat
where cat.category_name in ('B', 'F', 'G', 'AM')
on conflict do nothing;

with biker_pool as (select license_id, array_agg(id) as ids
                    from driverlicense
                    group by license_id
                    order by random()
                    limit 3000)
insert
into driverlicense_driverlicensecategory (driver_license_id, driver_license_category_id)
select unnest(ids), cat.id
from biker_pool
         cross join driverlicensecategory cat
where cat.category_name in ('A', 'A1', 'A2')
on conflict do nothing;

with truck_pool as (select license_id, array_agg(id) as ids
                    from driverlicense
                    group by license_id
                    order by random()
                    limit 2000),
     truck_base as (
         insert into driverlicense_driverlicensecategory (driver_license_id, driver_license_category_id)
             select unnest(ids), cat.id
             from truck_pool
                      cross join driverlicensecategory cat
             where cat.category_name in ('C', 'C1')
             on conflict do nothing
             returning driver_license_id)
insert
into driverlicense_driverlicensecategory (driver_license_id, driver_license_category_id)
select driver_license_id, cat.id
from truck_base
         cross join driverlicensecategory cat
where cat.category_name in ('CE', 'C1E')
  and random() > 0.5
on conflict do nothing;

with bus_pool as (select license_id, array_agg(id) as ids
                  from driverlicense
                  group by license_id
                  order by random()
                  limit 600),
     bus_base as (
         insert into driverlicense_driverlicensecategory (driver_license_id, driver_license_category_id)
             select unnest(ids), cat.id
             from bus_pool
                      cross join driverlicensecategory cat
             where cat.category_name in ('D', 'D1')
             on conflict do nothing
             returning driver_license_id)
insert
into driverlicense_driverlicensecategory (driver_license_id, driver_license_category_id)
select driver_license_id, cat.id
from bus_base
         cross join driverlicensecategory cat
where cat.category_name in ('DE', 'D1E')
  and random() > 0.3
on conflict do nothing;

with special_pool as (select license_id, array_agg(id) as ids from driverlicense group by license_id)
insert
into driverlicense_driverlicensecategory (driver_license_id, driver_license_category_id)
select unnest(ids), (select id from driverlicensecategory where category_name = 'BE')
from special_pool
where random() < 0.1
on conflict do nothing;

with special_pool as (select license_id, array_agg(id) as ids from driverlicense group by license_id)
insert
into driverlicense_driverlicensecategory (driver_license_id, driver_license_category_id)
select unnest(ids), (select id from driverlicensecategory where category_name = 'T')
from special_pool
where random() < 0.05
on conflict do nothing;

insert into driver
with unique_candidate_users as (select appuser.id
                                from appuser
                                where appuser.date_of_birth <= (current_date - interval '18 days')
                                  and not exists(select 1 from admin where admin.user_id = appuser.id)
                                  and not exists(select 1 from dispatcher where dispatcher.user_id = appuser.id)
                                order by random()
                                limit (select total_drivers from seed_params)),
     active_licenses as (select id, row_number() over () as rn
                         from driverlicense
                         where expire_date > current_date
                           and id in (select distinct on (license_id) id
                                      from driverlicense
                                      order by license_id, expire_date desc)),
     user_pool as (select id, row_number() over () as rn
                   from unique_candidate_users)
select user_pool.id       as user_id,
       active_licenses.id as driver_license_id
from user_pool
         join active_licenses on active_licenses.rn = user_pool.rn
on conflict do nothing;

insert into freelancedriver(driver_user_id)
select driver.user_id
from driver
order by random()
limit (select count(*) * 0.5 from driver);

insert into company (name, date_founded, tax_number, phone_number, email, website, active)
select
    (array ['City', 'Classic', 'Lotus', 'Ekstra', 'Global', 'Bel', 'Plav', 'Zolt', 'De Luks', 'Elite', 'Skopje', 'Vardar', 'Prilep', 'Veles', 'Struga', 'Bitola', 'Brajko'])[floor(random() * 14 + 1)] ||
    ' ' ||
    (array ['Taxi', 'Prevoz', 'Transport', 'Mobiliti', 'Ekspres'])[floor(random() * 5 + 1)] || ' ' ||
    (array ['DOOEL', 'DOO'])[floor(random() * 2 + 1)]                               as name,
    ('1995-01-01'::date + (random() * 10000)::int * INTERVAL '1 day')::date         as date_founded,
    '4' || floor(random() * (999999999999 - 100000000000 + 1) + 100000000000)::text as tax_number,
    ('+3897' || (array ['0', '1', '2', '5', '7', '8'])[random() * 5 + 1] ||
     floor(random() * (99999 - 10000 + 1) + 10000)::text)                           as phone_number,
    'contact@tmp.mk'                                                                as email,
    'http://tmp.mk'                                                                 as website,
    (random() < 0.95)                                                               as active
from generate_series(1, 300) as s(i);


update company
set email   = lower(regexp_replace(name, '[^a-zA-Z0-9]', '', 'g')) || '@' ||
              (array ['taxi.mk', 'telekom.mk', 'gmail.com', 'outlook.com', 'net.mk'])[floor(random() * 5 + 1)],
    website = 'https://www.' || lower(regexp_replace(name, '[^a-zA-Z0-9]', '', 'g')) || '.mk';

insert into area (latitude, longitude, radius, name)
values (41.9961, 21.4317, 1600, 'Centar'),
       (41.9781, 21.4678, 2500, 'Aerodrom'),
       (41.9939, 21.3917, 3300, 'Karposh'),
       (42.0114, 21.4503, 1000, 'Chair'),
       (41.9492, 21.4533, 3700, 'Kisela Voda'),
       (42.0039, 21.4939, 5400, 'Gazi Baba'),
       (42.0536, 21.4447, 4400, 'Butel'),
       (42.0647, 21.3783, 4600, 'Gjorche Petrov'),
       (42.0153, 21.2647, 8500, 'Saraj'),
       (42.0353, 21.4003, 1500, 'Shuto Orizari'),

       -- Eastern Region
       (41.7022, 22.8575, 13800, 'Berovo'),
       (41.9142, 22.2272, 6500, 'Cheshinovo-Obleshevo'),
       (41.9664, 22.7747, 11600, 'Delchevo'),
       (41.8156, 22.2319, 8600, 'Karbinci'),
       (41.9167, 22.4125, 10700, 'Kochani'),
       (42.0192, 22.5878, 7800, 'Makedonska Kamenica'),
       (41.7619, 22.8875, 8100, 'Pehchevo'),
       (41.8953, 22.1742, 10200, 'Probistip'),
       (41.7358, 22.1914, 13600, 'Shtip'),
       (41.8761, 22.5086, 11700, 'Vinica'),
       (41.8753, 22.4419, 4000, 'Zrnovci'),

       -- Northeastern Region
       (42.0792, 22.1750, 10900, 'Kratovo'),
       (42.2017, 22.3317, 12400, 'Kriva Palanka'),
       (42.1322, 21.7144, 12700, 'Kumanovo'),
       (42.1550, 21.5875, 9300, 'Lipkovo'),
       (42.1697, 22.1153, 8700, 'Rankovce'),
       (42.1983, 21.8267, 12800, 'Staro Nagorichane'),

       -- Pelagonia Region
       (41.0319, 21.3347, 15800, 'Bitola'),
       (41.2208, 21.2031, 12400, 'Demir Hisar'),
       (41.4253, 21.4533, 11500, 'Dolneni'),
       (41.3353, 21.3319, 5400, 'Krivogashtani'),
       (41.3697, 21.2492, 7800, 'Krushevo'),
       (41.3000, 21.4667, 9000, 'Mogila'),
       (41.0417, 21.5583, 15500, 'Novaci'),
       (41.3464, 21.5542, 19500, 'Prilep'),
       (41.0889, 21.0125, 13200, 'Resen'),

       -- Polog Region
       (41.9239, 20.9136, 6700, 'Bogovinje'),
       (41.9406, 20.9822, 7200, 'Brvenica'),
       (41.7920, 20.9082, 10900, 'Gostivar'),
       (42.0722, 21.1214, 7500, 'Jegunovce'),
       (41.6108, 20.5986, 16500, 'Mavrovo and Rostusha'),
       (42.0772, 21.0533, 6600, 'Tearce'),
       (42.0103, 20.9714, 9100, 'Tetovo'),
       (41.8317, 20.8856, 7100, 'Vrapchishte'),
       (41.9794, 21.0631, 8000, 'Zhelino'),

       -- Southwestern Region
       (41.5250, 20.5272, 6800, 'Debar'),
       (41.2144, 20.7497, 11600, 'Debarca'),
       (41.5142, 20.9631, 16200, 'Kichevo'),
       (41.0114, 21.1319, 5800, 'Makedonski Brod'),
       (41.1169, 20.8019, 11100, 'Ohrid'),
       (41.4667, 20.5333, 4200, 'Plasnica'),
       (41.1775, 20.6789, 12400, 'Struga'),
       (41.4939, 20.6019, 5800, 'Centar Zhupa'),
       (41.2406, 20.5928, 2800, 'Vevchani'),

       -- Southeastern Region
       (41.2033, 22.5103, 6000, 'Bogdanci'),
       (41.4300, 22.7275, 7200, 'Bosilovo'),
       (41.1878, 22.6931, 6400, 'Dojran'),
       (41.1392, 22.5025, 12400, 'Gevgelija'),
       (41.6000, 22.5667, 8600, 'Konche'),
       (41.4111, 22.9736, 8700, 'Novo Selo'),
       (41.6381, 22.4644, 12600, 'Radovish'),
       (41.4375, 22.6431, 10100, 'Strumica'),
       (41.3614, 22.7503, 6500, 'Vasilevo'),
       (41.3172, 22.5611, 10800, 'Valandovo'),

       -- Vardar Region
       (41.6431, 21.6911, 16100, 'Chashka'),
       (41.4111, 22.2417, 9900, 'Demir Kapija'),
       (41.5833, 21.9000, 8700, 'Gradsko'),
       (41.4328, 22.0117, 17700, 'Kavadarci'),
       (41.7803, 21.9000, 7300, 'Lozovo'),
       (41.4839, 22.0892, 11600, 'Negotino'),
       (41.5161, 21.9458, 6500, 'Rosoman'),
       (41.8650, 21.9425, 12400, 'Sveti Nikole'),
       (41.7153, 21.7753, 11700, 'Veles'),

       -- Additional Skopje Region (Rural)
       (42.0350, 21.6167, 3500, 'Arachinovo'),
       (42.1553, 21.4331, 8700, 'Chucher-Sandevo'),
       (41.9961, 21.5542, 5500, 'Ilinden'),
       (41.9286, 21.5956, 8400, 'Petrovec'),
       (41.8953, 21.3117, 8400, 'Sopishte'),
       (41.9167, 21.5333, 9400, 'Studenichani'),
       (41.8667, 21.4667, 6100, 'Zelenikovo'),
       (40.9950, 20.8000, 5000, 'Peshtani & Trpejca Coast'),
       (40.9150, 21.1500, 6000, 'Lower Prespa'),
       (41.0000, 21.2000, 7000, 'Pelister National Park'),

       (41.8800, 21.1850, 8000, 'Kozjak & Jasen Reserve'),
       (41.8950, 21.6150, 5000, 'Taor & Katlanovo Region'),
       (41.9400, 21.3000, 5000, 'Matka Canyon Deep'),

       (42.0140, 20.8800, 6000, 'Popova Shapka Resort'),
       (41.5500, 21.1900, 9000, 'Poreche Region'),

       (41.2460, 21.3850, 4500, 'Chepigovo & Stuberra'),
       (41.5210, 21.3420, 5000, 'Mount Dautica Foothills'),

       (42.0120, 22.2270, 5000, 'Lesnovo & Zletovo Region'),
       (41.3650, 22.9050, 5000, 'Belasica Foothills');


insert into pricinginfo (value, unit, currency_id)
select val, unt::unit_type, (select id from currencycatalog where currency = 'MKD')
from (values
          (25.00, 'kilometer'),
          (30.00, 'kilometer'),
          (35.00, 'kilometer'),
          (45.00, 'kilometer'),
          (5.00, 'minute'),
          (8.00, 'minute'),
          (12.00, 'minute')
     ) as t(val, unt);

insert into company_area
select company.id as company_id,
       area.id    as area_id,
       p.id       as pricing_info_id
from company
         join area on (company.id % 92) + 1 = (select rn
                                               from (select id, row_number() over (order by id) as rn from area) t
                                               where t.id = area.id)
         cross join lateral (
    select id
    from pricinginfo
    where unit = 'kilometer'
      and (
        (area.radius < 3000 and value <= 30) or
        (area.radius >= 3000 and area.radius < 10000) or
        (area.radius >= 10000 and value >= 35)
        )
    order by random()
    limit 1
    ) p
on conflict do nothing;

insert into company_area (company_id, area_id, pricing_info_id)
select c.id,
       a.id,
       p.id
from company c
         join area a on a.name in ('Centar', 'Aerodrom', 'Karposh', 'Bitola', 'Tetovo', 'Gostivar', 'Kumanovo')
         cross join lateral (
    select pi.id
    from pricinginfo pi
    where pi.unit = 'minute'
      and c.id is not null
    order by random()
    limit 1
    ) p
where random() < 0.4
on conflict do nothing;

insert into Brand (name, date_founded, phone_number, email)
values ('Toyota', '1937-08-28', '+38970100100', 'contact@toyota.mk'),
       ('Volkswagen', '1937-05-28', '+38971200200', 'info@volkswagen.mk'),
       ('Ford', '1903-06-16', '+38972300300', 'sales@ford.mk'),
       ('Honda', '1948-09-24', '+38975400400', 'office@honda.mk'),
       ('Chevrolet', '1911-11-03', '+38976500500', 'support@chevrolet.mk'),
       ('Nissan', '1933-12-26', '+38977600600', 'contact@nissan.mk'),
       ('Hyundai', '1967-12-29', '+38978700700', 'info@hyundai.mk'),
       ('Kia', '1944-06-09', '+38970800800', 'sales@kia.mk'),
       ('Mercedes-Benz', '1926-06-28', '+38971900900', 'office@mercedesbenz.mk'),
       ('BMW', '1916-03-07', '+38972111222', 'contact@bmw.mk'),
       ('Audi', '1909-07-16', '+38975222333', 'info@audi.mk'),
       ('Peugeot', '1896-05-09', '+38976333444', 'sales@peugeot.mk'),
       ('Renault', '1899-02-25', '+38977444555', 'office@renault.mk'),
       ('Fiat', '1899-07-11', '+38978555666', 'contact@fiat.mk'),
       ('Skoda', '1895-12-18', '+38970666777', 'info@skoda.mk'),
       ('Volvo', '1927-04-14', '+38971777888', 'sales@volvo.mk'),
       ('Mazda', '1920-01-30', '+38972888999', 'office@mazda.mk'),
       ('Subaru', '1953-07-15', '+38975999000', 'contact@subaru.mk'),
       ('Porsche', '1931-04-25', '+38976101101', 'info@porsche.mk'),
       ('Lexus', '1989-09-01', '+38977202202', 'sales@lexus.mk'),
       ('Jeep', '1941-07-15', '+38978303303', 'office@jeep.mk'),
       ('Land Rover', '1948-04-30', '+38970404404', 'contact@landrover.mk'),
       ('Tesla', '2003-07-01', '+38971505505', 'info@tesla.mk'),
       ('Mitsubishi', '1970-04-22', '+38972606606', 'sales@mitsubishi.mk'),
       ('Suzuki', '1909-10-01', '+38975707707', 'office@suzuki.mk'),
       ('Citroen', '1919-03-01', '+38976808808', 'contact@citroen.mk'),
       ('SEAT', '1950-05-09', '+38977909909', 'info@seat.mk'),
       ('Dacia', '1966-08-01', '+38978111000', 'sales@dacia.mk'),
       ('Alfa Romeo', '1910-06-24', '+38970222000', 'office@alfaromeo.mk'),
       ('Jaguar', '1922-09-04', '+38971333000', 'contact@jaguar.mk');

with brand_models as (select 'Toyota'                                                           as b_name,
                             array ['Corolla', 'Camry', 'Yaris', 'RAV4', 'Prius', 'Highlander'] as m_names
                      union all
                      select 'Volkswagen', array ['Golf', 'Polo', 'Passat', 'Tiguan', 'Touareg', 'Arteon']
                      union all
                      select 'Ford', array ['Fiesta', 'Focus', 'Mondeo', 'Mustang', 'Kuga', 'Puma']
                      union all
                      select 'Honda', array ['Civic', 'Accord', 'CR-V', 'HR-V', 'Jazz']
                      union all
                      select 'Chevrolet', array ['Spark', 'Malibu', 'Camaro', 'Tahoe', 'Equinox']
                      union all
                      select 'Nissan', array ['Micra', 'Qashqai', 'X-Trail', 'Altima', 'Leaf', 'Juke']
                      union all
                      select 'Hyundai', array ['i10', 'i20', 'i30', 'Tucson', 'Santa Fe', 'Kona']
                      union all
                      select 'Kia', array ['Picanto', 'Rio', 'Ceed', 'Sportage', 'Sorento', 'Stonic']
                      union all
                      select 'Mercedes-Benz', array ['A-Class', 'C-Class', 'E-Class', 'S-Class', 'GLC', 'GLE']
                      union all
                      select 'BMW', array ['1 Series', '3 Series', '5 Series', '7 Series', 'X3', 'X5']
                      union all
                      select 'Audi', array ['A3', 'A4', 'A6', 'Q3', 'Q5', 'Q7']
                      union all
                      select 'Peugeot', array ['208', '308', '508', '2008', '3008', '5008']
                      union all
                      select 'Renault', array ['Clio', 'Megane', 'Captur', 'Kadjar', 'Scenic', 'Twingo']
                      union all
                      select 'Fiat', array ['500', 'Panda', 'Tipo', 'Punto', 'Ducato']
                      union all
                      select 'Skoda', array ['Fabia', 'Octavia', 'Superb', 'Karoq', 'Kodiaq', 'Kamiq']
                      union all
                      select 'Volvo', array ['XC40', 'XC60', 'XC90', 'S60', 'V60', 'V90']
                      union all
                      select 'Mazda', array ['Mazda2', 'Mazda3', 'Mazda6', 'CX-30', 'CX-5']
                      union all
                      select 'Subaru', array ['Impreza', 'Outback', 'Forester', 'Crosstrek', 'Legacy']
                      union all
                      select 'Porsche', array ['911', 'Cayenne', 'Macan', 'Panamera', 'Taycan']
                      union all
                      select 'Lexus', array ['IS', 'ES', 'RX', 'NX', 'UX', 'LS']
                      union all
                      select 'Jeep', array ['Wrangler', 'Cherokee', 'Grand Cherokee', 'Renegade', 'Compass']
                      union all
                      select 'Land Rover', array ['Defender', 'Discovery', 'Range Rover', 'Evoque', 'Velar']
                      union all
                      select 'Tesla', array ['Model S', 'Model 3', 'Model X', 'Model Y']
                      union all
                      select 'Mitsubishi', array ['Colt', 'Lancer', 'Outlander', 'ASX', 'Eclipse Cross']
                      union all
                      select 'Suzuki', array ['Swift', 'Vitara', 'Jimny', 'Ignis', 'S-Cross']
                      union all
                      select 'Citroen', array ['C3', 'C4', 'C5 Aircross', 'Berlingo']
                      union all
                      select 'SEAT', array ['Ibiza', 'Leon', 'Arona', 'Ateca', 'Tarraco']
                      union all
                      select 'Dacia', array ['Sandero', 'Logan', 'Duster', 'Jogger', 'Spring']
                      union all
                      select 'Alfa Romeo', array ['Giulia', 'Stelvio', 'Tonale', 'Giulietta']
                      union all
                      select 'Jaguar', array ['XE', 'XF', 'F-Pace', 'E-Pace', 'I-Pace']),
     expanded_models as (
         select b_name, unnest(m_names) as m_name
         from brand_models)
insert
into Model (name, engine_capacity_cc, body_style, brand_id, model_fuel_type_catalog_id, model_transmission_catalog_id)
select e.m_name,
       case
           when ft.type = 'Electric' then null
           else (array [998, 1198, 1398, 1498, 1598, 1968, 1998, 2498, 2998])[floor(random() * 9 + 1)]
           end as engine_capacity_cc,

       case
           when e.m_name ilike '%X%' or e.m_name ilike '%Q%' or e.m_name ilike '%Cross%' or e.m_name ilike '%Tiguan%' or
                e.m_name ilike '%Pace%' then 'SUV'
           when e.m_name in ('Corolla', 'Passat', 'Mondeo', 'Accord', 'A4', '3 Series', 'Octavia', 'Superb', 'Giulia')
               then 'Sedan'
           when e.m_name in ('Golf', 'Polo', 'Fiesta', 'Clio', 'A3', '208', 'Sandero', 'Leon') then 'Hatchback'
           else (array ['Sedan', 'Hatchback', 'SUV', 'Station Wagon', 'Coupe', 'Minivan'])[floor(random() * 6 + 1)]
           end as body_style,

       b.id    as brand_id,
       ft.id   as model_fuel_type_catalog_id,
       tr.id   as model_transmission_catalog_id
from expanded_models e
         join Brand b on b.name = e.b_name
         cross join lateral (
    select id, type
    from ModelFuelTypeCatalog
    where (e.b_name = 'Tesla' and type = 'Electric')
       or (e.b_name != 'Tesla')
    order by random()
    limit 1
    ) ft
         cross join lateral (
    select id
    from ModelTransmissionCatalog
    where (ft.type = 'Electric' and transmission = 'Automatic')
       or (ft.type != 'Electric')
    order by random()
    limit 1
    ) tr;

with model_pool as (select id, body_style, row_number() over () as rn
                    from Model),
     max_model as (select count(*) as max_val
                   from model_pool)
insert
into Vehicle (VIN, passenger_capacity, year, wheelchair_accessible, model_id, category_id)
select upper(substring(md5(random()::text || s.i::text) from 1 for 17)) as VIN,
       floor(random() * 4 + 1)::int                                     as passenger_capacity,
       floor(random() * (2026 - 2010 + 1) + 2010)::int                  as year,
       case
           when mp.body_style in ('Minivan', 'SUV') then random() < 0.15
           when mp.body_style = 'Station Wagon' then random() < 0.05
           else random() < 0.01
           end                                                          as wheelchair_accessible,

       mp.id                                                            as model_id,
       (select id from DriverLicenseCategory where category_name = 'B') as category_id

from generate_series(1, 18000) as s(i)
         join max_model mm on true
         join model_pool mp on mp.rn = floor(random() * mm.max_val + 1)
on conflict (VIN) do nothing;

with vehicle_pool as (select VIN, row_number() over () as rn
                      from Vehicle),
     company_pool as (select id, row_number() over () as rn
                      from Company),
     freelancer_pool as (select driver_user_id, row_number() over () as rn
                         from FreelanceDriver),
     counts as (select (select count(*) from Company)         as c_count,
                       (select count(*) from FreelanceDriver) as f_count),
     area_codes as (select id,
                           case
                               when name in
                                    ('Centar', 'Aerodrom', 'Karposh', 'Chair', 'Kisela Voda', 'Gazi Baba', 'Butel',
                                     'Gjorche Petrov', 'Saraj', 'Shuto Orizari', 'Arachinovo', 'Chucher-Sandevo',
                                     'Ilinden', 'Petrovec', 'Sopishte', 'Studenichani', 'Zelenikovo') then 'SK'
                               when name = 'Bitola' then 'BT'
                               when name = 'Tetovo' then 'TE'
                               when name = 'Gostivar' then 'GV'
                               when name = 'Kumanovo' then 'KU'
                               when name = 'Ohrid' then 'OH'
                               when name = 'Struga' then 'SU'
                               when name = 'Prilep' then 'PP'
                               when name = 'Veles' then 'VE'
                               when name = 'Strumica' then 'SR'
                               when name = 'Shtip' then 'ST'
                               when name = 'Kochani' then 'KO'
                               when name = 'Kichevo' then 'KI'
                               when name = 'Kavadarci' then 'KA'
                               when name = 'Gevgelija' then 'GE'
                               when name = 'Radovish' then 'RA'
                               when name = 'Kriva Palanka' then 'KP'
                               when name = 'Debar' then 'DB'
                               when name = 'Resen' then 'RE'
                               when name = 'Sveti Nikole' then 'SN'
                               when name = 'Negotino' then 'NE'
                               when name = 'Delchevo' then 'DE'
                               when name = 'Vinica' then 'VI'
                               when name = 'Probistip' then 'PS'
                               when name = 'Berovo' then 'BE'
                               when name = 'Kratovo' then 'KR'
                               when name = 'Makedonski Brod' then 'MB'
                               when name = 'Valandovo' then 'VA'
                               when name = 'Krushevo' then 'KS'
                               when name = 'Demir Hisar' then 'DH'
                               when name = 'Pehchevo' then 'PE'
                               when name = 'Vevchani' then 'VV'
                               else 'SK'
                               end as prefix
                    from Area),
     company_primary_area as (select ca.company_id, max(ac.prefix) as prefix
                              from Company_Area ca
                                       join area_codes ac on ca.area_id = ac.id
                              group by ca.company_id),
     base_company as (select cp.id                                   as company_id,
                             null::int                               as freelance_driver_user_id,
                             vp.VIN,
                             '2023-01-01'::date + (vp.rn % 365)::int as from_date,
                             coalesce(cpa.prefix, 'SK')              as plate_prefix,
                             null::date                              as to_date
                      from company_pool cp
                               join vehicle_pool vp on vp.rn = cp.rn
                               left join company_primary_area cpa on cpa.company_id = cp.id),
     base_freelancer as (select null::int                                                                       as company_id,
                                fp.driver_user_id                                                               as freelance_driver_user_id,
                                vp.VIN,
                                '2023-01-01'::date + (vp.rn % 365)::int                                         as from_date,
                                (array ['SK', 'SK', 'BT', 'TE', 'KU', 'OH', 'SR', 'ST', 'PP'])[(vp.rn % 9) + 1] as plate_prefix,
                                null::date                                                                      as to_date
                         from freelancer_pool fp
                                  join counts c on true
                                  join vehicle_pool vp on vp.rn = fp.rn + c.c_count),
     base_extra as (select cp.id                                   as company_id,
                           null::int                               as freelance_driver_user_id,
                           vp.VIN,
                           '2023-01-01'::date + (vp.rn % 365)::int as from_date,
                           COALESCE(cpa.prefix, 'SK')              as plate_prefix,
                           null::date                              as to_date
                    from vehicle_pool vp
                             join counts c on true
                             join company_pool cp on cp.rn = (vp.rn % c.c_count) + 1
                             left join company_primary_area cpa on cpa.company_id = cp.id
                    where vp.rn > (c.c_count + c.f_count)),
     current_ownerships as (select *, row_number() over () as unique_seq
                            from (select *
                                  from base_company
                                  union all
                                  select *
                                  from base_freelancer
                                  union all
                                  select *
                                  from base_extra) t),
     past_ownership_1 as (select cp.id                                             as company_id,
                                 null::int                                         as freelance_driver_user_id,
                                 co.VIN,
                                 co.from_date - (700 + (co.unique_seq % 500))::int as from_date,
                                 co.from_date - (1 + (co.unique_seq % 30))::int    as to_date,
                                 COALESCE(cpa.prefix, 'SK')                        as plate_prefix,
                                 co.unique_seq
                          from current_ownerships co
                                   join counts c on true
                                   join company_pool cp on cp.rn = ((co.unique_seq + 15) % c.c_count) + 1
                                   left join company_primary_area cpa on cpa.company_id = cp.id
                          where (co.unique_seq % 100) < 75
     ),
     past_ownership_2 as (select cp.id                                             as company_id,
                                 null::int                                         as freelance_driver_user_id,
                                 p1.VIN,
                                 p1.from_date - (700 + (p1.unique_seq % 500))::int as from_date,
                                 p1.from_date - (1 + (p1.unique_seq % 30))::int    as to_date,
                                 coalesce(cpa.prefix, 'SK')                        as plate_prefix,
                                 p1.unique_seq
                          from past_ownership_1 p1
                                   join counts c on true
                                   join company_pool cp on cp.rn = ((p1.unique_seq + 88) % c.c_count) + 1
                                   left join company_primary_area cpa on cpa.company_id = cp.id
                          where (p1.unique_seq % 100) < 35
     ),
     all_ownerships as (select company_id, freelance_driver_user_id, VIN, from_date, to_date, plate_prefix
                        from current_ownerships
                        union all
                        select company_id, freelance_driver_user_id, VIN, from_date, to_date, plate_prefix
                        from past_ownership_1
                        union all
                        select company_id, freelance_driver_user_id, VIN, from_date, to_date, plate_prefix
                        from past_ownership_2)

insert
into VehicleOwnership (vehicle_VIN, company_id, freelance_driver_user_id, from_date, to_date, license_plate)
select VIN,
       company_id,
       freelance_driver_user_id,
       from_date,
       to_date,
       plate_prefix || '-' || floor(random() * 9000 + 1000)::text || '-' ||
       chr((floor(random() * 26) + 65)::int) ||
       chr((floor(random() * 26) + 65)::int)
from all_ownerships;


with company_pool as (select id, row_number() over () as rn
                      from Company),
     c_count as (select count(*) as val
                 from company_pool),
     nf_pool as (select d.user_id, row_number() over () as rn
                 from Driver d
                          left join FreelanceDriver fd on d.user_id = fd.driver_user_id
                 where fd.driver_user_id is null),
     f_pool as (select driver_user_id as user_id, row_number() over () as rn
                from FreelanceDriver),
     dispatcher_pool as (select user_id as user_id, row_number() over () as rn
                         from dispatcher),
     nf_current as (select np.user_id                              as driver_user_id,
                           '2022-01-01'::date + (np.rn % 700)::int as start_date,
                           null::date                              as end_date,
                           cp.id                                   as company_id
                    from nf_pool np
                             cross join c_count c
                             join company_pool cp on cp.rn = (np.rn % c.val) + 1
                    where (np.rn % 100) < 90),
     nf_past_1 as (select np.user_id                              as driver_user_id,
                          '2019-01-01'::date + (np.rn % 500)::int as start_date,
                          '2021-12-01'::date - (np.rn % 300)::int as end_date,
                          cp.id                                   as company_id
                   from nf_pool np
                            cross join c_count c
                            join company_pool cp on cp.rn = ((np.rn + 17) % c.val) + 1
                   where (np.rn % 100) < 30),
     nf_past_2 as (select np.user_id                              as driver_user_id,
                          '2015-01-01'::date + (np.rn % 600)::int as start_date,
                          '2018-12-01'::date - (np.rn % 300)::int as end_date,
                          cp.id                                   as company_id
                   from nf_pool np
                            cross join c_count c
                            join company_pool cp on cp.rn = ((np.rn + 17 + (np.rn % 2)) % c.val) + 1
                   where (np.rn % 100) < 15),
     f_past as (select fp.user_id                               as driver_user_id,
                       '2018-01-01'::date + (fp.rn % 1000)::int as start_date,
                       '2022-12-31'::date - (fp.rn % 500)::int  as end_date,
                       cp.id                                    as company_id
                from f_pool fp
                         cross join c_count c
                         join company_pool cp on cp.rn = (fp.rn % c.val) + 1
                where (fp.rn % 100) < 40),
     dispatcher_current as (select dp.user_id                              as driver_user_id,
                                   '2022-01-01'::date + (dp.rn % 700)::int as start_date,
                                   null::date                              as end_date,
                                   cp.id                                   as company_id
                            from dispatcher_pool dp
                                     cross join c_count c
                                     join company_pool cp on cp.rn = (dp.rn % c.val) + 1
                            where (dp.rn % 100) < 90),
     dispatcher_past as (select dp.user_id                              as driver_user_id,
                                '2019-01-01'::date + (dp.rn % 700)::int as start_date,
                                '2021-12-31'::date - (dp.rn % 500)::int as end_date,
                                cp.id                                   as compnay_id
                         from dispatcher_pool as dp
                                  cross join c_count c
                                  join company_pool cp on cp.rn = (dp.rn & c.val) + 1
                         where (dp.rn % 100) < 30),
     all_employment as (select *
                        from nf_current
                        union all
                        select *
                        from nf_past_1
                        union all
                        select *
                        from nf_past_2
                        union all
                        select *
                        from f_past
                        union all
                        select *
                        from dispatcher_current
                        union all
                        select *
                        from dispatcher_past)

insert
into EmploymentHistory (employee_user_id, start_date, end_date, company_id)
select driver_user_id, start_date, end_date, company_id
from all_employment
on conflict do nothing;


with vehicle_age as (select VIN,
                            extract(year from current_date) - year as age
                     from Vehicle),
     vehicle_service_counts as (select VIN,
                                       case
                                           when age >= 10 then floor(random() * 6 + 7)::int
                                           when age >= 5 then floor(random() * 4 + 4)::int
                                           when age >= 2 then floor(random() * 3 + 1)::int
                                           else floor(random() * 2)::int
                                           end as num_services
                                from vehicle_age),
     expanded_services as (
         select vsc.VIN, s.i as service_index
         from vehicle_service_counts vsc
                  cross join lateral generate_series(1, vsc.num_services) as s(i)
         where vsc.num_services > 0),
     service_records as (select es.VIN,
                                o.service_date,
                                floor(random() * 23500 + 1500)::numeric(19, 2) as service_price,
                                floor(random() * 7 + 1)::int                   as issue_type
                         from expanded_services es
                                  cross join lateral (
                             select vo.from_date +
                                    (random() * (coalesce(vo.to_date, current_date) - vo.from_date))::int as service_date
                             from VehicleOwnership vo
                             where vo.vehicle_VIN = es.VIN
                             order by random()
                             limit 1
                             ) o)
insert
into ServiceHistory (date, price, currency_catalog_id, fault_description, fix_description, vehicle_VIN)
select service_date,
       service_price,
       (select id from CurrencyCatalog where currency = 'MKD'),

       (array [
           'Routine maintenance interval reached.',
           'Squeaking noise when applying brakes.',
           'Check Engine light illuminated on dashboard.',
           'Air conditioning system blowing warm air.',
           'Vehicle pulls to the right during driving.',
           'Engine oil low and dirty.',
           'Battery failing to hold charge.'
           ])[issue_type],

       (array [
           'Performed comprehensive multipoint inspection, replaced oil and filters.',
           'Replaced front and rear brake pads, resurfaced rotors.',
           'Scanned OBD-II, replaced faulty O2 sensor and cleared codes.',
           'Evacuated and recharged A/C refrigerant, added UV dye to check for leaks.',
           'Performed 4-wheel alignment and adjusted camber/toe angles.',
           'Drained engine oil, replaced oil filter, added full synthetic oil.',
           'Installed new 12V battery and cleaned terminal corrosion.'
           ])[issue_type],

       VIN
from service_records;

with ownership_spans as (select vehicle_VIN,
                                from_date,
                                coalesce(to_date, current_date) as end_date
                         from VehicleOwnership),
     registration_series as (select os.vehicle_VIN,
                                    os.from_date                                             as ownership_from_date,
                                    (os.from_date + (s.i || ' years')::interval)::date       as registration_date,
                                    (os.from_date + ((s.i + 1) || ' years')::interval)::date as expiration_date
                             from ownership_spans os
                                      cross join lateral generate_series(0,
                                                                         floor((os.end_date - os.from_date) / 365.25)::int) as s(i))
insert
into Registration (vehicle_VIN, registration_date, expiration_date, vehicle_ownership_from_date)
select vehicle_VIN,
       registration_date,
       expiration_date,
       ownership_from_date
from registration_series
where registration_date < current_date;

with company_pool as (select id, row_number() over () as rn
                      from Company),
     freelancer_pool as (select driver_user_id, row_number() over () as rn
                         from FreelanceDriver),
     counts as (select (select count(*) from Company)         as c_count,
                       (select count(*) from FreelanceDriver) as f_count),
     customer_pool as (
         select user_id, row_number() over () as rn
         from Customer),
     expanded_preferences as (select cp.user_id                                 as customer_user_id,
                                     s.i                                        as seqno,
                                     cp.rn                                      as customer_rn,
                                     case when random() < 0.7 then 1 else 2 end as pref_type
                              from customer_pool cp
                                       cross join lateral generate_series(1, floor(random() * 5 + 1 + (cp.rn * 0))::int) as s(i))
insert
into CustomerPreference (seqno, customer_user_id, freelance_driver_user_id, company_id)
select ep.seqno,
       ep.customer_user_id,
       fp.driver_user_id as freelance_driver_user_id,
       cmp.id            as company_id
from expanded_preferences ep
         cross join counts c
         left join company_pool cmp
                   on ep.pref_type = 1 and cmp.rn = ((ep.customer_rn + ep.seqno * 13) % c.c_count) + 1
         left join freelancer_pool fp
                   on ep.pref_type = 2 and fp.rn = ((ep.customer_rn + ep.seqno * 17) % c.f_count) + 1
on conflict (customer_user_id, seqno) do nothing;


create table macedonia_atlas
(
    name     varchar(100),
    lat      double precision,
    lon      double precision,
    category varchar(20)
);

insert into macedonia_atlas (name, lat, lon, category)
values
('Monastery of St. George the Victorious, Rajchica', 41.5019, 20.5361, 'tourist'),
('Church of St. Panteleimon, Gorno Nerezi', 41.9768, 21.3751, 'tourist'),
('Osogovo Monastery, Kriva Palanka', 42.2081, 22.3619, 'tourist'),
('Lesnovo Monastery', 42.0119, 22.2272, 'tourist'),
('Treskavec Monastery, Prilep', 41.3991, 21.5342, 'tourist'),
('Zrze Monastery', 41.5211, 21.3422, 'tourist'),
('Arabati Baba Tekke, Tetovo', 42.0044, 20.9631, 'tourist'),
('Painted Mosque (Sarena Dzamija), Tetovo', 42.0061, 20.9669, 'tourist'),
('Holy Mother of God (Perivleptos), Ohrid', 41.1147, 20.7956, 'tourist'),
('St. Clement and Panteleimon (Plaoshnik)', 41.1122, 20.7911, 'tourist'),

('Stobi Ancient City', 41.5519, 21.9744, 'tourist'),
('Bargala Ancient Site', 41.7911, 22.2844, 'tourist'),
('Scupi Archaeological Site', 42.0167, 21.3944, 'tourist'),
('Isar Fortress, Shtip', 41.7375, 22.1869, 'tourist'),
('Vinica Fortress (Kale)', 41.8822, 22.5036, 'tourist'),
('Tauresium (Birthplace of Justinian)', 41.8986, 21.6111, 'tourist'),
('Stuberra Ancient City', 41.2464, 21.3853, 'tourist'),

('Duf Waterfall, Rostusha', 41.6119, 20.5983, 'tourist'),
('Koleshino Waterfall', 41.3803, 22.8106, 'tourist'),
('Vevchani Springs', 41.2406, 20.5928, 'tourist'),
('Cave Vrelo, Matka', 41.9386, 21.2981, 'tourist'),
('Cave Peshna, Makedonski Brod', 41.5517, 21.1894, 'tourist'),
('Markovi Kuli (Marko''s Towers)', 41.3622, 21.5394, 'tourist'),
('Demir Kapija Gorge', 41.4036, 22.2536, 'tourist'),
('Pelister Eyes (Glacial Lakes)', 41.0022, 21.2011, 'tourist'),
('Berovo Lake', 41.6833, 22.9167, 'tourist'),
('Doiran Lake Waterfront', 41.1853, 22.6917, 'tourist'),
('Kozjak Lake Viewpoint', 41.8797, 21.1844, 'tourist'),
('Tikvesh Lake', 41.3361, 21.9542, 'tourist'),
('Shar Planina - Popova Shapka', 42.0142, 20.8842, 'tourist'),

('Memorial House of Mother Teresa', 41.9939, 21.4308, 'tourist'),
('Makedonium (Ilinden Memorial)', 41.3711, 21.2486, 'tourist'),
('Millennium Cross Summit', 41.9647, 21.3944, 'tourist'),
('Clock Tower, Bitola', 41.0308, 21.3353, 'tourist'),
('Clock Tower, Prilep', 41.3444, 21.5542, 'tourist'),
('Museum on Water (Bay of Bones)', 40.9942, 20.7994, 'tourist'),
('Tumba Madzari Neolithic Village', 41.9961, 21.5019, 'tourist'),
('Aqueduct Skopje', 42.0231, 21.4183, 'tourist'),

('Galichnik Village', 41.5936, 20.6517, 'tourist'),
('Janche Village', 41.5833, 20.6167, 'tourist'),
('Lazaropole', 41.5367, 20.7019, 'tourist'),
('Malovishte Village', 41.0322, 21.1306, 'tourist'),
('Kratovo Stone Bridges', 42.0792, 22.1753, 'tourist'),
('Braichino Village', 40.9167, 21.1500, 'tourist'),
('Zrze Viewpoint', 41.5222, 21.3411, 'tourist'),
('Smolare Eco-path', 41.3653, 22.9053, 'tourist'),
('Belchishko Blato (Wetland)', 41.2917, 20.9167, 'tourist'),
('St. Bogorodica Eleusa, Veljusa', 41.4772, 22.5661, 'tourist'),
('St. Leontius, Vodocha', 41.4489, 22.5881, 'tourist');


select count(*)
from request;


insert into request (customer_user_id, start_latitude, start_longitude, end_latitude, end_longitude,
                     timestamp, number_of_adult_passengers, number_of_children, female_driver,
                     luggage, luggage_count, baby_seat_count)
with generated_users as (select user_id
                         from customer
                                  cross join generate_series(1, ceil(10000000.0 / (select count(*) from customer))::int)
                         order by random()),
     users as (select user_id, row_number() over () as rn
               from generated_users
               limit 10000000),

     user_areas as (select cp.customer_user_id,
                           array_agg(coalesce(ca.area_id, fd.area_id))                  as area_array,
                           array_length(array_agg(coalesce(ca.area_id, fd.area_id)), 1) as arr_len
                    from customerpreference cp
                             left join company_area ca on ca.company_id = cp.company_id
                             left join freelancedriver fd on cp.freelance_driver_user_id = fd.driver_user_id
                    group by cp.customer_user_id),

     generated_atlas as (select lat, lon
                         from macedonia_atlas
                                  cross join generate_series(1,
                                                             ceil(10000000.0 / (select count(*) from macedonia_atlas))::int)
                         order by random()),
     atlas_pool as (select lat, lon, row_number() over () as rn
                    from generated_atlas
                    limit 10000000),

     luggage_pool as (select case
                                 when (random() * 3 + 1)::int % 4 = 0 then true
                                 else false
                                 end              as luggage,
                             row_number() over () as rn
                      from generate_series(1, 10000000)
                      order by random())

select u.user_id,

       a.latitude + (a.radius * sqrt(random()) / 111000.0) * cos(random() * 2 * pi()) as start_latitude,
       a.longitude + (a.radius * sqrt(random()) / 83000.0) * sin(random() * 2 * pi()) as start_longitude,

       e_loc.lat                                                                      as end_latitude,
       e_loc.lon                                                                      as end_longitude,
       (now() - interval '20 days' * random())                                        as timestamp,
       (random() * 3 + 1)::int                                                        as number_of_adult_passengers,
       (round(random()))::int                                                         as number_of_children,
       (random() < 0.15)                                                              as female_driver,
       lp.luggage,
       case
           when lp.luggage = true then (random() * 2 + 1)
           else 0
           end                                                                        as luggage_count,
       (round(random()))::int                                                         as baby_seat_count
from generate_series(1, 10000000) as s(i)
         join users u on u.rn = s.i

         join user_areas ua on ua.customer_user_id = u.user_id

         cross join lateral (
    select ua.area_array[floor(random() * ua.arr_len + 1 + (s.i * 0))::int] as selected_area_id
    ) rand_pref
         join area a on a.id = rand_pref.selected_area_id

         join atlas_pool e_loc on e_loc.rn = s.i
         join luggage_pool lp on lp.rn = s.i;


with days as (select generate_series(
                             (current_date - interval '30 days')::date,
                             current_date::date,
                             '1 day'::interval
                     )::date as day_date),
     active_employment as (select eh.employee_user_id,
                                  eh.company_id,
                                  d.day_date,
                                  row_number()
                                  over (partition by eh.company_id, d.day_date order by eh.employee_user_id) -
                                  1 as driver_rn
                           from days d
                                    join EmploymentHistory eh
                                         on eh.start_date <= d.day_date and
                                            (eh.end_date is null or eh.end_date >= d.day_date)
                                    join driver on driver.user_id = eh.employee_user_id),
     active_company_cars as (select vo.vehicle_VIN,
                                    vo.company_id,
                                    d.day_date,
                                    row_number() over (partition by vo.company_id, d.day_date order by vo.vehicle_VIN) -
                                    1                                                      as car_rn,
                                    count(*) over (partition by vo.company_id, d.day_date) as total_cars
                             from days d
                                      join VehicleOwnership vo
                                           on vo.company_id is not null and vo.from_date <= d.day_date and
                                              (vo.to_date is null or vo.to_date >= d.day_date)),
     company_assignments as (select ae.day_date,
                                    ae.employee_user_id             as id_driver,
                                    acc.vehicle_VIN                 as vin_vehicle,
                                    (ae.driver_rn / acc.total_cars) as shift_id
                             from active_employment ae
                                      join active_company_cars acc
                                           on ae.company_id = acc.company_id and ae.day_date = acc.day_date
                             where acc.car_rn = (ae.driver_rn % acc.total_cars)
                               and (ae.driver_rn / acc.total_cars) < 3),
     active_freelancers as (select fd.driver_user_id, d.day_date
                            from days d
                                     join FreelanceDriver fd on true
                                     left join EmploymentHistory eh
                                               on fd.driver_user_id = eh.employee_user_id and
                                                  eh.start_date <= d.day_date and
                                                  (eh.end_date is null or eh.end_date >= d.day_date)
                            where eh.company_id is null),
     freelancer_cars as (select vo.vehicle_VIN,
                                vo.freelance_driver_user_id,
                                d.day_date,
                                row_number()
                                over (partition by vo.freelance_driver_user_id, d.day_date order by vo.vehicle_VIN) as rn
                         from days d
                                  join VehicleOwnership vo
                                       on vo.freelance_driver_user_id is not null and vo.from_date <= d.day_date and
                                          (vo.to_date is null or vo.to_date >= d.day_date)),
     freelancer_assignments as (select af.day_date,
                                       af.driver_user_id as id_driver,
                                       fc.vehicle_VIN    as vin_vehicle,
                                       0                 as shift_id
                                from active_freelancers af
                                         join freelancer_cars fc on af.driver_user_id = fc.freelance_driver_user_id and
                                                                    af.day_date = fc.day_date
                                where fc.rn = 1
     ),
     all_assignments as (select *
                         from company_assignments
                         union all
                         select *
                         from freelancer_assignments)
insert
into driver_vehicle (vin_vehicle, id_driver, time_from, time_to)
select vin_vehicle,
       id_driver,
       day_date + (6 + shift_id * 8) * interval '1 hour' as time_from,

       case
           when (day_date + (14 + shift_id * 8) * interval '1 hour') > current_timestamp then null
           else (day_date + (14 + shift_id * 8) * interval '1 hour')
           end                                           as time_to
from all_assignments
where (day_date + (6 + shift_id * 8) * interval '1 hour') <= current_timestamp;

with randomized_pricing_info as (select id, row_number() over () as rn
                                 from generate_series(1, 1450)
                                          cross join pricinginfo
                                 limit 10000),
     freelancers_with_rn as (select driver_user_id, row_number() over () as rn
                             from freelancedriver
                             order by random())
update freelancedriver
set pricing_info_id = (select randomized_pricing_info.id
                       from freelancers_with_rn
                                join randomized_pricing_info on freelancers_with_rn.rn = randomized_pricing_info.rn
                       where freelancers_with_rn.driver_user_id = freelancedriver.driver_user_id);

with randomized_area as (select id, row_number() over () as rn
                         from generate_series(1, 109)
                                  cross join area
                         limit 10000),
     freelancers_with_rn as (select driver_user_id, row_number() over () as rn
                             from freelancedriver
                             order by random())
update freelancedriver
set area_id = (select randomized_area.id
               from freelancers_with_rn
                        join randomized_area on freelancers_with_rn.rn = randomized_area.rn
               where freelancers_with_rn.driver_user_id = freelancedriver.driver_user_id);


create index idx_temp_driver_car_times on driver_vehicle (id_driver, time_from, time_to);
create index idx_customerpreference on customerpreference (customer_user_id);
create index idx_employmenthistory on employmenthistory (employee_user_id, company_id);

insert into offer(created_at, request_id, dispatcher_user_id, driver_user_id, price, currency_catalog_id, eta,
                  customer_user_id)
with CompanyDispatchers as (select eh.company_id,
                                   array_agg(d.user_id)                  as disp_array,
                                   array_length(array_agg(d.user_id), 1) as arr_len
                            from employmenthistory eh
                                     join dispatcher d on d.user_id = eh.employee_user_id
                            where eh.end_date is null
                            group by eh.company_id),

     CompanyPricing as (select company_id,
                               array_agg(pricing_info_id)                  as price_array,
                               array_length(array_agg(pricing_info_id), 1) as arr_len
                        from company_area
                        group by company_id),

     CompanyDrivers as (select eh.company_id,
                               array_agg(eh.employee_user_id)                  as driver_array,
                               array_length(array_agg(eh.employee_user_id), 1) as arr_len
                        from employmenthistory eh
                                 join driver d on d.user_id = eh.employee_user_id
                        where eh.end_date is null
                        group by eh.company_id)

select (request.timestamp + interval '2 minute' * random())                        as created_at,
       request.id                                                                  as request_id,
       cd.disp_array[floor(random() * cd.arr_len + 1)::int]                        as dispatcher_user_id,

       cdr.driver_array[floor(random() * cdr.arr_len + 1 + (request.id * 0))::int] as driver_user_id,

       greatest(
               case
                   when lower(trim(pricinginfo.unit::text)) = 'kilometer' then
                       (sqrt(pow(request.start_latitude - request.end_latitude, 2) +
                             pow(request.start_longitude - request.end_longitude, 2)) * 111.0) * pricinginfo.value
                   when lower(trim(pricinginfo.unit::text)) = 'minute' then
                       (sqrt(pow(request.start_latitude - request.end_latitude, 2) +
                             pow(request.start_longitude - request.end_longitude, 2)) * 111.0 / 50.0 * 60.0) *
                       pricinginfo.value
                   else 5.00
                   end,
               1.50)::numeric(19, 2)                                               as price,

       pricinginfo.currency_id                                                     as currency_catalog_id,
       (request.timestamp + interval '15 minute' * random())                       as eta,
       request.customer_user_id                                                    as customer_user_id

from request
         join customerpreference on customerpreference.customer_user_id = request.customer_user_id
    and customerpreference.freelance_driver_user_id is null
         join CompanyPricing cpr on cpr.company_id = customerpreference.company_id
         cross join lateral (
    select cpr.price_array[floor(random() * cpr.arr_len + 1 + (request.id * 0))::int] as pricing_info_id
    ) rand_price
         join pricinginfo on pricinginfo.id = rand_price.pricing_info_id
         join CompanyDispatchers cd on cd.company_id = customerpreference.company_id
         join CompanyDrivers cdr on cdr.company_id = customerpreference.company_id

union all

select (request.timestamp + interval '2 minute' * random())  as created_at,
       request.id                                            as request_id,
       null                                                  as dispatcher_user_id,
       freelancedriver.driver_user_id                        as driver_user_id,
       greatest(
               case
                   when lower(trim(pricinginfo.unit::text)) = 'kilometer' then
                       (sqrt(pow(request.start_latitude - request.end_latitude, 2) +
                             pow(request.start_longitude - request.end_longitude, 2)) * 111.0) * pricinginfo.value
                   when lower(trim(pricinginfo.unit::text)) = 'minute' then
                       (sqrt(pow(request.start_latitude - request.end_latitude, 2) +
                             pow(request.start_longitude - request.end_longitude, 2)) * 111.0 / 50.0 * 60.0) *
                       pricinginfo.value
                   else 5.00
                   end,
               1.50)::numeric(19, 2)                         as price,
       pricinginfo.currency_id                               as currency_catalog_id,
       (request.timestamp + interval '15 minute' * random()) as eta,
       request.customer_user_id                              as customer_user_id
from request
         join customerpreference on customerpreference.customer_user_id = request.customer_user_id
    and customerpreference.freelance_driver_user_id is not null
         join freelancedriver on freelancedriver.driver_user_id = customerpreference.freelance_driver_user_id
         join pricinginfo on pricinginfo.id = freelancedriver.pricing_info_id;



insert into ride(start_time, end_time, distance_traveled, vehicle_vin, driver_user_id, request_id, status, offer_id)
with UniqueOffers as (
    select id, created_at, driver_user_id, request_id
    from (select id,
                 created_at,
                 driver_user_id,
                 request_id,
                 row_number() over (partition by request_id order by created_at desc) as rn
          from offer) tmp
    where rn = 1),
     ActiveVehicles as (
         select distinct on (id_driver) id_driver, vin_vehicle
         from driver_vehicle
         order by id_driver, time_from desc),
     StagedMath as (
         select o.driver_user_id,
                o.id                                               as offer_id,
                r.id                                               as request_id,
                av.vin_vehicle,
                (sqrt(pow(r.start_latitude - r.end_latitude, 2) + pow(r.start_longitude - r.end_longitude, 2)) *
                 111.0)                                            as dist_km,
                (o.created_at + (interval '15 minute' * random())) as fixed_start_time,
                random()                                           as rand_end_delay,
                random()                                           as rand_dist_variance
         from UniqueOffers o
                  join request r on r.id = o.request_id
                  join ActiveVehicles av on av.id_driver = o.driver_user_id)
select fixed_start_time                        as start_time,

       fixed_start_time + ((dist_km / 50.0 * 60.0) * interval '1 minute') +
       (interval '10 minute' * rand_end_delay) as end_time,

       dist_km + (20.0 * rand_dist_variance)   as distance_traveled,
       vin_vehicle,
       driver_user_id,
       request_id,
       'in_progress'                           as status,
       offer_id
from StagedMath;

update offer
set status = 'rejected';

update offer
set status = 'accepted'
from ride
where offer.id = ride.offer_id;

insert into ChatMessage (message, timestamp, user_id_from, ride_id)
with ride_participants as (
    select r.id as ride_id,
           req.customer_user_id,
           r.driver_user_id,
           r.start_time
    from Ride r
             join Request req on r.request_id = req.id),
     ride_participants_randomized as (select *
                                      from ride_participants
                                      order by random())
select msg_data.msg,
       rp.start_time + (msg_data.offset_mins * interval '1 minute'),
       case when msg_data.sender = 'driver' then rp.driver_user_id else rp.customer_user_id end,
       rp.ride_id
from ride_participants_randomized rp
         cross join lateral (
    values
        ('I have arrived at the pickup location. I am in a silver sedan.', 'driver', -2),
        ('Great, I see you! Coming out now.', 'customer', -1),

        ('Are you at the main entrance or the side gate?', 'driver', -5),
        ('Main entrance, right next to the coffee shop.', 'customer', -4),
        ('Got it, see you in a minute.', 'driver', -3),

        ('Thank you for the smooth ride!', 'customer', 20),
        ('You are welcome! Have a great day.', 'driver', 22)
    ) as msg_data(msg, sender, offset_mins)
where random() < 0.10;


insert into Review (rating, comment, ride_id, customer_user_id)
select rating,
       case
           when rating >= 4 then
               (array [
                   'Great ride, driver was very polite and professional.',
                   'Everything went smoothly, very satisfied.',
                   'Clean car and safe driving. Would definitely ride again.',
                   'Driver arrived on time and the trip was comfortable.',
                   'Excellent experience, highly recommended.'
                   ])[floor(random() * 5 + 1)]

           when rating >= 2 then
               (array [
                   'Ride was okay, but could be improved.',
                   'Driver was decent, but communication was lacking.',
                   'Average experience, nothing special.',
                   'Car was fine but arrived a bit late.',
                   'It was fine overall, just expected a bit more.'
                   ])[floor(random() * 5 + 1)]

           else
               (array [
                   'Driver was late and unprofessional.',
                   'Car was not clean and ride felt unsafe.',
                   'Very bad experience, would not recommend.',
                   'Driver canceled last minute and caused issues.',
                   'Uncomfortable ride and poor communication.'
                   ])[floor(random() * 5 + 1)]
           end,
       r.id,
       o.customer_user_id
from Ride r
         inner join Offer o on o.id = r.offer_id
         cross join lateral (
    select round((random() * 5)::numeric, 2) as rating
    )
where random() > 0.2;

insert into Report (ride_id,
                    customer_user_id,
                    message,
                    title,
                    created_at,
                    latitude,
                    longitude,
                    reason)
select r.id,
       req.customer_user_id,

       (array [
           'The driver was very rude and made the trip uncomfortable.',
           'I felt unsafe due to aggressive driving.',
           'Driver did not follow the agreed route.',
           'The vehicle condition was unacceptable.',
           'Driver was late and did not communicate properly.'
           ])[floor(random() * 5 + 1)],

       (array [
           'Unsafe driving',
           'Driver behavior issue',
           'Route problem',
           'Vehicle condition',
           'Late arrival'
           ])[floor(random() * 5 + 1)],

       current_timestamp - (random() * interval '7 days'),

       req.start_latitude + (random() - 0.5) * 0.01,
       req.start_longitude + (random() - 0.5) * 0.01,

       (array [
           'Reckless driving',
           'Rude behavior',
           'Vehicle hygiene',
           'Wrong route taken',
           'Driver was late'
           ])[floor(random() * 5 + 1)]

from Ride r
         inner join request req on r.request_id = req.id
where random() > 0.75;

update ride
set status = 'completed'
where random() < 0.9985;


insert into request (customer_user_id, start_latitude, start_longitude, end_latitude, end_longitude,
                     timestamp, number_of_adult_passengers, number_of_children, female_driver,
                     luggage, luggage_count, baby_seat_count)
with generated_users as (select user_id
                         from customer
                                  cross join generate_series(1, ceil(500000.0 / (select count(*) from customer))::int)
                         order by random()),
     users as (select user_id, row_number() over () as rn
               from generated_users
               limit 500000),

     user_areas as (select cp.customer_user_id,
                           array_agg(coalesce(ca.area_id, fd.area_id))                  as area_array,
                           array_length(array_agg(coalesce(ca.area_id, fd.area_id)), 1) as arr_len
                    from customerpreference cp
                             left join company_area ca on ca.company_id = cp.company_id
                             left join freelancedriver fd on cp.freelance_driver_user_id = fd.driver_user_id
                    group by cp.customer_user_id),

     generated_atlas as (select lat, lon
                         from macedonia_atlas
                                  cross join generate_series(1,
                                                             ceil(500000.0 / (select count(*) from macedonia_atlas))::int)
                         order by random()),
     atlas_pool as (select lat, lon, row_number() over () as rn
                    from generated_atlas
                    limit 500000),

     luggage_pool as (select case
                                 when (random() * 3 + 1)::int % 4 = 0 then true
                                 else false
                                 end              as luggage,
                             row_number() over () as rn
                      from generate_series(1, 500000)
                      order by random())

select u.user_id,

       a.latitude + (a.radius * sqrt(random()) / 111000.0) * cos(random() * 2 * pi()) as start_latitude,
       a.longitude + (a.radius * sqrt(random()) / 83000.0) * sin(random() * 2 * pi()) as start_longitude,

       e_loc.lat                                                                      as end_latitude,
       e_loc.lon                                                                      as end_longitude,
       (now() - interval '20 days' * random())                                        as timestamp,
       (random() * 3 + 1)::int                                                        as number_of_adult_passengers,
       (round(random()))::int                                                         as number_of_children,
       (random() < 0.15)                                                              as female_driver,
       lp.luggage,
       case
           when lp.luggage = true then (random() * 2 + 1)
           else 0
           end                                                                        as luggage_count,
       (round(random()))::int                                                         as baby_seat_count
from generate_series(1, 500000) as s(i)
         join users u on u.rn = s.i

         join user_areas ua on ua.customer_user_id = u.user_id

         cross join lateral (
    select ua.area_array[floor(random() * ua.arr_len + 1 + (s.i * 0))::int] as selected_area_id
    ) rand_pref
         join area a on a.id = rand_pref.selected_area_id

         join atlas_pool e_loc on e_loc.rn = s.i
         join luggage_pool lp on lp.rn = s.i;


insert into offer(created_at, request_id, dispatcher_user_id, driver_user_id, price, currency_catalog_id, eta,
                  customer_user_id)
with CompanyDispatchers as (select eh.company_id,
                                   array_agg(d.user_id)                  as disp_array,
                                   array_length(array_agg(d.user_id), 1) as arr_len
                            from employmenthistory eh
                                     join dispatcher d on d.user_id = eh.employee_user_id
                            where eh.end_date is null
                            group by eh.company_id),

     CompanyPricing as (select company_id,
                               array_agg(pricing_info_id)                  as price_array,
                               array_length(array_agg(pricing_info_id), 1) as arr_len
                        from company_area
                        group by company_id),

     CompanyDrivers as (select eh.company_id,
                               array_agg(eh.employee_user_id)                  as driver_array,
                               array_length(array_agg(eh.employee_user_id), 1) as arr_len
                        from employmenthistory eh
                                 join driver d on d.user_id = eh.employee_user_id
                        where eh.end_date is null
                        group by eh.company_id)

select (request.timestamp + interval '2 minute' * random())                        as created_at,
       request.id                                                                  as request_id,
       cd.disp_array[floor(random() * cd.arr_len + 1)::int]                        as dispatcher_user_id,

       cdr.driver_array[floor(random() * cdr.arr_len + 1 + (request.id * 0))::int] as driver_user_id,

       greatest(
               case
                   when lower(trim(pricinginfo.unit::text)) = 'kilometer' then
                       (sqrt(pow(request.start_latitude - request.end_latitude, 2) +
                             pow(request.start_longitude - request.end_longitude, 2)) * 111.0) * pricinginfo.value
                   when lower(trim(pricinginfo.unit::text)) = 'minute' then
                       (sqrt(pow(request.start_latitude - request.end_latitude, 2) +
                             pow(request.start_longitude - request.end_longitude, 2)) * 111.0 / 50.0 * 60.0) *
                       pricinginfo.value
                   else 5.00
                   end,
               1.50)::numeric(19, 2)                                               as price,

       pricinginfo.currency_id                                                     as currency_catalog_id,
       (request.timestamp + interval '15 minute' * random())                       as eta,
       request.customer_user_id                                                    as customer_user_id

from (select * from request order by id desc limit 300000) request
         join customerpreference on customerpreference.customer_user_id = request.customer_user_id
    and customerpreference.freelance_driver_user_id is null
         join CompanyPricing cpr on cpr.company_id = customerpreference.company_id
         cross join lateral (
    select cpr.price_array[floor(random() * cpr.arr_len + 1 + (request.id * 0))::int] as pricing_info_id
    ) rand_price
         join pricinginfo on pricinginfo.id = rand_price.pricing_info_id
         join CompanyDispatchers cd on cd.company_id = customerpreference.company_id
         join CompanyDrivers cdr on cdr.company_id = customerpreference.company_id

union all

select (request.timestamp + interval '2 minute' * random())  as created_at,
       request.id                                            as request_id,
       null                                                  as dispatcher_user_id,
       freelancedriver.driver_user_id                        as driver_user_id,
       greatest(
               case
                   when lower(trim(pricinginfo.unit::text)) = 'kilometer' then
                       (sqrt(pow(request.start_latitude - request.end_latitude, 2) +
                             pow(request.start_longitude - request.end_longitude, 2)) * 111.0) * pricinginfo.value
                   when lower(trim(pricinginfo.unit::text)) = 'minute' then
                       (sqrt(pow(request.start_latitude - request.end_latitude, 2) +
                             pow(request.start_longitude - request.end_longitude, 2)) * 111.0 / 50.0 * 60.0) *
                       pricinginfo.value
                   else 5.00
                   end,
               1.50)::numeric(19, 2)                         as price,
       pricinginfo.currency_id                               as currency_catalog_id,
       (request.timestamp + interval '15 minute' * random()) as eta,
       request.customer_user_id                              as customer_user_id
from (select * from request order by id desc limit 300000) request
         join customerpreference on customerpreference.customer_user_id = request.customer_user_id
    and customerpreference.freelance_driver_user_id is not null
         join freelancedriver on freelancedriver.driver_user_id = customerpreference.freelance_driver_user_id
         join pricinginfo on pricinginfo.id = freelancedriver.pricing_info_id;


insert into waypoints(latitude, longitude, seqno, request_id)
select waypoint.latitude  as latitude,
       waypoint.longitude as longitude,
       waypoint.number    as seqno,
       request.id         as request_id
from request
         cross join lateral (
    select request.start_latitude + random(0, 0.2)  as latitude,
           request.start_longitude + random(0, 0.2) as longitude,
           s.i                                      as number
    from generate_series(1, (random() * 3 + 1)::int) as s(i)
    ) waypoint
where random() < 0.3;


insert into location(latitude, longitude, timestamp, ride_id)
select waypoint.latitude  as latitude,
       waypoint.longitude as longitude,
       waypoint.timestamp as timestamp,
       ride.id            as ride_id
from ride
         join request on request.id = ride.request_id
         cross join lateral (
    select request.start_latitude + random(0, 0.2)                    as latitude,
           request.start_longitude + random(0, 0.2)                   as longitude,
           ride.start_time + s.i * random(1, 3) * interval '1 minute' as timestamp
    from generate_series(1, (random() * 2 + 1)::int) as s(i)
    ) waypoint;



insert into payment(completed_ride_id, total_amount, currency_catalog_id)
select ride.id     as completed_ride_id,
       offer.price as total_amount,
       4           as currency_catalog_id
from ride
         join offer on offer.id = ride.offer_id
where ride.status = 'completed';


insert into customerpayment(payment_id, customer_user_id, amount, currency_catalog_id, payment_method, transaction_id)
select payment.id                     as payment_id,
       request.customer_user_id       as customer_user_id,
       payment.total_amount           as amount,
       4                              as currency_catalog_id,
       case
           when random() < 0.5 then 'CASH'::payment_method_type
           else 'CARD'::payment_method_type
           end                        as payment_method,
       gen_random_uuid()::varchar(64) as transaction_id
from payment
         join ride on ride.id = payment.completed_ride_id
         join request on request.id = ride.request_id;

with LastRides as (
    select distinct on (r.driver_user_id) r.driver_user_id,
                                          req.end_latitude,
                                          req.end_longitude
    from ride r
             join request req on req.id = r.request_id
    where r.status in ('completed', 'in_progress')
    order by r.driver_user_id, r.start_time desc),
     TargetLocations as (
         select d.user_id,
                coalesce(lr.end_latitude, 40.9 + (random() * 1.3))  as target_lat,
                coalesce(lr.end_longitude, 20.5 + (random() * 2.5)) as target_lon
         from driver d
                  left join LastRides lr on lr.driver_user_id = d.user_id)
update driver d
set latitude  = tl.target_lat,
    longitude = tl.target_lon
from TargetLocations tl
where d.user_id = tl.user_id;