DROP TABLE team_kingkong.offus_MID_CCDC_Daily_TXN_limit_Check_breaches;

-- CREATE TABLE team_kingkong.offus_MID_CCDC_Daily_TXN_limit_Check_breaches AS
INSERT INTO team_kingkong.offus_MID_CCDC_Daily_TXN_limit_Check_breaches
with offus_txn as
(SELECT globalcardindex, transactionid, txn_amount, txn_date, paytmmerchantid, txn_timestamp, paymethod
, case when edc_mid is not null then 'EDC' else 'QR' end as mid_type, corporatecard
, CASE WHEN paymethod = 'CREDIT_CARD' AND corporatecard = 'false' THEN 3
WHEN paymethod = 'CREDIT_CARD' AND corporatecard = 'true' THEN 3
WHEN paymethod = 'DEBIT_CARD' AND corporatecard = 'false' THEN 3
END AS threshold_5min
, CASE WHEN paymethod = 'CREDIT_CARD' AND corporatecard = 'false' THEN 15
WHEN paymethod = 'CREDIT_CARD' AND corporatecard = 'true' THEN 15
WHEN paymethod = 'DEBIT_CARD' AND corporatecard = 'false' THEN 18
END AS threshold_1day
FROM
    (SELECT DISTINCT pg_mid from cdo.total_offline_merchant_base_snapshot_v3) f
INNER join
    (select distinct transactionid
    , cast(eventamount as double)/100 as txn_amount
    , paytmmerchantid
    , globalcardindex
    , DATE(dl_last_updated) AS txn_date
    , CAST(velocitytimestamp AS DOUBLE) AS txn_timestamp
    , paymethod
    from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    where dl_last_updated BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31'
    and paymethod in ('CREDIT_CARD','DEBIT_CARD')
    AND actionrecommended <> 'BLOCK') a
on a.paytmmerchantid = f.pg_mid
LEFT JOIN
    (SELECT DISTINCT mid AS edc_mid FROM paytmpgdb.entity_edc_info_snapshot_v3
    WHERE terminal_status = 'ACTIVE' AND dl_last_updated >= DATE '2010-01-01') b
ON a.paytmmerchantid = b.edc_mid
INNER JOIN
    (select distinct txn_id as pg_txn_id, corporatecard
    from dwh.pg_olap
    where ingest_date BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31'
    and txn_started_at BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31'
    and txn_status = 'SUCCESS') d
on a.transactionid = d.pg_txn_id
WHERE paymethod = 'CREDIT_CARD' OR (paymethod = 'DEBIT_CARD' AND corporatecard = 'false'))
 
 
SELECT *, CASE
  WHEN txn5_min >= threshold_5min AND txn1_day >= threshold_1day THEN '5 min txn threshold breach, 1 day txn threshold breach'
  WHEN txn5_min >= threshold_5min THEN '5 min txn threshold breach'
  WHEN txn1_day >= threshold_1day THEN '1 day txn threshold breach'
  ELSE NULL END as breach_reason FROM
    (SELECT A.globalcardindex, A.transactionid, A.txn_amount, A.txn_date, A.paytmmerchantid, A.txn_timestamp
    , A.mid_type, A.paymethod, A.corporatecard
    , A.threshold_5min
    , A.threshold_1day
    , COUNT(IF((A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 300000, B.transactionid, NULL)) AS txn5_min
    , COUNT(B.transactionid) as txn1_day
    , 'edc_card_velocity_amount' AS rule_name
    FROM
        (SELECT * FROM offus_txn
        WHERE txn_date BETWEEN DATE'2025-01-01' AND  DATE'2025-01-31')A
    INNER JOIN
        (SELECT * FROM offus_txn)B
    ON A.globalcardindex = b.globalcardindex AND A.paytmmerchantid = B.paytmmerchantid
    AND A.transactionid <> B.transactionid
    AND (A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 86400000 -- <= 1d
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11)
WHERE (txn5_min >= threshold_5min) OR (txn1_day >= threshold_1day)