-- RISK342 upi_bioauth_txn_limit
-- DROP TABLE team_kingkong.tpap_risk342_breaches ;

-- CREATE TABLE team_kingkong.tpap_risk342_breaches AS 
INSERT INTO team_kingkong.tpap_risk342_breaches
SELECT txnid
, json_extract_scalar(request, '$.requestPayload.amount') AS txn_amount
, createdon as txn_time
, dl_last_updated as txn_date 
, json_extract_scalar(request, '$.requestPayload.extendedInfo.isBioAuthTxn') AS is_bioauth
, 'upi_bioauth_txn_limit' as rule_name
, 'IsBioAuth = true & amt > 5000' as breach_reason
FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN date'2026-03-01' AND DATE'2026-03-31'
AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
AND json_extract_scalar(request, '$.requestPayload.extendedInfo.isBioAuthTxn') = 'true'
AND CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) > 5000