<?php

declare(strict_types=1);

/**
 * DentalCare update-safety and transaction-foundation readiness helpers.
 * These functions are intentionally idempotent: they check before altering and never delete data.
 */

function dental_update_db_name(): string
{
    return (string) app_config('database.name');
}

function dental_update_table_exists(PDO $pdo, string $tableName): bool
{
    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name=?');
        $stmt->execute([dental_update_db_name(), $tableName]);
        return (int) $stmt->fetchColumn() > 0;
    } catch (Throwable) {
        return false;
    }
}

function dental_update_column_exists(PDO $pdo, string $tableName, string $column): bool
{
    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=? AND table_name=? AND column_name=?');
        $stmt->execute([dental_update_db_name(), $tableName, $column]);
        return (int) $stmt->fetchColumn() > 0;
    } catch (Throwable) {
        return false;
    }
}

function dental_update_index_exists(PDO $pdo, string $tableName, string $indexName): bool
{
    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=? AND table_name=? AND index_name=?');
        $stmt->execute([dental_update_db_name(), $tableName, $indexName]);
        return (int) $stmt->fetchColumn() > 0;
    } catch (Throwable) {
        return false;
    }
}

function dental_update_add_column(PDO $pdo, string $tableName, string $column, string $definition): void
{
    if (dental_update_table_exists($pdo, $tableName) && !dental_update_column_exists($pdo, $tableName, $column)) {
        $pdo->exec('ALTER TABLE `' . $tableName . '` ADD `' . $column . '` ' . $definition);
    }
}

function dental_update_add_index(PDO $pdo, string $tableName, string $indexName, string $columns): void
{
    if (dental_update_table_exists($pdo, $tableName) && !dental_update_index_exists($pdo, $tableName, $indexName)) {
        $pdo->exec('ALTER TABLE `' . $tableName . '` ADD KEY `' . $indexName . '` ' . $columns);
    }
}

function dental_update_required_specs(): array
{
    return [
        'Core version/update records' => [
            ['type' => 'table', 'name' => table('settings'), 'label' => 'Settings table'],
            ['type' => 'table', 'name' => table('install_history'), 'label' => 'Install history table'],
            ['type' => 'column', 'table' => table('install_history'), 'name' => 'version', 'label' => 'Install history version'],
            ['type' => 'column', 'table' => table('install_history'), 'name' => 'database_action', 'label' => 'Install history action'],
        ],
        'Appointment and clinical source records' => [
            ['type' => 'table', 'name' => table('appointments'), 'label' => 'Appointments'],
            ['type' => 'table', 'name' => table('appointment_services'), 'label' => 'Appointment service lines'],
            ['type' => 'table', 'name' => table('queue_entries'), 'label' => 'Queue / visit entries'],
            ['type' => 'table', 'name' => table('clinical_encounters'), 'label' => 'Clinical encounters'],
            ['type' => 'column', 'table' => table('appointment_services'), 'name' => 'sequence_no', 'label' => 'Service sequence number'],
            ['type' => 'column', 'table' => table('appointment_services'), 'name' => 'status', 'label' => 'Appointment-service status'],
        ],
        'Billing and payment records' => [
            ['type' => 'table', 'name' => table('invoices'), 'label' => 'Invoices'],
            ['type' => 'table', 'name' => table('invoice_items'), 'label' => 'Invoice items'],
            ['type' => 'table', 'name' => table('payments'), 'label' => 'Payments'],
            ['type' => 'table', 'name' => table('payment_item_allocations'), 'label' => 'Payment item allocations'],
            ['type' => 'table', 'name' => table('payment_adjustments'), 'label' => 'Payment adjustments'],
            ['type' => 'column', 'table' => table('invoices'), 'name' => 'status', 'label' => 'Invoice status'],
            ['type' => 'column', 'table' => table('invoices'), 'name' => 'paid_amount', 'label' => 'Invoice paid amount'],
            ['type' => 'column', 'table' => table('payments'), 'name' => 'reference_no', 'label' => 'Payment reference number'],
        ],
        'Cashier POS controls' => [
            ['type' => 'table', 'name' => table('cashier_windows'), 'label' => 'Cashier windows'],
            ['type' => 'table', 'name' => table('cashier_sessions'), 'label' => 'Cashier sessions'],
            ['type' => 'table', 'name' => table('cashier_checkouts'), 'label' => 'Cashier checkout records'],
            ['type' => 'column', 'table' => table('cashier_sessions'), 'name' => 'locked_patient_id', 'label' => 'Locked patient context'],
            ['type' => 'column', 'table' => table('cashier_sessions'), 'name' => 'window_id', 'label' => 'Cashier session window'],
        ],
        'v0.5.9.8.34 transaction foundation' => [
            ['type' => 'table', 'name' => table('cashier_transaction_locks'), 'label' => 'Cashier transaction locks'],
            ['type' => 'table', 'name' => table('invoice_state_history'), 'label' => 'Invoice state history'],
            ['type' => 'table', 'name' => table('transaction_audit_events'), 'label' => 'Transaction audit events'],
            ['type' => 'table', 'name' => table('payment_method_rules'), 'label' => 'Payment method rules'],
            ['type' => 'column', 'table' => table('invoices'), 'name' => 'transaction_type', 'label' => 'Invoice transaction type'],
            ['type' => 'column', 'table' => table('invoices'), 'name' => 'flow_state', 'label' => 'Invoice flow state'],
            ['type' => 'column', 'table' => table('invoices'), 'name' => 'balance_due', 'label' => 'Invoice balance due'],
            ['type' => 'column', 'table' => table('payments'), 'name' => 'reference_required', 'label' => 'Payment reference-required flag'],
            ['type' => 'column', 'table' => table('cashier_sessions'), 'name' => 'active_invoice_id', 'label' => 'Active invoice lock'],
        ],
        'v0.5.9.8.36 treatment-to-billing final flow' => [
            ['type' => 'table', 'name' => table('clinical_billing_handoff_events'), 'label' => 'Clinical billing handoff event log'],
            ['type' => 'column', 'table' => table('procedures'), 'name' => 'disposition_reason', 'label' => 'Procedure non-billable reason'],
            ['type' => 'column', 'table' => table('procedures'), 'name' => 'patient_agreement_recorded_at', 'label' => 'Patient agreement timestamp'],
            ['type' => 'column', 'table' => table('procedures'), 'name' => 'patient_agreement_recorded_by', 'label' => 'Patient agreement recorder'],
            ['type' => 'column', 'table' => table('procedures'), 'name' => 'cashier_handoff_status', 'label' => 'Procedure cashier handoff status'],
            ['type' => 'column', 'table' => table('procedures'), 'name' => 'cashier_handoff_at', 'label' => 'Procedure cashier handoff timestamp'],
        ],
        'v0.5.9.8.41 balance-only payment resilience' => [
            ['type' => 'table', 'name' => table('cashier_checkouts'), 'label' => 'Cashier checkout activity table'],
            ['type' => 'column', 'table' => table('cashier_checkouts'), 'name' => 'checkout_type', 'label' => 'Checkout type classification'],
            ['type' => 'column', 'table' => table('cashier_sessions'), 'name' => 'active_invoice_id', 'label' => 'Active invoice lock for balance payment'],
            ['type' => 'column', 'table' => table('cashier_sessions'), 'name' => 'transaction_mode', 'label' => 'Cashier transaction mode'],
            ['type' => 'column', 'table' => table('cashier_sessions'), 'name' => 'lock_started_at', 'label' => 'Focused transaction start timestamp'],
        ],
        'v0.5.9.8.43 security hardening' => [
            ['type' => 'table', 'name' => table('security_login_attempts'), 'label' => 'Login throttling attempt log'],
            ['type' => 'table', 'name' => table('user_sessions'), 'label' => 'Staff session/device tracking'],
            ['type' => 'table', 'name' => table('security_events'), 'label' => 'Security event log'],
            ['type' => 'table', 'name' => table('privileged_action_confirmations'), 'label' => 'High-risk action confirmation log'],
            ['type' => 'column', 'table' => table('security_login_attempts'), 'name' => 'email_hash', 'label' => 'Login attempt email hash'],
            ['type' => 'column', 'table' => table('user_sessions'), 'name' => 'revoked_at', 'label' => 'Revoked session timestamp'],
            ['type' => 'column', 'table' => table('security_events'), 'name' => 'severity', 'label' => 'Security event severity'],
            ['type' => 'column', 'table' => table('privileged_action_confirmations'), 'name' => 'expires_at', 'label' => 'Confirmation expiry'],
        ],
        'v0.5.9.8.44 appointment/schedule final guard' => [
            ['type' => 'table', 'name' => table('appointment_assignment_queue'), 'label' => 'Clinic-decides provider assignment queue'],
            ['type' => 'table', 'name' => table('appointment_safety_events'), 'label' => 'Appointment safety event log'],
            ['type' => 'column', 'table' => table('appointments'), 'name' => 'assignment_status', 'label' => 'Appointment provider assignment status'],
            ['type' => 'column', 'table' => table('appointments'), 'name' => 'assignment_note', 'label' => 'Appointment provider assignment note'],
            ['type' => 'column', 'table' => table('appointments'), 'name' => 'schedule_guard_status', 'label' => 'Appointment schedule guard status'],
            ['type' => 'column', 'table' => table('appointments'), 'name' => 'schedule_guard_checked_at', 'label' => 'Schedule guard timestamp'],
            ['type' => 'column', 'table' => table('appointment_requests'), 'name' => 'assignment_queue_status', 'label' => 'Online request assignment queue status'],
        ],
        'v0.5.9.8.45 clinical record completion' => [
            ['type' => 'table', 'name' => table('clinical_record_completion_reviews'), 'label' => 'Clinical record completion reviews'],
            ['type' => 'table', 'name' => table('procedure_note_templates'), 'label' => 'Procedure note templates'],
            ['type' => 'table', 'name' => table('treatment_plan_signatures'), 'label' => 'Treatment-plan signature foundation'],
            ['type' => 'column', 'table' => table('clinical_encounters'), 'name' => 'record_completion_status', 'label' => 'Encounter completion status'],
            ['type' => 'column', 'table' => table('documents'), 'name' => 'clinical_significance', 'label' => 'Clinical document significance'],
        ],
        'v0.5.9.8.46 inventory/sterilization completion' => [
            ['type' => 'table', 'name' => table('procedure_supply_templates'), 'label' => 'Procedure supply templates'],
            ['type' => 'table', 'name' => table('procedure_supply_template_lines'), 'label' => 'Procedure supply template lines'],
            ['type' => 'table', 'name' => table('clinical_supply_usage_audit'), 'label' => 'Clinical supply usage audit'],
            ['type' => 'table', 'name' => table('inventory_safety_events'), 'label' => 'Inventory safety events'],
            ['type' => 'table', 'name' => table('sterilization_release_reviews'), 'label' => 'Sterilization release reviews'],
            ['type' => 'column', 'table' => table('inventory_items'), 'name' => 'is_controlled_supply', 'label' => 'Controlled supply flag'],
            ['type' => 'column', 'table' => table('inventory_items'), 'name' => 'requires_lot_for_issue', 'label' => 'Lot required for issue flag'],
            ['type' => 'column', 'table' => table('inventory_lots'), 'name' => 'quarantine_reason', 'label' => 'Lot quarantine reason'],
            ['type' => 'column', 'table' => table('inventory_documents'), 'name' => 'clinical_consumption_review_status', 'label' => 'Clinical consumption review status'],
            ['type' => 'column', 'table' => table('sterilization_batches'), 'name' => 'release_review_status', 'label' => 'Sterilization release review status'],
            ['type' => 'column', 'table' => table('instrument_sets'), 'name' => 'last_release_review_id', 'label' => 'Instrument release-review link'],
            ['type' => 'column', 'table' => table('instrument_usage'), 'name' => 'usage_status', 'label' => 'Instrument usage status'],
            ['type' => 'column', 'table' => table('sterilization_equipment'), 'name' => 'maintenance_lock_reason', 'label' => 'Sterilizer maintenance lock reason'],
        ],

        'v0.5.9.8.47 chair resource / operatory flow' => [
            ['type' => 'table', 'name' => table('chair_session_assignments'), 'label' => 'Temporary chair/provider session assignments'],
            ['type' => 'table', 'name' => table('chair_status_events'), 'label' => 'Chair status and occupancy event log'],
            ['type' => 'table', 'name' => table('chair_visit_movements'), 'label' => 'Visit-level chair movement log'],
            ['type' => 'column', 'table' => table('clinic_chairs'), 'name' => 'resource_kind', 'label' => 'Chair resource kind'],
            ['type' => 'column', 'table' => table('clinic_chairs'), 'name' => 'default_provider_user_id', 'label' => 'Optional default provider'],
            ['type' => 'column', 'table' => table('clinic_chairs'), 'name' => 'last_released_at', 'label' => 'Last chair release timestamp'],
            ['type' => 'column', 'table' => table('doctor_chair_assignments'), 'name' => 'assignment_type', 'label' => 'Default assignment type'],
        ],

        'v0.5.9.8.49 release hardening / clean packages' => [
            ['type' => 'table', 'name' => table('release_hardening_audits'), 'label' => 'Release hardening audit log'],
        ],
        'v0.5.9.8.50 role satisfaction / permissions' => [
            ['type' => 'table', 'name' => table('role_satisfaction_audits'), 'label' => 'Role satisfaction audit log'],
        ],
        'v0.5.9.8.59 reset/layout compatibility' => [
            ['type' => 'column', 'table' => table('queue_entries'), 'name' => 'inhouse_provider_name', 'label' => 'Queue in-house provider display name'],
        ],
        'v0.5.9.8.60 chair/service edit usability' => [
            ['type' => 'table', 'name' => table('chair_session_assignments'), 'label' => 'Chair temporary-session table available'],
            ['type' => 'column', 'table' => table('clinic_chairs'), 'name' => 'resource_kind', 'label' => 'Chair resource kind available'],
            ['type' => 'column', 'table' => table('service_catalog'), 'name' => 'duration_minutes', 'label' => 'Service duration editable'],
            ['type' => 'column', 'table' => table('service_catalog'), 'name' => 'description', 'label' => 'Service description editable'],
        ],
        'v0.5.9.8.61 reset-runtime hardening' => [
            ['type' => 'column', 'table' => table('queue_entries'), 'name' => 'inhouse_provider_name', 'label' => 'Queue in-house provider compatibility column'],
        ],
    ];
}

