with cte_recoveryaction
as
(select row_number() over (partition by tenantid, disputeid, stage order by performedondatetime desc) as rn, tenantid, disputeid, claimid, stage, performedondatetime, actionname, cycle
from recoveryaction
)

select distinct
    client.legalname as "Client",
    c.reasontype as "Category",
    c.claimtype as "Type",
    c.reason as "Reason",
    d.acquirernetwork as "Network",
    d.claimid as "Claim ID",
    d.disputeid as "Dispute ID",
    c.customercontactdate as "Contact Date",
    d.status as "Dispute Status",
    d.disputeamount as "Dispute Amount",
    d.isreopened as "Reopened?",
--cycle 1
    to_date(cycle1action.performedondatetime) as "Cycle 1 Action Date",
    cycle1action.actionname as "Cycle 1 Action",
    (case
        when cycle1response.performedondatetime is not null then to_date(cycle1response.performedondatetime)
        when cycle1cancel.performedondatetime is not null then to_date(cycle1cancel.performedondatetime)
    end) as "Cycle 1 Response Date",
    (case
        when cycle1response.actionname in ('Response Received - Full Amount Recovered') then 'Full Amount Recovered'
        when cycle1response.actionname in ('Response Received - No Recovery') then 'No Recovery'
        when cycle1response.actionname in ('Response Received - Partial Amount Recovered') then 'Partial Recovery'
        when cycle1action.actionname in ('Rapid Dispute Resolution') then 'Acquirer Accepted - Full (RDR)'
        when cycle1response.actionname in ('Dispute Accepted - Full') then 'Acquirer Accepted - Full'
        when cycle1response.actionname in ('No Response Received') then 'Acquirer Timeframe Expired'
        when cycle1response.actionname in ('Dispute Declined') and cycle1responseaction.performedondatetime is null
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Acquirer Declined (Merchant Credit)'
        when cycle1response.actionname in ('Dispute Declined') then 'Acquirer Declined'
        when cycle1response.actionname in ('Dispute Accepted - Partial') and cycle1responseaction.performedondatetime is null
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Acquirer Accepted - Partial (Merchant Credit)'
        when cycle1response.actionname in ('Dispute Accepted - Partial') then 'Acquirer Accepted - Partial'
        when cycle1response.actionname in ('Pre-Arbitration Received') and cycle1responseaction.performedondatetime is null
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Inbound Pre-Arbitration (Merchant Credit)'
        when cycle1response.actionname in ('Pre-Arbitration Received') then 'Inbound Pre-Arbitration'
        when cycle1response.performedondatetime is null and cycle1cancel.actionname in ('Chargeback Rejected')
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Rejected (Merchant Credit)'
        when cycle1response.performedondatetime is null and cycle1cancel.actionname in ('Chargeback Rejected') then 'Rejected'
        when cycle1response.performedondatetime is null and cycle1cancel.actionname in ('Dispute Recalled')
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Recalled (Merchant Credit)'
        when cycle1response.performedondatetime is null and cycle1cancel.actionname in ('Dispute Recalled') then 'Recalled'
        when cycle1liability.actionname ilike '%Exception Denied%' or cycle1liability.actionname ilike '%Issuer Liability Accepted%' then 'Liability Assigned to Issuer'
    end) as "Cycle 1 Response",
    (case
        when "Cycle 1 Response" = 'Full Amount Recovered' then 'Won'
        when "Cycle 1 Response" = 'Partial Recovery' then 'Won'
        when "Cycle 1 Response" = 'Acquirer Accepted - Full' then 'Won'
        when "Cycle 1 Response" = 'Acquirer Accepted - Full (RDR)' then 'Won'
        when "Cycle 1 Response" = 'Acquirer Accepted - Partial' then 'Won'
        when "Cycle 1 Response" = 'Acquirer Timeframe Expired' then 'Won'
        when "Cycle 1 Response" ilike '%Merchant Credit%' then 'Won'
        when "Cycle 1 Response" = 'No Recovery' then 'Lost'
        when "Cycle 1 Response" = 'Acquirer Declined' then 'Lost'
        when "Cycle 1 Response" = 'Inbound Pre-Arbitration' then 'Lost'
        when "Cycle 1 Response" = 'Liability Assigned to Issuer' then 'Lost'
        when "Cycle 1 Response" = 'Rejected' then 'Lost'
        when "Cycle 1 Response" = 'Recalled' then 'N/A'
    end) as "Cycle 1 Outcome",
    (case
        when "Cycle 1 Action" is null then 'No Action'
        when "Cycle 1 Action" is not null and "Cycle 1 Outcome" is null then 'Pending'
        when "Cycle 1 Action" is not null and "Cycle 1 Outcome" is not null then 'Completed'
    end) as "Cycle 1 Status",
    (case
        when cycle1responseaction.performedondatetime is not null then to_date(cycle1responseaction.performedondatetime)
        else to_date(cycle1liability.performedondatetime)
    end) as "Cycle 1 Response Action Date",
    (case
        when cycle1responseaction.actionname in ('Dispute Response Timeframe Expired', 'Dispute Timeframe Expired', 'Inbound Pre-Arbitration Timeframe Expired') then 'Timeframe Expired'
        when cycle1liability.actionname ilike '%deny dispute%' then 'Deny'
        when cycle1liability.actionname ilike '%write off%' then 'Write Off'
        else cycle1responseaction.actionname
    end) as "Cycle 1 Response Action",
