<?php

declare(strict_types=1);

require_once dirname(__DIR__) . '/bootstrap.php';

if (!isset($GLOBALS['dental_request_started_at'])) {
    $GLOBALS['dental_request_started_at'] = (float) ($_SERVER['REQUEST_TIME_FLOAT'] ?? microtime(true));
}
if (!isset($GLOBALS['dental_request_reference'])) {
    try {
        $GLOBALS['dental_request_reference'] = strtoupper(substr(bin2hex(random_bytes(8)), 0, 12));
    } catch (Throwable) {
        $GLOBALS['dental_request_reference'] = strtoupper(substr(hash('sha256', microtime(true) . '|' . getmypid()), 0, 12));
    }
}

function dental_request_reference(): string
{
    return (string) ($GLOBALS['dental_request_reference'] ?? 'UNKNOWN');
}

function dental_route_slug_map(): array
{
    static $slugs = null;
    if ($slugs !== null) return $slugs;
    $file = DENTAL_ROOT . '/config/route_slugs.php';
    $loaded = is_file($file) ? require $file : [];
    return $slugs = is_array($loaded) ? $loaded : [];
}

function dental_route_to_slug(string $route): string
{
    $route = trim($route);
    if ($route === '') return '';
    $slugs = dental_route_slug_map();
    return (string) ($slugs[$route] ?? str_replace('_', '-', $route));
}

function dental_slug_to_route(string $slug): string
{
    $slug = trim($slug, '/');
    if ($slug === '') return '';
    $slugs = dental_route_slug_map();
    $route = array_search($slug, $slugs, true);
    return is_string($route) ? $route : str_replace('-', '_', $slug);
}

function dental_clean_url(string $url): string
{
    $url = trim($url);
    if ($url === '' || preg_match('#^(?:https?:)?//#i', $url) || str_starts_with($url, '#') || str_starts_with($url, 'mailto:') || str_starts_with($url, 'tel:')) {
        return $url;
    }

    $parts = parse_url($url);
    if ($parts === false) return $url;

    $path = (string) ($parts['path'] ?? '');
    $query = (string) ($parts['query'] ?? '');
    $fragment = (string) ($parts['fragment'] ?? '');

    if (preg_match('/^([A-Za-z0-9_-]+)\.php$/', basename($path), $match)) {
        $route = $match[1];
        $slugs = dental_route_slug_map();
        if (array_key_exists($route, $slugs)) {
            $slug = dental_route_to_slug($route);
            if ($slug !== '') {
                $prefix = trim(dirname($path), '.\/');
                $path = ($prefix !== '' ? $prefix . '/' : '') . $slug;
            }
        }
    }

    return $path . ($query !== '' ? '?' . $query : '') . ($fragment !== '' ? '#' . $fragment : '');
}

function dental_request_script(): string
{
    $route = trim((string) ($GLOBALS['DENTAL_ROUTE'] ?? ''));
    return $route !== '' ? $route . '.php' : basename((string) ($_SERVER['SCRIPT_NAME'] ?? ''));
}

function dental_request_slug(): string
{
    $route = trim((string) ($GLOBALS['DENTAL_ROUTE'] ?? ''));
    if ($route !== '') return dental_route_to_slug($route);

    $path = trim((string) parse_url((string) ($_SERVER['REQUEST_URI'] ?? ''), PHP_URL_PATH), '/');
    $leaf = basename($path);
    if (str_ends_with($leaf, '.php')) {
        return dental_route_to_slug(substr($leaf, 0, -4));
    }
    return $leaf;
}

$installationState = dental_installation_state();
if (!($installationState['installed'] ?? false)) {
    if (!in_array(dental_request_script(), ['install.php','license.php'], true)) {
        $destination = ($installationState['recovery_required'] ?? false)
            ? 'install.php?recovery=1'
            : 'install.php';
        header('Location: ' . $destination);
        exit;
    }
}

$config = dental_config();
date_default_timezone_set((string) ($config['app']['timezone'] ?? 'Asia/Manila'));

$licenseScript = dental_request_script();
$licenseExempt = ['license.php', 'install.php'];
$licenseStatus = dental_license_status();
if (!in_array($licenseScript, $licenseExempt, true) && !($licenseStatus['valid'] ?? false)) {
    header('Location: license.php');
    exit;
}

if (!headers_sent()) {
    header('X-Content-Type-Options: nosniff');
    header('X-Frame-Options: SAMEORIGIN');
    header('Referrer-Policy: strict-origin-when-cross-origin');
    header('Permissions-Policy: camera=(), microphone=(), geolocation=()');
    header('X-DentalCare-Request-Id: ' . dental_request_reference());
}

if (session_status() !== PHP_SESSION_ACTIVE) {
    $forceHttps = (bool) ($config['security']['force_https'] ?? false);
    $isHttps = !empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off';

    // cPanel/CloudLinux installations commonly share PHP's system session
    // directory. When that partition or its inode pool fills, session_start()
    // fails and POST actions lose their CSRF/session state after saving. Keep
    // DentalCare sessions in the application's protected writable storage so
    // clinical transactions do not depend on the shared host session volume.
    if (strtolower((string) ini_get('session.save_handler')) === 'files') {
        $localSessionPath = DENTAL_ROOT . '/storage/runtime/sessions';
        if ((!is_dir($localSessionPath) && @mkdir($localSessionPath, 0750, true))
            || (is_dir($localSessionPath) && is_writable($localSessionPath))) {
            @chmod($localSessionPath, 0750);
            session_save_path($localSessionPath);
            // Ensure abandoned local session files are periodically removed
            // even on hosts that disable the global session garbage collector.
            @ini_set('session.gc_probability', '1');
            @ini_set('session.gc_divisor', '100');
            @ini_set('session.gc_maxlifetime', (string) max(3600, (int) ini_get('session.gc_maxlifetime')));
        }
    }

    session_set_cookie_params([
        'httponly' => true,
        'secure' => $forceHttps || $isHttps,
        'samesite' => 'Lax',
        'path' => '/',
    ]);
    if (!@session_start()) {
        $sessionPath = (string) session_save_path();
        error_log('DentalCare could not start a secure session. Session path: ' . ($sessionPath !== '' ? $sessionPath : '[PHP default]'));
        http_response_code(500);
        echo '<!doctype html><html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Session storage unavailable</title></head><body><main><h1>Session storage unavailable</h1><p>DentalCare could not start a secure session. Check that <code>storage/runtime/sessions</code> is writable and that the hosting account has free disk space and inodes.</p></main></body></html>';
        exit;
    }
}

// v0.9.1.3 navigation circuit breaker. It records only server redirect edges
// and interrupts repeated cycles; ordinary refreshes and one-hop redirects are
// unaffected. The recovery page remains available even when Task View or
// patient context is stale.
if (PHP_SAPI !== 'cli' && is_file(__DIR__ . '/navigation_guard.php')) {
    require_once __DIR__ . '/navigation_guard.php';
    dental_navigation_guard_boot();
}

function dental_runtime_log_directory(): string
{
    $directory = DENTAL_ROOT . '/storage/runtime/logs';
    if (!is_dir($directory)) {
        @mkdir($directory, 0750, true);
    }
    return $directory;
}

function dental_runtime_write_log(string $message, ?string $reference = null): ?string
{
    $line = '[' . date('Y-m-d H:i:s T') . '] ' . ($reference !== null ? '[' . $reference . '] ' : '') . $message . PHP_EOL;
    $directory = dental_runtime_log_directory();
    $path = $directory . '/dentalcare-' . date('Y-m-d') . '.log';
    $written = is_dir($directory) && is_writable($directory)
        ? @file_put_contents($path, $line, FILE_APPEND | LOCK_EX)
        : false;
    error_log(rtrim($line));
    return $written === false ? null : $path;
}

function dental_request_route_label(): string
{
    $route = trim((string) ($GLOBALS['DENTAL_ROUTE'] ?? ''));
    if ($route !== '') return $route;
    $script = basename((string) ($_SERVER['SCRIPT_NAME'] ?? 'unknown'));
    return preg_replace('/\.php$/i', '', $script) ?: 'unknown';
}

function dental_record_request_performance(): void
{
    try {
        if (PHP_SAPI === 'cli') return;
        $started = (float) ($GLOBALS['dental_request_started_at'] ?? microtime(true));
        $elapsedMs = max(0.0, (microtime(true) - $started) * 1000);
        $statusCode = (int) http_response_code();
        if ($statusCode < 100) $statusCode = 200;
        if ($elapsedMs < 2500 && $statusCode < 500) return;

        $user = function_exists('current_user') ? (current_user() ?: []) : [];
        $record = [
            'recorded_at' => date(DATE_ATOM),
            'request_id' => dental_request_reference(),
            'route' => dental_request_route_label(),
            'method' => strtoupper((string) ($_SERVER['REQUEST_METHOD'] ?? 'GET')),
            'status_code' => $statusCode,
            'elapsed_ms' => round($elapsedMs, 1),
            'peak_memory_bytes' => memory_get_peak_usage(true),
            'user_id' => (int) ($user['id'] ?? 0),
            'actual_role' => function_exists('actual_role') ? actual_role() : '',
            'active_role' => function_exists('active_role') ? active_role() : '',
        ];
        $directory = dental_runtime_log_directory();
        if (!is_dir($directory) || !is_writable($directory)) return;
        @file_put_contents(
            $directory . '/performance-' . date('Y-m-d') . '.jsonl',
            json_encode($record, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) . PHP_EOL,
            FILE_APPEND | LOCK_EX
        );
    } catch (Throwable $exception) {
        error_log('DentalCare performance telemetry failure: ' . $exception->getMessage());
    }
}

if (PHP_SAPI !== 'cli') {
    register_shutdown_function(static function (): void {
        $last = error_get_last();
        if (!is_array($last) || !in_array((int)($last['type'] ?? 0), [E_ERROR,E_PARSE,E_CORE_ERROR,E_COMPILE_ERROR,E_USER_ERROR], true)) {
            return;
        }
        $reference = dental_request_reference();
        dental_runtime_write_log(
            'Fatal PHP error: ' . (string)($last['message'] ?? 'Unknown fatal error')
            . ' in ' . (string)($last['file'] ?? '[unknown file]')
            . ':' . (string)($last['line'] ?? 0),
            $reference
        );
        if (!headers_sent() && (int) ob_get_length() === 0) {
            http_response_code(500);
            header('Content-Type: text/html; charset=utf-8');
            $safeReference = htmlspecialchars($reference, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
            echo '<!doctype html><html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Request could not be completed</title><link rel="stylesheet" href="public/assets/css/app.css"></head><body class="public-page"><main class="login-shell"><section class="login-card"><h1>Request could not be completed</h1><div class="flash error">A server error interrupted this request. Reference: ' . $safeReference . '.</div><p>The technical error was saved in the protected DentalCare runtime log.</p><div class="actions"><a class="btn primary" href="dashboard.php">Dashboard</a></div></section></main></body></html>';
        }
    });

    set_exception_handler(static function (Throwable $exception): void {
        $reference = dental_request_reference();
        dental_runtime_write_log('DentalCare request failure: ' . $exception, $reference);
        $isValidation = $exception instanceof RuntimeException || $exception instanceof InvalidArgumentException;
        $status = $isValidation ? 422 : 500;
        http_response_code($status);
        $message = $isValidation ? $exception->getMessage() : 'The request could not be completed. Reference: ' . $reference . '.';
        $isJson = str_contains(strtolower((string) ($_SERVER['HTTP_ACCEPT'] ?? '')), 'application/json')
            || str_contains((string) ($_SERVER['SCRIPT_NAME'] ?? ''), '/api/');
        if ($isJson) {
            header('Content-Type: application/json; charset=utf-8');
            echo json_encode(['ok'=>false,'error'=>$message,'reference'=>$reference], JSON_UNESCAPED_SLASHES);
            return;
        }
        $safe = htmlspecialchars($message, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
        $back = htmlspecialchars((string) ($_SERVER['HTTP_REFERER'] ?? 'dashboard.php'), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
        echo '<!doctype html><html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Request could not be completed</title><link rel="stylesheet" href="public/assets/css/app.css"></head><body class="public-page"><main class="login-shell"><section class="login-card"><h1>Request could not be completed</h1><div class="flash error">' . $safe . '</div><p>Return to the previous page, review the information, and try again.</p><div class="actions"><a class="btn ghost" href="' . $back . '">Go back</a><a class="btn primary" href="dashboard.php">Dashboard</a></div></section></main></body></html>';
    });
}

if (PHP_SAPI !== 'cli') {
    register_shutdown_function('dental_record_request_performance');
}

function app_demo_read_only(): bool
{
    return function_exists('dental_demo_read_only') && dental_demo_read_only();
}

function app_orientation_mode_active(): bool
{
    if (app_demo_read_only()) {
        return true;
    }
    try {
        return (string) setting_get('system', 'orientation_mode_enabled', '0') === '1';
    } catch (Throwable) {
        return false;
    }
}

if (app_demo_read_only() && PHP_SAPI !== 'cli') {
    header('X-DentalCare-Mode: demo-read-only');
    $method = strtoupper((string) ($_SERVER['REQUEST_METHOD'] ?? 'GET'));
    $allowedMutationScripts = ['login.php','patient-login.php','role_switch.php'];
    if (!in_array($method, ['GET','HEAD','OPTIONS'], true)
        && !in_array(dental_request_script(), $allowedMutationScripts, true)) {
        throw new RuntimeException('This demonstration copy is permanently read-only. Adding, editing, deleting, importing, resetting, emailing, or processing records is disabled.');
    }
}

function app_config(?string $key = null, mixed $default = null): mixed
{
    static $cfg;
    if ($cfg === null) {
        $cfg = dental_config();
    }
    if ($key === null) {
        return $cfg;
    }

    $aliases = [
        'name' => 'app.name',
        'url' => 'app.url',
        'timezone' => 'app.timezone',
        'table_prefix' => 'database.prefix',
    ];
    $key = $aliases[$key] ?? $key;

    $value = $cfg;
    foreach (explode('.', $key) as $segment) {
        if (!is_array($value) || !array_key_exists($segment, $value)) {
            return $default;
        }
        $value = $value[$segment];
    }
    return $value;
}

function db(): PDO
{
    static $pdo;
    if ($pdo instanceof PDO) {
        return $pdo;
    }
    $cfg = dental_config();
    if (empty($cfg['database']['name'])) {
        throw new RuntimeException('The saved database configuration is unavailable. Do not reinstall. Restore config/generated.php or open install.php?recovery=1 for guidance.');
    }
    $pdo = dental_pdo($cfg);
    if (app_demo_read_only()) {
        // Enforce read-only behavior at the database session as a second layer
        // behind the request-method guard. This also blocks accidental writes
        // from GET handlers, audits, downloads, or future code paths.
        try {
            $pdo->exec('SET SESSION TRANSACTION READ ONLY');
        } catch (Throwable $exception) {
            throw new RuntimeException('The demo database session could not be locked read-only. Access is stopped to prevent any possible record change.', 0, $exception);
        }
    }
    return $pdo;
}

function table(string $name): string
{
    $prefix = (string) app_config('database.prefix', 'dent_');
    if (!preg_match('/^[A-Za-z][A-Za-z0-9_]{1,24}$/', $prefix)) {
        throw new RuntimeException('Invalid configured database table prefix.');
    }
    if (!preg_match('/^[A-Za-z][A-Za-z0-9_]*$/', $name)) {
        throw new InvalidArgumentException('Invalid table name.');
    }
    return $prefix . $name;
}


function schema_column_exists(string $tableName, string $column): bool
{
    static $cache = [];
    $key = $tableName . ':' . $column;
    if (array_key_exists($key, $cache)) return $cache[$key];
    try {
        $stmt = db()->prepare(
            'SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=? AND table_name=? AND column_name=?'
        );
        $stmt->execute([(string) app_config('database.name'), $tableName, $column]);
        return $cache[$key] = ((int) $stmt->fetchColumn() > 0);
    } catch (Throwable) {
        return $cache[$key] = false;
    }
}

function schema_table_exists(string $tableName): bool
{
    static $cache = [];
    if (array_key_exists($tableName, $cache)) return $cache[$tableName];
    try {
        $stmt = db()->prepare(
            'SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name=?'
        );
        $stmt->execute([(string) app_config('database.name'), $tableName]);
        return $cache[$tableName] = ((int) $stmt->fetchColumn() > 0);
    } catch (Throwable) {
        return $cache[$tableName] = false;
    }
}

function e(mixed $value): string
{
    return htmlspecialchars((string) $value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function csrf_token(): string
{
    if (empty($_SESSION['csrf'])) {
        $_SESSION['csrf'] = bin2hex(random_bytes(32));
    }
    return (string) $_SESSION['csrf'];
}

function csrf_request_token(): string
{
    // Accept the current field name and the former compatibility name. Both
    // values are still compared against the server-side session token.
    return (string) ($_POST['csrf'] ?? $_POST['csrf_token'] ?? '');
}

function csrf_request_is_valid(): bool
{
    $known = (string) ($_SESSION['csrf'] ?? '');
    $posted = csrf_request_token();
    return $known !== '' && $posted !== '' && hash_equals($known, $posted);
}

function dental_ini_bytes(string $value): int
{
    $value = trim($value);
    if ($value === '') return 0;
    $unit = strtolower(substr($value, -1));
    $number = (float) $value;
    return match ($unit) {
        'g' => (int) round($number * 1024 * 1024 * 1024),
        'm' => (int) round($number * 1024 * 1024),
        'k' => (int) round($number * 1024),
        default => (int) round($number),
    };
}

function dental_request_exceeds_post_limit(): bool
{
    $length = (int) ($_SERVER['CONTENT_LENGTH'] ?? 0);
    $limit = dental_ini_bytes((string) ini_get('post_max_size'));
    return $length > 0 && $limit > 0 && $length > $limit && empty($_POST);
}

function verify_csrf_or_redirect(string $location, string $context = 'request'): void
{
    if (dental_request_exceeds_post_limit()) {
        $limit = trim((string) ini_get('post_max_size'));
        flash('error', 'The ' . $context . ' was larger than the server request limit' . ($limit !== '' ? ' (' . $limit . ')' : '') . '. No backup or system record was changed. Choose a smaller package or increase PHP post_max_size and upload_max_filesize, then try again.');
        header('Location: ' . $location, true, 303);
        exit;
    }
    if (csrf_request_is_valid()) return;
    flash('error', 'The page security token expired or came from an older browser tab. No backup or system record was changed. The page has been refreshed with a new token; submit the action again.');
    header('Location: ' . $location, true, 303);
    exit;
}

function verify_csrf(): void
{
    if (!csrf_request_is_valid()) {
        http_response_code(419);
        exit('Invalid or expired request token. Please return to the previous page and try again.');
    }
}

// Backward-compatible alias for older modules and interrupted update steps.
// New code should call verify_csrf().
if (!function_exists('csrf_validate')) {
    function csrf_validate(): void
    {
        verify_csrf();
    }
}

function flash(string $type, string $message): void
{
    $type = trim($type) !== '' ? trim($type) : 'notice';
    $message = trim($message);
    if ($message === '') return;
    $items = isset($_SESSION['flash']) && is_array($_SESSION['flash']) ? $_SESSION['flash'] : [];
    foreach ($items as $item) {
        if ((string)($item['type'] ?? '') === $type && (string)($item['message'] ?? '') === $message) {
            return;
        }
    }
    $items[] = ['type' => $type, 'message' => $message];
    // Prevent background retries or redirect loops from flooding the next page.
    $_SESSION['flash'] = array_slice($items, -8);
}

function take_flashes(): array
{
    $items = $_SESSION['flash'] ?? [];
    unset($_SESSION['flash']);
    if (!is_array($items)) return [];
    $unique = [];
    $seen = [];
    foreach ($items as $item) {
        if (!is_array($item)) continue;
        $type = (string)($item['type'] ?? 'notice');
        $message = trim((string)($item['message'] ?? ''));
        if ($message === '') continue;
        $key = $type . "\0" . $message;
        if (isset($seen[$key])) continue;
        $seen[$key] = true;
        $unique[] = ['type'=>$type,'message'=>$message];
    }
    return array_slice($unique, -8);
}

function current_user(): ?array
{
    static $timeoutChecked = false;
    if (!isset($_SESSION['user']) || !is_array($_SESSION['user'])) {
        return null;
    }

    // Enforce the configurable inactivity timeout once per request. During a
    // database outage, retain the current session rather than signing staff out.
    if (!$timeoutChecked) {
        $timeoutChecked = true;
        try {
            $minutes = max(5, min(720, (int) setting_get('security', 'session_timeout_minutes', '30')));
            $lastActivity = (int) ($_SESSION['last_activity'] ?? time());
            if (time() - $lastActivity > ($minutes * 60)) {
                unset($_SESSION['user'], $_SESSION['last_activity']);
                flash('error', 'Your session expired due to inactivity. Please sign in again.');
                return null;
            }
            $_SESSION['last_activity'] = time();
        } catch (Throwable) {
            $_SESSION['last_activity'] = time();
        }
        if (function_exists('dental_security_session_is_revoked') && dental_security_session_is_revoked()) {
            unset($_SESSION['user'], $_SESSION['last_activity']);
            flash('error', 'This session was ended by a security control. Please sign in again.');
            return null;
        }
        if (function_exists('dental_mfa_staff_session_valid') && !dental_mfa_staff_session_valid($_SESSION['user'])) {
            flash('error', 'Your secure session ended. Please sign in again.');
            return null;
        }
        if (function_exists('dental_mfa_touch_staff_session')) {
            dental_mfa_touch_staff_session($_SESSION['user']);
        }
        if (function_exists('dental_security_touch_current_session')) {
            dental_security_touch_current_session();
        }
    }

    return $_SESSION['user'];
}

function application_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) {
        return $ready;
    }
    try {
        $required = ['patients', 'medical_histories', 'appointments', 'queue_entries', 'clinical_encounters', 'invoices', 'payments'];
        $placeholders = implode(',', array_fill(0, count($required), '?'));
        $names = array_map(static fn(string $name): string => table($name), $required);
        $stmt = db()->prepare(
            'SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ? AND table_name IN (' . $placeholders . ')'
        );
        $stmt->execute(array_merge([(string) app_config('database.name')], $names));
        $ready = (int) $stmt->fetchColumn() === count($required);
    } catch (Throwable) {
        $ready = false;
    }
    return $ready;
}

function availability_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) return $ready;
    try {
        $required = ['branch_operating_hours','doctor_availability_rules','doctor_availability_exceptions','service_catalog'];
        $placeholders = implode(',', array_fill(0, count($required), '?'));
        $names = array_map(static fn(string $name): string => table($name), $required);
        $stmt = db()->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name IN (' . $placeholders . ')');
        $stmt->execute(array_merge([(string) app_config('database.name')], $names));
        $ready = (int) $stmt->fetchColumn() === count($required);
    } catch (Throwable) { $ready = false; }
    return $ready;
}

function queue_cashier_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) return $ready;
    try {
        $tableStmt = db()->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name=?');
        $tableStmt->execute([(string) app_config('database.name'), table('queue_status_history')]);
        if ((int) $tableStmt->fetchColumn() !== 1) return $ready = false;
        $columnStmt = db()->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=? AND table_name=? AND column_name IN (?,?,?)');
        $columnStmt->execute([(string) app_config('database.name'), table('queue_entries'), 'source_type', 'cashier_called_at', 'service_name']);
        if ((int) $columnStmt->fetchColumn() !== 3) return $ready = false;
        $invoiceStmt = db()->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=? AND table_name=? AND column_name=?');
        $invoiceStmt->execute([(string) app_config('database.name'), table('invoices'), 'queue_entry_id']);
        return $ready = (int) $invoiceStmt->fetchColumn() === 1;
    } catch (Throwable) {
        return $ready = false;
    }
}

function require_application_schema(): void
{
    $script = dental_request_script();
    if (in_array($script, ['update.php', 'system_setup.php', 'navigation_recovery.php'], true)) return;

    // Only the core application schema and the exact current-release ledger are
    // global gates. Availability and Cashier/queue readiness are module-level
    // concerns; using them as a site-wide gate previously stranded unrelated
    // staff accounts after a completed update or a transient metadata lookup.
    $coreReady = application_schema_ready();
    $releasePending = app_update_pending();
    if ($coreReady && !$releasePending) return;

    if (actual_role() === 'super_admin') {
        // A pending-update gate must never preserve a focused Task View. Doing
        // so can bounce the owner between the focused dashboard and System
        // Setup. Clear only temporary workspace context, then enter the primary
        // Superuser update screen in one canonical redirect.
        if (function_exists('dental_clear_task_workspace_context')) {
            dental_clear_task_workspace_context();
        } else {
            unset($_SESSION['task_role'], $_SESSION['task_dentist_id']);
        }
        header('Location: ' . dental_clean_url('system_setup.php?tab=updates&from=update_gate'), true, 303);
        exit;
    }
    http_response_code(503);
    exit('A system update is pending. Please ask the Superuser to open System Setup → Updates & Database.');
}

function dental_request_is_api(): bool
{
    $script = str_replace('\\', '/', (string)($_SERVER['SCRIPT_NAME'] ?? ''));
    $path = str_replace('\\', '/', (string)(parse_url((string)($_SERVER['REQUEST_URI'] ?? ''), PHP_URL_PATH) ?: ''));
    return str_contains('/' . ltrim($script, '/'), '/api/') || str_contains('/' . ltrim($path, '/'), '/api/');
}

function dental_request_expects_json(): bool
{
    $accept = strtolower((string)($_SERVER['HTTP_ACCEPT'] ?? ''));
    $requestedWith = strtolower((string)($_SERVER['HTTP_X_REQUESTED_WITH'] ?? ''));
    return dental_request_is_api()
        || str_contains($accept, 'application/json')
        || $requestedWith === 'xmlhttprequest';
}

function dental_request_is_background(): bool
{
    return dental_request_expects_json()
        || isset($_SERVER['HTTP_X_DENTAL_LIVE_QUEUE'])
        || isset($_SERVER['HTTP_X_DENTAL_RECONCILIATION']);
}

function task_preview_safe_return_url(): string
{
    $fallback = 'dashboard.php';
    $candidates = [
        (string)($_SERVER['HTTP_REFERER'] ?? ''),
        (string)($_SERVER['REQUEST_URI'] ?? ''),
    ];
    $currentHost = strtolower(preg_replace('/:\d+$/', '', (string)($_SERVER['HTTP_HOST'] ?? '')));
    foreach ($candidates as $candidate) {
        if ($candidate === '') continue;
        $parts = parse_url($candidate);
        if (!is_array($parts)) continue;
        $host = strtolower((string)($parts['host'] ?? ''));
        if ($host !== '' && $currentHost !== '' && $host !== $currentHost) continue;
        $path = basename((string)($parts['path'] ?? ''));
        if ($path === '' || !preg_match('/^[A-Za-z0-9_.-]+\.php$/', $path)) continue;
        if (in_array($path, ['login.php','logout.php','install.php','license.php'], true)) continue;
        $url = $path;
        if (!empty($parts['query'])) $url .= '?' . (string)$parts['query'];
        return $url;
    }
    return $fallback;
}

function dental_superuser_primary_return_requested(): bool
{
    $route = task_route_key();
    if ($route !== 'role_switch') return false;

    $method = strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET'));
    if ($method === 'GET') {
        return (string)($_GET['return_primary'] ?? '') === '1';
    }
    if ($method !== 'POST') return false;

    return (string)($_POST['return_primary'] ?? '') === '1'
        || trim((string)($_POST['task_role'] ?? '')) === 'super_admin';
}

function dental_clear_task_workspace_context(): void
{
    unset(
        $_SESSION['task_role'],
        $_SESSION['task_dentist_id'],
        $_SESSION['dental_patient_context'],
        $_SESSION['active_patient_context'],
        $_SESSION['dental_active_clinical_context'],
        $_SESSION['cashier_patient_context']
    );
    $_SESSION['task_role_returned_at'] = time();
}

function dental_complete_superuser_primary_return(): never
{
    $method = strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET'));
    if ($method === 'POST' && !csrf_request_is_valid()) {
        // A stale browser tab must not strand the owner. Redirect to the narrow
        // GET recovery endpoint, which only clears this signed-in session's
        // temporary Task View and cannot alter clinic records.
        flash('warning', 'The role-switch security token expired. DentalCare is completing the safe return to Superuser in a fresh request.');
        header('Location: ' . dental_clean_url('role_switch.php?return_primary=1'), true, 303);
        exit;
    }

    $before = active_role();
    dental_clear_task_workspace_context();

    try {
        dental_record_role_switch('super_admin');
    } catch (Throwable $exception) {
        error_log('DentalCare Superuser return timestamp deferred: ' . $exception->getMessage());
    }

    try {
        if (!app_demo_read_only()) {
            audit('active_role_returned_to_superuser', 'user', (int)(current_user()['id'] ?? 0), [
                'actual_role' => 'super_admin',
                'from_role' => $before,
                'to_role' => 'super_admin',
                'recovery_route' => true,
                'workspace_context_cleared' => true,
            ]);
        }
    } catch (Throwable $exception) {
        error_log('DentalCare Superuser return audit deferred: ' . $exception->getMessage());
    }

    flash('success', 'Returned to the signed-in Superuser workspace.');
    if (session_status() === PHP_SESSION_ACTIVE) {
        @session_write_close();
    }
    header('Location: ' . dental_clean_url('dashboard.php?workspace=superuser&returned=1'), true, 303);
    exit;
}

