CREATE MATERIALIZED VIEW alinma_bff.book_of_business_portfolios_mv
WITH (
backfill_order=FIXED(olap.service_types_dm -> olap.portfolios_dm, olap.labels_dm -> olap.portfolios_dm, asset_service.currencies_dm -> olap.portfolios_dm)
) AS
WITH portfolio_groups AS (
SELECT
portfolio_id,
account_group_id,
type AS account_group_type
FROM insights.portfolio_to_account_groups_mv
UNION ALL
SELECT
portfolio_id,
CAST(NULL AS VARCHAR) AS account_group_id,
CAST('none' AS VARCHAR) AS account_group_type
FROM olap.portfolios_dm
WHERE
disabled_at IS NULL
)
SELECT
p.portfolio_id,
p.number,
p.base_currency_code,
p.service_type_id,
COALESCE(st.type, 'UNSPECIFIED') AS service_type,
p.opening_date,
p.closing_date,
p.created_at,
pg.account_group_type,
p.status_label_id,
lbl.name_en AS status_name_en,
lbl.name_ar AS status_name_ar,
lbl.color AS status_color,
p.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(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,
COALESCE(ref.reference_identifiers, CAST('[]' AS JSONB)) AS reference_identifiers
FROM olap.portfolios_dm AS p
JOIN portfolio_groups AS pg
ON p.portfolio_id = pg.portfolio_id
LEFT JOIN olap.service_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS st
ON p.service_type_id = st.service_type_id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS lbl
ON p.status_label_id = lbl.label_id
LEFT JOIN asset_service.currencies_dm FOR SYSTEM_TIME AS OF PROCTIME() AS cur
ON p.base_currency_code = cur.code
LEFT JOIN alinma_bff.portfolio_owners_mv AS own
ON p.portfolio_id = own.portfolio_id
LEFT JOIN alinma_bff.portfolio_task_counts_mv AS tc
ON p.portfolio_id = tc.portfolio_id
LEFT JOIN alinma_bff.portfolio_reference_identifiers_mv AS ref
ON p.portfolio_id = ref.portfolio_id
LEFT JOIN insights.position_snapshot_mv AS pos
ON pos.account_group_id = pg.account_group_id
AND pos.position_type = 'POSITION'
AND pos.currency_code = p.base_currency_code
WHERE
p.disabled_at IS NULL