mirror of
https://github.com/Tria-plc/edr-platform.git
synced 2026-08-26 18:42:49 +00:00
123f925cc1
- Implemented G9·S38 tests for customer self-haul truck assignments, ensuring compliance with container limits and truck assignments. - Added G9·S39 tests for last-mile delivery, self-haul, and yard pickup, verifying independent paths for multiple bookings on the same train. - Created seed data for Group 1 and Group 2 scenarios, ensuring proper setup for weight and capacity tests. - Updated booking interface to deprecate in favor of for better clarity in allocations.
175 lines
7.8 KiB
SQL
175 lines
7.8 KiB
SQL
-- Arrange-data for the GROUP 1 visual scenario specs (flows/g1_*.cy.ts).
|
||
-- Run AFTER seed-import-corridor.sql (it provides the corridor yards, the
|
||
-- 20FT/40FT container types + NW5 allow-list, the yard distances and the LIVE
|
||
-- CONTAINER_IMPORT rates every booking here prices against). Idempotent.
|
||
--
|
||
-- WHY A BUILT TRAIN
|
||
--
|
||
-- The Group 1 scenarios are written against a 53-wagon train. A loco-pair
|
||
-- schedule cannot hold that number: syncScheduleMaxWagons recomputes
|
||
-- max_wagons as floor(locoLength / shortest active wagon length), which the
|
||
-- corridor fixture deliberately pins at floor(760 / 13.966) = 54. A BUILT
|
||
-- train is exempt — booking-batch.service.ts:4152 takes the physical consist
|
||
-- count first and only falls back to the loco-derived figure:
|
||
--
|
||
-- const maxWagons = physicalWagons ?? capacityLimits(loco).base.wagons;
|
||
--
|
||
-- So 53 physically coupled wagons IS the capacity, and it survives the tick.
|
||
-- This is also what makes the specs' visual config phase load-bearing rather
|
||
-- than decorative: the consist staff marshal is the number the engine fills.
|
||
--
|
||
-- WEIGHT MUST NOT BIND IN GROUP 1
|
||
--
|
||
-- Group 1 tests slot arithmetic (fill, split, waitlist, priority). Weight is
|
||
-- Group 2's axis and must stay slack here or a scenario would fail for the
|
||
-- wrong reason. The locos below pull 9000T; a full 53-wagon board at the 10T
|
||
-- default VGM the specs book weighs
|
||
-- 53 × 22.4T tare + 106 × 10T cargo = 1187.2 + 1060 = 2247.2T — well under.
|
||
-- Length: 53 × 13.966 = 740.2m under the 760m cap. Slots bind, nothing else.
|
||
--
|
||
-- DEDICATED CODES
|
||
--
|
||
-- LOCO-G1-*, TRN-G1-1, WGN-G1-* are used by NO other spec. seed-import-corridor
|
||
-- re-parks loose NW5/CW4 stock between runs and sweeps whichever wagons sort
|
||
-- last into other yards, so sharing the general pool would let a sibling spec
|
||
-- steal this consist mid-run (see the header of seed-adjust-consist.sql for the
|
||
-- same hazard). Coupled wagons (train_id set) are never swept.
|
||
|
||
-- 1. Locomotive pair at DJIB_PORT — the import corridor's origin yard, which a
|
||
-- built-train schedule requires the train to be parked at. 9000T pull and 760m
|
||
-- keep both non-slot axes slack (see header). No overage tolerance configured:
|
||
-- Group 1 must never be rescued by tolerance, and Group 2 sets its own.
|
||
INSERT INTO freight.locomotives
|
||
(id, code, max_pull_weight_tons, max_train_length_meters,
|
||
overage_tolerance_tons, current_yard_id)
|
||
SELECT gen_random_uuid(), v.code, 9000, 760, 0, y.id
|
||
FROM (VALUES ('LOCO-G1-A'), ('LOCO-G1-B')) 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);
|
||
|
||
-- Re-assert limits every seed: a prior run's row may predate these numbers.
|
||
UPDATE freight.locomotives
|
||
SET max_pull_weight_tons = 9000, max_train_length_meters = 760,
|
||
overage_tolerance_tons = 0,
|
||
current_yard_id = (SELECT id FROM freight.yards WHERE code = 'DJIB_PORT')
|
||
WHERE code IN ('LOCO-G1-A', 'LOCO-G1-B')
|
||
AND (max_pull_weight_tons IS DISTINCT FROM 9000
|
||
OR max_train_length_meters IS DISTINCT FROM 760
|
||
OR overage_tolerance_tons IS DISTINCT FROM 0);
|
||
|
||
-- 2. The built train, parked 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(), 'TRN-G1-1', 'E2E Group-1 Container Carrier', 3500, y.id,
|
||
'9202', '9201'
|
||
FROM freight.yards y
|
||
WHERE y.code = 'DJIB_PORT'
|
||
AND NOT EXISTS (SELECT 1 FROM freight.trains t WHERE t.code = 'TRN-G1-1');
|
||
|
||
-- 3. Couple the locomotive pair to the 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 ('LOCO-G1-A', 0), ('LOCO-G1-B', 1)) AS v(loco_code, seq)
|
||
JOIN freight.trains t ON t.code = 'TRN-G1-1'
|
||
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. Fifty-three dedicated NW5 flat wagons at DJIB_PORT. NW5 is the type the
|
||
-- 20FT/40FT container types are allow-listed onto (seed-import-corridor 2b),
|
||
-- so a container booking can actually be allocated onto them.
|
||
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
|
||
SELECT gen_random_uuid(), 'WGN-G1-' || lpad(g::text, 2, '0'), wt.id, y.id
|
||
FROM generate_series(1, 53) AS g
|
||
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 = 'WGN-G1-' || lpad(g::text, 2, '0')
|
||
);
|
||
|
||
-- Couple all 53 onto the train, in order. Unconditionally re-asserted: a prior
|
||
-- run's adjust-consist or an allocation left mid-flight could have detached one,
|
||
-- and a 52-wagon consist would silently shift every scenario's arithmetic by a
|
||
-- slot (the exact-fit cases in S1/S2 would stop being exact).
|
||
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 ('WGN-G1-' || lpad(s::text, 2, '0')) AS num, s AS seq
|
||
FROM generate_series(1, 53) AS s
|
||
) g
|
||
WHERE t.code = 'TRN-G1-1'
|
||
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');
|
||
|
||
-- 5. A second built train for the multi-schedule scenarios (Group 4 reuses this
|
||
-- fixture; Group 1 never schedules it). Same 53-wagon shape so "two identical
|
||
-- trains on one day" is a true statement about capacity.
|
||
INSERT INTO freight.locomotives
|
||
(id, code, max_pull_weight_tons, max_train_length_meters,
|
||
overage_tolerance_tons, current_yard_id)
|
||
SELECT gen_random_uuid(), v.code, 9000, 760, 0, y.id
|
||
FROM (VALUES ('LOCO-G1-C'), ('LOCO-G1-D')) 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);
|
||
|
||
INSERT INTO freight.trains
|
||
(id, code, train_name, capacity_tons, current_yard_id,
|
||
import_train_number, export_train_number)
|
||
SELECT gen_random_uuid(), 'TRN-G1-2', 'E2E Group-1 Container Carrier II', 3500, y.id,
|
||
'9204', '9203'
|
||
FROM freight.yards y
|
||
WHERE y.code = 'DJIB_PORT'
|
||
AND NOT EXISTS (SELECT 1 FROM freight.trains t WHERE t.code = 'TRN-G1-2');
|
||
|
||
INSERT INTO freight.train_locomotives (id, train_id, locomotive_id, sequence_no)
|
||
SELECT gen_random_uuid(), t.id, l.id, v.seq
|
||
FROM (VALUES ('LOCO-G1-C', 0), ('LOCO-G1-D', 1)) AS v(loco_code, seq)
|
||
JOIN freight.trains t ON t.code = 'TRN-G1-2'
|
||
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
|
||
);
|
||
|
||
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
|
||
SELECT gen_random_uuid(), 'WGN-G2-' || lpad(g::text, 2, '0'), wt.id, y.id
|
||
FROM generate_series(1, 53) AS g
|
||
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 = 'WGN-G2-' || lpad(g::text, 2, '0')
|
||
);
|
||
|
||
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 ('WGN-G2-' || lpad(s::text, 2, '0')) AS num, s AS seq
|
||
FROM generate_series(1, 53) AS s
|
||
) g
|
||
WHERE t.code = 'TRN-G1-2'
|
||
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');
|
||
|
||
-- NOTE on S7's government queue-jump: nothing is needed here. The bonus
|
||
-- (GOVERNMENT_PRIORITY_BONUS = 50_000, rule-engine.service.ts:258) keys off
|
||
-- `contracts.is_government` / `government_institution`, which the booking
|
||
-- inherits (contract-booking.service.ts:268) — so it is set per contract by
|
||
-- seedImportContract({ government: true }), not by fleet arrange-data.
|