mirror of
https://github.com/Tria-plc/edr-platform.git
synced 2026-08-26 18:42:49 +00:00
84 lines
4.3 KiB
SQL
84 lines
4.3 KiB
SQL
-- Arrange-data for flows/train_builder_adjust_consist.cy.ts. Idempotent.
|
||
-- Run AFTER seed-import-corridor.sql (KALITY yard, the KALITY→DJIB_PORT export
|
||
-- route via ensureExportRoute()).
|
||
--
|
||
-- A dedicated small-capacity built train at KALITY so the headroom math is
|
||
-- exact and cheap: LOCO-ADJ-A/B pull 120T each, 20T overage tolerance ⇒ the
|
||
-- consist's pull cap is 120 + 20 = 140T. TRN-ADJ-1 starts with 4 coupled CW4
|
||
-- wagons (4 × 24.8T tare = 99.2T, well under cap) plus 2 free spare CW4
|
||
-- wagons at the same yard (WGN-ADJ-S1/S2) to add:
|
||
-- + one spare -> 124.0T (within the 140T cap) -> adjust-consist SUCCEEDS
|
||
-- + both spares -> 148.8T (over the 140T cap) -> adjust-consist REJECTS
|
||
--
|
||
-- Dedicated codes (LOCO-ADJ-*, TRN-ADJ-1, WGN-ADJ-*) so this spec never
|
||
-- competes with the shared KALITY CW4 export pocket other bulk specs draw
|
||
-- from (see cargo-two-wagon-types-breaks-length-budget.md for what happens
|
||
-- when a spec silently shares fleet stock with another).
|
||
|
||
-- 1. Locomotives at KALITY: small pull cap, generous 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, 120, 760, 20, y.id
|
||
FROM (VALUES ('LOCO-ADJ-A'), ('LOCO-ADJ-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);
|
||
|
||
-- Keep limits stable across re-seeds (in case a prior run's row predates this
|
||
-- fixture's numbers).
|
||
UPDATE freight.locomotives
|
||
SET max_pull_weight_tons = 120, max_train_length_meters = 760, overage_tolerance_tons = 20
|
||
WHERE code IN ('LOCO-ADJ-A', 'LOCO-ADJ-B')
|
||
AND (max_pull_weight_tons IS DISTINCT FROM 120
|
||
OR max_train_length_meters IS DISTINCT FROM 760
|
||
OR overage_tolerance_tons IS DISTINCT FROM 20);
|
||
|
||
-- 2. The built train, parked at KALITY (must match the export route's origin
|
||
-- yard for a built-train schedule to be creatable on it).
|
||
INSERT INTO freight.trains (id, code, train_name, capacity_tons, current_yard_id)
|
||
SELECT gen_random_uuid(), 'TRN-ADJ-1', 'E2E Adjust-Consist Carrier', 500, y.id
|
||
FROM freight.yards y WHERE y.code = 'KALITY'
|
||
AND NOT EXISTS (SELECT 1 FROM freight.trains t WHERE t.code = 'TRN-ADJ-1');
|
||
|
||
-- 3. Couple the locomotive pair.
|
||
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-ADJ-A', 0), ('LOCO-ADJ-B', 1)) AS v(loco_code, seq)
|
||
JOIN freight.trains t ON t.code = 'TRN-ADJ-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. Dedicated CW4 wagons at KALITY: 4 coupled onto the train, 2 free spares.
|
||
INSERT INTO freight.wagons (id, wagon_number, wagon_type_id, current_yard_id)
|
||
SELECT gen_random_uuid(), v.num, wt.id, y.id
|
||
FROM (VALUES
|
||
('WGN-ADJ-C1', 1), ('WGN-ADJ-C2', 2), ('WGN-ADJ-C3', 3), ('WGN-ADJ-C4', 4),
|
||
('WGN-ADJ-S1', NULL), ('WGN-ADJ-S2', NULL)
|
||
) AS v(num, seq)
|
||
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 = v.num);
|
||
|
||
-- Couple the C1..C4 quartet onto the train (idempotent re-couple in case a
|
||
-- prior run's adjust-consist test detached one).
|
||
UPDATE freight.wagons w
|
||
SET train_id = t.id, sequence_number = v.seq, status = 'ASSIGNED', current_yard_id = t.current_yard_id
|
||
FROM freight.trains t,
|
||
(VALUES ('WGN-ADJ-C1', 1), ('WGN-ADJ-C2', 2), ('WGN-ADJ-C3', 3), ('WGN-ADJ-C4', 4))
|
||
AS v(num, seq)
|
||
WHERE w.wagon_number = v.num AND t.code = 'TRN-ADJ-1'
|
||
AND (w.train_id IS DISTINCT FROM t.id OR w.sequence_number IS DISTINCT FROM v.seq);
|
||
|
||
-- The 2 spares stay loose (train_id NULL), AVAILABLE, at the train's yard —
|
||
-- unconditionally re-assert every seed (runs AFTER seed-import-corridor.sql,
|
||
-- whose own "re-park the export CW4 pocket" step sweeps any loose CW4 whose
|
||
-- number sorts last into NAGAD — 'WGN-ADJ-S%' sorts after every corridor
|
||
-- fixture code, so a prior run's spares are exactly the kind it would steal).
|
||
UPDATE freight.wagons w
|
||
SET train_id = NULL, sequence_number = NULL, status = 'AVAILABLE',
|
||
current_yard_id = (SELECT id FROM freight.yards WHERE code = 'KALITY')
|
||
WHERE w.wagon_number IN ('WGN-ADJ-S1', 'WGN-ADJ-S2');
|