function require_login(): void
{
    if (!current_user()) {
        if (dental_request_expects_json()) {
            http_response_code(401);
            header('Content-Type: application/json; charset=utf-8');
            echo json_encode(['ok'=>false,'message'=>'Your session has expired.'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
            exit;
        }
        $returnTo = basename($_SERVER['REQUEST_URI'] ?? 'dashboard.php');
        $_SESSION['login_return_to'] = $returnTo;
        header('Location: login.php');
        exit;
    }
    // Returning to the primary Superuser workspace is a session-recovery
    // action and must run before schema, MFA-enrollment, and Task View gates.
    // Otherwise those gates can redirect the request and permanently strand
    // the owner inside a focused role.
    if (dental_superuser_primary_return_requested() && signed_in_primary_role() === 'super_admin') {
        dental_complete_superuser_primary_return();
    }
    require_application_schema();
    if (function_exists('dental_mfa_enrollment_required_for_current_user') && dental_mfa_enrollment_required_for_current_user()) {
        $script = dental_request_script();
        if (!in_array($script, ['my_profile.php','logout.php'], true)) {
            if (dental_request_expects_json()) {
                http_response_code(403);
                header('Content-Type: application/json; charset=utf-8');
                echo json_encode(['ok'=>false,'message'=>'Multi-factor authentication enrollment is required before continuing.'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
                exit;
            }
            flash('warning', 'Set up multi-factor authentication before continuing privileged clinic work.');
            header('Location: my_profile.php?security=1&enroll_mfa=1');
            exit;
        }
    }
    if (function_exists('dental_multi_role_access_normalize_once')) dental_multi_role_access_normalize_once();
    if (function_exists('dental_break_glass_refresh')) dental_break_glass_refresh();
    // API endpoints enforce their own role, permission, patient, and branch rules.
    // Applying the page task-view guard to an API filename caused valid Dentist
    // lookups and polling calls to redirect and enqueue repeated dashboard errors.
    if (!dental_request_is_api()) {
        enforce_task_context_page();
        // Administrator/Superuser task views are inspection previews, not an
        // impersonation backdoor. All state-changing work must be performed by
        // an account whose signed-in role owns that workflow.
        if (strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET')) === 'POST'
            && task_preview_mode()
            && !in_array(task_route_key(), ['role_switch','my_profile'], true)) {
            $message = 'The selected role is review-only. No action was saved. Switch Active role back to your signed-in Administrator or Superuser role, or sign in with the responsible operational account.';
            if (dental_request_is_background()) {
                access_denied($message);
            }
            flash('warning', $message);
            header('Location: ' . task_preview_safe_return_url(), true, 303);
            exit;
        }
    }
}

function signed_in_primary_role(): string
{
    static $resolved = null;
    if ($resolved !== null) return $resolved;

    $user = current_user();
    if (!$user) return $resolved = '';

    $sessionRole = trim((string) ($user['role'] ?? ''));
    $userId = (int) ($user['id'] ?? 0);
    $resolved = $sessionRole;

    // The signed-in role is an identity attribute, not a Task View value. Resolve
    // it from the users table so an old session or a previous switcher cannot
    // strand a Superuser inside another workspace.
    if ($userId > 0) {
        try {
            if (schema_table_exists(table('users')) && schema_table_exists(table('roles'))) {
                $stmt = db()->prepare(
                    'SELECT r.code,u.branch_id FROM `' . table('users') . '` u '
                    . 'JOIN `' . table('roles') . '` r ON r.id=u.role_id '
                    . 'WHERE u.id=? AND u.status="active" LIMIT 1'
                );
                $stmt->execute([$userId]);
                $row = $stmt->fetch();
                $databaseRole = trim((string) ($row['code'] ?? ''));
                if ($databaseRole !== '') {
                    $resolved = $databaseRole;
                    $_SESSION['user']['role'] = $databaseRole;
                    if (array_key_exists('branch_id', (array) $row)) {
                        $_SESSION['user']['branch_id'] = $row['branch_id'] !== null ? (int) $row['branch_id'] : null;
                    }
                }
            }
        } catch (Throwable $exception) {
            error_log('DentalCare signed-in role refresh deferred: ' . $exception->getMessage());
        }
    }

    return $resolved;
}

function actual_role(): string
{
    return signed_in_primary_role();
}

function staff_profile_dual_role_schema_ready(): bool
{
    static $ready = null;
    if ($ready !== null) return $ready;
    try {
        return $ready = (schema_table_exists(table('user_role_assignments'))
            && schema_column_exists(table('users'), 'contact_number')
            && schema_column_exists(table('users'), 'job_title')
            && schema_column_exists(table('users'), 'emergency_contact_phone')
            && schema_column_exists(table('users'), 'profile_completed_at'));
    } catch (Throwable) {
        return $ready = false;
    }
}

function user_role_assignments_schema_ready(): bool
{
    static $ready = null;
    if ($ready !== null) return $ready;
    try {
        return $ready = schema_table_exists(table('user_role_assignments'));
    } catch (Throwable) {
        return $ready = false;
    }
}

function user_role_assignment_records(?int $userId = null): array
{
    static $cache = [];
    $user = current_user();
    if (!$user) return [];
    $userId = $userId !== null && $userId > 0 ? $userId : (int)($user['id'] ?? 0);
    if ($userId <= 0) return [];
    if (array_key_exists($userId, $cache)) return $cache[$userId];

    $primary = trim((string)($user['role'] ?? ''));
    $records = [];
    if (!user_role_assignments_schema_ready()) {
        if ($primary !== '') {
            $records[] = [
                'code' => $primary,
                'assignment_type' => 'primary',
                'status' => 'active',
                'expires_at' => null,
                'last_switched_at' => null,
            ];
        }
        return $cache[$userId] = $records;
    }

    try {
        $hasExpiry = schema_column_exists(table('user_role_assignments'), 'expires_at');
        $hasLastSwitch = schema_column_exists(table('user_role_assignments'), 'last_switched_at');
        $select = 'r.code,ura.assignment_type,ura.status'
            . ($hasExpiry ? ',ura.expires_at' : ',NULL expires_at')
            . ($hasLastSwitch ? ',ura.last_switched_at' : ',NULL last_switched_at');
        $where = 'ura.user_id=? AND ura.status="active"';
        if ($hasExpiry) $where .= ' AND (ura.expires_at IS NULL OR ura.expires_at>NOW())';
        $stmt = db()->prepare(
            'SELECT ' . $select . ' FROM `' . table('user_role_assignments') . '` ura '
            . 'JOIN `' . table('roles') . '` r ON r.id=ura.role_id '
            . 'WHERE ' . $where . ' ORDER BY FIELD(ura.assignment_type,"primary","secondary"),r.name'
        );
        $stmt->execute([$userId]);
        foreach ($stmt->fetchAll() as $row) {
            $code = trim((string)($row['code'] ?? ''));
            if ($code === '') continue;
            $records[$code] = [
                'code' => $code,
                'assignment_type' => (string)($row['assignment_type'] ?? 'secondary'),
                'status' => (string)($row['status'] ?? 'active'),
                'expires_at' => $row['expires_at'] ?? null,
                'last_switched_at' => $row['last_switched_at'] ?? null,
            ];
        }
    } catch (Throwable $exception) {
        error_log('DentalCare assigned-role lookup deferred: ' . $exception->getMessage());
    }

    // The users.role_id record remains the authoritative primary identity and is
    // always available even if an older database has not yet backfilled its row.
    if ($primary !== '' && !isset($records[$primary])) {
        $records = [$primary => [
            'code' => $primary,
            'assignment_type' => 'primary',
            'status' => 'active',
            'expires_at' => null,
            'last_switched_at' => null,
        ]] + $records;
    }
    return $cache[$userId] = array_values($records);
}

function user_assigned_role_codes(?int $userId = null): array
{
    return array_values(array_unique(array_filter(array_map(
        static fn(array $row): string => trim((string)($row['code'] ?? '')),
        user_role_assignment_records($userId)
    ))));
}

function user_secondary_role_codes(?int $userId = null): array
{
    $codes = [];
    foreach (user_role_assignment_records($userId) as $row) {
        if ((string)($row['assignment_type'] ?? '') !== 'secondary') continue;
        $code = trim((string)($row['code'] ?? ''));
        if ($code !== '') $codes[] = $code;
    }
    return array_values(array_unique($codes));
}

function user_has_assigned_role(array|string $roles): bool
{
    $roles = is_array($roles) ? $roles : [$roles];
    return (bool) array_intersect($roles, user_assigned_role_codes());
}

function dental_role_labels(): array
{
    return [
        'super_admin' => 'Superuser',
        'clinic_admin' => 'Administrator',
        'dentist' => 'Dentist',
        'receptionist' => 'Receptionist',
        'staff' => 'General Staff',
        'dental_assistant' => 'Dental Assistant',
        'cashier' => 'Cashier',
        'inventory_officer' => 'Inventory Officer',
    ];
}

function switchable_task_roles(?string $actualRole = null): array
{
    $actualRole ??= actual_role();
    $labels = dental_role_labels();

    // Superuser may deliberately cover non-clinical operational lanes. Clinical
    // roles remain review-only because diagnosis, prescription, assistant entry,
    // and signatures must stay attributable to the licensed assigned account.
    if ($actualRole === 'super_admin') {
        return [
            'super_admin' => 'Superuser',
            'clinic_admin' => 'Administrator',
            'receptionist' => 'Receptionist',
            'staff' => 'General Staff',
            'cashier' => 'Cashier',
            'inventory_officer' => 'Inventory Officer',
            'dentist' => 'Dentist review only',
            'dental_assistant' => 'Dental Assistant review only',
        ];
    }

    // Every other account may switch only to roles explicitly assigned to that
    // same named user. This is a role switch, not a union of permissions.
    $assigned = user_assigned_role_codes();
    $available = [];
    foreach ($assigned as $code) {
        if (isset($labels[$code])) $available[$code] = $labels[$code];
    }
    if (!isset($available[$actualRole]) && isset($labels[$actualRole])) {
        $available = [$actualRole => $labels[$actualRole]] + $available;
    }
    return count($available) > 1 ? $available : [];
}

function active_role_mode(?string $role = null): string
{
    $role ??= active_role();
    $actual = actual_role();
    if ($role === $actual) return 'primary';
    if (user_has_assigned_role($role)) return 'assigned';
    if ($actual === 'super_admin' && in_array($role, ['clinic_admin','receptionist','staff','cashier','inventory_officer'], true)) return 'superuser_operational';
    return 'review';
}

function active_role_can_operate(?string $role = null): bool
{
    return active_role_mode($role) !== 'review';
}

function active_role_is(string $role): bool
{
    return active_role() === $role && active_role_can_operate($role);
}

function dental_multi_role_access_normalize_once(): void
{
    static $ran = false;
    if ($ran || !current_user()) return;
    $ran = true;
    try {
        if ((string)setting_get('system','multi_role_access_isolation_hf8_ready','0') === '1') return;
        if (!schema_table_exists(table('users')) || !schema_table_exists(table('roles')) || !schema_table_exists(table('user_role_assignments'))) return;
        $pdo = db();
        $pdo->beginTransaction();

        $roleRows = $pdo->query('SELECT id,code FROM `' . table('roles') . '`')->fetchAll();
        $roleIdByCode = [];
        $roleCodeById = [];
        foreach ($roleRows as $row) {
            $roleIdByCode[(string)$row['code']] = (int)$row['id'];
            $roleCodeById[(int)$row['id']] = (string)$row['code'];
        }

        $upsert = $pdo->prepare('INSERT INTO `' . table('user_role_assignments') . '` (user_id,role_id,assignment_type,status,created_at,updated_at) VALUES (?,? ,"primary","active",NOW(),NOW()) ON DUPLICATE KEY UPDATE assignment_type="primary",status="active",updated_at=NOW()');
        foreach ($pdo->query('SELECT id,role_id FROM `' . table('users') . '`') as $userRow) {
            $uid=(int)$userRow['id']; $primaryRoleId=(int)$userRow['role_id'];
            if ($uid < 1 || $primaryRoleId < 1) continue;
            $pdo->prepare('UPDATE `' . table('user_role_assignments') . '` SET assignment_type="secondary",updated_at=NOW() WHERE user_id=? AND role_id<>? AND status="active" AND assignment_type="primary"')->execute([$uid,$primaryRoleId]);
            $upsert->execute([$uid,$primaryRoleId]);
        }

        $rows=$pdo->query('SELECT ura.id,ura.user_id,ura.role_id,ura.assignment_type,r.code FROM `' . table('user_role_assignments') . '` ura JOIN `' . table('roles') . '` r ON r.id=ura.role_id WHERE ura.status="active" ORDER BY ura.user_id,FIELD(ura.assignment_type,"primary","secondary"),ura.id')->fetchAll();
        $byUser=[]; foreach($rows as $row) $byUser[(int)$row['user_id']][]=$row;
        // Cashier is no longer globally exclusive: compatible multi-role users
        // can switch into a dedicated Cashier Task View. General Staff already
        // combines Reception and Cashier and therefore remains exclusive.
        $exclusive=['super_admin','staff'];
        $conflicts=[['dentist','dental_assistant'],['dentist','inventory_officer'],['dental_assistant','inventory_officer']];
        $disable=[];
        foreach($byUser as $assignments){
            $kept=[];
            foreach($assignments as $row){
                $code=(string)$row['code'];
                if((string)$row['assignment_type']==='primary'){ $kept[]=$code; continue; }
                $blocked=in_array($code,$exclusive,true) || array_intersect($kept,$exclusive);
                if(!$blocked){
                    foreach($conflicts as [$a,$b]){
                        if((in_array($a,$kept,true)&&$code===$b)||(in_array($b,$kept,true)&&$code===$a)){ $blocked=true; break; }
                    }
                }
                if($blocked) $disable[]=(int)$row['id']; else $kept[]=$code;
            }
        }
        $disable=array_values(array_unique(array_filter($disable)));
        if($disable){
            $marks=implode(',',array_fill(0,count($disable),'?'));
            $pdo->prepare('UPDATE `' . table('user_role_assignments') . '` SET status="inactive",updated_at=NOW() WHERE id IN ('.$marks.') AND assignment_type="secondary"')->execute($disable);
        }

        if (schema_table_exists(table('permissions')) && schema_table_exists(table('role_permissions')) && isset($roleIdByCode['dental_assistant'])) {
            $stmt=$pdo->prepare('UPDATE `' . table('role_permissions') . '` rp JOIN `' . table('permissions') . '` p ON p.id=rp.permission_id SET rp.allowed=0 WHERE rp.role_id=? AND p.code IN ("inventory.catalog","inventory.manage")');
            $stmt->execute([$roleIdByCode['dental_assistant']]);
        }
        setting_set('system','multi_role_access_isolation_hf8_ready','1');
        setting_set('system','multi_role_access_isolation_hf8_disabled_assignments',(string)count($disable));
        $pdo->commit();
        audit('multi_role_access_normalized','system',null,['disabled_secondary_assignments'=>count($disable),'dental_assistant_inventory_catalog_revoked'=>true]);
    } catch (Throwable $e) {
        if (isset($pdo) && $pdo instanceof PDO && $pdo->inTransaction()) $pdo->rollBack();
        error_log('DentalCare multi-role normalization deferred: ' . $e->getMessage());
    }
}

function dental_role_context_sync(): void
{
    static $checked = false;
    if ($checked || !current_user()) return;
    $checked = true;
    $userId = (int)(current_user()['id'] ?? 0);
    if ($userId < 1 || !schema_table_exists(table('users')) || !schema_column_exists(table('users'), 'role_context_version')) return;
    try {
        $stmt = db()->prepare('SELECT role_context_version FROM `' . table('users') . '` WHERE id=? LIMIT 1');
        $stmt->execute([$userId]);
        $databaseVersion = max(1, (int)$stmt->fetchColumn());
        $sessionVersion = max(0, (int)($_SESSION['role_context_version'] ?? 0));
        if ($sessionVersion > 0 && $sessionVersion !== $databaseVersion) {
            unset(
                $_SESSION['task_role'],
                $_SESSION['task_dentist_id'],
                $_SESSION['dental_patient_context'],
                $_SESSION['active_patient_context'],
                $_SESSION['cashier_patient_context']
            );
            flash('info', 'Your role assignments changed. DentalCare returned you to your primary role.');
        }
        $_SESSION['role_context_version'] = $databaseVersion;
    } catch (Throwable $exception) {
        error_log('DentalCare role-context refresh deferred: ' . $exception->getMessage());
    }
}


function dental_session_patient_context_active(): bool
{
    $context = (array)($_SESSION['dental_patient_context'] ?? []);
    $legacy = (array)($_SESSION['active_patient_context'] ?? []);
    if (!$context && !$legacy) return false;

    $patientId = max((int)($context['patient_id'] ?? 0), (int)($legacy['patient_id'] ?? 0));
    $encounterId = max((int)($context['encounter_id'] ?? 0), (int)($legacy['encounter_id'] ?? 0));
    $queueId = max((int)($context['queue_entry_id'] ?? 0), (int)($legacy['queue_entry_id'] ?? 0));

    try {
        $pdo = db();
        if ($queueId > 0 && schema_table_exists(table('queue_entries'))) {
            $stmt = $pdo->prepare('SELECT status FROM `' . table('queue_entries') . '` WHERE id=? LIMIT 1');
            $stmt->execute([$queueId]);
            $status = strtolower(trim((string)($stmt->fetchColumn() ?: '')));
            if ($status !== '' && !in_array($status,['completed','cancelled','left_before_treatment','no_show','closed','discharged'],true)) return true;
        }
        if ($encounterId > 0 && schema_table_exists(table('clinical_encounters'))) {
            $stmt = $pdo->prepare('SELECT status FROM `' . table('clinical_encounters') . '` WHERE id=? LIMIT 1');
            $stmt->execute([$encounterId]);
            $status = strtolower(trim((string)($stmt->fetchColumn() ?: '')));
            if ($status !== '' && !in_array($status,['finalized','addendum','completed','closed','cancelled'],true)) return true;
        }
        if ($patientId > 0) {
            if (schema_table_exists(table('queue_entries'))) {
                $stmt = $pdo->prepare('SELECT COUNT(*) FROM `' . table('queue_entries') . '` WHERE patient_id=? AND status NOT IN ("completed","cancelled","left_before_treatment","no_show","closed","discharged")');
                $stmt->execute([$patientId]);
                if ((int)$stmt->fetchColumn() > 0) return true;
            }
            if (schema_table_exists(table('clinical_encounters'))) {
                $stmt = $pdo->prepare('SELECT COUNT(*) FROM `' . table('clinical_encounters') . '` WHERE patient_id=? AND status NOT IN ("finalized","addendum","completed","closed","cancelled")');
                $stmt->execute([$patientId]);
                if ((int)$stmt->fetchColumn() > 0) return true;
            }
        }

        unset($_SESSION['dental_patient_context'], $_SESSION['active_patient_context']);
        return false;
    } catch (Throwable $exception) {
        error_log('DentalCare active patient context verification deferred: ' . $exception->getMessage());
        return true;
    }
}

function active_role_switch_blockers(string $targetRole): array
{
    $targetRole = trim($targetRole);
    $currentRole = active_role();
    if ($targetRole === '' || $targetRole === $currentRole) return [];
    if (actual_role() === 'super_admin' && $targetRole === 'super_admin') return [];

    $blockers = [];
    $userId = (int)(current_user()['id'] ?? 0);

    // Patient, chair, encounter, and viewed-record context must never follow a
    // user into another assigned role. Those contexts are cleared by the role
    // switcher instead of trapping a multi-role account. The underlying queue
    // or encounter remains safely stored for the responsible role to resume.

    // A Cashier patient lock is different: it protects a live financial posting
    // from being abandoned. An open empty drawer does not block Task View.
    if ($userId > 0 && in_array($currentRole, ['cashier','staff'], true) && !in_array($targetRole, ['cashier','staff'], true)) {
        try {
            if (schema_table_exists(table('cashier_sessions'))) {
                $stmt = db()->prepare('SELECT id,locked_patient_id,locked_queue_entry_id FROM `' . table('cashier_sessions') . '` WHERE cashier_user_id=? AND status="open" ORDER BY id DESC LIMIT 1');
                $stmt->execute([$userId]);
                $session = $stmt->fetch();
                if ($session && ((int)($session['locked_patient_id'] ?? 0) > 0 || (int)($session['locked_queue_entry_id'] ?? 0) > 0)) {
                    $blockers[] = 'Complete or cancel the active patient payment before switching roles. The open cashier drawer itself may remain open.';
                }
            }
        } catch (Throwable $exception) {
            error_log('DentalCare role-switch cashier lock check deferred: ' . $exception->getMessage());
            $blockers[] = 'DentalCare could not verify the active Cashier transaction. Reopen Billing and complete or cancel the patient payment before switching.';
        }
    }
    return array_values(array_unique($blockers));
}

function dental_record_role_switch(string $role): void
{
    $userId = (int)(current_user()['id'] ?? 0);
    if ($userId < 1) return;
    try {
        $pdo = db();
        if (schema_table_exists(table('users'))) {
            $sets = ['updated_at=NOW()'];
            $params = [];
            if (schema_column_exists(table('users'), 'last_role_switch_at')) $sets[] = 'last_role_switch_at=NOW()';
            if (schema_column_exists(table('users'), 'last_role_switch_role_id')) {
                $sets[] = 'last_role_switch_role_id=(SELECT id FROM `' . table('roles') . '` WHERE code=? LIMIT 1)';
                $params[] = $role;
            }
            $params[] = $userId;
            $pdo->prepare('UPDATE `' . table('users') . '` SET ' . implode(',', $sets) . ' WHERE id=?')->execute($params);
        }
        if (user_role_assignments_schema_ready() && schema_column_exists(table('user_role_assignments'), 'last_switched_at')) {
            $stmt = $pdo->prepare('UPDATE `' . table('user_role_assignments') . '` ura JOIN `' . table('roles') . '` r ON r.id=ura.role_id SET ura.last_switched_at=NOW(),ura.updated_at=NOW() WHERE ura.user_id=? AND r.code=?');
            $stmt->execute([$userId, $role]);
        }
    } catch (Throwable $exception) {
        error_log('DentalCare role-switch timestamp deferred: ' . $exception->getMessage());
    }
}

function active_role(): string
{
    dental_role_context_sync();
    $actual = actual_role();
    $allowed = switchable_task_roles($actual);
    if (!$allowed) {
        unset($_SESSION['task_role']);
        return $actual;
    }

    $selected = trim((string) ($_SESSION['task_role'] ?? ''));
    if ($selected === '' || !array_key_exists($selected, $allowed)) {
        unset($_SESSION['task_role']);
        return $actual;
    }
    return $selected;
}

function task_role_is_active(): bool
{
    return active_role() !== actual_role();
}

function role_context_has(array|string $roles): bool
{
    $roles = is_array($roles) ? $roles : [$roles];
    return in_array(active_role(), $roles, true);
}

function superuser_operational_task_role(): string
{
    if (actual_role() !== 'super_admin') return '';
    $role = active_role();
    return in_array($role, ['clinic_admin','receptionist','staff','cashier','inventory_officer'], true) ? $role : '';
}

function superuser_operational_mode(): bool
{
    return superuser_operational_task_role() !== '';
}

/**
 * The signed-in owner has unrestricted owner authority only in the explicit
 * Superuser workspace. Selecting another Task View is a real server-side
 * security boundary rather than a menu-only presentation filter.
 */
function superuser_primary_workspace(): bool
{
    return actual_role() === 'super_admin' && active_role() === 'super_admin' && !task_role_is_active();
}

function signed_identity_has_role(array|string $roles): bool
{
    $roles = is_array($roles) ? $roles : [$roles];
    return in_array(actual_role(), $roles, true);
}

function has_role(array|string $roles): bool
{
    $roles = is_array($roles) ? $roles : [$roles];
    $active = active_role();

    // Owner authority is available only after explicitly returning to the
    // Superuser workspace. Operational Task Views use that role's permissions.
    if (superuser_primary_workspace() && in_array('super_admin', $roles, true)) return true;
    if (!in_array($active, $roles, true)) return false;
    return active_role_can_operate($active);
}

function permission_is_security_sensitive(string $permission): bool
{
    return (bool) preg_match('/^(?:clinical|patient|appointment|queue|communication|document|record|report|print|prescription|consent|finance|billing|payment|refund|insurance|inventory|sterilization|audit|security|privacy|backup|restore|export|upload|staff|configuration|smtp|branch|service|chair|schedule|user|role|permission|license|system\.)/i', $permission);
}

function permission_lookup_security_event(string $eventType, string $role, string $permission): void
{
    if (function_exists('dental_security_record_event')) {
        dental_security_record_event($eventType, 'critical', ['role' => $role, 'permission' => $permission]);
        return;
    }
    error_log('DentalCare authorization failure: ' . $eventType . ' role=' . $role . ' permission=' . $permission);
}

function role_permission_granted_for(string $role, string $permission, array|string $fallbackRoles = []): bool
{
    static $rolePermissionMaps = [];
    static $rolePermissionLoaded = [];
    $fallbackRoles = is_array($fallbackRoles) ? $fallbackRoles : [$fallbackRoles];
    if (active_role_permission_hard_denied($role, $permission)) return false;
    $sensitive = permission_is_security_sensitive($permission);
    if (!permission_schema_ready()) {
        if ($sensitive) {
            permission_lookup_security_event('permission_schema_unavailable', $role, $permission);
            return false;
        }
        return $fallbackRoles === [] || in_array($role, $fallbackRoles, true);
    }

    if (!array_key_exists($role, $rolePermissionLoaded)) {
        $rolePermissionMaps[$role] = [];
        $rolePermissionLoaded[$role] = false;
        try {
            $stmt = db()->prepare(
                'SELECT p.code,rp.allowed FROM `' . table('role_permissions') . '` rp '
                . 'JOIN `' . table('permissions') . '` p ON p.id=rp.permission_id '
                . 'JOIN `' . table('roles') . '` r ON r.id=rp.role_id '
                . 'WHERE r.code=?'
            );
            $stmt->execute([$role]);
            foreach ($stmt->fetchAll() as $row) {
                $code = trim((string)($row['code'] ?? ''));
                if ($code !== '') $rolePermissionMaps[$role][$code] = (bool)($row['allowed'] ?? false);
            }
            $rolePermissionLoaded[$role] = true;
        } catch (Throwable $exception) {
            permission_lookup_security_event('permission_lookup_failed', $role, $permission);
            error_log('DentalCare permission lookup failed: ' . $exception->getMessage());
        }
    }
    if (!$rolePermissionLoaded[$role] && $sensitive) return false;
    if (array_key_exists($permission, $rolePermissionMaps[$role])) {
        return (bool)$rolePermissionMaps[$role][$permission];
    }
    if ($sensitive) return false;
    return $fallbackRoles === [] || in_array($role, $fallbackRoles, true);
}

function navigation_can(string $permission, array|string $fallbackRoles = []): bool
{
    // For a Superuser in a focused workspace, navigation follows the selected
    // role so unrelated tasks are hidden and server authority is restricted to
    // the selected operational role until the owner explicitly returns.
    if (actual_role() === 'super_admin' && task_role_is_active()) {
        return role_permission_granted_for(active_role(), $permission, $fallbackRoles);
    }
    return can($permission, $fallbackRoles);
}

function role_profile(?string $role = null): array
{
    $role ??= active_role();
    return match ($role) {
        'super_admin' => [
            'name' => 'Superuser',
            'dashboard' => 'Superuser Dashboard',
            'description' => 'System ownership, licensing, updates, backups, security, roles, and organization-wide oversight.',
        ],
        'clinic_admin' => [
            'name' => 'Administrator',
            'dashboard' => 'Administrator Dashboard',
            'description' => 'Clinic operations, staff, schedules, patient flow, services, inventory oversight, and reports.',
        ],
        'dentist' => [
            'name' => 'Dentist',
            'dashboard' => 'Dentist Dashboard',
            'description' => 'Your appointments, waiting patients, clinical encounters, treatment plans, prescriptions, and availability.',
        ],
        'receptionist' => [
            'name' => 'Receptionist',
            'dashboard' => 'Reception Dashboard',
            'description' => 'Patient registration, online requests, appointments, check-in, walk-ins, and the clinic queue.',
        ],
        'staff' => [
            'name' => 'General Staff',
            'dashboard' => 'General Staff Dashboard',
            'description' => 'Combined front-desk and cashier operations: patient registration, appointments, queue coordination, payments, receipts, and own drawer reconciliation.',
        ],
        'dental_assistant' => [
            'name' => 'Dental Assistant',
            'dashboard' => 'Dental Assistant Dashboard',
            'description' => 'First-seating dental safety, chairside workflow, clinical drafts, sterilization, and patient movement.',
        ],
        'cashier' => [
            'name' => 'Cashier',
            'dashboard' => 'Cashier Dashboard',
            'description' => 'Search payors, review all payable balances, receive payments, issue receipts, and reconcile your own cashier window.'
        ],
        'inventory_officer' => [
            'name' => 'Inventory Officer',
            'dashboard' => 'Inventory Dashboard',
            'description' => 'Dental supplies, stock transactions, lots, expirations, reorder alerts, and sterilization records.',
        ],
        default => [
            'name' => 'Staff',
            'dashboard' => 'Staff Dashboard',
            'description' => 'Your authorized clinic work and assigned responsibilities.',
        ],
    };
}


function task_workspace_definitions(): array
{
    static $definitions = null;
    if ($definitions !== null) return $definitions;
    return $definitions = [
        'super_admin' => [
            'label' => 'Superuser',
            'purpose' => 'Protect the installation, resolve system exceptions, control access, and keep the clinic platform healthy.',
            'pages' => ['*'],
            'actions' => ['*'],
            'steps' => ['Review system and update alerts', 'Resolve failed email, backup, security, or licensing checks', 'Manage users, permissions, and protected configuration', 'Review audit and operational exceptions', 'Use an operational Task View when personally covering Reception, General Staff, Cashier, or Inventory duties'],
            'limits' => 'Superuser may perform non-clinical operational duties with full audit attribution. Diagnosis, prescription, and clinical signature still require the assigned licensed Dentist.',
        ],
        'clinic_admin' => [
            'label' => 'Administrator',
            'purpose' => 'Coordinate clinic operations, staffing, schedules, patient flow, finance oversight, inventory, and reports.',
            'pages' => ['dashboard','break_glass','privacy_governance','patients','appointment_requests','portal_requests','record_requests','waitlist','appointments','appointment_service_actions','queue','dental_safety_assessment','inhouse_checkups','visit_workflow','visit_exceptions','followups','communications','clinical','clinical_day','doctor_treatment','clinical_workspace','clinical_tasks','doctor_profile','odontogram','periodontal','treatment','prescriptions','documents','consents','referrals','laboratory','billing','insurance','inventory','sterilization','reports','print_center','print_record','availability','doctor_schedule','schedule','branding','website_pages','website_media','operation_hours','services','settings','email_settings','email_templates','sms_settings','chairs','inhouse_team','reminders','staff','patient_numbering','permission_matrix','setup_wizard','uploads','doctor_asset','download_document','download_upload','clinical_autosave'],
            'actions' => ['*'],
            'steps' => ['Review today’s appointments, queue, and online requests', 'Resolve staffing, schedule, and chair exceptions', 'Monitor cashier, insurance, and inventory exceptions', 'Approve controlled adjustments and operational changes', 'Review reports and audit follow-up items'],
            'limits' => 'Administrator actions remain administrative. Clinical finalization still requires the assigned authenticated dentist.',
        ],
        'dentist' => [
            'label' => 'Dentist',
            'purpose' => 'Review completed first-seating safety information, treat assigned patients, complete clinical records, issue dental prescriptions, and define follow-up care.',
            'pages' => ['dashboard','doctor_treatment','appointment_service_actions','clinical_day','clinical_workspace','clinical_tasks','doctor_profile','patients','portal_requests','record_requests','appointments','queue','dental_safety_assessment','visit_workflow','visit_exceptions','clinical','odontogram','periodontal','treatment','prescriptions','documents','consents','referrals','laboratory','followups','availability','doctor_schedule','schedule','communications','reports','print_center','print_record','doctor_asset','download_document','clinical_autosave'],
            'actions' => ['patients.view','patients.medical','appointments.view','appointments.manage','queue.clinical','clinical.view','clinical.write_self','clinical.finalize_self','availability.self','communications.manage','followups.recommend','diagnostic.validate','reports.clinical','records.release.clinical_approve'],
            'steps' => ['Open My Treatment Dashboard and choose the assigned patient', 'Confirm first-seating dental safety is ready before treatment starts', 'Review alerts, records, consent, and planned services', 'Assess through examination, tooth chart, and diagnosis', 'Treat using approved procedures and record the outcome', 'Close with SOAP, billing preview, and follow-up recommendation'],
            'limits' => 'Only the assigned authenticated dentist may write or finalize the clinical encounter.',
        ],
        'receptionist' => [
            'label' => 'Receptionist',
            'purpose' => 'Register patients, process online requests, schedule visits, check patients in, and manage front-desk queue flow.',
            'pages' => ['dashboard','patients','appointment_requests','portal_requests','record_requests','waitlist','appointments','appointment_service_actions','queue','inhouse_checkups','visit_workflow','visit_exceptions','followups','communications','reports','print_center','print_record'],
            'actions' => ['patients.view','patients.manage','patients.medical','patients.portal','appointment_requests.manage','appointments.manage','appointment.service_disposition','queue.intake','queue.frontdesk','communications.manage','followups.schedule','diagnostic.request','reports.frontdesk','records.release.receive'],
            'steps' => ['Review online appointment requests', 'Register or verify the patient record', 'Book, confirm, reschedule, or cancel appointments', 'Check in scheduled patients and register walk-ins', 'Move patients through the front-desk queue and record communications'],
            'limits' => 'Receptionists cannot diagnose, edit clinical findings, prescribe, or post financial adjustments.',
        ],
        'staff' => [
            'label' => 'General Staff',
            'purpose' => 'Operate the combined Reception and Cashier lane for the assigned branch without receiving clinical, inventory, approval, or system-administration authority.',
            'pages' => ['dashboard','patients','appointment_requests','portal_requests','record_requests','waitlist','appointments','appointment_service_actions','queue','inhouse_checkups','visit_workflow','visit_exceptions','followups','communications','billing','reports','print_center','print_record'],
            'actions' => ['patients.view','patients.manage','patients.medical','patients.portal','appointment_requests.manage','appointments.manage','appointment.service_disposition','queue.intake','queue.frontdesk','communications.manage','followups.schedule','diagnostic.request','reports.frontdesk','billing.manage','cashier.statement.print','cashier.session.manage','records.release.receive','records.release.financial'],
            'steps' => ['Register or verify the patient', 'Book, confirm, reschedule, check in, or register a walk-in', 'Move the patient through the front-desk queue', 'Open an assigned cashier window and receive the authorized payable amount', 'Issue the receipt, complete checkout, and reconcile the drawer session'],
            'limits' => 'General Staff cannot diagnose, prescribe, finalize clinical records, approve discounts/refunds/waivers, manage inventory, manage users, or change protected setup.',
        ],
        'dental_assistant' => [
            'label' => 'Dental Assistant',
            'purpose' => 'Prepare assigned patients and chairs, complete first-seating dental safety review, support clinical drafts and documents, and complete sterilization duties.',
            'pages' => ['dashboard','patients','appointments','appointment_service_actions','queue','dental_safety_assessment','inhouse_checkups','visit_workflow','visit_exceptions','clinical','clinical_workspace','odontogram','periodontal','treatment','documents','consents','laboratory','followups','sterilization','reports','print_center','print_record','doctor_asset','download_document'],
            'actions' => ['patients.view','patients.medical','appointments.view','appointment.service_disposition','queue.clinical_support','clinical.view','clinical.draft','documents.upload','sterilization.manage','followups.view','diagnostic.request','reports.assistant'],
            'steps' => ['Review the chairside queue and assigned patients', 'Complete first-seating dental safety review before treatment and record only indicated chairside measurements', 'Prepare instruments, materials, and the treatment area', 'Enter permitted draft clinical information and upload documents', 'Complete sterilization and hand the record to the Dentist for finalization'],
            'limits' => 'Dental Assistants cannot create walk-ins, manage suppliers or inventory catalogues, diagnose, prescribe, receive payments, or finalize clinical records.',
        ],
        'cashier' => [
            'label' => 'Cashier',
            'purpose' => 'Accept payments only: search patient/payee accounts, view all payable invoices and balances, receive payment, issue receipts, and reconcile the cashier window.',
            'pages' => ['dashboard','billing','record_requests','print_center','print_record'],
            'actions' => ['billing.manage','records.release.financial'],
            'steps' => ['Open a cashier window and session', 'Search the patient or payee account from the Cashier Register', 'Review all unpaid, partial, and queue-linked payables', 'Receive payment with the required reference for card, GCash, Maya, bank, cheque, or mixed payment', 'Print or email the receipt, then complete or cancel the focused payment transaction'],
            'limits' => 'Cashiers are payment-only users. They cannot open patient management, appointments, clinical records, follow-ups, insurance, reports, or exception centers. Refunds, voids, and charge overrides require Administrator/Superuser review.',
        ],
        'inventory_officer' => [
            'label' => 'Inventory Officer',
            'purpose' => 'Maintain supplies, lots, expirations, stock movement, suppliers, and sterilization inventory controls.',
            'pages' => ['dashboard','visit_exceptions','inventory','sterilization','reports','print_center','print_record'],
            'actions' => ['inventory.manage','sterilization.manage','reports.inventory'],
            'steps' => ['Review low-stock and expiry alerts', 'Receive purchases and verify lots and expiration dates', 'Record issues, transfers, returns, and counts', 'Document damage, expiry, and controlled disposal', 'Review inventory and sterilization reports'],
            'limits' => 'Inventory work does not require detailed patient diagnosis, clinical notes, payments, licensing, or backups.',
        ],
    ];
}

function task_workspace(?string $role = null): array
{
    $role ??= active_role();
    $definitions = task_workspace_definitions();
    return $definitions[$role] ?? $definitions['staff'];
}

function task_route_key(?string $script = null): string
{
    $script ??= dental_request_script();
    $script = (string) (parse_url($script, PHP_URL_PATH) ?: $script);
    $script = basename($script);
    $key = preg_replace('/\.php$/i', '', $script) ?: $script;
    return function_exists('dental_route_from_slug') ? (dental_route_from_slug($key) ?? $key) : $key;
}

function task_context_page_allowed(?string $route = null, ?string $role = null): bool
{
    if (!current_user()) return true;
    $route = task_route_key($route);
    $role ??= active_role();
    if ($route === 'search' && $role === 'cashier') return false;
    if (in_array($route, ['dashboard','role_switch','search','help_support','my_profile','calendar'], true)) return true;
    if (function_exists('dental_break_glass_route_allowed') && dental_break_glass_route_allowed($route)) return true;
    $workspace = task_workspace($role);
    $pages = (array) ($workspace['pages'] ?? []);
    if (in_array('*', $pages, true) || in_array($route, $pages, true)) return true;

    // Keep the workspace list as the preferred navigation definition, but use
    // the route authorization map as the canonical fallback. This prevents a
    // valid Dentist page from being blocked merely because one duplicate page
    // name was omitted from the workspace presentation list.
    $requirement = task_route_access_requirements()[$route] ?? null;
    $roles = is_array($requirement) ? (array)($requirement['roles'] ?? []) : [];
    return $roles !== [] && in_array($role, $roles, true);
}



function access_denied(string $message = 'Access denied.'): never
{
    if (function_exists('dental_access_record_decision')) {
        dental_access_record_decision('denied', '', '', null, $message);
    }
    http_response_code(403);
    if (dental_request_is_background()) {
        if (dental_request_expects_json()) {
            header('Content-Type: application/json; charset=utf-8');
            echo json_encode(['ok'=>false,'message'=>$message], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
            exit;
        }
        exit($message);
    }
    $method = strtoupper((string) ($_SERVER['REQUEST_METHOD'] ?? 'GET'));
    if ($method === 'GET' && current_user()) {
        flash('error', $message);
        header('Location: dashboard.php');
        exit;
    }
    exit($message);
}

function task_route_access_requirements(): array
{
    static $requirements = null;
    if ($requirements !== null) return $requirements;
    $file = DENTAL_ROOT . '/config/route_access.php';
    $loaded = is_file($file) ? require $file : [];
    return $requirements = is_array($loaded) ? $loaded : [];
}

function task_link_allowed(string $url): bool
{
    $parts = parse_url($url);
    $route = task_route_key((string) ($parts['path'] ?? $url));
    if (!task_context_page_allowed($route)) return false;
    $requirements = task_route_access_requirements()[$route] ?? null;
    if (!$requirements) return true;
    $roles = (array) ($requirements['roles'] ?? []);
    if ($roles && !has_role($roles) && !(task_preview_mode() && role_context_has($roles))) return false;
    $permission = (string) ($requirements['permission'] ?? '');
    if ($permission !== '' && !navigation_can($permission, $roles)) return false;
    $action = (string) ($requirements['action'] ?? '');
    if ($action !== '' && !task_context_action_allowed($action)) return false;
    return true;
}


function task_route_allowed(string $route, bool $autoAlign = true): bool
{
    $route = task_route_key($route);
    if (!current_user()) return true;

    // A focused Task View is a server-side security boundary. The only
    // unconditional escape is the role_switch route, which is listed below.
    $publicOrSpecialRoutes = [
        'appointment-new','appointment-returning','appointment-received','website-page',
        'patient-login','patient-activate','patient-logout','patient-portal','patient-record','account-activate',
        'queue_display','calendar','download_backup','download_installer','download_release',
        'download_document','download_upload','download_inventory_evidence','download_kiosk_launcher',
        'doctor_asset','print_record'
    ];
    if (in_array($route, $publicOrSpecialRoutes, true)) return true;

    if (!task_context_page_allowed($route)) {
        if (!$autoAlign || !task_context_auto_align_for_route($route) || !task_context_page_allowed($route)) {
            return false;
        }
    }

    $requirements = task_route_access_requirements()[$route] ?? null;
    if (!$requirements) return true;

    $roles = (array) ($requirements['roles'] ?? []);
    if ($roles && !has_role($roles)) {
        $method = strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET'));
        if (!(task_preview_mode() && role_context_has($roles) && in_array($method, ['GET','HEAD'], true))) return false;
    }

    $permission = (string) ($requirements['permission'] ?? '');
    if ($permission !== '' && !navigation_can($permission, $roles)) return false;

    $action = (string) ($requirements['action'] ?? '');
    if ($action !== '' && !task_context_action_allowed($action)) return false;

    return true;
}

function enforce_task_route_access(?string $route = null): void
{
    if (!current_user()) return;
    $route = task_route_key($route);
    if (task_route_allowed($route, true)) return;
    $workspace = task_workspace();
    access_denied('This page or action is not available in the active ' . (string)($workspace['label'] ?? 'staff') . ' task view. Use the dashboard or task switcher to open an allowed workflow.');
}

function role_access_matrix(): array
{
    return [
        'super_admin' => [
            'label' => 'Superuser',
            'scope' => 'Owner-level system, license, update, backup, reset, role, audit, and release control in the explicit Superuser workspace.',
            'allow' => ['view_all','configure_system','apply_updates','reset_data','issue_license','manage_roles','override_with_audit','view_finance','view_clinical'],
            'deny' => ['focused_task_views_do_not_keep_owner_permissions','cannot_sign_clinical_record_unless_authenticated_as_the_responsible_dentist','break_glass_cannot_override_clinical_authorship_or_independent_approval'],
        ],
        'clinic_admin' => [
            'label' => 'Administrator',
            'scope' => 'Operational management for staff, schedules, services, reports, inventory oversight, and setup except owner secrets.',
            'allow' => ['manage_staff','manage_services','manage_appointments','monitor_queue','review_reports','approve_operational_exceptions','configure_public_site'],
            'deny' => ['no_final_diagnosis','no_prescription_signature','no_owner_license_secret','no_cashier_receipt_identity_unless_cashier_role'],
        ],
        'receptionist' => [
            'label' => 'Receptionist',
            'scope' => 'Front-desk intake, patient registration, appointment request review, scheduling, check-in, queue movement, and communications.',
            'allow' => ['register_patient','book_appointment','approve_online_request','check_in_scheduled_patient','check_in_walk_in','manage_frontdesk_queue','record_communication'],
            'deny' => ['no_diagnosis','no_prescription','no_payment_posting','no_inventory_adjustment','no_system_setup'],
        ],
        'staff' => [
            'label' => 'General Staff',
            'scope' => 'Combined branch Receptionist and Cashier operations with one accountable user identity and one assigned cashier drawer session.',
            'allow' => ['register_patient','manage_appointments','check_in_scheduled_patient','check_in_walk_in','manage_frontdesk_queue','record_communication','open_own_cashier_session','receive_payment','print_receipt','complete_checkout','close_own_drawer'],
            'deny' => ['no_diagnosis','no_prescription','no_clinical_finalization','no_discount_refund_or_waiver_approval','no_inventory_adjustment','no_admin_configuration'],
        ],
        'dentist' => [
            'label' => 'Dentist',
            'scope' => 'Assigned patient treatment, diagnosis, odontogram, treatment outcome, prescriptions, follow-up, and clinical completion.',
            'allow' => ['start_treatment','review_alerts','chart_tooth_findings','diagnose','complete_procedure','prescribe','release_to_billing','print_clinical_record'],
            'deny' => ['no_cashier_collection','no_global_setup','no_other_dentist_signature','no_inventory_count_adjustment'],
        ],
        'dental_assistant' => [
            'label' => 'Dental Assistant',
            'scope' => 'First-seating dental safety review, chairside support, indicated observations, X-ray/media upload, materials support notes, sterilization, and turnover.',
            'allow' => ['complete_first_seating_safety','record_indicated_observations','upload_xray_or_documents','draft_support_notes','record_material_support','chair_turnover','sterilization_support'],
            'deny' => ['no_walk_in_creation','no_supplier_or_inventory_catalogue','no_diagnosis','no_prescription','no_finalize_treatment','no_payment_posting','no_admin_override'],
        ],
        'cashier' => [
            'label' => 'Cashier',
            'scope' => 'Payment terminal only: search payor, review bill, receive payment, issue receipt, and reconcile cashier window.',
            'allow' => ['open_cashier_window','focus_patient_transaction','collect_payment','require_payment_reference','print_receipt','close_zero_balance'],
            'deny' => ['no_patient_edit','no_appointment_edit','no_clinical_access','no_inventory_access','no_system_setup'],
        ],
        'inventory_officer' => [
            'label' => 'Inventory Officer',
            'scope' => 'Supplies, lots, expirations, stock movement, documents, sterilization support, and inventory reports.',
            'allow' => ['batch_upload_catalog','receive_stock','adjust_stock_with_reason','track_lots','monitor_expiry','sterilization_inventory_report'],
            'deny' => ['no_patient_clinical_record','no_payment_posting','no_appointment_management','no_system_setup'],
        ],
    ];
}

function role_access_action_label(string $action): string
{
    return ucwords(str_replace('_', ' ', $action));
}

function role_navigation_audit_snapshot(): array
{
    $routes = is_file(DENTAL_ROOT . '/config/routes.php') ? (require DENTAL_ROOT . '/config/routes.php') : [];
    $requirements = task_route_access_requirements();
    $workspaces = task_workspace_definitions();
    $issues = [];
    $coveredRoutes = [];

    foreach ($workspaces as $role => $workspace) {
        foreach ((array)($workspace['pages'] ?? []) as $page) {
            if ($page === '*') continue;
            $coveredRoutes[(string)$page][] = (string)$role;
            if (!isset($routes[$page]) && !in_array($page, ['download_document','download_upload','doctor_asset','clinical_autosave'], true)) {
                $issues[] = ['severity'=>'warning','route'=>(string)$page,'message'=>'Workspace lists a page that is not present in config/routes.php.'];
            }
        }
    }

    foreach ($routes as $route => $path) {
        $route = (string)$route;
        if (in_array($route, ['appointment-new','appointment-returning','appointment-received','website-page','patient-login','patient-activate','patient-logout','patient-portal','patient-record','account-activate','queue_display'], true)) continue;
        if (!isset($requirements[$route]) && !isset($coveredRoutes[$route]) && !str_starts_with($route, 'download_') && !in_array($route, ['calendar','doctor_asset','print_record','gmail_oauth','update'], true)) {
            $issues[] = ['severity'=>'notice','route'=>$route,'message'=>'Route exists but is not explicitly mapped to a role workspace or route requirement.'];
        }
        if (is_string($path) && $path !== '' && !is_file($path)) {
            $issues[] = ['severity'=>'critical','route'=>$route,'message'=>'Configured route target file is missing.'];
        }
    }

    $highRisk = [
        ['cashier','clinical','Cashier must not see clinical workspace links.'],
        ['cashier','patients','Cashier must not edit patient master records.'],
        ['receptionist','billing','Receptionist must not post cashier payments.'],
        ['receptionist','clinical_workspace','Receptionist must not enter clinical workspace.'],
        ['dental_assistant','prescriptions','Dental Assistant must not prescribe.'],
        ['dental_assistant','inventory','Dental Assistant must not manage suppliers or inventory catalogue records.'],
        ['inventory_officer','patients','Inventory officer must not browse patient master records.'],
    ];
    foreach ($highRisk as [$role,$route,$message]) {
        if (task_context_page_allowed($route, $role)) {
            $issues[] = ['severity'=>'critical','route'=>$route,'message'=>$message . ' Current workspace allows it for ' . $role . '.'];
        }
    }

    return [
        'routes_total' => count($routes),
        'requirements_total' => count($requirements),
        'workspace_routes_total' => count($coveredRoutes),
        'issues' => $issues,
        'critical_count' => count(array_filter($issues, static fn(array $i): bool => ($i['severity'] ?? '') === 'critical')),
        'warning_count' => count(array_filter($issues, static fn(array $i): bool => ($i['severity'] ?? '') === 'warning')),
        'notice_count' => count(array_filter($issues, static fn(array $i): bool => ($i['severity'] ?? '') === 'notice')),
    ];
}

function task_context_auto_align_for_route(string $route): bool
{
    // The selected active role is an explicit security boundary. Never switch
    // back to the primary role merely because that role could open a clicked
    // URL. The user must switch roles deliberately from the header selector.
    return false;
}

function enforce_task_context_page(): void
{
    // Task Views are enforced for every identity, including the Superuser.
    // role_switch remains available in every workspace as the recovery path.
    $route = task_route_key();
    if (task_context_page_allowed($route)) return;
    if (task_context_auto_align_for_route($route) && task_context_page_allowed($route)) return;
    $workspace = task_workspace();
    access_denied('This page is not part of the active ' . (string) ($workspace['label'] ?? 'staff') . ' task view. Use the dashboard to open an available task.');
}

function task_context_action_allowed(string $action, ?string $role = null): bool
{
    if (superuser_primary_workspace()) return true;
    $workspace = task_workspace($role);
    $actions = (array) ($workspace['actions'] ?? []);
    return in_array('*', $actions, true) || in_array($action, $actions, true);
}

function require_task_action(string $action): void
{
    if (task_context_action_allowed($action)) return;
    $workspace = task_workspace();
    access_denied('This action is not part of the active ' . (string) ($workspace['label'] ?? 'staff') . ' task view.');
}

function task_preview_mode(): bool
{
    if (!task_role_is_active() || active_role_mode() !== 'review') return false;

    // Superuser clinical role selections are read-only only while actually on
    // that clinical workspace. They must not make System Control or other owner
    // pages read-only merely because the menu is focused on a clinical role.
    if (actual_role() === 'super_admin') {
        return task_context_page_allowed(task_route_key(), active_role());
    }
    return true;
}

function is_admin(): bool
{
    return has_role(['super_admin', 'clinic_admin']);
}

function require_roles(array|string $roles): void
{
    $roles = is_array($roles) ? array_values($roles) : [$roles];
    $privileged = array_values(array_intersect($roles, ['super_admin','clinic_admin']));
    if ($privileged && role_context_has($privileged) && !in_array(actual_role(), $privileged, true)) {
        access_denied('Administrator and Superuser authority cannot be borrowed through an additional role. Sign in with the responsible privileged account.');
    }
    if (has_role($roles)) return;
    $method = strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET'));
    if (task_preview_mode() && role_context_has($roles) && in_array($method, ['GET','HEAD'], true)) return;
    access_denied('Your signed-in role cannot open this task.');
}

function signed_user_branch_id(): int
{
    $branchId = max(0, (int) (current_user()['branch_id'] ?? 0));
    if ($branchId > 0) return $branchId;
    if (function_exists('dental_break_glass_active_session')) {
        $session = dental_break_glass_active_session();
        $emergencyBranchId = max(0, (int)($session['branch_id'] ?? 0));
        if ($emergencyBranchId > 0) return $emergencyBranchId;
    }
    return 0;
}

/**
 * Authorize a branch-owned record without incorrectly tying clinical access to
 * the patient's home branch. Dentists and Dental Assistants remain governed by
 * their concrete patient assignment; operational roles remain branch-scoped.
 */
function record_branch_scope_allowed(?int $recordBranchId, ?int $patientId = null): bool
{
    if (!current_user()) return false;
    if (superuser_primary_workspace()) return true;
    if ($recordBranchId !== null && $recordBranchId > 0
        && function_exists('dental_break_glass_branch_allowed')
        && dental_break_glass_branch_allowed($recordBranchId, $patientId ? 'patient' : '', $patientId)) return true;

    $role = active_role();
    if (in_array($role, ['dentist', 'dental_assistant'], true)) {
        return $patientId !== null && $patientId > 0 && task_patient_scope_allowed($patientId);
    }

    $userBranchId = signed_user_branch_id();
    if ($userBranchId < 1) return false;
    return $recordBranchId !== null && $recordBranchId > 0 && $recordBranchId === $userBranchId;
}

require_once DENTAL_ROOT . '/app/audit_integrity.php';

function audit_record_hash_from_row(array $row): string
{
    $chainSequence = (int)($row['chain_sequence'] ?? 0);
    $chainKey = trim((string)($row['chain_key'] ?? ''));
    if ($chainSequence > 0 && $chainKey !== '') {
        return hash('sha256', (string)json_encode([
            'integrity_version'=>(int)($row['integrity_version'] ?? 2),
            'chain_key'=>$chainKey,
            'chain_sequence'=>$chainSequence,
            'previous_hash'=>(string)($row['previous_hash'] ?? dental_audit_chain_zero_hash()),
            'id'=>(int)($row['id'] ?? 0),
            'user_id'=>isset($row['user_id']) ? (int)$row['user_id'] : null,
            'branch_id'=>isset($row['branch_id']) ? (int)$row['branch_id'] : null,
            'action'=>(string)($row['action'] ?? ''),
            'entity_type'=>$row['entity_type'] ?? null,
            'entity_id'=>$row['entity_id'] ?? null,
            'old_values_json'=>$row['old_values_json'] ?? null,
            'new_values_json'=>$row['new_values_json'] ?? null,
            'ip_address'=>$row['ip_address'] ?? null,
            'user_agent'=>$row['user_agent'] ?? null,
            'created_at'=>(string)($row['created_at'] ?? ''),
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
    }
    return hash('sha256', (string)json_encode([
        'id'=>(int)($row['id'] ?? 0),
        'user_id'=>isset($row['user_id']) ? (int)$row['user_id'] : null,
        'branch_id'=>isset($row['branch_id']) ? (int)$row['branch_id'] : null,
        'action'=>(string)($row['action'] ?? ''),
        'entity_type'=>$row['entity_type'] ?? null,
        'entity_id'=>$row['entity_id'] ?? null,
        'old_values_json'=>$row['old_values_json'] ?? null,
        'new_values_json'=>$row['new_values_json'] ?? null,
        'ip_address'=>$row['ip_address'] ?? null,
        'user_agent'=>$row['user_agent'] ?? null,
        'created_at'=>(string)($row['created_at'] ?? ''),
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
}

function audit_write_record(string $action, string $entity = '', int|string|null $entityId = null, array $newValues = [], array $oldValues = []): void
{
    $pdo = db();
    $auditTable = table('audit_logs');
    if (!schema_table_exists($auditTable)) throw new RuntimeException('The protected audit log is unavailable.');
    $hasBranch = schema_column_exists($auditTable, 'branch_id');
    $hasHash = schema_column_exists($auditTable, 'record_hash');
    $chainReady = $hasHash && function_exists('dental_audit_chain_schema_ready') && dental_audit_chain_schema_ready();
    if (current_user()) {
        $newValues['_role_context'] = [
            'signed_in_role' => actual_role(),
            'active_role' => active_role(),
            'active_role_mode' => active_role_mode(),
        ];
    }
    $branchId = (int)($newValues['branch_id'] ?? $oldValues['branch_id'] ?? signed_user_branch_id());
    $oldJson = $oldValues ? json_encode($oldValues, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null;
    $newJson = $newValues ? json_encode($newValues, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null;
    $createdAt = date('Y-m-d H:i:s');

    $ownsTransaction = !$pdo->inTransaction();
    $savepoint = 'dental_audit_' . substr(hash('sha256', $action . microtime(true)), 0, 12);
    try {
        if ($ownsTransaction) $pdo->beginTransaction();
        else $pdo->exec('SAVEPOINT ' . $savepoint);

        $chainKey = '';
        $chainSequence = 0;
        $previousHash = null;
        if ($chainReady) {
            $chainKey = dental_audit_chain_key($branchId > 0 ? $branchId : null);
            $head = dental_audit_chain_lock($pdo, $chainKey);
            $chainSequence = (int)($head['last_sequence'] ?? 0) + 1;
            $previousHash = trim((string)($head['last_hash'] ?? '')) ?: dental_audit_chain_zero_hash();
        }

        $columns = 'user_id,action,entity_type,entity_id,old_values_json,new_values_json,ip_address,user_agent,created_at';
        $values = ':user_id,:action,:entity_type,:entity_id,:old_values,:new_values,:ip,:agent,:created_at';
        $params = [
            ':user_id'=>current_user()['id'] ?? null,
            ':action'=>$action,
            ':entity_type'=>$entity !== '' ? $entity : null,
            ':entity_id'=>$entityId !== null ? (string)$entityId : null,
            ':old_values'=>$oldJson,
            ':new_values'=>$newJson,
            ':ip'=>$_SERVER['REMOTE_ADDR'] ?? null,
            ':agent'=>substr((string)($_SERVER['HTTP_USER_AGENT'] ?? ''),0,500),
            ':created_at'=>$createdAt,
        ];
        if ($hasBranch) {
            $columns='branch_id,'.$columns; $values=':branch_id,'.$values;
            $params[':branch_id']=$branchId>0?$branchId:null;
        }
        if ($chainReady) {
            $columns='chain_key,chain_sequence,previous_hash,integrity_version,'.$columns;
            $values=':chain_key,:chain_sequence,:previous_hash,2,'.$values;
            $params[':chain_key']=$chainKey;
            $params[':chain_sequence']=$chainSequence;
            $params[':previous_hash']=$previousHash;
        }
        $stmt=$pdo->prepare('INSERT INTO `'.$auditTable.'` ('.$columns.') VALUES ('.$values.')');
        $stmt->execute($params);
        $auditId=(int)$pdo->lastInsertId();
        if ($auditId < 1) throw new RuntimeException('The protected audit record could not be identified.');

        if ($hasHash) {
            $selectColumns = 'id,user_id,'.($hasBranch?'branch_id':'NULL branch_id').',action,entity_type,entity_id,old_values_json,new_values_json,ip_address,user_agent,created_at';
            if ($chainReady) $selectColumns .= ',chain_key,chain_sequence,previous_hash,integrity_version';
            $read=$pdo->prepare('SELECT '.$selectColumns.' FROM `'.$auditTable.'` WHERE id=? LIMIT 1');
            $read->execute([$auditId]);
            $row=$read->fetch();
            if (!$row) throw new RuntimeException('The protected audit record could not be verified after insertion.');
            $recordHash=audit_record_hash_from_row($row);
            $update=$pdo->prepare('UPDATE `'.$auditTable.'` SET record_hash=? WHERE id=? AND record_hash IS NULL');
            $update->execute([$recordHash,$auditId]);
            if ($update->rowCount() !== 1) throw new RuntimeException('The protected audit fingerprint could not be stored.');
            if ($chainReady) dental_audit_chain_update_head($pdo,$chainKey,$chainSequence,$auditId,$recordHash);
        }

        if ($ownsTransaction) $pdo->commit();
        else $pdo->exec('RELEASE SAVEPOINT ' . $savepoint);
    } catch (Throwable $exception) {
        try {
            if ($ownsTransaction && $pdo->inTransaction()) $pdo->rollBack();
            elseif ($pdo->inTransaction()) {
                $pdo->exec('ROLLBACK TO SAVEPOINT ' . $savepoint);
                $pdo->exec('RELEASE SAVEPOINT ' . $savepoint);
            }
        } catch (Throwable) {
        }
        throw $exception;
    }
}

function audit_integrity_summary(int $limit = 500): array
{
    try {
        $limit=max(1,min(10000,$limit));
        $auditTable=table('audit_logs');
        if (!schema_table_exists($auditTable) || !schema_column_exists($auditTable,'record_hash')) {
            return ['ready'=>false,'checked'=>0,'valid'=>0,'invalid'=>0,'unhashed'=>0,'latest_id'=>0,'chain_ready'=>false,'chain_rows'=>0,'legacy_rows'=>0,'broken_links'=>0,'head_mismatches'=>0,'latest_chain_hash'=>'','error'=>null];
        }
        $hasBranch=schema_column_exists($auditTable,'branch_id');
        $chainReady=function_exists('dental_audit_chain_schema_ready') && dental_audit_chain_schema_ready();
        $columns='id,user_id,'.($hasBranch?'branch_id':'NULL branch_id').',action,entity_type,entity_id,old_values_json,new_values_json,ip_address,user_agent,created_at,record_hash';
        if($chainReady)$columns.=',chain_key,chain_sequence,previous_hash,integrity_version';
        $rows=db()->query('SELECT '.$columns.' FROM `'.$auditTable.'` ORDER BY id DESC LIMIT '.$limit)->fetchAll();
        $valid=0;$invalid=0;$unhashed=0;$latestId=0;$chainRows=0;$legacyRows=0;$broken=0;$headMismatches=0;$latestChainHash='';
        $groups=[];
        foreach($rows as $row){
            $latestId=max($latestId,(int)($row['id']??0));
            $stored=trim((string)($row['record_hash']??''));
            if($stored===''){$unhashed++;continue;}
            if(hash_equals($stored,audit_record_hash_from_row($row)))$valid++;else $invalid++;
            $sequence=(int)($row['chain_sequence']??0);$key=trim((string)($row['chain_key']??''));
            if($chainReady && $sequence>0 && $key!==''){$chainRows++;$groups[$key][]=$row;}else{$legacyRows++;}
        }
        foreach($groups as $key=>$chain){
            usort($chain,static fn(array $a,array $b):int=>(int)$a['chain_sequence']<=>(int)$b['chain_sequence']);
            $first=$chain[0];$firstSequence=(int)$first['chain_sequence'];
            $expectedPrevious=dental_audit_chain_zero_hash();
            if($firstSequence>1){
                $prev=db()->prepare('SELECT record_hash FROM `'.$auditTable.'` WHERE chain_key=? AND chain_sequence=? LIMIT 1');
                $prev->execute([$key,$firstSequence-1]);
                $expectedPrevious=trim((string)$prev->fetchColumn());
                if($expectedPrevious==='')$broken++;
            }
            foreach($chain as $index=>$row){
                if($index>0){
                    $prior=$chain[$index-1];
                    if((int)$row['chain_sequence']!==(int)$prior['chain_sequence']+1)$broken++;
                    $expectedPrevious=trim((string)$prior['record_hash']);
                }
                if($expectedPrevious!=='' && !hash_equals($expectedPrevious,trim((string)($row['previous_hash']??''))))$broken++;
            }
            $last=$chain[count($chain)-1];
            $head=db()->prepare('SELECT last_sequence,last_audit_id,last_hash FROM `'.dental_audit_chain_heads_table().'` WHERE chain_key=? LIMIT 1');
            $head->execute([$key]);$headRow=$head->fetch();
            $latest=db()->prepare('SELECT id,chain_sequence,record_hash FROM `'.$auditTable.'` WHERE chain_key=? ORDER BY chain_sequence DESC LIMIT 1');
            $latest->execute([$key]);$latestRow=$latest->fetch();
            if(!$headRow||!$latestRow||(int)$headRow['last_sequence']!==(int)$latestRow['chain_sequence']||(int)$headRow['last_audit_id']!==(int)$latestRow['id']||!hash_equals((string)$headRow['last_hash'],(string)$latestRow['record_hash']))$headMismatches++;
            if((int)$latestRow['id']>=$latestId)$latestChainHash=(string)$latestRow['record_hash'];
        }
        return ['ready'=>true,'checked'=>count($rows),'valid'=>$valid,'invalid'=>$invalid,'unhashed'=>$unhashed,'latest_id'=>$latestId,'chain_ready'=>$chainReady,'chain_rows'=>$chainRows,'legacy_rows'=>$legacyRows,'broken_links'=>$broken,'head_mismatches'=>$headMismatches,'latest_chain_hash'=>$latestChainHash,'error'=>null];
    } catch (Throwable $exception) {
        return ['ready'=>false,'checked'=>0,'valid'=>0,'invalid'=>0,'unhashed'=>0,'latest_id'=>0,'chain_ready'=>false,'chain_rows'=>0,'legacy_rows'=>0,'broken_links'=>0,'head_mismatches'=>0,'latest_chain_hash'=>'','error'=>$exception->getMessage()];
    }
}

function audit(string $action, string $entity = '', int|string|null $entityId = null, array $newValues = [], array $oldValues = []): void
{
    if (app_demo_read_only()) return;
    try {
        audit_write_record($action,$entity,$entityId,$newValues,$oldValues);
    } catch (Throwable $exception) {
        error_log('Audit logging failed: '.$exception->getMessage());
    }
}

function audit_required(string $action, string $entity = '', int|string|null $entityId = null, array $newValues = [], array $oldValues = []): void
{
    if (app_demo_read_only()) return;
    audit_write_record($action,$entity,$entityId,$newValues,$oldValues);
}

function send_mail_message(string $to, string $subject, string $html, ?string $text = null, array $meta = []): bool
{
    if (function_exists('app_orientation_mode_active') && app_orientation_mode_active()) {
        error_log('Email delivery skipped because orientation/demo mode is active: ' . $to . ' / ' . $subject);
        return false;
    }
    try {
        $service = new Dental\Services\EmailService(dental_config(), db());
        $service->send($to, $subject, $html, $text, $meta);
        return true;
    } catch (Throwable $exception) {
        error_log('Email delivery failed: ' . $exception->getMessage());
        return false;
    }
}

function queue_mail_message(string $to, string $subject, string $html, ?string $text = null, array $meta = []): int
{
    if (function_exists('app_orientation_mode_active') && app_orientation_mode_active()) {
        error_log('Queued email skipped because orientation/demo mode is active: ' . $to . ' / ' . $subject);
        return 0;
    }
    $service = new Dental\Services\EmailService(dental_config(), db());
    return $service->queue($to, $subject, $html, $text, $meta);
}

function queue_mail_message_with_attempt(string $to, string $subject, string $html, ?string $text = null, array $meta = []): array
{
    if (function_exists('app_orientation_mode_active') && app_orientation_mode_active()) {
        error_log('Email send-with-attempt skipped because orientation/demo mode is active: ' . $to . ' / ' . $subject);
        return ['id' => 0, 'status' => 'skipped', 'message' => 'Orientation mode is active; no real email was sent.'];
    }
    $service = new Dental\Services\EmailService(dental_config(), db());
    $id = $service->queue($to, $subject, $html, $text, $meta);
    $delivery = $service->processMessage($id);
    return array_merge(['id' => $id], $delivery);
}

function email_queue_schema_ready(): bool
{
    try {
        return schema_table_exists(table('email_outbox'));
    } catch (Throwable) {
        return false;
    }
}

function email_queue_configuration(): array
{
    $orientationActive = function_exists('app_orientation_mode_active') && app_orientation_mode_active();
    return [
        'enabled' => !$orientationActive && (string) setting_get('email_queue','automatic_enabled','1') === '1',
        'interval_minutes' => max(1,min(60,(int) setting_get('email_queue','automatic_interval_minutes','2'))),
        'batch_size' => max(1,min(50,(int) setting_get('email_queue','automatic_batch_size','10'))),
        'stale_minutes' => max(5,min(1440,(int) setting_get('email_queue','stale_processing_minutes','15'))),
    ];
}

function email_history_retention_configuration(): array
{
    return [
        // Completed outbox rows include full HTML/text bodies, so they need a
        // bounded retention period. Unresolved pending/retry/processing/failed
        // rows are never removed by automatic maintenance.
        'outbox_days' => max(30, min(3650, (int) setting_get('email_history', 'outbox_days', '365'))),
        'email_log_days' => max(30, min(3650, (int) setting_get('email_history', 'email_log_days', '365'))),
        'run_days' => max(7, min(730, (int) setting_get('email_history', 'run_days', '90'))),
        'diagnostic_days' => max(7, min(730, (int) setting_get('email_history', 'diagnostic_days', '90'))),
        'transcript_days' => max(1, min(90, (int) setting_get('email_history', 'transcript_days', '14'))),
    ];
}

function email_queue_health_cache_clear(): void
{
    if (session_status() === PHP_SESSION_ACTIVE) unset($_SESSION['email_queue_health_cache_v0763']);
}

function email_history_maintenance(?PDO $pdo = null, bool $force = false): array
{
    $pdo ??= db();
    $result = ['outbox'=>0,'email_log'=>0,'runs'=>0,'diagnostics'=>0,'transcripts'=>0,'skipped'=>false];
    $lastTs = strtotime((string) setting_get('email_history', 'last_cleanup_at', '1970-01-01')) ?: 0;
    if (!$force && time() - $lastTs < 86400) {
        $result['skipped'] = true;
        return $result;
    }

    $retention = email_history_retention_configuration();
    $batch = 2000;
    try {
        if (schema_table_exists(table('smtp_diagnostics'))) {
            $result['transcripts'] = (int) $pdo->exec(
                'UPDATE `' . table('smtp_diagnostics') . '` SET transcript=NULL '
                . 'WHERE transcript IS NOT NULL AND created_at<DATE_SUB(NOW(),INTERVAL ' . $retention['transcript_days'] . ' DAY) LIMIT ' . $batch
            );
            $result['diagnostics'] = (int) $pdo->exec(
                'DELETE FROM `' . table('smtp_diagnostics') . '` '
                . 'WHERE created_at<DATE_SUB(NOW(),INTERVAL ' . $retention['diagnostic_days'] . ' DAY) LIMIT ' . $batch
            );
        }
        if (schema_table_exists(table('email_queue_runs'))) {
            $result['runs'] = (int) $pdo->exec(
                'DELETE FROM `' . table('email_queue_runs') . '` '
                . 'WHERE started_at<DATE_SUB(NOW(),INTERVAL ' . $retention['run_days'] . ' DAY) LIMIT ' . $batch
            );
        }
        if (schema_table_exists(table('email_outbox'))) {
            $result['outbox'] = (int) $pdo->exec(
                'DELETE FROM `' . table('email_outbox') . '` '
                . 'WHERE status IN ("sent","cancelled") AND created_at<DATE_SUB(NOW(),INTERVAL ' . $retention['outbox_days'] . ' DAY) LIMIT ' . $batch
            );
        }
        if (schema_table_exists(table('email_log'))) {
            $result['email_log'] = (int) $pdo->exec(
                'DELETE FROM `' . table('email_log') . '` '
                . 'WHERE created_at<DATE_SUB(NOW(),INTERVAL ' . $retention['email_log_days'] . ' DAY) LIMIT ' . $batch
            );
        }
        setting_set('email_history', 'last_cleanup_at', date('Y-m-d H:i:s'));
        setting_set('email_history', 'last_cleanup_result_json', json_encode($result, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE));
    } catch (Throwable $exception) {
        error_log('Email history maintenance failed: ' . $exception->getMessage());
    }
    email_queue_health_cache_clear();
    return $result;
}

function email_queue_record_result(array $result, string $trigger): void
{
    setting_set('email_queue','last_run_at',date('Y-m-d H:i:s'));
    setting_set('email_queue','last_trigger',$trigger);
    setting_set('email_queue','last_result_json',json_encode($result,JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE));
    if (!empty($result['failed'])) setting_set('email_queue','last_failure_at',date('Y-m-d H:i:s'));
    email_queue_health_cache_clear();

    // A normal page request may check the automatic queue every few minutes.
    // Do not create hundreds of history rows when there was no message to
    // process and no stale worker to recover.
    $emptyAutomatic = $trigger === 'automatic_request'
        && empty($result['selected']) && empty($result['sent'])
        && empty($result['retry']) && empty($result['failed'])
        && empty($result['skipped']) && empty($result['recovered'])
        && empty($result['busy']);
    if (!$emptyAutomatic) {
        try{
            if(schema_table_exists(table('email_queue_runs'))){
                $status=!empty($result['busy'])?'busy':((!empty($result['failed'])||!empty($result['retry']))?'warning':'success');
                $message='Selected '.(int)($result['selected']??0).', accepted '.(int)($result['sent']??0).', retry '.(int)($result['retry']??0).', failed '.(int)($result['failed']??0).'.';
                db()->prepare('INSERT INTO `' . table('email_queue_runs') . '` (trigger_type,status,selected_count,sent_count,retry_count,failed_count,skipped_count,recovered_count,message,started_by,started_at,completed_at) VALUES (?,?,?,?,?,?,?,?,?,?,NOW(),NOW())')->execute([
                    substr($trigger,0,40),$status,(int)($result['selected']??0),(int)($result['sent']??0),(int)($result['retry']??0),(int)($result['failed']??0),(int)($result['skipped']??0),(int)($result['recovered']??0),$message,(int)(current_user()['id']??0)?:null,
                ]);
            }
        }catch(Throwable $exception){error_log('Email queue run history failed: '.$exception->getMessage());}
    }
    email_history_maintenance(null, false);
}

function run_email_queue(?PDO $pdo=null,int $limit=10,string $trigger='manual'): array
{
    $pdo ??= db();
    if (!email_queue_schema_ready()) return ['selected'=>0,'sent'=>0,'retry'=>0,'failed'=>0,'skipped'=>0,'recovered'=>0,'busy'=>false];
    $service = new Dental\Services\EmailService(dental_config(),$pdo);
    $result = $service->processQueue($limit,email_queue_configuration()['stale_minutes']);
    email_queue_record_result($result,$trigger);
    return $result;
}

function maybe_run_automatic_email_queue(?PDO $pdo=null): void
{
    try {
        $pdo ??= db();
        $config = email_queue_configuration();
        if (!$config['enabled'] || !email_queue_schema_ready()) return;
        $last = strtotime((string) setting_get('email_queue','last_run_at','1970-01-01')) ?: 0;
        if (time()-$last < $config['interval_minutes']*60) return;
        run_email_queue($pdo,$config['batch_size'],'automatic_request');
    } catch (Throwable $exception) {
        error_log('Automatic email queue run failed: ' . $exception->getMessage());
    }
}

function email_queue_health(?PDO $pdo=null): array
{
    $pdo ??= db();
    if (session_status() === PHP_SESSION_ACTIVE) {
        $cached = $_SESSION['email_queue_health_cache_v0763'] ?? null;
        if (is_array($cached) && (int)($cached['expires_at'] ?? 0) > time() && is_array($cached['data'] ?? null)) {
            return $cached['data'];
        }
    }

    $mail = (array) app_config('mail',[]);
    $smtp = (array) ($mail['smtp']??[]);
    $config = email_queue_configuration();
    $health = [
        'configured'=>true,'driver'=>(string)($mail['driver']??'smtp'),'issues'=>[],
        'pending'=>0,'retry'=>0,'failed'=>0,'processing'=>0,'stale_pending'=>0,'overdue_retry'=>0,'stale_processing'=>0,
        'last_run_at'=>(string)setting_get('email_queue','last_run_at',''),'last_trigger'=>(string)setting_get('email_queue','last_trigger',''),
        'last_result'=>[],'run_stale'=>false,
    ];
    $decoded=json_decode((string)setting_get('email_queue','last_result_json','{}'),true);
    if(is_array($decoded))$health['last_result']=$decoded;
    if(trim((string)($mail['from_email']??''))===''||!filter_var((string)($mail['from_email']??''),FILTER_VALIDATE_EMAIL))$health['issues'][]='Sender email is missing or invalid.';
    if(!$config['enabled'])$health['issues'][]='Automatic email queue processing is disabled.';
    if(($health['driver']??'smtp')==='smtp'){
        if(trim((string)($smtp['host']??''))==='')$health['issues'][]='SMTP host is not configured.';
        if((int)($smtp['port']??0)<1)$health['issues'][]='SMTP port is not configured.';
        if(trim((string)($smtp['username']??''))!==''&&trim((string)($smtp['password']??''))==='')$health['issues'][]='SMTP password is missing.';
    }elseif(($health['driver']??'')==='log')$health['issues'][]='Log-only email mode cannot deliver messages to inboxes.';
    if(!email_queue_schema_ready()){
        $health['configured']=false;$health['issues'][]='Email outbox table is unavailable.';
        if (session_status() === PHP_SESSION_ACTIVE) $_SESSION['email_queue_health_cache_v0763']=['expires_at'=>time()+30,'data'=>$health];
        return $health;
    }

    // Use indexed, status-specific counts instead of evaluating multiple SUM
    // expressions across the entire outbox on every dashboard request.
    $statusRows=$pdo->query('SELECT status,COUNT(*) total FROM `' . table('email_outbox') . '` GROUP BY status');
    foreach($statusRows as $statusRow){$key=(string)($statusRow['status']??'');if(array_key_exists($key,$health))$health[$key]=(int)$statusRow['total'];}
    $stmt=$pdo->prepare('SELECT COUNT(*) FROM `' . table('email_outbox') . '` WHERE status="pending" AND created_at<DATE_SUB(NOW(),INTERVAL 10 MINUTE)');$stmt->execute();$health['stale_pending']=(int)$stmt->fetchColumn();
    $stmt=$pdo->prepare('SELECT COUNT(*) FROM `' . table('email_outbox') . '` WHERE status="retry" AND available_at<=NOW()');$stmt->execute();$health['overdue_retry']=(int)$stmt->fetchColumn();
    $stmt=$pdo->prepare('SELECT COUNT(*) FROM `' . table('email_outbox') . '` WHERE status="processing" AND COALESCE(last_attempt_at,created_at)<DATE_SUB(NOW(),INTERVAL '.$config['stale_minutes'].' MINUTE)');$stmt->execute();$health['stale_processing']=(int)$stmt->fetchColumn();

    $lastTs=strtotime((string)$health['last_run_at'])?:0;
    $health['run_stale']=$config['enabled'] && (time()-$lastTs)>max(600,$config['interval_minutes']*60*5);
    if($health['stale_pending']>0)$health['issues'][]=$health['stale_pending'].' queued email(s) have waited more than 10 minutes.';
    if($health['overdue_retry']>0)$health['issues'][]=$health['overdue_retry'].' retry message(s) are overdue.';
    if($health['stale_processing']>0)$health['issues'][]=$health['stale_processing'].' message(s) are stuck in Sending.';
    if($health['failed']>0)$health['issues'][]=$health['failed'].' email(s) reached the final failed state.';
    if($health['run_stale'])$health['issues'][]='Automatic queue processing has not run recently.';
    if (session_status() === PHP_SESSION_ACTIVE) $_SESSION['email_queue_health_cache_v0763']=['expires_at'=>time()+30,'data'=>$health];
    return $health;
}

function email_delivery_link_entity(int $outboxId,array $meta): void
{
    if($outboxId<1)return;
    $pdo=db();
    try{
        if(!empty($meta['appointment_id'])&&schema_column_exists(table('appointments'),'last_email_outbox_id')){
            $type=(string)($meta['type']??$meta['template_key']??'notification');
            $sets=['last_email_outbox_id=?'];$args=[$outboxId];
            if(schema_column_exists(table('appointments'),'last_email_type')){$sets[]='last_email_type=?';$args[]=substr($type,0,60);}
            if(schema_column_exists(table('appointments'),'last_email_at'))$sets[]='last_email_at=NOW()';
            if(schema_column_exists(table('appointments'),'updated_at'))$sets[]='updated_at=NOW()';
            $args[]=(int)$meta['appointment_id'];
            $pdo->prepare('UPDATE `' . table('appointments') . '` SET '.implode(',',$sets).' WHERE id=?')->execute($args);
        }
        if(!empty($meta['request_id'])&&schema_column_exists(table('appointment_requests'),'receipt_email_outbox_id')){
            $pdo->prepare('UPDATE `' . table('appointment_requests') . '` SET receipt_email_outbox_id=?,updated_at=NOW() WHERE id=?')->execute([$outboxId,(int)$meta['request_id']]);
        }
        if(!empty($meta['portal_invite_id'])&&schema_column_exists(table('patient_portal_invites'),'email_outbox_id')){
            $sets=['email_outbox_id=?'];$args=[$outboxId];
            if(schema_column_exists(table('patient_portal_invites'),'last_sent_at'))$sets[]='last_sent_at=NOW()';
            if(schema_column_exists(table('patient_portal_invites'),'updated_at'))$sets[]='updated_at=NOW()';
            $args[]=(int)$meta['portal_invite_id'];
            $pdo->prepare('UPDATE `' . table('patient_portal_invites') . '` SET '.implode(',',$sets).' WHERE id=?')->execute($args);
        }
        if(!empty($meta['payment_id'])&&schema_column_exists(table('payments'),'receipt_email_outbox_id')){
            $pdo->prepare('UPDATE `' . table('payments') . '` SET receipt_email_outbox_id=? WHERE id=?')->execute([$outboxId,(int)$meta['payment_id']]);
        }
        if(!empty($meta['communication_id'])&&schema_column_exists(table('communications'),'email_outbox_id')){
            $statusStmt=$pdo->prepare('SELECT status FROM `' . table('email_outbox') . '` WHERE id=? LIMIT 1');
            $statusStmt->execute([$outboxId]);
            $deliveryStatus=(string)($statusStmt->fetchColumn()?:'pending');
            $pdo->prepare('UPDATE `' . table('communications') . '` SET email_outbox_id=?,delivery_status=? WHERE id=?')->execute([$outboxId,$deliveryStatus,(int)$meta['communication_id']]);
        }
    }catch(Throwable $exception){error_log('Email entity link failed: '.$exception->getMessage());}
}

function email_outbox_record(int $id): ?array
{
    if($id<1||!email_queue_schema_ready())return null;
    $stmt=db()->prepare('SELECT id,recipient_email,subject,status,attempts,available_at,last_attempt_at,sent_at,last_error,created_at,updated_at,metadata_json FROM `' . table('email_outbox') . '` WHERE id=? LIMIT 1');
    $stmt->execute([$id]);$row=$stmt->fetch();
    return $row?:null;
}

function email_template_schema_ready(): bool
{
    try {
        return schema_table_exists(table('email_templates'));
    } catch (Throwable) {
        return false;
    }
}

function staff_invite_schema_ready(): bool
{
    try {
        return schema_table_exists(table('staff_account_invites'));
    } catch (Throwable) {
        return false;
    }
}

function clinic_chair_schema_ready(): bool
{
    try {
        return schema_table_exists(table('clinic_chairs'));
    } catch (Throwable) {
        return false;
    }
}

function email_template_fallbacks(): array
{
    return [
        'staff_account_invite' => [
            'name' => 'Staff account invitation', 'category' => 'Accounts',
            'subject' => 'Your {{clinic_name}} account is ready',
            'html' => '<h2>Welcome to {{clinic_name}}</h2><p>Hello {{recipient_name}},</p><p>Your <strong>{{role_name}}</strong> account has been created.</p><p><a href="{{activation_url}}">Create your password and activate your account</a></p><p>This secure link expires in {{expires_hours}} hours.</p><p>If you were not expecting this account, contact {{support_email}}.</p>',
            'text' => 'Welcome to {{clinic_name}}. Create your password: {{activation_url}}. This link expires in {{expires_hours}} hours.',
            'variables' => ['clinic_name','recipient_name','role_name','activation_url','expires_hours','support_email','login_url'],
        ],
        'staff_password_setup' => [
            'name' => 'Staff password setup or reset', 'category' => 'Accounts',
            'subject' => 'Set your {{clinic_name}} account password',
            'html' => '<h2>Set your account password</h2><p>Hello {{recipient_name}},</p><p>An administrator requested a secure password setup for your {{clinic_name}} account.</p><p><a href="{{activation_url}}">Set a new password</a></p><p>This link expires in {{expires_hours}} hours.</p>',
            'text' => 'Set your {{clinic_name}} password: {{activation_url}}. The link expires in {{expires_hours}} hours.',
            'variables' => ['clinic_name','recipient_name','role_name','activation_url','expires_hours','support_email','login_url'],
        ],
        'patient_portal_invite' => [
            'name' => 'Patient portal invitation', 'category' => 'Accounts',
            'subject' => 'Activate your {{clinic_name}} patient portal',
            'html' => '<h2>Patient portal invitation</h2><p>Hello {{recipient_name}},</p><p>{{clinic_name}} invited you to securely review approved appointments and records.</p><p><a href="{{activation_url}}">Activate patient portal</a></p><p>This invitation expires in {{expires_hours}} hours.</p>',
            'text' => 'Activate your {{clinic_name}} patient portal: {{activation_url}}. This invitation expires in {{expires_hours}} hours.',
            'variables' => ['clinic_name','recipient_name','activation_url','expires_hours','support_email'],
        ],
        'appointment_request_received' => [
            'name' => 'Appointment request received', 'category' => 'Appointments',
            'subject' => 'Dental appointment request {{reference}}',
            'html' => '<h2>We received your appointment request</h2><p>Hello {{recipient_name}},</p><p>Your request has been submitted to {{clinic_name}}.</p><p><strong>Reference:</strong> {{reference}}<br><strong>Service:</strong> {{service_name}}<br><strong>Requested schedule:</strong> {{appointment_datetime}}</p><p>This is a request, not a confirmed appointment. The clinic will contact you after reviewing availability.</p>',
            'text' => 'We received your appointment request {{reference}} for {{service_name}} on {{appointment_datetime}}. The clinic will confirm availability.',
            'variables' => ['clinic_name','recipient_name','reference','service_name','appointment_datetime','support_email'],
        ],
        'appointment_cancelled' => [
            'name' => 'Appointment cancelled', 'category' => 'Appointments',
            'subject' => 'Your dental appointment was cancelled',
            'html' => '<h2>Appointment cancelled</h2><p>Hello {{recipient_name}},</p><p>Your appointment for <strong>{{appointment_datetime}}</strong> has been cancelled.</p><p>To request another schedule, contact {{support_email}} or use the clinic booking page.</p>',
            'text' => 'Your appointment for {{appointment_datetime}} was cancelled. Contact {{support_email}} to book another schedule.',
            'variables' => ['clinic_name','recipient_name','appointment_datetime','service_name','provider_name','support_email','booking_url'],
        ],
        'payment_receipt' => [
            'name' => 'Payment receipt', 'category' => 'Finance',
            'subject' => 'Receipt from {{clinic_name}}',
            'html' => '<h2>Payment received</h2><p>Hello {{recipient_name}},</p><p>We received your payment of <strong>{{amount}}</strong>.</p><p><strong>Receipt:</strong> {{receipt_number}}<br><strong>Date:</strong> {{payment_date}}</p><p>Thank you.</p>',
            'text' => 'Payment received: {{amount}}. Receipt {{receipt_number}} dated {{payment_date}}.',
            'variables' => ['clinic_name','recipient_name','amount','receipt_number','payment_date','support_email'],
        ],
        'appointment_confirmed' => [
            'name' => 'Appointment confirmed', 'category' => 'Appointments',
            'subject' => 'Your dental appointment is confirmed',
            'html' => '<h2>Your appointment is confirmed</h2><p>Hello {{recipient_name}},</p><p>Your appointment is scheduled for <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Add this appointment to your calendar</a></p>',
            'text' => 'Your appointment is confirmed for {{appointment_datetime}}. Service: {{service_name}}. Provider: {{provider_name}}. Calendar: {{calendar_url}}',
            'variables' => ['clinic_name','recipient_name','appointment_datetime','service_name','provider_name','branch_name','calendar_url','support_email'],
        ],
        'appointment_rescheduled' => [
            'name' => 'Appointment rescheduled', 'category' => 'Appointments',
            'subject' => 'Your dental appointment was rescheduled',
            'html' => '<h2>Your appointment was rescheduled</h2><p>Hello {{recipient_name}},</p><p>Your new appointment schedule is <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Update your calendar</a></p>',
            'text' => 'Your appointment was rescheduled to {{appointment_datetime}}. Calendar: {{calendar_url}}',
            'variables' => ['clinic_name','recipient_name','appointment_datetime','service_name','provider_name','branch_name','calendar_url','support_email'],
        ],
        'appointment_reminder' => [
            'name' => 'Appointment reminder', 'category' => 'Reminders',
            'subject' => 'Reminder: dental appointment in {{lead_time}}',
            'html' => '<h2>Dental appointment reminder</h2><p>Hello {{recipient_name}},</p><p>This is a reminder for your appointment on <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Add or update this appointment in your calendar</a></p><p>Please contact the clinic as soon as possible if you need to reschedule or cancel.</p>',
            'text' => 'Reminder: your dental appointment is on {{appointment_datetime}}. Service: {{service_name}}. Provider: {{provider_name}}. Calendar: {{calendar_url}}',
            'variables' => ['clinic_name','recipient_name','lead_time','appointment_datetime','service_name','provider_name','branch_name','calendar_url','support_email'],
        ],
        'followup_reminder' => [
            'name' => 'Follow-up reminder', 'category' => 'Reminders',
            'subject' => 'Your dental follow-up is due',
            'html' => '<h2>Your dental follow-up is due</h2><p>Hello {{recipient_name}},</p><p>Your clinic care team recommended a follow-up for <strong>{{due_date}}</strong>.</p><p><strong>Purpose:</strong> {{purpose}}<br><strong>{{provider_label}}:</strong> {{provider_name}}</p><p>{{instructions}}</p><p><a href="{{booking_url}}">Request your follow-up appointment</a></p>',
            'text' => 'Your dental follow-up is due on {{due_date}}. {{provider_label}}: {{provider_name}}. Request an appointment: {{booking_url}}',
            'variables' => ['clinic_name','recipient_name','due_date','purpose','provider_label','provider_name','instructions','booking_url','support_email'],
        ],
        'recall_reminder' => [
            'name' => 'Recall reminder', 'category' => 'Reminders',
            'subject' => 'Your dental recall is due',
            'html' => '<h2>Dental recall reminder</h2><p>Hello {{recipient_name}},</p><p>Your <strong>{{recall_type}}</strong> recall is due on <strong>{{due_date}}</strong>.</p><p><a href="{{booking_url}}">Request an appointment</a></p>',
            'text' => 'Your {{recall_type}} dental recall is due. Request an appointment: {{booking_url}}',
            'variables' => ['clinic_name','recipient_name','recall_type','due_date','booking_url','support_email'],
        ],
    ];
}

function email_template_get(string $key): array
{
    $fallbacks = email_template_fallbacks();
    $fallback = $fallbacks[$key] ?? [
        'name' => ucwords(str_replace('_',' ', $key)), 'category' => 'General',
        'subject' => '{{clinic_name}} notification', 'html' => '<p>{{message}}</p>', 'text' => '{{message}}',
        'variables' => ['clinic_name','message','support_email'],
    ];
    if (!email_template_schema_ready()) return $fallback;
    try {
        $stmt = db()->prepare('SELECT * FROM `' . table('email_templates') . '` WHERE template_key=? AND is_active=1 LIMIT 1');
        $stmt->execute([$key]);
        $row = $stmt->fetch();
        if (!$row) return $fallback;
        $variables = json_decode((string)($row['allowed_variables_json'] ?? '[]'), true);
        return [
            'id' => (int)$row['id'], 'key' => (string)$row['template_key'], 'name' => (string)$row['name'],
            'category' => (string)$row['category'], 'subject' => (string)$row['subject_template'],
            'html' => (string)$row['html_template'], 'text' => (string)($row['text_template'] ?? ''),
            'variables' => is_array($variables) ? $variables : ($fallback['variables'] ?? []),
        ];
    } catch (Throwable) {
        return $fallback;
    }
}

function email_template_render_value(string $template, array $variables, bool $html = false): string
{
    $replace = [];
    foreach ($variables as $key => $value) {
        if (is_array($value) || is_object($value)) continue;
        $string = (string)$value;
        $replace['{{' . $key . '}}'] = $html ? htmlspecialchars($string, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') : $string;
    }
    return strtr($template, $replace);
}

function email_template_message(string $key, array $variables = []): array
{
    $clinic = public_clinic_profile();
    $variables = array_merge([
        'clinic_name' => (string)($clinic['name'] ?? app_config('app.name','Dental Clinic')),
        'support_email' => (string)($clinic['public_email'] ?? $clinic['email'] ?? app_config('mail.from_email','')),
        'login_url' => app_absolute_url('login.php'),
        'booking_url' => app_absolute_url('appointment-returning.php'),
    ], $variables);
    $template = email_template_get($key);
    return [
        'subject' => email_template_render_value((string)$template['subject'], $variables, false),
        'html' => email_template_render_value((string)$template['html'], $variables, true),
        'text' => email_template_render_value((string)$template['text'], $variables, false),
        'template' => $template,
        'variables' => $variables,
    ];
}

function queue_template_email(string $key, string $to, array $variables = [], array $meta = [], bool $attemptNow = false): array
{
    if (!filter_var($to, FILTER_VALIDATE_EMAIL)) {
        throw new RuntimeException('A valid recipient email is required.');
    }
    $message = email_template_message($key, $variables);
    $meta = array_merge($meta, ['template_key' => $key]);
    if ($attemptNow) {
        $delivery=queue_mail_message_with_attempt($to, $message['subject'], $message['html'], $message['text'], $meta);
        email_delivery_link_entity((int)($delivery['id']??0),$meta);
        return $delivery;
    }
    $id = queue_mail_message($to, $message['subject'], $message['html'], $message['text'], $meta);
    email_delivery_link_entity($id,$meta);
    return ['id' => $id, 'status' => 'pending', 'error' => null, 'attempts' => 0];
}

function create_staff_account_invite(PDO $pdo, int $userId, string $email, string $templateKey = 'staff_account_invite', int $expiresHours = 72): array
{
    if (!staff_invite_schema_ready() || !email_template_schema_ready()) {
        throw new RuntimeException('Apply Version 0.5.8.3 before sending secure staff account invitations.');
    }
    $stmt = $pdo->prepare('SELECT u.id,u.name,u.email,r.name role_name FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id WHERE u.id=? LIMIT 1');
    $stmt->execute([$userId]);
    $user = $stmt->fetch();
    if (!$user) throw new RuntimeException('Staff account not found.');
    $email = strtolower(trim($email !== '' ? $email : (string)$user['email']));
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) throw new RuntimeException('The staff account requires a valid email address.');

    $token = bin2hex(random_bytes(32));
    $tokenHash = hash('sha256', $token);
    $expiresHours = max(1, min(168, $expiresHours));
    $pdo->prepare('UPDATE `' . table('staff_account_invites') . '` SET expires_at=NOW(),updated_at=NOW() WHERE user_id=? AND used_at IS NULL AND expires_at>NOW()')->execute([$userId]);
    $insert = $pdo->prepare('INSERT INTO `' . table('staff_account_invites') . '` (user_id,email,token_hash,expires_at,created_by,created_at) VALUES (?,?,?,DATE_ADD(NOW(),INTERVAL ' . $expiresHours . ' HOUR),?,NOW())');
    $insert->execute([$userId,$email,$tokenHash,(int)(current_user()['id'] ?? 0) ?: null]);
    $inviteId = (int)$pdo->lastInsertId();
    $activationUrl = app_absolute_url('account-activate.php?token=' . rawurlencode($token));
    $delivery = queue_template_email($templateKey, $email, [
        'recipient_name' => (string)$user['name'], 'role_name' => (string)$user['role_name'],
        'activation_url' => $activationUrl, 'expires_hours' => (string)$expiresHours,
    ], ['type' => 'staff_account_invite', 'user_id' => $userId, 'invite_id' => $inviteId], true);
    $pdo->prepare('UPDATE `' . table('staff_account_invites') . '` SET email_outbox_id=?,last_sent_at=NOW() WHERE id=?')->execute([(int)$delivery['id'],$inviteId]);
    return array_merge($delivery, ['invite_id' => $inviteId, 'activation_url' => $activationUrl]);
}

function email_delivery_label(string $status): string
{
    return match ($status) {
        'sent' => 'Accepted by mail server',
        'pending' => 'Queued',
        'processing' => 'Sending',
        'retry' => 'Retry scheduled',
        'failed' => 'Delivery failed',
        'cancelled' => 'Cancelled',
        default => 'Not sent',
    };
}

function email_delivery_feedback(array $delivery,string $label='Email'): array
{
    $status=(string)($delivery['status']??'pending');
    $error=trim((string)($delivery['error']??''));
    if($status==='sent')return ['type'=>'success','message'=>$label.' accepted by the mail server. Inbox placement still depends on the recipient provider.'];
    if($status==='failed')return ['type'=>'warning','message'=>$label.' was not delivered. The record was saved. '.($error!==''?'Error: '.$error:'Open Email & SMTP to review the failure.')];
    if($status==='retry')return ['type'=>'warning','message'=>$label.' was not accepted and a retry was scheduled. The record was saved. '.($error!==''?'Error: '.$error:'')];
    if($status==='processing')return ['type'=>'warning','message'=>$label.' is currently being sent.'];
    if($status==='not_available')return ['type'=>'warning','message'=>$label.' was not created because the recipient has no valid email address. The record was saved.'];
    if($status==='skipped')return ['type'=>'warning','message'=>$label.' was not processed because it is no longer eligible for delivery.'];
    return ['type'=>'warning','message'=>$label.' is queued for automatic delivery.'];
}

function email_delivery_badge_class(string $status): string
{
    if (in_array($status,['not_available','skipped'],true)) return 'email-failed';
    return 'email-' . (in_array($status,['sent','pending','processing','retry','failed','cancelled'],true)?$status:'pending');
}

function reminder_configuration(): array
{
    $raw=(string)setting_get('reminders','appointment_lead_hours','72,24,3');
    $leads=[];
    foreach(preg_split('/\s*,\s*/',$raw,-1,PREG_SPLIT_NO_EMPTY)?:[] as $lead){
        $hours=(int)$lead;
        if($hours>=1&&$hours<=336)$leads[$hours]=$hours;
    }
    if(!$leads)$leads=[72=>72,24=>24,3=>3];
    rsort($leads,SORT_NUMERIC);
    return [
        'enabled'=>(string)setting_get('reminders','enabled','1')==='1',
        'appointment_lead_hours'=>array_values($leads),
        'followups'=>(string)setting_get('reminders','followups_enabled','1')==='1',
        'recalls'=>(string)setting_get('reminders','recalls_enabled','1')==='1',
        'automatic_interval_minutes'=>max(15,min(1440,(int)setting_get('reminders','automatic_interval_minutes','30'))),
    ];
}

function appointment_time_until_label(string $startAt, ?string $timezoneName = null, ?DateTimeImmutable $now = null): string
{
    $timezoneName = trim((string) $timezoneName);
    if ($timezoneName === '') $timezoneName = (string) app_config('app.timezone', 'Asia/Manila');
    try {
        $timezone = new DateTimeZone($timezoneName);
    } catch (Throwable) {
        $timezone = new DateTimeZone((string) app_config('app.timezone', 'Asia/Manila'));
    }

    try {
        $start = new DateTimeImmutable($startAt, $timezone);
    } catch (Throwable) {
        return 'soon';
    }
    $now ??= new DateTimeImmutable('now', $timezone);
    $seconds = max(0, $start->getTimestamp() - $now->getTimestamp());

    if ($seconds < 60) return 'less than a minute';

    $totalMinutes = (int) ceil($seconds / 60);
    if ($totalMinutes < 60) {
        return $totalMinutes . ' minute' . ($totalMinutes === 1 ? '' : 's');
    }

    $hours = intdiv($totalMinutes, 60);
    $minutes = $totalMinutes % 60;
    if ($hours < 24) {
        $label = $hours . ' hour' . ($hours === 1 ? '' : 's');
        if ($minutes > 0 && $hours < 6) {
            $label .= ' ' . $minutes . ' minute' . ($minutes === 1 ? '' : 's');
        }
        return $label;
    }

    $days = intdiv($hours, 24);
    $remainingHours = $hours % 24;
    $label = $days . ' day' . ($days === 1 ? '' : 's');
    if ($remainingHours > 0 && $days < 3) {
        $label .= ' ' . $remainingHours . ' hour' . ($remainingHours === 1 ? '' : 's');
    }
    return $label;
}

function reminder_queue_once(PDO $pdo,string $entityType,int $entityId,int $patientId,string $reminderKey,string $email,string $subject,string $html,string $text,string $scheduledFor,array $metadata=[]): bool
{
    if(!filter_var($email,FILTER_VALIDATE_EMAIL))return false;
    $stmt=$pdo->prepare('INSERT IGNORE INTO `' . table('appointment_reminders') . '` (entity_type,entity_id,patient_id,reminder_key,channel,scheduled_for,status,created_at) VALUES (?,?,?,?,"email",?,"queued",NOW())');
    $stmt->execute([$entityType,$entityId,$patientId,$reminderKey,$scheduledFor]);
    if($stmt->rowCount()!==1)return false;
    $reminderId=(int)$pdo->lastInsertId();
    try{
        $outboxId=queue_mail_message($email,$subject,$html,$text,array_merge($metadata,['type'=>'automatic_reminder','reminder_id'=>$reminderId,'entity_type'=>$entityType,'entity_id'=>$entityId]));
        $pdo->prepare('UPDATE `' . table('appointment_reminders') . '` SET outbox_id=?,updated_at=NOW() WHERE id=?')->execute([$outboxId,$reminderId]);
        return true;
    }catch(Throwable $exception){
        $pdo->prepare('UPDATE `' . table('appointment_reminders') . '` SET status="failed",last_error=?,updated_at=NOW() WHERE id=?')->execute([substr($exception->getMessage(),0,1000),$reminderId]);
        return false;
    }
}

function run_due_patient_reminders(?PDO $pdo=null,int $limit=200): array
{
    $pdo??=db();$config=reminder_configuration();$result=['queued'=>0,'skipped'=>0,'failed'=>0];
    if(!$config['enabled']||!schema_table_exists(table('appointment_reminders')))return $result;
    $limit=max(1,min(500,$limit));$now=time();$maxLead=max($config['appointment_lead_hours']);
    $stmt=$pdo->query('SELECT a.id,a.patient_id,a.start_at,a.end_at,a.service_name,a.provider_type,a.dentist_name,a.inhouse_provider_name,a.chair_name,a.branch_id,p.first_name,p.last_name,p.email,b.name branch_name,b.timezone branch_timezone FROM `' . table('appointments') . '` a JOIN `' . table('patients') . '` p ON p.id=a.patient_id LEFT JOIN `' . table('branches') . '` b ON b.id=a.branch_id WHERE a.start_at>NOW() AND a.start_at<=DATE_ADD(NOW(),INTERVAL '.($maxLead+24).' HOUR) AND a.status NOT IN ("cancelled","no_show","completed") AND p.email IS NOT NULL AND p.email<>"" ORDER BY a.start_at LIMIT '.$limit);
    $appointmentLeadHours=$config['appointment_lead_hours'];sort($appointmentLeadHours,SORT_NUMERIC);
    foreach($stmt->fetchAll() as $row){
        $timezoneName=trim((string)($row['branch_timezone']??''));
        if($timezoneName==='')$timezoneName=(string)app_config('app.timezone','Asia/Manila');
        try{$appointmentTimezone=new DateTimeZone($timezoneName);}catch(Throwable){$appointmentTimezone=new DateTimeZone((string)app_config('app.timezone','Asia/Manila'));}
        try{$startDateTime=new DateTimeImmutable((string)$row['start_at'],$appointmentTimezone);}catch(Throwable){continue;}
        $startTs=$startDateTime->getTimestamp();
        // Queue only the nearest reminder that is currently due. This avoids
        // sending every older missed lead time when the reminder runner resumes.
        foreach($appointmentLeadHours as $hours){
            $dueTs=$startTs-($hours*3600);if($dueTs>$now)continue;
            $key='appointment_'.$hours.'h_'.$startDateTime->format('YmdHi');
            $provider=($row['provider_type']??'dentist')==='inhouse'?(string)($row['inhouse_provider_name']?:'In-house clinic team'):(string)($row['dentist_name']?:'Assigned dentist');
            $calendar=app_absolute_url(appointment_calendar_relative_url(['id'=>(int)$row['id'],'patient_id'=>(int)$row['patient_id'],'start_at'=>(string)$row['start_at']],true));
            // The configured lead (for example 3 hours) decides when the message
            // becomes eligible. The patient-facing wording must use the real time
            // remaining at delivery, especially when the runner resumes late.
            $actualNow=new DateTimeImmutable('now',$appointmentTimezone);
            $lead=appointment_time_until_label((string)$row['start_at'],$timezoneName,$actualNow);
            $message=email_template_message('appointment_reminder',[
                'recipient_name'=>(string)$row['first_name'],
                'lead_time'=>$lead,
                'appointment_datetime'=>$startDateTime->format('F j, Y g:i A'),
                'service_name'=>(string)$row['service_name'],
                'provider_name'=>$provider,
                'branch_name'=>(string)($row['branch_name']?:'Dental clinic'),
                'calendar_url'=>$calendar,
            ]);
            $actualLeadMinutes=max(0,(int)ceil(($startTs-$actualNow->getTimestamp())/60));
            $ok=reminder_queue_once($pdo,'appointment',(int)$row['id'],(int)$row['patient_id'],$key,(string)$row['email'],$message['subject'],$message['html'],$message['text'],date('Y-m-d H:i:s',$dueTs),['appointment_id'=>(int)$row['id'],'lead_hours'=>$hours,'actual_lead_minutes'=>$actualLeadMinutes,'appointment_timezone'=>$timezoneName,'template_key'=>'appointment_reminder']);
            $ok?$result['queued']++:$result['skipped']++;
            break;
        }
    }
    if($config['followups']){
        $serviceProviderExpression = schema_column_exists(table('service_catalog'), 'requires_dentist')
            ? 'COALESCE(s.requires_dentist,CASE WHEN s.provider_type="inhouse" THEN 0 ELSE 1 END)'
            : 'CASE WHEN s.provider_type="inhouse" THEN 0 ELSE 1 END';
        $stmt=$pdo->query('SELECT f.id,f.patient_id,f.purpose,f.recommended_date,f.instructions,p.first_name,p.email,COALESCE(u.name,cu.name) dentist,' . $serviceProviderExpression . ' requires_dentist FROM `' . table('follow_ups') . '` f JOIN `' . table('patients') . '` p ON p.id=f.patient_id LEFT JOIN `' . table('users') . '` u ON u.id=f.dentist_user_id LEFT JOIN `' . table('users') . '` cu ON cu.id=p.primary_dentist_user_id LEFT JOIN `' . table('service_catalog') . '` s ON s.id=f.service_id WHERE f.status="pending" AND f.recommended_date<=CURDATE() AND f.recommended_date>=DATE_SUB(CURDATE(),INTERVAL 30 DAY) AND p.email IS NOT NULL AND p.email<>"" ORDER BY f.recommended_date LIMIT '.$limit);
        foreach($stmt->fetchAll() as $row){
            $key='followup_due_'.str_replace('-','',(string)$row['recommended_date']);$booking=app_absolute_url('appointment-returning.php');
            $inhouse=(int)($row['requires_dentist']??1)===0;
            $message=email_template_message('followup_reminder',[
                'recipient_name'=>(string)$row['first_name'],
                'due_date'=>date('F j, Y',strtotime((string)$row['recommended_date'])),
                'purpose'=>(string)$row['purpose'],
                'provider_label'=>$inhouse?'Care team':'Dentist',
                'provider_name'=>$inhouse?'In-house care team':(string)($row['dentist']?:'Assigned dentist'),
                'instructions'=>(string)$row['instructions'],
                'booking_url'=>$booking,
            ]);
            $ok=reminder_queue_once($pdo,'follow_up',(int)$row['id'],(int)$row['patient_id'],$key,(string)$row['email'],$message['subject'],$message['html'],$message['text'],date('Y-m-d 08:00:00',strtotime((string)$row['recommended_date'])),['follow_up_id'=>(int)$row['id'],'template_key'=>'followup_reminder']);
            $ok?$result['queued']++:$result['skipped']++;
        }
    }
    if($config['recalls']){
        $stmt=$pdo->query('SELECT r.id,r.patient_id,r.recall_type,r.due_date,p.first_name,p.email FROM `' . table('recalls') . '` r JOIN `' . table('patients') . '` p ON p.id=r.patient_id WHERE r.status IN ("due_soon","due","overdue") AND r.due_date<=CURDATE() AND r.due_date>=DATE_SUB(CURDATE(),INTERVAL 60 DAY) AND p.email IS NOT NULL AND p.email<>"" ORDER BY r.due_date LIMIT '.$limit);
        foreach($stmt->fetchAll() as $row){
            $key='recall_due_'.str_replace('-','',(string)$row['due_date']);$booking=app_absolute_url('appointment-returning.php');
            $message=email_template_message('recall_reminder',[
                'recipient_name'=>(string)$row['first_name'],
                'recall_type'=>(string)$row['recall_type'],
                'due_date'=>date('F j, Y',strtotime((string)$row['due_date'])),
                'booking_url'=>$booking,
            ]);
            $ok=reminder_queue_once($pdo,'recall',(int)$row['id'],(int)$row['patient_id'],$key,(string)$row['email'],$message['subject'],$message['html'],$message['text'],date('Y-m-d 08:00:00',strtotime((string)$row['due_date'])),['recall_id'=>(int)$row['id'],'template_key'=>'recall_reminder']);
            $ok?$result['queued']++:$result['skipped']++;
        }
    }
    return $result;
}

function maybe_run_automatic_reminders(?PDO $pdo=null): void
{
    try{
        $pdo??=db();$config=reminder_configuration();if(!$config['enabled']||!schema_table_exists(table('appointment_reminders')))return;
        $last=strtotime((string)setting_get('reminders','last_automatic_run_at','1970-01-01'))?:0;
        if(time()-$last<($config['automatic_interval_minutes']*60))return;
        $lockName=substr('dentalcare_reminders_'.preg_replace('/[^A-Za-z0-9_]/','_',table('appointment_reminders')),0,64);
        $lockStmt=$pdo->prepare('SELECT GET_LOCK(?,0)');$lockStmt->execute([$lockName]);
        $lock=(int)($lockStmt->fetchColumn()?:0);if($lock!==1)return;
        try{
            $reminderResult=run_due_patient_reminders($pdo,200);
            setting_set('reminders','last_automatic_run_at',date('Y-m-d H:i:s'));
            if((int)($reminderResult['queued']??0)>0)run_email_queue($pdo,email_queue_configuration()['batch_size'],'automatic_reminders');
        }
        finally{$releaseStmt=$pdo->prepare('SELECT RELEASE_LOCK(?)');$releaseStmt->execute([$lockName]);}
    }catch(Throwable $exception){error_log('Automatic reminder run failed: '.$exception->getMessage());}
}

function settings_table_ensure(): void
{
    static $ensured = false;
    if ($ensured) {
        return;
    }
    $ensured = true;
    try {
        $tableName = table('settings');
        $pdo = db();
        $pdo->exec(
            'CREATE TABLE IF NOT EXISTS `' . $tableName . '` ('
            . '`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,'
            . '`group_name` VARCHAR(80) NOT NULL,'
            . '`setting_key` VARCHAR(120) NOT NULL,'
            . '`setting_value` LONGTEXT NULL,'
            . '`is_secret` TINYINT(1) NOT NULL DEFAULT 0,'
            . '`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,'
            . '`updated_at` DATETIME NULL,'
            . 'PRIMARY KEY (`id`),'
            . 'UNIQUE KEY `uq_setting` (`group_name`,`setting_key`)'
            . ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        );

        $columns = [];
        $stmt = $pdo->prepare('SELECT column_name FROM information_schema.columns WHERE table_schema=? AND table_name=?');
        $stmt->execute([(string) app_config('database.name'), $tableName]);
        foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) ?: [] as $column) {
            $columns[strtolower((string) $column)] = true;
        }
        if (!isset($columns['group_name'])) {
            $pdo->exec('ALTER TABLE `' . $tableName . '` ADD `group_name` VARCHAR(80) NOT NULL DEFAULT \'system\' AFTER `id`');
            $columns['group_name'] = true;
        }
        if (!isset($columns['setting_key'])) {
            $pdo->exec('ALTER TABLE `' . $tableName . '` ADD `setting_key` VARCHAR(120) NOT NULL DEFAULT \'unknown\' AFTER `group_name`');
            $columns['setting_key'] = true;
        }
        if (!isset($columns['setting_value'])) {
            $pdo->exec('ALTER TABLE `' . $tableName . '` ADD `setting_value` LONGTEXT NULL AFTER `setting_key`');
        }
        if (!isset($columns['is_secret'])) {
            $pdo->exec('ALTER TABLE `' . $tableName . '` ADD `is_secret` TINYINT(1) NOT NULL DEFAULT 0 AFTER `setting_value`');
        }
        if (!isset($columns['created_at'])) {
            $pdo->exec('ALTER TABLE `' . $tableName . '` ADD `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `is_secret`');
        }
        if (!isset($columns['updated_at'])) {
            $pdo->exec('ALTER TABLE `' . $tableName . '` ADD `updated_at` DATETIME NULL AFTER `created_at`');
        }
    } catch (Throwable $exception) {
        error_log('Settings table auto-repair failed: ' . $exception->getMessage());
    }
}

function dental_request_cache_key(string $namespace, array $parts = []): string
{
    return $namespace . ':' . hash('sha256', json_encode($parts, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE));
}

function setting_request_cache_clear(?string $group = null, ?string $key = null): void
{
    if (!isset($GLOBALS['DENTAL_SETTING_REQUEST_CACHE']) || !is_array($GLOBALS['DENTAL_SETTING_REQUEST_CACHE'])) {
        $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'] = [];
        return;
    }

    if ($group === null || $key === null) {
        $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'] = [];
        return;
    }

    unset($GLOBALS['DENTAL_SETTING_REQUEST_CACHE'][$group . "\0" . $key]);
}

function setting_get(string $group, string $key, mixed $default = null): mixed
{
    if (!isset($GLOBALS['DENTAL_SETTING_REQUEST_CACHE']) || !is_array($GLOBALS['DENTAL_SETTING_REQUEST_CACHE'])) {
        $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'] = [];
    }

    $cacheKey = $group . "\0" . $key;
    if (array_key_exists($cacheKey, $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'])) {
        $cached = $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'][$cacheKey];
        if (is_array($cached) && array_key_exists('found', $cached)) {
            return !empty($cached['found']) ? ($cached['value'] ?? null) : $default;
        }
        // Compatibility with values cached by an older request helper.
        return $cached;
    }

    try {
        settings_table_ensure();
        $stmt = db()->prepare('SELECT setting_value FROM `' . table('settings') . '` WHERE group_name = ? AND setting_key = ? LIMIT 1');
        $stmt->execute([$group, $key]);
        $value = $stmt->fetchColumn();
        $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'][$cacheKey] = [
            'found' => $value !== false,
            'value' => $value !== false ? $value : null,
        ];
        return $value === false ? $default : $value;
    } catch (Throwable) {
        $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'][$cacheKey] = ['found' => false, 'value' => null];
        return $default;
    }
}

function setting_set(string $group, string $key, ?string $value, bool $secret = false): void
{
    settings_table_ensure();
    $stmt = db()->prepare(
        'INSERT INTO `' . table('settings') . '` (group_name, setting_key, setting_value, is_secret, created_at) '
        . 'VALUES (?, ?, ?, ?, NOW()) '
        . 'ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), is_secret = VALUES(is_secret), updated_at = NOW()'
    );
    $stmt->execute([$group, $key, $value, $secret ? 1 : 0]);
    if (!isset($GLOBALS['DENTAL_SETTING_REQUEST_CACHE']) || !is_array($GLOBALS['DENTAL_SETTING_REQUEST_CACHE'])) {
        $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'] = [];
    }
    $GLOBALS['DENTAL_SETTING_REQUEST_CACHE'][$group . "\0" . $key] = [
        'found' => true,
        'value' => $value,
    ];
}

function public_intake_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) {
        return $ready;
    }
    try {
        $stmt = db()->prepare(
            'SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = ?'
        );
        $stmt->execute([(string) app_config('database.name'), table('appointment_requests')]);
        $ready = (int) $stmt->fetchColumn() === 1;
    } catch (Throwable) {
        $ready = false;
    }
    return $ready;
}

function public_clinic_profile(): array
{
    static $profile;
    if ($profile !== null) {
        return $profile;
    }

    $mail = (array) app_config('mail', []);
    $profile = [
        'name' => (string) app_config('app.name', 'Dental Clinic'),
        'tagline' => 'Thoughtful dental care for every stage of life',
        'intro' => 'Book your visit online, review clinic information, and let our team prepare for your dental needs before you arrive.',
        'phone' => '',
        'email' => (string) ($mail['from_email'] ?? ''),
        'address' => '',
        'hours' => "Monday–Friday: 8:00 AM–5:00 PM\nSaturday: By appointment\nSunday: Closed",
        'services' => "Dental Examination\nOral Prophylaxis\nTooth Restoration\nTooth Extraction\nRoot Canal Consultation\nOrthodontic Consultation\nDenture Consultation\nEmergency Dental Visit",
        'appointment_note' => 'Appointment requests are reviewed by the clinic. Your selected schedule is not final until you receive a confirmation email or call.',
    ];

    try {
        $row = db()->query(
            'SELECT id,name,email,phone,address,timezone FROM `' . table('branches') . '` WHERE is_active = 1 ORDER BY id LIMIT 1'
        )->fetch();
        if ($row) {
            $profile['name'] = (string) ($row['name'] ?: $profile['name']);
            $profile['email'] = (string) ($row['email'] ?: $profile['email']);
            $profile['phone'] = (string) ($row['phone'] ?: $profile['phone']);
            $profile['address'] = (string) ($row['address'] ?: $profile['address']);
            $profile['timezone'] = (string) ($row['timezone'] ?? app_config('app.timezone', 'Asia/Manila'));
            try {
                $days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];
                $hoursStmt = db()->prepare('SELECT day_of_week,is_open,open_time,close_time FROM `' . table('branch_operating_hours') . '` WHERE branch_id=? ORDER BY day_of_week');
                $hoursStmt->execute([(int) $row['id']]);
                $hourLines = [];
                foreach ($hoursStmt->fetchAll() as $hourRow) {
                    $dayName = $days[(int) $hourRow['day_of_week']] ?? 'Day';
                    $hourLines[] = $dayName . ': ' . ((bool) $hourRow['is_open'] ? date('g:i A', strtotime((string) $hourRow['open_time'])) . '–' . date('g:i A', strtotime((string) $hourRow['close_time'])) : 'Closed');
                }
                if ($hourLines) $profile['hours'] = implode("
", $hourLines);
                $serviceRows = db()->query('SELECT name FROM `' . table('service_catalog') . '` WHERE is_active=1 AND is_public=1 AND (branch_id=' . (int) $row['id'] . ' OR branch_id IS NULL) ORDER BY name')->fetchAll(PDO::FETCH_COLUMN);
                if ($serviceRows) $profile['services'] = implode("
", array_map('strval', $serviceRows));
            } catch (Throwable) {
            }
        }
    } catch (Throwable) {
        $profile['timezone'] = (string) app_config('app.timezone', 'Asia/Manila');
    }

    // Clinic & Website settings are the authoritative public values. Branch
    // records supply sensible defaults only; otherwise a branch address or
    // schedule would silently overwrite a change made on the Settings page.
    $profile['phone'] = (string) setting_get('clinic', 'phone', $profile['phone']);
    $profile['address'] = (string) setting_get('clinic', 'address', $profile['address']);
    $profile['tagline'] = (string) setting_get('clinic', 'public_tagline', $profile['tagline']);
    $profile['intro'] = (string) setting_get('clinic', 'public_intro', $profile['intro']);
    $profile['public_email'] = (string) setting_get('clinic', 'public_email', $profile['email']);
    $profile['hours'] = (string) setting_get('clinic', 'public_hours', $profile['hours']);
    $profile['services'] = (string) setting_get('clinic', 'public_services', $profile['services']);
    $profile['appointment_note'] = (string) setting_get('clinic', 'public_appointment_note', $profile['appointment_note']);
    return $profile;
}

function public_lines(string $value): array
{
    return array_values(array_filter(array_map('trim', preg_split('/\R+/', $value) ?: []), static fn(string $line): bool => $line !== ''));
}


function public_landing_content(): array
{
    $clinic = public_clinic_profile();
    $defaults = [
        'hero_eyebrow' => 'Dental information and appointment center',
        'hero_title' => $clinic['name'],
        'primary_cta_label' => 'New Patient Appointment',
        'secondary_cta_label' => 'Returning Patient Appointment',
        'trust_items' => "Secure online intake\nClinic-reviewed schedules\nEmail request confirmation",
        'info_eyebrow' => 'Dental information center',
        'info_title' => 'Information for patients and families',
        'info_body' => 'Use this website to learn about the clinic, review available services, submit an appointment request, and reach the clinic team. Staff members use a separate secure portal for patient records and clinic operations.',
        'info_points' => "Before your visit|Prepare your contact details, health information relevant to dental care, current medicines, allergies, and the reason for your dental visit.\nAppointment review|The clinic reviews every online request against dentist, chair, and clinic availability before confirming the final schedule.\nClinical privacy|Public forms collect intake information only. Detailed dental records are managed inside the protected clinic system.",
        'appointment_eyebrow' => 'Appointment registration',
        'appointment_title' => 'Choose the appointment form that applies to you',
        'appointment_intro' => 'Complete only one form. Your preferred schedule remains a request until the clinic confirms it.',
        'services_eyebrow' => 'Dental services',
        'services_title' => 'Common services available for appointment requests',
        'services_intro' => 'A compact preview is shown here. The appointment form contains the full clinic-reviewed service list and allows the clinic to finalize the exact service after assessment.',
        'care_flow_eyebrow' => 'Patient care flow',
        'care_flow_title' => 'From online request to follow-up care',
        'care_flow_steps' => "Submit appointment request\nClinic reviews availability\nReceive confirmation\nCheck in at the clinic\nDental examination and treatment\nBilling and follow-up",
        'contact_eyebrow' => 'Contact the clinic',
        'contact_title' => 'Need help choosing the correct appointment?',
        'contact_body' => 'Contact the clinic for urgent concerns, accessibility needs, complex procedures, or questions about your existing appointment.',
        'portal_eyebrow' => 'Secure clinic portal',
        'portal_title' => 'Already part of the clinic team?',
        'portal_body' => 'Dentists, assistants, receptionists, cashiers, and administrators sign in here. Installer downloads and setup tools are intentionally kept in a separate section.',
        'clinic_info_heading' => 'Clinic Information',
        'clinic_info_subtitle' => 'Plan your visit before you arrive',
        'clinic_info_phone_label' => 'Phone',
        'clinic_info_email_label' => 'Email',
        'clinic_info_address_label' => 'Address',
        'clinic_info_hours_label' => 'Clinic Hours',
        'clinic_info_contact_link_label' => 'View contact information →',
        'first_visit_label' => 'First visit',
        'first_visit_icon' => 'NP',
        'first_visit_title' => 'Request a First Appointment',
        'first_visit_body' => 'Register your basic patient information, health details relevant to dental care, dental concern, and preferred appointment schedule.',
        'first_visit_items' => "Personal and contact information
Dental treatment safety alerts and medicines
Guardian details for minors
Preferred and alternate schedules",
        'first_visit_cta_label' => 'Submit a First-Visit Request →',
        'returning_visit_label' => 'Existing record',
        'returning_visit_icon' => 'RP',
        'returning_visit_title' => 'Request Another Appointment',
        'returning_visit_body' => 'Request another visit using your existing patient information and report any health changes relevant to dental care or contact changes.',
        'returning_visit_items' => "Patient identity verification
Updated contact information
Dental-safety history changes
Preferred and alternate schedules",
        'returning_visit_cta_label' => 'Submit a Returning-Patient Request →',
        'dentists_eyebrow' => 'Our dentists',
        'dentists_title' => 'Meet the clinic team',
        'dentists_intro' => 'Choose a dentist when requesting an appointment, or select any available dentist.',
        'services_primary_cta_label' => 'Request a first visit',
        'services_secondary_cta_label' => 'Request another visit',
        'services_more_label' => '+%d more in the appointment form',
        'contact_call_label' => 'Call {phone}',
        'contact_email_label' => 'Email the Clinic',
        'contact_card_phone_label' => 'Phone',
        'contact_card_email_label' => 'Email',
        'contact_card_hours_label' => 'Opening hours',
        'downloads_eyebrow' => 'Buyer installer access',
        'downloads_title' => 'DentalCare release packages',
        'downloads_intro' => "Visitors can evaluate the read-only demo. Purchased full installers require the signed license key issued for the buyer's deployment domain before the ZIP download starts.",
        'download_full_title' => 'Licensed buyer installer',
        'download_full_body' => 'For paid clinic deployment. The buyer enters the signed DentalCare license key first; the installed copy must still activate that same license on its own domain.',
        'download_full_cta_label' => 'Download with License Key →',
        'download_demo_title' => 'Read-only demo installer',
        'download_demo_body' => 'For evaluation only. The demo copy is locked to read-only mode and cannot add, edit, delete, email, generate installers, or issue licenses.',
        'download_demo_cta_label' => 'Download Demo Installer →',
        'download_support_title' => 'Purchase and support',
        'download_support_body' => 'Need a full installer? Purchase first, then use the signed license key sent by MTBCustoms to unlock the buyer download.',
        'download_support_cta_label' => 'Contact MTBCustoms →',
        'developer_eyebrow' => 'About the developer',
        'developer_title' => 'Developed and programmed by MTBCustoms',
        'developer_body' => 'DentalCare is developed and programmed by MTBCustoms for practical clinic workflows: appointments, queueing, treatment records, cashier payments, inventory, reporting, and release-safe deployment.',
        'developer_card_label' => 'Developer & Programmer',
        'developer_name' => 'MTBCustoms',
        'developer_email' => 'mtbcustomprints@gmail.com',
        'developer_card_body' => 'Focused on usable role-based systems for clinics, schools, and service operations.',
        'portal_staff_cta_label' => 'Open Staff Portal →',
        'portal_patient_cta_label' => 'Open Patient Portal →',
        'section_visibility_json' => '{}',
        'section_order_json' => '{}',
        'hero_image' => '',
        'hero_image_alt' => 'Clinic hero image',
        'information_image' => '',
        'information_image_alt' => 'Clinic information image',
        'appointment_image' => '',
        'appointment_image_alt' => 'Appointment section image',
        'dentists_image' => '',
        'dentists_image_alt' => 'Clinic team image',
        'services_image' => '',
        'services_image_alt' => 'Dental services image',
        'care_flow_image' => '',
        'care_flow_image_alt' => 'Patient care flow image',
        'contact_image' => '',
        'contact_image_alt' => 'Clinic contact image',
        'downloads_image' => '',
        'downloads_image_alt' => 'Installer download image',
        'developer_image' => '',
        'developer_image_alt' => 'Developer image',
        'portal_image' => '',
        'portal_image_alt' => 'Secure portal image',
    ];
    $content = [];
    foreach ($defaults as $key => $default) {
        $content[$key] = (string) setting_get('landing', $key, (string) $default);
    }
    if (trim($content['hero_title']) === '') {
        $content['hero_title'] = (string) $clinic['name'];
    }
    return $content;
}

function public_website_slugify(string $value): string
{
    $slug = strtolower(trim($value));
    $slug = preg_replace('/[^a-z0-9]+/', '-', $slug) ?: '';
    $slug = trim($slug, '-');
    return substr($slug ?: 'page', 0, 80);
}


function public_website_safe_url(string $url): bool
{
    $url = trim($url);
    if ($url === '' || preg_match('/\s/', $url) || preg_match('/^(?:javascript|data):/i', $url)) return false;
    return (bool) preg_match('/^(https?:\/\/|mailto:|tel:|[A-Za-z0-9_\-.\/]+(?:\.php)?(?:[?#][^\s]*)?|#[A-Za-z0-9_-]+)$/', $url);
}

function public_website_reserved_slugs(): array
{
    $reserved = ['index','login','logout','install','license','bootstrap','route','download','api','app','config','database','handlers','modules','resources','storage','tools','public'];
    $routesPath = DENTAL_ROOT . '/config/routes.php';
    if (is_file($routesPath)) {
        $routes = require $routesPath;
        if (is_array($routes)) {
            foreach (array_keys($routes) as $route) {
                $reserved[] = public_website_slugify((string) $route);
            }
        }
    }
    return array_values(array_unique(array_filter($reserved)));
}

function public_website_slug_reserved(string $slug): bool
{
    return in_array(public_website_slugify($slug), public_website_reserved_slugs(), true);
}

function public_website_image_path_allowed(string $path): bool
{
    $path = trim($path);
    if ($path === '' || str_contains($path, '..') || preg_match('/\s/', $path)) return false;
    return (bool) preg_match('/^public\/uploads\/(?:website-pages|website-media)\/[A-Za-z0-9_.-]+\.(?:jpg|jpeg|png|webp)$/i', $path);
}

function public_website_media_items(): array
{
    $raw = (string) setting_get('website', 'media_json', '[]');
    $decoded = json_decode($raw, true);
    if (!is_array($decoded)) return [];
    $items = [];
    foreach ($decoded as $item) {
        if (!is_array($item)) continue;
        $id = preg_replace('/[^a-zA-Z0-9_-]/', '', (string)($item['id'] ?? ''));
        $path = trim((string)($item['path'] ?? ''));
        if ($id === '' || !public_website_image_path_allowed($path)) continue;
        $items[] = [
            'id' => $id,
            'path' => $path,
            'label' => substr(trim((string)($item['label'] ?? basename($path))), 0, 120),
            'alt' => substr(trim((string)($item['alt'] ?? '')), 0, 160),
            'source' => substr(trim((string)($item['source'] ?? 'media_library')), 0, 60),
            'uploaded_at' => (string)($item['uploaded_at'] ?? ''),
        ];
    }
    usort($items, static fn(array $a, array $b): int => strcmp((string)($b['uploaded_at'] ?? ''), (string)($a['uploaded_at'] ?? '')));
    return $items;
}

function public_website_save_media_items(array $items): void
{
    $clean = [];
    foreach ($items as $item) {
        if (!is_array($item)) continue;
        $path = trim((string)($item['path'] ?? ''));
        if (!public_website_image_path_allowed($path)) continue;
        $id = preg_replace('/[^a-zA-Z0-9_-]/', '', (string)($item['id'] ?? '')) ?: 'media_' . substr(sha1($path), 0, 12);
        $clean[] = [
            'id' => $id,
            'path' => $path,
            'label' => substr(trim((string)($item['label'] ?? basename($path))), 0, 120),
            'alt' => substr(trim((string)($item['alt'] ?? '')), 0, 160),
            'source' => substr(trim((string)($item['source'] ?? 'media_library')), 0, 60),
            'uploaded_at' => (string)($item['uploaded_at'] ?? date('c')),
        ];
    }
    setting_set('website', 'media_json', json_encode(array_values($clean), JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE));
}

function public_website_register_media(string $path, string $label = '', string $alt = '', string $source = 'upload'): void
{
    if (!public_website_image_path_allowed($path)) return;
    $items = public_website_media_items();
    foreach ($items as $item) {
        if (($item['path'] ?? '') === $path) return;
    }
    $items[] = [
        'id' => 'media_' . bin2hex(random_bytes(6)),
        'path' => $path,
        'label' => substr(trim($label) ?: basename($path), 0, 120),
        'alt' => substr(trim($alt), 0, 160),
        'source' => substr(trim($source) ?: 'upload', 0, 60),
        'uploaded_at' => date('c'),
    ];
    public_website_save_media_items($items);
}

function public_website_media_path_from_id(string $mediaId): string
{
    $mediaId = preg_replace('/[^a-zA-Z0-9_-]/', '', $mediaId);
    if ($mediaId === '') return '';
    foreach (public_website_media_items() as $item) {
        if (($item['id'] ?? '') === $mediaId) return (string) $item['path'];
    }
    return '';
}

function public_landing_section_defaults(): array
{
    return [
        'hero' => ['label' => 'Hero', 'order' => 10, 'visible' => true],
        'information' => ['label' => 'Clinic information', 'order' => 20, 'visible' => true],
        'appointment' => ['label' => 'Appointments', 'order' => 30, 'visible' => true],
        'dentists' => ['label' => 'Dentist/team cards', 'order' => 35, 'visible' => true],
        'services' => ['label' => 'Services', 'order' => 40, 'visible' => true],
        'care_flow' => ['label' => 'Care flow', 'order' => 50, 'visible' => true],
        'contact' => ['label' => 'Contact', 'order' => 60, 'visible' => true],
        'downloads' => ['label' => 'Buyer installer access', 'order' => 70, 'visible' => true],
        'developer' => ['label' => 'Developer credit section', 'order' => 80, 'visible' => true],
        'portal' => ['label' => 'Secure portal banner', 'order' => 90, 'visible' => true],
    ];
}

function public_landing_section_visible(string $key, ?array $landing = null): bool
{
    $defaults = public_landing_section_defaults();
    if (!isset($defaults[$key])) return true;
    $landing = $landing ?? public_landing_content();
    $raw = json_decode((string)($landing['section_visibility_json'] ?? '{}'), true);
    if (!is_array($raw) || !array_key_exists($key, $raw)) return (bool)$defaults[$key]['visible'];
    return !empty($raw[$key]);
}

function public_landing_section_order(string $key, ?array $landing = null): int
{
    $defaults = public_landing_section_defaults();
    $default = (int)($defaults[$key]['order'] ?? 50);
    $landing = $landing ?? public_landing_content();
    $raw = json_decode((string)($landing['section_order_json'] ?? '{}'), true);
    if (!is_array($raw) || !array_key_exists($key, $raw)) return $default;
    return max(1, min(999, (int)$raw[$key]));
}

function public_landing_section_image(string $key, ?array $landing = null): string
{
    $landing = $landing ?? public_landing_content();
    $path = trim((string)($landing[$key . '_image'] ?? ''));
    return public_website_image_path_allowed($path) ? $path : '';
}

function public_landing_section_image_alt(string $key, ?array $landing = null): string
{
    $landing = $landing ?? public_landing_content();
    return substr(trim((string)($landing[$key . '_image_alt'] ?? '')), 0, 160);
}

function public_website_social_links(): array
{
    $links = [];
    foreach (public_lines((string)setting_get('website', 'social_links', '')) as $line) {
        $parts = array_map('trim', explode('|', $line, 2));
        $label = substr($parts[0] ?? '', 0, 60);
        $url = trim((string)($parts[1] ?? ''));
        if ($label === '' || !public_website_safe_url($url)) continue;
        $links[] = ['label' => $label, 'url' => $url];
    }
    return array_slice($links, 0, 8);
}

function public_website_pages(): array
{
    $raw = (string) setting_get('website', 'pages_json', '[]');
    $decoded = json_decode($raw, true);
    if (!is_array($decoded)) {
        return [];
    }
    $pages = [];
    foreach ($decoded as $page) {
        if (!is_array($page)) continue;
        $id = preg_replace('/[^a-zA-Z0-9_-]/', '', (string)($page['id'] ?? ''));
        $title = trim((string)($page['title'] ?? ''));
        $slug = public_website_slugify((string)($page['slug'] ?? $title));
        if ($id === '' || $title === '' || $slug === '') continue;
        $pages[] = [
            'id' => $id,
            'title' => substr($title, 0, 160),
            'slug' => $slug,
            'nav_label' => substr(trim((string)($page['nav_label'] ?? $title)), 0, 80),
            'nav_order' => (int)($page['nav_order'] ?? 50),
            'status' => ((string)($page['status'] ?? 'draft')) === 'published' ? 'published' : 'draft',
            'show_in_nav' => !empty($page['show_in_nav']) ? 1 : 0,
            'excerpt' => substr(trim((string)($page['excerpt'] ?? '')), 0, 360),
            'content' => substr((string)($page['content'] ?? ''), 0, 30000),
            'layout_style' => in_array((string)($page['layout_style'] ?? 'split'), ['split', 'centered', 'poster', 'cards'], true) ? (string)($page['layout_style'] ?? 'split') : 'split',
            'theme_style' => in_array((string)($page['theme_style'] ?? 'clinic'), ['clinic', 'soft', 'premium', 'minimal'], true) ? (string)($page['theme_style'] ?? 'clinic') : 'clinic',
            'image_position' => in_array((string)($page['image_position'] ?? 'right'), ['right', 'left', 'top', 'background'], true) ? (string)($page['image_position'] ?? 'right') : 'right',
            'hero_image' => public_website_image_path_allowed((string)($page['hero_image'] ?? '')) ? (string)($page['hero_image'] ?? '') : '',
            'hero_image_alt' => substr(trim((string)($page['hero_image_alt'] ?? $title)), 0, 160),
            'content_image' => public_website_image_path_allowed((string)($page['content_image'] ?? '')) ? (string)($page['content_image'] ?? '') : '',
            'content_image_alt' => substr(trim((string)($page['content_image_alt'] ?? $title)), 0, 160),
            'cta_label' => substr(trim((string)($page['cta_label'] ?? '')), 0, 80),
            'cta_url' => public_website_safe_url(trim((string)($page['cta_url'] ?? ''))) ? trim((string)($page['cta_url'] ?? '')) : '',
            'feature_cards' => substr(trim((string)($page['feature_cards'] ?? '')), 0, 4000),
            'updated_at' => (string)($page['updated_at'] ?? ''),
        ];
    }
    usort($pages, static fn(array $a, array $b): int => [$a['nav_order'], $a['title']] <=> [$b['nav_order'], $b['title']]);
    return $pages;
}

function public_website_save_pages(array $pages): void
{
    setting_set('website', 'pages_json', json_encode(array_values($pages), JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE));
}

function public_website_page_find(string $slugOrId, bool $publishedOnly = false): ?array
{
    $needle = public_website_slugify($slugOrId);
    foreach (public_website_pages() as $page) {
        if ($publishedOnly && $page['status'] !== 'published') continue;
        if ($page['slug'] === $needle || $page['id'] === $slugOrId) {
            return $page;
        }
    }
    return null;
}

function public_website_custom_nav_links(): array
{
    $raw = (string) setting_get('website', 'nav_links_json', '[]');
    $decoded = json_decode($raw, true);
    if (!is_array($decoded)) return [];
    $links = [];
    foreach ($decoded as $link) {
        if (!is_array($link)) continue;
        $id = preg_replace('/[^a-zA-Z0-9_-]/', '', (string)($link['id'] ?? ''));
        $label = substr(trim((string)($link['label'] ?? '')), 0, 80);
        $url = trim((string)($link['url'] ?? ''));
        if ($id === '' || $label === '' || $url === '') continue;
        if (!preg_match('/^(https?:\/\/|mailto:|tel:|[A-Za-z0-9_\-.\/]+(?:\.php)?(?:[?#][^\s]*)?|#[A-Za-z0-9_-]+)$/', $url)) continue;
        $links[] = [
            'id' => $id,
            'label' => $label,
            'url' => $url,
            'nav_order' => (int)($link['nav_order'] ?? 90),
            'is_active' => !empty($link['is_active']) ? 1 : 0,
        ];
    }
    usort($links, static fn(array $a, array $b): int => [$a['nav_order'], $a['label']] <=> [$b['nav_order'], $b['label']]);
    return $links;
}

function public_website_save_custom_nav_links(array $links): void
{
    setting_set('website', 'nav_links_json', json_encode(array_values($links), JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE));
}

function public_website_nav_links(): array
{
    $links = [
        ['key' => 'home', 'url' => 'index.php', 'label' => 'Home', 'order' => 10],
        ['key' => 'information', 'url' => 'index.php#information', 'label' => 'Clinic Information', 'order' => 20],
        ['key' => 'services', 'url' => 'index.php#services', 'label' => 'Services', 'order' => 30],
        ['key' => 'appointment', 'url' => 'index.php#appointment', 'label' => 'Appointments', 'order' => 40],
        ['key' => 'contact', 'url' => 'index.php#contact', 'label' => 'Contact', 'order' => 50],
    ];
    foreach (public_website_pages() as $page) {
        if ($page['status'] !== 'published' || empty($page['show_in_nav'])) continue;
        $links[] = [
            'key' => 'page:' . $page['slug'],
            'url' => function_exists('dental_public_page_clean_url') ? dental_public_page_clean_url((string)$page['slug']) : ('website-page.php?slug=' . rawurlencode((string)$page['slug'])),
            'label' => $page['nav_label'] ?: $page['title'],
            'order' => (int)$page['nav_order'],
        ];
    }
    foreach (public_website_custom_nav_links() as $link) {
        if (empty($link['is_active'])) continue;
        $links[] = [
            'key' => 'custom:' . $link['id'],
            'url' => $link['url'],
            'label' => $link['label'],
            'order' => (int)$link['nav_order'],
        ];
    }
    usort($links, static fn(array $a, array $b): int => [$a['order'], $a['label']] <=> [$b['order'], $b['label']]);
    foreach ($links as &$link) {
        if (!empty($link['url']) && function_exists('dental_clean_url')) {
            $link['url'] = dental_clean_url((string) $link['url']);
        }
    }
    unset($link);
    return $links;
}

function public_form_signature(string $type, int $startedAt): string
{
    $key = (string) app_config('app.key', 'dental-public-form');
    return hash_hmac('sha256', $type . '|' . $startedAt, $key);
}


function queue_display_token(int $branchId): string
{
    if ($branchId < 1) return '';
    $key = (string) app_config('app.key', 'dental-queue-display');
    return hash_hmac('sha256', 'queue-display|' . $branchId, $key);
}

function queue_display_relative_url(int $branchId): string
{
    return 'queue_display.php?branch_id=' . $branchId . '&token=' . rawurlencode(queue_display_token($branchId));
}

function queue_display_token_valid(int $branchId, string $token): bool
{
    $expected = queue_display_token($branchId);
    return $expected !== '' && $token !== '' && hash_equals($expected, $token);
}

function verify_public_form(string $type): void
{
    verify_csrf();
    if (trim((string) ($_POST['website'] ?? '')) !== '') {
        throw new RuntimeException('The appointment request could not be submitted.');
    }
    $startedAt = (int) ($_POST['form_started'] ?? 0);
    $signature = (string) ($_POST['form_signature'] ?? '');
    if ($startedAt < 1 || !hash_equals(public_form_signature($type, $startedAt), $signature)) {
        throw new RuntimeException('The form has expired. Reload the page and try again.');
    }
    $elapsed = time() - $startedAt;
    if ($elapsed < 2 || $elapsed > 14400) {
        throw new RuntimeException('The form has expired. Reload the page and try again.');
    }
    $last = (int) ($_SESSION['last_public_appointment_submission'] ?? 0);
    if ($last > 0 && time() - $last < 45) {
        throw new RuntimeException('A request was recently submitted from this browser. Please wait before sending another request.');
    }
}

function public_reference(): string
{
    return 'APT-' . date('ymd') . '-' . strtoupper(bin2hex(random_bytes(3)));
}

function public_request_branch_id(): ?int
{
    try {
        $value = db()->query('SELECT id FROM `' . table('branches') . '` WHERE is_active = 1 ORDER BY id LIMIT 1')->fetchColumn();
        return $value !== false ? (int) $value : null;
    } catch (Throwable) {
        return null;
    }
}

function public_patient_age(?string $birthDate): ?int
{
    if (!$birthDate) {
        return null;
    }
    try {
        return (new DateTimeImmutable($birthDate))->diff(new DateTimeImmutable('today'))->y;
    } catch (Throwable) {
        return null;
    }
}

function public_request_email(string $recipient, string $firstName, string $reference, array $request, array $clinic, int $requestId=0): array
{
    if (!filter_var($recipient, FILTER_VALIDATE_EMAIL)) {
        return ['id'=>0,'status'=>'not_available','error'=>'No valid patient email address.','attempts'=>0];
    }
    $preferred = date('F j, Y', strtotime((string) $request['preferred_date'])) . ' at ' . date('g:i A', strtotime((string) $request['preferred_time']));
    try {
        return queue_template_email('appointment_request_received',$recipient,[
            'recipient_name'=>$firstName,
            'reference'=>$reference,
            'service_name'=>(string)$request['service_name'],
            'appointment_datetime'=>$preferred,
        ],['type'=>'public_appointment_request','reference'=>$reference,'request_id'=>$requestId],true);
    } catch (Throwable $mailError) {
        error_log('Appointment request receipt email could not be queued: ' . $mailError->getMessage());
        return ['id'=>0,'status'=>'failed','error'=>$mailError->getMessage(),'attempts'=>0];
    }
}


function clinic_request_notification(string $reference, array $request, array $clinic): void
{
    $recipient = (string) ($clinic['public_email'] ?? $clinic['email'] ?? '');
    if (!filter_var($recipient, FILTER_VALIDATE_EMAIL)) {
        return;
    }
    $html = '<h2>New online appointment request</h2>'
        . '<p><strong>Reference:</strong> ' . e($reference) . '<br>'
        . '<strong>Patient type:</strong> ' . e(ucfirst((string) $request['patient_type'])) . '<br>'
        . '<strong>Patient:</strong> ' . e((string) $request['first_name'] . ' ' . (string) $request['last_name']) . '<br>'
        . '<strong>Service:</strong> ' . e((string) $request['service_name']) . '<br>'
        . '<strong>Preferred date:</strong> ' . e((string) $request['preferred_date']) . ' ' . e((string) $request['preferred_time']) . '</p>'
        . '<p>Open the clinic system and review Online Appointment Requests.</p>';
    try {
        queue_mail_message(
            $recipient,
            'New online appointment request ' . $reference,
            $html,
            'A new online appointment request was submitted. Reference: ' . $reference,
            ['type' => 'clinic_appointment_request', 'reference' => $reference]
        );
    } catch (Throwable $exception) {
        error_log('Clinic appointment notification could not be queued: ' . $exception->getMessage());
    }
}

function license_feature(string $feature, bool $default = false): bool
{
    $status = dental_license_status();
    if (($status['mode'] ?? '') === 'owner_domain') {
        return true;
    }
    return (bool) (($status['features'][$feature] ?? $status['payload']['features'][$feature] ?? $default));
}

function branding_profile(): array
{
    static $brand;
    if ($brand !== null) {
        return $brand;
    }
    $clinic = public_clinic_profile();
    $brand = [
        'name' => (string) setting_get('branding', 'name', $clinic['name']),
        'short_name' => (string) setting_get('branding', 'short_name', 'DC'),
        'logo' => (string) setting_get('branding', 'logo', ''),
        'favicon' => (string) setting_get('branding', 'favicon', ''),
        'primary' => (string) setting_get('branding', 'primary', '#0f766e'),
        'secondary' => (string) setting_get('branding', 'secondary', '#17324d'),
        'accent' => (string) setting_get('branding', 'accent', '#e6a23c'),
        'footer_text' => (string) setting_get('branding', 'footer_text', 'Developed and programmed by MTBCustoms'),
    ];
    if (trim(strtolower((string) $brand['footer_text'])) === 'powered by syllabuild') {
        $brand['footer_text'] = 'Developed and programmed by MTBCustoms';
    }
    foreach (['primary', 'secondary', 'accent'] as $key) {
        if (!preg_match('/^#[0-9A-Fa-f]{6}$/', $brand[$key])) {
            $brand[$key] = ['primary' => '#0f766e', 'secondary' => '#17324d', 'accent' => '#e6a23c'][$key];
        }
    }
    return $brand;
}

function active_dentists(?int $branchId = null): array
{
    $withProfiles = doctor_workflow_schema_ready();
    $select = $withProfiles
        ? 'u.id,u.name,u.branch_id,dp.accepting_new_patients,dp.default_chair_name,dp.maximum_daily_appointments,ds.name specialty_name'
        : 'u.id,u.name,u.branch_id,1 accepting_new_patients,NULL default_chair_name,NULL maximum_daily_appointments,NULL specialty_name';
    $joins = $withProfiles
        ? ' LEFT JOIN `' . table('dentist_profiles') . '` dp ON dp.user_id=u.id LEFT JOIN `' . table('dentist_specialties') . '` ds ON ds.id=dp.specialty_id '
        : '';
    $sql = 'SELECT ' . $select . ' FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id ' . $joins . ' WHERE r.code="dentist" AND u.status="active"';
    $params = [];
    if ($branchId !== null) {
        $sql .= ' AND (u.branch_id=? OR u.branch_id IS NULL';
        $params[] = $branchId;
        if ($withProfiles) {
            $sql .= ' OR EXISTS(SELECT 1 FROM `' . table('dentist_branch_assignments') . '` dba WHERE dba.dentist_user_id=u.id AND dba.branch_id=? AND dba.status="active")';
            $params[] = $branchId;
        }
        $sql .= ')';
    }
    $sql .= ' ORDER BY name';
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

/**
 * Return dentists who may be offered for one public appointment service.
 * Public booking excludes inactive profiles, unverified credentials, dentists
 * who are not accepting new patients, and dentists whose configured service
 * scope does not include the selected service.
 */
function bookable_dentists_for_service(int $branchId, int $serviceId, bool $newPatient = false): array
{
    $dentists = active_dentists($branchId);
    if ($serviceId < 1) return [];
    $service = service_definition($serviceId, $branchId);
    if (!$service || service_provider_type($service) !== 'dentist') return [];
    $requiredPrivilege = service_required_privilege_code($service);

    $profileStmt = null;
    $assignmentCountStmt = null;
    $assignmentMatchStmt = null;
    if (doctor_workflow_schema_ready()) {
        $profileStmt = db()->prepare(
            'SELECT profile_status,accepting_new_patients FROM `' . table('dentist_profiles') . '` WHERE user_id=? LIMIT 1'
        );
        if (schema_table_exists(table('dentist_service_assignments'))) {
            $assignmentCountStmt = db()->prepare(
                'SELECT COUNT(*) FROM `' . table('dentist_service_assignments') . '` WHERE dentist_user_id=? AND is_active=1'
            );
            $assignmentMatchStmt = db()->prepare(
                'SELECT COUNT(*) FROM `' . table('dentist_service_assignments') . '` WHERE dentist_user_id=? AND service_id=? AND is_active=1'
            );
        }
    }

    $result = [];
    foreach ($dentists as $dentist) {
        $dentistId = (int)($dentist['id'] ?? 0);
        if ($dentistId < 1) continue;

        if (dentist_credential_schema_ready()) {
            $reason = null;
            if (!dentist_credentials_verified($dentistId, $reason)) continue;
        }
        if ($requiredPrivilege !== null) {
            $privilegeReason = null;
            if (!dentist_has_clinical_privilege($dentistId, $requiredPrivilege, $privilegeReason)) continue;
        }

        if ($profileStmt) {
            $profileStmt->execute([$dentistId]);
            $profile = $profileStmt->fetch();
            if ($profile) {
                if ((string)($profile['profile_status'] ?? 'draft') !== 'active') continue;
                if ($newPatient && !(bool)($profile['accepting_new_patients'] ?? false)) continue;
            }
        }

        if ($assignmentCountStmt && $assignmentMatchStmt) {
            $assignmentCountStmt->execute([$dentistId]);
            $configured = (int)$assignmentCountStmt->fetchColumn();
            if ($configured > 0) {
                $assignmentMatchStmt->execute([$dentistId,$serviceId]);
                if ((int)$assignmentMatchStmt->fetchColumn() < 1) continue;
            }
        }

        $result[] = $dentist;
    }
    return $result;
}

function normalize_chair_name(string $name): string
{
    return preg_replace('/\s+/u', ' ', trim($name)) ?: '';
}

function doctor_chair_assignment_schema_ready(): bool
{
    return schema_table_exists(table('doctor_chair_assignments'));
}

/**
 * Resolve the treatment chair that is physically bound to a dentist for one branch.
 * A valid binding is required before appointment slots are exposed or saved.
 */
function doctor_chair_binding(int $doctorId, int $branchId): array
{
    $result = [
        'doctor_id' => $doctorId,
        'branch_id' => $branchId,
        'chair_id' => 0,
        'chair_name' => null,
        'valid' => false,
        'issue' => 'No default or active-session operatory is assigned to this dentist for the selected branch.',
        'source' => 'none',
    ];
    if ($doctorId < 1 || $branchId < 1) return $result;

    // v0.5.9.8.47: Chairs are clinic resources, not permanent doctor-owned seats.
    // A temporary session assignment wins over the optional default provider/chair preference.
    if (schema_table_exists(table('chair_session_assignments')) && clinic_chair_schema_ready()) {
        $stmt = db()->prepare(
            'SELECT csa.chair_id,csa.chair_name,c.status chair_status,c.usage_mode,c.resource_kind '
            . 'FROM `' . table('chair_session_assignments') . '` csa '
            . 'LEFT JOIN `' . table('clinic_chairs') . '` c ON c.id=csa.chair_id AND c.branch_id=csa.branch_id '
            . 'JOIN `' . table('users') . '` u ON u.id=csa.dentist_user_id '
            . 'WHERE csa.dentist_user_id=? AND csa.branch_id=? AND csa.status="active" AND u.status="active" '
            . 'AND csa.starts_at<=NOW() AND csa.ends_at>=NOW() ORDER BY csa.starts_at DESC,csa.id DESC LIMIT 1'
        );
        $stmt->execute([$doctorId,$branchId]);
        if ($row = $stmt->fetch()) {
            $chair = normalize_chair_name((string)($row['chair_name'] ?? ''));
            $result['chair_id'] = (int)($row['chair_id'] ?? 0);
            $result['chair_name'] = $chair !== '' ? $chair : null;
            $result['source'] = 'session_assignment';
            if ((int)($row['chair_id'] ?? 0) < 1 || $chair === '') return $result;
            if (($row['chair_status'] ?? '') !== 'available') {
                $result['issue'] = 'The active-session chair is currently ' . str_replace('_',' ',(string)($row['chair_status'] ?: 'unavailable')) . '. Choose another chair before seating or booking.';
                return $result;
            }
            $mode = (string)($row['usage_mode'] ?? $row['resource_kind'] ?? 'operatory');
            if (in_array($mode, ['shared_inhouse','xray','consultation'], true)) {
                $result['issue'] = 'The active-session resource is configured as ' . ucwords(str_replace('_',' ',$mode)) . ', not as a dentist treatment operatory.';
                return $result;
            }
            $result['valid'] = true;
            $result['issue'] = '';
            return $result;
        }
    }

    if (doctor_chair_assignment_schema_ready()) {
        if (clinic_chair_schema_ready()) {
            $stmt = db()->prepare(
                'SELECT dca.chair_name,dca.chair_id,c.status chair_status,c.usage_mode,c.resource_kind '
                . 'FROM `' . table('doctor_chair_assignments') . '` dca '
                . 'JOIN `' . table('users') . '` u ON u.id=dca.dentist_user_id '
                . 'LEFT JOIN `' . table('clinic_chairs') . '` c ON c.id=dca.chair_id AND c.branch_id=dca.branch_id '
                . 'WHERE dca.dentist_user_id=? AND dca.branch_id=? AND dca.is_active=1 AND u.status="active" LIMIT 1'
            );
            $stmt->execute([$doctorId,$branchId]);
            $row = $stmt->fetch();
            if (!$row) return $result;
            $chair = normalize_chair_name((string)($row['chair_name'] ?? ''));
            $result['chair_id'] = (int)($row['chair_id'] ?? 0);
            $result['chair_name'] = $chair !== '' ? $chair : null;
            $result['source'] = 'default_provider';
            if ((int)($row['chair_id'] ?? 0) < 1) {
                $result['issue'] = 'This default chair preference has not been linked to a formal chair resource. Ask an Administrator to review Chairs & Resources.';
                return $result;
            }
            if (($row['chair_status'] ?? '') !== 'available') {
                $result['issue'] = 'The default chair is currently ' . str_replace('_',' ',(string)($row['chair_status'] ?: 'unavailable')) . '. Choose another chair/session before booking.';
                return $result;
            }
            $mode = (string)($row['usage_mode'] ?? $row['resource_kind'] ?? 'operatory');
            if (in_array($mode, ['shared_inhouse','xray','consultation'], true)) {
                $result['issue'] = 'The default resource is configured as ' . ucwords(str_replace('_',' ',$mode)) . ', not as a dentist treatment operatory.';
                return $result;
            }
            if ($chair === '') return $result;
            $result['valid'] = true;
            $result['issue'] = '';
            return $result;
        }

        $stmt = db()->prepare(
            'SELECT dca.chair_name FROM `' . table('doctor_chair_assignments') . '` dca '
            . 'JOIN `' . table('users') . '` u ON u.id=dca.dentist_user_id '
            . 'WHERE dca.dentist_user_id=? AND dca.branch_id=? AND dca.is_active=1 AND u.status="active" LIMIT 1'
        );
        $stmt->execute([$doctorId,$branchId]);
        $chair = normalize_chair_name((string) ($stmt->fetchColumn() ?: ''));
        if ($chair === '') return $result;
        $result['chair_name'] = $chair;
        $result['valid'] = true;
        $result['issue'] = '';
        $result['source'] = 'default_provider';
        return $result;
    }

    if (!doctor_workflow_schema_ready()) return $result;
    $stmt = db()->prepare(
        'SELECT TRIM(COALESCE(dp.default_chair_name,"")) chair_name '
        . 'FROM `' . table('users') . '` u LEFT JOIN `' . table('dentist_profiles') . '` dp ON dp.user_id=u.id '
        . 'WHERE u.id=? AND (COALESCE(dp.primary_branch_id,u.branch_id)=? OR u.branch_id=? OR u.branch_id IS NULL) LIMIT 1'
    );
    $stmt->execute([$doctorId,$branchId,$branchId]);
    $chair = normalize_chair_name((string) ($stmt->fetchColumn() ?: ''));
    if ($chair === '') return $result;

    $result['chair_name'] = $chair;
    $result['valid'] = true;
    $result['issue'] = '';
    $result['source'] = 'legacy_profile_default';
    return $result;
}

function doctor_primary_chair_name(int $doctorId, int $branchId): ?string
{
    $binding = doctor_chair_binding($doctorId,$branchId);
    return $binding['valid'] ? (string) $binding['chair_name'] : null;
}

function appointment_service_margins(string $serviceName, int $branchId): array
{
    static $cache = [];
    $key = $branchId . '|' . mb_strtolower(trim($serviceName));
    if (isset($cache[$key])) return $cache[$key];
    $result = ['preparation_minutes'=>0,'cleanup_minutes'=>0];
    if ($serviceName === '') return $cache[$key] = $result;
    try {
        $stmt = db()->prepare(
            'SELECT preparation_minutes,cleanup_minutes FROM `' . table('service_catalog') . '` '
            . 'WHERE name=? AND is_active=1 AND (branch_id=? OR branch_id IS NULL) '
            . 'ORDER BY (branch_id=?) DESC,id LIMIT 1'
        );
        $stmt->execute([$serviceName,$branchId,$branchId]);
        if ($row = $stmt->fetch()) {
            $result['preparation_minutes'] = max(0,(int) ($row['preparation_minutes'] ?? 0));
            $result['cleanup_minutes'] = max(0,(int) ($row['cleanup_minutes'] ?? 0));
        }
    } catch (Throwable) {
    }
    return $cache[$key] = $result;
}

/**
 * Hard resource conflicts can never be bypassed: one patient, dentist, or chair
 * cannot occupy overlapping appointments.
 */
function appointment_resource_conflicts(int $branchId, int $patientId, int $doctorId, string $chairName, string $startAt, string $endAt, int $excludeAppointmentId = 0, int $prep = 0, int $cleanup = 0, int $inhouseProviderId = 0, int $chairId = 0): array
{
    $chairName = normalize_chair_name($chairName);
    $reserveStart = date('Y-m-d H:i:s',strtotime($startAt . ' -' . max(0,$prep) . ' minutes'));
    $reserveEnd = date('Y-m-d H:i:s',strtotime($endAt . ' +' . max(0,$cleanup) . ' minutes'));
    $appointmentTable = table('appointments');
    $serviceTable = table('appointment_services');
    $segmentReady = schema_table_exists($serviceTable);
    $hasChairId = schema_column_exists($appointmentTable,'chair_id');
    $rows = [];
    $types = [];

    // The parent appointment remains authoritative for patient occupancy across
    // the entire coordinated visit. Parent resource fields are used only for
    // legacy appointments that do not have segment rows.
    $resourceConditions = [];
    $resourceArgs = [];
    if ($doctorId > 0) { $resourceConditions[] = 'a.dentist_user_id=?'; $resourceArgs[] = $doctorId; }
    if ($inhouseProviderId > 0) { $resourceConditions[] = 'a.inhouse_provider_user_id=?'; $resourceArgs[] = $inhouseProviderId; }
    if ($hasChairId && $chairId > 0) { $resourceConditions[] = 'a.chair_id=?'; $resourceArgs[] = $chairId; }
    if ($chairName !== '') { $resourceConditions[] = 'LOWER(TRIM(COALESCE(a.chair_name,"")))=LOWER(?)'; $resourceArgs[] = $chairName; }

    $whereParts = ['a.patient_id=?'];
    $args = [$branchId,$reserveEnd,$reserveStart,$patientId];
    if ($resourceConditions) {
        $legacyGuard = $segmentReady
            ? 'NOT EXISTS(SELECT 1 FROM `' . $serviceTable . '` aps0 WHERE aps0.appointment_id=a.id) AND '
            : '';
        $whereParts[] = '(' . $legacyGuard . '(' . implode(' OR ',$resourceConditions) . '))';
        array_push($args,...$resourceArgs);
    }
    $select = $hasChairId
        ? 'a.id,a.patient_id,a.dentist_user_id,a.inhouse_provider_user_id,a.dentist_name,a.chair_id,a.chair_name,a.service_name,a.start_at,a.end_at'
        : 'a.id,a.patient_id,a.dentist_user_id,a.inhouse_provider_user_id,a.dentist_name,NULL chair_id,a.chair_name,a.service_name,a.start_at,a.end_at';
    $select .= $segmentReady ? ',EXISTS(SELECT 1 FROM `' . $serviceTable . '` apsx WHERE apsx.appointment_id=a.id) has_segments' : ',0 has_segments';
    $sql = 'SELECT ' . $select . ' FROM `' . $appointmentTable . '` a '
        . 'WHERE a.branch_id=? AND a.status NOT IN ("cancelled","no_show") '
        . 'AND a.start_at<? AND a.end_at>? AND (' . implode(' OR ',$whereParts) . ')';
    if ($excludeAppointmentId > 0) { $sql .= ' AND a.id<>?'; $args[] = $excludeAppointmentId; }
    $sql .= ' ORDER BY a.start_at,a.id';
    $stmt = db()->prepare($sql);
    $stmt->execute($args);
    foreach ($stmt->fetchAll() as $row) {
        [$existingStart,$existingEnd] = appointment_reserved_interval((string)$row['start_at'],(string)$row['end_at'],(string)($row['service_name']??''),$branchId);
        if (!($reserveStart < $existingEnd && $reserveEnd > $existingStart)) continue;
        $row['reserved_start_at'] = $existingStart;
        $row['reserved_end_at'] = $existingEnd;
        $rows[] = $row;
        if ((int)$row['patient_id'] === $patientId) $types['patient'] = true;
        if (!(bool)($row['has_segments'] ?? false)) {
            if ($doctorId > 0 && (int)($row['dentist_user_id'] ?? 0) === $doctorId) $types['dentist'] = true;
            if ($inhouseProviderId > 0 && (int)($row['inhouse_provider_user_id'] ?? 0) === $inhouseProviderId) $types['inhouse_provider'] = true;
            if (($chairId > 0 && (int)($row['chair_id'] ?? 0) === $chairId)
                || ($chairName !== '' && strcasecmp(normalize_chair_name((string)($row['chair_name'] ?? '')),$chairName) === 0)) {
                $types['chair'] = true;
            }
        }
    }

    // For coordinated visits, provider and chair occupancy comes from the exact
    // child service interval instead of the parent appointment's broad span.
    if ($segmentReady && ($doctorId > 0 || $inhouseProviderId > 0 || $chairId > 0 || $chairName !== '')) {
        $segmentConditions = [];
        $segmentArgs = [];
        if ($doctorId > 0) { $segmentConditions[] = 'aps.dentist_user_id=?'; $segmentArgs[] = $doctorId; }
        if ($inhouseProviderId > 0) { $segmentConditions[] = 'aps.inhouse_provider_user_id=?'; $segmentArgs[] = $inhouseProviderId; }
        if ($chairId > 0) { $segmentConditions[] = 'aps.chair_id=?'; $segmentArgs[] = $chairId; }
        if ($chairName !== '') { $segmentConditions[] = 'LOWER(TRIM(COALESCE(aps.chair_name,"")))=LOWER(?)'; $segmentArgs[] = $chairName; }
        $segmentSql = 'SELECT a.id,a.patient_id,aps.dentist_user_id,aps.inhouse_provider_user_id,aps.dentist_name,aps.chair_id,aps.chair_name,aps.service_name,aps.start_at,aps.end_at,aps.preparation_minutes,aps.cleanup_minutes,1 has_segments '
            . 'FROM `' . $serviceTable . '` aps JOIN `' . $appointmentTable . '` a ON a.id=aps.appointment_id '
            . 'WHERE a.branch_id=? AND a.status NOT IN ("cancelled","no_show") AND aps.status IN ("planned","completed") '
            . 'AND aps.start_at<? AND aps.end_at>? AND (' . implode(' OR ',$segmentConditions) . ')';
        $segmentQueryArgs = [$branchId,$reserveEnd,$reserveStart,...$segmentArgs];
        if ($excludeAppointmentId > 0) { $segmentSql .= ' AND a.id<>?'; $segmentQueryArgs[] = $excludeAppointmentId; }
        $segmentSql .= ' ORDER BY aps.start_at,aps.id';
        $segmentStmt = db()->prepare($segmentSql);
        $segmentStmt->execute($segmentQueryArgs);
        foreach ($segmentStmt->fetchAll() as $row) {
            $existingStart = date('Y-m-d H:i:s',strtotime((string)$row['start_at'] . ' -' . max(0,(int)$row['preparation_minutes']) . ' minutes'));
            $existingEnd = date('Y-m-d H:i:s',strtotime((string)$row['end_at'] . ' +' . max(0,(int)$row['cleanup_minutes']) . ' minutes'));
            if (!($reserveStart < $existingEnd && $reserveEnd > $existingStart)) continue;
            $row['reserved_start_at'] = $existingStart;
            $row['reserved_end_at'] = $existingEnd;
            $rows[] = $row;
            if ($doctorId > 0 && (int)($row['dentist_user_id'] ?? 0) === $doctorId) $types['dentist'] = true;
            if ($inhouseProviderId > 0 && (int)($row['inhouse_provider_user_id'] ?? 0) === $inhouseProviderId) $types['inhouse_provider'] = true;
            if (($chairId > 0 && (int)($row['chair_id'] ?? 0) === $chairId)
                || ($chairName !== '' && strcasecmp(normalize_chair_name((string)($row['chair_name'] ?? '')),$chairName) === 0)) {
                $types['chair'] = true;
            }
        }
    }

    return ['has_conflict'=>(bool)$rows,'types'=>array_keys($types),'appointments'=>$rows,'reserved_start_at'=>$reserveStart,'reserved_end_at'=>$reserveEnd];
}

function appointment_conflict_message(array $conflicts): string
{
    $types = (array) ($conflicts['types'] ?? []);
    $labels = [];
    if (in_array('patient',$types,true)) $labels[] = 'patient';
    if (in_array('dentist',$types,true)) $labels[] = 'dentist';
    if (in_array('inhouse_provider',$types,true)) $labels[] = 'in-house provider';
    if (in_array('chair',$types,true)) $labels[] = 'chair';
    return 'This schedule overlaps an existing ' . ($labels ? implode(', ', $labels) : 'appointment') . ' booking. Choose another available date or time. Resource conflicts cannot be bypassed.';
}

function services_catalog(?int $branchId = null, bool $publicOnly = false): array
{
    try {
        $privilegeColumn = schema_column_exists(table('service_catalog'),'required_privilege_code') ? ',required_privilege_code' : ',NULL required_privilege_code';
        $bookingScopeReady = schema_column_exists(table('service_catalog'),'booking_scope');
        $bookingScopeColumn = $bookingScopeReady ? ',booking_scope' : ',CASE WHEN is_public=1 THEN "public" ELSE "reception" END booking_scope';
        $providerColumns = schema_column_exists(table('service_catalog'),'provider_type')
            ? ',provider_type,provider_role_code,slot_capacity,requires_chair,is_public,standard_fee,description' . $privilegeColumn . $bookingScopeColumn
            : ',"dentist" provider_type,NULL provider_role_code,1 slot_capacity,1 requires_chair,1 is_public,0 standard_fee,NULL description,NULL required_privilege_code,"public" booking_scope';
        $sql = 'SELECT id,name,duration_minutes,preparation_minutes,cleanup_minutes' . $providerColumns . ' FROM `' . table('service_catalog') . '` WHERE is_active=1';
        $params = [];
        if ($publicOnly) {
            $sql .= ' AND is_public=1';
            if ($bookingScopeReady) $sql .= ' AND booking_scope="public"';
        }
        if ($branchId !== null) {
            $sql .= ' AND (branch_id=? OR branch_id IS NULL)';
            $params[] = $branchId;
        }
        $sql .= ' ORDER BY provider_type="inhouse" DESC,name';
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();
        if ($rows || !$publicOnly) return $rows;
    } catch (Throwable) {
    }
    if (!$publicOnly) return [];
    $clinic = public_clinic_profile();
    $rows = [];
    foreach (public_lines((string) $clinic['services']) as $index => $name) {
        $rows[] = ['id' => 0 - $index - 1, 'name' => $name, 'duration_minutes' => 30, 'preparation_minutes' => 0, 'cleanup_minutes' => 0, 'provider_type'=>'dentist','provider_role_code'=>null,'slot_capacity'=>1,'requires_chair'=>1,'is_public'=>1,'standard_fee'=>0,'description'=>null,'required_privilege_code'=>null,'booking_scope'=>'public'];
    }
    return $rows;
}

function public_services_catalog(?int $branchId = null): array
{
    return services_catalog($branchId, true);
}

function clinic_services_catalog(?int $branchId = null): array
{
    return services_catalog($branchId, false);
}

function appointment_booking_services_catalog(?int $branchId = null): array
{
    $rows = services_catalog($branchId, false);
    return array_values(array_filter($rows, static function (array $row): bool {
        $scope = (string)($row['booking_scope'] ?? ((int)($row['is_public'] ?? 0) === 1 ? 'public' : 'reception'));
        return in_array($scope, ['public','reception'], true);
    }));
}

function service_definition(int $serviceId, ?int $branchId = null): ?array
{
    if ($serviceId <= 0) {
        return null;
    }
    $privilegeColumn = schema_column_exists(table('service_catalog'),'required_privilege_code') ? ',required_privilege_code' : ',NULL required_privilege_code';
    $bookingScopeColumn = schema_column_exists(table('service_catalog'),'booking_scope') ? ',booking_scope' : ',CASE WHEN is_public=1 THEN "public" ELSE "reception" END booking_scope';
    $providerColumns = schema_column_exists(table('service_catalog'),'provider_type')
        ? ',provider_type,provider_role_code,slot_capacity,requires_chair,is_public,standard_fee,description' . $privilegeColumn . $bookingScopeColumn
        : ',"dentist" provider_type,NULL provider_role_code,1 slot_capacity,1 requires_chair,1 is_public,0 standard_fee,NULL description,NULL required_privilege_code,"public" booking_scope';
    $sql = 'SELECT id,name,duration_minutes,preparation_minutes,cleanup_minutes' . $providerColumns . ' FROM `' . table('service_catalog') . '` WHERE id=? AND is_active=1';
    $params = [$serviceId];
    if ($branchId !== null) {
        $sql .= ' AND (branch_id=? OR branch_id IS NULL)';
        $params[] = $branchId;
    }
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetch() ?: null;
}

function public_service_definition(int $serviceId, ?int $branchId = null): ?array
{
    $service = service_definition($serviceId, $branchId);
    if (!$service || (int)($service['is_public'] ?? 0) !== 1 || (string)($service['booking_scope'] ?? 'public') !== 'public') return null;
    return $service;
}

function service_booking_scope(array $service): string
{
    $scope = (string)($service['booking_scope'] ?? ((int)($service['is_public'] ?? 0) === 1 ? 'public' : 'reception'));
    return in_array($scope, ['public','reception','dentist_order','clinical_intake','inhouse_only'], true) ? $scope : 'reception';
}



function service_provider_type(array $service): string
{
    return (($service['provider_type'] ?? 'dentist') === 'inhouse') ? 'inhouse' : 'dentist';
}

function inhouse_resource_schema_ready(): bool
{
    try {
        return schema_table_exists(table('inhouse_service_staff'))
            && schema_column_exists(table('appointments'),'chair_id')
            && schema_column_exists(table('queue_entries'),'chair_id');
    } catch (Throwable) {
        return false;
    }
}

function inhouse_service_staff_candidates(int $branchId, array $service): array
{
    $serviceId=(int)($service['id']??0);
    if($branchId<1||$serviceId<1)return [];
    if(schema_table_exists(table('inhouse_service_staff'))){
        $stmt=db()->prepare(
            'SELECT u.id,u.name,u.email,r.code role_code,r.name role_name,"assigned" assignment_source '
            . 'FROM `' . table('inhouse_service_staff') . '` iss '
            . 'JOIN `' . table('users') . '` u ON u.id=iss.user_id AND u.status="active" '
            . 'JOIN `' . table('roles') . '` r ON r.id=u.role_id '
            . 'WHERE iss.branch_id=? AND iss.service_id=? AND iss.is_active=1 '
            . 'AND (u.branch_id=? OR u.branch_id IS NULL) ORDER BY u.name'
        );
        $stmt->execute([$branchId,$serviceId,$branchId]);
        $rows=$stmt->fetchAll();
        if($rows)return $rows;
    }
    $roleCode=trim((string)($service['provider_role_code']??''));
    $codes=$roleCode!==''&&$roleCode!=='inhouse'
        ? [$roleCode]
        : ['dental_assistant','staff','clinic_admin'];
    $marks=implode(',',array_fill(0,count($codes),'?'));
    $stmt=db()->prepare(
        'SELECT u.id,u.name,u.email,r.code role_code,r.name role_name,"role_fallback" assignment_source '
        . 'FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id '
        . 'WHERE u.status="active" AND r.code IN (' . $marks . ') AND (u.branch_id=? OR u.branch_id IS NULL) '
        . 'ORDER BY FIELD(r.code,"dental_assistant","staff","clinic_admin"),u.name'
    );
    $stmt->execute(array_merge($codes,[$branchId]));
    return $stmt->fetchAll();
}

function appointment_reserved_interval(string $startAt,string $endAt,string $serviceName,int $branchId): array
{
    $margins=appointment_service_margins($serviceName,$branchId);
    return [
        date('Y-m-d H:i:s',strtotime($startAt.' -'.(int)$margins['preparation_minutes'].' minutes')),
        date('Y-m-d H:i:s',strtotime($endAt.' +'.(int)$margins['cleanup_minutes'].' minutes')),
    ];
}

function inhouse_provider_available(int $userId,int $branchId,string $reserveStart,string $reserveEnd,int $excludeAppointmentId=0): bool
{
    if($userId<1)return false;
    $appointmentTable=table('appointments');
    $serviceTable=table('appointment_services');
    $segmentReady=schema_table_exists($serviceTable);

    if($segmentReady){
        $sql='SELECT aps.start_at,aps.end_at,aps.preparation_minutes,aps.cleanup_minutes FROM `' . $serviceTable . '` aps '
            . 'JOIN `' . $appointmentTable . '` a ON a.id=aps.appointment_id '
            . 'WHERE a.branch_id=? AND aps.inhouse_provider_user_id=? AND a.status NOT IN ("cancelled","no_show") AND aps.status IN ("planned","completed") '
            . 'AND aps.start_at<DATE_ADD(?,INTERVAL 1 DAY) AND aps.end_at>DATE_SUB(?,INTERVAL 1 DAY)';
        $args=[$branchId,$userId,$reserveEnd,$reserveStart];
        if($excludeAppointmentId>0){$sql.=' AND a.id<>?';$args[]=$excludeAppointmentId;}
        $stmt=db()->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $row){
            $existingStart=date('Y-m-d H:i:s',strtotime((string)$row['start_at'].' -'.max(0,(int)$row['preparation_minutes']).' minutes'));
            $existingEnd=date('Y-m-d H:i:s',strtotime((string)$row['end_at'].' +'.max(0,(int)$row['cleanup_minutes']).' minutes'));
            if($reserveStart<$existingEnd&&$reserveEnd>$existingStart)return false;
        }
    }

    $sql='SELECT a.id,a.service_name,a.start_at,a.end_at FROM `' . $appointmentTable . '` a WHERE a.branch_id=? AND a.inhouse_provider_user_id=? AND a.status NOT IN ("cancelled","no_show") AND a.start_at<DATE_ADD(?,INTERVAL 1 DAY) AND a.end_at>DATE_SUB(?,INTERVAL 1 DAY)';
    $args=[$branchId,$userId,$reserveEnd,$reserveStart];
    if($segmentReady)$sql.=' AND NOT EXISTS(SELECT 1 FROM `' . $serviceTable . '` aps0 WHERE aps0.appointment_id=a.id)';
    if($excludeAppointmentId>0){$sql.=' AND a.id<>?';$args[]=$excludeAppointmentId;}
    $stmt=db()->prepare($sql);$stmt->execute($args);
    foreach($stmt->fetchAll() as $row){
        [$existingStart,$existingEnd]=appointment_reserved_interval((string)$row['start_at'],(string)$row['end_at'],(string)($row['service_name']??''),$branchId);
        if($reserveStart<$existingEnd&&$reserveEnd>$existingStart)return false;
    }
    return true;
}

function shared_inhouse_chair_candidates(int $branchId): array
{
    if(!clinic_chair_schema_ready()||$branchId<1)return [];
    $stmt=db()->prepare('SELECT id,chair_code,chair_name,room_name FROM `' . table('clinic_chairs') . '` WHERE branch_id=? AND usage_mode="shared_inhouse" AND status="available" ORDER BY chair_name');
    $stmt->execute([$branchId]);
    return $stmt->fetchAll();
}

function shared_inhouse_chair_available(int $chairId,string $chairName,int $branchId,string $reserveStart,string $reserveEnd,int $excludeAppointmentId=0): bool
{
    if($chairId<1&&trim($chairName)==='')return false;
    $chairName=normalize_chair_name($chairName);
    $appointmentTable=table('appointments');
    $serviceTable=table('appointment_services');
    $segmentReady=schema_table_exists($serviceTable);
    $hasChairId=schema_column_exists($appointmentTable,'chair_id');

    if($segmentReady){
        $conditions=[];$args=[$branchId,$reserveEnd,$reserveStart];
        if($chairId>0){$conditions[]='aps.chair_id=?';$args[]=$chairId;}
        if($chairName!==''){$conditions[]='LOWER(TRIM(COALESCE(aps.chair_name,"")))=LOWER(?)';$args[]=$chairName;}
        $sql='SELECT aps.start_at,aps.end_at,aps.preparation_minutes,aps.cleanup_minutes FROM `' . $serviceTable . '` aps '
            . 'JOIN `' . $appointmentTable . '` a ON a.id=aps.appointment_id '
            . 'WHERE a.branch_id=? AND a.status NOT IN ("cancelled","no_show") AND aps.status IN ("planned","completed") '
            . 'AND aps.start_at<DATE_ADD(?,INTERVAL 1 DAY) AND aps.end_at>DATE_SUB(?,INTERVAL 1 DAY) AND (' . implode(' OR ',$conditions) . ')';
        if($excludeAppointmentId>0){$sql.=' AND a.id<>?';$args[]=$excludeAppointmentId;}
        $stmt=db()->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $row){
            $existingStart=date('Y-m-d H:i:s',strtotime((string)$row['start_at'].' -'.max(0,(int)$row['preparation_minutes']).' minutes'));
            $existingEnd=date('Y-m-d H:i:s',strtotime((string)$row['end_at'].' +'.max(0,(int)$row['cleanup_minutes']).' minutes'));
            if($reserveStart<$existingEnd&&$reserveEnd>$existingStart)return false;
        }
    }

    $conditions=[];$args=[$branchId,$reserveEnd,$reserveStart];
    if($hasChairId&&$chairId>0){$conditions[]='a.chair_id=?';$args[]=$chairId;}
    if($chairName!==''){$conditions[]='LOWER(TRIM(COALESCE(a.chair_name,"")))=LOWER(?)';$args[]=$chairName;}
    $sql='SELECT a.id,a.service_name,a.start_at,a.end_at FROM `' . $appointmentTable . '` a WHERE a.branch_id=? AND a.status NOT IN ("cancelled","no_show") '
        . 'AND a.start_at<DATE_ADD(?,INTERVAL 1 DAY) AND a.end_at>DATE_SUB(?,INTERVAL 1 DAY) AND (' . implode(' OR ',$conditions) . ')';
    if($segmentReady)$sql.=' AND NOT EXISTS(SELECT 1 FROM `' . $serviceTable . '` aps0 WHERE aps0.appointment_id=a.id)';
    if($excludeAppointmentId>0){$sql.=' AND a.id<>?';$args[]=$excludeAppointmentId;}
    $stmt=db()->prepare($sql);$stmt->execute($args);
    foreach($stmt->fetchAll() as $row){
        [$existingStart,$existingEnd]=appointment_reserved_interval((string)$row['start_at'],(string)$row['end_at'],(string)($row['service_name']??''),$branchId);
        if($reserveStart<$existingEnd&&$reserveEnd>$existingStart)return false;
    }
    return true;
}

function inhouse_slot_assignment(int $branchId,string $date,string $time,array $service,int $excludeAppointmentId=0,int $requestedProviderId=0,int $requestedChairId=0): array
{
    $startAt=$date.' '.substr($time,0,5).':00';
    $endAt=date('Y-m-d H:i:s',strtotime($startAt.' +'.max(5,(int)($service['duration_minutes']??30)).' minutes'));
    $reserveStart=date('Y-m-d H:i:s',strtotime($startAt.' -'.max(0,(int)($service['preparation_minutes']??0)).' minutes'));
    $reserveEnd=date('Y-m-d H:i:s',strtotime($endAt.' +'.max(0,(int)($service['cleanup_minutes']??0)).' minutes'));
    $providers=inhouse_service_staff_candidates($branchId,$service);
    if($requestedProviderId>0)usort($providers,static fn(array $a,array $b):int=>((int)$b['id']===$requestedProviderId)<=>((int)$a['id']===$requestedProviderId));
    $provider=null;
    foreach($providers as $candidate){if(inhouse_provider_available((int)$candidate['id'],$branchId,$reserveStart,$reserveEnd,$excludeAppointmentId)){$provider=$candidate;break;}}
    if(!$provider)return ['valid'=>false,'issue'=>'No assigned in-house staff member is available for this service and time. Configure the In-house Care Team or choose another schedule.'];
    $chair=null;
    if(!empty($service['requires_chair'])){
        $chairs=shared_inhouse_chair_candidates($branchId);
        if($requestedChairId>0)usort($chairs,static fn(array $a,array $b):int=>((int)$b['id']===$requestedChairId)<=>((int)$a['id']===$requestedChairId));
        foreach($chairs as $candidate){if(shared_inhouse_chair_available((int)$candidate['id'],(string)$candidate['chair_name'],$branchId,$reserveStart,$reserveEnd,$excludeAppointmentId)){$chair=$candidate;break;}}
        if(!$chair)return ['valid'=>false,'issue'=>'No available shared in-house chair can cover this service and time. Add or free a Shared in-house chair in Chairs & Resources.'];
    }
    return [
        'valid'=>true,'issue'=>'','provider_id'=>(int)$provider['id'],'provider_name'=>(string)$provider['name'],
        'provider_role'=>(string)($provider['role_name']??$provider['role_code']??'In-house provider'),
        'assignment_source'=>(string)($provider['assignment_source']??'assigned'),
        'chair_id'=>(int)($chair['id']??0),'chair_name'=>(string)($chair['chair_name']??''),
        'start_at'=>$startAt,'end_at'=>$endAt,'reserved_start_at'=>$reserveStart,'reserved_end_at'=>$reserveEnd,
    ];
}

function inhouse_available_slots(int $branchId, string $date, array $service, int $excludeAppointmentId = 0): array
{
    $hours = branch_hours_for_date($branchId,$date);
    if (!$hours || strtotime($date) < strtotime('today')) return [];
    $duration=max(5,(int)($service['duration_minutes']??30));
    $prep=max(0,(int)($service['preparation_minutes']??0));
    $cleanup=max(0,(int)($service['cleanup_minutes']??0));
    $capacity=max(1,(int)($service['slot_capacity']??1));
    $interval=max(5,(int)$hours['slot_interval_minutes']);
    $first=(int)(ceil((time_minutes((string)$hours['open_time'])+$prep)/$interval)*$interval);
    $last=time_minutes((string)$hours['close_time']);
    $slots=[];
    $appointmentTable=table('appointments');
    $serviceTable=table('appointment_services');
    $segmentReady=schema_table_exists($serviceTable);
    $existing=[];

    if($segmentReady){
        $sql='SELECT aps.start_at,aps.end_at,aps.preparation_minutes,aps.cleanup_minutes FROM `' . $serviceTable . '` aps '
            . 'JOIN `' . $appointmentTable . '` a ON a.id=aps.appointment_id '
            . 'WHERE a.branch_id=? AND a.status NOT IN ("cancelled","no_show") AND aps.status IN ("planned","completed") '
            . 'AND aps.service_id=? AND aps.start_at<DATE_ADD(?,INTERVAL 1 DAY) AND aps.end_at>DATE_SUB(?,INTERVAL 1 DAY)';
        $args=[$branchId,(int)($service['id']??0),$date.' 23:59:59',$date.' 00:00:00'];
        if($excludeAppointmentId>0){$sql.=' AND a.id<>?';$args[]=$excludeAppointmentId;}
        $stmt=db()->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $row){
            $existing[]=[
                date('Y-m-d H:i:s',strtotime((string)$row['start_at'].' -'.max(0,(int)$row['preparation_minutes']).' minutes')),
                date('Y-m-d H:i:s',strtotime((string)$row['end_at'].' +'.max(0,(int)$row['cleanup_minutes']).' minutes')),
            ];
        }
    }

    $sql='SELECT a.id,a.start_at,a.end_at,a.service_name FROM `' . $appointmentTable . '` a WHERE a.branch_id=? AND DATE(a.start_at)=? AND a.status NOT IN ("cancelled","no_show") AND a.provider_type="inhouse" AND a.service_id=?';
    $args=[$branchId,$date,(int)($service['id']??0)];
    if($segmentReady)$sql.=' AND NOT EXISTS(SELECT 1 FROM `' . $serviceTable . '` aps0 WHERE aps0.appointment_id=a.id)';
    if($excludeAppointmentId>0){$sql.=' AND a.id<>?';$args[]=$excludeAppointmentId;}
    $stmt=db()->prepare($sql);$stmt->execute($args);
    foreach($stmt->fetchAll() as $row)$existing[]=appointment_reserved_interval((string)$row['start_at'],(string)$row['end_at'],(string)($row['service_name']??''),$branchId);

    for($start=$first;$start+$duration+$cleanup<=$last;$start+=$interval){
        $time=minutes_time($start);
        $reserveStart=$date.' '.minutes_time(max(0,$start-$prep)).':00';
        $reserveEnd=$date.' '.minutes_time(min(1439,$start+$duration+$cleanup)).':00';
        $overlap=0;
        foreach($existing as [$bookStart,$bookEnd])if($reserveStart<$bookEnd&&$reserveEnd>$bookStart)$overlap++;
        if($overlap>=$capacity)continue;
        $assignment=inhouse_slot_assignment($branchId,$date,$time,$service,$excludeAppointmentId);
        if(!empty($assignment['valid']))$slots[]=$time;
    }
    return $slots;
}

function service_available_slots(int $branchId, array $service, string $date, int $doctorId = 0, int $excludeAppointmentId = 0): array
{
    if(service_provider_type($service)==='inhouse') return inhouse_available_slots($branchId,$date,$service,$excludeAppointmentId);
    if($doctorId<1)return [];
    return doctor_available_slots($doctorId,$branchId,$date,(int)$service['duration_minutes'],(int)$service['preparation_minutes'],(int)$service['cleanup_minutes'],$excludeAppointmentId);
}

function validate_service_slot(int $branchId, array $service, string $date, string $time, int $doctorId = 0, int $excludeAppointmentId = 0): bool
{
    return in_array(substr($time,0,5),service_available_slots($branchId,$service,$date,$doctorId,$excludeAppointmentId),true);
}

function time_minutes(string $time): int
{
    [$h, $m] = array_map('intval', explode(':', substr($time, 0, 5)));
    return $h * 60 + $m;
}

function minutes_time(int $minutes): string
{
    $minutes = max(0, min(1439, $minutes));
    return sprintf('%02d:%02d', intdiv($minutes, 60), $minutes % 60);
}

function branch_hours_for_date(int $branchId, string $date): ?array
{
    $exception = db()->prepare('SELECT is_open,open_time,close_time,reason FROM `' . table('branch_operating_exceptions') . '` WHERE branch_id=? AND exception_date=? LIMIT 1');
    $exception->execute([$branchId, $date]);
    $row = $exception->fetch();
    if ($row) {
        if (!(bool) $row['is_open']) {
            return null;
        }
        return ['open_time' => substr((string) $row['open_time'], 0, 5), 'close_time' => substr((string) $row['close_time'], 0, 5), 'slot_interval_minutes' => 30, 'source' => 'exception'];
    }
    $day = (int) (new DateTimeImmutable($date))->format('w');
    $stmt = db()->prepare('SELECT is_open,open_time,close_time,slot_interval_minutes FROM `' . table('branch_operating_hours') . '` WHERE branch_id=? AND day_of_week=? LIMIT 1');
    $stmt->execute([$branchId, $day]);
    $row = $stmt->fetch();
    if (!$row || !(bool) $row['is_open'] || !$row['open_time'] || !$row['close_time']) {
        return null;
    }
    return ['open_time' => substr((string) $row['open_time'], 0, 5), 'close_time' => substr((string) $row['close_time'], 0, 5), 'slot_interval_minutes' => max(5, (int) $row['slot_interval_minutes']), 'source' => 'weekly'];
}

function doctor_intervals_for_date(int $doctorId, int $branchId, string $date): array
{
    $hours = branch_hours_for_date($branchId, $date);
    if (!$hours) {
        return [];
    }
    $day = (int) (new DateTimeImmutable($date))->format('w');
    $stmt = db()->prepare('SELECT start_time,end_time FROM `' . table('doctor_availability_rules') . '` WHERE doctor_user_id=? AND branch_id=? AND day_of_week=? AND status IN ("approved","active") AND effective_from<=? AND (effective_until IS NULL OR effective_until>=?) ORDER BY start_time');
    $stmt->execute([$doctorId, $branchId, $day, $date, $date]);
    $intervals = [];
    foreach ($stmt->fetchAll() as $row) {
        $start = max(time_minutes($hours['open_time']), time_minutes((string) $row['start_time']));
        $end = min(time_minutes($hours['close_time']), time_minutes((string) $row['end_time']));
        if ($end > $start) {
            $intervals[] = [$start, $end];
        }
    }

    $ex = db()->prepare('SELECT exception_type,start_time,end_time,all_day FROM `' . table('doctor_availability_exceptions') . '` WHERE doctor_user_id=? AND branch_id=? AND exception_date=? AND status IN ("approved","active") ORDER BY FIELD(exception_type,"special_availability","break","training","unavailable","leave","closure"),id');
    $ex->execute([$doctorId, $branchId, $date]);
    foreach ($ex->fetchAll() as $row) {
        if ($row['exception_type'] === 'special_availability') {
            $start = $row['all_day'] ? time_minutes($hours['open_time']) : time_minutes((string) $row['start_time']);
            $end = $row['all_day'] ? time_minutes($hours['close_time']) : time_minutes((string) $row['end_time']);
            $intervals = [[$start, $end]];
            continue;
        }
        if ((bool) $row['all_day']) {
            return [];
        }
        $cutStart = time_minutes((string) $row['start_time']);
        $cutEnd = time_minutes((string) $row['end_time']);
        $next = [];
        foreach ($intervals as [$start, $end]) {
            if ($cutEnd <= $start || $cutStart >= $end) {
                $next[] = [$start, $end];
            } else {
                if ($cutStart > $start) $next[] = [$start, $cutStart];
                if ($cutEnd < $end) $next[] = [$cutEnd, $end];
            }
        }
        $intervals = $next;
    }
    return $intervals;
}

function doctor_available_slots(int $doctorId, int $branchId, string $date, int $duration, int $prep = 0, int $cleanup = 0, int $excludeAppointmentId = 0): array
{
    $hours = branch_hours_for_date($branchId, $date);
    if (!$hours || $doctorId < 1 || strtotime($date) < strtotime('today')) return [];

    $duration = max(5, $duration);
    $prep = max(0, $prep);
    $cleanup = max(0, $cleanup);
    $interval = max(5, (int) $hours['slot_interval_minutes']);
    $slots = [];

    foreach (doctor_intervals_for_date($doctorId, $branchId, $date) as [$availableStart, $availableEnd]) {
        $first = (int) (ceil(($availableStart + $prep) / $interval) * $interval);
        for ($start = $first; $start + $duration + $cleanup <= $availableEnd; $start += $interval) {
            $time = minutes_time($start);
            $startAt = $date . ' ' . $time . ':00';
            $endAt = date('Y-m-d H:i:s', strtotime($startAt . ' +' . $duration . ' minutes'));

            // Resolve the operatory for the actual candidate window. This is
            // important for future chair sessions: a dentist may not have a
            // chair active right now, but may have a valid session on the
            // searched date/time.
            $binding = function_exists('chair_resource_resolve_for_doctor')
                ? chair_resource_resolve_for_doctor($doctorId, $branchId, $startAt, $endAt)
                : doctor_chair_binding($doctorId, $branchId);
            if (empty($binding['valid'])) continue;

            $chair = normalize_chair_name((string)($binding['chair_name'] ?? ''));
            $chairId = (int)($binding['chair_id'] ?? 0);
            if ($chair === '') continue;

            $conflicts = appointment_resource_conflicts(
                $branchId,
                0,
                $doctorId,
                $chair,
                $startAt,
                $endAt,
                $excludeAppointmentId,
                $prep,
                $cleanup,
                0,
                $chairId
            );
            if (!empty($conflicts['has_conflict'])) continue;

            $slots[] = $time;
        }
    }
    return array_values(array_unique($slots));
}

function validate_doctor_slot(int $doctorId, int $branchId, string $date, string $time, int $duration, int $prep = 0, int $cleanup = 0, int $excludeAppointmentId = 0): bool
{
    return in_array(substr($time, 0, 5), doctor_available_slots($doctorId, $branchId, $date, $duration, $prep, $cleanup, $excludeAppointmentId), true);
}

function available_doctors_for_slot(int $branchId, int $serviceId, string $date, string $time, bool $newPatient = false): array
{
    $service = service_definition($serviceId, $branchId);
    if (!$service || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date) || !preg_match('/^(?:[01]\d|2[0-3]):[0-5]\d$/', substr($time, 0, 5))) {
        return [];
    }
    $matches = [];
    foreach (bookable_dentists_for_service($branchId,$serviceId,$newPatient) as $doctor) {
        if (validate_doctor_slot((int) $doctor['id'], $branchId, $date, $time, (int) $service['duration_minutes'], (int) $service['preparation_minutes'], (int) $service['cleanup_minutes'])) {
            $matches[] = $doctor;
        }
    }
    return $matches;
}

function schedule_bypass_allowed(): bool
{
    return has_role(['super_admin','clinic_admin']) && role_context_has(['super_admin','clinic_admin']);
}

function schedule_time_within_branch_hours(int $branchId, string $date, string $time, int $duration): bool
{
    $hours = branch_hours_for_date($branchId, $date);
    if (!$hours) return false;
    $start = time_minutes($time);
    $end = $start + max(1, $duration);
    return $start >= time_minutes((string) $hours['open_time']) && $end <= time_minutes((string) $hours['close_time']);
}

function app_release_version(): string
{
    static $version;
    if ($version !== null) return $version;
    $path = DENTAL_ROOT . '/VERSION';
    $version = is_file($path) ? trim((string) file_get_contents($path)) : '0.0.0';
    return $version !== '' ? $version : '0.0.0';
}

function installed_application_version(): string
{
    return (string) setting_get('system', 'application_version', '0.0.0');
}

function treatment_completion_schema_ready(): bool
{
    try {
        foreach (['completion_destination','no_payment_reason','discharge_summary_json','billing_handoff_amount','billing_handoff_at','discharged_at','chair_released_at'] as $column) {
            if (!schema_column_exists(table('clinical_encounters'), $column)) return false;
        }
        return true;
    } catch (Throwable) {
        return false;
    }
}

function require_treatment_completion_schema(): void
{
    if (treatment_completion_schema_ready()) return;
    http_response_code(503);
    exit('The guided treatment-completion update is pending. Ask the Superuser to apply Version 0.5.9.8.25.');
}

function encounter_assistant_schema_ready(): bool
{
    try {
        foreach (['assistant_user_id','assistant_name','assistant_assigned_by','assistant_assigned_at'] as $column) {
            if (!schema_column_exists(table('clinical_encounters'), $column)) return false;
        }
        foreach (['entered_by_user_id','entered_by_role','verified_by_user_id','verified_at'] as $column) {
            if (!schema_column_exists(table('procedures'), $column)) return false;
        }
        return true;
    } catch (Throwable) {
        return false;
    }
}

function require_encounter_assistant_schema(): void
{
    if (encounter_assistant_schema_ready()) return;
    http_response_code(503);
    exit('The encounter Dental Assistant update is pending. Ask the Superuser to apply Version 0.5.9.8.26.');
}

function documentation_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) return $ready;
    try {
        $required = [
            'odontogram_entries','periodontal_charts','treatment_plans','prescriptions','documents',
            'consent_forms','referrals','laboratory_cases','follow_ups','recalls','communications',
            'patient_insurance','insurance_claims','inventory_items','sterilization_batches','permissions',
            'patient_portal_accounts','backup_logs','system_uploads'
        ];
        $names = array_map(static fn(string $name): string => table($name), $required);
        $placeholders = implode(',', array_fill(0, count($names), '?'));
        $stmt = db()->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name IN (' . $placeholders . ')');
        $stmt->execute(array_merge([(string) app_config('database.name')], $names));
        $ready = (int) $stmt->fetchColumn() === count($names);
    } catch (Throwable) {
        $ready = false;
    }
    return $ready;
}

function require_documentation_schema(): void
{
    if (documentation_schema_ready()) return;

    $route = task_route_key();
    $portalRoute = in_array($route, [
        'patient-login','patient-activate','patient-logout','patient-portal','patient-record'
    ], true);

    // Public patient routes must never jump into the staff update workspace.
    // A browser can carry both staff and patient cookies; redirecting a portal
    // request based on the staff identity can create an alternating portal /
    // System Setup redirect chain. Show a stable maintenance response instead.
    if (!$portalRoute && actual_role() === 'super_admin') {
        if (function_exists('dental_clear_task_workspace_context')) {
            dental_clear_task_workspace_context();
        }
        header('Location: ' . dental_clean_url('system_setup.php?tab=updates&from=documentation_gate'), true, 303);
        exit;
    }

    http_response_code(503);
    if ($portalRoute) {
        exit('The patient portal is temporarily unavailable while the clinic completes a system update. Please try again later.');
    }
    exit('This module is awaiting a system update. Please contact the system administrator.');
}

function doctor_workflow_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) return $ready;
    try {
        $required = [
            'dentist_profiles','dentist_specialties','clinical_privileges','dentist_clinical_privileges',
            'dentist_branch_assignments','dentist_service_assignments','dentist_documents','dentist_templates',
            'dentist_favorite_procedures','dentist_assistant_assignments','smtp_diagnostics'
        ];
        $names = array_map(static fn(string $name): string => table($name), $required);
        $placeholders = implode(',', array_fill(0, count($names), '?'));
        $stmt = db()->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name IN (' . $placeholders . ')');
        $stmt->execute(array_merge([(string) app_config('database.name')], $names));
        $ready = (int) $stmt->fetchColumn() === count($names);
    } catch (Throwable) {
        $ready = false;
    }
    return $ready;
}

function require_doctor_workflow_schema(): void
{
    if (!doctor_workflow_schema_ready()) {
        if (actual_role() === 'super_admin') {
            header('Location: system_setup.php?tab=updates');
            exit;
        }
        http_response_code(503);
        exit('The Doctor Profile and Clinical Day update is pending. Ask the Superuser to apply Version 0.5.0 in System Setup.');
    }
}


function clinical_sequence_schema_ready(): bool
{
    try {
        $required = ['patient_intakes','clinical_assessments','diagnoses','visit_workflow_events'];
        $names = array_map(static fn(string $name): string => table($name), $required);
        $placeholders = implode(',', array_fill(0, count($names), '?'));
        $stmt = db()->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name IN (' . $placeholders . ')');
        $stmt->execute(array_merge([(string) app_config('database.name')], $names));
        if ((int) $stmt->fetchColumn() !== count($names)) return false;
        $stmt = db()->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=? AND table_name=? AND column_name IN (?,?,?,?,?)');
        $stmt->execute([(string) app_config('database.name'), table('appointments'), 'appointment_origin','source_request_id','follow_up_id','reason_for_visit','confirmation_status']);
        return (int) $stmt->fetchColumn() === 5;
    } catch (Throwable) {
        return false;
    }
}

function require_clinical_sequence_schema(): void
{
    if (clinical_sequence_schema_ready()) return;
    if (actual_role() === 'super_admin') {
        header('Location: system_setup.php?tab=updates');
        exit;
    }
    http_response_code(503);
    exit('The complete clinical workflow update is pending. Ask the Superuser to apply Version 0.5.4.');
}


function clinical_workspace_redesign_schema_ready(): bool
{
    try {
        return schema_table_exists(table('diagnosis_catalog'))
            && schema_table_exists(table('clinical_usage_items'))
            && schema_column_exists(table('clinical_encounters'),'clinical_disposition')
            && schema_column_exists(table('clinical_encounters'),'disposition_reason')
            && schema_column_exists(table('clinical_encounters'),'patient_advice')
            && schema_column_exists(table('clinical_assessments'),'extraoral_status')
            && schema_column_exists(table('clinical_assessments'),'radiographic_status')
            && schema_column_exists(table('invoice_items'),'clinical_usage_item_id');
    } catch (Throwable) {
        return false;
    }
}

function require_clinical_workspace_redesign_schema(): void
{
    if (clinical_workspace_redesign_schema_ready()) return;
    if (actual_role() === 'super_admin') {
        header('Location: system_setup.php?tab=updates');
        exit;
    }
    http_response_code(503);
    exit('The redesigned Clinical Encounter update is pending. Ask the Superuser to apply Version 0.5.9.8.7.');
}

require_once DENTAL_ROOT . '/app/clinical_vitals.php';
require_once DENTAL_ROOT . '/app/patient_timeline.php';
require_once DENTAL_ROOT . '/app/visit_integrity.php';
require_once DENTAL_ROOT . '/app/operation_integrity.php';
require_once DENTAL_ROOT . '/app/clinical_flow_stability.php';
require_once DENTAL_ROOT . '/app/clinic_defaults.php';
require_once DENTAL_ROOT . '/app/deployment_mode.php';
require_once DENTAL_ROOT . '/app/release_candidate.php';
require_once DENTAL_ROOT . '/app/hardening_foundation.php';

function workflow_event(
    int $patientId,
    string $stageCode,
    string $stageLabel,
    array $links = [],
    ?string $details = null
): void {
    if (!clinical_sequence_schema_ready() || $patientId < 1) return;
    try {
        $visitId = 0;
        if (visit_integrity_schema_ready()) {
            $canonicalLinks=$links;
            // A scheduled follow-up appointment is a new visit. Keep the follow-up
            // recommendation attached to the source encounter instead of merging
            // the future appointment into the completed source visit.
            if(!empty($canonicalLinks['appointment_id'])&&!empty($canonicalLinks['follow_up_id'])){
                $follow=visit_entity_row(db(),'follow_up',(int)$canonicalLinks['follow_up_id']);
                if($follow&&!empty($follow['encounter_id'])){
                    $encounter=visit_entity_row(db(),'encounter',(int)$follow['encounter_id']);
                    $sourceAppointmentId=(int)($encounter['appointment_id']??0);
                    if($sourceAppointmentId>0&&$sourceAppointmentId!==(int)$canonicalLinks['appointment_id'])unset($canonicalLinks['follow_up_id']);
                }
            }
            $visitId = visit_link_and_sync(db(), array_merge($canonicalLinks, ['patient_id'=>$patientId]), $stageCode, $details, (int)(current_user()['id'] ?? 0));
        }
        $hasVisit = schema_column_exists(table('visit_workflow_events'),'visit_id');
        $columns = $hasVisit
            ? '(visit_id,patient_id,appointment_request_id,appointment_id,queue_entry_id,encounter_id,invoice_id,follow_up_id,stage_code,stage_label,details,recorded_by,created_at)'
            : '(patient_id,appointment_request_id,appointment_id,queue_entry_id,encounter_id,invoice_id,follow_up_id,stage_code,stage_label,details,recorded_by,created_at)';
        $values = $hasVisit ? 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,NOW())' : 'VALUES (?,?,?,?,?,?,?,?,?,?,?,NOW())';
        $stmt = db()->prepare('INSERT INTO `' . table('visit_workflow_events') . '` ' . $columns . ' ' . $values);
        $params = [
            $patientId,
            !empty($links['appointment_request_id']) ? (int) $links['appointment_request_id'] : null,
            !empty($links['appointment_id']) ? (int) $links['appointment_id'] : null,
            !empty($links['queue_entry_id']) ? (int) $links['queue_entry_id'] : null,
            !empty($links['encounter_id']) ? (int) $links['encounter_id'] : null,
            !empty($links['invoice_id']) ? (int) $links['invoice_id'] : null,
            !empty($links['follow_up_id']) ? (int) $links['follow_up_id'] : null,
            substr($stageCode, 0, 60),
            substr($stageLabel, 0, 150),
            $details,
            current_user()['id'] ?? null,
        ];
        if ($hasVisit) array_unshift($params, $visitId ?: null);
        $stmt->execute($params);
    } catch (Throwable $exception) {
        // Workflow history supplements the authoritative records and must never block care.
        error_log('Visit workflow event warning: ' . $exception->getMessage());
    }
}

function active_patient_intake(int $patientId, ?int $queueId = null, ?int $appointmentId = null): ?array
{
    if (!clinical_sequence_schema_ready()) return null;
    $sql = 'SELECT * FROM `' . table('patient_intakes') . '` WHERE patient_id=?';
    $args = [$patientId];
    if ($queueId) { $sql .= ' AND queue_entry_id=?'; $args[] = $queueId; }
    elseif ($appointmentId) { $sql .= ' AND appointment_id=?'; $args[] = $appointmentId; }
    $sql .= ' ORDER BY id DESC LIMIT 1';
    $stmt = db()->prepare($sql);
    $stmt->execute($args);
    return $stmt->fetch() ?: null;
}

function support_request_schema_ready(): bool
{
    try {
        $requests = table('support_requests');
        if (!schema_table_exists($requests)) return false;
        foreach (['branch_id','user_id','category','priority','subject','message','page_route','page_url','error_reference','context_json','status','assigned_to_user_id','resolution_note','resolved_at','created_at','updated_at'] as $column) {
            if (!schema_column_exists($requests, $column)) return false;
        }
        return true;
    } catch (Throwable) {
        return false;
    }
}

function app_update_pending(): bool
{
    // Do not keep a function-static result here. A migration can be reconciled
    // later in the same request, and the session cache is explicitly cleared by
    // the updater. Re-reading after that clear prevents a stale navigation badge.
    $pending = false;
    $release = app_release_version();
    $cached = $_SESSION['dental_update_pending_cache'] ?? null;
    if (is_array($cached)
        && (string)($cached['release'] ?? '') === $release
        && (int)($cached['checked_at'] ?? 0) >= time() - 30
        && empty($cached['pending'])) {
        // A recent "current" result is safe to reuse. A cached "pending" result
        // is always rechecked because another signed-in Superuser session may
        // have completed the update after this staff session cached the banner.
        return false;
    }

    // From v0.9 onward, update status has one authority: the installed
    // version plus the exact current-release migration ledger row. Optional
    // documentation and broad health/readiness diagnostics never open the
    // update gate or show an update button.
    if (version_compare($release, '0.9.0.0', '>=')) {
        $pending = version_compare(installed_application_version(), $release, '<');
        if (!$pending) {
            try {
                $ledger = table('system_migration_ledger');
                if (!schema_table_exists($ledger)) {
                    $pending = true;
                } else {
                    $manifest = function_exists('dental_hardening_release_manifest')
                        ? dental_hardening_release_manifest()
                        : [];
                    $migrationKey = trim((string)($manifest['migration_key'] ?? ''));
                    $checksum = strtolower(trim((string)($manifest['migration_checksum_sha256'] ?? '')));
                    if ($migrationKey !== '') {
                        $stmt = db()->prepare('SELECT checksum_sha256,status FROM `' . $ledger . '` WHERE migration_key=? LIMIT 1');
                        $stmt->execute([$migrationKey]);
                    } else {
                        // Compatibility for installations where handbooks or
                        // legacy docs were removed: a completed exact version
                        // ledger row still proves the release is installed.
                        $stmt = db()->prepare('SELECT checksum_sha256,status FROM `' . $ledger . '` WHERE version=? ORDER BY completed_at DESC,id DESC LIMIT 1');
                        $stmt->execute([$release]);
                    }
                    $row = $stmt->fetch();
                    $pending = !$row || strtolower((string)($row['status'] ?? '')) !== 'completed';
                    $stored = strtolower(trim((string)($row['checksum_sha256'] ?? '')));
                    if (!$pending && $checksum !== '' && $stored !== '') {
                        $compatible = function_exists('dental_hardening_migration_checksum_is_officially_compatible')
                            ? dental_hardening_migration_checksum_is_officially_compatible($migrationKey, $stored, $checksum)
                            : hash_equals($stored, $checksum);
                        if (!$compatible) $pending = true;
                    }
                }
            } catch (Throwable) {
                $pending = true;
            }
        }
        $_SESSION['dental_update_pending_cache'] = [
            'release' => $release,
            'pending' => $pending,
            'checked_at' => time(),
        ];
        return $pending;
    }

    // From v0.8.1.2 onward, update status is deterministic: the installed
    // version marker and the current release's own schema/flags decide whether
    // an update is pending. Historical readiness markers remain diagnostics
    // and must never make a newer, already-applied release appear pending.
    if (version_compare($release, '0.8.1.2', '>=')
        && version_compare(installed_application_version(), $release, '>=')) {
        $pending = !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')
            || !schema_column_exists(table('users'), 'last_role_switch_at')
            || !schema_column_exists(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';
        $_SESSION['dental_update_pending_cache'] = [
            'release' => $release,
            'pending' => $pending,
            'checked_at' => time(),
        ];
        return $pending;
    }
    $pending = version_compare(installed_application_version(), $release, '<')
        || !application_schema_ready()
        || !public_intake_schema_ready()
        || !availability_schema_ready()
        || !queue_cashier_schema_ready()
        || !documentation_schema_ready()
        || !doctor_workflow_schema_ready()
        || !schema_column_exists(table('procedures'),'branch_id')
        || !backup_printing_schema_ready()
        || !clinical_sequence_schema_ready()
        || !patient_continuity_schema_ready()
        || !doctor_chair_assignment_schema_ready()
        || !doctor_integrity_schema_ready()
        || !inhouse_resource_schema_ready()
        || !revenue_cycle_schema_ready()
        || !schema_column_exists(table('service_catalog'),'required_privilege_code')
        || !schema_column_exists(table('appointment_requests'),'provider_preference_mode')
        || !clinical_task_assignment_schema_ready()
        || !schema_table_exists(table('dentist_credential_history'))
        || !schema_table_exists(table('medicine_catalog'))
        || !schema_column_exists(table('prescription_items'),'medicine_catalog_id')
        || !schema_table_exists(table('diagnostic_review_tasks'))
        || !clinical_workspace_redesign_schema_ready()
        || (version_compare($release,'0.5.9.8.22','>=') && !visit_integrity_schema_ready())
        || (version_compare($release,'0.5.9.8.24','>=') && !clinical_vitals_schema_ready())
        || (version_compare($release,'0.5.9.8.25','>=') && !treatment_completion_schema_ready())
        || (version_compare($release,'0.5.9.8.30','>=') && !support_request_schema_ready())
        || (version_compare($release,'0.5.9.8.43','>=') && function_exists('dental_security_schema_ready') && !dental_security_schema_ready())
        || (version_compare($release,'0.5.9.8.74','>=') && !staff_profile_dual_role_schema_ready())
        || (version_compare($release,'0.5.9.8.75','>=') && (string) setting_get('system','task_switcher_visibility_ready','0') !== '1')
        || (version_compare($release,'0.5.9.8.76','>=') && (string) setting_get('system','doctor_profile_credential_boundary_ready','0') !== '1')
        || (version_compare($release,'0.7.8.0','>=') && (
            (string) setting_get('system','production_acceptance_v0780_ready','0') !== '1'
            || (string) setting_get('system','chair_release_integrity_v0780_ready','0') !== '1'
            || (string) setting_get('system','task_view_dead_end_guard_v0780_ready','0') !== '1'
            || (string) setting_get('system','pending_only_update_v0780_ready','0') !== '1'
            || (string) setting_get('system','runtime_diagnostics_v0780_ready','0') !== '1'
            || (string) setting_get('system','clean_release_packaging_v0780_ready','0') !== '1'
        ))
        || (version_compare($release,'0.7.9.0','>=') && (
            (string) setting_get('system','production_monitoring_v0790_ready','0') !== '1'
            || (string) setting_get('system','request_telemetry_v0790_ready','0') !== '1'
            || (string) setting_get('system','role_acceptance_v0790_ready','0') !== '1'
            || (string) setting_get('system','concurrency_verification_v0790_ready','0') !== '1'
            || (string) setting_get('system','recovery_monitoring_v0790_ready','0') !== '1'
            || (string) setting_get('system','error_standardization_v0790_ready','0') !== '1'
        ))
        || (version_compare($release,'0.7.9.1','>=') && (
            !schema_column_exists(table('dentist_profiles'),'photo_original_path')
            || !schema_column_exists(table('dentist_profiles'),'photo_square_path')
            || !schema_column_exists(table('dentist_profiles'),'photo_portrait_path')
            || !schema_column_exists(table('dentist_profiles'),'photo_thumbnail_path')
            || (string) setting_get('system','professional_doctor_photo_v0791_ready','0') !== '1'
            || (string) setting_get('system','public_task_preview_isolation_v0791_ready','0') !== '1'
        ))
        || (version_compare($release,'0.7.9.2','>=') && (
            !clinical_flow_stability_schema_ready()
            || (string) setting_get('system','clinical_context_lock_v0792_ready','0') !== '1'
            || (string) setting_get('system','assistant_decision_gate_v0792_ready','0') !== '1'
            || (string) setting_get('system','clinical_dead_end_guard_v0792_ready','0') !== '1'
            || (string) setting_get('system','odontogram_interaction_guard_v0792_ready','0') !== '1'
            || (string) setting_get('system','walkin_focus_handoff_v0792_ready','0') !== '1'
        ))
        || (version_compare($release,'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($release,'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($release,'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($release,'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($release,'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($release,'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($release,'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($release,'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($release,'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')
            || !schema_column_exists(table('users'),'last_role_switch_at')
            || !schema_column_exists(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'
        ));
    $_SESSION['dental_update_pending_cache'] = ['release'=>$release,'pending'=>$pending,'checked_at'=>time()];
    return $pending;
}


function revenue_cycle_schema_ready(): bool
{
    try {
        return schema_table_exists(table('cashier_sessions'))
            && schema_table_exists(table('discount_authorizations'))
            && schema_table_exists(table('zero_charge_authorizations'))
            && schema_table_exists(table('payment_adjustments'))
            && schema_table_exists(table('insurance_claim_history'))
            && schema_column_exists(table('invoice_items'), 'source_type')
            && schema_column_exists(table('invoices'), 'discount_authorization_id')
            && schema_column_exists(table('invoices'), 'legacy_charge_reviewed_at')
            && schema_column_exists(table('payments'), 'cashier_session_id')
            && schema_column_exists(table('insurance_claims'), 'payment_id')
            && schema_column_exists(table('insurance_claims'), 'authorization_no')
            && schema_column_exists(table('insurance_claims'), 'created_by')
        && schema_column_exists(table('insurance_claims'), 'updated_at');
    } catch (Throwable) {
        return false;
    }
}


function backup_printing_schema_ready(): bool
{
    try {
        $stmt = db()->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name=?');
        $stmt->execute([(string) app_config('database.name'), table('print_logs')]);
        if ((int) $stmt->fetchColumn() !== 1) return false;
        $stmt = db()->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=? AND table_name=? AND column_name IN (?,?,?,?,?)');
        $stmt->execute([(string) app_config('database.name'), table('backup_logs'), 'backup_type','checksum_sha256','file_count','is_encrypted','verified_at']);
        return (int) $stmt->fetchColumn() === 5;
    } catch (Throwable) { return false; }
}

function permission_schema_ready(): bool
{
    static $ready = null;
    if ($ready !== null) return $ready;
    try {
        $stmt = db()->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name IN (?,?)');
        $stmt->execute([(string) app_config('database.name'), table('permissions'), table('role_permissions')]);
        return $ready = ((int) $stmt->fetchColumn() === 2);
    } catch (Throwable) {
        return $ready = false;
    }
}

function active_role_permission_hard_denied(string $role, string $permission): bool
{
    $denied = [
        'clinic_admin' => ['clinical.manage','clinical.draft','clinical.write_self','clinical.finalize','clinical.finalize_self'],
        'receptionist' => ['billing.manage','cashier.session.manage','inventory.manage','inventory.catalog','clinical.manage','clinical.draft','clinical.finalize','clinical.finalize_self','settings.manage','staff.manage','smtp.manage','uploads.manage'],
        'staff' => ['inventory.manage','inventory.catalog','clinical.manage','clinical.draft','clinical.finalize','clinical.finalize_self','settings.manage','staff.manage','smtp.manage','uploads.manage','insurance.manage','insurance.financial'],
        'dentist' => ['billing.manage','cashier.session.manage','inventory.manage','inventory.catalog','settings.manage','staff.manage','smtp.manage'],
        'dental_assistant' => ['queue.intake','queue.frontdesk','billing.manage','cashier.session.manage','inventory.manage','inventory.catalog','clinical.finalize','clinical.finalize_self','settings.manage','staff.manage','smtp.manage','uploads.manage'],
        'cashier' => ['patients.manage','patients.medical','patients.portal','appointment_requests.manage','appointments.manage','queue.intake','queue.frontdesk','queue.clinical','queue.clinical_support','clinical.view','clinical.manage','clinical.draft','clinical.finalize','inventory.manage','inventory.catalog','sterilization.manage','settings.manage','staff.manage','smtp.manage','uploads.manage'],
        'inventory_officer' => ['patients.manage','patients.medical','patients.portal','appointment_requests.manage','appointments.manage','queue.intake','queue.frontdesk','queue.clinical','queue.clinical_support','clinical.manage','clinical.draft','clinical.finalize','billing.manage','cashier.session.manage','settings.manage','staff.manage','smtp.manage','uploads.manage'],
    ];
    return in_array($permission, $denied[$role] ?? [], true);
}

function can(string $permission, array|string $fallbackRoles = []): bool
{
    if (superuser_primary_workspace()) return true;
    if (role_permission_granted_for(active_role(), $permission, $fallbackRoles)) return true;
    return function_exists('dental_break_glass_permission_allowed')
        && dental_break_glass_permission_allowed($permission, signed_user_branch_id() ?: null);
}

function clinical_task_can_draft(): bool
{
    return active_role_is('dentist') || active_role_is('dental_assistant');
}

function clinical_task_can_finalize(): bool
{
    return active_role_is('dentist');
}

function require_permission(string $permission, array|string $fallbackRoles = []): void
{
    if (!can($permission, $fallbackRoles)) access_denied('Your signed-in role does not have permission for this task.');
    if ($permission === 'clinical.manage' && !clinical_task_can_draft()) {
        access_denied('Clinical changes require the signed-in Dentist or Dental Assistant in their own task workspace.');
    }
    if ($permission === 'clinical.finalize' && !clinical_task_can_finalize()) {
        access_denied('Only the signed-in Dentist in the Dentist task workspace may finalize or prescribe.');
    }
}

function task_dentist_scope_ids(): array
{
    if (!current_user()) return [];
    $role = active_role();
    if ($role === 'dentist') {
        $id = active_role_can_operate('dentist') ? (int) (current_user()['id'] ?? 0) : (int) ($_SESSION['task_dentist_id'] ?? 0);
        return $id > 0 ? [$id] : [];
    }
    if ($role === 'dental_assistant' && active_role_can_operate('dental_assistant') && doctor_workflow_schema_ready()) {
        try {
            $stmt = db()->prepare('SELECT dentist_user_id FROM `' . table('dentist_assistant_assignments') . '` WHERE assistant_user_id=? AND status="active" AND (effective_from IS NULL OR effective_from<=CURDATE()) AND (effective_until IS NULL OR effective_until>=CURDATE())');
            $stmt->execute([(int) (current_user()['id'] ?? 0)]);
            return array_values(array_filter(array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN))));
        } catch (Throwable) {
            return [];
        }
    }
    return [];
}

function task_dentist_authorized_branch_ids(array $dentistIds): array
{
    $dentistIds = array_values(array_unique(array_filter(array_map('intval', $dentistIds))));
    if (!$dentistIds) return [];
    $branches = [];
    try {
        $marks = implode(',', array_fill(0, count($dentistIds), '?'));
        $stmt = db()->prepare('SELECT branch_id FROM `' . table('users') . '` WHERE id IN (' . $marks . ') AND branch_id IS NOT NULL');
        $stmt->execute($dentistIds);
        $branches = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
        if (schema_table_exists(table('dentist_branch_assignments'))) {
            $stmt = db()->prepare('SELECT branch_id FROM `' . table('dentist_branch_assignments') . '` WHERE dentist_user_id IN (' . $marks . ') AND status="active"');
            $stmt->execute($dentistIds);
            array_push($branches, ...array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN)));
        }
    } catch (Throwable $exception) {
        error_log('DentalCare dentist branch scope fallback: ' . $exception->getMessage());
    }
    return array_values(array_unique(array_filter($branches)));
}

function task_patient_scope_condition(string $patientExpression): array
{
    if (!current_user()) return ['', []];

    $clauses = [];
    $params = [];
    $role = active_role();
    $patientsTable = table('patients');
    $userBranchId = (int) (current_user()['branch_id'] ?? 0);
    $assignmentScopedRole = in_array($role, ['dentist','dental_assistant'], true);

    // General operational roles remain scoped to the patient's home branch.
    // Dentists and Dental Assistants are scoped instead through a concrete care
    // assignment and that assignment's authorized branch. This permits an
    // assigned Dentist to review a future patient's profile even when the
    // patient's home branch differs from the appointment branch.
    if (!$assignmentScopedRole && $role !== 'super_admin' && schema_column_exists($patientsTable, 'branch_id')) {
        if ($userBranchId > 0) {
            $clauses[] = 'EXISTS(SELECT 1 FROM `' . $patientsTable . '` tsp_branch WHERE tsp_branch.id=' . $patientExpression . ' AND tsp_branch.branch_id=?)';
            $params[] = $userBranchId;
        } elseif (!has_role(['super_admin','clinic_admin'])) {
            $clauses[] = '1=0';
        }
    }

    if ($assignmentScopedRole) {
        $ids = task_dentist_scope_ids();
        $assignmentBranches = [];
        $assignmentParams = [];

        if ($ids) {
            $dentistMarks = implode(',', array_fill(0, count($ids), '?'));
            $sources = [
                [table('appointments'),'tsa','dentist_user_id',' AND tsa.status NOT IN ("cancelled","no_show") AND tsa.start_at>=DATE_SUB(NOW(),INTERVAL 1 DAY)'],
                [table('appointment_requests'),'tsar','preferred_dentist_user_id',' AND tsar.status IN ("submitted","needs_verification","reviewing","approved","scheduled")'],
                [table('clinical_encounters'),'tsc','dentist_user_id',''],
                [table('queue_entries'),'tsq','dentist_user_id',' AND tsq.status NOT IN ("cancelled","left_before_treatment")'],
                [table('treatment_plans'),'tsp','dentist_user_id',' AND tsp.status NOT IN ("cancelled","expired")'],
                [table('treatment_plan_reassignments'),'tsr','to_dentist_user_id',' AND tsr.status IN ("pending","accepted")'],
                [table('clinical_task_assignments'),'tsta','assigned_dentist_user_id',' AND tsta.status IN ("pending","accepted","in_progress")'],
                [table('clinical_task_assignments'),'tstr','requesting_dentist_user_id',' AND tstr.status IN ("pending","accepted","in_progress")'],
                [table('diagnostic_review_tasks'),'tsd','assigned_dentist_user_id',' AND tsd.status IN ("pending","in_review","validated","closed")'],
            ];
            foreach ($sources as [$tableName,$alias,$dentistColumn,$statusSql]) {
                if (!schema_table_exists($tableName)
                    || !schema_column_exists($tableName,'patient_id')
                    || !schema_column_exists($tableName,$dentistColumn)) continue;
                $assignmentBranches[] = 'EXISTS(SELECT 1 FROM `' . $tableName . '` ' . $alias
                    . ' WHERE ' . $alias . '.patient_id=' . $patientExpression
                    . ' AND ' . $alias . '.`' . $dentistColumn . '` IN (' . $dentistMarks . ')' . $statusSql . ')';
                array_push($assignmentParams, ...$ids);
            }
        } elseif ($role === 'dental_assistant' && task_preview_mode()) {
            foreach ([[table('appointments'),'tsa','start_at'],[table('queue_entries'),'tsq','checked_in_at']] as [$tableName,$alias,$dateColumn]) {
                if (!schema_table_exists($tableName) || !schema_column_exists($tableName,'patient_id') || !schema_column_exists($tableName,$dateColumn)) continue;
                $assignmentBranches[] = 'EXISTS(SELECT 1 FROM `' . $tableName . '` ' . $alias
                    . ' WHERE ' . $alias . '.patient_id=' . $patientExpression
                    . ' AND DATE(' . $alias . '.`' . $dateColumn . '`)=CURDATE())';
            }
        }

        // An encounter-specific Dental Assistant assignment grants access only
        // to the patient attached to that exact encounter. It does not expose
        // every patient belonging to the responsible Dentist.
        if ($role === 'dental_assistant' && encounter_assistant_schema_ready()) {
            $assignmentBranches[] = 'EXISTS(SELECT 1 FROM `' . table('clinical_encounters') . '` tsea'
                . ' WHERE tsea.patient_id=' . $patientExpression . ' AND tsea.assistant_user_id=?)';
            $assignmentParams[] = (int)(current_user()['id'] ?? 0);
        }

        $clauses[] = $assignmentBranches ? '(' . implode(' OR ', $assignmentBranches) . ')' : '1=0';
        array_push($params, ...$assignmentParams);
    }

    return $clauses ? [' AND ' . implode(' AND ', $clauses), $params] : ['', []];
}

function task_patient_scope_allowed(int $patientId): bool
{
    if (!current_user()) {
        $portal = patient_portal_user();
        if (!$portal) return false;
        $accountId = (int) ($portal['id'] ?? 0);
        if ($accountId > 0 && patient_portal_account_patient_schema_ready()) {
            return patient_portal_access_for_patient($accountId, $patientId) !== null;
        }
        return (int)($portal['patient_id'] ?? 0) === $patientId;
    }
    [$condition, $params] = task_patient_scope_condition((string) max(0, $patientId));
    if ($condition === '') return true;
    try {
        $stmt = db()->prepare('SELECT 1 WHERE 1=1' . $condition);
        $stmt->execute($params);
        return (bool) $stmt->fetchColumn();
    } catch (Throwable $exception) {
        error_log('DentalCare patient scope compatibility fallback: ' . $exception->getMessage());
        return false;
    }
}

function patient_options(bool $activeOnly = true): array
{
    $patients = table('patients');
    $sql = 'SELECT p.id,p.patient_no,p.first_name,p.middle_name,p.last_name,p.email,p.mobile,p.medical_alerts FROM `' . $patients . '` p WHERE 1=1';
    if (schema_column_exists($patients, 'deleted_at')) $sql .= ' AND p.deleted_at IS NULL';
    $params = [];
    if ($activeOnly && schema_column_exists($patients, 'status')) $sql .= ' AND p.status IN ("active","pending")';
    [$scopeSql, $scopeParams] = task_patient_scope_condition('p.id');
    $sql .= $scopeSql . ' ORDER BY p.last_name,p.first_name';
    $stmt = db()->prepare($sql);
    $stmt->execute(array_merge($params, $scopeParams));
    return $stmt->fetchAll();
}

function dentist_options(?int $branchId = null): array
{
    $profileJoin = doctor_workflow_schema_ready()
        ? ' LEFT JOIN `' . table('dentist_profiles') . '` dp ON dp.user_id=u.id LEFT JOIN `' . table('dentist_specialties') . '` ds ON ds.id=dp.specialty_id '
        : '';
    $structuredNameSelect = schema_column_exists(table('users'),'first_name')
        && schema_column_exists(table('users'),'middle_name')
        && schema_column_exists(table('users'),'last_name')
        ? ',u.first_name,u.middle_name,u.last_name'
        : ',NULL first_name,NULL middle_name,NULL last_name';
    $select = doctor_workflow_schema_ready()
        ? 'u.id,u.name,u.email,u.branch_id,u.name display_name,dp.professional_title,ds.name specialty_name,dp.accepting_new_patients'.$structuredNameSelect
        : 'u.id,u.name,u.email,u.branch_id,u.name display_name,NULL professional_title,NULL specialty_name,1 accepting_new_patients'.$structuredNameSelect;
    $rolePredicate = 'r.code="dentist"';
    if (user_role_assignments_schema_ready()) {
        $assignmentExpiry = schema_column_exists(table('user_role_assignments'), 'expires_at')
            ? ' AND (ura.expires_at IS NULL OR ura.expires_at>NOW())'
            : '';
        $rolePredicate = '(r.code="dentist" OR EXISTS ('
            . 'SELECT 1 FROM `' . table('user_role_assignments') . '` ura '
            . 'JOIN `' . table('roles') . '` assigned_role ON assigned_role.id=ura.role_id '
            . 'WHERE ura.user_id=u.id AND ura.status="active" AND assigned_role.code="dentist"'
            . $assignmentExpiry
            . '))';
    }
    $sql = 'SELECT ' . $select . ' FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id ' . $profileJoin . ' WHERE ' . $rolePredicate . ' AND u.status="active"';
    $args = [];
    if ($branchId !== null) {
        $sql .= ' AND (u.branch_id=? OR u.branch_id IS NULL';
        if (doctor_workflow_schema_ready()) {
            $sql .= ' OR EXISTS(SELECT 1 FROM `' . table('dentist_branch_assignments') . '` dba WHERE dba.dentist_user_id=u.id AND dba.branch_id=? AND dba.status="active")';
            $args[] = $branchId;
        }
        $sql .= ')';
        array_unshift($args, $branchId);
    }
    $sql .= ' ORDER BY display_name';
    $stmt = db()->prepare($sql);
    $stmt->execute($args);
    $rows=$stmt->fetchAll();
    foreach($rows as &$row){
        $legacyName=trim((string)($row['name']??''));
        $displayName=trim((string)($row['display_name']??''));
        $structured=user_full_name($row);
        if($structured!==''){
            $row['name']=$structured;
            // v0.9.0.9: the staff identity record is the single source of truth.
            // Dentist professional settings add title/specialty but do not maintain a second name.
            $row['display_name']=$structured;
        }
    }
    unset($row);
    return $rows;
}

function dentist_profile(int $userId): ?array
{
    if (!doctor_workflow_schema_ready()) return null;
    $structuredNameSelect = schema_column_exists(table('users'),'first_name')
        && schema_column_exists(table('users'),'middle_name')
        && schema_column_exists(table('users'),'last_name')
        ? ',u.first_name,u.middle_name,u.last_name'
        : ',NULL first_name,NULL middle_name,NULL last_name';
    $rolePredicate = 'r.code="dentist"';
    if (user_role_assignments_schema_ready()) {
        $assignmentExpiry = schema_column_exists(table('user_role_assignments'), 'expires_at')
            ? ' AND (ura.expires_at IS NULL OR ura.expires_at>NOW())'
            : '';
        $rolePredicate = '(r.code="dentist" OR EXISTS ('
            . 'SELECT 1 FROM `' . table('user_role_assignments') . '` ura '
            . 'JOIN `' . table('roles') . '` assigned_role ON assigned_role.id=ura.role_id '
            . 'WHERE ura.user_id=u.id AND ura.status="active" AND assigned_role.code="dentist"'
            . $assignmentExpiry
            . '))';
    }
    $stmt = db()->prepare(
        'SELECT dp.*,u.name account_name,u.email,u.status user_status,u.branch_id account_branch_id'.$structuredNameSelect.',ds.name specialty_name,b.name branch_name '
        . 'FROM `' . table('users') . '` u '
        . 'JOIN `' . table('roles') . '` r ON r.id=u.role_id '
        . 'LEFT JOIN `' . table('dentist_profiles') . '` dp ON dp.user_id=u.id '
        . 'LEFT JOIN `' . table('dentist_specialties') . '` ds ON ds.id=dp.specialty_id '
        . 'LEFT JOIN `' . table('branches') . '` b ON b.id=COALESCE(dp.primary_branch_id,u.branch_id) '
        . 'WHERE u.id=? AND ' . $rolePredicate . ' LIMIT 1'
    );
    $stmt->execute([$userId]);
    $profile=$stmt->fetch() ?: null;
    if(!$profile)return null;
    $structured=user_full_name(array_merge($profile,['name'=>(string)($profile['account_name']??'')]));
    if($structured!=='')$profile['account_name']=$structured;
    return $profile;
}

function dentist_display_name(array $profile): string
{
    $identityName = trim((string)($profile['account_name'] ?? $profile['name'] ?? $profile['display_name'] ?? ''));
    return trim(implode(' ', array_filter([
        (string) ($profile['professional_title'] ?? ''),
        $identityName,
    ])));
}

function dentist_credential_schema_ready(): bool
{
    return schema_column_exists(table('dentist_profiles'), 'credential_status')
        && schema_column_exists(table('dentist_profiles'), 'credential_verified_at');
}

function dentist_credentials_verified(int $dentistUserId, ?string &$reason = null): bool
{
    if (!dentist_credential_schema_ready()) return true;
    $profile = dentist_profile($dentistUserId);
    if (!$profile) {
        $reason = 'Dentist professional record is missing.';
        return false;
    }
    if ((string)($profile['credential_status'] ?? 'pending') !== 'verified') {
        $reason = 'Professional credentials are not verified.';
        return false;
    }
    if (trim((string)($profile['license_number'] ?? '')) === '') {
        $reason = 'Professional license number is missing.';
        return false;
    }
    $expiry = trim((string)($profile['license_expiry'] ?? ''));
    if ($expiry === '' || $expiry < date('Y-m-d')) {
        $reason = $expiry === '' ? 'Professional license expiry is missing.' : 'Professional license is expired.';
        return false;
    }
    try {
        $stmt = db()->prepare(
            'SELECT COUNT(*) FROM `' . table('dentist_documents') . '` '
            . 'WHERE dentist_user_id=? AND document_type="license" AND verification_status="verified" '
            . 'AND (expires_at IS NULL OR expires_at>=CURDATE())'
        );
        $stmt->execute([$dentistUserId]);
        if ((int)$stmt->fetchColumn() < 1) {
            $reason = 'A current verified professional-license document is required.';
            return false;
        }
    } catch (Throwable $exception) {
        $reason = 'Professional-license verification could not be confirmed.';
        return false;
    }
    return true;
}

function require_dentist_credentials_verified(int $dentistUserId): void
{
    $reason = null;
    if (!dentist_credentials_verified($dentistUserId, $reason)) {
        throw new RuntimeException($reason ?: 'Professional credentials are not verified.');
    }
}

function clinical_consent_required_schema(): array
{
    return [
        table('consent_forms') => [
            'patient_id','encounter_id','treatment_plan_id','treatment_plan_item_id','consent_type','version_no',
            'content_snapshot','risks_snapshot','alternatives_snapshot','refusal_consequences_snapshot',
            'questions_answered','signer_name','signer_relationship','signer_type','guardian_id',
            'identity_verification_method','signature_method','signature_attestation_snapshot','status',
            'signed_at','expires_at','withdrawn_at','declined_at','decline_reason','form_hash','signature_hash',
            'witness_user_id','dentist_user_id','created_by','signed_by_user_id','created_at',
        ],
        table('clinical_encounters') => ['consent_form_id','consent_exception_reason'],
        table('treatment_plan_items') => ['consent_status'],
        table('procedures') => ['consent_form_id'],
    ];
}

function clinical_consent_missing_schema_columns(): array
{
    $missing = [];
    foreach (clinical_consent_required_schema() as $tableName => $columns) {
        foreach ($columns as $column) {
            if (!schema_column_exists($tableName, $column)) {
                $missing[] = $tableName . '.' . $column;
            }
        }
    }
    return $missing;
}

function clinical_consent_integrity_schema_ready(): bool
{
    return clinical_consent_missing_schema_columns() === [];
}

function clinical_valid_consent_for_item(PDO $pdo, int $patientId, int $treatmentItemId, int $dentistUserId): ?array
{
    if (!clinical_consent_integrity_schema_ready() || $patientId < 1 || $treatmentItemId < 1 || $dentistUserId < 1) return null;
    $stmt = $pdo->prepare(
        'SELECT c.* FROM `' . table('consent_forms') . '` c '
        . 'WHERE c.patient_id=? AND c.treatment_plan_item_id=? AND c.dentist_user_id=? '
        . 'AND c.status="signed" AND c.signed_at IS NOT NULL AND c.form_hash IS NOT NULL AND c.signature_hash IS NOT NULL '
        . 'AND (c.expires_at IS NULL OR c.expires_at>=CURDATE()) '
        . 'ORDER BY c.signed_at DESC,c.id DESC LIMIT 1'
    );
    $stmt->execute([$patientId,$treatmentItemId,$dentistUserId]);
    return $stmt->fetch() ?: null;
}

function clinical_valid_consent_for_encounter(PDO $pdo, int $patientId, int $encounterId, int $dentistUserId): ?array
{
    if (!clinical_consent_integrity_schema_ready() || $patientId < 1 || $encounterId < 1 || $dentistUserId < 1) return null;
    $stmt = $pdo->prepare(
        'SELECT c.* FROM `' . table('consent_forms') . '` c '
        . 'WHERE c.patient_id=? AND c.encounter_id=? AND c.dentist_user_id=? '
        . 'AND c.status="signed" AND c.signed_at IS NOT NULL AND c.form_hash IS NOT NULL AND c.signature_hash IS NOT NULL '
        . 'AND (c.expires_at IS NULL OR c.expires_at>=CURDATE()) '
        . 'ORDER BY c.signed_at DESC,c.id DESC LIMIT 1'
    );
    $stmt->execute([$patientId,$encounterId,$dentistUserId]);
    return $stmt->fetch() ?: null;
}

function clinical_consent_form_hash(array $payload): string
{
    ksort($payload);
    return hash('sha256', (string)json_encode($payload, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));
}

function dentist_has_clinical_privilege(int $dentistUserId, string $code, ?string &$reason = null): bool
{
    $profile = dentist_profile($dentistUserId);
    if (!$profile) {
        $reason = 'Dentist professional record is missing.';
        return false;
    }
    if (($profile['profile_status'] ?? 'active') !== 'active') {
        $reason = 'Dentist professional record is not active.';
        return false;
    }
    if (dentist_credential_schema_ready() && !dentist_credentials_verified($dentistUserId, $reason)) {
        return false;
    }
    $expiry = trim((string) ($profile['license_expiry'] ?? ''));
    if ($expiry !== '' && $expiry < date('Y-m-d')) {
        $reason = 'Professional license is expired.';
        return false;
    }
    try {
        $stmt = db()->prepare(
            'SELECT COUNT(*) FROM `' . table('dentist_clinical_privileges') . '` dcp '
            . 'JOIN `' . table('clinical_privileges') . '` cp ON cp.id=dcp.privilege_id '
            . 'WHERE dcp.dentist_user_id=? AND cp.code=? AND cp.is_active=1 AND dcp.status="granted" '
            . 'AND (dcp.effective_from IS NULL OR dcp.effective_from<=CURDATE()) '
            . 'AND (dcp.expires_at IS NULL OR dcp.expires_at>=CURDATE())'
        );
        $stmt->execute([$dentistUserId,$code]);
        if ((int) $stmt->fetchColumn() > 0) return true;
    } catch (Throwable) {
    }
    $reason = 'Clinical privilege "' . $code . '" is not currently granted.';
    return false;
}

function require_dentist_clinical_privilege(int $dentistUserId, string $code): void
{
    $reason = null;
    if (!dentist_has_clinical_privilege($dentistUserId,$code,$reason)) {
        throw new RuntimeException($reason ?: 'Required clinical privilege is unavailable.');
    }
}

function clinical_task_assignment_schema_ready(): bool
{
    return schema_table_exists(table('clinical_task_assignments'))
        && schema_column_exists(table('clinical_task_assignments'),'required_privilege_code')
        && schema_column_exists(table('clinical_task_assignments'),'assigned_dentist_user_id');
}

function clinical_privilege_code_for_text(string $text): ?string
{
    $value = strtolower(trim($text));
    if ($value === '') return null;
    $map = [
        'surgical_extraction'=>['surgical extraction','impacted','odontectomy'],
        'simple_extraction'=>['extraction','extract','tooth removal'],
        'root_canal'=>['root canal','endodont'],
        'orthodontics'=>['orthodont','braces','archwire'],
        'implant_placement'=>['implant'],
        'prosthodontics'=>['crown','bridge','denture','prosthodont'],
        'periodontal_surgery'=>['periodontal surgery','flap surgery','gingivectomy'],
        'oral_prophylaxis'=>['prophylaxis','oral cleaning','dental cleaning','scaling'],
        'restoration'=>['restoration','filling','composite','glass ionomer'],
        'pediatric_treatment'=>['pediatric','pulpotomy'],
        'sedation'=>['sedation'],
        'prescription'=>['prescription','medication'],
        'diagnosis'=>['diagnosis','diagnostic'],
        'consultation'=>['consultation','checkup','check-up','assessment','examination'],
    ];
    foreach ($map as $code=>$needles) foreach ($needles as $needle) if (str_contains($value,$needle)) return $code;
    return 'consultation';
}

function service_required_privilege_code(array $service): ?string
{
    if (service_provider_type($service) !== 'dentist') return null;
    $configured = trim((string)($service['required_privilege_code'] ?? ''));
    return $configured !== '' ? $configured : clinical_privilege_code_for_text((string)($service['name'] ?? ''));
}

function dentist_service_readiness(int $dentistUserId, array $service, ?string &$reason = null): bool
{
    if (service_provider_type($service) !== 'dentist') return true;
    if (!dentist_credentials_verified($dentistUserId,$reason)) return false;
    $code = service_required_privilege_code($service);
    if ($code !== null && !dentist_has_clinical_privilege($dentistUserId,$code,$reason)) return false;
    return true;
}

function qualified_dentists_for_privilege(int $branchId, ?string $privilegeCode, int $excludeDentistId = 0): array
{
    $result = [];
    foreach (active_dentists($branchId > 0 ? $branchId : null) as $doctor) {
        $id = (int)($doctor['id'] ?? 0);
        if ($id < 1 || ($excludeDentistId > 0 && $id === $excludeDentistId)) continue;
        $reason = null;
        if (!dentist_credentials_verified($id,$reason)) continue;
        if ($privilegeCode !== null && !dentist_has_clinical_privilege($id,$privilegeCode,$reason)) continue;
        $result[] = $doctor;
    }
    return $result;
}

function clinical_task_assignment_for_item(PDO $pdo, int $itemId, ?int $dentistUserId = null): ?array
{
    if (!clinical_task_assignment_schema_ready() || $itemId < 1) return null;
    $sql = 'SELECT * FROM `' . table('clinical_task_assignments') . '` WHERE treatment_plan_item_id=? AND status IN ("pending","accepted","in_progress")';
    $args = [$itemId];
    if ($dentistUserId !== null) { $sql .= ' AND assigned_dentist_user_id=?'; $args[] = $dentistUserId; }
    $sql .= ' ORDER BY id DESC LIMIT 1';
    $stmt = $pdo->prepare($sql);$stmt->execute($args);return $stmt->fetch() ?: null;
}

function dentist_can_work_treatment_item(PDO $pdo, int $dentistUserId, array $item, ?string &$reason = null): bool
{
    $ownerId = (int)($item['plan_dentist_user_id'] ?? $item['dentist_user_id'] ?? 0);
    $required = clinical_privilege_code_for_text(trim((string)($item['procedure_code'] ?? '') . ' ' . (string)($item['procedure_name'] ?? '')));
    if ($ownerId === $dentistUserId) {
        if ($required !== null && !dentist_has_clinical_privilege($dentistUserId,$required,$reason)) return false;
        return true;
    }
    $task = clinical_task_assignment_for_item($pdo,(int)($item['id'] ?? 0),$dentistUserId);
    if (!$task || !in_array((string)$task['status'],['accepted','in_progress'],true)) {
        $reason = 'This treatment item is assigned to another Dentist and no accepted task assignment exists.';
        return false;
    }
    if ($required !== null && !dentist_has_clinical_privilege($dentistUserId,$required,$reason)) return false;
    return true;
}

function can_manage_dentist_profile(int $dentistUserId): bool
{
    if (has_role(['super_admin','clinic_admin'])) return true;
    return has_role('dentist') && (int) (current_user()['id'] ?? 0) === $dentistUserId;
}

function require_manage_dentist_profile(int $dentistUserId): void
{
    if (!can_manage_dentist_profile($dentistUserId)) {
        http_response_code(403);
        exit('Access denied.');
    }
}

function private_dentist_directory(int $dentistUserId): string
{
    $path = DENTAL_ROOT . '/storage/private/dentists/' . $dentistUserId;
    if (!is_dir($path) && !mkdir($path, 0770, true) && !is_dir($path)) {
        throw new RuntimeException('Unable to create the protected dentist document directory.');
    }
    return $path;
}

function branch_options(): array
{
    return db()->query('SELECT id,name,code,timezone FROM `' . table('branches') . '` WHERE is_active=1 ORDER BY name')->fetchAll();
}

function person_name_from_parts(string $firstName, ?string $middleName, string $lastName): string
{
    return trim(implode(' ', array_filter([
        trim($firstName),
        trim((string)$middleName),
        trim($lastName),
    ], static fn(string $part): bool => $part !== '')));
}

function user_full_name(array $user): string
{
    $structured = person_name_from_parts(
        (string)($user['first_name'] ?? ''),
        isset($user['middle_name']) ? (string)$user['middle_name'] : null,
        (string)($user['last_name'] ?? '')
    );
    return $structured !== '' ? $structured : trim((string)($user['name'] ?? ''));
}

function patient_full_name(array $patient): string
{
    return person_name_from_parts((string)($patient['first_name'] ?? ''), (string)($patient['middle_name'] ?? ''), (string)($patient['last_name'] ?? ''));
}

function find_patient(int $patientId): ?array
{
    $patients = table('patients');
    $where = schema_column_exists($patients, 'deleted_at') ? ' AND deleted_at IS NULL' : '';
    $stmt = db()->prepare('SELECT * FROM `' . $patients . '` WHERE id=?' . $where . ' LIMIT 1');
    $stmt->execute([$patientId]);
    return $stmt->fetch() ?: null;
}

function require_patient(int $patientId): array
{
    $patient = find_patient($patientId);
    if (!$patient) {
        http_response_code(404);
        exit('Patient record not found.');
    }
    if (!task_patient_scope_allowed($patientId)) {
        access_denied('This patient is outside the active task workspace.');
    }
    return $patient;
}

function patient_portal_user(): ?array
{
    if (!isset($_SESSION['patient_user']) || !is_array($_SESSION['patient_user'])) return null;
    $timeoutMinutes = max(5, min(120, (int) setting_get('security', 'patient_session_timeout_minutes', '20')));
    $lastActivity = (int) ($_SESSION['patient_last_activity'] ?? 0);
    if ($lastActivity > 0 && time() - $lastActivity > ($timeoutMinutes * 60)) {
        unset($_SESSION['patient_user'], $_SESSION['portal_patient_id'], $_SESSION['patient_last_activity']);
        session_regenerate_id(true);
        return null;
    }
    $_SESSION['patient_last_activity'] = time();
    return $_SESSION['patient_user'];
}

function patient_portal_account_patient_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) return $ready;
    return $ready = schema_table_exists(table('patient_portal_account_patients'));
}

function patient_portal_account_patients(int $accountId): array
{
    if ($accountId < 1) return [];
    if (!patient_portal_account_patient_schema_ready()) {
        $stmt = db()->prepare('SELECT a.patient_id,p.patient_no,p.first_name,p.middle_name,p.last_name,1 is_self,1 can_view_records,1 can_manage_appointments,1 can_submit_health FROM `' . table('patient_portal_accounts') . '` a JOIN `' . table('patients') . '` p ON p.id=a.patient_id WHERE a.id=? LIMIT 1');
        $stmt->execute([$accountId]);
        $row = $stmt->fetch();
        return $row ? [$row] : [];
    }
    $stmt = db()->prepare(
        'SELECT m.*,p.patient_no,p.first_name,p.middle_name,p.last_name,p.status patient_status,g.name guardian_name,g.relationship, '
        . 'CASE WHEN m.is_self=1 THEN m.can_view_records ELSE (m.can_view_records=1 AND g.verification_status="verified" AND g.authorized_to_receive_records=1) END effective_can_view_records, '
        . 'CASE WHEN m.is_self=1 THEN m.can_manage_appointments ELSE (m.can_manage_appointments=1 AND g.verification_status="verified" AND g.authorized_to_receive_records=1 AND g.authorized_to_consent=1) END effective_can_manage_appointments, '
        . 'CASE WHEN m.is_self=1 THEN m.can_submit_health ELSE (m.can_submit_health=1 AND g.verification_status="verified" AND g.authorized_to_receive_records=1 AND g.authorized_to_consent=1) END effective_can_submit_health '
        . 'FROM `' . table('patient_portal_account_patients') . '` m '
        . 'JOIN `' . table('patients') . '` p ON p.id=m.patient_id '
        . 'LEFT JOIN `' . table('patient_guardians') . '` g ON g.id=m.guardian_id '
        . 'WHERE m.account_id=? AND m.status="active" '
        . 'AND (m.is_self=1 OR (m.guardian_id IS NOT NULL AND g.verification_status="verified" AND g.authorized_to_receive_records=1)) '
        . 'ORDER BY m.is_self DESC,p.last_name,p.first_name'
    );
    $stmt->execute([$accountId]);
    $rows = $stmt->fetchAll();
    foreach ($rows as &$row) {
        if (array_key_exists('effective_can_view_records', $row)) $row['can_view_records'] = (int) $row['effective_can_view_records'];
        if (array_key_exists('effective_can_manage_appointments', $row)) $row['can_manage_appointments'] = (int) $row['effective_can_manage_appointments'];
        if (array_key_exists('effective_can_submit_health', $row)) $row['can_submit_health'] = (int) $row['effective_can_submit_health'];
    }
    unset($row);
    return $rows;
}

function patient_portal_access_for_patient(int $accountId, int $patientId): ?array
{
    if ($accountId < 1 || $patientId < 1) return null;
    foreach (patient_portal_account_patients($accountId) as $row) {
        if ((int) ($row['patient_id'] ?? 0) === $patientId) return $row;
    }
    return null;
}

function patient_portal_request_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) return $ready;
    return $ready = schema_table_exists(table('patient_portal_requests'));
}

