CREATE MATERIALIZED VIEW alpheya_experience_bff.book_of_business_clients_mv
WITH (
backfill_order=FIXED(olap.labels_dm -> olap.clients_dm, asset_service.currencies_dm -> olap.clients_dm)
) AS
SELECT
c.id AS client_id,
c.type AS client_type,
c.base_currency_code,
c.created_at,
c.onboarding_date,
c.next_kyc_review_date,
cag.type AS account_group_type,
c.display_name,
c.local_display_name,
c.preferred_name,
c.prefix,
c.suffix,
c.first_name,
c.last_name,
c.customer_identification_file,
c.segment_label_id,
seg_lbl.name_en AS segment_name_en,
seg_lbl.name_ar AS segment_name_ar,
seg_lbl.color AS segment_color,
c.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,
c.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, 0) AS portfolio_count,
COALESCE(cc.contact_count, 0) AS contact_count,
COALESCE(tc.active_count, 0) AS task_active_count,
COALESCE(tc.high_priority_count, 0) AS task_high_priority_count,
COALESCE(lab.market_value, 0) AS market_value,
COALESCE(lab.fair_value, 0) AS fair_value,
COALESCE(lab.relationship_value, 0) AS relationship_value,
COALESCE(lab.fair_relationship_value, 0) AS fair_relationship_value,
COALESCE(lab.market_value_system_currency, 0) AS market_value_system_currency,
COALESCE(lab.fair_value_system_currency, 0) AS fair_value_system_currency,
COALESCE(lab.relationship_value_system_currency, 0) AS relationship_value_system_currency,
COALESCE(lab.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,
COALESCE(ref.reference_identifiers, CAST('[]' AS JSONB)) AS reference_identifiers
FROM olap.clients_dm AS c
JOIN insights.client_to_account_groups_mv AS cag
ON c.id = cag.client_id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS seg_lbl
ON c.segment_label_id = seg_lbl.label_id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS stat_lbl
ON c.status_label_id = stat_lbl.label_id
LEFT JOIN asset_service.currencies_dm FOR SYSTEM_TIME AS OF PROCTIME() AS cur
ON c.base_currency_code = cur.code
LEFT JOIN alpheya_experience_bff.client_portfolio_counts_mv AS pc
ON c.id = pc.client_id
LEFT JOIN alpheya_experience_bff.client_contact_counts_mv AS cc
ON c.id = cc.client_id
LEFT JOIN alpheya_experience_bff.client_task_counts_mv AS tc
ON c.id = tc.client_id
LEFT JOIN alpheya_experience_bff.client_reference_identifiers_mv AS ref
ON c.id = ref.client_id
LEFT JOIN insights.position_snapshot_mv AS lab
ON cag.account_group_id = lab.account_group_id
AND lab.position_type = 'POSITION'
AND lab.currency_code = c.base_currency_code
LEFT JOIN alpheya_experience_bff.client_aum_mv AS aum
ON c.id = aum.client_id AND cag.type = aum.account_group_type
LEFT JOIN alpheya_experience_bff.client_fee_totals_mv AS fees
ON c.id = fees.client_id AND cag.type = fees.account_group_type
WHERE
c.closing_date IS NULL AND c.disabled_at IS NULL