-- TPAP: RISK200
-- DROP TABLE team_kingkong.tpap_risk200_breaches;
 
-- CREATE TABLE team_kingkong.tpap_risk200_breaches AS
INSERT INTO team_kingkong.tpap_risk200_breaches
with temp_tpap_base as
(SELECT DISTINCT bt.txn_id,
bt.scope_cust_id,
bt.payer_vpa,
bt.payee_vpa,
bt.txn_date,
bt.txn_amount,
st.category,
COALESCE(rd.upi_subtype, CASE WHEN st.category = 'LITE_MANDATE' THEN 'UPI_LITE_MANDATE' ELSE '' END) 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
    FROM switch.txn_participants_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE '2025-06-01' AND DATE '2025-07-05'
    AND DATE(created_on) BETWEEN DATE '2025-06-01' AND DATE '2025-07-05'
    GROUP BY txn_id, scope_cust_id) bt
INNER JOIN
    (SELECT txn_id, category
    FROM switch.txn_info_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE '2025-06-01' AND DATE '2025-07-05'
    AND DATE(created_on) BETWEEN DATE '2025-06-01' AND DATE '2025-07-05'
    AND UPPER(status) = 'SUCCESS') st
ON bt.txn_id = st.txn_id
INNER JOIN (
    SELECT DISTINCT txnid,
    REGEXP_REPLACE(CAST(json_extract(request, '$.evaluationType') AS varchar), '"', '') AS upi_subtype
    FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE '2025-06-01' AND DATE '2025-07-05'
    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 json_extract_scalar(response, '$.action_recommended') <> 'BLOCK') rd
ON bt.txn_id = rd.txnid
WHERE (payer_vpa LIKE '%@paytm%') OR (payer_vpa LIKE '%@pt%')),
 
temp_blacklist AS
    (SELECT vpa AS blacklisted_vpa,
    DATE(FROM_UNIXTIME(CAST("timestamp" AS double) / 1000)) AS blacklist_date
    FROM team_kingkong.upi_blacklist_vpa_shivam
    WHERE "timestamp" IS NOT NULL
    AND "timestamp" <> ''
    AND DATE(FROM_UNIXTIME(CAST("timestamp" AS double) / 1000)) <= DATE '2025-07-05')
 
SELECT * FROM  
  (SELECT tb.txn_id,
  tb.scope_cust_id,
  tb.payer_vpa,
  tb.payee_vpa,
  tb.txn_date,
  tb.txn_amount,
  tb.category,
  tb.upi_subtype,
  COALESCE(pv.blacklisted_vpa, rv.blacklisted_vpa) AS blacklisted_vpa,
  COALESCE(pv.blacklist_date, rv.blacklist_date) AS blacklist_date,
  'RISK200' AS risk_code,
  'upi_blacklisted_vpa' AS rule_name
  , 'txn with blacklisted vpa' as breach_reason
FROM temp_tpap_base tb
LEFT JOIN temp_blacklist pv
  ON tb.payer_vpa = pv.blacklisted_vpa AND tb.txn_date > pv.blacklist_date
LEFT JOIN temp_blacklist rv
  ON tb.payee_vpa = rv.blacklisted_vpa AND tb.txn_date > rv.blacklist_date)
WHERE blacklisted_vpa IS NOT NULL AND blacklist_date < DATE(txn_date);