-- TPAP implementation date
SELECT DISTINCT regexp_replace(cast(json_extract(response, '$.messages.cst[0]') as varchar), '"', '') as risk_code
, MIN(DATE(dl_last_updated)) as implementation_date
FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
WHERE DATE(dl_last_updated) >= date'2024-01-01'
AND (lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '')) LIKE '%@paytm%'
or lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '')) like '%@pt%')
AND json_extract_scalar(response, '$.action_recommended') = 'BLOCK'
GROUP BY 1;

-- ONUS implementation date
select json_extract_scalar(actionrecommendedrules,'$.actionRecommendedRules[0]') as strategy_name
, MIN(DATE(dateinserted)) AS implementation_date
FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
WHERE dl_last_updated >= date '2022-01-01'
AND SOURCE = 'PG' AND actionrecommended = 'BLOCK' AND json_extract_scalar(actionrecommendedrules,'$.actionRecommendedRules[0]') IS NOT NULL
GROUP BY 1