SELECT
    d.tenantid,
    t.name,
    d.clientid,
    cl.commonname,
    c.claimtype,
    c.reasontype,
    CAMEL_CASE_TO_STRING(c.reason) AS "REASON",
    (CASE
        WHEN c.rege45datetime IS NOT NULL THEN 'Reg E'
        WHEN c.rege90datetime IS NOT NULL THEN 'Reg E'
        WHEN c.regz90datetime IS NOT NULL THEN 'Reg Z'
        WHEN c.isfcra = TRUE THEN 'FCRA'
        ELSE 'No Coverage'
    END) AS "Regulatory Coverage",
    (CASE
        WHEN d.acquirernetwork = 'MasterCard' THEN 'Mastercard'
        WHEN d.acquirernetwork = 'Debit Mastercard' THEN 'Mastercard Debit'
        WHEN d.acquirernetwork IS NULL THEN 'Unknown'
        ELSE d.acquirernetwork
    END) AS "Network",
    c.channelorigin,
    c.cardtype,
    c.cardbin,
    d.claimid,
    d.disputeid,
    c.status,
    d.status,
    d.decision,
    CAMEL_CASE_TO_STRING(d.denyreason) AS "DenyReason",
    c.customercontactdate,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.investigationcompletedatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000'),
    (CASE
        WHEN d.status = 'Resolved-Paid' THEN 'Paid'
        WHEN d.status = 'Resolved-Denied' THEN 'Denied'
        WHEN d.status = 'Resolved-NoPosting' THEN 'Never Posted'
    END) as "Resolution",
    DATEDIFF(d, d.createdatetime, d.resolveddatetime) AS "ResolutionTimeDays",
    DATEDIFF(d, c.customercontactdate, to_date(d.investigationcompletedatetime)) AS CustomerResolutionTimeDays,
    d.authorizationstatus,
    d.merchantcategorycode,
    d.merchantname,
    d.posentrymode,
    d.description,
    d.disputeamount,
    d.amount,
    d.accountholdertotal,
    IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid), 0) AS "MerchantCreditAmount",
    IFNULL((CASE
        WHEN d.transactiontype = 'ACH' THEN (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid AND r.actionname = 'Recovery Pursued') - (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid AND r.actionname = 'Recovery Cancelled')
        ELSE (SELECT max(r.cycle) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid)
    END), 0) AS "Recovery Attempts",
    d.merchanttotal * -1 AS "RecoveredAmount",
    d.losstotal * -1 AS "LossAmount",
    d.disputeamount - d.accountholdertotal - "MerchantCreditAmount" AS "DeniedAmount",
    "MerchantCreditAmount" + "RecoveredAmount" + "DeniedAmount" AS "RecapturedAmount",
    (CASE
        WHEN d.isreopened = TRUE THEN TRUE
        ELSE FALSE
    END) AS "Reopened"
FROM dispute AS d
JOIN tenant AS t
    ON d.tenantid = t.tenantid
JOIN claim AS c
    ON d.tenantid = c.tenantid AND d.claimid = c.claimid
JOIN client AS cl
    ON d.tenantid = cl.tenantid AND d.clientid = cl.clientid
WHERE
    d.status IN ('Resolved-Paid', 'Resolved-Denied', 'Resolved-NoPosting')