CREATE MATERIALIZED VIEW alinma_bff.book_of_business_accounts_mv
WITH (
backfill_order=FIXED(olap.product_types_dm -> olap.accounts_dm, olap.labels_dm -> olap.accounts_dm, asset_service.currencies_dm -> olap.accounts_dm)
) AS
SELECT
a.account_id,
a.name AS account_name,
a.number AS account_number,
COALESCE(pt.type, 'UNKNOWN') AS account_type,
a.base_currency_code,
a.opening_date,
a.created_at,
COALESCE(a.is_restricted, FALSE) AS is_restricted,
pt.product_type_id AS product_id,
pt.type AS product_type,
pt.name_en AS product_name_en,
pt.name_ar AS product_name_ar,
a.status_label_id,
lbl.name_en AS status_name_en,
lbl.name_ar AS status_name_ar,
lbl.color AS status_color,
COALESCE(a.system_status, 'ACTIVE') AS system_status,
a.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(own.owners, CAST('[]' AS JSONB)) AS owners,
COALESCE(own.owner_count, 0) AS owner_count,
COALESCE(port.portfolios, CAST('[]' AS JSONB)) AS portfolios,
COALESCE(port.portfolio_count, 0) AS portfolio_count,
COALESCE(tc.active_count, 0) AS task_active_count,
COALESCE(tc.high_priority_count, 0) AS task_high_priority_count,
COALESCE(pos.market_value, 0) AS market_value,
COALESCE(pos.fair_value, 0) AS fair_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,
lf.available_balance,
lf.hold_balance,
lf.purchasing_power,
lf.profit_rate,
lf.profit_accrued,
lf.principal,
COALESCE(fd_dm.maturity_date, fin_dm.maturity_date, sd_dm.maturity_date) AS maturity_date,
lf.auto_renewal_status,
lf.credit_limit,
lf.last_statement_balance,
lf.minimum_payment_due,
lf.payment_due_date,
cc_dm.last_4_digits,
lf.financing_limit,
lf.outstanding_balance,
COALESCE(ref.reference_identifiers, CAST('[]' AS JSONB)) AS reference_identifiers
FROM olap.accounts_dm AS a
LEFT JOIN olap.product_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS pt
ON a.product_type_id = pt.product_type_id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS lbl
ON a.status_label_id = lbl.label_id
LEFT JOIN asset_service.currencies_dm FOR SYSTEM_TIME AS OF PROCTIME() AS cur
ON a.base_currency_code = cur.code
LEFT JOIN alinma_bff.account_owners_mv AS own
ON a.account_id = own.account_id
LEFT JOIN alinma_bff.account_portfolios_mv AS port
ON a.account_id = port.account_id
LEFT JOIN alinma_bff.account_task_counts_mv AS tc
ON a.account_id = tc.account_id
LEFT JOIN alinma_bff.account_reference_identifiers_mv AS ref
ON a.account_id = ref.account_id
LEFT JOIN insights.position_snapshot_mv AS pos
ON pos.account_group_id = 'account_group_' || MD5(CAST((
a.account_id || 'all'
) AS BYTEA))
AND pos.position_type = 'POSITION'
AND pos.currency_code = a.base_currency_code
LEFT JOIN alinma_bff.account_latest_facts_mv AS lf
ON a.account_id = lf.account_id
LEFT JOIN olap.credit_card_accounts_dm AS cc_dm
ON a.account_id = cc_dm.account_id AND cc_dm.disabled_at IS NULL
LEFT JOIN olap.financing_accounts_dm AS fin_dm
ON a.account_id = fin_dm.account_id AND fin_dm.disabled_at IS NULL
LEFT JOIN olap.fixed_deposit_accounts_dm AS fd_dm
ON a.account_id = fd_dm.account_id AND fd_dm.disabled_at IS NULL
LEFT JOIN olap.structured_deposit_accounts_dm AS sd_dm
ON a.account_id = sd_dm.account_id AND sd_dm.disabled_at IS NULL
WHERE
a.closing_date IS NULL AND a.disabled_at IS NULL