-- Arrange-data for the IMPORT corridor flow specs
-- (flows/import_full_train, import_waiting_expiry, import_split_promote,
--  import_window_reopen, import_critical_matrix). Idempotent.
--
-- Long import corridor (A→B→C→D→E→T, 6 stops, DJ→ET = IMPORT):
--   DJIB_PORT → NAGAD → DIRE_DAWA → E2E_AWASH → MOJO → KALITY
--
-- The specs create the route + schedules through the API; this fixture provides
-- what the journeys cannot reasonably create in-flow:
--   1. the extra mid-corridor yard (E2E_AWASH) + container facility rows
--   2. container types 20FT/40FT + NW5 allow-list (shared with seed-intercity)
--   3. NW5 rated for 54 wagons per train (the corridor trains run 54)
--   4. eight locomotives at Djibouti Port (each import schedule needs >= 2)
--   5. free NW5 wagon stock parked at DJIB_PORT (+ a NAGAD pocket for the
--      sub-corridor scenario) so wagon allocation has physical stock
--   6. yard distances for every consecutive pair (route creation refuses
--      unconfigured pairs)
--   7. LIVE CONTAINER_IMPORT rates on the legs the specs book (pricing
--      hard-blocks a container line without a rate on its exact leg) + an
--      INTERCITY_CONTAINER rate for the ride-along scenario

-- 0. The split-remainder chain (assertExactRemainder in contract-booking)
-- deliberately creates a SECOND live booking under a split ONE_TIME contract,
-- but no migration ever relaxed the 1822 one-live-booking unique index for it
-- (the dev DB was hand-patched). Drop it here the same way — and note it as a
-- missing production migration.
DROP INDEX IF EXISTS freight.uq_one_active_booking_per_one_time_contract;

-- 0b. Schema drift guard: CUSTOMS_CLEARANCE / WITH_RETURN rates became
-- route-scoped in migrations 2820 + 2830, but an e2e image built before them
-- still carries the old CK_rates_yard_scope (yards allowed only for ALWAYS
-- BULK/CONTAINER/INTERCITY rows). Re-state the post-2830 shape — a no-op on an
-- up-to-date image, and what lets the customs-clearance lane rates below load.
ALTER TABLE freight.rates DROP CONSTRAINT IF EXISTS "CK_rates_yard_scope";
ALTER TABLE freight.rates
  ADD CONSTRAINT "CK_rates_yard_scope" CHECK (
    deleted_at IS NOT NULL
    OR status = 'SUPERSEDED'
    OR CASE
         WHEN ("trigger" = 'ALWAYS' AND applies_to IN ('BULK', 'CONTAINER', 'INTERCITY'))
           OR "trigger" IN ('CUSTOMS_CLEARANCE', 'WITH_RETURN')
           THEN origin_yard_id IS NOT NULL AND destination_yard_id IS NOT NULL
         ELSE origin_yard_id IS NULL AND destination_yard_id IS NULL
       END
  );

-- 1a. Extra Ethiopian mid-corridor yard.
INSERT INTO freight.yards (id, code, label, country, is_active, display_order)
SELECT gen_random_uuid(), 'E2E_AWASH', 'E2E Awash Yard', 'Ethiopia', true, 50
WHERE NOT EXISTS (SELECT 1 FROM freight.yards WHERE code = 'E2E_AWASH');

