<?php
/**
 * ============================================================================
 *  MEDMECS — MEDICAL CODING MODULE  (engine)
 * ============================================================================
 *  Self-contained coding workflow built on the Medmecs core (auth, RBAC,
 *  audit, xlsx, header/footer).  Loaded by includes/config.php.
 *
 *  Hierarchy (per the user's decision):
 *    GLOBAL master  : verticals > clients > processes   (existing core tables)
 *    Coding-owned   : coding_facilities > coding_providers (m:n) > CPT validation
 *
 *  Roles            : coder, coding_auditor, coding_supervisor, coding_manager
 *  Permission group : 'coding'  (auto-appears in the central Access Control page)
 *  Access model     : feature perms via role + per-user override (user_permissions);
 *                     data scope (which client/process/facility/provider a coder may
 *                     touch) via coding_user_scope (Phase 3).
 *
 *  Migrations for this module are registered in coding_register_migrations() and
 *  merged into the core auto-provisioner (includes/auto-migrate.php) so the
 *  zero-touch "extract zip → first login" deploy keeps working.
 * ============================================================================
 */

/* ----------------------------------------------------------------------------
 * CONSTANTS
 * ------------------------------------------------------------------------- */

/** Internal role codes that belong to the coding vertical. */
const CODING_ROLES = ['coder', 'coding_auditor', 'coding_supervisor', 'coding_manager', 'coding_validator', 'bs_uploader'];

/** Every permission code in the 'coding' group (also the seed source). */
const CODING_PERMISSIONS = [
    'coding.upload'        => 'Upload Inventory',
    'coding.bs_upload'     => 'Upload Billing Sheets',
    'coding.work'          => 'Work Coding Queue',
    'coding.audit'         => 'Audit Charts',
    'coding.validate'      => 'Validate RFI / Disregard',
    'coding.supervise'     => 'Supervise Coding',
    'coding.access.manage' => 'Manage Coder Access',
    'coding.cpt.manage'    => 'Manage CPT Validation',
    'coding.reports'       => 'View Coding Reports',
    'coding.reference'     => 'Manage Coding Reference',
    'coding.release_billing' => 'Release Charts to Billing',
    // Deletion Request Workflow. These exist as SEPARATE permissions because coding.supervise cannot express
    // the rule "only the Manager may permanently delete" — coding_supervisor and coding_manager BOTH hold it
    // (see CODING_ROLE_PERMISSIONS below), so any gate built on it lets a Team Lead destroy PHI.
    'coding.deletion.request' => 'Request Chart Deletion',
    'coding.deletion.review'  => 'Review Deletion Requests',
    'coding.deletion.execute' => 'Permanently Delete Charts',
];

/** Role → default permission codes (deviations are then per-user in Access Control). */
const CODING_ROLE_PERMISSIONS = [
    'coder'             => ['coding.upload', 'coding.work', 'coding.reports', 'coding.deletion.request'],
    'coding_auditor'    => ['coding.audit', 'coding.reports', 'coding.release_billing'],
    'coding_validator'  => ['coding.validate', 'coding.reports'],
    'bs_uploader'       => ['coding.bs_upload'],
    'coding_supervisor' => ['coding.upload', 'coding.bs_upload', 'coding.work', 'coding.audit', 'coding.validate', 'coding.supervise',
                            'coding.access.manage', 'coding.cpt.manage', 'coding.reports', 'coding.release_billing',
                            'coding.deletion.request', 'coding.deletion.review'],
    // NOTE the asymmetry, it is the whole point: the manager gets coding.deletion.execute and the supervisor
    // does NOT. This is the ONLY thing standing between a Team Lead and permanently destroying PHI.
    'coding_manager'    => ['coding.upload', 'coding.bs_upload', 'coding.work', 'coding.audit', 'coding.validate', 'coding.supervise',
                            'coding.access.manage', 'coding.cpt.manage', 'coding.reports', 'coding.reference', 'coding.release_billing',
                            'coding.deletion.request', 'coding.deletion.review', 'coding.deletion.execute'],
];

/** Modifiers seeded for the coding view (user-supplied reference list). Kept as the seed + fallback;
 *  the live list is now maintained in coding_modifiers (admin/coding-modifiers.php) — see coding_modifiers(). */
const CODING_MODIFIERS = ['25', '24', '57', '51', '59', 'GO', 'GT', 'JZ', 'JW'];

/**
 * Active modifier CODES from coding_modifiers (maintained by a supervisor/manager on
 * admin/coding-modifiers.php), ordered by sort_order. Cached per request. Falls back to the
 * CODING_MODIFIERS seed constant if the table isn't provisioned yet or is empty, so every
 * consumer (chart validation gate + the coder dropdown JSON) keeps working. Returns ['25','24',…].
 */
function coding_modifiers(PDO $pdo): array
{
    static $cache = null;
    if ($cache === null) {
        $cache = [];
        $provisioned = false;
        try {
            foreach ($pdo->query("SELECT code FROM coding_modifiers WHERE status='active' ORDER BY sort_order, code")->fetchAll() as $r)
                $cache[] = $r['code'];
            $provisioned = true;   // the table read succeeded (even if it returned zero active rows)
        } catch (Throwable $e) { /* table not provisioned yet */ }
        // Only seed-fallback when the table isn't there — an INTENTIONALLY empty active set (a supervisor
        // hid/deleted every modifier) must stay empty, not resurrect the 9 seed codes.
        if (!$provisioned) $cache = CODING_MODIFIERS;
    }
    return $cache;
}

/** Mandatory secondary reasons when a coder marks a chart RFI or Disregard. */
const CODING_RFI_REASONS = [
    'Missing documentation', 'Illegible documentation', 'Provider clarification needed',
    'Incomplete encounter', 'Signature missing', 'Query pending', 'Other',
];
const CODING_DISREGARD_REASONS = [
    'Duplicate chart', 'Not codable', 'Wrong patient', 'Cancelled / no encounter',
    'Already coded', 'Out of scope', 'Other',
];

/**
 * Active secondary reasons for RFI / Disregard — managed in coding_secondary_reasons by a
 * supervisor/manager (admin/coding-reasons.php). Falls back to the seed constants above if the
 * table isn't migrated yet or is empty. Returns ['rfi'=>[labels...], 'disregard'=>[labels...]],
 * or a flat label list when $kind ('rfi'|'disregard') is given.
 */
function coding_secondary_reasons(PDO $pdo, ?string $kind = null)
{
    static $cache = null;
    if ($cache === null) {
        $cache = ['rfi' => [], 'disregard' => []];
        try {
            foreach ($pdo->query("SELECT kind,label FROM coding_secondary_reasons WHERE status='active' ORDER BY sort_order, label")->fetchAll() as $r)
                if (isset($cache[$r['kind']])) $cache[$r['kind']][] = $r['label'];
        } catch (Throwable $e) { /* table not provisioned yet */ }
        if (!$cache['rfi'])       $cache['rfi']       = CODING_RFI_REASONS;
        if (!$cache['disregard']) $cache['disregard'] = CODING_DISREGARD_REASONS;
    }
    return $kind === null ? $cache : ($cache[$kind] ?? []);
}

/** CPT→ALF billing map (active rows only): ['99202'=>'99341', ...]. Empty if the table isn't provisioned. */
function coding_alf_map(PDO $pdo): array
{
    static $m = null;
    if ($m === null) {
        $m = [];
        try {
            foreach ($pdo->query("SELECT cpt_code, alf_code FROM coding_alf_map WHERE status='active'")->fetchAll() as $r)
                $m[(string) $r['cpt_code']] = (string) $r['alf_code'];
        } catch (Throwable $e) { /* table not provisioned yet */ }
    }
    return $m;
}

/** Billing label for a CPT: "99202/99341" when an ALF code is mapped, else the bare CPT. */
function coding_alf_label(PDO $pdo, ?string $cpt): string
{
    $cpt = trim((string) $cpt);
    if ($cpt === '') return '';
    $m = coding_alf_map($pdo);
    return isset($m[$cpt]) ? $cpt . '/' . $m[$cpt] : $cpt;
}

/**
 * Bulk effective (audit-corrected) CPT codes for many charts → [invId => ['99202', ...]].
 * Same correction logic as the deliverables export, batched for a list view. CPT order preserved
 * (DOS, sort_order); duplicates kept (a CPT can recur across DOS).
 */
function coding_effective_lines_bulk(PDO $pdo, array $invIds): array
{
    $invIds = array_values(array_unique(array_filter(array_map('intval', $invIds))));
    $out = [];
    if (!$invIds) return $out;
    $in = implode(',', $invIds);
    $cptRows = $pdo->query("SELECT id, inventory_id, dos, cpt_code, units FROM coding_chart_cpts WHERE inventory_id IN ($in) ORDER BY dos, sort_order, id")->fetchAll();
    $cptIds  = array_map('intval', array_column($cptRows, 'id'));
    $icdBy = []; $modBy = []; $icdBySo = []; $modBySo = [];
    if ($cptIds) {
        $cin = implode(',', $cptIds);
        foreach ($pdo->query("SELECT chart_cpt_id, icd_code, sort_order FROM coding_chart_cpt_icds WHERE chart_cpt_id IN ($cin) ORDER BY sort_order, id")->fetchAll() as $r) { $icdBy[$r['chart_cpt_id']][] = $r['icd_code']; $icdBySo[$r['chart_cpt_id']][] = ['c'=>$r['icd_code'],'so'=>(int)$r['sort_order']]; }
        foreach ($pdo->query("SELECT chart_cpt_id, modifier_code, sort_order FROM coding_chart_cpt_modifiers WHERE chart_cpt_id IN ($cin) ORDER BY sort_order, id")->fetchAll() as $r) { $modBy[$r['chart_cpt_id']][] = $r['modifier_code']; $modBySo[$r['chart_cpt_id']][] = ['c'=>$r['modifier_code'],'so'=>(int)$r['sort_order']]; }
    }
    $coderLines = [];
    foreach ($cptRows as $c) {
        $coderLines[(int) $c['inventory_id']][] = ['cptId'=>(int) $c['id'], 'dos'=>$c['dos'], 'cpt'=>$c['cpt_code'], 'units'=>(int) ($c['units'] ?? 1), 'icds'=>$icdBy[$c['id']] ?? [], 'mods'=>$modBy[$c['id']] ?? [], 'icds_so'=>$icdBySo[$c['id']] ?? [], 'mods_so'=>$modBySo[$c['id']] ?? []];
    }
    $findBy = [];
    // Skip findings the auditor SUPERSEDED via a direct QA correction (they corrected the chart by hand, so applying
    // the old findings again would double-correct). The rows stay for reporting/audit-trail; billing just ignores them.
    $supSql = app_col_exists($pdo, 'coding_audit_codes', 'superseded_at') ? ' AND superseded_at IS NULL' : '';
    foreach ($pdo->query("SELECT inventory_id, chart_cpt_id, code_type, code_value, result, correct_value, sort_order FROM coding_audit_codes WHERE inventory_id IN ($in)$supSql ORDER BY inventory_id, sort_order, id")->fetchAll() as $f) $findBy[(int) $f['inventory_id']][] = $f;
    foreach ($invIds as $iid) $out[$iid] = coding_apply_audit_corrections($coderLines[$iid] ?? [], $findBy[$iid] ?? []);
    return $out;
}

/** Bulk effective (audit-corrected) CPT codes per chart → [invId => ['99202', ...]] (per-line order preserved). */
function coding_effective_cpts_bulk(PDO $pdo, array $invIds): array
{
    $out = [];
    foreach (coding_effective_lines_bulk($pdo, $invIds) as $iid => $lines) {
        $cpts = [];
        foreach ($lines as $ln) { $c = trim((string) ($ln['cpt'] ?? '')); if ($c !== '') $cpts[] = $c; }
        $out[$iid] = $cpts;
    }
    return $out;
}

/**
 * Render a chart's coded lines for ONE flat-table cell — the values for $field
 * ('dos'|'cpt'|'units'|'mods'|'icds') stacked one per coded line so the DOS/CPT/Units/Mod/ICD
 * columns align row-wise. Bare CPT (no ALF pairing — that is billing-only, on Deliverables).
 */
function coding_lines_cell(array $lines, string $field): string
{
    if (!$lines) return '<span class="text-secondary">—</span>';
    $sep = '<div class="border-top my-1" style="opacity:.4"></div>';
    $out = [];
    foreach ($lines as $ln) {
        switch ($field) {
            case 'dos':   $out[] = e(fmt_date($ln['dos'] ?? null)) ?: '—'; break;
            case 'cpt':   $out[] = '<strong>' . e((string) ($ln['cpt'] ?? '')) . '</strong>'; break;
            case 'units': $out[] = (int) ($ln['units'] ?? 1); break;
            case 'mods':  $out[] = e(implode(', ', $ln['mods'] ?? [])) ?: '<span class="text-secondary">—</span>'; break;
            case 'icds':  $out[] = e(implode(', ', $ln['icds'] ?? [])) ?: '<span class="text-secondary">—</span>'; break;
        }
    }
    return implode($sep, $out);
}

/** Error categories for code-level audit findings (DPO). */
const CODING_AUDIT_ERROR_CATEGORIES = [
    'Incorrect code', 'Invalid / deleted code', 'Lacks specificity', 'Laterality',
    'Medical necessity', 'Unbundling / NCCI', 'Missing modifier', 'Incorrect modifier',
    'Diagnosis sequencing', 'Units', 'Wrong DOS', 'Wrong provider', 'Other',
];

/** Coder-submitted chart statuses. 'in_progress' = actively open (transient, auto-set);
 *  'hold' = parked by the coder to resume later (explicit "save & resume"). */
const CODING_CHART_STATUSES = ['in_progress', 'hold', 'completed', 'rfi', 'disregard'];

/** Auditor outcome statuses. */
const CODING_AUDIT_STATUSES = ['ready_to_deliver', 'in_progress', 'rfi', 'disregard'];

/** The name/code of the global vertical that roots the coding hierarchy. */
const CODING_VERTICAL_CODE = 'CODING';
const CODING_VERTICAL_NAME = 'Coding';

/** Coders within this many days of joining are exempt from the %-tier sampling
 *  rule (no 3-week history yet) — their completed charts are audited 100%. */
const CODING_NEW_CODER_DAYS = 30;

/**
 * Bucket-sampling tiers: ordered, evaluated top-to-bottom on the coder's
 * rolling last-3-week quality %.  Each entry: [min_inclusive, max_exclusive, percent].
 * A quality of exactly 95 falls in the >95 bucket per the spec's intent (>95 → 5%).
 */
const CODING_SAMPLING_TIERS = [
    [95.0, 100.01, 5],
    [94.0, 95.0,   6],
    [93.0, 94.0,   7],
    [92.0, 93.0,   8],
    [91.0, 92.0,   9],
    [90.0, 91.0,   10],
    [85.0, 90.0,   20],
    [80.0, 85.0,   30],
    [0.0,  80.0,   100],
];

/* ----------------------------------------------------------------------------
 * ROLE / PERMISSION HELPERS
 * ------------------------------------------------------------------------- */

/** True if the current user can see ANY part of the coding module. */
function is_coding_user(): bool
{
    return can(
        'coding.upload', 'coding.bs_upload', 'coding.work', 'coding.audit', 'coding.validate', 'coding.supervise',
        'coding.access.manage', 'coding.cpt.manage', 'coding.reports', 'coding.reference'
    );
}

/** True if the current user has supervisor-or-above powers in coding. */
function is_coding_supervisor(): bool
{
    return can('coding.supervise');
}

/** Landing page for a coding user — MUST return a page the user can actually open, or dashboard.php
 *  (which bounces non-report.view coding users back here) creates an infinite redirect loop. Every
 *  is_coding_user() permission is covered below by a page whose route gate allows it. */
function coding_home_url(): string
{
    if (can('coding.work') || can('coding.upload')) return '/admin/coding-home.php';
    if (can('coding.audit'))                         return '/admin/coding-audit.php';
    if (can('coding.validate'))                      return '/admin/coding-validate.php';
    // A pure billing-sheet uploader can ONLY open coding-bs-upload.php (coding-home/reports would deny → loop).
    if (can('coding.bs_upload') && !can('coding.reports')) return '/admin/coding-bs-upload.php';
    if (can('coding.reports'))                       return '/admin/coding-reports.php';
    // supervise / reference / cpt.manage / access.manage all satisfy coding-home.php's gate.
    return '/admin/coding-home.php';
}

/**
 * Sidebar nav items for the CODING section. Each item carries a 'p' (ANY-of)
 * permission gate; header.php filters by can() and hides empty section headers,
 * so coders see only operational items and supervisors/managers see the admin set.
 */
function coding_nav_items(): array
{
    // Lean sidebar: daily-driver items only. Supervisor/manager admin tools
    // (Reassign, RTO, Hierarchy, CPT validation, Coder access, Reference) live as
    // cards on the Coding home hub — keeps the sidebar short.
    return [
        ['hdr' => 'CODING'],
        ['h'=>'coding-home.php',     'u'=>'/admin/coding-home.php',     'i'=>'stethoscope',    't'=>'Coding home',  'p'=>['coding.work','coding.upload','coding.audit','coding.supervise','coding.reports','coding.reference','coding.cpt.manage','coding.access.manage']],
        ['h'=>'coding-upload.php',   'u'=>'/admin/coding-upload.php',   'i'=>'upload',         't'=>'Upload',       'p'=>['coding.upload']],
        ['h'=>'coding-bs-upload.php','u'=>'/admin/coding-bs-upload.php','i'=>'file-invoice',   't'=>'Billing sheets','p'=>['coding.bs_upload']],
        ['h'=>'coding-inventory.php','u'=>'/admin/coding-inventory.php','i'=>'list-check',     't'=>'My inventory', 'p'=>['coding.work']],
        ['h'=>'coding-rebuttals.php','u'=>'/admin/coding-rebuttals.php','i'=>'message-report', 't'=>'My rebuttals', 'p'=>['coding.work']],
        ['h'=>'coding-audit.php',    'u'=>'/admin/coding-audit.php',    'i'=>'checklist',      't'=>'Audit',        'p'=>['coding.audit']],
        ['h'=>'coding-review-editable.php','u'=>'/admin/coding-review-editable.php','i'=>'edit-circle','t'=>'Pre-audit review','p'=>['coding.supervise']],
        ['h'=>'coding-validate.php', 'u'=>'/admin/coding-validate.php', 'i'=>'shield-check',   't'=>'Validate',     'p'=>['coding.validate']],
        ['h'=>'coding-deliverables.php','u'=>'/admin/coding-deliverables.php','i'=>'package-export','t'=>'Deliverables','p'=>['coding.supervise','coding.audit']],
        // Coders need this too (it is where their own requests live), so coding.deletion.request is listed.
        ['h'=>'coding-deletions.php','u'=>'/admin/coding-deletions.php','i'=>'trash-x','t'=>'Deletion requests','p'=>['coding.deletion.request','coding.deletion.review','coding.deletion.execute']],
        ['h'=>'coding-history.php',  'u'=>'/admin/coding-history.php',  'i'=>'history',        't'=>'Validation history','p'=>['coding.validate','coding.supervise','coding.reports']],
        ['h'=>'coding-reports.php',  'u'=>'/admin/coding-reports.php',  'i'=>'chart-histogram','t'=>'Reports',      'p'=>['coding.reports']],
        ['h'=>'coding-inventory-report.php','u'=>'/admin/coding-inventory-report.php','i'=>'report-analytics','t'=>'Reconciliation report','p'=>['coding.reports','coding.supervise','coding.work','coding.validate','coding.audit']],
    ];
}

/** Master-data nav items (Hierarchy / Facilities / Providers) — visible to anyone who can
 *  manage coding reference data: sysadmin, supervisor, manager (coding.supervise OR coding.reference). */
function coding_master_nav_items(): array
{
    // 'p'=null: the block is gated by can_manage_master_data() at render time, and each page's route gate is
    // '__masterdata__' — so any vertical's supervisor/reference-admin sees + reaches the shared master data.
    $p = null;
    return [
        ['h'=>'coding-facilities.php', 'u'=>'/admin/coding-facilities.php', 'i'=>'building-hospital', 't'=>'Facilities', 'p'=>$p],
        ['h'=>'coding-providers.php',  'u'=>'/admin/coding-providers.php',  'i'=>'user-plus',          't'=>'Providers',  'p'=>$p],
        ['h'=>'coding-hierarchy.php',  'u'=>'/admin/coding-hierarchy.php',  'i'=>'sitemap',            't'=>'Hierarchy',  'p'=>$p],
    ];
}

/** True if the current user can see ANY part of the billing module. */
function is_billing_user(): bool
{
    return can('billing.work', 'billing.allocate', 'billing.audit', 'billing.supervise', 'billing.reports');
}

/** Sidebar nav items for the BILLING section (the team that bills released charts to insurance). */
function billing_nav_items(): array
{
    return [
        ['hdr' => 'BILLING'],
        // Biller: open allocated charts one-by-one and tag carrier + billing status + reason/comment.
        ['h'=>'my-billing.php',     'u'=>'/admin/my-billing.php',     'i'=>'inbox',        't'=>'My billing queue', 'p'=>['billing.work']],
        // Biller: charts of theirs QA flagged as an error — review the category/comment and dispute or rework.
        ['h'=>'billing-rebuttals.php','u'=>'/admin/billing-rebuttals.php','i'=>'message-report','t'=>'My billing errors','p'=>['billing.work']],
        // Supervisor/manager: read-only view of the WHOLE team's billing inventory (allocated / worked / billed …), filterable.
        ['h'=>'my-billing.php',     'u'=>'/admin/my-billing.php?mode=team','i'=>'users-group','t'=>'Team inventory', 'p'=>['billing.supervise','billing.allocate']],
        // Supervisor/manager: allocate released charts to billers, reallocate Hold/Unbilled, raise RFI to coding.
        ['h'=>'billing-allocation.php','u'=>'/admin/billing-allocation.php','i'=>'arrows-split','t'=>'Allocate billing', 'p'=>['billing.allocate']],
        // Billing Auditor / supervisor: QA the sampled worked charts (DPO scoring + rebuttals).
        ['h'=>'billing-audit.php',  'u'=>'/admin/billing-audit.php',  'i'=>'shield-check', 't'=>'Billing audit',    'p'=>['billing.audit']],
        // Supervisor/manager: set the per-biller audit sample % (overrides the global rate).
        ['h'=>'billing-sampling.php','u'=>'/admin/billing-sampling.php','i'=>'percentage',  't'=>'Billing sampling', 'p'=>['billing.supervise']],
        // Supervisor/manager: maintain the searchable carrier list + Unbilled reasons + comments.
        ['h'=>'billing-codes.php',  'u'=>'/admin/billing-codes.php',  'i'=>'list-details', 't'=>'Carriers & codes', 'p'=>['billing.allocate']],
        // Daily / weekly / monthly billing reports.
        ['h'=>'billing-reports.php','u'=>'/admin/billing-reports.php','i'=>'chart-bar',     't'=>'Billing reports',  'p'=>['billing.reports','billing.supervise','billing.allocate']],
    ];
}

/* ----------------------------------------------------------------------------
 * MIGRATIONS  (merged into core app_auto_provision; each idempotent, run once)
 * ------------------------------------------------------------------------- */

/**
 * One-time auto-cleanup of the DISCARDED earlier coding build.
 *
 * Detection is by table shape, not migration name: if `coding_inventory` exists but
 * lacks the new `in_sample` column, it is the old build → drop the coding domain
 * tables and forget their migration markers so the fresh schema/seed migrations
 * rebuild everything cleanly. No-op on a fresh server (no coding_inventory) and on
 * the new build (in_sample present). Keeps the zip-extract→login deploy zero-touch.
 *
 * Called from app_auto_provision() with the applied-migrations list; returns it with
 * any 'coding_*' entries stripped when a reset happened (so they re-run this pass).
 */
function coding_legacy_reset_if_needed(PDO $pdo, array $applied): array
{
    try {
        $has = $pdo->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='coding_inventory' LIMIT 1");
        $has->execute();
        if (!$has->fetchColumn()) return $applied;                       // fresh server
        if (app_col_exists($pdo, 'coding_inventory', 'in_sample')) return $applied; // already new build

        $pdo->exec("SET FOREIGN_KEY_CHECKS=0");
        foreach ([
            'coding_chart_cpt_modifiers','coding_chart_cpt_icds','coding_chart_cpts','coding_chart_events',
            'coding_billing_sheets','coding_inventory','coding_upload_batches','coding_user_scope','coding_coder_access','coding_cpt_validation',
            'coding_facility_providers','coding_facilities','coding_providers','coding_cpt_codes',
            'coding_icd_codes','coding_modifiers','coding_clients','coding_processes',
        ] as $t) { try { $pdo->exec("DROP TABLE IF EXISTS `$t`"); } catch (Throwable $e) {} }
        $pdo->exec("SET FOREIGN_KEY_CHECKS=1");
        $pdo->exec("DELETE FROM schema_migrations WHERE name LIKE 'coding\\_%'");
        error_log('coding: legacy build detected — coding tables reset for clean rebuild');
        return array_values(array_filter($applied, fn($n) => strpos($n, 'coding_') !== 0));
    } catch (Throwable $e) {
        return $applied;   // never block the deploy
    }
}

/**
 * @return array<string, callable(PDO):void>  ordered name => migration
 */
