<?php
/**
 * Zero-touch auto-provisioning.
 *
 * Runs the schema/seed changes for a deploy AUTOMATICALLY on the first
 * authenticated page load after the new code is uploaded — so deploying is
 * literally just "extract the ZIP", with no manual SQL or CLI commands.
 *
 * Each step is idempotent and recorded in `schema_migrations`, so it runs
 * exactly once and never repeats. A failed step is simply left unrecorded and
 * retried on the next load; nothing here is allowed to break the page.
 */

function app_col_exists(PDO $pdo, string $table, string $col): bool
{
    $st = $pdo->prepare("SELECT 1 FROM information_schema.columns
                          WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ? LIMIT 1");
    $st->execute([$table, $col]);
    return (bool) $st->fetchColumn();
}

function app_index_exists(PDO $pdo, string $table, string $index): bool
{
    $st = $pdo->prepare("SELECT 1 FROM information_schema.statistics
                          WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ? LIMIT 1");
    $st->execute([$table, $index]);
    return (bool) $st->fetchColumn();
}

/** Run an idempotent .sql seed file (strips any USE; the DB is already selected). */
function app_exec_sql_file(PDO $pdo, string $path): void
{
    if (!is_file($path)) return;
    $sql = preg_replace('/^\s*USE\s+[^;]+;/mi', '', (string) file_get_contents($path));
    if (trim($sql) !== '') $pdo->exec($sql);
}

/** Add the covering indexes for the drill-downs / scorecards (skip any present). */
function app_create_drill_indexes(PDO $pdo): void
{
    $defs = [
        ['collection_raw', 'idx_drill_carrier',  '`report_id`,`carrier1_name`,`dos_year`,`dos_month`,`visit_id`,`primary_ins_paid`,`outstanding`,`charge_amount`,`amount_allowed`,`days_to_first_payment`'],
        ['collection_raw', 'idx_drill_cpt',      '`report_id`,`charge_code`,`dos_year`,`dos_month`,`units`,`charge_amount`,`expected_payment`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`'],
        ['collection_raw', 'idx_drill_provider', '`report_id`,`provider_name`,`dos_year`,`dos_month`,`visit_id`,`charge_amount`,`expected_payment`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`,`outstanding`,`days_to_first_payment`'],
        ['collection_raw', 'idx_drill_aging',    '`report_id`,`dos`,`dos_year`,`dos_month`,`visit_id`,`outstanding`,`charge_amount`,`amount_allowed`'],
        ['collection_raw', 'idx_drill_summary',  '`report_id`,`dos_year`,`dos_month`,`facility`,`visit_id`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`,`patient_payment_amount`,`charge_amount`,`expected_payment`,`outstanding`'],
        ['facility_mappings', 'idx_fac_map_report_facility', '`report_id`,`facility`'],
        ['facility_mappings', 'idx_fac_map_report_id',       '`report_id`,`id`'],
    ];
    foreach ($defs as [$tbl, $name, $cols]) {
        if (app_index_exists($pdo, $tbl, $name)) continue;
        try {
            $pdo->exec("ALTER TABLE `$tbl` ADD INDEX `$name` ($cols), ALGORITHM=INPLACE, LOCK=NONE");
        } catch (Throwable $e) {
            // Older engines may reject the online options — fall back to a plain add.
            $pdo->exec("ALTER TABLE `$tbl` ADD INDEX `$name` ($cols)");
        }
    }
}

/** Index the STAGING table so the per-upload derivation JOINs don't fall back to full
 *  scans on large files: facility is matched by visit_id and expected-payment by
 *  charge_code, so (report_id,visit_id) and (report_id,charge_code) make both fast. */
function app_create_stage_indexes(PDO $pdo): void
{
    $defs = [
        ['collection_raw_stage', 'idx_stage_report_visit', '`report_id`,`visit_id`'],
        ['collection_raw_stage', 'idx_stage_report_cpt',   '`report_id`,`charge_code`'],
    ];
    foreach ($defs as [$tbl, $name, $cols]) {
        if (app_index_exists($pdo, $tbl, $name)) continue;
        try {
            $pdo->exec("ALTER TABLE `$tbl` ADD INDEX `$name` ($cols), ALGORITHM=INPLACE, LOCK=NONE");
        } catch (Throwable $e) {
            try { $pdo->exec("ALTER TABLE `$tbl` ADD INDEX `$name` ($cols)"); }
            catch (Throwable $e2) { /* stage table not present yet — ignore */ }
        }
    }
}

/** GROUP-BY covering indexes for the dashboard rebuild + the filtered live views. The facility /
 *  facility+provider / per-visit aggregates in computeAndCacheStats (and the same queries run live
 *  when the dashboard is FILTERED) have no index whose leading columns match their GROUP BY, so MySQL
 *  materialises the whole table into an on-disk temp table — which overflows a small MySQL tmpdir
 *  (e.g. a 4 GB tmpfs) and fails with "No space left on device". A leading (report_id,facility,…) index
 *  lets those group-bys stream in index order instead, so the temp shrinks to per-group state.
 *  idx_grp_visit additionally powers the per-visit rollup. Built ONLINE and idempotent — a build that
 *  can't fit temp space right now just defers (the runner records nothing on throw and retries). */
function app_create_analytics_group_indexes(PDO $pdo): void
{
    $defs = [
        ['collection_raw', 'idx_grp_facility', '`report_id`,`facility`,`provider_name`,`dos_year`,`dos_month`'],
        ['collection_raw', 'idx_grp_visit',    '`report_id`,`visit_id`'],
    ];
    foreach ($defs as [$tbl, $name, $cols]) {
        if (app_index_exists($pdo, $tbl, $name)) continue;
        try {
            $pdo->exec("ALTER TABLE `$tbl` ADD INDEX `$name` ($cols), ALGORITHM=INPLACE, LOCK=NONE");
        } catch (Throwable $e) {
            $pdo->exec("ALTER TABLE `$tbl` ADD INDEX `$name` ($cols)");   // older engines: plain add (throws → deferred if temp is full)
        }
    }
}

/** Remove the per-role demo logins + unused internal roles, leaving a clean minimal set:
 *  internal = SysAdmin / Manager / Supervisor; client portal roles are left untouched. */
function app_cleanup_demo_and_roles(PDO $pdo): void
{
    // 1) delete the demo <role>@medmecs.local logins (real accounts are never matched)
    $demo = ['developer@medmecs.local','vertical_manager@medmecs.local','client_manager@medmecs.local',
             'process_lead@medmecs.local','analyst@medmecs.local','auditor@medmecs.local',
             'report_controller@medmecs.local','manager@medmecs.local','supervisor@medmecs.local','user@medmecs.local'];
    $inU = implode(',', array_fill(0, count($demo), '?'));
    $pdo->prepare("DELETE ur FROM user_roles ur JOIN users u ON u.id = ur.user_id WHERE u.email IN ($inU)")->execute($demo);
    $pdo->prepare("DELETE FROM users WHERE email IN ($inU) AND user_type='internal'")->execute($demo);

    // 2) prune unused internal roles (keep sysadmin, manager, supervisor; client roles untouched)
    $drop = ['developer','vertical_manager','client_manager','process_lead','analyst','auditor','report_controller','user'];
    $inR = implode(',', array_fill(0, count($drop), '?'));
    $pdo->prepare("DELETE rp FROM role_permissions rp JOIN roles r ON r.id = rp.role_id WHERE r.code IN ($inR)")->execute($drop);
    $pdo->prepare("DELETE ur FROM user_roles ur JOIN roles r ON r.id = ur.role_id WHERE r.code IN ($inR)")->execute($drop);
    $pdo->prepare("DELETE FROM roles WHERE code IN ($inR)")->execute($drop);
}

/**
 * Apply any pending one-time migrations. Safe to call on every request — it is a
 * no-op once everything is recorded. Never throws.
 */
function app_auto_provision(PDO $pdo): void
{
    static $ranThisRequest = false;
    if ($ranThisRequest) return;
    $ranThisRequest = true;

    try {
        $pdo->exec("CREATE TABLE IF NOT EXISTS `schema_migrations` (
            `name` VARCHAR(190) NOT NULL,
            `applied_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`name`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $applied = $pdo->query("SELECT name FROM schema_migrations")->fetchAll(PDO::FETCH_COLUMN) ?: [];
    } catch (Throwable $e) {
        return;   // can't create/read the tracking table → leave the app untouched
    }

    $sqlDir = __DIR__ . '/../sql';
    $migrations = [
        // entry_date columns for the "recently billed" / Compare-by-entry features
        'collection_entry_date_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'collection_raw', 'entry_date'))      $pdo->exec("ALTER TABLE `collection_raw` ADD COLUMN `entry_date` DATE NULL");
            if (!app_col_exists($pdo, 'facility_mappings', 'entry_date'))   $pdo->exec("ALTER TABLE `facility_mappings` ADD COLUMN `entry_date` DATE NULL");
        },
        // Deductible feature: a per-report list of "deductible" Visit Numbers (mirrors facility_mappings),
        // baked onto collection_raw as a boolean `deductible` flag. The flag column is added at the END of
        // the table (no AFTER clause) so MySQL 8.0.12+/MariaDB 10.3+ use INSTANT DDL — no table rebuild,
        // so it cannot itself spill the temp-limited prod box. Charge is read from collection_raw (the
        // upload carries only Visit Numbers). See [[collection-tmpdir-4gb-constraint]].
        'deductible_mapping_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'collection_raw', 'visit_id')) return;   // collection module not installed → nothing to attach to
            $pdo->exec("CREATE TABLE IF NOT EXISTS `deductible_mappings` (
                `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `report_id` BIGINT UNSIGNED NOT NULL,
                `visit_id` VARCHAR(64) NOT NULL,
                `upload_id` BIGINT UNSIGNED NULL,
                `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `idx_ded_map_visit` (`visit_id`),
                KEY `idx_ded_map_report_visit` (`report_id`,`visit_id`),
                KEY `idx_ded_map_upload` (`upload_id`),
                KEY `idx_ded_map_report_id` (`report_id`,`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            $pdo->exec("CREATE TABLE IF NOT EXISTS `deductible_mappings_stage` LIKE `deductible_mappings`");
            // Add the flag to BOTH collection_raw AND its stage table. NEITHER step is wrapped in a
            // swallowing catch: the collection swap is `INSERT INTO collection_raw SELECT * FROM
            // collection_raw_stage`, so the two tables MUST carry an identical column set. If the live
            // ADD succeeds but the stage ADD fails transiently, we WANT the whole closure to throw so the
            // migration stays UNRECORDED and self-heals on the next load (the runner retries unrecorded
            // migrations); swallowing it would mark the migration applied and permanently desync the two
            // tables, which then breaks every Collection ingest. The app_col_exists() guards keep each
            // step idempotent, so a retry re-adds only what is still missing. Both ADDs land at the END of
            // the table (no AFTER) → INSTANT DDL, no rebuild → cannot itself spill the temp-limited box.
            if (!app_col_exists($pdo, 'collection_raw', 'deductible'))
                $pdo->exec("ALTER TABLE `collection_raw` ADD COLUMN `deductible` TINYINT(1) NOT NULL DEFAULT 0");
            if (app_col_exists($pdo, 'collection_raw_stage', 'visit_id') && !app_col_exists($pdo, 'collection_raw_stage', 'deductible'))
                $pdo->exec("ALTER TABLE `collection_raw_stage` ADD COLUMN `deductible` TINYINT(1) NOT NULL DEFAULT 0");
            // Widen the upload-kind ENUM so 'deductible_mapping' uploads are accepted (small table → trivial
            // rebuild). Also unrecorded-on-failure so it retries rather than silently rejecting uploads.
            $col = $pdo->query("SHOW COLUMNS FROM collection_uploads LIKE 'kind'")->fetch();
            if ($col && stripos((string)($col['Type'] ?? ''), 'deductible_mapping') === false)
                $pdo->exec("ALTER TABLE `collection_uploads`
                            MODIFY `kind` ENUM('collection','facility_mapping','fee_schedule','deductible_mapping') NOT NULL");
        },
        // RBAC: extra named roles, then the lock-down, then one demo login per role
        'roles_additional_v1' => fn(PDO $pdo) => app_exec_sql_file($pdo, "$sqlDir/seed-additional-roles.sql"),
        'role_lockdown_v1'    => fn(PDO $pdo) => app_exec_sql_file($pdo, "$sqlDir/seed-role-lockdown.sql"),
        // Performance covering indexes (built ONLINE so the tables stay available)
        'drill_indexes_v1'    => fn(PDO $pdo) => app_create_drill_indexes($pdo),
        // Indexes on the staging table → faster facility / expected-payment derivation
        'stage_indexes_v1'    => fn(PDO $pdo) => app_create_stage_indexes($pdo),
        // GROUP-BY covering indexes so the facility / per-visit dashboard aggregates stream in index
        // order instead of materialising the whole table into a temp (which overflows a small tmpdir).
        'analytics_group_indexes_v1' => fn(PDO $pdo) => app_create_analytics_group_indexes($pdo),
        // One-time cleanup of the accumulated per-upload dashboard snapshots: keep only the newest 3 per report
        // (matches the ongoing prune in computeAndCacheStats). Old snapshots are never read once superseded, so
        // this just reclaims space. Portable rank via a correlated COUNT (no window functions); the self-referencing
        // DELETE is wrapped in a derived table so MySQL allows it. Idempotent — reapplying keeps the same newest 3.
        'collection_stats_prune_v1' => function (PDO $pdo) {
            $pdo->exec("DELETE cs FROM collection_stats cs
                WHERE cs.upload_id NOT IN (
                    SELECT upload_id FROM (
                        SELECT cs2.upload_id
                        FROM collection_stats cs2
                        JOIN collection_uploads cu2 ON cu2.id = cs2.upload_id
                        WHERE (
                            SELECT COUNT(*) FROM collection_stats cs3
                            JOIN collection_uploads cu3 ON cu3.id = cs3.upload_id
                            WHERE cu3.report_id = cu2.report_id AND cs3.upload_id >= cs2.upload_id
                        ) <= 3
                    ) k
                )");
        },
        // Trim to the roles actually used (SysAdmin / Manager / Supervisor + client portal),
        // and remove the per-role demo logins that were seeded for testing.
        'cleanup_demo_roles_v1' => fn(PDO $pdo) => app_cleanup_demo_and_roles($pdo),
        // Supervisor + Manager may REPLACE/upload data into EXISTING reports (creating new
        // reports stays SysAdmin-only). Scoped to the reports they've been granted access to.
        'role_data_upload_v1' => function (PDO $pdo) {
            $pdo->exec("INSERT IGNORE INTO role_permissions (role_id, permission_id)
                        SELECT r.id, p.id FROM roles r JOIN permissions p
                          ON p.code IN ('report.upload','report.replace_all')
                        WHERE r.code IN ('supervisor','manager')");
        },
        // The Manager role may CREATE & MANAGE internal users (add staff, reset password, etc.).
        // The role lock-down (role_lockdown_v1) wiped every non-sysadmin role's perms, so the Manager
        // lost even user.internal.view — this re-grants both view + manage. Runs AFTER lockdown (later in
        // this array), and is idempotent (INSERT IGNORE), so existing prod gets it on the next load and a
        // fresh install gets it right after lockdown. SysAdmin can still Grant/Deny it per-user on the
        // Permissions page (a SysAdmin-role target is protected from non-sysadmin management in users.php).
        'role_internal_user_manage_v1' => function (PDO $pdo) {
            $pdo->exec("INSERT IGNORE INTO role_permissions (role_id, permission_id)
                        SELECT r.id, p.id FROM roles r JOIN permissions p
                          ON p.code IN ('user.internal.view','user.internal.manage')
                        WHERE r.code = 'manager'");
        },
        // Ensure the client-portal "Client Viewer" role exists with its default feature set (view + download the
        // dashboards/reports the sysadmin allocates). Client logins are created in the normal Users screen and
        // mapped to this role — it is their default feature. Idempotent so old installs gain it on next load.
        'client_viewer_role_ensure_v1' => function (PDO $pdo) {
            $pdo->exec("INSERT IGNORE INTO roles (code,name,description,is_system,scope,created_at,updated_at)
                        VALUES ('client_viewer','Client Viewer','View dashboards / reports for assigned client',1,'client',NOW(),NOW())");
            $pdo->exec("INSERT IGNORE INTO role_permissions (role_id, permission_id)
                        SELECT r.id, p.id FROM roles r JOIN permissions p
                          ON p.code IN ('report.view','report.download')
                        WHERE r.code = 'client_viewer'");
        },
        // Verticals get a WORKFLOW MODULE type so additional fully-independent verticals can run the Coding chart
        // workflow (module='coding'). Backfilled from the known verticals; new ones default to none until set.
        'vertical_module_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'verticals', 'module')) {
                $pdo->exec("ALTER TABLE `verticals` ADD COLUMN `module` VARCHAR(20) NULL");
                $pdo->exec("UPDATE verticals SET module='coding'  WHERE module IS NULL AND (code LIKE 'CODING%'  OR name LIKE 'Coding%')");
                $pdo->exec("UPDATE verticals SET module='ar'      WHERE module IS NULL AND (code LIKE 'AR%'      OR name LIKE 'AR%' OR name LIKE 'Account%')");
                $pdo->exec("UPDATE verticals SET module='billing' WHERE module IS NULL AND (code LIKE 'BILLING%' OR name LIKE 'Billing%')");
            }
        },
        // CPT → Specialty map (AR collection): a specialty groups a set of CPT/charge codes so the collection
        // dashboard can be filtered by specialty, and supervisors maintain it on admin/ar-specialty.php. Seeded
        // idempotently (INSERT IGNORE keeps any later edits) from the client's "CPT and Specialty" workbook.
        'ar_cpt_specialty_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `ar_cpt_specialty` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `cpt_code` VARCHAR(20) NOT NULL,
                `specialty` VARCHAR(100) NOT NULL,
                `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY `uq_cpt` (`cpt_code`),
                KEY `idx_spec` (`specialty`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            $seed = [
                ['99491','CCM'],['G0506','CCM'],['99437','CCM'],['99445','CCM'],['99490','CCM'],['99439','CCM'],['99470','CCM'],
                ['99487','CCM'],['99495','CCM'],['99492','COCM'],['99493','COCM'],['99494','COCM'],['99483','Cognitive'],['93228','Holter Monitoring'],
                ['93229','Holter Monitoring'],['93224','Holter Monitoring'],['93227','Holter Monitoring'],['G0179','Home Health'],
                ['G0180','Home Health'],['99232','In Patient Hospital'],['99233','In Patient Hospital'],['99223','In Patient Hospital'],
                ['99238','In Patient Hospital'],['99239','In Patient Hospital'],['99222','In Patient Hospital'],['99234','In Patient Hospital'],
                ['99235','In Patient Hospital'],['99231','In Patient Hospital'],['99236','In Patient Hospital'],['20553','Injection'],
                ['20552','Injection'],['20610','Injection'],['20605','Injection'],['J2003','Injection'],['J3301','Injection'],['99214','Office Visit'],
                ['99213','Office Visit'],['G2211','Office Visit'],['99203','Office Visit'],['99212','Office Visit'],['99204','Office Visit'],
                ['99215','Office Visit'],['99202','Office Visit'],['99205','Office Visit'],['90791','Primary Care ALF'],['90792','Primary Care ALF'],
                ['90832','Primary Care ALF'],['G8756','Primary Care ALF'],['G8754','Primary Care ALF'],['G8752','Primary Care ALF'],
                ['G8753','Primary Care ALF'],['M1373','Primary Care ALF'],['99342','Primary Care ALF'],['M1372','Primary Care ALF'],
                ['99343','Primary Care ALF'],['99344','Primary Care ALF'],['M1371','Primary Care ALF'],['G8755','Primary Care ALF'],
                ['M1212','Primary Care ALF'],['G9231','Primary Care ALF'],['99347','Primary Care ALF'],['99348','Primary Care ALF'],
                ['99349','Primary Care ALF'],['99350','Primary Care ALF'],['99394','Primary Care ALF'],['4011F','Primary Care ALF'],
                ['99497','Primary Care ALF'],['3078F','Primary Care ALF'],['99498','Primary Care ALF'],['99308','Primary Care Nursing Home'],
                ['99309','Primary Care Nursing Home'],['99306','Primary Care Nursing Home'],['99307','Primary Care Nursing Home'],
                ['99305','Primary Care Nursing Home'],['99315','Primary Care Nursing Home'],['99316','Primary Care Nursing Home'],
                ['99304','Primary Care Nursing Home'],['99310','Primary Care Nursing Home'],['99454','RPM'],['99457','RPM'],['99458','RPM'],
                ['99453','RPM'],['98001','Tele Health'],['98002','Tele Health'],['98005','Tele Health'],['98006','Tele Health'],
                ['98010','Tele Health'],['98013','Tele Health'],['98014','Tele Health'],['92507','Therapy Services'],['92523','Therapy Services'],
                ['92524','Therapy Services'],['92526','Therapy Services'],['92610','Therapy Services'],['96372','Therapy Services'],
                ['97010','Therapy Services'],['97110','Therapy Services'],['97112','Therapy Services'],['97116','Therapy Services'],
                ['97124','Therapy Services'],['97129','Therapy Services'],['97140','Therapy Services'],['97161','Therapy Services'],
                ['97162','Therapy Services'],['97163','Therapy Services'],['97165','Therapy Services'],['97166','Therapy Services'],
                ['97530','Therapy Services'],['97535','Therapy Services'],['97542','Therapy Services'],['97597','Therapy Services'],
                ['95923','TM Flow'],['93923','TM Flow'],['95921','TM Flow'],['15271','Wound Care - Graft'],['15273','Wound Care - Graft'],
                ['Q4353','Wound Care - Graft'],['15272','Wound Care - Graft'],['Q4316','Wound Care - Graft'],['Q4313','Wound Care - Graft'],
                ['15274','Wound Care - Graft'],
            ];
            $ins = $pdo->prepare("INSERT IGNORE INTO ar_cpt_specialty (cpt_code, specialty, created_at, updated_at) VALUES (?,?,NOW(),NOW())");
            foreach ($seed as $s) $ins->execute([$s[0], $s[1]]);
        },
        // Employee-import history: one row per uploaded workbook so the importer can show what was created/updated
        // (names, emails, roles, the default password) after upload AND for previously uploaded files.
        'employee_import_runs_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `employee_import_runs` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `file_name` VARCHAR(255) NULL,
                `uploaded_by` INT NULL,
                `total_count` INT NOT NULL DEFAULT 0,
                `created_count` INT NOT NULL DEFAULT 0,
                `updated_count` INT NOT NULL DEFAULT 0,
                `skipped_count` INT NOT NULL DEFAULT 0,
                `detail_json` LONGTEXT NULL,
                `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY `idx_eir_created` (`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        },
        // Two-step upload: track files that are stored-but-not-yet-processed so they
        // survive a page reload and can be processed later.
        'pending_uploads_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `pending_uploads` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `report_id` INT NOT NULL,
                `kind` VARCHAR(32) NOT NULL,
                `token` VARCHAR(64) NOT NULL,
                `file_name` VARCHAR(255) NOT NULL,
                `orig_name` VARCHAR(255) NULL,
                `file_size` BIGINT NULL,
                `uploaded_by` INT NULL,
                `status` VARCHAR(16) NOT NULL DEFAULT 'pending',
                `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `processed_at` TIMESTAMP NULL,
                KEY `idx_pending` (`report_id`,`kind`,`status`,`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        },
        // Outstanding Balance redefined: Charges − Insurance Paid (pri+sec+ter) − Write-off
        // (was: expected_payment − insurance paid).  Retro-apply to every existing row so the
        // stored value is correct on read.
        'outstanding_charge_minus_paid_v1' => function (PDO $pdo) {
            $pdo->exec("UPDATE collection_raw
                        SET outstanding = ROUND(COALESCE(charge_amount, 0)
                                              - (COALESCE(primary_ins_paid, 0) + COALESCE(secondary_ins_paid, 0) + COALESCE(tertiary_ins_paid, 0))
                                              - COALESCE(wo_amount, 0), 2)");
        },
        // The per-upload analytics cache has the OLD outstanding totals baked into its JSON,
        // so the dashboard's summary tiles keep showing the old numbers even after the row-level
        // recompute above.  Clear the cache so the dashboard falls back to live SQL (which uses
        // the corrected stored outstanding) until each report is re-processed and its cache
        // is rebuilt with the new formula.
        'clear_stats_cache_outstanding_v1' => function (PDO $pdo) {
            $pdo->exec("DELETE FROM collection_stats");
        },
        // Source-balance refactor: the dashboard now aggregates `insurance_balance` and
        // `patient_balance` directly (not the derived `outstanding`).  Old cached snapshots
        // were built with the old aggregates → clear them so the dashboard rebuilds on next
        // load from the new aggregates.  The `outstanding` column itself is kept in place.
        'clear_stats_cache_source_balance_v1' => function (PDO $pdo) {
            $pdo->exec("DELETE FROM collection_stats");
        },
        // After the source-balance refactor the existing drill indexes still lead with
        // `outstanding` but every dashboard predicate now references `insurance_balance` /
        // `patient_balance` instead — the planner falls back to full scans, which is why tab
        // switches feel sluggish on big reports.  Add v2 indexes that include both balance
        // columns so the new WHERE / aggregate predicates stay index-only.  Old v1 indexes are
        // left in place for now (a few MB on disk, no cost on reads).
        'drill_indexes_balance_v2' => function (PDO $pdo) {
            $defs = [
                ['collection_raw', 'idx_drill_aging_v2',    '`report_id`,`dos`,`dos_year`,`dos_month`,`visit_id`,`insurance_balance`,`patient_balance`,`charge_amount`,`amount_allowed`'],
                ['collection_raw', 'idx_drill_summary_v2',  '`report_id`,`dos_year`,`dos_month`,`facility`,`visit_id`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`,`patient_payment_amount`,`charge_amount`,`expected_payment`,`insurance_balance`,`patient_balance`'],
                ['collection_raw', 'idx_drill_carrier_v2',  '`report_id`,`carrier1_name`,`dos_year`,`dos_month`,`visit_id`,`primary_ins_paid`,`insurance_balance`,`patient_balance`,`charge_amount`,`amount_allowed`,`days_to_first_payment`'],
                ['collection_raw', 'idx_drill_provider_v2', '`report_id`,`provider_name`,`dos_year`,`dos_month`,`visit_id`,`charge_amount`,`expected_payment`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`,`insurance_balance`,`patient_balance`,`days_to_first_payment`'],
                ['collection_raw', 'idx_drill_cpt_v2',      '`report_id`,`charge_code`,`dos_year`,`dos_month`,`units`,`charge_amount`,`expected_payment`,`primary_ins_paid`,`secondary_ins_paid`,`tertiary_ins_paid`,`insurance_balance`,`patient_balance`'],
            ];
            foreach ($defs as [$tbl, $name, $cols]) {
                if (app_index_exists($pdo, $tbl, $name)) continue;
                try {
                    $pdo->exec("ALTER TABLE `$tbl` ADD INDEX `$name` ($cols), ALGORITHM=INPLACE, LOCK=NONE");
                } catch (Throwable $e) {
                    try { $pdo->exec("ALTER TABLE `$tbl` ADD INDEX `$name` ($cols)"); }
                    catch (Throwable $e2) { /* leave; old v1 index will still serve */ }
                }
            }
        },
        // Cross-report facility mirror: this table now defines the VIRTUAL rollup scope
        // applied at query time by collection_scope_for_report() in admin/collection.php
        // and admin/collection-drill.php.  Each row says "when querying target_report_id,
        // ALSO include rows from (source_report_id, facility)" — no physical copy.
        'report_facility_mirror_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `report_facility_mirror` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `target_report_id` BIGINT UNSIGNED NOT NULL,
                `source_report_id` BIGINT UNSIGNED NOT NULL,
                `facility` VARCHAR(255) NOT NULL,
                `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY `uniq_mirror` (`target_report_id`,`source_report_id`,`facility`),
                KEY `idx_source` (`source_report_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        },
        // Seed the initial 5 mirror rules for the 12 Blue Central rollup report.
        // Idempotent — INSERT IGNORE keys off the UNIQUE constraint; if no report named
        // '12 Blue Central' exists yet, the SELECT returns 0 rows and the migration is
        // recorded anyway (rerun later if the target report is created).
        'mirror_rules_12_blue_central_v1' => function (PDO $pdo) {
            $rules = [
                [7,  'BBW - BIG BEND WOODS HEALTHCARE CENTER'],
                [7,  'BLUE - BLUE CIRCLE REHAB AND NURSING'],
                [8,  'GCFRN - GREENERY CENTER FOR REHAB AND NURSING'],
                [10, 'WE - WOODS EDGE REHAB AND NURSING'],
                [10, 'HCR - HARMONY COURT REHAB & NURSING'],
            ];
            $st = $pdo->prepare("
                INSERT IGNORE INTO `report_facility_mirror` (target_report_id, source_report_id, facility)
                SELECT r.id, ?, ? FROM reports r WHERE r.name = '12 Blue Central' LIMIT 1");
            foreach ($rules as [$sourceId, $facility]) {
                $st->execute([$sourceId, $facility]);
            }
        },
        // Deprecated — the prior physical mirror sync has been replaced by a virtual
        // query-time scope (see collection_scope_for_report()).  Leaving these migration
        // records in place so they don't re-run on existing deploys; the closures are
        // no-ops to keep them safe if anyone ever wipes the schema_migrations row.
        'mirror_initial_sync_v1' => function (PDO $pdo) { /* deprecated — mirror is now virtual */ },
        'mirror_safe_resync_v2'  => function (PDO $pdo) { /* deprecated — mirror is now virtual */ },
        // Clean up physical mirror rows from the prior implementation.  Mirror is now
        // applied virtually at query time (collection_scope_for_report) so any rows
        // previously inserted with upload_id IS NULL into a target report are stale.
        // Only deletes rows in reports that ARE targets in report_facility_mirror.
        'mirror_cleanup_physical_rows_v1' => function (PDO $pdo) {
            $pdo->exec("
                DELETE cr FROM collection_raw cr
                WHERE cr.upload_id IS NULL
                  AND cr.report_id IN (SELECT DISTINCT target_report_id FROM report_facility_mirror)");
            // Also nuke stats cache for those targets so the dashboard rebuilds.
            $pdo->exec("
                DELETE cs FROM collection_stats cs
                JOIN collection_uploads cu ON cu.id = cs.upload_id
                WHERE cu.report_id IN (SELECT DISTINCT target_report_id FROM report_facility_mirror)");
        },
        // Account lockout removed (Jun 2026) — convert any existing 'locked' rows to
        // 'active' and clear residual attempt counters / lock timers so no user can
        // be carrying a stale lock when the new login flow ships.
        'remove_account_lock_v1' => function (PDO $pdo) {
            $pdo->exec("UPDATE users
                          SET status = CASE WHEN status='locked' THEN 'active' ELSE status END,
                              login_attempts = 0,
                              locked_until = NULL");
        },
        // In-app error log so SysAdmin can diagnose failures without needing server log access.
        'system_errors_v1' => function (PDO $pdo) {
            $pdo->exec("CREATE TABLE IF NOT EXISTS `system_errors` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `level` VARCHAR(16) NOT NULL DEFAULT 'error',
                `source` VARCHAR(64) NOT NULL DEFAULT 'app',
                `message` TEXT NOT NULL,
                `context` JSON NULL,
                `user_id` INT NULL,
                `url` VARCHAR(255) NULL,
                `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY `idx_errors_created` (`created_at`),
                KEY `idx_errors_source`  (`source`,`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        },
        // Per-user REPORT timezone preference ('EST' | 'IST'). Records always STAMP in Eastern; this only flips how a
        // supervisor/manager VIEWS report date-times. Default EST for everyone.
        'user_report_tz_v1' => function (PDO $pdo) {
            if (!app_col_exists($pdo, 'users', 'report_tz'))
                $pdo->exec("ALTER TABLE `users` ADD COLUMN `report_tz` VARCHAR(3) NOT NULL DEFAULT 'EST'");
        },
        // Per-PAGE access permissions: seed a dedicated `page.<file>` code for every grantable page (see
        // medmecs_grantable_pages() in auth.php) so an admin can give ONE page to ANY user, independent of role.
        // Held by no one until explicitly granted; the page's gate accepts it any-of. Bump to _v2 when pages change.
        // _v2 (2026-07-16): coding-deletions.php joined medmecs_grantable_pages(). _v1 is already recorded in
        // schema_migrations on every existing install and will never run again, so without a new key the new
        // page's `page.` permission is simply never seeded and cannot be granted from Access Control. The
        // INSERT IGNORE makes re-seeding every page harmless.
        'page_access_perms_v2' => function (PDO $pdo) {
            if (!function_exists('medmecs_grantable_pages')) return;
            $ins = $pdo->prepare("INSERT IGNORE INTO permissions (code,label,group_name,description,created_at,updated_at)
                                  VALUES (?,?,?,?,NOW(),NOW())");
            foreach (medmecs_grantable_pages() as $file => $meta) {
                $ins->execute([page_perm_code($file), 'Open: ' . $meta['label'], $meta['area'],
                    'Direct access to the ' . $meta['label'] . ' page (' . $file . '), independent of role.']);
            }
        },
    ];

    // Medical-coding module: first auto-clean any DISCARDED earlier coding build
    // (strips its 'coding_*' markers from $applied so the fresh ones re-run), then
    // register the current coding migrations after the core set.
    if (function_exists('coding_legacy_reset_if_needed')) {
        $applied = coding_legacy_reset_if_needed($pdo, $applied);
    }
    if (function_exists('coding_register_migrations')) {
        $migrations += coding_register_migrations();
    }
    if (function_exists('ar_register_migrations')) {
        $migrations += ar_register_migrations();   // AR / Collections allocation module
    }
    if (function_exists('billing_register_migrations')) {
        $migrations += billing_register_migrations();   // Billing workflow module
    }

    $pending = array_filter(array_keys($migrations), fn($n) => !in_array($n, $applied, true));
    if (!$pending) return;

    @set_time_limit(0);   // first-load only: index builds on large tables can take a little while
    $mark = $pdo->prepare("INSERT IGNORE INTO schema_migrations (name) VALUES (?)");
    foreach ($pending as $name) {
        try {
            ($migrations[$name])($pdo);
            $mark->execute([$name]);
        } catch (Throwable $e) {
            error_log("auto-provision migration '$name' failed: " . $e->getMessage());
            // unrecorded → retried on the next load (all steps are idempotent)
        }
    }
}
