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

CREATE MATERIALIZED VIEW alinma_bff.order_execution_fee_lines_mv AS
SELECT
  order_id,
  kind,
  currency_code,
  SUM(amount) AS amount
FROM (
  SELECT
    ex.order_id,
    COALESCE(
      CASE
        WHEN POSITION('FEE_KIND_' IN line ->> 'kind') = 1
        THEN line ->> 'kind'
        ELSE 'FEE_KIND_' || (
          line ->> 'kind'
        )
      END,
      'FEE_KIND_UNSPECIFIED'
    ) AS kind,
    COALESCE(
      line -> 'amount' -> 'currencyCode' ->> 'value',
      line -> 'amount' -> 'currency_code' ->> 'value'
    ) AS currency_code,
    CAST((
      line -> 'amount' -> 'amount' ->> 'value'
    ) AS DECIMAL) AS amount
  FROM alinma_bff.order_executions_bounded_mv AS ex
  CROSS JOIN JSONB_ARRAY_ELEMENTS(
    CASE
      WHEN JSONB_TYPEOF(ex.fees) = 'array'
      THEN ex.fees
      WHEN NOT ex.commission IS NULL
      THEN JSONB_BUILD_ARRAY(JSONB_BUILD_OBJECT('kind', 'FEE_KIND_COMMISSION', 'amount', ex.commission))
      ELSE CAST('[]' AS JSONB)
    END
  ) AS line
  WHERE
    (
      line -> 'amount' -> 'amount' ->> 'value'
    ) ~ '^-?[0-9]+(\.[0-9]+)?$'
) AS fee_lines
GROUP BY
  order_id,
  kind,
  currency_code
HAVING
  SUM(amount) > 0