SELECT
  o.id,
  o.number,
  o.total_minor,
  COALESCE(SUM(p.amount_minor), 0) AS paid_minor,
  o.total_minor - COALESCE(SUM(p.amount_minor), 0) AS remaining_minor,
  (o.status = 'confirmed' AND o.due_date < now() AND o.total_minor - COALESCE(SUM(p.amount_minor),0) > 0) AS is_overdue,
  o.dispatch_status
FROM orders o
LEFT JOIN payments p ON o.id = p.order_id
GROUP BY o.id;