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

CREATE MATERIALIZED VIEW alinma_bff.party_account_owner_candidates_mv AS
WITH owner_candidates AS (
  SELECT
    inv.entity_id AS account_id,
    inv.party_id,
    CASE
      WHEN inv.involvement_type = 'ACCOUNT_HOLDER'
      THEN 'PRIMARY'
      ELSE 'SECONDARY'
    END AS owner_type
  FROM olap.party_involvements_dm AS inv
  JOIN alinma_bff.party_active_customer_relationships_mv AS acr
    ON acr.party_id = inv.party_id
    AND acr.customer_relationship_id = inv.customer_relationship_id
  WHERE
    inv.entity_type = 'ACCOUNT'
    AND inv.involvement_type IN ('ACCOUNT_HOLDER', 'JOINT_ACCOUNT_HOLDER')
    AND inv.status = 'ACTIVE'
    AND inv.disabled_at IS NULL
    AND CAST(inv.effective_from AS TIMESTAMPTZ) <= CURRENT_TIMESTAMP
    AND CAST(COALESCE(inv.effective_to, CAST('9999-12-31' AS DATE)) AS TIMESTAMPTZ) > CURRENT_TIMESTAMP
  UNION ALL
  SELECT
    atp.account_id,
    inv.party_id,
    CASE
      WHEN inv.involvement_type = 'PORTFOLIO_HOLDER'
      THEN 'PRIMARY'
      ELSE 'SECONDARY'
    END AS owner_type
  FROM alinma_bff.party_active_portfolio_involvements_mv AS inv
  JOIN olap.account_to_portfolios_dm AS atp
    ON atp.portfolio_id = inv.portfolio_id
    AND atp.disabled_at IS NULL
    AND CAST(atp.effective_start_date AS TIMESTAMPTZ) <= CURRENT_TIMESTAMP
    AND CAST(COALESCE(atp.effective_end_date, CAST('9999-12-31' AS DATE)) AS TIMESTAMPTZ) > CURRENT_TIMESTAMP
)
SELECT
  account_id,
  party_id,
  MIN(owner_type) AS owner_type
FROM owner_candidates
GROUP BY
  account_id,
  party_id