TPAP: RISK303

-- RISK303
-- if( (txnAmount > 100000 && ((payeeType == ""ENTITY"") || (payeeMccCode == ""7407"")) && txnType == ""PAY"")){""PASS""}
-- if((in_oc_185_mcc_list == false) && (in_oc_86_mcc_list == false) && !(initiationMode == ""12"" || ((purposeCode == ""15"" && payeeMccCode !=""5969"")))){""BLOCK""}

DROP TABLE team_kingkong.tpap_risk303_breaches;

-- CREATE TABLE team_kingkong.tpap_risk303_breaches AS
INSERT INTO team_kingkong.tpap_risk303_breaches
SELECT DISTINCT B.*, C.category, C.txn_type, C.initiationMode, D.payeeType
, IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
, D.purposeCode
, 'oc_120_p2m_p2pm_txn_limit' as rule_name
, 'NA' 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 payeeMccCode,
    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)B
inner join
    (select DISTINCT txn_id, category, "type" AS txn_type
    , json_extract_scalar(extended_info, '$.initiationMode') as initiationMode
    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'
    and upper(status) = 'SUCCESS' AND category IN ('VPA2MERCHANT', 'VPA2VPA')
    AND "type" = 'PAY') 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.initiationMode') AS initiationMode
    , regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') AS payeeType
    , json_extract_scalar(request, '$.requestPayload.purposeCode') AS purposeCode
    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 (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 (lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '')) LIKE '%@paytm%'
    -- or lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '')) like '%@pt%')
    -- AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '') IS NOT NULL
    AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '') IS NOT NULL
    -- AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '') <> ''
    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, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION')D
ON B.txn_id = D.txnid
WHERE payer_vpa IS NOT NULL
AND txn_amount > 100000 
AND (payeeType = 'ENTITY' OR payeeMccCode = '7407')
AND payeeMccCode NOT IN ('8299', '8042', '8220', '8099', '742', '8049', '8021', '8244', '8211', '9311', '8031', '8041', '8071', '8062', '8249', '8011', '8241', '8050')
AND payeeMccCode NOT IN ('6012', '6529', '6300', '5960', '6211', '7322', '5413')
AND NOT (C.initiationMode = '12' OR (purposeCode = '15' AND payeeMccCode != '5969'))
;