CREATE TABLE team_kingkong.tpap_efrm_fraud_base2 AS
select DISTINCT
upi_txn_id_switch,
txn_date_switch,
txn_amount_switch,
payer_vpa_switch,
payee_vpa_switch,
payer_mcc_switch,
payee_mcc_switch,
payer_handle,
payee_handle,
scope_cust_id,
merchant_bene_type,
category,
payer_mobile_no,
upi_subtype,
'NA' AS reporting_date,
ingestion_date,
'EFRM' as source
from
       (select txn_id as upi_txn_id
        , DATE(MIN(dl_last_updated)) AS ingestion_date
        , cast(regexp_replace(txn_amount, ',', '') as double) as txn_amount
        -- , payee_vpa
        from frauds.efrm_data_snapshot_v3
        where dl_last_updated >= DATE'2024-12-01'
        GROUP BY 1,3) a
    inner join
        (select DISTINCT DATE(b.created_on) as txn_date_switch,
        a.txn_id as upi_txn_id_switch
        , a.vpa as payer_vpa_switch
        , b.vpa as  payee_vpa_switch
        , CAST(a.amount AS DOUBLE) as txn_amount_switch
        ,a.mcc as payer_mcc_switch
        , b.mcc as payee_mcc_switch
        ,a.handle as payer_handle
        , b.handle as payee_handle
        ,a.scope_cust_id
        ,a.mobile_no as payer_mobile_no
        ,(case when (lower(b.vpa) like '%@paytm%' or lower(b.vpa) like '%@pt%') then 'paytm merchant/bene' else 'others' end) merchant_bene_type
        from switch.txn_participants_snapshot_v3 a
            inner join
        switch.txn_participants_snapshot_v3 b
        on a.txn_id = b.txn_id
        where DATE(a.dl_last_updated) >= DATE'2024-12-01'
        and DATE(b.dl_last_updated) >= DATE'2024-12-01'
        and DATE(a.created_on) >= DATE'2024-12-01'
        and a.participant_type in ('PAYER')
        and b.participant_type in ('PAYEE')
        and (lower(a.vpa) like'%@paytm%' or lower(a.vpa) like '%@pt%')) c
    on a.upi_txn_id = c.upi_txn_id_switch AND a.txn_amount = c.txn_amount_switch AND C.txn_date_switch < a.ingestion_date
    inner join
        (select DISTINCT txn_id, category, CAST(amount AS DOUBLE) AS amount, DATE(created_on) as created_on
        from switch.txn_info_snapshot_v3
        where dl_last_updated >= DATE'2024-12-01'
        and created_on >= DATE'2024-12-01' 
        and upper(status) in ('SUCCESS')) e
    on a.upi_txn_id = e.txn_id AND a.txn_amount = e.amount AND e.created_on < a.ingestion_date
    LEFT JOIN
        (SELECT DISTINCT txnid
        , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
        , CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) as amount
        , json_extract_scalar(request, '$.requestPayload.payeeVpa') as payeeVpa
        , DATE(dl_last_updated) as dl_last_updated
        FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
        WHERE DATE(dl_last_updated) >= DATE'2024-12-01' 
        AND json_extract_scalar(response, '$.action_recommended') = 'PASS'
        AND json_extract_scalar(request, '$.source') = 'UPI'
        AND (lower(json_extract_scalar(request, '$.requestPayload.payerVpa')) LIKE '%@paytm%'
        or lower(json_extract_scalar(request, '$.requestPayload.payerVpa')) like '%@pt%'))b
    on a.upi_txn_id = b.txnid AND a.txn_amount = b.amount AND b.dl_last_updated < a.ingestion_date
    ;