--cycle 2
    (case
        when cycle1responseaction.actionname in ('Inbound Pre-Arbitration Declined') then to_date(cycle1responseaction.performedondatetime)
        else to_date(cycle2action.performedondatetime)
    end) as "Cycle 2 Action Date",
    (case
        when cycle1responseaction.actionname in ('Inbound Pre-Arbitration Declined') then 'Inbound Pre-Arbitration Declined'
        else cycle2action.actionname
    end) as "Cycle 2 Action",
    
    (case
        when cycle2response.performedondatetime is not null then to_date(cycle2response.performedondatetime)
        when cycle2cancel.performedondatetime is not null then to_date(cycle2cancel.performedondatetime)
    end) as "Cycle 2 Response Date",
    (case
        when cycle2response.actionname in ('Response Received - Full Amount Recovered') then 'Full Amount Recovered'
        when cycle2response.actionname in ('Response Received - No Recovery') then 'No Recovery'
        when cycle2response.actionname in ('Response Received - Partial Amount Recovered') then 'Partial Recovery'
        when cycle2response.actionname in ('Pre-Arbitration Accepted - Full') then 'Acquirer Accepted - Full'
        when cycle2response.actionname in ('Inbound Pre-Arbitration Response Accepted') then 'Acquirer Accepted - Full'
        when cycle2response.actionname in ('No Pre-Arbitration Response Received', 'No Inbound Arbitration Received') then 'Acquirer Timeframe Expired'
        when cycle2response.actionname in ('Pre-Arbitration Declined') and cycle2responseaction.performedondatetime is null
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Acquirer Declined (Merchant Credit)'
        when cycle2response.actionname in ('Pre-Arbitration Declined') then 'Acquirer Declined'
        when cycle2response.actionname in ('Pre-Arbitration Accepted - Partial') and cycle2responseaction.performedondatetime is null
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Acquirer Accepted - Partial (Merchant Credit)'
        when cycle2response.actionname in ('Pre-Arbitration Accepted - Partial') then 'Acquirer Accepted - Partial'
        when cycle2response.actionname in ('Inbound Arbitration Received') and cycle2responseaction.performedondatetime is null
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Inbound Arbitration (Merchant Credit)'
        when cycle2response.actionname in ('Inbound Arbitration Received') then 'Inbound Arbitration'
        when cycle2response.performedondatetime is null and cycle2cancel.actionname ilike '%Withdrawn%' and cycle3action.performedondatetime is null
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Recalled (Merchant Credit)'
        when cycle2response.performedondatetime is null and cycle2cancel.actionname ilike '%Withdrawn%' and cycle3action.performedondatetime is null then 'Withdrawn By Issuer'
    end) as "Cycle 2 Response",
    (case
        when "Cycle 2 Response" = 'Full Amount Recovered' then 'Won'
        when "Cycle 2 Response" = 'Partial Recovery' then 'Won'
        when "Cycle 2 Response" = 'Acquirer Accepted - Full' then 'Won'
        when "Cycle 2 Response" = 'Acquirer Accepted - Partial' then 'Won'
        when "Cycle 2 Response" = 'Acquirer Timeframe Expired' then 'Won'
        when "Cycle 2 Response" ilike '%Merchant Credit%' then 'Won'
        when "Cycle 2 Response" = 'No Recovery' then 'Lost'
        when "Cycle 2 Response" = 'Acquirer Declined' then 'Lost'
        when "Cycle 2 Response" = 'Inbound Arbitration' then 'Lost'
        when "Cycle 2 Response" = 'Withdrawn By Issuer' then 'Lost'
    end) as "Cycle 2 Outcome",
    (case
        when "Cycle 2 Action" is null then 'No Action'
        when "Cycle 2 Action" is not null and "Cycle 2 Outcome" is null then 'Pending'
        when "Cycle 2 Action" is not null and "Cycle 2 Outcome" is not null then 'Completed'
    end) as "Cycle 2 Status",
     (case
        when cycle2responseaction.performedondatetime is not null then to_date(cycle2responseaction.performedondatetime)
        else to_date(cycle2liability.performedondatetime)
    end) as "Cycle 2 Response Action Date",
    (case
        when cycle2responseaction.actionname in ('Pre-Arbitration Timeframe Expired') then 'Timeframe Expired'
        when cycle2liability.actionname ilike '%deny dispute%' then 'Deny'
        when cycle2liability.actionname ilike '%write off%' then 'Write Off'
        else cycle2responseaction.actionname
    end) as "Cycle 2 Response Action",
