TPAP: RISK115

-- RISK115
DROP TABLE team_kingkong.tpap_risk115_breaches;

-- CREATE TABLE team_kingkong.tpap_risk115_breaches AS
INSERT INTO team_kingkong.tpap_risk115_breaches
SELECT DISTINCT B.*, C.category, C.txn_type, C.payer_os, C.initiationMode, C.amount_limit
, IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
, 'upi_oc141_mcc5816_onlinegaming_v3' as rule_name
, CASE
  WHEN lower(payer_os) LIKE 'android%' AND txn_type = 'COLLECT'
    THEN 'Android Collect Txn'

  WHEN lower(payer_os) LIKE 'ios%' AND txn_amount > amount_limit AND txn_type = 'COLLECT'
    THEN 'iOS High Amount Collect Txn'

  WHEN initiationMode = '00' AND txn_type NOT IN ('PAY', 'DEBIT', 'CR')
    THEN 'Invalid Txn Type with Initiation Mode 00'

  WHEN initiationMode NOT IN ('00', '10', '04', '05')
    THEN 'Unexpected Initiation Mode'

  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 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, '$.payerOsApp') AS payer_os
    , json_extract_scalar(extended_info, '$.initiationMode') as initiationMode
    , 2000 as amount_limit
    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') 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
    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')D
ON B.txn_id = D.txnid
WHERE upi_subtype = 'UPI_TRANSACTION' AND payer_vpa IS NOT NULL
AND (((payeeMccCode = '5816') AND (lower(payer_os) LIKE 'android%') AND (txn_type = 'COLLECT'))
OR ((payeeMccCode = '5816') AND (lower(payer_os) LIKE 'iOS%') AND (txn_amount > amount_limit) AND (txn_type = 'COLLECT'))
OR ((payeeMccCode= '5816') AND (initiationMode = '00') AND (txn_type NOT IN ('PAY', 'DEBIT', 'CR')))
OR ((payeeMccCode = '5816') AND ((initiationMode NOT IN ('00', '10', '04', '05')))));