-- ── MANUFACTURERS (20) ───────────────────────────────────────
INSERT INTO manufacturers (name, country) VALUES
('Toyota',         'Japan'),
('Volkswagen',     'Germany'),
('BMW',            'Germany'),
('Mercedes-Benz',  'Germany'),
('Audi',           'Germany'),
('Ford',           'USA'),
('Opel',           'Germany'),
('Renault',        'France'),
('Peugeot',        'France'),
('Citroën',        'France'),
('Fiat',           'Italy'),
('Alfa Romeo',     'Italy'),
('Škoda',          'Czech Republic'),
('SEAT',           'Spain'),
('Hyundai',        'South Korea'),
('Kia',            'South Korea'),
('Nissan',         'Japan'),
('Honda',          'Japan'),
('Mazda',          'Japan'),
('Volvo',          'Sweden');

-- ── CITIES (20 — North Macedonia, real coordinates) ──────────
INSERT INTO cities (name, country, lat, lng) VALUES
('Skopje',     'North Macedonia', 41.9973, 21.4280),
('Bitola',     'North Macedonia', 41.0297, 21.3340),
('Kumanovo',   'North Macedonia', 42.1322, 21.7144),
('Prilep',     'North Macedonia', 41.3453, 21.5547),
('Tetovo',     'North Macedonia', 42.0094, 20.9716),
('Ohrid',      'North Macedonia', 41.1231, 20.8020),
('Veles',      'North Macedonia', 41.7153, 21.7745),
('Štip',       'North Macedonia', 41.7452, 22.1952),
('Gostivar',   'North Macedonia', 41.7992, 20.9086),
('Strumica',   'North Macedonia', 41.4378, 22.6429),
('Kavadarci',  'North Macedonia', 41.4328, 22.0115),
('Debar',      'North Macedonia', 41.5233, 20.5244),
('Radoviš',    'North Macedonia', 41.6381, 22.4674),
('Gevgelija',  'North Macedonia', 41.1428, 22.5036),
('Kočani',     'North Macedonia', 41.9167, 22.4167),
('Negotino',   'North Macedonia', 41.4839, 22.0906),
('Delčevo',    'North Macedonia', 41.9678, 22.7728),
('Vinica',     'North Macedonia', 41.8797, 22.5097),
('Kičevo',     'North Macedonia', 41.5147, 20.9575),
('Struga',     'North Macedonia', 41.1775, 20.6778);

-- ── TOLL POINTS (15 — A1 / A2 / A3 corridors) ────────────────
INSERT INTO toll_points (name, lat, lng, road_name, price_car, price_van) VALUES
('Tabanovce',       42.1050, 21.7300, 'A1', 40.00, 70.00),
('Petrovec',        41.9400, 21.6200, 'A1', 40.00, 70.00),
('Veles North',     41.7800, 21.7500, 'A1', 40.00, 70.00),
('Negotino',        41.5000, 22.0700, 'A1', 40.00, 70.00),
('Demir Kapija',    41.4100, 22.2400, 'A1', 40.00, 70.00),
('Udovo',           41.2500, 22.3800, 'A1', 40.00, 70.00),
('Bogorodica',      41.1700, 22.4900, 'A1', 40.00, 70.00),
('Blace',           42.0200, 21.2900, 'A2', 30.00, 55.00),
('Brodec',          41.9700, 21.1500, 'A2', 30.00, 55.00),
('Kičevo',          41.5200, 20.9600, 'A2', 30.00, 55.00),
('Struga',          41.1800, 20.6800, 'A2', 30.00, 55.00),
('Miladinovci',     41.9800, 21.5800, 'A3', 25.00, 45.00),
('Sveti Nikole',    41.8700, 21.9400, 'A3', 25.00, 45.00),
('Štip',            41.7400, 22.1900, 'A3', 25.00, 45.00),
('Vinica',          41.8800, 22.5100, 'A3', 25.00, 45.00);


-- ============================================================
-- PHASE 2 — USER DOMAIN
-- users (50K), passengers (35K), drivers (15K),
-- user_addresses (80K)
-- ============================================================

ALTER TABLE users          DISABLE TRIGGER ALL;
ALTER TABLE passengers     DISABLE TRIGGER ALL;
ALTER TABLE drivers        DISABLE TRIGGER ALL;
ALTER TABLE user_addresses DISABLE TRIGGER ALL;

