CREATE MATERIALIZED VIEW adib_rm.sdk_activity_enriched_mv
WITH (
backfill_order=FIXED(olap.transaction_types_dm -> adib_rm.sdk_activity_transactions_merged_mv, olap.labels_dm -> adib_rm.sdk_activity_transactions_merged_mv, olap.assets_dm -> adib_rm.sdk_activity_transactions_merged_mv, order_service.orders -> adib_rm.sdk_activity_transactions_merged_mv, olap.assets_dm -> order_service.orders)
) AS
SELECT
ac.client_id,
t.transaction_id AS activity_id,
CAST('TRANSACTION' AS VARCHAR) AS activity_kind,
t.transaction_valuation_date AS activity_date,
t.transaction_type_id,
tt.type AS transaction_type,
tt.name_en AS activity_type_name_en,
tt.name_ar AS activity_type_name_ar,
t.account_id,
CAST(NULL AS VARCHAR) AS portfolio_id,
a.asset_id,
a.name_en AS asset_name_en,
a.name_ar AS asset_name_ar,
a.type AS asset_type,
a.issue_currency_code AS asset_currency_code,
a.ticker AS asset_ticker,
a.isin AS asset_isin,
CAST(NULL AS VARCHAR) AS issuer_name_en,
CAST(NULL AS VARCHAR) AS issuer_name_ar,
oa.asset_id AS order_asset_id,
oa.name_en AS order_asset_name_en,
oa.name_ar AS order_asset_name_ar,
oa.type AS order_asset_type,
oa.issue_currency_code AS order_asset_currency_code,
oa.ticker AS order_asset_ticker,
oa.isin AS order_asset_isin,
t.order_side_label_id AS trade_order_side_label_id,
osl.name_en AS trade_order_side_name_en,
osl.name_ar AS trade_order_side_name_ar,
t.currency_code,
t.gross_value,
t.net_value,
t.quantity,
CAST(NULL AS VARCHAR) AS order_side,
CAST(NULL AS VARCHAR) AS order_status,
CAST(NULL AS DECIMAL) AS filled_quantity,
CAST(NULL AS DECIMAL) AS average_price,
CAST(NULL AS DECIMAL) AS gross_executed_amount,
CAST(NULL AS DECIMAL) AS total_fee,
CAST(NULL AS DECIMAL) AS estimated_gross_amount,
CAST(NULL AS DECIMAL) AS estimated_net_amount,
CAST(NULL AS DECIMAL) AS estimated_fee_amount,
t.order_id,
t.transaction_valuation_timestamp AS event_at,
t.transaction_source,
tp.portfolio_ids
FROM adib_rm.sdk_activity_transactions_merged_mv AS t
JOIN olap.accounts_to_clients_dm AS ac
ON ac.account_id = t.account_id
AND ac.disabled_at IS NULL
AND ac.effective_end_date IS NULL
LEFT JOIN olap.assets_dm FOR SYSTEM_TIME AS OF PROCTIME() AS a
ON a.asset_id = t.asset_id
LEFT JOIN order_service.orders FOR SYSTEM_TIME AS OF PROCTIME() AS o_tx
ON o_tx.id = t.order_id
LEFT JOIN olap.assets_dm FOR SYSTEM_TIME AS OF PROCTIME() AS oa
ON oa.asset_id = o_tx.asset_id
LEFT JOIN olap.labels_dm FOR SYSTEM_TIME AS OF PROCTIME() AS osl
ON osl.label_id = t.order_side_label_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 adib_rm.activity_transaction_portfolios_mv AS tp
ON tp.transaction_id = t.transaction_id
UNION ALL
SELECT
ac.client_id,
oe.id AS activity_id,
CAST('ORDER' AS VARCHAR) AS activity_kind,
CAST(oe.created_at AS DATE) AS activity_date,
CAST(NULL AS VARCHAR) AS transaction_type_id,
CAST(NULL AS VARCHAR) AS transaction_type,
CAST(NULL AS VARCHAR) AS activity_type_name_en,
CAST(NULL AS VARCHAR) AS activity_type_name_ar,
oe.security_account_id AS account_id,
oe.portfolio_id,
oe.asset_id,
oe.asset_name_en,
oe.asset_name_ar,
oe.asset_type,
oe.asset_currency_code,
oe.asset_ticker,
oe.asset_isin,
CAST(NULL AS VARCHAR) AS issuer_name_en,
CAST(NULL AS VARCHAR) AS issuer_name_ar,
CAST(NULL AS VARCHAR) AS order_asset_id,
CAST(NULL AS VARCHAR) AS order_asset_name_en,
CAST(NULL AS VARCHAR) AS order_asset_name_ar,
CAST(NULL AS VARCHAR) AS order_asset_type,
CAST(NULL AS VARCHAR) AS order_asset_currency_code,
CAST(NULL AS VARCHAR) AS order_asset_ticker,
CAST(NULL AS VARCHAR) AS order_asset_isin,
CAST(NULL AS VARCHAR) AS trade_order_side_label_id,
CAST(NULL AS VARCHAR) AS trade_order_side_name_en,
CAST(NULL AS VARCHAR) AS trade_order_side_name_ar,
oe.currency_code,
CAST(NULL AS DECIMAL) AS gross_value,
CAST(NULL AS DECIMAL) AS net_value,
oe.quantity,
oe.order_side,
oe.order_status,
oe.filled_quantity,
oe.average_price,
oe.gross_executed_amount,
oe.total_fee,
oe.estimated_gross_amount,
oe.estimated_net_amount,
oe.estimated_fee_amount,
CAST(NULL AS VARCHAR) AS order_id,
oe.created_at AS event_at,
CAST(NULL AS VARCHAR) AS transaction_source,
CASE
WHEN oe.portfolio_id IS NULL
THEN CAST(ARRAY[] AS VARCHAR[])
ELSE CAST(ARRAY[oe.portfolio_id] AS VARCHAR[])
END AS portfolio_ids
FROM adib_rm.activity_order_events_mv AS oe
JOIN olap.accounts_to_clients_dm AS ac
ON ac.account_id = oe.security_account_id
AND ac.disabled_at IS NULL
AND ac.effective_end_date IS NULL