ONUS : Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck

-- Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck

-- Enabling CC/DC in Fastag Weekly limit of 10K per VRN via CC/DC and monthly limit of 20K per VRN via CC on fastag recharge using Trusted sourcesWeekly limit of X per VRN via CC/DC 
-- and monthly limit of Y per VRN via CC on fastag recharge using Trusted sources	

-- "if(!(Seq(""BALANCE"",""COUPON"").contains(payMethod)) && !(payMethod contains ""DIGITAL_CREDIT"") && !(Seq(""ONE_CLICK_PAY"").contains(oneclick))){trusted_payload = 1}

-- if(Seq(""CREDIT_CARD"",""DEBIT_CARD"").contains(pay_method) &&
--  paytm_merchant_id==""PTMFVT32998068120662"" &&
-- ((event_amount+sm_gmv_rn_tid_fastag_trust_success_7d)>2529500||
-- (event_amount+sm_gmv_rn_tid_fastag_trust_success_30d)>5059000))
-- ""BLOCK"""

-- DROP TABLE team_kingkong.onus_Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck_breaches;
 
-- CREATE TABLE team_kingkong.onus_Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck_breaches AS
INSERT INTO team_kingkong.onus_Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck_breaches
with onus_txn_base as
    (SELECT DISTINCT A.*, case when m1.mid is not null then category else 'Others' end as business_category FROM 
        (select userid, transactionid,
        cast(eventAmount as double) / 100 as amt,
        dateinserted,
        substr(cast(dateinserted as varchar(30)), 1, 7) as mnth,
        paymethod, paytmmerchantid, responsestatus, actionrecommended, velocitytimestamp
        , subscriberid as vrn
        FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
        WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
        AND SOURCE = 'PG'
        AND responsestatus IN ('SUCCESS') AND actionrecommended = 'PASS'
        AND paytmmerchantid IN ('PTMFVT32998068120662') AND paymethod IN ('DEBIT_CARD', 'CREDIT_CARD')
        AND eventid IN (SELECT eventlinkid
        FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
        WHERE dl_last_updated BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31')) a
    left join
        (select * from team_kingkong.voc_mid_categorization where mid != '') m1
    on a.paytmmerchantid = m1.mid)
 
SELECT * FROM 
    (SELECT A.*
    , SUM(IF(DATE(B.dateinserted) BETWEEN DATE(DATE(A.dateinserted) - INTERVAL '7' DAY) AND DATE(A.dateinserted), B.amt, NULL)) AS week_amt
    , 25295 AS week_threshold
    -- No.of attempted txns per calendar month > 30 (consider only the CCBP transactions)
    , SUM(IF(DATE(B.dateinserted) BETWEEN date_trunc('month', DATE(A.dateinserted)) AND DATE(A.dateinserted), B.amt, NULL)) AS month_amt
    , 50590 AS month_threshold
    FROM
        (SELECT * FROM onus_txn_base
        WHERE DATE(dateinserted) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
        )A
    INNER JOIN
        (SELECT * FROM onus_txn_base)B
    ON A.vrn = B.vrn AND A.transactionid <> B.transactionid AND B.velocitytimestamp < A.velocitytimestamp
    AND DATE(B.dateinserted) BETWEEN DATE(A.dateinserted - INTERVAL '30' DAY) AND DATE(A.dateinserted)
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12)
WHERE ((amt + week_amt) >= week_threshold) OR ((amt + month_amt) >= month_threshold)
;