<?php
/**
 * Reconciliation Report (coding.work / coding.supervise / coding.reports).
 *
 * Per BILLING PROVIDER, per day, compares:
 *   - Count - BS : the patient count the BS-uploader entered on each billing sheet (coding_billing_sheets.patient_count),
 *                  summed per day and broken out by SCRIBE (a sheet can be tagged to a scribe under the billing provider).
 *   - Inventory  : coding charts whose effective billing provider = this provider and whose service day = that day,
 *                  with Completed / Pending / RFI / Disregard sub-counts.
 *   - Variance   : Inventory − Completed (the not-yet-completed backlog). Each Provider–Facility is a separate bunch.
 *
 * ROLE SCOPE: a coder sees only the client»process»facility»provider they are aligned to (coding_user_scope);
 * a supervisor/manager sees their reporting team's aligned scope; a sysadmin sees everything. Filter by
 * client / process / facility / date range. Inventory is matched to the billing sheet on DATE OF SERVICE.
 */
require_once __DIR__ . '/../includes/config.php';
$user = require_login('internal');                       // route gate: coding.work / coding.supervise / coding.reports
$pdo  = db();
$uid  = (int) $user['id'];

/* ---------------- role-based scope (which c»p»f»p this user may see) ----------------
 * coder → their own aligned c»p»f»p; supervisor/manager → their reporting team's union; sysadmin → everything.
 * Applied as an EXISTS over coding_user_scope (a NULL level there means "everything under the level above"). */
$canSupervise = can('coding.supervise');
// Vertical-wide oversight roles (supervisor / manager / validator / auditor / reports) see the whole coding vertical
// when neither they nor their team carry a personal alignment; a plain coder with no alignment sees nothing.
// page.coding-inventory-report is included deliberately: that grant is how an admin hands this report to someone
// OUTSIDE coding (an AR / Billing manager, for business oversight). Such a user holds no coding.* permission and
// has no coding_user_scope alignment, so without this they fell into the "plain coder with no alignment" branch
// below and got scopeUserIds='0' — the page opened but every table was EMPTY, which read as "the grant did
// nothing". An explicit page grant IS the authorisation, so it reads the whole coding vertical (read-only —
// this report has no write actions).
$isOversight  = $canSupervise || can('coding.reports') || can('coding.validate') || can('coding.audit')
             || can(page_perm_code('coding-inventory-report.php'));
$codVClause = coding_vertical_scope_sql('vertical_id');   // this is a CODING report → only CODING alignment rows count (fenced by code)
$team = coding_team_scope($pdo, $uid);                   // null = sysadmin (unrestricted)
$scopeUserIds = null;                                    // null → no scope filter (see all)
if ($team !== null) {
    $teamCsv  = implode(',', array_map('intval', $team ?: [0]));
    $hasScope = (int) $pdo->query("SELECT COUNT(*) FROM coding_user_scope WHERE user_id IN ($teamCsv) AND $codVClause")->fetchColumn() > 0;
    if ($hasScope)          $scopeUserIds = $teamCsv;    // restrict to the user/team's aligned c»p»f»p
    elseif (!$isOversight)  $scopeUserIds = '0';         // a plain coder with no alignment → nothing until a manager aligns them
    // else: an oversight role with no team alignment → no scope (sees the whole coding vertical)
}
/* Returns an EXISTS fragment restricting a table (with client_id/process_id/facility_id/provider_id) to the scope. */
$scopeSql = function (string $tbl) use ($scopeUserIds) {
    if ($scopeUserIds === null) return '';
    return " AND EXISTS (SELECT 1 FROM coding_user_scope sc WHERE sc.user_id IN ($scopeUserIds)
        AND " . coding_vertical_scope_sql('sc.vertical_id') . "
        AND sc.client_id=$tbl.client_id
        AND (sc.process_id  IS NULL OR sc.process_id =$tbl.process_id)
        AND (sc.facility_id IS NULL OR sc.facility_id=$tbl.facility_id)
        AND (sc.provider_id IS NULL OR sc.provider_id=$tbl.provider_id))";
};
/* SCRIBE sheets carry no billing provider (provider_id NULL), so they're gated at the FACILITY level — visible to
 * anyone aligned to the client»process»facility regardless of which provider they're aligned to. */
$scopeFacility = function (string $tbl) use ($scopeUserIds) {
    if ($scopeUserIds === null) return '';
    return " AND EXISTS (SELECT 1 FROM coding_user_scope sc WHERE sc.user_id IN ($scopeUserIds)
        AND " . coding_vertical_scope_sql('sc.vertical_id') . "
        AND sc.client_id=$tbl.client_id
        AND (sc.process_id  IS NULL OR sc.process_id =$tbl.process_id)
        AND (sc.facility_id IS NULL OR sc.facility_id=$tbl.facility_id))";
};

/* ---------------- filters ---------------- */
$fClient   = (int) ($_GET['client_id'] ?? 0);
$fProcess  = (int) ($_GET['process_id'] ?? 0);
$fFacility = (int) ($_GET['facility_id'] ?? 0);
$fProvider = (int) ($_GET['provider_id'] ?? 0);   // filter to one billing provider ($pid)
$fScribe   = (int) ($_GET['scribe_id'] ?? 0);     // filter to one scribe ($sid)
$today = date('Y-m-d');
$from = coding_parse_date($_GET['from'] ?? '') ?: date('Y-m-d', strtotime('-15 days'));
$to   = coding_parse_date($_GET['to'] ?? '') ?: $today;
if ($from > $to) { [$from, $to] = [$to, $from]; }
/* cap the span so the day-columns stay manageable (and the queries fast) */
$MAX_DAYS = 31;
if ((strtotime($to) - strtotime($from)) / 86400 > $MAX_DAYS) $from = date('Y-m-d', strtotime($to . ' -' . $MAX_DAYS . ' days'));
$days = [];
for ($d = strtotime($from); $d <= strtotime($to); $d = strtotime('+1 day', $d)) $days[] = date('Y-m-d', $d);

/* shared client/process/facility filter */
$fw = []; $fp = [];
if ($fClient)   { $fw[] = 'client_id=?';   $fp[] = $fClient; }
if ($fProcess)  { $fw[] = 'process_id=?';  $fp[] = $fProcess; }
if ($fFacility) { $fw[] = 'facility_id=?'; $fp[] = $fFacility; }
$fwSql = $fw ? (' AND ' . implode(' AND ', $fw)) : '';

/* ---------------- BS counts: per (billing provider, scribe, day) ----------------
 * (1) BILLING sheets (provider_id set) — counted directly under that billing provider.
 * (2) SCRIBE sheets (provider_id NULL, scribe_id set) — carry NO billing provider, so the scribe's count is mapped
 *     to a billing provider via the CODER's charts: the billing_provider_id the coder tagged on charts whose
 *     rendering provider = this scribe, same client»process»facility + DOS (the dominant one if several). A scribe
 *     sheet with no matching chart yet falls into bucket 0 ("not yet coded"). */
