CREATE MATERIALIZED VIEW opportunity.opportunity_conditions_mv AS
SELECT
o.opportunity_id,
COALESCE(cond ->> 'operator', 'AND') AS combine_operator,
leaf ->> 'activity_name' AS activity_name,
leaf ->> 'check' AS op,
CAST(REPLACE(leaf ->> 'value', ',', '') AS DECIMAL) AS threshold,
CAST((
leaf ->> 'interval_to_check_days'
) AS INT) AS interval_to_check_days,
o.as_of_date
FROM crm_service.opportunities_dm AS o, UNNEST(o.conditions) AS cond, JSONB_ARRAY_ELEMENTS(COALESCE(cond -> 'conditions', JSONB_BUILD_ARRAY(cond))) AS leaf
WHERE
o.status = 'ENABLED'