CREATE MATERIALIZED VIEW opportunity.opportunity_resources_mv AS
WITH signals AS (
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_portfolio_balance_increase_mv AS signal_portfolio_balance_increase_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_portfolio_balance_decrease_mv AS signal_portfolio_balance_decrease_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_account_balance_increase_mv AS signal_account_balance_increase_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_account_balance_decrease_mv AS signal_account_balance_decrease_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_portfolio_fx_exposure_mv AS signal_portfolio_fx_exposure_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_portfolio_unrealized_loss_mv AS signal_portfolio_unrealized_loss_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_portfolio_extreme_holding_mv AS signal_portfolio_extreme_holding_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_portfolio_extreme_sector_mv AS signal_portfolio_extreme_sector_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_portfolio_allocation_drift_mv AS signal_portfolio_allocation_drift_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_account_bond_maturity_mv AS signal_account_bond_maturity_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.deposit_maturity_breaches_mv AS deposit_maturity_breaches_mv_next
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.signal_portfolio_days_since_transaction_mv
UNION ALL
SELECT
opportunity_id,
activity_name,
resource_id,
fact_date
FROM opportunity.portfolio_idle_cash_breaches_mv AS portfolio_idle_cash_breaches_mv_next
), trigger_context_source AS (
SELECT
'PORTFOLIO_BALANCE' AS ctx_family,
portfolio_id AS resource_id,
dim_balance_date,
JSONB_BUILD_OBJECT(
'currency',
currency_code,
'portfolio_balance_absolute_change_1d',
CAST(ABS(market_value - prev_market_value) AS VARCHAR),
'portfolio_balance_percent_change_1d',
CAST((
CASE
WHEN market_value >= prev_market_value
THEN (
market_value - prev_market_value
) / prev_market_value
WHEN market_value <> 0
THEN (
prev_market_value - market_value
) / market_value
ELSE 1
END
) AS VARCHAR)
) AS trigger_context
FROM opportunity.portfolio_balance_delta_mv AS portfolio_balance_delta_mv_next
UNION ALL
SELECT
'ACCOUNT_BALANCE',
account_id AS resource_id,
dim_balance_date,
JSONB_BUILD_OBJECT(
'currency',
currency_code,
'account_balance_absolute_change_1d',
CAST(ABS(market_value - prev_market_value) AS VARCHAR),
'account_balance_percent_change_1d',
CAST((
CASE
WHEN market_value >= prev_market_value
THEN (
market_value - prev_market_value
) / prev_market_value
WHEN market_value <> 0
THEN (
prev_market_value - market_value
) / market_value
ELSE 1
END
) AS VARCHAR)
) AS trigger_context
FROM opportunity.account_balance_delta_mv AS account_balance_delta_mv_next
UNION ALL
SELECT
'PORTFOLIO_FX',
portfolio_id AS resource_id,
dim_balance_date,
JSONB_BUILD_OBJECT(
'base_currency',
currency_code,
'currency_exposure_percentage',
CAST(fx_exposure_pct AS VARCHAR)
) AS trigger_context
FROM opportunity.portfolio_fx_exposure_delta_mv AS portfolio_fx_exposure_delta_mv_next
UNION ALL
SELECT
'PORTFOLIO_UNREALIZED_LOSS',
portfolio_id AS resource_id,
dim_balance_date,
JSONB_BUILD_OBJECT('loss_percentage', CAST((
loss_ratio * 100
) AS VARCHAR)) AS trigger_context
FROM opportunity.portfolio_unrealized_loss_delta_mv AS portfolio_unrealized_loss_delta_mv_next
), array_trigger_context_source AS (
SELECT
'PORTFOLIO_EXTREME_HOLDING' AS ctx_family,
opportunity_id,
resource_id,
fact_date,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'asset_type',
asset_type,
'asset_name',
asset_name,
'asset_market_value',
asset_market_value,
'asset_currency_code',
asset_currency_code,
'asset_percentage_value',
asset_percentage_value,
'asset_weight',
asset_weight,
'portfolio_currency_code',
portfolio_currency_code,
'portfolio_market_value',
portfolio_market_value
)
) AS trigger_context
FROM opportunity.portfolio_extreme_holding_breaches_mv AS portfolio_extreme_holding_breaches_mv_next
GROUP BY
opportunity_id,
resource_id,
fact_date
UNION ALL
SELECT
'PORTFOLIO_EXTREME_SECTOR',
opportunity_id,
resource_id,
fact_date,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'sector_id',
taxonomy_node_id,
'sector_percentage_value',
sector_percentage_value,
'sector_weight',
sector_weight
)
) AS trigger_context
FROM opportunity.portfolio_extreme_sector_breaches_mv AS portfolio_extreme_sector_breaches_mv_next
GROUP BY
opportunity_id,
resource_id,
fact_date
UNION ALL
SELECT
'PORTFOLIO_ALLOCATION_DRIFT',
opportunity_id,
resource_id,
fact_date,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'asset_class_id',
taxonomy_node_id,
'current_allocation',
current_allocation,
'benchmark_allocation',
benchmark_allocation,
'drift_percentage',
drift_percentage
)
) AS trigger_context
FROM opportunity.portfolio_allocation_drift_breaches_mv AS portfolio_allocation_drift_breaches_mv_next
GROUP BY
opportunity_id,
resource_id,
fact_date
UNION ALL
SELECT
'ACCOUNT_BOND_MATURITY',
opportunity_id,
resource_id,
fact_date,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'maturity_date',
CAST(maturity_date AS VARCHAR),
'days_to_maturity',
CAST(days_to_maturity AS VARCHAR),
'yield_to_maturity',
CAST(yield_to_maturity AS VARCHAR),
'issuer_name_en',
issuer_name_en,
'issuer_name_ar',
issuer_name_ar
)
) AS trigger_context
FROM opportunity.account_bond_maturity_breaches_mv AS account_bond_maturity_breaches_mv_next
GROUP BY
opportunity_id,
resource_id,
fact_date
UNION ALL
SELECT
'FIXED_DEPOSIT_MATURITY',
opportunity_id,
resource_id,
fact_date,
JSONB_AGG(
CASE
WHEN activity_name = 'GET_DAYS_PAST_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE'
THEN JSONB_BUILD_OBJECT(
'maturity_date',
CAST(maturity_date AS VARCHAR),
'days_past_maturity',
CAST(days_delta AS VARCHAR),
'fixed_deposit_amount',
CAST(deposit_amount AS VARCHAR),
'currency',
currency
)
ELSE JSONB_BUILD_OBJECT(
'maturity_date',
CAST(maturity_date AS VARCHAR),
'days_to_fixed_deposit_maturity',
CAST(days_delta AS VARCHAR),
'fixed_deposit_amount',
CAST(deposit_amount AS VARCHAR),
'currency',
currency
)
END
) AS trigger_context
FROM opportunity.deposit_maturity_breaches_mv AS deposit_maturity_breaches_mv_next
WHERE
activity_name IN (
'GET_DAYS_TO_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_PAST_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE'
)
GROUP BY
opportunity_id,
resource_id,
fact_date
UNION ALL
SELECT
'STRUCTURED_DEPOSIT_MATURITY',
opportunity_id,
resource_id,
fact_date,
JSONB_AGG(
CASE
WHEN activity_name = 'GET_DAYS_PAST_STRUCTURED_DEPOSIT_ACCOUNT_MATURITY_DATE'
THEN JSONB_BUILD_OBJECT(
'maturity_date',
CAST(maturity_date AS VARCHAR),
'days_past_maturity',
CAST(days_delta AS VARCHAR),
'structured_deposit_amount',
CAST(deposit_amount AS VARCHAR),
'currency',
currency
)
ELSE JSONB_BUILD_OBJECT(
'maturity_date',
CAST(maturity_date AS VARCHAR),
'days_to_structured_deposit_maturity',
CAST(days_delta AS VARCHAR),
'structured_deposit_amount',
CAST(deposit_amount AS VARCHAR),
'currency',
currency
)
END
) AS trigger_context
FROM opportunity.deposit_maturity_breaches_mv AS deposit_maturity_breaches_mv_next
WHERE
activity_name IN (
'GET_DAYS_TO_STRUCTURED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_PAST_STRUCTURED_DEPOSIT_ACCOUNT_MATURITY_DATE'
)
GROUP BY
opportunity_id,
resource_id,
fact_date
UNION ALL
SELECT
'PORTFOLIO_DAYS_SINCE_TRANSACTION',
opportunity_id,
resource_id,
fact_date,
JSONB_AGG(JSONB_BUILD_OBJECT('days_since_last_transaction', CAST(days_since AS VARCHAR))) AS trigger_context
FROM opportunity.portfolio_days_since_transaction_breaches_mv
GROUP BY
opportunity_id,
resource_id,
fact_date
UNION ALL
SELECT
'PORTFOLIO_IDLE_CASH',
opportunity_id,
resource_id,
fact_date,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'currency',
currency,
'idle_cash_amount',
CAST(idle_cash_amount AS VARCHAR),
'days_idle_cash_duration',
CAST(interval_to_check_days AS VARCHAR),
'percent_change_value',
CAST(cash_ratio AS VARCHAR)
)
) AS trigger_context
FROM opportunity.portfolio_idle_cash_breaches_mv AS portfolio_idle_cash_breaches_mv_next
GROUP BY
opportunity_id,
resource_id,
fact_date
), matched AS (
SELECT
opportunity_id,
resource_id,
fact_date,
COUNT(*) AS matched_signals,
MAX(
CASE
WHEN activity_name LIKE 'GET_PORTFOLIO_BALANCE_%'
THEN 'PORTFOLIO_BALANCE'
WHEN activity_name LIKE 'GET_INVESTMENT_ACCOUNT_BALANCE_%'
THEN 'ACCOUNT_BALANCE'
WHEN activity_name = 'GET_PORTFOLIO_FOREIGN_CURRENCY_EXPOSURE_PERCENTAGE'
THEN 'PORTFOLIO_FX'
WHEN activity_name = 'GET_PORTFOLIO_UNREALIZED_LOSS_PERCENTAGE'
THEN 'PORTFOLIO_UNREALIZED_LOSS'
WHEN activity_name = 'GET_PORTFOLIO_EXTREME_SINGLE_HOLDING_PERCENTAGE'
THEN 'PORTFOLIO_EXTREME_HOLDING'
WHEN activity_name = 'GET_PORTFOLIO_EXTREME_SINGLE_SECTOR_PERCENTAGE'
THEN 'PORTFOLIO_EXTREME_SECTOR'
WHEN activity_name = 'GET_PORTFOLIO_ALLOCATION_DRIFT_PERCENTAGE'
THEN 'PORTFOLIO_ALLOCATION_DRIFT'
WHEN activity_name = 'GET_ACCOUNT_DAYS_TO_BOND_ASSET_MATURITY_DATE'
THEN 'ACCOUNT_BOND_MATURITY'
WHEN activity_name IN (
'GET_DAYS_TO_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_PAST_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE'
)
THEN 'FIXED_DEPOSIT_MATURITY'
WHEN activity_name IN (
'GET_DAYS_TO_STRUCTURED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_PAST_STRUCTURED_DEPOSIT_ACCOUNT_MATURITY_DATE'
)
THEN 'STRUCTURED_DEPOSIT_MATURITY'
WHEN activity_name = 'GET_PORTFOLIO_DAYS_SINCE_LAST_TRANSACTION'
THEN 'PORTFOLIO_DAYS_SINCE_TRANSACTION'
WHEN activity_name = 'GET_PORTFOLIO_IDLE_CASH_PERCENTAGE'
THEN 'PORTFOLIO_IDLE_CASH'
END
) AS ctx_family
FROM signals
GROUP BY
opportunity_id,
resource_id,
fact_date
), required AS (
SELECT
opportunity_id,
MAX(combine_operator) AS combine_operator,
COUNT(*) AS required_signals
FROM opportunity.opportunity_conditions_mv
GROUP BY
opportunity_id
)
SELECT
m.opportunity_id,
m.resource_id,
m.fact_date,
o.opportunity_resource,
o.name,
o.type_label_id,
o.priority,
o.days_to_expiry,
CAST((
m.fact_date + (
COALESCE(o.days_to_expiry, 30) * INTERVAL '1 DAY'
)
) AS DATE) AS expiry_date,
COALESCE(a.trigger_context, d.trigger_context, CAST('{}' AS JSONB)) AS trigger_context
FROM matched AS m
JOIN required AS r
ON r.opportunity_id = m.opportunity_id
LEFT JOIN trigger_context_source AS d
ON d.resource_id = m.resource_id
AND d.dim_balance_date = m.fact_date
AND d.ctx_family = m.ctx_family
LEFT JOIN array_trigger_context_source AS a
ON a.opportunity_id = m.opportunity_id
AND a.resource_id = m.resource_id
AND a.fact_date = m.fact_date
AND a.ctx_family = m.ctx_family
JOIN opportunity.opportunity_metadata_mv AS o
ON o.opportunity_id = m.opportunity_id
WHERE
(
r.combine_operator = 'OR' AND m.matched_signals >= 1
)
OR (
r.combine_operator = 'AND' AND m.matched_signals = r.required_signals
)