CREATE MATERIALIZED VIEW opportunity.deposit_maturity_breaches_mv AS
WITH deposits AS (
SELECT
d.account_id,
d.maturity_date,
oa.base_currency_code,
'FIXED' AS deposit_type
FROM olap.fixed_deposit_accounts_dm AS d
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = d.account_id
WHERE
d.disabled_at IS NULL
UNION ALL
SELECT
d.account_id,
d.maturity_date,
oa.base_currency_code,
'STRUCTURED' AS deposit_type
FROM olap.structured_deposit_accounts_dm AS d
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = d.account_id
WHERE
d.disabled_at IS NULL
), deposit_amounts AS (
SELECT
d.account_id,
d.maturity_date,
d.base_currency_code,
d.deposit_type,
SUM(h.market_value) AS deposit_amount
FROM deposits AS d
LEFT JOIN insights.holding_values_latest_mv AS h
ON h.account_id = d.account_id AND h.type = 'ASSET'
GROUP BY
d.account_id,
d.maturity_date,
d.base_currency_code,
d.deposit_type
), conditions AS (
SELECT
opportunity_id,
activity_name,
op,
threshold,
as_of_date,
activity_name IN (
'GET_DAYS_PAST_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_PAST_STRUCTURED_DEPOSIT_ACCOUNT_MATURITY_DATE'
) AS is_past,
CASE
WHEN activity_name IN (
'GET_DAYS_TO_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_PAST_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE'
)
THEN 'FIXED'
ELSE 'STRUCTURED'
END AS deposit_type
FROM opportunity.opportunity_conditions_mv AS opportunity_conditions_mv_next
WHERE
activity_name IN (
'GET_DAYS_TO_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_PAST_FIXED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_TO_STRUCTURED_DEPOSIT_ACCOUNT_MATURITY_DATE',
'GET_DAYS_PAST_STRUCTURED_DEPOSIT_ACCOUNT_MATURITY_DATE'
)
AND NOT as_of_date IS NULL
), matched AS (
SELECT
c.opportunity_id,
d.account_id,
c.as_of_date,
c.activity_name,
d.maturity_date,
d.base_currency_code,
d.deposit_amount,
c.op,
(
d.maturity_date - c.as_of_date
) AS days_delta,
CASE WHEN c.is_past THEN -c.threshold ELSE c.threshold END AS signed_threshold,
c.is_past
FROM deposit_amounts AS d
JOIN conditions AS c
ON c.deposit_type = d.deposit_type
)
SELECT
opportunity_id,
account_id AS resource_id,
as_of_date AS fact_date,
activity_name,
maturity_date,
days_delta,
deposit_amount,
base_currency_code AS currency
FROM matched
WHERE
CASE WHEN is_past THEN days_delta <= -1 ELSE days_delta >= 1 END
AND CASE op
WHEN 'GTE'
THEN days_delta >= signed_threshold
WHEN 'GT'
THEN days_delta > signed_threshold
WHEN 'LTE'
THEN days_delta <= signed_threshold
WHEN 'LT'
THEN days_delta < signed_threshold
WHEN 'EQ'
THEN days_delta = signed_threshold
ELSE FALSE
END