SELECT 
    c.tenantid,
    t.name,
    c.clientid,
    cl.legalname,
    c.claimtype,
    c.reasontype,
    c.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,
    c.cardtype,
    c.cardbin,
    c.channelorigin,
    c.claimid,
    c.claimamount,
    cs.state,
    CAMEL_CASE_TO_STRING(c.status),
    TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
    c.customercontactdate,
    TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000'),
    (SELECT COUNT(*) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeCount,
    (SELECT SUM(d.disputeamount) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeAmount,
    DATEDIFF(d, createdatetime, CURRENT_DATE)
FROM claim AS c
JOIN tenant AS t ON c.tenantid = t.tenantid
JOIN client AS cl ON c.tenantid = cl.tenantid AND c.clientid = cl.clientid
LEFT JOIN claim_state AS cs ON cs.status = c.status
ORDER BY
    c.createdatetime DESC