-- ── USERS (50,000) ──────────────────────────────────────────
INSERT INTO users (name, email, phone, is_verified, created_at, deleted_at)
WITH
first_names(n) AS (VALUES (ARRAY[
  'Aleksandar','Stefan','Nikola','Ivan','Bojan','Darko','Goran','Petar','Zoran','Filip',
  'Tome','Andrej','Martin','Kire','Dragan','Risto','Marko','Viktor','Dimitar','Gorjan',
  'Angel','Blagoj','Damjan','Emil','Georgi','Igor','Jovan','Lazar','Mile','Naum',
  'Oliver','Panche','Radoslav','Saše','Toni','Vasko','Zlatan','Boris','Dejan','Elvin',
  'Gligor','Hristo','Ilija','Jasmin','Kosta','Ljubčo','Metodij','Orce','Pavle','Robert',
  'Simon','Trajče','Velko','Zdravko','Dušan','Hristijan','Ivo','Krste','Luka','Mite',
  'Predrag','Radi','Slave','Trifun','Agon','Blerim','Emir','Fatmir','Gjorgi','Hasan',
  'Jakub','Bajram','Abdula','Cvetan','Dragi','Niko','Aco','Brane','Done','Erik',
  'Fidan','Gane','Hari','Ace','Janche','Kole','Lazo','Miro','Nase','Ogi',
  'Pero','Rade','Sale','Tase','Vane','Artan','Besnik','Drin','Enis','Florent',
  'Marija','Ana','Elena','Sara','Irena','Maja','Sonja','Kristina','Vesna','Jana',
  'Lena','Dina','Biljana','Snezana','Angela','Bojana','Danijela','Emilija','Frosina','Gordana',
  'Hristina','Ivana','Jasna','Katerina','Lidija','Milica','Neda','Olivera','Petra','Radmila',
  'Sanja','Tatjana','Valentina','Zaneta','Adela','Besa','Dragana','Eleonora','Fatima','Gorica',
  'Hana','Iskra','Jovana','Kalina','Ljupka','Meri','Nataša','Olga','Rozeta','Sofija',
  'Tamara','Vera','Zorica','Aneta','Cveta','Dušanka','Evgenija','Grozdana','Ilinka','Jelena',
  'Kosana','Lepa','Mencija','Nada','Violeta','Teuta','Silvija','Renata','Rada','Nadežda',
  'Metka','Lila','Keti','Jasmina','Isidora','Hamide','Gena','Florina','Ema','Dafina',
  'Branka','Amira','Alma','Aferdita','Zora','Mirjana','Sandra','Karolina','Nina','Tijana',
  'Vlatka','Anda','Ceca','Deja','Roza','Tanja','Urška','Vikica','Žana','Mila'
])),
last_names(n) AS (VALUES (ARRAY[
  'Petrov','Nikolov','Stojanoski','Jovanov','Iliev','Ristov','Gjorgjiev','Dimov','Janev','Kocev',
  'Trajanov','Blazevski','Cvetkov','Andonov','Micev','Todorov','Stefanov','Georgiev','Atanasov','Velkov',
  'Apostolов','Bojadziev','Cvetanovski','Dojcinov','Efremov','Filipov','Gruevski','Hristov','Ivanovski','Jovanovski',
  'Kitanovski','Lazarov','Manchev','Naumov','Pavlovski','Risteski','Sazdov','Tasev','Ugrinov','Vasilevski',
  'Zdravev','Arsov','Babunski','Damjanovski','Elenov','Fazliovski','Gligorov','Hadžiev','Isakov','Jakimovski',
  'Kostovski','Lazareski','Mihajlov','Nedelkovski','Petreski','Radeski','Spasov','Tanev','Uzunov','Veličkovski',
  'Zafirov','Acevski','Brankov','Doncev','Ferizi','Gorgioski','Halili','Ibrahimović','Jasarević','Krasniqi',
  'Limani','Mustafov','Nuredinov','Osmani','Popov','Rašidov','Selimi','Trpeski','Veljanoski','Zejneli',
  'Apostoloski','Belev','Crvenkovski','Dukoski','Ejupov','Frchkovski','Grcev','Hristoski','Ilievski','Jovanoski',
  'Kunovski','Lozanovski','Mirčevski','Nacevski','Ognenovski','Pendovski','Rizov','Stavrev','Trajanoski','Yordanov',
  'Zlatanoski','Andonovski','Canevski','Daskalovski','Efimov','Filiposki','Galevski','Hajdarovski','Ivanoski','Jakimov',
  'Kitanov','Lazaroski','Naumoski','Petrevski','Ristevski','Simov','Tasevski','Velickov','Zdraveski','Acoski',
  'Bundalevski','Čemerski','Dončev','Fejzulai','Gjorgjieski','Hadžieva','Ismaili','Jančevski','Korubin','Lazarević',
  'Mitev','Nikoloski','Ognjanov','Penov','Radev','Sazdoski','Teodosievski','Urosevski','Vanevski','Zografski',
  'Adamovski','Belčev','Cuckovski','Damevski','Etemi','Fildišev','Gordanoski','Hajrizi','Idrizi','Jankulovski',
  'Kolevski','Lirovski','Milosavljeski','Necev','Omerov','Pavlov','Stojanov','Trajkoski','Vitanov','Andreevski',
  'Bogevski','Dimitrovski','Ferati','Gligoroski','Hajredini','Ibrahimi','Jakovleski','Koleski','Markovska','Nikolovski',
  'Orovčanski','Popovski','Ristovski','Stanojeski','Taleski','Ugrinoska','Veselinoski','Zdravkoski','Aleksoski','Bogdanovski',
  'Čočkoski','Dabeski','Eftimoski','Fidanoski','Gavrovski','Hristomanov','Ilčevski','Josifoski','Kanevski','Lovreski',
  'Micevski','Nečev','Ovaneski','Pesevski','Runtevski','Spasevski','Trajčevski','Stojkovski','Todoroski','Veljanoski2'
]))
SELECT
    (SELECT n FROM first_names)[ ((i-1) % 200) + 1 ]
        || ' ' ||
    (SELECT n FROM last_names) [ ((i-1)/200 + ((i-1) % 200) * 11) % 200 + 1 ]  AS name,
    'user' || i || '@drivenet.mk'                                                AS email,
    '+3892' || LPAD(i::text, 7, '0')                                             AS phone,
    (hashint8(i::bigint *  3) & 2147483647) % 10 < 7                             AS is_verified,
    '2022-01-01'::timestamp
        + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 1096)   AS created_at,
    CASE WHEN (hashint8(i::bigint * 13) & 2147483647) % 20 = 0
         THEN '2022-01-01'::timestamp
              + make_interval(days =>  (hashint8(i::bigint *  7) & 2147483647) % 1096)
              + make_interval(days => 1 + (hashint8(i::bigint * 17) & 2147483647) % 365)
         ELSE NULL
    END                                                                          AS deleted_at
FROM generate_series(1, 50000) gs(i);

-- ── PASSENGERS (users 1–35,000) ─────────────────────────────
INSERT INTO passengers (user_id, smoking, pets, max_detour_percent, is_active, created_at)
SELECT
    i,
    (hashint8(i::bigint *  5) & 2147483647) % 5  = 0                            AS smoking,
    (hashint8(i::bigint * 11) & 2147483647) % 7  = 0                            AS pets,
    CASE WHEN (hashint8(i::bigint * 17) & 2147483647) % 10 < 4
         THEN ROUND((5 + (hashint8(i::bigint * 23) & 2147483647) % 46)::numeric, 2)
         ELSE NULL
    END                                                                          AS max_detour_percent,
    (hashint8(i::bigint * 29) & 2147483647) % 20 > 0                            AS is_active,
    '2022-01-01'::timestamp
        + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 1096)  AS created_at
