-- TPAP: RSIK_127
-- UPI Credit card Block txn if Payee name = Payer Name
-- "if ((payerAccountName == payeeAccountName) ???
-- && (payerAccountType == ""CREDIT"") 
-- && (payerType == ""PERSON"")) {
-- ""BLOCK""}"\
DROP TABLE team_kingkong.tpap_risk127_breaches;

-- CREATE TABLE team_kingkong.tpap_risk127_breaches AS
INSERT INTO team_kingkong.tpap_risk127_breaches
SELECT B.*, C.category
, IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
FROM
    (SELECT txn_id, scope_cust_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(created_on) as txn_date,
    MAX(amount) AS txn_amount,
    created_on AS txn_time
    FROM switch.txn_participants_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE'2025-05-01' AND DATE'2025-05-31'
    AND DATE(created_on) BETWEEN DATE'2025-05-01' AND DATE'2025-05-31'
    AND vpa IS NOT NULL
    GROUP BY 1,2,7)B
inner join
    (select txn_id, category
    from switch.txn_info_snapshot_v3
    where DATE(dl_last_updated) BETWEEN DATE'2025-05-01' AND DATE'2025-05-31'
    and DATE(created_on) BETWEEN DATE'2025-05-01' AND DATE'2025-05-31'
    and upper(status) in ('SUCCESS')) C
on B.txn_id = C.txn_id
inner JOIN
    (SELECT DISTINCT txnid
    , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
    , lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerAccountType') as varchar), '"', '')) as payerAccountType
    , lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerType') as varchar), '"', '')) as payerType
    , regexp_replace(cast(json_extract(request, '$.requestPayload.payerName') as varchar), '"', '') as payerName
    , regexp_replace(cast(json_extract(request, '$.requestPayload.payeeName') as varchar), '"', '') as payeeName
    FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN date'2025-05-01' AND DATE'2025-05-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.payerAccountType') as varchar), '"', '')) = 'credit'
    AND lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerType') as varchar), '"', '')) = 'person'
    AND lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payeeName') as varchar), '"', '')) = lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerName') as varchar), '"', ''))
    AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
    )D
ON B.txn_id = D.txnid;