function dental_update_readiness_report(PDO $pdo): array
{
    $report = [];
    foreach (dental_update_required_specs() as $group => $items) {
        $ready = 0;
        $missing = 0;
        $details = [];
        foreach ($items as $item) {
            if ($item['type'] === 'table') {
                $ok = dental_update_table_exists($pdo, (string) $item['name']);
                $subject = (string) $item['name'];
            } elseif ($item['type'] === 'column') {
                $ok = dental_update_column_exists($pdo, (string) $item['table'], (string) $item['name']);
                $subject = (string) $item['table'] . '.' . (string) $item['name'];
            } elseif ($item['type'] === 'index') {
                $ok = dental_update_index_exists($pdo, (string) $item['table'], (string) $item['name']);
                $subject = (string) $item['table'] . ':' . (string) $item['name'];
            } else {
                $ok = false;
                $subject = (string) ($item['name'] ?? 'unknown');
            }
            if ($ok) $ready++; else $missing++;
            $details[] = [
                'label' => (string) ($item['label'] ?? $subject),
                'subject' => $subject,
                'status' => $ok ? 'Ready' : 'Needs repair',
                'ready' => $ok,
            ];
        }
        $report[$group] = [
            'ready' => $missing === 0,
            'ready_count' => $ready,
            'missing_count' => $missing,
            'total' => count($items),
            'details' => $details,
        ];
    }
    return $report;
}

function dental_update_safety_ready(PDO $pdo): bool
{
    foreach (dental_update_readiness_report($pdo) as $group) {
        if (!$group['ready']) return false;
    }
    return true;
}

function dental_update_readiness_summary(PDO $pdo): array
{
    $groups = dental_update_readiness_report($pdo);
    $total = 0;
    $missing = 0;
    foreach ($groups as $group) {
        $total += (int) $group['total'];
        $missing += (int) $group['missing_count'];
    }
    return [
        'total' => $total,
        'missing' => $missing,
        'ready' => $missing === 0,
    ];
}

function dental_update_execute_migration(PDO $pdo, string $path): void
{
    if (!is_file($path)) throw new RuntimeException('Unable to read migration ' . basename($path));
    $sql = (string) file_get_contents($path);
    $sql = str_replace('{{prefix}}', (string) app_config('database.prefix', 'dent_'), $sql);
    $statements = array_filter(array_map('trim', preg_split('/;\s*(?:\r?\n|$)/', $sql) ?: []));
    foreach ($statements as $statement) {
        if ($statement !== '') $pdo->exec($statement);
    }
}

