-- FLOW-TWO fixture — rates for every sub-leg the segment-reuse specs book.
--
-- seed-import-corridor.sql only rates the four end-to-end pairs its own specs
-- use (DJIB_PORT/NAGAD × MOJO/KALITY). Flow-two books MID-corridor legs
-- (NAGAD→E2E_AWASH, DIRE_DAWA→MOJO, …) and pricing hard-blocks a booking on a
-- pair with no LIVE rate — so every ordered pair of the 6-stop corridor gets a
-- rate here. Distance-free flat values: these specs assert capacity, never money.
--
-- Corridor stop order (see CORRIDOR in import-utils.ts):
--   A=DJIB_PORT  B=NAGAD  C=DIRE_DAWA  D=E2E_AWASH  E=MOJO  F=KALITY
--
-- Idempotent: every insert is guarded by NOT EXISTS on the same key the app
-- resolves rates by, so re-running before every spec is free.

-- Ordered corridor pairs (i < j), as (from, to). A plain (non-ON COMMIT DROP)
-- temp table: db:seedFile sends the whole file as ONE implicit transaction, and
-- it is dropped explicitly at the end so a re-run on the same pooled session
-- doesn't hit "relation already exists".
DROP TABLE IF EXISTS flow2_pairs;
CREATE TEMP TABLE flow2_pairs AS
WITH stops(code, seq) AS (
  VALUES ('DJIB_PORT', 1), ('NAGAD', 2), ('DIRE_DAWA', 3),
         ('E2E_AWASH', 4), ('MOJO', 5), ('KALITY', 6)
)
SELECT a.code AS from_code, b.code AS to_code,
       (b.seq - a.seq) AS legs
FROM stops a JOIN stops b ON b.seq > a.seq;

-- 1. Freight rates. IMPORT direction (a DJ origin) prices as CONTAINER_IMPORT /
--    BULK_IMPORT; a wholly-Ethiopian pair is DOMESTIC and prices as INTERCITY_*.
--    resolveTradeDirectionForBooking derives the direction from the yards'
--    countries, so the rate_type must follow the same rule or pricing 404s.
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 flow2_pairs p
CROSS JOIN LATERAL (VALUES
    (CASE WHEN p.from_code = 'DJIB_PORT' THEN 'CONTAINER_IMPORT'
          ELSE 'INTERCITY_CONTAINER' END,
     CASE WHEN p.from_code = 'DJIB_PORT' THEN 'CONTAINER' ELSE 'INTERCITY' END,
     100 * p.legs, 'PER_CONTAINER'),
    (CASE WHEN p.from_code = 'DJIB_PORT' THEN 'BULK_IMPORT'
          ELSE 'INTERCITY_BULK' END,
     CASE WHEN p.from_code = 'DJIB_PORT' THEN 'BULK' ELSE 'INTERCITY' END,
     5 * p.legs, 'PER_TON')
  ) AS v(rate_type, applies_to, value, unit)
JOIN freight.yards a ON a.code = p.from_code
JOIN freight.yards b ON b.code = p.to_code
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
);

-- 2. Customs-clearance fees on the same pairs, per container type. Only the
--    import pairs need them (a DOMESTIC leg is never customs-cleared), but a
--    customs booking with no fee row hard-blocks at pricing — see the note in
--    seed-import-corridor.sql section 7b.
INSERT INTO freight.rates
  (id, rate_type, applies_to, trigger, currency, rate_value, rate_unit, status,
   trade_direction, container_type_id, origin_yard_id, destination_yard_id,
   proposed_by_staff_id)
SELECT gen_random_uuid(), 'CUSTOMS_CLEARANCE', 'OTHER', 'CUSTOMS_CLEARANCE',
       'USD', 50, 'PER_CONTAINER', 'LIVE', 'IMPORT', ct.id, a.id, b.id, u.id
FROM flow2_pairs p
JOIN freight.yards a ON a.code = p.from_code
JOIN freight.yards b ON b.code = p.to_code
JOIN freight.container_types ct ON ct.size_ft IN (20, 40) AND ct.is_active
JOIN iam.users u ON u.email = 'operation@edr.local'
WHERE p.from_code = 'DJIB_PORT'
  AND NOT EXISTS (
    SELECT 1 FROM freight.rates r
    WHERE r.rate_type = 'CUSTOMS_CLEARANCE'
      AND r.container_type_id = ct.id
      AND r.origin_yard_id = a.id AND r.destination_yard_id = b.id
      AND r.deleted_at IS NULL
  );

-- 3. Yard distances on every pair. Pricing and the available-days lookup both
--    walk yard_distances; a missing pair reads as "not on the network".
INSERT INTO freight.yard_distances (id, from_yard_id, to_yard_id, distance_km)
SELECT gen_random_uuid(), a.id, b.id, 100 * p.legs
FROM flow2_pairs p
JOIN freight.yards a ON a.code = p.from_code
JOIN freight.yards b ON b.code = p.to_code
WHERE NOT EXISTS (
  SELECT 1 FROM freight.yard_distances d
  WHERE d.from_yard_id = a.id AND d.to_yard_id = b.id AND d.deleted_at IS NULL
);

DROP TABLE flow2_pairs;