SELECT --top 2500
    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 RegulatoryCoverage,
    d.acquirernetwork"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.transactiontypeclaimid,
    d.claimiddisputeid,
    c.status,
    d.disputeidstatus,
    csd.statedecision,
    CAMEL_CASE_TO_STRING(d.statusdenyreason), AS "DenyReason",
  (CASE  c.customercontactdate,
      WHEN d.status LIKE CONCAT('%','-','%') THEN SUBSTRING(d.status, 1, CHARINDEX('-', d.status)-1   TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.investigationcompletedatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000'),
    )(CASE
        ELSEWHEN d.status = 'Resolved-Paid' THEN 'Paid'
  END) as statustype,    WHEN d.decision,
 status = 'Resolved-Denied' THEN 'Denied'
  CAMEL_CASE_TO_STRING(d.denyreason) AS DenyReason,    WHEN d.transactiondate,
    c.customercontactdate,status = 'Resolved-NoPosting' THEN 'Never Posted'
    END)  d.createdatetimeas "Resolution",
    to_dateDATEDIFF(d.investigationcompletedatetime, d.createdatetime, d.resolveddatetime) AS InvestigationDate"ResolutionTimeDays",
    DATEDIFF(d, c.customercontactdate, to_date(d.resolveddatetimeinvestigationcompletedatetime)) AS ResolvedDateCustomerResolutionTimeDays,
    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.statustransactiontype ILIKE= '%resolved%ACH' THEN (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.merchanttotaltenantid * -1
        ELSE 0
    END) AS RecoveredAmount,
    d.losstotal * -1 AS LossAmount,
    (CASE
        WHEN d.status ILIKE '%resolved%' AND d.originaldisputeamount IS NOT NULL THEN d.originaldisputeamount - d.accountholdertotal - MerchantCreditAmount
        WHEN d.status ILIKE '%resolved%' THENAND 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",
 ELSE 0  (CASE
  END) AS DeniedAmount,    WHEN d.isreopened = TRUE THEN TRUE
        ELSE FALSE
    END) AS "Reopened"
FROM dispute AS d
JOIN tenant AS t
    onON d.tenantid = t.tenantid
JOIN claim AS c
    onON d.tenantid = c.tenantid andAND d.claimid = c.claimid
JOIN client AS cl
    onON d.tenantid = cl.tenantid andAND d.clientid = cl.clientid
LEFTWHERE
JOIN claim_state AS cs ON csd.status = c.status IN ('Resolved-Paid', 'Resolved-Denied', 'Resolved-NoPosting')