-- RISK323 oc76c_share_pay_p2p
-- DROP TABLE team_kingkong.tpap_risk323_breaches; 

-- CREATE TABLE team_kingkong.tpap_risk323_breaches AS 
INSERT INTO team_kingkong.tpap_risk323_breaches
SELECT txnid
, regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
, regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') AS payee_type
, json_extract_scalar(request, '$.requestPayload.txnType') as txnType
, CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) as txn_amt 
, dl_last_updated as txn_date 
, createdon as txn_time
, json_extract_scalar(request,'$.requestPayload.extendedInfo.entryPoint') AS entry_point
, 'oc76c_share_pay_p2p' as rule_name
, 'payee_type = PERSON & entryPoint = upi_qr_scan_gallery_p2p and amt > 2k' as breach_reason
FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE'2026-01-01' AND DATE'2026-01-31'
AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
AND regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION'
AND json_extract_scalar(request,'$.requestPayload.extendedInfo.entryPoint') = 'upi_qr_scan_gallery_p2p'
AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') = 'PERSON'
AND CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) > 2000