CREATE MATERIALIZED VIEW alinma_bff.book_of_business_party_clients_mv
WITH (
backfill_order=FIXED(olap.labels_dm -> party.parties, asset_service.currencies_dm -> party.parties)
) AS
WITH active_party_lifecycle AS (
SELECT
acr.party_id,
MIN(lp.base_currency_code) AS base_currency_code,
MIN(lp.onboarding_date) AS onboarding_date,
MIN(lp.segment_id) AS segment_id,
MIN(cr.status_label_id) AS status_label_id,
CAST('ACTIVE' AS VARCHAR) AS customer_relationship_status
FROM alinma_bff.party_active_customer_relationships_mv AS acr
JOIN party.customer_relationships AS cr
ON cr.id = acr.customer_relationship_id
LEFT JOIN party.lifecycle_profiles AS lp
ON lp.customer_relationship_id = acr.customer_relationship_id
AND lp.disabled_at IS NULL
GROUP BY
acr.party_id
), draft_party_lifecycle AS (
SELECT
cr.party_id,
MIN(lp.base_currency_code) AS base_currency_code,
MIN(lp.onboarding_date) AS onboarding_date,
MIN(lp.segment_id) AS segment_id,
MIN(cr.status_label_id) AS status_label_id,
CAST('DRAFT' AS VARCHAR) AS customer_relationship_status
FROM party.customer_relationships AS cr
LEFT JOIN active_party_lifecycle AS apl
ON apl.party_id = cr.party_id
JOIN party.parties AS p
ON p.id = cr.party_id AND p.disabled_at IS NULL
LEFT JOIN party.lifecycle_profiles AS lp
ON lp.customer_relationship_id = cr.id AND lp.disabled_at IS NULL
WHERE
cr.type = 'CUSTOMER'
AND cr.status = 'DRAFT'
AND cr.disabled_at IS NULL
AND CAST(cr.effective_from AS TIMESTAMPTZ) <= CURRENT_TIMESTAMP
AND CAST(COALESCE(cr.effective_to, CAST('9999-12-31' AS DATE)) AS TIMESTAMPTZ) > CURRENT_TIMESTAMP
AND apl.party_id IS NULL
GROUP BY
cr.party_id
), party_client_account_groups AS (
SELECT
party_id,
'account_group_' || MD5(CAST((
party_id || 'all'
) AS BYTEA)) AS account_group_id,
CAST('all' AS VARCHAR) AS type
FROM active_party_lifecycle
UNION ALL
SELECT
party_id,
'account_group_' || MD5(CAST((
party_id || 'restricted'
) AS BYTEA)) AS account_group_id,
CAST('restricted' AS VARCHAR) AS type
FROM active_party_lifecycle
UNION ALL
SELECT
party_id,
'account_group_' || MD5(CAST((
party_id || 'un_restricted'
) AS BYTEA)) AS account_group_id,
CAST('un_restricted' AS VARCHAR) AS type
FROM active_party_lifecycle
UNION ALL
SELECT
party_id,
'account_group_' || MD5(CAST((
party_id || 'none'
) AS BYTEA)) AS account_group_id,
CAST('none' AS VARCHAR) AS type
FROM active_party_lifecycle
), active_party_clients AS (
SELECT
p.id AS party_id,
p.type AS party_type,
pl.base_currency_code,
CAST(NULL AS TIMESTAMPTZ) AS created_at,
pl.onboarding_date,
CAST(NULL AS DATE) AS closing_date,
pl.customer_relationship_status,
pag.type AS account_group_type,
p.display_name,
CAST(NULL AS VARCHAR) AS local_display_name,
CAST(NULL AS VARCHAR) AS preferred_name,
pi.salutation AS prefix,
pi.suffix,
pi.first_name,
pi.last_name,
pl.status_label_id,
stat_lbl.name_en AS status_name_en,
stat_lbl.name_ar AS status_name_ar,
stat_lbl.color AS status_color,
pl.segment_id,
seg_lbl.name_en AS segment_name_en,
seg_lbl.name_ar AS segment_name_ar,
seg_lbl.color AS segment_color,
pl.base_currency_code AS currency_code,
cur.name_en AS currency_name_en,
cur.name_ar AS currency_name_ar,
cur.symbol AS currency_symbol,
COALESCE(pc.portfolio_count, CAST(0 AS BIGINT)) AS portfolio_count,
COALESCE(cc.contact_count, CAST(0 AS BIGINT)) AS contact_count,
COALESCE(tc.active_count, CAST(0 AS BIGINT)) AS task_active_count,
COALESCE(tc.high_priority_count, CAST(0 AS BIGINT)) AS task_high_priority_count,
COALESCE(tc.overdue_count, CAST(0 AS BIGINT)) AS task_overdue_count,
COALESCE(pos.market_value, 0) AS market_value,
COALESCE(pos.fair_value, 0) AS fair_value,
COALESCE(pos.relationship_value, 0) AS relationship_value,
COALESCE(pos.fair_relationship_value, 0) AS fair_relationship_value,
COALESCE(pos.market_value_system_currency, 0) AS market_value_system_currency,
COALESCE(pos.fair_value_system_currency, 0) AS fair_value_system_currency,
COALESCE(pos.relationship_value_system_currency, 0) AS relationship_value_system_currency,
COALESCE(pos.fair_relationship_value_system_currency, 0) AS fair_relationship_value_system_currency,
COALESCE(aum.aum_market_value, 0) AS aum_value,
COALESCE(aum.fair_aum_market_value, 0) AS fair_aum_value,
COALESCE(aum.aum_market_value_system_currency, 0) AS aum_value_system_currency,
COALESCE(aum.fair_aum_market_value_system_currency, 0) AS fair_aum_value_system_currency,
COALESCE(fees.total_fees, 0) AS total_fees,
COALESCE(fees.transactional_fees, 0) AS transactional_fees,
COALESCE(fees.non_transactional_fees, 0) AS non_transactional_fees,
COALESCE(fees.total_fees_system_currency, 0) AS total_fees_system_currency,
COALESCE(fees.transactional_fees_system_currency, 0) AS transactional_fees_system_currency,
COALESCE(fees.non_transactional_fees_system_currency, 0) AS non_transactional_fees_system_currency
FROM party.parties AS p
JOIN active_party_lifecycle AS pl
ON pl.party_id = p.id
JOIN party_client_account_groups AS pag
ON pag.party_id = p.id
LEFT JOIN party.party_individual AS pi
ON pi.party_id = p.id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS stat_lbl
ON pl.status_label_id = stat_lbl.label_id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS seg_lbl
ON pl.segment_id = seg_lbl.label_id
LEFT JOIN asset_service.currencies_dm FOR SYSTEM_TIME AS OF PROCTIME() AS cur
ON pl.base_currency_code = cur.code
LEFT JOIN alinma_bff.party_portfolio_counts_mv AS pc
ON p.id = pc.party_id
LEFT JOIN alinma_bff.party_contact_counts_mv AS cc
ON p.id = cc.party_id
LEFT JOIN alinma_bff.party_task_counts_mv AS tc
ON p.id = tc.party_id
LEFT JOIN insights.position_snapshot_mv AS pos
ON pag.account_group_id = pos.account_group_id
AND pos.position_type = 'POSITION'
AND pos.currency_code = pl.base_currency_code
LEFT JOIN alinma_bff.party_aum_mv AS aum
ON p.id = aum.party_id AND pag.type = aum.account_group_type
LEFT JOIN alinma_bff.party_fee_totals_mv AS fees
ON p.id = fees.party_id AND pag.type = fees.account_group_type
WHERE
p.disabled_at IS NULL
)
SELECT
*
FROM active_party_clients
UNION ALL
SELECT
p.id AS party_id,
p.type AS party_type,
pl.base_currency_code,
CAST(NULL AS TIMESTAMPTZ) AS created_at,
pl.onboarding_date,
CAST(NULL AS DATE) AS closing_date,
pl.customer_relationship_status,
CAST('none' AS VARCHAR) AS account_group_type,
p.display_name,
CAST(NULL AS VARCHAR) AS local_display_name,
CAST(NULL AS VARCHAR) AS preferred_name,
pi.salutation AS prefix,
pi.suffix,
pi.first_name,
pi.last_name,
pl.status_label_id,
stat_lbl.name_en AS status_name_en,
stat_lbl.name_ar AS status_name_ar,
stat_lbl.color AS status_color,
pl.segment_id,
seg_lbl.name_en AS segment_name_en,
seg_lbl.name_ar AS segment_name_ar,
seg_lbl.color AS segment_color,
pl.base_currency_code AS currency_code,
cur.name_en AS currency_name_en,
cur.name_ar AS currency_name_ar,
cur.symbol AS currency_symbol,
CAST(0 AS BIGINT) AS portfolio_count,
COALESCE(cc.contact_count, CAST(0 AS BIGINT)) AS contact_count,
CAST(0 AS BIGINT) AS task_active_count,
CAST(0 AS BIGINT) AS task_high_priority_count,
CAST(0 AS BIGINT) AS task_overdue_count,
CAST(0 AS DECIMAL) AS market_value,
CAST(0 AS DECIMAL) AS fair_value,
CAST(0 AS DECIMAL) AS relationship_value,
CAST(0 AS DECIMAL) AS fair_relationship_value,
CAST(0 AS DECIMAL) AS market_value_system_currency,
CAST(0 AS DECIMAL) AS fair_value_system_currency,
CAST(0 AS DECIMAL) AS relationship_value_system_currency,
CAST(0 AS DECIMAL) AS fair_relationship_value_system_currency,
CAST(0 AS DECIMAL) AS aum_value,
CAST(0 AS DECIMAL) AS fair_aum_value,
CAST(0 AS DECIMAL) AS aum_value_system_currency,
CAST(0 AS DECIMAL) AS fair_aum_value_system_currency,
CAST(0 AS DECIMAL) AS total_fees,
CAST(0 AS DECIMAL) AS transactional_fees,
CAST(0 AS DECIMAL) AS non_transactional_fees,
CAST(0 AS DECIMAL) AS total_fees_system_currency,
CAST(0 AS DECIMAL) AS transactional_fees_system_currency,
CAST(0 AS DECIMAL) AS non_transactional_fees_system_currency
FROM party.parties AS p
JOIN draft_party_lifecycle AS pl
ON pl.party_id = p.id
LEFT JOIN party.party_individual AS pi
ON pi.party_id = p.id
LEFT JOIN alinma_bff.party_contact_counts_mv AS cc
ON p.id = cc.party_id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS stat_lbl
ON pl.status_label_id = stat_lbl.label_id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS seg_lbl
ON pl.segment_id = seg_lbl.label_id
LEFT JOIN asset_service.currencies_dm FOR SYSTEM_TIME AS OF PROCTIME() AS cur
ON pl.base_currency_code = cur.code
WHERE
p.disabled_at IS NULL