CREATE MATERIALIZED VIEW alinma_bff.book_of_business_transactions_mv
WITH (
backfill_order=FIXED(olap.transaction_types_dm -> olap.transactions_dm, olap.trade_transactions_dm -> olap.transactions_dm, olap.fee_transactions_dm -> olap.transactions_dm, olap.income_transactions_dm -> olap.transactions_dm)
) AS
WITH entity_accounts AS (
SELECT DISTINCT
entity_type,
entity_id,
account_group_type,
account_id
FROM (
SELECT
CAST('CLIENT' AS VARCHAR) AS entity_type,
cag.client_id AS entity_id,
cag.type AS account_group_type,
atag.account_id
FROM insights.client_to_account_groups_mv AS cag
JOIN insights.account_to_account_groups_mv AS atag
ON atag.account_group_id = cag.account_group_id AND atag.effective_end_date IS NULL
UNION ALL
SELECT
CAST('PARTY' AS VARCHAR) AS entity_type,
pag.party_id AS entity_id,
pag.type AS account_group_type,
atag.account_id
FROM insights.party_to_account_groups_mv AS pag
JOIN insights.account_to_account_groups_mv AS atag
ON atag.account_group_id = pag.account_group_id AND atag.effective_end_date IS NULL
UNION ALL
SELECT
CAST('ACCOUNT' AS VARCHAR) AS entity_type,
oa.account_id AS entity_id,
CAST('all' AS VARCHAR) AS account_group_type,
oa.account_id
FROM insights.open_accounts_mv AS oa
UNION ALL
SELECT
CAST('ACCOUNT' AS VARCHAR) AS entity_type,
oa.account_id AS entity_id,
CAST(CASE WHEN oa.is_restricted THEN 'restricted' ELSE 'un_restricted' END AS VARCHAR) AS account_group_type,
oa.account_id
FROM insights.open_accounts_mv AS oa
UNION ALL
SELECT
CAST('PORTFOLIO' AS VARCHAR) AS entity_type,
pag.portfolio_id AS entity_id,
pag.type AS account_group_type,
atag.account_id
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN insights.account_to_account_groups_mv AS atag
ON atag.account_group_id = pag.account_group_id AND atag.effective_end_date IS NULL
) AS scopes
)
SELECT
ea.entity_type,
ea.entity_id,
ea.account_group_type,
t.transaction_id,
t.account_id,
t.asset_id,
COALESCE(tt.type, 'OTHER') AS transaction_type,
CAST(NULL AS VARCHAR) AS status_id,
tr.order_side_label_id AS side_id,
t.currency_code,
t.quantity,
CAST(NULL AS DECIMAL) AS unit_price,
t.gross_value,
t.net_value,
COALESCE(t.external_reference, t.transaction_id) AS external_reference,
t.transaction_valuation_timestamp AS creation_timestamp,
t.transaction_valuation_timestamp AS valuation_timestamp,
CAST(t.transaction_settlement_date AS TIMESTAMPTZ) AS settlement_timestamp,
t.transaction_type_id AS type_id,
t.order_id,
tt.name_en AS description,
COALESCE(fee.source_asset_id, inc.source_asset_id) AS source_asset_id,
COALESCE(fee.source_account_id, inc.source_account_id) AS source_account_id,
fee.fee_type,
CAST(NULL AS VARCHAR) AS fee_currency_code,
CAST(NULL AS DECIMAL) AS fee_amount,
CAST(NULL AS DECIMAL) AS fee_amount_system_currency,
tr.order_side_label_id AS order_side_id,
CAST('[]' AS JSONB) AS custom_fields
FROM entity_accounts AS ea
JOIN olap.transactions_dm AS t
ON t.account_id = ea.account_id
LEFT JOIN olap.transaction_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS tt
ON tt.transaction_type_id = t.transaction_type_id
LEFT JOIN olap.trade_transactions_dm FOR SYSTEM_TIME AS OF PROCTIME() AS tr
ON tr.transaction_id = t.transaction_id
LEFT JOIN olap.fee_transactions_dm FOR SYSTEM_TIME AS OF PROCTIME() AS fee
ON fee.transaction_id = t.transaction_id
LEFT JOIN olap.income_transactions_dm FOR SYSTEM_TIME AS OF PROCTIME() AS inc
ON inc.transaction_id = t.transaction_id