TPAP: RISK314

-- RISK314	oc69_mcc_4812_4814_collect_limit	
-- MCC 4812 / 4814 (prepaid mobile recharge) in collect mode shall be restricted to maximum of Rs. 5,000 respectively (including from verified merchants).

DROP TABLE team_kingkong.tpap_risk314_breaches;

-- CREATE TABLE team_kingkong.tpap_risk314_breaches AS
INSERT INTO team_kingkong.tpap_risk314_breaches
SELECT DISTINCT B.*, C.category
, IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
, D.txnType
, 'oc69_mcc_4812_4814_collect_limit'  AS rule_name
, 'payee_mcc in (4812, 4814) & txnType = COLLECT & amt >5k' 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-01-01' AND DATE'2025-01-31'
    AND DATE(created_on) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31' 
    GROUP BY 1
    HAVING MAX(CASE WHEN participant_type = 'PAYEE' THEN mcc END) IN ('4812', '4814')
    AND MAX(amount) > 5000)B
inner join
    (select txn_id, category
    from switch.txn_info_snapshot_v3
    where DATE(dl_last_updated) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
    and DATE(created_on) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31') 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-01-01' AND DATE'2025-01-31'
    AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
    AND json_extract_scalar(request, '$.requestPayload.txnType') = 'COLLECT'
    AND regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION')D
ON B.txn_id = D.txnid
;