FROM generate_series(1, 35000) gs(i);

-- ── DRIVERS (users 35,001–50,000) ───────────────────────────
INSERT INTO drivers
    (user_id, license_number, license_class, license_expiry,
     status, verified_at, is_active, created_at)
WITH base AS (
    SELECT
        i,
        35000 + i                                                                AS user_id,
        'MK-LIC-' || LPAD(i::text, 6, '0')                                     AS license_number,
        (ARRAY['B','B','B','B+E','C','C+E','D'])
            [(hashint8(i::bigint * 3) & 2147483647) % 7 + 1]                   AS license_class,
        '2025-01-01'::date
            + make_interval(days => (hashint8(i::bigint * 5) & 2147483647) % 2190)
                                                                                 AS license_expiry,
        CASE
            WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 75 THEN 'active'
            WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 87 THEN 'pending'
            WHEN (hashint8(i::bigint * 7) & 2147483647) % 100 < 97 THEN 'suspended'
            ELSE                                                                 'banned'
        END                                                                      AS status,
        '2022-01-01'::timestamp
            + make_interval(days => (hashint8(i::bigint * 11) & 2147483647) % 1096)
                                                                                AS created_at,
        (hashint8(i::bigint * 13) & 2147483647) % 10                            AS rnd
    FROM generate_series(1, 15000) gs(i)
)
SELECT
    user_id, license_number, license_class, license_expiry, status,
    CASE WHEN status <> 'pending'
         THEN created_at + make_interval(days => (hashint8(i::bigint * 17) & 2147483647) % 180 + 1)
         ELSE NULL
    END AS verified_at,
    rnd > 0 AS is_active,
    created_at
FROM base;

-- ── USER_ADDRESSES (80,000) ──────────────────────────────────
INSERT INTO user_addresses (user_id, label, address, lat, lng)
SELECT
    i, 'Home',
    'ul. ' ||
    (ARRAY['Partizanska','Makedonija','Ilindenska','Vasil Gjorgov','Dame Gruev',
           'Borka Taleski','Orce Nikolov','Jane Sandanski','Kliment Ohridski'])[(i % 9) + 1]
    || ' br.' || (1 + (hashint8(i::bigint * 3) & 2147483647) % 99)::text,
    40.85 + ((hashint8(i::bigint *  5) & 2147483647) % 15200) / 10000.0,
    20.45 + ((hashint8(i::bigint *  7) & 2147483647) % 25800) / 10000.0
FROM generate_series(1, 50000) gs(i)
UNION ALL
SELECT
    i, 'Work',
    'ul. ' ||
    (ARRAY['Industriska','Tehnička','Komercijalna','Stara Čaršija','Centar','Novo Maalo'])[(i % 6) + 1]
    || ' br.' || (1 + (hashint8(i::bigint * 11) & 2147483647) % 99)::text,
    40.85 + ((hashint8(i::bigint * 13) & 2147483647) % 15200) / 10000.0,
    20.45 + ((hashint8(i::bigint * 17) & 2147483647) % 25800) / 10000.0
FROM generate_series(1, 30000) gs(i);

ALTER TABLE users          ENABLE TRIGGER ALL;
ALTER TABLE passengers     ENABLE TRIGGER ALL;
ALTER TABLE drivers        ENABLE TRIGGER ALL;
ALTER TABLE user_addresses ENABLE TRIGGER ALL;

ANALYZE users; ANALYZE passengers; ANALYZE drivers; ANALYZE user_addresses;


-- ============================================================
-- PHASE 3 — VEHICLES + ROUTES DOMAIN
-- vehicle_models (120), locations (160), vehicles (18K),
-- vehicle_ownership (18K), routes (400), route_stops (1 600)
-- ============================================================

ALTER TABLE vehicle_models    DISABLE TRIGGER ALL;
ALTER TABLE vehicles          DISABLE TRIGGER ALL;
ALTER TABLE vehicle_ownership DISABLE TRIGGER ALL;
ALTER TABLE locations         DISABLE TRIGGER ALL;
ALTER TABLE routes            DISABLE TRIGGER ALL;
ALTER TABLE route_stops       DISABLE TRIGGER ALL;

