mirror of
https://github.com/Tria-plc/edr-platform.git
synced 2026-08-27 12:00:59 +00:00
38 lines
1.9 KiB
SQL
38 lines
1.9 KiB
SQL
-- PER_ITEM break-bulk cargo types for the bulk_b2/bulk_b3 specs.
|
|
-- Run AFTER seed-import-corridor.sql (needs E2E_IMP_GRAINS + the CW4 fleet).
|
|
-- Idempotent — safe on re-runs and cross-origin before() replays.
|
|
|
|
-- Automobiles: PER_ITEM with a configured physical floor (4 cars per CW4).
|
|
INSERT INTO freight.cargo_types (id, code, cargo_type_name, parent_group_id, unit_of_measure, is_active)
|
|
SELECT gen_random_uuid(), 'E2E_IMP_AUTO', 'E2E Import Automobiles', g.id, 'PER_ITEM', true
|
|
FROM freight.cargo_types g
|
|
WHERE g.code = 'E2E_IMP_GRAINS'
|
|
AND NOT EXISTS (SELECT 1 FROM freight.cargo_types WHERE code = 'E2E_IMP_AUTO');
|
|
|
|
-- Machinery: PER_ITEM with NO items_per_wagon_map — exercises the
|
|
-- tonnage-only fallback in bulkItemWagonsRequired.
|
|
INSERT INTO freight.cargo_types (id, code, cargo_type_name, parent_group_id, unit_of_measure, is_active)
|
|
SELECT gen_random_uuid(), 'E2E_IMP_MACHINE', 'E2E Import Machinery', g.id, 'PER_ITEM', true
|
|
FROM freight.cargo_types g
|
|
WHERE g.code = 'E2E_IMP_GRAINS'
|
|
AND NOT EXISTS (SELECT 1 FROM freight.cargo_types WHERE code = 'E2E_IMP_MACHINE');
|
|
|
|
-- Both ride the corridor's CW4 bulk fleet.
|
|
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_IMP_AUTO', 'E2E_IMP_MACHINE')
|
|
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
|
|
);
|
|
|
|
-- Physical floor: 4 automobiles fit one CW4 regardless of tonnage headroom.
|
|
UPDATE freight.cargo_types ct
|
|
SET items_per_wagon_map = jsonb_build_object(
|
|
(SELECT wt.id::text FROM freight.wagon_types wt WHERE wt.code = 'CW4'), 4)
|
|
WHERE ct.code = 'E2E_IMP_AUTO'
|
|
AND (ct.items_per_wagon_map IS NULL
|
|
OR NOT ct.items_per_wagon_map ? (SELECT wt.id::text FROM freight.wagon_types wt WHERE wt.code = 'CW4'));
|