<?php
/**
 * AR / Collections inventory-allocation module — engine + migrations + helpers.
 *
 * Works the report-uploaded `collection_raw` data, but keeps allocation + work HISTORY in its OWN
 * persistent tables, because collection_raw is REPLACED on every profitability upload. The persistent
 * `ar_allocation` table is the source of truth for "who has which claim + its work state"; at display
 * time it is joined to the CURRENT collection_raw rows for live balances/DOS. `ar_allocation_event`
 * keeps the full work history (so re-uploads never lose worked data and we can cross-check allocability).
 *
 * Allocation UNIT = a whole patient: (process_id, chart_number). All of a patient's charge lines /
 * carriers stay with ONE user — a patient is never split across users.
 *
 * Migrations are registered in ar_register_migrations() and run by app_auto_provision().
 */

const AR_PRIORITIES   = ['today' => "Today's priority", 'high' => 'High priority', 'regular' => 'Regular'];
/** The criteria tier sets the work priority: Criteria 1 (primary)→Today's, 2 (secondary)→High, 3 (tertiary)→Regular. */
const AR_TIER_PRIORITY = [1 => 'today', 2 => 'high', 3 => 'regular'];
const AR_ALLOC_TYPES  = ['insurance_bal' => 'Insurance balance', 'patient_bal' => 'Patient balance'];
const AR_MAX_FOLLOWUP_DAYS = 45;   // a next-follow-up date may be at most this many days from today

/** The 5 audit parameters an AR auditor scores per worked visit (no code correction — pass/fail each). */
const AR_AUDIT_PARAMS = [
    'pre_call'          => 'Pre-call analysis',
    'documentation'     => 'Documentation & call-log integrity',
    'end_action'        => 'End action',
    'code_selection'    => 'Status & action-code selection',
    'appeal_compliance' => 'Appeal & adjustment compliance',
];
const AR_DEFAULT_SAMPLE_PCT = 100;   // until a supervisor sets a per-user %, every worked visit is audit-eligible
const AR_REBUTTAL_TAT_DAYS  = 5;     // the AR user must review/act on error-marked visits within this many days

/** SQL predicate (for an ar_allocation row aliased `a`): UNTOUCHED = allocated and NOT a single visit worked. */
const AR_UNTOUCHED_SQL = "NOT EXISTS (SELECT 1 FROM ar_visit_work w WHERE w.process_id=a.process_id AND w.chart_number=a.chart_number AND w.worked_at IS NOT NULL)";

/** Seed lists for the supervisor-customizable dropdowns (editable later via admin/ar-codes.php). */
const AR_SEED_CLAIM_STATUS = [
    'CLAIM DENIED AS AUTH REQUIRED/PROVIDER OON',
    'CLAIM DENIED AS INCLUSIVE / BUNDLED SERVICE',
    'CLAIM DENIED AS DUPLICATE CLAIM',
    'CLAIM DENIED AS ELIGIBILITY ISSUE',
    'CLAIM DENIED AS COB REQUIRED',
    'CLAIM DENIED AS DEMOGRAPHIC ERROR',
    'CLAIM DENIED AS INCORRECT / MISSING MODIFIER',
    'CLAIM DENIED AS INCORRECT / MISSING DIAGNOSIS',
    'CLAIM DENIED AS CODING ERROR',
    'CLAIM DENIED AS MEDICAL NECESSITY',
    'CLAIM DENIED AS NON-COVERED SERVICE',
    'CLAIM DENIED AS TIMELY FILING EXCEEDED',
    'CLAIM DENIED AS DOCUMENTATION REQUIRED',
    'CLAIM DENIED AS BILLING ERROR',
    'CLAIM DENIED AS PROVIDER NPI/TAXONOMY ISSUE',
    'CLAIM PROCESSED TOWARDS PATIENT RESPONSIBILITY',
    'CLAIM DENIED AS FREQUENCY LIMIT EXCEEDED',
    'CLAIM DENIED AS PAYER REQUIRES ADDITIONAL INFORMATION',
    'CLAIM PAID',
    'APPEAL UPHELD',
    'RESUBMITTED TO ADVANCE MD',
    'CLAIM DENIED AS PRIMARY PAID MORE THAN SECONDARY ALLOWED',
    'CLAIM IN PROCESS',
    'NO CLAIM ON FILE',
    'APPROVED TO PAY',
    'SECONDARY BALANCE DUE',
    'PROVIDER CREDENTIALING ISSUE',
];
const AR_SEED_ACTION_TAKEN = [
    'CLAIM REBILLED',
    'CORRECTED CLAIM SUBMITTED',
    'EOB SENT TO PAYMENT POSTING',
    'PAYMENT POSTED',
    'SENT TO CODING',
    '1ST LEVEL APPEAL SUBMITTED',
    '2ND LEVEL APPEAL SUBMITTED',
    'RECONSIDERATION SENT',
    '1ST PATIENT STATEMENT SENT',
    '2ND PATIENT STATEMENT SENT',
    '3RD PATIENT STATEMENT SENT',
    'NEED TO ALLOW TIME',
    'RECENTLY PAID NEED TO ALLOW TIME',
    'CLAIM BILLED TO SECONDARY INSURANCE',
    'WRITE OFF INSURANCE',
    'ESCALATED TO CLIENT',
    'WRITE OFF - AS PER RAJ UPDATE',
    'EOB REQUESTED - NEED TO ALLOW TIME',
    'CLAIM SENT BACK FOR REPROCESSING',
    'PAID TO INCORRECT ADDRESS - CHECK REISSUED',
    'NEED TO CONTACT PATIENT',
    'WRITE OFF - 2ND LEVEL APPEAL UPHELD',
    'WRITE OFF - NON COVERED CPT',
    'WRITE OFF - OFFSET',
];

/** Actions that CLOSE a visit (payment/write-off/EOB-posting) — a next-follow-up date is NOT required for these.
 *  Every other action_taken requires a follow-up date (enforced in ar-work.php, server + client). */
const AR_NO_FOLLOWUP_ACTIONS = [
    'PAYMENT POSTED',
    'WRITE OFF INSURANCE',
    'WRITE OFF - AS PER RAJ UPDATE',
    'WRITE OFF - 2ND LEVEL APPEAL UPHELD',
    'WRITE OFF - NON COVERED CPT',
    'WRITE OFF - OFFSET',
    'EOB SENT TO PAYMENT POSTING',
];
/** True when a next-follow-up date is mandatory for this action_taken (i.e. NOT one of the closing actions). */
function ar_action_requires_followup(string $action): bool
{
    return !in_array(strtoupper(trim($action)), AR_NO_FOLLOWUP_ACTIONS, true);
}