-- ── VEHICLE MODELS (120 = 20 manufacturers × 6 models) ──────
INSERT INTO vehicle_models (manufacturer_id, model_name, body_type, fuel_type) VALUES
(1,'Corolla','sedan','petrol'),(1,'Yaris','hatchback','petrol'),
(1,'RAV4','suv','hybrid'),(1,'Land Cruiser','suv','diesel'),
(1,'Camry','sedan','hybrid'),(1,'Avensis','sedan','diesel'),
(2,'Golf','hatchback','petrol'),(2,'Passat','sedan','diesel'),
(2,'Tiguan','suv','petrol'),(2,'Polo','hatchback','petrol'),
(2,'Transporter','van','diesel'),(2,'Caddy','van','diesel'),
(3,'3 Series','sedan','petrol'),(3,'5 Series','sedan','diesel'),
(3,'X5','suv','diesel'),(3,'1 Series','hatchback','petrol'),
(3,'X3','suv','petrol'),(3,'M3','coupe','petrol'),
(4,'C-Class','sedan','petrol'),(4,'E-Class','sedan','diesel'),
(4,'GLC','suv','diesel'),(4,'A-Class','hatchback','petrol'),
(4,'Sprinter','van','diesel'),(4,'Vito','van','diesel'),
(5,'A3','sedan','petrol'),(5,'A4','sedan','diesel'),
(5,'Q5','suv','diesel'),(5,'A1','hatchback','petrol'),
(5,'Q3','suv','petrol'),(5,'A6','sedan','diesel'),
(6,'Focus','hatchback','petrol'),(6,'Mondeo','sedan','diesel'),
(6,'Kuga','suv','petrol'),(6,'Fiesta','hatchback','petrol'),
(6,'Transit','van','diesel'),(6,'Galaxy','minivan','diesel'),
(7,'Astra','hatchback','petrol'),(7,'Vectra','sedan','diesel'),
(7,'Mokka','suv','petrol'),(7,'Corsa','hatchback','petrol'),
(7,'Vivaro','van','diesel'),(7,'Zafira','minivan','petrol'),
(8,'Clio','hatchback','petrol'),(8,'Megane','sedan','petrol'),
(8,'Kadjar','suv','diesel'),(8,'Twingo','hatchback','petrol'),
(8,'Trafic','van','diesel'),(8,'Scenic','minivan','diesel'),
(9,'208','hatchback','petrol'),(9,'308','hatchback','diesel'),
(9,'3008','suv','diesel'),(9,'107','hatchback','petrol'),
(9,'Partner','van','diesel'),(9,'5008','minivan','diesel'),
(10,'C3','hatchback','petrol'),(10,'C4','sedan','petrol'),
(10,'C5 Aircross','suv','diesel'),(10,'C1','hatchback','petrol'),
(10,'Berlingo','van','diesel'),(10,'C4 Picasso','minivan','petrol'),
(11,'Punto','hatchback','petrol'),(11,'500','hatchback','petrol'),
(11,'Tipo','sedan','diesel'),(11,'Bravo','hatchback','diesel'),
(11,'Ducato','van','diesel'),(11,'Doblo','minivan','diesel'),
(12,'Giulia','sedan','petrol'),(12,'Stelvio','suv','diesel'),
(12,'Giulietta','hatchback','petrol'),(12,'156','sedan','petrol'),
(12,'147','hatchback','diesel'),(12,'Tonale','suv','hybrid'),
(13,'Octavia','sedan','diesel'),(13,'Fabia','hatchback','petrol'),
(13,'Superb','sedan','diesel'),(13,'Karoq','suv','petrol'),
(13,'Kodiaq','suv','diesel'),(13,'Rapid','sedan','petrol'),
(14,'Ibiza','hatchback','petrol'),(14,'Leon','hatchback','petrol'),
(14,'Ateca','suv','diesel'),(14,'Arona','suv','petrol'),
(14,'Tarraco','suv','diesel'),(14,'Toledo','sedan','petrol'),
(15,'i20','hatchback','petrol'),(15,'i30','hatchback','diesel'),
(15,'Tucson','suv','petrol'),(15,'i10','hatchback','petrol'),
(15,'Santa Fe','suv','diesel'),(15,'Ioniq','hatchback','electric'),
(16,'Rio','hatchback','petrol'),(16,'Ceed','hatchback','diesel'),
(16,'Sportage','suv','petrol'),(16,'Picanto','hatchback','petrol'),
(16,'Sorento','suv','diesel'),(16,'Niro','suv','hybrid'),
(17,'Micra','hatchback','petrol'),(17,'Juke','suv','petrol'),
(17,'Qashqai','suv','diesel'),(17,'Note','hatchback','petrol'),
(17,'X-Trail','suv','diesel'),(17,'Leaf','hatchback','electric'),
(18,'Jazz','hatchback','petrol'),(18,'Civic','sedan','petrol'),
(18,'CR-V','suv','hybrid'),(18,'HR-V','suv','petrol'),
(18,'e','hatchback','electric'),(18,'Accord','sedan','petrol'),
(19,'Mazda2','hatchback','petrol'),(19,'Mazda3','sedan','petrol'),
(19,'CX-5','suv','diesel'),(19,'Mazda6','sedan','diesel'),
(19,'CX-3','suv','petrol'),(19,'MX-5','coupe','petrol'),
(20,'V40','hatchback','diesel'),(20,'S60','sedan','petrol'),
(20,'XC40','suv','diesel'),(20,'XC60','suv','petrol'),
(20,'V60','estate','diesel'),(20,'S90','sedan','hybrid');

-- ── LOCATIONS (160 = 20 cities × 8 types) ───────────────────
INSERT INTO locations (city_id, name, address, lat, lng)
SELECT
    c.id,
    (ARRAY['Bus Station','Train Station','City Center','Hospital',
           'University','Airport','Shopping Mall','Market Square'])[p.pos]
    || ' ' || c.name,
    NULL,
    ROUND((c.lat + (p.pos - 4.5) * 0.018)::numeric, 7),
    ROUND((c.lng + (p.pos - 4.5) * 0.022)::numeric, 7)
FROM cities c
CROSS JOIN generate_series(1, 8) p(pos)
ORDER BY c.id, p.pos;

-- ── VEHICLES (18,000) ────────────────────────────────────────
INSERT INTO vehicles (model_id, year, seats, license_plate, vehicle_type, color, is_active)
SELECT
    m.id,
    1995 + (hashint8(i::bigint * 5) & 2147483647) % 30,
    CASE
        WHEN m.body_type IN ('van','minivan') THEN 5 + (hashint8(i::bigint * 7) & 2147483647) % 3
        ELSE                                       4 + (hashint8(i::bigint * 7) & 2147483647) % 2
    END,
    (ARRAY['SK','BT','KU','PP','TT','OH','VE','ST','GS','SR',
           'KV','DC','RA','GV','KO','NE','DE','VI'])
        [((i-1) % 18) + 1]
    || LPAD(((i-1) / 18 + 1)::text, 4, '0')
    || 'MK',
    CASE WHEN m.body_type IN ('van','minivan') THEN 'van' ELSE 'car' END,
    (ARRAY['Black','White','Silver','Gray','Blue','Red',
           'Green','Brown','Yellow','Orange'])
        [(hashint8(i::bigint * 11) & 2147483647) % 10 + 1],
    (hashint8(i::bigint * 13) & 2147483647) % 20 > 0
FROM generate_series(1, 18000) gs(i)
JOIN vehicle_models m ON m.id = ((i-1) % 120) + 1;