function dental_update_safety_apply_v059834(PDO $pdo): void
{
    dental_update_execute_migration($pdo, DENTAL_ROOT . '/database/migrations/0.5.9.8.34_update_safety_transaction_foundation.sql');

    $invoices = table('invoices');
    dental_update_add_column($pdo, $invoices, 'transaction_type', "VARCHAR(40) NOT NULL DEFAULT 'clinical_visit'");
    dental_update_add_column($pdo, $invoices, 'flow_state', "VARCHAR(40) NOT NULL DEFAULT 'ready_for_payment'");
    dental_update_add_column($pdo, $invoices, 'source_confirmed_by', 'BIGINT UNSIGNED NULL');
    dental_update_add_column($pdo, $invoices, 'source_confirmed_at', 'DATETIME NULL');
    dental_update_add_column($pdo, $invoices, 'source_confirmation_note', 'VARCHAR(500) NULL');
    dental_update_add_column($pdo, $invoices, 'balance_due', 'DECIMAL(12,2) NOT NULL DEFAULT 0.00');
    dental_update_add_column($pdo, $invoices, 'pos_lock_id', 'BIGINT UNSIGNED NULL');
    dental_update_add_column($pdo, $invoices, 'pos_lock_expires_at', 'DATETIME NULL');
    dental_update_add_column($pdo, $invoices, 'last_transaction_event_id', 'BIGINT UNSIGNED NULL');
    dental_update_add_index($pdo, $invoices, 'idx_invoice_flow_state', '(`flow_state`,`status`,`created_at`)');
    dental_update_add_index($pdo, $invoices, 'idx_invoice_balance_due', '(`patient_id`,`balance_due`,`status`)');

    $payments = table('payments');
    dental_update_add_column($pdo, $payments, 'reference_required', 'TINYINT(1) NOT NULL DEFAULT 0');
    dental_update_add_column($pdo, $payments, 'processor_reference_no', 'VARCHAR(120) NULL');
    dental_update_add_column($pdo, $payments, 'external_receipt_no', 'VARCHAR(120) NULL');
    dental_update_add_column($pdo, $payments, 'payment_status', "VARCHAR(40) NOT NULL DEFAULT 'posted'");
    dental_update_add_index($pdo, $payments, 'idx_payment_reference', '(`method`,`reference_no`,`created_at`)');

    $items = table('invoice_items');
    dental_update_add_column($pdo, $items, 'clinical_status_snapshot', 'VARCHAR(40) NULL');
    dental_update_add_column($pdo, $items, 'billable_status', "VARCHAR(40) NOT NULL DEFAULT 'billable'");
    dental_update_add_index($pdo, $items, 'idx_invoice_item_billable_status', '(`billable_status`,`source_type`)');

    $sessions = table('cashier_sessions');
    dental_update_add_column($pdo, $sessions, 'active_invoice_id', 'BIGINT UNSIGNED NULL');
    dental_update_add_column($pdo, $sessions, 'transaction_mode', "VARCHAR(40) NOT NULL DEFAULT 'patient_visit'");
    dental_update_add_column($pdo, $sessions, 'lock_started_at', 'DATETIME NULL');
    dental_update_add_index($pdo, $sessions, 'idx_cashier_session_active_invoice', '(`active_invoice_id`,`status`)');

    if (dental_update_table_exists($pdo, $invoices)) {
        $pdo->exec("UPDATE `{$invoices}` SET transaction_type=COALESCE(NULLIF(transaction_type,''),'clinical_visit') WHERE transaction_type IS NULL OR transaction_type=''");
        $pdo->exec("UPDATE `{$invoices}` SET flow_state=CASE WHEN status='paid' THEN 'paid' WHEN status='partial' THEN 'partially_paid' WHEN status='voided' THEN 'voided' WHEN status='refunded' THEN 'refunded' WHEN status='written_off' THEN 'written_off' ELSE COALESCE(NULLIF(flow_state,''),'ready_for_payment') END");
        $pdo->exec("UPDATE `{$invoices}` SET balance_due=GREATEST(amount-paid_amount,0) WHERE balance_due=0 OR balance_due IS NULL");
    }
    if (dental_update_table_exists($pdo, $payments)) {
        $pdo->exec("UPDATE `{$payments}` SET reference_required=CASE WHEN LOWER(method) IN ('card','credit_card','debit_card','gcash','maya','bank_transfer','online','digital_wallet') THEN 1 ELSE 0 END WHERE reference_required=0");
        $pdo->exec("UPDATE `{$payments}` SET processor_reference_no=COALESCE(processor_reference_no,reference_no) WHERE processor_reference_no IS NULL AND reference_no IS NOT NULL");
    }
}

function dental_update_safety_apply_v059836(PDO $pdo): void
{
    dental_update_execute_migration($pdo, DENTAL_ROOT . '/database/migrations/0.5.9.8.36_treatment_to_billing_final_flow.sql');

    $procedures = table('procedures');
    dental_update_add_column($pdo, $procedures, 'disposition_reason', 'VARCHAR(500) NULL');
    dental_update_add_column($pdo, $procedures, 'patient_agreement_recorded_at', 'DATETIME NULL');
    dental_update_add_column($pdo, $procedures, 'patient_agreement_recorded_by', 'BIGINT UNSIGNED NULL');
    dental_update_add_column($pdo, $procedures, 'cashier_handoff_status', "VARCHAR(40) NOT NULL DEFAULT 'pending_review'");
    dental_update_add_column($pdo, $procedures, 'cashier_handoff_at', 'DATETIME NULL');
    dental_update_add_index($pdo, $procedures, 'idx_procedure_cashier_handoff', '(`encounter_id`,`cashier_handoff_status`,`status`)');
}

function dental_update_safety_v059836_ready(PDO $pdo): bool
{
    return dental_update_table_exists($pdo, table('clinical_billing_handoff_events'))
        && dental_update_column_exists($pdo, table('procedures'), 'disposition_reason')
        && dental_update_column_exists($pdo, table('procedures'), 'patient_agreement_recorded_at')
        && dental_update_column_exists($pdo, table('procedures'), 'patient_agreement_recorded_by')
        && dental_update_column_exists($pdo, table('procedures'), 'cashier_handoff_status')
        && dental_update_column_exists($pdo, table('procedures'), 'cashier_handoff_at');
}

