RWM Console cluster: risingwave-ai-hub-int.ai-hub-rwm.svc.cluster.local

CREATE MATERIALIZED VIEW alpheya_experience_bff.portfolio_owners_mv AS
WITH owner_rows AS (
  SELECT
    cp.portfolio_id,
    c.id AS owner_id,
    c.display_name,
    c.local_display_name,
    c.preferred_name,
    cp.owner_type
  FROM olap.clients_portfolios_dm AS cp
  JOIN olap.clients_dm AS c
    ON c.id = cp.client_id AND c.closing_date IS NULL
  WHERE
    cp.disabled_at IS NULL AND cp.effective_end_date IS NULL
  UNION ALL
  SELECT
    ptp.portfolio_id,
    p.id AS owner_id,
    p.display_name,
    CAST(NULL AS VARCHAR) AS local_display_name,
    CAST(NULL AS VARCHAR) AS preferred_name,
    ptp.involvement_type AS owner_type
  FROM alpheya_experience_bff.party_active_portfolio_involvements_mv AS ptp
  JOIN party.parties AS p
    ON p.id = ptp.party_id AND p.disabled_at IS NULL
)
SELECT
  portfolio_id,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'id',
      owner_id,
      'displayName',
      display_name,
      'localDisplayName',
      local_display_name,
      'preferredName',
      preferred_name,
      'ownerType',
      owner_type
    ) ORDER BY owner_id
  ) AS owners,
  CAST(COUNT(*) AS INT) AS owner_count
FROM owner_rows
GROUP BY
  portfolio_id