-- ── VEHICLE OWNERSHIP (18,000) ───────────────────────────────
INSERT INTO vehicle_ownership (driver_id, vehicle_id, owned_from, owned_to, is_active)
SELECT
    ((i-1) % 15000) + 1,
    i,
    '2019-01-01'::date
        + make_interval(days => (hashint8(i::bigint * 7) & 2147483647) % 2028),
    NULL,
    TRUE
FROM generate_series(1, 18000) gs(i);

-- ── ROUTES (400) ─────────────────────────────────────────────
INSERT INTO routes (origin_id, destination_id, distance_km, estimated_duration)
WITH city_pairs AS (
    SELECT
        c1.id AS c1_id, c2.id AS c2_id,
        (c1.id-1)*8+1 AS orig_loc,
        (c2.id-1)*8+1 AS dest_loc,
        GREATEST(5.00,
            ROUND(SQRT(POWER((c2.lat-c1.lat)*111, 2) +
                       POWER((c2.lng-c1.lng)*83,  2))::numeric, 2)
        ) AS dist_km
    FROM cities c1
    CROSS JOIN cities c2
    WHERE c1.id < c2.id
)
SELECT orig_loc, dest_loc, dist_km, GREATEST(10, ROUND(dist_km * 60.0 / 80)::int) FROM city_pairs
UNION ALL
SELECT dest_loc, orig_loc, dist_km, GREATEST(10, ROUND(dist_km * 60.0 / 80)::int) FROM city_pairs
UNION ALL
SELECT (c.id-1)*8+1, (c.id-1)*8+3, 4.00, 12 FROM cities c;

-- ── ROUTE STOPS (1,600) ──────────────────────────────────────
INSERT INTO route_stops (route_id, location_id, stop_order, estimated_time)
SELECT
    r.id,
    CASE s.ord
        WHEN 0 THEN r.origin_id
        WHEN 1 THEN ((r.origin_id      + r.id * 3) % 160) + 1
        WHEN 2 THEN ((r.destination_id + r.id * 5) % 160) + 1
        WHEN 3 THEN r.destination_id
    END,
    s.ord,
    CASE s.ord
        WHEN 0 THEN '00:00:00'::time
        WHEN 1 THEN '00:25:00'::time
        WHEN 2 THEN '00:50:00'::time
        WHEN 3 THEN '01:15:00'::time
    END
FROM routes r
CROSS JOIN generate_series(0, 3) s(ord);

ALTER TABLE vehicle_models    ENABLE TRIGGER ALL;
ALTER TABLE vehicles          ENABLE TRIGGER ALL;
ALTER TABLE vehicle_ownership ENABLE TRIGGER ALL;
ALTER TABLE locations         ENABLE TRIGGER ALL;
ALTER TABLE routes            ENABLE TRIGGER ALL;
ALTER TABLE route_stops       ENABLE TRIGGER ALL;

ANALYZE vehicle_models; ANALYZE vehicles; ANALYZE vehicle_ownership;
ANALYZE locations; ANALYZE routes; ANALYZE route_stops;


-- ============================================================
-- PHASE 4 — RIDES DOMAIN
-- rides (5M), route_segments (15M), ride_status_history (~13.6M)
-- ============================================================

ALTER TABLE rides               DISABLE TRIGGER ALL;
ALTER TABLE route_segments      DISABLE TRIGGER ALL;
ALTER TABLE ride_status_history DISABLE TRIGGER ALL;

CREATE TEMP TABLE _route_seg_tmpl AS
SELECT
    rs1.route_id,
    rs1.stop_order                                                              AS segment_order,
    rs1.id                                                                      AS from_stop_id,
    rs2.id                                                                      AS to_stop_id,
    GREATEST(1.00, ROUND((r.distance_km / 3.0)::numeric, 2))                   AS seg_dist
FROM route_stops rs1
JOIN route_stops rs2
    ON  rs2.route_id   = rs1.route_id
    AND rs2.stop_order = rs1.stop_order + 1
JOIN routes r ON r.id = rs1.route_id;

CREATE INDEX idx_rst_route_id ON _route_seg_tmpl(route_id);