function dental_update_release_apply_map(): array
{
    return [
        '0.5.9.8.33' => 'setup_apply_v059833',
        '0.5.9.8.34' => 'setup_apply_v059834',
        '0.5.9.8.35' => 'setup_apply_v059835',
        '0.5.9.8.36' => 'setup_apply_v059836',
        '0.5.9.8.37' => 'setup_apply_v059837',
        '0.5.9.8.38' => 'setup_apply_v059838',
        '0.5.9.8.39' => 'setup_apply_v059839',
        '0.5.9.8.40' => 'setup_apply_v059840',
        '0.5.9.8.41' => 'setup_apply_v059841',
        '0.5.9.8.42' => 'setup_apply_v059842',
        '0.5.9.8.43' => 'setup_apply_v059843',
        '0.5.9.8.44' => 'setup_apply_v059844',
        '0.5.9.8.45' => 'setup_apply_v059845',
        '0.5.9.8.46' => 'setup_apply_v059846',
        '0.5.9.8.47' => 'setup_apply_v059847',
        '0.5.9.8.48' => 'setup_apply_v059848',
        '0.5.9.8.49' => 'setup_apply_v059849',
        '0.5.9.8.50' => 'setup_apply_v059850',
        '0.5.9.8.51' => 'setup_apply_v059851',
        '0.5.9.8.52' => 'setup_apply_v059852',
        '0.5.9.8.53' => 'setup_apply_v059853',
        '0.5.9.8.54' => 'setup_apply_v059854',
        '0.5.9.8.55' => 'setup_apply_v059855',
        '0.5.9.8.56' => 'setup_apply_v059856',
        '0.5.9.8.57' => 'setup_apply_v059857',
        '0.5.9.8.58' => 'setup_apply_v059858',
        '0.5.9.8.59' => 'setup_apply_v059859',
        '0.5.9.8.60' => 'setup_apply_v059860',
        '0.5.9.8.61' => 'setup_apply_v059861',
        '0.5.9.8.62' => 'setup_apply_v059862',
        '0.5.9.8.63' => 'setup_apply_v059863',
        '0.5.9.8.64' => 'setup_apply_v059864',
        '0.5.9.8.65' => 'setup_apply_v059865',
        '0.5.9.8.65.1' => 'setup_apply_v0598651',
        '0.5.9.8.66' => 'setup_apply_v059866',
        '0.5.9.8.66.2' => 'setup_apply_v0598662',
        '0.5.9.8.66.3' => 'setup_apply_v0598663',
        '0.5.9.8.66.4' => 'setup_apply_v0598664',
        '0.5.9.8.67' => 'setup_apply_v059867',
        '0.5.9.8.68' => 'setup_apply_v059868',
        '0.5.9.8.69' => 'setup_apply_v059869',
        '0.5.9.8.70' => 'setup_apply_v059870',
        '0.5.9.8.71' => 'setup_apply_v059871',
        '0.5.9.8.71.1' => 'setup_apply_v0598711',
        '0.5.9.8.71.2' => 'setup_apply_v0598712',
        '0.5.9.8.72' => 'setup_apply_v059872',
        '0.5.9.8.73' => 'setup_apply_v059873',
        '0.5.9.8.74' => 'setup_apply_v059874',
        '0.5.9.8.75' => 'setup_apply_v059875',
        '0.5.9.8.76' => 'setup_apply_v059876',
        '0.5.9.8.78' => 'setup_apply_v059878',
        '0.5.9.8.79' => 'setup_apply_v059879',
        '0.5.9.8.80' => 'setup_apply_v059880',
        '0.5.9.8.81' => 'setup_apply_v059881',
        '0.5.9.8.82' => 'setup_apply_v059882',
        '0.5.9.8.83' => 'setup_apply_v059883',
        '0.5.9.8.84' => 'setup_apply_v059884',
        '0.5.9.8.85' => 'setup_apply_v059885',
        '0.5.9.8.86' => 'setup_apply_v059886',
        '0.5.9.8.87' => 'setup_apply_v059887',
        '0.5.9.8.88' => 'setup_apply_v059888',
        '0.5.9.8.89' => 'setup_apply_v059889',
        '0.5.9.8.90' => 'setup_apply_v059890',
        '0.5.9.8.91' => 'setup_apply_v059891',
        '0.5.9.8.92' => 'setup_apply_v059892',
        '0.5.9.8.93' => 'setup_apply_v059893',
        '0.5.9.8.94' => 'setup_apply_v059894',
        '0.5.9.8.95' => 'setup_apply_v059895',
        '0.5.9.8.96' => 'setup_apply_v059896',
        '0.5.9.8.97' => 'setup_apply_v059897',
        '0.5.9.8.98' => 'setup_apply_v059898',
        '0.5.9.8.99' => 'setup_apply_v059899',
        '0.5.9.8.100' => 'setup_apply_v0598100',
        '0.5.9.8.101' => 'setup_apply_v0598101',
        '0.5.9.8.102' => 'setup_apply_v0598102',
        '0.5.9.8.103' => 'setup_apply_v0598103',
        '0.5.9.8.104' => 'setup_apply_v0598104',
        '0.5.9.8.105' => 'setup_apply_v0598105',
        '0.5.9.8.106' => 'setup_apply_v0598106',
        '0.5.9.8.107' => 'setup_apply_v0598107',
        '0.5.9.8.108' => 'setup_apply_v0598108',
        '0.5.9.8.109' => 'setup_apply_v0598109',
        '0.5.9.8.110' => 'setup_apply_v0598110',
        '0.6.0' => 'setup_apply_v0600',
        '0.6.1' => 'setup_apply_v0601',
        '0.6.2' => 'setup_apply_v0602',
        '0.6.2.1' => 'setup_apply_v06021',
        '0.6.2.2' => 'setup_apply_v06022',
        '0.6.3' => 'setup_apply_v0603',
        '0.6.3.1' => 'setup_apply_v06031',
        '0.6.4' => 'setup_apply_v0604',
        '0.6.5' => 'setup_apply_v0605',
        '0.6.6' => 'setup_apply_v0606',
        '0.6.7' => 'setup_apply_v0607',
        '0.6.8' => 'setup_apply_v0608',
        '0.6.9' => 'setup_apply_v0609',
        '0.6.9.1' => 'setup_apply_v06091',
        '0.7.0' => 'setup_apply_v0700',
        '0.7.1' => 'setup_apply_v0710',
        '0.7.2' => 'setup_apply_v0720',
        '0.7.3' => 'setup_apply_v0730',
        '0.7.4' => 'setup_apply_v0740',
        '0.7.5' => 'setup_apply_v0750',
        '0.7.5.1' => 'setup_apply_v0751',
        '0.7.5.2' => 'setup_apply_v0752',
        '0.7.6' => 'setup_apply_v0760',
        '0.7.6.1' => 'setup_apply_v0761',
        '0.7.6.2' => 'setup_apply_v0762',
        '0.7.6.3' => 'setup_apply_v0763',
        '0.7.6.4' => 'setup_apply_v0764',
        '0.7.6.5' => 'setup_apply_v0765',
        '0.7.8.0' => 'setup_apply_v0780',
        '0.7.9.0' => 'setup_apply_v0790',
        '0.7.9.1' => 'setup_apply_v0791',
        '0.7.9.2' => 'setup_apply_v0792',
        '0.8.0.0' => 'setup_apply_v0800',
        '0.8.0.1' => 'setup_apply_v0801',
        '0.8.0.2' => 'setup_apply_v0802',
        '0.8.0.3' => 'setup_apply_v0803',
        '0.8.0.4' => 'setup_apply_v0804',
        '0.8.0.5' => 'setup_apply_v0805',
        '0.8.1.0' => 'setup_apply_v0810',
        '0.8.1.1' => 'setup_apply_v0811',
        '0.8.1.2' => 'setup_apply_v0812',
        '0.9.0.0' => 'setup_apply_v0900',
        '0.9.0.1' => 'setup_apply_v0901',
        '0.9.0.2' => 'setup_apply_v0902',
        '0.9.0.3' => 'setup_apply_v0903',
        '0.9.0.4' => 'setup_apply_v0904',
        '0.9.0.5' => 'setup_apply_v0905',
        '0.9.0.6' => 'setup_apply_v0906',
        '0.9.0.7' => 'setup_apply_v0907',
        '0.9.0.8' => 'setup_apply_v0908',
        '0.9.0.9' => 'setup_apply_v0909',
        '0.9.1.0' => 'setup_apply_v0910',
        '0.9.1.1' => 'setup_apply_v0911',
        '0.9.1.2' => 'setup_apply_v0912',
        '0.9.1.3' => 'setup_apply_v0913',
        '0.9.1.4' => 'setup_apply_v0914',
        '0.9.2.0' => 'setup_apply_v0920',
        '0.9.3.0' => 'setup_apply_v0930',
        '0.9.4.0' => 'setup_apply_v0940',
        '1.0.0.0' => 'setup_apply_v1000',
    ];
}

function dental_update_known_releases(?string $releaseVersion = null): array
{
    $versions = array_keys(dental_update_release_apply_map());
    $releaseVersion = $releaseVersion ?: app_release_version();
    return array_values(array_filter($versions, static fn(string $version): bool => version_compare($version, $releaseVersion, '<=')));
}

function dental_update_apply_chain_status(?string $releaseVersion = null): array
{
    $status = [];
    foreach (dental_update_release_apply_map() as $version => $functionName) {
        if ($releaseVersion && version_compare($version, $releaseVersion, '>')) continue;
        $status[$version] = [
            'function' => $functionName,
            'available' => function_exists($functionName),
        ];
    }
    return $status;
}

function dental_update_apply_chain_complete(?string $releaseVersion = null): bool
{
    foreach (dental_update_apply_chain_status($releaseVersion) as $row) {
        if (empty($row['available'])) return false;
    }
    return true;
}

function dental_update_history_versions(PDO $pdo): array
{
    try {
        if (!dental_update_table_exists($pdo, table('install_history'))) return [];
        $stmt = $pdo->query('SELECT DISTINCT version FROM `' . table('install_history') . '` WHERE version IS NOT NULL AND version<>""');
        return array_values(array_unique(array_map('strval', $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [])));
    } catch (Throwable) {
        return [];
    }
}

function dental_update_missing_versions(PDO $pdo, ?string $releaseVersion = null): array
{
    $releaseVersion = $releaseVersion ?: app_release_version();
    $installed = installed_application_version();
    $history = array_flip(dental_update_history_versions($pdo));
    $missing = [];
    foreach (dental_update_known_releases($releaseVersion) as $version) {
        if (version_compare($version, $installed, '<=')) {
            // If the system says it is past this version, do not force a history-only retry.
            // The readiness checker below catches real missing tables/columns.
            continue;
        }
        if (!isset($history[$version])) $missing[] = $version;
    }
    return $missing;
}

