-- Terminal 1: Subscribe to notifications
LISTEN order_updates;

-- Terminal 2: Send notification on order update
CREATE OR REPLACE FUNCTION notify_order_update()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM PG_NOTIFY(
    'order_updates',
    JSON_BUILD_OBJECT(
      'order_id', NEW.order_id,
      'old_status', OLD.status,
      'new_status', NEW.status,
      'changed_at', NOW()
    )::TEXT
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_status_notify
  AFTER UPDATE OF status ON orders
  FOR EACH ROW
  WHEN (OLD.status IS DISTINCT FROM NEW.status)
  EXECUTE FUNCTION notify_order_update();