-- RISK236
-- DROP TABLE team_kingkong.tpap_risk236_breaches;

-- CREATE TABLE team_kingkong.tpap_risk236_breaches AS
INSERT INTO team_kingkong.tpap_risk236_breaches
with tpap_base as
(
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.latitude, D.longitude
, 'upi_p2p_multiple_locations_60min' as rule_name
, 'Txns from >15 locations in 60 mins' 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(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(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31'
    AND DATE(created_on) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31'
    GROUP BY 1)B
inner join
    (select txn_id, category
    from switch.txn_info_snapshot_v3
    where DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31'
    and DATE(created_on) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31'
    and upper(status) = 'SUCCESS' AND category IN ('VPA2VPA', 'VPA2ACCOUNT')) C
on B.txn_id = C.txn_id
INNER JOIN
    (
        SELECT txnid
    , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
    , regexp_replace(cast(json_extract(request, '$.requestPayload.latitude') as varchar), '"', '') as latitude
    , regexp_replace(cast(json_extract(request, '$.requestPayload.longitude') as varchar), '"', '') as longitude
    FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) 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%'
    or 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 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 regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION')D
ON B.txn_id = D.txnid
WHERE (payer_vpa LIKE '%@paytm%') OR (payer_vpa LIKE '%@pt%') OR (payee_vpa LIKE '%@paytm%') OR (payee_vpa LIKE '%@pt%'))
 
SELECT * FROM
    (SELECT t1.payer_vpa,
      t1.payee_vpa,
      t1.txn_id,
      t1.txn_amount,
      t1.category,
      t1.upi_subtype,
      t1.txn_time,
      t1.latitude,
      t1.longitude,
      DATE(t1.txn_time) AS txn_date,
      COUNT(DISTINCT CONCAT(t2.latitude, '_', t2.longitude)) AS distinct_lat_lon_count,
      15 AS lat_long_cnt_threshold
    FROM tpap_base t1
    INNER JOIN tpap_base t2
    ON t1.payee_vpa = t2.payee_vpa
      AND t2.txn_time BETWEEN (t1.txn_time - INTERVAL '3600' SECOND) AND t1.txn_time -- 60 MIN
      AND t1.txn_id <> t2.txn_id AND t1.txn_amount > 5000
      AND NOT (t1.latitude = t2.latitude AND t1.longitude = t2.longitude)
    GROUP BY t1.payer_vpa, t1.payee_vpa, t1.txn_id, t1.txn_amount, t1.category, t1.upi_subtype, t1.txn_time, DATE(t1.txn_time), t1.latitude, t1.longitude)
WHERE distinct_lat_lon_count >= lat_long_cnt_threshold
;