Shipments

1-- core.cargoson.shipments
2-- Every booking as a shipment: lane, carrier, service, agreed freight price,
3-- goods weight, and how its transit went against the carrier's estimate.
4-- Grain: one row per cargoson_account x booking_reference.
5-- Currency: freight_currency, per booking. Never sum freight_price across it.
6-- Depends on: staging.cargoson.booking, staging.cargoson.booking_row
7--
8-- This is the shipment-grain model the other reports read. Classify once here.
9--
10-- STATUS. Cargoson has two status fields. `status` is the booking workflow
11-- (new, requested, quoted, booked, withdrawn, declined, lost, rejected,
12-- processing); `latest_status` adds the transport milestones (confirmed,
13-- collected, delivered, completed). shipment_status reads latest_status first and
14-- falls back to status:
15--   delivered   delivered or completed
16--   in_transit  collected
17--   cancelled   withdrawn, declined, lost or rejected
18--   open        everything else (new, requested, quoted, saved, booked,
19--               processing, confirmed)
20-- A status Cargoson adds later lands in open; check shipment_status = 'open'
21-- against your oldest bookings now and then.
22--
23-- FREIGHT PRICE is the price of the offer the booking was made on: what was
24-- agreed when booking, not the carrier's final invoice. Cancelled shipments keep
25-- their price in freight_price but contribute nothing to freight_cost, which is
26-- the column to sum.
27--
28-- Goods lines are aggregated to the booking BEFORE the join, so a booking with
29-- six pallet lines still contributes its freight price once.
30
31WITH rows_per_booking AS (
32    SELECT
33        cargoson_account,
34        booking_reference,
35        COUNT(*)        AS goods_lines,
36        SUM(quantity)   AS packages,
37        SUM(weight)     AS total_weight,
38        SUM(cbm)        AS total_cbm,
39        SUM(ldm)        AS total_ldm
40    FROM {{staging.cargoson.booking_row}}