-- 1b. Container-capable facility rows for every corridor yard the specs load
-- or unload at (booking-journey's yard gate reads freight.yard_facilities).
INSERT INTO freight.yard_facilities
  (id, yard_id, has_warehouse, handles_container, handles_bulk, is_active)
SELECT gen_random_uuid(), y.id, false, true, true, true
FROM freight.yards y
WHERE y.code IN ('DJIB_PORT', 'NAGAD', 'DIRE_DAWA', 'E2E_AWASH', 'MOJO', 'KALITY')
  AND NOT EXISTS (
    SELECT 1 FROM freight.yard_facilities f
    WHERE f.yard_id = y.id AND f.deleted_at IS NULL
  );

-- 2a. Container types (booking form + API resolve 20ft/40ft by size_ft).
INSERT INTO freight.container_types (id, code, label, size_ft, is_active)
SELECT gen_random_uuid(), v.code, v.label, v.size_ft, true
FROM (VALUES ('20FT', '20FT', 20), ('40FT', '40FT', 40)) AS v(code, label, size_ft)
WHERE NOT EXISTS (SELECT 1 FROM freight.container_types t WHERE t.code = v.code);

-- 2b. 20ft/40ft containers ride NW5 flat wagons.
INSERT INTO freight.container_type_wagon_types (container_type_id, wagon_type_id)
SELECT ct.id, wt.id
FROM freight.container_types ct
JOIN freight.wagon_types wt ON wt.code = 'NW5'
WHERE ct.code IN ('20FT', '40FT')
  AND NOT EXISTS (
    SELECT 1 FROM freight.container_type_wagon_types x
    WHERE x.container_type_id = ct.id AND x.wagon_type_id = wt.id
  );

-- 3. Pin the derived slot count at 54: the batch engine recomputes
-- schedule.max_wagons as floor(locoLength / SHORTEST active wagon length)
-- (syncScheduleMaxWagons). GW2 (12.228 m) is not part of these flows but is
-- the shortest active type — deactivate it so NW5 (13.966 m) governs, and run
-- 760 m locos: floor(760 / 13.966) = 54 slots, and 54 NW5 = 754.2 m still
-- fits the per-edge length budget.
UPDATE freight.wagon_types SET is_active = false WHERE code = 'GW2' AND is_active;

-- 4. Fourteen locomotives at Djibouti Port. 9000T pull comfortably clears a
-- 54-wagon container consist; each spec's schedule picks its own pair.
INSERT INTO freight.locomotives
  (id, code, max_pull_weight_tons, max_train_length_meters, current_yard_id)
SELECT gen_random_uuid(), v.code, 9000, 760, y.id
FROM (VALUES ('LOCO-IMP-1'), ('LOCO-IMP-2'), ('LOCO-IMP-3'), ('LOCO-IMP-4'),
             ('LOCO-IMP-5'), ('LOCO-IMP-6'), ('LOCO-IMP-7'), ('LOCO-IMP-8'),
             ('LOCO-IMP-9'), ('LOCO-IMP-10'), ('LOCO-IMP-11'), ('LOCO-IMP-12'),
             ('LOCO-IMP-13'), ('LOCO-IMP-14'), ('LOCO-IMP-15'), ('LOCO-IMP-16'),
             ('LOCO-IMP-17'), ('LOCO-IMP-18'), ('LOCO-IMP-19'), ('LOCO-IMP-20'),
             ('LOCO-IMP-21'), ('LOCO-IMP-22'), ('LOCO-IMP-23'), ('LOCO-IMP-24'),
             ('LOCO-IMP-25'), ('LOCO-IMP-26'), ('LOCO-IMP-27'), ('LOCO-IMP-28'))
     AS v(code)
JOIN freight.yards y ON y.code = 'DJIB_PORT'
WHERE NOT EXISTS (SELECT 1 FROM freight.locomotives l WHERE l.code = v.code);

-- Prior seeds may have created the fleet at other dimensions — enforce.
UPDATE freight.locomotives
SET max_pull_weight_tons = 9000, max_train_length_meters = 760
WHERE code LIKE 'LOCO-IMP-%'
  AND (max_pull_weight_tons IS DISTINCT FROM 9000
       OR max_train_length_meters IS DISTINCT FROM 760);

-- 5. Wagon stock: park every free NW5 flat at Djibouti Port, then move 20 of
-- them to NAGAD for the sub-corridor boarding scenario. Coupled wagons
-- (train_id set — e.g. TRN-E2E-1's four) are untouched.
UPDATE freight.wagons w
SET current_yard_id = (SELECT id FROM freight.yards WHERE code = 'DJIB_PORT')
FROM freight.wagon_types wt
WHERE wt.id = w.wagon_type_id AND wt.code = 'NW5'
  AND w.train_id IS NULL AND w.deleted_at IS NULL;

UPDATE freight.wagons w
SET current_yard_id = (SELECT id FROM freight.yards WHERE code = 'NAGAD')
FROM (
  SELECT w2.id
  FROM freight.wagons w2
  JOIN freight.wagon_types wt ON wt.id = w2.wagon_type_id AND wt.code = 'NW5'
  WHERE w2.train_id IS NULL AND w2.deleted_at IS NULL
  ORDER BY w2.wagon_number DESC
  LIMIT 20
) pick
WHERE w.id = pick.id;

-- 5b. Export-corridor rolling stock: the reversed corridor (KALITY →
-- DJIB_PORT) loads at KALITY, sub-corridor exports board at DIRE_DAWA, and
-- twelve dedicated locomotives live at KALITY.
UPDATE freight.wagons w
SET current_yard_id = (SELECT id FROM freight.yards WHERE code = 'KALITY')
FROM (
  SELECT w2.id
  FROM freight.wagons w2
  JOIN freight.wagon_types wt ON wt.id = w2.wagon_type_id AND wt.code = 'NW5'
  JOIN freight.yards y ON y.id = w2.current_yard_id AND y.code = 'DJIB_PORT'
  WHERE w2.train_id IS NULL AND w2.deleted_at IS NULL
  ORDER BY w2.wagon_number ASC
  LIMIT 200
) pick
WHERE w.id = pick.id;

UPDATE freight.wagons w
SET current_yard_id = (SELECT id FROM freight.yards WHERE code = 'DIRE_DAWA')
FROM (
  SELECT w2.id
  FROM freight.wagons w2
  JOIN freight.wagon_types wt ON wt.id = w2.wagon_type_id AND wt.code = 'NW5'
  JOIN freight.yards y ON y.id = w2.current_yard_id AND y.code = 'DJIB_PORT'
  WHERE w2.train_id IS NULL AND w2.deleted_at IS NULL
  ORDER BY w2.wagon_number ASC
  LIMIT 20
) pick
WHERE w.id = pick.id;

INSERT INTO freight.locomotives
  (id, code, max_pull_weight_tons, max_train_length_meters, current_yard_id)
SELECT gen_random_uuid(), v.code, 9000, 760, y.id
FROM (VALUES ('LOCO-EXP-1'), ('LOCO-EXP-2'), ('LOCO-EXP-3'), ('LOCO-EXP-4'),
             ('LOCO-EXP-5'), ('LOCO-EXP-6'), ('LOCO-EXP-7'), ('LOCO-EXP-8'),
             ('LOCO-EXP-9'), ('LOCO-EXP-10'), ('LOCO-EXP-11'), ('LOCO-EXP-12'))
     AS v(code)
JOIN freight.yards y ON y.code = 'KALITY'
WHERE NOT EXISTS (SELECT 1 FROM freight.locomotives l WHERE l.code = v.code);

-- 5b2. Export-bulk rolling stock: the boot CW4 fleet (110) is already spoken
-- for by the import-bulk specs at DJIB_PORT — mint dedicated e2e CW4 wagons
-- for the export side: 80 at KALITY (full trains) + 20 at DIRE_DAWA
-- (sub-corridor boarding).
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
SELECT gen_random_uuid(), 'ECW' || lpad(g::text, 4, '0'), wt.id,
       (SELECT id FROM freight.yards
        WHERE code = CASE WHEN g <= 120 THEN 'KALITY' ELSE 'DIRE_DAWA' END)
FROM generate_series(1, 160) AS g
JOIN freight.wagon_types wt ON wt.code = 'CW4'
WHERE NOT EXISTS (
  SELECT 1 FROM freight.wagons w WHERE w.wagon_number = 'ECW' || lpad(g::text, 4, '0')
);

-- 5b3. Ledger-day rolling stock: GRAINS ride PW2 box wagons on a BUILT
-- Train-Builder consist — 37 PW2 wagons coupled at KALITY behind two dedicated
-- locos. A built train's physical wagon count IS its schedule capacity (37),
-- immune to the loco-length slot recompute.
--
-- GRAINS gets PW2 (17.066 m), WHEAT gets CW4 (13.976 m) below — kept on SEPARATE
-- cargo types on purpose. `dimsFor`/`needFor` size a bulk booking off its cargo
-- type's FIRST wagon type, so a cargo carrying two wagon-lengths sizes
-- inconsistently on loco-pair export trains (one booking as CW4, another as PW2)
-- and the length budget never matches the 54-wagon CW4 board the export specs
-- expect. One cargo → one wagon length keeps that math deterministic.
DELETE FROM freight.cargo_type_wagon_types x
USING freight.cargo_types ct, freight.wagon_types wt
WHERE x.cargo_type_id = ct.id AND x.wagon_type_id = wt.id
  AND ((ct.code = 'E2E_IMP_WHEAT'  AND wt.code = 'PW2')
    OR (ct.code = 'E2E_IMP_GRAINS' AND wt.code = 'CW4'));

INSERT INTO freight.cargo_type_wagon_types (cargo_type_id, wagon_type_id)
SELECT ct.id, wt.id
FROM freight.cargo_types ct
JOIN freight.wagon_types wt ON wt.code = 'PW2'
WHERE ct.code = 'E2E_IMP_GRAINS'
  AND NOT EXISTS (
    SELECT 1 FROM freight.cargo_type_wagon_types x
    WHERE x.cargo_type_id = ct.id AND x.wagon_type_id = wt.id
  );

INSERT INTO freight.locomotives
  (id, code, max_pull_weight_tons, max_train_length_meters, current_yard_id)
SELECT gen_random_uuid(), v.code, 9000, 760, y.id
FROM (VALUES ('LOCO-LED-1'), ('LOCO-LED-2'), ('LOCO-LED-3'), ('LOCO-LED-4'),
             ('LOCO-LED-5'), ('LOCO-LED-6')) AS v(code)
JOIN freight.yards y ON y.code = 'KALITY'
WHERE NOT EXISTS (SELECT 1 FROM freight.locomotives l WHERE l.code = v.code);

INSERT INTO freight.trains
  (id, code, train_name, capacity_tons, current_yard_id,
   import_train_number, export_train_number)
SELECT gen_random_uuid(), 'TRN-LEDGER-PW2', 'Ledger Grain Carrier', 2600, y.id,
       '9102', '9101'
FROM freight.yards y
WHERE y.code = 'KALITY'
  AND NOT EXISTS (SELECT 1 FROM freight.trains t WHERE t.code = 'TRN-LEDGER-PW2');

INSERT INTO freight.train_locomotives (id, train_id, locomotive_id, sequence_no)
SELECT gen_random_uuid(), t.id, l.id,
       row_number() OVER (ORDER BY l.code) - 1
FROM freight.trains t
JOIN freight.locomotives l ON l.code IN ('LOCO-LED-1', 'LOCO-LED-2')
WHERE t.code = 'TRN-LEDGER-PW2'
  AND NOT EXISTS (
    SELECT 1 FROM freight.train_locomotives tl
    WHERE tl.train_id = t.id AND tl.locomotive_id = l.id
  );

UPDATE freight.wagons w
SET train_id = t.id,
    sequence_number = sub.rn,
    current_yard_id = (SELECT id FROM freight.yards WHERE code = 'KALITY')
FROM freight.trains t,
     LATERAL (
       SELECT w2.id, row_number() OVER (ORDER BY w2.wagon_number) AS rn
       FROM freight.wagons w2
       JOIN freight.wagon_types wt ON wt.id = w2.wagon_type_id AND wt.code = 'PW2'
       WHERE w2.train_id IS NULL AND w2.deleted_at IS NULL
       ORDER BY w2.wagon_number
       LIMIT 37
     ) sub
WHERE t.code = 'TRN-LEDGER-PW2'
  AND w.id = sub.id
  AND NOT EXISTS (SELECT 1 FROM freight.wagons wx WHERE wx.train_id = t.id);

-- 5c. Approved exporter profile — export contracts bill against it
-- (seed-company.sql only creates the importer).
INSERT INTO freight.company_profiles (id, company_id, type, status, reference)
SELECT gen_random_uuid(), c.id, 'exporter', 'active', 'EXP-E2E-0002'
FROM freight.companies c
WHERE c.tin = '0102030405'
  AND NOT EXISTS (
    SELECT 1 FROM freight.company_profiles p
    WHERE p.company_id = c.id AND p.type = 'exporter' AND p.deleted_at IS NULL
  );

-- 6. Segment distances for every consecutive corridor pair (symmetric rows).
INSERT INTO freight.yard_distances (id, from_yard_id, to_yard_id, distance_km)
SELECT gen_random_uuid(), a.id, b.id, v.km
FROM (VALUES
        ('DJIB_PORT', 'NAGAD',     20),
        ('NAGAD',     'DIRE_DAWA', 310),
        ('DIRE_DAWA', 'E2E_AWASH', 200),
        ('E2E_AWASH', 'MOJO',      250),
        ('MOJO',      'KALITY',    70)
     ) AS v(from_code, to_code, km)
JOIN freight.yards a ON a.code = v.from_code
JOIN freight.yards b ON b.code = v.to_code
WHERE NOT EXISTS (
  SELECT 1 FROM freight.yard_distances d
  WHERE (d.from_yard_id = a.id AND d.to_yard_id = b.id)
     OR (d.from_yard_id = b.id AND d.to_yard_id = a.id)
);

-- 6b. BULK import cargo hierarchy: group "E2E Import Grains" → commodity
-- "E2E Import Wheat", carried on CW4 covered gondolas (13.976 m — the only
-- bulk-capable type whose 54-wagon consist still fits the 760 m locos).
INSERT INTO freight.cargo_types (id, code, cargo_type_name, is_active)
SELECT gen_random_uuid(), 'E2E_IMP_GRAINS', 'E2E Import Grains', true
WHERE NOT EXISTS (SELECT 1 FROM freight.cargo_types WHERE code = 'E2E_IMP_GRAINS');

INSERT INTO freight.cargo_types (id, code, cargo_type_name, parent_group_id, is_active)
SELECT gen_random_uuid(), 'E2E_IMP_WHEAT', 'E2E Import Wheat', g.id, true
FROM freight.cargo_types g
WHERE g.code = 'E2E_IMP_GRAINS'
  AND NOT EXISTS (SELECT 1 FROM freight.cargo_types WHERE code = 'E2E_IMP_WHEAT');

INSERT INTO freight.cargo_type_wagon_types (cargo_type_id, wagon_type_id)
SELECT ct.id, wt.id
FROM freight.cargo_types ct
JOIN freight.wagon_types wt ON wt.code = 'CW4'
WHERE ct.code = 'E2E_IMP_WHEAT'
  AND NOT EXISTS (
    SELECT 1 FROM freight.cargo_type_wagon_types x
    WHERE x.cargo_type_id = ct.id AND x.wagon_type_id = wt.id
  );

-- 6c. Park the free CW4 fleet for the bulk specs: bulk trains load at
-- Djibouti Port, a NAGAD pocket serves the sub-corridor scenario and a MOJO
-- pocket the bulk intercity ride-along. The e2e-minted ECW% wagons are the
-- EXPORT pocket (KALITY / DIRE_DAWA) — never sweep them to Djibouti.
UPDATE freight.wagons w
SET current_yard_id = (SELECT id FROM freight.yards WHERE code = 'DJIB_PORT')
FROM freight.wagon_types wt
WHERE wt.id = w.wagon_type_id AND wt.code = 'CW4'
  AND w.train_id IS NULL AND w.deleted_at IS NULL
  AND w.wagon_number NOT LIKE 'ECW%';

-- Re-park the export CW4 pocket every seed (the mint above is insert-guarded).
UPDATE freight.wagons w
SET current_yard_id = (SELECT id FROM freight.yards
                       WHERE code = CASE WHEN substring(w.wagon_number FROM 4)::int <= 120
                                         THEN 'KALITY' ELSE 'DIRE_DAWA' END)
FROM freight.wagon_types wt
WHERE wt.id = w.wagon_type_id AND wt.code = 'CW4'
  AND w.wagon_number LIKE 'ECW%'
  AND w.train_id IS NULL AND w.deleted_at IS NULL;

UPDATE freight.wagons w
SET current_yard_id = (SELECT id FROM freight.yards WHERE code = 'NAGAD')
FROM (
  SELECT w2.id
  FROM freight.wagons w2
  JOIN freight.wagon_types wt ON wt.id = w2.wagon_type_id AND wt.code = 'CW4'
  WHERE w2.train_id IS NULL AND w2.deleted_at IS NULL
  ORDER BY w2.wagon_number DESC
  LIMIT 10
) pick
WHERE w.id = pick.id;

UPDATE freight.wagons w
SET current_yard_id = (SELECT id FROM freight.yards WHERE code = 'MOJO')
FROM (
  SELECT w2.id
  FROM freight.wagons w2
  JOIN freight.wagon_types wt ON wt.id = w2.wagon_type_id AND wt.code = 'CW4'
  JOIN freight.yards y ON y.id = w2.current_yard_id AND y.code = 'DJIB_PORT'
  WHERE w2.train_id IS NULL AND w2.deleted_at IS NULL
  ORDER BY w2.wagon_number ASC
  LIMIT 6
) pick
WHERE w.id = pick.id;

-- 7. LIVE import rates on every leg the specs book, plus the intercity
-- ride-along leg (rates are configured in USD and converted per booking).
INSERT INTO freight.rates
  (id, rate_type, applies_to, trigger, currency, rate_value, rate_unit, status,
   origin_yard_id, destination_yard_id, proposed_by_staff_id)
SELECT gen_random_uuid(), v.rate_type, v.applies_to, 'ALWAYS', 'USD', v.value,
       v.unit, 'LIVE', a.id, b.id, u.id
FROM (VALUES
        ('CONTAINER_IMPORT',    'CONTAINER', 'DJIB_PORT', 'KALITY', 800, 'PER_CONTAINER'),
        ('CONTAINER_IMPORT',    'CONTAINER', 'DJIB_PORT', 'MOJO',   700, 'PER_CONTAINER'),
        ('CONTAINER_IMPORT',    'CONTAINER', 'NAGAD',     'KALITY', 650, 'PER_CONTAINER'),
        ('CONTAINER_IMPORT',    'CONTAINER', 'NAGAD',     'MOJO',   600, 'PER_CONTAINER'),
        ('INTERCITY_CONTAINER', 'INTERCITY', 'MOJO',      'KALITY', 200, 'PER_CONTAINER'),
        ('BULK_IMPORT',         'BULK',      'DJIB_PORT', 'KALITY', 30,  'PER_TON'),
        ('BULK_IMPORT',         'BULK',      'DJIB_PORT', 'MOJO',   28,  'PER_TON'),
        ('BULK_IMPORT',         'BULK',      'NAGAD',     'KALITY', 26,  'PER_TON'),
        ('BULK_IMPORT',         'BULK',      'NAGAD',     'MOJO',   25,  'PER_TON'),
        ('INTERCITY_BULK',      'INTERCITY', 'MOJO',      'KALITY', 10,  'PER_TON'),
        ('CONTAINER_EXPORT',    'CONTAINER', 'KALITY',    'DJIB_PORT', 800, 'PER_CONTAINER'),
        ('CONTAINER_EXPORT',    'CONTAINER', 'MOJO',      'DJIB_PORT', 700, 'PER_CONTAINER'),
        ('CONTAINER_EXPORT',    'CONTAINER', 'DIRE_DAWA', 'DJIB_PORT', 500, 'PER_CONTAINER'),
        ('INTERCITY_CONTAINER', 'INTERCITY', 'KALITY',    'MOJO',   200, 'PER_CONTAINER'),
        ('BULK_EXPORT',         'BULK',      'KALITY',    'DJIB_PORT', 30, 'PER_TON'),
        ('BULK_EXPORT',         'BULK',      'MOJO',      'DJIB_PORT', 25, 'PER_TON'),
        ('BULK_EXPORT',         'BULK',      'DIRE_DAWA', 'DJIB_PORT', 20, 'PER_TON'),
        ('INTERCITY_BULK',      'INTERCITY', 'KALITY',    'MOJO',   10,  'PER_TON')
     ) AS v(rate_type, applies_to, from_code, to_code, value, unit)
JOIN freight.yards a ON a.code = v.from_code
JOIN freight.yards b ON b.code = v.to_code
JOIN iam.users u ON u.email = 'operation@edr.local'
WHERE NOT EXISTS (
  SELECT 1 FROM freight.rates r
  WHERE r.rate_type = v.rate_type
    AND r.origin_yard_id = a.id AND r.destination_yard_id = b.id
    AND r.deleted_at IS NULL
);

-- 7b. Customs clearance service fees — billed on the booking invoice together
-- with the freight. Sold per direction + route + cargo kind: container fees
-- per container type (one row per active 20/40ft type so whichever type the
-- server resolves for a size always matches), bulk fees per commodity.
-- Without these, every customs booking hard-blocks at pricing.
INSERT INTO freight.rates
  (id, rate_type, applies_to, trigger, currency, rate_value, rate_unit, status,
   trade_direction, container_type_id, origin_yard_id, destination_yard_id,
   proposed_by_staff_id)
SELECT gen_random_uuid(), 'CUSTOMS_CLEARANCE', 'OTHER', 'CUSTOMS_CLEARANCE',
       'USD', 50, 'PER_CONTAINER', 'LIVE', v.direction, ct.id, a.id, b.id, u.id
FROM (VALUES
        ('IMPORT', 'DJIB_PORT', 'KALITY'),
        ('IMPORT', 'DJIB_PORT', 'MOJO'),
        ('IMPORT', 'NAGAD',     'KALITY'),
        ('IMPORT', 'NAGAD',     'MOJO'),
        ('EXPORT', 'KALITY',    'DJIB_PORT'),
        ('EXPORT', 'MOJO',      'DJIB_PORT'),
        ('EXPORT', 'DIRE_DAWA', 'DJIB_PORT')
     ) AS v(direction, from_code, to_code)
JOIN freight.yards a ON a.code = v.from_code
JOIN freight.yards b ON b.code = v.to_code
JOIN freight.container_types ct ON ct.size_ft IN (20, 40) AND ct.is_active
JOIN iam.users u ON u.email = 'operation@edr.local'
WHERE NOT EXISTS (
  SELECT 1 FROM freight.rates r
  WHERE r.rate_type = 'CUSTOMS_CLEARANCE'
    AND r.container_type_id = ct.id
    AND r.origin_yard_id = a.id AND r.destination_yard_id = b.id
    AND r.deleted_at IS NULL
);

INSERT INTO freight.rates
  (id, rate_type, applies_to, trigger, currency, rate_value, rate_unit, status,
   trade_direction, cargo_type_id, origin_yard_id, destination_yard_id,
   proposed_by_staff_id)
SELECT gen_random_uuid(), 'CUSTOMS_CLEARANCE', 'OTHER', 'CUSTOMS_CLEARANCE',
       'USD', 2, 'PER_TON', 'LIVE', v.direction, cgt.id, a.id, b.id, u.id
FROM (VALUES
        ('IMPORT', 'DJIB_PORT', 'KALITY'),
        ('IMPORT', 'DJIB_PORT', 'MOJO'),
        ('IMPORT', 'NAGAD',     'KALITY'),
        ('IMPORT', 'NAGAD',     'MOJO'),
        ('EXPORT', 'KALITY',    'DJIB_PORT'),
        ('EXPORT', 'MOJO',      'DJIB_PORT'),
        ('EXPORT', 'DIRE_DAWA', 'DJIB_PORT')
     ) AS v(direction, from_code, to_code)
JOIN freight.yards a ON a.code = v.from_code
JOIN freight.yards b ON b.code = v.to_code
JOIN freight.cargo_types cgt ON cgt.code = 'E2E_IMP_WHEAT'
JOIN iam.users u ON u.email = 'operation@edr.local'
WHERE NOT EXISTS (
  SELECT 1 FROM freight.rates r
  WHERE r.rate_type = 'CUSTOMS_CLEARANCE'
    AND r.cargo_type_id = cgt.id
    AND r.origin_yard_id = a.id AND r.destination_yard_id = b.id
    AND r.deleted_at IS NULL
);