function appointment_provider_display(array $appointment): string
{
    if (($appointment['provider_type'] ?? 'dentist') === 'inhouse') {
        $name = trim((string) ($appointment['inhouse_provider_name'] ?? ''));
        return $name !== '' ? $name : 'In-house clinic team';
    }
    $name = trim((string) ($appointment['dentist_name'] ?? ''));
    return $name !== '' ? $name : 'Assigned dentist';
}

function patient_portal_request_type_label(string $type): string
{
    return match ($type) {
        'appointment_confirm' => 'Appointment confirmation',
        'appointment_cancel' => 'Appointment cancellation',
        'appointment_reschedule' => 'Reschedule request',
        'followup_booking' => 'Follow-up booking',
        'medical_history_update' => 'Dental safety information update',
        'record_copy' => 'Dental record copy request',
        default => ucwords(str_replace('_', ' ', $type)),
    };
}

function patient_portal_request_pending(int $patientId, string $type, ?int $appointmentId = null, ?int $followUpId = null): bool
{
    if (!patient_portal_request_schema_ready()) return false;
    $activeStatuses = $type === 'record_copy'
        ? '("pending_identity","pending_clinical","approved")'
        : '("pending")';
    $sql = 'SELECT COUNT(*) FROM `' . table('patient_portal_requests') . '` WHERE patient_id=? AND request_type=? AND status IN ' . $activeStatuses;
    $args = [$patientId, $type];
    if ($appointmentId !== null) { $sql .= ' AND appointment_id=?'; $args[] = $appointmentId; }
    if ($followUpId !== null) { $sql .= ' AND follow_up_id=?'; $args[] = $followUpId; }
    $stmt = db()->prepare($sql);
    $stmt->execute($args);
    return (int) $stmt->fetchColumn() > 0;
}



