-- RISK324: oc76c_share_pay_p2m
-- DROP TABLE team_kingkong.tpap_risk324_breaches ; 

-- CREATE TABLE team_kingkong.tpap_risk324_breaches AS 
INSERT INTO team_kingkong.tpap_risk324_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
, 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
, COALESCE(json_extract_scalar(request, '$.requestPayload.merchantGenre'),
    json_extract_scalar(request, '$.requestPayload.extendedInfo.merchantGenre')) AS merchant_genre
, CAST(json_extract_scalar(request, '$.requestPayload.isVerifiedMerchant') AS BOOLEAN) AS is_verified_merchant
, 'oc76c_share_pay_p2m' as rule_name
, 'payee_type = ENTITY & entryPoint = upi_qr_scan_gallery_p2p & amt>2k & mx genre = offline' as breach_reason
FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE'2026-02-01' AND DATE'2026-02-10'
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_p2m'
AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') = 'ENTITY'
AND CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) > 2000
AND COALESCE(json_extract_scalar(request, '$.requestPayload.merchantGenre'),
    json_extract_scalar(request, '$.requestPayload.extendedInfo.merchantGenre')) = 'OFFLINE' 
AND CAST(json_extract_scalar(request, '$.requestPayload.isVerifiedMerchant') AS BOOLEAN) = FALSE