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

CREATE MATERIALIZED VIEW alinma_bff.account_primary_client_mv AS
WITH party_external_ids AS (
  SELECT
    entity_id AS party_id,
    value AS cif
  FROM party.reference_identifiers AS reference_identifiers_next
  WHERE
    entity_type = 'party'
    AND key = 'CustomerIdentificationFileId'
    AND disabled_at IS NULL
), primary_owners AS (
  SELECT
    account_id,
    MIN(party_id) AS party_id
  FROM alinma_bff.party_account_owner_candidates_mv
  WHERE
    owner_type = 'PRIMARY'
  GROUP BY
    account_id
  HAVING
    COUNT(*) = 1
), enriched AS (
  SELECT
    owners.account_id,
    owners.party_id,
    JSONB_BUILD_OBJECT(
      'id',
      p.id,
      'name',
      JSONB_BUILD_OBJECT('en', COALESCE(p.display_name, ''), 'ar', ''),
      'client_type',
      CASE p.type WHEN 'INDIVIDUAL' THEN 1 WHEN 'ORGANIZATION' THEN 2 ELSE 0 END,
      'organization',
      1,
      'external_ids',
      JSONB_BUILD_OBJECT('cif', COALESCE(ext.cif, ''))
    ) AS client_json
  FROM primary_owners AS owners
  JOIN party.parties AS p
    ON p.id = owners.party_id AND p.disabled_at IS NULL
  LEFT JOIN party_external_ids AS ext
    ON ext.party_id = p.id
)
SELECT
  account_id,
  (
    JSONB_AGG(client_json ORDER BY party_id) -> 0
  ) AS client_json
FROM enriched
GROUP BY
  account_id