function appointment_calendar_token(array $appointment): string
{
    $key = (string) app_config('app.key', 'dental-calendar');
    return hash_hmac('sha256', 'appointment|' . (int)($appointment['id'] ?? 0) . '|' . (int)($appointment['patient_id'] ?? 0) . '|' . (string)($appointment['start_at'] ?? ''), $key);
}

function appointment_calendar_relative_url(array $appointment, bool $signed = false): string
{
    $params = ['type'=>'appointment','appointment_id'=>(int)($appointment['id'] ?? 0)];
    if ($signed) $params['token'] = appointment_calendar_token($appointment);
    return 'calendar.php?' . http_build_query($params);
}

function patient_continuity_schema_ready(): bool
{
    return schema_column_exists(table('patients'), 'primary_dentist_user_id');
}

function doctor_integrity_schema_ready(): bool
{
    static $ready;
    if ($ready !== null) return $ready;
    try {
        $requiredColumns = [
            [table('service_catalog'),'provider_type'],
            [table('service_catalog'),'slot_capacity'],
            [table('appointments'),'service_id'],
            [table('appointments'),'provider_type'],
            [table('queue_entries'),'service_id'],
            [table('queue_entries'),'provider_type'],
            [table('clinical_encounters'),'signed_by_name'],
            [table('clinical_encounters'),'finalized_hash'],
        ];
        foreach ($requiredColumns as [$tableName,$columnName]) {
            if (!schema_column_exists($tableName,$columnName)) return $ready = false;
        }
        if (!schema_table_exists(table('appointment_reminders'))) return $ready = false;
        $stmt=db()->prepare('SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=? AND table_name=? AND index_name="uq_clinical_queue_entry"');
        $stmt->execute([(string)app_config('database.name'),table('clinical_encounters')]);
        return $ready=((int)$stmt->fetchColumn() > 0);
    } catch (Throwable) {
        return $ready=false;
    }
}

