OFFUS : ICA_OddTime_PerCard_PerMID_EDC

-- ICA_OddTime_PerCard_PerMID_EDC
CREATE TABLE team_kingkong.offus_ICA_OddTime_PerCard_PerMID_EDC_breaches AS
-- INSERT INTO team_kingkong.offus_ICA_OddTime_PerCard_PerMID_EDC_breaches
with offus_txn as
(SELECT transactionid, txn_amount, txn_date, paytmmerchantid, txn_timestamp, globalcardindex
, case when edc_mid is not null then 'EDC' else 'QR' end as mid_type
, merchantcategory, merchantsubcategory, isindian 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
    , DATE(dl_last_updated) AS txn_date
    , CAST(velocitytimestamp AS DOUBLE) AS txn_timestamp
    , globalcardindex
    , merchantcategory, merchantsubcategory, isindian
    from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    where dl_last_updated BETWEEN DATE(DATE'2025-02-01' - INTERVAL '1' DAY) AND DATE'2025-02-28'
    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)

SELECT * FROM
    (SELECT A.transactionid, A.txn_amount, A.txn_date, A.paytmmerchantid, A.globalcardindex, A.mid_type, 'ICA_OddTime_PerCard_PerMID_EDC' AS rule_name, A.txn_timestamp
    , 5000 as per_txn_limit
    , COUNT(B.transactionid) as txn1_day
    , 2 as txn1_day_threshold
    FROM
        (SELECT * FROM offus_txn
        WHERE txn_date BETWEEN date '2025-02-01' AND DATE'2025-02-28'
        AND isindian = 'false'
        AND HOUR(FROM_UNIXTIME(txn_timestamp / 1000)) BETWEEN 0 AND 4
        AND (merchantsubcategory NOT IN ('Restaurant', 'Foodcourt','Restaurants and Bars', 'Fast Food and QSR' , 'Hotel', 'Aviation','Tours and Travel Agency' , 'Pharmacy', 'Hospital','Taxi','Pharmacy', 'Hospital', 'Taxi')
        OR merchantcategory NOT IN ('Airport','Gas and Petrol')))A
    LEFT JOIN
        (SELECT * FROM offus_txn)B
    ON A.globalcardindex = B.globalcardindex AND A.paytmmerchantid = B.paytmmerchantid AND (A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 86400000 -- <= 1day
    AND A.transactionid <> B.transactionid
    GROUP BY 1,2,3,4,5,6,7,8)
WHERE (txn_amount > per_txn_limit) OR (txn1_day>= txn1_day_threshold)
-- LIMIT 100
;