<?php
/**
 * Collection Dashboard — Blue Central-style report viewer for PY-Medical SNF.
 *
 *  Tabs: Dashboard · Summary · Pivot (Look Up) · Detail (Raw) · History
 *
 *  All queries run directly against the indexed `collection_raw` table.
 *  Per-tab lazy loading keeps every tab under ~1 s even at 200k+ rows.
 */
require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/collection-engine.php';   // shared claim-status filter helpers
// Allow BOTH internal staff and client-portal users. Internal users keep the normal route gate
// (require_login('internal') → report.view) + per-report access. A CLIENT opens this dashboard in a
// locked-down READ-ONLY "client mode": strictly scoped to a report that belongs to their OWN client
// and is granted to them (report_access). The client portal shell replaces the internal admin chrome.
$cu         = current_user();
$clientMode = $cu && (($cu['user_type'] ?? '') === 'client');
$me  = require_login($clientMode ? 'client' : 'internal');
$pdo = db();
$clientRawOk = true; $clientCanDownload = true;   // internal users: unrestricted; set per-grant for client mode below

/* ---------------- Resolve the report context ---------------- */
$reportId = (int) ($_GET['id'] ?? 0);
if ($clientMode) {
    // No "default report" for clients — they always open a specific, granted report of their client.
    if ($reportId <= 0) redirect('/client/overall.php');
    // Visible to a client if the (active) report is under their HOME client OR has been explicitly granted to
    // them via report_access (a client contact may oversee several clients). Raw rows + export stay opt-in below.
    $own = $pdo->prepare("SELECT p.client_id FROM reports r JOIN processes p ON p.id=r.process_id
                          WHERE r.id=? AND r.deleted_at IS NULL AND r.status='active'");
    $own->execute([$reportId]); $rc = $own->fetchColumn();
    if ($rc === false || ((int)$rc !== (int)($me['client_id'] ?? 0) && !user_can_view_report((int)$me['id'], $reportId))) {
        http_response_code(403); exit('You do not have access to this report.');
    }
    // Client portal branding for the shell partials — brand to the REPORT's OWN client (a client contact may
    // oversee several clients), NOT the viewer's home client, so the banner names the client being viewed.
    $cpB = $pdo->prepare("SELECT c.name cn, cb.primary_color, cb.login_slug, cb.allow_raw_data
        FROM clients c LEFT JOIN client_branding cb ON cb.client_id=c.id WHERE c.id=?");
    $cpB->execute([(int) $rc]); $cpB = $cpB->fetch() ?: [];
    $cp_primary = $cpB['primary_color'] ?: '#206bc4';
    $cp_brand   = $cpB['cn'] ?? 'Reports';
    $cp_slug    = (string) ($cpB['login_slug'] ?? '');
    // Row-level RAW data (Detail tab + Excel/CSV export) is opt-in for clients: it requires BOTH the
    // client_branding.allow_raw_data flag AND a report_access.can_download grant (the new-client auto-grant gives
    // View only). KPIs / charts / aggregate tables always render. This stops a client bulk-exporting — or browsing
    // — raw claim-line PHI they were never granted. SECURITY: keep these false unless explicitly enabled.
    $clientRawOk = !empty($cpB['allow_raw_data']);
    $dlq = $pdo->prepare("SELECT can_download FROM report_access WHERE user_id=? AND report_id=?");
    $dlq->execute([(int) $me['id'], $reportId]);
    $clientCanDownload = ((int) $dlq->fetchColumn() === 1);
} else {
    if ($reportId > 0) require_report_access($reportId);   // SysAdmin-granted per-report access
}
// Shell selector — client portal chrome (no admin nav) for clients, the internal admin shell otherwise.
$cpHead = __DIR__ . '/../includes/' . ($clientMode ? 'client-portal-head.php' : 'header.php');
$cpFoot = __DIR__ . '/../includes/' . ($clientMode ? 'client-portal-foot.php' : 'footer.php');
// Whether to SHOW export (download) buttons. Internal: always. Client: only with download + raw-data grants.
$showExport = !$clientMode || ($clientCanDownload && $clientRawOk);
$reportCtx = null;
if ($reportId) {
    $st = $pdo->prepare('SELECT r.*, p.name AS pname, p.code AS pcode,
                                c.name AS cname, c.code AS ccode, c.id AS cid,
                                v.name AS vname
        FROM reports r JOIN processes p ON p.id = r.process_id
        JOIN clients c ON c.id = p.client_id JOIN verticals v ON v.id = c.vertical_id
        WHERE r.id = ? AND r.deleted_at IS NULL');
    $st->execute([$reportId]);
    $reportCtx = $st->fetch();
}
if (!$reportCtx) {
    $st = $pdo->query("SELECT r.*, p.name AS pname, p.code AS pcode,
                              c.name AS cname, c.code AS ccode, c.id AS cid, v.name AS vname
        FROM reports r JOIN processes p ON p.id = r.process_id
        JOIN clients c ON c.id = p.client_id JOIN verticals v ON v.id = c.vertical_id
        WHERE r.code = 'COLL-DASH' AND r.deleted_at IS NULL LIMIT 1");
    $reportCtx = $st ? $st->fetch() : false;
    if ($reportCtx) {
        $reportId = (int) $reportCtx['id'];
        // SECURITY: the DEFAULT report (no ?id) must be access-gated too — otherwise a user who can merely open this
        // page (e.g. a per-page grant) but holds no report_access grant would read/export the default report's raw
        // PHI. Mirrors the ?id path (require_report_access above). Client-mode access is enforced separately (branding
        // + report_access, lines ~44-50). SysAdmin passes; a non-granted internal user is redirected to workspace.
        if (!$clientMode) require_report_access($reportId);
    }
}
if (!$reportCtx) {
    $pageTitle = 'Collection Dashboard';
    require $cpHead;
    echo '<div class="container-xl"><div class="card border-warning mt-4 p-4">'
       . '<h2>Migration not applied</h2>'
       . '<p>Run <code>mysql -u root medmecs_mis &lt; sql/collection-dashboard.sql</code> then refresh.</p>'
       . '</div></div>';
    require $cpFoot;
    exit;
}

/* ---------------- Required tables ---------------- */
try {
    $pdo->query('SELECT 1 FROM collection_raw LIMIT 1');
    $pdo->query('SELECT 1 FROM facility_mappings LIMIT 1');
    $pdo->query('SELECT 1 FROM collection_uploads LIMIT 1');
} catch (Throwable $e) {
    $pageTitle = 'Collection Dashboard';
    require $cpHead;
    echo '<div class="container-xl"><div class="alert alert-danger mt-4">'
       . '<h3><i class="ti ti-alert-octagon me-1"></i>Collection schema not applied</h3>'
       . '<p>Run <code>mysql -u root medmecs_mis &lt; sql/collection-dashboard.sql</code>.</p>'
       . '<p class="small mb-0">' . e($e->getMessage()) . '</p></div></div>';
    require $cpFoot;
    exit;
}

/* ---------------- Filter state (multi-select — every dimension accepts 0..N values) ---------------- */
$tab = $_GET['tab'] ?? 'dashboard';
if ($clientMode && !$clientRawOk && $tab === 'detail') $tab = 'dashboard';   // raw row-level data is opt-in for clients

/* Normalise a GET value into a clean array of strings.  Accepts a single value,
   an array, or null; deduplicates and drops empties. */
$cleanArr = function ($raw): array {
    if ($raw === null) return [];
    $arr = is_array($raw) ? $raw : [$raw];
    $arr = array_map(fn($v) => trim((string) $v), $arr);
    $arr = array_filter($arr, fn($v) => $v !== '');
    return array_values(array_unique($arr));
};
$fFacilities = $cleanArr($_GET['facility'] ?? null);
$fProviders  = $cleanArr($_GET['provider'] ?? null);
$fYears      = array_values(array_filter(array_map('intval', $cleanArr($_GET['year']  ?? null)), fn($n) => $n > 0));
$fMonths     = array_values(array_filter(array_map('intval', $cleanArr($_GET['month'] ?? null)), fn($n) => $n >= 1 && $n <= 12));
$statusOpts  = collection_status_options();
$fStatuses   = array_values(array_filter($cleanArr($_GET['status'] ?? null), fn($s) => isset($statusOpts[$s]) && $s !== ''));
/* Deductible filter: '' = all, '1' = deductible only, '0' = non-deductible. Applied on every tab. */
$fDeductible = (string)($_GET['deductible'] ?? '');
if ($fDeductible !== '0' && $fDeductible !== '1') $fDeductible = '';

/* Specialty filter — a specialty groups a set of CPT/charge codes (ar_cpt_specialty,
   maintained on admin/ar-specialty.php). We scope collection_raw by charge_code IN the
   specialty's CPTs. Guarded so the page still works pre-migration / if the table is absent. */
$fSpecialty     = trim((string)($_GET['specialty'] ?? ''));
$specialties    = [];
$specialtyTableOk = false;
try {
    $specialties = $pdo->query("SELECT DISTINCT specialty FROM ar_cpt_specialty ORDER BY specialty")
        ->fetchAll(PDO::FETCH_COLUMN) ?: [];
    $specialtyTableOk = true;
} catch (Throwable $t) {
    $specialties = [];
    $fSpecialty  = ''; // table missing → ignore any incoming filter
}
if ($fSpecialty !== '' && $specialtyTableOk && !in_array($fSpecialty, $specialties, true)) {
    $fSpecialty = ''; // unknown specialty → treat as no filter
}

/**
 * Build the WHERE-clause fragment that scopes collection_raw queries for a report.
 * Includes the report's own rows PLUS any (source, facility) rules registered in
 * report_facility_mirror — a "virtual" rollup that doesn't duplicate data.
 *
 * Returns ['sql' => '(...)', 'params' => [...]] — wrap the sql in the outer WHERE
 * builder normally (e.g. $where[] = $scope['sql']; foreach ($scope['params'] as $p) $params[] = $p;).
 *
 * Cached per-request via static so we don't re-query the rules table on every call.
 */
function collection_scope_for_report(PDO $pdo, int $reportId): array
{
    static $cache = [];
    if (isset($cache[$reportId])) return $cache[$reportId];
    try {
        // SECURITY (multi-tenant): only include mirror SOURCE reports that belong to the SAME CLIENT as the
        // target. The rollup expands a report's scope to also pull other reports' facility rows; without this
        // same-client fence a mis-configured cross-client mirror rule would leak another client's PHI — critical
        // now that this dashboard is reachable by client-portal users. Cross-client sources are silently dropped.
        $st = $pdo->prepare("SELECT m.source_report_id, m.facility
            FROM report_facility_mirror m
            JOIN reports tr ON tr.id = m.target_report_id JOIN processes tp ON tp.id = tr.process_id
            JOIN reports sr ON sr.id = m.source_report_id JOIN processes sp ON sp.id = sr.process_id
            WHERE m.target_report_id = ? AND sp.client_id = tp.client_id
            ORDER BY m.source_report_id");
        $st->execute([$reportId]);
        $rules = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    } catch (Throwable $t) {
        // Table doesn't exist yet (pre-migration) → fall back to plain scope.
        $rules = [];
    }
    if (!$rules) {
        return $cache[$reportId] = ['sql' => 'report_id = ?', 'params' => [$reportId]];
    }
    // Group rules by source so we emit one IN-clause per source instead of per facility.
    $bySource = [];
    foreach ($rules as $r) $bySource[(int) $r['source_report_id']][] = (string) $r['facility'];
    $clauses = ['report_id = ?'];
    $params  = [$reportId];
    foreach ($bySource as $srcId => $facs) {
        $ph = implode(',', array_fill(0, count($facs), '?'));
        $clauses[] = "(report_id = ? AND facility IN ($ph))";
        $params[]  = $srcId;
        foreach ($facs as $f) $params[] = $f;
    }
    return $cache[$reportId] = [
        'sql'    => '(' . implode(' OR ', $clauses) . ')',
        'params' => $params,
    ];
}

/* Multi-tenant scope: every collection_raw query MUST be scoped by report_id.
   For rollup reports (12 Blue Central et al.), `collection_scope_for_report` expands
   this into a UNION-style OR that also includes (source_report_id, facility) pairs
   listed in `report_facility_mirror` — the mirror is applied virtually at query time
   rather than via row-copying.  Single-report queries get the plain `report_id = ?`. */
$scope  = collection_scope_for_report($pdo, $reportId);
$where  = [$scope['sql']];
$params = $scope['params'];

/* Is the `deductible` flag column provisioned? (Added by migration deductible_mapping_v1, run at
   login.) If provisioning was deferred/failed we degrade gracefully — no filter, KPI or column —
   rather than breaking the dashboard with a missing-column error. */
$deductibleReady = false;
try { $deductibleReady = function_exists('app_col_exists') ? app_col_exists($pdo, 'collection_raw', 'deductible') : false; } catch (Throwable $e) {}
if (!$deductibleReady) $fDeductible = '';

/* Build an IN (?,?,?) fragment and append its params; returns '' when $values is empty. */
$inClause = function (string $col, array $values) use (&$params): string {
    if (!$values) return '';
    $ph = implode(',', array_fill(0, count($values), '?'));
    foreach ($values as $v) $params[] = $v;
    return "$col IN ($ph)";
};
if ($f = $inClause('facility',      $fFacilities)) $where[] = $f;
if ($f = $inClause('provider_name', $fProviders))  $where[] = $f;
if ($f = $inClause('dos_year',      $fYears))      $where[] = $f;
if ($f = $inClause('dos_month',     $fMonths))     $where[] = $f;
if ($deductibleReady && ($fDeductible === '0' || $fDeductible === '1')) { $where[] = 'deductible = ?'; $params[] = (int)$fDeductible; }

/* Specialty → the rows whose charge_code belongs to that specialty's CPT set.
   Relies on collection_raw.charge_code being stored UPPERCASE (upcased on ingest in
   includes/collection-engine.php) to match ar_cpt_specialty.cpt_code (also uppercase). */
if ($fSpecialty !== '') {
    $where[]  = "charge_code IN (SELECT cpt_code FROM ar_cpt_specialty WHERE specialty = ?)";
    $params[] = $fSpecialty;
}

/* Multi-status → OR the per-status SQL fragments together. */
if ($fStatuses) {
    $sFrags = [];
    foreach ($fStatuses as $s) {
        [$frag, $sParams] = collection_status_sql($s, $reportId);
        if ($frag) { $sFrags[] = "($frag)"; foreach ($sParams as $p) $params[] = $p; }
    }
    if ($sFrags) $where[] = '(' . implode(' OR ', $sFrags) . ')';
}
$whereSql = implode(' AND ', $where);

/* ---------------- Helpers ---------------- */
function qRow(PDO $pdo, string $sql, array $params = []) {
    $st = $pdo->prepare($sql); $st->execute($params); return $st->fetch();
}
function qAll(PDO $pdo, string $sql, array $params = []) {
    $st = $pdo->prepare($sql); $st->execute($params); return $st->fetchAll();
}
/* Subset pre-computed per-month cache rows by a year/month filter (in PHP, no DB hit).
   The cached by_month / summary_subs rows are already aggregated across all
   facilities & providers, so they're valid to reuse whenever NO facility/provider
   filter is active — we just keep the months the filter asks for. */
function filterMonthRows(array $rows, array $fYears, array $fMonths): array {
    if (!$fYears && !$fMonths) return $rows;
    return array_values(array_filter($rows, function ($r) use ($fYears, $fMonths) {
        if ($fYears  && !in_array((int)$r['dos_year'],  $fYears,  true)) return false;
        if ($fMonths && !in_array((int)$r['dos_month'], $fMonths, true)) return false;
        return true;
    }));
}

/* Build the &f_facility[]=…&f_provider[]=…&f_year[]=…&f_month[]=…&status[]=… query
   string the drill-down endpoint expects.  Called five times below — one per tab. */
function drillFilterQS(array $fFacilities, array $fProviders, array $fYears, array $fMonths, array $fStatuses): string {
    global $fSpecialty, $fDeductible;
    $qs = '';
    foreach ($fFacilities as $f) $qs .= '&f_facility[]=' . rawurlencode((string) $f);
    foreach ($fProviders  as $p) $qs .= '&f_provider[]=' . rawurlencode((string) $p);
    foreach ($fYears      as $y) $qs .= '&f_year[]='     . (int) $y;
    foreach ($fMonths     as $m) $qs .= '&f_month[]='    . (int) $m;
    foreach ($fStatuses   as $s) $qs .= '&status[]='     . rawurlencode((string) $s);
    if ($fSpecialty !== '') $qs .= '&f_specialty=' . rawurlencode((string) $fSpecialty);
    if ($fDeductible !== '') $qs .= '&f_deductible=' . rawurlencode((string) $fDeductible);
    return $qs;
}
function fmt_money($v) { return '$' . number_format((float)$v, 2); }
/* Client wants the FULL accurate $ amount everywhere — not K/M abbreviated.
   Kept as a separate function (vs. fmt_money) so a future toggle is one-line. */
function fmt_money_short($v) {
    return '$' . number_format((float) $v, 2);
}
function fmt_int($v) { return number_format((int)$v); }
function dollar_cells($v): string {
    $v = (float) $v;
    if ($v == 0) return '<td class="dollar-col">$</td><td class="dollar-num">-</td>';
    return '<td class="dollar-col">$</td><td class="dollar-num">' . number_format($v, 2) . '</td>';
}
function pct($num, $den) { return $den > 0 ? ($num/$den)*100 : 0; }
function col_url(array $extra = []): string {
    global $reportId, $tab, $fFacilities, $fProviders, $fYears, $fMonths, $fStatuses, $fSpecialty, $fDeductible;
    $p = ['id'=>$reportId, 'tab'=>$tab];
    if ($fFacilities) $p['facility'] = $fFacilities;
    if ($fProviders)  $p['provider'] = $fProviders;
    if ($fYears)      $p['year']     = $fYears;
    if ($fMonths)     $p['month']    = $fMonths;
    if ($fStatuses)   $p['status']   = $fStatuses;
    if ($fSpecialty !== '') $p['specialty'] = $fSpecialty;
    if ($fDeductible !== '') $p['deductible'] = $fDeductible;
    foreach ($extra as $k => $v) {
        if ($v === null || $v === '' || $v === []) unset($p[$k]);
        else $p[$k] = $v;
    }
    return url('/admin/collection.php?' . http_build_query($p));
}

/* ---- Deductible surfacing helpers. `deductible` is a per-visit flag baked onto collection_raw by
   admin/deductible-mapping.php. Because deductible = 1 is a small SUBSET of rows, both queries stay
   cheap and temp-safe even on tabs not rewritten to the per-visit rollup — no 4 GB spill risk. All
   callers gate on $deductibleReady, and each call is wrapped in try/catch as a second belt. ---- */
function coll_deductible_total(PDO $pdo, string $whereSql, array $params): array {
    try {
        $r = qRow($pdo, "SELECT COUNT(DISTINCT visit_id) n, COALESCE(SUM(charge_amount),0) charge
                           FROM collection_raw WHERE $whereSql AND deductible = 1", $params);
        return ['n' => (int)($r['n'] ?? 0), 'charge' => (float)($r['charge'] ?? 0)];
    } catch (Throwable $e) { return ['n' => 0, 'charge' => 0.0]; }
}
function coll_deductible_by(PDO $pdo, string $dimExpr, string $whereSql, array $params): array {
    $out = [];
    try {
        foreach (qAll($pdo, "SELECT $dimExpr AS k, COUNT(DISTINCT visit_id) n
                               FROM collection_raw WHERE $whereSql AND deductible = 1
                               GROUP BY $dimExpr", $params) as $row) {
            $out[(string)$row['k']] = (int)$row['n'];
        }
    } catch (Throwable $e) {}
    return $out;
}
/* Deductible visits per aging bucket — SAME domain as the Aging tab's "Claims" (open balance + dos-dated),
   filtered to deductible = 1. CURDATE() inlined so no extra placeholder (params still match $whereSql).
   Used by BOTH the Aging render and its Excel export so the two stay identical. */
function coll_deductible_by_aging(PDO $pdo, string $whereSql, array $params): array {
    $out = [];
    try {
        foreach (qAll($pdo, "
            SELECT CASE WHEN DATEDIFF(CURDATE(), dos) <= 30  THEN '0-30'
                        WHEN DATEDIFF(CURDATE(), dos) <= 60  THEN '31-60'
                        WHEN DATEDIFF(CURDATE(), dos) <= 90  THEN '61-90'
                        WHEN DATEDIFF(CURDATE(), dos) <= 120 THEN '91-120'
                        ELSE '121+' END AS bucket,
                   COUNT(DISTINCT visit_id) n
            FROM collection_raw
            WHERE $whereSql AND (insurance_balance > 0 OR patient_balance > 0) AND dos IS NOT NULL AND deductible = 1
            GROUP BY bucket
        ", $params) as $row) { $out[(string)$row['bucket']] = (int)$row['n']; }
    } catch (Throwable $e) {}
    return $out;
}

/* Accurate grand totals for the dimension scorecards (Carriers / CPT / Providers).
   A single aggregate (no GROUP BY) so distinct-count columns (patients, facilities,
   CPTs) total correctly instead of being over-counted by summing the rows, and ratio
   columns (collection %, variance, unpaid %) are recomputed from the totals. */
/* Per-VISIT rollup of collection_raw for the Carrier scorecard — collapses line-level rows to ONE row per visit so
 * the six COUNT(DISTINCT visit_id) become SUM(flag), avoiding the >4GB temp table that overran the 4GB /var/mysqltmp
 * ceiling (Errcode 28 → "Something went wrong"). carrier1_name & chart_number are claim-level (constant per visit) →
 * MAX is exact; per-visit flags (MAX of the per-line condition) preserve the "any line qualifies" semantics; days
 * carries line-level SUM/COUNT so the outer AVG matches to the cent. GROUP BY visit_id uses the visit index → no
 * giant filesort. hasv excludes NULL-visit rows from claim/flag counts (matching COUNT(DISTINCT) which ignores NULL)
 * while their dollars still SUM in (matching the original SUMs over all rows). */
function coll_carrier_visit_sub(string $whereSql): string {
    return "SELECT COALESCE(MAX(carrier1_name),'(none)') carrier, (COALESCE(visit_id,'') <> '') hasv, MAX(chart_number) chart_number,
                   SUM(charge_amount) charge, SUM(primary_ins_paid) pri, SUM(secondary_ins_paid) sec,
                   SUM(tertiary_ins_paid) ter, SUM(patient_payment_amount) pat_paid,
                   SUM(insurance_balance) bal, SUM(patient_balance) pat_bal, SUM(expected_payment) expected,
                   MAX(primary_ins_paid > 0) f_paid,
                   MAX(primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0)) f_unpaid,
                   MAX(primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0)) f_secpend,
                   SUM(days_to_first_payment) days_sum, SUM(days_to_first_payment IS NOT NULL) days_cnt
            FROM collection_raw WHERE $whereSql
            GROUP BY visit_id, carrier1_name, chart_number";   // carrier1_name & chart_number are constant per real
            // visit, so this collapses each visit to one row exactly like GROUP BY visit_id — but for NULL/empty
            // visit_id rows (dirty data) it keeps each (carrier, patient) distinct instead of pooling them into one
            // MAX bucket, so per-carrier dollars and the patient distinct-count stay exact. hasv still = (visit_id IS
            // NOT NULL), so NULL-visit groups are excluded from claim/flag counts exactly like COUNT(DISTINCT visit_id).
}
function coll_grand_totals(PDO $pdo, string $which, string $whereSql, array $params): array {
    if ($which === 'carriers') {
        $sql = "SELECT SUM(hasv) claims, COUNT(DISTINCT chart_number) patients,
                       SUM(charge) charge, SUM(pri) pri, SUM(sec) sec, SUM(ter) ter, SUM(pat_paid) pat_paid,
                       SUM(pri+sec+ter) ins_paid, SUM(pri+sec+ter+pat_paid) total_paid, SUM(expected) expected,
                       SUM(bal) bal, SUM(pat_bal) pat_bal,
                       SUM(CASE WHEN hasv THEN f_paid ELSE 0 END) paid_n,
                       SUM(CASE WHEN hasv THEN f_unpaid ELSE 0 END) unpaid_n,
                       SUM(CASE WHEN hasv THEN f_unpaid ELSE 0 END) prim_pend_n,
                       SUM(CASE WHEN hasv THEN f_secpend ELSE 0 END) sec_pend_n,
                       SUM(days_sum) / NULLIF(SUM(days_cnt),0) avg_days
                FROM (" . coll_carrier_visit_sub($whereSql) . ") v";
    } elseif ($which === 'cpt') {
        $sql = "SELECT COUNT(DISTINCT visit_id) claims,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) paid_n,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 THEN visit_id END) unpaid_claims_n,
                       COUNT(DISTINCT chart_number) patients,
                       SUM(charge_amount) charge, SUM(expected_payment) expected,
                       SUM(primary_ins_paid) pri,
                       SUM(secondary_ins_paid) sec,
                       SUM(tertiary_ins_paid) ter,
                       SUM(patient_payment_amount) pat_paid,
                       SUM(primary_ins_paid+secondary_ins_paid+tertiary_ins_paid) paid,
                       SUM(primary_ins_paid+secondary_ins_paid+tertiary_ins_paid+patient_payment_amount) total_paid,
                       SUM(insurance_balance) bal, SUM(patient_balance) pat_bal,
                       SUM(CASE WHEN primary_ins_paid=0 THEN 1 ELSE 0 END) unpaid_n,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) prim_pend_n,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) sec_pend_n,
                       COUNT(DISTINCT CASE WHEN (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) outst_n,
                       SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) prim_outst,
                       SUM(CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) sec_outst
                FROM collection_raw WHERE $whereSql";
    } elseif ($which === 'providers') {
        $sql = "SELECT COUNT(DISTINCT visit_id) claims, COUNT(DISTINCT chart_number) patients,
                       COUNT(DISTINCT facility) facilities, COUNT(DISTINCT charge_code) cpts,
                       SUM(charge_amount) charge, SUM(expected_payment) expected,
                       SUM(primary_ins_paid+secondary_ins_paid+tertiary_ins_paid) paid,
                       SUM(primary_ins_paid) pri,
                       SUM(secondary_ins_paid) sec,
                       SUM(tertiary_ins_paid) ter,
                       SUM(patient_payment_amount) pat_paid,
                       SUM(primary_ins_paid+secondary_ins_paid+tertiary_ins_paid+patient_payment_amount) total_paid,
                       SUM(insurance_balance) bal, SUM(patient_balance) pat_bal,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) paid_n,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 THEN visit_id END) unpaid_n,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid=0 AND (insurance_balance>0 OR patient_balance>0) THEN visit_id END) prim_pend_n,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid>0 AND secondary_ins_paid=0 AND (insurance_balance>0 OR patient_balance>0) THEN visit_id END) sec_pend_n,
                       AVG(days_to_first_payment) avg_days
                FROM collection_raw WHERE $whereSql";
    } else {
        return [];
    }
    $st = $pdo->prepare($sql);
    $st->execute($params);
    return $st->fetch(PDO::FETCH_ASSOC) ?: [];
}

/* Derive headline A/R totals from a cached snapshot's by_month — used by the Compare tab
   to put two point-in-time uploads side by side. */
function snap_totals(?array $snap): array {
    $t = ['claims'=>0,'charge'=>0,'pri'=>0,'sec'=>0,'expected'=>0,'allowed'=>0,'bal'=>0,'pat_bal'=>0,
          'paid_n'=>0,'unpaid_n'=>0,'prim_pend'=>0,'sec_pend'=>0,'outst_n'=>0,'prim_outst'=>0,'sec_outst'=>0];
    foreach ((($snap['by_month'] ?? []) ?: []) as $m) {
        $t['claims']    += (int)($m['claims']??0);    $t['charge']   += (float)($m['charge']??0);
        $t['pri']       += (float)($m['pri']??0);     $t['sec']      += (float)($m['sec']??0);
        $t['expected']  += (float)($m['expected']??0);$t['allowed']  += (float)($m['allowed']??0);
        $t['bal']       += (float)($m['bal']??0);     $t['pat_bal']  += (float)($m['pat_bal']??0);
        $t['paid_n']    += (int)($m['paid_n']??0);
        $t['unpaid_n']  += (int)($m['unpaid_n']??0);  $t['outst_n']  += (int)($m['outst_n']??0);
        $t['prim_pend'] += (int)($m['prim_pend_n']??0);$t['sec_pend']+= (int)($m['sec_pend_n']??0);
        $t['prim_outst']+= (float)($m['prim_outst']??0);$t['sec_outst']+=(float)($m['sec_outst']??0);
    }
    $t['ins_paid'] = $t['pri'] + $t['sec'];
    // Collection % = paid claim count ÷ total claim count
    $t['collpct']  = $t['claims'] > 0 ? $t['paid_n']/$t['claims']*100 : 0;
    $rb = $snap['recently_billed'] ?? null;
    $t['rb_claims']      = (int)($rb['claims'] ?? 0);
    $t['rb_outstanding'] = (float)($rb['outstanding'] ?? 0);
    return $t;
}

/* Month-bucket helpers (Excel-compatible) */
$monthNames = [1=>'Jan',2=>'Feb',3=>'Mar',4=>'Apr',5=>'May',6=>'Jun',7=>'Jul',8=>'Aug',9=>'Sep',10=>'Oct',11=>'Nov',12=>'Dec'];

/* ---------------- Light, always-needed lookups (scoped to current report) ---------------- */
$lastUpload    = qRow($pdo, "SELECT * FROM collection_uploads WHERE report_id = ? AND kind='collection'       AND status='completed' ORDER BY completed_at DESC LIMIT 1", [$reportId]);
$mapLastUpload = qRow($pdo, "SELECT * FROM collection_uploads WHERE report_id = ? AND kind='facility_mapping' AND status='completed' ORDER BY completed_at DESC LIMIT 1", [$reportId]);

/* Row counts come straight from the upload record (data is fully replaced per
   upload, so valid_rows == live row count) — no COUNT(*) scan on page load. */
$rowCount = $lastUpload    ? (int)$lastUpload['valid_rows']
                           : (int) qRow($pdo, "SELECT COUNT(*) c FROM collection_raw WHERE {$scope['sql']}", $scope['params'])['c'];
$mapCount = $mapLastUpload ? (int)$mapLastUpload['valid_rows']
                           : (int) qRow($pdo, 'SELECT COUNT(*) c FROM facility_mappings WHERE report_id = ?', [$reportId])['c'];

$hasFilters = ($fFacilities || $fProviders || $fYears || $fMonths || $fStatuses || $fSpecialty !== '' || $fDeductible !== '');

/* Load the pre-computed snapshot whenever it exists.  Its facets + KPI snapshots
   do NOT depend on the active filters, so we reuse them even on FILTERED views —
   only the per-tab aggregations fall back to live SQL when a filter is applied.
   This keeps a filtered page load from re-scanning the whole report for the
   filter dropdowns (the part that was making filtering feel slow). */
$cache = null;
if ($lastUpload) {
    $cs = $pdo->prepare("SELECT snapshot FROM collection_stats WHERE upload_id = ?");
    $cs->execute([(int)$lastUpload['id']]);
    $cs = $cs->fetchColumn();
    if ($cs) $cache = json_decode($cs, true);
}
$useCache = (!$hasFilters && $cache);   // per-tab aggregations use cache only when unfiltered
/* by-month tables (Summary / Dashboard) can reuse the cache for YEAR/MONTH filters
   too — the cached per-month rows are aggregated across all facilities/providers,
   so we just subset them in PHP.  Only a facility/provider filter forces live SQL. */
/* A specialty filter restricts charge_code, but the cached per-month rows are
   aggregated across ALL charge codes → can't be reused when a specialty is picked. */
$useMonthCache = ($cache && !$fFacilities && !$fProviders && !$fStatuses && $fSpecialty === '' && $fDeductible === '');

/* Filter dropdown sources — always from the snapshot if present (the dropdowns
   must list ALL options regardless of the current filter); else compute once. */
/* Use the snapshot facets only when they actually carry options. A broken/empty snapshot (facets present but all
   arrays empty — e.g. one written during the temp-disk incident) must NOT leave the filter dropdowns blank while the
   report clearly has facilities/providers; fall through to a live scan of collection_raw instead. */
if (!empty($cache['facets']['facilities']) || !empty($cache['facets']['providers']) || !empty($cache['facets']['years'])) {
    $facilities = $cache['facets']['facilities'] ?? [];
    $providers  = $cache['facets']['providers'] ?? [];
    $years      = $cache['facets']['years'] ?? [];
} else {
    $st = $pdo->prepare("SELECT DISTINCT facility FROM collection_raw WHERE {$scope['sql']} AND facility IS NOT NULL AND facility <> '' ORDER BY facility"); $st->execute($scope['params']); $facilities = $st->fetchAll(PDO::FETCH_COLUMN);
    $st = $pdo->prepare("SELECT DISTINCT provider_name FROM collection_raw WHERE {$scope['sql']} AND provider_name IS NOT NULL AND provider_name <> '' ORDER BY provider_name"); $st->execute($scope['params']); $providers = $st->fetchAll(PDO::FETCH_COLUMN);
    $st = $pdo->prepare("SELECT DISTINCT dos_year FROM collection_raw WHERE {$scope['sql']} AND dos_year IS NOT NULL ORDER BY dos_year DESC"); $st->execute($scope['params']); $years = $st->fetchAll(PDO::FETCH_COLUMN);
}

/* ---------------- Per-tab data (cache first, live fallback) ---------------- */
$byMonth = []; $totals = []; $byFacility = []; $byFacility10 = [];
$pivot = []; $insights = []; $unbilled = $unpaid = $hold = []; $collSplit = []; $dedDash = ['n'=>0,'charge'=>0.0];
$sumA = ['c'=>0,'exp'=>0,'out'=>0]; $sumB = ['c'=>0,'charge'=>0,'exp'=>0,'out'=>0]; $sumC = ['c'=>0,'exp'=>0,'out'=>0];
$detailRows = []; $detailTotal = 0; $upHist = [];
$cmpSnaps = []; $cmpCur = $cmpBase = null; $cmpCurId = $cmpBaseId = 0; $cmpCurMeta = $cmpBaseMeta = null;

/* Recently billed (by Entry Date — within 30 days of the newest entry date). Report-level
   KPI: from the snapshot when available, else computed live. (Empty until a Facility Mapping
   with Entry Date is uploaded.) */
$recentlyBilled = $cache['recently_billed'] ?? null;
if ($recentlyBilled === null && $rowCount > 0) {
    $st = $pdo->prepare("SELECT MAX(entry_date) FROM collection_raw WHERE {$scope['sql']}");
    $st->execute($scope['params']); $latestEntry = $st->fetchColumn();
    $recentlyBilled = ['claims'=>0, 'outstanding'=>0, 'charge'=>0, 'latest_entry'=>$latestEntry ?: null, 'since'=>null];
    if ($latestEntry) {
        /* 'outstanding' here is total balance (ins + pat) — back-compat with the recently-billed
           cache shape from collection-engine. */
        $st = $pdo->prepare("SELECT COUNT(DISTINCT visit_id) claims,
                                    SUM(insurance_balance + patient_balance) outstanding,
                                    SUM(charge_amount) charge
                             FROM collection_raw WHERE {$scope['sql']} AND entry_date IS NOT NULL AND entry_date >= DATE_SUB(?, INTERVAL 30 DAY)");
        $st->execute(array_merge($scope['params'], [$latestEntry])); $r = $st->fetch();
        $recentlyBilled['claims'] = (int)$r['claims']; $recentlyBilled['outstanding'] = (float)$r['outstanding'];
        $recentlyBilled['charge'] = (float)$r['charge'];
        $recentlyBilled['since'] = date('Y-m-d', strtotime($latestEntry . ' -30 days'));
    }
}

if ($rowCount > 0 && in_array($tab, ['dashboard','summary'], true)) {
    // Reuse the cached by-month rollup only if it carries the decoupled tertiary
    // column. Older snapshots stored `sec` = SUM(sec+ter); reading those without
    // `ter` would understate Tertiary Paid and double-count it in Secondary Paid.
    $byMonthCacheOk = $useMonthCache && !empty($cache['by_month'])
                   && isset($cache['by_month'][0])
                   && array_key_exists('ter', $cache['by_month'][0]);
    if ($byMonthCacheOk) {
        $monthRows = filterMonthRows($cache['by_month'], $fYears, $fMonths);
    } else {
        // Live aggregation (facility/provider filter applied)
        $monthRows = qAll($pdo, "
            SELECT dos_year, dos_month,
                   COUNT(DISTINCT visit_id) AS claims,
                   SUM(charge_amount)                                  AS charge,
                   SUM(primary_ins_paid)                               AS pri,
                   SUM(secondary_ins_paid)                             AS sec,
                   SUM(tertiary_ins_paid)                              AS ter,
                   SUM(insurance_balance)                              AS bal,
                   SUM(patient_balance)                                AS pat_bal,
                   SUM(patient_payment_amount)                         AS pat_paid,
                   SUM(expected_payment)                               AS expected,
                   SUM(amount_allowed)                                 AS allowed,
                   SUM(wo_amount)                                      AS wo,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                   (COUNT(DISTINCT visit_id) - COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END)) AS unpaid_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS prim_pend_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS sec_pend_n,
                   COUNT(DISTINCT CASE WHEN (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS outst_n,
                   SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS prim_outst,
                   SUM(CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS sec_outst
            FROM collection_raw
            WHERE $whereSql AND dos_year IS NOT NULL AND dos_month IS NOT NULL
            GROUP BY dos_year, dos_month
            ORDER BY dos_year, dos_month
        ", $params);
    }

    foreach ($monthRows as $r) {
        $label = $monthNames[(int)$r['dos_month']] . " " . substr((string)$r['dos_year'], -2);
        $byMonth[$label] = [
            'year' => (int)$r['dos_year'], 'month' => (int)$r['dos_month'],
            'claims' => (int)$r['claims'],
            'charge' => (float)$r['charge'], 'pri' => (float)$r['pri'], 'sec' => (float)($r['sec'] ?? 0),
            'ter' => (float)($r['ter'] ?? 0),
            'bal' => (float)$r['bal'], 'pat_bal' => (float)$r['pat_bal'], 'pat_paid' => (float)$r['pat_paid'],
            'expected' => (float)$r['expected'], 'allowed' => (float)$r['allowed'], 'wo' => (float)$r['wo'],
            'paid' => (int)$r['paid_n'], 'unpaid' => (int)$r['unpaid_n'],
            'prim_pend' => (int)($r['prim_pend_n'] ?? 0), 'sec_pend' => (int)($r['sec_pend_n'] ?? 0), 'outst_n' => (int)($r['outst_n'] ?? 0),
            'prim_outst' => (float)($r['prim_outst'] ?? 0), 'sec_outst' => (float)($r['sec_outst'] ?? 0),
        ];
    }

    $totals = ['claims'=>0,'charge'=>0,'pri'=>0,'sec'=>0,'ter'=>0,'bal'=>0,'pat_bal'=>0,'pat_paid'=>0,'expected'=>0,'allowed'=>0,'wo'=>0,'paid'=>0,'unpaid'=>0,'prim_pend'=>0,'sec_pend'=>0,'outst_n'=>0,'prim_outst'=>0,'sec_outst'=>0];
    foreach ($byMonth as $v) foreach ($totals as $k => $_) $totals[$k] += ($v[$k] ?? 0);

    /* Insights: best/worst month, trend, anomalies */
    if ($byMonth) {
        $maxM = $minM = null; $maxV = -INF; $minV = INF;
        foreach ($byMonth as $m => $v) {
            if ($v['charge'] > $maxV) { $maxV = $v['charge']; $maxM = $m; }
            if ($v['charge'] < $minV) { $minV = $v['charge']; $minM = $m; }
        }
        $vals = array_map(fn($x) => $x['charge'], array_values($byMonth));
        $mean = $vals ? array_sum($vals)/count($vals) : 0;
        $sd = 0;
        if ($vals) { $variance = array_sum(array_map(fn($v)=>pow($v-$mean,2), $vals))/count($vals); $sd = sqrt($variance); }
        $monthsArr = array_values($byMonth);
        $trend = 'flat';
        if (count($monthsArr) >= 2) {
            $last = end($monthsArr)['charge']; $prev = prev($monthsArr)['charge'];
            if ($prev > 0) {
                $delta = (($last - $prev) / $prev) * 100;
                $trend = $delta > 5 ? 'up' : ($delta < -5 ? 'down' : 'flat');
            }
        }
        $insights = [
            'best_month' => ['m'=>$maxM,'v'=>$maxV],
            'worst_month'=> ['m'=>$minM,'v'=>$minV],
            'mean' => $mean, 'sd' => $sd, 'trend' => $trend,
            'collection_pct'  => pct($totals['paid'], $totals['claims']),
            'outstanding_pct' => pct($totals['bal'] + $totals['pat_bal'], $totals['charge']),
        ];
        $anomalies = [];
        foreach ($byMonth as $m => $v) {
            if ($sd > 0 && abs($v['charge'] - $mean) > 2 * $sd) {
                $anomalies[] = ['m'=>$m, 'val'=>$v['charge'], 'dir'=>$v['charge']>$mean?'+':'-'];
            }
        }
        $insights['anomalies'] = $anomalies;
    }
}

if ($rowCount > 0 && $tab === 'dashboard') {
    if ($useCache && !empty($cache['top_facilities'])) {
        $facRows = $cache['top_facilities'];
    } else {
        $facRows = qAll($pdo, "
            SELECT COALESCE(facility,'(unmapped)') AS facility,
                   COUNT(DISTINCT visit_id) AS claims,
                   SUM(charge_amount) AS charge,
                   SUM(primary_ins_paid) AS pri,
                   SUM(secondary_ins_paid + tertiary_ins_paid) AS sec,
                   SUM(patient_payment_amount) AS pat_paid,
                   SUM(insurance_balance) AS bal,
                   SUM(patient_balance) AS pat_bal
            FROM collection_raw
            WHERE $whereSql
            GROUP BY facility ORDER BY charge DESC LIMIT 10
        ", $params);
    }
    foreach ($facRows as $r) {
        $byFacility10[$r['facility']] = [
            'claims'=>(int)$r['claims'],'charge'=>(float)$r['charge'],
            'pri'=>(float)$r['pri'],'sec'=>(float)$r['sec'],
            'pat_paid'=>(float)($r['pat_paid'] ?? 0),
            'bal'=>(float)$r['bal'],'pat_bal'=>(float)($r['pat_bal'] ?? 0),
        ];
    }

    /* ---- Collection-rate bifurcation for the navy KPI banner: WITH vs WITHOUT recently
       billed claims. Recently-billed claims (Entry Date within 30 days of the newest entry)
       have not had time to be paid yet, so they drag the headline rate down; the "excl."
       figure is the collection rate on mature claims only. Both come from ONE per-VISIT
       rollup (single GROUP BY visit_id, covered by idx_grp_visit) so the two numbers share
       an identical basis AND it stays inside the 4 GB tmp budget — no extra COUNT(DISTINCT).
       $rbSince is a computed YYYY-MM-DD string (or null → excl == incl); quoted inline so the
       whole query keeps ONLY the positional placeholders that $whereSql already carries. ---- */
    $rbSince    = $recentlyBilled['since'] ?? null;
    $recentExpr = $rbSince ? "(entry_date IS NOT NULL AND entry_date >= " . $pdo->quote($rbSince) . ")" : "0";
    $csRow = qRow($pdo, "
        SELECT COUNT(*)                                            AS all_claims,
               COALESCE(SUM(f_paid), 0)                            AS all_paid,
               SUM(CASE WHEN f_recent = 0 THEN 1 ELSE 0 END)       AS mature_claims,
               COALESCE(SUM(CASE WHEN f_recent = 0 THEN f_paid ELSE 0 END), 0) AS mature_paid
        FROM (
            SELECT visit_id,
                   MAX(primary_ins_paid > 0) AS f_paid,
                   MAX($recentExpr)          AS f_recent
            FROM collection_raw
            WHERE $whereSql AND visit_id IS NOT NULL AND visit_id <> ''
            GROUP BY visit_id
        ) v
    ", $params);
    $collSplit = [
        'incl_pct'      => pct((int)($csRow['all_paid'] ?? 0),    (int)($csRow['all_claims'] ?? 0)),
        'excl_pct'      => pct((int)($csRow['mature_paid'] ?? 0), (int)($csRow['mature_claims'] ?? 0)),
        'all_claims'    => (int)($csRow['all_claims'] ?? 0),
        'mature_claims' => (int)($csRow['mature_claims'] ?? 0),
        'recent_claims' => (int)($csRow['all_claims'] ?? 0) - (int)($csRow['mature_claims'] ?? 0),
    ];

    // Deductible KPI (count of deductible visits + their total charge, read from collection_raw),
    // honouring the active filters. Temp-safe (deductible=1 is a small subset).
    if ($deductibleReady) $dedDash = coll_deductible_total($pdo, $whereSql, $params);
}

if ($rowCount > 0 && $tab === 'summary') {
    /* Use cache only if it was built WITH the balance columns (un_out);
       otherwise compute live so a not-yet-rebuilt cache never shows blank balances. */
    if ($useMonthCache && !empty($cache['summary_subs']) && isset($cache['summary_subs'][0]['un_out'])) {
        $subRows = filterMonthRows($cache['summary_subs'], $fYears, $fMonths);
    } else {
        $subRows = qAll($pdo, "
            SELECT dos_year, dos_month,
                   COUNT(DISTINCT CASE WHEN wo_amount > 0 AND primary_ins_paid = 0 AND secondary_ins_paid = 0 THEN visit_id END) AS u_c,
                   SUM(CASE WHEN wo_amount > 0 AND primary_ins_paid = 0 AND secondary_ins_paid = 0 THEN expected_payment ELSE 0 END) AS u_exp,
                   SUM(CASE WHEN wo_amount > 0 AND primary_ins_paid = 0 AND secondary_ins_paid = 0 THEN insurance_balance + patient_balance ELSE 0 END) AS u_out,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND charge_amount > 0 AND insurance_balance < charge_amount*0.4 THEN visit_id END) AS h_c,
                   SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND charge_amount > 0 AND insurance_balance < charge_amount*0.4 THEN expected_payment ELSE 0 END) AS h_exp,
                   SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND charge_amount > 0 AND insurance_balance < charge_amount*0.4 THEN insurance_balance + patient_balance ELSE 0 END) AS h_out,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND NOT (charge_amount > 0 AND insurance_balance < charge_amount*0.4) THEN visit_id END) AS un_c,
                   SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND NOT (charge_amount > 0 AND insurance_balance < charge_amount*0.4) THEN charge_amount ELSE 0 END) AS un_charge,
                   SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND NOT (charge_amount > 0 AND insurance_balance < charge_amount*0.4) THEN expected_payment ELSE 0 END) AS un_exp,
                   SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND NOT (charge_amount > 0 AND insurance_balance < charge_amount*0.4) THEN insurance_balance + patient_balance ELSE 0 END) AS un_out
            FROM collection_raw
            WHERE $whereSql AND dos_year IS NOT NULL AND dos_month IS NOT NULL
            GROUP BY dos_year, dos_month
            ORDER BY dos_year, dos_month
        ", $params);
    }
    foreach ($subRows as $r) {
        $label = $monthNames[(int)$r['dos_month']] . " " . substr((string)$r['dos_year'], -2);
        $unbilled[$label] = ['c'=>(int)$r['u_c'], 'exp'=>(float)$r['u_exp'], 'out'=>(float)($r['u_out'] ?? 0)];
        $hold[$label]     = ['c'=>(int)$r['h_c'], 'exp'=>(float)$r['h_exp'], 'out'=>(float)($r['h_out'] ?? 0)];
        $unpaid[$label]   = ['c'=>(int)$r['un_c'], 'charge'=>(float)$r['un_charge'], 'exp'=>(float)$r['un_exp'], 'out'=>(float)($r['un_out'] ?? 0)];
        $sumA['c']+=(int)$r['u_c']; $sumA['exp']+=(float)$r['u_exp']; $sumA['out']+=(float)($r['u_out'] ?? 0);
        $sumB['c']+=(int)$r['un_c']; $sumB['charge']+=(float)$r['un_charge']; $sumB['exp']+=(float)$r['un_exp']; $sumB['out']+=(float)($r['un_out'] ?? 0);
        $sumC['c']+=(int)$r['h_c']; $sumC['exp']+=(float)$r['h_exp']; $sumC['out']+=(float)($r['h_out'] ?? 0);
    }
}

if ($rowCount > 0 && $tab === 'pivot') {
    // Pivot mirrors the Summary scorecard: every per-day bucket carries the
    // full metric set so facility / month rollups can sum into the same shape.
    // Old cache snapshots (only `v,c,ex,p,s,b,pb`) are ignored — fall back to live.
    // Newer cached rows include `t` (tertiary) and `pp` (patient paid); when missing
    // we fall back to live so the new payment columns aren't blank.
    $cacheOk = $useCache && !empty($cache['pivot_fmd']) && isset($cache['pivot_fmd'][0]['cl']) && array_key_exists('t', $cache['pivot_fmd'][0]);
    if ($cacheOk) {
        $pivRows = $cache['pivot_fmd'];
    } else {
        $pivRows = qAll($pdo, "
            SELECT COALESCE(facility,'(unmapped)') AS facility,
                   dos_year, dos_month, DAY(dos) AS dos_day,
                   COUNT(DISTINCT visit_id) AS cl,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 THEN visit_id END) AS unpaid_n,
                   SUM(charge_amount) AS c,
                   SUM(expected_payment) AS ex,
                   SUM(primary_ins_paid) AS p,
                   SUM(secondary_ins_paid) AS s,
                   SUM(tertiary_ins_paid) AS t,
                   SUM(patient_payment_amount) AS pp,
                   SUM(insurance_balance) AS b,
                   SUM(patient_balance) AS pb,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS prim_pend_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS sec_pend_n,
                   COUNT(DISTINCT CASE WHEN (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS outst_n,
                   SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS prim_outst,
                   SUM(CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS sec_outst
            FROM collection_raw
            WHERE $whereSql AND dos IS NOT NULL
            GROUP BY facility, dos_year, dos_month, dos_day
            ORDER BY facility, dos_year, dos_month, dos_day
        ", $params);
    }
    foreach ($pivRows as $r) {
        $f = $r['facility'];
        $m = $monthNames[(int)$r['dos_month']] . " " . substr((string)$r['dos_year'], -2);
        $d = (int)$r['dos_day'] . '-' . $monthNames[(int)$r['dos_month']];
        $pivot[$f][$m][$d] = [
            'cl'        => (int)   ($r['cl'] ?? 0),
            'paid_n'    => (int)   ($r['paid_n'] ?? 0),
            'unpaid_n'  => (int)   ($r['unpaid_n'] ?? 0),
            'c'         => (float) ($r['c'] ?? 0),
            'ex'        => (float) ($r['ex'] ?? 0),
            'p'         => (float) ($r['p'] ?? 0),
            's'         => (float) ($r['s'] ?? 0),
            't'         => (float) ($r['t'] ?? 0),
            'pp'        => (float) ($r['pp'] ?? 0),
            'b'         => (float) ($r['b'] ?? 0),
            'pb'        => (float) ($r['pb'] ?? 0),
            'prim_pend' => (int)   ($r['prim_pend_n'] ?? 0),
            'sec_pend'  => (int)   ($r['sec_pend_n'] ?? 0),
            'outst_n'   => (int)   ($r['outst_n'] ?? 0),
            'prim_outst'=> (float) ($r['prim_outst'] ?? 0),
            'sec_outst' => (float) ($r['sec_outst'] ?? 0),
        ];
    }
    // Merge deductible-visit counts per (facility, month, day) cell — SAME grain as the pivot, filtered to
    // deductible=1 (a small subset → temp-safe). $pivAdd rolls it up to month/facility/grand like the claim
    // count. Always live (even when $pivRows came from cache) so the column is populated on every view.
    if ($deductibleReady) {
        $dedPiv = qAll($pdo, "
            SELECT COALESCE(facility,'(unmapped)') AS facility, dos_year, dos_month, DAY(dos) AS dos_day,
                   COUNT(DISTINCT visit_id) AS ded
            FROM collection_raw
            WHERE $whereSql AND dos IS NOT NULL AND deductible = 1
            GROUP BY facility, dos_year, dos_month, dos_day
        ", $params);
        foreach ($dedPiv as $r) {
            $f = $r['facility'];
            $m = $monthNames[(int)$r['dos_month']] . " " . substr((string)$r['dos_year'], -2);
            $d = (int)$r['dos_day'] . '-' . $monthNames[(int)$r['dos_month']];
            if (isset($pivot[$f][$m][$d])) $pivot[$f][$m][$d]['ded'] = (int)$r['ded'];
        }
    }
}

/* ============= UNIQUE PATIENTS TAB =============
   Three-level drill-down: Facility → Provider → Month.
   COUNT(DISTINCT chart_number) is computed independently at each level. */
$uniqFac = $uniqFacProv = $uniqFacProvMon = [];
$uniqGrandTotal = ['uniq_pt'=>0, 'visits'=>0, 'charge'=>0, 'paid'=>0, 'bal'=>0, 'pat_bal'=>0];
if ($rowCount > 0 && $tab === 'unique-patients') {
    // Only reuse the cache when it already carries the new metric columns we render
    // below. Older snapshots (pre-expansion) lack `expected`, `days_span` and `top_carrier`
    // so we fall back to live SQL until the next upload rebuilds the snapshot.
    $facCacheOk  = !empty($cache['uniq_pt_facility'])
                && isset($cache['uniq_pt_facility'][0])
                && array_key_exists('expected', $cache['uniq_pt_facility'][0])
                && array_key_exists('days_span', $cache['uniq_pt_facility'][0])
                && array_key_exists('pri', $cache['uniq_pt_facility'][0]);
    $provCacheOk = !empty($cache['uniq_pt_facility_provider'])
                && isset($cache['uniq_pt_facility_provider'][0])
                && array_key_exists('expected', $cache['uniq_pt_facility_provider'][0])
                && array_key_exists('top_carrier', $cache['uniq_pt_facility_provider'][0])
                && array_key_exists('pri', $cache['uniq_pt_facility_provider'][0]);
    if ($useCache && $facCacheOk && $provCacheOk) {
        $uniqFac        = $cache['uniq_pt_facility'];
        $uniqFacProv    = $cache['uniq_pt_facility_provider'];
        $uniqFacProvMon = $cache['uniq_pt_facility_provider_month'];
    } else {
        // Live: 3 separate queries (filter-aware).
        $uniqFac = qAll($pdo, "
            SELECT COALESCE(facility,'(unmapped)') AS facility,
                   COUNT(DISTINCT chart_number) AS uniq_pt,
                   COUNT(DISTINCT provider_name) AS providers,
                   COUNT(DISTINCT visit_id) AS visits,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                   SUM(charge_amount) AS charge,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS paid,
                   SUM(primary_ins_paid) AS pri,
                   SUM(secondary_ins_paid) AS sec,
                   SUM(tertiary_ins_paid) AS ter,
                   SUM(patient_payment_amount) AS pat_paid,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                   SUM(insurance_balance) AS bal,
                   SUM(patient_balance) AS pat_bal,
                   SUM(expected_payment) AS expected
            FROM collection_raw WHERE $whereSql
            GROUP BY facility ORDER BY uniq_pt DESC
        ", $params);
        // Per-facility avg "days as patient" = DATEDIFF(MAX(dos),MIN(dos)) per chart_number,
        // then AVG of that per facility. Done as a separate query so it stays index-friendly
        // and doesn't bloat the main aggregate above.
        $facDays = qAll($pdo, "
            SELECT facility, AVG(span_d) AS days_span
            FROM (
                SELECT COALESCE(facility,'(unmapped)') AS facility, chart_number,
                       DATEDIFF(MAX(dos), MIN(dos)) AS span_d
                FROM collection_raw
                WHERE $whereSql AND dos IS NOT NULL AND chart_number IS NOT NULL
                GROUP BY facility, chart_number
            ) per_pt
            GROUP BY facility
        ", $params);
        $facDaysMap = [];
        foreach ($facDays as $fd) { $facDaysMap[$fd['facility']] = (float) $fd['days_span']; }
        foreach ($uniqFac as &$_ufRow) { $_ufRow['days_span'] = $facDaysMap[$_ufRow['facility']] ?? null; }
        unset($_ufRow);

        $uniqFacProv = qAll($pdo, "
            SELECT COALESCE(facility,'(unmapped)') AS facility,
                   COALESCE(provider_name,'(no provider)') AS provider,
                   COUNT(DISTINCT chart_number) AS uniq_pt,
                   COUNT(DISTINCT visit_id) AS visits,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                   SUM(charge_amount) AS charge,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS paid,
                   SUM(primary_ins_paid) AS pri,
                   SUM(secondary_ins_paid) AS sec,
                   SUM(tertiary_ins_paid) AS ter,
                   SUM(patient_payment_amount) AS pat_paid,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                   SUM(insurance_balance) AS bal,
                   SUM(patient_balance) AS pat_bal,
                   SUM(expected_payment) AS expected
            FROM collection_raw
            WHERE $whereSql
            GROUP BY facility, provider_name ORDER BY facility, uniq_pt DESC
        ", $params);
        // Top carrier per (facility, provider) and per facility. Built from one grouped
        // scan instead of a correlated subquery so it stays cheap even with hundreds of
        // (facility × provider × carrier) buckets. We pick the carrier with the highest
        // total charge in each combo.
        $topCarrierRows = qAll($pdo, "
            SELECT COALESCE(facility,'(unmapped)') AS facility,
                   COALESCE(provider_name,'(no provider)') AS provider,
                   COALESCE(carrier1_name,'(none)') AS carrier,
                   SUM(charge_amount) AS c
            FROM collection_raw
            WHERE $whereSql
            GROUP BY facility, provider_name, carrier1_name
        ", $params);
        $topCarrierMap = [];     // facility\0provider -> {carrier,c}
        $facCarrierMap = [];     // facility -> [carrier => sum_charge]
        foreach ($topCarrierRows as $tr) {
            $key = $tr['facility'] . "\0" . $tr['provider'];
            $charge = (float) $tr['c'];
            if (!isset($topCarrierMap[$key]) || $charge > $topCarrierMap[$key]['c']) {
                $topCarrierMap[$key] = ['carrier' => $tr['carrier'], 'c' => $charge];
            }
            $fk = $tr['facility'];
            $facCarrierMap[$fk][$tr['carrier']] = ($facCarrierMap[$fk][$tr['carrier']] ?? 0) + $charge;
        }
        foreach ($uniqFacProv as &$_upRow) {
            $key = $_upRow['facility'] . "\0" . $_upRow['provider'];
            $_upRow['top_carrier'] = $topCarrierMap[$key]['carrier'] ?? '(none)';
        }
        unset($_upRow);
        // Pre-compute the per-facility top carrier so the HTML doesn't have to derive
        // it from the provider rows (which would only be approximate).
        $facTopCarrierMap = [];
        foreach ($facCarrierMap as $fac => $carriers) {
            arsort($carriers);
            $facTopCarrierMap[$fac] = array_key_first($carriers);
        }
        $uniqFacProvMon = qAll($pdo, "
            SELECT COALESCE(facility,'(unmapped)') AS facility,
                   COALESCE(provider_name,'(no provider)') AS provider,
                   dos_year, dos_month,
                   COUNT(DISTINCT chart_number) AS uniq_pt,
                   COUNT(DISTINCT visit_id) AS visits,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                   SUM(charge_amount) AS charge,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS paid,
                   SUM(primary_ins_paid) AS pri,
                   SUM(secondary_ins_paid) AS sec,
                   SUM(tertiary_ins_paid) AS ter,
                   SUM(patient_payment_amount) AS pat_paid,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                   SUM(insurance_balance) AS bal,
                   SUM(patient_balance) AS pat_bal,
                   SUM(expected_payment) AS expected
            FROM collection_raw
            WHERE $whereSql AND dos_year IS NOT NULL AND dos_month IS NOT NULL
            GROUP BY facility, provider_name, dos_year, dos_month
            ORDER BY facility, provider_name, dos_year, dos_month
        ", $params);
    }
    // Grand total — cached when no filters AND the cached row carries the new fields.
    $gtCacheOk = $useCache && !empty($cache['unique_grand_total'])
              && array_key_exists('expected', $cache['unique_grand_total'])
              && array_key_exists('days_span', $cache['unique_grand_total'])
              && array_key_exists('pri', $cache['unique_grand_total']);
    if ($gtCacheOk) {
        $gt = $cache['unique_grand_total'];
        $uniqGrandTotal = ['uniq_pt'=>(int)$gt['uniq_pt'], 'visits'=>(int)$gt['visits'],
                           'paid_n'=>isset($gt['paid_n']) ? (int)$gt['paid_n'] : null,
                           'charge'=>(float)$gt['charge'], 'paid'=>(float)$gt['paid'],
                           'pri'=>(float)($gt['pri'] ?? 0),
                           'sec'=>(float)($gt['sec'] ?? 0),
                           'ter'=>(float)($gt['ter'] ?? 0),
                           'pat_paid'=>(float)($gt['pat_paid'] ?? 0),
                           'total_paid'=>(float)($gt['total_paid'] ?? $gt['paid'] ?? 0),
                           'bal'=>(float)$gt['bal'], 'pat_bal'=>(float)($gt['pat_bal'] ?? 0),
                           'expected'=>(float)($gt['expected'] ?? 0),
                           'days_span'=>($gt['days_span'] === null ? null : (float)$gt['days_span'])];
    } else {
        $gt = qRow($pdo, "SELECT COUNT(DISTINCT chart_number) up, COUNT(DISTINCT visit_id) v,
                                 COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) pn,
                                 SUM(charge_amount) c,
                                 SUM(primary_ins_paid+secondary_ins_paid+tertiary_ins_paid) p,
                                 SUM(primary_ins_paid) p1,
                                 SUM(secondary_ins_paid) p2,
                                 SUM(tertiary_ins_paid) p3,
                                 SUM(patient_payment_amount) pp,
                                 SUM(primary_ins_paid+secondary_ins_paid+tertiary_ins_paid+patient_payment_amount) tp,
                                 SUM(insurance_balance) b,
                                 SUM(patient_balance) pb,
                                 SUM(expected_payment) ex
                            FROM collection_raw WHERE $whereSql", $params);
        // Overall avg "days as patient" = AVG(DATEDIFF(MAX,MIN)) per chart_number.
        $gtDays = qRow($pdo, "
            SELECT AVG(span_d) AS days_span FROM (
                SELECT chart_number, DATEDIFF(MAX(dos), MIN(dos)) AS span_d
                FROM collection_raw
                WHERE $whereSql AND dos IS NOT NULL AND chart_number IS NOT NULL
                GROUP BY chart_number
            ) p
        ", $params);
        $uniqGrandTotal = ['uniq_pt'=>(int)$gt['up'], 'visits'=>(int)$gt['v'],
                           'paid_n'=>(int)($gt['pn'] ?? 0),
                           'charge'=>(float)$gt['c'], 'paid'=>(float)$gt['p'],
                           'pri'=>(float)($gt['p1'] ?? 0),
                           'sec'=>(float)($gt['p2'] ?? 0),
                           'ter'=>(float)($gt['p3'] ?? 0),
                           'pat_paid'=>(float)($gt['pp'] ?? 0),
                           'total_paid'=>(float)($gt['tp'] ?? 0),
                           'bal'=>(float)$gt['b'], 'pat_bal'=>(float)($gt['pb'] ?? 0),
                           'expected'=>(float)($gt['ex'] ?? 0),
                           'days_span'=>($gtDays && $gtDays['days_span'] !== null) ? (float)$gtDays['days_span'] : null];
    }
}

/* ============= AGING TAB ============= */
$agingBuckets = $agingByCarrier = $agingByFacility = [];
$agingTotalBal = 0;
if ($rowCount > 0 && $tab === 'aging') {
    // Only reuse cached aging buckets when they include the new pri/sec/tertiary/patient/total_paid
    // columns; older snapshots predate them so fall back to live SQL.
    $agingCacheOk = $useCache && !empty($cache['aging_buckets'])
                 && isset($cache['aging_buckets'][0])
                 && array_key_exists('pri', $cache['aging_buckets'][0]);
    if ($agingCacheOk) {
        $agingBuckets    = $cache['aging_buckets'];
        $agingByCarrier  = $cache['aging_by_carrier'];
        $agingByFacility = $cache['aging_by_facility'];
    } else {
        $cur = date('Y-m-d');
        $bucketCase = "
            CASE WHEN DATEDIFF(?, dos) <= 30  THEN '0-30'
                 WHEN DATEDIFF(?, dos) <= 60  THEN '31-60'
                 WHEN DATEDIFF(?, dos) <= 90  THEN '61-90'
                 WHEN DATEDIFF(?, dos) <= 120 THEN '91-120'
                 ELSE '121+'
            END";
        // Note: Aggregating expected_payment (not amount_allowed) so the column label and
        // value agree.  Primary / Secondary paid added so the full payment chain is visible
        // for each aging bucket.
        $agingBuckets = qAll($pdo, "
            SELECT $bucketCase AS bucket, COUNT(DISTINCT visit_id) claims,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) paid_n,
                   SUM(insurance_balance) bal, SUM(patient_balance) pat_bal,
                   SUM(charge_amount) charge, SUM(expected_payment) expected,
                   SUM(primary_ins_paid) pri,
                   SUM(secondary_ins_paid) sec,
                   SUM(tertiary_ins_paid) ter,
                   SUM(patient_payment_amount) pat_paid,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) total_paid
            FROM collection_raw
            WHERE $whereSql AND (insurance_balance > 0 OR patient_balance > 0) AND dos IS NOT NULL
            GROUP BY bucket ORDER BY FIELD(bucket,'0-30','31-60','61-90','91-120','121+')
        ", array_merge([$cur,$cur,$cur,$cur], $params));
        $agingByCarrier = qAll($pdo, "
            SELECT COALESCE(carrier1_name,'(none)') carrier, $bucketCase AS bucket,
                   COUNT(DISTINCT visit_id) claims,
                   SUM(insurance_balance) bal, SUM(patient_balance) pat_bal
            FROM collection_raw
            WHERE $whereSql AND (insurance_balance > 0 OR patient_balance > 0) AND dos IS NOT NULL
            GROUP BY carrier, bucket
        ", array_merge([$cur,$cur,$cur,$cur], $params));
        $agingByFacility = qAll($pdo, "
            SELECT COALESCE(facility,'(unmapped)') facility, $bucketCase AS bucket,
                   COUNT(DISTINCT visit_id) claims,
                   SUM(insurance_balance) bal, SUM(patient_balance) pat_bal
            FROM collection_raw
            WHERE $whereSql AND (insurance_balance > 0 OR patient_balance > 0) AND dos IS NOT NULL
            GROUP BY facility, bucket
        ", array_merge([$cur,$cur,$cur,$cur], $params));
    }
    foreach ($agingBuckets as $b) $agingTotalBal += (float)$b['bal'] + (float)($b['pat_bal'] ?? 0);
}

/* ============= CARRIERS TAB ============= */
$carrierPerf = [];
if ($rowCount > 0 && $tab === 'carriers') {
    // Only reuse the cache when it already carries the decoupled tertiary + patient
    // payment columns; otherwise fall back to live SQL so Tertiary/Patient/Total Paid
    // render correctly on the carrier scorecard.
    $carrCacheOk = $useCache && !empty($cache['carrier_performance'])
                && isset($cache['carrier_performance'][0])
                && array_key_exists('ter', $cache['carrier_performance'][0]);
    if ($carrCacheOk) {
        $carrierPerf = $cache['carrier_performance'];
    } else {
        // Per-VISIT rollup (see coll_carrier_visit_sub) — same numbers as the old six-COUNT(DISTINCT visit_id) query
        // but without the >4GB temp table that tripped Errcode 28 on /var/mysqltmp.
        $carrierPerf = qAll($pdo, "
            SELECT carrier,
                   SUM(hasv) AS claims, COUNT(DISTINCT chart_number) AS patients,
                   SUM(charge) AS charge, SUM(pri) AS pri, SUM(sec) AS sec, SUM(ter) AS ter, SUM(pat_paid) AS pat_paid,
                   SUM(bal) AS bal, SUM(pat_bal) AS pat_bal,
                   SUM(pri+sec+ter) AS ins_paid, SUM(pri+sec+ter+pat_paid) AS total_paid, SUM(expected) AS expected,
                   SUM(CASE WHEN hasv THEN f_paid ELSE 0 END) AS paid_n,
                   SUM(CASE WHEN hasv THEN f_unpaid ELSE 0 END) AS unpaid_n,
                   SUM(CASE WHEN hasv THEN f_unpaid ELSE 0 END) AS prim_pend_n,
                   SUM(CASE WHEN hasv THEN f_secpend ELSE 0 END) AS sec_pend_n,
                   SUM(days_sum) / NULLIF(SUM(days_cnt),0) AS avg_days
            FROM (" . coll_carrier_visit_sub($whereSql) . ") v
            GROUP BY carrier ORDER BY charge DESC
        ", $params);
    }
}

/* ============= CPT ANALYSIS TAB ============= */
$cptAnalysis = [];
if ($rowCount > 0 && $tab === 'cpt') {
    // Only reuse the cached CPT rollup when it already includes the Summary-style
    // claim/paid/pending/outst metrics added in the Jun-26 expansion AND the new
    // decoupled tertiary + patient payment columns. Older snapshots
    // lack `claims`/`ter` so we fall back to live SQL until the next ingest rebuilds them.
    $cacheOk = $useCache && !empty($cache['cpt_analysis'])
            && isset($cache['cpt_analysis'][0]['claims'])
            && array_key_exists('ter', $cache['cpt_analysis'][0]);
    if ($cacheOk) {
        $cptAnalysis = $cache['cpt_analysis'];
    } else {
        $cptAnalysis = qAll($pdo, "
            SELECT charge_code, MAX(charge_code_desc) AS description,
                   COUNT(DISTINCT visit_id) AS claims,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 THEN visit_id END) AS unpaid_claims_n,
                   COUNT(DISTINCT chart_number) AS patients,
                   SUM(charge_amount) AS charge,
                   SUM(amount_allowed) AS allowed,
                   SUM(primary_ins_paid) AS pri,
                   SUM(secondary_ins_paid) AS sec,
                   SUM(tertiary_ins_paid) AS ter,
                   SUM(patient_payment_amount) AS pat_paid,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS paid,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                   SUM(expected_payment) AS expected,
                   SUM(insurance_balance) AS bal, SUM(patient_balance) AS pat_bal,
                   SUM(CASE WHEN primary_ins_paid = 0 THEN 1 ELSE 0 END) AS unpaid_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS prim_pend_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS sec_pend_n,
                   COUNT(DISTINCT CASE WHEN (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS outst_n,
                   SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS prim_outst,
                   SUM(CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS sec_outst
            FROM collection_raw
            WHERE $whereSql AND charge_code IS NOT NULL AND charge_code <> '' AND charge_code NOT LIKE '#%'
            GROUP BY charge_code ORDER BY charge DESC
        ", $params);
    }
}

/* ============= PROVIDERS TAB ============= */
$providerPerf = [];
if ($rowCount > 0 && $tab === 'providers') {
    // Skip the cache when it lacks the new pri/sec/tertiary/patient/total_paid columns
    // so the per-payer breakdown columns aren't blank on stale snapshots.
    $provCacheOk = $useCache && !empty($cache['provider_performance'])
                && isset($cache['provider_performance'][0])
                && array_key_exists('pri', $cache['provider_performance'][0]);
    if ($provCacheOk) {
        $providerPerf = $cache['provider_performance'];
    } else {
        $providerPerf = qAll($pdo, "
            SELECT provider_name, COUNT(DISTINCT visit_id) AS claims, COUNT(DISTINCT chart_number) AS patients,
                   COUNT(DISTINCT facility) AS facilities, COUNT(DISTINCT charge_code) AS cpts,
                   SUM(charge_amount) AS charge, SUM(amount_allowed) AS allowed,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS paid,
                   SUM(primary_ins_paid) AS pri,
                   SUM(secondary_ins_paid) AS sec,
                   SUM(tertiary_ins_paid) AS ter,
                   SUM(patient_payment_amount) AS pat_paid,
                   SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                   SUM(expected_payment) AS expected,
                   SUM(insurance_balance) AS bal, SUM(patient_balance) AS pat_bal,
                   AVG(days_to_first_payment) AS avg_days,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 THEN visit_id END) AS unpaid_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS prim_pend_n,
                   COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS sec_pend_n
            FROM collection_raw
            WHERE $whereSql AND provider_name IS NOT NULL AND provider_name <> ''
            GROUP BY provider_name ORDER BY charge DESC
        ", $params);
    }
}

if ($rowCount > 0 && $tab === 'compare') {
    /* Point-in-time comparison: two completed upload snapshots side by side
       ("Till this date" = a past upload, "Till today" = the latest upload). */
    $cmpSnaps = qAll($pdo, "SELECT cu.id, cu.completed_at, cu.valid_rows, cu.file_name
        FROM collection_uploads cu JOIN collection_stats cs ON cs.upload_id = cu.id
        WHERE cu.report_id = ? AND cu.kind='collection' AND cu.status='completed'
        ORDER BY cu.completed_at DESC", [$reportId]);
    $cmpCurId  = (int) ($cmpSnaps[0]['id'] ?? 0);          // latest snapshot = "Till today"
    $cmpBaseId = (int) ($_GET['baseline'] ?? 0);           // chosen snapshot = "Till this date"
    if (!$cmpBaseId && count($cmpSnaps) > 1) $cmpBaseId = (int) $cmpSnaps[1]['id'];
    $loadSnap = function($id) use ($pdo) {
        if (!$id) return null;
        $s = $pdo->prepare("SELECT snapshot FROM collection_stats WHERE upload_id = ?");
        $s->execute([$id]); $j = $s->fetchColumn();
        return $j ? json_decode($j, true) : null;
    };
    $cmpCur  = snap_totals($loadSnap($cmpCurId));
    $cmpBase = $cmpBaseId ? snap_totals($loadSnap($cmpBaseId)) : null;
    $cmpCurMeta = $cmpBaseMeta = null;
    foreach ($cmpSnaps as $s) { if ((int)$s['id'] === $cmpCurId) $cmpCurMeta = $s; if ((int)$s['id'] === $cmpBaseId) $cmpBaseMeta = $s; }
}

if ($rowCount > 0 && $tab === 'detail') {
    /* Detail: first 1000 rows. DataTables handles paging/sorting client-side. */
    $detailTotal = (int) qRow($pdo, "SELECT COUNT(*) c FROM collection_raw WHERE $whereSql", $params)['c'];
    /* Two-step strategy:
       Step 1: pull the 1000 newest row IDs via the (report_id, dos) covering index.
               Single-column ORDER BY dos DESC = "Using index" (no filesort).
       Step 2: fetch full columns + CONCAT(patient_full_name) only for those 1000 IDs.
       This drops the Detail-tab query from ~9 s (filesort over 87 k rows) to ~50 ms. */
    $idRows = qAll($pdo, "
        SELECT id FROM collection_raw USE INDEX (idx_col_report_dos)
        WHERE $whereSql
        ORDER BY dos DESC
        LIMIT 1000
    ", $params);
    if ($idRows) {
        $ids = array_column($idRows, 'id');
        $in  = implode(',', array_fill(0, count($ids), '?'));
        $dedSel = $deductibleReady ? ', deductible' : '';
        $detailRows = qAll($pdo, "
            SELECT visit_id, facility, provider_name,
                   TRIM(CONCAT(COALESCE(patient_last_name,''),
                               CASE WHEN patient_last_name IS NOT NULL AND patient_first_name IS NOT NULL THEN ', ' ELSE '' END,
                               COALESCE(patient_first_name,''))) AS patient_full_name,
                   dos, charge_code, charge_code_desc, units, charge_amount,
                   primary_ins_paid, secondary_ins_paid, tertiary_ins_paid, patient_payment_amount,
                   insurance_balance, patient_balance, expected_payment, amount_allowed, wo_amount,
                   carrier1_name, dos_year, dos_month, entry_date $dedSel
            FROM collection_raw WHERE id IN ($in)
            ORDER BY dos DESC, visit_id
        ", $ids);
    } else {
        $detailRows = [];
    }
}

if ($tab === 'history') {
    $upHist = qAll($pdo, "SELECT * FROM collection_uploads WHERE report_id = ? ORDER BY created_at DESC LIMIT 50", [$reportId]);
}

/* ---------------- Report export — every report downloads as a single Excel (.xlsx)
   workbook with TWO sheets: "Summary" (the totals shown for the tab) and "Raw Data"
   (the claim / line-item rows behind it, which reconcile to the summary).  Honours the
   active page filters.  Falls back to a single-sheet CSV only if the host has no Zip. */
if (in_array(($_GET['export'] ?? ''), ['csv','xlsx'], true)) {
    // Client mode: downloads are opt-in — require BOTH a report_access.can_download grant AND
    // client_branding.allow_raw_data. Never stream raw claim-line PHI to a client who wasn't granted it.
    if ($clientMode && (!$clientCanDownload || !$clientRawOk)) {
        http_response_code(403);
        exit('Data download is not enabled for your account. Please contact your account manager.');
    }
    @set_time_limit(0);
    $which = (string) ($_GET['which'] ?? $tab);
    $m = fn($v) => round((float)$v, 2);

    /* ===== SHEET 1 — SUMMARY (the table shown for this report) ===== */
    $sumHeaders = []; $sumRows = [];
    if ($which === 'carriers') {
        $dedBy = $deductibleReady ? coll_deductible_by($pdo, "COALESCE(carrier1_name,'(none)')", $whereSql, $params) : [];
        $sumHeaders = ['Carrier','Patients','Claims','Deductible','Total Paid Claims','Charge Amount','Primary Paid','Secondary Paid','Tertiary Paid','Patient Paid','Total Paid','Insurance Balance','Patient Balance','Primary Pending','Secondary Pending','$ per Paid Claim','Collection %','Unpaid Claims','Avg Days to Pay'];
        foreach ($carrierPerf as $r) {
            $insPaid = (float)($r['ins_paid'] ?? ((float)($r['pri']??0)+(float)($r['sec']??0)+(float)($r['ter']??0)));
            $totalPaidAmt = (float)($r['total_paid'] ?? ($insPaid + (float)($r['pat_paid']??0)));
            $paidN   = (int)($r['paid_n'] ?? 0);
            $claimsN = (int)($r['claims']  ?? 0);
            $cp      = $claimsN > 0 ? round($paidN/$claimsN*100, 1) : 0;   // paid claims / total claims
            $perPaid = $paidN > 0 ? $m($totalPaidAmt/$paidN) : 0;
            $sumRows[]=[$r['carrier'],(int)$r['patients'],(int)$r['claims'],($dedBy[(string)$r['carrier']] ?? 0),$paidN,$m($r['charge']),$m($r['pri']??0),$m($r['sec']??0),$m($r['ter']??0),$m($r['pat_paid']??0),$m($totalPaidAmt),$m($r['bal']),$m($r['pat_bal'] ?? 0),(int)($r['prim_pend_n']??0),(int)($r['sec_pend_n']??0),$perPaid,$cp,(int)$r['unpaid_n'],$r['avg_days']!==null?round((float)$r['avg_days']):''];
        }
    } elseif ($which === 'cpt') {
        $dedBy = $deductibleReady ? coll_deductible_by($pdo, "charge_code", $whereSql, $params) : [];
        $sumHeaders = ['CPT Code','Total Claims','Deductible','Total Paid Claims','Unpaid Claims','Patients','Charge Amount','Fees Scheduled Projection','Primary Paid','Secondary Paid','Tertiary Paid','Patient Paid','Total Paid','Expected to be Paid','$ per Paid Claim','$ per Claim (Overall)','Variance %','Pri Pending','Sec Pending','Insurance Balance','Patient Balance','Unpaid %','Collection %'];
        foreach ($cptAnalysis as $r) {
            $paidAmt = (float)$r['paid'];
            $totalPaidAmt = (float)($r['total_paid'] ?? ((float)($r['pri']??0)+(float)($r['sec']??0)+(float)($r['ter']??0)+(float)($r['pat_paid']??0)));
            $expAmt  = (float)$r['expected'];
            $claimsN = (int)($r['claims'] ?? 0);
            $paidN   = (int)($r['paid_n'] ?? 0);
            $unpaidCl= (int)($r['unpaid_claims_n'] ?? max(0, $claimsN - $paidN));
            $var     = $expAmt > 0 ? round(($totalPaidAmt - $expAmt) / $expAmt * 100, 1) : 0;
            $perPaid = $paidN  > 0 ? $m($totalPaidAmt / $paidN)  : 0;
            $perAll  = $claimsN> 0 ? $m($totalPaidAmt / $claimsN): 0;
            $up      = $claimsN> 0 ? round($unpaidCl / $claimsN * 100) : 0;
            $cp      = $claimsN> 0 ? round($paidN / $claimsN * 100, 1) : 0;   // paid claims / total claims
            $sumRows[] = [
                $r['charge_code'], $claimsN, ($dedBy[(string)$r['charge_code']] ?? 0), $paidN, $unpaidCl,
                (int)$r['patients'], $m($r['charge']), $m($expAmt),
                $m($r['pri'] ?? 0), $m($r['sec'] ?? 0), $m($r['ter'] ?? 0), $m($r['pat_paid'] ?? 0),
                $m($totalPaidAmt),
                $m($expAmt - $totalPaidAmt),
                $perPaid, $perAll, $var,
                (int)($r['prim_pend_n'] ?? 0), (int)($r['sec_pend_n'] ?? 0),
                $m($r['bal'] ?? 0), $m($r['pat_bal'] ?? 0),
                $up, $cp,
            ];
        }
    } elseif ($which === 'providers') {
        $dedBy = $deductibleReady ? coll_deductible_by($pdo, "provider_name", $whereSql, $params) : [];
        $sumHeaders = ['Provider','Total Claims','Deductible','Total Paid Claims','Unpaid Claims','Patients','Facilities','CPTs','Charge Amount','Fees Scheduled Projection','Primary Paid','Secondary Paid','Tertiary Paid','Patient Paid','Total Paid','Expected to be Paid','Insurance Balance','Patient Balance','$ per Paid Claim','$ per Claim (Overall)','Primary Pending Claims','Secondary Pending Claims','Collection %','Avg Days to Pay'];
        foreach ($providerPerf as $r) {
            $paidAmt = (float)$r['paid'];
            $rPri    = (float)($r['pri'] ?? 0);
            $rSec    = (float)($r['sec'] ?? 0);
            $rTer    = (float)($r['ter'] ?? 0);
            $rPatPaid= (float)($r['pat_paid'] ?? 0);
            $totalPaidAmt = (float)($r['total_paid'] ?? ($paidAmt + $rPatPaid));
            $paidN   = (int)($r['paid_n'] ?? 0);
            $claimsN = (int)$r['claims'];
            $unpaidN = (int)($r['unpaid_n'] ?? max(0, $claimsN - $paidN));
            $cp      = $claimsN > 0 ? round($paidN/$claimsN*100, 1) : 0;   // paid claims / total claims
            $perPaid = $paidN   > 0 ? $m($totalPaidAmt/$paidN)   : 0;
            $perAll  = $claimsN > 0 ? $m($totalPaidAmt/$claimsN) : 0;
            $sumRows[]=[$r['provider_name'],$claimsN,($dedBy[(string)$r['provider_name']] ?? 0),$paidN,$unpaidN,(int)$r['patients'],(int)$r['facilities'],(int)$r['cpts'],$m($r['charge']),$m($r['expected']),$m($rPri),$m($rSec),$m($rTer),$m($rPatPaid),$m($totalPaidAmt),$m((float)$r['expected'] - $totalPaidAmt),$m($r['bal']),$m($r['pat_bal'] ?? 0),$perPaid,$perAll,(int)($r['prim_pend_n']??0),(int)($r['sec_pend_n']??0),$cp,$r['avg_days']!==null?round((float)$r['avg_days']):''];
        }
    } elseif ($which === 'aging') {
        $dedBy = $deductibleReady ? coll_deductible_by_aging($pdo, $whereSql, $params) : [];
        $sumHeaders = ['Aging Bucket (days since DOS)','Claims','Deductible','Total Paid Claims','Charge Amount','Fees Scheduled Projection','Primary Paid','Secondary Paid','Tertiary Paid','Patient Paid','Total Paid','Expected to be Paid','Insurance Balance','Patient Balance'];
        foreach (['0-30','31-60','61-90','91-120','121+'] as $bk){ $b=null; foreach($agingBuckets as $r) if($r['bucket']===$bk){$b=$r;break;} if(!$b)$b=['claims'=>0,'paid_n'=>0,'charge'=>0,'expected'=>0,'bal'=>0,'pat_bal'=>0,'pri'=>0,'sec'=>0,'ter'=>0,'pat_paid'=>0,'total_paid'=>0];
            $sumRows[]=[$bk,(int)$b['claims'],($dedBy[$bk] ?? 0),(int)($b['paid_n']??0),$m($b['charge']),$m($b['expected']),$m($b['pri']??0),$m($b['sec']??0),$m($b['ter']??0),$m($b['pat_paid']??0),$m($b['total_paid']??0),$m((float)$b['expected'] - (float)($b['total_paid']??0)),$m($b['bal']),$m($b['pat_bal'] ?? 0)]; }
    } elseif ($which === 'unique-patients') {
        $dedBy = $deductibleReady ? coll_deductible_by($pdo, "COALESCE(facility,'(unmapped)')", $whereSql, $params) : [];
        $sumHeaders = ['Facility','Unique Patients','Providers','Visits (claims)','Deductible','Charge Amount','Paid','Insurance Balance','Patient Balance','Collection %','Days as Patient','Top Carrier'];
        // Reuse the same facility -> top-carrier lookup the UI builds. When unavailable
        // (e.g. exporting from a cached snapshot), fall back to the per-provider charge max.
        $facTopExp = $facTopCarrierMap ?? [];
        if (!$facTopExp) {
            $tmp = [];
            foreach ($uniqFacProv as $p) {
                $f = $p['facility'];
                if (!isset($tmp[$f]) || (float)$p['charge'] > $tmp[$f]['charge']) {
                    $tmp[$f] = ['carrier' => $p['top_carrier'] ?? '—', 'charge' => (float)$p['charge']];
                }
            }
            foreach ($tmp as $f => $row) $facTopExp[$f] = $row['carrier'];
        }
        foreach ($uniqFac as $r) {
            $visits = (int)($r['visits'] ?? 0);
            $paidN  = isset($r['paid_n']) ? (int)$r['paid_n'] : null;
            // Collection % = paid claims / total claims; older snapshots that don't carry paid_n leave it blank.
            $cp     = ($paidN !== null && $visits > 0) ? round($paidN/$visits*100, 1) : '';
            $top    = $facTopExp[$r['facility']] ?? '—';
            $days   = isset($r['days_span']) && $r['days_span'] !== null ? round((float)$r['days_span']) : '';
            $sumRows[]=[$r['facility'],(int)$r['uniq_pt'],(int)($r['providers']??0),$visits,($dedBy[$r['facility']] ?? 0),$m($r['charge']),$m($r['paid']),$m($r['bal']),$m($r['pat_bal'] ?? 0),$cp,$days,$top];
        }
    } elseif ($which === 'unbilled') {
        $sumHeaders = ['Month','Unbilled Claims','Fees Scheduled Projection','Total Balance'];
        foreach ($unbilled as $mo=>$v) $sumRows[]=[$mo,(int)$v['c'],$m($v['exp']),$m($v['out'])];
    } elseif ($which === 'unpaid') {
        $sumHeaders = ['Month','Unpaid / Appealed Claims','Charge Amount','Fees Scheduled Projection','Total Balance','% of Expected'];
        foreach ($unpaid as $mo=>$v){ $p=(float)$v['charge']>0?round((float)$v['exp']/(float)$v['charge']*100,1):0; $sumRows[]=[$mo,(int)$v['c'],$m($v['charge']),$m($v['exp']),$m($v['out']),$p]; }
    } elseif ($which === 'hold') {
        $sumHeaders = ['Month','Hold-for-Deductible Claims','Fees Scheduled Projection','Total Balance'];
        foreach ($hold as $mo=>$v) $sumRows[]=[$mo,(int)$v['c'],$m($v['exp']),$m($v['out'])];
    } elseif ($which === 'recent') {
        $sumHeaders = ['Entry Date On/After','Recently Billed Claims','Charge Amount','Total Balance'];
        $sumRows[] = [(string)($recentlyBilled['since'] ?? ''),(int)($recentlyBilled['claims'] ?? 0),$m($recentlyBilled['charge'] ?? 0),$m($recentlyBilled['outstanding'] ?? 0)];
    } else { // summary (default) and the Detail (Raw) tab → monthly summary
        $dedByMonth = $deductibleReady ? coll_deductible_by($pdo, "CONCAT(dos_year,'-',dos_month)", $whereSql, $params) : [];
        $sumHeaders = ['Month','Total Claims','Deductible','Total Paid Claims','Unpaid Claims','Charge Amount','Fees Scheduled Projection','Primary Paid','Secondary Paid','Tertiary Paid','Patient Paid','Total Paid','Expected to be Paid','$ per Paid Claim','$ per Claim (Overall)','Primary Pending Claims','Secondary Pending Claims','Total Outstanding Claims','Primary Outstanding','Secondary Outstanding','Insurance Balance','Patient Balance','Collection %'];
        $monthData = $byMonth;
        if (!$monthData) {   // exporting from a tab that didn't build $byMonth (e.g. Detail / Raw)
            $monthData = [];
            $mr = qAll($pdo, "SELECT dos_year, dos_month,
                       COUNT(DISTINCT visit_id) claims,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid>0 THEN visit_id END) paid,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid=0 AND (insurance_balance>0 OR patient_balance>0) THEN visit_id END) unpaid,
                       SUM(charge_amount) charge, SUM(primary_ins_paid) pri,
                       SUM(secondary_ins_paid) sec, SUM(tertiary_ins_paid) ter,
                       SUM(patient_payment_amount) pat_paid,
                       SUM(expected_payment) expected,
                       SUM(insurance_balance) bal, SUM(patient_balance) pat_bal,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid=0 AND (insurance_balance>0 OR patient_balance>0) THEN visit_id END) prim_pend,
                       COUNT(DISTINCT CASE WHEN primary_ins_paid>0 AND secondary_ins_paid=0 AND (insurance_balance>0 OR patient_balance>0) THEN visit_id END) sec_pend,
                       COUNT(DISTINCT CASE WHEN (insurance_balance>0 OR patient_balance>0) THEN visit_id END) outst_n,
                       SUM(CASE WHEN primary_ins_paid=0 AND (insurance_balance>0 OR patient_balance>0) THEN insurance_balance + patient_balance ELSE 0 END) prim_outst,
                       SUM(CASE WHEN primary_ins_paid>0 AND secondary_ins_paid=0 AND (insurance_balance>0 OR patient_balance>0) THEN insurance_balance + patient_balance ELSE 0 END) sec_outst
                   FROM collection_raw WHERE $whereSql AND dos_year IS NOT NULL AND dos_month IS NOT NULL
                   GROUP BY dos_year, dos_month ORDER BY dos_year, dos_month", $params);
            foreach ($mr as $r) { $lab=$monthNames[(int)$r['dos_month']].' '.substr((string)$r['dos_year'],-2);
                $monthData[$lab]=['year'=>(int)$r['dos_year'],'month'=>(int)$r['dos_month'],'claims'=>(int)$r['claims'],'paid'=>(int)$r['paid'],'unpaid'=>(int)$r['unpaid'],'charge'=>(float)$r['charge'],'pri'=>(float)$r['pri'],'sec'=>(float)$r['sec'],'ter'=>(float)$r['ter'],'pat_paid'=>(float)$r['pat_paid'],'expected'=>(float)$r['expected'],'bal'=>(float)$r['bal'],'pat_bal'=>(float)$r['pat_bal'],'prim_pend'=>(int)$r['prim_pend'],'sec_pend'=>(int)$r['sec_pend'],'outst_n'=>(int)$r['outst_n'],'prim_outst'=>(float)$r['prim_outst'],'sec_outst'=>(float)$r['sec_outst']]; }
        }
        foreach ($monthData as $mo=>$v){
            $vTer = (float)($v['ter'] ?? 0);
            $vPatP = (float)($v['pat_paid'] ?? 0);
            $totPaid = $v['pri'] + $v['sec'] + $vTer + $vPatP;   // Total Paid = Pri + Sec + Ter + Patient
            $pp=$v['paid']>0?$m($totPaid/$v['paid']):0;
            $po=$v['claims']>0?$m($totPaid/$v['claims']):0;
            $cp=$v['claims']>0?round($v['paid']/$v['claims']*100,1):0;   // paid claims / total claims
            $sumRows[]=[$mo,(int)$v['claims'],($dedByMonth[($v['year'] ?? '').'-'.($v['month'] ?? '')] ?? 0),(int)$v['paid'],(int)$v['unpaid'],$m($v['charge']),$m($v['expected']),$m($v['pri']),$m($v['sec']),$m($vTer),$m($vPatP),$m($totPaid),$m((float)$v['expected'] - $totPaid),$pp,$po,(int)$v['prim_pend'],(int)$v['sec_pend'],(int)$v['outst_n'],$m($v['prim_outst']),$m($v['sec_outst']),$m($v['bal']),$m($v['pat_bal'] ?? 0),$cp];
        }
    }

    /* ----- GRAND TOTAL row appended to the Summary sheet (skip the 1-line "recent" summary).
       Dimension scorecards use an accurate aggregate (distinct counts + recomputed ratios);
       month / bucket reports sum the additive columns and blank the non-summable ratios. ----- */
    if ($which !== 'recent' && $sumRows) {
        // Deductible grand total for the dimension scorecards = distinct deductible visits (matches the
        // on-screen GRAND TOTAL). The month/bucket reports (else branch) auto-sum their per-row counts.
        $dedGrandN = ($deductibleReady && in_array($which, ['carriers','cpt','providers'], true)) ? coll_deductible_total($pdo, $whereSql, $params)['n'] : 0;
        if (in_array($which, ['carriers','cpt','providers'], true)) {
            $gt = coll_grand_totals($pdo, $which, $whereSql, $params);
            if ($which === 'carriers') {
                $insPaid = (float)($gt['ins_paid'] ?? ((float)($gt['pri']??0)+(float)($gt['sec']??0)+(float)($gt['ter']??0)));
                $totalPaidAmt = (float)($gt['total_paid'] ?? ($insPaid + (float)($gt['pat_paid']??0)));
                $paidN   = (int)($gt['paid_n'] ?? 0);
                $claimsN = (int)($gt['claims']  ?? 0);
                $cp      = $claimsN > 0 ? round($paidN/$claimsN*100, 1) : 0;   // paid claims / total claims
                $perPaid = $paidN > 0 ? $m($totalPaidAmt/$paidN) : 0;
                $sumRows[]=['TOTAL',(int)($gt['patients']??0),(int)($gt['claims']??0),$dedGrandN,$paidN,$m($gt['charge']??0),$m($gt['pri']??0),$m($gt['sec']??0),$m($gt['ter']??0),$m($gt['pat_paid']??0),$m($totalPaidAmt),$m($gt['bal']??0),$m($gt['pat_bal']??0),(int)($gt['prim_pend_n']??0),(int)($gt['sec_pend_n']??0),$perPaid,$cp,(int)($gt['unpaid_n']??0),(isset($gt['avg_days'])&&$gt['avg_days']!==null)?round((float)$gt['avg_days']):''];
            } elseif ($which === 'cpt') {
                $gtPaidAmt = (float)($gt['paid'] ?? 0);
                $gtTotalPaid = (float)($gt['total_paid'] ?? ((float)($gt['pri']??0)+(float)($gt['sec']??0)+(float)($gt['ter']??0)+(float)($gt['pat_paid']??0)));
                $gtExp     = (float)($gt['expected'] ?? 0);
                $gtClaims  = (int)($gt['claims'] ?? 0);
                $gtPaidN   = (int)($gt['paid_n'] ?? 0);
                $gtUnpaidCl= (int)($gt['unpaid_claims_n'] ?? max(0, $gtClaims - $gtPaidN));
                $var = $gtExp    > 0 ? round(($gtTotalPaid - $gtExp) / $gtExp * 100, 1) : 0;
                $pp  = $gtPaidN  > 0 ? $m($gtTotalPaid / $gtPaidN)  : 0;
                $po  = $gtClaims > 0 ? $m($gtTotalPaid / $gtClaims) : 0;
                $up  = $gtClaims > 0 ? round($gtUnpaidCl / $gtClaims * 100) : 0;
                $cp  = $gtClaims > 0 ? round($gtPaidN  / $gtClaims * 100, 1) : 0;   // paid claims / total claims
                $sumRows[] = ['TOTAL',$gtClaims,$dedGrandN,$gtPaidN,$gtUnpaidCl,
                    (int)($gt['patients']??0),$m($gt['charge']??0),$m($gtExp),
                    $m($gt['pri']??0),$m($gt['sec']??0),$m($gt['ter']??0),$m($gt['pat_paid']??0),
                    $m($gtTotalPaid),
                    $m($gtExp - $gtTotalPaid),
                    $pp,$po,$var,
                    (int)($gt['prim_pend_n']??0),(int)($gt['sec_pend_n']??0),
                    $m($gt['bal']??0),$m($gt['pat_bal']??0),
                    $up,$cp];
            } else { // providers
                $paidAmt = (float)($gt['paid'] ?? 0);
                $gtPri   = (float)($gt['pri'] ?? 0);
                $gtSec   = (float)($gt['sec'] ?? 0);
                $gtTer   = (float)($gt['ter'] ?? 0);
                $gtPatP  = (float)($gt['pat_paid'] ?? 0);
                $gtTotalPaid = (float)($gt['total_paid'] ?? ($paidAmt + $gtPatP));
                $paidN   = (int)($gt['paid_n'] ?? 0);
                $claimsN = (int)($gt['claims'] ?? 0);
                $unpaidN = (int)($gt['unpaid_n'] ?? max(0, $claimsN - $paidN));
                $cp      = $claimsN > 0 ? round($paidN/$claimsN*100, 1) : 0;   // paid claims / total claims
                $perPaid = $paidN   > 0 ? $m($gtTotalPaid/$paidN)   : 0;
                $perAll  = $claimsN > 0 ? $m($gtTotalPaid/$claimsN) : 0;
                $sumRows[]=['TOTAL',$claimsN,$dedGrandN,$paidN,$unpaidN,(int)($gt['patients']??0),(int)($gt['facilities']??0),(int)($gt['cpts']??0),$m($gt['charge']??0),$m($gt['expected']??0),$m($gtPri),$m($gtSec),$m($gtTer),$m($gtPatP),$m($gtTotalPaid),$m((float)($gt['expected']??0) - $gtTotalPaid),$m($gt['bal']??0),$m($gt['pat_bal']??0),$perPaid,$perAll,(int)($gt['prim_pend_n']??0),(int)($gt['sec_pend_n']??0),$cp,(isset($gt['avg_days'])&&$gt['avg_days']!==null)?round((float)$gt['avg_days']):''];
            }
        } else {
            $tot = array_fill(0, count($sumHeaders), '');
            $tot[0] = 'TOTAL';
            foreach ($sumRows as $row){ $vals=array_values($row); $c=count($vals); for($i=1;$i<$c;$i++){ $v=$vals[$i]; if(is_int($v)||is_float($v)) $tot[$i]=(is_string($tot[$i])?0:$tot[$i])+$v; } }
            foreach ($tot as $k=>$v){ if (is_float($v)) $tot[$k]=round($v,2); }
            if ($which === 'unpaid')      { $tot[5]=(is_numeric($tot[2])&&$tot[2]>0)?round($tot[3]/$tot[2]*100,1):0; }   // % of Expected = expected / charge
            elseif ($which === 'summary') {
                // Columns (after the Deductible column at 2): 0 Month, 1 Total, 2 Deductible, 3 PaidClaims,
                // 4 Unpaid, 5 Charge, 6 Expected, 7 Pri, 8 Sec, 9 Ter, 10 PatPaid, 11 TotalPaid,
                // 12 ExpToBePaid (auto-summed), 13 $/Paid, 14 $/Claim, 15 PrimPend, 16 SecPend, 17 OutstN,
                // 18 PrimOutst, 19 SecOutst, 20 InsBal, 21 PatBal, 22 Collpct
                $tot[13] =(is_numeric($tot[3])&&$tot[3]>0)?round($tot[11]/$tot[3],2):0;    // $ per Paid Claim = total paid / paid claims
                $tot[14] =(is_numeric($tot[1])&&$tot[1]>0)?round($tot[11]/$tot[1],2):0;    // $ per Claim (Overall) = total paid / total claims
                $tot[22] =(is_numeric($tot[1])&&$tot[1]>0)?round($tot[3]/$tot[1]*100,1):0; // Collection % = paid claims / total claims
            }
            if ($which === 'unique-patients' && !empty($uniqGrandTotal)) {   // accurate distinct totals
                // Columns shifted by the Deductible column at index 4 (auto-summed = distinct total, one facility per visit).
                $tot[1]=(int)$uniqGrandTotal['uniq_pt']; $tot[2]=''; $tot[3]=(int)$uniqGrandTotal['visits'];
                $tot[5]=$m($uniqGrandTotal['charge']); $tot[6]=$m($uniqGrandTotal['paid']);
                $tot[7]=$m($uniqGrandTotal['bal']); $tot[8]=$m($uniqGrandTotal['pat_bal'] ?? 0);
            }
            $sumRows[] = $tot;
        }
    }

    /* ===== SHEET 2 — RAW DATA (the claim / line-item rows behind the report) =====
       Each report's own definition is applied ON TOP of the active page filters, so the
       rows reconcile to the summary above.  Streamed row-by-row so it stays memory-light. */
    $dWhere = $whereSql; $dParams = $params;
    if ($which === 'unpaid') {
        $dWhere .= " AND primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND NOT (charge_amount > 0 AND insurance_balance < charge_amount*0.4)";
    } elseif ($which === 'hold') {
        $dWhere .= " AND primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) AND charge_amount > 0 AND insurance_balance < charge_amount*0.4";
    } elseif ($which === 'unbilled') {
        $dWhere .= " AND wo_amount > 0 AND primary_ins_paid = 0 AND secondary_ins_paid = 0";
    } elseif ($which === 'aging') {
        $dWhere .= " AND (insurance_balance > 0 OR patient_balance > 0) AND dos IS NOT NULL";
        $agMap = ['0-30'=>[0,30],'31-60'=>[31,60],'61-90'=>[61,90],'91-120'=>[91,120],'121+'=>[121,100000]];
        if (isset($agMap[(string)($_GET['bucket'] ?? '')])) { [$lo,$hi]=$agMap[(string)$_GET['bucket']]; $dWhere .= " AND DATEDIFF(CURDATE(),dos) BETWEEN ? AND ?"; $dParams[]=$lo; $dParams[]=$hi; }
    } elseif ($which === 'recent') {
        // report-level rule (ignores page filters) → reconciles to the "Recently billed" banner.
        // Both the outer scope and the MAX(entry_date) subquery use the virtual scope so the
        // rollup view aggregates entry dates across mirror sources too.
        $dWhere  = "{$scope['sql']} AND entry_date IS NOT NULL AND entry_date >= DATE_SUB((SELECT MAX(entry_date) FROM collection_raw WHERE {$scope['sql']}), INTERVAL 30 DAY)";
        $dParams = array_merge($scope['params'], $scope['params']);
    }
    $rawHeaders = ['Visit ID','Patient Account','Facility','Provider','Patient','DOS','Entry Date','Aging Bucket','Days Since DOS','CPT Code','CPT Description','Units','Charge Amount','Fees Scheduled Projection','Amount Allowed','Primary Paid','Secondary Paid','Tertiary Paid','Patient Paid','Write-off','Insurance Balance','Patient Balance','Primary Carrier','Secondary Carrier'];
    $rawNumeric = [8,11,12,13,14,15,16,17,18,19,20,21];   // Days Since DOS, Units, and all $ columns (incl. Patient Balance)
    // Deductible flag as a trailing column (0/1) — appended so no existing column index shifts. Conditional
    // on the column existing so the SELECT never references a missing column.
    $rawDedSel = '';
    if ($deductibleReady) { $rawHeaders[] = 'Deductible'; $rawDedSel = ', deductible'; }
    $rawStmt = $pdo->prepare("SELECT visit_id, chart_number, COALESCE(facility,''), COALESCE(provider_name,''),
           TRIM(CONCAT(COALESCE(patient_last_name,''), CASE WHEN patient_last_name IS NOT NULL AND patient_first_name IS NOT NULL THEN ', ' ELSE '' END, COALESCE(patient_first_name,''))) AS patient,
           DATE_FORMAT(dos,'%m/%d/%Y') AS dos_disp, DATE_FORMAT(entry_date,'%m/%d/%Y') AS entry_disp,
           CASE WHEN (insurance_balance > 0 OR patient_balance > 0) AND dos IS NOT NULL THEN
                CASE WHEN DATEDIFF(CURDATE(),dos) <= 30 THEN '0-30' WHEN DATEDIFF(CURDATE(),dos) <= 60 THEN '31-60'
                     WHEN DATEDIFF(CURDATE(),dos) <= 90 THEN '61-90' WHEN DATEDIFF(CURDATE(),dos) <= 120 THEN '91-120' ELSE '121+' END
                ELSE '' END AS aging_bucket,
           CASE WHEN dos IS NOT NULL THEN DATEDIFF(CURDATE(),dos) ELSE '' END AS days_since_dos,
           charge_code, charge_code_desc, units, charge_amount, expected_payment, amount_allowed,
           primary_ins_paid, secondary_ins_paid, tertiary_ins_paid, patient_payment_amount, wo_amount,
           insurance_balance, patient_balance, carrier1_name, carrier2_name $rawDedSel
        FROM collection_raw WHERE $dWhere ORDER BY (insurance_balance + patient_balance) DESC, dos DESC LIMIT 300000");
    $rawStmt->execute($dParams);

    /* ===== OUTPUT — single .xlsx with both sheets (CSV fallback if no Zip ext) ===== */
    $base = preg_replace('/[^A-Za-z0-9]+/','-', (string)($reportCtx['cname'] ?? 'report')) . '_' . $which
          . ($fYears  ? '_y'.implode('+', $fYears)  : '')
          . ($fMonths ? '_m'.implode('+', array_map(fn($m)=>str_pad((string)$m,2,'0',STR_PAD_LEFT), $fMonths)) : '')
          . '_' . date('Ymd_His');

    if (class_exists('ZipArchive')) {
        require_once __DIR__ . '/../includes/xlsx.php';
        $xl = new SimpleXlsx();
        $xl->addSheet('Summary', $sumHeaders, $sumRows);
        $xl->addSheetFromStmt('Raw Data', $rawHeaders, $rawStmt, $rawNumeric);
        $xl->download($base . '.xlsx');     // sends headers + body, cleans temp files, exit
    }
    // Fallback (host without the Zip extension): single-sheet CSV of the raw supporting data.
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $base . '.csv"');
    $o = fopen('php://output', 'w'); fwrite($o, "\xEF\xBB\xBF");
    fputcsv($o, $rawHeaders);
    while ($r = $rawStmt->fetch(PDO::FETCH_NUM)) fputcsv($o, $r);
    fclose($o); exit;
}

$pageTitle = 'Collection Dashboard';
require $cpHead;
?>
<style>
.rv-title-bar { padding:14px 0 10px; border-bottom: 1px solid var(--m-border); margin-bottom:14px; }
.rv-title-bar .title { font-size: 1.45rem; font-weight: 700; line-height:1.2; color: var(--m-text); }
.rv-title-bar .crumbs { font-size: .82rem; color: var(--m-text-soft); margin-top:4px; }

.rv-insights {
    background: linear-gradient(135deg, #1f2937 0%, #1e3a8a 100%);
    color:#fff; border-radius:12px; padding:16px 20px; margin-bottom:14px;
    box-shadow: var(--m-shadow-md);
}
.rv-insights .insight-stat { padding-right: 18px; border-right: 1px solid rgba(255,255,255,.12); margin-right: 12px; }
.rv-insights .insight-stat:last-child { border-right: 0; }
.rv-insights .lbl { font-size:.7rem; text-transform: uppercase; letter-spacing: .04em; opacity: .65; }
.rv-insights .val { font-size:1.15rem; font-weight: 700; line-height: 1.2; margin-top:2px; }
.rv-insights .trend-up   { color:#86efac; }
.rv-insights .trend-down { color:#fca5a5; }
.rv-insights .trend-flat { color:#cbd5e1; }
.rv-insights .anomaly { background: rgba(245, 158, 11, .15); padding:2px 8px; border-radius:12px; font-size:.75rem; margin-right:6px; }

.kpi-strip .kpi { background: var(--m-surface); border:1px solid var(--m-border); border-radius:10px; padding:11px 13px; transition: all .12s; }
.kpi-strip .kpi:hover { border-color: var(--m-border-strong); box-shadow: var(--m-shadow-md); }
.kpi-strip .kpi .lbl { font-size:.65rem; text-transform: uppercase; color: var(--m-text-soft); letter-spacing:.04em; }
.kpi-strip .kpi .val { font-size:1.4rem; font-weight:700; line-height:1.1; color: var(--m-text); margin-top:2px; font-variant-numeric: tabular-nums; }
.kpi-strip .kpi .delta { font-size:.72rem; margin-top:2px; }
.kpi-strip .kpi .spark { height: 32px; margin-top:4px; }

.rv-filters { background: var(--m-surface); border:1px solid var(--m-border); border-radius:10px; padding:10px 14px; margin-bottom:14px; }
.rv-filters .form-select-sm, .rv-filters .form-control-sm { font-size:.82rem; }
.filter-chips { display:inline-flex; gap:6px; flex-wrap:wrap; margin-top:6px; }
.filter-chip { display:inline-flex; align-items:center; gap:6px; background: var(--m-surface-2); border:1px solid var(--m-border); padding: 3px 8px 3px 10px; border-radius:14px; font-size:.78rem; color: var(--m-text); }
.filter-chip a { color: var(--m-text-soft); text-decoration:none; }
.filter-chip a:hover { color: var(--m-bad); }

.rv-tabs { border-bottom: 2px solid var(--m-border); margin-bottom:14px; }
.rv-tabs .nav-link { padding: 9px 16px; font-size:.88rem; color: var(--m-text-soft); border-radius:6px 6px 0 0; }
.rv-tabs .nav-link:hover { color: var(--m-text); }
.rv-tabs .nav-link.active { color:var(--tblr-primary); border-bottom: 2px solid var(--tblr-primary); margin-bottom:-2px; background:transparent; font-weight: 600; }

.snap-badge { display:inline-flex; align-items:center; gap:6px; font-size:.78rem; padding:3px 9px; border-radius:99px; background: var(--m-surface-2); border:1px solid var(--m-border); color: var(--m-text-soft); }

.databar { position: relative; display: inline-block; min-width: 60px; padding-left: 4px; }
.databar .bar { position: absolute; left:0; top: 50%; transform: translateY(-50%); height: 16px; border-radius: 2px; opacity: .22; pointer-events: none; }
.databar .v { position: relative; z-index: 1; font-variant-numeric: tabular-nums; }
</style>

<div class="container-xl">

<!-- =============== TITLE BAR =============== -->
<div class="rv-title-bar">
    <div class="row align-items-center g-3">
        <div class="col">
            <a href="<?= e(url($clientMode ? ('/client/overall.php' . ($cp_slug !== '' ? '?slug=' . $cp_slug : '')) : '/admin/reports.php')) ?>" class="text-secondary small text-decoration-none"><i class="ti ti-chevron-left"></i> <?= $clientMode ? 'Dashboard' : 'Reports' ?></a>
            <div class="title mt-1"><?= e($reportCtx['name']) ?>
                <code class="ms-2 text-secondary fw-normal" style="font-size:.6em"><?= e($reportCtx['code']) ?></code>
            </div>
            <div class="crumbs">
                <i class="ti ti-building me-1"></i><?= e($reportCtx['vname']) ?>
                <span class="opacity-50 mx-1">›</span>
                <?= e($reportCtx['cname']) ?>
                <span class="opacity-50 mx-1">›</span>
                <?= e($reportCtx['pname']) ?>
                <?php if ($lastUpload): ?>
                    <span class="ms-2 snap-badge"><i class="ti ti-camera"></i> Collection updated <?= e(fmt_dt($lastUpload['completed_at'])) ?> · <?= number_format((int)$lastUpload['valid_rows']) ?> rows</span>
                <?php endif; ?>
            </div>
        </div>
        <?php if (can('report.upload')): ?>
        <div class="col-auto">
            <div class="btn-group btn-group-sm">
                <a href="<?= e(url('/admin/upload-collection.php?id='.$reportId)) ?>" class="btn btn-primary"><i class="ti ti-cloud-upload me-1"></i>Replace Collection</a>
                <a href="<?= e(url('/admin/facility-mapping.php?id='.$reportId)) ?>" class="btn btn-outline-primary"><i class="ti ti-building-community me-1"></i>Mapping<?php if ($mapCount === 0): ?> <span class="badge bg-warning text-dark ms-1">!</span><?php endif; ?></a>
                <a href="<?= e(url('/admin/deductible-mapping.php?id='.$reportId)) ?>" class="btn btn-outline-primary" title="Upload the list of deductible Visit IDs"><i class="ti ti-receipt-tax me-1"></i>Deductible</a>
            </div>
        </div>
        <?php endif; ?>
    </div>
</div>

<?php if ($rowCount === 0): ?>
    <div class="card text-center p-5">
        <div style="font-size:3rem">📥</div>
        <h3 class="mt-3">No collection data loaded yet</h3>
        <p class="text-secondary mb-3">Upload the Collection Raw CSV to begin. Facility mapping can be loaded before or after — it auto-applies. Fees Scheduled Projection is computed automatically from each CPT's average primary-insurance payment.</p>
        <div class="d-flex justify-content-center gap-2">
            <?php if (can('report.upload')): ?>
            <a href="<?= e(url('/admin/upload-collection.php?id='.$reportId)) ?>" class="btn btn-primary"><i class="ti ti-cloud-upload me-1"></i>Upload Collection Raw</a>
            <a href="<?= e(url('/admin/facility-mapping.php?id='.$reportId)) ?>" class="btn btn-outline-primary"><i class="ti ti-building-community me-1"></i>Facility Mapping</a>
            <?php else: ?>
            <span class="text-secondary small"><i class="ti ti-info-circle me-1"></i>No data loaded yet. Ask a user with upload access to load the Collection Raw file.</span>
            <?php endif; ?>
        </div>
    </div>
<?php else: ?>

    <!-- =============== FILTER BAR =============== -->
    <?php $hasAnyFilter = $fFacilities || $fProviders || $fYears || $fMonths || $fStatuses || $fSpecialty !== '' || $fDeductible !== ''; ?>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/tom-select@2.3.1/dist/css/tom-select.bootstrap5.min.css">
    <script src="https://cdn.jsdelivr.net/npm/tom-select@2.3.1/dist/js/tom-select.complete.min.js"></script>
    <style>
        .rv-filters .ts-wrapper.form-select-sm { min-height: calc(1.5em + .5rem + 2px); padding: 0; }
        .rv-filters .ts-control { padding: .25rem .5rem; font-size: .875rem; min-height: calc(1.5em + .5rem + 2px); background: var(--tblr-body-bg, #fff); }
        .rv-filters .ts-control input { font-size: .875rem; }
        /* Solid background + above-tabs stacking so the open menu never bleeds into the tab bar below. */
        .ts-dropdown {
            background: var(--tblr-body-bg, #fff) !important;
            border: 1px solid var(--tblr-border-color, #dadfe5) !important;
            box-shadow: 0 6px 24px rgba(0,0,0,.12) !important;
            z-index: 1080 !important;
            font-size: .875rem;
        }
        .ts-dropdown .option, .ts-dropdown .optgroup-header { background: var(--tblr-body-bg, #fff); color: var(--tblr-body-color, #1d273b); }
        .ts-dropdown .option.active, .ts-dropdown .active { background: var(--tblr-primary, #206bc4) !important; color: #fff !important; }
        .ts-dropdown .option:hover { background: rgba(32,107,196,.08); }
        /* Tom Select wrapper should also stack above the tab bar while open. */
        .rv-filters .ts-wrapper.focus, .rv-filters .ts-wrapper.dropdown-active { z-index: 1080; position: relative; }
    </style>
    <form method="GET" class="rv-filters" id="collFilterForm">
        <input type="hidden" name="id"  value="<?= $reportId ?>">
        <input type="hidden" name="tab" value="<?= e($tab) ?>">
        <div class="row g-2 align-items-center">
            <div class="col-auto small text-secondary"><i class="ti ti-filter me-1"></i><strong>Filters:</strong></div>
            <div class="col-md-3">
                <select name="facility[]" multiple class="form-select form-select-sm coll-ms" data-placeholder="All facilities (<?= count($facilities) ?>)">
                    <?php foreach ($facilities as $f): ?>
                        <option value="<?= e($f) ?>" <?= in_array($f, $fFacilities, true) ? 'selected' : '' ?>><?= e($f) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-3">
                <select name="provider[]" multiple class="form-select form-select-sm coll-ms" data-placeholder="All providers (<?= count($providers) ?>)">
                    <?php foreach ($providers as $p): ?>
                        <option value="<?= e($p) ?>" <?= in_array($p, $fProviders, true) ? 'selected' : '' ?>><?= e($p) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <select name="year[]" multiple class="form-select form-select-sm coll-ms" data-placeholder="All years">
                    <?php foreach ($years as $y): ?>
                        <option value="<?= (int)$y ?>" <?= in_array((int)$y, $fYears, true) ? 'selected' : '' ?>><?= (int)$y ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <select name="month[]" multiple class="form-select form-select-sm coll-ms" data-placeholder="All months">
                    <?php foreach ($monthNames as $i => $mn): ?>
                        <option value="<?= (int)$i ?>" <?= in_array((int)$i, $fMonths, true) ? 'selected' : '' ?>><?= $mn ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <?php if ($specialtyTableOk && $specialties): ?>
            <div class="col-md-2">
                <select name="specialty" class="form-select form-select-sm coll-single" data-placeholder="All specialties" title="Filter by specialty — scopes the dashboard to the CPT/charge codes mapped to that specialty">
                    <option value="">All specialties</option>
                    <?php foreach ($specialties as $sp): ?>
                        <option value="<?= e($sp) ?>" <?= $sp === $fSpecialty ? 'selected' : '' ?>><?= e($sp) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <?php endif; ?>
            <div class="col-md-3">
                <select name="status[]" multiple class="form-select form-select-sm coll-ms" data-placeholder="— Claim status —" title="Filter the whole dashboard to a claim subset (recently billed, unpaid, primary/secondary pending …)">
                    <?php foreach ($statusOpts as $sv => $sl): if ($sv === '') continue; ?>
                        <option value="<?= e($sv) ?>" <?= in_array($sv, $fStatuses, true) ? 'selected' : '' ?>><?= e($sl) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <select name="deductible" class="form-select form-select-sm coll-single" data-placeholder="All claims" title="Filter the whole dashboard by deductible status (deductible visit IDs uploaded on the Deductible page)">
                    <option value=""  <?= $fDeductible === ''  ? 'selected' : '' ?>>All (deductible + not)</option>
                    <option value="1" <?= $fDeductible === '1' ? 'selected' : '' ?>>Deductible only</option>
                    <option value="0" <?= $fDeductible === '0' ? 'selected' : '' ?>>Non-deductible</option>
                </select>
            </div>
            <div class="col-12 d-flex justify-content-end gap-2 mt-1">
                <button type="submit" class="btn btn-sm btn-primary"><i class="ti ti-check me-1"></i>Apply filters</button>
                <?php if (!in_array($tab, ['history'], true)): ?>
                    <?php if ($showExport): ?><a href="<?= e(col_url(['export'=>'xlsx','which'=>$tab])) ?>" class="btn btn-sm btn-outline-success" title="Download Excel — Sheet 1: summary totals, Sheet 2: raw claim data (current filters applied)"><i class="ti ti-file-spreadsheet me-1"></i>Download Excel</a><?php endif; ?>
                <?php endif; ?>
                <?php if ($hasAnyFilter): ?>
                    <a href="?id=<?= $reportId ?>&tab=<?= e($tab) ?>" class="btn btn-sm btn-ghost-secondary"><i class="ti ti-x"></i> Clear all</a>
                <?php endif; ?>
            </div>
        </div>
        <?php if ($hasAnyFilter): ?>
            <div class="filter-chips">
                <?php foreach ($fStatuses as $s): ?>
                    <span class="filter-chip" style="background:#fde68a;border-color:#f59e0b"><i class="ti ti-filter-cog"></i><?= e($statusOpts[$s] ?? $s) ?><a href="<?= e(col_url(['status'=>array_values(array_diff($fStatuses, [$s]))])) ?>"><i class="ti ti-x"></i></a></span>
                <?php endforeach; ?>
                <?php if ($fSpecialty !== ''): ?>
                    <span class="filter-chip" style="background:#dbeafe;border-color:#3b82f6"><i class="ti ti-stethoscope"></i><?= e($fSpecialty) ?><a href="<?= e(col_url(['specialty'=>null])) ?>"><i class="ti ti-x"></i></a></span>
                <?php endif; ?>
                <?php if ($fDeductible !== ''): ?>
                    <span class="filter-chip" style="background:#fee2e2;border-color:#ef4444"><i class="ti ti-receipt-tax"></i><?= $fDeductible === '1' ? 'Deductible only' : 'Non-deductible' ?><a href="<?= e(col_url(['deductible'=>null])) ?>"><i class="ti ti-x"></i></a></span>
                <?php endif; ?>
                <?php foreach ($fFacilities as $fv): ?>
                    <span class="filter-chip"><i class="ti ti-building"></i><?= e($fv) ?><a href="<?= e(col_url(['facility'=>array_values(array_diff($fFacilities, [$fv]))])) ?>"><i class="ti ti-x"></i></a></span>
                <?php endforeach; ?>
                <?php foreach ($fProviders as $pv): ?>
                    <span class="filter-chip"><i class="ti ti-stethoscope"></i><?= e($pv) ?><a href="<?= e(col_url(['provider'=>array_values(array_diff($fProviders, [$pv]))])) ?>"><i class="ti ti-x"></i></a></span>
                <?php endforeach; ?>
                <?php foreach ($fYears as $yv): ?>
                    <span class="filter-chip"><i class="ti ti-calendar"></i><?= (int)$yv ?><a href="<?= e(col_url(['year'=>array_values(array_diff($fYears, [$yv]))])) ?>"><i class="ti ti-x"></i></a></span>
                <?php endforeach; ?>
                <?php foreach ($fMonths as $mv): ?>
                    <span class="filter-chip"><i class="ti ti-calendar-month"></i><?= e($monthNames[$mv] ?? '') ?><a href="<?= e(col_url(['month'=>array_values(array_diff($fMonths, [$mv]))])) ?>"><i class="ti ti-x"></i></a></span>
                <?php endforeach; ?>
            </div>
        <?php endif; ?>
    </form>
    <script>
    (function(){
        if (typeof TomSelect === 'undefined') return;
        document.querySelectorAll('.coll-ms').forEach(function(sel){
            new TomSelect(sel, {
                plugins: ['remove_button', 'clear_button'],
                placeholder: sel.dataset.placeholder || '',
                maxOptions: 5000,
                hidePlaceholder: false,
                persist: false,
            });
        });
        /* Single-select (specialty): searchable, with a clear button to return to "All". */
        document.querySelectorAll('.coll-single').forEach(function(sel){
            new TomSelect(sel, {
                plugins: ['clear_button'],
                maxOptions: 5000,
                allowEmptyOption: true,
                persist: false,
            });
        });
    })();
    </script>

    <!-- =============== TABS =============== -->
    <ul class="nav rv-tabs">
        <?php foreach ([
            ['key'=>'dashboard','label'=>'Dashboard','icon'=>'chart-pie-2'],
            ['key'=>'summary','label'=>'Summary','icon'=>'table'],
            ['key'=>'pivot','label'=>'Pivot (Look Up)','icon'=>'list-tree'],
            ['key'=>'unique-patients','label'=>'Unique Patients','icon'=>'users'],
            ['key'=>'aging','label'=>'Aging','icon'=>'clock-hour-4'],
            ['key'=>'carriers','label'=>'Carriers','icon'=>'shield-check'],
            ['key'=>'cpt','label'=>'CPT Analysis','icon'=>'medical-cross'],
            ['key'=>'providers','label'=>'Providers','icon'=>'stethoscope'],
            ['key'=>'detail','label'=>'Detail (Raw)','icon'=>'database'],
            ['key'=>'compare','label'=>'Compare','icon'=>'git-compare'],
            ['key'=>'history','label'=>'History','icon'=>'history'],
        ] as $t): ?>
            <?php if ($t['key'] === 'detail' && $clientMode && !$clientRawOk) continue;   // hide raw-rows tab from clients without raw access ?>
            <li class="nav-item">
                <a class="nav-link <?= $tab === $t['key'] ? 'active' : '' ?>" href="<?= e(col_url(['tab'=>$t['key']])) ?>">
                    <i class="ti ti-<?= $t['icon'] ?> me-1"></i><?= $t['label'] ?>
                </a>
            </li>
        <?php endforeach; ?>
    </ul>

    <style>
    /* ===== Freeze first column on report scorecards (first row is already pinned via the
       sticky <thead>; this adds the matching left-column freeze so labels stay visible
       when scrolling horizontally). Scoped to tables tagged with .frz. ===== */
    .frz thead th:first-child { position: sticky; left: 0; z-index: 12; }
    .frz tbody td:first-child { position: sticky; left: 0; z-index: 4; }
    .frz thead th:first-child,
    .frz tbody td:first-child { box-shadow: 1px 0 0 #cbd5e1; }
    html[data-bs-theme="dark"] .frz thead th:first-child,
    html[data-bs-theme="dark"] .frz tbody td:first-child { box-shadow: 1px 0 0 #334155; }
    </style>

    <?php if ($tab === 'dashboard'): ?>
        <?php
        $sparkClaims = array_map(fn($x)=>$x['claims'], array_values($byMonth));
        $sparkCharge = array_map(fn($x)=>round($x['charge'],2), array_values($byMonth));
        $sparkPaid   = array_map(fn($x)=>round($x['pri']+$x['sec']+($x['ter']??0)+($x['pat_paid']??0),2), array_values($byMonth));
        $sparkBal    = array_map(fn($x)=>round($x['bal'],2), array_values($byMonth));
        ?>

        <!-- ============= INSIGHTS BANNER ============= -->
        <div class="rv-insights mb-3">
            <div class="d-flex flex-wrap align-items-center">
                <div class="insight-stat">
                    <div class="lbl"><i class="ti ti-bulb me-1"></i>Months covered</div>
                    <div class="val"><?= count($byMonth) ?> · <?= e(array_key_first($byMonth) ?: '--') ?> &rarr; <?= e(array_key_last($byMonth) ?: '--') ?></div>
                </div>
                <div class="insight-stat">
                    <div class="lbl" title="Paid claims &divide; claims, EXCLUDING recently-billed claims (Entry Date within the last 30 days). A fairer read of collections, since recently-billed claims have not had time to be paid.">Coll % &middot; excl. recent</div>
                    <div class="val"><?= number_format($collSplit['excl_pct'] ?? ($insights['collection_pct'] ?? 0), 1) ?>%<?php if (!empty($collSplit['mature_claims'])): ?> <small style="opacity:.6"><?= fmt_int($collSplit['mature_claims']) ?> cl</small><?php endif; ?></div>
                </div>
                <div class="insight-stat">
                    <div class="lbl" title="Paid claims &divide; claims, INCLUDING recently-billed claims — the overall collection rate.">Coll % &middot; incl. recent</div>
                    <div class="val"><?= number_format($collSplit['incl_pct'] ?? ($insights['collection_pct'] ?? 0), 1) ?>%
                        <span class="trend-<?= $insights['trend'] ?? 'flat' ?>"><?= ['up'=>'<i class="ti ti-trending-up"></i>','down'=>'<i class="ti ti-trending-down"></i>','flat'=>'<i class="ti ti-minus"></i>'][$insights['trend'] ?? 'flat'] ?></span>
                    </div>
                </div>
                <div class="insight-stat">
                    <div class="lbl">Total Balance</div>
                    <div class="val"><?= number_format($insights['outstanding_pct'] ?? 0, 1) ?>% of charge</div>
                </div>
                <div class="insight-stat">
                    <div class="lbl">Best month</div>
                    <div class="val"><?= e((string)($insights['best_month']['m'] ?? '--')) ?>
                        <small style="opacity:.7"><?= isset($insights['best_month']['v']) ? fmt_money_short($insights['best_month']['v']) : '' ?></small>
                    </div>
                </div>
                <div class="insight-stat">
                    <div class="lbl">Lowest month</div>
                    <div class="val"><?= e((string)($insights['worst_month']['m'] ?? '--')) ?>
                        <small style="opacity:.7"><?= isset($insights['worst_month']['v']) ? fmt_money_short($insights['worst_month']['v']) : '' ?></small>
                    </div>
                </div>
                <div class="insight-stat">
                    <div class="lbl">Top facility</div>
                    <div class="val"><?php $topF = $byFacility10 ? array_key_first($byFacility10) : null; echo $topF ? e(mb_substr($topF, 0, 28)) : '--'; ?></div>
                </div>
                <?php if ($deductibleReady): ?>
                <div class="insight-stat">
                    <div class="lbl" title="Visits flagged as deductible (uploaded on the Deductible page) and their total charge — from the Collection data. Honours the active filters.">Deductible</div>
                    <div class="val"><?= fmt_int($dedDash['n']) ?> <small style="opacity:.7"><?= fmt_money_short($dedDash['charge']) ?></small></div>
                </div>
                <?php endif; ?>
                <div class="ms-auto text-end small" style="opacity:.85">
                    <div><i class="ti ti-database me-1"></i><?= number_format($totals['claims']) ?> claims</div>
                    <?php if ($lastUpload): ?><div><i class="ti ti-clock me-1"></i><?= e(fmt_dt($lastUpload['completed_at'])) ?></div><?php endif; ?>
                </div>
            </div>
            <?php if (!empty($insights['anomalies'])): ?>
                <div class="mt-2 small">
                    <i class="ti ti-alert-triangle me-1 text-warning"></i><strong>Anomalies (>2σ):</strong>
                    <?php foreach ($insights['anomalies'] as $a): ?>
                        <span class="anomaly"><?= e($a['m']) ?> <?= $a['dir'] ?><?= fmt_money_short(abs($a['val'] - $insights['mean'])) ?></span>
                    <?php endforeach; ?>
                </div>
            <?php endif; ?>
        </div>

        <!-- ============= KPI STRIP ============= -->
        <div class="row row-cards kpi-strip mb-3">
            <div class="col"><div class="kpi"><div class="lbl">Claims</div><div class="val"><?= fmt_int($totals['claims']) ?></div><div class="spark" id="spark1"></div></div></div>
            <div class="col"><div class="kpi"><div class="lbl">Charge Amount</div><div class="val"><?= fmt_money_short($totals['charge']) ?></div><div class="spark" id="spark2"></div></div></div>
            <div class="col"><div class="kpi"><div class="lbl">Total Paid</div><div class="val"><?= fmt_money_short($totals['pri']+$totals['sec']+($totals['ter']??0)+($totals['pat_paid']??0)) ?></div><div class="spark" id="spark3"></div></div></div>
            <div class="col"><div class="kpi"><div class="lbl">Insurance Balance</div><div class="val text-warning"><?= fmt_money_short($totals['bal']) ?></div><div class="spark" id="spark4"></div></div></div>
            <div class="col"><div class="kpi"><div class="lbl">Patient Balance</div><div class="val"><?= fmt_money_short($totals['pat_bal']) ?></div></div></div>
            <div class="col"><div class="kpi"><div class="lbl">Avg Paid / Claim</div><div class="val"><?= $totals['paid']>0 ? fmt_money_short(($totals['pri']+$totals['sec']+($totals['ter']??0)+($totals['pat_paid']??0))/$totals['paid']) : '$0' ?></div><div class="form-hint mt-1" style="font-size:.62rem">paid &divide; paid claims</div></div></div>
        </div>

        <!-- ============= CHARTS ============= -->
        <div class="row row-deck row-cards mb-3">
            <div class="col-md-8">
                <div class="card">
                    <div class="card-header py-2"><h3 class="card-title small mb-0"><i class="ti ti-chart-line me-1 text-primary"></i>Monthly Revenue Trend</h3></div>
                    <div class="card-body p-2"><div id="chart_monthly" style="min-height:300px"></div></div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="card">
                    <div class="card-header py-2"><h3 class="card-title small mb-0"><i class="ti ti-chart-donut-2 me-1 text-success"></i>Revenue Breakdown</h3></div>
                    <div class="card-body p-2"><div id="chart_donut" style="min-height:300px"></div></div>
                </div>
            </div>
            <div class="col-12">
                <div class="card">
                    <div class="card-header py-2"><h3 class="card-title small mb-0"><i class="ti ti-chart-bar me-1 text-info"></i>Monthly Summary — All metrics</h3></div>
                    <div class="card-body p-2"><div id="chart_monthly_full" style="min-height:340px"></div></div>
                </div>
            </div>
            <div class="col-12">
                <div class="card">
                    <div class="card-header py-2"><h3 class="card-title small mb-0"><i class="ti ti-chart-bar me-1 text-warning"></i>Top 10 Facilities by Charge</h3></div>
                    <div class="card-body p-2"><div id="chart_top_fac" style="min-height:340px"></div></div>
                </div>
            </div>
        </div>

        <!-- ============= OVERALL REVENUE SUMMARY TABLE ============= -->
        <div class="legacy-title mb-0"><?= e(strtoupper($reportCtx['cname'])) ?> OVERALL REVENUE SUMMARY REPORT</div>
        <div class="card mb-3"><div class="table-responsive"><table class="table table-bordered table-legacy sortable mb-0">
            <thead><tr>
                <th>Month</th>
                <th>Total Claims Billed</th>
                <th colspan="2">Total Charge Amount</th>
                <th colspan="2">Fees Scheduled Projection</th>
                <th title="Claims with primary insurance paid">Total Paid Claims</th>
                <th colspan="2">Primary Insurance Paid</th>
                <th colspan="2">Secondary Insurance Paid</th>
                <th colspan="2" title="Tertiary insurance paid ($)">Tertiary Paid</th>
                <th colspan="2" title="Patient payment amount ($)">Patient Paid</th>
                <th colspan="2" title="Primary + Secondary + Tertiary + Patient paid">Total Paid</th>
                <th colspan="2" title="Fees Scheduled Projection − Total Paid">Expected to be Paid</th>
                <th colspan="2">Insurance Balance</th>
                <th colspan="2">Patient Balance</th>
            </tr></thead>
            <tbody>
            <?php foreach ($byMonth as $m => $v):
                $vTer       = (float)($v['ter'] ?? 0);
                $vPatPaid   = (float)($v['pat_paid'] ?? 0);
                $vTotalPaid = (float)$v['pri'] + (float)$v['sec'] + $vTer + $vPatPaid;
            ?>
                <tr>
                    <td class="text-center"><strong><?= e($m) ?></strong></td>
                    <td class="num"><?= fmt_int($v['claims']) ?></td>
                    <?= dollar_cells($v['charge']) ?>
                    <?= dollar_cells($v['expected']) ?>
                    <td class="num"><?= fmt_int($v['paid']) ?></td>
                    <?= dollar_cells($v['pri']) ?>
                    <?= dollar_cells($v['sec']) ?>
                    <?= dollar_cells($vTer) ?>
                    <?= dollar_cells($vPatPaid) ?>
                    <?= dollar_cells($vTotalPaid) ?>
                    <?= dollar_cells((float)$v['expected'] - $vTotalPaid) ?>
                    <?= dollar_cells($v['bal']) ?>
                    <?= dollar_cells($v['pat_bal']) ?>
                </tr>
            <?php endforeach;
            $gtTer        = (float)($totals['ter'] ?? 0);
            $gtPatPaid    = (float)($totals['pat_paid'] ?? 0);
            $gtTotalPaid  = (float)$totals['pri'] + (float)$totals['sec'] + $gtTer + $gtPatPaid;
            ?>
                <tr class="total-row">
                    <td class="text-center">TOTAL</td>
                    <td class="num"><?= fmt_int($totals['claims']) ?></td>
                    <?= dollar_cells($totals['charge']) ?>
                    <?= dollar_cells($totals['expected']) ?>
                    <td class="num"><?= fmt_int($totals['paid']) ?></td>
                    <?= dollar_cells($totals['pri']) ?>
                    <?= dollar_cells($totals['sec']) ?>
                    <?= dollar_cells($gtTer) ?>
                    <?= dollar_cells($gtPatPaid) ?>
                    <?= dollar_cells($gtTotalPaid) ?>
                    <?= dollar_cells((float)$totals['expected'] - $gtTotalPaid) ?>
                    <?= dollar_cells($totals['bal']) ?>
                    <?= dollar_cells($totals['pat_bal']) ?>
                </tr>
            </tbody>
        </table></div></div>

    <?php elseif ($tab === 'summary'):
        $maxCharge = 0; foreach ($byMonth as $v) $maxCharge = max($maxCharge, $v['charge']);
        $maxBal    = 0; foreach ($byMonth as $v) $maxBal    = max($maxBal,    $v['bal']);
    ?>
        <?php $recentSelected = in_array('recent', $fStatuses, true); ?>
        <?php if ($recentlyBilled && (int)$recentlyBilled['claims'] > 0): ?>
        <div class="alert <?= $recentSelected ? 'alert-warning' : 'alert-info' ?> d-flex flex-wrap align-items-center gap-3 mb-3">
            <span><i class="ti ti-clock-bolt me-1"></i><strong>Recently billed</strong> <span class="text-secondary small">(Entry Date on/after <?= e(fmt_date($recentlyBilled['since'])) ?>)</span>:</span>
            <span><strong><?= fmt_int($recentlyBilled['claims']) ?></strong> claims</span>
            <span class="text-secondary">|</span>
            <span>Charge <strong><?= fmt_money($recentlyBilled['charge']) ?></strong></span>
            <span>Total balance on recently billed: <strong class="text-warning"><?= fmt_money($recentlyBilled['outstanding']) ?></strong></span>
            <span class="ms-auto d-flex gap-2">
                <?php if ($recentSelected): ?>
                    <a href="<?= e(col_url(['status'=>null])) ?>" class="btn btn-sm btn-outline-secondary" title="Show all claims again"><i class="ti ti-x me-1"></i>Exit view</a>
                <?php else: ?>
                    <a href="<?= e(col_url(['status'=>'recent'])) ?>" class="btn btn-sm btn-primary" title="Filter the whole dashboard (this table + drill-down) to recently-billed claims"><i class="ti ti-table me-1"></i>View in table</a>
                <?php endif; ?>
                <?php if ($showExport): ?><a href="<?= e(url('/admin/collection.php?id='.$reportId.'&tab=summary&export=xlsx&which=recent')) ?>" class="btn btn-sm btn-success" title="Download Excel — Sheet 1: recently-billed summary, Sheet 2: every recently-billed claim"><i class="ti ti-file-spreadsheet me-1"></i>Download (Excel)</a><?php endif; ?>
            </span>
        </div>
        <?php elseif ($recentlyBilled && empty($recentlyBilled['latest_entry'])): ?>
        <div class="alert alert-secondary py-2 small mb-3"><i class="ti ti-info-circle me-1"></i><strong>Recently billed</strong> (by Entry Date) will populate once you upload a Facility Mapping that includes the <strong>Entry Date</strong> column.</div>
        <?php endif; ?>
        <?php
        // Deductible visits per month (keyed "year-month" to match $v['year'].'-'.$v['month']). The TOTAL
        // row sums ONLY the displayed (dos-dated) months — same domain as the "Total Claims" total, so a
        // null-DOS deductible visit can't make the total exceed the visible rows.
        $dedByMonth = $deductibleReady ? coll_deductible_by($pdo, "CONCAT(dos_year,'-',dos_month)", $whereSql, $params) : [];
        $dedMonthTot = 0;
        foreach ($byMonth as $v) $dedMonthTot += ($dedByMonth[$v['year'].'-'.$v['month']] ?? 0);
        ?>
        <div class="legacy-title mb-0"><?= e(strtoupper($reportCtx['cname'])) ?> ALL FACILITIES REVENUE AND BALANCES SUMMARY REPORT</div>
        <div class="card mb-3"><div class="table-responsive" style="max-height:760px;overflow:auto"><table class="table table-bordered table-legacy sortable frz mb-0">
            <thead style="position:sticky;top:0;z-index:5"><tr>
                <th>Month</th><th>Collection %</th><th>Total Claims</th><th title="Deductible visits (uploaded on the Deductible page)">Deductible</th><th>Total Paid Claims</th><th>Unpaid</th>
                <th>Charge Amount</th>
                <th>Fees Scheduled Projection</th>
                <th>Primary Paid</th><th>Secondary Paid</th>
                <th title="Tertiary insurance paid ($)">Tertiary Paid</th>
                <th title="Patient payment amount ($)">Patient Paid</th>
                <th title="Pri + Sec + Ter + Patient paid">Total Paid</th>
                <th title="Fees Scheduled Projection − Total Paid">Expected to be Paid</th>
                <th>$ per Paid Claim</th>
                <th title="Insurance paid divided by ALL claims (paid + unpaid) — the average collected across the whole book">$ per Claim (Overall)</th>
                <th title="Claims awaiting primary insurance">Pri Pending</th>
                <th title="Primary paid, awaiting secondary">Sec Pending</th>
                <th title="All claims with an open balance (insurance or patient)">O/S Claims</th>
                <th title="Total balance on primary-pending claims">Primary O/S</th>
                <th title="Total balance on secondary-pending claims">Secondary O/S</th>
                <th>Insurance Balance</th>
                <th>Patient Balance</th>
                <th title="Flagged when the month's total charge equals its total write-off amount">Write off</th>
            </tr></thead>
            <tbody>
            <?php foreach ($byMonth as $m => $v):
                $vTer        = (float)($v['ter'] ?? 0);
                $totPaid     = $v['pri'] + $v['sec'] + $vTer + (float)($v['pat_paid'] ?? 0);   // pri + sec + ter + patient
                $perPaid     = $v['paid']  > 0 ? $totPaid/$v['paid']   : 0;   // average across PAID claims only
                $perOverall  = $v['claims']> 0 ? $totPaid/$v['claims'] : 0;   // average across ALL claims (paid + unpaid)
                $colPct      = (int)$v['claims'] > 0 ? ((int)$v['paid']/(int)$v['claims'])*100 : 0;   // paid claims / total claims
                $chargeBarW = $maxCharge > 0 ? ($v['charge']/$maxCharge)*100 : 0;
                $balBarW    = $maxBal > 0    ? ($v['bal']/$maxBal)*100        : 0;
                $colColor   = $colPct >= 70 ? '#16a34a' : ($colPct >= 40 ? '#f59e0b' : '#dc2626');
            ?>
                <tr class="drill-parent" data-key="sm<?= (int)$v['year'] ?>_<?= (int)$v['month'] ?>" data-depth="0" data-c='<?= e(json_encode(['year'=>(int)$v['year'],'month'=>(int)$v['month']])) ?>' onclick="mDrillToggle(this)">
                    <td><i class="ti ti-chevron-right drill-arrow"></i> <strong><?= e($m) ?></strong></td>
                    <td class="num"><span style="color:<?= $colColor ?>;font-weight:600"><?= number_format($colPct, 1) ?>%</span></td>
                    <td class="num"><?= fmt_int($v['claims']) ?></td>
                    <td class="num"><?= fmt_int($dedByMonth[$v['year'].'-'.$v['month']] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($v['paid']) ?></td>
                    <td class="num"><?= fmt_int($v['unpaid']) ?></td>
                    <td class="num"><span class="databar"><span class="bar" style="width:<?= number_format($chargeBarW,1) ?>%;background:#206bc4"></span><span class="v"><?= fmt_money($v['charge']) ?></span></span></td>
                    <td class="num"><?= fmt_money($v['expected']) ?></td>
                    <td class="num"><?= fmt_money($v['pri']) ?></td>
                    <td class="num"><?= fmt_money($v['sec']) ?></td>
                    <td class="num"><?= fmt_money($vTer) ?></td>
                    <td class="num"><?= fmt_money($v['pat_paid'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money($totPaid) ?></td>
                    <td class="num"><?= fmt_money((float)$v['expected'] - $totPaid) ?></td>
                    <td class="num"><?= fmt_money($perPaid) ?></td>
                    <td class="num"><?= fmt_money($perOverall) ?></td>
                    <td class="num"><?= fmt_int($v['prim_pend']) ?></td>
                    <td class="num"><?= fmt_int($v['sec_pend']) ?></td>
                    <td class="num"><strong><?= fmt_int($v['outst_n']) ?></strong></td>
                    <td class="num"><?= fmt_money($v['prim_outst']) ?></td>
                    <td class="num"><?= fmt_money($v['sec_outst']) ?></td>
                    <td class="num"><span class="databar"><span class="bar" style="width:<?= number_format($balBarW,1) ?>%;background:#f76707"></span><span class="v"><?= fmt_money($v['bal']) ?></span></span></td>
                    <td class="num"><?= fmt_money($v['pat_bal']) ?></td>
                    <td><?php // "Write off" when this month's total charge equals its total write-off amount (both > 0)
                        $chg=(float)($v['charge']??0); $woAmt=(float)($v['wo']??0);
                        echo ($chg>0 && round($chg,2)===round($woAmt,2)) ? '<span class="badge bg-red-lt">Write off</span>' : ''; ?></td>
                </tr>
            <?php endforeach;
            $tTer         = (float)($totals['ter'] ?? 0);
            $tTotalPaid   = $totals['pri'] + $totals['sec'] + $tTer + (float)($totals['pat_paid'] ?? 0);
            $tPerPaid     = $totals['paid']  > 0 ? $tTotalPaid/$totals['paid']   : 0;
            $tPerOverall  = $totals['claims']> 0 ? $tTotalPaid/$totals['claims'] : 0;
            $tColPct      = (int)$totals['claims'] > 0 ? ((int)$totals['paid']/(int)$totals['claims'])*100 : 0;   // paid claims / total claims
            ?>
                <tr class="total-row">
                    <td>TOTAL</td>
                    <td class="num"><strong><?= number_format($tColPct, 1) ?>%</strong></td>
                    <td class="num"><?= fmt_int($totals['claims']) ?></td>
                    <td class="num"><strong><?= fmt_int($dedMonthTot) ?></strong></td>
                    <td class="num"><?= fmt_int($totals['paid']) ?></td>
                    <td class="num"><?= fmt_int($totals['unpaid']) ?></td>
                    <td class="num"><?= fmt_money($totals['charge']) ?></td>
                    <td class="num"><?= fmt_money($totals['expected']) ?></td>
                    <td class="num"><?= fmt_money($totals['pri']) ?></td>
                    <td class="num"><?= fmt_money($totals['sec']) ?></td>
                    <td class="num"><?= fmt_money($tTer) ?></td>
                    <td class="num"><?= fmt_money($totals['pat_paid'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money($tTotalPaid) ?></td>
                    <td class="num"><?= fmt_money((float)$totals['expected'] - $tTotalPaid) ?></td>
                    <td class="num"><?= fmt_money($tPerPaid) ?></td>
                    <td class="num"><?= fmt_money($tPerOverall) ?></td>
                    <td class="num"><?= fmt_int($totals['prim_pend']) ?></td>
                    <td class="num"><?= fmt_int($totals['sec_pend']) ?></td>
                    <td class="num"><strong><?= fmt_int($totals['outst_n']) ?></strong></td>
                    <td class="num"><?= fmt_money($totals['prim_outst']) ?></td>
                    <td class="num"><?= fmt_money($totals['sec_outst']) ?></td>
                    <td class="num"><?= fmt_money($totals['bal']) ?></td>
                    <td class="num"><?= fmt_money($totals['pat_bal']) ?></td>
                    <td><?php $tChg=(float)($totals['charge']??0); $tWo=(float)($totals['wo']??0);
                        echo ($tChg>0 && round($tChg,2)===round($tWo,2)) ? '<span class="badge bg-red-lt">Write off</span>' : ''; ?></td>
                </tr>
            </tbody>
        </table></div></div>
        <?php $df = drillFilterQS($fFacilities, $fProviders, $fYears, $fMonths, $fStatuses); ?>
        <script>
        window.MDRILL = { endpoint: <?= json_encode(url('/admin/collection-drill.php')) ?>, id: <?= (int) $reportId ?>,
            tab: 'summary', cols: 24, labelCol: 0, filters: <?= json_encode($df) ?>, money: 'single',
            map: { collpct: 1, claims: 2, paidn: 4, unpaidn: 5, charge: 6, expected: 7, pri: 8, sec: 9, ter: 10, patpaid: 11, inspaid: 12, exptopaid: 13, perpaid: 14, peroverall: 15, primpend: 16, secpend: 17, outstn: 18, primoutst: 19, secoutst: 20, insbal: 21, patbal: 22 },
            chain: ['facility'] };
        </script>
        <?php require __DIR__ . '/../includes/drill-js.php'; ?>
        <div class="small text-secondary mt-1 mb-2"><i class="ti ti-info-circle me-1"></i>Click a month to drill into its <strong>facilities</strong> (same columns).</div>

    <?php elseif ($tab === 'pivot'):
        ksort($pivot);
        // Empty rollup template shared by grand / facility / month accumulators.
        $pivZero = ['cl'=>0,'ded'=>0,'paid_n'=>0,'unpaid_n'=>0,'c'=>0,'ex'=>0,'p'=>0,'s'=>0,'t'=>0,'pp'=>0,'b'=>0,'pb'=>0,'prim_pend'=>0,'sec_pend'=>0,'outst_n'=>0,'prim_outst'=>0,'sec_outst'=>0];
        $pivAdd = function(array $a, array $b): array { foreach ($a as $k=>$_) $a[$k] += ($b[$k] ?? 0); return $a; };
        $gT = $pivZero;
        foreach ($pivot as $months) foreach ($months as $days) foreach ($days as $r) $gT = $pivAdd($gT, $r);
        // Cell builders shared across facility/month/day/grand rows.
        // Collection % = paid claims ÷ total claims
        $pivotPct = function(int $paidN, int $claims): string {
            if ($claims <= 0) return '<td class="num">—</td>';
            $p = $paidN / $claims * 100;
            $col = $p >= 70 ? '#16a34a' : ($p >= 40 ? '#f59e0b' : '#dc2626');
            return '<td class="num"><span style="color:'.$col.';font-weight:600">' . number_format($p, 1) . '%</span></td>';
        };
        // Render every metric column for one rollup row. Bold = $bold (used for headers/grand-total).
        $pivotMetricCells = function(array $r, bool $bold = false) use ($pivotPct): string {
            $rTer    = (float)($r['t'] ?? 0);
            $rPatP   = (float)($r['pp'] ?? 0);
            $totalPaid = (float)($r['p'] ?? 0) + (float)($r['s'] ?? 0) + $rTer + $rPatP;   // pri + sec + ter + patient
            $perPaid    = ($r['paid_n'] ?? 0) > 0 ? $totalPaid / $r['paid_n']    : 0;
            $perOverall = ($r['cl']     ?? 0) > 0 ? $totalPaid / $r['cl']        : 0;
            $b = $bold ? 'strong' : 'span';
            $intCell  = fn($v) => '<td class="num"><'.$b.'>'.fmt_int($v).'</'.$b.'></td>';
            $moneyCell= fn($v) => dollar_cells((float)$v);
            return $pivotPct((int)($r['paid_n'] ?? 0), (int)($r['cl'] ?? 0))
                 . $intCell((int)($r['cl']       ?? 0))
                 . $intCell((int)($r['ded']      ?? 0))
                 . $intCell((int)($r['paid_n']   ?? 0))
                 . $intCell((int)($r['unpaid_n'] ?? 0))
                 . $moneyCell($r['c']       ?? 0)
                 . $moneyCell($r['ex']      ?? 0)
                 . $moneyCell($r['p']       ?? 0)
                 . $moneyCell($r['s']       ?? 0)
                 . $moneyCell($rTer)
                 . $moneyCell($rPatP)
                 . $moneyCell($totalPaid)
                 . $moneyCell((float)($r['ex'] ?? 0) - $totalPaid)
                 . $moneyCell($perPaid)
                 . $moneyCell($perOverall)
                 . $intCell((int)($r['prim_pend'] ?? 0))
                 . $intCell((int)($r['sec_pend']  ?? 0))
                 . $intCell((int)($r['outst_n']   ?? 0))
                 . $moneyCell($r['prim_outst'] ?? 0)
                 . $moneyCell($r['sec_outst']  ?? 0)
                 . $moneyCell($r['b']  ?? 0)
                 . $moneyCell($r['pb'] ?? 0);
        };
    ?>
        <div class="legacy-title mb-0"><?= e(strtoupper($reportCtx['cname'])) ?> &mdash; LOOK UP DATA (Facility / Month / Day)</div>
        <div class="card mb-0">
            <div class="card-body py-2 d-flex flex-wrap gap-2 align-items-center" style="border-bottom:1px solid var(--m-border)">
                <input type="text" id="pivot_search" class="form-control form-control-sm" placeholder="Search facility name..." style="max-width:300px">
                <button class="btn btn-sm btn-outline-secondary" onclick="pivotExpandAll()"><i class="ti ti-square-plus me-1"></i>Expand all</button>
                <button class="btn btn-sm btn-outline-secondary" onclick="pivotCollapseAll()"><i class="ti ti-square-minus me-1"></i>Collapse all</button>
                <span class="ms-auto small text-secondary">Click any <strong>facility</strong> or <strong>month</strong> row to drill down</span>
            </div>
        </div>
        <div class="card mb-3"><div class="table-responsive" style="max-height:700px;overflow:auto">
        <table id="pivot_table" class="table table-sm table-bordered table-legacy ultra-compact frz mb-0" style="font-size:13px">
            <thead style="position:sticky;top:0;z-index:10"><tr>
                <th style="text-align:left;min-width:340px">Row Labels</th>
                <th title="Paid claims ÷ Total claims">Collection %</th>
                <th title="Distinct visit_id (claim) count">Total Claims</th>
                <th title="Deductible visits (uploaded on the Deductible page)">Deductible</th>
                <th title="Claims with primary insurance paid">Total Paid Claims</th>
                <th>Unpaid</th>
                <th colspan="2">Charge Amount</th>
                <th colspan="2">Fees Scheduled Projection</th>
                <th colspan="2">Primary Paid</th>
                <th colspan="2">Secondary Paid</th>
                <th colspan="2" title="Tertiary insurance paid ($)">Tertiary Paid</th>
                <th colspan="2" title="Patient payment amount ($)">Patient Paid</th>
                <th colspan="2" title="Pri + Sec + Ter + Patient paid">Total Paid</th>
                <th colspan="2" title="Fees Scheduled Projection − Total Paid">Expected to be Paid</th>
                <th colspan="2" title="Total paid ÷ paid claim count">$ per Paid Claim</th>
                <th colspan="2" title="Total paid ÷ total claim count">$ per Claim (Overall)</th>
                <th title="Claims with primary unpaid and any balance open">Pri Pending</th>
                <th title="Claims with primary paid but secondary unpaid and any balance open">Sec Pending</th>
                <th title="Distinct claims with any open balance">O/S Claims</th>
                <th colspan="2" title="Total balance ($) on primary-pending claims">Primary O/S</th>
                <th colspan="2" title="Total balance ($) on secondary-pending claims">Secondary O/S</th>
                <th colspan="2">Insurance Balance</th>
                <th colspan="2">Patient Balance</th>
            </tr></thead>
            <tbody>
            <?php foreach ($pivot as $facility => $months):
                $facKey = preg_replace('/[^a-z0-9]/i', '_', $facility);
                $fT = $pivZero;
                foreach ($months as $days) foreach ($days as $r) $fT = $pivAdd($fT, $r);
            ?>
                <tr class="pivot-fac pivot-facility pivot-row" data-key="<?= e($facKey) ?>" data-name="<?= e(strtolower($facility)) ?>" onclick="pivotToggle(this.dataset.key)">
                    <td><i class="ti ti-chevron-right pivot-arrow" id="arrow-<?= e($facKey) ?>"></i> <strong><?= e($facility) ?></strong></td>
                    <?= $pivotMetricCells($fT, true) ?>
                </tr>
                <?php foreach ($months as $m => $days):
                    $monKey = $facKey . '_' . preg_replace('/[^a-z0-9]/i', '_', $m);
                    $mT = $pivZero;
                    foreach ($days as $r) $mT = $pivAdd($mT, $r);
                ?>
                    <tr class="pivot-mon pivot-month pivot-row" data-key="<?= e($monKey) ?>" data-parent="<?= e($facKey) ?>" onclick="event.stopPropagation(); pivotToggle(this.dataset.key)">
                        <td style="padding-left:30px"><i class="ti ti-chevron-right pivot-arrow" id="arrow-<?= e($monKey) ?>"></i> <strong><?= e($m) ?></strong></td>
                        <?= $pivotMetricCells($mT, true) ?>
                    </tr>
                    <?php foreach ($days as $d => $r): ?>
                        <tr class="pivot-day pivot-row" data-parent="<?= e($monKey) ?>">
                            <td style="padding-left:60px"><?= e($d) ?></td>
                            <?= $pivotMetricCells($r, false) ?>
                        </tr>
                    <?php endforeach; endforeach; endforeach; ?>
                <tr class="pivot-grand total-row">
                    <td><strong>Grand Total</strong></td>
                    <?= $pivotMetricCells($gT, true) ?>
                </tr>
            </tbody>
        </table></div></div>
        <style>
        #pivot_table tr.pivot-mon, #pivot_table tr.pivot-day { display:none; }
        #pivot_table tr.pivot-mon.open, #pivot_table tr.pivot-day.open { display:table-row; }
        .pivot-arrow { display:inline-block; width:14px; transition: transform .15s; }
        .ultra-compact { font-size: 10.5px !important; line-height: 1.15; }
        .ultra-compact thead th { font-size: 9.5px !important; padding: 4px 5px !important; text-transform: uppercase; letter-spacing: 0.02em; }
        .ultra-compact tbody td { padding: 3px 5px !important; white-space: nowrap; }
        .ultra-compact tfoot td, .ultra-compact .total-row td, .ultra-compact .pivot-grand td { padding: 4px 5px !important; }
        /* Right-align money/numbers tighter */
        .ultra-compact .num, .ultra-compact td.num { font-variant-numeric: tabular-nums; }
        /* Truncate long row labels (facility/provider names) with ellipsis */
        .ultra-compact td:first-child, .ultra-compact th:first-child { max-width: 200px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }
        </style>
        <script>
        function pivotToggle(k) {
            const arrow = document.getElementById('arrow-' + k);
            const row = document.querySelector('#pivot_table tr.pivot-row[data-key="' + CSS.escape(k) + '"]');
            const open = row && row.classList.contains('expanded');
            const ch = document.querySelectorAll('#pivot_table tr[data-parent="' + CSS.escape(k) + '"]');
            if (open) {
                row.classList.remove('expanded');
                if (arrow) arrow.classList.replace('ti-chevron-down','ti-chevron-right');
                ch.forEach(el => { el.classList.remove('open'); if (el.classList.contains('expanded')) pivotToggle(el.dataset.key); });
            } else {
                if (row) row.classList.add('expanded');
                if (arrow) arrow.classList.replace('ti-chevron-right','ti-chevron-down');
                ch.forEach(el => el.classList.add('open'));
            }
        }
        function pivotExpandAll()  { document.querySelectorAll('#pivot_table tr.pivot-fac, #pivot_table tr.pivot-mon').forEach(tr => { if (!tr.classList.contains('expanded')) pivotToggle(tr.dataset.key); }); }
        function pivotCollapseAll(){ document.querySelectorAll('#pivot_table tr.pivot-mon.expanded').forEach(tr => pivotToggle(tr.dataset.key)); document.querySelectorAll('#pivot_table tr.pivot-fac.expanded').forEach(tr => pivotToggle(tr.dataset.key)); }
        document.getElementById('pivot_search').addEventListener('input', function() {
            const q = this.value.toLowerCase().trim();
            document.querySelectorAll('#pivot_table tr.pivot-fac').forEach(tr => {
                const ok = !q || tr.dataset.name.includes(q);
                tr.style.display = ok ? '' : 'none';
                if (!ok && tr.classList.contains('expanded')) pivotToggle(tr.dataset.key);
            });
        });
        </script>

    <?php elseif ($tab === 'unique-patients'):
        // Build the nested provider/month structure indexed by facility
        $provByFac = [];
        foreach ($uniqFacProv as $r) { $provByFac[$r['facility']][$r['provider']] = $r; }
        $monByFacProv = [];
        foreach ($uniqFacProvMon as $r) { $monByFacProv[$r['facility']][$r['provider']][] = $r; }
    ?>
        <div class="legacy-title mb-0"><?= e(strtoupper($reportCtx['cname'])) ?> — UNIQUE PATIENT COUNT BY FACILITY, PROVIDER &amp; MONTH</div>
        <div class="card mb-0">
            <div class="card-body py-2 d-flex flex-wrap gap-2 align-items-center" style="border-bottom:1px solid var(--m-border)">
                <input type="text" id="up_search" class="form-control form-control-sm" placeholder="Search facility or provider..." style="max-width:320px">
                <button class="btn btn-sm btn-outline-secondary" onclick="upExpandAll()"><i class="ti ti-square-plus me-1"></i>Expand all</button>
                <button class="btn btn-sm btn-outline-secondary" onclick="upCollapseAll()"><i class="ti ti-square-minus me-1"></i>Collapse all</button>
                <span class="ms-auto small text-secondary">
                    <strong><?= number_format($uniqGrandTotal['uniq_pt']) ?></strong> unique patients ·
                    <strong><?= number_format($uniqGrandTotal['visits']) ?></strong> visits ·
                    <strong><?= fmt_money_short($uniqGrandTotal['charge']) ?></strong> charge
                </span>
            </div>
        </div>
        <?php
            // Collection % = paid claims ÷ total claims.
            // Older cache rows may lack paid_n — show "—" rather than a misleading 0%.
            $upPct = function($paidN, $claims): string {
                if ($paidN === null || !is_numeric($paidN)) return '—';
                $c = (int)$claims;
                if ($c <= 0) return '—';
                $p = (int)$paidN / $c * 100;
                $col = $p >= 70 ? '#16a34a' : ($p >= 40 ? '#f59e0b' : '#dc2626');
                return '<span style="color:'.$col.';font-weight:600">' . number_format($p, 1) . '%</span>';
            };
            // Per-facility top carrier — falls back to the highest-charge carrier from
            // the provider rows when the new $facTopCarrierMap isn't populated (e.g.
            // when reading from an older cache that doesn't carry it).
            $facTopCarrier = $facTopCarrierMap ?? [];
            if (!$facTopCarrier) {
                $tmpFac = [];
                foreach ($uniqFacProv as $p) {
                    $f = $p['facility'];
                    if (!isset($tmpFac[$f]) || (float)$p['charge'] > $tmpFac[$f]['charge']) {
                        $tmpFac[$f] = ['carrier' => $p['top_carrier'] ?? '—', 'charge' => (float)$p['charge']];
                    }
                }
                foreach ($tmpFac as $f => $row) $facTopCarrier[$f] = $row['carrier'];
            }
            // Deductible visits per facility (shown at the facility level only — like "Days as Patient",
            // sub-rows show "—"). Keyed to COALESCE(facility,'(unmapped)') to match $f['facility'].
            $dedUpFac = $deductibleReady ? coll_deductible_by($pdo, "COALESCE(facility,'(unmapped)')", $whereSql, $params) : [];
            $dedUpTot = $deductibleReady ? coll_deductible_total($pdo, $whereSql, $params)['n'] : 0;
        ?>
        <div class="card mb-3"><div class="table-responsive" style="max-height:760px; overflow:auto">
            <table id="up_table" class="table table-sm table-bordered table-legacy ultra-compact frz mb-0" style="font-size:13px">
                <thead style="position:sticky; top:0; z-index:10"><tr>
                    <th style="text-align:left; min-width:340px">Facility / Provider / Month</th>
                    <th class="text-end" title="Paid claims ÷ Total claims">Collection %</th>
                    <th class="text-end">Unique Patients</th>
                    <th class="text-end">Visits</th>
                    <th class="text-end" title="Deductible visits (uploaded on the Deductible page) — shown at the facility level">Deductible</th>
                    <th class="text-end">Visits / Patient</th>
                    <th class="text-end">Charge</th>
                    <th class="text-end">Primary Paid</th>
                    <th class="text-end">Secondary Paid</th>
                    <th class="text-end">Tertiary Paid</th>
                    <th class="text-end">Patient Paid</th>
                    <th class="text-end">Total Paid</th>
                    <th class="text-end">Insurance Balance</th>
                    <th class="text-end">Patient Balance</th>
                    <th class="text-end" title="Avg days between a patient's first and last DOS in this bucket">Days as Patient</th>
                    <th class="text-end" title="Carrier with the highest total charge in this bucket">Top Carrier</th>
                </tr></thead>
                <tbody>
                <?php foreach ($uniqFac as $f):
                    $facKey = preg_replace('/[^a-z0-9]/i', '_', $f['facility']);
                    $facName = $f['facility'];
                    $facVisitsPerPt = $f['uniq_pt'] > 0 ? $f['visits'] / $f['uniq_pt'] : 0;
                    $facPaidN = isset($f['paid_n']) ? (int)$f['paid_n'] : null;   // null = cache lacks this field
                    $facDays = $f['days_span'] ?? null;
                    $facTop = $facTopCarrier[$facName] ?? '—';
                ?>
                    <tr class="up-fac up-row pivot-facility" data-key="<?= e($facKey) ?>" data-name="<?= e(strtolower($facName)) ?>" onclick="upToggle(this.dataset.key)">
                        <td>
                            <i class="ti ti-chevron-right pivot-arrow" id="up-arrow-<?= e($facKey) ?>"></i>
                            <strong><?= e($facName) ?></strong>
                            <span class="badge bg-blue-lt text-blue ms-2"><?= (int)($f['providers'] ?? 0) ?> providers</span>
                        </td>
                        <td class="num text-end"><?= $upPct($facPaidN, (int)$f['visits']) ?></td>
                        <td class="num text-end"><strong><?= number_format((int)$f['uniq_pt']) ?></strong></td>
                        <td class="num text-end"><?= number_format((int)$f['visits']) ?></td>
                        <td class="num text-end"><strong><?= fmt_int($dedUpFac[$facName] ?? 0) ?></strong></td>
                        <td class="num text-end"><?= number_format($facVisitsPerPt, 1) ?></td>
                        <td class="num text-end"><?= fmt_money($f['charge']) ?></td>
                        <td class="num text-end"><?= fmt_money($f['pri'] ?? 0) ?></td>
                        <td class="num text-end"><?= fmt_money($f['sec'] ?? 0) ?></td>
                        <td class="num text-end"><?= fmt_money($f['ter'] ?? 0) ?></td>
                        <td class="num text-end"><?= fmt_money($f['pat_paid'] ?? 0) ?></td>
                        <td class="num text-end"><?= fmt_money($f['total_paid'] ?? $f['paid']) ?></td>
                        <td class="num text-end"><?= fmt_money($f['bal']) ?></td>
                        <td class="num text-end"><?= fmt_money($f['pat_bal'] ?? 0) ?></td>
                        <td class="num text-end"><?= $facDays !== null ? number_format((float)$facDays, 0) . ' d' : '—' ?></td>
                        <td class="text-truncate" style="max-width:180px;text-align:left" title="<?= e($facTop) ?>"><?= e($facTop) ?></td>
                    </tr>
                    <?php foreach (($provByFac[$facName] ?? []) as $provName => $p):
                        $provKey = $facKey . '__' . preg_replace('/[^a-z0-9]/i','_', $provName);
                        $provVisitsPerPt = $p['uniq_pt'] > 0 ? $p['visits'] / $p['uniq_pt'] : 0;
                        $provPaidN = isset($p['paid_n']) ? (int)$p['paid_n'] : null;
                        $provTop  = $p['top_carrier'] ?? '—';
                    ?>
                        <tr class="up-prov up-row pivot-month" data-key="<?= e($provKey) ?>" data-parent="<?= e($facKey) ?>"
                            data-name="<?= e(strtolower($facName . ' ' . $provName)) ?>"
                            onclick="event.stopPropagation(); upToggle(this.dataset.key)">
                            <td style="padding-left:34px">
                                <i class="ti ti-chevron-right pivot-arrow" id="up-arrow-<?= e($provKey) ?>"></i>
                                <strong><?= e($provName) ?></strong>
                            </td>
                            <td class="num text-end"><?= $upPct($provPaidN, (int)$p['visits']) ?></td>
                            <td class="num text-end"><strong><?= number_format((int)$p['uniq_pt']) ?></strong></td>
                            <td class="num text-end"><?= number_format((int)$p['visits']) ?></td>
                            <td class="num text-end">—</td>
                            <td class="num text-end"><?= number_format($provVisitsPerPt, 1) ?></td>
                            <td class="num text-end"><?= fmt_money($p['charge']) ?></td>
                            <td class="num text-end"><?= fmt_money($p['pri'] ?? 0) ?></td>
                            <td class="num text-end"><?= fmt_money($p['sec'] ?? 0) ?></td>
                            <td class="num text-end"><?= fmt_money($p['ter'] ?? 0) ?></td>
                            <td class="num text-end"><?= fmt_money($p['pat_paid'] ?? 0) ?></td>
                            <td class="num text-end"><?= fmt_money($p['total_paid'] ?? $p['paid']) ?></td>
                            <td class="num text-end"><?= fmt_money($p['bal']) ?></td>
                            <td class="num text-end"><?= fmt_money($p['pat_bal'] ?? 0) ?></td>
                            <td class="num text-end">—</td>
                            <td class="text-truncate" style="max-width:180px;text-align:left" title="<?= e($provTop) ?>"><?= e($provTop) ?></td>
                        </tr>
                        <?php foreach (($monByFacProv[$facName][$provName] ?? []) as $mr):
                            $mLabel = $monthNames[(int)$mr['dos_month']] . " " . substr((string)$mr['dos_year'], -2);
                            $mvpp = $mr['uniq_pt'] > 0 ? $mr['visits'] / $mr['uniq_pt'] : 0;
                            $mPaidN = isset($mr['paid_n']) ? (int)$mr['paid_n'] : null;
                        ?>
                            <tr class="up-mon up-row pivot-day" data-parent="<?= e($provKey) ?>">
                                <td style="padding-left:70px"><?= e($mLabel) ?></td>
                                <td class="num text-end"><?= $upPct($mPaidN, (int)$mr['visits']) ?></td>
                                <td class="num text-end"><?= number_format((int)$mr['uniq_pt']) ?></td>
                                <td class="num text-end"><?= number_format((int)$mr['visits']) ?></td>
                                <td class="num text-end">—</td>
                                <td class="num text-end"><?= number_format($mvpp, 1) ?></td>
                                <td class="num text-end"><?= fmt_money($mr['charge']) ?></td>
                                <td class="num text-end"><?= fmt_money($mr['pri'] ?? 0) ?></td>
                                <td class="num text-end"><?= fmt_money($mr['sec'] ?? 0) ?></td>
                                <td class="num text-end"><?= fmt_money($mr['ter'] ?? 0) ?></td>
                                <td class="num text-end"><?= fmt_money($mr['pat_paid'] ?? 0) ?></td>
                                <td class="num text-end"><?= fmt_money($mr['total_paid'] ?? $mr['paid']) ?></td>
                                <td class="num text-end"><?= fmt_money($mr['bal']) ?></td>
                                <td class="num text-end"><?= fmt_money($mr['pat_bal'] ?? 0) ?></td>
                                <td class="num text-end">—</td>
                                <td class="num text-end">—</td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endforeach; ?>
                <?php endforeach; ?>
                <tr class="total-row">
                    <td><strong>Grand Total</strong></td>
                    <td class="num text-end"><?= $upPct($uniqGrandTotal['paid_n'] ?? null, (int)$uniqGrandTotal['visits']) ?></td>
                    <td class="num text-end"><strong><?= number_format($uniqGrandTotal['uniq_pt']) ?></strong></td>
                    <td class="num text-end"><?= number_format($uniqGrandTotal['visits']) ?></td>
                    <td class="num text-end"><strong><?= fmt_int($dedUpTot) ?></strong></td>
                    <td class="num text-end"><?= $uniqGrandTotal['uniq_pt'] > 0 ? number_format($uniqGrandTotal['visits']/$uniqGrandTotal['uniq_pt'], 1) : '—' ?></td>
                    <td class="num text-end"><?= fmt_money($uniqGrandTotal['charge']) ?></td>
                    <td class="num text-end"><?= fmt_money($uniqGrandTotal['pri'] ?? 0) ?></td>
                    <td class="num text-end"><?= fmt_money($uniqGrandTotal['sec'] ?? 0) ?></td>
                    <td class="num text-end"><?= fmt_money($uniqGrandTotal['ter'] ?? 0) ?></td>
                    <td class="num text-end"><?= fmt_money($uniqGrandTotal['pat_paid'] ?? 0) ?></td>
                    <td class="num text-end"><?= fmt_money($uniqGrandTotal['total_paid'] ?? $uniqGrandTotal['paid']) ?></td>
                    <td class="num text-end"><?= fmt_money($uniqGrandTotal['bal']) ?></td>
                    <td class="num text-end"><?= fmt_money($uniqGrandTotal['pat_bal'] ?? 0) ?></td>
                    <td class="num text-end"><?= isset($uniqGrandTotal['days_span']) && $uniqGrandTotal['days_span'] !== null ? number_format((float)$uniqGrandTotal['days_span'], 0) . ' d' : '—' ?></td>
                    <td class="num text-end">—</td>
                </tr>
                </tbody>
            </table>
        </div></div>

        <style>
        #up_table tr.up-prov, #up_table tr.up-mon { display: none; }
        #up_table tr.up-prov.open, #up_table tr.up-mon.open { display: table-row; }
        .ultra-compact { font-size: 10.5px !important; line-height: 1.15; }
        .ultra-compact thead th { font-size: 9.5px !important; padding: 4px 5px !important; text-transform: uppercase; letter-spacing: 0.02em; }
        .ultra-compact tbody td { padding: 3px 5px !important; white-space: nowrap; }
        .ultra-compact tfoot td, .ultra-compact .total-row td, .ultra-compact .pivot-grand td { padding: 4px 5px !important; }
        /* Right-align money/numbers tighter */
        .ultra-compact .num, .ultra-compact td.num { font-variant-numeric: tabular-nums; }
        /* Truncate long row labels (facility/provider names) with ellipsis */
        .ultra-compact td:first-child, .ultra-compact th:first-child { max-width: 200px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }
        </style>
        <script>
        function upToggle(k) {
            const arrow = document.getElementById('up-arrow-' + k);
            const row = document.querySelector('#up_table tr.up-row[data-key="' + CSS.escape(k) + '"]');
            const open = row && row.classList.contains('expanded');
            const ch = document.querySelectorAll('#up_table tr[data-parent="' + CSS.escape(k) + '"]');
            if (open) {
                row.classList.remove('expanded');
                if (arrow) arrow.classList.replace('ti-chevron-down','ti-chevron-right');
                ch.forEach(el => { el.classList.remove('open'); if (el.classList.contains('expanded')) upToggle(el.dataset.key); });
            } else {
                if (row) row.classList.add('expanded');
                if (arrow) arrow.classList.replace('ti-chevron-right','ti-chevron-down');
                ch.forEach(el => el.classList.add('open'));
            }
        }
        function upExpandAll() {
            document.querySelectorAll('#up_table tr.up-fac, #up_table tr.up-prov').forEach(tr => {
                if (!tr.classList.contains('expanded')) upToggle(tr.dataset.key);
            });
        }
        function upCollapseAll() {
            document.querySelectorAll('#up_table tr.up-prov.expanded').forEach(tr => upToggle(tr.dataset.key));
            document.querySelectorAll('#up_table tr.up-fac.expanded').forEach(tr => upToggle(tr.dataset.key));
        }
        document.getElementById('up_search').addEventListener('input', function() {
            const q = this.value.toLowerCase().trim();
            document.querySelectorAll('#up_table tr.up-fac').forEach(tr => {
                const facMatch = !q || tr.dataset.name.includes(q);
                const provs = document.querySelectorAll('#up_table tr.up-prov[data-parent="' + CSS.escape(tr.dataset.key) + '"]');
                let provMatch = false;
                provs.forEach(p => { if (q && p.dataset.name.includes(q)) provMatch = true; });
                const show = !q || facMatch || provMatch;
                tr.style.display = show ? '' : 'none';
                if (!show && tr.classList.contains('expanded')) upToggle(tr.dataset.key);
                // When matching by provider, expand the facility and hide non-matching providers
                if (q && provMatch && !facMatch) {
                    if (!tr.classList.contains('expanded')) upToggle(tr.dataset.key);
                    provs.forEach(p => {
                        const ok = p.dataset.name.includes(q);
                        p.style.display = ok ? '' : 'none';
                    });
                } else {
                    provs.forEach(p => { p.style.display = ''; });
                }
            });
        });
        </script>

    <?php elseif ($tab === 'aging'):
        $bucketColors = ['0-30'=>'#16a34a','31-60'=>'#84cc16','61-90'=>'#f59e0b','91-120'=>'#f97316','121+'=>'#dc2626'];
        $balOver90 = 0; $balUnder30 = 0; $maxBucketBal = 0; $worstBucket = '0-30';
        foreach ($agingBuckets as $b) {
            // Total balance per bucket = insurance + patient
            $bal = (float)$b['bal'] + (float)($b['pat_bal'] ?? 0);
            if (in_array($b['bucket'], ['91-120','121+'])) $balOver90 += $bal;
            if ($b['bucket'] === '0-30') $balUnder30 += $bal;
            if ($bal > $maxBucketBal) { $maxBucketBal = $bal; $worstBucket = $b['bucket']; }
        }
        $pctOver90 = $agingTotalBal > 0 ? ($balOver90 / $agingTotalBal) * 100 : 0;
        $pctUnder30 = $agingTotalBal > 0 ? ($balUnder30 / $agingTotalBal) * 100 : 0;
        // Top stale carrier (90+ days) — total balance (ins + pat)
        $worstCarrier = null; $worstCarrierBal = 0;
        foreach ($agingByCarrier as $r) {
            $rBal = (float)$r['bal'] + (float)($r['pat_bal'] ?? 0);
            if (in_array($r['bucket'], ['91-120','121+']) && $rBal > $worstCarrierBal) {
                $worstCarrierBal = $rBal; $worstCarrier = $r['carrier'];
            }
        }
    ?>
        <div class="rv-insights mb-3">
            <div class="d-flex flex-wrap align-items-center">
                <div class="insight-stat">
                    <div class="lbl"><i class="ti ti-bulb me-1"></i>Total open A/R (ins + pat)</div>
                    <div class="val"><?= fmt_money_short($agingTotalBal) ?></div>
                </div>
                <div class="insight-stat">
                    <div class="lbl">Healthy (0-30 days)</div>
                    <div class="val text-success"><?= number_format($pctUnder30, 1) ?>% <small style="opacity:.7"><?= fmt_money_short($balUnder30) ?></small></div>
                </div>
                <div class="insight-stat">
                    <div class="lbl">At risk (90+ days)</div>
                    <div class="val <?= $pctOver90 > 25 ? 'text-warning' : '' ?>"><?= number_format($pctOver90, 1) ?>% <small style="opacity:.7"><?= fmt_money_short($balOver90) ?></small></div>
                </div>
                <div class="insight-stat">
                    <div class="lbl">Largest bucket</div>
                    <div class="val"><?= e($worstBucket) ?> <small style="opacity:.7"><?= fmt_money_short($maxBucketBal) ?></small></div>
                </div>
                <?php if ($worstCarrier): ?>
                <div class="insight-stat">
                    <div class="lbl">Top stale carrier (90+)</div>
                    <div class="val" style="font-size:.95rem"><?= e(mb_substr($worstCarrier, 0, 28)) ?> <small style="opacity:.7"><?= fmt_money_short($worstCarrierBal) ?></small></div>
                </div>
                <?php endif; ?>
            </div>
        </div>

        <?php
        // Deductible visits per aging bucket (open-balance + dos-dated domain, deductible=1). The TOTAL
        // sums the per-bucket counts, matching how this tab totals "Claims".
        $dedByBucket = $deductibleReady ? coll_deductible_by_aging($pdo, $whereSql, $params) : [];
        $dedBucketTot = array_sum($dedByBucket);
        ?>
        <div class="legacy-title mb-0">A/R AGING — BUCKETS BY DAYS SINCE DATE OF SERVICE</div>
        <div class="card mb-3"><div class="table-responsive"><table class="table table-bordered table-legacy sortable mb-0">
            <thead><tr>
                <th>Aging Bucket</th><th>Claims</th>
                <th title="Deductible visits (uploaded on the Deductible page) with an open balance in this bucket">Deductible</th>
                <th title="Claims with primary insurance paid">Total Paid Claims</th>
                <th colspan="2">Charge</th>
                <th colspan="2" title="Fees Scheduled Projection — sum of expected_payment for claims in this bucket">Fees Scheduled Projection</th>
                <th colspan="2" title="Primary insurance paid ($)">Primary Paid</th>
                <th colspan="2" title="Secondary insurance paid ($)">Secondary Paid</th>
                <th colspan="2" title="Tertiary insurance paid ($)">Tertiary Paid</th>
                <th colspan="2" title="Patient payment amount ($)">Patient Paid</th>
                <th colspan="2" title="Pri + Sec + Ter + Patient payments">Total Paid</th>
                <th colspan="2" title="Fees Scheduled Projection − Total Paid">Expected to be Paid</th>
                <th colspan="2">Insurance Balance</th>
                <th colspan="2">Patient Balance</th>
                <th>% of Total A/R</th>
            </tr></thead>
            <tbody>
            <?php foreach (['0-30','31-60','61-90','91-120','121+'] as $bk):
                $b = null; foreach ($agingBuckets as $r) if ($r['bucket']===$bk) { $b = $r; break; }
                if (!$b) $b = ['claims'=>0,'paid_n'=>0,'charge'=>0,'expected'=>0,'bal'=>0,'pat_bal'=>0,'pri'=>0,'sec'=>0,'ter'=>0,'pat_paid'=>0,'total_paid'=>0];
                $bucketTotal = (float)$b['bal'] + (float)($b['pat_bal'] ?? 0);
                $pctBal = $agingTotalBal > 0 ? ($bucketTotal/$agingTotalBal)*100 : 0;
            ?>
                <tr class="drill-parent" data-key="ag<?= preg_replace('/[^a-z0-9]/i','',$bk) ?>" data-depth="0" data-c='<?= e(json_encode(['bucket'=>$bk])) ?>' onclick="mDrillToggle(this)">
                    <td class="text-center"><i class="ti ti-chevron-right drill-arrow"></i> <strong style="color:<?= $bucketColors[$bk] ?>"><?= $bk ?> days</strong></td>
                    <td class="num"><?= fmt_int($b['claims']) ?></td>
                    <td class="num"><?= fmt_int($dedByBucket[$bk] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($b['paid_n'] ?? 0) ?></td>
                    <?= dollar_cells($b['charge']) ?>
                    <?= dollar_cells($b['expected']) ?>
                    <?= dollar_cells($b['pri'] ?? 0) ?>
                    <?= dollar_cells($b['sec'] ?? 0) ?>
                    <?= dollar_cells($b['ter'] ?? 0) ?>
                    <?= dollar_cells($b['pat_paid'] ?? 0) ?>
                    <?= dollar_cells($b['total_paid'] ?? 0) ?>
                    <?= dollar_cells((float)$b['expected'] - (float)($b['total_paid'] ?? 0)) ?>
                    <?= dollar_cells($b['bal']) ?>
                    <?= dollar_cells($b['pat_bal'] ?? 0) ?>
                    <td class="num"><span class="databar"><span class="bar" style="width:<?= number_format($pctBal,1) ?>%;background:<?= $bucketColors[$bk] ?>"></span><span class="v"><?= number_format($pctBal, 1) ?>%</span></span></td>
                </tr>
            <?php endforeach; ?>
                <tr class="total-row">
                    <td class="text-center">TOTAL</td>
                    <td class="num"><?= fmt_int(array_sum(array_column($agingBuckets, 'claims'))) ?></td>
                    <td class="num"><strong><?= fmt_int($dedBucketTot) ?></strong></td>
                    <td class="num"><?= fmt_int(array_sum(array_column($agingBuckets, 'paid_n'))) ?></td>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'charge'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'expected'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'pri'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'sec'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'ter'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'pat_paid'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'total_paid'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'expected')) - array_sum(array_column($agingBuckets, 'total_paid'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'bal'))) ?>
                    <?= dollar_cells(array_sum(array_column($agingBuckets, 'pat_bal'))) ?>
                    <td class="num"><strong>100.0%</strong></td>
                </tr>
            </tbody>
        </table></div></div>
        <?php $df = drillFilterQS($fFacilities, $fProviders, $fYears, $fMonths, $fStatuses); ?>
        <script>
        window.MDRILL = { endpoint: <?= json_encode(url('/admin/collection-drill.php')) ?>, id: <?= (int) $reportId ?>,
            tab: 'aging', cols: 25, labelCol: 0, filters: <?= json_encode($df) ?>, money: 'split',
            map: { claims: 1, paidn: 3, charge: 4, expected: 6, pri: 8, sec: 10, ter: 12, patpaid: 14, totalpaid: 16, exptopaid: 18, insbal: 20, patbal: 22 },
            chain: ['year', 'month'] };
        </script>
        <?php require __DIR__ . '/../includes/drill-js.php'; ?>
        <div class="small text-secondary mt-1 mb-2"><i class="ti ti-info-circle me-1"></i>Click an aging bucket to drill <strong>Bucket &rsaquo; Year &rsaquo; Month</strong>.</div>

        <div class="row g-3">
            <div class="col-md-7">
                <div class="legacy-title mb-0" style="font-size:.85rem">Aging by Carrier — Total Balance (Ins + Pat) per Bucket</div>
                <div class="table-responsive" style="max-height:520px;overflow:auto">
                <table class="table table-sm table-bordered table-legacy sortable mb-0">
                    <thead style="position:sticky;top:0;z-index:5"><tr>
                        <th style="text-align:left;min-width:200px">Carrier</th>
                        <?php foreach (['0-30','31-60','61-90','91-120','121+'] as $bk): ?>
                            <th style="background:<?= $bucketColors[$bk] ?>;color:#fff"><?= $bk ?></th>
                        <?php endforeach; ?>
                        <th>Total</th>
                    </tr></thead>
                    <tbody>
                    <?php
                    $carrierMap = [];
                    foreach ($agingByCarrier as $r) $carrierMap[$r['carrier']][$r['bucket']] = (float)$r['bal'] + (float)($r['pat_bal'] ?? 0);
                    $carriers = array_keys($carrierMap);
                    usort($carriers, fn($a,$b) => array_sum($carrierMap[$b]) <=> array_sum($carrierMap[$a]));
                    foreach (array_slice($carriers, 0, 20) as $c):
                        $totalC = array_sum($carrierMap[$c]); ?>
                        <tr>
                            <td class="text-truncate" style="max-width:260px" title="<?= e($c) ?>"><?= e($c) ?></td>
                            <?php foreach (['0-30','31-60','61-90','91-120','121+'] as $bk):
                                $v = $carrierMap[$c][$bk] ?? 0; ?>
                                <td class="num"><?= $v > 0 ? fmt_money_short($v) : '—' ?></td>
                            <?php endforeach; ?>
                            <td class="num"><strong><?= fmt_money_short($totalC) ?></strong></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table></div>
            </div>
            <div class="col-md-5">
                <div class="legacy-title mb-0" style="font-size:.85rem">Aging by Facility — Top 15 by Total Balance (Ins + Pat)</div>
                <div class="table-responsive" style="max-height:520px;overflow:auto">
                <table class="table table-sm table-bordered table-legacy sortable mb-0">
                    <thead style="position:sticky;top:0;z-index:5"><tr>
                        <th style="text-align:left;min-width:200px">Facility</th>
                        <th>0-30</th><th>31-90</th><th>90+</th><th>Total</th>
                    </tr></thead>
                    <tbody>
                    <?php
                    $facMap = [];
                    foreach ($agingByFacility as $r) $facMap[$r['facility']][$r['bucket']] = (float)$r['bal'] + (float)($r['pat_bal'] ?? 0);
                    $facs = array_keys($facMap);
                    usort($facs, fn($a,$b) => array_sum($facMap[$b]) <=> array_sum($facMap[$a]));
                    foreach (array_slice($facs, 0, 15) as $f):
                        $u30 = $facMap[$f]['0-30'] ?? 0;
                        $u90 = ($facMap[$f]['31-60'] ?? 0) + ($facMap[$f]['61-90'] ?? 0);
                        $o90 = ($facMap[$f]['91-120'] ?? 0) + ($facMap[$f]['121+'] ?? 0);
                        $tot = $u30 + $u90 + $o90; ?>
                        <tr>
                            <td class="text-truncate" style="max-width:240px" title="<?= e($f) ?>"><?= e(mb_substr($f, 0, 32)) ?></td>
                            <td class="num"><?= $u30>0 ? fmt_money_short($u30) : '—' ?></td>
                            <td class="num"><?= $u90>0 ? fmt_money_short($u90) : '—' ?></td>
                            <td class="num"><?= $o90>0 ? '<span style="color:#dc2626;font-weight:600">'.fmt_money_short($o90).'</span>' : '—' ?></td>
                            <td class="num"><strong><?= fmt_money_short($tot) ?></strong></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table></div>
            </div>
        </div>

    <?php elseif ($tab === 'carriers'):
        $totalCharge = array_sum(array_map(fn($r) => (float)$r['charge'], $carrierPerf));
        $totalExp    = array_sum(array_map(fn($r) => (float)$r['expected'], $carrierPerf));
        $totalInsPaid= array_sum(array_map(fn($r) => (float)($r['ins_paid'] ?? (((float)($r['pri'] ?? 0))+((float)($r['sec'] ?? 0))+((float)($r['ter'] ?? 0)))), $carrierPerf));
        $totalClaims = array_sum(array_map(fn($r) => (int)($r['claims']  ?? 0), $carrierPerf));
        $totalPaidN  = array_sum(array_map(fn($r) => (int)($r['paid_n']  ?? 0), $carrierPerf));
        $bestCol = null; $bestPct = -1; $slowestPay = null; $slowestDays = 0;
        foreach ($carrierPerf as $r) {
            if ((float)$r['charge'] > 50000) { // exclude tiny carriers from "best"
                $rClaims = (int)($r['claims'] ?? 0); $rPaidN = (int)($r['paid_n'] ?? 0);
                $pct = $rClaims > 0 ? ($rPaidN/$rClaims)*100 : 0;
                if ($pct > $bestPct) { $bestPct = $pct; $bestCol = $r['carrier']; }
                if ((float)$r['avg_days'] > $slowestDays && (int)$r['paid_n'] > 50) {
                    $slowestDays = (float)$r['avg_days']; $slowestPay = $r['carrier'];
                }
            }
        }
        // Maxes for in-cell data bars
        $maxCharge = 0; foreach ($carrierPerf as $r) $maxCharge = max($maxCharge, (float)$r['charge']);
    ?>
        <div class="rv-insights mb-3">
            <div class="d-flex flex-wrap align-items-center">
                <div class="insight-stat">
                    <div class="lbl"><i class="ti ti-bulb me-1"></i>Active carriers</div>
                    <div class="val"><?= count($carrierPerf) ?></div>
                </div>
                <div class="insight-stat">
                    <div class="lbl" title="Paid claims ÷ Total claims">Overall collection %</div>
                    <div class="val"><?= $totalClaims > 0 ? number_format(($totalPaidN/$totalClaims)*100, 1) : 0 ?>%</div>
                </div>
                <?php if ($bestCol): ?>
                <div class="insight-stat">
                    <div class="lbl">Best collection rate</div>
                    <div class="val" style="font-size:.95rem"><?= e(mb_substr($bestCol, 0, 26)) ?> <small style="opacity:.7"><?= number_format($bestPct, 1) ?>%</small></div>
                </div>
                <?php endif; ?>
                <?php if ($slowestPay): ?>
                <div class="insight-stat">
                    <div class="lbl">Slowest to pay</div>
                    <div class="val" style="font-size:.95rem"><?= e(mb_substr($slowestPay, 0, 26)) ?> <small style="opacity:.7"><?= number_format($slowestDays, 0) ?> days</small></div>
                </div>
                <?php endif; ?>
                <div class="insight-stat">
                    <div class="lbl">Top carrier by charge</div>
                    <div class="val" style="font-size:.95rem"><?= $carrierPerf ? e(mb_substr($carrierPerf[0]['carrier'], 0, 26)) : '--' ?></div>
                </div>
            </div>
        </div>

        <?php
        $dedByCarrier = $deductibleReady ? coll_deductible_by($pdo, "COALESCE(carrier1_name,'(none)')", $whereSql, $params) : [];
        $dedTot       = $deductibleReady ? coll_deductible_total($pdo, $whereSql, $params) : ['n'=>0,'charge'=>0.0];
        ?>
        <div class="legacy-title mb-0">CARRIER PERFORMANCE SCORECARD</div>
        <div class="card mb-3"><div class="table-responsive" style="max-height:760px;overflow:auto">
        <table class="table table-sm table-bordered table-legacy sortable frz mb-0">
            <thead style="position:sticky;top:0;z-index:5"><tr>
                <th style="text-align:left;min-width:240px">Carrier</th>
                <th class="text-end" title="Paid claims ÷ Total claims">Collection %</th>
                <th class="text-end">Patients</th>
                <th class="text-end">Claims</th>
                <th class="text-end" title="Visits flagged as deductible (uploaded on the Deductible page)">Deductible</th>
                <th class="text-end" title="Claims with primary insurance paid">Total Paid Claims</th>
                <th class="text-end">Charge</th>
                <th class="text-end">Primary Paid</th>
                <th class="text-end">Secondary Paid</th>
                <th class="text-end" title="Tertiary insurance paid ($)">Tertiary Paid</th>
                <th class="text-end" title="Patient payment amount ($)">Patient Paid</th>
                <th class="text-end" title="Pri + Sec + Ter + Patient paid">Total Paid</th>
                <th class="text-end">Insurance Balance</th>
                <th class="text-end">Patient Balance</th>
                <th class="text-end" title="Claims awaiting primary insurance (primary unpaid AND any balance open)">Pri Pending</th>
                <th class="text-end" title="Primary paid, awaiting secondary (any balance open)">Sec Pending</th>
                <th class="text-end" title="Total paid ÷ # of paid claims">$ per Paid Claim</th>
                <th class="text-end">Unpaid Claims</th>
                <th class="text-end">Avg Days to Pay</th>
            </tr></thead>
            <tbody>
            <?php foreach ($carrierPerf as $ci => $r):
                $insPaid   = (float)($r['ins_paid'] ?? ((float)($r['pri']??0)+(float)($r['sec']??0)+(float)($r['ter']??0)));
                $totalPaidAmt = (float)($r['total_paid'] ?? ($insPaid + (float)($r['pat_paid']??0)));
                $paidN     = (int)($r['paid_n'] ?? 0);
                $claimsN   = (int)($r['claims'] ?? 0);
                $colPct    = $claimsN > 0 ? ($paidN/$claimsN)*100 : 0;
                $unpaidPct = $claimsN > 0 ? ((int)$r['unpaid_n']/$claimsN)*100 : 0;
                $perPaidC  = $paidN > 0 ? $totalPaidAmt / $paidN : 0;
                $colColor  = $colPct >= 70 ? '#16a34a' : ($colPct >= 40 ? '#f59e0b' : '#dc2626');
                $chargeBarW= $maxCharge > 0 ? ((float)$r['charge']/$maxCharge)*100 : 0;
                $cv        = ($r['carrier']==='' || $r['carrier']===null) ? '(no carrier)' : $r['carrier'];
            ?>
                <tr class="drill-parent" data-key="car<?= (int)$ci ?>" data-depth="0" data-c='<?= e(json_encode(['carrier' => $cv])) ?>' onclick="mDrillToggle(this)">
                    <td class="text-truncate" style="max-width:300px" title="<?= e($r['carrier']) ?>"><i class="ti ti-chevron-right drill-arrow"></i> <?= e($r['carrier']) ?></td>
                    <td class="num"><span style="color:<?= $colColor ?>;font-weight:600"><?= number_format($colPct, 1) ?>%</span></td>
                    <td class="num"><?= fmt_int($r['patients']) ?></td>
                    <td class="num"><?= fmt_int($r['claims']) ?></td>
                    <td class="num"><?= fmt_int($dedByCarrier[(string)$r['carrier']] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($paidN) ?></td>
                    <td class="num"><span class="databar"><span class="bar" style="width:<?= number_format($chargeBarW,1) ?>%;background:#206bc4"></span><span class="v"><?= fmt_money_short($r['charge']) ?></span></span></td>
                    <td class="num"><?= fmt_money_short($r['pri'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($r['sec'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($r['ter'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($r['pat_paid'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($totalPaidAmt) ?></td>
                    <td class="num"><?= fmt_money_short($r['bal']) ?></td>
                    <td class="num"><?= fmt_money_short($r['pat_bal'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($r['prim_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($r['sec_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($perPaidC) ?></td>
                    <td class="num"><?= fmt_int($r['unpaid_n']) ?> <small class="text-secondary">(<?= number_format($unpaidPct,0) ?>%)</small></td>
                    <td class="num"><?= $r['avg_days'] !== null ? number_format((float)$r['avg_days'], 0) . ' d' : '—' ?></td>
                </tr>
            <?php endforeach; ?>
            <?php $gt = coll_grand_totals($pdo, 'carriers', $whereSql, $params);
                  $gtInsPaid  = (float)($gt['ins_paid'] ?? ((float)($gt['pri']??0)+(float)($gt['sec']??0)+(float)($gt['ter']??0)));
                  $gtTotalPaid= (float)($gt['total_paid'] ?? ($gtInsPaid + (float)($gt['pat_paid']??0)));
                  $gtPaidN    = (int)($gt['paid_n'] ?? 0);
                  $gtClaimsN  = (int)($gt['claims'] ?? 0);
                  $gtCol      = $gtClaimsN > 0 ? ($gtPaidN/$gtClaimsN)*100 : 0;
                  $gtUp       = $gtClaimsN > 0 ? ((int)$gt['unpaid_n']/$gtClaimsN)*100 : 0;
                  $gtPerPaid  = $gtPaidN > 0 ? $gtTotalPaid / $gtPaidN : 0; ?>
                <tr class="total-row sticky-total">
                    <td style="text-align:left"><strong>GRAND TOTAL</strong> <span class="text-secondary fw-semibold">(<?= count($carrierPerf) ?> carriers)</span></td>
                    <td class="num"><?= number_format($gtCol, 1) ?>%</td>
                    <td class="num"><?= fmt_int($gt['patients'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($gt['claims'] ?? 0) ?></td>
                    <td class="num"><strong><?= fmt_int($dedTot['n']) ?></strong></td>
                    <td class="num"><?= fmt_int($gtPaidN) ?></td>
                    <td class="num"><?= fmt_money_short($gt['charge'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['pri'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['sec'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['ter'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['pat_paid'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gtTotalPaid) ?></td>
                    <td class="num"><?= fmt_money_short($gt['bal'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['pat_bal'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($gt['prim_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($gt['sec_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gtPerPaid) ?></td>
                    <td class="num"><?= fmt_int($gt['unpaid_n'] ?? 0) ?> <small class="text-secondary">(<?= number_format($gtUp, 0) ?>%)</small></td>
                    <td class="num"><?= (isset($gt['avg_days']) && $gt['avg_days'] !== null) ? number_format((float)$gt['avg_days'], 0).' d' : '—' ?></td>
                </tr>
            </tbody>
        </table></div></div>
        <?php $df = drillFilterQS($fFacilities, $fProviders, $fYears, $fMonths, $fStatuses); ?>
        <script>
        window.MDRILL = { endpoint: <?= json_encode(url('/admin/collection-drill.php')) ?>, id: <?= (int) $reportId ?>,
            tab: 'carriers', cols: 19, labelCol: 0, filters: <?= json_encode($df) ?>,
            map: { collpct: 1, patients: 2, claims: 3, paidn: 5, charge: 6, pri: 7, sec: 8, ter: 9, patpaid: 10, inspaid: 11, insbal: 12, patbal: 13, primpend: 14, secpend: 15, perpaid: 16, unpaid: 17, avgdays: 18 },
            chain: ['year', 'month'] };
        </script>
        <?php require __DIR__ . '/../includes/drill-js.php'; ?>
        <div class="small text-secondary mt-1 mb-3"><i class="ti ti-info-circle me-1"></i>Click a carrier to drill <strong>Carrier &rsaquo; Year &rsaquo; Month</strong> — every level shows the same metrics.</div>

    <?php elseif ($tab === 'cpt'):
        $totalCharge = array_sum(array_map(fn($r) => (float)$r['charge'], $cptAnalysis));
        $totalExpected = array_sum(array_map(fn($r) => (float)$r['expected'], $cptAnalysis));
        $totalPaid = array_sum(array_map(fn($r) => (float)$r['paid'], $cptAnalysis));
        $topCpt = $cptAnalysis[0] ?? null;
        // Find CPT with highest unpaid % (based on total claims since Volume column removed)
        $worstCpt = null; $worstPct = 0;
        foreach ($cptAnalysis as $r) {
            if ((int)$r['claims'] >= 100) {
                $pct = (int)$r['claims'] > 0 ? ((int)$r['unpaid_n']/(int)$r['claims'])*100 : 0;
                if ($pct > $worstPct) { $worstPct = $pct; $worstCpt = $r; }
            }
        }
        $maxCharge = $cptAnalysis ? (float)$cptAnalysis[0]['charge'] : 0;
    ?>
        <div class="rv-insights mb-3">
            <div class="d-flex flex-wrap align-items-center">
                <div class="insight-stat">
                    <div class="lbl"><i class="ti ti-bulb me-1"></i>Distinct CPTs billed</div>
                    <div class="val"><?= count($cptAnalysis) ?></div>
                </div>
                <?php if ($topCpt): ?>
                <div class="insight-stat">
                    <div class="lbl">Top CPT by revenue</div>
                    <div class="val"><?= e($topCpt['charge_code']) ?> <small style="opacity:.7"><?= fmt_money_short($topCpt['charge']) ?></small></div>
                </div>
                <?php endif; ?>
                <div class="insight-stat">
                    <div class="lbl">Total expected</div>
                    <div class="val"><?= fmt_money_short($totalExpected) ?></div>
                </div>
                <div class="insight-stat">
                    <div class="lbl">Actually paid</div>
                    <div class="val"><?= fmt_money_short($totalPaid) ?> <small style="opacity:.7"><?= $totalExpected > 0 ? number_format(($totalPaid/$totalExpected)*100, 0) : 0 ?>% of expected</small></div>
                </div>
                <?php if ($worstCpt): ?>
                <div class="insight-stat">
                    <div class="lbl">Most-unpaid CPT</div>
                    <div class="val text-warning"><?= e($worstCpt['charge_code']) ?> <small style="opacity:.7"><?= number_format($worstPct, 0) ?>%</small></div>
                </div>
                <?php endif; ?>
            </div>
        </div>

        <?php
        $dedByCpt = $deductibleReady ? coll_deductible_by($pdo, "charge_code", $whereSql, $params) : [];
        $dedTot   = $deductibleReady ? coll_deductible_total($pdo, $whereSql, $params) : ['n'=>0,'charge'=>0.0];
        ?>
        <div class="legacy-title mb-0">CPT / CHARGE CODE PERFORMANCE</div>
        <div class="card mb-3"><div class="table-responsive" style="max-height:760px;overflow:auto">
        <table class="table table-sm table-bordered table-legacy sortable frz mb-0">
            <thead style="position:sticky;top:0;z-index:5"><tr>
                <th>CPT</th>
                <th class="text-end" title="Paid claims ÷ Total claims">Collection %</th>
                <th class="text-end" title="Distinct visit_id">Total Claims</th>
                <th class="text-end" title="Deductible visits (uploaded on the Deductible page) that include this CPT">Deductible</th>
                <th class="text-end" title="Claims with primary insurance paid">Total Paid Claims</th>
                <th class="text-end" title="Claims with primary insurance unpaid">Unpaid</th>
                <th class="text-end">Patients</th>
                <th class="text-end">Charge</th>
                <th class="text-end" title="Fees Scheduled Projection (expected payment)">Fees Scheduled Projection</th>
                <th class="text-end">Primary Paid</th>
                <th class="text-end">Secondary Paid</th>
                <th class="text-end" title="Tertiary insurance paid ($)">Tertiary Paid</th>
                <th class="text-end" title="Patient payment amount ($)">Patient Paid</th>
                <th class="text-end" title="Pri + Sec + Ter + Patient paid">Total Paid</th>
                <th class="text-end" title="Fees Scheduled Projection − Total Paid">Expected to be Paid</th>
                <th class="text-end" title="Total paid ÷ # paid claims">$ per Paid Claim</th>
                <th class="text-end" title="Total paid ÷ all claims">$ per Claim (Overall)</th>
                <th class="text-end" title="(Total Paid − Expected) ÷ Expected">Variance</th>
                <th class="text-end" title="Awaiting primary insurance">Pri Pending</th>
                <th class="text-end" title="Primary paid, awaiting secondary">Sec Pending</th>
                <th class="text-end">Insurance Balance</th>
                <th class="text-end">Patient Balance</th>
                <th class="text-end" title="Unpaid claims ÷ Total Claims">Unpaid %</th>
            </tr></thead>
            <tbody>
            <?php foreach ($cptAnalysis as $ci => $r):
                $paidAmt    = (float)$r['paid'];
                $totalPaidAmt = (float)($r['total_paid'] ?? ((float)($r['pri']??0) + (float)($r['sec']??0) + (float)($r['ter']??0) + (float)($r['pat_paid']??0)));
                $expAmt     = (float)$r['expected'];
                $claimsN    = (int)($r['claims']    ?? 0);
                $paidN      = (int)($r['paid_n']    ?? 0);
                $unpaidClN  = (int)($r['unpaid_claims_n'] ?? max(0, $claimsN - $paidN));
                $variance   = $expAmt   > 0 ? (($totalPaidAmt - $expAmt) / $expAmt) * 100 : 0;
                $perPaidC   = $paidN    > 0 ? $totalPaidAmt / $paidN   : 0;
                $perAllC    = $claimsN  > 0 ? $totalPaidAmt / $claimsN : 0;
                $unpaidPct  = $claimsN  > 0 ? ($unpaidClN / $claimsN) * 100 : 0;
                $colPct     = $claimsN  > 0 ? ($paidN / $claimsN) * 100 : 0;
                $varColor   = $variance > -5  ? '#16a34a' : ($variance > -20 ? '#f59e0b' : '#dc2626');
                $colColor   = $colPct   >= 70 ? '#16a34a' : ($colPct   >= 40 ? '#f59e0b' : '#dc2626');
                $chargeBarW = $maxCharge > 0 ? ((float)$r['charge']/$maxCharge)*100 : 0;
            ?>
                <tr class="drill-parent" data-key="cpt<?= (int)$ci ?>" data-depth="0" data-c='<?= e(json_encode(['cpt' => (string)$r['charge_code']])) ?>' onclick="mDrillToggle(this)">
                    <td><i class="ti ti-chevron-right drill-arrow"></i> <strong><?= e($r['charge_code']) ?></strong></td>
                    <td class="num"><span style="color:<?= $colColor ?>;font-weight:600"><?= number_format($colPct, 1) ?>%</span></td>
                    <td class="num"><?= fmt_int($claimsN) ?></td>
                    <td class="num"><?= fmt_int($dedByCpt[(string)$r['charge_code']] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($paidN) ?></td>
                    <td class="num"><?= fmt_int($unpaidClN) ?></td>
                    <td class="num"><?= fmt_int($r['patients']) ?></td>
                    <td class="num"><span class="databar"><span class="bar" style="width:<?= number_format($chargeBarW,1) ?>%;background:#206bc4"></span><span class="v"><?= fmt_money_short($r['charge']) ?></span></span></td>
                    <td class="num"><?= fmt_money_short($expAmt) ?></td>
                    <td class="num"><?= fmt_money_short($r['pri'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($r['sec'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($r['ter'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($r['pat_paid'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($totalPaidAmt) ?></td>
                    <td class="num"><?= fmt_money_short($expAmt - $totalPaidAmt) ?></td>
                    <td class="num"><?= fmt_money_short($perPaidC) ?></td>
                    <td class="num"><?= fmt_money_short($perAllC) ?></td>
                    <td class="num"><span style="color:<?= $varColor ?>;font-weight:600"><?= number_format($variance, 1) ?>%</span></td>
                    <td class="num"><?= fmt_int($r['prim_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($r['sec_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($r['bal'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($r['pat_bal'] ?? 0) ?></td>
                    <td class="num"><?= number_format($unpaidPct, 0) ?>%</td>
                </tr>
            <?php endforeach; ?>
            <?php $gt = coll_grand_totals($pdo, 'cpt', $whereSql, $params);
                  $gtPaid     = (float)($gt['paid'] ?? 0);
                  $gtTotalPaid= (float)($gt['total_paid'] ?? ((float)($gt['pri']??0)+(float)($gt['sec']??0)+(float)($gt['ter']??0)+(float)($gt['pat_paid']??0)));
                  $gtExp      = (float)($gt['expected'] ?? 0);
                  $gtClaims   = (int)($gt['claims'] ?? 0);
                  $gtPaidN    = (int)($gt['paid_n'] ?? 0);
                  $gtUnpaidCl = (int)($gt['unpaid_claims_n'] ?? max(0, $gtClaims - $gtPaidN));
                  $gtVar      = $gtExp    > 0 ? (($gtTotalPaid - $gtExp) / $gtExp) * 100 : 0;
                  $gtPerPaid  = $gtPaidN  > 0 ? $gtTotalPaid / $gtPaidN  : 0;
                  $gtPerAll   = $gtClaims > 0 ? $gtTotalPaid / $gtClaims : 0;
                  $gtUp       = $gtClaims > 0 ? ($gtUnpaidCl / $gtClaims) * 100 : 0;
                  $gtCol      = $gtClaims > 0 ? ($gtPaidN / $gtClaims) * 100 : 0; ?>
                <tr class="total-row sticky-total">
                    <td><strong>GRAND TOTAL</strong> <span class="text-secondary fw-semibold">(<?= count($cptAnalysis) ?> CPT codes)</span></td>
                    <td class="num"><?= number_format($gtCol, 1) ?>%</td>
                    <td class="num"><?= fmt_int($gtClaims) ?></td>
                    <td class="num"><strong><?= fmt_int($dedTot['n']) ?></strong></td>
                    <td class="num"><?= fmt_int($gtPaidN) ?></td>
                    <td class="num"><?= fmt_int($gtUnpaidCl) ?></td>
                    <td class="num"><?= fmt_int($gt['patients'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['charge'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gtExp) ?></td>
                    <td class="num"><?= fmt_money_short($gt['pri'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['sec'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['ter'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['pat_paid'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gtTotalPaid) ?></td>
                    <td class="num"><?= fmt_money_short($gtExp - $gtTotalPaid) ?></td>
                    <td class="num"><?= fmt_money_short($gtPerPaid) ?></td>
                    <td class="num"><?= fmt_money_short($gtPerAll) ?></td>
                    <td class="num"><?= number_format($gtVar, 1) ?>%</td>
                    <td class="num"><?= fmt_int($gt['prim_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($gt['sec_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['bal'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['pat_bal'] ?? 0) ?></td>
                    <td class="num"><?= number_format($gtUp, 0) ?>%</td>
                </tr>
            </tbody>
        </table></div></div>
        <?php $df = drillFilterQS($fFacilities, $fProviders, $fYears, $fMonths, $fStatuses); ?>
        <script>
        window.MDRILL = { endpoint: <?= json_encode(url('/admin/collection-drill.php')) ?>, id: <?= (int) $reportId ?>,
            tab: 'cpt', cols: 23, labelCol: 0, filters: <?= json_encode($df) ?>,
            map: { collpct: 1, claims: 2, paidn: 4, unpaidn: 5, patients: 6, charge: 7, expected: 8, pri: 9, sec: 10, ter: 11, patpaid: 12, inspaid: 13, exptopaid: 14, perpaid: 15, peroverall: 16, variance: 17, primpend: 18, secpend: 19, insbal: 20, patbal: 21, unpaidpct: 22 },
            chain: ['year', 'month'] };
        </script>
        <?php require __DIR__ . '/../includes/drill-js.php'; ?>
        <div class="small text-secondary mt-1 mb-3"><i class="ti ti-info-circle me-1"></i>Click a CPT to drill <strong>CPT &rsaquo; Year &rsaquo; Month</strong>.</div>

    <?php elseif ($tab === 'providers'):
        $totalCharge  = array_sum(array_map(fn($r) => (float)$r['charge'], $providerPerf));
        $totalPaid    = array_sum(array_map(fn($r) => (float)$r['paid'],   $providerPerf));
        $totalExp     = array_sum(array_map(fn($r) => (float)$r['expected'], $providerPerf));
        $totalClaims  = array_sum(array_map(fn($r) => (int)($r['claims']  ?? 0), $providerPerf));
        $totalPaidN   = array_sum(array_map(fn($r) => (int)($r['paid_n']  ?? 0), $providerPerf));
        $top = $providerPerf[0] ?? null;
        // Best collection rate among providers with significant volume
        $bestRate = -1; $bestProv = null; $fastest = null; $fastestDays = 9999;
        foreach ($providerPerf as $r) {
            if ((int)$r['claims'] >= 50) {
                $rClaims = (int)$r['claims']; $rPaidN = (int)($r['paid_n'] ?? 0);
                $pct = $rClaims > 0 ? ($rPaidN/$rClaims)*100 : 0;
                if ($pct > $bestRate) { $bestRate = $pct; $bestProv = $r; }
                if ($r['avg_days'] !== null && (float)$r['avg_days'] < $fastestDays && (float)$r['avg_days'] > 0) {
                    $fastestDays = (float)$r['avg_days']; $fastest = $r['provider_name'];
                }
            }
        }
        $maxCharge = $providerPerf ? (float)$providerPerf[0]['charge'] : 0;
    ?>
        <div class="rv-insights mb-3">
            <div class="d-flex flex-wrap align-items-center">
                <div class="insight-stat">
                    <div class="lbl"><i class="ti ti-bulb me-1"></i>Active providers</div>
                    <div class="val"><?= count($providerPerf) ?></div>
                </div>
                <?php if ($top): ?>
                <div class="insight-stat">
                    <div class="lbl">Top biller</div>
                    <div class="val" style="font-size:.95rem"><?= e(mb_substr($top['provider_name'], 0, 26)) ?> <small style="opacity:.7"><?= fmt_money_short($top['charge']) ?></small></div>
                </div>
                <?php endif; ?>
                <?php if ($bestProv): ?>
                <div class="insight-stat">
                    <div class="lbl">Best collection</div>
                    <div class="val" style="font-size:.95rem"><?= e(mb_substr($bestProv['provider_name'], 0, 22)) ?> <small style="opacity:.7"><?= number_format($bestRate,1) ?>%</small></div>
                </div>
                <?php endif; ?>
                <?php if ($fastest): ?>
                <div class="insight-stat">
                    <div class="lbl">Fastest paid</div>
                    <div class="val" style="font-size:.95rem"><?= e(mb_substr($fastest, 0, 22)) ?> <small style="opacity:.7"><?= number_format($fastestDays, 0) ?>d</small></div>
                </div>
                <?php endif; ?>
                <div class="insight-stat">
                    <div class="lbl" title="Paid claims ÷ Total claims">Overall collection %</div>
                    <div class="val"><?= $totalClaims > 0 ? number_format(($totalPaidN/$totalClaims)*100, 1) : 0 ?>%</div>
                </div>
            </div>
        </div>

        <?php
        $dedByProv = $deductibleReady ? coll_deductible_by($pdo, "provider_name", $whereSql, $params) : [];
        $dedTot    = $deductibleReady ? coll_deductible_total($pdo, $whereSql, $params) : ['n'=>0,'charge'=>0.0];
        ?>
        <div class="legacy-title mb-0">PROVIDER PRODUCTIVITY &amp; COLLECTION SCORECARD</div>
        <div class="card mb-3"><div class="table-responsive" style="max-height:760px;overflow:auto">
        <table class="table table-sm table-bordered table-legacy sortable frz mb-0">
            <thead style="position:sticky;top:0;z-index:5"><tr>
                <th style="text-align:left;min-width:220px">Provider</th>
                <th class="text-end" title="Paid claims ÷ Total claims">Collection %</th>
                <th class="text-end" title="Distinct visit_id (claim) count">Total Claims</th>
                <th class="text-end" title="Deductible visits (uploaded on the Deductible page) for this provider">Deductible</th>
                <th class="text-end" title="Claims with primary insurance paid">Total Paid Claims</th>
                <th class="text-end" title="Claims with primary insurance unpaid">Unpaid</th>
                <th class="text-end">Patients</th>
                <th class="text-end">Facilities</th>
                <th class="text-end">CPTs</th>
                <th class="text-end">Charge</th>
                <th class="text-end" title="Fees Scheduled Projection (expected payment)">Fees Scheduled Projection</th>
                <th class="text-end" title="Primary insurance paid ($)">Primary Paid</th>
                <th class="text-end" title="Secondary insurance paid ($)">Secondary Paid</th>
                <th class="text-end" title="Tertiary insurance paid ($)">Tertiary Paid</th>
                <th class="text-end" title="Patient payment amount ($)">Patient Paid</th>
                <th class="text-end" title="Pri + Sec + Ter + Patient paid">Total Paid</th>
                <th class="text-end" title="Fees Scheduled Projection − Total Paid">Expected to be Paid</th>
                <th class="text-end">Insurance Balance</th>
                <th class="text-end">Patient Balance</th>
                <th class="text-end" title="Total paid ÷ # of paid claims">$ per Paid Claim</th>
                <th class="text-end" title="Total paid ÷ all claims (paid + unpaid)">$ per Claim (Overall)</th>
                <th class="text-end" title="Awaiting primary insurance">Pri Pending</th>
                <th class="text-end" title="Primary paid, awaiting secondary">Sec Pending</th>
                <th class="text-end">Avg Days to Pay</th>
            </tr></thead>
            <tbody>
            <?php foreach ($providerPerf as $ci => $r):
                $paidAmt    = (float)$r['paid'];
                $rPri       = (float)($r['pri'] ?? 0);
                $rSec       = (float)($r['sec'] ?? 0);
                $rTer       = (float)($r['ter'] ?? 0);
                $rPatPaid   = (float)($r['pat_paid'] ?? 0);
                $totalPaidAmt = (float)($r['total_paid'] ?? ($paidAmt + $rPatPaid));
                $paidN      = (int)($r['paid_n'] ?? 0);
                $claimsN    = (int)$r['claims'];
                $unpaidN    = (int)($r['unpaid_n'] ?? max(0, $claimsN - $paidN));
                $colPct     = $claimsN > 0 ? ($paidN/$claimsN)*100 : 0;
                $colColor   = $colPct >= 70 ? '#16a34a' : ($colPct >= 40 ? '#f59e0b' : '#dc2626');
                $chargeBarW = $maxCharge > 0 ? ((float)$r['charge']/$maxCharge)*100 : 0;
                $perPaidC   = $paidN   > 0 ? $totalPaidAmt / $paidN   : 0;
                $perAllC    = $claimsN > 0 ? $totalPaidAmt / $claimsN : 0;
            ?>
                <tr class="drill-parent" data-key="prv<?= (int)$ci ?>" data-depth="0" data-c='<?= e(json_encode(['provider' => (string)$r['provider_name']])) ?>' onclick="mDrillToggle(this)">
                    <td><i class="ti ti-chevron-right drill-arrow"></i> <?= e($r['provider_name']) ?></td>
                    <td class="num"><span style="color:<?= $colColor ?>;font-weight:600"><?= number_format($colPct, 1) ?>%</span></td>
                    <td class="num"><?= fmt_int($claimsN) ?></td>
                    <td class="num"><?= fmt_int($dedByProv[(string)$r['provider_name']] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($paidN) ?></td>
                    <td class="num"><?= fmt_int($unpaidN) ?></td>
                    <td class="num"><?= fmt_int($r['patients']) ?></td>
                    <td class="num"><?= fmt_int($r['facilities']) ?></td>
                    <td class="num"><?= fmt_int($r['cpts']) ?></td>
                    <td class="num"><span class="databar"><span class="bar" style="width:<?= number_format($chargeBarW,1) ?>%;background:#206bc4"></span><span class="v"><?= fmt_money_short($r['charge']) ?></span></span></td>
                    <td class="num"><?= fmt_money_short($r['expected']) ?></td>
                    <td class="num"><?= fmt_money_short($rPri) ?></td>
                    <td class="num"><?= fmt_money_short($rSec) ?></td>
                    <td class="num"><?= fmt_money_short($rTer) ?></td>
                    <td class="num"><?= fmt_money_short($rPatPaid) ?></td>
                    <td class="num"><?= fmt_money_short($totalPaidAmt) ?></td>
                    <td class="num"><?= fmt_money_short((float)$r['expected'] - $totalPaidAmt) ?></td>
                    <td class="num"><?= fmt_money_short($r['bal']) ?></td>
                    <td class="num"><?= fmt_money_short($r['pat_bal'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($perPaidC) ?></td>
                    <td class="num"><?= fmt_money_short($perAllC) ?></td>
                    <td class="num"><?= fmt_int($r['prim_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($r['sec_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= $r['avg_days'] !== null ? number_format((float)$r['avg_days'], 0) . ' d' : '—' ?></td>
                </tr>
            <?php endforeach; ?>
            <?php $gt = coll_grand_totals($pdo, 'providers', $whereSql, $params);
                  $gtPaid     = (float)($gt['paid'] ?? 0);
                  $gtPri      = (float)($gt['pri'] ?? 0);
                  $gtSec      = (float)($gt['sec'] ?? 0);
                  $gtTer      = (float)($gt['ter'] ?? 0);
                  $gtPatPaid  = (float)($gt['pat_paid'] ?? 0);
                  $gtTotalPaid= (float)($gt['total_paid'] ?? ($gtPaid + $gtPatPaid));
                  $gtPaidN    = (int)($gt['paid_n'] ?? 0);
                  $gtClaims   = (int)($gt['claims'] ?? 0);
                  $gtUnpaidN  = (int)($gt['unpaid_n'] ?? max(0, $gtClaims - $gtPaidN));
                  $gtCol      = $gtClaims > 0 ? ($gtPaidN/$gtClaims)*100 : 0;
                  $gtPerPaid  = $gtPaidN  > 0 ? $gtTotalPaid / $gtPaidN  : 0;
                  $gtPerAll   = $gtClaims > 0 ? $gtTotalPaid / $gtClaims : 0; ?>
                <tr class="total-row sticky-total">
                    <td style="text-align:left"><strong>GRAND TOTAL</strong> <span class="text-secondary fw-semibold">(<?= count($providerPerf) ?> providers)</span></td>
                    <td class="num"><?= number_format($gtCol, 1) ?>%</td>
                    <td class="num"><?= fmt_int($gtClaims) ?></td>
                    <td class="num"><strong><?= fmt_int($dedTot['n']) ?></strong></td>
                    <td class="num"><?= fmt_int($gtPaidN) ?></td>
                    <td class="num"><?= fmt_int($gtUnpaidN) ?></td>
                    <td class="num"><?= fmt_int($gt['patients'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($gt['facilities'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($gt['cpts'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['charge'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['expected'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gtPri) ?></td>
                    <td class="num"><?= fmt_money_short($gtSec) ?></td>
                    <td class="num"><?= fmt_money_short($gtTer) ?></td>
                    <td class="num"><?= fmt_money_short($gtPatPaid) ?></td>
                    <td class="num"><?= fmt_money_short($gtTotalPaid) ?></td>
                    <td class="num"><?= fmt_money_short((float)($gt['expected'] ?? 0) - $gtTotalPaid) ?></td>
                    <td class="num"><?= fmt_money_short($gt['bal'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gt['pat_bal'] ?? 0) ?></td>
                    <td class="num"><?= fmt_money_short($gtPerPaid) ?></td>
                    <td class="num"><?= fmt_money_short($gtPerAll) ?></td>
                    <td class="num"><?= fmt_int($gt['prim_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= fmt_int($gt['sec_pend_n'] ?? 0) ?></td>
                    <td class="num"><?= (isset($gt['avg_days']) && $gt['avg_days'] !== null) ? number_format((float)$gt['avg_days'], 0).' d' : '—' ?></td>
                </tr>
            </tbody>
        </table></div></div>
        <?php $df = drillFilterQS($fFacilities, $fProviders, $fYears, $fMonths, $fStatuses); ?>
        <script>
        window.MDRILL = { endpoint: <?= json_encode(url('/admin/collection-drill.php')) ?>, id: <?= (int) $reportId ?>,
            tab: 'providers', cols: 24, labelCol: 0, filters: <?= json_encode($df) ?>,
            map: { collpct: 1, claims: 2, paidn: 4, unpaidn: 5, patients: 6, facilities: 7, cpts: 8, charge: 9, expected: 10, pri: 11, sec: 12, ter: 13, patpaid: 14, paid: 15, exptopaid: 16, insbal: 17, patbal: 18, perpaid: 19, peroverall: 20, primpend: 21, secpend: 22, avgdays: 23 },
            chain: ['year', 'month'] };
        </script>
        <?php require __DIR__ . '/../includes/drill-js.php'; ?>
        <div class="small text-secondary mt-1 mb-3"><i class="ti ti-info-circle me-1"></i>Click a provider to drill <strong>Provider &rsaquo; Year &rsaquo; Month</strong>.</div>

    <?php elseif ($tab === 'detail'): ?>
        <div class="card"><div class="card-header py-2">
            <h3 class="card-title small mb-0">Raw rows <span class="text-secondary">(showing first <?= count($detailRows) ?> of <?= number_format($detailTotal) ?>)</span></h3>
        </div>
        <div class="card-body p-0">
        <table id="dt_detail" class="table table-sm table-vcenter card-table" style="font-size:12px">
            <thead><tr>
                <th>Visit ID</th><th>Facility</th><th>Provider</th><th>Patient</th><th>DOS</th>
                <?php if ($deductibleReady): ?><th title="This visit is on the uploaded Deductible list">Deductible</th><?php endif; ?>
                <th>Charge Code</th><th class="text-end">Units</th>
                <th class="text-end">Charge</th><th class="text-end" title="Fees Scheduled Projection (expected payment)">Fees Scheduled Projection</th>
                <th class="text-end">Pri Paid</th><th class="text-end">Sec Paid</th>
                <th class="text-end" title="Tertiary insurance paid ($)">Ter Paid</th>
                <th class="text-end" title="Patient payment amount ($)">Patient Paid</th>
                <th class="text-end" title="Pri + Sec + Ter + Patient payments">Total Paid</th>
                <th class="text-end" title="Fees Scheduled Projection − Total Paid">Expected to be Paid</th>
                <th class="text-end">Ins Balance</th><th class="text-end">Pat Balance</th>
                <th class="text-nowrap">Entry Date</th>
            </tr></thead>
            <tbody>
            <?php foreach ($detailRows as $r):
                $rowTotalPaid = (float)($r['primary_ins_paid'] ?? 0) + (float)($r['secondary_ins_paid'] ?? 0)
                              + (float)($r['tertiary_ins_paid'] ?? 0) + (float)($r['patient_payment_amount'] ?? 0);
            ?>
                <tr>
                    <td><code><?= e((string)$r['visit_id']) ?></code></td>
                    <td class="text-truncate" style="max-width:170px" title="<?= e((string)$r['facility']) ?>"><?= e((string)$r['facility']) ?></td>
                    <td class="text-truncate" style="max-width:140px"><?= e((string)$r['provider_name']) ?></td>
                    <td class="text-truncate" style="max-width:140px"><?= e((string)$r['patient_full_name']) ?></td>
                    <td class="text-nowrap" data-order="<?= e((string)$r['dos']) ?>"><?= e(fmt_date($r['dos'])) ?></td>
                    <?php if ($deductibleReady): ?><td data-order="<?= (int)($r['deductible'] ?? 0) ?>"><?= !empty($r['deductible']) ? '<span class="badge bg-red-lt text-red">Yes</span>' : '<span class="text-secondary">—</span>' ?></td><?php endif; ?>
                    <td><?= e((string)$r['charge_code']) ?></td>
                    <td class="text-end"><?= e((string)($r['units'] ?? '')) ?></td>
                    <td class="text-end"><?= fmt_money($r['charge_amount'] ?? 0) ?></td>
                    <td class="text-end"><?= fmt_money($r['expected_payment'] ?? 0) ?></td>
                    <td class="text-end"><?= fmt_money($r['primary_ins_paid'] ?? 0) ?></td>
                    <td class="text-end"><?= fmt_money($r['secondary_ins_paid'] ?? 0) ?></td>
                    <td class="text-end"><?= fmt_money($r['tertiary_ins_paid'] ?? 0) ?></td>
                    <td class="text-end"><?= fmt_money($r['patient_payment_amount'] ?? 0) ?></td>
                    <td class="text-end"><?= fmt_money($rowTotalPaid) ?></td>
                    <td class="text-end"><?= fmt_money((float)($r['expected_payment'] ?? 0) - $rowTotalPaid) ?></td>
                    <td class="text-end"><?= fmt_money($r['insurance_balance'] ?? 0) ?></td>
                    <td class="text-end"><?= fmt_money($r['patient_balance'] ?? 0) ?></td>
                    <td class="text-nowrap" data-order="<?= e((string)($r['entry_date'] ?? '')) ?>"><?= e(fmt_date($r['entry_date'] ?? null)) ?></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table></div></div>
        <script>
        $(function(){
            $('#dt_detail').DataTable({
                pageLength: 25, order: [[4,'desc']],
                dom: '<"row"<"col-md-6"B><"col-md-6"f>>rt<"row"<"col-md-6"i><"col-md-6"p>>',
                buttons: [
                    { extend: 'colvis', text: '<i class="ti ti-columns-3"></i> Columns' },
                    { extend: 'csv',    text: '<i class="ti ti-file-text"></i> CSV' },
                    { extend: 'excel',  text: '<i class="ti ti-file-spreadsheet"></i> Excel' },
                ],
                fixedHeader: true, deferRender: true,
            });
        });
        </script>

    <?php elseif ($tab === 'compare'): ?>
        <?php if (count($cmpSnaps) < 2): ?>
            <div class="card"><div class="card-body text-center text-secondary py-5">
                <div style="font-size:2.5rem">🆚</div>
                <h3 class="mt-2">Need at least two uploads to compare</h3>
                <p class="mb-0">This report has <?= count($cmpSnaps) ?> saved snapshot(s). Upload a newer Collection file and the comparison (latest vs the previous snapshot) will appear here automatically.</p>
            </div></div>
        <?php else:
            $fmtMoney = fn($v) => '$'.number_format((float)$v, 2);
            $baseDate = $cmpBaseMeta ? fmt_date($cmpBaseMeta['completed_at']) : '—';
            $curDate  = $cmpCurMeta  ? fmt_date($cmpCurMeta['completed_at'])  : 'today';

            // Derive $-per-claim metrics so they can be compared even though snap_totals
            // doesn't store them as keys.
            $cmpDeriveRow = function (?array $t) {
                if (!$t) return null;
                $t['unpaid_n']   = max(0, (int)($t['claims'] ?? 0) - (int)($t['paid_n'] ?? 0));
                $t['per_paid']   = (int)($t['paid_n'] ?? 0) > 0 ? (float)($t['ins_paid'] ?? 0) / (int)$t['paid_n']   : 0;
                $t['per_overall']= (int)($t['claims'] ?? 0) > 0 ? (float)($t['ins_paid'] ?? 0) / (int)$t['claims']   : 0;
                $t['exp_to_pay'] = (float)($t['expected'] ?? 0) - (float)($t['ins_paid'] ?? 0);   // Fees Scheduled Projection − Total Paid
                return $t;
            };
            $cmpCur  = $cmpDeriveRow($cmpCur);
            $cmpBase = $cmpDeriveRow($cmpBase);

            // Deductible is a CURRENT classification (from the latest Deductible upload), not a point-in-time
            // snapshot metric, so show the same live count on both sides (Δ = 0, neutral) rather than implying
            // historical movement. Honours the active filters.
            if ($deductibleReady) {
                $dedNow = coll_deductible_total($pdo, $whereSql, $params)['n'];
                if (is_array($cmpCur))  $cmpCur['deductible_n']  = $dedNow;
                if (is_array($cmpBase)) $cmpBase['deductible_n'] = $dedNow;
            }

            /* [label, snapshot key, type(money|int|pct), goodDirection (+1 up=good, -1 down=good, 0 neutral)] */
            /* Mirrors the 19-column Summary scorecard. "Month" is the row axis here
               so it's omitted; everything else lines up with the Summary header. */
            $cmpRows = [
                ['Total Claims',                  'claims',        'int',   0],
                ['Deductible Visits',             'deductible_n',  'int',   0],
                ['Paid Claims',                   'paid_n',        'int',   1],
                ['Unpaid Claims',                 'unpaid_n',      'int',  -1],
                ['Charge Amount',                 'charge',        'money', 0],
                ['Fees Scheduled Projection',        'expected',      'money', 0],
                ['Primary Paid',                  'pri',           'money', 1],
                ['Secondary Paid',                'sec',           'money', 1],
                ['Total Paid',                    'ins_paid',      'money', 1],
                ['Expected to be Paid',           'exp_to_pay',    'money', 0],
                ['$ per Paid Claim',              'per_paid',      'money', 1],
                ['$ per Claim (Overall)',         'per_overall',   'money', 1],
                ['Pri Pending',                   'prim_pend',     'int',  -1],
                ['Sec Pending',                   'sec_pend',      'int',  -1],
                ['O/S Claims',                    'outst_n',       'int',  -1],
                ['Primary O/S',                   'prim_outst',    'money',-1],
                ['Secondary O/S',                 'sec_outst',     'money',-1],
                ['Insurance Balance',             'bal',           'money',-1],
                ['Patient Balance',               'pat_bal',       'money',-1],
                ['Collection %',                  'collpct',       'pct',   1],
                ['Recently-billed Total Balance', 'rb_outstanding','money', 0],
            ];
        ?>
            <form method="GET" class="d-flex flex-wrap align-items-center gap-2 mb-3">
                <input type="hidden" name="id" value="<?= $reportId ?>"><input type="hidden" name="tab" value="compare">
                <span class="text-secondary small">Compare snapshot from</span>
                <select name="baseline" class="form-select form-select-sm" style="width:auto" onchange="this.form.submit()">
                    <?php foreach ($cmpSnaps as $s): if ((int)$s['id'] === $cmpCurId) continue; ?>
                        <option value="<?= (int)$s['id'] ?>" <?= (int)$s['id'] === $cmpBaseId ? 'selected' : '' ?>>
                            <?= e(fmt_dt($s['completed_at'])) ?> (<?= number_format((int)$s['valid_rows']) ?> rows)
                        </option>
                    <?php endforeach; ?>
                </select>
                <span class="text-secondary small">vs latest upload (<?= e($curDate) ?>)</span>
            </form>

            <div class="alert alert-info d-flex flex-wrap gap-3 align-items-center">
                <span><i class="ti ti-git-compare me-1"></i><strong>Point-in-time A/R comparison</strong></span>
                <span class="text-secondary">|</span>
                <span>Till <strong><?= e($baseDate) ?></strong> <span class="text-secondary small">(baseline upload)</span></span>
                <span class="text-secondary">vs</span>
                <span>Till <strong>Today</strong> <span class="text-secondary small">(<?= e($curDate) ?> — latest)</span></span>
            </div>

            <div class="legacy-title mb-0">A/R MOVEMENT &mdash; TILL <?= e(strtoupper($baseDate)) ?> vs TILL TODAY</div>
            <div class="card mb-3"><div class="table-responsive">
            <table class="table table-bordered table-legacy mb-0">
                <thead><tr>
                    <th style="text-align:left">Metric</th>
                    <th class="text-end" title="Snapshot A">Till <?= e($baseDate) ?> <span class="text-secondary fw-normal">(A)</span></th>
                    <th class="text-end" title="Snapshot B (latest)">Till Today <span class="text-secondary fw-normal">(B)</span></th>
                    <th class="text-end" title="B - A">&Delta;</th>
                    <th class="text-end" title="(B - A) / A">&Delta;%</th>
                </tr></thead>
                <tbody>
                <?php foreach ($cmpRows as [$label,$key,$type,$dir]):
                    // Older snapshots may not carry every key — fall back to 0 and flag in the cell.
                    $haveBase = $cmpBase !== null && array_key_exists($key, $cmpBase);
                    $haveCur  = $cmpCur  !== null && array_key_exists($key, $cmpCur);
                    $b = (float) ($cmpBase[$key] ?? 0); $c = (float) ($cmpCur[$key] ?? 0);
                    $delta = $c - $b;
                    $pctChg = $b != 0.0 ? ($delta / $b * 100) : ($c != 0.0 ? 100 : 0);
                    $fmt = fn($v) => $type==='money' ? $fmtMoney($v) : ($type==='pct' ? number_format($v,1).'%' : number_format($v));
                    $deltaTxt = ($delta>0?'+':'') . ($type==='money' ? $fmtMoney($delta) : ($type==='pct' ? number_format($delta,1).' pts' : number_format($delta)));
                    $color = '';
                    if ($dir !== 0 && abs($delta) > 0.0001) { $good = ($delta>0 && $dir>0) || ($delta<0 && $dir<0); $color = $good ? '#16a34a' : '#dc2626'; }
                ?>
                    <tr>
                        <td style="text-align:left"><strong><?= e($label) ?></strong></td>
                        <td class="num"><?= $haveBase ? $fmt($b) : '<span class="text-secondary">—</span>' ?></td>
                        <td class="num"><?= $haveCur  ? $fmt($c) : '<span class="text-secondary">—</span>' ?></td>
                        <td class="num" style="<?= $color ? 'color:'.$color.';font-weight:700' : '' ?>"><?= $deltaTxt ?></td>
                        <td class="num" style="<?= $color ? 'color:'.$color.';font-weight:700' : '' ?>"><?= ($pctChg>0?'+':'') . number_format($pctChg,1) ?>%</td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table></div></div>

            <?php
                // ------------- TOP MOVERS ----------------------------------------------
                // Compute per-facility and per-carrier swings between the two snapshots.
                // We use the cached carrier_performance / top_facilities blocks the engine
                // already writes for each snapshot. Older snapshots may not include them,
                // so we wrap everything in null guards and quietly skip when missing.
                $loadSnapFull = function ($id) use ($pdo) {
                    if (!$id) return null;
                    $s = $pdo->prepare("SELECT snapshot FROM collection_stats WHERE upload_id = ?");
                    $s->execute([$id]); $j = $s->fetchColumn();
                    return $j ? json_decode($j, true) : null;
                };
                $snapA = $cmpBaseId ? $loadSnapFull($cmpBaseId) : null;
                $snapB = $loadSnapFull($cmpCurId);

                $facCollMovers = $facBalMovers = $carCollMovers = $carBalMovers = [];
                $moversOK = false;
                // Collection % = paid claims / total claims (paid_n / claims).  The cached
                // top_facilities rows may predate paid_n; in that case fall back to the
                // older $ ratio so existing snapshots still render something useful.
                $facPct = function (array $r): float {
                    $claims = (int)($r['claims'] ?? 0);
                    if (isset($r['paid_n']) && $claims > 0) return ((int)$r['paid_n'] / $claims) * 100;
                    $exp = (float)($r['expected'] ?? 0);
                    if ($exp <= 0) return 0;
                    $ins = (float)($r['pri'] ?? 0) + (float)($r['sec'] ?? 0);
                    return $ins / $exp * 100;
                };
                try {
                    // Facilities — collection % and outstanding balance swings.
                    $facA = $facB = [];
                    foreach (($snapA['top_facilities'] ?? []) as $r) {
                        $facA[$r['facility']] = [
                            'pct'  => $facPct($r),
                            'bal'  => (float)($r['bal'] ?? 0) + (float)($r['pat_bal'] ?? 0),
                            'name' => (string) $r['facility'],
                        ];
                    }
                    foreach (($snapB['top_facilities'] ?? []) as $r) {
                        $facB[$r['facility']] = [
                            'pct'  => $facPct($r),
                            'bal'  => (float)($r['bal'] ?? 0) + (float)($r['pat_bal'] ?? 0),
                            'name' => (string) $r['facility'],
                        ];
                    }
                    $facKeys = array_unique(array_merge(array_keys($facA), array_keys($facB)));
                    foreach ($facKeys as $k) {
                        $a = $facA[$k] ?? null; $b = $facB[$k] ?? null;
                        if (!$a || !$b) continue; // only consider facilities present in both
                        $facCollMovers[] = ['name'=>$a['name'], 'a'=>$a['pct'], 'b'=>$b['pct'], 'delta'=>$b['pct']-$a['pct']];
                        $facBalMovers[]  = ['name'=>$a['name'], 'a'=>$a['bal'], 'b'=>$b['bal'], 'delta'=>$b['bal']-$a['bal']];
                    }

                    // Carriers — same idea, from carrier_performance.  carrier_performance
                    // already ships paid_n; older snapshots may not, so reuse $facPct's logic.
                    $carPct = function (array $r): float {
                        $claims = (int)($r['claims'] ?? 0);
                        if (isset($r['paid_n']) && $claims > 0) return ((int)$r['paid_n'] / $claims) * 100;
                        $exp = (float)($r['expected'] ?? 0);
                        if ($exp <= 0) return 0;
                        $ins = (float)($r['ins_paid'] ?? $r['paid'] ?? 0);
                        return $ins / $exp * 100;
                    };
                    $carA = $carB = [];
                    foreach (($snapA['carrier_performance'] ?? []) as $r) {
                        $carA[$r['carrier']] = [
                            'pct'  => $carPct($r),
                            'bal'  => (float)($r['bal'] ?? 0) + (float)($r['pat_bal'] ?? 0),
                            'name' => (string) $r['carrier'],
                        ];
                    }
                    foreach (($snapB['carrier_performance'] ?? []) as $r) {
                        $carB[$r['carrier']] = [
                            'pct'  => $carPct($r),
                            'bal'  => (float)($r['bal'] ?? 0) + (float)($r['pat_bal'] ?? 0),
                            'name' => (string) $r['carrier'],
                        ];
                    }
                    $carKeys = array_unique(array_merge(array_keys($carA), array_keys($carB)));
                    foreach ($carKeys as $k) {
                        $a = $carA[$k] ?? null; $b = $carB[$k] ?? null;
                        if (!$a || !$b) continue;
                        $carCollMovers[] = ['name'=>$a['name'], 'a'=>$a['pct'], 'b'=>$b['pct'], 'delta'=>$b['pct']-$a['pct']];
                        $carBalMovers[]  = ['name'=>$a['name'], 'a'=>$a['bal'], 'b'=>$b['bal'], 'delta'=>$b['bal']-$a['bal']];
                    }

                    // Largest absolute swings first; top 5 each. Render in that order so
                    // the most dramatic mover always sits at the top of the card.
                    $cmpAbs = fn($a, $b) => abs($b['delta']) <=> abs($a['delta']);
                    usort($facCollMovers, $cmpAbs); $facCollMovers = array_slice($facCollMovers, 0, 5);
                    usort($facBalMovers,  $cmpAbs); $facBalMovers  = array_slice($facBalMovers,  0, 5);
                    usort($carCollMovers, $cmpAbs); $carCollMovers = array_slice($carCollMovers, 0, 5);
                    usort($carBalMovers,  $cmpAbs); $carBalMovers  = array_slice($carBalMovers,  0, 5);
                    $moversOK = !empty($facCollMovers) || !empty($carCollMovers);
                } catch (Throwable $t) {
                    $moversOK = false; // silently skip Top Movers on old snapshots
                }

                // Render a single mover-list card. $kind = 'pct' (collection %) or 'money' (balance).
                $renderMovers = function (string $title, array $rows, string $kind, int $dir) use ($fmtMoney) {
                    echo '<div class="card h-100"><div class="card-header py-2"><h3 class="card-title small mb-0">' . e($title) . '</h3></div>';
                    if (!$rows) { echo '<div class="card-body text-secondary small">No data for this comparison.</div></div>'; return; }
                    echo '<div class="table-responsive"><table class="table table-sm card-table mb-0"><thead><tr>'
                       . '<th>Name</th><th class="text-end">A</th><th class="text-end">B</th><th class="text-end">&Delta;</th></tr></thead><tbody>';
                    foreach ($rows as $r) {
                        $fmt = ($kind === 'pct') ? fn($v) => number_format((float)$v, 1) . '%' : $fmtMoney;
                        $delta = (float) $r['delta'];
                        $color = '';
                        if ($dir !== 0 && abs($delta) > 0.0001) {
                            $good  = ($delta > 0 && $dir > 0) || ($delta < 0 && $dir < 0);
                            $color = $good ? '#16a34a' : '#dc2626';
                        }
                        $sign = $delta > 0 ? '+' : '';
                        $deltaTxt = ($kind === 'pct')
                            ? $sign . number_format($delta, 1) . ' pts'
                            : $sign . '$' . number_format($delta, 2);
                        echo '<tr><td class="text-truncate" style="max-width:240px" title="' . e($r['name']) . '">' . e($r['name']) . '</td>'
                           . '<td class="num">' . $fmt($r['a']) . '</td>'
                           . '<td class="num">' . $fmt($r['b']) . '</td>'
                           . '<td class="num" style="' . ($color ? 'color:'.$color.';font-weight:700' : '') . '">' . $deltaTxt . '</td></tr>';
                    }
                    echo '</tbody></table></div></div>';
                };
            ?>

            <?php if ($moversOK): ?>
            <div class="legacy-title mb-0">TOP MOVERS &mdash; LARGEST CHANGES BETWEEN SNAPSHOTS</div>
            <div class="row g-3 mb-3">
                <div class="col-md-6"><?php $renderMovers('Facilities — biggest swing in Collection %', $facCollMovers, 'pct',   1); ?></div>
                <div class="col-md-6"><?php $renderMovers('Carriers — biggest swing in Collection %',   $carCollMovers, 'pct',   1); ?></div>
                <div class="col-md-6"><?php $renderMovers('Facilities — biggest swing in Total Balance', $facBalMovers, 'money', -1); ?></div>
                <div class="col-md-6"><?php $renderMovers('Carriers — biggest swing in Total Balance',   $carBalMovers, 'money', -1); ?></div>
            </div>
            <?php else: ?>
            <div class="alert alert-secondary py-2 small mb-3"><i class="ti ti-info-circle me-1"></i>Top movers require both snapshots to carry per-facility &amp; per-carrier performance data. Re-upload the older Collection file to refresh its cached snapshot, then this section will populate.</div>
            <?php endif; ?>

            <div class="small text-secondary"><i class="ti ti-info-circle me-1"></i>Each snapshot is the data exactly as it stood when that Collection file was uploaded. <span style="color:#16a34a">Green</span> = favourable movement (more collected / fewer outstanding), <span style="color:#dc2626">red</span> = unfavourable.</div>
        <?php endif; ?>

    <?php elseif ($tab === 'history'): ?>
        <div class="card"><div class="card-header py-2"><h3 class="card-title small mb-0">Upload history (last 50)</h3></div>
        <div class="table-responsive"><table class="table card-table table-sm">
            <thead><tr><th>When</th><th>Kind</th><th>File</th><th class="text-end">Rows</th><th>Status</th></tr></thead><tbody>
            <?php if (!$upHist): ?>
                <tr><td colspan="5" class="text-secondary text-center py-4">No uploads recorded yet.</td></tr>
            <?php else: foreach ($upHist as $h): ?>
                <tr>
                    <td class="small text-secondary text-nowrap"><?= e(fmt_dt($h['created_at'])) ?></td>
                    <td><span class="badge bg-<?= $h['kind']==='collection'?'primary':($h['kind']==='facility_mapping'?'info':'secondary') ?>-lt"><?= e($h['kind']) ?></span></td>
                    <td class="small text-truncate" style="max-width:280px"><?= e($h['file_name']) ?></td>
                    <td class="text-end"><?= number_format((int)$h['valid_rows']) ?> ok <?php if ((int)$h['error_rows']): ?>· <span class="text-danger"><?= (int)$h['error_rows'] ?> err</span><?php endif; ?></td>
                    <td><span class="badge bg-<?= ['completed'=>'success','failed'=>'danger','processing'=>'info'][$h['status']] ?? 'secondary' ?>-lt"><?= e($h['status']) ?></span></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody></table></div></div>
    <?php endif; ?>

<?php endif; /* end of $rowCount > 0 */ ?>

</div>

<?php if ($rowCount > 0): ?>
<style>
table.sortable thead th { cursor: pointer; user-select: none; position: relative; }
table.sortable thead th .sort-ind { display: inline-block; margin-left: 4px; opacity: .35; font-size: .85em; vertical-align: middle; }
table.sortable thead th.sort-asc .sort-ind, table.sortable thead th.sort-desc .sort-ind { opacity: 1; }
</style>
<script>
/* Lightweight click-to-sort for `<table class="sortable">`.
   - Skips rows with class total-row / sticky-total / drill-claim / drill-agg (kept at bottom)
   - Detects numeric vs text from cell content (strips $ , % and 'd' suffix)
   - Toggles asc/desc on repeat clicks, single-column sort
   - Adds ti-arrows-sort/ti-arrow-up/ti-arrow-down indicator beside each header */
(function(){
    function parseCell(td){
        if (!td) return '';
        var raw = td.getAttribute('data-sort') || td.textContent || '';
        raw = raw.trim();
        // Numeric? Strip $ , % and trailing 'd' (days), allow leading -
        var num = raw.replace(/[$,%]/g, '').replace(/\s*d\s*$/i, '').trim();
        if (num !== '' && /^-?\d+(\.\d+)?$/.test(num)) return parseFloat(num);
        return raw.toLowerCase();
    }
    function isStickyRow(tr){
        return tr.classList.contains('total-row') || tr.classList.contains('sticky-total')
            || tr.classList.contains('drill-claim') || tr.classList.contains('drill-agg');
    }
    function attachSort(table){
        var thead = table.tHead, tbody = table.tBodies[0];
        if (!thead || !tbody) return;
        var headerRow = thead.rows[thead.rows.length - 1];
        var ths = headerRow.cells;
        // Build a mapping: header-index -> body cell index, accounting for colspans
        var bodyColIdx = [];
        var acc = 0;
        for (var i = 0; i < ths.length; i++) {
            bodyColIdx.push(acc);
            acc += (parseInt(ths[i].getAttribute('colspan')||'1',10) || 1);
        }
        for (var i = 0; i < ths.length; i++) {
            (function(headerIdx, th){
                if (th.querySelector('.sort-ind')) return;
                var ind = document.createElement('i');
                ind.className = 'ti ti-arrows-sort sort-ind';
                th.appendChild(ind);
                th.addEventListener('click', function(){
                    var dir = th.classList.contains('sort-asc') ? 'desc' : 'asc';
                    for (var j = 0; j < ths.length; j++) {
                        ths[j].classList.remove('sort-asc','sort-desc');
                        var oi = ths[j].querySelector('.sort-ind');
                        if (oi) oi.className = 'ti ti-arrows-sort sort-ind';
                    }
                    th.classList.add('sort-' + dir);
                    ind.className = 'ti ti-arrow-' + (dir === 'asc' ? 'up' : 'down') + ' sort-ind';
                    // For colspan=2 money columns the NUMBER is in the 2nd cell of the pair —
                    // try the 2nd cell first, fall back to the 1st if it's empty.
                    var colSpan = parseInt(th.getAttribute('colspan')||'1',10) || 1;
                    var primary = bodyColIdx[headerIdx];
                    var secondary = colSpan > 1 ? (bodyColIdx[headerIdx] + 1) : null;
                    var rows = Array.prototype.slice.call(tbody.rows);
                    var sortable = rows.filter(function(r){ return !isStickyRow(r); });
                    var sticky   = rows.filter(function(r){ return  isStickyRow(r); });
                    sortable.sort(function(a, b){
                        var va = parseCell(a.cells[primary]);
                        var vb = parseCell(b.cells[primary]);
                        // If primary cell looks empty / non-numeric, try the secondary (the $ sign vs amount split)
                        if (secondary !== null && (va === '' || va === '$')) va = parseCell(a.cells[secondary]);
                        if (secondary !== null && (vb === '' || vb === '$')) vb = parseCell(b.cells[secondary]);
                        if (va < vb) return dir === 'asc' ? -1 : 1;
                        if (va > vb) return dir === 'asc' ?  1 : -1;
                        return 0;
                    });
                    sortable.forEach(function(r){ tbody.appendChild(r); });
                    sticky.forEach(function(r){ tbody.appendChild(r); });
                });
            })(i, ths[i]);
        }
    }
    document.querySelectorAll('table.sortable').forEach(attachSort);
})();
</script>
<?php endif; ?>

<?php if ($rowCount > 0 && $tab === 'dashboard'): ?>
<script>
(function(){
    function isDark(){ return document.documentElement.getAttribute('data-bs-theme') === 'dark'; }
    function axis(){ return isDark() ? '#97a2b6' : '#6c7a89'; }
    function grid(){ return isDark() ? '#1e2a44' : '#eef0f3'; }

    const months = <?= json_encode(array_keys($byMonth)) ?>;
    const sparkClaims = <?= json_encode($sparkClaims) ?>;
    const sparkCharge = <?= json_encode($sparkCharge) ?>;
    const sparkPaid   = <?= json_encode($sparkPaid) ?>;
    const sparkBal    = <?= json_encode($sparkBal) ?>;

    function sparkOpts(d, c) {
        return {
            chart: { type:'area', height:32, sparkline:{enabled:true} },
            theme: { mode: isDark() ? 'dark' : 'light' },
            series: [{ data: d }], colors:[c], stroke:{ curve:'smooth', width:2 },
            fill: { type:'gradient', gradient:{ shadeIntensity:1, opacityFrom:.35, opacityTo:.05 } },
            tooltip: { fixed:{enabled:false}, x:{show:false}, marker:{show:false} },
        };
    }
    [['#spark1', sparkClaims, '#206bc4'], ['#spark2', sparkCharge, '#2fb344'],
     ['#spark3', sparkPaid, '#0ea5e9'],   ['#spark4', sparkBal, '#f76707']].forEach(([s,d,c]) => {
        const el = document.querySelector(s); if (el) new ApexCharts(el, sparkOpts(d, c)).render();
    });

    /* Main monthly trend */
    new ApexCharts(document.querySelector('#chart_monthly'), {
        chart: { type:'line', height:300, toolbar:{ show:false }, fontFamily:'inherit', background:'transparent' },
        theme: { mode: isDark() ? 'dark' : 'light' },
        series: [
            { name:'Charge Amount',     type:'column', data: sparkCharge },
            { name:'Total Paid',        type:'line',   data: sparkPaid },
            { name:'Insurance Balance', type:'line',   data: sparkBal },
        ],
        stroke: { curve:'smooth', width:[0,3,3] },
        colors: ['#206bc4','#2fb344','#f76707'],
        xaxis: { categories: months, labels:{ style:{ colors: axis() } } },
        yaxis: { labels:{ style:{ colors: axis() }, formatter: v => '$' + Math.round(v).toLocaleString() } },
        tooltip: { y: { formatter: v => '$' + v.toLocaleString(undefined,{minimumFractionDigits:2}) } },
        legend: { position:'top', labels:{ colors: axis() } },
        grid: { borderColor: grid() },
    }).render();

    /* Donut */
    new ApexCharts(document.querySelector('#chart_donut'), {
        chart: { type:'donut', height:300, fontFamily:'inherit', background:'transparent' },
        theme: { mode: isDark() ? 'dark' : 'light' },
        series: [<?= round($totals['pri'],2) ?>, <?= round($totals['sec'],2) ?>, <?= round($totals['bal'],2) ?>, <?= round($totals['pat_bal'],2) ?>],
        labels: ['Primary Paid','Secondary Paid','Insurance Balance','Patient Balance'],
        colors: ['#2fb344','#206bc4','#f76707','#ae3ec9'],
        legend: { position:'bottom', labels:{ colors: axis() } },
        dataLabels: { enabled:true, formatter: v => v.toFixed(1) + '%' },
    }).render();

    /* All-metrics chart */
    const monthlyPri = <?= json_encode(array_map(fn($x)=>round($x['pri'],2), array_values($byMonth))) ?>;
    const monthlySec = <?= json_encode(array_map(fn($x)=>round($x['sec'],2), array_values($byMonth))) ?>;
    const monthlyExp = <?= json_encode(array_map(fn($x)=>round($x['expected'],2), array_values($byMonth))) ?>;
    new ApexCharts(document.querySelector('#chart_monthly_full'), {
        chart: { type:'line', height:340, toolbar:{ show:true }, fontFamily:'inherit', background:'transparent' },
        theme: { mode: isDark() ? 'dark' : 'light' },
        series: [
            { name:'Charge Amount',     type:'column', data: sparkCharge },
            { name:'Insurance Balance', type:'column', data: sparkBal },
            { name:'Primary Paid',      type:'line',   data: monthlyPri },
            { name:'Secondary Paid',    type:'line',   data: monthlySec },
            { name:'Fees Scheduled Projection',  type:'line',   data: monthlyExp },
            { name:'Claims',            type:'line',   data: sparkClaims, yAxisIndex: 1 },
        ],
        stroke: { curve:'smooth', width:[0,0,3,3,3,3] },
        markers: { size:[0,0,5,5,5,5] },
        colors: ['#475569','#f59e0b','#16a34a','#0ea5e9','#7c3aed','#dc2626'],
        xaxis: { categories: months, labels:{ style:{ colors: axis() } } },
        yaxis: [
            { title:{ text:'Amount ($)', style:{ fontSize:'11px', color: axis() } }, labels:{ style:{ colors: axis() }, formatter: v => '$' + Math.round(v).toLocaleString() } },
            { opposite:true, title:{ text:'Claims', style:{ fontSize:'11px', color: axis() } }, labels:{ style:{ colors: axis() }, formatter: v => Math.round(v).toLocaleString() } },
        ],
        plotOptions: { bar:{ borderRadius:3, columnWidth:'45%' } },
        dataLabels: { enabled:false },
        legend: { position:'top', fontSize:'11px', labels:{ colors: axis() } },
        grid: { borderColor: grid() },
    }).render();

    /* Top facilities bar */
    new ApexCharts(document.querySelector('#chart_top_fac'), {
        chart: { type:'bar', height:340, toolbar:{ show:false }, background:'transparent' },
        theme: { mode: isDark() ? 'dark' : 'light' },
        series: [
            { name:'Charge',            data: <?= json_encode(array_values(array_map(fn($x)=>round($x['charge'],2), $byFacility10))) ?> },
            { name:'Total Paid',        data: <?= json_encode(array_values(array_map(fn($x)=>round($x['pri']+$x['sec']+($x['ter']??0)+($x['pat_paid']??0),2), $byFacility10))) ?> },
            { name:'Insurance Balance', data: <?= json_encode(array_values(array_map(fn($x)=>round($x['bal'],2),    $byFacility10))) ?> },
            { name:'Patient Balance',   data: <?= json_encode(array_values(array_map(fn($x)=>round($x['pat_bal'] ?? 0,2), $byFacility10))) ?> },
        ],
        plotOptions: { bar:{ horizontal:true, borderRadius:3 } },
        colors: ['#206bc4','#2fb344','#f76707','#ae3ec9'],
        dataLabels: { enabled:false },
        xaxis: { categories: <?= json_encode(array_map(fn($f)=>mb_substr($f,0,32), array_keys($byFacility10))) ?>,
                 labels:{ style:{ colors: axis() }, formatter: v => '$' + (typeof v==='number' ? Math.round(v).toLocaleString() : v) } },
        yaxis: { labels:{ style:{ colors: axis() } } },
        legend: { position:'top', labels:{ colors: axis() } },
        grid: { borderColor: grid() },
        tooltip: { y: { formatter: v => '$' + v.toLocaleString(undefined,{minimumFractionDigits:2}) } },
    }).render();
})();
</script>
<?php endif; ?>

<?php require $cpFoot; ?>