function patient_primary_dentist(int $patientId): ?array
{
    if ($patientId < 1) return null;
    try {
        if (patient_continuity_schema_ready()) {
            $stmt = db()->prepare('SELECT u.id,u.name,p.primary_dentist_assigned_at,p.primary_dentist_change_reason FROM `' . table('patients') . '` p JOIN `' . table('users') . '` u ON u.id=p.primary_dentist_user_id WHERE p.id=? AND u.status="active" LIMIT 1');
            $stmt->execute([$patientId]);
            $row = $stmt->fetch();
            if ($row) return $row;
        }
        $stmt = db()->prepare('SELECT u.id,u.name,NULL primary_dentist_assigned_at,NULL primary_dentist_change_reason FROM `' . table('appointments') . '` a JOIN `' . table('users') . '` u ON u.id=a.dentist_user_id WHERE a.patient_id=? AND a.dentist_user_id IS NOT NULL AND a.status NOT IN ("cancelled","no_show") AND u.status="active" ORDER BY CASE WHEN a.status="completed" THEN 0 ELSE 1 END,a.start_at DESC,a.id DESC LIMIT 1');
        $stmt->execute([$patientId]);
        return $stmt->fetch() ?: null;
    } catch (Throwable) {
        return null;
    }
}

function patient_dentist_transfer_allowed(): bool
{
    return has_role(['super_admin','clinic_admin','receptionist','staff']) && task_context_action_allowed('appointments.manage');
}

