select
	d.tenantid,
    tenant.name,
    d.clientid,
    client.commonname,
    c.claimtype,
    c.reasontype,
    c.reason,
    d.acquirernetwork,
    d.claimid,
    d.disputeid,
    (CASE
        WHEN d.status LIKE '%Resolved%' THEN 'Resolved'
        ELSE 'Open'
    END) as statustype,
    d.status,
    c.customercontactdate,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000'),
    d.disputeamount,
    (case
        when r1.actionname is null then 'Pending Response'
        else 'Completed'
    end) as cycle1Status,
    a1.recoveryoptionlabel as cycle1recoveryoption,
    (case
        when a1.actionname = 'Dispute Submitted' then 'Chargeback'
        else a1.actionname
    end) as Action,
    TO_TIMESTAMP_TZ(a1.performedondatetime::varchar || ' +0000'),
    a1.amount,
    r1.actionname,
    TO_TIMESTAMP_TZ(r1.performedondatetime::varchar || ' +0000'),
    r1.amount,
    (case
        when r1.actionname IN ('No Funds Recovered', 'Response Received - No Recovery', 'Dispute Declined', 'Pre-Arbitration Received') then 'No Recovery'
        when r1.actionname IN ('Full Amount Recovered', 'Rapid Dispute Resolution', 'Response Received - Full Amount Recovered', 'Dispute Accepted - Full', 'No Response Received', 'Representment Not Received') then 'Recovered'
        when r1.actionname IN ('Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Dispute Accepted - Partial') then 'Recovered'
        else 'N/A'  -- recalls, rejects
    end) as cycle1Outcome,
    (case
        when a2.actionname is null then 'Not Attempted'
        when r2.actionname is null then 'Pending Response'
        else 'Completed'
    end) as Cycle2Status,
    a2.recoveryoptionlabel as cycle2recoveryoption,
    (case
        when a2.actionname IN ('Inbound Pre-Arbitration Declined', 'Pre-Arbitration Submitted') then 'Pre-Arbitration'
        when a2.actionname = 'Arbitration Chargeback Submitted' then 'Arbitration Chargeback'
        else a2.actionname
    end) as Action,
    TO_TIMESTAMP_TZ(a2.performedondatetime::varchar || ' +0000'),
    a2.amount,
    r2.actionname,
    TO_TIMESTAMP_TZ(r2.performedondatetime::varchar || ' +0000'),
    r2.amount,
    (case
        when r2.actionname IN ('Inbound Arbitration Received', 'No Funds Recovered', 'Pre-Arbitration Declined', 'Response Received - No Recovery') then 'No Recovery'
        when r2.actionname IN ('Full Amount Recovered', 'Inbound Pre-Arbitration Response Accepted', 'Pre-Arbitration Accepted - Full', 'Response Received - Full Amount Recovered', 'No Inbound Arbitration Received', 'No Pre-Arbitration Response Received') then 'Recovered'
        when r2.actionname IN ('Partial Amount Recovered', 'Pre-Arbitration Accepted - Partial', 'Response Received - Partial Amount Recovered') then 'Recovered'
        else 'N/A' 
    end) as Cycle2Outcome,
    (case
        when a3.actionname is null then 'Not Attempted'
        when r3.actionname is null then 'Pending Response'
        else 'Completed'
    end) as Cycle3Status,
    a3.recoveryoptionlabel as cycle3recoveryoption,
    (case
        when a3.actionname IN ('Pre-Arbitration Escalated', 'Arbitration Submitted') then 'Arbitration'
        else a3.actionname
    end) as Action,
    TO_TIMESTAMP_TZ(a3.performedondatetime::varchar || ' +0000'),
    a3.amount,
    r3.actionname,
    TO_TIMESTAMP_TZ(r3.performedondatetime::varchar || ' +0000'),
    r3.amount,
    (case
        when r3.actionname IN ('Arbitration Lost', 'No Funds Recovered', 'Response Received - No Recovery') then 'No Recovery'
        when r3.actionname IN ('Arbitration Accepted', 'Arbitration Won', 'Full Amount Recovered', 'Response Received - Full Amount Recovered') then 'Recovered'
        when r3.actionname IN ('Arbitration Split', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered') then 'Recovered'
        else 'N/A' 
    end) as Cycle3Outcome,
    (case
        when Cycle3Outcome = 'Recovered' or Cycle2Outcome = 'Recovered' or Cycle1Outcome = 'Recovered' then 'Recovered'
        when r1.actionname is null then 'Not Attempted'
        else 'No Recovery'
    end) as OverallOutcome
from
    dispute as d
join claim as c on d.tenantid = c.tenantid and d.claimid = c.claimid
join tenant as tenant on d.tenantid = tenant.tenantid
join client as client on d.tenantid = client.tenantid and d.clientid = client.clientid

left join recoveryaction as a1 on a1.tenantid = d.tenantid and a1.disputeid = d.disputeid and a1.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)') and (a1.actionname = 'Dispute Submitted' or a1.actionname in ('Request Submitted', 'Review Initiated') and a1.cycle = '1') and a1.performedondatetime = (select max(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and (b.actionname = 'Dispute Submitted' or b.actionname in ('Request Submitted', 'Review Initiated') and b.cycle = '1') and b.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)'))

