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

CREATE MATERIALIZED VIEW alinma_bff.party_current_account_membership_mv AS
WITH direct_accounts AS (
  SELECT
    acr.party_id,
    acr.customer_relationship_id,
    inv.entity_id AS account_id,
    oa.is_restricted
  FROM alinma_bff.party_active_customer_relationships_mv AS acr
  JOIN olap.party_involvements_dm AS inv
    ON inv.customer_relationship_id = acr.customer_relationship_id
    AND inv.party_id = acr.party_id
    AND 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
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = inv.entity_id
), portfolio_accounts AS (
  SELECT
    acr.party_id,
    acr.customer_relationship_id,
    atp.account_id,
    oa.is_restricted
  FROM alinma_bff.party_active_customer_relationships_mv AS acr
  JOIN olap.party_involvements_dm AS inv
    ON inv.customer_relationship_id = acr.customer_relationship_id
    AND inv.party_id = acr.party_id
    AND inv.entity_type = 'PORTFOLIO'
    AND inv.involvement_type IN ('PORTFOLIO_HOLDER', 'JOINT_PORTFOLIO_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
  JOIN olap.account_to_portfolios_dm AS atp
    ON atp.portfolio_id = inv.entity_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
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = atp.account_id
), current_account_candidates AS (
  SELECT
    party_id,
    customer_relationship_id,
    account_id,
    is_restricted
  FROM direct_accounts
  UNION ALL
  SELECT
    party_id,
    customer_relationship_id,
    account_id,
    is_restricted
  FROM portfolio_accounts
), current_accounts AS (
  SELECT
    party_id,
    customer_relationship_id,
    account_id,
    is_restricted
  FROM current_account_candidates
  GROUP BY
    party_id,
    customer_relationship_id,
    account_id,
    is_restricted
), party_lifecycle AS (
  SELECT
    acr.party_id,
    acr.customer_relationship_id,
    lp.base_currency_code AS party_currency
  FROM alinma_bff.party_active_customer_relationships_mv AS acr
  LEFT JOIN party.lifecycle_profiles AS lp
    ON lp.customer_relationship_id = acr.customer_relationship_id
    AND lp.disabled_at IS NULL
), account_groups AS (
  SELECT
    party_id,
    customer_relationship_id,
    account_id,
    CAST('all' AS VARCHAR) AS account_group_type
  FROM current_accounts
  UNION ALL
  SELECT
    party_id,
    customer_relationship_id,
    account_id,
    CAST('restricted' AS VARCHAR)
  FROM current_accounts
  WHERE
    is_restricted = TRUE
  UNION ALL
  SELECT
    party_id,
    customer_relationship_id,
    account_id,
    CAST('un_restricted' AS VARCHAR)
  FROM current_accounts
  WHERE
    NOT is_restricted IS TRUE
)
SELECT
  ag.party_id,
  ag.customer_relationship_id,
  ag.account_id,
  ag.account_group_type,
  pl.party_currency
FROM account_groups AS ag
JOIN party_lifecycle AS pl
  ON pl.party_id = ag.party_id
  AND pl.customer_relationship_id = ag.customer_relationship_id