function validate_patient_dentist_continuity(int $patientId, int $doctorId, bool $transferRequested = false, string $reason = ''): ?array
{
    $primary = patient_primary_dentist($patientId);
    if (!$primary || (int)$primary['id'] === $doctorId) return $primary;
    if (!$transferRequested || !patient_dentist_transfer_allowed()) {
        throw new RuntimeException('Continuity of care requires this appointment to remain with ' . (string)$primary['name'] . '. An authorized care transfer with a reason is required to change dentists.');
    }
    if (trim($reason) === '') throw new RuntimeException('Enter the reason for changing the patient’s continuity dentist.');
    return $primary;
}

function commit_patient_dentist_continuity(int $patientId, int $doctorId, bool $transferRequested = false, string $reason = ''): void
{
    if ($patientId < 1 || $doctorId < 1 || !patient_continuity_schema_ready()) return;
    $primary = patient_primary_dentist($patientId);
    if ($primary && (int)$primary['id'] === $doctorId) return;
    if ($primary && (!$transferRequested || !patient_dentist_transfer_allowed())) return;
    $changeReason = $primary ? trim($reason) : 'Assigned from the patient’s first confirmed appointment';
    db()->prepare('UPDATE `' . table('patients') . '` SET primary_dentist_user_id=?,primary_dentist_assigned_at=NOW(),primary_dentist_change_reason=?,primary_dentist_changed_by=?,updated_at=NOW() WHERE id=?')
        ->execute([$doctorId,$changeReason,current_user()['id'] ?? null,$patientId]);
    audit($primary ? 'patient_continuity_dentist_changed' : 'patient_continuity_dentist_assigned','patient',$patientId,['dentist_user_id'=>$doctorId,'reason'=>$changeReason],$primary ? ['dentist_user_id'=>(int)$primary['id']] : []);
}

