-- Arrange-data for flows/export_one_time.cy.ts. Idempotent.
-- Run AFTER seed-intercity.sql (reuses its container types, locomotives,
-- built train TRN-E2E-1 and yard distances).
--
--   1. bulk cargo hierarchy: group "E2E Grains" → commodity "E2E Wheat",
--      carried on CW4 covered wagons
--   2. two CW4 wagons coupled onto the train (bulk capacity)
--   3. LIVE export rates for Mojo → Djibouti Port: container (per container)
--      and bulk (per ton) — booking pricing hard-blocks without them

-- 0. Approved exporter profile — picking "Export" in the wizard opens the
-- "Set up your exporter profile" modal unless the company already has an
-- active exporter profile (seed-company.sql only creates the importer).
INSERT INTO freight.company_profiles (id, company_id, type, status, reference)
SELECT gen_random_uuid(), c.id, 'exporter', 'active', 'EXP-E2E-0001'
FROM freight.companies c
WHERE c.tin = '0102030405'
  AND NOT EXISTS (
    SELECT 1 FROM freight.company_profiles p
    WHERE p.company_id = c.id AND p.type = 'exporter'
  );

-- 1a. Cargo type group + commodity.
INSERT INTO freight.cargo_types (id, code, cargo_type_name, is_active)
SELECT gen_random_uuid(), 'E2E_GRAINS', 'E2E Grains', true
WHERE NOT EXISTS (SELECT 1 FROM freight.cargo_types WHERE code = 'E2E_GRAINS');

INSERT INTO freight.cargo_types (id, code, cargo_type_name, parent_group_id, is_active)
SELECT gen_random_uuid(), 'E2E_WHEAT', 'E2E Wheat', g.id, true
FROM freight.cargo_types g
WHERE g.code = 'E2E_GRAINS'
  AND NOT EXISTS (SELECT 1 FROM freight.cargo_types WHERE code = 'E2E_WHEAT');

-- 1b. Wheat rides CW4 covered wagons.
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_WHEAT', 'E2E_GRAINS')
  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
  );

-- 2. Couple two free CW4 wagons onto the train, parked at Mojo with it.
UPDATE freight.wagons w
SET train_id = t.id,
    sequence_number = 100 + sub.rn,
    current_yard_id = (SELECT id FROM freight.yards WHERE code = 'MOJO')
FROM freight.trains t,
     LATERAL (
       SELECT w2.id, row_number() OVER (ORDER BY w2.wagon_number) AS rn
       FROM freight.wagons w2
       JOIN freight.wagon_types wt ON wt.id = w2.wagon_type_id AND wt.code = 'CW4'
       WHERE w2.train_id IS NULL AND w2.deleted_at IS NULL
       ORDER BY w2.wagon_number
       LIMIT 2
     ) sub
WHERE t.code = 'TRN-E2E-1'
  AND w.id = sub.id
  AND NOT EXISTS (
    SELECT 1 FROM freight.wagons wx
    JOIN freight.wagon_types wxt ON wxt.id = wx.wagon_type_id AND wxt.code = 'CW4'
    WHERE wx.train_id = t.id
  );

-- 3. LIVE export rates Mojo → Djibouti Port.
INSERT INTO freight.rates
  (id, rate_type, applies_to, trigger, currency, rate_value, rate_unit, status,
   origin_yard_id, destination_yard_id, proposed_by_staff_id)
SELECT gen_random_uuid(), v.rate_type, v.applies_to, 'ALWAYS', 'USD', v.value,
       v.unit, 'LIVE', a.id, b.id, u.id
FROM (VALUES
        ('CONTAINER_EXPORT', 'CONTAINER', 600, 'PER_CONTAINER'),
        ('BULK_EXPORT', 'BULK', 25, 'PER_TON')
     ) AS v(rate_type, applies_to, value, unit)
JOIN freight.yards a ON a.code = 'MOJO'
JOIN freight.yards b ON b.code = 'DJIB_PORT'
JOIN iam.users u ON u.email = 'operation@edr.local'
WHERE NOT EXISTS (
  SELECT 1 FROM freight.rates r
  WHERE r.rate_type = v.rate_type
    AND r.origin_yard_id = a.id AND r.destination_yard_id = b.id
    AND r.deleted_at IS NULL
);