mirror of
https://github.com/Tria-plc/edr-platform.git
synced 2026-08-26 18:42:49 +00:00
183 lines
8.3 KiB
SQL
183 lines
8.3 KiB
SQL
-- FLOW-TWO fixture — the EXPORT-direction, THREE-POOL consist.
|
||
--
|
||
-- Run AFTER seed-import-corridor.sql. Idempotent.
|
||
--
|
||
-- WHY THIS TRAIN EXISTS
|
||
--
|
||
-- The second flow-two batch is written against a "60 wagons = 35 CNT + 20 BLK
|
||
-- + 5 FLT" baseline, running F→A (KALITY → DJIB_PORT). Nothing in the existing
|
||
-- fixtures gives that:
|
||
-- - TRN-G1-1 is 53 × NW5, one pool, parked at DJIB_PORT (import end).
|
||
-- - TRN-F2-MIX is 30 NW5 + 20 PW2 — two pools, also at DJIB_PORT.
|
||
-- - TRN-LEDGER-PW2 is 37 PW2 at KALITY — right end, wrong pools.
|
||
--
|
||
-- TRN-F2-EXP is 60 wagons at KALITY split across THREE cargo-incompatible
|
||
-- types, so "container overflow must not eat bulk wagons" (TC-02) and
|
||
-- "bulk-to-container substitution" (TC-03) are expressible at all. On a
|
||
-- one-pool consist the abstract slot count and the physical stock count are
|
||
-- the same number, and a broken pool separation is invisible.
|
||
--
|
||
-- 35 × NW5 — CNT. The only type 20FT/40FT containers ride
|
||
-- (container_type_wagon_types, seed-import-corridor 2b).
|
||
-- 20 × CW4 — BLK. Carries E2E_IMP_WHEAT (cargo_type_wagon_types, 5b2).
|
||
-- 5 × NW6 — FLT. Allow-listed to NOTHING here, deliberately: it is the
|
||
-- idle-but-unusable pool TC-02 asserts a container booking may
|
||
-- NOT reach for. See section 5.
|
||
--
|
||
-- LENGTH AND PULL MUST NOT BIND — this suite tests SLOTS and TYPE, and a
|
||
-- length rejection would masquerade as a pool rejection:
|
||
-- 35 × 14.000 + 20 × 13.976 + 5 × 18.560 = 490.0 + 279.5 + 92.8 = 862.3 m
|
||
-- so the locos below run 1000 m. Gross weight at full load is roughly
|
||
-- 60 × (70 + ~25) ≈ 5700 T, so the pull is set to 12000 T. Both axes stay
|
||
-- slack; only wagon slots and wagon TYPE can ever bind.
|
||
--
|
||
-- The wagon numbers (WGN-F2X-*) are distinct from every other fixture's, so
|
||
-- this consist never competes for stock with the import specs.
|
||
|
||
-- 1. Locomotive pair at KALITY (the export origin), 1000 m so length is slack.
|
||
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, 12000, 1000, 0, y.id
|
||
FROM (VALUES ('LOCO-F2X-A'), ('LOCO-F2X-B')) 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);
|
||
|
||
-- Re-assert unconditionally: a prior run (or another fixture) could have left
|
||
-- these at different limits, and a 760 m loco silently re-caps the consist.
|
||
UPDATE freight.locomotives
|
||
SET max_pull_weight_tons = 12000, max_train_length_meters = 1000,
|
||
overage_tolerance_tons = 0,
|
||
current_yard_id = (SELECT id FROM freight.yards WHERE code = 'KALITY')
|
||
WHERE code IN ('LOCO-F2X-A', 'LOCO-F2X-B')
|
||
AND (max_pull_weight_tons IS DISTINCT FROM 12000
|
||
OR max_train_length_meters IS DISTINCT FROM 1000
|
||
OR overage_tolerance_tons IS DISTINCT FROM 0);
|
||
|
||
-- 2. The train, parked at KALITY — a built-train schedule requires the consist
|
||
-- to already stand at the schedule's 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-F2-EXP', 'E2E Flow-2 Export Three-Pool', 4200, y.id,
|
||
'9312', '9311'
|
||
FROM freight.yards y
|
||
WHERE y.code = 'KALITY'
|
||
AND NOT EXISTS (SELECT 1 FROM freight.trains t WHERE t.code = 'TRN-F2-EXP');
|
||
|
||
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-F2X-A', 0), ('LOCO-F2X-B', 1)) AS v(loco_code, seq)
|
||
JOIN freight.trains t ON t.code = 'TRN-F2-EXP'
|
||
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
|
||
);
|
||
|
||
-- 3. The three pools of rolling stock, minted fresh at KALITY.
|
||
--
|
||
-- Minted rather than borrowed: the boot fleet at KALITY is already spoken
|
||
-- for by the export-bulk and ledger specs, and a shared wagon that another
|
||
-- spec's schedule has pinned makes this consist silently short.
|
||
|
||
-- 3a. 35 × NW5 — the CONTAINER pool.
|
||
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
|
||
SELECT gen_random_uuid(), 'WGN-F2X-C' || lpad(g::text, 2, '0'), wt.id, y.id
|
||
FROM generate_series(1, 35) AS g
|
||
JOIN freight.wagon_types wt ON wt.code = 'NW5'
|
||
JOIN freight.yards y ON y.code = 'KALITY'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM freight.wagons w
|
||
WHERE w.wagon_number = 'WGN-F2X-C' || lpad(g::text, 2, '0')
|
||
);
|
||
|
||
-- 3b. 20 × CW4 — the BULK pool (E2E_IMP_WHEAT rides CW4).
|
||
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
|
||
SELECT gen_random_uuid(), 'WGN-F2X-B' || lpad(g::text, 2, '0'), wt.id, y.id
|
||
FROM generate_series(1, 20) AS g
|
||
JOIN freight.wagon_types wt ON wt.code = 'CW4'
|
||
JOIN freight.yards y ON y.code = 'KALITY'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM freight.wagons w
|
||
WHERE w.wagon_number = 'WGN-F2X-B' || lpad(g::text, 2, '0')
|
||
);
|
||
|
||
-- 3c. 5 × NW6 — the FLATBED pool. Allow-listed to no cargo in this fixture.
|
||
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
|
||
SELECT gen_random_uuid(), 'WGN-F2X-F' || lpad(g::text, 2, '0'), wt.id, y.id
|
||
FROM generate_series(1, 5) AS g
|
||
JOIN freight.wagon_types wt ON wt.code = 'NW6'
|
||
JOIN freight.yards y ON y.code = 'KALITY'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM freight.wagons w
|
||
WHERE w.wagon_number = 'WGN-F2X-F' || lpad(g::text, 2, '0')
|
||
);
|
||
|
||
-- 4. Couple all 60, CNT then BLK then FLT. Unconditionally re-asserted: a
|
||
-- prior run could have left one detached, and a 59-wagon consist shifts
|
||
-- every scenario's arithmetic by a slot.
|
||
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-F2X-C' || lpad(s::text, 2, '0')) AS num, s AS seq
|
||
FROM generate_series(1, 35) AS s
|
||
UNION ALL
|
||
SELECT ('WGN-F2X-B' || lpad(s::text, 2, '0')), 35 + s
|
||
FROM generate_series(1, 20) AS s
|
||
UNION ALL
|
||
SELECT ('WGN-F2X-F' || lpad(s::text, 2, '0')), 55 + s
|
||
FROM generate_series(1, 5) AS s
|
||
) g
|
||
WHERE t.code = 'TRN-F2-EXP'
|
||
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. The FLATBED pool's allow-list, asserted EMPTY.
|
||
--
|
||
-- TC-02 turns on NW6 being idle AND unreachable: a container booking that
|
||
-- overflows the 35-wagon NW5 pool must be refused even though 5 NW6 slots
|
||
-- stand free. If some other fixture ever allow-lists a container type or a
|
||
-- cargo type onto NW6, that scenario silently starts passing for the wrong
|
||
-- reason — so the link is removed here rather than merely never added.
|
||
DELETE FROM freight.container_type_wagon_types x
|
||
USING freight.wagon_types wt
|
||
WHERE x.wagon_type_id = wt.id AND wt.code = 'NW6';
|
||
|
||
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 wt.code = 'NW6'
|
||
AND ct.code IN ('E2E_IMP_WHEAT', 'E2E_IMP_GRAINS', 'E2E_IMP_AUTO',
|
||
'E2E_IMP_MACHINE', 'E2E_EXP_CEMENT', 'E2E_EXP_FERT');
|
||
|
||
-- 6. Segregation cargo types for TC-10 (bulk commodity segregation).
|
||
--
|
||
-- Fertilizer and cement are separate cargo TYPES sharing the CW4 pool with
|
||
-- wheat. Whether the engine keeps them out of the same physical wagon is
|
||
-- exactly what TC-10 asks — planWagonsWithStock tops off an existing wagon
|
||
-- only when the cargo type matches, so distinct types is the mechanism.
|
||
INSERT INTO freight.cargo_types (id, code, cargo_type_name, is_active)
|
||
SELECT gen_random_uuid(), 'E2E_EXP_FERT', 'E2E Export Fertilizer', true
|
||
WHERE NOT EXISTS (SELECT 1 FROM freight.cargo_types WHERE code = 'E2E_EXP_FERT');
|
||
|
||
INSERT INTO freight.cargo_types (id, code, cargo_type_name, is_active)
|
||
SELECT gen_random_uuid(), 'E2E_EXP_CEMENT', 'E2E Export Cement', true
|
||
WHERE NOT EXISTS (SELECT 1 FROM freight.cargo_types WHERE code = 'E2E_EXP_CEMENT');
|
||
|
||
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 IN ('E2E_EXP_FERT', 'E2E_EXP_CEMENT')
|
||
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
|
||
);
|