-- rides — 10 × 500K batches
INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(1,500000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(500001,1000000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(1000001,1500000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(1500001,2000000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(2000001,2500000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(2500001,3000000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(3000001,3500000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(3500001,4000000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(4000001,4500000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

INSERT INTO rides (driver_id, vehicle_id, route_id, departure_time, status, price_per_km, seats_available, is_recurring, created_at)
WITH gen AS (SELECT i,'2022-01-01'::timestamp + make_interval(days=>(hashint8(i::bigint*7)&2147483647)%1461) + make_interval(hours=>(hashint8(i::bigint*11)&2147483647)%24) AS dep FROM generate_series(4500001,5000000) gs(i))
SELECT ((i-1)%15000)+1,((i-1)%18000)+1,((i-1)%400)+1,dep,CASE WHEN (hashint8(i::bigint*17)&2147483647)%100<5 THEN 'scheduled' WHEN (hashint8(i::bigint*17)&2147483647)%100<10 THEN 'in_progress' WHEN (hashint8(i::bigint*17)&2147483647)%100<35 THEN 'cancelled' ELSE 'completed' END,ROUND((1.50+((hashint8(i::bigint*19)&2147483647)%651)/100.0)::numeric,2),(hashint8(i::bigint*23)&2147483647)%5,FALSE,dep-make_interval(days=>1+(hashint8(i::bigint*13)&2147483647)%7) FROM gen;

ANALYZE rides;

-- route_segments — 10 × 1.5M batches
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 1 AND 500000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 500001 AND 1000000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 1000001 AND 1500000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 1500001 AND 2000000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 2000001 AND 2500000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 2500001 AND 3000000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 3000001 AND 3500000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 3500001 AND 4000000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 4000001 AND 4500000;
INSERT INTO route_segments (ride_id, from_stop_id, to_stop_id, distance_km, segment_order) SELECT r.id,t.from_stop_id,t.to_stop_id,t.seg_dist,t.segment_order FROM rides r JOIN _route_seg_tmpl t ON t.route_id=r.route_id WHERE r.id BETWEEN 4500001 AND 5000000;

ANALYZE route_segments;

-- ride_status_history — 3 passes
INSERT INTO ride_status_history (ride_id, status, changed_at) SELECT id,'scheduled',created_at FROM rides;
INSERT INTO ride_status_history (ride_id, status, changed_at) SELECT id,'in_progress',created_at+make_interval(mins=>10+(hashint8(id::bigint*3)&2147483647)%51) FROM rides WHERE status IN ('completed','in_progress') OR (status='cancelled' AND (hashint8(id::bigint*29)&2147483647)%2=0);
INSERT INTO ride_status_history (ride_id, status, changed_at) SELECT id,status,CASE WHEN status='completed' THEN departure_time+make_interval(mins=>60+(hashint8(id::bigint*7)&2147483647)%121) ELSE created_at+make_interval(mins=>5+(hashint8(id::bigint*7)&2147483647)%26) END FROM rides WHERE status IN ('completed','cancelled');

ALTER TABLE rides               ENABLE TRIGGER ALL;
ALTER TABLE route_segments      ENABLE TRIGGER ALL;
ALTER TABLE ride_status_history ENABLE TRIGGER ALL;

ANALYZE rides; ANALYZE route_segments; ANALYZE ride_status_history;


-- ============================================================
-- PHASE 5 — BOOKINGS DOMAIN
-- bookings (12M), booking_status_history (~15M),
-- passenger_segments (36M), booking_final_fare (12M)
-- ============================================================

ALTER TABLE bookings               DISABLE TRIGGER ALL;
ALTER TABLE booking_status_history DISABLE TRIGGER ALL;
ALTER TABLE passenger_segments     DISABLE TRIGGER ALL;
ALTER TABLE booking_final_fare     DISABLE TRIGGER ALL;

CREATE TEMP TABLE _route_stops_map AS
SELECT route_id,
    MAX(CASE WHEN stop_order=0 THEN id END) AS s0,
    MAX(CASE WHEN stop_order=1 THEN id END) AS s1,
    MAX(CASE WHEN stop_order=2 THEN id END) AS s2,
    MAX(CASE WHEN stop_order=3 THEN id END) AS s3
FROM route_stops GROUP BY route_id;
CREATE INDEX idx_rsm_route ON _route_stops_map(route_id);

-- bookings wave 1 / 3
INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at)
WITH base AS (
    SELECT r.id AS ride_id, ((r.id-1)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff,
        CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*7)&2147483647)%20<16 THEN 'completed'
             WHEN r.status='completed' AND (hashint8(r.id::bigint*7)&2147483647)%20<19 THEN 'cancelled'
             WHEN r.status='completed' THEN 'picked_up'
             WHEN r.status='cancelled' AND (hashint8(r.id::bigint*7)&2147483647)%10<6 THEN 'cancelled'
             WHEN r.status='cancelled' THEN 'pending'
             WHEN r.status='in_progress' AND (hashint8(r.id::bigint*7)&2147483647)%10<7 THEN 'picked_up'
             WHEN r.status='in_progress' THEN 'confirmed'
             ELSE 'confirmed' END AS bk_status,
        r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*11)&2147483647)%14) AS bk_created,
        r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*13)&2147483647)%30) AS pup_at
    FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id
)
SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created,
    CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END,
    CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*17)&2147483647)%91) END
FROM base;

-- bookings wave 2 / 3
INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at)
WITH base AS (
    SELECT r.id AS ride_id, ((r.id-1+7001)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff,
        CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*23)&2147483647)%20<16 THEN 'completed'
             WHEN r.status='completed' AND (hashint8(r.id::bigint*23)&2147483647)%20<19 THEN 'cancelled'
             WHEN r.status='completed' THEN 'picked_up'
             WHEN r.status='cancelled' AND (hashint8(r.id::bigint*23)&2147483647)%10<6 THEN 'cancelled'
             WHEN r.status='cancelled' THEN 'pending'
             WHEN r.status='in_progress' AND (hashint8(r.id::bigint*23)&2147483647)%10<7 THEN 'picked_up'
             WHEN r.status='in_progress' THEN 'confirmed'
             ELSE 'confirmed' END AS bk_status,
        r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*29)&2147483647)%14) AS bk_created,
        r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*31)&2147483647)%30) AS pup_at
    FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id
)
SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created,
    CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END,
    CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*37)&2147483647)%91) END
FROM base;

-- bookings wave 3 / 3  (rides 1-2M only)
INSERT INTO bookings (ride_id, passenger_id, pickup_stop_id, dropoff_stop_id, status, created_at, pickup_confirmed_at, dropoff_confirmed_at)
WITH base AS (
    SELECT r.id AS ride_id, ((r.id-1+14002)%35000)+1 AS passenger_id, t.s0 AS pu, t.s3 AS doff,
        CASE WHEN r.status='completed' AND (hashint8(r.id::bigint*41)&2147483647)%20<16 THEN 'completed'
             WHEN r.status='completed' AND (hashint8(r.id::bigint*41)&2147483647)%20<19 THEN 'cancelled'
             WHEN r.status='completed' THEN 'picked_up'
             WHEN r.status='cancelled' AND (hashint8(r.id::bigint*41)&2147483647)%10<6 THEN 'cancelled'
             WHEN r.status='cancelled' THEN 'pending'
             WHEN r.status='in_progress' AND (hashint8(r.id::bigint*41)&2147483647)%10<7 THEN 'picked_up'
             WHEN r.status='in_progress' THEN 'confirmed'
             ELSE 'confirmed' END AS bk_status,
        r.departure_time - make_interval(days=>1+(hashint8(r.id::bigint*43)&2147483647)%14) AS bk_created,
        r.departure_time + make_interval(mins=>(hashint8(r.id::bigint*47)&2147483647)%30) AS pup_at
    FROM rides r JOIN _route_stops_map t ON t.route_id=r.route_id WHERE r.id BETWEEN 1 AND 2000000
)
SELECT ride_id,passenger_id,pu,doff,bk_status,bk_created,
    CASE WHEN bk_status IN ('picked_up','completed') THEN pup_at END,
    CASE WHEN bk_status='completed' THEN pup_at+make_interval(mins=>30+(hashint8(ride_id::bigint*53)&2147483647)%91) END