function dental_update_schema_drift_detected(PDO $pdo): bool
{
    $release = app_release_version();
    if (version_compare($release, '0.8.1.2', '>=')
        && version_compare(installed_application_version(), $release, '>=')) {
        return !dental_update_table_exists($pdo, table('system_migration_ledger'))
            || !dental_update_column_exists($pdo, table('user_role_assignments'), 'expires_at')
            || !dental_update_column_exists($pdo, table('user_role_assignments'), 'last_switched_at')
            || !dental_update_column_exists($pdo, table('users'), 'role_context_version')
            || !dental_update_column_exists($pdo, table('users'), 'last_role_switch_at')
            || !dental_update_column_exists($pdo, table('users'), 'last_role_switch_role_id')
            || (string) setting_get('system', 'controlled_active_roles_v0812_ready', '0') !== '1'
            || (string) setting_get('system', 'deterministic_update_plan_v0812_ready', '0') !== '1';
    }

    return !dental_update_safety_ready($pdo)
        || !dental_update_table_exists($pdo, table('cashier_transaction_locks'))
        || !dental_update_table_exists($pdo, table('clinical_billing_handoff_events'))
        || !dental_update_column_exists($pdo, table('cashier_sessions'), 'transaction_mode')
        || !dental_update_column_exists($pdo, table('cashier_sessions'), 'lock_started_at')
        || !dental_update_column_exists($pdo, table('cashier_checkouts'), 'checkout_type')
        || !dental_update_table_exists($pdo, table('appointment_assignment_queue'))
        || !dental_update_table_exists($pdo, table('appointment_safety_events'))
        || !dental_update_column_exists($pdo, table('appointments'), 'assignment_status')
        || !dental_update_column_exists($pdo, table('appointments'), 'schedule_guard_status')
        || !dental_update_table_exists($pdo, table('clinical_record_completion_reviews'))
        || !dental_update_table_exists($pdo, table('procedure_note_templates'))
        || !dental_update_table_exists($pdo, table('treatment_plan_signatures'))
        || !dental_update_column_exists($pdo, table('clinical_encounters'), 'record_completion_status')
        || !dental_update_column_exists($pdo, table('documents'), 'clinical_significance')
        || !dental_update_table_exists($pdo, table('procedure_supply_templates'))
        || !dental_update_table_exists($pdo, table('clinical_supply_usage_audit'))
        || !dental_update_table_exists($pdo, table('inventory_safety_events'))
        || !dental_update_table_exists($pdo, table('sterilization_release_reviews'))
        || !dental_update_column_exists($pdo, table('inventory_items'), 'is_controlled_supply')
        || !dental_update_column_exists($pdo, table('inventory_lots'), 'quarantine_reason')
        || !dental_update_column_exists($pdo, table('inventory_documents'), 'clinical_consumption_review_status')
        || !dental_update_column_exists($pdo, table('sterilization_batches'), 'release_review_status')
        || !dental_update_column_exists($pdo, table('instrument_usage'), 'usage_status')
        || !dental_update_column_exists($pdo, table('sterilization_equipment'), 'maintenance_lock_reason')
        || !dental_update_table_exists($pdo, table('chair_session_assignments'))
        || !dental_update_table_exists($pdo, table('chair_status_events'))
        || !dental_update_table_exists($pdo, table('chair_visit_movements'))
        || !dental_update_column_exists($pdo, table('clinic_chairs'), 'resource_kind')
        || !dental_update_column_exists($pdo, table('clinic_chairs'), 'default_provider_user_id')
        || !dental_update_column_exists($pdo, table('doctor_chair_assignments'), 'assignment_type')
        || !dental_update_table_exists($pdo, table('release_hardening_audits'))
        || (version_compare(app_release_version(),'0.7.9.2','>=') && (
            !dental_update_column_exists($pdo, table('clinical_encounters'), 'assistant_decision')
            || !dental_update_column_exists($pdo, table('clinical_encounters'), 'assistant_decided_by')
            || !dental_update_column_exists($pdo, table('clinical_encounters'), 'assistant_decided_at')
        ))
        || (version_compare(app_release_version(),'0.8.0.0','>=') && (
            (string) setting_get('system','release_candidate_framework_v0800_ready','0') !== '1'
            || (string) setting_get('system','release_candidate_installer_gate_v0800_ready','0') !== '1'
            || (string) setting_get('system','release_candidate_cleanup_v0800_ready','0') !== '1'
            || (string) setting_get('system','release_candidate_evidence_v0800_ready','0') !== '1'
            || (string) setting_get('system','release_candidate_signoff_v0800_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.8.0.1','>=') && (
            (string) setting_get('system','release_candidate_evidence_binding_v0801_ready','0') !== '1'
            || (string) setting_get('system','verified_backup_selection_v0801_ready','0') !== '1'
            || (string) setting_get('system','role_prerequisite_gate_v0801_ready','0') !== '1'
            || (string) setting_get('system','monitor_review_fingerprint_v0801_ready','0') !== '1'
            || (string) setting_get('system','installer_approval_binding_v0801_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.8.0.2','>=') && (
            (string) setting_get('system','recovery_evidence_integrity_v0802_ready','0') !== '1'
            || (string) setting_get('system','explicit_backup_verification_v0802_ready','0') !== '1'
            || (string) setting_get('system','version_bound_manual_flow_v0802_ready','0') !== '1'
            || (string) setting_get('system','version_bound_restore_evidence_v0802_ready','0') !== '1'
            || (string) setting_get('system','future_timestamp_guard_v0802_ready','0') !== '1'
            || (string) setting_get('system','role_evidence_fingerprint_v0802_ready','0') !== '1'
                ))
        || (version_compare(app_release_version(),'0.8.0.3','>=') && (
            (string) setting_get('system','patient_visit_ledger_v0803_ready','0') !== '1'
            || (string) setting_get('system','general_staff_combined_role_v0803_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.8.0.4','>=') && (
            !schema_column_exists(table('clinical_encounters'),'care_path')
            || !schema_column_exists(table('clinical_encounters'),'vitals_skip_reason')
            || !schema_column_exists(table('clinical_encounters'),'care_path_decided_by')
            || !schema_column_exists(table('clinical_encounters'),'care_path_decided_at')
            || (string) setting_get('system','adaptive_encounter_workflow_v0804_ready','0') !== '1'
            || (string) setting_get('system','production_gate_completion_center_v0804_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.8.0.5','>=') && (
            !schema_table_exists(table('system_update_runs'))
            || !schema_column_exists(table('backup_logs'),'verification_evidence_json')
            || !schema_column_exists(table('backup_logs'),'verification_file_count')
            || !schema_column_exists(table('backup_logs'),'verification_format_version')
            || (string) setting_get('system','automated_production_validation_v0805_ready','0') !== '1'
            || (string) setting_get('system','resumable_updates_v0805_ready','0') !== '1'
            || (string) setting_get('system','simplified_production_release_v0805_ready','0') !== '1'
            || (string) setting_get('system','backup_verification_status_fix_v0805_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.8.1.0','>=') && (
            (string) setting_get('system','quota_aware_storage_health_v0810_ready','0') !== '1'
            || (string) setting_get('system','update_transport_reliability_v0810_ready','0') !== '1'
            || (string) setting_get('system','csrf_compatibility_v0810_ready','0') !== '1'
            || (string) setting_get('system','production_release_v0810_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.8.1.1','>=') && (
            (string) setting_get('system','shared_filesystem_percentage_nonblocking_v0811_ready','0') !== '1'
            || (string) setting_get('system','unknown_quota_nonblocking_v0811_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.8.1.2','>=') && (
            !schema_table_exists(table('system_migration_ledger'))
            || !schema_column_exists(table('user_role_assignments'),'expires_at')
            || !schema_column_exists(table('user_role_assignments'),'last_switched_at')
            || !schema_column_exists(table('users'),'role_context_version')
            || (string) setting_get('system','controlled_active_roles_v0812_ready','0') !== '1'
            || (string) setting_get('system','deterministic_update_plan_v0812_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.0.0','>=') && (
            !schema_table_exists(table('system_hardening_runs'))
            || !schema_column_exists(table('system_migration_ledger'),'package_version')
            || !schema_column_exists(table('system_migration_ledger'),'attempt_count')
            || !schema_column_exists(table('system_migration_ledger'),'started_at')
            || !schema_column_exists(table('system_migration_ledger'),'completed_at')
            || !schema_column_exists(table('system_migration_ledger'),'execution_ms')
            || (string) setting_get('system','hardening_foundation_v0900_ready','0') !== '1'
            || (string) setting_get('system','deterministic_migration_ledger_v0900_ready','0') !== '1'
            || (string) setting_get('system','automated_test_foundation_v0900_ready','0') !== '1'
            || (string) setting_get('system','sast_ci_foundation_v0900_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.0.1','>=') && (
            (string) setting_get('system','hardening_tracker_v0901_ready','0') !== '1'
            || (string) setting_get('system','migration_recovery_v0901_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.0.2','>=') && (
            (string) setting_get('system','release_sanitization_v0902_ready','0') !== '1'
            || (string) setting_get('system','sensitive_permissions_fail_closed_v0902_ready','0') !== '1'
            || (string) setting_get('system','patient_portal_session_hardening_v0902_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.0.3','>=') && (
            !schema_table_exists(table('user_mfa_methods'))
            || !schema_column_exists(table('user_mfa_methods'),'secret_encrypted')
            || !schema_table_exists(table('user_mfa_recovery_codes'))
            || !schema_column_exists(table('user_mfa_recovery_codes'),'code_hash')
            || (string) setting_get('system','privileged_mfa_v0903_ready','0') !== '1'
            || (string) setting_get('system','staff_session_security_v0903_ready','0') !== '1'
            || (string) setting_get('system','service_staffing_bulk_scope_v0903_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.0.4','>=') && (
            !schema_table_exists(table('access_decision_logs'))
            || !schema_column_exists(table('access_decision_logs'),'decision')
            || !schema_column_exists(table('access_decision_logs'),'permission_code')
            || !schema_column_exists(table('access_decision_logs'),'route_key')
            || !schema_column_exists(table('access_decision_logs'),'created_at')
            || (string) setting_get('system','granular_patient_permissions_v0904_ready','0') !== '1'
            || (string) setting_get('system','global_search_minimum_necessary_v0904_ready','0') !== '1'
            || (string) setting_get('system','independent_approval_guard_v0904_ready','0') !== '1'
            || (string) setting_get('system','denied_access_logging_v0904_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.0.5','>=') && (
            !schema_table_exists(table('administrative_break_glass_sessions'))
            || !schema_column_exists(table('administrative_break_glass_sessions'),'scope_permissions_json')
            || !schema_column_exists(table('administrative_break_glass_sessions'),'review_due_at')
            || !schema_table_exists(table('administrative_break_glass_events'))
            || !schema_table_exists(table('administrative_break_glass_notifications'))
            || (string) setting_get('system','controlled_break_glass_v0905_ready','0') !== '1'
            || (string) setting_get('system','superuser_task_isolation_v0905_ready','0') !== '1'
            || (string) setting_get('system','hard_schedule_conflicts_unbypassable_v0905_ready','0') !== '1'
            || (string) setting_get('system','break_glass_review_required_v0905_ready','0') !== '1'
        
        ))
        || (version_compare(app_release_version(),'0.9.0.6','>=') && (
            !schema_column_exists(table('audit_logs'),'chain_key')
            || !schema_column_exists(table('audit_logs'),'chain_sequence')
            || !schema_column_exists(table('audit_logs'),'previous_hash')
            || !schema_column_exists(table('audit_logs'),'integrity_version')
            || !schema_table_exists(table('audit_chain_heads'))
            || !schema_table_exists(table('audit_integrity_snapshots'))
            || !schema_table_exists(table('privacy_processing_activities'))
            || !schema_table_exists(table('privacy_rights_requests'))
            || !schema_table_exists(table('privacy_incidents'))
            || !schema_table_exists(table('privacy_retention_rules'))
            || (string) setting_get('system','tamper_evident_audit_chain_v0906_ready','0') !== '1'
            || (string) setting_get('system','privacy_governance_center_v0906_ready','0') !== '1'
            || (string) setting_get('system','privacy_incident_rights_register_v0906_ready','0') !== '1'
            || (string) setting_get('system','audit_integrity_snapshot_v0906_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.0.7','>=') && (
            !schema_table_exists(table('patient_allergies'))
            || !schema_column_exists(table('patient_allergies'),'normalized_substance')
            || !schema_table_exists(table('patient_medications'))
            || !schema_column_exists(table('patient_medications'),'normalized_medication')
            || !schema_table_exists(table('patient_conditions'))
            || !schema_column_exists(table('patient_conditions'),'normalized_condition')
            || !schema_table_exists(table('medicine_safety_profiles'))
            || !schema_column_exists(table('medicine_safety_profiles'),'evidence_status')
            || !schema_table_exists(table('prescription_safety_assessments'))
            || !schema_table_exists(table('prescription_safety_findings'))
            || !schema_table_exists(table('clinical_safety_events'))
            || !schema_column_exists(table('prescriptions'),'safety_assessment_id')
            || !schema_column_exists(table('prescriptions'),'safety_status')
            || !schema_column_exists(table('prescriptions'),'safety_override_reason')
            || (string) setting_get('system','structured_clinical_safety_v0907_ready','0') !== '1'
            || (string) setting_get('system','medication_safety_review_v0907_ready','0') !== '1'
            || (string) setting_get('system','prescription_hard_stop_v0907_ready','0') !== '1'
            || (string) setting_get('system','clinical_safety_events_v0907_ready','0') !== '1'
        
        ))
        || (version_compare(app_release_version(),'0.9.0.8','>=') && (
            (string) setting_get('system','patient_context_isolation_v0908_ready','0') !== '1'
            || (string) setting_get('system','completed_visit_context_cleanup_v0908_ready','0') !== '1'
            || (string) setting_get('system','dental_scope_labels_v0908_ready','0') !== '1'
            || (string) setting_get('system','clinical_scope','') !== 'dental_facility_only'
        ))
        || (version_compare(app_release_version(),'0.9.0.9','>=') && (
            !schema_column_exists(table('users'),'employee_no')
            || !schema_column_exists(table('users'),'job_title')
            || (string) setting_get('system','profile_center_v0909_ready','0') !== '1'
            || (string) setting_get('system','staff_identity_single_source_v0909_ready','0') !== '1'
            || (string) setting_get('system','staff_work_assignment_boundary_v0909_ready','0') !== '1'
            || (string) setting_get('system','dentist_professional_record_boundary_v0909_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.1.0','>=') && (
            !schema_table_exists(table('dental_safety_assessments'))
            || !schema_column_exists(table('clinical_encounters'),'dental_safety_assessment_id')
            || !schema_column_exists(table('vital_signs'),'queue_entry_id')
            || !schema_column_exists(table('vital_signs'),'dental_safety_assessment_id')
            || (string) setting_get('system','first_seating_dental_safety_v0910_ready','0') !== '1'
            || (string) setting_get('system','task_context_isolation_v0910_ready','0') !== '1'
            || (string) setting_get('system','multi_role_task_switch_v0910_ready','0') !== '1'
            || (string) setting_get('system','treatment_requires_first_seating_v0910_ready','0') !== '1'
            || (string) setting_get('system','general_health_services_disabled_v0910_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.1.1','>=') && (
            !schema_column_exists(table('patient_allergies'),'canonical_key')
            || !schema_column_exists(table('patient_medications'),'canonical_key')
            || !schema_column_exists(table('patient_conditions'),'canonical_key')
            || !dental_update_index_exists($pdo,table('patient_allergies'),'uq_patient_allergy_current_key')
            || !dental_update_index_exists($pdo,table('patient_medications'),'uq_patient_med_current_key')
            || !dental_update_index_exists($pdo,table('patient_conditions'),'uq_patient_condition_current_key')
            || !dental_update_index_exists($pdo,table('print_logs'),'idx_print_user_time')
            || !dental_update_index_exists($pdo,table('print_logs'),'idx_print_branch_time')
            || (string) setting_get('system','allergy_canonicalization_v0911_ready','0') !== '1'
            || (string) setting_get('system','dental_safety_timeline_v0911_ready','0') !== '1'
            || (string) setting_get('system','print_activity_v0911_ready','0') !== '1'
            || (string) setting_get('system','cross_session_update_status_v0911_ready','0') !== '1'
        
        ))
        || (version_compare(app_release_version(),'0.9.1.2','>=') && (
            !schema_table_exists(table('patient_record_releases'))
            || !schema_column_exists(table('patient_portal_accounts'),'preferred_channel')
            || !schema_column_exists(table('patient_portal_accounts'),'appointment_notifications')
            || !schema_column_exists(table('patient_portal_accounts'),'record_notifications')
            || !dental_update_index_exists($pdo,table('patient_record_releases'),'uq_patient_record_release_request')
            || !dental_update_index_exists($pdo,table('patient_record_releases'),'idx_patient_record_release_patient')
            || (string) setting_get('system','patient_portal_sidebar_v0912_ready','0') !== '1'
            || (string) setting_get('system','controlled_record_release_v0912_ready','0') !== '1'
            || (string) setting_get('system','patient_safe_record_copy_v0912_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.1.3','>=') && (
            (string) setting_get('system','navigation_cycle_guard_v0913_ready','0') !== '1'
            || (string) setting_get('system','route_role_integrity_v0913_ready','0') !== '1'
            || (string) setting_get('system','workspace_recovery_v0913_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.1.4','>=') && (
            !schema_table_exists(table('operation_requests'))
            || !dental_update_index_exists($pdo,table('operation_requests'),'uq_operation_dedupe_key')
            || !dental_update_index_exists($pdo,table('operation_requests'),'idx_operation_status_expiry')
            || (string) setting_get('system','transaction_integrity_v0914_ready','0') !== '1'
            || (string) setting_get('system','duplicate_submission_guard_v0914_ready','0') !== '1'
            || (string) setting_get('system','workflow_concurrency_monitor_v0914_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.2.0','>=') && (
            (string) setting_get('system','unified_update_state_v0920_ready','0') !== '1'
            || (string) setting_get('system','machine_release_manifest_v0920_ready','0') !== '1'
            || (string) setting_get('system','stable_update_button_v0920_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.3.0','>=') && (
            !schema_table_exists(table('operational_health_snapshots'))
            || !dental_update_index_exists($pdo,table('operational_health_snapshots'),'idx_operational_health_created')
            || !dental_update_index_exists($pdo,table('operational_health_snapshots'),'idx_operational_health_status')
            || (string) setting_get('system','operational_health_v0930_ready','0') !== '1'
            || (string) setting_get('system','system_recovery_sidebar_v0930_ready','0') !== '1'
            || (string) setting_get('system','nonblocking_health_gate_v0930_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'0.9.4.0','>=') && (
            !is_file(DENTAL_ROOT . '/app/query_integrity.php')
            || (string) setting_get('system','query_integrity_v0940_ready','0') !== '1'
            || (string) setting_get('system','joined_query_alias_guard_v0940_ready','0') !== '1'
            || (string) setting_get('system','release_query_regression_gate_v0940_ready','0') !== '1'
        ))
        || (version_compare(app_release_version(),'1.0.0.0','>=') && (
            !is_file(DENTAL_ROOT . '/app/data_integrity.php')
            || !schema_table_exists(table('data_integrity_snapshots'))
            || !dental_update_index_exists($pdo,table('data_integrity_snapshots'),'idx_data_integrity_created')
            || !dental_update_index_exists($pdo,table('data_integrity_snapshots'),'idx_data_integrity_status')
            || !dental_update_index_exists($pdo,table('operation_requests'),'idx_operation_cleanup')
            || (string) setting_get('system','production_data_integrity_v1000_ready','0') !== '1'
            || (string) setting_get('system','database_contract_v1000_ready','0') !== '1'
            || (string) setting_get('system','integrity_evidence_v1000_ready','0') !== '1'
            || (string) setting_get('system','request_ledger_maintenance_v1000_ready','0') !== '1'
        ));
}

function dental_update_recovery_summary(PDO $pdo, ?string $releaseVersion = null): array
{
    $releaseVersion = $releaseVersion ?: app_release_version();

    if (version_compare($releaseVersion, '0.8.1.2', '>=')
        && version_compare(installed_application_version(), $releaseVersion, '>=')) {
        $schemaDrift = dental_update_schema_drift_detected($pdo);
        $terminalReady = dental_update_terminal_apply_ready($pdo, $releaseVersion);
        $applyMap = dental_update_release_apply_map();
        $missingFunctions = [];
        if (isset($applyMap[$releaseVersion]) && !function_exists($applyMap[$releaseVersion])) {
            $missingFunctions[$releaseVersion] = $applyMap[$releaseVersion];
        }
        $ledgerOnly = version_compare($releaseVersion, '0.9.0.0', '>=');
        return [
            'code_version' => $releaseVersion,
            'database_version' => installed_application_version(),
            'missing_versions' => [],
            'missing_apply_functions' => $missingFunctions,
            'schema_drift_detected' => $schemaDrift,
            'readiness' => [
                'groups' => 1,
                'ready' => $ledgerOnly || !$schemaDrift ? 1 : 0,
                'missing' => $ledgerOnly || !$schemaDrift ? 0 : 1,
            ],
            'ready' => !$missingFunctions && $terminalReady && ($ledgerOnly || !$schemaDrift),
        ];
    }

    $chain = dental_update_apply_chain_status($releaseVersion);
    $missingFunctions = [];
    foreach ($chain as $version => $row) {
        if (empty($row['available'])) $missingFunctions[$version] = $row['function'];
    }
    $readiness = dental_update_readiness_summary($pdo);
    return [
        'code_version' => $releaseVersion,
        'database_version' => installed_application_version(),
        'missing_versions' => dental_update_missing_versions($pdo, $releaseVersion),
        'missing_apply_functions' => $missingFunctions,
        'schema_drift_detected' => dental_update_schema_drift_detected($pdo),
        'readiness' => $readiness,
        'ready' => empty($missingFunctions) && empty(dental_update_missing_versions($pdo, $releaseVersion)) && !$readiness['missing'] && !dental_update_schema_drift_detected($pdo),
    ];
}


function dental_update_terminal_apply_ready(PDO $pdo, string $releaseVersion): bool
{
    $installed = installed_application_version();
    if (version_compare($installed, $releaseVersion, '<')) return false;
    $map = dental_update_release_apply_map();
    if (isset($map[$releaseVersion]) && !function_exists($map[$releaseVersion])) return false;

    // v0.9+ is deterministic-ledger only. The machine manifest is loaded from
    // config, but a completed exact-version ledger row remains a safe fallback
    // when optional client documentation has been removed.
    if (version_compare($releaseVersion, '0.9.0.0', '>=')) {
        $ledger = table('system_migration_ledger');
        if (!dental_update_table_exists($pdo, $ledger)) return false;

        $manifest = function_exists('dental_hardening_release_manifest')
            ? dental_hardening_release_manifest()
            : [];
        $migrationKey = trim((string)($manifest['migration_key'] ?? ''));
        $expectedChecksum = strtolower(trim((string)($manifest['migration_checksum_sha256'] ?? '')));

        try {
            if ($migrationKey !== '') {
                $stmt = $pdo->prepare('SELECT status,checksum_sha256 FROM `' . $ledger . '` WHERE migration_key=? LIMIT 1');
                $stmt->execute([$migrationKey]);
            } else {
                $stmt = $pdo->prepare('SELECT status,checksum_sha256 FROM `' . $ledger . '` WHERE version=? ORDER BY completed_at DESC,id DESC LIMIT 1');
                $stmt->execute([$releaseVersion]);
            }
            $row = $stmt->fetch();
            if (!$row || strtolower((string)($row['status'] ?? '')) !== 'completed') return false;
            $storedChecksum = strtolower(trim((string)($row['checksum_sha256'] ?? '')));
            if ($expectedChecksum !== '' && $storedChecksum !== '') {
                $compatible = function_exists('dental_hardening_migration_checksum_is_officially_compatible')
                    ? dental_hardening_migration_checksum_is_officially_compatible($migrationKey, $storedChecksum, $expectedChecksum)
                    : hash_equals($storedChecksum, $expectedChecksum);
                if (!$compatible) return false;
            }
            return true;
        } catch (Throwable) {
            return false;
        }
    }

    if (version_compare($releaseVersion, '0.8.1.2', '>=')) {
        $ledger = table('system_migration_ledger');
        if (dental_update_table_exists($pdo, $ledger)) {
            try {
                $stmt = $pdo->prepare('SELECT 1 FROM `' . $ledger . '` WHERE version=? AND status="completed" LIMIT 1');
                $stmt->execute([$releaseVersion]);
                if ((bool)$stmt->fetchColumn()) return true;
            } catch (Throwable) {
                // Fall through to the install-history compatibility record.
            }
        }
    }

    $history = array_flip(dental_update_history_versions($pdo));
    return isset($history[$releaseVersion]);
}

function dental_update_pending_reason_rows(PDO $pdo, string $releaseVersion): array
{
    $summary = dental_update_recovery_summary($pdo, $releaseVersion);
    $rows = [];
    if (version_compare(installed_application_version(), $releaseVersion, '<')) {
        $rows[] = ['label' => 'Database version marker', 'status' => 'Package files are newer than the recorded database version.'];
    }
    if (!empty($summary['missing_versions'])) {
        $rows[] = ['label' => 'Skipped update history', 'status' => implode(' → ', $summary['missing_versions'])];
    }
    if (!empty($summary['missing_apply_functions'])) {
        $rows[] = ['label' => 'Missing apply functions', 'status' => implode(', ', array_values($summary['missing_apply_functions']))];
    }
    if (!empty($summary['schema_drift_detected']) && version_compare($releaseVersion, '0.9.0.0', '<')) {
        $rows[] = ['label' => 'Schema drift', 'status' => 'One or more required safe tables/columns are missing.'];
    }
    if (!empty($summary['readiness']['missing'])) {
        $rows[] = ['label' => 'Database readiness', 'status' => (string)$summary['readiness']['missing'] . ' required readiness checks still missing.'];
    }
    if (!dental_update_terminal_apply_ready($pdo, $releaseVersion)) {
        $rows[] = ['label' => 'Terminal apply record', 'status' => 'The latest update marker/history does not yet prove the chain reached ' . $releaseVersion . '.'];
    }
    return $rows;
}

function dental_update_repair_skipped_foundations(PDO $pdo): array
{
    $repaired = [];

    if (!dental_update_table_exists($pdo, table('cashier_transaction_locks'))
        || !dental_update_column_exists($pdo, table('invoices'), 'flow_state')
        || !dental_update_column_exists($pdo, table('payments'), 'reference_required')
        || !dental_update_column_exists($pdo, table('cashier_sessions'), 'active_invoice_id')) {
        dental_update_safety_apply_v059834($pdo);
        $repaired[] = '0.5.9.8.34 transaction foundation';
    }

    if (!dental_update_safety_v059836_ready($pdo)) {
        dental_update_safety_apply_v059836($pdo);
        $repaired[] = '0.5.9.8.36 treatment-to-billing handoff foundation';
    }

    if (dental_update_table_exists($pdo, table('cashier_sessions'))) {
        dental_update_add_column($pdo, table('cashier_sessions'), 'active_invoice_id', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('cashier_sessions'), 'transaction_mode', "VARCHAR(40) NOT NULL DEFAULT 'patient_visit'");
        dental_update_add_column($pdo, table('cashier_sessions'), 'lock_started_at', 'DATETIME NULL');
    }

    if (dental_update_table_exists($pdo, table('cashier_checkouts'))) {
        dental_update_add_column($pdo, table('cashier_checkouts'), 'checkout_type', "VARCHAR(40) NOT NULL DEFAULT 'settled'");
        try {
            $pdo->exec('ALTER TABLE `' . table('cashier_checkouts') . '` MODIFY `checkout_type` VARCHAR(40) NOT NULL DEFAULT "settled"');
        } catch (Throwable $exception) {
            error_log('Skipped-update recovery checkout_type compatibility: ' . $exception->getMessage());
        }
    }

    if (!dental_update_table_exists($pdo, table('appointment_assignment_queue'))
        || !dental_update_table_exists($pdo, table('appointment_safety_events'))
        || !dental_update_column_exists($pdo, table('appointments'), 'assignment_status')
        || !dental_update_column_exists($pdo, table('appointment_requests'), 'assignment_queue_status')) {
        dental_update_execute_migration($pdo, DENTAL_ROOT . '/database/migrations/0.5.9.8.44_appointment_schedule_final_guard.sql');
        dental_update_add_column($pdo, table('appointments'), 'assignment_status', "ENUM('not_required','needs_assignment','assigned','blocked') NOT NULL DEFAULT 'assigned'");
        dental_update_add_column($pdo, table('appointments'), 'assignment_note', 'VARCHAR(500) NULL');
        dental_update_add_column($pdo, table('appointments'), 'assignment_requested_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('appointments'), 'assignment_resolved_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('appointments'), 'schedule_guard_status', "ENUM('unchecked','passed','conflict_blocked','bypass_approved') NOT NULL DEFAULT 'unchecked'");
        dental_update_add_column($pdo, table('appointments'), 'schedule_guard_checked_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('appointments'), 'schedule_guard_message', 'VARCHAR(500) NULL');
        dental_update_add_column($pdo, table('appointments'), 'no_show_marked_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('appointment_requests'), 'assignment_queue_status', "ENUM('not_required','pending','resolved') NOT NULL DEFAULT 'not_required'");
        dental_update_add_column($pdo, table('appointment_requests'), 'assignment_queue_note', 'VARCHAR(500) NULL');
        dental_update_add_index($pdo, table('appointments'), 'idx_appointment_assignment_guard', '(`branch_id`,`assignment_status`,`start_at`)');
        dental_update_add_index($pdo, table('appointments'), 'idx_appointment_schedule_guard', '(`branch_id`,`schedule_guard_status`,`start_at`)');
        dental_update_add_index($pdo, table('appointment_requests'), 'idx_request_assignment_queue', '(`assignment_queue_status`,`status`,`preferred_date`)');
        $repaired[] = '0.5.9.8.44 appointment schedule final guard';
    }



    if (!dental_update_table_exists($pdo, table('clinical_record_completion_reviews'))
        || !dental_update_table_exists($pdo, table('procedure_note_templates'))
        || !dental_update_table_exists($pdo, table('treatment_plan_signatures'))
        || !dental_update_column_exists($pdo, table('clinical_encounters'), 'record_completion_status')
        || !dental_update_column_exists($pdo, table('documents'), 'clinical_significance')) {
        dental_update_execute_migration($pdo, DENTAL_ROOT . '/database/migrations/0.5.9.8.45_clinical_record_completion.sql');
        dental_update_add_column($pdo, table('clinical_encounters'), 'record_completion_status', "ENUM('unchecked','needs_review','complete','locked') NOT NULL DEFAULT 'unchecked'");
        dental_update_add_column($pdo, table('clinical_encounters'), 'record_completion_checked_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('clinical_encounters'), 'record_completion_missing_json', 'LONGTEXT NULL');
        dental_update_add_column($pdo, table('documents'), 'clinical_significance', "ENUM('supporting','diagnostic','pre_op','post_op','consent_evidence','external_record') NOT NULL DEFAULT 'supporting'");
        dental_update_add_column($pdo, table('documents'), 'captured_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('documents'), 'review_status', "ENUM('uploaded','reviewed','needs_retake','archived') NOT NULL DEFAULT 'uploaded'");
        dental_update_add_column($pdo, table('documents'), 'review_note', 'TEXT NULL');
        if (dental_update_table_exists($pdo, table('procedures'))) {
            dental_update_add_column($pdo, table('procedures'), 'note_template_id', 'BIGINT UNSIGNED NULL');
            dental_update_add_column($pdo, table('procedures'), 'procedure_note_quality', "ENUM('unchecked','template_used','free_text_complete','needs_detail') NOT NULL DEFAULT 'unchecked'");
        }
        $repaired[] = '0.5.9.8.45 clinical record completion foundation';
    }


    if (!dental_update_table_exists($pdo, table('procedure_supply_templates'))
        || !dental_update_table_exists($pdo, table('clinical_supply_usage_audit'))
        || !dental_update_table_exists($pdo, table('inventory_safety_events'))
        || !dental_update_table_exists($pdo, table('sterilization_release_reviews'))
        || !dental_update_column_exists($pdo, table('inventory_items'), 'is_controlled_supply')
        || !dental_update_column_exists($pdo, table('inventory_lots'), 'quarantine_reason')
        || !dental_update_column_exists($pdo, table('inventory_documents'), 'clinical_consumption_review_status')
        || !dental_update_column_exists($pdo, table('sterilization_batches'), 'release_review_status')
        || !dental_update_column_exists($pdo, table('instrument_usage'), 'usage_status')
        || !dental_update_column_exists($pdo, table('sterilization_equipment'), 'maintenance_lock_reason')) {
        dental_update_execute_migration($pdo, DENTAL_ROOT . '/database/migrations/0.5.9.8.46_inventory_sterilization_supply_completion.sql');
        dental_update_add_column($pdo, table('inventory_items'), 'is_controlled_supply', 'TINYINT(1) NOT NULL DEFAULT 1');
        dental_update_add_column($pdo, table('inventory_items'), 'requires_lot_for_issue', 'TINYINT(1) NOT NULL DEFAULT 1');
        dental_update_add_column($pdo, table('inventory_items'), 'requires_expiry_for_receipt', 'TINYINT(1) NOT NULL DEFAULT 0');
        dental_update_add_column($pdo, table('inventory_items'), 'default_usage_policy', "VARCHAR(40) NOT NULL DEFAULT 'manual_issue'");
        dental_update_add_column($pdo, table('inventory_items'), 'sterile_item_policy', "VARCHAR(40) NOT NULL DEFAULT 'not_applicable'");
        dental_update_add_column($pdo, table('inventory_lots'), 'quarantine_reason', 'VARCHAR(500) NULL');
        dental_update_add_column($pdo, table('inventory_lots'), 'quarantined_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('inventory_lots'), 'quarantined_by', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('inventory_lots'), 'last_safety_checked_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('inventory_documents'), 'clinical_consumption_review_status', "VARCHAR(40) NOT NULL DEFAULT 'not_required'");
        dental_update_add_column($pdo, table('inventory_documents'), 'clinical_consumption_reviewed_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('inventory_documents'), 'clinical_consumption_reviewed_by', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('inventory_documents'), 'procedure_supply_template_id', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('sterilization_batches'), 'release_review_status', "VARCHAR(40) NOT NULL DEFAULT 'pending'");
        dental_update_add_column($pdo, table('sterilization_batches'), 'release_review_id', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('sterilization_batches'), 'release_block_reason', 'VARCHAR(500) NULL');
        dental_update_add_column($pdo, table('sterilization_batches'), 'biological_result_due_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('instrument_sets'), 'last_release_review_id', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('instrument_sets'), 'quarantine_reason', 'VARCHAR(500) NULL');
        dental_update_add_column($pdo, table('instrument_sets'), 'last_patient_use_id', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('instrument_sets'), 'next_inspection_due_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('instrument_usage'), 'chair_id', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('instrument_usage'), 'usage_status', "VARCHAR(40) NOT NULL DEFAULT 'assigned'");
        dental_update_add_column($pdo, table('instrument_usage'), 'return_condition', 'VARCHAR(120) NULL');
        dental_update_add_column($pdo, table('instrument_usage'), 'decontamination_received_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('instrument_usage'), 'decontamination_received_by', 'BIGINT UNSIGNED NULL');
        dental_update_add_column($pdo, table('sterilization_equipment'), 'maintenance_lock_reason', 'VARCHAR(500) NULL');
        dental_update_add_column($pdo, table('sterilization_equipment'), 'service_status_checked_at', 'DATETIME NULL');
        dental_update_add_column($pdo, table('sterilization_equipment'), 'service_status', "VARCHAR(40) NOT NULL DEFAULT 'unknown'");
        $repaired[] = '0.5.9.8.46 inventory, sterilization, and supply completion foundation';
    }


    if (!dental_update_table_exists($pdo, table('chair_session_assignments'))
        || !dental_update_table_exists($pdo, table('chair_status_events'))
        || !dental_update_table_exists($pdo, table('chair_visit_movements'))
        || !dental_update_column_exists($pdo, table('clinic_chairs'), 'resource_kind')
        || !dental_update_column_exists($pdo, table('clinic_chairs'), 'default_provider_user_id')
        || !dental_update_column_exists($pdo, table('doctor_chair_assignments'), 'assignment_type')) {
        dental_update_execute_migration($pdo, DENTAL_ROOT . '/database/migrations/0.5.9.8.47_chair_resource_operatory_flow.sql');
        if (dental_update_table_exists($pdo, table('clinic_chairs'))) {
            try { $pdo->exec('ALTER TABLE `' . table('clinic_chairs') . '` MODIFY `usage_mode` VARCHAR(40) NOT NULL DEFAULT "operatory"'); } catch (Throwable $exception) { error_log('Chair usage_mode compatibility skipped: ' . $exception->getMessage()); }
            dental_update_add_column($pdo, table('clinic_chairs'), 'resource_kind', "VARCHAR(40) NOT NULL DEFAULT 'operatory'");
            dental_update_add_column($pdo, table('clinic_chairs'), 'default_provider_user_id', 'BIGINT UNSIGNED NULL');
            dental_update_add_column($pdo, table('clinic_chairs'), 'service_scope_json', 'LONGTEXT NULL');
            dental_update_add_column($pdo, table('clinic_chairs'), 'current_status_note', 'VARCHAR(500) NULL');
            dental_update_add_column($pdo, table('clinic_chairs'), 'last_released_at', 'DATETIME NULL');
            dental_update_add_index($pdo, table('clinic_chairs'), 'idx_chair_resource_kind_status', '(`branch_id`,`resource_kind`,`status`)');
            dental_update_add_index($pdo, table('clinic_chairs'), 'idx_chair_default_provider', '(`branch_id`,`default_provider_user_id`,`status`)');
            try { $pdo->exec('UPDATE `' . table('clinic_chairs') . '` SET resource_kind=CASE WHEN usage_mode="shared_inhouse" THEN "shared_inhouse" WHEN usage_mode="dentist_bound" THEN "operatory" ELSE usage_mode END WHERE resource_kind="operatory" OR resource_kind IS NULL'); } catch (Throwable) {}
        }
        if (dental_update_table_exists($pdo, table('doctor_chair_assignments'))) {
            dental_update_add_column($pdo, table('doctor_chair_assignments'), 'assignment_type', "VARCHAR(40) NOT NULL DEFAULT 'default_provider'");
            dental_update_add_column($pdo, table('doctor_chair_assignments'), 'starts_at', 'DATETIME NULL');
            dental_update_add_column($pdo, table('doctor_chair_assignments'), 'ends_at', 'DATETIME NULL');
            dental_update_add_column($pdo, table('doctor_chair_assignments'), 'notes', 'VARCHAR(500) NULL');
        }
        $repaired[] = '0.5.9.8.47 chair resource and operatory flow foundation';
    }

    return $repaired;
}