function coding_register_migrations(): array
{
    return [

        /* ---- Phase 1: per-user permission OVERRIDE layer --------------------
         * A row OVERRIDES the user's role default for that one permission.
         * effect='grant' forces allow, effect='deny' forces block; no row = inherit
         * from roles.  SysAdmin ('*') always bypasses (never stored here). */
        'coding_user_permissions_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `user_permissions` (
                `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `user_id`         BIGINT UNSIGNED NOT NULL,
                `permission_code` VARCHAR(120) NOT NULL,
                `effect`          ENUM('grant','deny') NOT NULL DEFAULT 'grant',
                `granted_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_user_perm` (`user_id`,`permission_code`),
                KEY `idx_up_user` (`user_id`),
                CONSTRAINT `fk_up_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
                  ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        },

        /* ---- Phase 1: coding permissions, roles, role→permission map ------- */
        'coding_perms_roles_v1' => function (PDO $pdo) {
            // permissions (group 'coding' → auto-listed in Access Control)
            $ins = $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),updated_at=NOW()");
            foreach (CODING_PERMISSIONS as $code => $label) {
                $ins->execute([$code, $label, 'coding', $label . ' (coding module)']);
            }
            // roles
            $roleNames = [
                'coder'             => 'Coder',
                'coding_auditor'    => 'Coding Auditor',
                'coding_supervisor' => 'Coding Supervisor',
                'coding_manager'    => 'Coding Manager',
            ];
            $insRole = $pdo->prepare("INSERT INTO roles (code,name,description,is_system,scope,created_at,updated_at)
                                      VALUES (?,?,?,0,'internal',NOW(),NOW())
                                      ON DUPLICATE KEY UPDATE name=VALUES(name),updated_at=NOW()");
            foreach ($roleNames as $code => $name) {
                $insRole->execute([$code, $name, $name . ' — coding vertical']);
            }
            // role → permissions
            $map = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id)
                                  SELECT r.id, p.id FROM roles r JOIN permissions p
                                   ON p.code = ? WHERE r.code = ?");
            foreach (CODING_ROLE_PERMISSIONS as $roleCode => $perms) {
                foreach ($perms as $pc) $map->execute([$pc, $roleCode]);
            }
        },

        /* ==== Phase 2: reference data + hierarchy ========================= */

        // Coding-owned tables (facilities/providers + CPT validation + ICD/modifier reference).
        'coding_schema_v1' => function (PDO $pdo) { coding_create_schema($pdo); },

        // Distinguish supervisor/manager-added CPTs ('manual') from coder in-chart add-ons ('coder').
        'coding_cpt_source_v2' => function (PDO $pdo) {
            $pdo->exec("ALTER TABLE coding_cpt_validation
                        MODIFY `source` ENUM('seed','coder','manual') NOT NULL DEFAULT 'seed'");
        },

        // 9 modifiers from the user's reference list.
        'coding_modifiers_v1' => function (PDO $pdo) { coding_seed_modifiers($pdo); },

        // ~74.7k ICD-10-CM codes from the bundled text file (streamed, batched, idempotent).
        'coding_icd_import_v1' => function (PDO $pdo) {
            coding_import_icd($pdo, __DIR__ . '/../data/coding/icd10cm_codes_2026.txt');
        },

        // Global Coding vertical/clients/processes (find-or-create) + facilities, providers,
        // facility↔provider links, CPT master and per-(facility,provider) CPT validation set,
        // all seeded from the bundled mapping workbook.
        'coding_hierarchy_seed_v1' => function (PDO $pdo) {
            coding_seed_hierarchy_and_cpt($pdo, __DIR__ . '/../data/coding/cpt-mapping.xlsx');
        },

        /* ==== Phase 3: inventory, allocation, data scope ================== */
        'coding_inventory_v1' => function (PDO $pdo) { coding_create_inventory_schema($pdo); },

        /* ==== Phase 4: chart lines (CPT ▸ ICDs + modifiers) =============== */
        'coding_chart_lines_v1' => function (PDO $pdo) { coding_create_chart_line_schema($pdo); },

        /* ==== Phase 5: audit, sampling, rebuttal, RTO ===================== */
        'coding_audit_v1' => function (PDO $pdo) {
            coding_add_audit_columns($pdo);
            coding_create_events_schema($pdo);
        },

        /* Per-code audit findings → DPO (defects per opportunity) quality. */
        'coding_audit_codes_v1' => function (PDO $pdo) {
            coding_create_audit_codes_schema($pdo);
            if (!app_col_exists($pdo, 'coding_inventory', 'audit_opportunities')) $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `audit_opportunities` INT NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'audit_defects'))       $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `audit_defects` INT NULL");
        },

        /* 'hold' status — coder parks a chart to resume later (GetNext resumes in_progress first). */
        'coding_hold_status_v1' => function (PDO $pdo) {
            $pdo->exec("ALTER TABLE `coding_inventory`
                        MODIFY `status` ENUM('pending','in_progress','hold','completed','rfi','disregard') NOT NULL DEFAULT 'pending'");
        },

        /* Per-finding rebuttal: the coder can dispute individual flagged codes. */
        'coding_audit_dispute_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_audit_codes', 'disputed'))        $pdo->exec("ALTER TABLE `coding_audit_codes` ADD COLUMN `disputed` TINYINT(1) NOT NULL DEFAULT 0");
            if (!app_col_exists($pdo, 'coding_audit_codes', 'dispute_reason'))  $pdo->exec("ALTER TABLE `coding_audit_codes` ADD COLUMN `dispute_reason` VARCHAR(255) NULL");
            if (!app_col_exists($pdo, 'coding_audit_codes', 'rebuttal_result')) $pdo->exec("ALTER TABLE `coding_audit_codes` ADD COLUMN `rebuttal_result` ENUM('pending','accepted','rejected') NULL");
        },

        /* B: re-sequencing of auditor-ADDED codes — sort_order preserves the order the auditor chose, so added
         * missing codes flow to billing in that order (lower sort_order first) instead of insertion (id) order. */
        'coding_audit_sort_order_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_audit_codes', 'sort_order')) $pdo->exec("ALTER TABLE `coding_audit_codes` ADD COLUMN `sort_order` INT NOT NULL DEFAULT 0");
        },

        /* When the Quality Auditor directly corrects an agreed chart (coding-chart.php QA-edit), its per-code
         * findings are marked SUPERSEDED rather than deleted: coding_effective_lines_bulk skips superseded rows
         * (so billing gets the edited lines verbatim, no double-correction) while quality reports + the coder's
         * agreed rebuttal record still see the original flagged codes (audit trail preserved). */
        'coding_audit_superseded_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_audit_codes', 'superseded_at')) $pdo->exec("ALTER TABLE `coding_audit_codes` ADD COLUMN `superseded_at` TIMESTAMP NULL");
        },

        /* Bump EXISTING audit codes into a high range so the billing merge keeps "missing appended AFTER the coder's
         * codes" for charts audited before interleaving existed. Re-auditing a chart re-sequences both the coder and
         * the added codes into a unified low range (interleaving as the auditor chose). Idempotent via the WHERE. */
        'coding_audit_sort_order_legacy_v1' => function (PDO $pdo) {
            $pdo->exec("UPDATE coding_audit_codes SET sort_order = sort_order + 10000 WHERE sort_order < 10000");
        },

        /* One-time cleanup: a chart that isn't completed/rfi/disregard (i.e. still pending,
         * in-progress or on hold with the coder) must NOT carry any audit verdict. Clears any
         * such contradictory rows left by earlier builds (the audit view is now guarded). */
        'coding_audit_consistency_v1' => function (PDO $pdo) {
            $pdo->exec("UPDATE coding_inventory
                        SET audit_status=NULL, audit_result=NULL, audit_opportunities=NULL, audit_defects=NULL, auditor_id=NULL, audited_at=NULL
                        WHERE status NOT IN ('completed','rfi','disregard') AND audit_status IS NOT NULL");
            $pdo->exec("DELETE cac FROM coding_audit_codes cac JOIN coding_inventory ci ON ci.id=cac.inventory_id
                        WHERE ci.status NOT IN ('completed','rfi','disregard')");
        },

        /* Multiple dates of service per chart: each CPT line now carries its own DOS,
         * so one chart can hold DOS1{cpt…}, DOS2{cpt…}, etc. Backfill existing single-DOS
         * charts so their lines inherit the chart-level DOS. */
        'coding_chart_cpt_dos_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_chart_cpts', 'dos'))
                $pdo->exec("ALTER TABLE `coding_chart_cpts` ADD COLUMN `dos` DATE NULL AFTER `cpt_code`");
            $pdo->exec("UPDATE coding_chart_cpts cc JOIN coding_inventory ci ON ci.id=cc.inventory_id
                        SET cc.dos = ci.dos WHERE cc.dos IS NULL AND ci.dos IS NOT NULL");
        },

        /* PCP provider may be someone outside the facility's provider list, so store a free-text name
         * alongside the (optional) resolved provider id. */
        'coding_pcp_name_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_inventory', 'pcp_provider_name'))
                $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `pcp_provider_name` VARCHAR(150) NULL AFTER `pcp_provider_id`");
        },

        /* People-hierarchy + employee identity columns on users (for the reporting org chart:
         * supervisor sees their team, manager sees their supervisors + those teams). */
        'org_hierarchy_cols_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'users', 'employee_code')) $pdo->exec("ALTER TABLE `users` ADD COLUMN `employee_code` VARCHAR(20) NULL AFTER `email`");
            if (!app_col_exists($pdo, 'users', 'vertical'))      $pdo->exec("ALTER TABLE `users` ADD COLUMN `vertical` VARCHAR(20) NULL AFTER `employee_code`");
            if (!app_col_exists($pdo, 'users', 'reports_to'))    $pdo->exec("ALTER TABLE `users` ADD COLUMN `reports_to` BIGINT UNSIGNED NULL AFTER `vertical`");
            if (!app_index_exists($pdo, 'users', 'idx_users_reports_to')) $pdo->exec("ALTER TABLE `users` ADD INDEX `idx_users_reports_to` (`reports_to`)");
        },

        /* AR + Billing role/permission framework so those employees can be imported with proper roles
         * and granted access. (Their functional work modules are a separate build.) */
        'ar_billing_roles_v1' => function (PDO $pdo) {
            $perms = [
                'ar.work'=>'Work AR Queue', 'ar.audit'=>'Audit AR', 'ar.supervise'=>'Supervise AR', 'ar.reports'=>'View AR Reports',
                'billing.work'=>'Work Billing Queue', 'billing.audit'=>'Audit Billing', 'billing.supervise'=>'Supervise Billing', 'billing.reports'=>'View Billing Reports',
            ];
            $ins = $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),updated_at=NOW()");
            foreach ($perms as $c=>$l) { $ins->execute([$c, $l, (strpos($c,'ar.')===0?'ar':'billing'), $l]); }
            $roles = [
                'ar_manager'=>'AR Manager', 'ar_supervisor'=>'AR Supervisor', 'account_receivable'=>'Account Receivable', 'ar_auditor'=>'AR Auditor',
                'billing_manager'=>'Billing Manager', 'billing_supervisor'=>'Billing Supervisor', 'biller'=>'Biller', 'billing_auditor'=>'Billing Auditor',
            ];
            $insR = $pdo->prepare("INSERT INTO roles (code,name,description,is_system,scope,created_at,updated_at) VALUES (?,?,?,0,'internal',NOW(),NOW())
                                   ON DUPLICATE KEY UPDATE name=VALUES(name),updated_at=NOW()");
            foreach ($roles as $c=>$n) { $insR->execute([$c, $n, $n]); }
            $rp = [
                'ar_manager'=>['ar.work','ar.audit','ar.supervise','ar.reports'],
                'ar_supervisor'=>['ar.work','ar.audit','ar.supervise','ar.reports'],
                'account_receivable'=>['ar.work','ar.reports'],
                'ar_auditor'=>['ar.audit','ar.reports'],
                'billing_manager'=>['billing.work','billing.audit','billing.supervise','billing.reports'],
                'billing_supervisor'=>['billing.work','billing.audit','billing.supervise','billing.reports'],
                'biller'=>['billing.work','billing.reports'],
                'billing_auditor'=>['billing.audit','billing.reports'],
            ];
            $map = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code=? WHERE r.code=?");
            foreach ($rp as $rc=>$ps) { foreach ($ps as $pc) $map->execute([$pc, $rc]); }
        },

        /* Release-to-billing handoff: audited "ready_to_deliver" charts are released by the
         * auditor/supervisor/manager to the billing team (who then bill insurance). RFI/Disregard
         * charts never reach billing — they go back to the client via RTO. */
        'coding_billing_release_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_inventory', 'released_to_billing_at')) $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `released_to_billing_at` TIMESTAMP NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'released_by'))            $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `released_by` BIGINT UNSIGNED NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'billing_status'))         $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `billing_status` ENUM('pending','sent','void') NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'claim_sent_at'))          $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `claim_sent_at` TIMESTAMP NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'claim_ref'))              $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `claim_ref` VARCHAR(60) NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'billed_by'))              $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `billed_by` BIGINT UNSIGNED NULL");
            if (!app_index_exists($pdo, 'coding_inventory', 'idx_ci_billing'))       $pdo->exec("ALTER TABLE `coding_inventory` ADD INDEX `idx_ci_billing` (`billing_status`,`released_to_billing_at`)");
            // new permission + grant to the three roles that may release (idempotent for already-seeded DBs)
            $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),updated_at=NOW()")
                ->execute(['coding.release_billing', 'Release Charts to Billing', 'coding', 'Release audited charts to the billing team']);
            $map = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code=? WHERE r.code=?");
            foreach (['coding_auditor','coding_supervisor','coding_manager'] as $rc) $map->execute(['coding.release_billing', $rc]);
        },

        /* DEPRECATED (2026-06-18 access-control rebuild) — now a NO-OP. Formerly granted every active coding
         * worker whole-client scope to all Coding clients ("grant by vertical"). That blanket default is gone:
         * coders are aligned to a specific client » process » facility by a manager on admin/coding-access.php.
         * Kept (not deleted) so the marker stays recorded; emptied so a fresh rebuild / legacy-reset
         * (which strips coding_* markers) can never re-seed blanket access. */
        'coding_coder_scope_seed_v1' => function (PDO $pdo) { /* no-op — see admin/coding-access.php */ },

        /* Client-delivery tracking for RFI / Disregard (mirror of billing release): mark which RFI /
         * Disregard charts have been SENT TO THE CLIENT vs are still pending delivery. */
        'coding_client_delivery_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_inventory', 'client_sent_at')) $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `client_sent_at` TIMESTAMP NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'client_sent_by')) $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `client_sent_by` BIGINT UNSIGNED NULL");
            if (!app_index_exists($pdo, 'coding_inventory', 'idx_ci_client_sent')) $pdo->exec("ALTER TABLE `coding_inventory` ADD INDEX `idx_ci_client_sent` (`client_sent_at`)");
        },

        /* Client RESPONSE on an RFI/Disregard chart: the validator records what the client replied + the
         * date it was received, then sends the chart back to a coder. Feeds the history report. */
        'coding_client_response_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_inventory', 'client_response_comment')) $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `client_response_comment` TEXT NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'client_response_date'))    $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `client_response_date` DATE NULL");
            if (!app_col_exists($pdo, 'coding_inventory', 'client_response_by'))      $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `client_response_by` BIGINT UNSIGNED NULL");
        },

        /* De-duplicate overlapping roles (user request 2026-06-15). The legacy generic roles
         *   ar → Account Receivable, supervisor → AR Supervisor, manager → AR Manager
         * collapse onto the canonical, code-wired AR roles (which already carry the imported staff and
         * the right names). Users on a legacy role are moved onto its canonical role; the canonical
         * AR sup/mgr roles also gain read-only report access so migrated report-viewers keep viewing.
         * Idempotent: a missing legacy role is simply skipped. */
        'coding_role_dedupe_v1' => function (PDO $pdo) {
            $roleId = []; foreach ($pdo->query("SELECT id,code FROM roles")->fetchAll() as $r) $roleId[$r['code']] = (int) $r['id'];
            // keep report viewing for migrated supervisors/managers (read-only — not upload/replace)
            $grant = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code=? WHERE r.code=?");
            foreach (['ar_supervisor','ar_manager'] as $rc) foreach (['report.view','report.view_detail'] as $pc) { try { $grant->execute([$pc, $rc]); } catch (Throwable $e) {} }
            foreach (['ar' => 'account_receivable', 'supervisor' => 'ar_supervisor', 'manager' => 'ar_manager'] as $old => $new) {
                if (empty($roleId[$old]) || empty($roleId[$new])) continue;
                $o = $roleId[$old]; $n = $roleId[$new];
                try { $pdo->prepare("INSERT IGNORE INTO user_roles (user_id,role_id) SELECT user_id, ? FROM user_roles WHERE role_id=?")->execute([$n, $o]); } catch (Throwable $e) {}
                try { $pdo->prepare("DELETE FROM user_roles WHERE role_id=?")->execute([$o]); } catch (Throwable $e) {}
                try { $pdo->prepare("DELETE FROM role_permissions WHERE role_id=?")->execute([$o]); } catch (Throwable $e) {}
                try { $pdo->prepare("DELETE FROM roles WHERE id=?")->execute([$o]); } catch (Throwable $e) {}
            }
        },

        /* ---- Consolidate duplicate same-named verticals ---------------------
         * Prod accumulated >1 vertical with the same name (e.g. two "Coding") — the
         * access/reports dropdowns showed both and one carried no clients. For each
         * duplicated name keep the row with the MOST clients (tie → lowest id),
         * repoint the other rows' clients onto the keeper, and SOFT-delete the now
         * empty duplicates (deleted_at + status=inactive). Non-destructive + idempotent. */
        'coding_dedupe_verticals_v1' => function (PDO $pdo) {
            try {
                $names = $pdo->query("SELECT name FROM verticals WHERE deleted_at IS NULL
                                      GROUP BY name HAVING COUNT(*) > 1")->fetchAll(PDO::FETCH_COLUMN);
            } catch (Throwable $e) { return; }
            foreach ($names as $name) {
                $rs = $pdo->prepare("SELECT v.id, (SELECT COUNT(*) FROM clients c WHERE c.vertical_id=v.id) ncli
                                     FROM verticals v WHERE v.deleted_at IS NULL AND v.name=? ORDER BY ncli DESC, v.id ASC");
                $rs->execute([$name]); $rows = $rs->fetchAll();
                if (count($rows) < 2) continue;
                $keeper = (int) $rows[0]['id'];
                foreach (array_slice($rows, 1) as $r) {
                    $loser = (int) $r['id'];
                    try { $pdo->prepare("UPDATE clients SET vertical_id=? WHERE vertical_id=?")->execute([$keeper, $loser]); } catch (Throwable $e) {}
                    try { $pdo->prepare("UPDATE verticals SET deleted_at=NOW(), status='inactive' WHERE id=?")->execute([$loser]); } catch (Throwable $e) {}
                }
            }
        },

        /* ---- Coder acknowledgement of audit findings ------------------------
         * After an auditor flags errors, the coder must ACKNOWLEDGE: 'agreed'
         * (accepts the findings → Agreed queue, terminal) or 'raised' (disputes
         * → goes to the auditor). Adds 'agreed' to rebuttal_status + ack columns. */
        'coding_rebuttal_ack_v1' => function (PDO $pdo) {
            try { $pdo->exec("UPDATE coding_inventory SET rebuttal_status='none' WHERE rebuttal_status IS NULL"); } catch (Throwable $e) {}
            try { $pdo->exec("ALTER TABLE coding_inventory MODIFY COLUMN rebuttal_status ENUM('none','agreed','raised','accepted','rejected') NOT NULL DEFAULT 'none'"); } catch (Throwable $e) {}
            if (!app_col_exists($pdo, 'coding_inventory', 'ack_at')) { try { $pdo->exec("ALTER TABLE coding_inventory ADD COLUMN ack_at TIMESTAMP NULL"); } catch (Throwable $e) {} }
            if (!app_col_exists($pdo, 'coding_inventory', 'ack_by')) { try { $pdo->exec("ALTER TABLE coding_inventory ADD COLUMN ack_by BIGINT UNSIGNED NULL"); } catch (Throwable $e) {} }
        },

        /* ---- Per-CPT units (default 1, editable by the coder) ---------------- */
        'coding_cpt_units_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_chart_cpts', 'units')) {
                try { $pdo->exec("ALTER TABLE `coding_chart_cpts` ADD COLUMN `units` SMALLINT UNSIGNED NOT NULL DEFAULT 1 AFTER `cpt_code`"); } catch (Throwable $e) {}
            }
        },

        /* ---- Validator role: validates RFI/Disregard charts, sends back to any
         * coder vertical-wide. New permission coding.validate + coding_validator role.
         * Idempotent (the original seed already ran; this only ADDS). ---------- */
        'coding_validator_role_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),updated_at=NOW()")
                ->execute(['coding.validate', 'Validate RFI / Disregard', 'coding', 'Validate RFI / Disregard charts and send back to a coder (coding module)']);
            $pdo->prepare("INSERT INTO roles (code,name,description,is_system,scope,created_at,updated_at)
                           VALUES (?,?,?,0,'internal',NOW(),NOW())
                           ON DUPLICATE KEY UPDATE name=VALUES(name),updated_at=NOW()")
                ->execute(['coding_validator', 'Coding Validator', 'Validates RFI / Disregard charts — coding vertical']);
            $map = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id)
                                  SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code=? WHERE r.code=?");
            $map->execute(['coding.validate', 'coding_validator']);   // validator's own permission
            $map->execute(['coding.reports',  'coding_validator']);   // + can see reports
            foreach (['coding_supervisor','coding_manager'] as $rc) $map->execute(['coding.validate', $rc]);  // sup/mgr can validate too
        },

        /* ---- BS-Uploader role: a dedicated coding-vertical role that uploads provider BILLING SHEETS
         * (admin/coding-bs-upload.php). New permission coding.bs_upload + role bs_uploader; supervisors /
         * managers also get the permission. Idempotent (only ADDs). ---------- */
        'coding_bs_uploader_role_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),updated_at=NOW()")
                ->execute(['coding.bs_upload', 'Upload Billing Sheets', 'coding', 'Upload provider billing sheets (coding module)']);
            $pdo->prepare("INSERT INTO roles (code,name,description,is_system,scope,created_at,updated_at)
                           VALUES (?,?,?,0,'internal',NOW(),NOW())
                           ON DUPLICATE KEY UPDATE name=VALUES(name),updated_at=NOW()")
                ->execute(['bs_uploader', 'Billing Sheet Uploader', 'Uploads provider billing sheets — coding vertical']);
            $map = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id)
                                  SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code=? WHERE r.code=?");
            $map->execute(['coding.bs_upload', 'bs_uploader']);
            foreach (['coding_supervisor','coding_manager'] as $rc) $map->execute(['coding.bs_upload', $rc]);
        },

        /* ---- Billing-sheet storage: one row per uploaded provider billing-sheet FILE. A BS-Uploader picks
         * client»process»facility»provider + a sheet date and uploads a file (pdf/word/excel/jpg/png/…);
         * coders view/download it from My inventory. Multiple sheets per tuple+date are kept (no supersede).
         * The file bytes live under storage/uploads/billing-sheets/ (excluded from the deploy zip — PHI). ---- */
        'coding_billing_sheets_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_billing_sheets` (
                `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `client_id`     BIGINT UNSIGNED NOT NULL,
                `process_id`    BIGINT UNSIGNED NOT NULL,
                `facility_id`   BIGINT UNSIGNED NOT NULL,
                `provider_id`   BIGINT UNSIGNED NOT NULL,
                `patient_count` INT UNSIGNED NULL,
                `scribe_id`     BIGINT UNSIGNED NULL,
                `sheet_date`    DATE NOT NULL,
                `stored_name`   VARCHAR(255) NOT NULL,
                `original_name` VARCHAR(255) NOT NULL,
                `mime`          VARCHAR(120) NULL,
                `file_size`     BIGINT UNSIGNED NULL,
                `uploaded_by`   BIGINT UNSIGNED NULL,
                `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `idx_cbs_tuple` (`client_id`,`process_id`,`facility_id`,`provider_id`,`sheet_date`),
                KEY `idx_cbs_uploader` (`uploaded_by`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        },

        /* ---- Per-provider billing routing: INTERNAL billing team vs EXTERNAL vendor. On Deliverables
         * "send to billing", the chart's provider billing_route is stamped onto coding_inventory.release_destination;
         * internal charts feed the biller queue, external charts go to the External-vendor deliverables tab and
         * are excluded from internal billing (see billing_released_where). Idempotent ALTERs. ---- */
        'coding_provider_routing_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_providers', 'billing_route'))
                $pdo->exec("ALTER TABLE `coding_providers` ADD COLUMN `billing_route` ENUM('internal','external') NOT NULL DEFAULT 'internal' AFTER `status`");
            if (!app_col_exists($pdo, 'coding_providers', 'vendor_name'))
                $pdo->exec("ALTER TABLE `coding_providers` ADD COLUMN `vendor_name` VARCHAR(200) NULL AFTER `billing_route`");
            if (!app_col_exists($pdo, 'coding_inventory', 'release_destination'))
                $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `release_destination` ENUM('internal','external') NULL");
        },

        /* ---- Provider "movement date": the date a provider switched INTO internal billing (it billed via an
         * external vendor before that date). At release, a chart whose DOS is BEFORE the movement date routes to
         * the External-vendor tab even though the provider is now internal; DOS on/after follows billing_route. ---- */
        'coding_provider_movement_date_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_providers', 'movement_date'))
                $pdo->exec("ALTER TABLE `coding_providers` ADD COLUMN `movement_date` DATE NULL AFTER `vendor_name`");
        },

        /* ---- Provider TYPE: a provider is either a real BILLING provider (facility-linked) or a SCRIBE (global —
         * works under any client/process/facility, documenting for a billing provider). Used by the billing-sheet
         * uploader (tag a scribe under a billing provider) and the chart Scribe flow. ---- */
        'coding_provider_type_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_providers', 'provider_type'))
                $pdo->exec("ALTER TABLE `coding_providers` ADD COLUMN `provider_type` ENUM('billing','scribe') NOT NULL DEFAULT 'billing' AFTER `status`");
        },

        /* ---- Cross-vertical foundation: ensure every operational vertical has a row so a user's vertical can be
         * DERIVED from their role (permission group_name = vertical code). AR had no row; seed it idempotently.
         * Master data (client»process»facility»provider) is shared across all verticals; this only guarantees the
         * vertical exists for role→vertical derivation + the alignment assignment flow. ---- */
        'verticals_seed_operational_v1' => function (PDO $pdo) {
            foreach ([['AR', 'AR'], ['BILLING', 'Billing'], ['CODING', 'Coding']] as [$code, $name]) {
                $has = $pdo->prepare("SELECT id FROM verticals WHERE code=? OR name=? LIMIT 1");
                $has->execute([$code, $name]);
                if (!$has->fetchColumn()) {
                    $pdo->prepare("INSERT INTO verticals (code, name, status, created_at, updated_at) VALUES (?,?,'active',NOW(),NOW())")
                        ->execute([$code, $name]);
                }
            }
        },

        /* ---- Cross-vertical alignment: tag each user-scope row with the vertical it is FOR, so a user with roles in
         * more than one vertical can hold different client»process»facility»provider per vertical. Existing rows were
         * all created by the Coding alignment page → backfill to the Coding vertical. (NULL is treated as Coding by the
         * coding scope checks, so this is non-breaking even before the backfill runs.) ---- */
        'coding_user_scope_vertical_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_user_scope', 'vertical_id')) {
                $pdo->exec("ALTER TABLE `coding_user_scope` ADD COLUMN `vertical_id` BIGINT UNSIGNED NULL AFTER `user_id`");
                $pdo->exec("ALTER TABLE `coding_user_scope` ADD INDEX `idx_cus_vertical` (`vertical_id`)");
                $cv = $pdo->query("SELECT id FROM verticals WHERE code='CODING' OR name='Coding' LIMIT 1")->fetchColumn();
                if ($cv) $pdo->prepare("UPDATE coding_user_scope SET vertical_id=? WHERE vertical_id IS NULL")->execute([(int) $cv]);
            }
        },

        /* ---- Employee ID integrity: make users.employee_code globally UNIQUE at the DB level (the workbook importer
         * and the user create/edit form match staff on it, so duplicates cause wrong-account writes). NULLs are allowed
         * (MySQL permits many NULLs in a unique index). Pre-existing blanks become NULL and non-null duplicates are
         * collapsed first — lowest id keeps the code, the rest are nulled + audited — so the index can build. ---- */
        'users_employee_code_unique_v1' => function (PDO $pdo) {
            $has = $pdo->query("SELECT COUNT(*) FROM information_schema.STATISTICS
                WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND INDEX_NAME='uq_users_employee_code'")->fetchColumn();
            if ($has) return;
            $pdo->exec("UPDATE users SET employee_code=NULL WHERE employee_code=''");   // blank = no code (avoid '' collisions)
            $dupes = $pdo->query("SELECT id, employee_code FROM users
                WHERE employee_code IS NOT NULL
                  AND id NOT IN (SELECT m FROM (SELECT MIN(id) m FROM users WHERE employee_code IS NOT NULL GROUP BY employee_code) t)")
                ->fetchAll(PDO::FETCH_ASSOC);
            foreach ($dupes as $d) {
                $pdo->prepare("UPDATE users SET employee_code=NULL WHERE id=?")->execute([(int) $d['id']]);
                if (function_exists('audit_log')) audit_log('user.employee_code.dedupe', 'user', (int) $d['id'], ['employee_code' => $d['employee_code']], ['employee_code' => null]);
            }
            $pdo->exec("ALTER TABLE `users` ADD UNIQUE KEY `uq_users_employee_code` (`employee_code`)");
        },

        /* ---- Billing sheet: PATIENT COUNT (the count the uploader reads off the sheet — drives the Inventory
         * Report's "Count - BS") + optional SCRIBE (when the sheet is a scribe's, provider_id = the billing provider
         * it is tagged under, scribe_id = the scribe). ---- */
        'coding_bs_count_scribe_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_billing_sheets', 'patient_count'))
                $pdo->exec("ALTER TABLE `coding_billing_sheets` ADD COLUMN `patient_count` INT UNSIGNED NULL AFTER `provider_id`");
            if (!app_col_exists($pdo, 'coding_billing_sheets', 'scribe_id'))
                $pdo->exec("ALTER TABLE `coding_billing_sheets` ADD COLUMN `scribe_id` BIGINT UNSIGNED NULL AFTER `patient_count`");
        },

        /* ---- A SCRIBE billing sheet carries NO billing provider (the scribe→billing link comes from the coder's
         * charts), so provider_id must be nullable. ---- */
        'coding_bs_provider_nullable_v1' => function (PDO $pdo) {
            $col = $pdo->query("SHOW COLUMNS FROM coding_billing_sheets LIKE 'provider_id'")->fetch(PDO::FETCH_ASSOC);
            if ($col && strtoupper((string) $col['Null']) === 'NO')
                $pdo->exec("ALTER TABLE `coding_billing_sheets` MODIFY `provider_id` BIGINT UNSIGNED NULL");
        },

        /* ---- DISCRETE permission for managing the shared Master Data pages (Facilities / Providers / Hierarchy), so
         * it can be granted/denied PER USER from the Permissions page instead of being implied by any supervisor/
         * reference role. Seeded to the roles that have that derived access TODAY so nothing changes on upgrade. ---- */
        'master_data_manage_perm_v1' => function (PDO $pdo) {
            $pdo->exec("INSERT IGNORE INTO permissions (code, label, group_name, created_at, updated_at)
                        VALUES ('master.data.manage', 'Manage Master Data (Facilities, Providers, Hierarchy)', 'master', NOW(), NOW())");
            $pid = (int) $pdo->query("SELECT id FROM permissions WHERE code='master.data.manage'")->fetchColumn();
            if ($pid) {
                $rids = $pdo->query("SELECT DISTINCT r.id FROM roles r JOIN role_permissions rp ON rp.role_id=r.id
                    JOIN permissions p ON p.id=rp.permission_id WHERE p.code LIKE '%.supervise' OR p.code LIKE '%.reference'")->fetchAll(PDO::FETCH_COLUMN);
                $ins = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES (?, ?)");
                foreach ($rids as $rid) $ins->execute([(int) $rid, $pid]);
            }
        },

        /* ---- Remove DEAD legacy permissions. An earlier build seeded inventory.allocate / inventory.work /
         * inventory.manage_dropdowns, which NOTHING references (the AR module gates on ar.allocate / ar.work).
         * They only clutter the Permissions page, so drop the rows + any role/user grants. Idempotent. ---- */
        'drop_legacy_inventory_perms_v1' => function (PDO $pdo) {
            $codes = ['inventory.allocate', 'inventory.work', 'inventory.manage_dropdowns'];
            $in = "'" . implode("','", $codes) . "'";
            $ids = $pdo->query("SELECT id FROM permissions WHERE code IN ($in)")->fetchAll(PDO::FETCH_COLUMN);
            if ($ids) {
                $idin = implode(',', array_map('intval', $ids));
                $pdo->exec("DELETE FROM role_permissions WHERE permission_id IN ($idin)");
                $pdo->exec("DELETE FROM permissions WHERE id IN ($idin)");
            }
            $pdo->exec("DELETE FROM user_permissions WHERE permission_code IN ($in)");   // user overrides key by code
        },

        /* ---- coding_inventory.source: distinguishes UPLOAD charts from MANUAL charts a coder creates one-by-one
         * (clients whose inventory arrives in Excel and can't be uploaded). Manual charts loop on submit. ---- */
        'coding_inventory_source_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_inventory', 'source'))
                $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `source` VARCHAR(16) NOT NULL DEFAULT 'upload'");
        },

        /* ---- Chart SCRIBE flag: replaces the old "PCP?" toggle. When 1, the coder entered a SCRIBE in the
         * Rendering provider field (rendering_provider_id then points at a scribe-type provider); the chart's
         * billing_provider_id stays the real billing provider used for reporting. ---- */
        'coding_chart_scribe_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'coding_inventory', 'scribe_flag'))
                $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `scribe_flag` TINYINT(1) NOT NULL DEFAULT 0");
        },

        /* ---- Editable RFI/Disregard secondary reasons (supervisor/manager manage) ---- */
        'coding_secondary_reasons_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_secondary_reasons` (
                `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `kind`       ENUM('rfi','disregard') NOT NULL,
                `label`      VARCHAR(120) 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");
            // Seed from the constants the first time only (so later edits/deletes aren't undone).
            if ((int) $pdo->query("SELECT COUNT(*) FROM coding_secondary_reasons")->fetchColumn() === 0) {
                $ins = $pdo->prepare("INSERT IGNORE INTO coding_secondary_reasons (kind,label,sort_order) VALUES (?,?,?)");
                $o = 0; foreach (CODING_RFI_REASONS as $r)       $ins->execute(['rfi', $r, $o++]);
                $o = 0; foreach (CODING_DISREGARD_REASONS as $r) $ins->execute(['disregard', $r, $o++]);
            }
        },

        /* ---- CPT→ALF billing map: when a chart goes to billing each CPT is paired with its ALF
         *      code as "CPT/ALF" (e.g. 99202/99341). Supervisor/manager manage it on coding-alf.php. ---- */
        'coding_alf_map_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_alf_map` (
                `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `cpt_code`   VARCHAR(10) NOT NULL,
                `alf_code`   VARCHAR(10) NOT NULL,
                `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_cpt` (`cpt_code`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            // Seed the standard CPT→ALF pairs the first time only (later add/edit/delete are preserved).
            if ((int) $pdo->query("SELECT COUNT(*) FROM coding_alf_map")->fetchColumn() === 0) {
                $seed = [
                    ['99202','99341'],['99203','99342'],['99204','99344'],['99205','99345'],
                    ['99212','99347'],['99213','99348'],['99214','99349'],['99215','99350'],
                    ['98000','99202'],['98001','99203'],['98002','99204'],['98003','99205'],
                    ['98008','99202'],['98009','99203'],['98010','99204'],['98011','99205'],
                    ['98004','99212'],['98005','99213'],['98006','99214'],['98007','99215'],
                    ['98012','99212'],['98013','99213'],['98014','99214'],['98015','99215'],
                ];
                $ins = $pdo->prepare("INSERT IGNORE INTO coding_alf_map (cpt_code,alf_code) VALUES (?,?)");
                foreach ($seed as $s) $ins->execute($s);
            }
        },

        'coding_rebuttal_reopen_resolved_errors_v1' => function (PDO $pdo) {
            // New rule (2026-06-24): a resolved rebuttal that STILL has errors re-opens to the coder's
            // To-acknowledge queue so they can dispute the remaining error(s) again. Bring existing
            // resolved-with-errors charts (non-released) into that 'none' state; going forward the resolve
            // handler sets 'none' directly. Fully-clean resolutions (audit_result='pass') are untouched.
            try {
                $pdo->exec("UPDATE coding_inventory SET rebuttal_status='none'
                    WHERE rebuttal_status IN ('accepted','rejected') AND audit_result='error' AND released_to_billing_at IS NULL");
            } catch (Throwable $e) { /* very old schema may lack a column — ignore */ }
        },

        /* ---- Deletion Request Workflow ------------------------------------------------------------------
         * Coder requests deletion of a non-workable chart (mandatory reason) → Team Lead approves or rejects
         * it back to the coder → MANAGER permanently deletes. */
        'coding_deletion_workflow_v1' => function (PDO $pdo) {
            /* 1. THE BUCKET — a new STATUS value, not a side flag.
             * This is what makes "a chart in the Deletion Requests bucket must not appear in Coding,
             * Validation, Deliverables or Billing" true BY CONSTRUCTION rather than by diligence: every one
             * of those queues enumerates its statuses POSITIVELY (audit wants 'completed', validate wants
             * 'rfi'/'disregard', deliverables wants 'completed', billing wants released_to_billing_at), so an
             * unlisted value drops out of all of them with zero predicate edits — and any queue written in the
             * future inherits the exclusion for free. A side flag would fail OPEN: each of ~10 workable
             * queries would need its own exclusion, and coding-inventory.php's export=full builds its own
             * WHERE array while coding-chart.php's $editable gate keys off status — both would leak. */
            $pdo->exec("ALTER TABLE `coding_inventory`
                        MODIFY `status` ENUM('pending','in_progress','hold','completed','rfi','disregard','deletion_requested')
                        NOT NULL DEFAULT 'pending'");
            /* 2. Reject must return the chart EXACTLY where it came from — 'hold' and 'pending' are different
             * queues to a coder — but the new status overwrites the old one, so stash it. NULL when not in the
             * bucket. VARCHAR, not ENUM: it mirrors a value we already validate on the way in, and a VARCHAR
             * cannot silently coerce to '' the way an out-of-range ENUM write does. */
            if (!app_col_exists($pdo, 'coding_inventory', 'deletion_prev_status')) {
                $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN `deletion_prev_status` VARCHAR(16) NULL");
            }
            /* 3. THE TRAIL — deliberately NO FOREIGN KEY to coding_inventory.
             * coding_chart_events IS FK ON DELETE CASCADE (fk_cce_inv), so a chart's entire event history is
             * destroyed by the very DELETE it is supposed to document. The audit trail has to OUTLIVE its
             * subject, so this table holds inventory_id as a plain BIGINT and freezes a snapshot of the chart
             * at request time — once the Manager deletes, this row is the only forensic record that the chart
             * ever existed, who asked for it gone, who approved, and who pulled the trigger. */
            $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_deletion_requests` (
                `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `inventory_id`        BIGINT UNSIGNED NOT NULL,
                `state`               ENUM('requested','approved','rejected','deleted') NOT NULL DEFAULT 'requested',
                `reason`              VARCHAR(2000) NOT NULL,
                `requested_by`        BIGINT UNSIGNED NOT NULL,
                `requested_at`        DATETIME NOT NULL,
                `reviewed_by`         BIGINT UNSIGNED NULL,
                `reviewed_at`         DATETIME NULL,
                `review_comment`      VARCHAR(2000) NULL,
                `deleted_by`          BIGINT UNSIGNED NULL,
                `deleted_at`          DATETIME NULL,
                `prev_status`         VARCHAR(16) NULL,
                /* TWO snapshots, deliberately — they answer different questions and can differ:
                 *   chart_snapshot_json   = the chart AS REVIEWED (frozen at request time; what the Lead and
                 *                           Manager actually approved destroying);
                 *   deleted_snapshot_json = the chart AS DESTROYED (frozen inside the execute transaction).
                 * Both carry the full CPT/ICD/modifier content via coding_chart_full_snapshot(), because the
                 * inventory row holds no codes and the cascade destroys the tables that do. */
                `chart_snapshot_json`   MEDIUMTEXT NULL,
                `deleted_snapshot_json` MEDIUMTEXT NULL,
                `created_at`          DATETIME NOT NULL,
                `updated_at`          DATETIME NOT NULL,
                PRIMARY KEY (`id`),
                KEY `idx_cdr_inv` (`inventory_id`),
                KEY `idx_cdr_state` (`state`,`requested_at`),
                KEY `idx_cdr_requested_by` (`requested_by`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            // Belt-and-braces for any install that ran an earlier cut of THIS migration key (which created the
            // table without deleted_snapshot_json): CREATE TABLE IF NOT EXISTS would no-op and the column would
            // silently never appear, and this key is already recorded so it never re-runs.
            if (!app_col_exists($pdo, 'coding_deletion_requests', 'deleted_snapshot_json')) {
                $pdo->exec("ALTER TABLE `coding_deletion_requests` ADD COLUMN `deleted_snapshot_json` MEDIUMTEXT NULL");
            }
            /* 4. PERMISSIONS. Editing the CODING_PERMISSIONS / CODING_ROLE_PERMISSIONS constants seeds nothing
             * on an existing database — their only consumer, coding_perms_roles_v1, is already recorded in
             * schema_migrations and will never run again. So seed them here too. */
            $perm = $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),updated_at=NOW()");
            $perm->execute(['coding.deletion.request', 'Request Chart Deletion', 'coding',
                            'Submit a deletion request, with a reason, for a workable chart (coding module)']);
            $perm->execute(['coding.deletion.review', 'Review Deletion Requests', 'coding',
                            'Approve or reject coder deletion requests — Team Lead (coding module)']);
            $perm->execute(['coding.deletion.execute', 'Permanently Delete Charts', 'coding',
                            'Permanently delete approved charts — MANAGER ONLY (coding module)']);
            $map = $pdo->prepare("INSERT IGNORE INTO role_permissions (role_id,permission_id)
                                  SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code=? WHERE r.code=?");
            foreach (['coder','coding_supervisor','coding_manager'] as $rc) $map->execute(['coding.deletion.request', $rc]);
            foreach (['coding_supervisor','coding_manager'] as $rc)         $map->execute(['coding.deletion.review',  $rc]);
            // MANAGER ONLY — deliberately not granted to coding_supervisor. See CODING_ROLE_PERMISSIONS.
            $map->execute(['coding.deletion.execute', 'coding_manager']);
        },

    ];
}

/**
 * May this user PERMANENTLY delete a chart? Requires BOTH halves, deliberately:
 *   - the permission, so the capability is visible and revocable per-user in Access Control (a raw role
 *     check would bypass that page entirely and could never be granted to an exception);
 *   - the manager role, so that a stray per-user grant of the permission cannot BY ITSELF hand a Team Lead
 *     the trigger. Destroying PHI is irreversible; one gate is not enough.
 * SysAdmin satisfies the role half, as it does everywhere else — note can() already bypasses for sysadmin,
 * so WITHOUT the is_sysadmin() arm here a SysAdmin would be locked out of their own delete.
 */
function coding_can_delete_charts(int $uid): bool
{
    return can('coding.deletion.execute') && (is_sysadmin() || user_has_role($uid, 'coding_manager'));
}

/**
 * A COMPLETE, self-contained record of a chart — everything coding_delete_chart_cascade() is about to destroy,
 * frozen into plain data that survives the delete.
 *
 * The chart ROW ALONE is not enough, and that gap is the point of this function: coding_inventory holds no
 * codes. The billable clinical content — which CPTs, which ICDs, which modifiers — lives in coding_chart_cpts
 * and its children, all of which the cascade hard-deletes. A snapshot of just the inventory row would leave a
 * forensic record saying a chart existed while saying nothing about what was in it.
 *
 * Names are resolved and inlined (_client_name etc.) because the raw row stores only ids, and after the delete
 * there is no chart left to JOIN from — an id alone is unreadable to whoever reads this record later.
 *
 * Returns a plain array ready for json_encode. Safe on a missing chart (returns chart => null).
 */
function coding_chart_full_snapshot(PDO $pdo, int $id): array
{
    $st = $pdo->prepare("SELECT ci.*, c.name AS _client_name, f.name AS _facility_name,
                                pr.name AS _provider_name, u.name AS _coder_name, au.name AS _auditor_name
        FROM coding_inventory ci
        LEFT JOIN clients c            ON c.id  = ci.client_id
        LEFT JOIN coding_facilities f  ON f.id  = ci.facility_id
        LEFT JOIN coding_providers pr  ON pr.id = COALESCE(ci.coded_provider_id, ci.provider_id)
        LEFT JOIN users u              ON u.id  = ci.coder_id
        LEFT JOIN users au             ON au.id = ci.auditor_id
        WHERE ci.id=?");
    $st->execute([$id]);
    $chart = $st->fetch(PDO::FETCH_ASSOC) ?: null;
    $snap  = ['snapshot_at' => date('Y-m-d H:i:s'), 'chart' => $chart, 'cpts' => [], 'audit_codes' => []];
    if (!$chart) return $snap;
    // Reuse the canonical line assembler rather than re-query — it already nests ICDs + modifiers per CPT.
    $snap['cpts'] = coding_chart_lines($pdo, $id);
    // Per-code audit findings. In practice an eligible (workable) chart has none — every send-back path clears
    // them — but capture them anyway: this record is worthless if it is only sometimes complete.
    try {
        $ac = $pdo->prepare("SELECT * FROM coding_audit_codes WHERE inventory_id=? ORDER BY sort_order, id");
        $ac->execute([$id]);
        $snap['audit_codes'] = $ac->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) { /* table may predate this feature — the rest of the snapshot still stands */ }
    return $snap;
}

/**
 * PERMANENTLY delete one chart and every row that hangs off it. THE one implementation — the cascade was
 * previously copy-pasted in admin/data-cleanup.php and includes/billing.php and SKIPPED in
 * admin/coding-inventory.php, which is a live orphan bug waiting for a guard change (see below).
 *
 * Deletes children EXPLICITLY rather than leaning on FK ON DELETE CASCADE, for two independent reasons:
 *   1. billing_allocation, billing_allocation_event and billing_chart_cpt are FK-LESS. No cascade will ever
 *      touch them. admin/coding-inventory.php's bare DELETE only gets away with it because its never-coded
 *      guard means those rows cannot exist yet — one loosened guard away from silent corruption.
 *   2. The coding_* cascades exist ONLY in CREATE TABLE statements, and every one of those is CREATE TABLE
 *      IF NOT EXISTS. There is no ADD CONSTRAINT anywhere in this codebase. On any database whose tables
 *      predate the constraint being added to the CREATE, it was silently never applied — so the cascade may
 *      simply not exist in production. Deleting children explicitly is correct either way (if a cascade does
 *      exist it finds nothing left to do).
 *
 * Order matters: rows keyed on chart_cpt_id must die BEFORE the coding_chart_cpts rows they point at, or the
 * subquery that finds them returns nothing and they orphan.
 *
 * The CALLER owns the transaction, the eligibility fence, and the audit_log() before-snapshot — callers
 * legitimately differ (never-coded only / SysAdmin-any / approved-deletion-request), and the snapshot MUST be
 * taken before this runs because afterwards there is nothing left to snapshot.
 */
function coding_delete_chart_cascade(PDO $pdo, int $id): void
{
    // billing_* tables are absent on installs that never enabled billing — probe, don't assume.
    $tableExists = static function (string $t) use ($pdo): bool {
        try { $pdo->query("SELECT 1 FROM `" . preg_replace('/[^a-z_0-9]/i', '', $t) . "` LIMIT 1"); return true; }
        catch (Throwable $e) { return false; }
    };
    if ($tableExists('billing_chart_cpt')) $pdo->prepare("DELETE FROM billing_chart_cpt WHERE inventory_id=?")->execute([$id]);
    if ($tableExists('billing_allocation')) {
        if ($tableExists('billing_allocation_event')) {
            $pdo->prepare("DELETE e FROM billing_allocation_event e JOIN billing_allocation a ON a.id=e.allocation_id
                           WHERE a.inventory_id=?")->execute([$id]);
            // Second pass, NOT redundant: events carrying the chart's inventory_id but a NULL/stale
            // allocation_id (rfi_to_coding writes these) are invisible to the JOIN above. Dropping this is a
            // silent orphan. Tolerated failure: very old schemas lack the inventory_id column.
            try { $pdo->prepare("DELETE FROM billing_allocation_event WHERE inventory_id=?")->execute([$id]); } catch (Throwable $e) {}
        }
        $pdo->prepare("DELETE FROM billing_allocation WHERE inventory_id=?")->execute([$id]);
    }
    $pdo->prepare("DELETE FROM coding_audit_codes WHERE inventory_id=?")->execute([$id]);
    $pdo->prepare("DELETE FROM coding_chart_events WHERE inventory_id=?")->execute([$id]);
    $pdo->prepare("DELETE FROM coding_chart_cpt_icds WHERE chart_cpt_id IN (SELECT id FROM coding_chart_cpts WHERE inventory_id=?)")->execute([$id]);
    $pdo->prepare("DELETE FROM coding_chart_cpt_modifiers WHERE chart_cpt_id IN (SELECT id FROM coding_chart_cpts WHERE inventory_id=?)")->execute([$id]);
    $pdo->prepare("DELETE FROM coding_chart_cpts WHERE inventory_id=?")->execute([$id]);
    $pdo->prepare("DELETE FROM coding_inventory WHERE id=?")->execute([$id]);
}

/** Recompute audit_defects/audit_result from the current findings (after a rebuttal flip). */
function coding_recompute_defects(PDO $pdo, int $invId): void
{
    $st = $pdo->prepare("SELECT COUNT(*) opp, SUM(result<>'correct') def FROM coding_audit_codes WHERE inventory_id=?");
    $st->execute([$invId]);
    $r = $st->fetch();
    $opp = (int) ($r['opp'] ?? 0); $def = (int) ($r['def'] ?? 0);
    $pdo->prepare("UPDATE coding_inventory SET audit_opportunities=?, audit_defects=?, audit_result=? WHERE id=?")
        ->execute([$opp ?: null, $opp ? $def : null, $opp ? ($def ? 'error' : 'pass') : null, $invId]);
}

/** Per-code audit findings table (one row per opportunity reviewed). */
function coding_create_audit_codes_schema(PDO $pdo): void
{
    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_audit_codes` (
        `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `inventory_id`   BIGINT UNSIGNED NOT NULL,
        `chart_cpt_id`   BIGINT UNSIGNED NULL,
        `code_type`      ENUM('cpt','icd','modifier') NOT NULL,
        `code_value`     VARCHAR(20) NOT NULL,
        `result`         ENUM('correct','incorrect','missing','extra') NOT NULL DEFAULT 'correct',
        `error_category` VARCHAR(60) NULL,
        `correct_value`  VARCHAR(20) NULL,
        `note`           VARCHAR(255) NULL,
        `auditor_id`     BIGINT UNSIGNED NULL,
        `sort_order`     INT NOT NULL DEFAULT 0,
        `created_at`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_cac_inv` (`inventory_id`),
        CONSTRAINT `fk_cac_inv` FOREIGN KEY (`inventory_id`) REFERENCES `coding_inventory`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

/**
 * Replace a chart's per-code audit findings.
 * @param array $findings list of [chart_cpt_id?,code_type,code_value,result,error_category?,correct_value?,note?]
 * @return array{0:int opportunities,1:int defects}
 */
function coding_save_audit_codes(PDO $pdo, int $invId, array $findings, int $auditorId): array
{
    $pdo->prepare("DELETE FROM coding_audit_codes WHERE inventory_id=?")->execute([$invId]);
    $ins = $pdo->prepare("INSERT INTO coding_audit_codes
        (inventory_id,chart_cpt_id,code_type,code_value,result,error_category,correct_value,note,auditor_id,sort_order,created_at)
        VALUES (?,?,?,?,?,?,?,?,?,?,NOW())");
    $opp = 0; $def = 0; $seen = []; $so = 0;   // sort_order = the order findings arrive (the auditor's row order) — see B
    foreach ($findings as $f) {
        $type = in_array($f['code_type'] ?? '', ['cpt','icd','modifier'], true) ? $f['code_type'] : 'cpt';
        $val  = strtoupper(trim((string) ($f['code_value'] ?? '')));
        if ($val === '' && ($f['result'] ?? '') !== 'missing') continue;
        $res  = in_array($f['result'] ?? '', ['correct','incorrect','missing','extra'], true) ? $f['result'] : 'correct';
        $dk = $type . '|' . ((int) ($f['chart_cpt_id'] ?? 0)) . '|' . ($val ?: '(missing)');   // one finding per code on a CPT — no duplicates
        if (isset($seen[$dk])) continue; $seen[$dk] = true;
        $ins->execute([
            $invId, ($f['chart_cpt_id'] ?? null) ?: null, $type, $val ?: '(missing)', $res,
            ($f['error_category'] ?? '') ?: null, (strtoupper(trim((string) ($f['correct_value'] ?? ''))) ?: null),
            (mb_substr(trim((string) ($f['note'] ?? '')), 0, 255) ?: null), $auditorId, $so++,
        ]);
        $opp++;
        if ($res !== 'correct') $def++;
    }
    return [$opp, $def];
}

/** Load a chart's audit findings (for re-audit display). */
function coding_audit_codes(PDO $pdo, int $invId): array
{
    $st = $pdo->prepare("SELECT * FROM coding_audit_codes WHERE inventory_id=? ORDER BY sort_order, id");
    $st->execute([$invId]);
    return $st->fetchAll();
}

/* ============================================================================
 * PHASE 5 — AUDIT, BUCKET SAMPLING, REBUTTAL, RTO
 * ========================================================================== */

function coding_add_audit_columns(PDO $pdo): void
{
    $add = function (string $col, string $ddl) use ($pdo) {
        if (!app_col_exists($pdo, 'coding_inventory', $col)) $pdo->exec("ALTER TABLE `coding_inventory` ADD COLUMN $ddl");
    };
    $add('in_sample',              "`in_sample` TINYINT(1) NOT NULL DEFAULT 0");
    $add('sample_pct',             "`sample_pct` INT NULL");
    $add('audit_status',           "`audit_status` ENUM('ready_to_deliver','in_progress','rfi','disregard') NULL");
    $add('audit_result',           "`audit_result` ENUM('pass','error') NULL");
    $add('auditor_id',             "`auditor_id` BIGINT UNSIGNED NULL");
    $add('audited_at',             "`audited_at` TIMESTAMP NULL DEFAULT NULL");
    $add('audit_comment',          "`audit_comment` TEXT NULL");
    $add('audit_secondary_reason', "`audit_secondary_reason` VARCHAR(120) NULL");
    $add('rebuttal_status',        "`rebuttal_status` ENUM('none','raised','accepted','rejected') NOT NULL DEFAULT 'none'");
    $add('rebuttal_comment',       "`rebuttal_comment` TEXT NULL");
    $add('rebuttal_at',            "`rebuttal_at` TIMESTAMP NULL DEFAULT NULL");
    $add('rebuttal_resolved_at',   "`rebuttal_resolved_at` TIMESTAMP NULL DEFAULT NULL");
    $add('rto_at',                 "`rto_at` TIMESTAMP NULL DEFAULT NULL");
    $add('rto_by',                 "`rto_by` BIGINT UNSIGNED NULL");
    if (!app_index_exists($pdo, 'coding_inventory', 'idx_ci_audit'))
        $pdo->exec("ALTER TABLE `coding_inventory` ADD INDEX `idx_ci_audit` (`status`,`in_sample`,`audit_status`)");
}

function coding_create_events_schema(PDO $pdo): void
{
    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_chart_events` (
        `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `inventory_id` BIGINT UNSIGNED NOT NULL,
        `event_type`   VARCHAR(40) NOT NULL,
        `actor_id`     BIGINT UNSIGNED NULL,
        `from_state`   VARCHAR(40) NULL,
        `to_state`     VARCHAR(40) NULL,
        `note`         TEXT NULL,
        `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_cce_inv` (`inventory_id`,`created_at`),
        CONSTRAINT `fk_cce_inv` FOREIGN KEY (`inventory_id`) REFERENCES `coding_inventory`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

/** Insert an in-app notification for a user (safe — never breaks the calling action). */
function notify(int $userId, string $type, string $title, string $message, ?string $link = null): void
{
    if ($userId <= 0) return;
    try {
        db()->prepare("INSERT INTO notifications (user_id,type,title,message,link,is_read,created_at)
                       VALUES (?,?,?,?,?,0,NOW())")
            ->execute([$userId, mb_substr($type, 0, 40), mb_substr($title, 0, 200), mb_substr($message, 0, 500),
                       $link ? mb_substr($link, 0, 255) : null]);
    } catch (Throwable $e) { /* notifications are best-effort */ }
}

function coding_log_event(PDO $pdo, int $invId, string $type, ?int $actorId, ?string $note = null, ?string $from = null, ?string $to = null): void
{
    try {
        $pdo->prepare("INSERT INTO coding_chart_events (inventory_id,event_type,actor_id,from_state,to_state,note,created_at)
                       VALUES (?,?,?,?,?,?,NOW())")->execute([$invId, $type, $actorId, $from, $to, $note]);
    } catch (Throwable $e) { /* events are best-effort */ }

    // In-app notification for the cross-role hand-offs (recipient depends on the event). Centralised here so
    // every send-back / rebuttal generates one regardless of which page triggered it.
    static $NOTIFY = ['validate_return'=>1,'rto'=>1,'billing_rfi'=>1,'rebuttal_raise'=>1,'rebuttal_resolve'=>1,'audit'=>1,'coder_submit'=>1];
    if (isset($NOTIFY[$type])) {
        try {
            $ch = $pdo->prepare("SELECT ci.patient_name, ci.assigned_to, ci.coder_id, ci.auditor_id, ci.audit_defects,
                                        co.reports_to coder_sup
                                 FROM coding_inventory ci LEFT JOIN users co ON co.id=ci.coder_id WHERE ci.id=?");
            $ch->execute([$invId]); $ch = $ch->fetch();
            if ($ch) {
                $pt = $ch['patient_name'] ?: ('chart #' . $invId);
                $rcpt = 0; $title = ''; $msg = ''; $link = null;
                if ($type === 'validate_return' || $type === 'rto') {           // → the coder it was sent back to
                    $rcpt  = (int) ($ch['assigned_to'] ?: $ch['coder_id']);
                    $title = 'Chart sent back for rework';
                    $msg   = $pt . ($note ? ' — ' . $note : '') . '. It is back in your inventory (top priority).';
                    $link  = '/admin/coding-chart.php?id=' . $invId;
                } elseif ($type === 'billing_rfi') {                            // billing raised an RFI back to coding → the coder
                    $rcpt  = (int) ($ch['assigned_to'] ?: $ch['coder_id']);
                    $title = 'Chart sent back from billing (RFI)';
                    $msg   = $pt . ' — billing raised an RFI on this chart' . ($note ? ': ' . $note : '') . '. It is back in the coding RFI queue.';
                    $link  = '/admin/coding-chart.php?id=' . $invId;
                } elseif ($type === 'audit') {                                  // audit found errors → the coder (only when defects)
                    if ((int) $ch['audit_defects'] > 0) {
                        $rcpt  = (int) $ch['coder_id'];
                        $title = 'Audit found errors to acknowledge';
                        $msg   = $pt . ' — your audited chart has findings. Agree or raise a rebuttal in My rebuttals.';
                        $link  = '/admin/coding-rebuttals.php?id=' . $invId;
                    }
                } elseif ($type === 'coder_submit') {                           // coder flagged RFI/Disregard → the coder's supervisor
                    if (in_array((string) $to, ['rfi', 'disregard'], true)) {
                        $rcpt  = (int) $ch['coder_sup'];
                        $title = 'RFI / Disregard from your team';
                        $msg   = $pt . ' was flagged ' . $to . ' by a coder and is waiting in the validation queue.';
                        $link  = '/admin/coding-validate.php';
                    }
                } elseif ($type === 'rebuttal_raise') {                         // → the auditor who audited it
                    $rcpt  = (int) $ch['auditor_id'];
                    $title = 'Audit rebuttal raised';
                    $msg   = $pt . ' — a coder disputed your audit' . ($note ? ': ' . $note : '') . '.';
                    $link  = '/admin/coding-audit.php?id=' . $invId;
                } else {                                                        // rebuttal_resolve → the coder who raised it
                    $rcpt  = (int) $ch['coder_id'];
                    $title = 'Rebuttal resolved';
                    $msg   = $pt . ' — your rebuttal was ' . ($to ?: 'resolved') . '.';
                    $link  = '/admin/coding-rebuttals.php?id=' . $invId;
                }
                if ($rcpt > 0 && $rcpt !== (int) $actorId) notify($rcpt, 'coding', $title, $msg, $link);
            }
        } catch (Throwable $e) { /* never block the action */ }
    }
}

/** Tenure proxy in days (account age, falling back to first submitted chart). */
function coding_coder_tenure_days(PDO $pdo, int $coderId): int
{
    $st = $pdo->prepare("SELECT created_at FROM users WHERE id=?"); $st->execute([$coderId]);
    $start = $st->fetchColumn();
    if (!$start) { $st = $pdo->prepare("SELECT MIN(submitted_at) FROM coding_inventory WHERE coder_id=?"); $st->execute([$coderId]); $start = $st->fetchColumn(); }
    if (!$start) return 0;
    return (int) floor((time() - strtotime((string) $start)) / 86400);
}

/** Rolling DPO accuracy % over the last $days for a coder (null = no audited codes).
 *  Accuracy = (opportunities − defects) / opportunities, summed across audited charts. */
function coding_quality_pct(PDO $pdo, int $coderId, int $days = 21): ?float
{
    $st = $pdo->prepare("SELECT SUM(audit_opportunities) opp, SUM(audit_defects) def
        FROM coding_inventory WHERE coder_id=? AND audit_opportunities IS NOT NULL AND audited_at >= (NOW() - INTERVAL ? DAY)");
    $st->execute([$coderId, $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);
}

/** Sampling % to apply to a coder's completed charts (per the 3-week-quality tiers). */
function coding_sampling_pct(PDO $pdo, int $coderId): int
{
    if (coding_coder_tenure_days($pdo, $coderId) < CODING_NEW_CODER_DAYS) return 100; // new coder → audit all
    $q = coding_quality_pct($pdo, $coderId, 21);
    if ($q === null) return 100;                                                       // no history yet → audit all
    foreach (CODING_SAMPLING_TIERS as [$min, $max, $pct]) if ($q >= $min && $q < $max) return (int) $pct;
    return 100;
}

/** Decide whether a just-completed chart enters the audit sample. @return array{0:int inSample,1:int pct} */
function coding_sample_decision(PDO $pdo, int $coderId): array
{
    $pct = coding_sampling_pct($pdo, $coderId);
    $inSample = (random_int(1, 100) <= $pct) ? 1 : 0;
    return [$inSample, $pct];
}

/* ============================================================================
 * PHASE 4 — CODING VIEW (GetNext, demographics, CPT/ICD/modifier entry)
 * ========================================================================== */

function coding_create_chart_line_schema(PDO $pdo): void
{
    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_chart_cpts` (
        `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `inventory_id` BIGINT UNSIGNED NOT NULL,
        `cpt_code`     VARCHAR(10) NOT NULL,
        `dos`          DATE NULL,
        `sort_order`   INT NOT NULL DEFAULT 0,
        `created_at`   TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_ccc_inv` (`inventory_id`,`sort_order`),
        CONSTRAINT `fk_ccc_inv` FOREIGN KEY (`inventory_id`) REFERENCES `coding_inventory`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_chart_cpt_icds` (
        `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `chart_cpt_id` BIGINT UNSIGNED NOT NULL,
        `icd_code`     VARCHAR(10) NOT NULL,
        `sort_order`   INT NOT NULL DEFAULT 0,
        PRIMARY KEY (`id`),
        KEY `idx_ccci_cpt` (`chart_cpt_id`,`sort_order`),
        CONSTRAINT `fk_ccci_cpt` FOREIGN KEY (`chart_cpt_id`) REFERENCES `coding_chart_cpts`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_chart_cpt_modifiers` (
        `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `chart_cpt_id`  BIGINT UNSIGNED NOT NULL,
        `modifier_code` VARCHAR(8) NOT NULL,
        `sort_order`    INT NOT NULL DEFAULT 0,
        PRIMARY KEY (`id`),
        KEY `idx_cccm_cpt` (`chart_cpt_id`,`sort_order`),
        CONSTRAINT `fk_cccm_cpt` FOREIGN KEY (`chart_cpt_id`) REFERENCES `coding_chart_cpts`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

/**
 * Next chart for a coder. RESUME the chart already in progress first (so a coder
 * must finish or HOLD it before pulling new work), otherwise the oldest pending
 * one — TOP-priority first, then FIFO by uploaded_at. Held charts are NOT
 * auto-served; the coder resumes those deliberately from My inventory.
 *
 * $returnedOnly = serve only charts SENT BACK to the coder (rto_at set) by a
 * validator / auditor / supervisor / manager — the "Get next sent-back" flow.
 */
function coding_next_chart_id(PDO $pdo, int $uid, bool $returnedOnly = false): int
{
    $rf = $returnedOnly ? ' AND rto_at IS NOT NULL' : '';
    $st = $pdo->prepare("SELECT id FROM coding_inventory
        WHERE assigned_to=? AND status='in_progress'$rf
        ORDER BY is_priority DESC, started_at ASC, id ASC LIMIT 1");
    $st->execute([$uid]);
    if ($id = (int) $st->fetchColumn()) return $id;

    $st = $pdo->prepare("SELECT id FROM coding_inventory
        WHERE assigned_to=? AND status='pending'$rf
        ORDER BY is_priority DESC, uploaded_at ASC, id ASC LIMIT 1");
    $st->execute([$uid]);
    return (int) $st->fetchColumn();
}

/** Create a blank MANUAL chart (source='manual') the coder will fill in, for clients whose inventory isn't uploaded.
 *  Returns the new inventory id. Caller must have already validated the c»p»f»provider is in the coder's access. */
function coding_create_manual_chart(PDO $pdo, int $uid, int $cid, int $pid, int $fid, int $prid): int
{
    $pdo->prepare("INSERT INTO coding_inventory
        (client_id,process_id,facility_id,provider_id,patient_name,assigned_to,uploaded_by,uploaded_at,status,started_at,source,created_at,updated_at)
        VALUES (?,?,?,?,'',?,?,NOW(),'in_progress',NOW(),'manual',NOW(),NOW())")
        ->execute([$cid, $pid, $fid, $prid, $uid, $uid]);
    return (int) $pdo->lastInsertId();
}

/** Replace a chart's CPT lines (+ linked ICDs and modifiers) from a posted structure.
 *  @param array $cpts list of ['code'=>str, 'dos'=>?str, 'icds'=>string[], 'mods'=>string[]] */
/**
 * Replace a chart's coded lines. Dedups CPT lines: by default (coding UI) one CPT per DATE OF SERVICE. When
 * $modAware=true (backdated-billing import) the dedup ALSO keys on the modifier set, so a legitimately-repeated
 * CPT on the same DOS distinguished only by its modifier (e.g. 99213 mod-25 AND 99213 mod-59) is kept as TWO
 * distinct billable lines instead of silently collapsed. Returns the number of CPT lines actually written.
 */
function coding_save_chart_lines(PDO $pdo, int $inventoryId, array $cpts, bool $modAware = false): int
{
    $pdo->prepare("DELETE FROM coding_chart_cpts WHERE inventory_id=?")->execute([$inventoryId]);  // cascades to icds+mods
    $insC = $pdo->prepare("INSERT INTO coding_chart_cpts (inventory_id,cpt_code,units,dos,sort_order,created_at) VALUES (?,?,?,?,?,NOW())");
    $insI = $pdo->prepare("INSERT INTO coding_chart_cpt_icds (chart_cpt_id,icd_code,sort_order) VALUES (?,?,?)");
    $insM = $pdo->prepare("INSERT INTO coding_chart_cpt_modifiers (chart_cpt_id,modifier_code,sort_order) VALUES (?,?,?)");
    $cs = 0; $seenCpt = [];
    foreach ($cpts as $cpt) {
        $code = strtoupper(trim((string) ($cpt['code'] ?? '')));
        if ($code === '') continue;
        $dos = coding_parse_date($cpt['dos'] ?? null);
        $normMods = array_values(array_unique(array_filter(array_map(fn($m) => strtoupper(trim((string) $m)), (array) ($cpt['mods'] ?? [])), fn($m) => $m !== '')));
        $key = $code . '|' . (string) $dos;
        if ($modAware) { $mk = $normMods; sort($mk); $key .= '|' . implode(',', $mk); }   // same CPT+DOS but different modifier set = distinct line
        if (isset($seenCpt[$key])) continue;                          // no duplicate CPT on the same date of service (mirrors the UI guard)
        $seenCpt[$key] = true;
        $units = max(1, (int) round((float) ($cpt['units'] ?? 1)));   // whole units, default 1
        $insC->execute([$inventoryId, $code, $units, $dos, $cs++]);
        $cptId = (int) $pdo->lastInsertId();
        $is = 0; $seenIcd = [];
        foreach ((array) ($cpt['icds'] ?? []) as $icd) {
            $icd = strtoupper(trim((string) $icd)); if ($icd === '' || isset($seenIcd[$icd])) continue;   // no duplicate ICD per CPT
            $seenIcd[$icd] = true; $insI->execute([$cptId, $icd, $is++]);
        }
        $ms = 0; $seenMod = [];
        foreach ($normMods as $mod) { $insM->execute([$cptId, $mod, $ms++]); }   // already normalized + de-duped above
    }
    return $cs;
}

/** Re-sequence a chart's ICDs / modifiers within each CPT (auditor correction).
 *  @param array $seq [chartCptId => ['icds'=>[ordered codes], 'mods'=>[ordered codes]]] */
function coding_resequence_chart_codes(PDO $pdo, int $inventoryId, array $seq): void
{
    $own = $pdo->prepare("SELECT 1 FROM coding_chart_cpts WHERE id=? AND inventory_id=?");
    $ui  = $pdo->prepare("UPDATE coding_chart_cpt_icds SET sort_order=? WHERE chart_cpt_id=? AND icd_code=?");
    $um  = $pdo->prepare("UPDATE coding_chart_cpt_modifiers SET sort_order=? WHERE chart_cpt_id=? AND modifier_code=?");
    // an auditor-ADDED (missing) code lives in coding_audit_codes — give it the SAME per-CPT position so the billing
    // merge can interleave it among the coder's codes at the auditor's chosen spot. Each captured value is either a
    // coder code (updates the coder table) or a missing code (updates the audit table); the other UPDATE is a no-op.
    $ua  = $pdo->prepare("UPDATE coding_audit_codes SET sort_order=? WHERE inventory_id=? AND chart_cpt_id=? AND code_value=? AND code_type=? AND result='missing'");
    foreach ($seq as $cptId => $g) {
        $cptId = (int) $cptId; if ($cptId <= 0 || !is_array($g)) continue;
        $own->execute([$cptId, $inventoryId]); if (!$own->fetchColumn()) continue;   // must belong to this chart
        $so = 0; foreach ((array) ($g['icds'] ?? []) as $v) { $v = strtoupper(trim((string) $v)); if ($v === '') continue; $ui->execute([$so, $cptId, $v]); $ua->execute([$so, $inventoryId, $cptId, $v, 'icd']); $so++; }
        $so = 0; foreach ((array) ($g['mods'] ?? []) as $v) { $v = strtoupper(trim((string) $v)); if ($v === '') continue; $um->execute([$so, $cptId, $v]); $ua->execute([$so, $inventoryId, $cptId, $v, 'modifier']); $so++; }
    }
}

/** Load a chart's CPT lines as nested arrays for display/edit. */
function coding_chart_lines(PDO $pdo, int $inventoryId): array
{
    $cpts = $pdo->prepare("SELECT * FROM coding_chart_cpts WHERE inventory_id=? ORDER BY sort_order, id");
    $cpts->execute([$inventoryId]); $cpts = $cpts->fetchAll();
    if (!$cpts) return [];
    $ids = array_column($cpts, 'id');
    $in  = implode(',', array_fill(0, count($ids), '?'));
    $icds = $pdo->prepare("SELECT chart_cpt_id, icd_code, sort_order FROM coding_chart_cpt_icds WHERE chart_cpt_id IN ($in) ORDER BY sort_order, id");
    $icds->execute($ids);
    $mods = $pdo->prepare("SELECT chart_cpt_id, modifier_code, sort_order FROM coding_chart_cpt_modifiers WHERE chart_cpt_id IN ($in) ORDER BY sort_order, id");
    $mods->execute($ids);
    // icds/mods are the flat code lists (unchanged); icds_so/mods_so add each code's sort_order so the billing-apply
    // can interleave the auditor's added codes among the coder's by the auditor's chosen sequence.
    $byC = []; foreach ($cpts as $c) { $c['icds'] = []; $c['mods'] = []; $c['icds_so'] = []; $c['mods_so'] = []; $byC[$c['id']] = $c; }
    foreach ($icds->fetchAll() as $r) { $byC[$r['chart_cpt_id']]['icds'][] = $r['icd_code']; $byC[$r['chart_cpt_id']]['icds_so'][] = ['c'=>$r['icd_code'], 'so'=>(int)$r['sort_order']]; }
    foreach ($mods->fetchAll() as $r) { $byC[$r['chart_cpt_id']]['mods'][] = $r['modifier_code']; $byC[$r['chart_cpt_id']]['mods_so'][] = ['c'=>$r['modifier_code'], 'so'=>(int)$r['sort_order']]; }
    return array_values($byC);
}

/**
 * Apply an auditor's per-code findings to a chart's CODER lines to get the FINAL (billable) codes:
 *   incorrect → corrected value, 'extra' → dropped, 'missing' → added, 'correct'/none → kept.
 * Pure (no DB). Pass coder lines (each with 'cptId','dos','cpt','icds'[],'mods'[]) and the chart's
 * coding_audit_codes rows. Returns [['dos','cpt','icds','mods','corrected'=>bool], ...].
 * If $findings is empty the coder lines are returned unchanged (corrected=false).
 */
function coding_apply_audit_corrections(array $lines, array $findings): array
{
    $up = fn($s) => strtoupper(trim((string) $s));
    if (!$findings) {
        return array_map(fn($l) => ['dos'=>$l['dos'] ?? null, 'cpt'=>$l['cpt'], 'units'=>(int)($l['units'] ?? 1), 'icds'=>array_values($l['icds'] ?? []), 'mods'=>array_values($l['mods'] ?? []), 'corrected'=>false], $lines);
    }
    // Index: fix[cptId][type][UPPER(value)] = corrected value, or null to DROP ('extra').
    // missingLine[cptId][type][] = codes to add; missingCpt[] = new CPTs to add.
    $fix = []; $missingLine = []; $missingCpt = [];
    foreach ($findings as $f) {
        $cid = (int) ($f['chart_cpt_id'] ?? 0);
        $type = $f['code_type']; $val = $up($f['code_value'] ?? ''); $res = $f['result'] ?? 'correct';
        $cv = $up($f['correct_value'] ?? '');
        if ($res === 'missing') {
            $code = ($cv !== '' && $cv !== '(MISSING)') ? $cv : (($val !== '' && $val !== '(MISSING)') ? $val : '');
            if ($code === '') continue;
            $mso = (int) ($f['sort_order'] ?? PHP_INT_MAX);   // the auditor's chosen position (unified with coder codes)
            if ($type === 'cpt') $missingCpt[] = ['c'=>$code, 'so'=>$mso];
            else $missingLine[$cid][$type][] = ['c'=>$code, 'so'=>$mso];
        } elseif ($res === 'extra') {
            $fix[$cid][$type][$val] = null;                       // drop it
        } elseif ($res === 'incorrect' && $cv !== '') {
            $fix[$cid][$type][$val] = $cv;                        // replace with corrected value
        } // 'correct' or incorrect-without-value → keep as entered
    }
    // Merge a CPT's coder codes (fix-applied; each carries its sort_order when known) and the auditor's MISSING codes
    // (each with its sort_order) into ONE sequence ordered by sort_order — the auditor's chosen position. Coder-before-
    // missing on a tie keeps the legacy "missing appended after coder" order when no explicit re-sequence happened.
    $mergeSeq = function (array $coderSo, array $coderFlat, array $fixMap, array $missing) use ($up) {
        $items = [];
        if ($coderSo) { foreach ($coderSo as $it) { $v = $up($it['c'] ?? '');
            $fv = array_key_exists($v, $fixMap) ? $fixMap[$v] : $v; if ($fv !== null && $fv !== '') $items[] = ['c'=>$fv, 'so'=>(int)($it['so'] ?? 0), 'm'=>0]; } }
        else { $p = 0; foreach ($coderFlat as $c) { $v = $up($c);
            $fv = array_key_exists($v, $fixMap) ? $fixMap[$v] : $v; if ($fv !== null && $fv !== '') $items[] = ['c'=>$fv, 'so'=>$p, 'm'=>0]; $p++; } }
        foreach ($missing as $m) if (($m['c'] ?? '') !== '') $items[] = ['c'=>$m['c'], 'so'=>(int)($m['so'] ?? PHP_INT_MAX), 'm'=>1];
        usort($items, fn($a, $b) => ($a['so'] <=> $b['so']) ?: ($a['m'] <=> $b['m']));   // PHP 8 usort is stable
        $seen = []; $out = [];
        foreach ($items as $it) { $u = $up($it['c']); if (isset($seen[$u])) continue; $seen[$u] = 1; $out[] = $it['c']; }
        return $out;
    };
    $out = []; $firstDos = $lines[0]['dos'] ?? null;
    foreach ($lines as $l) {
        $cid = (int) ($l['cptId'] ?? 0);
        $cptV = $up($l['cpt']);
        $cptFix = array_key_exists($cptV, $fix[$cid]['cpt'] ?? []) ? $fix[$cid]['cpt'][$cptV] : $cptV;
        if ($cptFix === null) continue;                           // whole CPT line marked extra → drop
        $icds = $mergeSeq($l['icds_so'] ?? [], $l['icds'] ?? [], $fix[$cid]['icd'] ?? [],      $missingLine[$cid]['icd'] ?? []);
        $mods = $mergeSeq($l['mods_so'] ?? [], $l['mods'] ?? [], $fix[$cid]['modifier'] ?? [], $missingLine[$cid]['modifier'] ?? []);
        $out[] = ['dos'=>$l['dos'] ?? null, 'cpt'=>$cptFix, 'units'=>(int)($l['units'] ?? 1), 'icds'=>$icds, 'mods'=>$mods, 'corrected'=>true];
    }
    usort($missingCpt, fn($a, $b) => ((int)($a['so'] ?? 0)) <=> ((int)($b['so'] ?? 0)));   // added CPTs in the auditor's order
    $seenCpt = [];
    foreach ($missingCpt as $mc) { $c = $up($mc['c'] ?? ''); if ($c === '' || isset($seenCpt[$c])) continue; $seenCpt[$c] = 1;
        $out[] = ['dos'=>$firstDos, 'cpt'=>$mc['c'], 'units'=>1, 'icds'=>[], 'mods'=>[], 'corrected'=>true]; }
    return $out;
}

/** Final billable lines for one chart = coder lines with auditor corrections applied (DB wrapper). */
function coding_effective_lines(PDO $pdo, int $invId): array
{
    $lines = array_map(fn($l) => ['cptId'=>(int)$l['id'], 'dos'=>$l['dos'], 'cpt'=>$l['cpt_code'], 'units'=>(int)($l['units'] ?? 1), 'icds'=>$l['icds'], 'mods'=>$l['mods'], 'icds_so'=>$l['icds_so'] ?? [], 'mods_so'=>$l['mods_so'] ?? []], coding_chart_lines($pdo, $invId));
    $af = $pdo->prepare("SELECT chart_cpt_id, code_type, code_value, result, correct_value, sort_order FROM coding_audit_codes WHERE inventory_id=? ORDER BY sort_order, id");
    $af->execute([$invId]);
    return coding_apply_audit_corrections($lines, $af->fetchAll());
}

/** Validated CPT search for an autocomplete, scoped to (facility, provider). */
function coding_cpt_validation_search(PDO $pdo, int $facilityId, int $providerId, string $q): array
{
    $st = $pdo->prepare("SELECT cpt_code, description FROM coding_cpt_validation
        WHERE facility_id=? AND provider_id=? AND status='active'
          AND (cpt_code LIKE ? OR description LIKE ?)
        ORDER BY (cpt_code LIKE ?) DESC, cpt_code LIMIT 20");
    $st->execute([$facilityId, $providerId, $q . '%', '%' . $q . '%', $q . '%']);
    return $st->fetchAll();
}

/** True if a CPT is in the validation set for (facility, provider). */
function coding_cpt_is_valid(PDO $pdo, int $facilityId, int $providerId, string $code): bool
{
    $st = $pdo->prepare("SELECT 1 FROM coding_cpt_validation WHERE facility_id=? AND provider_id=? AND cpt_code=? AND status='active' LIMIT 1");
    $st->execute([$facilityId, $providerId, strtoupper($code)]);
    return (bool) $st->fetchColumn();
}

/** True if an ICD code (dotted or undotted) exists in the reference set — guards against incomplete entries. */
function coding_icd_is_valid(PDO $pdo, string $code): bool
{
    $code = strtoupper(trim($code));
    if ($code === '') return false;
    $st = $pdo->prepare("SELECT 1 FROM coding_icd_codes WHERE code=? OR code_dotted=? LIMIT 1");
    $st->execute([$code, $code]);
    return (bool) $st->fetchColumn();
}

/** Add a coder-introduced CPT to the validation set (source='coder'). */
function coding_cpt_add_on(PDO $pdo, int $facilityId, int $providerId, string $code, string $desc, int $uid): void
{
    // CPT validation is per-(facility,provider) — a scribe here is a de-facto facility link. Coding charts never
    // carry a scribe provider, but guard defensively so no path can facility-scope a scribe.
    if (coding_provider_is_scribe($pdo, $providerId)) throw new InvalidArgumentException('A scribe is global and cannot be linked to a facility.');
    $code = strtoupper(trim($code));
    $pdo->prepare("INSERT INTO coding_cpt_codes (code,description,is_addon,created_at) VALUES (?,?,1,NOW())
                   ON DUPLICATE KEY UPDATE description=COALESCE(NULLIF(description,''),VALUES(description))")->execute([$code, $desc ?: null]);
    $pdo->prepare("INSERT INTO coding_cpt_validation (facility_id,provider_id,cpt_code,description,source,added_by,status,created_at,updated_at)
                   VALUES (?,?,?,?, 'coder', ?, 'active', NOW(), NOW())
                   ON DUPLICATE KEY UPDATE status='active', updated_at=NOW()")->execute([$facilityId, $providerId, $code, $desc ?: null, $uid]);
}

/** ICD autocomplete. */
function coding_icd_search(PDO $pdo, string $q): array
{
    $st = $pdo->prepare("SELECT code, code_dotted, description FROM coding_icd_codes
        WHERE code LIKE ? OR code_dotted LIKE ? OR description LIKE ?
        ORDER BY (code LIKE ?) DESC, code LIMIT 20");
    $st->execute([$q . '%', $q . '%', '%' . $q . '%', $q . '%']);
    return $st->fetchAll();
}

/** Prior coded charts for the same patient at a facility (most-recent first). */
function coding_patient_history(PDO $pdo, int $facilityId, ?string $mrn, ?string $name, int $excludeId = 0, int $limit = 5): array
{
    $cond = []; $params = [$facilityId];
    if ($mrn !== null && $mrn !== '') { $cond[] = 'ci.patient_mrn = ?'; $params[] = $mrn; }
    elseif ($name !== null && $name !== '') { $cond[] = 'ci.patient_name = ?'; $params[] = $name; }
    else return [];
    $params[] = $excludeId;
    // Demographics (MRN, record-sign date, scribe flag, age, rendering & billing provider names) are pulled
    // alongside the coded lines so the chart's "Past coded DOS" panel can show the SAME header fields as the
    // live demographic container for each prior chart — not just the codes. Rendering = the scribe when scribe_flag.
    $sql = "SELECT ci.id, ci.patient_name, ci.patient_mrn, ci.patient_dob, ci.gender,
                   ci.record_sign_date, ci.scribe_flag, ci.age_at_dos, ci.dos, ci.status,
                   ci.audit_status, ci.audit_result, ci.audit_opportunities, ci.audit_defects,
                   pr.name AS provider, rp.name AS rendering_provider, bp.name AS billing_provider, au.name AS auditor
            FROM coding_inventory ci
            LEFT JOIN coding_providers pr ON pr.id=ci.coded_provider_id
            LEFT JOIN coding_providers rp ON rp.id=ci.rendering_provider_id
            LEFT JOIN coding_providers bp ON bp.id=COALESCE(ci.billing_provider_id, ci.provider_id)
            LEFT JOIN users au ON au.id=ci.auditor_id
            WHERE ci.facility_id=? AND (" . implode(' OR ', $cond) . ") AND ci.id<>? AND ci.dos IS NOT NULL
            ORDER BY ci.dos DESC LIMIT " . (int) $limit;
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rows = $st->fetchAll();
    foreach ($rows as &$r) {
        $lines = coding_chart_lines($pdo, (int) $r['id']);
        // Full per-line detail (each CPT with its own DOS, ICDs and modifiers) so the chart's
        // "Past coded DOS" panel can show complete history, not just a flat code list.
        $r['lines'] = array_map(fn($l) => [
            'cpt'  => $l['cpt_code'],
            'units'=> (int)($l['units'] ?? 1),
            'dos'  => $l['dos'] ?: $r['dos'],
            'icds' => array_values($l['icds']),
            'mods' => array_values($l['mods']),
        ], $lines);
        // Flat lists kept for any older consumer.
        $r['cpts'] = array_map(fn($l) => $l['cpt_code'], $lines);
        $icds = [];
        foreach ($lines as $l) foreach ($l['icds'] as $i) $icds[] = $i;
        $r['icds'] = array_values(array_unique($icds));
        // A friendly accuracy string when the prior chart was DPO-audited.
        $o = (int) $r['audit_opportunities']; $d = (int) $r['audit_defects'];
        $r['accuracy'] = $o > 0 ? round(100 * ($o - $d) / $o, 1) : null;
    }
    return $rows;
}

/** Most recent demographics for a patient at a facility (for auto-populate). */
function coding_patient_demographics(PDO $pdo, int $facilityId, ?string $mrn, ?string $name, int $excludeId = 0): ?array
{
    // Find the patient's MOST RECENT prior entry by MRN or name, ACROSS ALL facilities (demographics are patient-level).
    // Rendering / Billing providers are facility-specific, so they are only returned when that last entry is at the
    // CURRENT chart's facility ($facilityId) — flagged via 'same_facility'.
    $cond = []; $params = [];
    if ($mrn !== null && $mrn !== '') { $cond[] = 'patient_mrn = ?'; $params[] = $mrn; }
    elseif ($name !== null && $name !== '') { $cond[] = 'patient_name = ?'; $params[] = $name; }
    else return null;
    $ex = ''; if ($excludeId) { $ex = ' AND id <> ?'; $params[] = $excludeId; }
    $st = $pdo->prepare("SELECT patient_name, patient_mrn, patient_dob, gender, facility_id,
            rendering_provider_id, COALESCE(billing_provider_id, provider_id) billing_provider_id
        FROM coding_inventory
        WHERE (" . implode(' OR ', $cond) . ")$ex
          AND (patient_dob IS NOT NULL OR gender IS NOT NULL OR patient_mrn IS NOT NULL)
        ORDER BY updated_at DESC, id DESC LIMIT 1");
    $st->execute($params);
    $row = $st->fetch();
    if (!$row) return null;
    $same = ((int) $row['facility_id'] === $facilityId);
    return [
        'patient_name' => $row['patient_name'],
        'patient_mrn'  => $row['patient_mrn'],
        'patient_dob'  => $row['patient_dob'],
        'gender'       => $row['gender'],
        'same_facility'=> $same,
        'rendering_provider_id' => $same ? ($row['rendering_provider_id'] ?: null) : null,
        'billing_provider_id'   => $same ? ($row['billing_provider_id'] ?: null) : null,
    ];
}

/* ============================================================================
 * PHASE 3 — INVENTORY + ALLOCATION + DATA-SCOPE
 * ========================================================================== */

function coding_create_inventory_schema(PDO $pdo): void
{
    // Per-upload batch (carries the auto-created "uploaded date").
    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_upload_batches` (
        `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `uploaded_by` BIGINT UNSIGNED NULL,
        `client_id`   BIGINT UNSIGNED NOT NULL,
        `process_id`  BIGINT UNSIGNED NOT NULL,
        `facility_id` BIGINT UNSIGNED NOT NULL,
        `provider_id` BIGINT UNSIGNED NOT NULL,
        `file_name`   VARCHAR(255) NULL,
        `row_count`   INT NOT NULL DEFAULT 0,
        `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_cub_user` (`uploaded_by`,`created_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // One inventory row = one chart/account to code. Grows demographics + status as it is worked.
    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_inventory` (
        `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `batch_id`         BIGINT UNSIGNED NULL,
        `client_id`        BIGINT UNSIGNED NOT NULL,
        `process_id`       BIGINT UNSIGNED NOT NULL,
        `facility_id`      BIGINT UNSIGNED NOT NULL,
        `provider_id`      BIGINT UNSIGNED NOT NULL,
        `patient_mrn`      VARCHAR(64) NULL,
        `patient_name`     VARCHAR(200) NOT NULL,
        `patient_dob`      DATE NULL,
        `record_sign_date` DATE NULL,
        `assigned_to`      BIGINT UNSIGNED NULL,
        `uploaded_by`      BIGINT UNSIGNED NULL,
        `uploaded_at`      TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        `is_priority`      TINYINT(1) NOT NULL DEFAULT 0,
        `priority_by`      BIGINT UNSIGNED NULL,
        `priority_at`      TIMESTAMP NULL DEFAULT NULL,
        `status`           ENUM('pending','in_progress','completed','rfi','disregard') NOT NULL DEFAULT 'pending',
        `started_at`       TIMESTAMP NULL DEFAULT NULL,
        `submitted_at`     TIMESTAMP NULL DEFAULT NULL,
        -- coding-view fields (filled in Phase 4)
        `coded_provider_id`     BIGINT UNSIGNED NULL,
        `gender`                ENUM('male','female','other','unknown') NULL,
        `dos`                   DATE NULL,
        `age_at_dos`            INT NULL,
        `pcp_flag`              TINYINT(1) NULL,
        `pcp_provider_id`       BIGINT UNSIGNED NULL,
        `scribe_flag`           TINYINT(1) NOT NULL DEFAULT 0,
        `rendering_provider_id` BIGINT UNSIGNED NULL,
        `billing_provider_id`   BIGINT UNSIGNED NULL,
        `secondary_reason`      VARCHAR(120) NULL,
        `comment`               TEXT NULL,
        `coder_id`              BIGINT UNSIGNED NULL,
        `created_at`            TIMESTAMP NULL DEFAULT NULL,
        `updated_at`            TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_ci_queue` (`assigned_to`,`status`,`is_priority`,`uploaded_at`),
        KEY `idx_ci_scope` (`client_id`,`process_id`,`facility_id`,`provider_id`),
        KEY `idx_ci_status` (`status`),
        KEY `idx_ci_facility` (`facility_id`),
        KEY `idx_ci_batch` (`batch_id`),
        CONSTRAINT `fk_ci_batch`    FOREIGN KEY (`batch_id`)    REFERENCES `coding_upload_batches`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
        CONSTRAINT `fk_ci_client`   FOREIGN KEY (`client_id`)   REFERENCES `clients`(`id`)            ON DELETE RESTRICT ON UPDATE CASCADE,
        CONSTRAINT `fk_ci_process`  FOREIGN KEY (`process_id`)  REFERENCES `processes`(`id`)          ON DELETE RESTRICT ON UPDATE CASCADE,
        CONSTRAINT `fk_ci_facility` FOREIGN KEY (`facility_id`) REFERENCES `coding_facilities`(`id`)  ON DELETE RESTRICT ON UPDATE CASCADE,
        CONSTRAINT `fk_ci_provider` FOREIGN KEY (`provider_id`) REFERENCES `coding_providers`(`id`)   ON DELETE RESTRICT ON UPDATE CASCADE,
        CONSTRAINT `fk_ci_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`)              ON DELETE SET NULL ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // Per-user DATA scope: which client/process/facility/provider a coder may touch.
    // NULL at a level = "all" below the set levels. Supervisors grant extra rows for coverage.
    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_user_scope` (
        `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `user_id`     BIGINT UNSIGNED NOT NULL,
        `vertical_id` BIGINT UNSIGNED NULL,
        `client_id`   BIGINT UNSIGNED NOT NULL,
        `process_id`  BIGINT UNSIGNED NULL,
        `facility_id` BIGINT UNSIGNED NULL,
        `provider_id` BIGINT UNSIGNED NULL,
        `granted_by`  BIGINT UNSIGNED NULL,
        `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_cus_user` (`user_id`),
        KEY `idx_cus_client` (`client_id`),
        KEY `idx_cus_vertical` (`vertical_id`),
        CONSTRAINT `fk_cus_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

/* ---- data-scope helpers -------------------------------------------------- */

/** Supervisors / managers / sysadmin see the whole coding tree (no scope limit). */
function coding_scope_unrestricted(int $uid): bool
{
    return user_has_role($uid, 'sysadmin') || user_has_permission($uid, 'coding.supervise');
}

/**
 * Robust SQL predicate fencing an alignment-row's vertical_id column to the CODING vertical, for use in coding
 * scope READS. Matches by the vertical CODE via a subquery (NOT a runtime-resolved id), so it survives the Coding
 * vertical row being deduped / soft-deleted / re-id'd — a stamped id is honored as long as it is a Coding-code row.
 * NULL = legacy coding. (This is a coding-module helper; referencing the Coding vertical here is by design.)
 */
/**
 * The vertical ids that run the CODING chart workflow. Originally this was literally the one "Coding" vertical;
 * it is now DATA-DRIVEN by `verticals.module='coding'` so additional fully-independent parallel verticals can run
 * the same workflow. Falls back to the legacy Coding-by-code/name when the module column is absent or nothing is
 * flagged yet (so behaviour is unchanged until a vertical is explicitly typed 'coding'). Request-cached.
 */
function coding_workflow_vertical_ids(): array
{
    static $cache = null;
    if ($cache !== null) return $cache;
    $cache = [];
    try {
        foreach (db()->query("SELECT id FROM verticals WHERE deleted_at IS NULL AND COALESCE(status,'active')='active' AND module='coding'") as $r) $cache[] = (int) $r['id'];
    } catch (Throwable $e) { $cache = []; }   // module column not migrated yet
    if (!$cache) {
        try { foreach (db()->query("SELECT id FROM verticals WHERE deleted_at IS NULL AND (code='CODING' OR name='Coding')") as $r) $cache[] = (int) $r['id']; } catch (Throwable $e) {}
    }
    return $cache;
}

/** "$col IN (coding-workflow vertical ids)" — for fencing a NOT-NULL vertical column (e.g. clients.vertical_id). */
function coding_workflow_vertical_in_sql(string $col): string
{
    $ids = coding_workflow_vertical_ids();
    return "$col IN (" . ($ids ? implode(',', $ids) : '0') . ")";
}

function coding_vertical_scope_sql(string $col): string
{
    // Alignment rows (coding_user_scope.vertical_id) may be NULL for legacy coding; otherwise must be a coding-workflow vertical.
    $ids = coding_workflow_vertical_ids();
    return "($col IS NULL OR $col IN (" . ($ids ? implode(',', $ids) : '0') . "))";
}

/** Does $uid have access to the (client,process,facility,provider) tuple? */
function coding_user_can_tuple(int $uid, int $clientId, int $processId, int $facilityId, int $providerId): bool
{
    if (coding_scope_unrestricted($uid)) return true;
    $st = db()->prepare("SELECT 1 FROM coding_user_scope
        WHERE user_id=? AND " . coding_vertical_scope_sql('vertical_id') . " AND client_id=?
          AND (process_id IS NULL OR process_id=?)
          AND (facility_id IS NULL OR facility_id=?)
          AND (provider_id IS NULL OR provider_id=?) LIMIT 1");
    $st->execute([$uid, $clientId, $processId, $facilityId, $providerId]);
    return (bool) $st->fetchColumn();
}

/**
 * Nested accessible tree for the cascading pickers / scope display.
 * @return array<int,array{name:string,processes:array<int,array{name:string,facilities:array<int,array{name:string,providers:array<int,string>}>}>}>
 */
function coding_accessible_tree(int $uid, bool $onlyAligned = false): array
{
    // $onlyAligned forces the coding_user_scope fence even for supervisor/manager — used by "Add New Chart", where
    // you may only create charts for clients you are actually ALLOCATED to. BUT SysAdmin is the platform owner with
    // no alignment rows, so the fence would leave them with an EMPTY picker — they are never fenced (see all Coding clients).
    $restricted = !user_has_role($uid, 'sysadmin') && ($onlyAligned || !coding_scope_unrestricted($uid));
    // The Coding module's pickers show only CODING-vertical clients (separate per-vertical copies — Option B), so
    // AR / Billing same-name duplicates never appear here. A RESTRICTED coder is further narrowed to their own
    // CODING alignment rows (the JOIN below). Master data itself is still shared (one tree per vertical); this only
    // scopes what the CODING screens display.
    // LEFT JOINs so EVERY client appears (and every process / facility under it) even before facilities or providers
    // are added — the dropdowns must reflect the actual master data, not only fully-built branches.
    $sql = "SELECT DISTINCT c.id cid, c.name cname, p.id pid, p.name pname, f.id fid, f.name fname, pr.id prid, pr.name prname
            FROM clients c
            LEFT JOIN processes p ON p.client_id=c.id AND p.deleted_at IS NULL
            LEFT JOIN coding_facilities f ON f.process_id=p.id AND f.status='active'
            LEFT JOIN coding_facility_providers fp ON fp.facility_id=f.id
            LEFT JOIN coding_providers pr ON pr.id=fp.provider_id AND pr.status='active' AND pr.provider_type='billing'";
    $params = [];
    if ($restricted) {
        $sql .= " JOIN coding_user_scope s ON s.user_id=? AND " . coding_vertical_scope_sql('s.vertical_id') . " AND s.client_id=c.id
                   AND (s.process_id IS NULL OR s.process_id=p.id)
                   AND (s.facility_id IS NULL OR s.facility_id=f.id)
                   AND (s.provider_id IS NULL OR s.provider_id=pr.id)";
        $params[] = $uid;
    }
    $sql .= " WHERE c.deleted_at IS NULL AND " . coding_workflow_vertical_in_sql('c.vertical_id') . "
              ORDER BY c.name, p.name, f.name, pr.name";
    $st = db()->prepare($sql);
    $st->execute($params);

    $tree = [];
    foreach ($st->fetchAll() as $r) {
        $cid = $r['cid'];
        if (!isset($tree[$cid])) $tree[$cid] = ['name' => $r['cname'], 'processes' => []];
        if ($r['pid'] === null) continue;
        $pid = $r['pid'];
        if (!isset($tree[$cid]['processes'][$pid])) $tree[$cid]['processes'][$pid] = ['name' => $r['pname'], 'facilities' => []];
        if ($r['fid'] === null) continue;
        $fid = $r['fid'];
        if (!isset($tree[$cid]['processes'][$pid]['facilities'][$fid])) $tree[$cid]['processes'][$pid]['facilities'][$fid] = ['name' => $r['fname'], 'providers' => []];
        if ($r['prid'] !== null) $tree[$cid]['processes'][$pid]['facilities'][$fid]['providers'][$r['prid']] = $r['prname'];
    }
    return $tree;
}

/**
 * Alignment tree for the Coder-access pickers, to PROVIDER level: [cid => [name, processes => [pid =>
 * [name, facilities => [fid => [name, providers => [prid => prname]]]]]]]. Coding vertical only; a facility
 * only appears if it has ≥1 active provider (same as the upload picker coding_accessible_tree, so the two
 * stay consistent). Internal builder — callers use the two named wrappers below.
 * @param int|null $coderId  null = the whole Coding tree (manager picker); an id = restricted to that
 *                           coder's coding_user_scope (supervisor picker — can only deepen owned clients).
 */
function coding_facility_tree_build(PDO $pdo, ?int $coderId, bool $codingOnly = true): array
{
    // LEFT JOINs so every client / process / facility shows even with nothing below it yet (dropdowns reflect the
    // real master data, not only fully-built branches).
    $sql = "SELECT DISTINCT c.id cid, c.name cname, p.id pid, p.name pname, f.id fid, f.name fname, pr.id prid, pr.name prname
            FROM clients c
            LEFT JOIN processes p ON p.client_id=c.id AND p.deleted_at IS NULL
            LEFT JOIN coding_facilities f ON f.process_id=p.id AND f.status='active'
            LEFT JOIN coding_facility_providers fp ON fp.facility_id=f.id
            LEFT JOIN coding_providers pr ON pr.id=fp.provider_id AND pr.status='active' AND pr.provider_type='billing'";
    $params = [];
    if ($coderId !== null) {
        $sql .= " JOIN coding_user_scope s ON s.user_id=? AND " . coding_vertical_scope_sql('s.vertical_id') . " AND s.client_id=c.id
                   AND (s.process_id IS NULL OR s.process_id=p.id)
                   AND (s.facility_id IS NULL OR s.facility_id=f.id)
                   AND (s.provider_id IS NULL OR s.provider_id=pr.id)";
        $params[] = $coderId;
    }
    $sql .= " WHERE c.deleted_at IS NULL" . ($codingOnly ? " AND " . coding_workflow_vertical_in_sql('c.vertical_id') : "") . " ORDER BY c.name, p.name, f.name, pr.name";
    $st = $pdo->prepare($sql); $st->execute($params);
    $tree = [];
    foreach ($st->fetchAll() as $r) {
        $cid = $r['cid'];
        if (!isset($tree[$cid])) $tree[$cid] = ['name' => $r['cname'], 'processes' => []];
        if ($r['pid'] === null) continue;
        $pid = $r['pid'];
        if (!isset($tree[$cid]['processes'][$pid])) $tree[$cid]['processes'][$pid] = ['name' => $r['pname'], 'facilities' => []];
        if ($r['fid'] === null) continue;
        $fid = $r['fid'];
        if (!isset($tree[$cid]['processes'][$pid]['facilities'][$fid])) $tree[$cid]['processes'][$pid]['facilities'][$fid] = ['name' => $r['fname'], 'providers' => []];
        if ($r['prid'] !== null) $tree[$cid]['processes'][$pid]['facilities'][$fid]['providers'][$r['prid']] = $r['prname'];
    }
    return $tree;
}

/** Whole tree to facility level — for the manager/sysadmin assign picker (may align a NEW client). Defaults to
 *  CODING-vertical clients only; pass $codingOnly=false for the cross-vertical alignment picker (user-alignment.php),
 *  which must show whichever vertical's clients the target is being aligned to. */
function coding_full_facility_tree(PDO $pdo, bool $codingOnly = true): array { return coding_facility_tree_build($pdo, null, $codingOnly); }

/** Facility-level tree restricted to ONE coder's existing scope — for the supervisor assign picker, so a
 *  supervisor can only deepen (process/facility) WITHIN a client the coder already works, never add a new one.
 *  Separate, explicitly-named function (not a param on coding_accessible_tree, which is keyed to the viewer's
 *  own scope) so a caller can never accidentally widen a coder's picker. */
function coding_scope_tree_for_user(PDO $pdo, int $coderId): array { return coding_facility_tree_build($pdo, $coderId); }

/**
 * Alignment-picker tree, grouped by VERTICAL → client → process → facility → provider. Unlike the WORK pickers,
 * this includes EVERY client via LEFT JOINs — even a client with no processes/facilities/providers yet — so a user
 * can be aligned at the client (or process) level before the rest of the tree exists. Providers are billing-type.
 * Shape: [vid => [cid => ['name', 'processes' => [pid => ['name', 'facilities' => [fid => ['name',
 *        'providers' => [prid => prname]]]]]]]]. The caller filters clients by the chosen vertical.
 */
function coding_alignment_tree(PDO $pdo): array
{
    // Canonicalize each client's vertical id so a client always keys under the ACTIVE vertical id the alignment
    // dropdown uses. Three old-build / post-dedup hazards this fixes (clients were previously INVISIBLE here):
    //   • a legacy NULL vertical_id counts as Coding — the module-wide convention (see coding_vertical_scope_sql);
    //   • a soft-deleted / duplicate vertical row (e.g. code 'CODING-2' when 'CODING' was deleted) maps to the
    //     ACTIVE vertical of the same canonical GROUP — matched by name, robust to the suffixed code;
    //   • an empty code resolves by name too.
    $activeByGroup = [];   // canonical group ('coding'/'ar'/'billing') => active vertical id
    foreach (app_verticals() as $vvid => $vv) { $g = app_vertical_group_canonical($vv); if ($g !== '' && !isset($activeByGroup[$g])) $activeByGroup[$g] = (int) $vvid; }
    $codingVid = $activeByGroup['coding'] ?? app_vertical_id_by_code('CODING');
    $canon = [];   // raw vertical row id (incl. soft-deleted) => active vertical id of the same canonical group (self if none)
    foreach ($pdo->query("SELECT id, code, name FROM verticals") as $v) {
        $g = app_vertical_group_canonical($v);
        $canon[(int) $v['id']] = ($g !== '' && isset($activeByGroup[$g])) ? $activeByGroup[$g] : (int) $v['id'];
    }
    $sql = "SELECT c.vertical_id vid, c.id cid, c.name cname, p.id pid, p.name pname, f.id fid, f.name fname, pr.id prid, pr.name prname
            FROM clients c
            LEFT JOIN processes p ON p.client_id=c.id AND p.deleted_at IS NULL
            LEFT JOIN coding_facilities f ON f.process_id=p.id AND f.status='active'
            LEFT JOIN coding_facility_providers fp ON fp.facility_id=f.id
            LEFT JOIN coding_providers pr ON pr.id=fp.provider_id AND pr.status='active' AND pr.provider_type='billing'
            WHERE c.deleted_at IS NULL
            ORDER BY c.name, p.name, f.name, pr.name";
    $tree = [];
    foreach ($pdo->query($sql) as $r) {
        $vid = $r['vid'] === null ? (int) $codingVid : ($canon[(int) $r['vid']] ?? (int) $r['vid']);
        $cid = (int)$r['cid'];
        if (!isset($tree[$vid][$cid])) $tree[$vid][$cid] = ['name' => $r['cname'], 'processes' => []];
        if ($r['pid'] === null) continue;
        $pid = (int)$r['pid'];
        if (!isset($tree[$vid][$cid]['processes'][$pid])) $tree[$vid][$cid]['processes'][$pid] = ['name' => $r['pname'], 'facilities' => []];
        if ($r['fid'] === null) continue;
        $fid = (int)$r['fid'];
        if (!isset($tree[$vid][$cid]['processes'][$pid]['facilities'][$fid])) $tree[$vid][$cid]['processes'][$pid]['facilities'][$fid] = ['name' => $r['fname'], 'providers' => []];
        if ($r['prid'] !== null) $tree[$vid][$cid]['processes'][$pid]['facilities'][$fid]['providers'][(int)$r['prid']] = $r['prname'];
    }
    return $tree;
}

/** Parse a user-entered / Excel date into 'Y-m-d' (or null). Accepts Y-m-d, m/d/Y, Excel-serial-derived. */
function coding_parse_date(mixed $v): ?string
{
    $s = trim((string) $v);
    if ($s === '' || str_starts_with($s, '0000')) return null;
    if (preg_match('/^\d{4}-\d{2}-\d{2}/', $s)) return substr($s, 0, 10);
    $ts = strtotime($s);
    return $ts ? date('Y-m-d', $ts) : null;
}

/* ============================================================================
 * PHASE 2 — SCHEMA + REFERENCE/HIERARCHY IMPORT HELPERS
 * ========================================================================== */

/** Create the coding-owned tables (idempotent). */
function coding_create_schema(PDO $pdo): void
{
    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_facilities` (
        `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `process_id` BIGINT UNSIGNED NOT NULL,
        `name`       VARCHAR(200) NOT NULL,
        `code`       VARCHAR(60) NULL,
        `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
        `created_at` TIMESTAMP NULL DEFAULT NULL,
        `updated_at` TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uq_cf_process_name` (`process_id`,`name`),
        KEY `idx_cf_process` (`process_id`),
        KEY `idx_cf_name` (`name`),
        CONSTRAINT `fk_cf_process` FOREIGN KEY (`process_id`) REFERENCES `processes`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_providers` (
        `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `name`       VARCHAR(200) NOT NULL,
        `npi`        VARCHAR(20) NULL,
        `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
        `provider_type` ENUM('billing','scribe') NOT NULL DEFAULT 'billing',
        `billing_route` ENUM('internal','external') NOT NULL DEFAULT 'internal',
        `vendor_name`   VARCHAR(200) NULL,
        `movement_date` DATE NULL,
        `created_at` TIMESTAMP NULL DEFAULT NULL,
        `updated_at` TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uq_cp_name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_facility_providers` (
        `facility_id` BIGINT UNSIGNED NOT NULL,
        `provider_id` BIGINT UNSIGNED NOT NULL,
        `created_at`  TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (`facility_id`,`provider_id`),
        KEY `idx_cfp_provider` (`provider_id`),
        CONSTRAINT `fk_cfp_facility` FOREIGN KEY (`facility_id`) REFERENCES `coding_facilities`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT `fk_cfp_provider` FOREIGN KEY (`provider_id`) REFERENCES `coding_providers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_cpt_codes` (
        `code`        VARCHAR(10) NOT NULL,
        `description` VARCHAR(255) NULL,
        `is_addon`    TINYINT(1) NOT NULL DEFAULT 0,
        `created_at`  TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (`code`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_cpt_validation` (
        `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `facility_id` BIGINT UNSIGNED NOT NULL,
        `provider_id` BIGINT UNSIGNED NOT NULL,
        `cpt_code`    VARCHAR(10) NOT NULL,
        `description` VARCHAR(255) NULL,
        `source`      ENUM('seed','coder','manual') NOT NULL DEFAULT 'seed',
        `added_by`    BIGINT UNSIGNED NULL,
        `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
        `created_at`  TIMESTAMP NULL DEFAULT NULL,
        `updated_at`  TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uq_ccv` (`facility_id`,`provider_id`,`cpt_code`),
        KEY `idx_ccv_lookup` (`facility_id`,`provider_id`,`status`),
        KEY `idx_ccv_source` (`source`),
        KEY `idx_ccv_added_by` (`added_by`),
        CONSTRAINT `fk_ccv_facility` FOREIGN KEY (`facility_id`) REFERENCES `coding_facilities`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT `fk_ccv_provider` FOREIGN KEY (`provider_id`) REFERENCES `coding_providers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT `fk_ccv_added_by` FOREIGN KEY (`added_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_icd_codes` (
        `code`        VARCHAR(10) NOT NULL,
        `code_dotted` VARCHAR(12) NULL,
        `description` VARCHAR(512) NULL,
        `created_at`  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`code`),
        KEY `idx_icd_desc` (`description`(64))
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS `coding_modifiers` (
        `code`        VARCHAR(8) NOT NULL,
        `description` VARCHAR(255) NULL,
        `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
        `sort_order`  INT NOT NULL DEFAULT 0,
        `created_at`  TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (`code`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

/** Seed the 9 modifiers from the user's reference list (idempotent). */
function coding_seed_modifiers(PDO $pdo): void
{
    $desc = [
        '25' => 'Significant, separately identifiable E/M service by the same physician on the same day',
        '24' => 'Unrelated E/M service by the same physician during a postoperative period',
        '57' => 'Decision for surgery',
        '51' => 'Multiple procedures',
        '59' => 'Distinct procedural service',
        'GO' => 'Services delivered under an outpatient occupational therapy plan of care',
        'GT' => 'Via interactive audio and video telecommunication systems (telehealth)',
        'JZ' => 'Zero drug amount discarded / not administered to any patient',
        'JW' => 'Drug amount discarded / not administered to any patient',
    ];
    $ins = $pdo->prepare("INSERT INTO coding_modifiers (code,description,status,sort_order,created_at)
                          VALUES (?,?,?,?,NOW())
                          ON DUPLICATE KEY UPDATE description=VALUES(description), sort_order=VALUES(sort_order)");
    $i = 0;
    foreach (CODING_MODIFIERS as $code) {
        $ins->execute([$code, $desc[$code] ?? null, 'active', $i++]);
    }
}

/** Stream-import the ICD-10-CM code list (CODE␠␠description). Idempotent + guarded. */
function coding_import_icd(PDO $pdo, string $txtPath): int
{
    if (!is_file($txtPath)) return 0;
    $have = (int) $pdo->query("SELECT COUNT(*) FROM coding_icd_codes")->fetchColumn();
    if ($have >= 74000) return $have;   // already imported — don't re-stream the file

    $fh = fopen($txtPath, 'rb');
    if (!$fh) return 0;
    $base = "INSERT IGNORE INTO coding_icd_codes (code, code_dotted, description) VALUES ";
    $rows = []; $params = [];
    $flush = function () use (&$rows, &$params, $pdo, $base) {
        if (!$rows) return;
        $pdo->prepare($base . implode(',', $rows))->execute($params);
        $rows = []; $params = [];
    };
    while (($line = fgets($fh)) !== false) {
        $line = rtrim($line, "\r\n");
        if ($line === '' || !preg_match('/^(\S+)\s+(.*)$/', $line, $m)) continue;
        $code   = strtoupper(trim($m[1]));
        $dotted = strlen($code) > 3 ? substr($code, 0, 3) . '.' . substr($code, 3) : $code;
        $rows[] = '(?,?,?)';
        array_push($params, $code, $dotted, mb_substr(trim($m[2]), 0, 512));
        if (count($rows) >= 1000) $flush();
    }
    $flush();
    fclose($fh);
    return (int) $pdo->query("SELECT COUNT(*) FROM coding_icd_codes")->fetchColumn();
}

/* ---- hierarchy/CPT seeding (find-or-create, idempotent) ------------------ */

/** UPPER_SNAKE slug for codes. */
function coding_slug(string $s, int $max = 40): string
{
    $s = preg_replace('/[^A-Z0-9]+/', '_', strtoupper($s));
    return substr(trim((string) $s, '_'), 0, $max);
}

/** A code unique within ($table.$col) derived from $base ($table/$col are internal literals). */
function coding_unique_code(PDO $pdo, string $table, string $col, string $base): string
{
    $base = substr($base, 0, 50) ?: 'X';
    $try = $base; $i = 1;
    $chk = $pdo->prepare("SELECT 1 FROM `$table` WHERE `$col` = ? LIMIT 1");
    while (true) {
        $chk->execute([$try]);
        if (!$chk->fetchColumn()) return $try;
        $i++; $suffix = '_' . $i; $try = substr($base, 0, 50 - strlen($suffix)) . $suffix;
    }
}

function coding_find_or_create_vertical(PDO $pdo): int
{
    foreach ([['code', CODING_VERTICAL_CODE], ['name', CODING_VERTICAL_NAME]] as [$col, $val]) {
        $st = $pdo->prepare("SELECT id FROM verticals WHERE `$col` = ? AND deleted_at IS NULL LIMIT 1");
        $st->execute([$val]);
        if ($id = (int) $st->fetchColumn()) return $id;
    }
    $pdo->prepare("INSERT INTO verticals (name,code,description,status,created_at,updated_at) VALUES (?,?,?,?,NOW(),NOW())")
        ->execute([CODING_VERTICAL_NAME, CODING_VERTICAL_CODE, 'Medical coding operations', 'active']);
    return (int) $pdo->lastInsertId();
}

/**
 * Seed the whole tree + CPT validation from the mapping workbook (only 'Coding' rows).
 * @return array<string,int> insert counts
 */
function coding_seed_hierarchy_and_cpt(PDO $pdo, string $xlsxPath): array
{
    require_once __DIR__ . '/xlsx-reader.php';
    $counts = ['clients'=>0,'processes'=>0,'facilities'=>0,'providers'=>0,'facility_providers'=>0,'cpt_master'=>0,'validation'=>0,'scribes_skipped'=>0];
    if (!is_file($xlsxPath)) return $counts;

    $vid = coding_find_or_create_vertical($pdo);

    $clientCache = []; $procCache = []; $facCache = []; $provCache = []; $cptCache = []; $scribeCache = [];
    $insFacProv = $pdo->prepare("INSERT IGNORE INTO coding_facility_providers (facility_id,provider_id,created_at) VALUES (?,?,NOW())");
    $insCpt     = $pdo->prepare("INSERT IGNORE INTO coding_cpt_codes (code,description,created_at) VALUES (?,?,NOW())");
    $insVal     = $pdo->prepare("INSERT IGNORE INTO coding_cpt_validation (facility_id,provider_id,cpt_code,description,source,status,created_at,updated_at)
                                 VALUES (?,?,?,?,'seed','active',NOW(),NOW())");

    $reader = new XlsxReader($xlsxPath);
    $pdo->beginTransaction();
    try {
        $first = true;
        foreach ($reader->rows(0) as $row) {
            if ($first) { $first = false; continue; }   // header
            [$v, $c, $p, $f, $pr, $cc, $d] = array_map(fn($x) => trim((string) $x), array_pad($row, 7, ''));
            if (strcasecmp($v, CODING_VERTICAL_NAME) !== 0) continue;       // Coding rows only
            if ($c === '' || $p === '' || $f === '' || $pr === '' || $cc === '') continue;

            $clientId = $clientCache[$c]            ??= coding_foc_client($pdo, $vid, $c, $counts);
            $procId   = $procCache["$clientId|$p"]  ??= coding_foc_process($pdo, $clientId, $p, $counts);
            $facId    = $facCache["$procId|$f"]     ??= coding_foc_facility($pdo, $procId, $f, $counts);
            $provId   = $provCache[$pr]             ??= coding_foc_provider($pdo, $pr, $counts);
            // A scribe is GLOBAL — never facility-scoped. coding_foc_provider resolves BY NAME, so a scribe whose
            // name collides with a workbook row would otherwise be written into both facility tables below. Skip it.
            // (Cached per provider id so this costs at most one query per distinct provider, not one per row.)
            if ($scribeCache[$provId] ??= coding_provider_is_scribe($pdo, $provId)) { $counts['scribes_skipped']++; continue; }

            $insFacProv->execute([$facId, $provId]);
            $counts['facility_providers'] += $insFacProv->rowCount();

            if (!isset($cptCache[$cc])) { $insCpt->execute([$cc, $d ?: null]); $counts['cpt_master'] += $insCpt->rowCount(); $cptCache[$cc] = true; }

            $insVal->execute([$facId, $provId, $cc, $d ?: null]);
            $counts['validation'] += $insVal->rowCount();
        }
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        $reader->close();
        throw $e;
    }
    $reader->close();
    return $counts;
}

function coding_foc_client(PDO $pdo, int $vid, string $name, array &$counts): int
{
    $st = $pdo->prepare("SELECT id FROM clients WHERE vertical_id=? AND name=? AND deleted_at IS NULL LIMIT 1");
    $st->execute([$vid, $name]);
    if ($id = (int) $st->fetchColumn()) return $id;
    $code = coding_unique_code($pdo, 'clients', 'code', 'CDG_' . coding_slug($name, 42));
    $pdo->prepare("INSERT INTO clients (vertical_id,name,code,status,created_at,updated_at) VALUES (?,?,?,?,NOW(),NOW())")
        ->execute([$vid, $name, $code, 'active']);
    $counts['clients']++;
    return (int) $pdo->lastInsertId();
}

function coding_foc_process(PDO $pdo, int $clientId, string $name, array &$counts): int
{
    $st = $pdo->prepare("SELECT id FROM processes WHERE client_id=? AND name=? AND deleted_at IS NULL LIMIT 1");
    $st->execute([$clientId, $name]);
    if ($id = (int) $st->fetchColumn()) return $id;
    $base = coding_slug($name, 46) ?: 'PROC';
    $code = $base; $i = 1;
    $chk = $pdo->prepare("SELECT 1 FROM processes WHERE client_id=? AND code=? LIMIT 1");
    while (true) { $chk->execute([$clientId, $code]); if (!$chk->fetchColumn()) break; $i++; $code = substr($base, 0, 46) . '_' . $i; }
    $pdo->prepare("INSERT INTO processes (client_id,name,code,status,created_at,updated_at) VALUES (?,?,?,?,NOW(),NOW())")
        ->execute([$clientId, $name, $code, 'active']);
    $counts['processes']++;
    return (int) $pdo->lastInsertId();
}

function coding_foc_facility(PDO $pdo, int $procId, string $name, array &$counts): int
{
    $st = $pdo->prepare("SELECT id FROM coding_facilities WHERE process_id=? AND name=? LIMIT 1");
    $st->execute([$procId, $name]);
    if ($id = (int) $st->fetchColumn()) return $id;
    $code = (strpos($name, ' - ') !== false) ? substr($name, 0, strpos($name, ' - ')) : null;
    $pdo->prepare("INSERT INTO coding_facilities (process_id,name,code,status,created_at,updated_at) VALUES (?,?,?,?,NOW(),NOW())")
        ->execute([$procId, $name, $code ? substr(trim($code), 0, 60) : null, 'active']);
    $counts['facilities']++;
    return (int) $pdo->lastInsertId();
}

function coding_foc_provider(PDO $pdo, string $name, array &$counts): int
{
    $st = $pdo->prepare("SELECT id FROM coding_providers WHERE name=? LIMIT 1");
    $st->execute([$name]);
    if ($id = (int) $st->fetchColumn()) return $id;
    $pdo->prepare("INSERT INTO coding_providers (name,status,created_at,updated_at) VALUES (?,?,NOW(),NOW())")
        ->execute([$name, 'active']);
    $counts['providers']++;
    return (int) $pdo->lastInsertId();
}

/* ----------------------------------------------------------------------------
 * MASTER DATA: ad-hoc add/edit of facilities & providers (the admin provision,
 * separate from the bulk xlsx seed). All throw InvalidArgumentException on a
 * validation problem so the calling page can flash the message.
 * ------------------------------------------------------------------------- */

function coding_add_facility(PDO $pdo, int $processId, string $name, ?string $code, string $status, int $actorId): int
{
    $name = trim($name); $code = ($code !== null && trim($code) !== '') ? mb_substr(trim($code), 0, 60) : null;
    $status = in_array($status, ['active','inactive'], true) ? $status : 'active';
    if ($processId <= 0) throw new InvalidArgumentException('Choose a process for the facility.');
    if ($name === '')    throw new InvalidArgumentException('Facility name is required.');
    $dup = $pdo->prepare("SELECT id FROM coding_facilities WHERE process_id=? AND name=? LIMIT 1");
    $dup->execute([$processId, $name]);
    if ($dup->fetchColumn()) throw new InvalidArgumentException('A facility with that name already exists in this process.');
    $pdo->prepare("INSERT INTO coding_facilities (process_id,name,code,status,created_at,updated_at) VALUES (?,?,?,?,NOW(),NOW())")
        ->execute([$processId, mb_substr($name, 0, 200), $code, $status]);
    $id = (int) $pdo->lastInsertId();
    audit_log('coding.facility.create', 'coding_facilities', $id, null, ['process_id'=>$processId,'name'=>$name]);
    return $id;
}

function coding_update_facility(PDO $pdo, int $facilityId, string $name, ?string $code, string $status, int $actorId): void
{
    $name = trim($name); $code = ($code !== null && trim($code) !== '') ? mb_substr(trim($code), 0, 60) : null;
    $status = in_array($status, ['active','inactive'], true) ? $status : 'active';
    if ($name === '') throw new InvalidArgumentException('Facility name is required.');
    $row = $pdo->prepare("SELECT process_id FROM coding_facilities WHERE id=?"); $row->execute([$facilityId]); $pid = (int) $row->fetchColumn();
    if (!$pid) throw new InvalidArgumentException('Facility not found.');
    $dup = $pdo->prepare("SELECT id FROM coding_facilities WHERE process_id=? AND name=? AND id<>? LIMIT 1");
    $dup->execute([$pid, $name, $facilityId]);
    if ($dup->fetchColumn()) throw new InvalidArgumentException('Another facility in this process already uses that name.');
    $pdo->prepare("UPDATE coding_facilities SET name=?, code=?, status=?, updated_at=NOW() WHERE id=?")
        ->execute([mb_substr($name, 0, 200), $code, $status, $facilityId]);
    audit_log('coding.facility.update', 'coding_facilities', $facilityId, null, ['name'=>$name,'status'=>$status]);
}

/** Hard-delete a facility (SysAdmin only). REFUSED while any chart (coding_inventory) or billing sheet references it —
 *  that is real work data; deactivate the facility instead, or clear the data first (SysAdmin ▸ Data cleanup). On
 *  delete the FK cascades drop its provider links (coding_facility_providers) + CPT validation (coding_cpt_validation);
 *  invalid user-scope alignments pointing at this facility are pruned (no FK there). Returns counts of what was removed. */
function coding_delete_facility(PDO $pdo, int $facilityId, int $actorId): array
{
    $f = $pdo->prepare("SELECT id, name, process_id, status FROM coding_facilities WHERE id=?");
    $f->execute([$facilityId]); $fac = $f->fetch(PDO::FETCH_ASSOC);
    if (!$fac) throw new InvalidArgumentException('Facility not found.');

    $q = $pdo->prepare("SELECT COUNT(*) FROM coding_inventory WHERE facility_id=?"); $q->execute([$facilityId]);
    if (($n = (int) $q->fetchColumn()) > 0)
        throw new RuntimeException("Cannot delete “{$fac['name']}” — $n chart(s) exist for this facility. Deactivate it instead, or clear the charts first (SysAdmin ▸ Data cleanup).");
    $q = $pdo->prepare("SELECT COUNT(*) FROM coding_billing_sheets WHERE facility_id=?"); $q->execute([$facilityId]);
    if (($n = (int) $q->fetchColumn()) > 0)
        throw new RuntimeException("Cannot delete “{$fac['name']}” — $n billing sheet(s) reference this facility. Remove them first.");

    $q = $pdo->prepare("SELECT COUNT(*) FROM coding_facility_providers WHERE facility_id=?"); $q->execute([$facilityId]); $linkN = (int) $q->fetchColumn();
    $q = $pdo->prepare("SELECT COUNT(*) FROM coding_cpt_validation   WHERE facility_id=?"); $q->execute([$facilityId]); $cptN  = (int) $q->fetchColumn();

    $pdo->beginTransaction();
    try {
        $sc = $pdo->prepare("DELETE FROM coding_user_scope WHERE facility_id=?"); $sc->execute([$facilityId]); $scopeN = $sc->rowCount();
        // Upload-batch rows carry NOT-NULL facility_id with no FK; once the facility is gone the (charts-already-cleared)
        // batch is orphan metadata that would vanish from upload history via its INNER JOINs — prune it here.
        $bt = $pdo->prepare("DELETE FROM coding_upload_batches WHERE facility_id=?"); $bt->execute([$facilityId]); $batchN = $bt->rowCount();
        $pdo->prepare("DELETE FROM coding_facilities WHERE id=?")->execute([$facilityId]);   // cascades: facility_providers + cpt_validation
        $pdo->commit();
    } catch (Throwable $e) { $pdo->rollBack(); throw $e; }
    audit_log('coding.facility.delete', 'coding_facilities', $facilityId, $fac, ['provider_links'=>$linkN,'cpt_validation'=>$cptN,'user_scope'=>$scopeN,'upload_batches'=>$batchN]);
    return ['provider_links'=>$linkN, 'cpt_validation'=>$cptN, 'user_scope'=>$scopeN, 'upload_batches'=>$batchN];
}

/** Hard-delete a provider (SysAdmin only). REFUSED while any chart references it in ANY role (provider / coded /
 *  rendering / billing / PCP) or any billing sheet references it (as provider OR scribe) — deactivate the provider
 *  instead, or clear the data first. On delete the FK cascades drop its facility links + CPT validation; invalid
 *  user-scope alignments pointing at this provider are pruned. Returns counts of what was removed. */
function coding_delete_provider(PDO $pdo, int $providerId, int $actorId): array
{
    $p = $pdo->prepare("SELECT id, name, provider_type, status FROM coding_providers WHERE id=?");
    $p->execute([$providerId]); $prov = $p->fetch(PDO::FETCH_ASSOC);
    if (!$prov) throw new InvalidArgumentException('Provider not found.');

    $q = $pdo->prepare("SELECT COUNT(*) FROM coding_inventory
        WHERE provider_id=? OR coded_provider_id=? OR rendering_provider_id=? OR billing_provider_id=? OR pcp_provider_id=?");
    $q->execute([$providerId, $providerId, $providerId, $providerId, $providerId]);
    if (($n = (int) $q->fetchColumn()) > 0)
        throw new RuntimeException("Cannot delete “{$prov['name']}” — referenced by $n chart(s). Deactivate it instead, or clear the charts first (SysAdmin ▸ Data cleanup).");
    $q = $pdo->prepare("SELECT COUNT(*) FROM coding_billing_sheets WHERE provider_id=? OR scribe_id=?");
    $q->execute([$providerId, $providerId]);
    if (($n = (int) $q->fetchColumn()) > 0)
        throw new RuntimeException("Cannot delete “{$prov['name']}” — referenced by $n billing sheet(s). Remove them first.");

    $q = $pdo->prepare("SELECT COUNT(*) FROM coding_facility_providers WHERE provider_id=?"); $q->execute([$providerId]); $linkN = (int) $q->fetchColumn();
    $q = $pdo->prepare("SELECT COUNT(*) FROM coding_cpt_validation   WHERE provider_id=?"); $q->execute([$providerId]); $cptN  = (int) $q->fetchColumn();

    $pdo->beginTransaction();
    try {
        $sc = $pdo->prepare("DELETE FROM coding_user_scope WHERE provider_id=?"); $sc->execute([$providerId]); $scopeN = $sc->rowCount();
        // Upload-batch rows carry NOT-NULL provider_id with no FK; once the provider is gone the (charts-already-cleared)
        // batch is orphan metadata that would vanish from upload history via its INNER JOINs — prune it here.
        $bt = $pdo->prepare("DELETE FROM coding_upload_batches WHERE provider_id=?"); $bt->execute([$providerId]); $batchN = $bt->rowCount();
        $pdo->prepare("DELETE FROM coding_providers WHERE id=?")->execute([$providerId]);   // cascades: facility_providers + cpt_validation
        $pdo->commit();
    } catch (Throwable $e) { $pdo->rollBack(); throw $e; }
    audit_log('coding.provider.delete', 'coding_providers', $providerId, $prov, ['facility_links'=>$linkN,'cpt_validation'=>$cptN,'user_scope'=>$scopeN,'upload_batches'=>$batchN]);
    return ['facility_links'=>$linkN, 'cpt_validation'=>$cptN, 'user_scope'=>$scopeN, 'upload_batches'=>$batchN];
}

/** Normalise a billing route + vendor name pair (vendor only kept when external). */
function coding_norm_route(string $route, ?string $vendor): array
{
    $route = in_array($route, ['internal','external'], true) ? $route : 'internal';
    $vendor = ($route === 'external' && $vendor !== null && trim($vendor) !== '') ? mb_substr(trim($vendor), 0, 200) : null;
    return [$route, $vendor];
}

function coding_add_provider(PDO $pdo, string $name, ?string $npi, string $status, int $actorId, string $billingRoute = 'internal', ?string $vendorName = null, ?string $movementDate = null, string $providerType = 'billing'): int
{
    $name = trim($name); $npi = ($npi !== null && trim($npi) !== '') ? trim($npi) : null;
    $status = in_array($status, ['active','inactive'], true) ? $status : 'active';
    $providerType = in_array($providerType, ['billing','scribe'], true) ? $providerType : 'billing';
    [$billingRoute, $vendorName] = coding_norm_route($billingRoute, $vendorName);
    $movementDate = coding_norm_movement_date($movementDate);
    if ($name === '') throw new InvalidArgumentException('Provider name is required.');
    if ($npi !== null && !preg_match('/^\d{10}$/', $npi)) throw new InvalidArgumentException('NPI must be 10 digits (or left blank).');
    $dup = $pdo->prepare("SELECT id FROM coding_providers WHERE name=? LIMIT 1");
    $dup->execute([$name]);
    if ($dup->fetchColumn()) throw new InvalidArgumentException('A provider with that name already exists.');
    $pdo->prepare("INSERT INTO coding_providers (name,npi,status,provider_type,billing_route,vendor_name,movement_date,created_at,updated_at) VALUES (?,?,?,?,?,?,?,NOW(),NOW())")
        ->execute([mb_substr($name, 0, 200), $npi, $status, $providerType, $billingRoute, $vendorName, $movementDate]);
    $id = (int) $pdo->lastInsertId();
    audit_log('coding.provider.create', 'coding_providers', $id, null, ['name'=>$name,'npi'=>$npi,'type'=>$providerType,'route'=>$billingRoute,'movement_date'=>$movementDate]);
    return $id;
}

function coding_update_provider(PDO $pdo, int $providerId, string $name, ?string $npi, string $status, int $actorId, string $billingRoute = 'internal', ?string $vendorName = null, ?string $movementDate = null, string $providerType = 'billing'): void
{
    $name = trim($name); $npi = ($npi !== null && trim($npi) !== '') ? trim($npi) : null;
    $status = in_array($status, ['active','inactive'], true) ? $status : 'active';
    $providerType = in_array($providerType, ['billing','scribe'], true) ? $providerType : 'billing';
    [$billingRoute, $vendorName] = coding_norm_route($billingRoute, $vendorName);
    $movementDate = coding_norm_movement_date($movementDate);
    if ($name === '') throw new InvalidArgumentException('Provider name is required.');
    if ($npi !== null && !preg_match('/^\d{10}$/', $npi)) throw new InvalidArgumentException('NPI must be 10 digits (or left blank).');
    $dup = $pdo->prepare("SELECT id FROM coding_providers WHERE name=? AND id<>? LIMIT 1");
    $dup->execute([$name, $providerId]);
    if ($dup->fetchColumn()) throw new InvalidArgumentException('Another provider already uses that name.');
    $pdo->prepare("UPDATE coding_providers SET name=?, npi=?, status=?, provider_type=?, billing_route=?, vendor_name=?, movement_date=?, updated_at=NOW() WHERE id=?")
        ->execute([mb_substr($name, 0, 200), $npi, $status, $providerType, $billingRoute, $vendorName, $movementDate, $providerId]);
    audit_log('coding.provider.update', 'coding_providers', $providerId, null, ['name'=>$name,'status'=>$status,'type'=>$providerType,'route'=>$billingRoute,'movement_date'=>$movementDate]);
}

/* =========================================================================================================
 * BULK IMPORTERS (xlsx) for master data — facilities, providers, CPT↔provider links. Each takes data rows
 * (header already stripped by the page) as ['key'=>value] maps and returns a report:
 *   ['created'=>n, 'updated'=>n, 'reactivated'=>n, 'skipped'=>n, 'errors'=>['Row N: …', …]].
 * Per-row errors never abort the batch. Idempotent: re-running skips/updates instead of duplicating.
 * ========================================================================================================= */

/** SQL fragment: a clients alias is a CODING-vertical client (master data is shared, but these importers feed the
 *  Coding module's separate per-vertical copies, so resolution is fenced to Coding). */
function coding_bulk_coding_client_sql(string $alias = 'c'): string
{
    return coding_workflow_vertical_in_sql("$alias.vertical_id");
}

/** Bulk-create facilities. Rows: ['client','process','name','code','status']. Resolves client»process (by name OR
 *  code, Coding vertical), then find-or-create the facility (UNIQUE process_id+name; reactivates an inactive match). */
function coding_import_facilities(PDO $pdo, array $rows, int $actorId): array
{
    $rep = ['created'=>0, 'reactivated'=>0, 'skipped'=>0, 'errors'=>[], 'rows'=>[]];
    // Record one result per non-blank row: status created|reactivated|skipped|error (+why). 'rows' powers the
    // downloadable per-row result; 'errors' (strings) stays for the existing on-page summary card.
    $record = function (int $ln, string $client, string $proc, string $name, string $code, string $status, string $why = '') use (&$rep) {
        $rep['rows'][] = ['row'=>$ln, 'client'=>$client, 'process'=>$proc, 'facility'=>$name, 'code'=>$code, 'status'=>$status, 'note'=>$why];
        if ($status === 'error') $rep['errors'][] = "Row $ln: $why";
    };
    $cv = coding_bulk_coding_client_sql('c');
    $findProc = $pdo->prepare("SELECT p.id FROM processes p JOIN clients c ON c.id=p.client_id
        WHERE c.deleted_at IS NULL AND p.deleted_at IS NULL AND $cv AND (c.name=? OR c.code=?) AND (p.name=? OR p.code=?) LIMIT 2");
    $findFac = $pdo->prepare("SELECT id,status FROM coding_facilities WHERE process_id=? AND name=? LIMIT 1");
    foreach ($rows as $n => $r) {
        $ln = (int)$n + 2;
        $client = trim((string)($r['client'] ?? '')); $proc = trim((string)($r['process'] ?? ''));
        $name = trim((string)($r['name'] ?? '')); $code = trim((string)($r['code'] ?? ''));
        $status = strtolower(trim((string)($r['status'] ?? 'active'))) === 'inactive' ? 'inactive' : 'active';
        if ($client === '' && $proc === '' && $name === '' && $code === '') continue;       // blank row (not recorded)
        if ($name === '')                    { $record($ln,$client,$proc,$name,$code,'error','facility name is required.'); continue; }
        if ($client === '' || $proc === '')  { $record($ln,$client,$proc,$name,$code,'error','client and process are required.'); continue; }
        $findProc->execute([$client, $client, $proc, $proc]); $pids = $findProc->fetchAll(PDO::FETCH_COLUMN);
        if (!$pids)            { $record($ln,$client,$proc,$name,$code,'error',"no Coding client » process matches \"$client » $proc\" (check spelling / that the client is a Coding client)."); continue; }
        if (count($pids) > 1)  { $record($ln,$client,$proc,$name,$code,'error',"\"$client » $proc\" is ambiguous (matches several)."); continue; }
        $pid = (int)$pids[0];
        $findFac->execute([$pid, $name]); $ex = $findFac->fetch(PDO::FETCH_ASSOC);
        if ($ex) {
            if ($ex['status'] !== 'active' && $status === 'active') {
                $pdo->prepare("UPDATE coding_facilities SET status='active', code=COALESCE(NULLIF(?,''),code), updated_at=NOW() WHERE id=?")->execute([$code, (int)$ex['id']]);
                $rep['reactivated']++;
                $record($ln,$client,$proc,$name,$code,'reactivated','existed but was inactive — re-activated.');
            } else {
                $rep['skipped']++;
                $record($ln,$client,$proc,$name,$code,'skipped', $ex['status']==='active' ? 'already exists (active) in this process — no change.' : 'already exists (inactive); sheet did not request activation.');
            }
            continue;
        }
        try { coding_add_facility($pdo, $pid, $name, $code !== '' ? $code : null, $status, $actorId); $rep['created']++; $record($ln,$client,$proc,$name,$code,'created'); }
        catch (Throwable $e) { $record($ln,$client,$proc,$name,$code,'error',$e->getMessage()); }
    }
    return $rep;
}

/** Bulk-create/update providers (global, UNIQUE by name). Rows: ['name','npi','type','route','vendor','movement']. */
function coding_import_providers(PDO $pdo, array $rows, int $actorId): array
{
    $rep = ['created'=>0, 'updated'=>0, 'skipped'=>0, 'errors'=>[]];
    $find = $pdo->prepare("SELECT id FROM coding_providers WHERE name=? LIMIT 1");
    foreach ($rows as $n => $r) {
        $ln = (int)$n + 2;
        $name = trim((string)($r['name'] ?? ''));
        $npi  = preg_replace('/\D/', '', (string)($r['npi'] ?? ''));
        $type = strtolower(trim((string)($r['type'] ?? 'billing'))); $type = str_starts_with($type, 'scrib') ? 'scribe' : 'billing';
        $route= strtolower(trim((string)($r['route'] ?? 'internal'))); $route = str_starts_with($route, 'ext') ? 'external' : 'internal';
        $vendor = trim((string)($r['vendor'] ?? ''));
        $mvRaw = trim((string)($r['movement'] ?? '')); $mv = null;
        if ($name === '') {
            if (($npi.$vendor.$mvRaw) === '') continue;                                       // blank row
            $rep['errors'][] = "Row $ln: provider name is required."; continue;
        }
        if ($npi !== '' && strlen($npi) !== 10) { $rep['errors'][] = "Row $ln: NPI must be 10 digits."; continue; }
        if ($mvRaw !== '') { $d = date_create($mvRaw); if (!$d) { $rep['errors'][] = "Row $ln: movement date \"$mvRaw\" is not a valid date."; continue; } $mv = $d->format('Y-m-d'); }
        $find->execute([$name]); $exId = (int)$find->fetchColumn();
        try {
            if ($exId) { coding_update_provider($pdo, $exId, $name, $npi ?: null, 'active', $actorId, $route, $vendor ?: null, $mv, $type); $rep['updated']++; }
            else       { coding_add_provider($pdo, $name, $npi ?: null, 'active', $actorId, $route, $vendor ?: null, $mv, $type); $rep['created']++; }
        } catch (Throwable $e) { $rep['errors'][] = "Row $ln: " . $e->getMessage(); }
    }
    return $rep;
}

/** Bulk-link CPTs to a (facility, provider). Rows: ['client','process','facility','provider','cpt','desc'].
 *  Resolves client»process»facility (Coding) + provider (by name); upserts coding_cpt_validation (UNIQUE fac+prov+cpt). */
function coding_import_cpt_links(PDO $pdo, array $rows, int $actorId): array
{
    $rep = ['created'=>0, 'updated'=>0, 'skipped'=>0, 'errors'=>[], 'rows'=>[]];
    // Record one result per non-blank input row: status created|updated|error (+message). The 'rows' detail
    // powers the downloadable per-CPT result; 'errors' (strings) is kept for the existing on-page summary.
    $record = function (int $ln, string $client, string $proc, string $fac, string $prov, string $cpt, string $desc, string $status, string $err = '') use (&$rep) {
        $rep['rows'][] = ['row'=>$ln, 'client'=>$client, 'process'=>$proc, 'facility'=>$fac, 'provider'=>$prov, 'cpt'=>$cpt, 'description'=>$desc, 'status'=>$status, 'error'=>$err];
        if ($status === 'error') $rep['errors'][] = "Row $ln: $err";
    };
    $cv = coding_bulk_coding_client_sql('c');
    $findFac = $pdo->prepare("SELECT f.id FROM coding_facilities f JOIN processes p ON p.id=f.process_id JOIN clients c ON c.id=p.client_id
        WHERE c.deleted_at IS NULL AND p.deleted_at IS NULL AND $cv AND (c.name=? OR c.code=?) AND (p.name=? OR p.code=?) AND (f.name=? OR f.code=?) LIMIT 2");
    $findProv = $pdo->prepare("SELECT id FROM coding_providers WHERE name=? LIMIT 2");
    $exists   = $pdo->prepare("SELECT 1 FROM coding_cpt_validation WHERE facility_id=? AND provider_id=? AND cpt_code=? LIMIT 1");
    $cptMaster= $pdo->prepare("INSERT IGNORE INTO coding_cpt_codes (code,description,created_at) VALUES (?,?,NOW())");
    $link     = $pdo->prepare("INSERT INTO coding_cpt_validation (facility_id,provider_id,cpt_code,description,source,added_by,status,created_at,updated_at)
                               VALUES (?,?,?,?, 'manual', ?, 'active', NOW(), NOW())
                               ON DUPLICATE KEY UPDATE status='active', description=VALUES(description), updated_at=NOW()");
    foreach ($rows as $n => $r) {
        $ln = (int)$n + 2;
        $client = trim((string)($r['client'] ?? '')); $proc = trim((string)($r['process'] ?? '')); $fac = trim((string)($r['facility'] ?? ''));
        $prov = trim((string)($r['provider'] ?? '')); $cpt = strtoupper(trim((string)($r['cpt'] ?? ''))); $desc = trim((string)($r['desc'] ?? ''));
        if ($client === '' && $proc === '' && $fac === '' && $prov === '' && $cpt === '') continue;   // blank row
        if (!preg_match('/^[A-Z0-9]{3,7}$/', $cpt)) { $record($ln,$client,$proc,$fac,$prov,$cpt,$desc,'error',"CPT \"$cpt\" must be 3–7 letters/digits."); continue; }
        if ($client === '' || $proc === '' || $fac === '' || $prov === '') { $record($ln,$client,$proc,$fac,$prov,$cpt,$desc,'error','client, process, facility and provider are all required.'); continue; }
        $findFac->execute([$client, $client, $proc, $proc, $fac, $fac]); $fids = $findFac->fetchAll(PDO::FETCH_COLUMN);
        if (!$fids)           { $record($ln,$client,$proc,$fac,$prov,$cpt,$desc,'error',"facility \"$client » $proc » $fac\" not found (Coding)."); continue; }
        if (count($fids) > 1) { $record($ln,$client,$proc,$fac,$prov,$cpt,$desc,'error',"\"$client » $proc » $fac\" is ambiguous."); continue; }
        $findProv->execute([$prov]); $pids = $findProv->fetchAll(PDO::FETCH_COLUMN);
        if (!$pids)           { $record($ln,$client,$proc,$fac,$prov,$cpt,$desc,'error',"provider \"$prov\" not found."); continue; }
        if (count($pids) > 1) { $record($ln,$client,$proc,$fac,$prov,$cpt,$desc,'error',"provider \"$prov\" is ambiguous (rename one)."); continue; }
        $fid = (int)$fids[0]; $pid = (int)$pids[0];
        if (coding_provider_is_scribe($pdo, $pid)) { $record($ln,$client,$proc,$fac,$prov,$cpt,$desc,'error',"provider \"$prov\" is a scribe — scribes are global and cannot be linked to a facility."); continue; }
        $exists->execute([$fid, $pid, $cpt]); $had = (bool)$exists->fetchColumn();
        try {
            $cptMaster->execute([$cpt, $desc ?: null]);
            $link->execute([$fid, $pid, $cpt, $desc ?: null, $actorId]);
            if ($had) $rep['updated']++; else $rep['created']++;
            $record($ln,$client,$proc,$fac,$prov,$cpt,$desc, $had ? 'updated' : 'created');
        } catch (Throwable $e) { $record($ln,$client,$proc,$fac,$prov,$cpt,$desc,'error',$e->getMessage()); }
    }
    return $rep;
}

/** Bulk-link billing providers to facilities. Rows: ['client','process','facility','provider']. Resolves the facility
 *  (client»process»facility, Coding) + provider (by name); links via coding_link_provider (scribes rejected;
 *  already-linked = skipped). */
function coding_import_facility_providers(PDO $pdo, array $rows, int $actorId): array
{
    $rep = ['linked'=>0, 'skipped'=>0, 'errors'=>[]];
    $cv = coding_bulk_coding_client_sql('c');
    $findFac = $pdo->prepare("SELECT f.id FROM coding_facilities f JOIN processes p ON p.id=f.process_id JOIN clients c ON c.id=p.client_id
        WHERE c.deleted_at IS NULL AND p.deleted_at IS NULL AND $cv AND (c.name=? OR c.code=?) AND (p.name=? OR p.code=?) AND (f.name=? OR f.code=?) LIMIT 2");
    $findProv = $pdo->prepare("SELECT id FROM coding_providers WHERE name=? LIMIT 2");
    $isLinked = $pdo->prepare("SELECT 1 FROM coding_facility_providers WHERE facility_id=? AND provider_id=? LIMIT 1");
    foreach ($rows as $n => $r) {
        $ln = (int)$n + 2;
        $client = trim((string)($r['client'] ?? '')); $proc = trim((string)($r['process'] ?? ''));
        $fac = trim((string)($r['facility'] ?? '')); $prov = trim((string)($r['provider'] ?? ''));
        if ($client === '' && $proc === '' && $fac === '' && $prov === '') continue;
        if ($client === '' || $proc === '' || $fac === '' || $prov === '') { $rep['errors'][] = "Row $ln: client, process, facility and provider are all required."; continue; }
        $findFac->execute([$client, $client, $proc, $proc, $fac, $fac]); $fids = $findFac->fetchAll(PDO::FETCH_COLUMN);
        if (!$fids)           { $rep['errors'][] = "Row $ln: facility \"$client » $proc » $fac\" not found (Coding)."; continue; }
        if (count($fids) > 1) { $rep['errors'][] = "Row $ln: \"$client » $proc » $fac\" is ambiguous."; continue; }
        $findProv->execute([$prov]); $pids = $findProv->fetchAll(PDO::FETCH_COLUMN);
        if (!$pids)           { $rep['errors'][] = "Row $ln: provider \"$prov\" not found."; continue; }
        if (count($pids) > 1) { $rep['errors'][] = "Row $ln: provider \"$prov\" is ambiguous (rename one)."; continue; }
        $fid = (int)$fids[0]; $pid = (int)$pids[0];
        $isLinked->execute([$fid, $pid]);
        if ($isLinked->fetchColumn()) { $rep['skipped']++; continue; }
        try { coding_link_provider($pdo, $fid, $pid, $actorId); $rep['linked']++; }
        catch (Throwable $e) { $rep['errors'][] = "Row $ln: " . $e->getMessage(); }   // e.g. a scribe is rejected (global, not facility-linked)
    }
    return $rep;
}

/** Render a consistent "Bulk upload" card (template download + .xlsx file form + last report). $c keys:
 *  title, template_url, action_url, columns (string[]), report (array|null). */
function coding_bulk_upload_render(array $c): void
{
    $r = $c['report'] ?? null;
    ?>
    <div class="card mb-3">
      <div class="card-header"><h3 class="card-title mb-0"><i class="ti ti-file-spreadsheet me-1"></i><?= e($c['title'] ?? 'Bulk upload') ?></h3>
        <div class="card-actions"><a href="<?= e($c['template_url']) ?>" class="btn btn-sm btn-outline-secondary"><i class="ti ti-download me-1"></i>Download .xlsx template</a></div></div>
      <div class="card-body">
        <?php if ($r): $bits = [];
          foreach (['created'=>'created','linked'=>'linked','updated'=>'updated','reactivated'=>'reactivated','skipped'=>'skipped (already present)'] as $k=>$lbl) if (!empty($r[$k])) $bits[] = ((int)$r[$k]) . ' ' . $lbl; ?>
          <div class="alert alert-<?= empty($r['errors']) ? 'success' : 'warning' ?> mb-2">
            <strong>Import complete.</strong> <?= $bits ? e(implode(' · ', $bits)) : 'No changes.' ?>
            <?php if (!empty($r['errors'])): ?>
              <div class="mt-2"><strong><?= count($r['errors']) ?> row(s) skipped:</strong>
                <ul class="mb-0 small"><?php foreach (array_slice($r['errors'], 0, 60) as $e): ?><li><?= e($e) ?></li><?php endforeach;
                  if (count($r['errors']) > 60): ?><li>… and <?= count($r['errors']) - 60 ?> more.</li><?php endif; ?></ul></div>
            <?php endif; ?>
          </div>
        <?php endif; ?>
        <form method="POST" action="<?= e($c['action_url']) ?>" enctype="multipart/form-data" class="row g-2 align-items-end">
          <?= csrf_field() ?><input type="hidden" name="action" value="bulk_import">
          <div class="col-md-8"><label class="form-label small mb-1">Excel (.xlsx) — columns in order: <strong><?= e(implode(', ', $c['columns'])) ?></strong></label>
            <input type="file" name="file" accept=".xlsx" class="form-control" required></div>
          <div class="col-auto"><button class="btn btn-primary"><i class="ti ti-upload me-1"></i>Upload &amp; import</button></div>
          <div class="col-12"><div class="form-hint">First row = header. Re-uploading is safe — existing rows are updated/skipped, never duplicated. Download the template for the exact layout.</div></div>
        </form>
      </div>
    </div>
    <?php
}

/* Normalize a provider movement date: '' / null → NULL; otherwise parse to Y-m-d (reject garbage). */
function coding_norm_movement_date(?string $d): ?string
{
    $d = trim((string) $d);
    if ($d === '') return null;
    $parsed = coding_parse_date($d);
    if (!$parsed) throw new InvalidArgumentException('Movement date is not a valid date.');
    return $parsed;
}

/** Scribe CANDIDATES for one facility (CONTEXTUAL model): any active provider who is NOT a billing provider OF THIS
 *  facility may act as a scribe here — i.e. dedicated scribe-type providers PLUS billing providers whose billing home
 *  is some OTHER facility. A provider is never a scribe at the facility where they are the billing provider (they are
 *  the billing provider there). Used where the facility is fixed (the chart). Returns [id,name,npi,provider_type]. */
function coding_scribe_options(PDO $pdo, int $facilityId): array
{
    $st = $pdo->prepare("SELECT id, name, npi, provider_type FROM coding_providers
        WHERE status='active' AND id NOT IN (SELECT provider_id FROM coding_facility_providers WHERE facility_id=?)
        ORDER BY name");
    $st->execute([$facilityId]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}

/** All active providers (id,name,npi,provider_type). Used to build a facility-contextual scribe picker CLIENT-side,
 *  where the facility is chosen in the browser so the server cannot pre-filter — the JS excludes the chosen facility's
 *  billing providers to derive its scribe candidates. Server-side, the role is re-derived from facility linkage. */
function coding_all_active_providers(PDO $pdo): array
{
    return $pdo->query("SELECT id, name, npi, provider_type FROM coding_providers WHERE status='active' ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
}

/** True if the provider is a scribe. Scribes are GLOBAL and must NEVER be tied to a facility — not via the
 *  facility-provider link table NOR via the per-(facility,provider) CPT validation set. A scribe in either
 *  would masquerade as a billing provider in the chart / billing-sheet pickers and corrupt billing routing
 *  + the inventory report. Every facility-scoping write goes through this guard. */
function coding_provider_is_scribe(PDO $pdo, int $providerId): bool
{
    if ($providerId <= 0) return false;
    $st = $pdo->prepare("SELECT provider_type FROM coding_providers WHERE id=?");
    $st->execute([$providerId]);
    return $st->fetchColumn() === 'scribe';
}

/** Link an existing provider to a facility (idempotent). */
function coding_link_provider(PDO $pdo, int $facilityId, int $providerId, int $actorId): void
{
    if ($facilityId <= 0 || $providerId <= 0) throw new InvalidArgumentException('Pick a provider to link.');
    if (coding_provider_is_scribe($pdo, $providerId)) throw new InvalidArgumentException('A scribe is global and cannot be linked to a facility.');
    $pdo->prepare("INSERT IGNORE INTO coding_facility_providers (facility_id,provider_id,created_at) VALUES (?,?,NOW())")
        ->execute([$facilityId, $providerId]);
    audit_log('coding.provider.link', 'coding_facility_providers', null, null, ['facility_id'=>$facilityId,'provider_id'=>$providerId]);
}

/** Unlink a provider from a facility. Blocked if that pair still has CPT validation rows. */
function coding_unlink_provider(PDO $pdo, int $facilityId, int $providerId, int $actorId): void
{
    $cpt = $pdo->prepare("SELECT COUNT(*) FROM coding_cpt_validation WHERE facility_id=? AND provider_id=?");
    $cpt->execute([$facilityId, $providerId]);
    if ((int) $cpt->fetchColumn() > 0) throw new InvalidArgumentException('This provider still has CPT validation entries at this facility — remove or reassign those first.');
    $pdo->prepare("DELETE FROM coding_facility_providers WHERE facility_id=? AND provider_id=?")->execute([$facilityId, $providerId]);
    // Prune now-orphaned provider-level allocations (a coder aligned to exactly this facility+provider) so the
    // Allocation summary doesn't keep a dangling grant to a provider who no longer works at the facility.
    $pdo->prepare("DELETE FROM coding_user_scope WHERE facility_id=? AND provider_id=?")->execute([$facilityId, $providerId]);
    audit_log('coding.provider.unlink', 'coding_facility_providers', null, null, ['facility_id'=>$facilityId,'provider_id'=>$providerId]);
}

/**
 * Duplicate-encounter detection: OTHER already-COMPLETED charts for the SAME patient + DOS + facility — a likely
 * re-upload of an already-coded visit (e.g. the provider edited the note, so it re-arrives on a new sign date).
 * Split by provider:
 *   'same' = the same billing/upload provider → a true double (hard-block on completion; the coder should tag RFI).
 *   'diff' = a different provider seeing the patient the same day → allowed, soft warning only.
 * Patient identity = MRN when present, else name + DOB. Returns ['same'=>[rows], 'diff'=>[rows]] (each row has
 * id, provider_id, provider name, coder, record_sign_date, submitted_at). Empty when facility/DOS/identity is missing.
 */
function coding_duplicate_encounters(PDO $pdo, int $excludeId, int $facilityId, ?string $dos, string $mrn, string $name, ?string $dob, int $providerId): array
{
    $out = ['same' => [], 'diff' => []];
    if (!$facilityId || !$dos) return $out;
    $w = ['ci.id<>?', 'ci.facility_id=?', 'ci.dos=?', "ci.status='completed'"];
    $p = [$excludeId, $facilityId, $dos];
    $mrn = trim($mrn); $name = trim($name);
    if ($mrn !== '')      { $w[] = 'ci.patient_mrn=?'; $p[] = $mrn; }
    elseif ($name !== '') { $w[] = 'ci.patient_name=?'; $p[] = $name; $w[] = 'ci.patient_dob <=> ?'; $p[] = $dob ?: null; }
    else return $out;     // no identity to match on
    $st = $pdo->prepare("SELECT ci.id, ci.provider_id, ci.record_sign_date, ci.submitted_at, pr.name provider, u.name coder
        FROM coding_inventory ci
        LEFT JOIN coding_providers pr ON pr.id=ci.provider_id
        LEFT JOIN users u ON u.id=ci.coder_id
        WHERE " . implode(' AND ', $w) . " ORDER BY ci.submitted_at DESC LIMIT 20");
    $st->execute($p);
    foreach ($st->fetchAll() as $r) { if ((int) $r['provider_id'] === $providerId) $out['same'][] = $r; else $out['diff'][] = $r; }
    return $out;
}

/**
 * Clone a SOURCE client's master tree (active processes → facilities → billing-provider links) INTO a TARGET
 * client — typically a same-named client in another vertical. Master data is otherwise shared; this is the
 * Option-B helper for keeping SEPARATE per-vertical copies of the tree.
 *
 * - Idempotent: matches by NAME at each level (find-or-create); existing rows are reused, never duplicated.
 * - Providers are GLOBAL — they are RE-LINKED to the cloned facilities, never copied. Scribe links are skipped
 *   (scribes are never facility-linked).
 * - Process codes are unique per client, so a clashing code is suffixed; facility names are unique per process.
 * - $dryRun = compute what WOULD be created without writing (drives the preview).
 *
 * Returns ['proc_new','proc_existing','fac_new','fac_existing','link_new','link_existing','errors'].
 * The caller is responsible for the transaction around a non-dry run.
 */
function coding_clone_tree(PDO $pdo, int $srcClientId, int $dstClientId, int $actorId, bool $dryRun = false): array
{
    $out = ['proc_new'=>0,'proc_existing'=>0,'fac_new'=>0,'fac_existing'=>0,'link_new'=>0,'link_existing'=>0,'errors'=>[]];
    if ($srcClientId <= 0 || $dstClientId <= 0 || $srcClientId === $dstClientId) {
        $out['errors'][] = 'Source and target must be two different clients.'; return $out;
    }
    $sp = $pdo->prepare("SELECT id,name,code,status FROM processes WHERE client_id=? AND deleted_at IS NULL ORDER BY name");
    $sp->execute([$srcClientId]);
    foreach ($sp->fetchAll(PDO::FETCH_ASSOC) as $proc) {
        // process: find target by name, else create (suffix code if it collides under the target client)
        $tp = $pdo->prepare("SELECT id FROM processes WHERE client_id=? AND name=? AND deleted_at IS NULL LIMIT 1");
        $tp->execute([$dstClientId, $proc['name']]);
        $dstProcId = (int) $tp->fetchColumn();
        if ($dstProcId) { $out['proc_existing']++; }
        else {
            $out['proc_new']++;
            if (!$dryRun) {
                $base = ((string) $proc['code'] !== '') ? (string) $proc['code'] : (coding_slug($proc['name'], 46) ?: 'PROC');
                $try = $base; $k = 1;
                $chk = $pdo->prepare("SELECT 1 FROM processes WHERE client_id=? AND code=? LIMIT 1");
                $chk->execute([$dstClientId, $try]);
                while ($chk->fetchColumn()) { $try = mb_substr($base, 0, 46) . '_' . $k++; $chk->execute([$dstClientId, $try]); }
                $pdo->prepare("INSERT INTO processes (client_id,name,code,status,created_at,updated_at) VALUES (?,?,?,?,NOW(),NOW())")
                    ->execute([$dstClientId, mb_substr((string) $proc['name'], 0, 200), $try, $proc['status'] ?: 'active']);
                $dstProcId = (int) $pdo->lastInsertId();
                audit_log('coding.tree.clone.process', 'processes', $dstProcId, null, ['from_client'=>$srcClientId,'to_client'=>$dstClientId,'name'=>$proc['name']]);
            }
        }
        // facilities under this source process
        $sf = $pdo->prepare("SELECT id,name,code,status FROM coding_facilities WHERE process_id=? ORDER BY name");
        $sf->execute([(int) $proc['id']]);
        foreach ($sf->fetchAll(PDO::FETCH_ASSOC) as $fac) {
            $dstFacId = 0;
            if ($dstProcId) {
                $tf = $pdo->prepare("SELECT id FROM coding_facilities WHERE process_id=? AND name=? LIMIT 1");
                $tf->execute([$dstProcId, $fac['name']]); $dstFacId = (int) $tf->fetchColumn();
            }
            if ($dstFacId) { $out['fac_existing']++; }
            else {
                $out['fac_new']++;
                if (!$dryRun && $dstProcId) {
                    $code = ($fac['code'] !== null && trim((string) $fac['code']) !== '') ? mb_substr(trim((string) $fac['code']), 0, 60) : null;
                    $pdo->prepare("INSERT INTO coding_facilities (process_id,name,code,status,created_at,updated_at) VALUES (?,?,?,?,NOW(),NOW())")
                        ->execute([$dstProcId, mb_substr((string) $fac['name'], 0, 200), $code, $fac['status'] ?: 'active']);
                    $dstFacId = (int) $pdo->lastInsertId();
                    audit_log('coding.tree.clone.facility', 'coding_facilities', $dstFacId, null, ['from_client'=>$srcClientId,'to_client'=>$dstClientId,'name'=>$fac['name']]);
                }
            }
            // re-link the SAME global billing providers (scribes excluded)
            $lp = $pdo->prepare("SELECT fp.provider_id FROM coding_facility_providers fp
                JOIN coding_providers pr ON pr.id=fp.provider_id WHERE fp.facility_id=? AND pr.provider_type='billing'");
            $lp->execute([(int) $fac['id']]);
            foreach ($lp->fetchAll(PDO::FETCH_COLUMN) as $pvId) {
                $exists = 0;
                if ($dstFacId) {
                    $le = $pdo->prepare("SELECT 1 FROM coding_facility_providers WHERE facility_id=? AND provider_id=? LIMIT 1");
                    $le->execute([$dstFacId, (int) $pvId]); $exists = (int) $le->fetchColumn();
                }
                if ($exists) { $out['link_existing']++; }
                else {
                    $out['link_new']++;
                    if (!$dryRun && $dstFacId) $pdo->prepare("INSERT IGNORE INTO coding_facility_providers (facility_id,provider_id,created_at) VALUES (?,?,NOW())")->execute([$dstFacId, (int) $pvId]);
                }
            }
        }
    }
    if (!$dryRun) audit_log('coding.tree.clone', 'clients', $dstClientId, null, ['from'=>$srcClientId,'to'=>$dstClientId,'counts'=>$out]);
    return $out;
}

/* ============================================================================
 * PEOPLE: employee import, reporting hierarchy, bulk access grant
 * ========================================================================== */

/** Map a roster (vertical, Sw_Role) to an app role code. */
function coding_role_for_employee(string $vertical, string $swRole): ?string
{
    $v = strtolower(trim($vertical)); $r = strtolower(trim($swRole));
    $m = [
        'coding'  => ['manager'=>'coding_manager','supervisor'=>'coding_supervisor','coder'=>'coder','auditor'=>'coding_auditor','validator'=>'coding_validator','bs uploader'=>'bs_uploader','bs-uploader'=>'bs_uploader','billing sheet uploader'=>'bs_uploader'],
        'ar'      => ['manager'=>'ar_manager','supervisor'=>'ar_supervisor','account receivable'=>'account_receivable','auditor'=>'ar_auditor'],
        'billing' => ['manager'=>'billing_manager','supervisor'=>'billing_supervisor','biller'=>'biller','auditor'=>'billing_auditor'],
    ];
    return $m[$v][$r] ?? null;
}

/** All user ids that report (transitively) to $uid — supervisor's team, or manager's supervisors + their teams. */
function coding_managed_user_ids(PDO $pdo, int $uid): array
{
    try {
        // Iterative BFS over reports_to (one indexed query + PHP traversal). Deliberately NOT `WITH RECURSIVE`:
        // that is unsupported on MySQL <8.0 / MariaDB <10.2.2, where it THROWS → caught → [] → EVERY supervisor's
        // team silently empty while sysadmin (no scope) still sees all (the exact "supervisor sees nothing" symptom).
        // Portable across all versions; includes a cycle guard.
        $children = [];
        foreach ($pdo->query("SELECT id, reports_to FROM users WHERE reports_to IS NOT NULL") as $r)
            $children[(int) $r['reports_to']][] = (int) $r['id'];
        $out = []; $seen = []; $stack = $children[$uid] ?? [];
        while ($stack) {
            $id = (int) array_pop($stack);
            if (isset($seen[$id])) continue;                       // cycle / diamond guard
            $seen[$id] = true; $out[] = $id;
            foreach ($children[$id] ?? [] as $c) if (!isset($seen[$c])) $stack[] = $c;
        }
        return $out;
    } catch (Throwable $e) { return []; }
}

/** Members a supervisor's TEAM ROLL-UP should cover. Primary = the reports_to tree. FALLBACK when that is
 *  empty: coders whose DATA ACCESS this supervisor personally granted on Coder access (coding_user_scope.granted_by)
 *  — an action-based "these are the coders I manage" signal — so a supervisor who set up their coders but never had
 *  "reports to" filled in still gets a roll-up. Excludes self. Returns user-id list (empty if neither applies). */
function coding_team_member_ids(PDO $pdo, int $uid): array
{
    $ids = coding_managed_user_ids($pdo, $uid);
    if ($ids) return $ids;
    try {
        $st = $pdo->prepare(
            "SELECT DISTINCT s.user_id
               FROM coding_user_scope s
               JOIN users u ON u.id = s.user_id AND u.status='active' AND u.user_type='internal'
              WHERE s.granted_by = ? AND s.user_id <> ? AND " . coding_vertical_scope_sql('s.vertical_id')
        );
        $st->execute([$uid, $uid]);
        return array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
    } catch (Throwable $e) { return []; }
}

/** Coder/assignee user-ids a supervisor/manager may see & control (their reporting team + self).
 *  Returns null = unrestricted (sysadmin sees everyone). */
function coding_team_scope(PDO $pdo, int $uid): ?array
{
    if (user_has_role($uid, 'sysadmin')) return null;
    $ids = coding_managed_user_ids($pdo, $uid);
    $ids[] = $uid;
    return array_values(array_unique(array_map('intval', $ids)));
}

/** SQL "IN (...)" fragment for a team-scope list on a column; '' when unrestricted (sysadmin). */
function coding_team_in_sql(?array $teamIds, string $col): string
{
    if ($teamIds === null) return '';                       // sysadmin: no restriction
    if (!$teamIds) return " AND 1=0";                       // no team: see nothing extra
    return " AND $col IN (" . implode(',', array_map('intval', $teamIds)) . ")";
}

function coding_name_tokens(string $name): array
{
    $t = preg_split('/\s+/', strtolower(trim(preg_replace('/[^A-Za-z ]+/', '', $name))));
    return array_values(array_filter($t));
}

/** Best fuzzy match of a reporting-manager name against an index of [uid,tokens]; null if no confident match.
 *  Reporting names are usually the first 2 tokens of the full name (e.g. "Abrar Ahmed" → "Abrar Ahmed Lakadwala"),
 *  so we require the first name to match and EVERY reporting token to appear somewhere in the candidate. */
function coding_match_user_by_name(string $mgr, array $index, int $excludeUid): ?int
{
    $rt = coding_name_tokens($mgr); if (!$rt) return null;
    $rf = $rt[0];
    $fuzzy = function (string $a, string $b): bool {
        return $a === $b
            || (strlen($a) >= 3 && strlen($b) >= 3 && (strpos($a, $b) === 0 || strpos($b, $a) === 0))
            || levenshtein($a, $b) <= 2;
    };
    $best = null; $bestScore = -1;
    foreach ($index as $cand) {
        if ($cand['uid'] === $excludeUid || !$cand['tokens']) continue;
        $ct = $cand['tokens'];
        if (!$fuzzy($rf, $ct[0])) continue;                       // first name must match
        $allFound = true;
        foreach ($rt as $tok) { $hit = false; foreach ($ct as $c2) { if ($fuzzy($tok, $c2)) { $hit = true; break; } } if (!$hit) { $allFound = false; break; } }
        if (!$allFound) continue;                                 // every reporting token must appear in the candidate
        $sc = count($rt) * 10 + ($rf === $ct[0] ? 3 : 0) - count($ct);   // prefer exact first-name + most-specific (shortest) name
        if ($sc > $bestScore) { $bestScore = $sc; $best = $cand['uid']; }
    }
    return $best;
}

/**
 * Import/upsert employees (idempotent by employee_code) → users + single mapped role + reporting hierarchy.
 * @param array $rows each ['code','name','vertical','role','manager']
 * @return array report
 */
function coding_import_employees(PDO $pdo, array $rows, int $granterId, string $defaultPassword = 'Medmecs@2026'): array
{
    $rep = ['total'=>0,'created'=>0,'updated'=>0,'roles'=>0,'linked'=>0,'scoped'=>0,'unmatched'=>[],'skipped'=>[],'no_role'=>[]];
    $domain = '@medmecsbillingservices.com';
    $pwHash = password_hash($defaultPassword, PASSWORD_BCRYPT, ['cost'=>12]);
    $roleId = []; foreach ($pdo->query("SELECT id,code FROM roles")->fetchAll() as $r) $roleId[$r['code']] = (int)$r['id'];

    $findByCode  = $pdo->prepare("SELECT id, email FROM users WHERE employee_code=?");
    $findByEmail = $pdo->prepare("SELECT id FROM users WHERE email=?");
    $insUser = $pdo->prepare("INSERT INTO users (name,email,employee_code,vertical,password,user_type,status,must_change_password,created_at,updated_at)
                              VALUES (?,?,?,?,?, 'internal','active',1,NOW(),NOW())");
    $updUser = $pdo->prepare("UPDATE users SET name=?, vertical=?, status='active', updated_at=NOW() WHERE id=?");
    $delRoles= $pdo->prepare("DELETE FROM user_roles WHERE user_id=?");
    $insRole = $pdo->prepare("INSERT IGNORE INTO user_roles (user_id,role_id) VALUES (?,?)");

    $byCode = []; $index = []; $codingUids = []; $rowsOut = [];   // $rowsOut = per-employee result detail for the UI/history
    foreach ($rows as $row) {
        $code = strtoupper(trim((string)($row['code'] ?? ''))); $name = trim((string)($row['name'] ?? ''));
        $vert = trim((string)($row['vertical'] ?? '')); $sw = trim((string)($row['role'] ?? ''));
        if ($code === '' || $name === '') { $rep['skipped'][] = $name ?: $code; continue; }
        $rep['total']++;
        $appRole = coding_role_for_employee($vert, $sw);
        $findByCode->execute([$code]); $ex = $findByCode->fetch();
        if ($ex) { $exId = (int) $ex['id']; $updUser->execute([$name, $vert, $exId]); $uid = $exId; $rep['updated']++; $rowStatus = 'updated'; $email = (string) $ex['email']; }
        else {
            $base = coding_emailify_name($name); $email = $base . $domain; $n = 1;
            while (true) { $findByEmail->execute([$email]); if (!$findByEmail->fetchColumn()) break; $email = $base . '.' . ($n++) . $domain; }
            $insUser->execute([$name, $email, $code, $vert, $pwHash]); $uid = (int) $pdo->lastInsertId(); $rep['created']++; $rowStatus = 'created';
        }
        if ($appRole && isset($roleId[$appRole])) { $delRoles->execute([$uid]); $insRole->execute([$uid, $roleId[$appRole]]); $rep['roles']++; }
        else $rep['no_role'][] = "$name ($vert/$sw)";
        $byCode[$code] = $uid;
        $index[] = ['uid'=>$uid, 'tokens'=>coding_name_tokens($name)];
        if (strtolower($vert) === 'coding') $codingUids[] = $uid;   // coding staff get data scope below
        $rowsOut[$code] = ['code'=>$code, 'name'=>$name, 'email'=>$email, 'vertical'=>$vert,
            'role'=>($appRole ?: ''), 'sw'=>$sw, 'status'=>$rowStatus, 'manager'=>'', 'manager_linked'=>null];
    }
    $updMgr = $pdo->prepare("UPDATE users SET reports_to=? WHERE id=?");
    foreach ($rows as $row) {
        $code = strtoupper(trim((string)($row['code'] ?? ''))); $mgr = trim((string)($row['manager'] ?? ''));
        if (!isset($byCode[$code])) continue; $uid = $byCode[$code];
        $mid = $mgr === '' ? null : coding_match_user_by_name($mgr, $index, $uid);
        $updMgr->execute([$mid, $uid]);
        if ($mgr !== '') { if ($mid) $rep['linked']++; else $rep['unmatched'][$mgr] = ($rep['unmatched'][$mgr] ?? 0) + 1; }
        if (isset($rowsOut[$code])) { $rowsOut[$code]['manager'] = $mgr; $rowsOut[$code]['manager_linked'] = ($mgr === '' ? null : (bool) $mid); }
    }
    $rep['rows'] = array_values($rowsOut);
    $rep['default_password'] = $defaultPassword;
    // Imported coding staff start with NO data scope (2026-06-18 access-control rebuild). A manager aligns
    // each coder to a specific client » process » facility on the Coder access page (admin/coding-access.php);
    // a supervisor then deepens within an existing client. The old blanket "all Coding clients" default is gone.
    $rep['scoped'] = 0;
    audit_log('user.import', 'users', null, null, ['created'=>$rep['created'],'updated'=>$rep['updated'],'linked'=>$rep['linked'],'scoped'=>$rep['scoped']]);
    return $rep;
}

function coding_emailify_name(string $name): string
{
    $t = coding_name_tokens($name); if (!$t) return 'user';
    return count($t) > 1 ? ($t[0] . '.' . end($t)) : $t[0];
}

/** Bulk data-scope grant: users[] × selected (clients/processes/facilities/providers) → coding_user_scope rows. Returns rows inserted. */
function coding_bulk_grant_scope(PDO $pdo, array $userIds, array $sel, int $granterId): int
{
    $userIds = array_values(array_unique(array_filter(array_map('intval', $userIds))));
    if (!$userIds) return 0;
    $clean = fn($a) => array_values(array_unique(array_filter(array_map('intval', (array) $a))));
    $clients = $clean($sel['clients'] ?? []); $procs = $clean($sel['processes'] ?? []);
    $facs = $clean($sel['facilities'] ?? []); $provs = $clean($sel['providers'] ?? []);
    $inq = fn($a) => implode(',', array_fill(0, count($a), '?'));
    $tuples = [];
    foreach ($clients as $c) $tuples[] = [$c, null, null, null];
    if ($procs) { $st = $pdo->prepare("SELECT id,client_id FROM processes WHERE id IN (".$inq($procs).")"); $st->execute($procs);
        foreach ($st->fetchAll() as $r) $tuples[] = [(int)$r['client_id'], (int)$r['id'], null, null]; }
    if ($facs) { $st = $pdo->prepare("SELECT f.id,f.process_id,p.client_id FROM coding_facilities f JOIN processes p ON p.id=f.process_id WHERE f.id IN (".$inq($facs).")"); $st->execute($facs);
        foreach ($st->fetchAll() as $r) $tuples[] = [(int)$r['client_id'], (int)$r['process_id'], (int)$r['id'], null]; }
    if ($provs) {
        $params = $provs; $facCond = '';
        if ($facs) { $facCond = " AND fp.facility_id IN (".$inq($facs).")"; $params = array_merge($params, $facs); }
        $st = $pdo->prepare("SELECT fp.provider_id,fp.facility_id,f.process_id,p.client_id
            FROM coding_facility_providers fp JOIN coding_facilities f ON f.id=fp.facility_id JOIN processes p ON p.id=f.process_id
            WHERE fp.provider_id IN (".$inq($provs).")".$facCond);
        $st->execute($params);
        foreach ($st->fetchAll() as $r) $tuples[] = [(int)$r['client_id'], (int)$r['process_id'], (int)$r['facility_id'], (int)$r['provider_id']];
    }
    if (!$tuples) return 0;
    $seen = []; $uniq = [];
    foreach ($tuples as $t) { $k = implode('|', array_map(fn($x) => $x === null ? '_' : $x, $t)); if (!isset($seen[$k])) { $seen[$k] = 1; $uniq[] = $t; } }
    $cv     = app_vertical_id_by_code('CODING');   // active CODING id for new rows (NULL stays NULL = legacy coding, never 0)
    $exists = $pdo->prepare("SELECT 1 FROM coding_user_scope WHERE user_id=? AND " . coding_vertical_scope_sql('vertical_id') . " AND client_id<=>? AND process_id<=>? AND facility_id<=>? AND provider_id<=>? LIMIT 1");
    $ins    = $pdo->prepare("INSERT INTO coding_user_scope (user_id,vertical_id,client_id,process_id,facility_id,provider_id,granted_by,created_at) VALUES (?,?,?,?,?,?,?,NOW())");
    $n = 0; $pdo->beginTransaction();
    try {
        foreach ($userIds as $u) foreach ($uniq as $t) {
            $exists->execute([$u, $t[0], $t[1], $t[2], $t[3]]); if ($exists->fetchColumn()) continue;
            $ins->execute([$u, $cv, $t[0], $t[1], $t[2], $t[3], $granterId]); $n++;
        }
        $pdo->commit();
    } catch (Throwable $e) { if ($pdo->inTransaction()) $pdo->rollBack(); throw $e; }
    if ($n) audit_log('coding.scope.bulk_grant', 'coding_user_scope', null, null, ['users'=>count($userIds), 'tuples'=>count($uniq), 'rows'=>$n]);
    return $n;
}

/** Coding-vertical client ids — coders are scoped to the Coding vertical's clients only (never AR / Billing). */
function coding_coding_client_ids(PDO $pdo): array
{
    return array_map('intval', $pdo->query(
        "SELECT c.id FROM clients c JOIN verticals v ON v.id=c.vertical_id
         WHERE c.deleted_at IS NULL AND v.deleted_at IS NULL AND " . coding_workflow_vertical_in_sql('c.vertical_id') . "
         ORDER BY c.id")->fetchAll(PDO::FETCH_COLUMN));
}

/** @deprecated 2026-06-18 — blanket "whole-client to every Coding client" default removed; coders are aligned
 *  to a specific client » process » facility on admin/coding-access.php. No live callers. */
function coding_grant_coder_scope(PDO $pdo, array $userIds, int $granterId): int
{
    $userIds = array_values(array_filter(array_map('intval', $userIds)));
    $clients = coding_coding_client_ids($pdo);
    if (!$userIds || !$clients) return 0;
    return coding_bulk_grant_scope($pdo, $userIds, ['clients' => $clients], $granterId);
}

/**
 * Merge duplicate clients INTO a survivor. Moves every duplicate's processes (and EVERY other client_id reference,
 * discovered dynamically so none is missed) onto $survivorId, dedupes the moved alignment rows, then soft-archives
 * the duplicates. Works WITHIN or ACROSS verticals — the caller decides which ids are "duplicates" — because master
 * data is shared, so the survivor's processes/facilities/providers become visible to every vertical. Work keyed by
 * process/chart is preserved (process ids are stable; only their parent client pointer moves). Runs in one
 * transaction (joins an outer one if present). Returns counts. The CALLER validates permission + audits.
 *
 * @return array{merged:int, processes:int, scope:int, tables:array<string,int>, dups:int[]}
 */
function app_merge_clients(PDO $pdo, int $survivorId, array $dupIds): array
{
    $survivorId = (int) $survivorId;
    $dupIds = array_values(array_unique(array_filter(array_map('intval', $dupIds), fn($x) => $x > 0 && $x !== $survivorId)));
    $out = ['merged' => 0, 'processes' => 0, 'scope' => 0, 'tables' => [], 'dups' => []];
    if (!$survivorId || !$dupIds) return $out;

    $sv = $pdo->prepare("SELECT id FROM clients WHERE id=? AND deleted_at IS NULL"); $sv->execute([$survivorId]);
    if (!$sv->fetchColumn()) throw new RuntimeException('Survivor client not found or archived.');
    $place = implode(',', array_fill(0, count($dupIds), '?'));
    $liveQ = $pdo->prepare("SELECT id FROM clients WHERE id IN ($place) AND deleted_at IS NULL");
    $liveQ->execute($dupIds);
    $dupIds = array_map('intval', $liveQ->fetchAll(PDO::FETCH_COLUMN));
    if (!$dupIds) return $out;
    $in = implode(',', $dupIds);
    $out['dups'] = $dupIds; $out['merged'] = count($dupIds);

    $own = !$pdo->inTransaction();
    if ($own) $pdo->beginTransaction();
    try {
        // processes: UNIQUE(client_id, code) — move each, suffixing its code on collision with the survivor.
        $mp  = $pdo->query("SELECT id, code FROM processes WHERE client_id IN ($in)");
        $pex = $pdo->prepare("SELECT 1 FROM processes WHERE client_id=? AND code=? AND id<>? LIMIT 1");
        $pmv = $pdo->prepare("UPDATE processes SET client_id=?, code=? WHERE id=?");
        foreach ($mp->fetchAll() as $pr) {
            $code = (string) $pr['code']; $try = $code; $k = 1;
            $pex->execute([$survivorId, $try, (int) $pr['id']]);
            while ($pex->fetch()) { $try = mb_substr($code, 0, 40) . '-' . $k++; $pex->execute([$survivorId, $try, (int) $pr['id']]); }
            $pmv->execute([$survivorId, $try, (int) $pr['id']]);
            $out['processes']++;
        }
        // coding_user_scope has no UNIQUE key — repoint, then drop exact-duplicate alignment rows (keep lowest id)
        // so an AR + a Coding alignment to the same client stay distinct (different vertical_id) but identical rows merge.
        $out['scope'] = (int) $pdo->exec("UPDATE coding_user_scope SET client_id=$survivorId WHERE client_id IN ($in)");
        $pdo->exec("DELETE s1 FROM coding_user_scope s1 JOIN coding_user_scope s2
                      ON s1.user_id=s2.user_id AND s1.client_id=s2.client_id
                     AND s1.vertical_id<=>s2.vertical_id AND s1.process_id<=>s2.process_id
                     AND s1.facility_id<=>s2.facility_id AND s1.provider_id<=>s2.provider_id AND s1.id>s2.id
                    WHERE s1.client_id=$survivorId");
        // client_branding is 1:1 (unique login_slug) — survivor keeps its own; drop the duplicates'.
        $pdo->exec("DELETE FROM client_branding WHERE client_id IN ($in)");
        // Every other table that references a client by id → repoint to the survivor. Discovered dynamically so a
        // future table is never silently missed (this also covers coding_inventory, coding_billing_sheets,
        // coding_upload_batches, users). A UNIQUE-constraint clash here aborts the whole merge (no silent loss).
        $skip = ['clients', 'processes', 'coding_user_scope', 'client_branding'];
        $tbls = $pdo->query("SELECT col.TABLE_NAME FROM information_schema.COLUMNS col
            JOIN information_schema.TABLES tbl ON tbl.TABLE_SCHEMA=col.TABLE_SCHEMA AND tbl.TABLE_NAME=col.TABLE_NAME
            WHERE col.TABLE_SCHEMA=DATABASE() AND col.COLUMN_NAME='client_id'
              AND tbl.TABLE_TYPE='BASE TABLE'")->fetchAll(PDO::FETCH_COLUMN);   // base tables only (never a reporting VIEW)
        foreach ($tbls as $t) {
            if (in_array($t, $skip, true)) continue;
            $n = (int) $pdo->exec("UPDATE `$t` SET client_id=$survivorId WHERE client_id IN ($in)");
            if ($n) $out['tables'][$t] = $n;
        }
        $pdo->exec("UPDATE clients SET deleted_at=NOW(), status='inactive' WHERE id IN ($in)");
        if ($own) $pdo->commit();
    } catch (Throwable $e) {
        if ($own && $pdo->inTransaction()) $pdo->rollBack();
        throw $e;
    }
    return $out;
}

/** @deprecated 2026-06-18 — see coding_grant_coder_scope(). No live callers (seed migration is now a no-op). */
function coding_grant_all_coder_scope(PDO $pdo, int $granterId): int
{
    $users = array_map('intval', $pdo->query("SELECT DISTINCT u.id FROM users u
        JOIN user_roles ur ON ur.user_id=u.id JOIN roles r ON r.id=ur.role_id
        WHERE u.status='active' AND r.code IN ('coder','coding_auditor','coding_validator','coding_supervisor','coding_manager')")->fetchAll(PDO::FETCH_COLUMN));
    return coding_grant_coder_scope($pdo, $users, $granterId);
}