function private_document_directory(): string
{
    $path = DENTAL_ROOT . '/storage/private/documents';
    if (!is_dir($path)) mkdir($path, 0775, true);
    return $path;
}

function private_system_upload_directory(): string
{
    $path = DENTAL_ROOT . '/storage/private/system_uploads';
    if (!is_dir($path)) mkdir($path, 0775, true);
    return $path;
}

function backup_directory(): string
{
    $path = DENTAL_ROOT . '/storage/backups';
    if (!is_dir($path)) mkdir($path, 0775, true);
    return $path;
}

function app_absolute_url(string $path = ''): string
{
    $base = rtrim((string) app_config('app.url', ''), '/');
    if ($base === '') {
        $https = !empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off';
        $scheme = $https ? 'https' : 'http';
        $host = (string) ($_SERVER['HTTP_HOST'] ?? 'localhost');
        $dir = rtrim(str_replace('\\', '/', dirname((string) ($_SERVER['SCRIPT_NAME'] ?? '/'))), '/.');
        $base = $scheme . '://' . $host . ($dir !== '' ? $dir : '');
    }
    $path = dental_clean_url($path);
    return $base . '/' . ltrim($path, '/');
}

/**
 * Backward-compatible alias for modules created before app_absolute_url()
 * became the canonical helper. Keeping the alias prevents a single stale
 * template call from crashing a partially rendered clinical or finance page.
 */
