TPAP: RISK161

-- RISK161	upi_oc141A_mcc6540_gift
DROP TABLE team_kingkong.tpap_risk161_breaches;

CREATE TABLE team_kingkong.tpap_risk161_breaches AS
-- INSERT INTO team_kingkong.tpap_risk161_breaches
SELECT DISTINCT B.*, C.category
, C.initiationMode as initiationMode_switch -- ,  D.initiationMode as initiationMode_tpaphss
, IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
, D.txnType
, 'upi_oc141A_mcc6540_gift'  AS rule_name
, CASE
    WHEN payee_mcc = '6540' AND initiationMode = '00' AND txnType NOT IN ('PAY', 'DEBIT') 
        THEN 'Invalid txnType for initiationMode 00 and MCC 6540'
    WHEN payee_mcc = '6540' AND initiationMode NOT IN ('00', '10', '04', '05', '01', '02', '11') 
        THEN 'Invalid initiationMode for MCC 6540'
    ELSE NULL
END 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(CASE WHEN participant_type = 'PAYEE' THEN mcc END) AS payee_mcc,
    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-09-01' AND DATE'2025-09-15'
    AND DATE(created_on) BETWEEN DATE'2025-09-01' AND DATE'2025-09-15' 
    GROUP BY 1)B
inner join
    (select txn_id, category
    , json_extract_scalar(extended_info, '$.initiationMode') as initiationMode
    from switch.txn_info_snapshot_v3
    where DATE(dl_last_updated) BETWEEN DATE'2025-09-01' AND DATE'2025-09-15'
    and DATE(created_on) BETWEEN DATE'2025-09-01' AND DATE'2025-09-15'
    -- and upper(status) = 'SUCCESS' AND category IN ('VPA2MERCHANT', 'VPA2VPA')
    ) C
on B.txn_id = C.txn_id
INNER JOIN
    (SELECT txnid
    , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
    , json_extract_scalar(request, '$.requestPayload.txnType') AS txnType
    FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE'2025-09-01' AND DATE'2025-09-15'
    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 regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '') IS NOT NULL
    AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '') <> ''
    AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
    -- AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerType') AS varchar),'"','') = 'PERSON'
    -- AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') = 'PERSON'
    -- AND JSON_EXTRACT_SCALAR(request, '$.requestPayload.initiationMode') = '04'
    -- AND json_extract_scalar(request, '$.requestPayload.merchantGenre') = 'OFFLINE'
    -- AND regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION'
    )D
ON B.txn_id = D.txnid
WHERE (payee_mcc = '6540') AND
((initiationMode ='00' AND txnType NOT IN ('PAY','DEBIT')) OR 
(payee_mcc = '6540' AND initiationMode NOT IN ('00', '10', '04', '05', '01', '02', '11')))
;