edr-platform/e2e/freight/cypress/fixtures/seed-g2-weight.sql

-- Arrange-data for the GROUP 2 weight/tolerance scenarios (flows/g2_weight.cy.ts).
-- Run AFTER seed-import-corridor.sql. Idempotent.
--
-- Group 2 is the only group where WEIGHT binds before slots. Group 1's train
-- (TRN-G1-1) deliberately keeps every non-slot axis slack; this one does the
-- opposite, so the two must never share rolling stock.
--
-- THE ARITHMETIC (all from train-capacity.util.ts)
--
-- The weight axis is GROSS — a locomotive hauls the wagon as well as its
-- cargo (`grossWagonWeightTons` = tare + cargo). NW5 tare is 22.4T, and two
-- 20ft containers ride one NW5. So with a heavy VGM of 28T per container:
--
--     wagon gross = 2 × 28 + 22.4 = 78.4T          ← S9's number exactly
--
--   S9  base pull 3500T:  35 wagons × 78.4 = 2744.0T  ✔ fits
--                         45 wagons × 78.4 = 3528.0T  ✘ over base
--       → C is blocked on WEIGHT while 18 slots still sit empty.
--
--   S10 tolerance 90T → cap 3590T: 3528 ≤ 3590, so C is admitted WHOLE.
--       Tolerance is spendable ONLY to admit a booking whole.
--
--   S11 after 2744T used, base room = 3500 − 2744 = 756T.
--       756 / 78.4 = 9.64 → a split may size at most 9 wagons (705.6T).
--       It may NEVER reach 10 by dipping into the 90T tolerance, because
--       sizePartialOfferWagons budgets against BASE only
--       (train-capacity.util.ts:58, booking-batch.service.ts:4109).
--
--   S12 light cargo (12T/container): wagon gross = 2 × 12 + 22.4 = 46.4T.
--       53 × 46.4 = 2459.2T — only 70% of base. SLOTS bind, not weight.
--
-- TWO LOCOMOTIVE PAIRS, ONE TRAIN EACH
--
-- Pull weight ADDS UP across a set but length takes the MINIMUM
-- (combinedLocomotiveLimits, train-capacity.util.ts:358), so a pair of 1750T
-- locos gives exactly the 3500T base the scenarios assume. Weight tolerance
-- adds up too — hence 45T each for the 90T set.
--
--   TRN-G2-BASE  LOCO-G2-A/B  1750T each, tolerance 0   → 3500T base, no slack
--   TRN-G2-TOL   LOCO-G2-C/D  1750T each, tolerance 45T → 3500T base + 90T
--
-- Both carry 53 NW5 wagons so the SLOT axis is identical to Group 1 and any
-- difference in outcome is attributable to weight alone.

-- 1. Locomotives. Length 760m keeps the length axis slack (53 × 13.966 =
-- 740.2m < 760m), so only weight and slots can ever bind here.
INSERT INTO freight.locomotives
  (id, code, max_pull_weight_tons, max_train_length_meters,
   overage_tolerance_tons, overage_tolerance_meters, current_yard_id)
SELECT gen_random_uuid(), v.code, 1750, 760, v.tol, 0, y.id
FROM (VALUES
        ('LOCO-G2-A', 0), ('LOCO-G2-B', 0),
        ('LOCO-G2-C', 45), ('LOCO-G2-D', 45)
     ) AS v(code, tol)
JOIN freight.yards y ON y.code = 'DJIB_PORT'
WHERE NOT EXISTS (SELECT 1 FROM freight.locomotives l WHERE l.code = v.code);

-- Re-assert every seed: a prior run's row may predate these numbers, and the
-- whole group's arithmetic depends on them being exact.
UPDATE freight.locomotives l
SET max_pull_weight_tons = 1750,
    max_train_length_meters = 760,
    overage_tolerance_tons = v.tol,
    overage_tolerance_meters = 0,
    current_yard_id = (SELECT id FROM freight.yards WHERE code = 'DJIB_PORT')
