RWM Console cluster: risingwave-alinma.alinma-rw.svc.cluster.local

CREATE MATERIALIZED VIEW alinma_bff.order_execution_summaries_mv AS
WITH execution_totals AS (
  SELECT
    ex.order_id,
    SUM(CAST(ex.quantity AS DECIMAL)) AS filled_quantity,
    SUM(
      CAST(ex.quantity AS DECIMAL) * CAST((
        ex.price -> 'amount' ->> 'value'
      ) AS DECIMAL)
    ) AS total_amount,
    MAX(
      COALESCE(ex.price -> 'currencyCode' ->> 'value', ex.price -> 'currency_code' ->> 'value')
    ) AS currency_code
  FROM alinma_bff.order_executions_bounded_mv AS ex
  GROUP BY
    ex.order_id
)
SELECT
  t.order_id,
  JSONB_BUILD_OBJECT(
    'filledQuantity',
    JSONB_BUILD_OBJECT('value', CAST(MAX(t.filled_quantity) AS VARCHAR)),
    'averagePrice',
    JSONB_BUILD_OBJECT(
      'currencyCode',
      JSONB_BUILD_OBJECT('value', COALESCE(MAX(t.currency_code), '')),
      'amount',
      JSONB_BUILD_OBJECT(
        'value',
        CASE
          WHEN MAX(t.filled_quantity) > 0
          THEN CAST((
            MAX(t.total_amount) / MAX(t.filled_quantity)
          ) AS VARCHAR)
          ELSE '0'
        END
      )
    ),
    'totalAmount',
    JSONB_BUILD_OBJECT(
      'currencyCode',
      JSONB_BUILD_OBJECT('value', COALESCE(MAX(t.currency_code), '')),
      'amount',
      JSONB_BUILD_OBJECT('value', CAST(MAX(t.total_amount) AS VARCHAR))
    ),
    'totalFees',
    JSONB_BUILD_OBJECT(
      'currencyCode',
      JSONB_BUILD_OBJECT('value', COALESCE(MAX(t.currency_code), '')),
      'amount',
      JSONB_BUILD_OBJECT('value', CAST(COALESCE(SUM(fl.amount), 0) AS VARCHAR))
    ),
    'fees',
    COALESCE(
      JSONB_AGG(
        JSONB_BUILD_OBJECT(
          'kind',
          fl.kind,
          'amount',
          JSONB_BUILD_OBJECT(
            'currencyCode',
            JSONB_BUILD_OBJECT('value', COALESCE(t.currency_code, '')),
            'amount',
            JSONB_BUILD_OBJECT('value', CAST(fl.amount AS VARCHAR))
          )
        ) ORDER BY fl.kind
      ) FILTER(WHERE
        NOT fl.order_id IS NULL),
      CAST('[]' AS JSONB)
    )
  ) AS execution_summary
FROM execution_totals AS t
LEFT JOIN alinma_bff.order_execution_fee_lines_mv AS fl
  ON fl.order_id = t.order_id
GROUP BY
  t.order_id