/* ============================================================================ MIGRATIONS */
function ar_register_migrations(): array
{
    return [
        /* ---- snapshot patient name + provider onto the ACCOUNT allocation so AR display survives collection_raw
               being wiped/replaced on the next collection upload (the live lookup returns nothing otherwise) ---- */
        'ar_alloc_demographics_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'ar_allocation', 'patient_name'))
                $pdo->exec("ALTER TABLE `ar_allocation` ADD COLUMN `patient_name` VARCHAR(160) NULL AFTER `chart_number`");
            if (!app_col_exists($pdo, 'ar_allocation', 'provider_name'))
                $pdo->exec("ALTER TABLE `ar_allocation` ADD COLUMN `provider_name` VARCHAR(160) NULL AFTER `patient_name`");
        },
        /* ---- customizable claim_status + action_taken dropdowns (supervisor-managed) ---- */
        'ar_dropdowns_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `ar_dropdowns` (
                `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `kind`       ENUM('claim_status','action_taken') NOT NULL,
                `label`      VARCHAR(180) NOT NULL,
                `sort_order` INT NOT NULL DEFAULT 0,
                `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
                `created_by` BIGINT UNSIGNED NULL,
                `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`), UNIQUE KEY `uq_kind_label` (`kind`,`label`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            if ((int) $pdo->query("SELECT COUNT(*) FROM ar_dropdowns")->fetchColumn() === 0) {
                $ins = $pdo->prepare("INSERT IGNORE INTO ar_dropdowns (kind,label,sort_order) VALUES (?,?,?)");
                $o = 0; foreach (AR_SEED_CLAIM_STATUS as $l) $ins->execute(['claim_status', $l, $o++]);
                $o = 0; foreach (AR_SEED_ACTION_TAKEN as $l) $ins->execute(['action_taken', $l, $o++]);
            }
        },

        /* ---- persistent allocation + work state, one row per (process_id, chart_number) ---- */
        'ar_allocation_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `ar_allocation` (
                `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `process_id`          BIGINT UNSIGNED NOT NULL,
                `chart_number`        VARCHAR(64) NOT NULL,
                `carrier_name`        VARCHAR(255) NULL,
                `dos`                 DATE NULL,
                `inventory_status`    VARCHAR(40) NULL,                              -- NULL/'' = unallocated (allocable); 'allocated','in_progress','worked'
                `allocation_type`     ENUM('insurance_bal','patient_bal') NULL,
                `priority`            ENUM('today','high','regular') NULL,
                `allocated_user`      BIGINT UNSIGNED NULL,
                `allocated_by`        BIGINT UNSIGNED NULL,
                `allocated_at`        TIMESTAMP NULL,
                `last_worked_date`    DATE NULL,
                `worked_at`           TIMESTAMP NULL,
                `notes`               TEXT NULL,
                `claim_status`        VARCHAR(180) NULL,
                `action_taken`        VARCHAR(180) NULL,
                `next_follow_up_date` DATE NULL,
                `insurance_balance`   DECIMAL(15,2) NULL,                            -- snapshot at allocation (live value joined from collection_raw at display)
                `patient_balance`     DECIMAL(15,2) NULL,
                `created_at`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `uq_proc_chart` (`process_id`,`chart_number`),
                KEY `idx_ar_user`     (`allocated_user`,`inventory_status`),
                KEY `idx_ar_followup` (`next_follow_up_date`),
                KEY `idx_ar_worked`   (`last_worked_date`),
                KEY `idx_ar_queue`    (`process_id`,`allocation_type`,`priority`,`inventory_status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

            // Full work/allocation HISTORY — survives collection_raw re-uploads; used for cross-checking
            // allocability and avoiding duplicate/mismatched allocation between users.
            $pdo->exec("CREATE TABLE IF NOT EXISTS `ar_allocation_event` (
                `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `allocation_id` BIGINT UNSIGNED NULL,
                `process_id`    BIGINT UNSIGNED NULL,
                `chart_number`  VARCHAR(64) NULL,
                `event`         VARCHAR(40) NOT NULL,                                -- allocate / reassign / pullback / work / force_allocate / reallocate
                `user_id`       BIGINT UNSIGNED NULL,                                -- who performed it
                `from_user`     BIGINT UNSIGNED NULL,
                `to_user`       BIGINT UNSIGNED NULL,
                `claim_status`  VARCHAR(180) NULL,
                `action_taken`  VARCHAR(180) NULL,
                `next_follow_up_date` DATE NULL,
                `notes`         TEXT NULL,
                `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `idx_are_alloc` (`allocation_id`),
                KEY `idx_are_chart` (`process_id`,`chart_number`),
                KEY `idx_are_event` (`event`,`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        },

        /* ---- permission: run/manage AR inventory allocation (+ the customizable dropdowns) ---- */
        'ar_allocate_perm_v1' => function (PDO $pdo) {
            $pdo->prepare("INSERT INTO permissions (code,label,group_name,description,created_at,updated_at)
                           VALUES (?,?,?,?,NOW(),NOW())
                           ON DUPLICATE KEY UPDATE label=VALUES(label), group_name=VALUES(group_name)")
                ->execute(['ar.allocate', 'Allocate AR inventory', 'ar', 'Run & manage AR/Collections inventory allocation and its dropdowns']);
            $pid = (int) $pdo->query("SELECT id FROM permissions WHERE code='ar.allocate'")->fetchColumn();
            if ($pid) {
                // AR supervisors / managers only — AR and Billing are SEPARATE departments.
                $roleNames = ['AR Manager', 'AR Supervisor'];
                $in = implode(',', array_fill(0, count($roleNames), '?'));
                $rids = $pdo->prepare("SELECT id FROM roles WHERE name IN ($in)");
                $rids->execute($roleNames);
                $grant = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id) VALUES (?,?)");
                foreach ($rids->fetchAll(PDO::FETCH_COLUMN) as $rid) $grant->execute([(int) $rid, $pid]);
            }
        },

        /* ---- one-time cleanup: the earlier ar_allocate_perm_v1 also granted ar.allocate to the Billing
         *      Manager/Supervisor roles, which leaked the entire AR section to billing supervisors/managers.
         *      That migration already ran on provisioned DBs, so revoke those rows here. AR ≠ Billing. ---- */
        'ar_allocate_revoke_billing_v1' => function (PDO $pdo) {
            $pdo->exec("DELETE rp FROM role_permissions rp
                        JOIN roles r ON r.id = rp.role_id
                        JOIN permissions p ON p.id = rp.permission_id
                        WHERE p.code = 'ar.allocate' AND r.name IN ('Billing Manager','Billing Supervisor')");
        },

        /* ---- AR is its OWN vertical: every ar.* permission belongs ONLY to the AR roles. Revoke any ar.*
         *      grant that leaked onto a coding / billing / other role so a coder (or biller) never sees the
         *      AR / Collections section. Idempotent; safe no-op once clean. ---- */
        'ar_perms_ar_roles_only_v1' => function (PDO $pdo) {
            $pdo->exec("DELETE rp FROM role_permissions rp
                        JOIN roles r ON r.id = rp.role_id
                        JOIN permissions p ON p.id = rp.permission_id
                        WHERE p.code LIKE 'ar.%'
                          AND r.name NOT IN ('Account Receivable','AR Manager','AR Supervisor','AR Auditor')");
        },

        /* ---- PHASE 1 (2026-06-25 restructure): per-VISIT work ledger ----------------------------------
         *  collection_raw is replaced daily, so visit-level work HISTORY lives here, keyed on the stable
         *  business identity (process_id, chart_number, visit_id). ar_allocation stays the ACCOUNT-ownership
         *  table (one Patient Account # = one user); ar_visit_work is the per-visit child carrying status /
         *  claim_status / action_taken / follow-up / bucket. Production = COUNT(visit rows). A visit returns
         *  as REWORK only once worked AND its next_follow_up_date has arrived AND it is still open
         *  (insurance_balance>0 in the CURRENT collection_raw). */
        'ar_visit_work_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `ar_visit_work` (
                `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `process_id`          BIGINT UNSIGNED NOT NULL,
                `chart_number`        VARCHAR(64) NOT NULL,
                `visit_id`            VARCHAR(64) NOT NULL,
                `carrier_name`        VARCHAR(255) NULL,                              -- current responsible carrier (snapshot)
                `tier`                TINYINT UNSIGNED NULL,                          -- 1 primary / 2 secondary / 3 tertiary at allocation
                `dos`                 DATE NULL,
                `priority`            ENUM('today','high','regular') NULL,
                `allocated_user`      BIGINT UNSIGNED NULL,                           -- denormalized account owner (= ar_allocation.allocated_user)
                `allocated_by`        BIGINT UNSIGNED NULL,
                `allocated_at`        TIMESTAMP NULL,
                `inventory_status`    VARCHAR(40) NULL,                               -- 'allocated' | 'worked'
                `bucket`              VARCHAR(40) NULL,                               -- Phase 3: escalated_to_client / sent_to_coding
                `last_worked_date`    DATE NULL,
                `worked_at`           TIMESTAMP NULL,
                `notes`               TEXT NULL,
                `claim_status`        VARCHAR(180) NULL,
                `action_taken`        VARCHAR(180) NULL,
                `next_follow_up_date` DATE NULL,
                `insurance_balance`   DECIMAL(15,2) NULL,                             -- snapshot at allocation (live value joined from collection_raw at display)
                `created_at`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `uq_proc_chart_visit` (`process_id`,`chart_number`,`visit_id`),
                KEY `idx_arv_user`     (`allocated_user`,`inventory_status`),
                KEY `idx_arv_chart`    (`process_id`,`chart_number`),
                KEY `idx_arv_followup` (`next_follow_up_date`),
                KEY `idx_arv_bucket`   (`bucket`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            // visit-grain history on the event log (account-grain rows keep visit_id NULL)
            if (!app_col_exists($pdo, 'ar_allocation_event', 'visit_id'))
                $pdo->exec("ALTER TABLE `ar_allocation_event` ADD COLUMN `visit_id` VARCHAR(64) NULL AFTER `chart_number`");
        },

        /* ---- backfill the visit ledger from existing ACCOUNT allocations + current collection_raw, so any
         *      already-allocated patient becomes visit-grained. One visit row per open visit, inheriting the
         *      account's owner + work state. Idempotent (INSERT IGNORE on the unique key). ---- */
        'ar_visit_work_backfill_v1' => function (PDO $pdo) {
            try {
                $tier = ar_tier_case('cr');
                $pdo->exec("INSERT IGNORE INTO ar_visit_work
                        (process_id,chart_number,visit_id,carrier_name,tier,dos,priority,allocated_user,allocated_by,allocated_at,
                         inventory_status,last_worked_date,worked_at,notes,claim_status,action_taken,next_follow_up_date,insurance_balance,created_at)
                    SELECT a.process_id, a.chart_number, cr.visit_id,
                           MAX(cr.carrier1_name), MIN($tier), MIN(cr.dos), a.priority,
                           a.allocated_user, a.allocated_by, a.allocated_at,
                           CASE WHEN a.worked_at IS NOT NULL THEN 'worked' ELSE 'allocated' END,
                           a.last_worked_date, a.worked_at, a.notes, a.claim_status, a.action_taken, a.next_follow_up_date,
                           ROUND(SUM(cr.insurance_balance),2), NOW()
                    FROM ar_allocation a
                    JOIN reports rp ON rp.process_id=a.process_id AND rp.deleted_at IS NULL
                    JOIN collection_raw cr ON cr.report_id=rp.id AND cr.chart_number=a.chart_number
                    WHERE a.allocated_user IS NOT NULL AND COALESCE(cr.visit_id,'')<>'' AND cr.insurance_balance>0
                    GROUP BY a.process_id, a.chart_number, cr.visit_id");
            } catch (Throwable $e) { /* no AR data yet, or schema differs — safe no-op */ }
        },

        /* ---- PHASE 3: per-visit GetNext work + supervisor routing buckets (escalated_to_client / sent_to_coding) ---- */
        'ar_visit_buckets_v1' => function (PDO $pdo) {
            foreach ([
                'bucket_at' => 'TIMESTAMP NULL',
                'bucket_by' => 'BIGINT UNSIGNED NULL',
                'sent_at'   => 'TIMESTAMP NULL',          // supervisor confirmed the routing (sent to client/coding)
                'sent_by'   => 'BIGINT UNSIGNED NULL',
            ] as $col => $def) {
                if (!app_col_exists($pdo, 'ar_visit_work', $col)) $pdo->exec("ALTER TABLE `ar_visit_work` ADD COLUMN `$col` $def");
            }
        },

        /* ---- PHASE 4: audit (5-parameter scoring per visit) + sampling + rebuttal ---- */
        'ar_audit_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'ar_visit_work', 'in_sample'))
                $pdo->exec("ALTER TABLE `ar_visit_work` ADD COLUMN `in_sample` TINYINT(1) NOT NULL DEFAULT 0 AFTER `bucket`");
            // per-AR-user audit sample % (supervisor/manager managed)
            $pdo->exec("CREATE TABLE IF NOT EXISTS `ar_user_sample` (
                `user_id`     BIGINT UNSIGNED NOT NULL,
                `sample_pct`  TINYINT UNSIGNED NOT NULL DEFAULT " . AR_DEFAULT_SAMPLE_PCT . ",
                `updated_by`  BIGINT UNSIGNED NULL,
                `updated_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`user_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            // one audit per worked visit (5-parameter DPO; no code correction)
            $pdo->exec("CREATE TABLE IF NOT EXISTS `ar_visit_audit` (
                `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `visit_work_id`      BIGINT UNSIGNED NOT NULL,
                `process_id`         BIGINT UNSIGNED NOT NULL,
                `chart_number`       VARCHAR(64) NOT NULL,
                `visit_id`           VARCHAR(64) NOT NULL,
                `worker_id`          BIGINT UNSIGNED NULL,                            -- the AR user being audited
                `auditor_id`         BIGINT UNSIGNED NULL,
                `audited_at`         TIMESTAMP NULL,
                `opportunities`      TINYINT UNSIGNED NULL,                           -- 5
                `defects`            TINYINT UNSIGNED NULL,
                `result`             ENUM('pass','error') NULL,
                `comment`            TEXT NULL,
                `rebuttal_status`    ENUM('none','agreed','raised','accepted','rejected') NOT NULL DEFAULT 'none',
                `rebuttal_comment`   TEXT NULL,
                `rebuttal_at`        TIMESTAMP NULL,
                `rebuttal_resolved_at` TIMESTAMP NULL,
                `rebuttal_due`       DATE NULL,                                       -- TAT: worker must act by this date
                `created_at`         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at`         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`), UNIQUE KEY `uq_audit_visit` (`visit_work_id`),
                KEY `idx_ara_worker` (`worker_id`,`audited_at`),
                KEY `idx_ara_reb`    (`rebuttal_status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            // per-parameter result (+ rebuttal markers), one row per audit×param
            $pdo->exec("CREATE TABLE IF NOT EXISTS `ar_audit_param` (
                `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `audit_id`        BIGINT UNSIGNED NOT NULL,
                `param_key`       VARCHAR(40) NOT NULL,
                `result`          ENUM('pass','fail') NOT NULL DEFAULT 'pass',
                `note`            VARCHAR(255) NULL,
                `disputed`        TINYINT(1) NOT NULL DEFAULT 0,
                `dispute_reason`  VARCHAR(255) NULL,
                `rebuttal_result` ENUM('pending','accepted','rejected') NULL,
                PRIMARY KEY (`id`), UNIQUE KEY `uq_audit_param` (`audit_id`,`param_key`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        },

        /* ---- PHASE 5: dedicated ar.reports permission (role-scoped reports + inventory pivots). Granted to
         *      ALL four AR roles — each sees only their own slice in-page. AR ≠ Billing (no billing role). ---- */
        'ar_reports_perm_v1' => function (PDO $pdo) {
            $pdo->prepare("INSERT INTO permissions (code,label,group_name,description,created_at,updated_at)
                           VALUES (?,?,?,?,NOW(),NOW())
                           ON DUPLICATE KEY UPDATE label=VALUES(label), group_name=VALUES(group_name)")
                ->execute(['ar.reports', 'AR reports', 'ar', 'View AR/Collections reports, analytics & inventory pivots (role-scoped)']);
            $pid = (int) $pdo->query("SELECT id FROM permissions WHERE code='ar.reports'")->fetchColumn();
            if ($pid) {
                $roleNames = ['AR Manager', 'AR Supervisor', 'AR Auditor', 'Account Receivable'];
                $in = implode(',', array_fill(0, count($roleNames), '?'));
                $rids = $pdo->prepare("SELECT id FROM roles WHERE name IN ($in)"); $rids->execute($roleNames);
                $grant = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id) VALUES (?,?)");
                foreach ($rids->fetchAll(PDO::FETCH_COLUMN) as $rid) $grant->execute([(int) $rid, $pid]);
            }
        },

        /* ---- PHASE 4 hardening (post-review): 'agreed' acknowledgement state + worked_by attribution snapshot ---- */
        'ar_audit_v2' => function (PDO $pdo) {
            // the worker can AGREE with an audit (not just dispute) — add the enum value (mirror coding)
            try { $pdo->exec("ALTER TABLE `ar_visit_audit` MODIFY COLUMN `rebuttal_status` ENUM('none','agreed','raised','accepted','rejected') NOT NULL DEFAULT 'none'"); } catch (Throwable $e) {}
            // snapshot WHO actually worked the visit, so audit attribution survives a later account reassign
            if (!app_col_exists($pdo, 'ar_visit_work', 'worked_by'))
                $pdo->exec("ALTER TABLE `ar_visit_work` ADD COLUMN `worked_by` BIGINT UNSIGNED NULL AFTER `worked_at`");
        },
    ];
}

/* ============================================================================ HELPERS */

/** Active dropdown labels for a kind ('claim_status'|'action_taken'); falls back to the seed list. */
function ar_dropdowns(PDO $pdo, string $kind): array
{
    try {
        $st = $pdo->prepare("SELECT label FROM ar_dropdowns WHERE kind=? AND status='active' ORDER BY sort_order, label");
        $st->execute([$kind]);
        $rows = $st->fetchAll(PDO::FETCH_COLUMN);
        if ($rows) return $rows;
    } catch (Throwable $e) { /* table not provisioned yet */ }
    return $kind === 'action_taken' ? AR_SEED_ACTION_TAKEN : AR_SEED_CLAIM_STATUS;
}

/** Users who can be allocated AR/Collections inventory = AR staff only (ar.work). AR and Billing are
 *  SEPARATE departments — a billing-only user (billing.work) is NOT an AR worker and must not appear here. */
function ar_allocatable_users(PDO $pdo): array
{
    return $pdo->query("SELECT u.id, u.name, u.email, u.vertical,
            GROUP_CONCAT(DISTINCT r.name ORDER BY r.name SEPARATOR ', ') roles
        FROM users u
        JOIN user_roles ur ON ur.user_id=u.id
        JOIN roles r ON r.id=ur.role_id
        JOIN role_permissions rp ON rp.role_id=ur.role_id
        JOIN permissions p ON p.id=rp.permission_id
        WHERE u.status='active' AND u.user_type='internal' AND p.code = 'ar.work'
        GROUP BY u.id, u.name, u.email, u.vertical
        ORDER BY u.name")->fetchAll();
}

/** The id set of allocatable users (ar.work only) — for server-side validation of POSTed targets. */
function ar_allocatable_user_ids(PDO $pdo): array
{
    static $ids = null;
    if ($ids === null) $ids = array_map('intval', array_column(ar_allocatable_users($pdo), 'id'));
    return $ids;
}

/** Per-user existing AR workload, across ALL processes — so a supervisor deciding who to distribute NEW inventory
 *  to can see who is already loaded (a user busy on another process is still busy). Keyed by user id; each entry:
 *    accounts    = distinct patient accounts allocated to them
 *    alloc_pri / alloc_sec   = visits already allocated, split PRIMARY (tier 1) vs SECONDARY (tier 2/3 — "the rest",
 *                              same split the seq filter uses). primary+secondary = every allocated visit.
 *    worked_pri / worked_sec = the subset of those visits that are WORKED, same split
 *    pending     = visits allocated but NOT yet worked (the remaining backlog)
 *  Empty input → []. Reads only ar_visit_work (indexed on allocated_user) so it is cheap. tier<=>1 is the
 *  NULL-safe "= 1" so a NULL tier folds into secondary and the two columns always sum to the total. */
function ar_users_open_load(PDO $pdo, array $userIds): array
{
    $ids = array_values(array_unique(array_filter(array_map('intval', $userIds))));
    if (!$ids) return [];
    $in = implode(',', $ids);                                            // ints only — safe to inline
    $rows = $pdo->query("SELECT allocated_user uid,
                COUNT(DISTINCT chart_number)                             accounts,
                COALESCE(SUM(tier<=>1),0)                                alloc_pri,
                COALESCE(SUM(NOT (tier<=>1)),0)                          alloc_sec,
                COALESCE(SUM(worked_at IS NOT NULL AND (tier<=>1)),0)    worked_pri,
                COALESCE(SUM(worked_at IS NOT NULL AND NOT (tier<=>1)),0) worked_sec,
                COALESCE(SUM(worked_at IS NULL),0)                       pending
            FROM ar_visit_work
            WHERE allocated_user IN ($in)
            GROUP BY allocated_user")->fetchAll();
    $out = [];
    foreach ($rows as $r) $out[(int) $r['uid']] = [
        'accounts'   => (int) $r['accounts'],
        'alloc_pri'  => (int) $r['alloc_pri'],  'alloc_sec'  => (int) $r['alloc_sec'],
        'worked_pri' => (int) $r['worked_pri'], 'worked_sec' => (int) $r['worked_sec'],
        'pending'    => (int) $r['pending']];
    return $out;
}

/** AR team scope = reporting-tree members (incl self); null = unrestricted (sysadmin). Reuses the
 *  vertical-agnostic reports_to walker from the coding module (so a manager sees supervisors + their teams). */
function ar_team_scope(PDO $pdo, int $uid): ?array
{
    if (function_exists('coding_team_scope')) return coding_team_scope($pdo, $uid);
    return user_has_role($uid, 'sysadmin') ? null : [$uid];
}

/** True when $when (Y-m-d) is no more than AR_MAX_FOLLOWUP_DAYS ahead of today (and not blank). */
function ar_followup_within_cap(?string $when): bool
{
    if (!$when || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $when)) return false;
    $days = (strtotime($when) - strtotime(date('Y-m-d'))) / 86400;
    return $days <= AR_MAX_FOLLOWUP_DAYS;   // past / today / up to the cap are all fine
}

/** True if the current user can see ANY part of the AR/Collections module. AR ≠ Billing: a billing-only
 *  user (billing.work / billing.supervise) is NOT an AR user and must not see this section. */
function is_ar_user(): bool
{
    return can('ar.work', 'ar.allocate', 'ar.supervise', 'ar.audit');
}

/** Landing page for an AR/Collections user (mirrors coding_home_url) — each role's first usable page, so
 *  dashboard.php never drops an AR user on the "workspace isn't set up" placeholder. */
function ar_home_url(): string
{
    if (can('ar.work'))      return '/admin/ar-queue.php';        // worker → their queue
    if (can('ar.allocate'))  return '/admin/ar-allocation.php';   // supervisor/manager → allocation console
    if (can('ar.audit'))     return '/admin/ar-audit.php';        // auditor → audit queue
    if (can('ar.supervise')) return '/admin/ar-verify.php';
    if (can('ar.reports'))   return '/admin/ar-reports.php';
    return '/admin/ar-queue.php';
}

/** Sidebar nav for the AR / Collections allocation module. */
function ar_nav_items(): array
{
    return [
        ['hdr' => 'AR / COLLECTIONS'],
        ['h'=>'ar-queue.php',      'u'=>'/admin/ar-queue.php',      'i'=>'inbox',          't'=>'My AR queue',          'p'=>['ar.work']],
        ['h'=>'ar-rebuttals.php',  'u'=>'/admin/ar-rebuttals.php',  'i'=>'message-report', 't'=>'My audit rebuttals',    'p'=>['ar.work']],
        ['h'=>'ar-audit.php',      'u'=>'/admin/ar-audit.php',      'i'=>'clipboard-check','t'=>'Audit',                 'p'=>['ar.audit']],
        ['h'=>'ar-sampling.php',   'u'=>'/admin/ar-sampling.php',   'i'=>'percentage',     't'=>'Audit sampling',        'p'=>['ar.supervise']],
        ['h'=>'ar-allocation.php', 'u'=>'/admin/ar-allocation.php', 'i'=>'arrows-shuffle', 't'=>'Allocate inventory',    'p'=>['ar.allocate']],
        ['h'=>'ar-force.php',      'u'=>'/admin/ar-force.php',      'i'=>'file-upload',    't'=>'Force allocation',      'p'=>['ar.allocate']],
        ['h'=>'ar-verify.php',     'u'=>'/admin/ar-verify.php',     'i'=>'route',          't'=>'Verify & route',        'p'=>['ar.supervise']],
        ['h'=>'ar-codes.php',      'u'=>'/admin/ar-codes.php',      'i'=>'list-check',     't'=>'Claim status & actions','p'=>['ar.allocate']],
        ['h'=>'ar-specialty.php',  'u'=>'/admin/ar-specialty.php',  'i'=>'stethoscope',    't'=>'CPT ↔ Specialty',       'p'=>['ar.supervise','ar.allocate']],
        ['h'=>'ar-reports.php',    'u'=>'/admin/ar-reports.php',    'i'=>'chart-histogram','t'=>'AR reports',            'p'=>['ar.reports']],
    ];
}

/* ============================================================================ ALLOCATION ENGINE
 *  collection_raw is the (replaceable) source; ar_allocation is the persistent allocation/work state.
 *  Allocation UNIT = whole patient (process_id, chart_number). insurance_balance>0 is a hard gate —
 *  a claim with no insurance balance is NEVER allocated even if patient_balance>0 (that goes via the
 *  supervisor's force/patient-balance flow). */

/** True only when a process sits under an AR-canonical vertical — the server-side fence that keeps AR
 *  allocation/force/download from ever touching a Coding/Billing process via a forged process_id. */
function ar_process_in_ar_vertical(PDO $pdo, int $processId): bool
{
    if ($processId <= 0) return false;
    static $cache = [];
    if (array_key_exists($processId, $cache)) return $cache[$processId];
    $st = $pdo->prepare("SELECT v.code, v.name FROM processes p JOIN clients c ON c.id=p.client_id
                         JOIN verticals v ON v.id=c.vertical_id WHERE p.id=? AND p.deleted_at IS NULL");
    $st->execute([$processId]); $v = $st->fetch();
    return $cache[$processId] = ($v ? app_vertical_group_canonical($v) === 'ar' : false);
}

/** Report ids that back a process (collection_raw.report_id → reports.process_id). */
function ar_process_report_ids(PDO $pdo, int $processId): array
{
    $st = $pdo->prepare("SELECT id FROM reports WHERE process_id=? AND deleted_at IS NULL");
    $st->execute([$processId]);
    return array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
}

/**
 * SQL CASE giving a collection_raw row's AR tier — 1=primary, 2=secondary, 3=tertiary, 9=none/closed.
 * DEFAULT rule (balance sits with the carrier that hasn't paid yet). >>> THIS IS THE ONE PLACE to
 * change if the exact tier rule differs <<<. Requires insurance_balance>0 to count toward a tier.
 * $a = table alias (e.g. 'cr').
 */
function ar_tier_case(string $a = 'cr'): string
{
    $p = $a !== '' ? $a . '.' : '';
    return "CASE
        WHEN {$p}insurance_balance>0 AND {$p}primary_ins_paid=0   AND COALESCE({$p}carrier1_name,'')<>'' THEN 1
        WHEN {$p}insurance_balance>0 AND {$p}primary_ins_paid>0   AND {$p}secondary_ins_paid=0 AND COALESCE({$p}carrier2_name,'')<>'' THEN 2
        WHEN {$p}insurance_balance>0 AND {$p}secondary_ins_paid>0 AND {$p}tertiary_ins_paid=0  AND COALESCE({$p}carrier3_name,'')<>'' THEN 3
        ELSE 9 END";
}

/** HAVING fragment for the "Allocation sequence" filter on the computed `tier` alias:
 *  PRIMARY  = tier 1 (insurance_balance>0 AND primary_ins_paid=0) — the primary carrier hasn't paid yet.
 *  SECONDARY = tier 2 or 3 (insurance_balance>0 AND primary_ins_paid>0) — "the rest": a payer has paid but a
 *  balance remains with the next carrier. Empty/unknown = every allocable tier. Returns a safe literal
 *  (the caller validates $seq to a fixed set, so no injection). */
function ar_seq_having(?string $seq): string
{
    if ($seq === 'primary')   return 'tier = 1';
    if ($seq === 'secondary') return 'tier IN (2,3)';
    return 'tier IN (1,2,3)';
}

/** Distinct WORKING carriers present in a process's collection_raw, for the supervisor filter — seq-aware so the
 *  dropdown lists the carrier actually being chased for the chosen Allocation sequence:
 *    primary   → carrier1_name (the primary insurer)
 *    secondary → carrier2_name ∪ carrier3_name ("the rest" — secondary + tertiary insurers)
 *    all/blank → every carrier column (carrier1 ∪ carrier2 ∪ carrier3)
 *  Matches ar_tier_carrier_expr(), which shows/filters each claim by the carrier of its active tier. */
function ar_process_carriers(PDO $pdo, int $processId, ?string $seq = null): array
{
    $rids = ar_process_report_ids($pdo, $processId);
    if (!$rids) return [];
    $in = implode(',', $rids);
    $cols = $seq === 'primary'   ? ['carrier1_name']
          : ($seq === 'secondary' ? ['carrier2_name', 'carrier3_name']
          : ['carrier1_name', 'carrier2_name', 'carrier3_name']);
    $union = implode(' UNION ', array_map(fn($c) => "SELECT DISTINCT $c v FROM collection_raw WHERE report_id IN ($in) AND COALESCE($c,'')<>''", $cols));
    return $pdo->query("SELECT v FROM ($union) x ORDER BY v")->fetchAll(PDO::FETCH_COLUMN);
}

/** SQL expression for a visit's WORKING carrier = the carrier of its active tier (1→carrier1, 2→carrier2,
 *  3→carrier3), as an aggregate over the (chart_number, visit_id) group. So a SECONDARY claim shows/filters by
 *  its secondary carrier, not the primary insurer. MUST be used in a SELECT grouped by chart_number+visit_id.
 *  MIN($tier) is the visit's tier (same value ar_seq_having / the tier column use). */
function ar_tier_carrier_expr(string $a = 'cr'): string
{
    $p = $a !== '' ? $a . '.' : '';
    $tier = ar_tier_case($a);
    return "CASE MIN($tier) WHEN 1 THEN MAX({$p}carrier1_name) WHEN 2 THEN MAX({$p}carrier2_name) WHEN 3 THEN MAX({$p}carrier3_name) ELSE MAX({$p}carrier1_name) END";
}

/**
 * Ordered ALLOCABLE patients for a process + optional (carrier / dos_year / dos_month) filters:
 * one row per chart_number, tier∈{1,2,3}, total insurance_balance>0, and NOT already allocated to
 * anyone (cross-checked against the persistent ar_allocation). Ordered by criteria: tier (1→3),
 * then oldest DOS, then insurance_balance high→low.
 */
function ar_candidate_patients(PDO $pdo, array $f): array
{
    $rids = ar_process_report_ids($pdo, (int) $f['process_id']);
    if (!$rids) return [];
    $in = implode(',', $rids);
    $w = ["cr.report_id IN ($in)", "COALESCE(cr.chart_number,'')<>''"]; $p = [];
    if (!empty($f['carrier']))   { $w[] = "cr.carrier1_name = ?"; $p[] = $f['carrier']; }
    if (!empty($f['dos_year']))  { $w[] = "cr.dos_year = ?";      $p[] = (int) $f['dos_year']; }
    if (!empty($f['dos_month'])) {   // accepts a single month (int) OR several (array) → dos_month IN (...)
        $ms = array_values(array_unique(array_filter(array_map('intval', (array) $f['dos_month']), fn($m) => $m >= 1 && $m <= 12)));
        if ($ms) { $w[] = "cr.dos_month IN (" . implode(',', array_fill(0, count($ms), '?')) . ")"; foreach ($ms as $m) $p[] = $m; }
    }
    $tier = ar_tier_case('cr');
    $sql = "SELECT cr.chart_number,
                   MAX(cr.carrier1_name) carrier_name,
                   MIN(cr.dos) min_dos,
                   ROUND(SUM(cr.insurance_balance),2) ins_bal,
                   ROUND(SUM(cr.patient_balance),2)   pat_bal,
                   MIN($tier) tier
            FROM collection_raw cr
            WHERE " . implode(' AND ', $w) . "
              AND cr.chart_number NOT IN (SELECT chart_number FROM ar_allocation WHERE process_id=? AND allocated_user IS NOT NULL)
            GROUP BY cr.chart_number
            HAVING ins_bal > 0 AND " . ar_seq_having($f['seq'] ?? null) . "
            ORDER BY tier ASC, min_dos ASC, ins_bal DESC";
    $p[] = (int) $f['process_id'];
    $st = $pdo->prepare($sql); $st->execute($p);
    return $st->fetchAll();
}

/**
 * Allocable VISITS for a process (+ optional carrier/dos filters), each tagged with its current account owner.
 * The unit of work is the VISIT (process_id, chart_number, visit_id). A visit is allocable when, in the CURRENT
 * (replaceable) collection_raw, its summed insurance_balance>0 and tier∈{1,2,3}, AND in the persistent ledger it
 * is either UNTOUCHED (no ar_visit_work row) or REWORK-DUE (worked AND next_follow_up_date NULL/≤today). Visits
 * already allocated-not-worked, or worked-and-still-waiting (follow-up in the future), are excluded — that is the
 * provision that keeps a worked-open account out of inventory until its follow-up date arrives.
 * Ordered by criteria: tier (1→3), oldest DOS, balance high→low.
 */
/** Shared builder for the allocable-visit set (no ORDER BY). Returns [sql, params] — or ['', []] when the
 *  process has no reports. Both ar_candidate_visits (rows) and ar_candidate_preview (counts) use it so the
 *  filter logic never drifts. */
function ar_candidate_visits_sql(PDO $pdo, array $f): array
{
    $pid  = (int) $f['process_id'];
    $rids = ar_process_report_ids($pdo, $pid);
    if (!$rids) return ['', []];
    $in = implode(',', $rids);
    $w = ["cr.report_id IN ($in)", "COALESCE(cr.chart_number,'')<>''", "COALESCE(cr.visit_id,'')<>''"]; $wp = [];
    if (!empty($f['dos_year']))  { $w[] = "cr.dos_year = ?";      $wp[] = (int) $f['dos_year']; }
    if (!empty($f['dos_month'])) {   // accepts a single month (int) OR several (array) → dos_month IN (...)
        $ms = array_values(array_unique(array_filter(array_map('intval', (array) $f['dos_month']), fn($m) => $m >= 1 && $m <= 12)));
        if ($ms) { $w[] = "cr.dos_month IN (" . implode(',', array_fill(0, count($ms), '?')) . ")"; foreach ($ms as $m) $wp[] = $m; }
    }
    // carrier now filters the WORKING carrier (carrier of the visit's active tier) so a SECONDARY run matches the
    // secondary carrier — that's a computed alias, so it goes in HAVING (post-group), not the pre-group WHERE.
    $having = "ins_bal > 0 AND " . ar_seq_having($f['seq'] ?? null); $hp = [];
    if (!empty($f['carrier'])) {   // accepts a single carrier (string) OR several (array)
        $cs = array_values(array_unique(array_filter(array_map('strval', (array) $f['carrier']), fn($s) => $s !== '')));
        if ($cs) { $having .= " AND carrier_name IN (" . implode(',', array_fill(0, count($cs), '?')) . ")"; foreach ($cs as $c) $hp[] = $c; }
    }
    $tier = ar_tier_case('cr'); $carrier = ar_tier_carrier_expr('cr');
    $sub = "SELECT cr.chart_number, cr.visit_id,
                   $carrier carrier_name, MIN(cr.dos) min_dos,
                   ROUND(SUM(cr.insurance_balance),2) ins_bal, MIN($tier) tier,
                   TRIM(BOTH ', ' FROM CONCAT_WS(', ', MAX(NULLIF(cr.patient_last_name,'')), MAX(NULLIF(cr.patient_first_name,'')))) patient_name,
                   MAX(cr.provider_name) provider_name
            FROM collection_raw cr
            WHERE " . implode(' AND ', $w) . "
            GROUP BY cr.chart_number, cr.visit_id
            HAVING $having";
    $sql = "SELECT v.chart_number, v.visit_id, v.carrier_name, v.min_dos, v.ins_bal, v.tier,
                   v.patient_name, v.provider_name,
                   a.allocated_user owner,
                   (w.id IS NOT NULL AND w.worked_at IS NOT NULL) is_rework
            FROM ($sub) v
            LEFT JOIN ar_allocation a ON a.process_id=? AND a.chart_number=v.chart_number
            LEFT JOIN ar_visit_work w ON w.process_id=? AND w.chart_number=v.chart_number AND w.visit_id=v.visit_id
            WHERE w.id IS NULL
               OR (w.worked_at IS NOT NULL AND (w.next_follow_up_date IS NULL OR w.next_follow_up_date <= CURDATE()))";
    $p = array_merge($wp, $hp);   // WHERE (dos) params, then HAVING (carrier) params — SQL-appearance order
    $p[] = $pid; $p[] = $pid;
    return [$sql, $p];
}

function ar_candidate_visits(PDO $pdo, array $f): array
{
    [$sql, $p] = ar_candidate_visits_sql($pdo, $f);
    if ($sql === '') return [];
    $st = $pdo->prepare($sql . " ORDER BY v.tier ASC, v.min_dos ASC, v.ins_bal DESC");
    $st->execute($p);
    return $st->fetchAll();
}

/** Fast preview counts for the allocable set — visits, distinct accounts, and per-tier visit counts — computed
 *  ENTIRELY in SQL (COUNT/SUM) instead of fetching every candidate row into PHP just to tally them. */
function ar_candidate_preview(PDO $pdo, array $f): array
{
    $out = ['visits' => 0, 'accounts' => 0, 1 => 0, 2 => 0, 3 => 0];
    [$sql, $p] = ar_candidate_visits_sql($pdo, $f);
    if ($sql === '') return $out;
    $st = $pdo->prepare("SELECT COUNT(*) visits, COUNT(DISTINCT chart_number) accounts,
            SUM(tier=1) t1, SUM(tier=2) t2, SUM(tier=3) t3 FROM ($sql) x");
    $st->execute($p); $r = $st->fetch();
    return ['visits' => (int) $r['visits'], 'accounts' => (int) $r['accounts'],
            1 => (int) $r['t1'], 2 => (int) $r['t2'], 3 => (int) $r['t3']];
}

/** Classified inventory builder — [innerSql, params] for every in-scope visit (ins_bal>0 + carrier/DOS/seq
 *  filters) tagged with a lifecycle `status`: 'unallocated' (never assigned), 'allocated' (assigned, not yet
 *  worked), 'worked' (worked/completed). No status filter or ORDER BY here so both the row list and the counts
 *  reuse identical logic. Status is derived from the visit ledger (ar_visit_work) + account allocation. */
function ar_status_visits_sql(PDO $pdo, array $f): array
{
    $pid  = (int) $f['process_id'];
    $rids = ar_process_report_ids($pdo, $pid);
    if (!$rids) return ['', []];
    $in = implode(',', array_map('intval', $rids));
    $w = ["cr.report_id IN ($in)", "COALESCE(cr.chart_number,'')<>''", "COALESCE(cr.visit_id,'')<>''"]; $wp = [];
    if (!empty($f['dos_year']))  { $w[] = "cr.dos_year = ?"; $wp[] = (int) $f['dos_year']; }
    if (!empty($f['dos_month'])) {
        $ms = array_values(array_unique(array_filter(array_map('intval', (array) $f['dos_month']), fn($m) => $m >= 1 && $m <= 12)));
        if ($ms) { $w[] = "cr.dos_month IN (" . implode(',', array_fill(0, count($ms), '?')) . ")"; foreach ($ms as $m) $wp[] = $m; }
    }
    // carrier filters the WORKING carrier (of the active tier) → HAVING on the computed alias (see ar_candidate_visits_sql).
    $having = "ins_bal > 0 AND " . ar_seq_having($f['seq'] ?? null); $hp = [];
    if (!empty($f['carrier'])) {
        $cs = array_values(array_unique(array_filter(array_map('strval', (array) $f['carrier']), fn($s) => $s !== '')));
        if ($cs) { $having .= " AND carrier_name IN (" . implode(',', array_fill(0, count($cs), '?')) . ")"; foreach ($cs as $c) $hp[] = $c; }
    }
    $tier = ar_tier_case('cr'); $carrier = ar_tier_carrier_expr('cr');
    $sub = "SELECT cr.chart_number, cr.visit_id, $carrier carrier_name, MIN(cr.dos) min_dos,
                   ROUND(SUM(cr.insurance_balance),2) ins_bal, MIN($tier) tier,
                   MAX(cr.facility) facility, MAX(cr.provider_name) provider_name
            FROM collection_raw cr
            WHERE " . implode(' AND ', $w) . "
            GROUP BY cr.chart_number, cr.visit_id
            HAVING $having";
    // A WORKED visit re-enters the allocable pool once its next_follow_up_date arrives (NULL or <= today) → that is
    // 'worked_due' (Eligible to reallocate). Worked with a future follow-up = 'worked_waiting' (Not eligible — only a
    // forced reallocation applies). Not worked but owned = 'allocated'; nothing = 'unallocated'.
    $inner = "SELECT v.chart_number, v.visit_id, v.carrier_name, v.min_dos, v.ins_bal, v.tier,
                     v.facility, v.provider_name,
                     COALESCE(cu.name, au.name) owner_name, w.next_follow_up_date, w.last_worked_date, w.worked_at,
                     CASE WHEN w.worked_at IS NOT NULL AND (w.next_follow_up_date IS NULL OR w.next_follow_up_date <= CURDATE()) THEN 'worked_due'
                          WHEN w.worked_at IS NOT NULL                                                                          THEN 'worked_waiting'
                          WHEN w.id IS NOT NULL OR a.allocated_user IS NOT NULL                                                 THEN 'allocated'
                          ELSE 'unallocated' END AS status
              FROM ($sub) v
              LEFT JOIN ar_visit_work w ON w.process_id=? AND w.chart_number=v.chart_number AND w.visit_id=v.visit_id
              LEFT JOIN ar_allocation a ON a.process_id=? AND a.chart_number=v.chart_number
              LEFT JOIN users cu ON cu.id=w.allocated_user
              LEFT JOIN users au ON au.id=a.allocated_user";
    $p = array_merge($wp, $hp);
    $p[] = $pid; $p[] = $pid;
    return [$inner, $p];
}

/** Rows for the status filter (optionally narrowed to one status). $f['status'] ∈ unallocated|allocated|worked|''. */
function ar_status_visits(PDO $pdo, array $f): array
{
    [$inner, $p] = ar_status_visits_sql($pdo, $f);
    if ($inner === '') return [];
    $status = in_array($f['status'] ?? '', ['unallocated', 'allocated', 'worked_due', 'worked_waiting'], true) ? (string) $f['status'] : '';
    $sql = "SELECT * FROM ($inner) z" . ($status !== '' ? " WHERE z.status = ?" : "") . " ORDER BY z.tier ASC, z.min_dos ASC, z.ins_bal DESC";
    if ($status !== '') $p[] = $status;
    $st = $pdo->prepare($sql); $st->execute($p);
    return $st->fetchAll();
}

/** Counts per lifecycle status for the current filters → ['unallocated'=>n,'allocated'=>n,'worked'=>n]. */
function ar_status_counts(PDO $pdo, array $f): array
{
    $c = ['unallocated' => 0, 'allocated' => 0, 'worked_due' => 0, 'worked_waiting' => 0];
    [$inner, $p] = ar_status_visits_sql($pdo, $f);
    if ($inner === '') return $c;
    $st = $pdo->prepare("SELECT z.status, COUNT(*) n FROM ($inner) z GROUP BY z.status");
    $st->execute($p);
    foreach ($st->fetchAll() as $r) if (isset($c[$r['status']])) $c[$r['status']] = (int) $r['n'];
    return $c;
}

/** Visit-grained OPEN inventory for a process — the data the supervisor downloads (to review) and the body of
 *  the force-allocation template. One row per open visit, with its current ledger owner/status joined in.
 *  $type filters by balance: 'insurance_bal' = ins>0 (default), 'patient_bal' = pat>0, '' / 'all' = either>0
 *  (so patient-balance-only visits are included). Headers map 1:1 to the force-upload parser. */
function ar_inventory_rows(PDO $pdo, int $processId, string $type = 'insurance_bal'): array
{
    $rids = ar_process_report_ids($pdo, $processId);
    if (!$rids) return [];
    $in = implode(',', $rids); $tier = ar_tier_case('cr');
    $having = $type === 'patient_bal' ? 'pat_bal>0' : ($type === 'all' || $type === '' ? '(ins_bal>0 OR pat_bal>0)' : 'ins_bal>0');
    $sub = "SELECT cr.chart_number, cr.visit_id, MAX(cr.carrier1_name) carrier_name, MIN(cr.dos) min_dos,
                   ROUND(SUM(cr.insurance_balance),2) ins_bal, ROUND(SUM(cr.patient_balance),2) pat_bal, MIN($tier) tier
            FROM collection_raw cr
            WHERE cr.report_id IN ($in) AND COALESCE(cr.chart_number,'')<>'' AND COALESCE(cr.visit_id,'')<>''
            GROUP BY cr.chart_number, cr.visit_id HAVING $having";
    $sql = "SELECT v.chart_number, v.visit_id, v.carrier_name, v.min_dos, v.ins_bal, v.pat_bal, v.tier,
                   cu.email cur_email, cu.name cur_name, w.inventory_status cur_status
            FROM ($sub) v
            LEFT JOIN ar_visit_work w ON w.process_id=? AND w.chart_number=v.chart_number AND w.visit_id=v.visit_id
            LEFT JOIN users cu ON cu.id = w.allocated_user
            ORDER BY v.tier ASC, v.chart_number, v.visit_id";
    $st = $pdo->prepare($sql); $st->execute([$processId]);
    return $st->fetchAll();
}

/** Upsert ar_visit_work rows for every OPEN (insurance_balance>0) visit of an account, owned by $user.
 *  Used by force-allocate so a manually-assigned account is visit-grained like a run-allocated one. */
function ar_alloc_visit_rows(PDO $pdo, int $pid, string $chart, int $user, int $by): int
{
    $rids = ar_process_report_ids($pdo, $pid);
    if (!$rids) return 0;
    $in = implode(',', $rids); $tier = ar_tier_case('cr');
    $q = $pdo->prepare("SELECT cr.visit_id, MAX(cr.carrier1_name) carrier_name, MIN(cr.dos) min_dos,
                               ROUND(SUM(cr.insurance_balance),2) ins_bal, MIN($tier) tier
                        FROM collection_raw cr
                        WHERE cr.report_id IN ($in) AND cr.chart_number=? AND COALESCE(cr.visit_id,'')<>''
                        GROUP BY cr.visit_id HAVING ins_bal>0");
    $q->execute([$chart]); $visits = $q->fetchAll();
    if (!$visits) return 0;
    $up = $pdo->prepare("INSERT INTO ar_visit_work
            (process_id,chart_number,visit_id,carrier_name,tier,dos,priority,allocated_user,allocated_by,allocated_at,inventory_status,insurance_balance)
            VALUES (?,?,?,?,?,?,?,?,?,NOW(),'allocated',?)
            ON DUPLICATE KEY UPDATE carrier_name=VALUES(carrier_name), tier=VALUES(tier), dos=VALUES(dos),
              allocated_user=VALUES(allocated_user), allocated_by=VALUES(allocated_by), allocated_at=NOW(),
              inventory_status=IF(worked_at IS NULL,'allocated',inventory_status), insurance_balance=VALUES(insurance_balance)");
    $n = 0;
    foreach ($visits as $v) {
        $prio = AR_TIER_PRIORITY[(int) $v['tier']] ?? 'regular';
        $up->execute([$pid, $chart, $v['visit_id'], $v['carrier_name'], $v['tier'] ? (int) $v['tier'] : null, $v['min_dos'], $prio, $user, $by, $v['ins_bal']]);
        $n++;
    }
    return $n;
}

/** Resolve an ar_allocation row id for (process,chart), creating none — returns 0 if absent. */
function ar_alloc_id(PDO $pdo, int $processId, string $chart): int
{
    $q = $pdo->prepare("SELECT id FROM ar_allocation WHERE process_id=? AND chart_number=?");
    $q->execute([$processId, $chart]);
    return (int) $q->fetchColumn();
}

/** Run criteria-based allocation at VISIT grain. Allocable visits are grouped by patient account; a WHOLE
 *  Patient Account # goes to ONE user (never split), and accounts are distributed to EQUALISE each user's
 *  total VISIT count (greedy least-loaded-first), so production = number of visit ids. Priority per visit is
 *  set automatically from its criteria tier (1→Today's, 2→High, 3→Regular). REWORK (a worked-open visit whose
 *  follow-up date has arrived) either stays with the current owner ($f['rework']='keep') or returns to the pool
 *  ('repool'). Returns ['accounts','visits','per_user'(visit counts)]. */
function ar_run_allocation(PDO $pdo, array $f, array $userIds, int $by): array
{
    $userIds = array_values(array_intersect(array_filter(array_map('intval', $userIds)), ar_allocatable_user_ids($pdo)));
    if (!$userIds) return ['accounts' => 0, 'visits' => 0, 'per_user' => []];
    $reworkMode = (($f['rework'] ?? 'keep') === 'repool') ? 'repool' : 'keep';
    $cands = ar_candidate_visits($pdo, $f);
    if (!$cands) return ['accounts' => 0, 'visits' => 0, 'per_user' => []];
    $pid = (int) $f['process_id'];

    // group allocable visits by patient account
    $accts = [];
    foreach ($cands as $c) {
        $ch = (string) $c['chart_number'];
        if (!isset($accts[$ch])) $accts[$ch] = ['visits'=>[], 'owner'=>($c['owner']!==null?(int)$c['owner']:null), 'count'=>0, 'tier'=>9, 'dos'=>null, 'carrier'=>$c['carrier_name'], 'ins'=>0.0, 'patient'=>'', 'provider'=>''];
        $accts[$ch]['visits'][] = $c;
        $accts[$ch]['count']++;
        $accts[$ch]['tier'] = min($accts[$ch]['tier'], (int) $c['tier']);
        $accts[$ch]['ins'] += (float) $c['ins_bal'];
        if ($accts[$ch]['patient']  === '' && !empty($c['patient_name']))  $accts[$ch]['patient']  = (string) $c['patient_name'];   // snapshot name/provider so AR
        if ($accts[$ch]['provider'] === '' && !empty($c['provider_name'])) $accts[$ch]['provider'] = (string) $c['provider_name']; // survives collection_raw being replaced
        if (!empty($c['min_dos']) && ($accts[$ch]['dos'] === null || $c['min_dos'] < $accts[$ch]['dos'])) $accts[$ch]['dos'] = $c['min_dos'];
    }

    // decide each account's user: keep-with-owner (rework, keep mode) else into the balancing pool
    $load = array_fill_keys($userIds, 0);   // running visit count per selected user
    $assign = []; $pool = [];
    foreach ($accts as $ch => $a) {
        if ($reworkMode === 'keep' && $a['owner'] !== null) {
            $assign[$ch] = $a['owner'];                                   // keep with current owner
            if (isset($load[$a['owner']])) $load[$a['owner']] += $a['count'];
        } else {
            $pool[$ch] = $a;                                             // fresh, or re-pool → balance
        }
    }
    uasort($pool, fn($x, $y) => $y['count'] <=> $x['count']);           // largest accounts first
    foreach ($pool as $ch => $a) { asort($load); $u = (int) array_key_first($load); $assign[$ch] = $u; $load[$u] += $a['count']; }

    $upAcct = $pdo->prepare("INSERT INTO ar_allocation
            (process_id,chart_number,carrier_name,dos,inventory_status,allocation_type,priority,allocated_user,allocated_by,allocated_at,insurance_balance,patient_balance,patient_name,provider_name)
            VALUES (?,?,?,?, 'allocated','insurance_bal',?,?,?,NOW(),?,0,?,?)
            ON DUPLICATE KEY UPDATE carrier_name=VALUES(carrier_name), dos=VALUES(dos), insurance_balance=VALUES(insurance_balance),
              priority=VALUES(priority), inventory_status='allocated', allocation_type='insurance_bal',
              allocated_user=VALUES(allocated_user), allocated_by=VALUES(allocated_by), allocated_at=NOW(),
              patient_name=COALESCE(NULLIF(VALUES(patient_name),''), patient_name), provider_name=COALESCE(NULLIF(VALUES(provider_name),''), provider_name)");
    // (re)allocate a visit: fresh slate (clears any prior work fingerprint so a re-worked visit starts clean and
    // can't immediately re-qualify as rework; visit-grain history stays in ar_allocation_event).
    $upVisit = $pdo->prepare("INSERT INTO ar_visit_work
            (process_id,chart_number,visit_id,carrier_name,tier,dos,priority,allocated_user,allocated_by,allocated_at,inventory_status,insurance_balance)
            VALUES (?,?,?,?,?,?,?,?,?,NOW(),'allocated',?)
            ON DUPLICATE KEY UPDATE carrier_name=VALUES(carrier_name), tier=VALUES(tier), dos=VALUES(dos), priority=VALUES(priority),
              allocated_user=VALUES(allocated_user), allocated_by=VALUES(allocated_by), allocated_at=NOW(),
              inventory_status='allocated', insurance_balance=VALUES(insurance_balance),
              last_worked_date=NULL, worked_at=NULL, worked_by=NULL, notes=NULL, claim_status=NULL, action_taken=NULL, next_follow_up_date=NULL, bucket=NULL, in_sample=0");
    $ownAll = $pdo->prepare("UPDATE ar_visit_work SET allocated_user=? WHERE process_id=? AND chart_number=?");
    $ev = $pdo->prepare("INSERT INTO ar_allocation_event (allocation_id,process_id,chart_number,visit_id,event,user_id,to_user) VALUES (?,?,?,?,'allocate',?,?)");
    // a re-allocated (rework) visit starts a fresh audit cycle — drop any prior cycle's audit so it isn't stale
    $dropAudV = $pdo->prepare("DELETE a, ap FROM ar_visit_audit a LEFT JOIN ar_audit_param ap ON ap.audit_id=a.id
                               JOIN ar_visit_work v ON v.id=a.visit_work_id WHERE v.process_id=? AND v.chart_number=? AND v.visit_id=?");

    $perUser = array_fill_keys($userIds, 0); $nVisits = 0; $nAccts = 0;
    $pdo->beginTransaction();
    foreach ($assign as $ch => $u) {
        $a = $accts[$ch];
        $prio = AR_TIER_PRIORITY[(int) $a['tier']] ?? 'regular';
        $upAcct->execute([$pid, $ch, $a['carrier'], $a['dos'], $prio, $u, $by, round($a['ins'], 2), ($a['patient'] ?? '') ?: null, ($a['provider'] ?? '') ?: null]);
        $aid = ar_alloc_id($pdo, $pid, $ch);
        $ownAll->execute([$u, $pid, $ch]);                              // whole account follows its (possibly new) owner
        foreach ($a['visits'] as $v) {
            $vprio = AR_TIER_PRIORITY[(int) $v['tier']] ?? 'regular';
            $dropAudV->execute([$pid, $ch, $v['visit_id']]);            // clear stale audit before the visit is reset
            $upVisit->execute([$pid, $ch, $v['visit_id'], $v['carrier_name'], (int) $v['tier'], $v['min_dos'], $vprio, $u, $by, $v['ins_bal']]);
            $ev->execute([$aid, $pid, $ch, $v['visit_id'], $by, $u]);
            if (isset($perUser[$u])) $perUser[$u]++;
            $nVisits++;
        }
        $nAccts++;
    }
    $pdo->commit();
    audit_log('ar.allocate', 'ar_allocation', null, null, ['process'=>$pid, 'accounts'=>$nAccts, 'visits'=>$nVisits, 'users'=>$userIds, 'rework'=>$reworkMode]);
    foreach ($perUser as $u => $cnt) if ($cnt > 0 && (int) $u !== $by)   // one ping per worker per run
        notify((int) $u, 'ar', 'New AR claims allocated', $cnt . ' visit(s) were allocated to your AR queue.', '/admin/ar-queue.php');
    return ['accounts' => $nAccts, 'visits' => $nVisits, 'per_user' => $perUser];
}

/** Pull UNTOUCHED inventory back to origin (allocated but never worked) so it can be reshuffled.
 *  Optionally scope to a process and/or a single user. $teamIds (null=unrestricted/sysadmin) fences the
 *  SOURCE so a supervisor only ever pulls back their OWN team's untouched claims. Returns count reset. */
function ar_pullback_untouched(PDO $pdo, ?int $processId, ?int $onlyUser, int $by, ?array $teamIds = null): int
{
    // "untouched" = allocated but NO visit has ever been worked. In the visit-grain model the work markers live
    // on ar_visit_work (ar_allocation.worked_at is not maintained), so we MUST check ar_visit_work — otherwise a
    // partially/fully-worked account would be wrongly pulled back, wiping its work + history.
    $w = ["a.allocated_user IS NOT NULL", AR_UNTOUCHED_SQL]; $p = [];
    if ($processId) { $w[] = "a.process_id=?";     $p[] = $processId; }
    if ($onlyUser)  { $w[] = "a.allocated_user=?"; $p[] = $onlyUser; }
    $teamSql = coding_team_in_sql($teamIds, 'a.allocated_user');   // '' | ' AND a.allocated_user IN (...)' | ' AND 1=0'
    $sel = $pdo->prepare("SELECT a.id,a.process_id,a.chart_number,a.allocated_user FROM ar_allocation a WHERE " . implode(' AND ', $w) . $teamSql);
    $sel->execute($p); $rows = $sel->fetchAll();
    if (!$rows) return 0;
    $ev = $pdo->prepare("INSERT INTO ar_allocation_event (allocation_id,process_id,chart_number,event,user_id,from_user) VALUES (?,?,?,'pullback',?,?)");
    foreach ($rows as $r) $ev->execute([$r['id'], $r['process_id'], $r['chart_number'], $by, $r['allocated_user']]);
    $ids = implode(',', array_map(fn($r) => (int) $r['id'], $rows));
    $pdo->exec("UPDATE ar_allocation SET allocated_user=NULL, allocated_by=NULL, allocated_at=NULL, inventory_status=NULL, priority=NULL, allocation_type=NULL WHERE id IN ($ids)");
    // these accounts were never worked → drop their visit-ledger rows so they re-enter open inventory clean
    $delV = $pdo->prepare("DELETE FROM ar_visit_work WHERE process_id=? AND chart_number=?");
    foreach ($rows as $r) { ar_drop_account_audits($pdo, (int) $r['process_id'], $r['chart_number']); $delV->execute([(int) $r['process_id'], $r['chart_number']]); }
    audit_log('ar.pullback', 'ar_allocation', null, null, ['count'=>count($rows)]);
    return count($rows);
}

/** Process-independent overview of UNTOUCHED inventory (allocated, no visit ever worked) that COULD be pulled
 *  back — team-scoped (null=all/sysadmin). Returns totals plus a by-process and a by-user breakdown (accounts +
 *  visit counts), each ordered by account count desc. Powers the Pull-back tab's dashboard. */
function ar_untouched_breakdown(PDO $pdo, ?array $teamIds): array
{
    $teamSql = coding_team_in_sql($teamIds, 'a.allocated_user');                 // integer ids only — safe to interpolate
    $cond = "a.allocated_user IS NOT NULL AND " . AR_UNTOUCHED_SQL . $teamSql;
    $vc = "LEFT JOIN (SELECT process_id, chart_number, COUNT(*) cnt FROM ar_visit_work GROUP BY process_id, chart_number) vc
           ON vc.process_id=a.process_id AND vc.chart_number=a.chart_number";    // per-account visit count (bounded by the working set)
    $byProcess = $pdo->query("SELECT a.process_id, p.name pname, c.name cname,
            COUNT(*) accounts, COALESCE(SUM(vc.cnt),0) visits
        FROM ar_allocation a JOIN processes p ON p.id=a.process_id JOIN clients c ON c.id=p.client_id $vc
        WHERE $cond GROUP BY a.process_id, p.name, c.name ORDER BY accounts DESC")->fetchAll();
    $byUser = $pdo->query("SELECT a.allocated_user uid, u.name uname,
            COUNT(*) accounts, COALESCE(SUM(vc.cnt),0) visits
        FROM ar_allocation a JOIN users u ON u.id=a.allocated_user $vc
        WHERE $cond GROUP BY a.allocated_user, u.name ORDER BY accounts DESC")->fetchAll();
    return [
        'total_accounts' => array_sum(array_map(fn($r) => (int) $r['accounts'], $byProcess)),
        'total_visits'   => array_sum(array_map(fn($r) => (int) $r['visits'], $byProcess)),
        'by_process'     => $byProcess,
        'by_user'        => $byUser,
    ];
}

/* ============================================================================ FOLLOW-UP HOLD PULL-BACK
 *  A visit that has been WORKED and given a next_follow_up_date is TERMINAL for its owner: ar_save_visit_work
 *  refuses to re-save it and GetNext only serves inventory_status='allocated'. Nothing reopens it on its own —
 *  the account simply sits in the owner's queue (ar-queue.php lists ar_allocation with no status predicate)
 *  until a supervisor re-allocates it. These helpers let the supervisor break that hold EARLY and return the
 *  account to the allocable pool, WITHOUT destroying the work the way the untouched/send-to-origin paths do.
 *
 *  CRITICAL: releasing ownership is not enough. ar_candidate_visits_sql admits a worked visit only when its
 *  next_follow_up_date is NULL or past (and does not look at ownership at all), so an owner-less visit holding a
 *  FUTURE follow-up date matches nothing, appears on no dashboard, and is invisible for up to AR_MAX_FOLLOWUP_DAYS.
 *  The follow-up date MUST be cleared on both grains — that flips the visit to 'worked_due' = allocable.
 */

/** Accounts EXCLUDED from a follow-up pull-back, because releasing the owner would strand live work:
 *   1. a pending routing hand-off (bucket set, not yet confirmed sent) — ar-verify.php fences its list, its
 *      counts AND its confirm UPDATE on v.allocated_user, and `NULL IN (team)` is NULL, so a non-sysadmin
 *      supervisor could never confirm it again and the escalation to coding/client would be silently lost;
 *   2. an OPEN rebuttal (rebuttal_status='raised') — the dispute itself survives (it keys on worker_id), but the
 *      first re-allocation hard-deletes the audit row mid-TAT, destroying the AR user's open appeal.
 *  Account-grain, because the whole Patient Account # always moves together. */
const AR_HOLD_EXCLUDE_SQL = "
    NOT EXISTS (SELECT 1 FROM ar_visit_work xb WHERE xb.process_id=a.process_id AND xb.chart_number=a.chart_number
                AND xb.bucket IS NOT NULL AND xb.sent_at IS NULL)
    AND NOT EXISTS (SELECT 1 FROM ar_visit_work xr JOIN ar_visit_audit xa ON xa.visit_work_id=xr.id
                WHERE xr.process_id=a.process_id AND xr.chart_number=a.chart_number AND xa.rebuttal_status='raised')";

/** Shared WHERE builder for the follow-up-hold population → [sql, params], for alias `a` = ar_allocation.
 *  $scope 'waiting' (default) = the follow-up date is still in the FUTURE — the genuinely unreachable inventory.
 *  $scope 'all' also includes already-DUE follow-ups (those are allocable again on their own, via Run allocation
 *  with rework='repool', so 'waiting' is the honest default and 'all' is the deliberate override).
 *  Every caller (dashboard, export, write) uses this so the counts can never drift from what the button acts on. */
function ar_followup_hold_where(?array $teamIds, ?int $processId, ?int $onlyUser, string $scope = 'waiting'): array
{
    $future = $scope === 'all' ? '' : " AND xh.next_follow_up_date > CURDATE()";
    $w = ["a.allocated_user IS NOT NULL",
          "EXISTS (SELECT 1 FROM ar_visit_work xh WHERE xh.process_id=a.process_id AND xh.chart_number=a.chart_number
                   AND xh.worked_at IS NOT NULL AND xh.next_follow_up_date IS NOT NULL$future)",
          AR_HOLD_EXCLUDE_SQL];
    $p = [];
    if ($processId) { $w[] = "a.process_id=?";     $p[] = $processId; }
    if ($onlyUser)  { $w[] = "a.allocated_user=?"; $p[] = $onlyUser; }
    return [implode(' AND ', $w) . coding_team_in_sql($teamIds, 'a.allocated_user'), $p];
}

/** Dashboard for the follow-up-hold population — totals + a by-user breakdown, plus how many accounts were
 *  HELD BACK by the exclusion rules (so the supervisor sees why a number doesn't reconcile). Team-scoped. */
function ar_followup_hold_breakdown(PDO $pdo, ?array $teamIds, ?int $processId, string $scope = 'waiting'): array
{
    [$where, $p] = ar_followup_hold_where($teamIds, $processId, null, $scope);
    // per-account visit rollup, restricted to the same accounts so the numbers match the write exactly
    $agg = "COUNT(DISTINCT CONCAT(a.process_id,'|',a.chart_number)) accounts,
            COUNT(vw.id) visits,
            COALESCE(SUM(vw.worked_at IS NOT NULL),0) worked_visits,
            ROUND(COALESCE(SUM(vw.insurance_balance),0),2) ins_bal";
    // LEFT JOIN the display table, never INNER — ar_* tables carry no FKs and users.php hard-deletes logins, so an
    // owner id can dangle. An INNER JOIN would hide those accounts from the count and the export while the write
    // (which joins nothing) still released them, and the shortfall would be misreported as an exclusion.
    $from = "FROM ar_allocation a LEFT JOIN users u ON u.id=a.allocated_user
             LEFT JOIN ar_visit_work vw ON vw.process_id=a.process_id AND vw.chart_number=a.chart_number";
    $st = $pdo->prepare("SELECT a.allocated_user uid, COALESCE(u.name, CONCAT('(deleted user #', a.allocated_user, ')')) uname,
            $agg $from WHERE $where GROUP BY a.allocated_user, u.name ORDER BY accounts DESC");
    $st->execute($p); $byUser = $st->fetchAll();
    // the same population WITHOUT the exclusion rules → the difference is what was held back and why
    $future = $scope === 'all' ? '' : " AND xh.next_follow_up_date > CURDATE()";
    $rawW = ["a.allocated_user IS NOT NULL",
             "EXISTS (SELECT 1 FROM ar_visit_work xh WHERE xh.process_id=a.process_id AND xh.chart_number=a.chart_number
                      AND xh.worked_at IS NOT NULL AND xh.next_follow_up_date IS NOT NULL$future)"];
    $rawP = [];
    if ($processId) { $rawW[] = "a.process_id=?"; $rawP[] = $processId; }
    $rawSql = implode(' AND ', $rawW) . coding_team_in_sql($teamIds, 'a.allocated_user');
    $st = $pdo->prepare("SELECT COUNT(*) FROM ar_allocation a WHERE $rawSql");
    $st->execute($rawP); $rawAccounts = (int) $st->fetchColumn();
    return [
        'total_accounts' => array_sum(array_map(fn($r) => (int) $r['accounts'], $byUser)),
        'total_visits'   => array_sum(array_map(fn($r) => (int) $r['visits'], $byUser)),
        'total_worked'   => array_sum(array_map(fn($r) => (int) $r['worked_visits'], $byUser)),
        'total_ins_bal'  => array_sum(array_map(fn($r) => (float) $r['ins_bal'], $byUser)),
        'by_user'        => $byUser,
        'excluded'       => max(0, $rawAccounts - array_sum(array_map(fn($r) => (int) $r['accounts'], $byUser))),
    ];
}

/** Visit-grain rows of the follow-up-hold population — the supervisor's review/export list. */
function ar_followup_hold_rows(PDO $pdo, ?array $teamIds, ?int $processId, string $scope = 'waiting', ?int $onlyUser = null): array
{
    [$where, $p] = ar_followup_hold_where($teamIds, $processId, $onlyUser, $scope);
    $st = $pdo->prepare("SELECT a.process_id, a.chart_number, a.patient_name,
                COALESCE(u.name, CONCAT('(deleted user #', a.allocated_user, ')')) owner_name,
                c.name cname, pr.name pname,
                vw.visit_id, vw.carrier_name, vw.dos, vw.insurance_balance, vw.inventory_status,
                vw.last_worked_date, vw.claim_status, vw.action_taken, vw.next_follow_up_date, vw.notes,
                CASE WHEN vw.worked_at IS NULL THEN 'not_worked'
                     WHEN vw.next_follow_up_date IS NULL OR vw.next_follow_up_date <= CURDATE() THEN 'due'
                     ELSE 'waiting' END hold_state
            FROM ar_allocation a
            LEFT JOIN users u ON u.id=a.allocated_user
            LEFT JOIN processes pr ON pr.id=a.process_id
            LEFT JOIN clients c ON c.id=pr.client_id
            LEFT JOIN ar_visit_work vw ON vw.process_id=a.process_id AND vw.chart_number=a.chart_number
            WHERE $where
            ORDER BY u.name, a.chart_number, vw.next_follow_up_date, vw.visit_id");
    $st->execute($p);
    return $st->fetchAll();
}

/** Summary of FOLLOW-UP-DUE claims — worked visits whose follow-up date has arrived (NULL or <= today) and are
 *  still owned, i.e. ready to be reallocated — grouped client » process » carrier with account + VISIT counts and
 *  snapshot insurance balance. Team-scoped (fenced on the current owner). $processId narrows to one process;
 *  omit it for a cross-process overview. Read straight from ar_visit_work (no collection_raw join), so it is cheap
 *  and works across every process at once. Returns ['rows'=>[...], 'accounts'=>n, 'visits'=>n, 'ins_bal'=>f].
 *  NOTE: insurance_balance is the allocation-time SNAPSHOT (collection_raw is replaced on each upload); the counts
 *  are exact but the balance is indicative. */
function ar_followup_due_summary(PDO $pdo, ?array $teamIds, ?int $processId = null): array
{
    $w = ["v.allocated_user IS NOT NULL", "v.worked_at IS NOT NULL",
          "(v.next_follow_up_date IS NULL OR v.next_follow_up_date <= CURDATE())"];
    $p = [];
    if ($processId) { $w[] = "v.process_id=?"; $p[] = $processId; }
    $where = implode(' AND ', $w) . coding_team_in_sql($teamIds, 'v.allocated_user');   // integer-id fence — safe
    $st = $pdo->prepare("SELECT c.name cname, pr.id process_id, pr.name pname,
                COALESCE(NULLIF(v.carrier_name,''),'(no carrier)') carrier,
                COUNT(DISTINCT v.chart_number) accounts, COUNT(*) visits,
                ROUND(COALESCE(SUM(v.insurance_balance),0),2) ins_bal
            FROM ar_visit_work v
            JOIN processes pr ON pr.id=v.process_id
            JOIN clients c ON c.id=pr.client_id
            WHERE $where
            GROUP BY c.name, pr.id, pr.name, carrier
            ORDER BY c.name, pr.name, visits DESC");
    $st->execute($p);
    $rows = $st->fetchAll();
    // true grand totals (a distinct account can span carriers, so the per-row account column is NOT additive — count
    // distinct (process,chart) over the same predicate instead). $where touches only v.* so no join is needed here.
    $tot = $pdo->prepare("SELECT COUNT(DISTINCT CONCAT(v.process_id,'|',v.chart_number)) accounts, COUNT(*) visits,
                ROUND(COALESCE(SUM(v.insurance_balance),0),2) ins_bal FROM ar_visit_work v WHERE $where");
    $tot->execute($p); $t = $tot->fetch() ?: ['accounts'=>0, 'visits'=>0, 'ins_bal'=>0];
    return ['rows' => $rows, 'accounts' => (int) $t['accounts'], 'visits' => (int) $t['visits'], 'ins_bal' => (float) $t['ins_bal']];
}

/** Break the follow-up hold: release ownership of the matching accounts and return them to the allocable pool,
 *  PRESERVING the work (worked_at / worked_by / claim_status / action_taken / notes / in_sample and the whole
 *  audit chain) — unlike ar_pullback_untouched and ar_reassign-to-origin, which delete the visit ledger outright.
 *  The write order matters and is all one transaction:
 *    1. backfill worked_by from allocated_user FIRST — rows created by the ar_visit_work backfill migration have
 *       worked_by NULL, and once the owner is gone ar_save_audit would attribute the audit to worker_id 0,
 *       stranding it: the AR user could never see or dispute it and it would score toward nobody's quality;
 *    2. clear the owner AND next_follow_up_date on the worked rows (see the header note — not optional);
 *    3. DELETE the never-worked rows of the same accounts so they re-enter open inventory clean, exactly as the
 *       untouched path does. NOT ar_drop_account_audits() — that is account-grain and would take the worked
 *       siblings' audits with it;
 *    4. release the account row but KEEP it (patient_name/provider_name are the PHI fallback once collection_raw
 *       is replaced).
 *  Whole-account grain throughout — never a subset of visit ids, or the account splits across owners. */
function ar_pullback_followup(PDO $pdo, ?int $processId, ?int $onlyUser, int $by, ?array $teamIds = null, string $scope = 'waiting'): array
{
    [$where, $p] = ar_followup_hold_where($teamIds, $processId, $onlyUser, $scope);
    $sel = $pdo->prepare("SELECT a.id, a.process_id, a.chart_number, a.allocated_user FROM ar_allocation a WHERE $where");
    $sel->execute($p); $rows = $sel->fetchAll();
    if (!$rows) return ['accounts' => 0, 'visits' => 0, 'per_user' => []];

    $fixBy  = $pdo->prepare("UPDATE ar_visit_work SET worked_by=allocated_user
                             WHERE process_id=? AND chart_number=? AND worked_at IS NOT NULL AND worked_by IS NULL AND allocated_user IS NOT NULL");
    $relV   = $pdo->prepare("UPDATE ar_visit_work SET allocated_user=NULL, next_follow_up_date=NULL
                             WHERE process_id=? AND chart_number=? AND worked_at IS NOT NULL");
    $delV   = $pdo->prepare("DELETE FROM ar_visit_work WHERE process_id=? AND chart_number=? AND worked_at IS NULL");
    $cntV   = $pdo->prepare("SELECT COUNT(*) FROM ar_visit_work WHERE process_id=? AND chart_number=? AND worked_at IS NOT NULL");
    $ev     = $pdo->prepare("INSERT INTO ar_allocation_event (allocation_id,process_id,chart_number,event,user_id,from_user)
                             VALUES (?,?,?,'pullback_followup',?,?)");
    $nVisits = 0; $perUser = [];
    $pdo->beginTransaction();
    foreach ($rows as $r) {
        $pid = (int) $r['process_id']; $ch = (string) $r['chart_number']; $own = (int) $r['allocated_user'];
        $cntV->execute([$pid, $ch]); $held = (int) $cntV->fetchColumn();
        $fixBy->execute([$pid, $ch]);                                    // 1. attribution BEFORE the owner is lost
        $ev->execute([(int) $r['id'], $pid, $ch, $by, $own ?: null]);
        $relV->execute([$pid, $ch]);                                     // 2. release + clear the follow-up hold
        $delV->execute([$pid, $ch]);                                     // 3. never-worked siblings re-enter clean
        $nVisits += $held;
        $perUser[$own] = ($perUser[$own] ?? 0) + 1;
    }
    // 4. release the account rows (keep the row + its name snapshots); clear the stale account-grain fingerprint too
    $ids = implode(',', array_map(fn($r) => (int) $r['id'], $rows));
    $pdo->exec("UPDATE ar_allocation SET allocated_user=NULL, allocated_by=NULL, allocated_at=NULL,
                    inventory_status=NULL, priority=NULL, allocation_type=NULL, next_follow_up_date=NULL WHERE id IN ($ids)");
    $pdo->commit();
    audit_log('ar.pullback_followup', 'ar_allocation', null, null,
        ['process' => $processId, 'only_user' => $onlyUser, 'scope' => $scope, 'accounts' => count($rows), 'visits' => $nVisits]);
    // tell the losing worker — these are dated call-backs they committed to; neither existing release path notifies.
    foreach ($perUser as $u => $cnt) if ($u && (int) $u !== $by)
        notify((int) $u, 'ar', 'AR accounts pulled back', $cnt . ' account(s) awaiting follow-up were pulled back to the allocation pool by your supervisor.', '/admin/ar-queue.php');
    return ['accounts' => count($rows), 'visits' => $nVisits, 'per_user' => $perUser];
}

/** Reassign selected ar_allocation rows to $toUser (NULL = send back to origin). $teamIds (null=unrestricted/
 *  sysadmin) fences BOTH ends: the source rows must currently be owned by the team, and the destination must
 *  be on the team — so a supervisor can never move another team's account. Returns count. */
function ar_reassign(PDO $pdo, array $allocIds, ?int $toUser, int $by, ?array $teamIds = null): int
{
    $allocIds = array_values(array_filter(array_map('intval', $allocIds)));
    if (!$allocIds) return 0;
    if ($toUser !== null && !in_array((int) $toUser, ar_allocatable_user_ids($pdo), true)) return 0;  // refuse a non-allocatable target
    if ($toUser !== null && $teamIds !== null && !in_array((int) $toUser, array_map('intval', $teamIds), true)) return 0;  // destination off-team
    $in = implode(',', array_fill(0, count($allocIds), '?'));
    $teamSql = coding_team_in_sql($teamIds, 'allocated_user');               // '' | ' AND ... IN (...)' | ' AND 1=0'
    $sel = $pdo->prepare("SELECT id,process_id,chart_number,allocated_user FROM ar_allocation WHERE id IN ($in)$teamSql");
    $sel->execute($allocIds); $rows = $sel->fetchAll();
    if (!$rows) return 0;
    // act ONLY on the rows that survived the team fence (not the raw posted ids)
    $in = implode(',', array_map(fn($r) => (int) $r['id'], $rows));
    if ($toUser) {
        $st = $pdo->prepare("UPDATE ar_allocation SET allocated_user=?, allocated_by=?, allocated_at=NOW(),
                              inventory_status=IF(COALESCE(inventory_status,'')='','allocated',inventory_status) WHERE id IN ($in)");
        $st->execute([(int) $toUser, $by]);
    } else {
        // Back to origin = unworked again: clear allocation AND the work fingerprint so it re-allocates clean
        // (history is preserved in ar_allocation_event). Otherwise stale worked_at/last_worked_date would make
        // the freshly-returned chart un-pullback-able and surface old notes to the next owner.
        $st = $pdo->prepare("UPDATE ar_allocation SET allocated_user=NULL, allocated_by=NULL, allocated_at=NULL, inventory_status=NULL, priority=NULL, allocation_type=NULL,
                              last_worked_date=NULL, worked_at=NULL, notes=NULL, claim_status=NULL, action_taken=NULL, next_follow_up_date=NULL WHERE id IN ($in)");
        $st->execute();
    }
    // The whole patient account moves together: re-point its visit rows to the new owner, or drop them on send-to-origin.
    if ($toUser) { $sv = $pdo->prepare("UPDATE ar_visit_work SET allocated_user=? WHERE process_id=? AND chart_number=?");
        foreach ($rows as $r) $sv->execute([(int) $toUser, (int) $r['process_id'], $r['chart_number']]); }
    else { $sv = $pdo->prepare("DELETE FROM ar_visit_work WHERE process_id=? AND chart_number=?");
        foreach ($rows as $r) { ar_drop_account_audits($pdo, (int) $r['process_id'], $r['chart_number']); $sv->execute([(int) $r['process_id'], $r['chart_number']]); } }
    $ev = $pdo->prepare("INSERT INTO ar_allocation_event (allocation_id,process_id,chart_number,event,user_id,from_user,to_user) VALUES (?,?,?,?,?,?,?)");
    foreach ($rows as $r) $ev->execute([$r['id'], $r['process_id'], $r['chart_number'], $toUser ? 'reassign' : 'pullback', $by, $r['allocated_user'], $toUser ?: null]);
    audit_log('ar.reassign', 'ar_allocation', null, null, ['count'=>count($rows), 'to'=>$toUser]);
    if ($toUser && (int) $toUser !== $by)
        notify((int) $toUser, 'ar', 'AR claims reassigned to you', count($rows) . ' collection claim(s) were reassigned to your AR queue.', '/admin/ar-queue.php');
    return count($rows);
}

/** Force-allocate specific (process,chart) claims to specific users (from the supervisor's template
 *  upload). $items = [['process_id','chart_number','user_id','allocation_type','priority'], ...]. */
function ar_force_allocate(PDO $pdo, array $items, int $by): array
{
    // Account upsert. On an owner CHANGE the work fingerprint is cleared (the IF()s read the pre-update
    // allocated_user because it is assigned LAST) so the new owner never inherits the prior owner's work.
    $up = $pdo->prepare("INSERT INTO ar_allocation
            (process_id,chart_number,carrier_name,dos,inventory_status,allocation_type,priority,allocated_user,allocated_by,allocated_at,insurance_balance,patient_balance)
            VALUES (?,?,?,?, 'allocated',?,?,?,?,NOW(),?,?)
            ON DUPLICATE KEY UPDATE inventory_status='allocated', allocation_type=VALUES(allocation_type),
              priority=VALUES(priority), allocated_by=VALUES(allocated_by), allocated_at=NOW(),
              carrier_name=VALUES(carrier_name), dos=VALUES(dos),
              insurance_balance=VALUES(insurance_balance), patient_balance=VALUES(patient_balance),
              last_worked_date   =IF(allocated_user<>VALUES(allocated_user),NULL,last_worked_date),
              worked_at          =IF(allocated_user<>VALUES(allocated_user),NULL,worked_at),
              notes              =IF(allocated_user<>VALUES(allocated_user),NULL,notes),
              claim_status       =IF(allocated_user<>VALUES(allocated_user),NULL,claim_status),
              action_taken       =IF(allocated_user<>VALUES(allocated_user),NULL,action_taken),
              next_follow_up_date=IF(allocated_user<>VALUES(allocated_user),NULL,next_follow_up_date),
              allocated_user     =VALUES(allocated_user)");
    $ev = $pdo->prepare("INSERT INTO ar_allocation_event (allocation_id,process_id,chart_number,event,user_id,to_user) VALUES (?,?,?,'force_allocate',?,?)");
    // re-point the WHOLE account's visit rows to the forced owner (incl. closed/stale rows), resetting work only where the owner changed
    $resetV = $pdo->prepare("UPDATE ar_visit_work SET inventory_status='allocated', last_worked_date=NULL, worked_at=NULL, worked_by=NULL, notes=NULL, claim_status=NULL, action_taken=NULL, next_follow_up_date=NULL, bucket=NULL, in_sample=0
                             WHERE process_id=? AND chart_number=? AND COALESCE(allocated_user,0)<>?");
    // when the owner changes, the work is wiped → its audit is stale, drop it too (keep audits where owner is unchanged)
    $dropAudOwn = $pdo->prepare("DELETE a, ap FROM ar_visit_audit a LEFT JOIN ar_audit_param ap ON ap.audit_id=a.id
                                 JOIN ar_visit_work v ON v.id=a.visit_work_id WHERE v.process_id=? AND v.chart_number=? AND COALESCE(v.allocated_user,0)<>?");
    $ownV   = $pdo->prepare("UPDATE ar_visit_work SET allocated_user=? WHERE process_id=? AND chart_number=?");
    $n = 0; $skipped = 0; $perUser = [];
    $pdo->beginTransaction();
    foreach ($items as $it) {
        $pid = (int) ($it['process_id'] ?? 0); $chart = trim((string) ($it['chart_number'] ?? '')); $u = (int) ($it['user_id'] ?? 0);
        if (!$pid || $chart === '' || !$u || !in_array($u, ar_allocatable_user_ids($pdo), true)) { $skipped++; continue; }
        if (!ar_process_in_ar_vertical($pdo, $pid)) { $skipped++; continue; }   // fence: AR-vertical processes only (no cross-vertical force-allocate)
        // snapshot live balances/carrier/dos for the patient
        $rids = ar_process_report_ids($pdo, $pid);
        $car = null; $dos = null; $ib = 0; $pb = 0;
        if ($rids) {
            $q = $pdo->prepare(sprintf("SELECT MAX(carrier1_name) c, MIN(dos) d, ROUND(SUM(insurance_balance),2) ib, ROUND(SUM(patient_balance),2) pb
                                FROM collection_raw WHERE report_id IN (%s) AND chart_number=?", implode(',', $rids)));
            $q->execute([$chart]); $r = $q->fetch();
            if ($r) { $car = $r['c']; $dos = $r['d']; $ib = $r['ib']; $pb = $r['pb']; }
        }
        // honour an explicit valid type, else pick by the live balance (open insurance vs patient-balance)
        $type = in_array($it['allocation_type'] ?? '', ['insurance_bal','patient_bal'], true) ? $it['allocation_type'] : ((float) $ib > 0 ? 'insurance_bal' : 'patient_bal');
        $prio = isset(AR_PRIORITIES[$it['priority'] ?? '']) ? $it['priority'] : 'regular';
        $up->execute([$pid, $chart, $car, $dos, $type, $prio, $u, $by, $ib, $pb]);
        $aid = ar_alloc_id($pdo, $pid, $chart);
        $ev->execute([$aid, $pid, $chart, $by, $u]);
        $dropAudOwn->execute([$pid, $chart, $u]);             // drop stale audits on rows whose owner changes
        $resetV->execute([$pid, $chart, $u]);                 // clear work on rows whose owner changes
        $ownV->execute([$u, $pid, $chart]);                   // whole account follows the forced owner
        ar_alloc_visit_rows($pdo, $pid, $chart, $u, $by);     // create/refresh the OPEN visit rows
        $n++; $perUser[$u] = ($perUser[$u] ?? 0) + 1;
    }
    $pdo->commit();
    audit_log('ar.force_allocate', 'ar_allocation', null, null, ['count'=>$n, 'skipped'=>$skipped]);
    foreach ($perUser as $u => $cnt) if ($cnt > 0 && (int) $u !== $by)
        notify((int) $u, 'ar', 'AR claims assigned to you', $cnt . ' collection claim(s) were assigned to your AR queue.', '/admin/ar-queue.php');
    return ['allocated' => $n, 'skipped' => $skipped];
}

/** When nothing new is left to allocate, re-distribute a process's already-allocated inventory across
 *  $userIds by next_follow_up_date (oldest→newest) then last_worked_date (oldest→newest). Returns count. */
function ar_reallocate_followup(PDO $pdo, int $processId, array $userIds, int $by, ?array $teamIds = null): int
{
    $userIds = array_values(array_intersect(array_filter(array_map('intval', $userIds)), ar_allocatable_user_ids($pdo)));
    if ($teamIds !== null) $userIds = array_values(array_intersect($userIds, array_map('intval', $teamIds)));  // destinations on-team only
    if (!$userIds) return 0;
    $teamSql = coding_team_in_sql($teamIds, 'allocated_user');               // fence the SOURCE rows to the team
    $sel = $pdo->prepare("SELECT id,chart_number,allocated_user FROM ar_allocation
        WHERE process_id=? AND allocated_user IS NOT NULL$teamSql
        ORDER BY (next_follow_up_date IS NULL), next_follow_up_date ASC, (last_worked_date IS NULL), last_worked_date ASC, id ASC");
    $sel->execute([$processId]); $rows = $sel->fetchAll();
    if (!$rows) return 0;
    $st = $pdo->prepare("UPDATE ar_allocation SET allocated_user=?, allocated_by=?, allocated_at=NOW() WHERE id=?");
    $ownV = $pdo->prepare("UPDATE ar_visit_work SET allocated_user=? WHERE process_id=? AND chart_number=?");
    $ev = $pdo->prepare("INSERT INTO ar_allocation_event (allocation_id,process_id,chart_number,event,user_id,from_user,to_user) VALUES (?,?,?,'reallocate',?,?,?)");
    $n = 0; $i = 0;
    $pdo->beginTransaction();
    foreach ($rows as $r) {
        $u = $userIds[$i % count($userIds)]; $i++;
        $st->execute([$u, $by, (int) $r['id']]);
        $ownV->execute([$u, $processId, $r['chart_number']]);   // whole account's visits follow the new owner
        $ev->execute([(int) $r['id'], $processId, $r['chart_number'], $by, $r['allocated_user'], $u]);
        $n++;
    }
    $pdo->commit();
    audit_log('ar.reallocate', 'ar_allocation', null, null, ['process'=>$processId, 'count'=>$n]);
    return $n;
}

/* ============================================================================ PHASE 3 — PER-VISIT WORK
 *  The AR worker opens ONE patient account (GetNext), sees its demographics + every allocated visit, and tags
 *  EACH visit individually (claim_status + action_taken + follow-up ≤45d + notes). A worked visit moves to its
 *  own bucket; the account is NOT held until every visit is worked. */

/** The routing bucket implied by an action_taken value (text-driven, since the lists are supervisor-editable). */
function ar_visit_bucket_for_action(string $action): ?string
{
    $a = strtoupper(trim($action));
    if ($a === '') return null;
    if (str_contains($a, 'SENT TO CODING'))      return 'sent_to_coding';
    if (str_contains($a, 'ESCALATED TO CLIENT')) return 'escalated_to_client';
    if (str_contains($a, 'CONTACT PATIENT'))     return 'contact_patient';   // supervisor routes a patient call-out
    return null;                                  // a normal worked visit (re-enters as rework at its follow-up)
}

/** GetNext for the AR worker: the most-urgent patient account that still has an UNWORKED allocated visit.
 *  Returns ['process_id','chart_number'] or null. Whole accounts are served together (one account at a time). */
function ar_next_account(PDO $pdo, int $uid): ?array
{
    $st = $pdo->prepare("SELECT process_id, chart_number
        FROM ar_visit_work
        WHERE allocated_user=? AND inventory_status='allocated'
        GROUP BY process_id, chart_number
        ORDER BY MIN(FIELD(priority,'today','high','regular')) ASC, MIN(dos) ASC, chart_number
        LIMIT 1");
    $st->execute([$uid]);
    return $st->fetch() ?: null;
}

/** The next UNWORKED visit of THIS account for the worker (priority → DOS → visit_id order), optionally skipping
 *  one visit id. Returns the visit_id or null when the account has no more unworked visits. Powers the in-account
 *  "next visit" flow so a worker finishes a patient before GetNext moves them to a new account. */
function ar_next_unworked_visit(PDO $pdo, int $uid, int $pid, string $chart, ?string $exceptVisit = null): ?string
{
    $sql = "SELECT visit_id FROM ar_visit_work
            WHERE allocated_user=? AND process_id=? AND chart_number=? AND inventory_status <> 'worked'";
    $p = [$uid, $pid, $chart];
    if ($exceptVisit !== null && $exceptVisit !== '') { $sql .= " AND visit_id <> ?"; $p[] = $exceptVisit; }
    $sql .= " ORDER BY FIELD(priority,'today','high','regular'), dos, visit_id LIMIT 1";
    $st = $pdo->prepare($sql); $st->execute($p);
    $v = $st->fetchColumn();
    return $v !== false ? (string) $v : null;
}

/** Live patient demographics for an account (from the CURRENT collection_raw). */
function ar_account_demographics(PDO $pdo, int $pid, string $chart): ?array
{
    $rids = ar_process_report_ids($pdo, $pid);
    if (!$rids) return null;
    $in = implode(',', $rids);
    $q = $pdo->prepare("SELECT MAX(patient_first_name) first_name, MAX(patient_last_name) last_name,
                               MAX(provider_name) provider, MAX(financial_class) financial_class, MAX(carrier1_name) carrier,
                               MAX(patient_zip) zip, ROUND(SUM(insurance_balance),2) ins_bal, ROUND(SUM(patient_balance),2) pat_bal,
                               COUNT(DISTINCT visit_id) visits, MIN(dos) min_dos, MAX(dos) max_dos
                        FROM collection_raw WHERE report_id IN ($in) AND chart_number=?");
    $q->execute([$chart]);
    return $q->fetch() ?: null;
}

/** The worker's visit-ledger rows for one account, each enriched with LIVE balance / carrier / CPT(s). */
function ar_account_work_visits(PDO $pdo, int $uid, int $pid, string $chart): array
{
    $st = $pdo->prepare("SELECT * FROM ar_visit_work WHERE allocated_user=? AND process_id=? AND chart_number=?
                         ORDER BY FIELD(priority,'today','high','regular'), dos, visit_id");
    $st->execute([$uid, $pid, $chart]); $visits = $st->fetchAll();
    if (!$visits) return [];
    $rids = ar_process_report_ids($pdo, $pid); $live = [];
    if ($rids) {
        $in = implode(',', $rids);
        // aggregate ALL of a visit's CPT lines (a visit can have >1 CPT) into one row of live financials
        $q = $pdo->prepare("SELECT visit_id,
                ROUND(SUM(insurance_balance),2) ins_bal, ROUND(SUM(patient_balance),2) pat_bal,
                ROUND(SUM(charge_amount),2) charge, ROUND(SUM(amount_allowed),2) allowed,
                ROUND(SUM(primary_ins_paid),2) ppaid, ROUND(SUM(secondary_ins_paid),2) spaid,
                ROUND(SUM(tertiary_ins_paid),2) tpaid, ROUND(SUM(wo_amount),2) wo,
                MIN(dos) dos, MAX(carrier1_name) carrier, MAX(provider_name) provider,
                GROUP_CONCAT(DISTINCT charge_code ORDER BY charge_code SEPARATOR ', ') cpts
            FROM collection_raw WHERE report_id IN ($in) AND chart_number=? GROUP BY visit_id");
        $q->execute([$chart]);
        foreach ($q->fetchAll() as $r) $live[$r['visit_id']] = $r;
    }
    foreach ($visits as &$v) {
        $l = $live[$v['visit_id']] ?? null;
        $v['live_ins_bal']  = $l['ins_bal']  ?? null;
        $v['live_pat_bal']  = $l['pat_bal']  ?? null;
        $v['live_carrier']  = $l['carrier']  ?? $v['carrier_name'];
        $v['live_provider'] = $l['provider'] ?? null;
        $v['charge']        = $l['charge']   ?? null;
        $v['allowed']       = $l['allowed']  ?? null;
        $v['ppaid']         = $l['ppaid']    ?? null;
        $v['spaid']         = $l['spaid']    ?? null;
        $v['tpaid']         = $l['tpaid']    ?? null;
        $v['wo']            = $l['wo']       ?? null;
        $v['cpts']          = $l['cpts']     ?? '';
    }
    return $visits;
}

/** Full live detail for ONE visit id of an account — the per-CPT charge lines plus provider / carrier(s) /
 *  financial class and rolled-up totals, read straight from the CURRENT collection_raw. */
function ar_visit_detail(PDO $pdo, int $pid, string $chart, string $visitId): array
{
    $out = ['lines' => [], 'provider' => null, 'financial_class' => null, 'dos' => null,
            'carrier1' => null, 'carrier2' => null, 'carrier3' => null,
            'charges' => 0.0, 'ppaid' => 0.0, 'spaid' => 0.0, 'tpaid' => 0.0, 'wo' => 0.0,
            'paid' => 0.0, 'ins_bal' => 0.0, 'pat_bal' => 0.0];
    $rids = ar_process_report_ids($pdo, $pid);
    if (!$rids) return $out;
    $in = implode(',', $rids);
    $q = $pdo->prepare("SELECT charge_code, charge_code_desc, units, charge_amount, amount_allowed,
                               primary_ins_paid, secondary_ins_paid, tertiary_ins_paid, wo_amount, patient_payment_amount,
                               insurance_balance, patient_balance, dos,
                               provider_name, financial_class, carrier1_name, carrier2_name, carrier3_name
                        FROM collection_raw WHERE report_id IN ($in) AND chart_number=? AND visit_id=?
                        ORDER BY charge_code");
    $q->execute([$chart, $visitId]); $out['lines'] = $q->fetchAll();
    foreach ($out['lines'] as $l) {
        $out['charges'] += (float) $l['charge_amount'];
        $out['ppaid']   += (float) $l['primary_ins_paid'];
        $out['spaid']   += (float) $l['secondary_ins_paid'];
        $out['tpaid']   += (float) $l['tertiary_ins_paid'];
        $out['wo']      += (float) $l['wo_amount'];
        $out['paid']    += (float) $l['primary_ins_paid'] + (float) $l['secondary_ins_paid'] + (float) $l['tertiary_ins_paid'];
        $out['ins_bal'] += (float) $l['insurance_balance'];
        $out['pat_bal'] += (float) $l['patient_balance'];
        $out['provider']        ??= $l['provider_name'];
        $out['financial_class'] ??= $l['financial_class'];
        $out['dos']             ??= $l['dos'];
        $out['carrier1']        ??= $l['carrier1_name'];
        $out['carrier2']        ??= $l['carrier2_name'];
        $out['carrier3']        ??= $l['carrier3_name'];
    }
    return $out;
}

/** Worked HISTORY for a visit id — every prior 'work' event (who, when, claim_status, action_taken,
 *  follow-up, notes), newest first. Survives the daily collection_raw replacement (lives in the ledger). */
function ar_visit_work_history(PDO $pdo, int $pid, string $chart, string $visitId): array
{
    $q = $pdo->prepare("SELECT e.created_at, e.claim_status, e.action_taken, e.next_follow_up_date, e.notes, u.name worker_name
                        FROM ar_allocation_event e LEFT JOIN users u ON u.id=e.user_id
                        WHERE e.process_id=? AND e.chart_number=? AND e.visit_id=? AND e.event='work'
                        ORDER BY e.created_at DESC, e.id DESC");
    $q->execute([$pid, $chart, $visitId]);
    return $q->fetchAll();
}

/** Persist ONE visit's work (ownership re-checked). Returns true on success. Caller validates the dropdown
 *  values + follow-up cap; this sets worked state + the routing bucket and logs a visit-grain 'work' event. */
function ar_save_visit_work(PDO $pdo, int $visitWorkId, int $uid, string $claimStatus, string $actionTaken, ?string $followUp, string $notes): bool
{
    $row = $pdo->prepare("SELECT * FROM ar_visit_work WHERE id=? AND allocated_user=?");
    $row->execute([$visitWorkId, $uid]); $row = $row->fetch();
    if (!$row) return false;
    // a worked visit is READ-ONLY — it can only be reopened via supervisor rework re-allocation (which resets it
    // to 'allocated'); this guard backs the read-only UI so a forged/replayed POST can't overwrite a worked visit.
    if (($row['inventory_status'] ?? '') === 'worked') return false;
    // an audited visit is likewise locked (rework re-allocation drops the audit)
    $locked = $pdo->prepare("SELECT 1 FROM ar_visit_audit WHERE visit_work_id=?"); $locked->execute([$visitWorkId]);
    if ($locked->fetchColumn()) return false;
    $bucket   = ar_visit_bucket_for_action($actionTaken);
    $bucketAt = $bucket ? date('Y-m-d H:i:s') : null;
    $fu = ($followUp && ar_followup_within_cap($followUp)) ? $followUp : null;
    $inSample = ar_visit_in_sample_decision($pdo, $uid);   // audit sampling, sticky (once sampled, stays sampled)
    $pdo->prepare("UPDATE ar_visit_work
            SET claim_status=?, action_taken=?, notes=?, next_follow_up_date=?,
                last_worked_date=CURDATE(), worked_at=NOW(), worked_by=?, inventory_status='worked',
                bucket=?, bucket_at=?, sent_at=NULL, sent_by=NULL, in_sample=GREATEST(in_sample, ?), updated_at=NOW()
            WHERE id=? AND allocated_user=?")
        ->execute([$claimStatus, $actionTaken, $notes, $fu, $uid, $bucket, $bucketAt, $inSample, $visitWorkId, $uid]);
    $pdo->prepare("INSERT INTO ar_allocation_event (process_id,chart_number,visit_id,event,user_id,claim_status,action_taken,next_follow_up_date,notes)
                   VALUES (?,?,?,'work',?,?,?,?,?)")
        ->execute([(int) $row['process_id'], $row['chart_number'], $row['visit_id'], $uid, $claimStatus, $actionTaken, $fu, $notes]);
    return true;
}

/** Bulk-apply the SAME disposition (claim status · action taken · follow-up · notes) to many visits at once — the
 *  AR worker selects several visits and tags them in one go. Each visit is saved through ar_save_visit_work(), so it
 *  carries the same per-visit security (must be allocated to $uid, not already worked, not audited); visits that fail
 *  those checks are SKIPPED, not fatal. The whole batch runs in one transaction — a mid-batch error rolls it all
 *  back. Returns ['ok'=>int saved, 'skipped'=>int]. */
function ar_save_visits_bulk(PDO $pdo, array $visitWorkIds, int $uid, string $claimStatus, string $actionTaken, ?string $followUp, string $notes): array
{
    $ids = array_values(array_unique(array_filter(array_map('intval', $visitWorkIds), fn($i) => $i > 0)));
    if (!$ids) return ['ok' => 0, 'skipped' => 0];
    $ok = 0; $skipped = 0;
    $pdo->beginTransaction();
    try {
        foreach ($ids as $vid) {
            if (ar_save_visit_work($pdo, $vid, $uid, $claimStatus, $actionTaken, $followUp, $notes)) $ok++;
            else $skipped++;
        }
        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        throw $e;
    }
    return ['ok' => $ok, 'skipped' => $skipped];
}

/* ============================================================================ PHASE 4 — AUDIT / SAMPLING / QUALITY */

/** The per-AR-user audit sample % (supervisor-set; default AR_DEFAULT_SAMPLE_PCT until configured). */
function ar_user_sample_pct(PDO $pdo, int $uid): int
{
    try { $st = $pdo->prepare("SELECT sample_pct FROM ar_user_sample WHERE user_id=?"); $st->execute([$uid]);
        $p = $st->fetchColumn(); if ($p !== false) return max(0, min(100, (int) $p)); } catch (Throwable $e) {}
    return AR_DEFAULT_SAMPLE_PCT;
}

/** Supervisor/manager sets an AR user's audit sample %. */
function ar_set_user_sample(PDO $pdo, int $uid, int $pct, int $by): void
{
    $pct = max(0, min(100, $pct));
    $pdo->prepare("INSERT INTO ar_user_sample (user_id,sample_pct,updated_by) VALUES (?,?,?)
                   ON DUPLICATE KEY UPDATE sample_pct=VALUES(sample_pct), updated_by=VALUES(updated_by)")->execute([$uid, $pct, $by]);
}

/** Should a just-worked visit enter the audit sample for this user? (1/0) */
function ar_visit_in_sample_decision(PDO $pdo, int $uid): int
{
    $pct = ar_user_sample_pct($pdo, $uid);
    if ($pct >= 100) return 1;
    if ($pct <= 0)   return 0;
    return random_int(1, 100) <= $pct ? 1 : 0;
}

/** Save (or re-save) the 5-parameter audit of a worked visit. $params keyed by AR_AUDIT_PARAMS key →
 *  ['result'=>'pass'|'fail','note'=>...]. Computes opportunities/defects/result, (re)opens a 5-day rebuttal
 *  TAT, and resets any prior rebuttal state. Returns the audit id (0 on failure). */
function ar_save_audit(PDO $pdo, int $visitWorkId, int $auditorId, array $params, string $comment): int
{
    $vw = $pdo->prepare("SELECT * FROM ar_visit_work WHERE id=?"); $vw->execute([$visitWorkId]); $vw = $vw->fetch();
    if (!$vw) return 0;
    // server-side rebuttal lock: never re-finalize over a worker's OPEN dispute (would silently wipe it)
    $ex = $pdo->prepare("SELECT rebuttal_status FROM ar_visit_audit WHERE visit_work_id=?"); $ex->execute([$visitWorkId]);
    if (($ex->fetchColumn() ?: 'none') === 'raised') return 0;
    $workerId = (int) ($vw['worked_by'] ?: $vw['allocated_user']);   // attribute to who actually worked it, not the live owner
    $opp = count(AR_AUDIT_PARAMS); $def = 0;
    foreach (AR_AUDIT_PARAMS as $k => $_) if (($params[$k]['result'] ?? 'pass') === 'fail') $def++;
    $result = $def > 0 ? 'error' : 'pass';
    $due = date('Y-m-d', strtotime('+' . AR_REBUTTAL_TAT_DAYS . ' days'));
    $pdo->beginTransaction();
    $pdo->prepare("INSERT INTO ar_visit_audit (visit_work_id,process_id,chart_number,visit_id,worker_id,auditor_id,audited_at,opportunities,defects,result,comment,rebuttal_due)
            VALUES (?,?,?,?,?,?,NOW(),?,?,?,?,?)
            ON DUPLICATE KEY UPDATE auditor_id=VALUES(auditor_id), audited_at=NOW(), opportunities=VALUES(opportunities),
              defects=VALUES(defects), result=VALUES(result), comment=VALUES(comment), rebuttal_due=VALUES(rebuttal_due),
              rebuttal_status='none', rebuttal_comment=NULL, rebuttal_at=NULL, rebuttal_resolved_at=NULL")
        ->execute([$visitWorkId, (int) $vw['process_id'], $vw['chart_number'], $vw['visit_id'], $workerId, $auditorId, $opp, $def, $result, $comment ?: null, $due]);
    $aid = (int) (function () use ($pdo, $visitWorkId) { $q = $pdo->prepare("SELECT id FROM ar_visit_audit WHERE visit_work_id=?"); $q->execute([$visitWorkId]); return $q->fetchColumn(); })();
    $up = $pdo->prepare("INSERT INTO ar_audit_param (audit_id,param_key,result,note) VALUES (?,?,?,?)
                         ON DUPLICATE KEY UPDATE result=VALUES(result), note=VALUES(note), disputed=0, dispute_reason=NULL, rebuttal_result=NULL");
    foreach (AR_AUDIT_PARAMS as $k => $_) {
        $r = (($params[$k]['result'] ?? 'pass') === 'fail') ? 'fail' : 'pass';
        $up->execute([$aid, $k, $r, trim((string) ($params[$k]['note'] ?? '')) ?: null]);
    }
    $pdo->commit();
    return $aid;
}

/** Delete the audit chain (params + audit) for the given visit_work ids. Call before DELETING or RESETTING
 *  those visit rows so audits are never orphaned and a re-worked visit re-enters the audit cycle clean. */
function ar_drop_visit_audits(PDO $pdo, array $visitWorkIds): void
{
    $ids = array_values(array_filter(array_map('intval', $visitWorkIds)));
    if (!$ids) return;
    $in = implode(',', array_fill(0, count($ids), '?'));
    $pdo->prepare("DELETE ap FROM ar_audit_param ap JOIN ar_visit_audit a ON a.id=ap.audit_id WHERE a.visit_work_id IN ($in)")->execute($ids);
    $pdo->prepare("DELETE FROM ar_visit_audit WHERE visit_work_id IN ($in)")->execute($ids);
}

/** Drop the audit chain for every visit row of an account (used when those rows are deleted/reset wholesale). */
function ar_drop_account_audits(PDO $pdo, int $processId, string $chart): void
{
    $st = $pdo->prepare("SELECT id FROM ar_visit_work WHERE process_id=? AND chart_number=?");
    $st->execute([$processId, $chart]);
    ar_drop_visit_audits($pdo, array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN)));
}

/** Recompute an audit's opportunities/defects/result from its params (after a rebuttal flip). */
function ar_recompute_audit(PDO $pdo, int $auditId): void
{
    $st = $pdo->prepare("SELECT COUNT(*) opp, SUM(result<>'pass') def FROM ar_audit_param WHERE audit_id=?");
    $st->execute([$auditId]); $r = $st->fetch();
    $opp = (int) ($r['opp'] ?? 0); $def = (int) ($r['def'] ?? 0);
    $pdo->prepare("UPDATE ar_visit_audit SET opportunities=?, defects=?, result=? WHERE id=?")
        ->execute([$opp ?: null, $opp ? $def : null, $opp ? ($def ? 'error' : 'pass') : null, $auditId]);
}

/** Rolling AR quality % for a user (audited visits): 100*(opp-def)/opp over the last $days. Null if none. */
function ar_quality_pct(PDO $pdo, int $uid, int $days = 21): ?float
{
    $st = $pdo->prepare("SELECT SUM(opportunities) opp, SUM(defects) def FROM ar_visit_audit
        WHERE worker_id=? AND opportunities IS NOT NULL AND audited_at >= (NOW() - INTERVAL ? DAY)");
    $st->execute([$uid, $days]); $r = $st->fetch();
    $opp = (int) ($r['opp'] ?? 0); if ($opp === 0) return null;
    return round(100.0 * ($opp - (int) ($r['def'] ?? 0)) / $opp, 2);
}