FROM (VALUES
        ('LOCO-G2-A', 0), ('LOCO-G2-B', 0),
        ('LOCO-G2-C', 45), ('LOCO-G2-D', 45)
     ) AS v(code, tol)
WHERE l.code = v.code
  AND (l.max_pull_weight_tons IS DISTINCT FROM 1750
       OR l.max_train_length_meters IS DISTINCT FROM 760
       OR l.overage_tolerance_tons IS DISTINCT FROM v.tol);

-- 2. The two trains at the corridor origin.
INSERT INTO freight.trains
  (id, code, train_name, capacity_tons, current_yard_id,
   import_train_number, export_train_number)
SELECT gen_random_uuid(), v.code, v.name, 3500, y.id, v.imp, v.exp
FROM (VALUES
        ('TRN-G2-BASE', 'E2E Group-2 Base-Weight Carrier', '9302', '9301'),
        ('TRN-G2-TOL',  'E2E Group-2 Tolerance Carrier',   '9304', '9303')
     ) AS v(code, name, imp, exp)
JOIN freight.yards y ON y.code = 'DJIB_PORT'
WHERE NOT EXISTS (SELECT 1 FROM freight.trains t WHERE t.code = v.code);

-- 3. Couple each locomotive pair to its train.
INSERT INTO freight.train_locomotives (id, train_id, locomotive_id, sequence_no)
SELECT gen_random_uuid(), t.id, l.id, v.seq
FROM (VALUES
        ('TRN-G2-BASE', 'LOCO-G2-A', 0), ('TRN-G2-BASE', 'LOCO-G2-B', 1),
        ('TRN-G2-TOL',  'LOCO-G2-C', 0), ('TRN-G2-TOL',  'LOCO-G2-D', 1)
     ) AS v(train_code, loco_code, seq)
JOIN freight.trains t ON t.code = v.train_code
JOIN freight.locomotives l ON l.code = v.loco_code
WHERE NOT EXISTS (
  SELECT 1 FROM freight.train_locomotives tl
  WHERE tl.train_id = t.id AND tl.locomotive_id = l.id
);

-- 4. 53 dedicated NW5 wagons per train (WGN-G2B-*, WGN-G2T-*). Same slot count
-- as Group 1 on purpose — see the header.
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
SELECT gen_random_uuid(), v.prefix || lpad(g::text, 2, '0'), wt.id, y.id
FROM generate_series(1, 53) AS g
CROSS JOIN (VALUES ('WGN-G2B-'), ('WGN-G2T-')) AS v(prefix)
JOIN freight.wagon_types wt ON wt.code = 'NW5'
JOIN freight.yards y ON y.code = 'DJIB_PORT'
WHERE NOT EXISTS (
  SELECT 1 FROM freight.wagons w
  WHERE w.wagon_number = v.prefix || lpad(g::text, 2, '0')
);

-- Couple them, unconditionally re-asserted: a 52-wagon consist would shift the
-- weight-vs-slot boundary the whole group is built to probe.
UPDATE freight.wagons w
SET train_id = t.id,
    sequence_number = g.seq,
    status = 'ASSIGNED',
    current_yard_id = t.current_yard_id
FROM freight.trains t,
     LATERAL (
       SELECT s AS seq,
              CASE WHEN t.code = 'TRN-G2-BASE' THEN 'WGN-G2B-' ELSE 'WGN-G2T-' END
                || lpad(s::text, 2, '0') AS num
       FROM generate_series(1, 53) AS s
     ) g
WHERE t.code IN ('TRN-G2-BASE', 'TRN-G2-TOL')
  AND w.wagon_number = g.num
  AND (w.train_id IS DISTINCT FROM t.id
       OR w.sequence_number IS DISTINCT FROM g.seq
       OR w.status IS DISTINCT FROM 'ASSIGNED');