--cycle 3
    (case
        when cycle2response.actionname in ('Inbound Arbitration') then to_date(cycle2response.performedondatetime)
        else to_date(cycle3action.performedondatetime)
    end) as "Cycle 3 Action Date",
    (case
        when cycle2response.actionname in ('Inbound Arbitration') then 'Inbound Arbitration'
        else cycle3action.actionname
    end) as "Cycle 3 Action",
    (case
        when cycle3response.performedondatetime is not null then to_date(cycle3response.performedondatetime)
        when cycle3cancel.performedondatetime is not null then to_date(cycle3cancel.performedondatetime)
    end) as "Cycle 3 Response Date",
    (case
        when cycle3response.actionname in ('Response Received - Full Amount Recovered') then 'Full Amount Recovered'
        when cycle3response.actionname in ('Response Received - No Recovery') then 'No Recovery'
        when cycle3response.actionname in ('Response Received - Partial Amount Recovered') then 'Partial Recovery'
        when cycle3response.actionname in ('Arbitration Won') then 'Ruled in Issuer Favor'
        when cycle3response.actionname in ('Arbitration Lost') then 'Ruled in Acquirer Favor'
        when cycle3response.actionname in ('Arbitration Split') then 'Split Ruling'
        when cycle3response.actionname in ('Arbitration Accepted') then 'Acquirer Accepted'
        when cycle3response.performedondatetime is null and cycle3cancel.actionname ilike '%Withdrawn%' and cycle3action.performedondatetime is null
            and d.denyreason ilike '%Merchant Issued Credit%' then 'Withdrawn (Merchant Credit)'
        when cycle3response.performedondatetime is null and cycle3cancel.actionname ilike '%Withdrawn%' and cycle3action.performedondatetime is null then 'Withdrawn By Issuer'
    end) as "Cycle 3 Response",
    (case
        when "Cycle 3 Response" = 'Full Amount Recovered' then 'Won'
        when "Cycle 3 Response" = 'Partial Recovery' then 'Won'
        when "Cycle 3 Response" = 'Ruled in Issuer Favor' then 'Won'
        when "Cycle 3 Response" = 'Split Ruling' then 'Won'
        when "Cycle 3 Response" = 'Acquirer Accepted' then 'Won'
        when "Cycle 3 Response" ilike '%Merchant Credit%' then 'Won'
        when "Cycle 3 Response" = 'No Recovery' then 'Lost'
        when "Cycle 3 Response" = 'Ruled in Acquirer Favor' then 'Lost'
        when "Cycle 3 Response" = 'Withdrawn By Issuer' then 'Lost'
    end) as "Cycle 3 Outcome",
    (case
        when "Cycle 3 Action" is null then 'No Action'
        when "Cycle 3 Action" is not null and "Cycle 3 Outcome" is null then 'Pending'
        when "Cycle 3 Action" is not null and "Cycle 3 Outcome" is not null then 'Completed'
    end) as "Cycle 3 Status",
   to_date(cycle3liability.performedondatetime) as "Cycle 3 Response Action Date",
    (case
        when cycle3liability.actionname ilike '%deny dispute%' then 'Deny'
        when cycle3liability.actionname ilike '%write off%' then 'Write Off'
        else cycle3liability.actionname
    end) as "Cycle 3 Response Action",
