DROP TABLE team_kingkong.offus_ICA_Unsafe_Country_Transactions_breaches;

-- CREATE TABLE team_kingkong.offus_ICA_Unsafe_Country_Transactions_breaches AS
INSERT INTO team_kingkong.offus_ICA_Unsafe_Country_Transactions_breaches
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, bankcountry, small_vpa, country_bl_date FROM
    (SELECT DISTINCT pg_mid from cdo.total_offline_merchant_base_snapshot_v3) f
INNER join
    (select distinct transactionid, bankcountry, small_vpa
    , 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-05-31'
    AND actionrecommended <> 'BLOCK' AND responsestatus = 'SUCCESS'
    AND isindian = 'false' AND bankcountry IS NOT NULL) 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 content as country, "timestamp" AS country_bl_date
    FROM team_kingkong.high_risk_country_list
    WHERE DATE(FROM_UNIXTIME(CAST("timestamp" AS double) / 1000)) <= DATE'2025-05-31')C
ON a.bankcountry = C.country AND DATE(FROM_UNIXTIME(CAST(country_bl_date AS double) / 1000)) < a.txn_date;