%jdbc(hive)
set tez.queue.name=phonepe_verified;
set hive.execution.engine=tez;

SELECT DISTINCT DATE_SUB('2023-07-02', 32) AS starting_date
,DATE_SUB('2023-07-02', 2) AS ending_date
,active_days
,'monthly' as date_range
,d.identifier AS identifier
,'mult_cc_wallet_topup_monthly' as red_flag
,topupAmt as value
,CCused as comment
,case when (ee.identifier is null AND e.identifier IS NULL) then 'New' else 'Repeat' end as new_or_repeat
,'AML' as `group`
,'FRA' as `type`
,'Alerts' as type_fra
,'User' as issue_type
,'Wallet' as sub_issue_type
,'2023-07-02' as run_date
FROM
    (SELECT senderuserid as identifier, CCused, topupAmt, active_days
    , row_number() OVER (ORDER BY CCused DESC, topupAmt DESC) rn
    FROM
        (SELECT A.senderuserid
        , count(distinct(CONCAT(COALESCE(sendercardbankid,'null'),"_",COALESCE(masked_card_number,'null'),"_",COALESCE(card_issuer,'null'),"_",COALESCE(sendercard_type,'null')))) AS CCused
        , SUM(A.totaltransactionamount) AS topupAmt
        , COUNT(DISTINCT updated_date) as active_days FROM
            (SELECT senderuserid, totaltransactionamount, transaction_id, card_issuer, sendercard_type
            , updated_date, sendercardbankid 
            from fraud.transaction_details_v3
            where updated_date BETWEEN DATE_SUB('2023-07-02', 32) AND DATE_SUB('2023-07-02', 2)
            and sendertype = 'INTERNAL_USER' AND workflowtype = 'CONSUMER_TO_MERCHANT'
            and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
            and receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') AND card_flag = true)A
        INNER JOIN
            (SELECT transaction_id, masked_card_number, bank_id
            FROM payment.transaction_payer_sources
            WHERE year IN (year(DATE_SUB('2023-07-02', 32)), year(DATE_SUB('2023-07-02', 2)))
            AND month IN (month(DATE_SUB('2023-07-02', 32)), month(DATE_SUB('2023-07-02', 2)))
            AND masked_card_number IS NOT NULL)B
        ON A.transaction_id = B.transaction_id
        GROUP BY A.senderuserid)Y)d
    left join
        (select DISTINCT identifier from fraud.aml_freshdesk WHERE run_date < DATE_SUB('2023-07-02', 32))e
    On d.identifier = e.identifier
    left join
        (select identifier from fraud.aml_freshdesk_month WHERE run_date < DATE_SUB('2023-07-02', 32)) ee
    on d.identifier = ee.identifier
    left join
        (select user_ext_id as identifier
        from users.users where blacklisted = 1
        and blacklist_reason = 'SAM' AND updated < '2023-07-02')f
    On d.identifier = f.identifier
    -- left join 
    --     (select identifier from fraud.avg_vpa_amt
    --     UNION SELECT identifier FROM fraud.Merchant_high_nonqr
    --     UNION SELECT identifier FROM fraud.high_spend_on_cc_weekly) g 
    -- On d.identifier = g.identifier
where d.identifier is NOT NULL AND f.identifier is NULL AND rn <= 16 -- AND g.identifier IS NULL;