from
	dispute as d
join claim as c
    on d.tenantid = c.tenantid and d.claimid = c.claimid
join client as client
    on d.tenantid = client.tenantid and d.clientid = client.clientid
--CYCLE 1
    --ACTIONS
        left outer join
           cte_recoveryaction as cycle1action
                on d.tenantid = cycle1action.tenantid and d.claimid = cycle1action.claimid and d.disputeid = cycle1action.disputeid and cycle1action.actionname in ('Dispute Submitted', 'Request Submitted', 'Rapid Dispute Resolution') and cycle1action.cycle = '1' and cycle1action.rn = 1
    --CANCELLATIONS
        left outer join
            cte_recoveryaction as cycle1cancel
        	on d.tenantid = cycle1cancel.tenantid and d.claimid = cycle1cancel.claimid and d.disputeid = cycle1cancel.disputeid and cycle1cancel.cycle = '1' and cycle1cancel.actionname in ('Dispute Recalled', 'Recovery Not Pursued', 'Recovery Cancelled', 'Chargeback Rejected') and cycle1cancel.rn = 1
    --RESPONSES
        left outer join
            cte_recoveryaction as cycle1response
                on d.tenantid = cycle1response.tenantid and d.claimid = cycle1response.claimid and d.disputeid = cycle1response.disputeid and (cycle1response.actionname in ('Dispute Accepted - Partial', 'Dispute Accepted - Full', 'No Response Received', 'Dispute Declined', 'Representment Not Received', 'Pre-Arbitration Received') or cycle1response.cycle = '1' and cycle1response.stage = 'Response Received') and cycle1response.rn = 1
    --RESPONSE ACTIONS
        left outer join
            cte_recoveryaction as cycle1responseaction
                on d.tenantid = cycle1responseaction.tenantid and d.claimid = cycle1responseaction.claimid and d.disputeid = cycle1responseaction.disputeid and cycle1responseaction.actionname in ('Dispute Response Accepted - Partial', 'Dispute Response Declined', 'Dispute Response Accepted - Full', 'Inbound Pre-Arbitration Declined', 'Dispute Response Timeframe Expired', 'Dispute Timeframe Expired', 'Inbound Pre-Arbitration Timeframe Expired') and (cycle1responseaction.rn = 1 or cycle1responseaction.rn = 2)
    --RESPONSE LIABILITY ACTIONS
        left outer join
            cte_recoveryaction as cycle1liability
                on d.tenantid = cycle1liability.tenantid and d.claimid = cycle1liability.claimid and d.disputeid = cycle1liability.disputeid and cycle1liability.actionname in ('Exception Denied - Deny Dispute', 'Exception Denied - Write Off', 'Issuer Liability Accepted - Deny Dispute', 'Issuer Liability Accepted - Write Off', 'Dispute Response Accepted - Full - Deny Dispute', 'Dispute Response Accepted - Full - Write Off', 'Inbound Pre-Arbitration Accepted - Full - Deny Dispute', 'Inbound Pre-Arbitration Accepted - Full - Write Off') and cycle1liability.rn = 1