if (!function_exists('app_url')) {
    function app_url(string $path = ''): string
    {
        return app_absolute_url($path);
    }
}


/**
 * Keep a cashier inside the active patient transaction without running
 * application helpers from route.php before this bootstrap has loaded.
 *
 * The v0.5.9.8.14 route-level implementation executed current_user(),
 * role_context_has(), table(), and schema helpers too early, causing a fatal
 * "undefined function" error on routed pages. This late bootstrap guard keeps
 * the transaction lock while failing open if the database is temporarily
 * unavailable, so navigation enforcement can never take down the application.
 */
function enforce_cashier_transaction_route_lock(): void
{
    if (PHP_SAPI === 'cli') return;

    $route = trim((string) ($GLOBALS['DENTAL_ROUTE'] ?? ''));
    if ($route === '' || in_array($route, ['billing','help_support'], true)) return;

    $user = current_user();
    if (!$user || !role_context_has('cashier')) return;

    try {
        $sessionTable = table('cashier_sessions');
        if (!schema_table_exists($sessionTable)
            || !schema_column_exists($sessionTable, 'locked_patient_id')) {
            return;
        }

        $userId = (int) ($user['id'] ?? 0);
        $branchId = (int) ($user['branch_id'] ?? 0);
        if ($userId < 1) return;

        $sql = 'SELECT locked_patient_id FROM `' . $sessionTable . '` '
            . 'WHERE cashier_user_id=? AND status="open"';
        $args = [$userId];
        if ($branchId > 0) {
            $sql .= ' AND branch_id=?';
            $args[] = $branchId;
        }
        $sql .= ' ORDER BY id DESC LIMIT 1';

        $stmt = db()->prepare($sql);
        $stmt->execute($args);
        $lockedPatientId = (int) ($stmt->fetchColumn() ?: 0);
        if ($lockedPatientId < 1) return;

        flash('warning', 'Complete or cancel the active cashier transaction before leaving the Cashier Register.');
        header('Location: billing.php?transaction=locked');
        exit;
    } catch (Throwable $exception) {
        // A navigation guard must never make the whole application unavailable.
        error_log('Cashier transaction route-lock check skipped: ' . $exception->getMessage());
    }
}

require_once DENTAL_ROOT . '/app/security_hardening.php';
require_once DENTAL_ROOT . '/app/access_control.php';
require_once DENTAL_ROOT . '/app/privacy_governance.php';
require_once DENTAL_ROOT . '/app/dental_safety_intake.php';

enforce_cashier_transaction_route_lock();


if (!function_exists('dental_patient_numbering_settings')) {
    function dental_patient_numbering_settings(): array
    {
        $mode = (string) setting_get('patient_numbering', 'mode', 'auto');
        if (!in_array($mode, ['auto','auto_allow_override','manual'], true)) $mode = 'auto';
        $prefix = trim((string) setting_get('patient_numbering', 'prefix', 'P'));
        if ($prefix === '') $prefix = 'P';
        $yearFormat = (string) setting_get('patient_numbering', 'year_format', 'Y');
        if (!in_array($yearFormat, ['none','Y','y','ym','ymd'], true)) $yearFormat = 'Y';
        $separator = (string) setting_get('patient_numbering', 'separator', '');
        if (mb_strlen($separator) > 3) $separator = mb_substr($separator, 0, 3);
        $padding = (int) setting_get('patient_numbering', 'padding', '4');
        $padding = max(1, min(8, $padding));
        $nextNumber = (int) setting_get('patient_numbering', 'next_number', '1');
        $nextNumber = max(1, $nextNumber);
        return [
            'mode' => $mode,
            'prefix' => $prefix,
            'year_format' => $yearFormat,
            'separator' => $separator,
            'padding' => $padding,
            'next_number' => $nextNumber,
        ];
    }
}

if (!function_exists('dental_patient_number_year_segment')) {
    function dental_patient_number_year_segment(array $settings): string
    {
        return match ((string)($settings['year_format'] ?? 'Y')) {
            'Y' => date('Y'),
            'y' => date('y'),
            'ym' => date('ym'),
            'ymd' => date('ymd'),
            default => date('Y'),
        };
    }
}

if (!function_exists('dental_patient_number_preview')) {
    function dental_patient_number_preview(?array $settings = null, ?int $number = null): string
    {
        $settings ??= dental_patient_numbering_settings();
        $number ??= (int)($settings['next_number'] ?? 1);
        $year = dental_patient_number_year_segment($settings);
        $parts = [(string)$settings['prefix']];
        if ($year !== '') $parts[] = $year;
        $parts[] = str_pad((string)max(1, $number), (int)$settings['padding'], '0', STR_PAD_LEFT);
        $separator = (string)($settings['separator'] ?? '');
        return $separator === '' ? implode('', $parts) : implode($separator, $parts);
    }
}

if (!function_exists('dental_next_patient_number')) {
    function dental_next_patient_number(PDO $pdo, int $branchId = 0, ?array $settings = null): string
    {
        $settings ??= dental_patient_numbering_settings();
        $patientsTable = table('patients');
        for ($attempt = 0; $attempt < 100; $attempt++) {
            $next = max(1, (int) setting_get('patient_numbering', 'next_number', (string)($settings['next_number'] ?? 1)));
            $candidate = dental_patient_number_preview($settings, $next);
            $where = 'patient_no=?';
            $args = [$candidate];
            if (schema_column_exists($patientsTable, 'deleted_at')) $where .= ' AND deleted_at IS NULL';
            $stmt = $pdo->prepare('SELECT COUNT(*) FROM `' . $patientsTable . '` WHERE ' . $where);
            $stmt->execute($args);
            setting_set('patient_numbering', 'next_number', (string)($next + 1));
            if ((int)$stmt->fetchColumn() === 0) {
                return $candidate;
            }
        }
        return 'P' . date('Y') . strtoupper(bin2hex(random_bytes(3))); 
    }
}



if (!function_exists('dental_patient_numbering_next_counter')) {
    function dental_patient_numbering_next_counter(PDO $pdo, ?array $settings = null): int
    {
        $settings ??= dental_patient_numbering_settings();
        $prefix = (string)($settings['prefix'] ?? 'P');
        $year = dental_patient_number_year_segment($settings);
        $separator = (string)($settings['separator'] ?? '');
        $padding = max(1, min(8, (int)($settings['padding'] ?? 4)));
        $literal = preg_quote($prefix . ($year !== '' ? ($separator === '' ? $year : $separator . $year) : '') . ($separator !== '' ? $separator : ''), '/');
        $regex = '/^' . $literal . '(\d{' . $padding . ',})$/';
        $like = $prefix . ($year !== '' ? ($separator === '' ? $year : $separator . $year) : '') . ($separator !== '' ? $separator : '') . '%';
        $max = 0;
        $patientsTable = table('patients');
        if (!schema_table_exists($patientsTable)) return 1;
        try {
            $where = 'patient_no LIKE ?';
            if (schema_column_exists($patientsTable, 'deleted_at')) $where .= ' AND deleted_at IS NULL';
            $stmt = $pdo->prepare('SELECT patient_no FROM `' . $patientsTable . '` WHERE ' . $where);
            $stmt->execute([$like]);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $patientNo = (string)($row['patient_no'] ?? '');
                if (preg_match($regex, $patientNo, $match)) {
                    $max = max($max, (int)$match[1]);
                }
            }
        } catch (Throwable) {
            return max(1, (int)($settings['next_number'] ?? 1));
        }
        return max(1, $max + 1);
    }
}

if (!function_exists('dental_patient_number_looks_legacy_random')) {
    function dental_patient_number_looks_legacy_random(string $patientNo): bool
    {
        $patientNo = trim($patientNo);
        if (!preg_match('/^P(\d{2})(\d{2})(\d{2})(\d{4})$/', $patientNo, $m)) return false;
        $yy = (int)$m[1]; $month = (int)$m[2]; $day = (int)$m[3];
        return $yy >= 20 && $yy <= 99 && checkdate($month, $day, 2000 + $yy);
    }
}

if (!function_exists('dental_patient_number_validate_unique')) {
    function dental_patient_number_validate_unique(PDO $pdo, string $patientNo, int $excludePatientId = 0): void
    {
        $patientNo = trim($patientNo);
        if ($patientNo === '') throw new RuntimeException('Patient number is required.');
        if (mb_strlen($patientNo) > 40) throw new RuntimeException('Patient number is too long.');
        if (!preg_match('/^[A-Za-z0-9][A-Za-z0-9._\-\/]{1,39}$/', $patientNo)) {
            throw new RuntimeException('Use only letters, numbers, dash, slash, underscore, or dot for the patient number.');
        }
        $patientsTable = table('patients');
        $where = 'patient_no=?';
        $args = [$patientNo];
        if ($excludePatientId > 0) { $where .= ' AND id<>?'; $args[] = $excludePatientId; }
        if (schema_column_exists($patientsTable, 'deleted_at')) $where .= ' AND deleted_at IS NULL';
        $stmt = $pdo->prepare('SELECT id FROM `' . $patientsTable . '` WHERE ' . $where . ' LIMIT 1');
        $stmt->execute($args);
        $existing = (int)($stmt->fetchColumn() ?: 0);
        if ($existing > 0) throw new RuntimeException('Patient number already exists. Use a different number or open the existing patient record.');
    }
}

if (!function_exists('user_display_name_by_id')) {
    function user_display_name_by_id(int $userId): string
    {
        if ($userId < 1) return '';
        try {
            $stmt = db()->prepare('SELECT name,first_name,middle_name,last_name,email FROM `' . table('users') . '` WHERE id=? LIMIT 1');
            $stmt->execute([$userId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
            if (!$row) return '';
            if (function_exists('user_full_name')) {
                $name = trim((string)user_full_name($row));
                if ($name !== '') return $name;
            }
            $parts = array_filter([(string)($row['first_name'] ?? ''),(string)($row['middle_name'] ?? ''),(string)($row['last_name'] ?? '')], static fn(string $v): bool => trim($v) !== '');
            $name = trim(implode(' ', $parts));
            return $name !== '' ? $name : (string)($row['name'] ?? $row['email'] ?? '');
        } catch (Throwable) { return ''; }
    }
}


// v1.0.0.0 production data-integrity contracts and evidence helpers.
if (is_file(__DIR__ . '/data_integrity.php')) { require_once __DIR__ . '/data_integrity.php'; }

// Chair-resource helpers are loaded after bootstrap functions so appointment
// slot validation can resolve future chair sessions outside API-only flows.
if (is_file(__DIR__ . '/chair_resource.php')) { require_once __DIR__ . '/chair_resource.php'; }

// SMS readiness helpers are optional, defensive, and safe in log-only mode.
if (is_file(__DIR__ . '/sms_service.php')) { require_once __DIR__ . '/sms_service.php'; }

// Major 0.6.0 workflow stabilization helpers.
if (is_file(__DIR__ . '/major_stabilization.php')) { require_once __DIR__ . '/major_stabilization.php'; }

// Major 0.6.1 role-flow QA and release-readiness helpers.
if (is_file(__DIR__ . '/role_flow_qa.php')) { require_once __DIR__ . '/role_flow_qa.php'; }
if (is_file(__DIR__ . '/workflow_enforcement.php')) { require_once __DIR__ . '/workflow_enforcement.php'; }

// v0.6.7 clean-route access guard. The guard runs here, not in route.php,
// because individual modules include app/bootstrap.php themselves.
if (PHP_SAPI !== 'cli'
    && !defined('DENTAL_ROUTE_ACCESS_GUARD_V067_RAN')
    && !empty($GLOBALS['DENTAL_ROUTE'])
    && function_exists('enforce_task_route_access')) {
    define('DENTAL_ROUTE_ACCESS_GUARD_V067_RAN', true);
    enforce_task_route_access((string)$GLOBALS['DENTAL_ROUTE']);
}