FROM base;

ANALYZE bookings;

-- booking_status_history — 5 passes
INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 1 AND 3000000;
INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 3000001 AND 6000000;
INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id BETWEEN 6000001 AND 9000000;
INSERT INTO booking_status_history (booking_id, status, changed_at) SELECT id,'pending',created_at FROM bookings WHERE id > 9000000;
INSERT INTO booking_status_history (booking_id, status, changed_at)
SELECT id, CASE WHEN status='completed' THEN 'completed' WHEN status='cancelled' THEN 'cancelled' WHEN status='picked_up' THEN 'picked_up' ELSE 'confirmed' END,
    created_at+make_interval(hours=>2+(hashint8(id::bigint*5)&2147483647)%22)
FROM bookings WHERE (hashint8(id::bigint*37)&2147483647)%4=0;

ANALYZE booking_status_history;

-- passenger_segments — 6 × 2M batches
INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 1 AND 2000000;
INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 2000001 AND 4000000;
INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 4000001 AND 6000000;
INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 6000001 AND 8000000;
INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id BETWEEN 8000001 AND 10000000;
INSERT INTO passenger_segments (booking_id,segment_id,is_present,segment_cost,passengers_on_segment,amount_charged) SELECT b.id,rs.id,TRUE,GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2)),(hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1,ROUND(GREATEST(0.01,ROUND((rs.distance_km*2.50)::numeric,2))/((hashint8(b.id::bigint*3+rs.segment_order)&2147483647)%3+1),2) FROM bookings b JOIN route_segments rs ON rs.ride_id=b.ride_id WHERE b.id > 10000000;

ANALYZE passenger_segments;

-- booking_final_fare — 3 batches
INSERT INTO booking_final_fare (booking_id,total_amount,calculated_at) SELECT id,ROUND((10.00+((hashint8(id::bigint*7)&2147483647)%4001)/100.0)::numeric,2),created_at+INTERVAL '1 day' FROM bookings WHERE id BETWEEN 1 AND 4000000;
INSERT INTO booking_final_fare (booking_id,total_amount,calculated_at) SELECT id,ROUND((10.00+((hashint8(id::bigint*7)&2147483647)%4001)/100.0)::numeric,2),created_at+INTERVAL '1 day' FROM bookings WHERE id BETWEEN 4000001 AND 8000000;
INSERT INTO booking_final_fare (booking_id,total_amount,calculated_at) SELECT id,ROUND((10.00+((hashint8(id::bigint*7)&2147483647)%4001)/100.0)::numeric,2),created_at+INTERVAL '1 day' FROM bookings WHERE id > 8000000;

ALTER TABLE bookings               ENABLE TRIGGER ALL;
ALTER TABLE booking_status_history ENABLE TRIGGER ALL;
ALTER TABLE passenger_segments     ENABLE TRIGGER ALL;
ALTER TABLE booking_final_fare     ENABLE TRIGGER ALL;

ANALYZE bookings; ANALYZE booking_status_history; ANALYZE passenger_segments; ANALYZE booking_final_fare;


-- ============================================================
-- PHASE 6 — TOLLS
-- ride_tolls (~600K), toll_passenger_split (~1.4M)
-- ============================================================

ALTER TABLE ride_tolls            DISABLE TRIGGER ALL;
ALTER TABLE toll_passenger_split  DISABLE TRIGGER ALL;

INSERT INTO ride_tolls (ride_id, toll_point_id, actual_amount_paid, passed_at)
SELECT r.id, ((hashint8(r.id::bigint*3)&2147483647)%15)+1,
    ROUND((tp.price_car+((hashint8(r.id::bigint*5)&2147483647)%201)/100.0)::numeric,2),
    r.departure_time+make_interval(mins=>30+(hashint8(r.id::bigint*7)&2147483647)%91)
FROM rides r
JOIN toll_points tp ON tp.id=((hashint8(r.id::bigint*3)&2147483647)%15)+1
WHERE (hashint8(r.id::bigint*11)&2147483647)%100<12;

ANALYZE ride_tolls;

INSERT INTO toll_passenger_split (ride_toll_id, booking_id, amount_due)
SELECT rt.id, b.id, ROUND(rt.actual_amount_paid/COUNT(b.id) OVER (PARTITION BY rt.id),2)
FROM ride_tolls rt JOIN bookings b ON b.ride_id=rt.ride_id;

ALTER TABLE ride_tolls            ENABLE TRIGGER ALL;
ALTER TABLE toll_passenger_split  ENABLE TRIGGER ALL;

ANALYZE ride_tolls; ANALYZE toll_passenger_split;


-- ============================================================
-- PHASE 7 — SOCIAL & SYSTEM
-- ratings (~2.1M), chat_threads (~500K), chat_messages (~5M),
-- incident_reports (~50K), location_pings (~10M),
-- notifications (~10M), audit_logs (~200K)
-- ============================================================

ALTER TABLE ratings           DISABLE TRIGGER ALL;
ALTER TABLE chat_threads      DISABLE TRIGGER ALL;
ALTER TABLE chat_messages     DISABLE TRIGGER ALL;
ALTER TABLE incident_reports  DISABLE TRIGGER ALL;
ALTER TABLE location_pings    DISABLE TRIGGER ALL;
ALTER TABLE notifications     DISABLE TRIGGER ALL;
ALTER TABLE audit_logs        DISABLE TRIGGER ALL;

INSERT INTO ratings (reviewer_user_id, reviewee_user_id, ride_id, score, comment, created_at)
SELECT b.passenger_id, r.driver_id+35000, b.ride_id,
    (hashint8(b.id::bigint*7)&2147483647)%5+1, NULL,
    r.departure_time+make_interval(hours=>3+(hashint8(b.id::bigint*5)&2147483647)%24)
FROM bookings b JOIN rides r ON r.id=b.ride_id
WHERE b.status='completed' AND (hashint8(b.id::bigint*13)&2147483647)%10<3;

ANALYZE ratings;

INSERT INTO chat_threads (ride_id, participant_1_id, participant_2_id, created_at)
SELECT r.id, r.driver_id+35000, ((r.id-1)%35000)+1, r.departure_time-make_interval(days=>1)
FROM rides r WHERE (hashint8(r.id::bigint*7)&2147483647)%10=0;

ANALYZE chat_threads;

INSERT INTO chat_messages (thread_id, sender_id, content, sent_at, is_deleted)
SELECT ct.id,
    CASE WHEN s.msg%2=0 THEN ct.participant_1_id ELSE ct.participant_2_id END,
    (ARRAY['Ok!','Sounds good','When exactly?','See you there','Got it',
           'On my way','Thanks a lot','Perfect!','Sure thing','Got it, thanks'])[s.msg],
    ct.created_at+make_interval(mins=>s.msg*15), FALSE
FROM chat_threads ct CROSS JOIN generate_series(1,10) s(msg);

ANALYZE chat_messages;

INSERT INTO incident_reports (ride_id, reporter_id, type, description, reported_at)
SELECT r.id, ((r.id-1)%35000)+1,
    (ARRAY['accident','reckless_driving','harassment','no_show','fraud','other'])[(hashint8(r.id::bigint*3)&2147483647)%6+1],
    (ARRAY['Minor incident during the ride','Unsafe driving behavior observed','Passenger reported discomfort','Driver did not arrive at pickup','Suspicious payment activity','General issue reported by passenger'])[(hashint8(r.id::bigint*3)&2147483647)%6+1],
    r.departure_time+make_interval(hours=>1)
FROM rides r WHERE (hashint8(r.id::bigint*7)&2147483647)%100=0;

ANALYZE incident_reports;

-- location_pings — 2 × 2.5M ride batches
INSERT INTO location_pings (ride_id,user_id,lat,lng,pinged_at,speed)
SELECT r.id, r.driver_id+35000,
    ROUND((41.00+((hashint8(r.id::bigint*p.n*5)&2147483647)%12001)/10000.0)::numeric,7),
    ROUND((20.50+((hashint8(r.id::bigint*p.n*7)&2147483647)%25001)/10000.0)::numeric,7),
    r.departure_time+make_interval(mins=>p.n*30),
    CASE WHEN (hashint8(r.id::bigint*p.n*11)&2147483647)%5=0 THEN NULL ELSE ROUND(((hashint8(r.id::bigint*p.n*13)&2147483647)%131)::numeric,1) END
FROM rides r CROSS JOIN generate_series(1,2) p(n) WHERE r.id BETWEEN 1 AND 2500000;

INSERT INTO location_pings (ride_id,user_id,lat,lng,pinged_at,speed)
SELECT r.id, r.driver_id+35000,
    ROUND((41.00+((hashint8(r.id::bigint*p.n*5)&2147483647)%12001)/10000.0)::numeric,7),
    ROUND((20.50+((hashint8(r.id::bigint*p.n*7)&2147483647)%25001)/10000.0)::numeric,7),
    r.departure_time+make_interval(mins=>p.n*30),
    CASE WHEN (hashint8(r.id::bigint*p.n*11)&2147483647)%5=0 THEN NULL ELSE ROUND(((hashint8(r.id::bigint*p.n*13)&2147483647)%131)::numeric,1) END
FROM rides r CROSS JOIN generate_series(1,2) p(n) WHERE r.id BETWEEN 2500001 AND 5000000;

ANALYZE location_pings;

-- notifications — ride reminders (5M) + booking confirmations (5M)
INSERT INTO notifications (user_id,type,title,body,sent_at,read_at)
SELECT r.driver_id+35000,'ride','Ride Departing Soon',
    'You have a ride scheduled for '||TO_CHAR(r.departure_time,'DD Mon HH24:MI'),
    r.departure_time-make_interval(hours=>2),
    CASE WHEN (hashint8(r.id::bigint*3)&2147483647)%10<3 THEN r.departure_time-make_interval(hours=>1) ELSE NULL END
FROM rides r;

INSERT INTO notifications (user_id,type,title,body,sent_at,read_at)
SELECT b.passenger_id,'booking','Booking Confirmed',
    'Your booking for ride #'||b.ride_id||' is '||b.status,
    b.created_at+make_interval(hours=>1),
    CASE WHEN (hashint8(b.id::bigint*7)&2147483647)%10<4 THEN b.created_at+make_interval(hours=>3) ELSE NULL END
FROM bookings b WHERE b.id<=5000000;

ANALYZE notifications;

INSERT INTO audit_logs (table_name,record_id,operation,old_data,new_data,changed_at,changed_by)
SELECT 'rides',id,'INSERT',NULL,'{"status":"'||status||'","driver_id":'||driver_id||'}',created_at,driver_id+35000
FROM rides WHERE id BETWEEN 4800001 AND 5000000;

ALTER TABLE ratings           ENABLE TRIGGER ALL;
ALTER TABLE chat_threads      ENABLE TRIGGER ALL;
ALTER TABLE chat_messages     ENABLE TRIGGER ALL;
ALTER TABLE incident_reports  ENABLE TRIGGER ALL;
ALTER TABLE location_pings    ENABLE TRIGGER ALL;
ALTER TABLE notifications     ENABLE TRIGGER ALL;
ALTER TABLE audit_logs        ENABLE TRIGGER ALL;

ANALYZE ratings; ANALYZE chat_threads; ANALYZE chat_messages; ANALYZE incident_reports;
ANALYZE location_pings; ANALYZE notifications; ANALYZE audit_logs;