--CYCLE 2
    --ACTIONS
        left outer join
            cte_recoveryaction as cycle2action
                on d.tenantid = cycle2action.tenantid and d.claimid = cycle2action.claimid and d.disputeid = cycle2action.disputeid and cycle2action.actionname in ('Arbitration Chargeback Submitted', 'Request Submitted', 'Pre-Arbitration Submitted') and cycle2action.cycle = '2' and cycle2action.rn = 1
    --CANCELLATIONS
        left outer join 
            cte_recoveryaction as cycle2cancel
        	on d.tenantid = cycle2cancel.tenantid and d.claimid = cycle2cancel.claimid and d.disputeid = cycle2cancel.disputeid and cycle2cancel.actionname ilike '%Pre-Arbitration Withdrawn%' and cycle2cancel.rn = 1
    --RESPONSES
        left outer join
            cte_recoveryaction as cycle2response
                on d.tenantid = cycle2response.tenantid and d.claimid = cycle2response.claimid and d.disputeid = cycle2response.disputeid and (cycle2response.actionname in ('Inbound Pre-Arbitration Response Accepted', 'Pre-Arbitration Accepted - Full', 'Pre-Arbitration Accepted - Partial', 'Inbound Arbitration Received', 'Pre-Arbitration Declined') or cycle2response.cycle = '2' and cycle2response.stage = 'Response Received') and cycle2response.rn = 1
    --RESPONSE ACTIONS
        left outer join
            cte_recoveryaction as cycle2responseaction
                on d.tenantid = cycle2responseaction.tenantid and d.claimid = cycle2responseaction.claimid and d.disputeid = cycle2responseaction.disputeid and cycle2responseaction.actionname in ('Pre-Arbitration Response Declined', 'Pre-Arbitration Response Accepted - Partial', 'Pre-Arbitration Response Accepted - Full', 'Pre-Arbitration Timeframe Expired') and (cycle2responseaction.rn = 1 or cycle2responseaction.rn = 2)
    --RESPONSE LIABILITY ACTIONS
        left outer join
            cte_recoveryaction as cycle2liability
                on d.tenantid = cycle2liability.tenantid and d.claimid = cycle2liability.claimid and d.disputeid = cycle2liability.disputeid and cycle2liability.actionname in ('Pre-Arbitration Response Accepted - Full - Deny Dispute','Pre-Arbitration Response Accepted - Full - Write Off','Pre-Arbitration Withdrawn - Deny Dispute','Pre-Arbitration Withdrawn - Write Off') and cycle2liability.rn = 1
--CYCLE 3
    --ACTIONS
        left outer join
            cte_recoveryaction as cycle3action
                on d.tenantid = cycle3action.tenantid and d.claimid = cycle3action.claimid and d.disputeid = cycle3action.disputeid and (cycle3action.actionname in ('Pre-Arbitration Escalated', 'Arbitration Submitted') or cycle3action.actionname in ('Request Submitted') and cycle3action.cycle = '3') and cycle3action.rn = 1
    --CANCELLATIONS
        left outer join 
            cte_recoveryaction as cycle3cancel
        	on d.tenantid = cycle3cancel.tenantid and d.claimid = cycle3cancel.claimid and d.disputeid = cycle3cancel.disputeid and cycle3cancel.actionname ilike '%Arbitration Withdrawn%' and cycle3cancel.cycle = '3' and cycle3cancel.rn = 1
    --RESPONSES
        left outer join
            cte_recoveryaction as cycle3response
                on d.tenantid = cycle3response.tenantid and d.claimid = cycle3response.claimid and d.disputeid = cycle3response.disputeid and (cycle3response.actionname in ('Arbitration Won', 'Arbitration Lost', 'Arbitration Split', 'Arbitration Accepted') or cycle3response.cycle = '3' and cycle3response.stage = 'Response Received') and cycle3response.rn = 1
    --RESPONSE LIABILITY ACTIONS
        left outer join
            cte_recoveryaction as cycle3liability
                on d.tenantid = cycle3liability.tenantid and d.claimid = cycle3liability.claimid and d.disputeid = cycle3liability.disputeid and cycle3liability.actionname in ('Arbitration Lost - Deny Dispute', 'Arbitration Lost - Write Off', 'Arbitration Withdrawn - Deny Dispute', 'Arbitration Withdrawn - Write Off') and cycle3liability.rn = 1
  
where 
    d.tenantid = 'YOURTENANTIDHERE'
    AND cycle1action.performedondatetime is not null
    AND d.resolveddatetime =:daterange