$bs = []; $provIds = []; $scribeIds = [];
$bsB = $pdo->prepare("SELECT provider_id pid, facility_id fid, sheet_date d, SUM(patient_count) cnt
    FROM coding_billing_sheets
    WHERE provider_id IS NOT NULL AND sheet_date BETWEEN ? AND ?$fwSql AND patient_count IS NOT NULL" . $scopeSql('coding_billing_sheets') . "
    GROUP BY provider_id, facility_id, sheet_date");
$bsB->execute(array_merge([$from, $to], $fp));
foreach ($bsB as $r) { $bs[(int)$r['pid']][(int)$r['fid']][0][$r['d']] = (int)$r['cnt']; $provIds[(int)$r['pid']] = 1; }

$bsS = $pdo->prepare("SELECT pid, fid, sid, d, SUM(cnt) cnt FROM (
        SELECT cbs.scribe_id sid, cbs.facility_id fid, cbs.sheet_date d, cbs.patient_count cnt,
            COALESCE((SELECT COALESCE(ci.billing_provider_id, ci.coded_provider_id, ci.provider_id) bp FROM coding_inventory ci
                WHERE ci.client_id=cbs.client_id AND ci.process_id=cbs.process_id AND ci.facility_id=cbs.facility_id
                  AND ci.rendering_provider_id=cbs.scribe_id AND COALESCE(ci.dos, ci.record_sign_date)=cbs.sheet_date
                GROUP BY bp ORDER BY COUNT(*) DESC, bp LIMIT 1), 0) pid
        FROM coding_billing_sheets cbs
        WHERE cbs.provider_id IS NULL AND cbs.scribe_id IS NOT NULL AND cbs.sheet_date BETWEEN ? AND ?$fwSql
          AND cbs.patient_count IS NOT NULL" . $scopeFacility('cbs') . "
    ) x GROUP BY pid, fid, sid, d");
$bsS->execute(array_merge([$from, $to], $fp));
foreach ($bsS as $r) {
    $bs[(int)$r['pid']][(int)$r['fid']][(int)$r['sid']][$r['d']] = (int)($bs[(int)$r['pid']][(int)$r['fid']][(int)$r['sid']][$r['d']] ?? 0) + (int)$r['cnt'];
    $provIds[(int)$r['pid']] = 1; $scribeIds[(int)$r['sid']] = 1;
}

/* ---------------- inventory: per (effective billing provider, service day) ----------------
 * A chart's service day = its DOS (set during coding), falling back to record_sign_date (set at upload) so a
 * NOT-YET-CODED chart — which has no DOS — still counts on its service day. Statuses: Completed / Pending
 * (pending+in_progress+hold) / RFI / Disregard; Inventory = all of them. */
$invStmt = $pdo->prepare("SELECT COALESCE(billing_provider_id, coded_provider_id, provider_id) bp,
        facility_id fid,
        CASE WHEN scribe_flag=1 AND rendering_provider_id IS NOT NULL THEN rendering_provider_id ELSE 0 END sid,
        COALESCE(dos, record_sign_date) d,
        COUNT(*) inv,
        SUM(status='completed') comp,
        SUM(status IN ('pending','in_progress')) pend,
        SUM(status='hold') hold,
        SUM(status='rfi') rfi,
        SUM(status='disregard') dis
    FROM coding_inventory ci
    WHERE COALESCE(dos, record_sign_date) BETWEEN ? AND ?$fwSql AND COALESCE(dos, record_sign_date) IS NOT NULL" . $scopeSql('ci') . "
    GROUP BY bp, facility_id, sid, COALESCE(dos, record_sign_date)");
$invStmt->execute(array_merge([$from, $to], $fp));
$inv = [];
foreach ($invStmt as $r) {
    $bp = (int)$r['bp']; $fid = (int)$r['fid']; $sid = (int)$r['sid'];
    $inv[$bp][$fid][$sid][$r['d']] = ['inv'=>(int)$r['inv'], 'comp'=>(int)$r['comp'], 'pend'=>(int)$r['pend'], 'hold'=>(int)$r['hold'], 'rfi'=>(int)$r['rfi'], 'dis'=>(int)$r['dis']];
    $provIds[$bp] = 1;
    if ($sid) $scribeIds[$sid] = 1;   // a scribe with coded charts but maybe no BS sheet still gets a name
}

/* ---------------- billing status counts: per (BILLING PROVIDER, facility, day) ----------------
 * The biller's actual disposition (billing_allocation.billing_status) rolled up to the BILLING PROVIDER
 * (COALESCE(billing_provider_id, coded_provider_id, provider_id)) — NEVER the scribe. So a scribed chart's
 * Billed / Unbilled / On-Hold-Deductible counts always land under its billing provider (the critical requirement).
 * Same facility + service-day (COALESCE(dos, record_sign_date)) keys as $inv, so the cells line up. */
$bstat = [];
$bstStmt = $pdo->prepare("SELECT COALESCE(ci.billing_provider_id, ci.coded_provider_id, ci.provider_id) bp,
        ci.facility_id fid, COALESCE(ci.dos, ci.record_sign_date) d,
        SUM(ba.billing_status='billed') billed,
        SUM(ba.billing_status='unbilled') unbilled,
        SUM(ba.billing_status='hold_deductible') hold
    FROM coding_inventory ci
    JOIN billing_allocation ba ON ba.inventory_id=ci.id AND ba.billing_status IS NOT NULL
    WHERE COALESCE(ci.dos, ci.record_sign_date) BETWEEN ? AND ?$fwSql AND COALESCE(ci.dos, ci.record_sign_date) IS NOT NULL" . $scopeSql('ci') . "
    GROUP BY bp, ci.facility_id, COALESCE(ci.dos, ci.record_sign_date)");
$bstStmt->execute(array_merge([$from, $to], $fp));
foreach ($bstStmt as $r) $bstat[(int)$r['bp']][(int)$r['fid']][$r['d']] = ['billed'=>(int)$r['billed'], 'unbilled'=>(int)$r['unbilled'], 'hold_deductible'=>(int)$r['hold']];

/* ---------------- "Received by billing": coding charts RELEASED to the INTERNAL billing team ----------------
 * released_to_billing_at IS NOT NULL, not void, internal destination (mirrors billing_released_where('ci')) — the
 * count HANDED OFF to the billing team, whether or not a biller has been allocated / has dispositioned it yet.
 * Same keys as $bstat (billing provider · facility · service-day); folded into the $bstat cells so the billing rows
 * render from one source. Billed/Unbilled/On-Hold-Deductible are the biller's disposition OF these received charts. */
$brStmt = $pdo->prepare("SELECT COALESCE(ci.billing_provider_id, ci.coded_provider_id, ci.provider_id) bp,
        ci.facility_id fid, COALESCE(ci.dos, ci.record_sign_date) d, COUNT(*) received
    FROM coding_inventory ci
    WHERE ci.released_to_billing_at IS NOT NULL AND COALESCE(ci.billing_status,'pending') <> 'void'
        AND COALESCE(ci.release_destination,'internal') = 'internal'
        AND COALESCE(ci.dos, ci.record_sign_date) BETWEEN ? AND ?$fwSql AND COALESCE(ci.dos, ci.record_sign_date) IS NOT NULL" . $scopeSql('ci') . "
    GROUP BY bp, ci.facility_id, COALESCE(ci.dos, ci.record_sign_date)");
$brStmt->execute(array_merge([$from, $to], $fp));
foreach ($brStmt as $r) {
    $p = (int) $r['bp']; $f = (int) $r['fid']; $d = $r['d'];
    if (!isset($bstat[$p][$f][$d])) $bstat[$p][$f][$d] = ['billed'=>0, 'unbilled'=>0, 'hold_deductible'=>0];
    $bstat[$p][$f][$d]['received'] = (int) $r['received'];
}
/* "Yet to be billed" = received by billing but NOT yet dispositioned by a biller (Received − Billed − Unbilled −
 * On-Hold-Deductible). Normalize every billing cell so all keys exist, then derive it (clamped ≥0). */
foreach ($bstat as $bp2 => $facs2) foreach ($facs2 as $bf2 => $ds2) foreach ($ds2 as $bd2 => $cell2) {
    $cell2 += ['received' => 0, 'billed' => 0, 'unbilled' => 0, 'hold_deductible' => 0];
    $cell2['yet_to_bill'] = max(0, (int) $cell2['received'] - (int) $cell2['billed'] - (int) $cell2['unbilled'] - (int) $cell2['hold_deductible']);
    $bstat[$bp2][$bf2][$bd2] = $cell2;
}

/* ---------------- Provider / Scribe filters ----------------
 * The full in-scope provider/scribe sets are captured FIRST (they populate the filter dropdowns); then $bs/$inv are
 * PRUNED to the picked provider/scribe so the pivot, all aggregates AND the Excel export stay consistent. */
$allProvIds = $provIds; $allScribeIds = $scribeIds;
if ($fProvider) {
    $bs    = isset($bs[$fProvider])    ? [$fProvider => $bs[$fProvider]]    : [];
    $inv   = isset($inv[$fProvider])   ? [$fProvider => $inv[$fProvider]]   : [];
    $bstat = isset($bstat[$fProvider]) ? [$fProvider => $bstat[$fProvider]] : [];
}
if ($fScribe) {
    $keepScribe = function (array $data) use ($fScribe) { $o = [];
        foreach ($data as $pid => $ff) foreach ($ff as $fid => $ss) if (isset($ss[$fScribe])) $o[$pid][$fid][$fScribe] = $ss[$fScribe];
        return $o; };
    $bs = $keepScribe($bs); $inv = $keepScribe($inv);
}
if ($fProvider || $fScribe) {   // rebuild the provider set from whatever survived the filters
    $provIds = [];
    foreach ($bs as $pid => $x)  $provIds[$pid] = 1;
    foreach ($inv as $pid => $x) $provIds[$pid] = 1;
}

/* ---------------- facility names (facility is now a first-class grouping key) ----------------
 * Each Provider–Facility is its own bunch, so we no longer collapse facility into a comma-joined label — the
 * facility id lives in the $bs / $inv keys and every cell belongs to exactly one facility. */
$facIds = [];
foreach ($bs  as $pd) foreach ($pd as $fid => $x) $facIds[(int)$fid] = 1;
foreach ($inv as $pd) foreach ($pd as $fid => $x) $facIds[(int)$fid] = 1;
$facNames = [];
if ($facIds) {
    $fids = array_keys($facIds); $fin = implode(',', array_fill(0, count($fids), '?'));
    $fnq = $pdo->prepare("SELECT id, name FROM coding_facilities WHERE id IN ($fin)");
    $fnq->execute($fids);
    foreach ($fnq as $r) $facNames[(int)$r['id']] = $r['name'];
}
$facName = fn($fid) => (int)$fid ? ($facNames[(int)$fid] ?? ('#' . (int)$fid)) : '—';
/* the facilities a provider has (BS or inventory), and the scribes within one (provider, facility) */
$facsOf    = fn($pid) => array_keys(($bs[$pid] ?? []) + ($inv[$pid] ?? []));
$scribesOf = fn($pid, $fid) => array_keys(($bs[$pid][$fid] ?? []) + ($inv[$pid][$fid] ?? []));

/* provider + scribe names — resolve the FULL in-scope sets so the filter dropdowns label correctly even when pruned */
$names = [];
$idSet = $allProvIds + $allScribeIds;
if ($idSet) {
    $ids = array_keys($idSet); $in = implode(',', array_fill(0, count($ids), '?'));
    $ns = $pdo->prepare("SELECT id, name FROM coding_providers WHERE id IN ($in)");
    $ns->execute($ids);
    foreach ($ns as $r) $names[(int)$r['id']] = $r['name'];
}
$providers = array_keys($provIds);
// 0 = scribe sheets not yet mapped to a billing provider (no matching chart) — sort it last.
usort($providers, function ($a, $b) use ($names) { if ($a === 0) return 1; if ($b === 0) return -1; return strcasecmp($names[$a] ?? '', $names[$b] ?? ''); });

/* Provider / Scribe filter dropdown options — every provider / scribe present in this scope + range (BEFORE the
   provider/scribe filter), name-sorted. */
$provFilterOpts = []; foreach (array_keys($allProvIds)   as $pid) if ($pid) $provFilterOpts[$pid]   = $names[$pid] ?? ('#' . $pid);
$scribeFilterOpts = []; foreach (array_keys($allScribeIds) as $sid) if ($sid) $scribeFilterOpts[$sid] = $names[$sid] ?? ('#' . $sid);
asort($provFilterOpts, SORT_STRING | SORT_FLAG_CASE); asort($scribeFilterOpts, SORT_STRING | SORT_FLAG_CASE);

/* GRAND TOTAL for the currently-applied filters — per day, per metric, summed across every (provider, facility,
   scribe) that survived the filters. Variance = Inventory − Completed. Used by both the on-screen pivot + the export. */
$grand = []; $grandAny = false;
foreach ($providers as $gp) foreach ($facsOf($gp) as $gf) foreach ($scribesOf($gp, $gf) as $gs) foreach ($days as $gd) {
    $bC = (int) ($bs[$gp][$gf][$gs][$gd] ?? 0);
    $iv = $inv[$gp][$gf][$gs][$gd] ?? null;
    if (!$bC && !$iv) continue;
    $grandAny = true;
    $grand['bs'][$gd] = ($grand['bs'][$gd] ?? 0) + $bC;
    if ($iv) foreach (['inv','comp','pend','hold','rfi','dis'] as $gk) $grand[$gk][$gd] = ($grand[$gk][$gd] ?? 0) + (int) $iv[$gk];
}
foreach ($days as $gd) $grand['var'][$gd] = (int) ($grand['inv'][$gd] ?? 0) - (int) ($grand['comp'][$gd] ?? 0);
/* billing-status grand totals (per day) — provider-level, summed across every displayed provider/facility */
foreach ($providers as $gp) foreach ($facsOf($gp) as $gf) foreach ($days as $gd)
    if (isset($bstat[$gp][$gf][$gd])) { $grandAny = true; foreach (['received','billed','unbilled','hold_deductible','yet_to_bill'] as $bk) $grand[$bk][$gd] = ($grand[$bk][$gd] ?? 0) + (int) ($bstat[$gp][$gf][$gd][$bk] ?? 0); }
$billDefs = [['Received by billing','received'],['Billed','billed'],['Unbilled','unbilled'],['Billed – On Hold Deductible','hold_deductible'],['Yet to be billed','yet_to_bill']];

/* filter option source — the cascade tree limited to what this user may see (coder = their scope, sup/sysadmin = all) */
$tree = coding_accessible_tree($uid);
$scopeLabel = ($scopeUserIds === null) ? 'all clients (admin)' : ($canSupervise ? 'your team’s aligned clients' : 'your aligned clients only');

/* labels shared by the page header and the Excel export */
$provName    = fn($pid) => $pid === 0 ? '⚠ Scribe sheets — not yet coded' : ($names[$pid] ?? ('#' . $pid));
$scribeLabel = fn($pid, $sid) => $sid ? ($names[$sid] ?? ('#' . $sid)) : ($pid === 0 ? 'unmapped' : '— direct (no scribe) —');

/* ============================================================================================================
 * ALTERNATE VIEWS (tabs) — the SAME 13 metrics pivoted three more ways: by DATE, by CLIENT, and as a collapsible
 * CLIENT » PROCESS » FACILITY » PROVIDER tree. Built from the same three sources (BS sheets, inventory, billing
 * disposition) under the same scope + client/process/facility + date filters. The provider/scribe sub-filters are
 * NOT applied here (those drill the main "By provider" pivot); these tabs are cross-provider overviews.
 * ========================================================================================================== */
$METRICS = [
    ['Count - BS','bs'],['Inventory','inv'],['Completed','comp'],['Pending','pend'],['Hold','hold'],['RFI','rfi'],
    ['Disregard','dis'],['Variance','var'],['Received by billing','received'],['Billed','billed'],['Unbilled','unbilled'],
    ['Billed – On Hold Deductible','hold_deductible'],['Yet to be billed','yet_to_bill'],
];
$mBase = ['bs'=>0,'inv'=>0,'comp'=>0,'pend'=>0,'hold'=>0,'rfi'=>0,'dis'=>0,'received'=>0,'billed'=>0,'unbilled'=>0,'hold_deductible'=>0];
$mval = function (array $m, string $k): int {                                     // derive Variance + Yet-to-be-billed on read
    if ($k === 'var')         return (int)($m['inv'] ?? 0) - (int)($m['comp'] ?? 0);
    if ($k === 'yet_to_bill') return max(0, (int)($m['received'] ?? 0) - (int)($m['billed'] ?? 0) - (int)($m['unbilled'] ?? 0) - (int)($m['hold_deductible'] ?? 0));
    return (int)($m[$k] ?? 0);
};
$mSum = function (array $rows) use ($mBase): array { $o = $mBase; foreach ($rows as $r) foreach ($mBase as $k => $_) $o[$k] += (int)($r[$k] ?? 0); return $o; };

/* [client_id][process_id][facility_id][billing_provider] => base metric map */
$agg = [];
$aggAdd = function ($cid, $pcid, $fid, $prov, $k, $v) use (&$agg, $mBase) {
    if (!isset($agg[$cid][$pcid][$fid][$prov])) $agg[$cid][$pcid][$fid][$prov] = $mBase;
    $agg[$cid][$pcid][$fid][$prov][$k] += (int) $v;
};
$aq1 = $pdo->prepare("SELECT client_id cid, process_id pcid, facility_id fid, COALESCE(billing_provider_id,coded_provider_id,provider_id) prov,
        COUNT(*) inv, SUM(status='completed') comp, SUM(status IN('pending','in_progress')) pend, SUM(status='hold') hold, SUM(status='rfi') rfi, SUM(status='disregard') dis
    FROM coding_inventory ci WHERE COALESCE(dos,record_sign_date) BETWEEN ? AND ?$fwSql AND COALESCE(dos,record_sign_date) IS NOT NULL" . $scopeSql('ci') . "
    GROUP BY client_id, process_id, facility_id, prov");
$aq1->execute(array_merge([$from, $to], $fp));
foreach ($aq1 as $r) foreach (['inv','comp','pend','hold','rfi','dis'] as $k) $aggAdd((int)$r['cid'],(int)$r['pcid'],(int)$r['fid'],(int)$r['prov'],$k,$r[$k]);
$aq2 = $pdo->prepare("SELECT ci.client_id cid, ci.process_id pcid, ci.facility_id fid, COALESCE(ci.billing_provider_id,ci.coded_provider_id,ci.provider_id) prov,
        COUNT(*) received, SUM(ba.billing_status='billed') billed, SUM(ba.billing_status='unbilled') unbilled, SUM(ba.billing_status='hold_deductible') hold_deductible
    FROM coding_inventory ci LEFT JOIN billing_allocation ba ON ba.inventory_id=ci.id
    WHERE ci.released_to_billing_at IS NOT NULL AND COALESCE(ci.billing_status,'pending')<>'void' AND COALESCE(ci.release_destination,'internal')='internal'
      AND COALESCE(ci.dos,ci.record_sign_date) BETWEEN ? AND ?$fwSql AND COALESCE(ci.dos,ci.record_sign_date) IS NOT NULL" . $scopeSql('ci') . "
    GROUP BY ci.client_id, ci.process_id, ci.facility_id, prov");
$aq2->execute(array_merge([$from, $to], $fp));
foreach ($aq2 as $r) foreach (['received','billed','unbilled','hold_deductible'] as $k) $aggAdd((int)$r['cid'],(int)$r['pcid'],(int)$r['fid'],(int)$r['prov'],$k,$r[$k]);
$aq3 = $pdo->prepare("SELECT cid, pcid, fid, prov, SUM(cnt) cnt FROM (
        SELECT client_id cid, process_id pcid, facility_id fid, provider_id prov, patient_count cnt
        FROM coding_billing_sheets WHERE provider_id IS NOT NULL AND sheet_date BETWEEN ? AND ?$fwSql AND patient_count IS NOT NULL" . $scopeSql('coding_billing_sheets') . "
        UNION ALL
        SELECT cbs.client_id, cbs.process_id, cbs.facility_id,
            COALESCE((SELECT COALESCE(ci.billing_provider_id,ci.coded_provider_id,ci.provider_id) bp FROM coding_inventory ci
                WHERE ci.client_id=cbs.client_id AND ci.process_id=cbs.process_id AND ci.facility_id=cbs.facility_id
                  AND ci.rendering_provider_id=cbs.scribe_id AND COALESCE(ci.dos,ci.record_sign_date)=cbs.sheet_date
                GROUP BY bp ORDER BY COUNT(*) DESC, bp LIMIT 1),0),
            cbs.patient_count
        FROM coding_billing_sheets cbs WHERE cbs.provider_id IS NULL AND cbs.scribe_id IS NOT NULL AND cbs.sheet_date BETWEEN ? AND ?$fwSql AND cbs.patient_count IS NOT NULL" . $scopeFacility('cbs') . "
    ) x GROUP BY cid, pcid, fid, prov");
$aq3->execute(array_merge([$from, $to], $fp, [$from, $to], $fp));
foreach ($aq3 as $r) $aggAdd((int)$r['cid'],(int)$r['pcid'],(int)$r['fid'],(int)$r['prov'],'bs',$r['cnt']);

/* client + process names for the alternate views */
$cliName = []; $procName = [];
if ($cids = array_keys($agg)) {
    $cin = implode(',', array_fill(0, count($cids), '?'));
    $cq = $pdo->prepare("SELECT id, name FROM clients WHERE id IN ($cin)"); $cq->execute($cids);
    foreach ($cq as $r) $cliName[(int)$r['id']] = $r['name'];
    $pcids = [];
    foreach ($agg as $procs) foreach (array_keys($procs) as $pc) $pcids[(int)$pc] = 1;
    if ($pcids) { $pk = array_keys($pcids); $pin = implode(',', array_fill(0, count($pk), '?'));
        $pq = $pdo->prepare("SELECT id, name FROM processes WHERE id IN ($pin)"); $pq->execute($pk);
        foreach ($pq as $r) $procName[(int)$r['id']] = $r['name']; }
}
$cliLabel  = fn($id) => (int)$id ? ($cliName[(int)$id] ?? ('Client #' . (int)$id)) : '—';
$procLabel = fn($id) => (int)$id ? ($procName[(int)$id] ?? ('Process #' . (int)$id)) : '—';

/* client-level totals (By client view) + a grand total across everything shown */
$clientTot = [];
foreach ($agg as $cid => $procs) { $leaves = [];
    foreach ($procs as $fs) foreach ($fs as $pv) foreach ($pv as $m) $leaves[] = $m;
    $clientTot[$cid] = $mSum($leaves); }
$clientOrder = array_keys($clientTot);
usort($clientOrder, fn($a, $b) => strcasecmp($cliLabel($a), $cliLabel($b)));
$gTot = $mSum(array_values($clientTot));

/* emits ONE hierarchy row (client/process/facility/provider) with its 13 metric cells */
$treeRow = function (string $path, ?string $parent, int $level, string $label, array $m, bool $hasChild) use ($METRICS, $mval) {
    $pad = 8 + $level * 20;
    echo '<tr class="cir-lv' . $level . '" data-row="' . e($path) . '"' . ($parent !== null ? ' data-parent="' . e($parent) . '"' : '')
        . ' data-level="' . $level . '"' . ($hasChild ? ' data-haschild="1" data-open="0"' : '') . ($level > 0 ? ' style="display:none"' : '') . '>';
    echo '<td class="cir-lbl" style="padding-left:' . $pad . 'px">';
    if ($hasChild) echo '<span class="cir-tw" role="button" tabindex="0" aria-label="Expand"><span class="cir-chev">▸</span></span> ';
    echo '<span class="' . ($level === 0 ? 'fw-bold' : '') . '">' . e($label) . '</span></td>';
    foreach ($METRICS as [$ml, $mk]) { $v = $mval($m, $mk); echo '<td class="text-center' . ($mk === 'var' && $v ? ' cir-var' : '') . '">' . ($v ?: '') . '</td>'; }
    echo '</tr>';
};

/* ---------------- Excel export (current pivot + analytics insights) ----------------
 * ?export=xlsx streams a multi-sheet workbook: the on-screen reconciliation pivot verbatim,
 * plus rolled-up analytics (summary KPIs, per-provider, per-scribe, daily trend, variance
 * exceptions) — all computed from the same $bs / $inv structures the page already built. */
if (($_GET['export'] ?? '') === 'xlsx') {
    require_once __DIR__ . '/../includes/xlsx.php';

    /* ---- single pass over every (provider, FACILITY, scribe, day) cell → all aggregates ----
     * Variance = Inventory − Completed (the outstanding, not-yet-completed count). Since Completed ≤ Inventory,
     * variance is always ≥ 0; a 0 means every chart in that cell is completed. */
    $blank   = fn() => array_fill_keys(['bs','inv','comp','pend','hold','rfi','dis'], 0);
    $g       = $blank();                                                             // grand totals
    $pfAgg   = [];                                                                   // per (billing provider, facility)
    $scrAgg  = [];                                                                   // per scribe (0 = direct/no-scribe)
    $dayAgg  = []; foreach ($days as $d) $dayAgg[$d] = $blank();                      // per service day
    $pfDay   = [];                                                                   // [$pid][$fid][$d] => inv/comp/bs, for day-variance counting
    $exceptions = [];                                                                // cells with outstanding work (inv − comp ≠ 0)
    $cellsActive = 0; $cellsBalanced = 0; $totAbsVar = 0;

    foreach ($providers as $pid) {
        foreach ($facsOf($pid) as $fid) {
            if (!isset($pfAgg[$pid][$fid])) $pfAgg[$pid][$fid] = $blank() + ['daysVar' => 0];
            foreach ($scribesOf($pid, $fid) as $sid) {
                if (!isset($scrAgg[$sid])) $scrAgg[$sid] = $blank();
                foreach ($days as $d) {
                    $bC = (int) ($bs[$pid][$fid][$sid][$d] ?? 0);
                    $iv = $inv[$pid][$fid][$sid][$d] ?? null;
                    $iC = $iv ? (int) $iv['inv'] : 0;
                    $cC = $iv ? (int) $iv['comp'] : 0;
                    if (!$bC && !$iv) continue;                                      // no activity in this cell
                    $var = $iC - $cC;                                                // Variance = Inventory − Completed
                    $cellsActive++; $totAbsVar += abs($var); if ($var === 0) $cellsBalanced++;

                    $g['bs'] += $bC; $g['inv'] += $iC;
                    $pfAgg[$pid][$fid]['bs'] += $bC; $pfAgg[$pid][$fid]['inv'] += $iC;
                    $scrAgg[$sid]['bs']  += $bC; $scrAgg[$sid]['inv']  += $iC;
                    $dayAgg[$d]['bs']    += $bC; $dayAgg[$d]['inv']    += $iC;
                    $pfDay[$pid][$fid][$d]['inv']  = ($pfDay[$pid][$fid][$d]['inv']  ?? 0) + $iC;
                    $pfDay[$pid][$fid][$d]['comp'] = ($pfDay[$pid][$fid][$d]['comp'] ?? 0) + $cC;
                    $pfDay[$pid][$fid][$d]['bs']   = ($pfDay[$pid][$fid][$d]['bs']   ?? 0) + $bC;
                    if ($iv) foreach (['comp','pend','hold','rfi','dis'] as $k) {
                        $g[$k] += (int) $iv[$k]; $pfAgg[$pid][$fid][$k] += (int) $iv[$k];
                        $scrAgg[$sid][$k] += (int) $iv[$k]; $dayAgg[$d][$k] += (int) $iv[$k];
                    }
                    if ($var !== 0) $exceptions[] = ['d'=>$d,'pid'=>$pid,'fid'=>$fid,'sid'=>$sid,'bs'=>$bC,'inv'=>$iC,'var'=>$var,
                        'comp'=>$cC,'pend'=>$iv?(int)$iv['pend']:0,'hold'=>$iv?(int)$iv['hold']:0,
                        'rfi'=>$iv?(int)$iv['rfi']:0,'dis'=>$iv?(int)$iv['dis']:0];
                }
            }
        }
    }
    foreach ($pfDay as $pid => $ff) foreach ($ff as $fid => $dd) foreach ($dd as $pv)
        if (($pv['inv'] || $pv['comp']) && ($pv['inv'] - $pv['comp']) !== 0) $pfAgg[$pid][$fid]['daysVar']++;

    $pct = fn($num, $den) => $den ? round(100 * $num / $den, 1) : 0;
    $x = new SimpleXlsx();

    /* ---- Sheet 1: the on-screen pivot, verbatim — one bunch per (billing provider, FACILITY) ---- */
    $cfHeader = array_merge(['Billing provider','Facility','Scribe','Type'], array_map(fn($d) => date('j-M', strtotime($d)), $days), ['Total']);
    $cfRows = [];
    $metricDefs = [['Count - BS','bs'],['Inventory','inv'],['Completed','comp'],['Pending','pend'],['Hold','hold'],['RFI','rfi'],['Disregard','dis'],['Variance','var']];
    foreach ($providers as $pid) {
        $pname = $provName($pid);
        $provFirst = true;
        foreach ($facsOf($pid) as $fid) {
            $fname = $facName($fid);
            $sids = $scribesOf($pid, $fid);
            usort($sids, function ($a, $b) use ($names) { if ($a === 0) return -1; if ($b === 0) return 1; return strcasecmp($names[$a] ?? '', $names[$b] ?? ''); });
            $facFirst = true;
            foreach ($sids as $sid) {
                $sLabel = $scribeLabel($pid, $sid);
                $first = true;
                foreach ($metricDefs as [$mlabel, $mkey]) {
                    $row = [($provFirst && $facFirst && $first) ? $pname : '', ($facFirst && $first) ? $fname : '', $first ? $sLabel : '', $mlabel];
                    $rt = 0; $anyV = false;
                    foreach ($days as $d) {
                        if ($mkey === 'bs')      { $v = $bs[$pid][$fid][$sid][$d] ?? null; }
                        elseif ($mkey === 'var') { $iv = $inv[$pid][$fid][$sid][$d] ?? null; $bC = (int) ($bs[$pid][$fid][$sid][$d] ?? 0);
                                                   $v = ($iv || $bC) ? (($iv ? (int) $iv['inv'] : 0) - ($iv ? (int) $iv['comp'] : 0)) : null; }
                        else                     { $v = isset($inv[$pid][$fid][$sid][$d]) ? (int) $inv[$pid][$fid][$sid][$d][$mkey] : null; }
                        if ($v !== null) { $rt += (int) $v; $anyV = true; }
                        $row[] = $v === null ? '' : (int) $v;
                    }
                    $row[] = $anyV ? $rt : '';
                    $cfRows[] = $row; $first = false;
                }
                $facFirst = false;
            }
            if ($pid !== 0 && !empty($bstat[$pid][$fid])) foreach ($billDefs as $bi => [$blabel, $bkey]) {   // billing status (provider-level)
                $row = ['', '', $bi === 0 ? 'billing status' : '', $blabel]; $rt = 0;
                foreach ($days as $d) { $bv = (int) ($bstat[$pid][$fid][$d][$bkey] ?? 0); $rt += $bv; $row[] = $bv ?: ''; }
                $row[] = $rt ?: '';
                $cfRows[] = $row;
            }
            $cfRows[] = array_fill(0, 5 + count($days), '');                         // separator between (provider, facility) bunches
            $provFirst = false;
        }
    }
    if ($grandAny) {   // GRAND TOTAL for the applied filters (coding metrics + billing status)
        foreach ($metricDefs as $gi => [$glabel, $gkey]) {
            $row = [$gi === 0 ? 'GRAND TOTAL (filtered)' : '', '', '', $glabel]; $rt = 0;
            foreach ($days as $d) { $gv = (int) ($grand[$gkey][$d] ?? 0); $rt += $gv; $row[] = $gv ?: ''; }
            $row[] = $rt ?: '';
            $cfRows[] = $row;
        }
        foreach ($billDefs as $bi => [$blabel, $bkey]) {
            $row = ['', '', $bi === 0 ? 'billing status' : '', $blabel]; $rt = 0;
            foreach ($days as $d) { $gv = (int) ($grand[$bkey][$d] ?? 0); $rt += $gv; $row[] = $gv ?: ''; }
            $row[] = $rt ?: '';
            $cfRows[] = $row;
        }
    }
    $x->addSheet('Reconciliation', $cfHeader, $cfRows);

    /* ---- Sheet 2: Summary — report context + grand-total KPIs ---- */
    $fLabel = function ($table, $id) use ($pdo) { if (!$id) return ''; $s = $pdo->prepare("SELECT name FROM $table WHERE id=?"); $s->execute([$id]); return (string) $s->fetchColumn(); };
    $x->addSheet('Summary', ['Metric','Value'], [
        ['Report', 'Coding — Reconciliation (billing-sheet count vs coded inventory)'],
        ['Date range', $from . '  to  ' . $to],
        ['Days in range', count($days)],
        ['Scope', $scopeLabel],
        ['Client filter', $fLabel('clients', $fClient) ?: 'All clients'],
        ['Process filter', $fLabel('processes', $fProcess) ?: 'All processes'],
        ['Facility filter', $fLabel('coding_facilities', $fFacility) ?: 'All facilities'],
        ['Generated at', date('Y-m-d H:i')],
        ['', ''],
        ['Billing providers', count(array_filter($providers, fn($p) => $p !== 0))],
        ['Scribes involved', count(array_filter(array_keys($scrAgg), fn($s) => $s !== 0))],
        ['', ''],
        ['Total BS count', $g['bs']],
        ['Total inventory (coded charts)', $g['inv']],
        ['Total variance (Inventory − Completed)', $g['inv'] - $g['comp']],
        ['Total absolute variance', $totAbsVar],
        ['', ''],
        ['Completed', $g['comp']],
        ['Pending', $g['pend']],
        ['Hold', $g['hold']],
        ['RFI', $g['rfi']],
        ['Disregard', $g['dis']],
        ['', ''],
        ['Received by billing', array_sum($grand['received'] ?? [])],
        ['Billed', array_sum($grand['billed'] ?? [])],
        ['Unbilled', array_sum($grand['unbilled'] ?? [])],
        ['Billed – On Hold Deductible', array_sum($grand['hold_deductible'] ?? [])],
        ['Yet to be billed', array_sum($grand['yet_to_bill'] ?? [])],
        ['', ''],
        ['Completion rate %', $pct($g['comp'], $g['inv'])],
        ['Cells fully completed %', $pct($cellsBalanced, $cellsActive)],
        ['Active cells (provider×facility×scribe×day)', $cellsActive],
        ['Cells with outstanding work (inv ≠ comp)', count($exceptions)],
    ]);

    /* ---- Sheet 3: Provider analytics — one row per billing provider ---- */
    $provRows = [];
    foreach ($providers as $pid) {                                                   // $providers already sorted (unmapped 0 last)
        foreach ($facsOf($pid) as $fid) {
            $a = $pfAgg[$pid][$fid] ?? ($blank() + ['daysVar' => 0]);
            $net = $a['inv'] - $a['comp'];                                            // outstanding = Inventory − Completed (always ≥ 0)
            $status = $pid === 0 ? 'Unmapped scribe sheets (not yet coded)'
                    : ($net === 0 ? 'All completed' : $net . ' outstanding (pending / hold / RFI / disregard)');
            $bT = ['received'=>0,'billed'=>0,'unbilled'=>0,'hold_deductible'=>0,'yet_to_bill'=>0];   // billing status totals for this (provider, facility)
            foreach (($bstat[$pid][$fid] ?? []) as $dd) foreach (['received','billed','unbilled','hold_deductible','yet_to_bill'] as $bk) $bT[$bk] += (int) ($dd[$bk] ?? 0);
            $provRows[] = [$provName($pid), $facName($fid), $a['bs'], $a['inv'], $net, $a['comp'], $a['pend'], $a['hold'], $a['rfi'], $a['dis'], $bT['received'], $bT['billed'], $bT['unbilled'], $bT['hold_deductible'], $bT['yet_to_bill'], $pct($a['comp'], $a['inv']), $a['daysVar'], $status];
        }
    }
    $gReceived = array_sum($grand['received'] ?? []); $gBilled = array_sum($grand['billed'] ?? []); $gUnbilled = array_sum($grand['unbilled'] ?? []); $gHold = array_sum($grand['hold_deductible'] ?? []); $gYet = array_sum($grand['yet_to_bill'] ?? []);
    $provRows[] = ['TOTAL', '', $g['bs'], $g['inv'], $g['inv'] - $g['comp'], $g['comp'], $g['pend'], $g['hold'], $g['rfi'], $g['dis'], $gReceived, $gBilled, $gUnbilled, $gHold, $gYet, $pct($g['comp'], $g['inv']), '', ''];
    $x->addSheet('Provider–facility analytics',
        ['Billing provider','Facility','Total BS','Total inventory','Total variance','Completed','Pending','Hold','RFI','Disregard','Received by billing','Billed','Unbilled','On-Hold-Deductible','Yet to be billed','Completion %','Days w/ variance','Status'],
        $provRows);

    /* ---- Sheet 4: Scribe analytics — one row per scribe (incl. direct work) ---- */
    $scrKeys = array_keys($scrAgg);
    usort($scrKeys, function ($a, $b) use ($names) { if ($a === 0) return 1; if ($b === 0) return -1; return strcasecmp($names[$a] ?? '', $names[$b] ?? ''); });
    $scrRows = [];
    foreach ($scrKeys as $sid) { $a = $scrAgg[$sid];
        $label = $sid ? ($names[$sid] ?? ('#' . $sid)) : 'Direct (no scribe)';
        $scrRows[] = [$label, $a['bs'], $a['inv'], $a['inv'] - $a['comp'], $a['comp'], $a['pend'], $a['hold'], $a['rfi'], $a['dis'], $pct($a['comp'], $a['inv'])];
    }
    $x->addSheet('Scribe analytics',
        ['Scribe','Total BS','Total inventory','Total variance','Completed','Pending','Hold','RFI','Disregard','Completion %'],
        $scrRows);

    /* ---- Sheet 5: Daily trend — grand totals per service day ---- */
    $dayRows = [];
    foreach ($days as $d) { $a = $dayAgg[$d];
        $dayRows[] = [$d, $a['bs'], $a['inv'], $a['inv'] - $a['comp'], $a['comp'], $a['pend'], $a['hold'], $a['rfi'], $a['dis'], $pct($a['comp'], $a['inv'])];
    }
    $x->addSheet('Daily trend',
        ['Date','BS count','Inventory','Total variance','Completed','Pending','Hold','RFI','Disregard','Completion %'],
        $dayRows);

    /* ---- Sheet 6: Variance exceptions — every mismatched cell, worst first ---- */
    usort($exceptions, fn($a, $b) => (abs($b['var']) <=> abs($a['var'])) ?: strcmp($a['d'], $b['d']));
    $excRows = [];
    foreach ($exceptions as $ex) {
        $cause = $ex['pid'] === 0 ? 'Scribe sheet not yet mapped to a billing provider (no matching coded chart)'
               : ($ex['var'] . ' chart(s) not yet completed (pending / hold / RFI / disregard)');
        $excRows[] = [$ex['d'], $provName($ex['pid']), $facName($ex['fid']), $scribeLabel($ex['pid'], $ex['sid']), $ex['bs'], $ex['inv'], $ex['var'],
                      $ex['comp'], $ex['pend'], $ex['hold'], $ex['rfi'], $ex['dis'], $cause];
    }
    if (!$excRows) $excRows[] = ['—', 'Nothing outstanding — every active cell is fully completed', '', '', '', '', '', '', '', '', '', '', ''];
    $x->addSheet('Outstanding (variance)',
        ['Date','Billing provider','Facility','Scribe','BS count','Inventory','Variance (Inv − Comp)','Completed','Pending','Hold','RFI','Disregard','Note'],
        $excRows);

    $x->download('reconciliation-' . $from . '_' . $to . '.xlsx');
}

$pageTitle = 'Reconciliation report';
$expUrl = url('/admin/coding-inventory-report.php') . '?' . http_build_query(array_merge(
    array_filter(['client_id' => $fClient, 'process_id' => $fProcess, 'facility_id' => $fFacility, 'provider_id' => $fProvider, 'scribe_id' => $fScribe]),
    ['from' => $from, 'to' => $to, 'export' => 'xlsx']));
require __DIR__ . '/../includes/header.php';
?>
<div class="page-header"><div class="container-xl"><div class="row align-items-center">
    <div class="col"><h2 class="page-title"><i class="ti ti-report-analytics me-2 text-primary"></i>Reconciliation report</h2>
        <div class="text-secondary small">Billing-sheet patient count vs coded inventory, per billing provider, scribe &amp; facility, by date of service. Showing <strong><?= e($scopeLabel) ?></strong>.</div></div>
</div></div></div>
<div class="page-body"><div class="container-xl">

    <div class="card mb-3"><div class="card-body py-2">
        <form method="GET" class="row g-2 align-items-end">
            <div class="col-md-2"><label class="form-label small mb-1">Client</label>
                <select name="client_id" id="r_client" class="form-select form-select-sm"><option value="">All clients</option></select></div>
            <div class="col-md-2"><label class="form-label small mb-1">Process</label>
                <select name="process_id" id="r_process" class="form-select form-select-sm"><option value="">All processes</option></select></div>
            <div class="col-md-2"><label class="form-label small mb-1">Facility</label>
                <select name="facility_id" id="r_facility" class="form-select form-select-sm"><option value="">All facilities</option></select></div>
            <div class="col-md-2"><label class="form-label small mb-1">Provider</label>
                <select name="provider_id" class="form-select form-select-sm"><option value="">All providers</option>
                    <?php foreach ($provFilterOpts as $pid => $pn): ?><option value="<?= (int)$pid ?>" <?= $fProvider===(int)$pid?'selected':'' ?>><?= e($pn) ?></option><?php endforeach; ?></select></div>
            <div class="col-md-2"><label class="form-label small mb-1">Scribe</label>
                <select name="scribe_id" class="form-select form-select-sm"><option value="">All scribes</option>
                    <?php foreach ($scribeFilterOpts as $sid => $sn): ?><option value="<?= (int)$sid ?>" <?= $fScribe===(int)$sid?'selected':'' ?>><?= e($sn) ?></option><?php endforeach; ?></select></div>
            <div class="col-md-2"><label class="form-label small mb-1">Date from</label><input type="date" name="from" value="<?= e($from) ?>" class="form-control form-control-sm"></div>
            <div class="col-md-2"><label class="form-label small mb-1">Date to</label><input type="date" name="to" value="<?= e($to) ?>" class="form-control form-control-sm"></div>
            <div class="col-md-2"><button class="btn btn-sm btn-primary"><i class="ti ti-filter me-1"></i>Run</button>
                <a href="<?= e(url('/admin/coding-inventory-report.php')) ?>" class="btn btn-sm btn-ghost-secondary">Clear</a></div>
        </form>
        <div class="form-hint mt-1">Range capped at <?= $MAX_DAYS ?> days. Inventory is matched to the sheet day by <strong>date of service</strong> (or record-sign date for charts not yet coded), and split into Completed / Pending / Hold / RFI / Disregard. <strong>Pending = still to be coded (yet to work)</strong>; <strong>Variance = Inventory − Completed</strong> (the whole not-yet-completed backlog). Each <strong>Provider–Facility</strong> is its own bunch. The billing rows show <strong>Received by billing</strong> (charts handed to the billing team), the biller's <strong>Billed / Unbilled / On-Hold-Deductible</strong> disposition of them, and <strong>Yet to be billed</strong> (received but not yet actioned by a biller = Received − Billed − Unbilled − On-Hold-Deductible) — always rolled up to the <strong>billing provider</strong>, regardless of scribe. The rightmost <strong>Total</strong> column sums every row across the range.</div>
    </div></div>

    <div class="card">
        <div class="card-header py-2 d-flex flex-wrap align-items-center gap-2">
            <h3 class="card-title mb-0"><?= e(fmt_date($from)) ?> → <?= e(fmt_date($to)) ?></h3>
            <div class="btn-group btn-group-sm ms-1" role="tablist" id="cirTabs">
                <button type="button" class="btn btn-primary cir-tab active" data-pane="provider"><i class="ti ti-user me-1"></i>By provider</button>
                <button type="button" class="btn btn-outline-primary cir-tab" data-pane="date"><i class="ti ti-calendar me-1"></i>By date</button>
                <button type="button" class="btn btn-outline-primary cir-tab" data-pane="client"><i class="ti ti-building me-1"></i>By client</button>
                <button type="button" class="btn btn-outline-primary cir-tab" data-pane="tree"><i class="ti ti-sitemap me-1"></i>Hierarchy</button>
            </div>
            <a href="<?= e($expUrl) ?>" class="btn btn-sm btn-outline-success ms-auto"><i class="ti ti-file-spreadsheet me-1"></i>Excel</a></div>
        <div class="cir-pane" data-pane="provider"><div class="table-responsive">
        <table class="table table-sm table-bordered card-table cir-tbl mb-0">
            <thead><tr>
                <th style="min-width:150px">Billing provider</th><th style="min-width:130px">Facility</th><th style="min-width:120px">Scribe</th><th style="min-width:96px">Type</th>
                <?php foreach ($days as $d): ?><th class="text-center text-nowrap"><?= e(date('j-M', strtotime($d))) ?></th><?php endforeach; ?>
                <th class="text-center text-nowrap cir-tot" style="min-width:60px">Total</th>
            </tr></thead>
            <tbody>
            <?php if (!$providers): ?>
                <tr><td colspan="<?= 5 + count($days) ?>" class="text-center text-secondary py-4">No billing sheets or inventory in this range / filter.</td></tr>
            <?php else:
                $metricDefs = [['Count - BS','bs',false],['Inventory','inv',true],['Completed','comp',false],['Pending','pend',false],['Hold','hold',false],['RFI','rfi',false],['Disregard','dis',false],['Variance','var',true]];
                foreach ($providers as $pid):
                $pname = $pid === 0 ? '⚠ Scribe sheets — not yet coded' : ($names[$pid] ?? ('#' . $pid));
                $provFirst = true;
                // One bunch per (provider, FACILITY): the same provider at two facilities = two separate bunches.
                foreach ($facsOf($pid) as $fid):
                    $fname = $facName($fid);
                    // Within a bunch: sid=0 = direct (no scribe); each other sid = a scribe who worked under this
                    // billing provider AT this facility (from the coder's charts: rendering=scribe + billing=provider).
                    $sids = $scribesOf($pid, $fid);
                    usort($sids, function ($a, $b) use ($names) { if ($a === 0) return -1; if ($b === 0) return 1; return strcasecmp($names[$a] ?? '', $names[$b] ?? ''); });
                    $facFirst = true;
            ?>
                <?php foreach ($sids as $sid): ?>
                    <?php foreach ($metricDefs as $mi => [$mlabel, $mkey, $hi]): ?>
                    <tr class="<?= $mkey==='var'?'cir-ana':'' ?>">
                        <td class="cir-prov"><?= ($provFirst && $facFirst && $mi===0) ? '<strong>'.e($pname).'</strong>' : '' ?></td>
                        <td class="small"><?php if ($facFirst && $mi===0): echo e($fname); endif; ?></td>
                        <td class="small"><?php if ($mi===0): echo $sid
                            ? '<span class="badge bg-purple-lt"><i class="ti ti-pencil me-1"></i>'.e($names[$sid] ?? ('#'.$sid)).'</span>'
                            : ($pid===0 ? '<span class="text-secondary">unmapped</span>' : '<span class="text-secondary fst-italic">— direct (no scribe) —</span>'); endif; ?></td>
                        <td class="small text-nowrap <?= $hi?'fw-bold':'text-secondary' ?>"><?= $mlabel ?></td>
                        <?php $rowTot = 0; $anyVal = false; foreach ($days as $d):
                            if ($mkey === 'bs')        { $v = $bs[$pid][$fid][$sid][$d] ?? null; }
                            elseif ($mkey === 'var')   { $iv = $inv[$pid][$fid][$sid][$d] ?? null; $bC = (int)($bs[$pid][$fid][$sid][$d] ?? 0);
                                                         $v = ($iv || $bC) ? (($iv ? (int)$iv['inv'] : 0) - ($iv ? (int)$iv['comp'] : 0)) : null; }
                            else                       { $v = isset($inv[$pid][$fid][$sid][$d]) ? (int)$inv[$pid][$fid][$sid][$d][$mkey] : null; }
                            if ($v !== null) { $rowTot += (int)$v; $anyVal = true; }
                        ?>
                            <td class="text-center <?= $hi?'cir-hi':'' ?> <?= ($mkey==='var' && $v!==null && $v!==0)?'cir-var':'' ?>"><?= $v===null ? '' : (int)$v ?></td>
                        <?php endforeach; ?>
                            <td class="text-center fw-bold cir-tot <?= $hi?'cir-hi':'' ?> <?= ($mkey==='var' && $rowTot!==0)?'cir-var':'' ?>"><?= $anyVal ? (int)$rowTot : '' ?></td>
                    </tr>
                    <?php endforeach; $facFirst = false; ?>
                <?php endforeach; ?>
                <?php /* Billing status (biller's disposition) — provider-level, rolled up across scribes for this (provider, facility). */
                    if ($pid !== 0 && !empty($bstat[$pid][$fid])): foreach ($billDefs as $bi => [$blabel, $bkey]): ?>
                    <tr class="cir-bill">
                        <td class="cir-prov"></td><td></td>
                        <td class="small text-secondary"><?= $bi===0 ? '<i class="ti ti-receipt me-1"></i>billing status' : '' ?></td>
                        <td class="small text-nowrap fw-bold"><?= e($blabel) ?></td>
                        <?php $rowTot = 0; foreach ($days as $d): $bv = (int) ($bstat[$pid][$fid][$d][$bkey] ?? 0); $rowTot += $bv; ?>
                            <td class="text-center"><?= $bv ?: '' ?></td>
                        <?php endforeach; ?>
                            <td class="text-center fw-bold cir-tot"><?= $rowTot ?: '' ?></td>
                    </tr>
                    <?php endforeach; endif; ?>
                <tr class="cir-sep"><td colspan="<?= 5 + count($days) ?>"></td></tr>
                <?php $provFirst = false; endforeach; /* facilities */ ?>
            <?php endforeach; /* providers */ ?>
            <?php if ($grandAny): foreach ($metricDefs as $gi => [$glabel, $gkey, $ghi]): ?>
                <tr class="cir-grand">
                    <td class="cir-prov"><?= $gi===0 ? '<strong>GRAND TOTAL</strong> <span class="text-secondary" style="font-size:.7rem">(filtered)</span>' : '' ?></td>
                    <td></td><td></td>
                    <td class="small fw-bold text-nowrap"><?= $glabel ?></td>
                    <?php $rowTot = 0; foreach ($days as $d): $gv = (int) ($grand[$gkey][$d] ?? 0); $rowTot += $gv; ?>
                        <td class="text-center cir-hi <?= ($gkey==='var' && $gv!==0)?'cir-var':'' ?>"><?= $gv ?: '' ?></td>
                    <?php endforeach; ?>
                        <td class="text-center fw-bold cir-tot cir-hi <?= ($gkey==='var' && $rowTot!==0)?'cir-var':'' ?>"><?= $rowTot ?: '' ?></td>
                </tr>
                <?php endforeach; ?>
                <?php foreach ($billDefs as $bi => [$blabel, $bkey]): ?>
                <tr class="cir-grand">
                    <td class="cir-prov"></td><td></td>
                    <td class="small text-secondary"><?= $bi===0 ? 'billing status' : '' ?></td>
                    <td class="small fw-bold text-nowrap"><?= e($blabel) ?></td>
                    <?php $rowTot = 0; foreach ($days as $d): $gv = (int) ($grand[$bkey][$d] ?? 0); $rowTot += $gv; ?>
                        <td class="text-center cir-hi"><?= $gv ?: '' ?></td>
                    <?php endforeach; ?>
                        <td class="text-center fw-bold cir-tot cir-hi"><?= $rowTot ?: '' ?></td>
                </tr>
                <?php endforeach; endif; ?>
            <?php endif; /* !providers */ ?>
            </tbody>
        </table>
        </div></div><!-- /table-responsive /provider pane -->

        <?php /* ═══════════ BY DATE — metrics (rows) × day (columns) ═══════════ */ ?>
        <div class="cir-pane d-none" data-pane="date"><div class="table-responsive">
        <table class="table table-sm table-bordered card-table cir-tbl mb-0">
            <thead><tr><th class="cir-lbl" style="min-width:200px">Type</th>
                <?php foreach ($days as $d): ?><th class="text-center text-nowrap"><?= e(date('j-M', strtotime($d))) ?></th><?php endforeach; ?>
                <th class="text-center text-nowrap cir-tot" style="min-width:60px">Total</th></tr></thead>
            <tbody>
            <?php foreach ($METRICS as [$mlabel, $mkey]):
                $strong = in_array($mkey, ['inv','var'], true); $bill = in_array($mkey, ['received','billed','unbilled','hold_deductible','yet_to_bill'], true); $rowTot = 0; ?>
                <tr class="<?= $bill ? 'cir-bill' : '' ?> <?= $strong ? 'cir-strong' : '' ?>">
                    <td class="cir-lbl"><?= e($mlabel) ?></td>
                    <?php foreach ($days as $d): $v = (int) ($grand[$mkey][$d] ?? 0); $rowTot += $v; ?>
                        <td class="text-center <?= ($mkey === 'var' && $v) ? 'cir-var' : '' ?>"><?= $v ?: '' ?></td>
                    <?php endforeach; ?>
                    <td class="text-center fw-bold cir-tot <?= ($mkey === 'var' && $rowTot) ? 'cir-var' : '' ?>"><?= $rowTot ?: '' ?></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table></div></div>

        <?php /* ═══════════ BY CLIENT — metrics (rows) × client (columns) ═══════════ */ ?>
        <div class="cir-pane d-none" data-pane="client"><div class="table-responsive">
        <?php if (!$clientOrder): ?>
            <div class="text-center text-secondary py-4">No billing sheets or inventory in this range / filter.</div>
        <?php else: ?>
        <table class="table table-sm table-bordered card-table cir-tbl mb-0">
            <thead><tr><th class="cir-lbl" style="min-width:200px">Type</th>
                <?php foreach ($clientOrder as $cid): ?><th class="text-center text-nowrap"><?= e($cliLabel($cid)) ?></th><?php endforeach; ?>
                <th class="text-center text-nowrap cir-tot" style="min-width:60px">Total</th></tr></thead>
            <tbody>
            <?php foreach ($METRICS as [$mlabel, $mkey]):
                $strong = in_array($mkey, ['inv','var'], true); $bill = in_array($mkey, ['received','billed','unbilled','hold_deductible','yet_to_bill'], true); ?>
                <tr class="<?= $bill ? 'cir-bill' : '' ?> <?= $strong ? 'cir-strong' : '' ?>">
                    <td class="cir-lbl"><?= e($mlabel) ?></td>
                    <?php foreach ($clientOrder as $cid): $v = $mval($clientTot[$cid], $mkey); ?>
                        <td class="text-center <?= ($mkey === 'var' && $v) ? 'cir-var' : '' ?>"><?= $v ?: '' ?></td>
                    <?php endforeach; ?>
                    <?php $tv = $mval($gTot, $mkey); ?>
                    <td class="text-center fw-bold cir-tot <?= ($mkey === 'var' && $tv) ? 'cir-var' : '' ?>"><?= $tv ?: '' ?></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
        <?php endif; ?>
        </div></div>

        <?php /* ═══════════ HIERARCHY — Client » Process » Facility » Provider (collapsible) × metrics ═══════════ */ ?>
        <div class="cir-pane d-none" data-pane="tree"><div class="table-responsive">
        <div class="d-flex align-items-center gap-2 px-2 py-1 border-bottom small text-secondary">
            <i class="ti ti-sitemap"></i> Click a row to expand it.
            <button type="button" class="btn btn-sm btn-ghost-secondary ms-auto" id="cirTreeExpand"><i class="ti ti-arrows-maximize me-1"></i>Expand all</button>
            <button type="button" class="btn btn-sm btn-ghost-secondary" id="cirTreeCollapse"><i class="ti ti-arrows-minimize me-1"></i>Collapse all</button>
        </div>
        <table class="table table-sm table-bordered card-table cir-tbl cir-tree mb-0" id="cirTree">
            <thead><tr><th class="cir-lbl" style="min-width:260px">Client » Process » Facility » Provider</th>
                <?php foreach ($METRICS as [$mlabel, $mkey]): ?><th class="text-center text-nowrap"><?= e($mlabel) ?></th><?php endforeach; ?>
            </tr></thead>
            <tbody>
            <?php if (!$clientOrder): ?>
                <tr><td colspan="<?= 1 + count($METRICS) ?>" class="text-center text-secondary py-4">No billing sheets or inventory in this range / filter.</td></tr>
            <?php else: foreach ($clientOrder as $cid):
                $procs = $agg[$cid];
                $cLeaves = []; foreach ($procs as $fs) foreach ($fs as $pv) foreach ($pv as $m) $cLeaves[] = $m;
                $cpath = 'c' . $cid;
                $treeRow($cpath, null, 0, $cliLabel($cid), $mSum($cLeaves), !empty($procs));
                $procKeys = array_keys($procs); usort($procKeys, fn($a, $b) => strcasecmp($procLabel($a), $procLabel($b)));
                foreach ($procKeys as $pcid):
                    $facs = $procs[$pcid];
                    $pLeaves = []; foreach ($facs as $pv) foreach ($pv as $m) $pLeaves[] = $m;
                    $ppath = $cpath . '-p' . $pcid;
                    $treeRow($ppath, $cpath, 1, $procLabel($pcid), $mSum($pLeaves), !empty($facs));
                    $facKeys = array_keys($facs); usort($facKeys, fn($a, $b) => strcasecmp($facName($a), $facName($b)));
                    foreach ($facKeys as $fid):
                        $provs = $facs[$fid];
                        $fpath = $ppath . '-f' . $fid;
                        $treeRow($fpath, $ppath, 2, $facName($fid), $mSum(array_values($provs)), !empty($provs));
                        $provKeys = array_keys($provs); usort($provKeys, fn($a, $b) => strcasecmp($provName($a), $provName($b)));
                        foreach ($provKeys as $prov):
                            $treeRow($fpath . '-pr' . $prov, $fpath, 3, $provName($prov), $provs[$prov], false);
                        endforeach;
                    endforeach;
                endforeach;
            endforeach; endif; ?>
            </tbody>
        </table></div></div>
    </div>
</div></div>

<style>
.cir-tbl td, .cir-tbl th{ font-size:.72rem; padding:3px 7px; }
.cir-tbl thead th{ background:var(--m-surface,#f8fafc); position:sticky; top:0; z-index:1; }
.cir-tbl tr.cir-total td{ background:color-mix(in srgb,var(--cw-accent,#4f46e5) 8%,transparent); font-weight:600; border-top:2px solid var(--m-border-strong,#cbd5e1); }
.cir-tbl tr.cir-grand td{ background:color-mix(in srgb,var(--cw-accent,#4f46e5) 16%,transparent); font-weight:700; border-top:2px solid var(--cw-accent,#4f46e5); }
.cir-tbl tr.cir-bill td{ background:color-mix(in srgb,#16a34a 7%,transparent); }
.cir-tbl td.cir-hi{ background:#fff8c4; font-weight:600; }
.cir-tbl td.cir-var{ color:#b91c1c; }
.cir-tbl tr.cir-sep td{ height:8px; background:transparent; border-left:0; border-right:0; }
.cir-tbl .cir-tot{ border-left:2px solid var(--m-border-strong,#cbd5e1); }
.cir-tbl td.cir-prov{ vertical-align:top; }
.cir-tbl td.cir-lbl, .cir-tbl th.cir-lbl{ text-align:left; white-space:nowrap; }
.cir-tbl tr.cir-strong td{ font-weight:600; }
.cir-tw{ cursor:pointer; display:inline-block; width:1.1em; text-align:center; color:#64748b; user-select:none; border-radius:3px; }
.cir-tw:focus-visible{ outline:2px solid var(--cw-accent,#4f46e5); outline-offset:1px; }
.cir-chev{ font-size:.8em; }
.cir-tree tr.cir-lv0 td{ background:color-mix(in srgb,var(--cw-accent,#4f46e5) 11%,transparent); }
.cir-tree tr.cir-lv1 td{ background:color-mix(in srgb,var(--cw-accent,#4f46e5) 5%,transparent); }
.cir-tree tr.cir-lv2 td{ background:color-mix(in srgb,#94a3b8 8%,transparent); }
.cir-tree tr:hover td{ filter:brightness(0.98); }
</style>
<script>
(function(){
    // ── view tabs (By provider / By date / By client / Hierarchy) ──
    var tabs  = Array.prototype.slice.call(document.querySelectorAll('.cir-tab'));
    var panes = Array.prototype.slice.call(document.querySelectorAll('.cir-pane'));
    tabs.forEach(function(t){ t.addEventListener('click', function(){
        var pane = t.getAttribute('data-pane');
        tabs.forEach(function(x){ var on = x === t; x.classList.toggle('active', on); x.classList.toggle('btn-primary', on); x.classList.toggle('btn-outline-primary', !on); });
        panes.forEach(function(p){ p.classList.toggle('d-none', p.getAttribute('data-pane') !== pane); });
    }); });
    // ── hierarchy collapse / expand ──
    var tree = document.getElementById('cirTree');
    if (tree) {
        function esc(s){ return (window.CSS && CSS.escape) ? CSS.escape(s) : String(s).replace(/[^\w-]/g,'\\$&'); }
        function chev(r, open){ var c = r.querySelector('.cir-chev'); if (c) c.textContent = open ? '▾' : '▸'; }
        function expand(r){ tree.querySelectorAll('tr[data-parent="'+esc(r.dataset.row)+'"]').forEach(function(k){ k.style.display=''; }); r.setAttribute('data-open','1'); chev(r, true); }
        function collapse(r){ tree.querySelectorAll('tr[data-row^="'+esc(r.dataset.row)+'-"]').forEach(function(d){ d.style.display='none'; d.setAttribute('data-open','0'); chev(d, false); }); r.setAttribute('data-open','0'); chev(r, false); }
        tree.querySelectorAll('tr[data-haschild]').forEach(function(r){
            var tw = r.querySelector('.cir-tw'); if (!tw) return;
            function toggle(){ (r.getAttribute('data-open')==='1' ? collapse : expand)(r); }
            tw.addEventListener('click', function(e){ e.stopPropagation(); toggle(); });
            tw.addEventListener('keydown', function(e){ if (e.key==='Enter' || e.key===' ') { e.preventDefault(); toggle(); } });
        });
        var exp = document.getElementById('cirTreeExpand'), col = document.getElementById('cirTreeCollapse');
        if (exp) exp.addEventListener('click', function(){ tree.querySelectorAll('tr[data-row]').forEach(function(r){ r.style.display=''; }); tree.querySelectorAll('tr[data-haschild]').forEach(function(r){ r.setAttribute('data-open','1'); chev(r, true); }); });
        if (col) col.addEventListener('click', function(){ tree.querySelectorAll('tr[data-level]').forEach(function(r){ if (r.getAttribute('data-level') !== '0') r.style.display='none'; r.setAttribute('data-open','0'); chev(r, false); }); });
    }
})();
</script>
<script>
(function(){
    var TREE = <?= json_encode($tree, JSON_HEX_TAG|JSON_HEX_AMP|JSON_HEX_QUOT|JSON_HEX_APOS) ?>;
    var pre = { c:'<?= (int)$fClient ?>', p:'<?= (int)$fProcess ?>', f:'<?= (int)$fFacility ?>' };
    var elC=document.getElementById('r_client'), elP=document.getElementById('r_process'), elF=document.getElementById('r_facility');
    function opt(v,t){ var o=document.createElement('option'); o.value=v; o.textContent=t; return o; }
    function nm(x){ return (x && x.name!=null) ? x.name : x; }
    function fill(el, map, ph){ var keep=el.firstChild; el.innerHTML=''; el.appendChild(opt('', ph));
        Object.keys(map||{}).sort(function(a,b){ return String(nm(map[a])).localeCompare(String(nm(map[b]))); }).forEach(function(k){ el.appendChild(opt(k, nm(map[k]))); }); }
    Object.keys(TREE).sort(function(a,b){ return TREE[a].name.localeCompare(TREE[b].name); }).forEach(function(k){ elC.appendChild(opt(k, TREE[k].name)); });
    function procs(){ var c=TREE[elC.value]; return c?c.processes:{}; }
    function facs(){ var c=TREE[elC.value]; if(!c) return {}; var p=c.processes[elP.value]; return p?p.facilities:{}; }
    elC.onchange=function(){ fill(elP, procs(), 'All processes'); fill(elF, {}, 'All facilities'); };
    elP.onchange=function(){ fill(elF, facs(), 'All facilities'); };
    // prefill from the current filter
    if(pre.c && TREE[pre.c]){ elC.value=pre.c; fill(elP, procs(), 'All processes');
        if(pre.p && TREE[pre.c].processes[pre.p]){ elP.value=pre.p; fill(elF, facs(), 'All facilities');
            if(pre.f){ elF.value=pre.f; } } }
})();
</script>
<?php require __DIR__ . '/../includes/footer.php'; ?>
