-- RISK316 oc_120_p2m_intent_non_verified_offline_2k
DROP TABLE team_kingkong.tpap_risk316_breaches;
CREATE TABLE team_kingkong.tpap_risk316_breaches AS
-- INSERT INTO team_kingkong.tpap_risk316_breaches
SELECT DISTINCT B.*, C.category, C.initiationMode, C.isVerifiedMerchant, C.merchantGenre
, D.payerType, D.payeeType
, IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
, 'upi_oc141_mcc7995_betting_v3' AS rule_name
, 'Breached: initmode in (04,05) & IsverifiedMerchant = False & MerchantGenre = Offline & amt > 2k' AS breach_reason
FROM
(SELECT txn_id,
MAX(CASE WHEN participant_type = 'PAYER' THEN vpa END) AS payer_vpa,
MAX(CASE WHEN participant_type = 'PAYEE' THEN vpa END) AS payee_vpa,
MAX(DATE(created_on)) as txn_date,
MAX(amount) AS txn_amount,
MAX(created_on) AS txn_time
FROM switch.txn_participants_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE'2025-07-01' AND DATE'2025-08-06'
AND DATE(created_on) BETWEEN DATE'2025-07-01' AND DATE'2025-08-06'
GROUP BY 1
HAVING MAX(amount) > 2000)B
inner join
(select txn_id, category
, json_extract_scalar(extended_info, '$.initiationMode') as initiationMode
, json_extract_scalar(extended_info, '$.isVerifiedMerchant') as isVerifiedMerchant
, json_extract_scalar(extended_info, '$.merchantGenre') as merchantGenre
from switch.txn_info_snapshot_v3
where DATE(dl_last_updated) BETWEEN DATE'2025-07-01' AND DATE'2025-08-06'
and DATE(created_on) BETWEEN DATE'2025-07-01' AND DATE'2025-08-06'
AND json_extract_scalar(extended_info, '$.initiationMode') IN ('04', '05')
AND json_extract_scalar(extended_info, '$.merchantGenre') = 'OFFLINE'
AND json_extract_scalar(extended_info, '$.isVerifiedMerchant') = 'false'
and upper(status) = 'SUCCESS'
AND category = 'VPA2MERCHANT') C
on B.txn_id = C.txn_id
INNER JOIN
(SELECT txnid
, regexp_replace(cast(json_extract(request, '$.requestPayload.payerType') AS varchar),'"','') AS payerType
, regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') AS payeeType
, JSON_EXTRACT_SCALAR(request, '$.requestPayload.initiationMode') AS initiationMode
, regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE'2025-07-01' AND DATE'2025-08-06'
AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
AND JSON_EXTRACT_SCALAR(request, '$.requestPayload.initiationMode') IN ('04', '05')
AND regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION')D
ON B.txn_id = D.txnid;
