Data model

Vessel  (reference, by imo_number)
  │
  ├── Voyage  (unique on vessel_id + voyage_number)
  │     │
  │     └── Report  (unique on vessel_id + report_type + report_datetime_utc)
  │           │
  │           ├── ReportEvent (cascade)
  │           │     └── EventFuelConsumption (cascade)
  │           │
  │           ├── ReportBunkerRob (cascade)
  │           ├── ReportUpcomingPort (cascade)
  │           ├── ReportFowePeriod (cascade)
  │           ├── ReportScrubberBreakdown (cascade)
  │           ├── BunkerDelivery (cascade)
  │           ├── BunkerBiofuel (cascade)
  │           ├── SofActivity (cascade)
  │           ├── ReportCargo (cascade)
  │           ├── MonthEndBunkerReport (cascade)
  │           ├── BerthingDetails (cascade)
  │           └── ReportDelay (cascade)
  │
  └── (no direct children)

FuelType  (reference, standalone lookup table)

-- Latest noon for each vessel
SELECT DISTINCT ON (vessel_id) *
FROM metaweave_report
WHERE report_type = 'NOON'
ORDER BY vessel_id, report_datetime_utc DESC;

-- Total fuel consumed per voyage
SELECT v.voyage_number,
       SUM(efc.total_consumption) AS total_fuel
FROM metaweave_voyage v
JOIN metaweave_report r ON r.voyage_id = v.voyage_id
JOIN metaweave_report_event e ON e.report_id = r.report_id
JOIN metaweave_event_fuel_consumption efc ON efc.event_id = e.event_id
WHERE v.vessel_id = $1
GROUP BY v.voyage_number;

-- Get the raw payload for one report
SELECT raw_json
FROM metaweave_report
WHERE report_id = $1;