DROP TABLE team_kingkong.tpap_rej_rate_monthly;

-- CREATE TABLE team_kingkong.tpap_rej_rate_monthly2 AS
INSERT INTO team_kingkong.tpap_rej_rate_monthly2
SELECT DISTINCT A.year_month
, A.txn_date
, A.category
, risk_code
, IF(C.upi_subtype IS NOT NULL, c.upi_subtype, IF(A.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
, SUM(IF(action_recommended = 'BLOCK', A.amount, 0)) AS rejected_gmv
, COUNT(IF(action_recommended = 'BLOCK', A.txn_id, NULL)) AS rejected_txns 
, SUM(A.amount) AS attempted_gmv
, COUNT(A.txn_id) AS attempted_txns 
FROM
    (SELECT DISTINCT substr(cast(created_on as varchar(30)), 1, 7) as year_month, DATE(created_on) AS txn_date
    , txn_id, amount, category
    FROM switch.txn_info_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN date'2025-01-01' AND DATE('2025-02-28')
    AND DATE(created_on) BETWEEN date'2025-01-01' AND DATE('2025-02-28'))A
INNER JOIN
    (SELECT DISTINCT txnid
    , CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) as txn_amount
    , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
    , regexp_replace(cast(json_extract(response, '$.messages.cst[0]') as varchar), '"', '') as risk_code
    , json_extract_scalar(response, '$.action_recommended') AS action_recommended
    FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN date'2025-01-01' AND DATE('2025-02-28'))C
on A.txn_id = C.txnid
GROUP BY 1,2,3,4,5;
        
        
-- Rule X Monthly Breach rate
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk200_breaches' AS rule_name
FROM team_kingkong.tpap_risk200_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk307_breaches' AS rule_name 
FROM team_kingkong.tpap_risk307_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk304_breaches' AS rule_name  
FROM team_kingkong.tpap_risk304_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk127_breaches' AS rule_name  
FROM team_kingkong.tpap_risk127_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk005_breaches' AS rule_name  
FROM team_kingkong.tpap_risk005_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk306_breaches' AS rule_name  
FROM team_kingkong.tpap_risk306_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk236_breaches' AS rule_name  
FROM team_kingkong.tpap_risk236_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk235_breaches' AS rule_name  
FROM team_kingkong.tpap_risk235_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk152_breaches' AS rule_name  
FROM team_kingkong.tpap_risk152_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk292_breaches' AS rule_name  
FROM team_kingkong.tpap_risk292_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk510_breaches' AS rule_name  
FROM team_kingkong.tpap_risk510_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk318_breaches' AS rule_name  
FROM team_kingkong.tpap_risk318_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk239_breaches' AS rule_name  
FROM team_kingkong.tpap_risk239_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk151_breaches' AS rule_name  
FROM team_kingkong.tpap_risk151_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt
, 'tpap_risk009_breaches' AS rule_name  
FROM team_kingkong.tpap_risk009_breaches
GROUP BY 1
;


-- TPAP OVERALL BREACH RATE
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(txn_id) as breach_cnt, SUM(txn_amount) as breach_amt FROM
(SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk200_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk307_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount 
FROM team_kingkong.tpap_risk304_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk127_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk005_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk306_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk236_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk235_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk152_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk292_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk510_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk318_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk239_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk151_breaches

UNION

SELECT DATE(txn_date) AS txn_date, txn_id, txn_amount
FROM team_kingkong.tpap_risk009_breaches
)
GROUP BY 1;        
        
        
        
-- RISK314	oc69_mcc_4812_4814_collect_limit
-- DROP TABLE team_kingkong.tpap_risk314_breaches;
 
-- CREATE TABLE team_kingkong.tpap_risk314_breaches AS
INSERT INTO team_kingkong.tpap_risk314_breaches
SELECT DISTINCT B.*, C.category, C.txn_type, D.payerType
, 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(CASE WHEN participant_type = 'PAYEE' THEN mcc END) AS payeeMccCode,
    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(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,2,8
    HAVING MAX(CASE WHEN participant_type = 'PAYEE' THEN mcc END) IN ('4812, 4814')
    AND MAX(amount) > 5000)B
inner join
    (select txn_id, category, "type" AS txn_type
    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) in ('SUCCESS') AND "type" = 'COLLECT') C
on B.txn_id = C.txn_id
INNER JOIN
    (SELECT txnid
    , regexp_replace(cast(json_extract(request, '$.requestPayload.payerType') AS varchar),'"','') AS payerType
    , 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(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31'
    AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK')D
ON B.txn_id = D.txnid;