mirror of
https://github.com/Tria-plc/edr-platform.git
synced 2026-08-26 18:42:49 +00:00
109 lines
4.7 KiB
SQL
109 lines
4.7 KiB
SQL
-- FLOW-TWO fixture — a MIXED-TYPE consist, for the wagon-pool scenario (TC-08).
|
||
--
|
||
-- Run AFTER seed-import-corridor.sql and seed-g1-train.sql. Idempotent.
|
||
--
|
||
-- WHY A SEPARATE TRAIN
|
||
--
|
||
-- TRN-G1-1 is 53 × NW5 — one wagon type. On that consist "container booking
|
||
-- can't consume flatbed wagons" is untestable: there is only one pool, so the
|
||
-- abstract slot count and the physical stock count are the same number and a
|
||
-- broken pool separation is invisible.
|
||
--
|
||
-- TRN-F2-MIX splits the consist across two types the cargo allow-lists do NOT
|
||
-- share:
|
||
-- 30 × NW5 — the type 20FT/40FT containers ride (seed-import-corridor 2b)
|
||
-- 20 × PW2 — the type E2E_IMP_GRAINS bulk rides (seed-import-corridor 4)
|
||
--
|
||
-- So 50 abstract slots, but a container booking may only ever draw on 30 of
|
||
-- them and a grains booking on 20. A 40-wagon container booking sees "50 free"
|
||
-- from CorridorBudget and must still be refused/split by WagonStockLedger —
|
||
-- which is the whole assertion (wagon-stock-ledger.util.ts header: "money taken
|
||
-- for space that never existed").
|
||
--
|
||
-- LENGTH MUST NOT BIND. NW5 is 13.966 m, PW2 is 17.066 m:
|
||
-- 30 × 13.966 + 20 × 17.066 = 418.98 + 341.32 = 760.3 m
|
||
-- which is EXACTLY over a 760 m loco. The locos below run 900 m so the length
|
||
-- axis stays slack and TC-08 fails only on the pool separation it is testing.
|
||
-- Pull: 9000T against 50 wagons of tare+cargo is far under. Slots and TYPE bind.
|
||
|
||
-- 1. Locomotive pair, 900 m so the mixed consist's length never binds.
|
||
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, 900, 0, y.id
|
||
FROM (VALUES ('LOCO-F2-A'), ('LOCO-F2-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);
|
||
|
||
UPDATE freight.locomotives
|
||
SET max_pull_weight_tons = 9000, max_train_length_meters = 900,
|
||
overage_tolerance_tons = 0,
|
||
current_yard_id = (SELECT id FROM freight.yards WHERE code = 'DJIB_PORT')
|
||
WHERE code IN ('LOCO-F2-A', 'LOCO-F2-B')
|
||
AND (max_pull_weight_tons IS DISTINCT FROM 9000
|
||
OR max_train_length_meters IS DISTINCT FROM 900
|
||
OR overage_tolerance_tons IS DISTINCT FROM 0);
|
||
|
||
-- 2. The train, parked at the corridor origin (a built-train schedule requires it).
|
||
INSERT INTO freight.trains
|
||
(id, code, train_name, capacity_tons, current_yard_id,
|
||
import_train_number, export_train_number)
|
||
SELECT gen_random_uuid(), 'TRN-F2-MIX', 'E2E Flow-2 Mixed Consist', 3500, y.id,
|
||
'9302', '9301'
|
||
FROM freight.yards y
|
||
WHERE y.code = 'DJIB_PORT'
|
||
AND NOT EXISTS (SELECT 1 FROM freight.trains t WHERE t.code = 'TRN-F2-MIX');
|
||
|
||
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-F2-A', 0), ('LOCO-F2-B', 1)) AS v(loco_code, seq)
|
||
JOIN freight.trains t ON t.code = 'TRN-F2-MIX'
|
||
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
|
||
);
|
||
|
||
-- 3a. Thirty NW5 (container-capable) wagons.
|
||
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
|
||
SELECT gen_random_uuid(), 'WGN-F2C-' || lpad(g::text, 2, '0'), wt.id, y.id
|
||
FROM generate_series(1, 30) 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-F2C-' || lpad(g::text, 2, '0')
|
||
);
|
||
|
||
-- 3b. Twenty PW2 (bulk-grains) wagons — NOT container-capable.
|
||
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
|
||
SELECT gen_random_uuid(), 'WGN-F2B-' || lpad(g::text, 2, '0'), wt.id, y.id
|
||
FROM generate_series(1, 20) AS g
|
||
JOIN freight.wagon_types wt ON wt.code = 'PW2'
|
||
JOIN freight.yards y ON y.code = 'DJIB_PORT'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM freight.wagons w
|
||
WHERE w.wagon_number = 'WGN-F2B-' || lpad(g::text, 2, '0')
|
||
);
|
||
|
||
-- 4. Couple all 50, containers first. Unconditionally re-asserted: a prior run
|
||
-- could have left one detached, and a 49-wagon consist shifts TC-08's premise.
|
||
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-F2C-' || lpad(s::text, 2, '0')) AS num, s AS seq
|
||
FROM generate_series(1, 30) AS s
|
||
UNION ALL
|
||
SELECT ('WGN-F2B-' || lpad(s::text, 2, '0')), 30 + s
|
||
FROM generate_series(1, 20) AS s
|
||
) g
|
||
WHERE t.code = 'TRN-F2-MIX'
|
||
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');
|