left join recoveryaction as r1 on r1.tenantid = d.tenantid and r1.disputeid = d.disputeid and r1.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Inbound Arbitration Received', 'Rapid Dispute Resolution', 'Dispute Accepted - Partial', 'Dispute Accepted - Full', 'Dispute Declined', 'Pre-Arbitration Received', 'No Response Received', 'Representment Not Received', 'Chargeback Rejected', 'Dispute Recalled') and r1.performedondatetime = (select min(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and b.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Inbound Arbitration Received', 'Rapid Dispute Resolution', 'Dispute Accepted - Partial', 'Dispute Accepted - Full', 'Dispute Declined', 'Pre-Arbitration Received', 'No Response Received', 'Representment Not Received', 'Chargeback Rejected', 'Dispute Recalled') and b.performedondatetime >= a1.performedondatetime)

left join recoveryaction as a2 on a2.tenantid = d.tenantid and a2.disputeid = d.disputeid and a2.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)') and (a2.actionname IN ('Arbitration Chargeback Submitted', 'Pre-Arbitration Submitted', 'Inbound Pre-Arbitration Declined') or a2.actionname in ('Request Submitted', 'Review Initiated') and a2.cycle = '2') and a2.performedondatetime = (select max(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and (b.actionname IN ('Arbitration Chargeback Submitted', 'Pre-Arbitration Submitted', 'Inbound Pre-Arbitration Declined') or b.actionname in ('Request Submitted', 'Review Initiated') and b.cycle = '2') and b.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)'))

left join recoveryaction as r2 on r2.tenantid = d.tenantid and r2.disputeid = d.disputeid and r2.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Inbound Pre-Arbitration Response Accepted', 'Pre-Arbitration Accepted - Full', 'Pre-Arbitration Accepted - Partial', 'Inbound Arbitration Received', 'Pre-Arbitration Declined', 'No Inbound Arbitration Received', 'No Pre-Arbitration Response Received') and r2.performedondatetime = (select min(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and b.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Inbound Pre-Arbitration Response Accepted', 'Pre-Arbitration Accepted - Full', 'Pre-Arbitration Accepted - Partial', 'Inbound Arbitration Received', 'Pre-Arbitration Declined', 'No Inbound Arbitration Received', 'No Pre-Arbitration Response Received') and b.performedondatetime >= a2.performedondatetime)

left join recoveryaction as a3 on a3.tenantid = d.tenantid and a3.disputeid = d.disputeid and a3.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)') and (a3.actionname IN ('Pre-Arbitration Escalated', 'Arbitration Submitted') or a3.actionname in ('Request Submitted', 'Review Initiated') and a3.cycle = '3') and a3.performedondatetime = (select max(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and (b.actionname IN ('Pre-Arbitration Escalated', 'Arbitration Submitted') or b.actionname in ('Request Submitted', 'Review Initiated') and b.cycle = '3') and b.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)'))

left join recoveryaction as r3 on r3.tenantid = d.tenantid and r3.disputeid = d.disputeid and r3.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Arbitration Won', 'Arbitration Lost', 'Arbitration Split', 'Arbitration Accepted') and r3.performedondatetime = (select min(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and b.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Arbitration Won', 'Arbitration Lost', 'Arbitration Split', 'Arbitration Accepted') and b.performedondatetime >= a3.performedondatetime)


--NOTES:  Excludes unduly enriched (debits to claimant)