<?php
$host = "localhost";
$user = "mainppg1_sistem_fail_mainpp";
$pass = "Ud;_T;ArC;I0";
$db = "mainppg1_sistem_fail_mainpp";


$conn = mysqli_connect($host, $user, $pass, $db);

if (!$conn) {
    die("Sambungan gagal: " . mysqli_connect_error());
}

mysqli_set_charset($conn, "utf8mb4");
ensureCoreSchemaCompatibility($conn);
ensureRecycleBinSchema($conn);
ensureCustomDataSchema($conn);
ensureDocumentTypeSchema($conn);
ensureFolderTypeSchema($conn);
ensureDocumentAttachmentsSchema($conn);
ensurePermissionSchema($conn);
ensureDepartmentPermissionSchema($conn);
ensureUserPermissionSchema($conn);
ensureLetterTemplateSchema($conn);
ensureLetterDraftSchema($conn);
ensureNotificationReadSchema($conn);
ensurePushNotificationSchema($conn);
ensurePasswordResetSchema($conn);
ensureCustomFieldsSchema($conn);
ensureDeleteAuditSchema($conn);
ensureUATSchema($conn);
ensureStaffDistributionListSchema($conn);
cleanupLegacySchema($conn);
normalizeLegacyData($conn);
syncApplicationData($conn);

function ensureCoreSchemaCompatibility($conn) {
    ensureTableColumnDefinition($conn, 'users', 'department', "VARCHAR(255) NOT NULL");
    ensureGenericTableColumn($conn, 'users', 'short_name', "VARCHAR(100) DEFAULT NULL AFTER `fullname`");
    ensureGenericTableColumn($conn, 'users', 'position', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'users', 'signature_path', "VARCHAR(255) DEFAULT NULL");
    ensureTableColumnDefinition($conn, 'users', 'email', "VARCHAR(191) NOT NULL");
    ensureGenericTableColumn($conn, 'users', 'registered_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP");
    ensureTableColumnDefinition($conn, 'folders', 'department', "VARCHAR(255) NOT NULL");
    ensureGenericTableColumn($conn, 'folders', 'file_number_start', "INT(11) NOT NULL DEFAULT 1 AFTER `folder_type`");
    ensureTableColumnDefinition($conn, 'documents', 'file_path', "VARCHAR(255) NULL DEFAULT NULL");
    ensureTableColumnDefinition($conn, 'documents', 'file_numbering', "VARCHAR(100) NOT NULL DEFAULT ''");
    ensureGenericTableColumn($conn, 'users', 'last_login', "TIMESTAMP NULL DEFAULT NULL");
}

function ensureRecycleBinSchema($conn) {
    ensureTableColumn($conn, 'folders', 'deleted_at', "DATETIME NULL DEFAULT NULL");
    ensureTableColumn($conn, 'folders', 'deleted_by', "INT NULL DEFAULT NULL");
    ensureTableColumn($conn, 'folders', 'closed_at', "DATETIME NULL DEFAULT NULL");
    ensureTableColumn($conn, 'folders', 'closed_by', "INT NULL DEFAULT NULL");
    ensureTableColumn($conn, 'documents', 'deleted_at', "DATETIME NULL DEFAULT NULL");
    ensureTableColumn($conn, 'documents', 'deleted_by', "INT NULL DEFAULT NULL");
}

function getUserSignaturePath($conn, $user_id) {
    $user_id = (int) $user_id;
    if ($user_id <= 0) {
        return '';
    }

    $stmt = mysqli_prepare($conn, "SELECT signature_path FROM users WHERE id = ? AND status = 'approved' LIMIT 1");
    if (!$stmt) {
        return '';
    }

    mysqli_stmt_bind_param($stmt, "i", $user_id);
    mysqli_stmt_execute($stmt);
    $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));
    $signature_path = trim((string) ($row['signature_path'] ?? ''));

    return ($signature_path !== '' && file_exists(__DIR__ . '/' . $signature_path)) ? $signature_path : '';
}

function ensureCustomDataSchema($conn) {
    ensureTableColumn($conn, 'folders', 'custom_data', "LONGTEXT NULL DEFAULT NULL");
    ensureTableColumn($conn, 'documents', 'custom_data', "LONGTEXT NULL DEFAULT NULL");
}

function ensureTableColumn($conn, $table, $column, $definition) {
    $allowed_tables = ['folders', 'documents'];
    if (!in_array($table, $allowed_tables, true)) {
        return false;
    }

    $check_sql = "SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
        LIMIT 1";
    $check_stmt = mysqli_prepare($conn, $check_sql);
    if (!$check_stmt) {
        return false;
    }

    mysqli_stmt_bind_param($check_stmt, "ss", $table, $column);
    mysqli_stmt_execute($check_stmt);
    $exists = mysqli_num_rows(mysqli_stmt_get_result($check_stmt)) > 0;

    if ($exists) {
        return true;
    }

    $table_safe = '`' . $table . '`';
    $column_safe = '`' . $column . '`';
    $alter_sql = "ALTER TABLE {$table_safe} ADD COLUMN {$column_safe} {$definition}";
    return mysqli_query($conn, $alter_sql) === true;
}

function ensureTableColumnDefinition($conn, $table, $column, $definition) {
    $check_sql = "SELECT DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
        LIMIT 1";
    $check_stmt = mysqli_prepare($conn, $check_sql);
    if (!$check_stmt) {
        return false;
    }

    mysqli_stmt_bind_param($check_stmt, "ss", $table, $column);
    mysqli_stmt_execute($check_stmt);
    $column_meta = mysqli_fetch_assoc(mysqli_stmt_get_result($check_stmt));
    $exists = $column_meta !== null;

    $table_safe = '`' . str_replace('`', '``', $table) . '`';
    $column_safe = '`' . str_replace('`', '``', $column) . '`';

    if (!$exists) {
        $alter_sql = "ALTER TABLE {$table_safe} ADD COLUMN {$column_safe} {$definition}";
        return mysqli_query($conn, $alter_sql) === true;
    }

    if (!columnDefinitionNeedsUpdate($column_meta, $definition)) {
        return true;
    }

    $alter_sql = "ALTER TABLE {$table_safe} MODIFY COLUMN {$column_safe} {$definition}";
    return mysqli_query($conn, $alter_sql) === true;
}

function columnDefinitionNeedsUpdate($column_meta, $definition) {
    $definition_normalized = strtolower(preg_replace('/\s+/', ' ', trim((string) $definition)));
    $current_type = strtolower(trim((string) ($column_meta['COLUMN_TYPE'] ?? '')));
    $current_data_type = strtolower(trim((string) ($column_meta['DATA_TYPE'] ?? '')));
    $current_nullable = strtoupper((string) ($column_meta['IS_NULLABLE'] ?? 'YES')) === 'YES';
    $current_default = $column_meta['COLUMN_DEFAULT'];
    $current_extra = strtolower(trim((string) ($column_meta['EXTRA'] ?? '')));

    if (preg_match('/^varchar\((\d+)\)/', $definition_normalized, $matches)) {
        if ($current_type !== 'varchar(' . $matches[1] . ')') {
            return true;
        }
    } elseif (preg_match('/^int\((\d+)\)/', $definition_normalized, $matches)) {
        if (strpos($current_type, 'int(' . $matches[1] . ')') !== 0) {
            return true;
        }
    } elseif (preg_match('/^enum\((.+?)\)/', $definition_normalized, $matches)) {
        $desired_enum = 'enum(' . $matches[1] . ')';
        if ($current_type !== $desired_enum) {
            return true;
        }
    } elseif (strpos($definition_normalized, 'timestamp') === 0) {
        if ($current_data_type !== 'timestamp') {
            return true;
        }
    } elseif (strpos($definition_normalized, 'datetime') === 0) {
        if ($current_data_type !== 'datetime') {
            return true;
        }
    }

    $desired_nullable = strpos($definition_normalized, 'not null') === false;
    if ($desired_nullable !== $current_nullable) {
        return true;
    }

    $has_default_clause = preg_match('/default\s+(.+?)(?:\s+on update current_timestamp)?$/i', $definition_normalized, $default_matches) === 1;
    if ($has_default_clause) {
        $desired_default_raw = trim((string) $default_matches[1]);
        if ($desired_default_raw === 'null') {
            if ($current_default !== null) {
                return true;
            }
        } elseif (strpos($desired_default_raw, 'current_timestamp') !== false) {
            $current_default_normalized = strtolower((string) $current_default);
            if (strpos($current_default_normalized, 'current_timestamp') === false) {
                return true;
            }
        } else {
            $desired_default_value = trim($desired_default_raw, "'");
            if ((string) $current_default !== $desired_default_value) {
                return true;
            }
        }
    } elseif ($current_default !== null) {
        return true;
    }

    $needs_on_update = strpos($definition_normalized, 'on update current_timestamp') !== false;
    $has_on_update = strpos($current_extra, 'on update current_timestamp') !== false;
    if ($needs_on_update !== $has_on_update) {
        return true;
    }

    return false;
}

function ensureDocumentTypeSchema($conn) {
    $sql = "SELECT COLUMN_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'documents'
        AND COLUMN_NAME = 'doc_type'
        LIMIT 1";
    $result = mysqli_query($conn, $sql);
    $row = $result ? mysqli_fetch_assoc($result) : null;

    if (!$row) {
        return false;
    }

    $expected_types = getDocumentTypeList();
    $current_enum = strtolower(trim((string) ($row['COLUMN_TYPE'] ?? '')));
    $expected_enum = "enum(" . implode(',', array_map(function ($type) use ($conn) {
        return "'" . strtolower(mysqli_real_escape_string($conn, $type)) . "'";
    }, $expected_types)) . ")";

    if ($current_enum === $expected_enum) {
        return true;
    }

    $enum_values = [];
    foreach ($expected_types as $type) {
        $enum_values[] = "'" . mysqli_real_escape_string($conn, $type) . "'";
    }

    $alter_sql = "ALTER TABLE `documents` MODIFY COLUMN `doc_type` ENUM(" . implode(',', $enum_values) . ") NULL DEFAULT NULL";
    return mysqli_query($conn, $alter_sql) === true;
}

function ensureFolderTypeSchema($conn) {
    $sql = "SELECT COLUMN_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'folders'
        AND COLUMN_NAME = 'folder_type'
        LIMIT 1";
    $result = mysqli_query($conn, $sql);
    $row = $result ? mysqli_fetch_assoc($result) : null;
    $expected_types = getFolderTypeList();

    $enum_values = [];
    foreach ($expected_types as $type) {
        $enum_values[] = "'" . mysqli_real_escape_string($conn, $type) . "'";
    }

    if (!$row) {
        $alter_sql = "ALTER TABLE `folders` ADD COLUMN `folder_type` ENUM(" . implode(',', $enum_values) . ") NULL DEFAULT NULL AFTER `department`";
        return mysqli_query($conn, $alter_sql) === true;
    }

    $current_enum = strtolower(trim((string) ($row['COLUMN_TYPE'] ?? '')));
    $missing_types = [];
    foreach ($expected_types as $type) {
        $quoted_type = "'" . strtolower($type) . "'";
        if (strpos($current_enum, $quoted_type) === false) {
            $missing_types[] = $type;
        }
    }

    if (empty($missing_types)) {
        return true;
    }

    $alter_sql = "ALTER TABLE `folders` MODIFY COLUMN `folder_type` ENUM(" . implode(',', $enum_values) . ") NULL DEFAULT NULL";
    return mysqli_query($conn, $alter_sql) === true;
}

function ensureDocumentAttachmentsSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `document_attachments` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `document_id` INT(11) NOT NULL,
        `file_path` VARCHAR(255) NOT NULL,
        `original_name` VARCHAR(255) DEFAULT NULL,
        `is_replacement_letter` TINYINT(1) NOT NULL DEFAULT 0,
        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_document_attachments_document_id` (`document_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureGenericTableColumn($conn, 'document_attachments', 'is_replacement_letter', "TINYINT(1) NOT NULL DEFAULT 0 AFTER `original_name`");
    ensureTableIndex($conn, 'document_attachments', 'idx_document_attachments_document_id', ['document_id']);
    return true;
}

function ensurePermissionSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `role_permissions` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `role_name` VARCHAR(50) NOT NULL,
        `module` VARCHAR(50) NOT NULL,
        `action` VARCHAR(50) NOT NULL,
        `is_allowed` TINYINT(1) NOT NULL DEFAULT 0,
        `updated_by` INT(11) DEFAULT NULL,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_role_module_action` (`role_name`, `module`, `action`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureGenericTableColumn($conn, 'role_permissions', 'updated_by', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'role_permissions', 'updated_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    ensureTableIndex($conn, 'role_permissions', 'uniq_role_module_action', ['role_name', 'module', 'action'], true);

    $seed = mysqli_prepare($conn, "INSERT INTO role_permissions (role_name, module, action, is_allowed, updated_by)
        VALUES (?, ?, ?, ?, NULL)");

    if (!$seed) {
        return false;
    }

    $existing_permissions = getRolePermissions($conn);

    foreach (getPermissionDefaultMap() as $role_name => $modules) {
        foreach ($modules as $module => $actions) {
            foreach ($actions as $action => $allowed) {
                if (isset($existing_permissions[$role_name][$module][$action])) {
                    continue;
                }
                $allowed_flag = $allowed ? 1 : 0;
                mysqli_stmt_bind_param($seed, "sssi", $role_name, $module, $action, $allowed_flag);
                mysqli_stmt_execute($seed);
            }
        }
    }

    migrateDeletedItemRestorePermission($conn, 'role_permissions');
    pruneObsoletePermissionRows($conn, 'role_permissions');
    return true;
}

function ensureDepartmentPermissionSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `department_permissions` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `department` VARCHAR(255) NOT NULL,
        `module` VARCHAR(50) NOT NULL,
        `action` VARCHAR(50) NOT NULL,
        `is_allowed` TINYINT(1) NOT NULL DEFAULT 0,
        `updated_by` INT(11) DEFAULT NULL,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_department_module_action` (`department`, `module`, `action`),
        KEY `idx_department_permissions_department` (`department`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureGenericTableColumn($conn, 'department_permissions', 'updated_by', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'department_permissions', 'updated_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    ensureTableIndex($conn, 'department_permissions', 'uniq_department_module_action', ['department', 'module', 'action'], true);
    ensureTableIndex($conn, 'department_permissions', 'idx_department_permissions_department', ['department']);

    seedDepartmentPermissions($conn);
    migrateDeletedItemRestorePermission($conn, 'department_permissions');
    pruneObsoletePermissionRows($conn, 'department_permissions');
    pruneObsoleteDepartmentPermissionRows($conn);
    return true;
}

function ensureUserPermissionSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `user_permissions` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `user_id` INT(11) NOT NULL,
        `module` VARCHAR(50) NOT NULL,
        `action` VARCHAR(50) NOT NULL,
        `is_allowed` TINYINT(1) NOT NULL DEFAULT 0,
        `updated_by` INT(11) DEFAULT NULL,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_user_module_action` (`user_id`, `module`, `action`),
        KEY `idx_user_permissions_user_id` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureGenericTableColumn($conn, 'user_permissions', 'updated_by', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'user_permissions', 'updated_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    ensureTableIndex($conn, 'user_permissions', 'uniq_user_module_action', ['user_id', 'module', 'action'], true);
    ensureTableIndex($conn, 'user_permissions', 'idx_user_permissions_user_id', ['user_id']);

    migrateDeletedItemRestorePermission($conn, 'user_permissions');
    pruneObsoletePermissionRows($conn, 'user_permissions');
    return true;
}

function seedDepartmentPermissions($conn) {
    $departments = getDepartmentList();
    if (empty($departments)) {
        return true;
    }

    $existing_permissions = getDepartmentPermissions($conn);
    $role_permissions = getRolePermissions($conn);
    $defaults = getPermissionDefaultMap();

    $seed = mysqli_prepare($conn, "INSERT INTO department_permissions (department, module, action, is_allowed, updated_by)
        VALUES (?, ?, ?, ?, NULL)");
    if (!$seed) {
        return false;
    }

    foreach ($departments as $department) {
        $department = normalizeDepartmentValue($department);
        if ($department === '') {
            continue;
        }

        $seed_role = getDepartmentSeedRole($conn, $department);
        foreach (getPermissionDefinition() as $module => $module_config) {
            foreach ($module_config['actions'] as $action => $action_label) {
                if (isset($existing_permissions[$department][$module][$action])) {
                    continue;
                }

                $allowed = $role_permissions[$seed_role][$module][$action]
                    ?? $defaults[$seed_role][$module][$action]
                    ?? 0;
                if (isBaselineRolePermission($seed_role, $module, $action)) {
                    $allowed = 1;
                }

                $allowed_flag = !empty($allowed) ? 1 : 0;
                mysqli_stmt_bind_param($seed, "sssi", $department, $module, $action, $allowed_flag);
                mysqli_stmt_execute($seed);
            }
        }
    }

    return true;
}

function pruneObsoletePermissionRows($conn, $table) {
    $allowed_tables = ['role_permissions', 'department_permissions', 'user_permissions'];
    if (!in_array($table, $allowed_tables, true)) {
        return false;
    }

    $valid_actions = [];
    foreach (getPermissionDefinition() as $module => $module_config) {
        foreach ($module_config['actions'] as $action => $label) {
            $valid_actions[strtolower((string) $module)][strtolower((string) $action)] = true;
        }
    }

    $result = mysqli_query($conn, "SELECT id, module, action FROM `{$table}`");
    if (!$result) {
        return false;
    }

    $delete_stmt = mysqli_prepare($conn, "DELETE FROM `{$table}` WHERE id = ?");
    if (!$delete_stmt) {
        return false;
    }

    while ($row = mysqli_fetch_assoc($result)) {
        $module = strtolower(trim((string) ($row['module'] ?? '')));
        $action = strtolower(trim((string) ($row['action'] ?? '')));
        if (isset($valid_actions[$module][$action])) {
            continue;
        }

        $id = (int) ($row['id'] ?? 0);
        if ($id > 0) {
            mysqli_stmt_bind_param($delete_stmt, "i", $id);
            mysqli_stmt_execute($delete_stmt);
        }
    }

    return true;
}

function pruneObsoleteDepartmentPermissionRows($conn) {
    $valid_departments = array_flip(array_map('normalizeDepartmentValue', getDepartmentList()));
    $result = mysqli_query($conn, "SELECT id, department FROM `department_permissions`");
    if (!$result) {
        return false;
    }

    $delete_stmt = mysqli_prepare($conn, "DELETE FROM `department_permissions` WHERE id = ?");
    if (!$delete_stmt) {
        return false;
    }

    while ($row = mysqli_fetch_assoc($result)) {
        $department = normalizeDepartmentValue($row['department'] ?? '');
        if ($department !== '' && isset($valid_departments[$department])) {
            continue;
        }

        $id = (int) ($row['id'] ?? 0);
        if ($id > 0) {
            mysqli_stmt_bind_param($delete_stmt, "i", $id);
            mysqli_stmt_execute($delete_stmt);
        }
    }

    mysqli_stmt_close($delete_stmt);
    mysqli_free_result($result);
    return true;
}

function migrateDeletedItemRestorePermission($conn, $table) {
    $queries = [
        'role_permissions' => "INSERT INTO role_permissions (role_name, module, action, is_allowed, updated_by)
            SELECT role_name, 'deleted_item', 'restore', is_allowed, updated_by
            FROM role_permissions
            WHERE module = 'deleted_item' AND action = 'update'
            ON DUPLICATE KEY UPDATE is_allowed = VALUES(is_allowed), updated_by = VALUES(updated_by), updated_at = CURRENT_TIMESTAMP",
        'department_permissions' => "INSERT INTO department_permissions (department, module, action, is_allowed, updated_by)
            SELECT department, 'deleted_item', 'restore', is_allowed, updated_by
            FROM department_permissions
            WHERE module = 'deleted_item' AND action = 'update'
            ON DUPLICATE KEY UPDATE is_allowed = VALUES(is_allowed), updated_by = VALUES(updated_by), updated_at = CURRENT_TIMESTAMP",
        'user_permissions' => "INSERT INTO user_permissions (user_id, module, action, is_allowed, updated_by)
            SELECT user_id, 'deleted_item', 'restore', is_allowed, updated_by
            FROM user_permissions
            WHERE module = 'deleted_item' AND action = 'update'
            ON DUPLICATE KEY UPDATE is_allowed = VALUES(is_allowed), updated_by = VALUES(updated_by), updated_at = CURRENT_TIMESTAMP",
    ];

    if (!isset($queries[$table])) {
        return false;
    }

    return (bool) mysqli_query($conn, $queries[$table]);
}

function getDepartmentSeedRole($conn, $department) {
    $department = normalizeDepartmentValue($department);
    if ($department === '') {
        return 'user';
    }

    $stmt = mysqli_prepare($conn, "SELECT role
        FROM users
        WHERE department = ? AND role IN ('admin', 'superadmin')
        ORDER BY FIELD(role, 'admin', 'superadmin')
        LIMIT 1");
    if (!$stmt) {
        return 'user';
    }

    mysqli_stmt_bind_param($stmt, "s", $department);
    mysqli_stmt_execute($stmt);
    $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));

    return ($row && ($row['role'] ?? '') === 'admin') ? 'admin' : 'user';
}

function ensureLetterDraftSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `letter_drafts` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `document_id` INT(11) NOT NULL,
        `folder_id` INT(11) NOT NULL,
        `department` VARCHAR(255) NOT NULL,
        `ref_no` VARCHAR(100) NOT NULL,
        `letter_date` DATE NOT NULL,
        `subject` VARCHAR(255) NOT NULL,
        `doc_type` VARCHAR(100) NOT NULL DEFAULT 'Memo Kelulusan',
        `recipient_name` VARCHAR(255) DEFAULT NULL,
        `recipient_organization` VARCHAR(255) DEFAULT NULL,
        `recipient_address` TEXT DEFAULT NULL,
        `attention_to` VARCHAR(255) DEFAULT NULL,
        `routing_positions` LONGTEXT DEFAULT NULL,
        `greeting` VARCHAR(255) DEFAULT NULL,
        `letter_body` LONGTEXT DEFAULT NULL,
        `letter_pages` LONGTEXT DEFAULT NULL,
        `approval_page_body` LONGTEXT DEFAULT NULL,
        `approval_page_subject` TEXT DEFAULT NULL,
        `approval_route_bodies` LONGTEXT DEFAULT NULL,
        `letter_page_signers` LONGTEXT DEFAULT NULL,
        `page_number_position` ENUM('none','bottom_center','bottom_right') NOT NULL DEFAULT 'none',
        `page_header_text` TEXT DEFAULT NULL,
        `page_footer_text` TEXT DEFAULT NULL,
        `closing_text` VARCHAR(255) DEFAULT NULL,
        `signatory_name` VARCHAR(255) DEFAULT NULL,
        `signatory_position` VARCHAR(255) DEFAULT NULL,
        `signatory_department` VARCHAR(255) DEFAULT NULL,
        `signatory_signature_path` VARCHAR(255) DEFAULT NULL,
        `status` ENUM('draf','siap') NOT NULL DEFAULT 'draf',
        `support_required` TINYINT(1) NOT NULL DEFAULT 0,
        `support_user_id` INT(11) DEFAULT NULL,
        `support_name` VARCHAR(255) DEFAULT NULL,
        `support_email` VARCHAR(191) DEFAULT NULL,
        `support_status` ENUM('tidak_perlu','menunggu','disokong','ditolak') NOT NULL DEFAULT 'tidak_perlu',
        `support_requested_at` DATETIME DEFAULT NULL,
        `support_responded_at` DATETIME DEFAULT NULL,
        `support_note` TEXT DEFAULT NULL,
        `submitted_at` DATETIME(6) DEFAULT NULL,
        `created_by` INT(11) DEFAULT NULL,
        `updated_by` INT(11) DEFAULT NULL,
        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `deleted_at` DATETIME DEFAULT NULL,
        `deleted_by` INT(11) DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_letter_drafts_document_id` (`document_id`),
        KEY `idx_letter_drafts_folder_id` (`folder_id`),
        KEY `idx_letter_drafts_department` (`department`),
        KEY `idx_letter_drafts_ref_no` (`ref_no`),
        KEY `idx_letter_drafts_deleted_at` (`deleted_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureTableColumnDefinition($conn, 'letter_drafts', 'department', "VARCHAR(255) NOT NULL");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'ref_no', "VARCHAR(100) NOT NULL");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'letter_date', "DATE NOT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'template_id', "INT(11) DEFAULT NULL AFTER `folder_id`");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'subject', "VARCHAR(255) NOT NULL");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'doc_type', "VARCHAR(100) NOT NULL DEFAULT 'Memo Kelulusan'");
    ensureGenericTableColumn($conn, 'letter_drafts', 'recipient_name', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'recipient_organization', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'recipient_address', "TEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'attention_to', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'routing_positions', "LONGTEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'greeting', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'letter_body', "LONGTEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'letter_pages', "LONGTEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'approval_page_body', "LONGTEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'approval_page_subject', "TEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'approval_route_bodies', "LONGTEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'letter_page_signers', "LONGTEXT DEFAULT NULL");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'page_number_position', "ENUM('none','bottom_center','bottom_right') NOT NULL DEFAULT 'none'");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'page_header_text', "TEXT DEFAULT NULL");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'page_footer_text', "TEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'closing_text', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'signatory_name', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'signatory_position', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'signatory_department', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'signatory_signature_path', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'preparer_signed_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'support_required', "TINYINT(1) NOT NULL DEFAULT 0");
    ensureGenericTableColumn($conn, 'letter_drafts', 'support_user_id', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'support_name', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'support_email', "VARCHAR(191) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'support_status', "ENUM('tidak_perlu','menunggu','disokong','ditolak') NOT NULL DEFAULT 'tidak_perlu'");
    ensureGenericTableColumn($conn, 'letter_drafts', 'support_requested_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'support_responded_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'support_note', "TEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'submitted_at', "DATETIME(6) DEFAULT NULL AFTER `support_note`");
    ensureGenericTableColumn($conn, 'letter_drafts', 'include_distribution_page', "TINYINT(1) NOT NULL DEFAULT 0 AFTER `submitted_at`");
    ensureGenericTableColumn($conn, 'letter_drafts', 'distribution_staff_list', "LONGTEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'distribution_page_content', "LONGTEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'approval_flow', "ENUM('single','two_level') NOT NULL DEFAULT 'single'");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'approval_status', "ENUM('draft','menunggu_pegawai_bahagian','perlu_pindaan_penyedia','sedang_dipinda_ketua_bahagian','menunggu_pelulus','menunggu_pelulus_akhir','lulus','ditolak') NOT NULL DEFAULT 'draft'");
    ensureGenericTableColumn($conn, 'letter_drafts', 'approval_stage', "TINYINT(1) NOT NULL DEFAULT 0");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_user_id', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_name', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_email', "VARCHAR(191) DEFAULT NULL");
    ensureTableColumnDefinition($conn, 'letter_drafts', 'department_officer_status', "ENUM('tidak_perlu','menunggu','menunggu_tandatangan','lulus','ditolak') NOT NULL DEFAULT 'tidak_perlu'");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_decision', "ENUM('belum_diputuskan','lulus','ditolak') NOT NULL DEFAULT 'belum_diputuskan'");
    mysqli_query($conn, "UPDATE letter_drafts
        SET department_officer_decision = CAST(department_officer_status AS CHAR)
        WHERE department_officer_decision = 'belum_diputuskan'
          AND department_officer_status IN ('lulus','ditolak')");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_signature_path', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_note', "TEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_approved_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_requested_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'department_officer_responded_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'memo_preparer_head_user_id', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'memo_preparer_head_name', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'memo_preparer_head_email', "VARCHAR(191) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'memo_preparer_head_status', "ENUM('tidak_perlu','menunggu','lulus','ditolak') NOT NULL DEFAULT 'tidak_perlu'");
    ensureGenericTableColumn($conn, 'letter_drafts', 'memo_preparer_head_note', "TEXT DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'memo_preparer_head_requested_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'memo_preparer_head_responded_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'created_by', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'updated_by', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'created_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP");
    ensureGenericTableColumn($conn, 'letter_drafts', 'updated_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    ensureGenericTableColumn($conn, 'letter_drafts', 'deleted_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'letter_drafts', 'deleted_by', "INT(11) DEFAULT NULL");
    ensureTableIndex($conn, 'letter_drafts', 'uniq_letter_drafts_document_id', ['document_id'], true);
    ensureTableIndex($conn, 'letter_drafts', 'idx_letter_drafts_folder_id', ['folder_id']);
    ensureTableIndex($conn, 'letter_drafts', 'idx_letter_drafts_department', ['department']);
    ensureTableIndex($conn, 'letter_drafts', 'idx_letter_drafts_ref_no', ['ref_no']);
    ensureTableIndex($conn, 'letter_drafts', 'idx_letter_drafts_submitted_at', ['submitted_at']);
    ensureTableIndex($conn, 'letter_drafts', 'idx_letter_drafts_deleted_at', ['deleted_at']);
    cleanupLegacyLetterDraftApprovalColumns($conn);

    $recipient_sql = "CREATE TABLE IF NOT EXISTS `letter_approval_recipients` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `draft_id` INT(11) NOT NULL,
        `user_id` INT(11) NOT NULL,
        `position_label` VARCHAR(255) NOT NULL,
        `source_type` ENUM('kepada','melalui','tambahan') NOT NULL DEFAULT 'tambahan',
        `route_order` INT(11) NOT NULL DEFAULT 1,
        `status` ENUM('belum_mula','menunggu','menunggu_tandatangan','lulus','ditolak') NOT NULL DEFAULT 'belum_mula',
        `decision` ENUM('belum_diputuskan','lulus','ditolak') NOT NULL DEFAULT 'belum_diputuskan',
        `signature_path` VARCHAR(255) DEFAULT NULL,
        `note` TEXT DEFAULT NULL,
        `requested_at` DATETIME DEFAULT NULL,
        `approved_at` DATETIME DEFAULT NULL,
        `responded_at` DATETIME DEFAULT NULL,
        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_letter_approval_draft_user` (`draft_id`, `user_id`),
        KEY `idx_letter_approval_draft` (`draft_id`),
        KEY `idx_letter_approval_user_status` (`user_id`, `status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";
    if (!mysqli_query($conn, $recipient_sql)) {
        return false;
    }
    ensureGenericTableColumn($conn, 'letter_approval_recipients', 'route_order', "INT(11) NOT NULL DEFAULT 1");
    ensureTableColumnDefinition($conn, 'letter_approval_recipients', 'status', "ENUM('belum_mula','menunggu','menunggu_tandatangan','lulus','ditolak') NOT NULL DEFAULT 'belum_mula'");
    ensureGenericTableColumn($conn, 'letter_approval_recipients', 'decision', "ENUM('belum_diputuskan','lulus','ditolak') NOT NULL DEFAULT 'belum_diputuskan'");
    mysqli_query($conn, "UPDATE letter_approval_recipients
        SET decision = CAST(status AS CHAR)
        WHERE decision = 'belum_diputuskan'
          AND status IN ('lulus','ditolak')");
    ensureGenericTableColumn($conn, 'letter_approval_recipients', 'approved_at', "DATETIME DEFAULT NULL");
    ensureTableIndex($conn, 'letter_approval_recipients', 'idx_letter_approval_draft_order', ['draft_id', 'route_order']);

    return true;
}

function ensureLetterTemplateSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `letter_templates` (
        `template_id` INT(11) NOT NULL AUTO_INCREMENT,
        `template_name` VARCHAR(150) NOT NULL,
        `template_content` LONGTEXT NOT NULL,
        `is_active` TINYINT(1) NOT NULL DEFAULT 1,
        `created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`template_id`),
        UNIQUE KEY `uniq_letter_templates_name` (`template_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureTableColumnDefinition($conn, 'letter_templates', 'template_name', "VARCHAR(150) NOT NULL");
    ensureGenericTableColumn($conn, 'letter_templates', 'template_content', "LONGTEXT NOT NULL");
    ensureGenericTableColumn($conn, 'letter_templates', 'is_active', "TINYINT(1) NOT NULL DEFAULT 1");
    ensureGenericTableColumn($conn, 'letter_templates', 'created_date', "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP");
    ensureGenericTableColumn($conn, 'letter_templates', 'updated_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    ensureTableIndex($conn, 'letter_templates', 'uniq_letter_templates_name', ['template_name'], true);
    seedDefaultLetterTemplates($conn);
    normalizeDefaultLetterTemplates($conn);

    return true;
}

function seedDefaultLetterTemplates($conn) {
    $template_name = 'Memo Kelulusan';
    $template_content = '<p>YBhg. Dato&rsquo;,</p><p><br></p><p><strong><u>{{subject}}</u></strong></p><p>Dengan hormatnya saya diarah merujuk perkara di atas.</p><p><br></p><p>2. &nbsp;&nbsp;&nbsp;&nbsp;{{letter_content}}</p>';
    $stmt = mysqli_prepare($conn, "INSERT INTO letter_templates (template_name, template_content, is_active)
        VALUES (?, ?, 1)
        ON DUPLICATE KEY UPDATE is_active = VALUES(is_active)");
    if (!$stmt) {
        return false;
    }
    mysqli_stmt_bind_param($stmt, "ss", $template_name, $template_content);
    return mysqli_stmt_execute($stmt);
}

function normalizeDefaultLetterTemplates($conn) {
    $template_name = 'Memo Kelulusan';
    $template_content = '<p>YBhg. Dato&rsquo;,</p><p><br></p><p><strong><u>{{subject}}</u></strong></p><p>Dengan hormatnya saya diarah merujuk perkara di atas.</p><p><br></p><p>2. &nbsp;&nbsp;&nbsp;&nbsp;{{letter_content}}</p>';
    $template_stmt = mysqli_prepare($conn, "UPDATE letter_templates SET template_content = ? WHERE template_name = ?");
    if ($template_stmt) {
        mysqli_stmt_bind_param($template_stmt, "ss", $template_content, $template_name);
        mysqli_stmt_execute($template_stmt);
    }

    $old_number_three = '<p>3. &nbsp;&nbsp;&nbsp;&nbsp;</p>';
    $stmt = mysqli_prepare($conn, "UPDATE letter_templates SET template_content = REPLACE(template_content, ?, '') WHERE template_content LIKE ?");
    if (!$stmt) {
        return false;
    }
    $like = '%' . $old_number_three . '%';
    mysqli_stmt_bind_param($stmt, "ss", $old_number_three, $like);
    return mysqli_stmt_execute($stmt);
}

function getLetterTemplates($conn, $active_only = true) {
    $sql = "SELECT template_id, template_name, template_content, is_active, created_date
        FROM letter_templates";
    if ($active_only) {
        $sql .= " WHERE is_active = 1";
    }
    $sql .= " ORDER BY template_name ASC";
    $result = mysqli_query($conn, $sql);
    if (!$result) {
        return [];
    }
    return mysqli_fetch_all($result, MYSQLI_ASSOC);
}

function getLetterTemplateById($conn, $template_id) {
    $template_id = (int) $template_id;
    if ($template_id <= 0) {
        return null;
    }
    $stmt = mysqli_prepare($conn, "SELECT template_id, template_name, template_content, is_active, created_date
        FROM letter_templates
        WHERE template_id = ? AND is_active = 1
        LIMIT 1");
    if (!$stmt) {
        return null;
    }
    mysqli_stmt_bind_param($stmt, "i", $template_id);
    mysqli_stmt_execute($stmt);
    $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));
    return $row ?: null;
}

function ensureStaffDistributionListSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `staff_distribution_list` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `agency_type` ENUM('MAINPP', 'AGENSI LUAR') NOT NULL,
        `name` VARCHAR(255) NOT NULL,
        `position` VARCHAR(255) NOT NULL,
        `department` VARCHAR(255) DEFAULT NULL,
        `agency` VARCHAR(255) DEFAULT NULL,
        `address` TEXT DEFAULT NULL,
        `source` ENUM('USER_LIST', 'MANUAL') DEFAULT 'MANUAL',
        `user_id` INT(11) DEFAULT NULL,
        `created_by` INT(11) DEFAULT NULL,
        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_by` INT(11) DEFAULT NULL,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `deleted_at` DATETIME DEFAULT NULL,
        `deleted_by` INT(11) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_agency_type` (`agency_type`),
        KEY `idx_deleted_at` (`deleted_at`),
        KEY `idx_user_id` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureTableColumnDefinition($conn, 'staff_distribution_list', 'agency_type', "ENUM('MAINPP', 'AGENSI LUAR') NOT NULL");
    ensureTableColumnDefinition($conn, 'staff_distribution_list', 'name', "VARCHAR(255) NOT NULL");
    ensureTableColumnDefinition($conn, 'staff_distribution_list', 'position', "VARCHAR(255) NOT NULL");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'department', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'agency', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'address', "TEXT DEFAULT NULL");
    ensureTableColumnDefinition($conn, 'staff_distribution_list', 'source', "ENUM('USER_LIST', 'MANUAL') DEFAULT 'MANUAL'");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'user_id', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'created_by', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'created_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'updated_by', "INT(11) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'updated_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'deleted_at', "DATETIME DEFAULT NULL");
    ensureGenericTableColumn($conn, 'staff_distribution_list', 'deleted_by', "INT(11) DEFAULT NULL");
    ensureTableIndex($conn, 'staff_distribution_list', 'idx_agency_type', ['agency_type']);
    ensureTableIndex($conn, 'staff_distribution_list', 'idx_deleted_at', ['deleted_at']);
    ensureTableIndex($conn, 'staff_distribution_list', 'idx_user_id', ['user_id']);

    return true;
}

function normalizeStaffDistributionIds($value) {
    if (is_array($value)) {
        $items = $value;
    } else {
        $decoded = json_decode((string) $value, true);
        $items = is_array($decoded) ? $decoded : [];
    }

    $ids = [];
    foreach ($items as $item) {
        if (is_array($item)) {
            $raw_id = $item['id'] ?? $item['staff_id'] ?? $item['distribution_staff_id'] ?? $item['value'] ?? 0;
        } else {
            $raw_id = $item;
        }
        $id = (int) $raw_id;
        if ($id > 0 && !in_array($id, $ids, true)) {
            $ids[] = $id;
        }
    }
    return $ids;
}

function isStaffDistributionPageContentInvalid($content) {
    $text = trim(html_entity_decode(strip_tags((string) $content), ENT_QUOTES, 'UTF-8'));
    return $text === '' || $text === '0';
}

function buildStaffDistributionPageContent($conn, $staff_ids) {
    $staff_ids = normalizeStaffDistributionIds($staff_ids);
    if (empty($staff_ids)) {
        return '';
    }

    $placeholders = implode(',', array_fill(0, count($staff_ids), '?'));
    $stmt = mysqli_prepare($conn, "SELECT id, name, agency_type, position, department, agency, address
        FROM staff_distribution_list
        WHERE deleted_at IS NULL AND id IN ($placeholders)");
    if (!$stmt) {
        return '';
    }

    mysqli_stmt_bind_param($stmt, str_repeat('i', count($staff_ids)), ...$staff_ids);
    mysqli_stmt_execute($stmt);
    $rows = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
    if (empty($rows)) {
        return '';
    }

    $rows_by_id = [];
    foreach ($rows as $row) {
        $rows_by_id[(int) ($row['id'] ?? 0)] = $row;
    }

    $html = '';
    $ordered_rows = [];
    foreach ($staff_ids as $id) {
        if (isset($rows_by_id[$id])) {
            $ordered_rows[] = $rows_by_id[$id];
        }
    }

    foreach ($ordered_rows as $index => $staff) {
        $html .= '<div style="margin-bottom: 20px;">';
        $html .= '<div>' . htmlspecialchars($staff['name'] ?? '-', ENT_QUOTES, 'UTF-8') . '</div>';
        $html .= '<div>' . htmlspecialchars($staff['position'] ?? '-', ENT_QUOTES, 'UTF-8') . '</div>';
        $html .= '<div>' . htmlspecialchars($staff['department'] ?? '-', ENT_QUOTES, 'UTF-8') . '</div>';
        if (($staff['agency_type'] ?? '') === 'AGENSI LUAR') {
            $html .= '<div>' . htmlspecialchars($staff['agency'] ?? '-', ENT_QUOTES, 'UTF-8') . '</div>';
            $html .= '<div>' . htmlspecialchars($staff['address'] ?? '-', ENT_QUOTES, 'UTF-8') . '</div>';
        }
        $html .= '</div>';
        if ($index < count($ordered_rows) - 1) {
            $html .= '<p><br></p>';
        }
    }

    return $html;
}

function syncMainPPUsersToStaffDistributionList($conn) {
    // Get all approved users from users table
    $users_sql = "SELECT id, fullname, position, department 
                  FROM users 
                  WHERE status != 'pending' 
                  ORDER BY registered_at DESC";
    $users_result = mysqli_query($conn, $users_sql);
    
    if (!$users_result) {
        return false;
    }
    
    $users = mysqli_fetch_all($users_result, MYSQLI_ASSOC);
    
    if (empty($users)) {
        return true;
    }
    
    // Get existing MAINPP records linked to users. Use user_id as the identity
    // so edited user-linked rows are not reinserted as duplicates.
    $existing_sql = "SELECT id, user_id FROM staff_distribution_list 
                     WHERE agency_type = 'MAINPP' 
                     AND user_id IS NOT NULL 
                     AND deleted_at IS NULL
                     ORDER BY user_id ASC, CASE WHEN source = 'MANUAL' THEN 0 ELSE 1 END ASC, updated_at DESC, id DESC";
    $existing_result = mysqli_query($conn, $existing_sql);
    
    $existing_user_ids = [];
    $duplicate_ids = [];
    if ($existing_result) {
        while ($row = mysqli_fetch_assoc($existing_result)) {
            $row_user_id = (int) $row['user_id'];
            $row_id = (int) $row['id'];
            if (in_array($row_user_id, $existing_user_ids, true)) {
                $duplicate_ids[] = $row_id;
                continue;
            }
            $existing_user_ids[] = $row_user_id;
        }
    }

    if (!empty($duplicate_ids)) {
        $duplicate_ids = array_values(array_unique(array_filter($duplicate_ids)));
        $duplicate_id_list = implode(',', array_map('intval', $duplicate_ids));
        if ($duplicate_id_list !== '') {
            mysqli_query(
                $conn,
                "UPDATE staff_distribution_list
                 SET deleted_at = NOW()
                 WHERE id IN ($duplicate_id_list)
                 AND agency_type = 'MAINPP'
                 AND user_id IS NOT NULL
                 AND deleted_at IS NULL"
            );
        }
    }
    
    // Insert new users
    $insert_sql = "INSERT INTO staff_distribution_list 
                   (agency_type, name, position, department, source, user_id, created_at) 
                   VALUES ('MAINPP', ?, ?, ?, 'USER_LIST', ?, NOW())";
    $insert_stmt = mysqli_prepare($conn, $insert_sql);
    
    if (!$insert_stmt) {
        return false;
    }
    
    $synced_count = 0;
    foreach ($users as $user) {
        $user_id = (int) $user['id'];
        
        // Skip if already exists
        if (in_array($user_id, $existing_user_ids, true)) {
            continue;
        }
        
        $name = trim($user['fullname'] ?? '');
        $position = trim($user['position'] ?? '');
        $department = trim($user['department'] ?? '');
        
        // Convert empty values to '-'
        if (empty($name)) $name = '-';
        if (empty($position)) $position = '-';
        if (empty($department)) $department = '-';
        
        mysqli_stmt_bind_param($insert_stmt, "sssi", $name, $position, $department, $user_id);
        if (mysqli_stmt_execute($insert_stmt)) {
            $synced_count++;
        }
    }
    
    mysqli_stmt_close($insert_stmt);
    
    return true;
}

function buildLetterTemplatePlaceholderData($data) {
    return [
        'reference_no' => (string) ($data['reference_no'] ?? ''),
        'date' => (string) ($data['date'] ?? ''),
        'receiver_name' => (string) ($data['receiver_name'] ?? ''),
        'receiver_department' => (string) ($data['receiver_department'] ?? ''),
        'subject' => (string) ($data['subject'] ?? ''),
        'letter_content' => (string) ($data['letter_content'] ?? ''),
        'sender_name' => (string) ($data['sender_name'] ?? ''),
        'sender_position' => (string) ($data['sender_position'] ?? ''),
        'sender_department' => (string) ($data['sender_department'] ?? ''),
    ];
}

function replaceLetterTemplatePlaceholders($content, $data) {
    $placeholders = buildLetterTemplatePlaceholderData($data);
    $replacements = [];
    foreach ($placeholders as $key => $value) {
        $safe_value = $key === 'letter_content'
            ? (string) $value
            : htmlspecialchars((string) $value, ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $replacements['{{' . $key . '}}'] = $safe_value;
    }

    return strtr((string) $content, $replacements);
}

function ensureNotificationReadSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `user_notification_reads` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `user_id` INT(11) NOT NULL,
        `notification_key` VARCHAR(100) NOT NULL,
        `read_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_user_notification_read` (`user_id`, `notification_key`),
        KEY `idx_user_notification_reads_user` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureGenericTableColumn($conn, 'user_notification_reads', 'read_at', "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP");
    ensureTableIndex($conn, 'user_notification_reads', 'uniq_user_notification_read', ['user_id', 'notification_key'], true);
    ensureTableIndex($conn, 'user_notification_reads', 'idx_user_notification_reads_user', ['user_id']);

    return true;
}

function ensurePushNotificationSchema($conn) {
    $devices_sql = "CREATE TABLE IF NOT EXISTS `push_devices` (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `user_id` INT NOT NULL,
        `token` VARCHAR(512) NOT NULL,
        `platform` VARCHAR(20) NOT NULL DEFAULT 'unknown',
        `device_name` VARCHAR(120) DEFAULT NULL,
        `enabled` TINYINT(1) NOT NULL DEFAULT 1,
        `last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_push_device_token` (`token`),
        KEY `idx_push_devices_user_enabled` (`user_id`, `enabled`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    $notifications_sql = "CREATE TABLE IF NOT EXISTS `push_notifications` (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `user_id` INT NOT NULL,
        `title` VARCHAR(180) NOT NULL,
        `body` VARCHAR(500) NOT NULL,
        `notification_type` VARCHAR(50) NOT NULL DEFAULT 'general',
        `entity_id` VARCHAR(100) DEFAULT NULL,
        `payload_json` LONGTEXT DEFAULT NULL,
        `status` ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
        `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
        `last_error` VARCHAR(1000) DEFAULT NULL,
        `scheduled_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `sent_at` DATETIME DEFAULT NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_push_queue` (`status`, `scheduled_at`),
        KEY `idx_push_notifications_user` (`user_id`, `created_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    return mysqli_query($conn, $devices_sql) === true
        && mysqli_query($conn, $notifications_sql) === true;
}

function ensurePasswordResetSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `password_resets` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `email` VARCHAR(191) NOT NULL,
        `token` VARCHAR(20) NOT NULL,
        `expires_at` DATETIME NOT NULL,
        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_password_resets_email` (`email`),
        KEY `idx_password_resets_expires_at` (`expires_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureTableColumnDefinition($conn, 'password_resets', 'email', "VARCHAR(191) NOT NULL");
    ensureTableColumnDefinition($conn, 'password_resets', 'token', "VARCHAR(20) NOT NULL");
    ensureTableColumnDefinition($conn, 'password_resets', 'expires_at', "DATETIME NOT NULL");
    ensureTableColumnDefinition($conn, 'password_resets', 'created_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP");
    ensureTableIndex($conn, 'password_resets', 'idx_password_resets_email', ['email']);
    ensureTableIndex($conn, 'password_resets', 'idx_password_resets_expires_at', ['expires_at']);

    return true;
}

function ensureCustomFieldsSchema($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS `custom_fields` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `module` VARCHAR(50) NOT NULL,
        `field_key` VARCHAR(100) NOT NULL,
        `field_label` VARCHAR(100) NOT NULL,
        `field_type` ENUM('text','number','date','textarea','select') DEFAULT 'text',
        `field_options` TEXT DEFAULT NULL,
        `is_required` TINYINT(1) DEFAULT 0,
        `sort_order` INT(11) DEFAULT 0,
        `is_active` TINYINT(1) DEFAULT 1,
        PRIMARY KEY (`id`),
        UNIQUE KEY `unique_field` (`module`,`field_key`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $sql)) {
        return false;
    }

    ensureGenericTableColumn($conn, 'custom_fields', 'field_options', "TEXT DEFAULT NULL");
    return true;
}

function ensureDeleteAuditSchema($conn) {
    $folder_sql = "CREATE TABLE IF NOT EXISTS `folder_delete_log` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `folder_id` INT(11) DEFAULT NULL,
        `folder_name` VARCHAR(255) DEFAULT NULL,
        `deleted_by` INT(11) DEFAULT NULL,
        `deleted_at` DATETIME DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_folder_delete_log_deleted_at` (`deleted_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    $document_sql = "CREATE TABLE IF NOT EXISTS `document_delete_log` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `document_id` INT(11) DEFAULT NULL,
        `document_subject` VARCHAR(255) DEFAULT NULL,
        `deleted_by` INT(11) DEFAULT NULL,
        `deleted_at` DATETIME DEFAULT NULL,
        `reason` TEXT DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_document_delete_log_deleted_at` (`deleted_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $folder_sql)) {
        return false;
    }

    if (!mysqli_query($conn, $document_sql)) {
        return false;
    }

    ensureGenericTableColumn($conn, 'folder_delete_log', 'folder_name', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'document_delete_log', 'document_subject', "VARCHAR(255) DEFAULT NULL");
    ensureGenericTableColumn($conn, 'document_delete_log', 'reason', "TEXT DEFAULT NULL");
    ensureTableIndex($conn, 'folder_delete_log', 'idx_folder_delete_log_deleted_at', ['deleted_at']);
    ensureTableIndex($conn, 'document_delete_log', 'idx_document_delete_log_deleted_at', ['deleted_at']);

    return true;
}

function ensureUATSchema($conn) {
    $questions_sql = "CREATE TABLE IF NOT EXISTS `uat_questions` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `module` VARCHAR(100) NOT NULL,
        `item_title` VARCHAR(255) NOT NULL,
        `test_description` TEXT NOT NULL,
        `expected_result` TEXT DEFAULT NULL,
        `is_active` TINYINT(1) NOT NULL DEFAULT 1,
        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_uat_questions_module` (`module`),
        KEY `idx_uat_questions_active` (`is_active`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    $responses_sql = "CREATE TABLE IF NOT EXISTS `uat_responses` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `user_id` INT(11) NOT NULL,
        `question_id` INT(11) NOT NULL,
        `status` ENUM('belum_test','pass','isu') NOT NULL DEFAULT 'belum_test',
        `notes` TEXT DEFAULT NULL,
        `submitted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_uat_user_question` (`user_id`, `question_id`),
        KEY `idx_uat_responses_user_id` (`user_id`),
        KEY `idx_uat_responses_question_id` (`question_id`),
        KEY `idx_uat_responses_status` (`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    $user_info_sql = "CREATE TABLE IF NOT EXISTS `uat_user_info` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `user_id` INT(11) NOT NULL,
        `department` VARCHAR(255) NOT NULL,
        `position` VARCHAR(255) DEFAULT NULL,
        `phone` VARCHAR(20) DEFAULT NULL,
        `additional_notes` TEXT DEFAULT NULL,
        `completed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_uat_user_info_user_id` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    if (!mysqli_query($conn, $questions_sql)) {
        return false;
    }

    if (!mysqli_query($conn, $responses_sql)) {
        return false;
    }

    if (!mysqli_query($conn, $user_info_sql)) {
        return false;
    }

    // Add new columns if they don't exist (for backward compatibility)
    ensureGenericTableColumn($conn, 'uat_questions', 'item_title', "VARCHAR(255) NOT NULL AFTER `module`");
    ensureGenericTableColumn($conn, 'uat_questions', 'test_description', "TEXT NOT NULL AFTER `item_title`");
    ensureGenericTableColumn($conn, 'uat_questions', 'expected_result', "TEXT DEFAULT NULL AFTER `test_description`");
    ensureGenericTableColumn($conn, 'uat_responses', 'status', "ENUM('belum_test','pass','fail','tidak_berkenaan') NOT NULL DEFAULT 'belum_test' AFTER `question_id`");
    ensureGenericTableColumn($conn, 'uat_responses', 'notes', "TEXT DEFAULT NULL AFTER `status`");
    ensureGenericTableColumn($conn, 'uat_responses', 'updated_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `submitted_at`");

    seedUATQuestions($conn);

    return true;
}

function seedUATQuestions($conn) {
    $check = mysqli_query($conn, "SELECT COUNT(*) as count FROM uat_questions");
    $row = mysqli_fetch_assoc($check);
    
    if (($row['count'] ?? 0) > 0) {
        return true;
    }

    $modules = [
        'Dashboard' => [
            ['title' => 'Paparan Dashboard', 'description' => 'Buka dashboard dan semak paparan statistik, senarai fail, dan navigasi.', 'expected' => 'Semua statistik (jumlah fail, jumlah surat) dipaparkan dengan betul dan navigasi berfungsi dengan lancar.'],
            ['title' => 'Tab Bahagian', 'description' => 'Semak tab navigasi antara bahagian yang berbeza.', 'expected' => 'Tab berfungsi dengan baik dan paparan berubah mengikut bahagian yang dipilih.'],
            ['title' => 'Statistik Fail', 'description' => 'Semak jumlah fail dan jumlah surat dipaparkan dengan betul.', 'expected' => 'Statistik dipaparkan dengan tepat dan dikemaskini secara real-time apabila fail/surat baru ditambah.'],
            ['title' => 'Menu Sidebar', 'description' => 'Semak semua menu sidebar berfungsi dan akses ke halaman yang sesuai.', 'expected' => 'Setiap menu sidebar membuka halaman yang betul dan pengguna tidak boleh akses halaman tanpa kebenaran.'],
            ['title' => 'Log Keluar', 'description' => 'Uji fungsi log keluar dan pastikan sesi dimansuh.', 'expected' => 'Pengguna berjaya log keluar dan dialihkan ke halaman log masuk dengan sesi yang dimansuh.'],
        ],
        'Senarai Fail' => [
            ['title' => 'Paparan Senarai Fail Office', 'description' => 'Buka senarai fail dan semak fail office dipaparkan dengan betul.', 'expected' => 'Fail office dipaparkan dalam jadual dengan bilangan, kod fail, nama fail, jenis, bahagian, status, dan tarikh.'],
            ['title' => 'Paparan Senarai Fail Personal', 'description' => 'Semak fail personal dipaparkan di bawah bahagian yang sesuai.', 'expected' => 'Fail personal dipaparkan dengan betul dan hanya menunjukkan fail milik pengguna atau bahagian yang betul.'],
            ['title' => 'Pembilangan Baris', 'description' => 'Semak nombor baris (Bil.) pada setiap jadual fail.', 'expected' => 'Nombor baris dipaparkan dengan betul dan berurutan dari 1 untuk setiap halaman.'],
            ['title' => 'Tajuk Jadual', 'description' => 'Semak tajuk jadual (Fail, No. Rujukan, Perkara, Tarikh, Daripada, Kepada, Jenis, Tindakan) dipaparkan dengan betul.', 'expected' => 'Semua tajuk jadual dipaparkan dengan betul dan sejajar di tengah.'],
            ['title' => 'Pemadanan Jadual', 'description' => 'Semak fail office dan fail personal dipisahkan dengan tajuk bahagian.', 'expected' => 'Fail office dan fail personal dipaparkan dalam bahagian yang berasingan dengan tajuk "Fail Office" dan "Fail Personal".'],
            ['title' => 'Kosongkan Senarai', 'description' => 'Semak mesej "Tiada fail" dipaparkan apabila tiada fail.', 'expected' => 'Mesej "Tiada fail office dijumpai" atau "Tiada fail personal dijumpai" dipaparkan apabila tiada fail.'],
            ['title' => 'Butiran Fail', 'description' => 'Buka butiran fail dan semak maklumat dokumen.', 'expected' => 'Butiran fail terbuka dengan betul menunjukkan senarai dokumen dalam fail.'],
            ['title' => 'Tutup Fail', 'description' => 'Uji fungsi tutup fail dan semak status berubah.', 'expected' => 'Fail berjaya ditutup dan status bertukar kepada "Ditutup". Fail yang sudah ditutup tidak boleh ditambah surat baru.'],
            ['title' => 'Padam Fail', 'description' => 'Uji fungsi padam fail.', 'expected' => 'Fail berjaya dipadamkan dan tidak lagi muncul dalam senarai fail.'],
        ],
        'Tambah Fail' => [
            ['title' => 'Borang Tambah Fail', 'description' => 'Buka halaman tambah fail dan semak semua medan wajib.', 'expected' => 'Borang tambah fail dipaparkan dengan medan untuk bahagian, jenis fail, kod fail, nama fail, dan no. rujukan mula.'],
            ['title' => 'Pilihan Bahagian', 'description' => 'Pilih bahagian dari senarai drop-down.', 'expected' => 'Bahagian yang betul dipilih dan disimpan dengan betul.'],
            ['title' => 'Pilihan Jenis Fail', 'description' => 'Pilih jenis fail (office atau personal) dari drop-down.', 'expected' => 'Jenis fail dipilih dengan betul dan medan yang relevan muncul.'],
            ['title' => 'Kod Fail Office', 'description' => 'Masukkan kod fail office mengikut format rasmi.', 'expected' => 'Kod fail office diterima dan disimpan dengan betul. Format kod mengikut standard rasmi.'],
            ['title' => 'Kod Fail Personal', 'description' => 'Masukkan kod fail personal dengan format bebas.', 'expected' => 'Kod fail personal diterima tanpa sekatan format tertentu.'],
            ['title' => 'Nama Fail', 'description' => 'Masukkan nama fail pada medan yang sesuai.', 'expected' => 'Nama fail disimpan dengan betul dan muncul dalam senarai fail.'],
            ['title' => 'No. Rujukan Mula', 'description' => 'Masukkan nombor rujukan mula untuk fail.', 'expected' => 'Nombor rujukan mula disimpan dengan betul dan digunakan sebagai titik permulaan penomoran.'],
            ['title' => 'Saiz Fail', 'description' => 'Semak saiz maksimum fail yang boleh dimuat naik.', 'expected' => 'Saiz fail tidak melebihi had yang ditetapkan dan mesej ralat dipaparkan jika melebihi had.'],
            ['title' => 'Format Fail', 'description' => 'Uji penerimaan format fail yang berbeza (PDF, DOC, dll).', 'expected' => 'Format fail yang diterima dipaparkan dengan betul dan fail yang tidak diterima ditolak dengan mesej yang jelas.'],
        ],
        'Carian Lanjutan' => [
            ['title' => 'Carian Berdasarkan Kod Fail', 'description' => 'Masukkan kod fail pada medan carian dan jalankan carian.', 'expected' => 'Hasil carian dipaparkan dengan betul mengikut kod fail yang dicari.'],
            ['title' => 'Carian Berdasarkan No. Rujukan', 'description' => 'Masukkan nombor rujukan pada medan carian.', 'expected' => 'Hasil carian dipaparkan dengan betul mengikut nombor rujukan.'],
            ['title' => 'Carian Berdasarkan Perkara', 'description' => 'Masukkan perkara pada medan carian.', 'expected' => 'Hasil carian dipaparkan dengan betul mengikut perkara yang dicari.'],
            ['title' => 'Carian Berdasarkan Tarikh', 'description' => 'Pilih tarikh pada medan carian.', 'expected' => 'Hasil carian dipaparkan dengan betul mengikut tarikh yang dipilih.'],
            ['title' => 'Carian Berdasarkan Daripada', 'description' => 'Pilih pengirim pada medan carian.', 'expected' => 'Hasil carian dipaparkan dengan betul mengikut pengirim yang dipilih.'],
            ['title' => 'Carian Berdasarkan Kepada', 'description' => 'Pilih penerima pada medan carian.', 'expected' => 'Hasil carian dipaparkan dengan betul mengikut penerima yang dipilih.'],
            ['title' => 'Carian Berdasarkan Jenis Surat', 'description' => 'Pilih jenis surat pada medan carian.', 'expected' => 'Hasil carian dipaparkan dengan betul mengikut jenis surat yang dipilih.'],
            ['title' => 'Carian Berdasarkan Jenis Fail', 'description' => 'Pilih jenis fail (office atau personal) pada medan carian.', 'expected' => 'Hasil carian dipaparkan dengan betul mengikut jenis fail yang dipilih.'],
            ['title' => 'Pembilangan Baris', 'description' => 'Semak nombor baris (Bil.) pada jadual hasil carian.', 'expected' => 'Nombor baris dipaparkan dengan betul dan berurutan dari 1.'],
            ['title' => 'Tajuk Jadual', 'description' => 'Semak tajuk jadual (Bil., Fail, No. Rujukan, Perkara, Tarikh, Daripada, Kepada, Jenis, Tindakan) dipaparkan dengan betul.', 'expected' => 'Semua tajuk jadual dipaparkan dengan betul dan sejajar di tengah.'],
            ['title' => 'Kosongkan Hasil', 'description' => 'Semak mesej "Tiada hasil carian" dipaparkan apabila tiada hasil.', 'expected' => 'Mesej "Tiada hasil carian untuk kategori ini" dipaparkan apabila tiada hasil.'],
            ['title' => 'Butiran Fail dari Hasil', 'description' => 'Buka butiran fail dari hasil carian.', 'expected' => 'Butiran fail terbuka dengan betul dari halaman carian.'],
            ['title' => 'Tajuk Table "Hasil Carian Fail Office"', 'description' => 'Semak tajuk "Hasil Carian Fail Office" dipaparkan pada table pertama.', 'expected' => 'Tajuk "Hasil Carian Fail Office" dipaparkan dengan betul di atas table pertama.'],
            ['title' => 'Tajuk Table "Hasil Carian Fail Personal"', 'description' => 'Semak tajuk "Hasil Carian Fail Personal" dipaparkan pada table kedua.', 'expected' => 'Tajuk "Hasil Carian Fail Personal" dipaparkan dengan betul di atas table kedua.'],
            ['title' => 'Pengaturan Jadual (Center/Left)', 'description' => 'Semak tajuk jadual berada di tengah dan rekod berada di kiri.', 'expected' => 'Tajuk jadual (th) sejajar di tengah dan data rekod (td) sejajar di kiri.'],
        ],
        'Dashboard Surat' => [
            ['title' => 'Paparan Dashboard Surat', 'description' => 'Buka dashboard surat dan semak paparan statistik.', 'expected' => 'Semua statistik dashboard surat (jumlah surat, jumlah draf, dll.) dipaparkan dengan betul.'],
            ['title' => 'Tab Bahagian Dashboard Surat', 'description' => 'Semak tab navigasi antara bahagian pada dashboard surat.', 'expected' => 'Tab berfungsi dengan baik dan paparan berubah mengikut bahagian yang dipilih.'],
            ['title' => 'Statistik Surat', 'description' => 'Semak jumlah surat dan draf dipaparkan dengan betul.', 'expected' => 'Statistik surat dipaparkan dengan tepat dan dikemaskini secara real-time.'],
        ],
        'Senarai Surat' => [
            ['title' => 'Paparan Senarai Surat', 'description' => 'Buka senarai surat dan semak semua surat dipaparkan.', 'expected' => 'Senarai surat dipaparkan dengan betul termasuk rujukan, perkara, tarikh, dan status.'],
            ['title' => 'Pembilangan Baris', 'description' => 'Semak nombor baris pada jadual senarai surat.', 'expected' => 'Nombor baris dipaparkan dengan betul dan berurutan dari 1.'],
            ['title' => 'Tajuk Jadual', 'description' => 'Semak tajuk jadual senarai surat dipaparkan dengan betul.', 'expected' => 'Semua tajuk jadual dipaparkan dengan betul dan sejajar.'],
            ['title' => 'Kosongkan Senarai', 'description' => 'Semak mesej "Tiada surat" dipaparkan apabila tiada surat.', 'expected' => 'Mesej kosong dipaparkan apabila tiada surat untuk dipaparkan.'],
            ['title' => 'Butiran Surat', 'description' => 'Buka butiran surat dari senarai.', 'expected' => 'Butiran surat terbuka dengan betul menunjukkan semua maklumat surat.'],
        ],
        'Sediakan Surat' => [
            ['title' => 'Borang Sediakan Surat', 'description' => 'Buka borang untuk menyediakan surat baru.', 'expected' => 'Borang penyediaan surat dipaparkan dengan semua medan yang diperlukan.'],
            ['title' => 'Pilihan Template', 'description' => 'Pilih template surat dari senarai.', 'expected' => 'Template dimuatkan dengan betul dan format surat dipaparkan.'],
            ['title' => 'Isi Maklumat Surat', 'description' => 'Isi semua maklumat surat yang diperlukan.', 'expected' => 'Maklumat surat disimpan dengan betul dan boleh disemak sebelum dihantar.'],
            ['title' => 'Simpan Sebagai Draf', 'description' => 'Simpan surat sebagai draf tanpa menghantar.', 'expected' => 'Surat berjaya disimpan sebagai draf dan boleh diedit semula.'],
            ['title' => 'Hantar Surat', 'description' => 'Hantar surat untuk kelulusan.', 'expected' => 'Surat berjaya dihantar dan status kelulusan dikemaskini.'],
            ['title' => 'Format Memo Kelulusan', 'description' => 'Semak format memo kelulusan dipaparkan dengan betul.', 'expected' => 'Format memo kelulusan standard dipaparkan dengan jelas dan profesional.'],
            ['title' => 'Format Memo', 'description' => 'Semak format memo dipaparkan dengan betul.', 'expected' => 'Format memo standard dipaparkan dengan jelas dan profesional mengikut template organisasi.'],
            ['title' => 'Format Surat Rasmi', 'description' => 'Semak format surat rasmi dipaparkan dengan betul.', 'expected' => 'Format surat rasmi standard dipaparkan dengan jelas dan profesional mengikut template organisasi.'],
        ],
        'Pengurusan Pengguna' => [
            ['title' => 'Daftar Pengguna', 'description' => 'Daftar pengguna baru dengan semua maklumat (nama, email, role, bahagian).', 'expected' => 'Pengguna berjaya didaftarkan dan boleh log masuk dengan kredensial yang diberi.'],
            ['title' => 'Role & Permission', 'description' => 'Semak pengurusan role dan permission pengguna untuk mengakses modul tertentu.', 'expected' => 'Role dan permission boleh ditetapkan dengan betul dan pengguna hanya boleh akses modul yang dibenarkan.'],
            ['title' => 'Senarai Pengguna', 'description' => 'Semak senarai pengguna dipaparkan dengan betul dan boleh ditapis mengikut role/bahagian.', 'expected' => 'Senarai pengguna lengkap dan boleh ditapis/dicari dengan mudah.'],
            ['title' => 'Pembilangan Baris', 'description' => 'Semak nombor baris pada jadual senarai pengguna.', 'expected' => 'Nombor baris dipaparkan dengan betul dan berurutan dari 1.'],
            ['title' => 'Tajuk Jadual', 'description' => 'Semak tajuk jadual pengguna dipaparkan dengan betul.', 'expected' => 'Semua tajuk jadual pengguna dipaparkan dengan betul dan sejajar.'],
            ['title' => 'Kelulusan Pengguna', 'description' => 'Uji proses kelulusan permohonan pengguna.', 'expected' => 'Permohonan pengguna boleh diluluskan atau ditolak dengan betul.'],
            ['title' => 'Pengurusan Capaian Sistem', 'description' => 'Semak pengurusan capaian sistem untuk modul tertentu.', 'expected' => 'Capaian modul boleh dikawal dengan betul mengikut role pengguna.'],
            ['title' => 'Aktiviti Pengguna', 'description' => 'Semak log aktiviti pengguna dipaparkan dengan betul.', 'expected' => 'Log aktiviti pengguna dipaparkan dengan betul dan boleh ditapis mengikut tarikh/modul.'],
            ['title' => 'Data Dipadam', 'description' => 'Semak paparan data yang dipadamkan.', 'expected' => 'Data dipadamkan dipaparkan dengan betul dan boleh dipulihkan jika perlu.'],
        ],
        'Profil Pengguna' => [
            ['title' => 'Kemaskini Profil', 'description' => 'Kemaskini maklumat profil pengguna (nama, email, no telefon).', 'expected' => 'Maklumat profil berjaya dikemaskini dan disimpan dalam sistem.'],
            ['title' => 'Tukar Password', 'description' => 'Tukar password pengguna dengan password baru dan sahkan.', 'expected' => 'Password berjaya ditukar dan pengguna boleh log masuk dengan password baru. Password lama tidak lagi boleh digunakan.'],
            ['title' => 'Muat Naik Tandatangan', 'description' => 'Muat naik gambar tandatangan untuk profil pengguna.', 'expected' => 'Tandatangan berjaya dimuat naik dan dipaparkan pada dokumen berkaitan.'],
            ['title' => 'Pembilangan Baris', 'description' => 'Semak nombor baris pada jadual jika ada.', 'expected' => 'Nombor baris dipaparkan dengan betul dan berurutan dari 1.'],
            ['title' => 'Tajuk Jadual', 'description' => 'Semak tajuk jadual pada halaman profil dipaparkan dengan betul.', 'expected' => 'Semua tajuk jadual dipaparkan dengan betul dan sejajar.'],
        ],
        'Sistem Secara Keseluruhan' => [
            ['title' => 'Antaramuka', 'description' => 'Semak keseluruhan antaramuka sistem termasuk menu, navigasi, dan layout.', 'expected' => 'Antaramuka mesra pengguna, mudah digunakan, dan konsisten di semua halaman.'],
            ['title' => 'Responsiviti', 'description' => 'Semak kelajuan dan responsiviti sistem apabila melakukan operasi.', 'expected' => 'Sistem responsif dan pantas, tiada delay yang ketara semasa loading atau memproses data.'],
            ['title' => 'Kepuasan', 'description' => 'Nilai kepuasan keseluruhan terhadap sistem dari segi kegunaan dan kefungsian.', 'expected' => 'Pengguna berpuas hati dengan sistem dan dapat menyelesaikan tugas dengan efisien.'],
            ['title' => 'Navigasi Sidebar', 'description' => 'Semak semua menu sidebar berfungsi dan akses ke halaman yang sesuai.', 'expected' => 'Setiap menu sidebar membuka halaman yang betul dan pengguna tidak boleh akses halaman tanpa kebenaran.'],
            ['title' => 'Log Keluar', 'description' => 'Uji fungsi log keluar dan pastikan sesi dimansuh.', 'expected' => 'Pengguna berjaya log keluar dan dialihkan ke halaman log masuk dengan sesi yang dimansuh.'],
            ['title' => 'Mesej Ralat', 'description' => 'Semak mesej ralat dipaparkan dengan betul apabila ada kesilapan.', 'expected' => 'Mesej ralat yang jelas dan membantu dipaparkan apabila pengguna membuat kesilapan.'],
            ['title' => 'Mesej Berjaya', 'description' => 'Semak mesej kejayaan dipaparkan selepas operasi berjaya.', 'expected' => 'Mesej kejayaan yang jelas dipaparkan selepas setiap operasi berjaya.'],
        ],
    ];

    $stmt = mysqli_prepare($conn, "INSERT INTO uat_questions (module, item_title, test_description, expected_result) VALUES (?, ?, ?, ?)");

    foreach ($modules as $module => $questions) {
        foreach ($questions as $q) {
            mysqli_stmt_bind_param($stmt, "ssss", $module, $q['title'], $q['description'], $q['expected']);
            mysqli_stmt_execute($stmt);
        }
    }

    mysqli_stmt_close($stmt);
    return true;
}

function ensureGenericTableColumn($conn, $table, $column, $definition) {
    $check_sql = "SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
        LIMIT 1";
    $check_stmt = mysqli_prepare($conn, $check_sql);
    if (!$check_stmt) {
        return false;
    }

    mysqli_stmt_bind_param($check_stmt, "ss", $table, $column);
    mysqli_stmt_execute($check_stmt);
    $exists = mysqli_num_rows(mysqli_stmt_get_result($check_stmt)) > 0;

    if ($exists) {
        return true;
    }

    $table_safe = '`' . str_replace('`', '``', $table) . '`';
    $column_safe = '`' . str_replace('`', '``', $column) . '`';
    $alter_sql = "ALTER TABLE {$table_safe} ADD COLUMN {$column_safe} {$definition}";
    return mysqli_query($conn, $alter_sql) === true;
}

function getTableIndexes($conn, $table) {
    $indexes = [];
    $sql = "SELECT INDEX_NAME, NON_UNIQUE, COLUMN_NAME, SEQ_IN_INDEX
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        ORDER BY INDEX_NAME, SEQ_IN_INDEX";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return $indexes;
    }

    mysqli_stmt_bind_param($stmt, "s", $table);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    while ($row = mysqli_fetch_assoc($result)) {
        $name = (string) ($row['INDEX_NAME'] ?? '');
        if ($name === '') {
            continue;
        }

        if (!isset($indexes[$name])) {
            $indexes[$name] = [
                'unique' => ((int) ($row['NON_UNIQUE'] ?? 1)) === 0,
                'columns' => [],
            ];
        }

        $indexes[$name]['columns'][] = (string) ($row['COLUMN_NAME'] ?? '');
    }

    return $indexes;
}

function ensureTableIndex($conn, $table, $index_name, $columns, $unique = false) {
    $indexes = getTableIndexes($conn, $table);
    $normalized_columns = array_values(array_map('strval', $columns));

    foreach ($indexes as $existing_name => $index) {
        $existing_columns = array_values(array_map('strval', $index['columns'] ?? []));
        $existing_unique = !empty($index['unique']);

        if ($existing_columns === $normalized_columns && $existing_unique === (bool) $unique) {
            return true;
        }
    }

    if (isset($indexes[$index_name])) {
        return true;
    }

    $table_safe = '`' . str_replace('`', '``', $table) . '`';
    $index_safe = '`' . str_replace('`', '``', $index_name) . '`';
    $column_sql = [];
    foreach ($normalized_columns as $column) {
        $column_sql[] = '`' . str_replace('`', '``', $column) . '`';
    }

    $alter_sql = "ALTER TABLE {$table_safe} ADD " . ($unique ? 'UNIQUE ' : '') . "INDEX {$index_safe} (" . implode(', ', $column_sql) . ")";
    return mysqli_query($conn, $alter_sql) === true;
}

function normalizeLegacyData($conn) {
    normalizeStoredDepartmentValues($conn, 'users');
    normalizeStoredDepartmentValues($conn, 'folders');
    normalizeStoredEmailValues($conn, 'users');
    normalizeStoredEmailValues($conn, 'password_resets');
}

function cleanupLegacySchema($conn) {
    dropColumnIfExists($conn, 'documents', 'log_deleted_by');
    dropColumnIfExists($conn, 'documents', 'log_deleted_at');
}

function cleanupLegacyLetterDraftApprovalColumns($conn) {
    $legacy_columns = [
        'approver1_user_id',
        'approver1_name',
        'approver1_email',
        'approver1_status',
        'approver1_signature_path',
        'approver1_note',
        'approver1_requested_at',
        'approver1_responded_at',
        'approver2_user_id',
        'approver2_name',
        'approver2_email',
        'approver2_status',
        'approver2_signature_path',
        'approver2_note',
        'approver2_requested_at',
        'approver2_responded_at',
    ];

    foreach ($legacy_columns as $column) {
        dropColumnIfExists($conn, 'letter_drafts', $column);
    }
}

function syncApplicationData($conn) {
    syncUserLastLoginFromActivityLogs($conn);
    syncMissingFolderTypeValues($conn);
    syncDocumentFileNumberingValues($conn);
    syncFolderDeleteLogNames($conn);
    resetTableAutoIncrementToMax($conn, 'role_permissions');
    resetTableAutoIncrementToMax($conn, 'department_permissions');
    resetTableAutoIncrementToMax($conn, 'user_permissions');
    resetTableAutoIncrementToMax($conn, 'letter_drafts');
}

function dropTableIfEmpty($conn, $table, $must_be_empty = true) {
    if (!tableExists($conn, $table)) {
        return true;
    }

    if ($must_be_empty) {
        $table_safe = '`' . str_replace('`', '``', $table) . '`';
        $result = mysqli_query($conn, "SELECT COUNT(*) AS total FROM {$table_safe}");
        $row = $result ? mysqli_fetch_assoc($result) : null;
        if ((int) ($row['total'] ?? 0) > 0) {
            return false;
        }
    }

    $table_safe = '`' . str_replace('`', '``', $table) . '`';
    return mysqli_query($conn, "DROP TABLE {$table_safe}") === true;
}

function dropColumnIfExists($conn, $table, $column) {
    if (!columnExists($conn, $table, $column)) {
        return true;
    }

    $table_safe = '`' . str_replace('`', '``', $table) . '`';
    $column_safe = '`' . str_replace('`', '``', $column) . '`';
    return mysqli_query($conn, "ALTER TABLE {$table_safe} DROP COLUMN {$column_safe}") === true;
}

function tableExists($conn, $table) {
    $stmt = mysqli_prepare($conn, "SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        LIMIT 1");
    if (!$stmt) {
        return false;
    }

    mysqli_stmt_bind_param($stmt, "s", $table);
    mysqli_stmt_execute($stmt);
    return mysqli_num_rows(mysqli_stmt_get_result($stmt)) > 0;
}

function columnExists($conn, $table, $column) {
    $stmt = mysqli_prepare($conn, "SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
        LIMIT 1");
    if (!$stmt) {
        return false;
    }

    mysqli_stmt_bind_param($stmt, "ss", $table, $column);
    mysqli_stmt_execute($stmt);
    return mysqli_num_rows(mysqli_stmt_get_result($stmt)) > 0;
}

function syncUserLastLoginFromActivityLogs($conn) {
    $sql = "UPDATE users u
        LEFT JOIN (
            SELECT user_id, MAX(created_at) AS last_login_at
            FROM activity_logs
            WHERE activity_type = 'login' AND user_id > 0
            GROUP BY user_id
        ) l ON l.user_id = u.id
        SET u.last_login = l.last_login_at
        WHERE (u.last_login IS NULL AND l.last_login_at IS NOT NULL)
            OR (u.last_login IS NOT NULL AND l.last_login_at IS NOT NULL AND u.last_login <> l.last_login_at)";

    return mysqli_query($conn, $sql) === true;
}

function syncMissingFolderTypeValues($conn) {
    return mysqli_query($conn, "UPDATE folders SET folder_type = 'office' WHERE folder_type IS NULL OR folder_type = ''") === true;
}

function syncDocumentFileNumberingValues($conn) {
    return mysqli_query($conn, "UPDATE documents SET file_numbering = ref_no WHERE (file_numbering IS NULL OR file_numbering = '') AND ref_no IS NOT NULL AND ref_no <> ''") === true;
}

function syncFolderDeleteLogNames($conn) {
    $sql = "UPDATE folder_delete_log l
        INNER JOIN folders f ON f.id = l.folder_id
        SET l.folder_name = f.folder_name
        WHERE (l.folder_name IS NULL OR l.folder_name = '')";
    return mysqli_query($conn, $sql) === true;
}

function resetTableAutoIncrementToMax($conn, $table) {
    if (!tableExists($conn, $table)) {
        return false;
    }

    $table_safe = '`' . str_replace('`', '``', $table) . '`';
    $result = mysqli_query($conn, "SELECT COALESCE(MAX(id), 0) + 1 AS next_id FROM {$table_safe}");
    $row = $result ? mysqli_fetch_assoc($result) : null;
    $next_id = max(1, (int) ($row['next_id'] ?? 1));

    $current_auto_result = mysqli_query($conn, "SELECT AUTO_INCREMENT
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = '" . mysqli_real_escape_string($conn, $table) . "'
        LIMIT 1");
    $current_auto_row = $current_auto_result ? mysqli_fetch_assoc($current_auto_result) : null;
    $current_auto_increment = (int) ($current_auto_row['AUTO_INCREMENT'] ?? 0);

    if ($current_auto_increment > 0 && $current_auto_increment <= $next_id) {
        return true;
    }

    return mysqli_query($conn, "ALTER TABLE {$table_safe} AUTO_INCREMENT = {$next_id}") === true;
}

function normalizeStoredDepartmentValues($conn, $table) {
    $table_safe = '`' . str_replace('`', '``', $table) . '`';
    $result = mysqli_query($conn, "SELECT id, department FROM {$table_safe}");
    if (!$result) {
        return false;
    }

    $update = mysqli_prepare($conn, "UPDATE {$table_safe} SET department = ? WHERE id = ?");
    if (!$update) {
        return false;
    }

    while ($row = mysqli_fetch_assoc($result)) {
        $current = (string) ($row['department'] ?? '');
        $normalized = normalizeDepartmentValue($current);

        if ($normalized !== $current) {
            $id = (int) ($row['id'] ?? 0);
            mysqli_stmt_bind_param($update, "si", $normalized, $id);
            mysqli_stmt_execute($update);
        }
    }

    return true;
}

function normalizeStoredEmailValues($conn, $table) {
    $table_safe = '`' . str_replace('`', '``', $table) . '`';
    $result = mysqli_query($conn, "SELECT id, email FROM {$table_safe}");
    if (!$result) {
        return false;
    }

    $update = mysqli_prepare($conn, "UPDATE {$table_safe} SET email = ? WHERE id = ?");
    if (!$update) {
        return false;
    }

    while ($row = mysqli_fetch_assoc($result)) {
        $current = (string) ($row['email'] ?? '');
        $normalized = normalizeEmailAddress($current);

        if ($normalized !== $current) {
            $id = (int) ($row['id'] ?? 0);
            mysqli_stmt_bind_param($update, "si", $normalized, $id);
            mysqli_stmt_execute($update);
        }
    }

    return true;
}

function getMailConfig() {
    static $config = null;

    if ($config !== null) {
        return $config;
    }

    $defaults = [
        'enabled' => false,
        'host' => 'smtp.gmail.com',
        'port' => 587,
        'encryption' => 'tls',
        'username' => '',
        'password' => '',
        'from_email' => '',
        'from_name' => 'Sistem Pengurusan Fail',
    ];

    $config_file = __DIR__ . DIRECTORY_SEPARATOR . 'mail_config.php';
    if (!is_file($config_file)) {
        $config = $defaults;
        return $config;
    }

    $loaded_config = include $config_file;
    if (!is_array($loaded_config)) {
        $config = $defaults;
        return $config;
    }

    $config = array_merge($defaults, $loaded_config);

    if (hasPlaceholderMailValue($config['from_email']) && !hasPlaceholderMailValue($config['username']) && filter_var($config['username'], FILTER_VALIDATE_EMAIL)) {
        $config['from_email'] = $config['username'];
    }

    return $config;
}

function hasPlaceholderMailValue($value) {
    $value = strtolower(trim((string) $value));

    return $value === ''
        || in_array($value, [
            'gmail-anda@gmail.com',
            'masukkan-app-password-gmail',
            'your-email@gmail.com',
            'your-app-password',
        ], true);
}

function isMailConfigured() {
    return count(getMailConfigurationIssues()) === 0;
}

function getMailConfigurationIssues() {
    $config = getMailConfig();
    $issues = [];

    if (empty($config['enabled'])) {
        $issues[] = 'Penghantaran emel dimatikan.';
    }

    if (empty($config['host'])) {
        $issues[] = 'Host SMTP belum diisi.';
    }

    if (empty($config['username']) || hasPlaceholderMailValue($config['username']) || !filter_var($config['username'], FILTER_VALIDATE_EMAIL)) {
        $issues[] = 'Alamat Gmail penghantar pada username belum diisi dengan betul.';
    }

    if (empty($config['password']) || hasPlaceholderMailValue($config['password'])) {
        $issues[] = 'App Password Gmail belum diisi.';
    }

    if (empty($config['from_email']) || hasPlaceholderMailValue($config['from_email']) || !filter_var($config['from_email'], FILTER_VALIDATE_EMAIL)) {
        $issues[] = 'Alamat from_email belum diisi dengan betul.';
    }

    return $issues;
}

function normalizeEmailAddress($email) {
    return strtolower(trim((string) $email));
}

function isValidEmailAddress($email) {
    $email = normalizeEmailAddress($email);

    return $email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
}

function isMainppEmailAddress($email) {
    $email = normalizeEmailAddress($email);
    $required_domain = '@mainpp.gov.my';

    if (!isValidEmailAddress($email)) {
        return false;
    }

    return substr($email, -strlen($required_domain)) === $required_domain;
}

function normalizeIcNumber($ic_number) {
    return preg_replace('/\D+/', '', (string) $ic_number);
}

function getPasswordResetExpiryMinutes() {
    return 15;
}

function buildAppUrl($path = '') {
    $host = trim((string) ($_SERVER['HTTP_HOST'] ?? ''));
    $https = !empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off';
    $base_path = rtrim(str_replace('\\', '/', dirname((string) ($_SERVER['SCRIPT_NAME'] ?? ''))), '/');
    $normalized_path = ltrim((string) $path, '/');

    if ($host === '') {
        return $normalized_path;
    }

    $scheme = $https ? 'https' : 'http';
    $url = $scheme . '://' . $host;

    if ($base_path !== '' && $base_path !== '.') {
        $url .= $base_path;
    }

    if ($normalized_path !== '') {
        $url .= '/' . $normalized_path;
    }

    return $url;
}

function sendGmailMessage($to_email, $to_name, $subject, $html_body, $plain_body = '') {
    if (!isMailConfigured()) {
        return [
            'success' => false,
            'message' => implode(' ', getMailConfigurationIssues()),
        ];
    }

    $phpmailer_base = __DIR__ . DIRECTORY_SEPARATOR . 'vendor' . DIRECTORY_SEPARATOR . 'phpmailer' . DIRECTORY_SEPARATOR . 'src' . DIRECTORY_SEPARATOR;
    $phpmailer_files = [
        $phpmailer_base . 'Exception.php',
        $phpmailer_base . 'PHPMailer.php',
        $phpmailer_base . 'SMTP.php',
    ];

    foreach ($phpmailer_files as $file) {
        if (!is_file($file)) {
            return [
                'success' => false,
                'message' => 'Perpustakaan emel PHPMailer tidak dijumpai.',
            ];
        }
    }

    require_once $phpmailer_base . 'Exception.php';
    require_once $phpmailer_base . 'PHPMailer.php';
    require_once $phpmailer_base . 'SMTP.php';

    $config = getMailConfig();

    try {
        $mail = new \PHPMailer\PHPMailer\PHPMailer(true);
        $mail->isSMTP();
        $mail->Host = (string) $config['host'];
        $mail->Port = (int) $config['port'];
        $mail->SMTPAuth = true;
        $mail->Username = (string) $config['username'];
        $mail->Password = (string) $config['password'];
        $mail->CharSet = 'UTF-8';

        $encryption = strtolower(trim((string) $config['encryption']));
        if ($encryption === 'ssl') {
            $mail->SMTPSecure = \PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_SMTPS;
        } elseif ($encryption === 'tls') {
            $mail->SMTPSecure = \PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
        }

        $mail->setFrom((string) $config['from_email'], (string) $config['from_name']);
        $mail->addAddress($to_email, $to_name);
        $mail->isHTML(true);
        $mail->Subject = $subject;
        $mail->Body = $html_body;
        $mail->AltBody = $plain_body !== '' ? $plain_body : strip_tags(str_replace(['<br>', '<br/>', '<br />'], "\n", $html_body));
        $mail->send();

        return [
            'success' => true,
            'message' => '',
        ];
    } catch (\Throwable $e) {
        return [
            'success' => false,
            'message' => $e->getMessage(),
        ];
    }
}

function sendPasswordResetTokenEmail($email, $token, $expires_at, $recipient_name = '') {
    $safe_token = htmlspecialchars((string) $token, ENT_QUOTES, 'UTF-8');
    $safe_email = htmlspecialchars((string) $email, ENT_QUOTES, 'UTF-8');
    $expires_display = date('d/m/Y H:i', strtotime($expires_at));
    $expiry_minutes = getPasswordResetExpiryMinutes();
    $reset_url = buildAppUrl('reset_password.php?email=' . urlencode((string) $email));
    $safe_reset_url = htmlspecialchars($reset_url, ENT_QUOTES, 'UTF-8');
    $safe_name = htmlspecialchars(trim((string) $recipient_name), ENT_QUOTES, 'UTF-8');
    $greeting = $safe_name !== '' ? "Salam {$safe_name}," : 'Salam,';

    $subject = 'Token Reset Kata Laluan';
    $html_body = "
        <div style=\"font-family:'Segoe UI','Inter',Tahoma,Geneva,Verdana,sans-serif;max-width:560px;margin:0 auto;padding:24px;border:1px solid #dbe7f3;border-radius:14px;background:#ffffff;\">
            <h2 style=\"margin:0 0 12px;color:#0b5cab;\">Permintaan Reset Kata Laluan</h2>
            <p style=\"margin:0 0 16px;color:#334155;line-height:1.6;\">{$greeting}</p>
            <p style=\"margin:0 0 16px;color:#334155;line-height:1.6;\">Kami menerima permintaan reset kata laluan untuk akaun <strong>{$safe_email}</strong>.</p>
            <p style=\"margin:0 0 10px;color:#334155;line-height:1.6;\">Gunakan token di bawah untuk tetapkan kata laluan baru:</p>
            <div style=\"margin:18px 0;padding:16px 20px;background:#f3f9ff;border:1px solid #cfe4ff;border-radius:12px;font-size:28px;font-weight:700;letter-spacing:6px;color:#0b5cab;text-align:center;\">{$safe_token}</div>
            <p style=\"margin:0 0 10px;color:#334155;line-height:1.6;\">Token ini sah selama <strong>{$expiry_minutes} minit</strong>, sehingga <strong>{$expires_display}</strong>.</p>
            <p style=\"margin:0 0 16px;color:#334155;line-height:1.6;\">Selepas itu, buka pautan berikut untuk masukkan token dan tetapkan kata laluan baru:</p>
            <p style=\"margin:0 0 16px;\"><a href=\"{$safe_reset_url}\" style=\"display:inline-block;padding:12px 18px;background:#0b5cab;color:#ffffff;text-decoration:none;border-radius:10px;font-weight:600;\">Buka Halaman Reset Kata Laluan</a></p>
            <p style=\"margin:0 0 10px;color:#64748b;line-height:1.6;word-break:break-all;\">Jika butang tidak berfungsi, salin pautan ini ke pelayar anda:<br>{$safe_reset_url}</p>
            <p style=\"margin:0;color:#64748b;line-height:1.6;\">Jika anda tidak membuat permintaan ini, abaikan emel ini dan tiada perubahan akan dibuat pada akaun anda.</p>
        </div>";

    $plain_body = ($recipient_name !== '' ? "Salam {$recipient_name},\n\n" : '')
        . "Permintaan Reset Kata Laluan\n\n"
        . "Akaun: {$email}\n"
        . "Token: {$token}\n"
        . "Sah selama: {$expiry_minutes} minit\n"
        . "Sah sehingga: {$expires_display}\n"
        . "Pautan reset: {$reset_url}\n\n"
        . "Jika anda tidak membuat permintaan ini, abaikan emel ini.";

    return sendGmailMessage($email, $recipient_name, $subject, $html_body, $plain_body);
}

function sendSignatureChangeOtpEmail($email, $token, $expires_at, $recipient_name = '') {
    $safe_token = htmlspecialchars((string) $token, ENT_QUOTES, 'UTF-8');
    $safe_email = htmlspecialchars((string) $email, ENT_QUOTES, 'UTF-8');
    $safe_name = htmlspecialchars(trim((string) $recipient_name), ENT_QUOTES, 'UTF-8');
    $expires_display = date('d/m/Y H:i', strtotime($expires_at));
    $expiry_minutes = getPasswordResetExpiryMinutes();
    $greeting = $safe_name !== '' ? "Salam {$safe_name}," : 'Salam,';

    $subject = 'OTP Tukar Tandatangan Digital';
    $html_body = "
        <div style=\"font-family:'Segoe UI','Inter',Tahoma,Geneva,Verdana,sans-serif;max-width:560px;margin:0 auto;padding:24px;border:1px solid #dbe7f3;border-radius:14px;background:#ffffff;\">
            <h2 style=\"margin:0 0 12px;color:#0b5cab;\">Pengesahan Tukar Tandatangan Digital</h2>
            <p style=\"margin:0 0 16px;color:#334155;line-height:1.6;\">{$greeting}</p>
            <p style=\"margin:0 0 16px;color:#334155;line-height:1.6;\">Kami menerima permintaan untuk menukar tandatangan digital bagi akaun <strong>{$safe_email}</strong>.</p>
            <p style=\"margin:0 0 10px;color:#334155;line-height:1.6;\">Masukkan OTP di bawah pada halaman profil untuk membenarkan upload tandatangan baharu:</p>
            <div style=\"margin:18px 0;padding:16px 20px;background:#f3f9ff;border:1px solid #cfe4ff;border-radius:12px;font-size:28px;font-weight:700;letter-spacing:6px;color:#0b5cab;text-align:center;\">{$safe_token}</div>
            <p style=\"margin:0 0 10px;color:#334155;line-height:1.6;\">OTP ini sah selama <strong>{$expiry_minutes} minit</strong>, sehingga <strong>{$expires_display}</strong>.</p>
            <p style=\"margin:0;color:#64748b;line-height:1.6;\">Jika anda tidak membuat permintaan ini, abaikan emel ini dan tandatangan anda tidak akan berubah.</p>
        </div>";

    $plain_body = ($recipient_name !== '' ? "Salam {$recipient_name},\n\n" : '')
        . "Pengesahan Tukar Tandatangan Digital\n\n"
        . "Akaun: {$email}\n"
        . "OTP: {$token}\n"
        . "Sah selama: {$expiry_minutes} minit\n"
        . "Sah sehingga: {$expires_display}\n\n"
        . "Jika anda tidak membuat permintaan ini, abaikan emel ini.";

    return sendGmailMessage($email, $recipient_name, $subject, $html_body, $plain_body);
}

function sendDraftLetterSupportRequestEmail($email, $recipient_name, $draft) {
    $draft_id = (int) ($draft['id'] ?? 0);
    $ref_no = htmlspecialchars((string) ($draft['ref_no'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $subject_text = htmlspecialchars((string) ($draft['subject'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $requester = htmlspecialchars((string) ($_SESSION['fullname'] ?? 'Sistem'), ENT_QUOTES, 'UTF-8');
    $target_path = 'draft_letter_print.php?id=' . $draft_id;
    $review_url = buildAppUrl('login.php?redirect=' . urlencode($target_path));
    $safe_review_url = htmlspecialchars($review_url, ENT_QUOTES, 'UTF-8');
    $safe_name = htmlspecialchars(trim((string) $recipient_name), ENT_QUOTES, 'UTF-8');
    $greeting = $safe_name !== '' ? "Salam {$safe_name}," : 'Salam,';

    $subject = 'Permohonan Sokongan Surat: ' . ($draft['ref_no'] ?? '');
    $html_body = "
        <div style=\"font-family:'Segoe UI','Inter',Tahoma,Geneva,Verdana,sans-serif;max-width:580px;margin:0 auto;padding:24px;border:1px solid #dbe7f3;border-radius:14px;background:#ffffff;\">
            <h2 style=\"margin:0 0 12px;color:#0b5cab;\">Permohonan Tandatangan Sokongan Surat</h2>
            <p style=\"margin:0 0 16px;color:#334155;line-height:1.6;\">{$greeting}</p>
            <p style=\"margin:0 0 16px;color:#334155;line-height:1.6;\">{$requester} telah menghantar surat untuk semakan dan sokongan anda.</p>
            <div style=\"margin:18px 0;padding:16px 18px;background:#f8fbfd;border:1px solid #dbe7f3;border-radius:12px;color:#334155;line-height:1.7;\">
                <strong>No. Rujukan:</strong> {$ref_no}<br>
                <strong>Perkara:</strong> {$subject_text}
            </div>
            <p style=\"margin:0 0 16px;\"><a href=\"{$safe_review_url}\" style=\"display:inline-block;padding:12px 18px;background:#0b5cab;color:#ffffff;text-decoration:none;border-radius:10px;font-weight:600;\">Buka Surat Dalam Sistem</a></p>
            <p style=\"margin:0;color:#64748b;line-height:1.6;word-break:break-all;\">Jika butang tidak berfungsi, salin pautan ini ke pelayar anda:<br>{$safe_review_url}</p>
        </div>";

    $plain_body = "Permohonan Tandatangan Sokongan Surat\n\n"
        . "No. Rujukan: " . ($draft['ref_no'] ?? '-') . "\n"
        . "Perkara: " . ($draft['subject'] ?? '-') . "\n"
        . "Pautan sistem: {$review_url}\n\n"
        . "Sila log masuk ke sistem untuk melihat dan memberi sokongan.";

    return sendGmailMessage($email, $recipient_name, $subject, $html_body, $plain_body);
}

function getPermissionDefinition() {
    return [
        'folder' => [
            'label' => 'Fail',
            'actions' => [
                'create' => 'Tambah',
                'close' => 'Tutup Fail',
                'read' => 'Lihat',
                'update' => 'Ubah',
                'delete' => 'Padam',
            ],
        ],
        'document' => [
            'label' => 'Dokumen',
            'actions' => [
                'create' => 'Tambah',
                'read' => 'Lihat',
                'update' => 'Ubah',
                'delete' => 'Padam',
                'download' => 'Download',
            ],
        ],
        'draft_letter' => [
            'label' => 'Surat',
            'actions' => [
                'create' => 'Tambah',
                'read' => 'Lihat',
                'update' => 'Ubah',
                'delete' => 'Padam',
            ],
        ],
        'deleted_item' => [
            'label' => 'Data Dipadam',
            'actions' => [
                'read' => 'Lihat',
                'restore' => 'Pulih',
            ],
        ],
        'uat' => [
            'label' => 'UAT',
            'actions' => [
                'read' => 'Isi UAT',
                'view_all' => 'Lihat Semua Pengguna',
            ],
        ],
    ];
}

function getAssignableRoles() {
    return [
        'admin' => 'Admin',
        'user' => 'User',
    ];
}

function isBaselineRolePermission($role, $module, $action) {
    $role = strtolower(trim((string) $role));
    $module = strtolower(trim((string) $module));
    $action = strtolower(trim((string) $action));

    if ($role === 'admin'
        && (
            ($module === 'folder' && in_array($action, ['close', 'delete'], true))
            || ($module === 'document' && $action === 'delete')
        )
    ) {
        return true;
    }

    return in_array($role, ['admin', 'user'], true)
        && in_array($module, ['folder', 'document', 'draft_letter'], true)
        && in_array($action, ['create', 'read'], true);
}

function getPermissionDefaultMap() {
    return [
        'admin' => [
            'folder' => [
                'create' => 1,
                'close' => 1,
                'read' => 1,
                'update' => 0,
                'delete' => 1,
            ],
            'document' => [
                'create' => 1,
                'read' => 1,
                'update' => 0,
                'delete' => 1,
                'download' => 0,
            ],
            'draft_letter' => [
                'create' => 1,
                'read' => 1,
                'update' => 1,
                'delete' => 0,
            ],
            'deleted_item' => [
                'read' => 0,
                'restore' => 0,
            ],
            'uat' => [
                'read' => 1,
                'view_all' => 0,
            ],
        ],
        'user' => [
            'folder' => [
                'create' => 1,
                'close' => 0,
                'read' => 1,
                'update' => 0,
                'delete' => 0,
            ],
            'document' => [
                'create' => 1,
                'read' => 1,
                'update' => 0,
                'delete' => 0,
                'download' => 1,
            ],
            'draft_letter' => [
                'create' => 1,
                'read' => 1,
                'update' => 1,
                'delete' => 0,
            ],
            'deleted_item' => [
                'read' => 0,
                'restore' => 0,
            ],
            'uat' => [
                'read' => 1,
                'view_all' => 0,
            ],
        ],
    ];
}

function normalizeUploadedFiles($input_name = 'file_upload') {
    if (empty($_FILES[$input_name])) {
        return [];
    }

    $file_input = $_FILES[$input_name];
    $normalized = [];

    if (is_array($file_input['name'])) {
        $count = count($file_input['name']);
        for ($i = 0; $i < $count; $i++) {
            if (($file_input['error'][$i] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE || trim((string) ($file_input['name'][$i] ?? '')) === '') {
                continue;
            }

            $normalized[] = [
                'name' => $file_input['name'][$i] ?? '',
                'type' => $file_input['type'][$i] ?? '',
                'tmp_name' => $file_input['tmp_name'][$i] ?? '',
                'error' => $file_input['error'][$i] ?? UPLOAD_ERR_NO_FILE,
                'size' => $file_input['size'][$i] ?? 0,
            ];
        }
    } else {
        if (($file_input['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_NO_FILE && trim((string) ($file_input['name'] ?? '')) !== '') {
            $normalized[] = $file_input;
        }
    }

    return $normalized;
}

function saveDocumentAttachments($conn, $document_id, $folder_id, $files) {
    if (empty($files)) {
        return ['count' => 0, 'first_path' => null];
    }

    $upload_dir = 'uploads/documents/';
    if (!file_exists($upload_dir)) {
        mkdir($upload_dir, 0755, true);
    }

    $allowed_ext = ['pdf', 'doc', 'docx', 'xls', 'xlsx'];
    $moved_files = [];
    $first_path = null;

    try {
        $insert = mysqli_prepare($conn, "INSERT INTO document_attachments (document_id, file_path, original_name) VALUES (?, ?, ?)");
        if (!$insert) {
            throw new Exception('Gagal menyediakan penyimpanan attachment.');
        }

        foreach ($files as $file) {
            if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
                throw new Exception('Muat naik salah satu lampiran gagal.');
            }

            $original_name = basename((string) ($file['name'] ?? ''));
            $file_ext = strtolower(pathinfo($original_name, PATHINFO_EXTENSION));

            if (!in_array($file_ext, $allowed_ext, true)) {
                throw new Exception("Format fail {$file_ext} tidak dibenarkan.");
            }

            if ((int) ($file['size'] ?? 0) > 10 * 1024 * 1024) {
                throw new Exception("Saiz fail {$original_name} melebihi 10MB.");
            }

            $new_filename = 'doc_' . $folder_id . '_' . $document_id . '_' . time() . '_' . uniqid() . '.' . $file_ext;
            $upload_path = $upload_dir . $new_filename;

            if (!move_uploaded_file($file['tmp_name'], $upload_path)) {
                throw new Exception("Gagal memuat naik fail {$original_name}.");
            }

            $moved_files[] = $upload_path;
            if ($first_path === null) {
                $first_path = $upload_path;
            }

            mysqli_stmt_bind_param($insert, "iss", $document_id, $upload_path, $original_name);
            if (!mysqli_stmt_execute($insert)) {
                throw new Exception('Gagal menyimpan maklumat attachment.');
            }
        }
    } catch (Exception $e) {
        foreach ($moved_files as $moved_file) {
            if (file_exists($moved_file)) {
                unlink($moved_file);
            }
        }
        throw $e;
    }

    return [
        'count' => count($moved_files),
        'first_path' => $first_path,
    ];
}

function getDocumentAttachmentsMap($conn, $documents) {
    $map = [];
    if (empty($documents)) {
        return $map;
    }

    $doc_ids = [];
    $legacy_paths = [];

    foreach ($documents as $document) {
        $doc_id = (int) ($document['id'] ?? 0);
        if ($doc_id <= 0) {
            continue;
        }

        $doc_ids[] = $doc_id;
        $legacy_paths[$doc_id] = $document['file_path'] ?? null;
        $map[$doc_id] = [];
    }

    if (empty($doc_ids)) {
        return $map;
    }

    $placeholders = implode(',', array_fill(0, count($doc_ids), '?'));
    $types = str_repeat('i', count($doc_ids));
    $stmt = mysqli_prepare($conn, "SELECT id, document_id, file_path, original_name, is_replacement_letter, created_at FROM document_attachments WHERE document_id IN ($placeholders) ORDER BY is_replacement_letter DESC, id ASC");

    if ($stmt) {
        mysqli_stmt_bind_param($stmt, $types, ...$doc_ids);
        mysqli_stmt_execute($stmt);
        $rows = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);

        foreach ($rows as $row) {
            $doc_id = (int) $row['document_id'];
            $map[$doc_id][] = $row;
        }
    }

    foreach ($legacy_paths as $doc_id => $legacy_path) {
        if (empty($legacy_path)) {
            continue;
        }

        $already_listed = false;
        foreach ($map[$doc_id] as $attachment) {
            if (($attachment['file_path'] ?? '') === $legacy_path) {
                $already_listed = true;
                break;
            }
        }

        if (!$already_listed) {
            $insert_stmt = mysqli_prepare($conn, "INSERT INTO document_attachments (document_id, file_path, original_name) VALUES (?, ?, ?)");
            if ($insert_stmt) {
                $original_name = basename($legacy_path);
                mysqli_stmt_bind_param($insert_stmt, "iss", $doc_id, $legacy_path, $original_name);
                if (mysqli_stmt_execute($insert_stmt)) {
                    $map[$doc_id][] = [
                        'id' => mysqli_insert_id($conn),
                        'document_id' => $doc_id,
                        'file_path' => $legacy_path,
                        'original_name' => $original_name,
                        'is_replacement_letter' => 0,
                        'created_at' => null,
                    ];
                    continue;
                }
            }

            $map[$doc_id][] = [
                'id' => 0,
                'document_id' => $doc_id,
                'file_path' => $legacy_path,
                'original_name' => basename($legacy_path),
                'is_replacement_letter' => 0,
                'created_at' => null,
                'is_legacy' => 1,
            ];
        }
    }

    return $map;
}

function syncDocumentPrimaryFilePath($conn, $document_id) {
    $stmt = mysqli_prepare($conn, "SELECT file_path FROM document_attachments WHERE document_id = ? ORDER BY id ASC LIMIT 1");
    if (!$stmt) {
        return false;
    }

    mysqli_stmt_bind_param($stmt, "i", $document_id);
    mysqli_stmt_execute($stmt);
    $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));
    $primary_file_path = $row['file_path'] ?? null;

    $update = mysqli_prepare($conn, "UPDATE documents SET file_path = ? WHERE id = ?");
    if (!$update) {
        return false;
    }

    mysqli_stmt_bind_param($update, "si", $primary_file_path, $document_id);
    return mysqli_stmt_execute($update);
}

function logActivity($conn, $user_id, $activity_type, $description = '') {
    $user_email = $_SESSION['email'] ?? 'unknown';
    $user_name = $_SESSION['fullname'] ?? 'Unknown User';
    $user_role = $_SESSION['role'] ?? 'user';
    $ip_address = $_SERVER['REMOTE_ADDR'] ?? '';
    $user_agent = substr($_SERVER['HTTP_USER_AGENT'] ?? '', 0, 255);

    $stmt = mysqli_prepare($conn, "INSERT INTO activity_logs (user_id, user_email, user_name, user_role, activity_type, activity_description, ip_address, user_agent) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    if ($stmt) {
        mysqli_stmt_bind_param($stmt, "isssssss", $user_id, $user_email, $user_name, $user_role, $activity_type, $description, $ip_address, $user_agent);
        return mysqli_stmt_execute($stmt);
    }

    return false;
}

function logActivityGuest($conn, $activity_type, $description = '') {
    $ip_address = $_SERVER['REMOTE_ADDR'] ?? '';
    $user_agent = substr($_SERVER['HTTP_USER_AGENT'] ?? '', 0, 255);

    $stmt = mysqli_prepare($conn, "INSERT INTO activity_logs (user_id, user_email, user_name, user_role, activity_type, activity_description, ip_address, user_agent) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    if ($stmt) {
        $null_id = 0;
        $guest_email = 'guest';
        $guest_name = 'Guest';
        $guest_role = 'guest';
        mysqli_stmt_bind_param($stmt, "isssssss", $null_id, $guest_email, $guest_name, $guest_role, $activity_type, $description, $ip_address, $user_agent);
        return mysqli_stmt_execute($stmt);
    }

    return false;
}

function hasPermission($conn, $role, $module, $action) {
    $role = strtolower(trim((string) $role));
    $module = strtolower(trim((string) $module));
    $action = strtolower(trim((string) $action));

    if ($role === 'superadmin') {
        return true;
    }

    if (isBaselineRolePermission($role, $module, $action)) {
        return true;
    }

    $stmt = mysqli_prepare($conn, "SELECT is_allowed FROM role_permissions WHERE role_name = ? AND module = ? AND action = ?");
    if ($stmt) {
        mysqli_stmt_bind_param($stmt, "sss", $role, $module, $action);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        $row = mysqli_fetch_assoc($result);

        if ($row !== null) {
            return (int) ($row['is_allowed'] ?? 0) === 1;
        }
    }

    $defaults = getPermissionDefaultMap();
    return !empty($defaults[$role][$module][$action]);
}

function hasDepartmentPermission($conn, $department, $module, $action, $fallback_role = 'user') {
    $department = normalizeDepartmentValue($department);
    $module = strtolower(trim((string) $module));
    $action = strtolower(trim((string) $action));
    $fallback_role = strtolower(trim((string) $fallback_role));

    if ($fallback_role === 'superadmin') {
        return true;
    }

    if ($department === '') {
        return hasPermission($conn, $fallback_role, $module, $action);
    }

    $stmt = mysqli_prepare($conn, "SELECT is_allowed FROM department_permissions WHERE department = ? AND module = ? AND action = ? LIMIT 1");
    if ($stmt) {
        mysqli_stmt_bind_param($stmt, "sss", $department, $module, $action);
        mysqli_stmt_execute($stmt);
        $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));

        if ($row !== null) {
            return (int) ($row['is_allowed'] ?? 0) === 1;
        }
    }

    return hasPermission($conn, $fallback_role, $module, $action);
}

function currentUserCanStrict($conn, $module, $action) {
    return hasUserPermission(
        $conn,
        (int) ($_SESSION['user_id'] ?? 0),
        $module,
        $action,
        currentUserDepartment($conn),
    );
}

function currentUserCan($conn, $module, $action) {
    if (function_exists('isAccessPreviewRequest') && isAccessPreviewRequest()) {
        return true;
    }

    return currentUserCanStrict($conn, $module, $action);
}

function hasUserPermission($conn, $user_id, $module, $action, $user_department) {
    $current_role = $_SESSION['role'] ?? 'user';
    $user_id = (int) $user_id;
    $module = strtolower(trim((string) $module));
    $action = strtolower(trim((string) $action));

    if ($current_role === 'superadmin') {
        return true;
    }

    if ($current_role === 'admin'
        && (
            ($module === 'folder' && in_array($action, ['close', 'delete'], true))
            || ($module === 'document' && $action === 'delete')
        )
    ) {
        return true;
    }

    if ($user_id > 0) {
        $specific_permission = hasUserSpecificPermission($conn, $user_id, $module, $action);
        if ($specific_permission !== null) {
            return $specific_permission;
        }
    }

    return hasDepartmentPermission($conn, $user_department, $module, $action, $current_role);
}

function hasUserSpecificPermission($conn, $user_id, $module, $action) {
    $user_id = (int) $user_id;
    $module = strtolower(trim((string) $module));
    $action = strtolower(trim((string) $action));

    if ($user_id <= 0 || $module === '' || $action === '') {
        return null;
    }

    $stmt = mysqli_prepare($conn, "SELECT is_allowed FROM user_permissions WHERE user_id = ? AND module = ? AND action = ? LIMIT 1");
    if (!$stmt) {
        return null;
    }

    mysqli_stmt_bind_param($stmt, "iss", $user_id, $module, $action);
    mysqli_stmt_execute($stmt);
    $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));

    if ($row === null) {
        return null;
    }

    return (int) ($row['is_allowed'] ?? 0) === 1;
}

function renderCustomFields($conn, $module, $values = []) {
    $sql = "SELECT * FROM custom_fields WHERE module = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return '';
    }

    mysqli_stmt_bind_param($stmt, "s", $module);
    mysqli_stmt_execute($stmt);
    $fields = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);

    if (empty($fields)) {
        return '';
    }

    $html = '';
    foreach ($fields as $field) {
        $required = !empty($field['is_required']) ? 'required' : '';
        $required_star = !empty($field['is_required']) ? '<span style="color:var(--danger);">*</span>' : '';
        $label = htmlspecialchars($field['field_label'] ?? '');
        $field_key = (string) ($field['field_key'] ?? '');
        $key = 'custom_' . htmlspecialchars($field_key);
        $type = $field['field_type'] ?? 'text';
        $placeholder = htmlspecialchars($field['field_label'] ?? '');
        $current_value = htmlspecialchars((string) ($values[$field_key] ?? ''), ENT_QUOTES, 'UTF-8');

        $html .= "<div class='form-group'>";
        $html .= "<label>{$label} {$required_star}</label>";

        if ($type === 'textarea') {
            $html .= "<textarea name='{$key}' {$required} placeholder='{$placeholder}' rows='3' style='width:100%;padding:11px 14px;border:1px solid var(--border);border-radius:8px;font-size:0.95rem;'>{$current_value}</textarea>";
        } elseif ($type === 'select') {
            $html .= "<select name='{$key}' {$required} style='width:100%;padding:11px 14px;border:1px solid var(--border);border-radius:8px;font-size:0.95rem;'>";
            $html .= "<option value=''>-- Pilih --</option>";
            foreach (parseCustomFieldOptions($field['field_options'] ?? '') as $option) {
                $option_value = htmlspecialchars($option['value'], ENT_QUOTES, 'UTF-8');
                $option_label = htmlspecialchars($option['label'], ENT_QUOTES, 'UTF-8');
                $selected = ((string) ($values[$field_key] ?? '') === $option['value']) ? ' selected' : '';
                $html .= "<option value='{$option_value}'{$selected}>{$option_label}</option>";
            }
            $html .= "</select>";
        } else {
            $html .= "<input type='{$type}' name='{$key}' value='{$current_value}' {$required} placeholder='{$placeholder}' style='width:100%;padding:11px 14px;border:1px solid var(--border);border-radius:8px;font-size:0.95rem;'>";
        }

        $html .= "</div>";
    }

    return $html;
}

function saveCustomData($conn, $module, $record_id) {
    $data = [];

    $sql = "SELECT field_key FROM custom_fields WHERE module = ? AND is_active = 1";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return false;
    }

    mysqli_stmt_bind_param($stmt, "s", $module);
    mysqli_stmt_execute($stmt);
    $fields = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);

    if (empty($fields)) {
        return true;
    }

    foreach ($fields as $field) {
        $key = 'custom_' . $field['field_key'];
        if (isset($_POST[$key]) && trim((string) $_POST[$key]) !== '') {
            $data[$field['field_key']] = trim((string) $_POST[$key]);
        }
    }

    $json = !empty($data) ? json_encode($data, JSON_UNESCAPED_UNICODE) : null;
    $table = $module === 'folder' ? 'folders' : 'documents';
    $update = mysqli_prepare($conn, "UPDATE {$table} SET custom_data = ? WHERE id = ?");
    if ($update) {
        mysqli_stmt_bind_param($update, "si", $json, $record_id);
        return mysqli_stmt_execute($update);
    }

    return false;
}

function loadCustomData($conn, $module, $record_id) {
    $table = $module === 'folder' ? 'folders' : 'documents';
    $stmt = mysqli_prepare($conn, "SELECT custom_data FROM {$table} WHERE id = ?");
    if (!$stmt) {
        return [];
    }

    mysqli_stmt_bind_param($stmt, "i", $record_id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_assoc($result);

    if ($row && !empty($row['custom_data'])) {
        return json_decode($row['custom_data'], true) ?: [];
    }

    return [];
}

function getCustomFieldValue($custom_data, $field_key, $default = '') {
    return isset($custom_data[$field_key]) ? htmlspecialchars((string) $custom_data[$field_key]) : $default;
}

function parseCustomFieldOptions($raw_options) {
    $raw_options = trim((string) $raw_options);
    if ($raw_options === '') {
        return [];
    }

    $separator = strpos($raw_options, "\n") !== false ? "\n" : ',';
    $parts = array_map('trim', explode($separator, str_replace(["\r\n", "\r"], "\n", $raw_options)));
    $options = [];

    foreach ($parts as $part) {
        if ($part === '') {
            continue;
        }

        $value = $part;
        $label = $part;

        if (strpos($part, '|') !== false) {
            [$value, $label] = array_map('trim', explode('|', $part, 2));
        }

        if ($value === '') {
            continue;
        }

        $options[] = [
            'value' => $value,
            'label' => $label !== '' ? $label : $value,
        ];
    }

    return $options;
}

function getUsersByDepartment($conn, $department = null) {
    if ($department) {
        $sql = "SELECT id, fullname, email, role, department FROM users WHERE department = ? AND status = 'approved' ORDER BY fullname";
        $stmt = mysqli_prepare($conn, $sql);
        mysqli_stmt_bind_param($stmt, "s", $department);
        mysqli_stmt_execute($stmt);
    } else {
        $sql = "SELECT id, fullname, email, role, department FROM users WHERE status = 'approved' ORDER BY department, fullname";
        $stmt = mysqli_prepare($conn, $sql);
        mysqli_stmt_execute($stmt);
    }

    return mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
}

function getDepartmentList() {
    return [
        'Pengurusan',
        'Bahagian Pentadbiran Sumber Manusia',
        'Bahagian Undang-Undang',
        'Unit Pengurusan dan Penyelenggaraan Premis',
        'Unit Pentadbiran Tanah',
        'Bahagian Kewangan Dan Akaun',
        'Unit Pengurusan Dana Wakaf',
        'Bahagian Audit Dalam',
        'Unit Pembangunan',
        'Unit Latihan',
        'Bahagian Teknologi Maklumat'
    ];
}

function getDepartmentPositionMap() {
    $management_positions = [
        'YDP MAINPP',
        'Timbalan YDP',
        'Setiausaha MAINPP',
        'Pengurus Baitulmal',
        'Setiausaha Pejabat N6 (TBK 1)',
        'Pegawai Khas',
    ];

    return [
        'Pengurusan' => $management_positions,
        'Bahagian Pentadbiran Sumber Manusia' => [
            'Pegawai Tadbir N9',
            'Penolong Pegawai Tadbir N7',
            'Penolong Pegawai Komunikasi & Korporat N5',
            'Pembantu Tadbir N2',
            'Pembantu Tadbir N1',
            'Pembantu Khidmat Am H1',
            'Personel MySTEP',
        ],
        'Bahagian Kewangan Dan Akaun' => [
            'Ketua Bahagian Kewangan dan Akaun',
            'Akauntan WA9',
            'Penolong Akauntan W5',
            'Pembantu Tadbir N2',
            'Pembantu Tadbir N2 (TBK 1)',
            'Pembantu Tadbir N1',
        ],
        'Bahagian Teknologi Maklumat' => [
            'Pegawai Teknologi Maklumat F9',
            'Penolong Pegawai Teknologi Maklumat FA5',
        ],
        'Bahagian Undang-Undang' => [
            'Pegawai Undang-Undang L12',
            'Pegawai Undang-Undang L9',
            'Penolong Pegawai Undang-Undang L5',
            'Pegawai Tadbir N5',
            'Pembantu Tadbir N1',
        ],
        'Bahagian Audit Dalam' => [
            'Juruaudit W9',
            'Penolong Juruaudit W5',
        ],
        'Unit Pengurusan dan Penyelenggaraan Premis' => [
            'Pegawai Tadbir N10',
            'Penolong Pegawai Tadbir N5',
            'Pembantu Tadbir N1',
        ],
        'Unit Pentadbiran Tanah' => [
            'Pegawai Tadbir N10',
            'Penolong Pegawai Tadbir N6',
            'Penolong Pegawai Tanah NT5',
            'Pembantu Tadbir N1',
        ],
        'Unit Pembangunan' => [
            'Jurutera J9',
            'Pegawai Tadbir N9',
            'Penolong Pegawai Tadbir N5',
            'Penolong Jurutera JA5',
            'Pembantu Tadbir N1',
        ],
        'Unit Latihan' => [
            'Pegawai Tadbir N10',
            'Pembantu Tadbir N1',
        ],
        'Unit Pengurusan Dana Wakaf' => [
            'Pegawai Tadbir N5',
            'Pembantu Tadbir N1',
        ],
    ];
}

function getDepartmentPositions($department) {
    $department = normalizeDepartmentValue($department);
    $map = getDepartmentPositionMap();
    return $map[$department] ?? [];
}

function isValidDepartmentPosition($department, $position) {
    $position = trim((string) $position);
    return $position !== '' && in_array($position, getDepartmentPositions($department), true);
}

function isOfficerPosition($position) {
    $position = strtolower(trim((string) $position));
    if ($position === '') {
        return false;
    }

    foreach (['pegawai', 'pengurus', 'ketua', 'ydp', 'setiausaha', 'juruaudit', 'akauntan', 'jurutera'] as $keyword) {
        if (strpos($position, $keyword) !== false) {
            return true;
        }
    }

    return false;
}

function isHeadOfficerPosition($position) {
    $position = preg_replace('/\s+/', ' ', trim((string) $position));
    $position_lower = strtolower($position);
    if ($position_lower === '') {
        return false;
    }

    foreach (['penolong', 'pembantu', 'personel', 'mystep'] as $non_head_keyword) {
        if (strpos($position_lower, $non_head_keyword) !== false) {
            return false;
        }
    }

    foreach (['pegawai ', 'ketua bahagian', 'ketua pegawai'] as $head_prefix) {
        if (strpos($position_lower, $head_prefix) === 0) {
            return true;
        }
    }

    foreach (['pengurus', 'ydp', 'setiausaha', 'juruaudit', 'akauntan', 'jurutera'] as $head_keyword) {
        if (strpos($position_lower, $head_keyword) !== false) {
            return true;
        }
    }

    return $position_lower === 'pegawai';
}

function getFolderTypeList() {
    return [
        'office',
        'sendirian'
    ];
}

function normalizeFolderType($folder_type) {
    $folder_type = strtolower(trim((string) $folder_type));
    return $folder_type === 'sendirian' ? 'sendirian' : 'office';
}

function getFolderTypeLabel($folder_type) {
    return normalizeFolderType($folder_type) === 'sendirian' ? 'Personal' : 'Office';
}

function normalizeDepartmentValue($department) {
    $department = preg_replace('/\s+/', ' ', trim((string) $department));
    $department_key = strtolower(str_replace('&', 'dan', $department));
    $department_key = preg_replace('/\s+/', ' ', $department_key);

    if (in_array($department_key, ['bahagian pengurusan', 'unit pengurusan', 'pengurusan'], true)) {
        return 'Pengurusan';
    }

    if ($department_key === 'bahagian kewangan dan akaun') {
        return 'Bahagian Kewangan Dan Akaun';
    }

    return $department;
}

function isSameDepartment($left, $right) {
    return normalizeDepartmentValue($left) === normalizeDepartmentValue($right);
}

function isSameDepartmentDb($conn, $left, $right) {
    return isSameDepartment($left, $right);
}

function currentUserDepartment($conn) {
    $session_department = normalizeDepartmentValue($_SESSION['department'] ?? '');
    $user_id = (int) ($_SESSION['user_id'] ?? 0);

    if ($user_id <= 0) {
        return $session_department;
    }

    $stmt = mysqli_prepare($conn, "SELECT department FROM users WHERE id = ? LIMIT 1");
    if (!$stmt) {
        return $session_department;
    }

    mysqli_stmt_bind_param($stmt, "i", $user_id);
    mysqli_stmt_execute($stmt);
    $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));
    $db_department = normalizeDepartmentValue($row['department'] ?? '');

    if ($db_department !== '') {
        $_SESSION['department'] = $db_department;
        return $db_department;
    }

    return $session_department;
}

function normalizeOfficeFolderBaseCode($folder_code) {
    $folder_code = strtoupper(trim((string) $folder_code));
    $folder_code = preg_replace('/\s+/', ' ', $folder_code);
    $folder_code = preg_replace('/\s+Jld\.\s*\d+$/i', '', $folder_code);

    return trim($folder_code);
}

function isValidOfficeFolderBaseCode($folder_code) {
    $folder_code = normalizeOfficeFolderBaseCode($folder_code);
    return preg_match('/^MAINPP\/[A-Z]{2,10}[\/.]\d{3}-\d{1,2}\/\d+\/\d+$/', $folder_code) === 1;
}

function isValidOfficeFolderCode($folder_code) {
    $folder_code = strtoupper(trim((string) $folder_code));
    return isValidOfficeFolderBaseCode($folder_code)
        || preg_match('/^MAINPP\/[A-Z]{2,10}[\/.]\d{3}-\d{1,2}\/\d+\/\d+\s+Jld\.\d+$/', $folder_code) === 1;
}

function getOfficialFolderNameByCode($conn, $folder_code) {
    $base_code = normalizeOfficeFolderBaseCode($folder_code);
    if (!isValidOfficeFolderBaseCode($base_code)) {
        return '';
    }

    $like = $base_code . ' Jld.%';
    $stmt = mysqli_prepare($conn, "SELECT folder_name FROM folders WHERE (folder_code = ? OR folder_code LIKE ?) AND folder_name IS NOT NULL AND TRIM(folder_name) != '' ORDER BY id ASC LIMIT 1");
    if (!$stmt) {
        return '';
    }

    mysqli_stmt_bind_param($stmt, "ss", $base_code, $like);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_assoc($result);

    return trim((string) ($row['folder_name'] ?? ''));
}

function getOfficeFolderJilidInfo($conn, $input_code) {
    $input_code = strtoupper(trim((string) $input_code));
    $input_code = preg_replace('/\s+/', ' ', $input_code);
    $base_code = normalizeOfficeFolderBaseCode($input_code);
    $official_name = getOfficialFolderNameByCode($conn, $base_code);

    if (!isValidOfficeFolderBaseCode($base_code)) {
        return [
            'status' => 'invalid',
            'message' => 'Format kod rujukan fail office tidak sah.',
            'base_code' => $base_code,
            'suggested_code' => $input_code,
            'active_code' => '',
            'has_official_name' => !empty($official_name),
            'official_name' => $official_name
        ];
    }

    $like = $base_code . ' Jld.%';
    $stmt = mysqli_prepare($conn, "SELECT folder_code, closed_at, deleted_at FROM folders WHERE folder_code = ? OR folder_code LIKE ?");
    if (!$stmt) {
        return [
            'status' => 'error',
            'message' => 'Ralat pangkalan data.',
            'base_code' => $base_code,
            'suggested_code' => $input_code,
            'active_code' => '',
            'has_official_name' => !empty($official_name),
            'official_name' => $official_name
        ];
    }

    mysqli_stmt_bind_param($stmt, "ss", $base_code, $like);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);

    $has_active = false;
    $active_code = '';
    $has_existing_closed = false;
    $max_jilid = 0;

    foreach ($rows as $row) {
        $code = strtoupper(trim((string) ($row['folder_code'] ?? '')));
        $is_closed = !empty($row['closed_at']);
        $is_deleted = !empty($row['deleted_at']);

        if (!$is_closed && !$is_deleted) {
            $has_active = true;
            $active_code = $row['folder_code'];
        } else {
            $has_existing_closed = true;
        }

        if ($code === $base_code) {
            $max_jilid = max($max_jilid, 1);
        } elseif (preg_match('/\s+Jld\.(\d+)$/i', $code, $matches)) {
            $jilid_num = (int) $matches[1];
            $max_jilid = max($max_jilid, $jilid_num);
        }
    }

    if ($has_active) {
        return [
            'status' => 'active',
            'message' => "Fail terdahulu dengan kod ('" . $active_code . "') masih aktif. Sila tutup fail tersebut sebelum membuat jilid baharu.",
            'base_code' => $base_code,
            'suggested_code' => '',
            'active_code' => $active_code,
            'has_official_name' => !empty($official_name),
            'official_name' => $official_name
        ];
    }

    if ($has_existing_closed || $max_jilid > 0) {
        $next_jilid = $max_jilid + 1;
        $suggested_code = $base_code . ' Jld.' . $next_jilid;
        return [
            'status' => 'suggested',
            'message' => "Nombor Jilid baharu telah dijana secara automatik (Jld." . $next_jilid . ").",
            'base_code' => $base_code,
            'suggested_code' => $suggested_code,
            'active_code' => '',
            'has_official_name' => !empty($official_name),
            'official_name' => $official_name
        ];
    }

    // Tiada rekod fail terdahulu - kekalkan kod input jika ia mempunyai Jld atau kod asas
    $formatted_input = preg_replace('/\s+JLD\.(\d+)$/i', ' Jld.$1', $input_code);
    return [
        'status' => 'new',
        'message' => 'Kod fail baharu.',
        'base_code' => $base_code,
        'suggested_code' => $formatted_input,
        'active_code' => '',
        'has_official_name' => !empty($official_name),
        'official_name' => $official_name
    ];
}

function getNextOfficeFolderJilidCode($conn, $base_code) {
    $info = getOfficeFolderJilidInfo($conn, $base_code);
    if ($info['status'] === 'active') {
        return '';
    }
    return $info['suggested_code'];
}


function getDocumentTypeList() {
    return [
        'Memo Kelulusan',
        'Memo',
        'Surat Rasmi'
    ];
}

function getMemoPositionOptions() {
    $map = getDepartmentPositionMap();
    $options = [];

    foreach ($map as $department => $positions) {
        $department = normalizeDepartmentValue($department);
        $group = $department === 'Pengurusan' ? 'Pengurusan' : 'Ketua Bahagian';

        foreach ($positions as $position) {
            $position = trim((string) $position);
            if ($position === '') {
                continue;
            }

            $include = $department === 'Pengurusan' || isHeadOfficerPosition($position);

            if (!$include) {
                continue;
            }

            $label = $position;
            if ($department !== 'Pengurusan' && isHeadOfficerPosition($position)) {
                $label = getMemoDepartmentHeadPosition($department) ?: ('Ketua ' . $department);
            }

            $options[$group][$label] = $label;
        }
    }

    foreach ($options as $group => $values) {
        $options[$group] = array_values($values);
        sort($options[$group], SORT_NATURAL | SORT_FLAG_CASE);
    }

    return $options;
}

function getMemoDepartmentHeadPosition($department) {
    $department = normalizeDepartmentValue($department);

    if ($department === 'Pengurusan') {
        return '';
    }

    foreach (getDepartmentPositions($department) as $position) {
        if (stripos($position, 'Ketua Bahagian') === 0 || stripos($position, 'Ketua Pegawai') === 0) {
            return $position;
        }
    }

    if (stripos($department, 'Bahagian ') === 0) {
        return 'Ketua ' . $department;
    }

    if (stripos($department, 'Unit ') === 0) {
        return 'Ketua ' . $department;
    }

    if ($department !== '') {
        return 'Ketua ' . $department;
    }

    return '';
}

function getDisplayPositionForDepartment($department, $position) {
    $department = normalizeDepartmentValue($department);
    $position = trim((string) $position);

    if ($position === '') {
        return '';
    }

    if ($department !== 'Pengurusan' && isHeadOfficerPosition($position)) {
        return getMemoDepartmentHeadPosition($department) ?: $position;
    }

    return $position;
}

function getDisplayPositionForUser($user, $fallback_department = '') {
    if (!$user) {
        return '';
    }

    $department = trim((string) (($user['department'] ?? '') ?: $fallback_department));
    return getDisplayPositionForDepartment($department, $user['position'] ?? '');
}

function getMemoRoutingPositionForUser($user) {
    $department = normalizeDepartmentValue($user['department'] ?? '');
    $position = trim((string) (($user['actual_position'] ?? '') ?: ($user['position'] ?? '')));

    if ($department === 'Pengurusan') {
        return $position;
    }

    if (isHeadOfficerPosition($position)) {
        return getMemoDepartmentHeadPosition($department);
    }

    return '';
}

function getMemoRoutingUsers($conn) {
    $stmt = mysqli_prepare($conn, "SELECT id, fullname, email, department, position, role
        FROM users
        WHERE status = 'approved'
        ORDER BY department, fullname");
    if (!$stmt) {
        return [];
    }

    mysqli_stmt_execute($stmt);
    $users = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
    $routing_users = [];

    foreach ($users as $user) {
        $routing_position = getMemoRoutingPositionForUser($user);
        if ($routing_position === '') {
            continue;
        }
        if (normalizeDepartmentValue($user['department'] ?? '') !== 'Pengurusan' && isHeadOfficerPosition($user['position'] ?? '')) {
            $user['actual_position'] = $user['position'] ?? '';
            $user['position'] = getDisplayPositionForUser($user);
        }
        $user['routing_position'] = $routing_position;
        $routing_users[] = $user;
    }

    return $routing_users;
}

function getLetterApprovalRecipients($conn, $draft_id, $user_id = null) {
    $draft_id = (int) $draft_id;
    if ($draft_id <= 0) {
        return [];
    }

    $sql = "SELECT lar.*, u.fullname, u.email, u.department, u.position
        FROM letter_approval_recipients lar
        INNER JOIN letter_drafts ld ON ld.id = lar.draft_id AND ld.deleted_at IS NULL
        INNER JOIN users u ON u.id = lar.user_id
        WHERE lar.draft_id = ?";
    $types = 'i';
    $params = [$draft_id];

    if ($user_id !== null) {
        $sql .= " AND lar.user_id = ?";
        $types .= 'i';
        $params[] = (int) $user_id;
    }

    $sql .= " ORDER BY lar.route_order, lar.id";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return [];
    }

    mysqli_stmt_bind_param($stmt, $types, ...$params);
    mysqli_stmt_execute($stmt);
    return mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
}

function getDraftLetterStatusList() {
    return [
        'draf' => 'Draf',
        'siap' => 'Siap Untuk Dihantar',
    ];
}

function getDraftLetterSupportStatusList() {
    return [
        'tidak_perlu' => 'Tidak Perlu Sokongan',
        'menunggu' => 'Menunggu Sokongan',
        'disokong' => 'Disokong',
        'ditolak' => 'Ditolak',
    ];
}

function getDraftLetterApprovalStatusList() {
    return [
        'draft' => 'Belum Dihantar Kelulusan',
        'menunggu_pegawai_bahagian' => 'Menunggu Ketua Pegawai',
        'perlu_pindaan_penyedia' => 'Perlu Pindaan oleh Penyedia',
        'sedang_dipinda_ketua_bahagian' => 'Sedang Dipinda oleh Ketua Bahagian',
        'menunggu_pelulus' => 'Menunggu Kelulusan',
        'menunggu_pelulus_akhir' => 'Menunggu Kelulusan',
        'lulus' => 'Lulus',
        'ditolak' => 'Ditolak',
    ];
}

function canDraftLetterBePrinted($conn, $draft, $approval_recipients = null, $current_user_id = 0) {
    if (!is_array($draft)) {
        return false;
    }

    $current_user_id = (int) $current_user_id;
    $created_by = (int) ($draft['created_by'] ?? 0);
    if ($current_user_id <= 0 || $created_by <= 0 || $current_user_id !== $created_by) {
        return false;
    }

    return true;
}

function decodeDraftLetterPages($letter_pages, $letter_body = '') {
    $pages = [];
    $decoded = json_decode((string) $letter_pages, true);
    if (is_array($decoded)) {
        foreach ($decoded as $page) {
            $page = trim((string) $page);
            if ($page !== '') {
                $pages[] = $page;
            }
        }
    }

    if (empty($pages)) {
        $body = trim((string) $letter_body);
        if ($body !== '') {
            $pages[] = $body;
        }
    }

    return !empty($pages) ? $pages : [''];
}

function decodeDraftLetterPageSigners($letter_page_signers, $page_count = 1) {
    $allowed = ['preparer', 'department_officer'];
    $decoded = json_decode((string) $letter_page_signers, true);
    $signers = [];
    $page_count = max(1, (int) $page_count);

    for ($index = 0; $index < $page_count; $index++) {
        $value = is_array($decoded) ? (string) ($decoded[$index] ?? '') : '';
        $signers[] = in_array($value, $allowed, true) ? $value : ($index === 0 ? 'preparer' : '');
    }

    return $signers;
}

function decodeMemoRoutingPositions($routing_positions) {
    $decoded = json_decode((string) $routing_positions, true);
    if (!is_array($decoded)) {
        return [];
    }

    $positions = [];
    foreach ($decoded as $position) {
        $position = trim((string) $position);
        if ($position !== '') {
            $positions[$position] = $position;
        }
    }

    return array_values($positions);
}

function decodeDraftApprovalRouteBodies($route_bodies) {
    $decoded = json_decode((string) $route_bodies, true);
    if (!is_array($decoded)) {
        return [];
    }

    $bodies = [];
    foreach ($decoded as $key => $body) {
        $key = trim((string) $key);
        if ($key !== '') {
            $bodies[$key] = trim((string) $body);
        }
    }

    return $bodies;
}

function getRegisteredSupportUsers($conn) {
    $stmt = mysqli_prepare($conn, "SELECT id, fullname, email, department, position, role FROM users WHERE status = 'approved' ORDER BY department, fullname");
    if (!$stmt) {
        return [];
    }

    mysqli_stmt_execute($stmt);
    $users = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
    $management_positions = getDepartmentPositions('Pengurusan');

    return array_values(array_filter($users, function ($user) use ($management_positions) {
        $department = normalizeDepartmentValue($user['department'] ?? '');
        $position = trim((string) ($user['position'] ?? ''));

        return $department === 'Pengurusan'
            || in_array($position, $management_positions, true);
    }));
}

function getKbkuApproverUsers($conn) {
    $stmt = mysqli_prepare($conn, "SELECT id, fullname, email, department, position, role FROM users WHERE status = 'approved' ORDER BY department, fullname");
    if (!$stmt) {
        return [];
    }

    mysqli_stmt_execute($stmt);
    $users = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);

    return array_values(array_filter($users, function ($user) {
        $department = normalizeDepartmentValue($user['department'] ?? '');

        return isOfficerPosition($user['position'] ?? '')
            && $department !== 'Pengurusan';
    }));
}

function getDepartmentHeadOfficerUsers($conn) {
    $stmt = mysqli_prepare($conn, "SELECT id, fullname, email, department, position, role FROM users WHERE status = 'approved' ORDER BY department, fullname");
    if (!$stmt) {
        return [];
    }

    mysqli_stmt_execute($stmt);
    $users = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);

    $head_users = array_values(array_filter($users, function ($user) {
        $department = normalizeDepartmentValue($user['department'] ?? '');

        return isHeadOfficerPosition($user['position'] ?? '')
            && $department !== 'Pengurusan';
    }));

    foreach ($head_users as &$user) {
        $user['actual_position'] = $user['position'] ?? '';
        $user['position'] = getDisplayPositionForUser($user);
    }
    unset($user);

    return $head_users;
}

function getDepartmentHeadOfficerByDepartment($conn, $department) {
    $department = normalizeDepartmentValue($department);
    if ($department === '') {
        return null;
    }

    foreach (getDepartmentHeadOfficerUsers($conn) as $user) {
        if (isSameDepartment($user['department'] ?? '', $department)) {
            return $user;
        }
    }

    return null;
}

function isManagementApprovalUser($user) {
    if (!$user) {
        return false;
    }

    $department = normalizeDepartmentValue($user['department'] ?? '');
    $position = trim((string) ($user['position'] ?? ''));

    return $department === 'Pengurusan'
        || in_array($position, getDepartmentPositions('Pengurusan'), true);
}

function formatDraftLetterReferenceDisplay($department, $ref_no, $file_numbering = '') {
    $ref_no = trim((string) $ref_no);
    $file_numbering = trim((string) $file_numbering);

    if ($ref_no === '') {
        return '-';
    }

    $display = $ref_no;
    if ($file_numbering !== '' && $file_numbering !== $ref_no) {
        $display .= '(' . $file_numbering . ')';
    }

    return $display;
}

function generateNextDocumentReference($folder_code, $latest_ref_no = '') {
    $folder_code = rtrim(trim((string) $folder_code), '/');

    if ($folder_code === '') {
        return '';
    }

    return $folder_code;
}

function generateNextDocumentFileNumbering($conn, $folder_id) {
    $folder_id = (int) $folder_id;
    if ($folder_id <= 0) {
        return '1';
    }

    $stmt = mysqli_prepare($conn, "SELECT file_numbering FROM documents WHERE folder_id = ? AND deleted_at IS NULL");
    if (!$stmt) {
        return '1';
    }

    mysqli_stmt_bind_param($stmt, "i", $folder_id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $highest_number = 0;
    while ($row = mysqli_fetch_assoc($result)) {
        $current = trim((string) ($row['file_numbering'] ?? ''));
        if ($current === '' || strtoupper($current) === 'X') {
            continue;
        }

        if (preg_match('/^\d+$/', $current)) {
            $number = (int) $current;
            $highest_number = max($highest_number, $number);
        }
    }

    if ($highest_number > 0) {
        return (string) ($highest_number + 1);
    }

    $start_number = 1;
    $start_stmt = mysqli_prepare($conn, "SELECT file_number_start FROM folders WHERE id = ? LIMIT 1");
    if ($start_stmt) {
        mysqli_stmt_bind_param($start_stmt, "i", $folder_id);
        mysqli_stmt_execute($start_stmt);
        $start_row = mysqli_fetch_assoc(mysqli_stmt_get_result($start_stmt));
        $start_number = max(1, (int) ($start_row['file_number_start'] ?? 1));
    }

    $count_stmt = mysqli_prepare($conn, "SELECT COUNT(*) AS total FROM documents WHERE folder_id = ? AND deleted_at IS NULL AND UPPER(COALESCE(file_numbering, '')) <> 'X'");
    if (!$count_stmt) {
        return (string) $start_number;
    }

    mysqli_stmt_bind_param($count_stmt, "i", $folder_id);
    mysqli_stmt_execute($count_stmt);
    $count_row = mysqli_fetch_assoc(mysqli_stmt_get_result($count_stmt));

    return (string) ($start_number + (int) ($count_row['total'] ?? 0));
}

function renderBreadcrumb($items) {
    if (empty($items) || !is_array($items)) {
        return;
    }

    echo '<nav aria-label="Breadcrumb" style="margin-bottom:20px;">';
    echo '<ol style="list-style:none;display:flex;align-items:center;flex-wrap:wrap;gap:8px;background:#ffffffcc;border:1px solid #e0e8f0;border-radius:12px;padding:12px 16px;box-shadow:0 2px 10px rgba(0,0,0,0.04);">';

    $last_index = count($items) - 1;

    foreach ($items as $index => $item) {
        $label = htmlspecialchars(trim((string) ($item['label'] ?? '')));
        if ($label === '') {
            continue;
        }

        $url = trim((string) ($item['url'] ?? ''));
        $is_current = !empty($item['current']) || $index === $last_index;

        echo '<li style="display:inline-flex;align-items:center;gap:8px;">';

        if (!$is_current && $url !== '') {
            echo '<a href="' . htmlspecialchars($url) . '" style="color:#0077b6;text-decoration:none;font-size:0.9rem;font-weight:500;">' . $label . '</a>';
        } else {
            echo '<span aria-current="page" style="color:' . ($is_current ? '#1a3a5c' : '#6c757d') . ';font-size:0.9rem;font-weight:' . ($is_current ? '700' : '500') . ';">' . $label . '</span>';
        }

        if ($index < $last_index) {
            echo '<span aria-hidden="true" style="color:#9aa7b5;">/</span>';
        }

        echo '</li>';
    }

    echo '</ol>';
    echo '</nav>';
}

function paginateArrayItems($items, $page_param, $per_page = 15) {
    $items = is_array($items) ? array_values($items) : [];
    $page_param = preg_replace('/[^a-zA-Z0-9_]/', '', (string) $page_param);
    $per_page = max(1, (int) $per_page);
    $total_items = count($items);
    $total_pages = max(1, (int) ceil($total_items / $per_page));
    $current_page = (int) ($_GET[$page_param] ?? 1);
    $current_page = max(1, min($current_page, $total_pages));
    $offset = ($current_page - 1) * $per_page;

    return [
        'items' => array_slice($items, $offset, $per_page),
        'current_page' => $current_page,
        'total_pages' => $total_pages,
        'total_items' => $total_items,
        'per_page' => $per_page,
        'offset' => $offset,
        'page_param' => $page_param,
    ];
}

function renderPaginationControls($pagination, $label = 'rekod') {
    if (empty($pagination) || (int) ($pagination['total_pages'] ?? 1) <= 1) {
        return;
    }

    $page_param = preg_replace('/[^a-zA-Z0-9_]/', '', (string) ($pagination['page_param'] ?? 'page'));
    $current_page = max(1, (int) ($pagination['current_page'] ?? 1));
    $total_pages = max(1, (int) ($pagination['total_pages'] ?? 1));
    $total_items = max(0, (int) ($pagination['total_items'] ?? 0));
    $per_page = max(1, (int) ($pagination['per_page'] ?? 15));
    $start = $total_items > 0 ? (($current_page - 1) * $per_page) + 1 : 0;
    $end = min($total_items, $current_page * $per_page);
    $base_query = $_GET;
    $base_path = basename((string) ($_SERVER['PHP_SELF'] ?? ''));
    $label = htmlspecialchars((string) $label);

    $buildUrl = static function ($page) use ($base_query, $page_param, $base_path) {
        $query = $base_query;
        $query[$page_param] = max(1, (int) $page);
        return htmlspecialchars($base_path . '?' . http_build_query($query));
    };

    echo '<div class="pagination-wrap" style="display:flex;justify-content:space-between;align-items:center;gap:14px;flex-wrap:wrap;margin-top:18px;padding-top:14px;border-top:1px solid var(--border,#e0e8f0);">';
    echo '<div class="pagination-info" style="color:var(--text-muted,#6c757d);font-size:0.9rem;">Memaparkan ' . $start . ' - ' . $end . ' daripada ' . $total_items . ' ' . $label . '</div>';
    echo '<div class="pagination" style="display:flex;align-items:center;gap:6px;flex-wrap:wrap;">';

    $link_style = 'min-width:36px;height:36px;padding:0 10px;border:1px solid var(--border,#e0e8f0);border-radius:8px;display:inline-flex;align-items:center;justify-content:center;text-decoration:none;font-size:0.9rem;font-weight:600;color:var(--primary,#0077b6);background:#fff;';
    $disabled_style = $link_style . 'color:var(--text-muted,#6c757d);background:#f8fafc;cursor:not-allowed;';
    $active_style = $link_style . 'background:linear-gradient(90deg,var(--primary,#0077b6),var(--secondary,#00b4d8));color:#fff;border-color:transparent;';

    if ($current_page > 1) {
        echo '<a href="' . $buildUrl($current_page - 1) . '" style="' . $link_style . '">&laquo;</a>';
    } else {
        echo '<span style="' . $disabled_style . '">&laquo;</span>';
    }

    $start_page = max(1, $current_page - 2);
    $end_page = min($total_pages, $current_page + 2);
    if ($start_page > 1) {
        echo '<a href="' . $buildUrl(1) . '" style="' . $link_style . '">1</a>';
        if ($start_page > 2) {
            echo '<span style="' . $disabled_style . '">...</span>';
        }
    }

    for ($page = $start_page; $page <= $end_page; $page++) {
        if ($page === $current_page) {
            echo '<span style="' . $active_style . '">' . $page . '</span>';
        } else {
            echo '<a href="' . $buildUrl($page) . '" style="' . $link_style . '">' . $page . '</a>';
        }
    }

    if ($end_page < $total_pages) {
        if ($end_page < $total_pages - 1) {
            echo '<span style="' . $disabled_style . '">...</span>';
        }
        echo '<a href="' . $buildUrl($total_pages) . '" style="' . $link_style . '">' . $total_pages . '</a>';
    }

    if ($current_page < $total_pages) {
        echo '<a href="' . $buildUrl($current_page + 1) . '" style="' . $link_style . '">&raquo;</a>';
    } else {
        echo '<span style="' . $disabled_style . '">&raquo;</span>';
    }

    echo '</div>';
    echo '</div>';
}

function renderRoleBasedSidebarSection($conn, $role, $current_page = '') {
    if ($role === 'superadmin') {
        return;
    }

    $extra_items = [];
    if (hasPermission($conn, $role, 'deleted_item', 'read')) {
        $extra_items[] = [
            'url' => 'deleted_items.php',
            'label' => 'Data Dipadam',
            'icon' => 'fa-trash-restore',
        ];
    }

    echo '<li><hr style="border-color:var(--border);margin:8px 0;"></li>';

    if (empty($extra_items)) {
        return;
    }

    $active_urls = array_column($extra_items, 'url');
    $is_active = in_array($current_page, $active_urls, true);

    echo '<li class="dropdown' . ($is_active ? ' active' : '') . '">';
    echo '<button class="dropdown-toggle' . ($is_active ? ' active' : '') . '">';
    echo '<span><i class="fas fa-shield-alt"></i> <span class="menu-text">Capaian Tambahan</span></span>';
    echo '<i class="fas fa-chevron-down chevron"></i>';
    echo '</button>';
    echo '<div class="dropdown-menu">';

    foreach ($extra_items as $item) {
        $active_class = $current_page === $item['url'] ? ' class="active"' : '';
        echo '<a href="' . htmlspecialchars($item['url']) . '"' . $active_class . '><i class="fas ' . htmlspecialchars($item['icon']) . '"></i> ' . htmlspecialchars($item['label']) . '</a>';
    }

    echo '</div>';
    echo '</li>';
}

function renderDraftLetterSidebarMenu($conn, $current_page = '') {
    if (!currentUserCan($conn, 'draft_letter', 'read')) {
        return;
    }

    echo '<li><hr style="border-color:var(--border);margin:8px 0;"></li>';
    echo '<li class="sidebar-section">Modul Surat</li>';

    $dashboard_pages = ['draft_dashboard.php'];
    $list_pages = ['draft_letters.php', 'draft_letter_print.php'];
    $form_pages = ['draft_letter_form.php', 'memo_kelulusan.php', 'memo.php', 'surat_rasmi.php'];

    $dashboard_active = in_array($current_page, $dashboard_pages, true) ? ' class="active"' : '';
    $list_active = in_array($current_page, $list_pages, true) ? ' class="active"' : '';
    $form_active = in_array($current_page, $form_pages, true) ? ' class="active"' : '';

    echo '<li><a href="draft_dashboard.php"' . $dashboard_active . '><i class="fas fa-chart-line"></i> <span class="menu-text">Dashboard Surat</span></a></li>';
    echo '<li><a href="draft_letters.php"' . $list_active . '><i class="fas fa-file-signature"></i> <span class="menu-text">Senarai Surat</span></a></li>';

    if (currentUserCan($conn, 'draft_letter', 'create')) {
        echo '<li><a href="draft_letter_form.php"' . $form_active . '><i class="fas fa-plus-square"></i> <span class="menu-text">Sediakan Surat</span></a></li>';
    }
}

function renderUnifiedSidebarMenu($conn, $role, $current_page = '') {
    $current_page = strtolower(trim((string) $current_page));

    $active_map = [
        'dashboard_fail' => ['dashboard.php'],
        'senarai_folder' => ['list_folder.php', 'view_folder.php', 'add_documents.php', 'edit_document.php'],
        'tambah_folder' => ['add_folder.php'],
        'carian' => ['search.php'],
        'dashboard_draf' => ['draft_dashboard.php'],
        'senarai_draf' => ['draft_letters.php', 'draft_letter_print.php'],
        'cipta_draf' => ['draft_letter_form.php', 'memo_kelulusan.php', 'memo.php', 'surat_rasmi.php'],
        'my_task' => ['my_task.php'],
        'my_cuti' => ['my_cuti.php'],
        'my_meeting' => ['my_meeting.php'],
        'my_schedule' => ['my_schedule.php'],
        'my_team' => ['my_team.php'],
        'my_team_schedule' => ['my_team_schedule.php'],
        'pengurusan_pengguna' => ['user_management.php', 'user_register.php', 'user_list.php', 'user_approval.php', 'system_config.php', 'user_activity.php', 'deleted_items.php'],
    ];

    $isActive = function ($key) use ($active_map, $current_page) {
        return in_array($current_page, $active_map[$key] ?? [], true) ? ' class="active"' : '';
    };
    $accessDeniedMessage = 'Anda tiada akses ke bahagian ini. Sila hubungi Superadmin untuk membuka capaian.';
    $buildDeniedUrl = function ($label, $target_url = '') {
        $separator = strpos((string) $target_url, '?') === false ? '?' : '&';
        return (string) $target_url
            . $separator
            . 'access_preview=1&access_menu=' . urlencode((string) $label);
    };
    $linkAttrs = function ($is_allowed, $label) use ($accessDeniedMessage) {
        $attrs = ' data-label="' . htmlspecialchars($label) . '" title="' . htmlspecialchars($label) . '"';

        if (!$is_allowed) {
            $attrs .= ' data-access-message="' . htmlspecialchars($accessDeniedMessage) . '" aria-disabled="true"';
        }

        return $attrs;
    };
    $renderMenuLink = function ($url, $icon, $label, $active_class = '', $is_allowed = true) use ($linkAttrs, $buildDeniedUrl) {
        $class_attr = $active_class;
        if (!$is_allowed) {
            $class_attr = $class_attr === '' ? ' class="sidebar-no-access"' : str_replace('class="', 'class="sidebar-no-access ', $class_attr);
        }

        $href = $is_allowed ? $url : $buildDeniedUrl($label, $url);
        echo '<li><a href="' . htmlspecialchars($href) . '"' . $class_attr . $linkAttrs($is_allowed, $label) . '><i class="fas ' . htmlspecialchars($icon) . '"></i> <span class="menu-text">' . htmlspecialchars($label) . '</span></a></li>';
    };

    echo '<li class="sidebar-section">Pengurusan Fail</li>';
    $renderMenuLink('dashboard.php', 'fa-home', 'Dashboard', $isActive('dashboard_fail'));
    $renderMenuLink('list_folder.php', 'fa-folder-open', 'Senarai Fail', $isActive('senarai_folder'), currentUserCanStrict($conn, 'folder', 'read'));
    $renderMenuLink('add_folder.php', 'fa-folder-plus', 'Tambah Fail', $isActive('tambah_folder'), currentUserCanStrict($conn, 'folder', 'create'));
    $renderMenuLink('search.php', 'fa-search', 'Carian Lanjutan', $isActive('carian'), currentUserCanStrict($conn, 'folder', 'read'));

    echo '<li><hr style="border-color:var(--border);margin:8px 0;"></li>';
    echo '<li class="sidebar-section">Modul Surat</li>';
    $renderMenuLink('draft_dashboard.php', 'fa-chart-line', 'Dashboard Surat', $isActive('dashboard_draf'), currentUserCanStrict($conn, 'draft_letter', 'read'));
    $renderMenuLink('draft_letters.php', 'fa-file-signature', 'Senarai Surat', $isActive('senarai_draf'), currentUserCanStrict($conn, 'draft_letter', 'read'));
    $renderMenuLink('draft_letter_form.php', 'fa-plus-square', 'Sediakan Surat', $isActive('cipta_draf'), currentUserCanStrict($conn, 'draft_letter', 'create'));

    echo '<li><hr style="border-color:var(--border);margin:8px 0;"></li>';
    echo '<li class="sidebar-section">Modul Peribadi</li>';
    $renderMenuLink('my_task.php', 'fa-tasks', 'My Task', $isActive('my_task'));
    $renderMenuLink('my_cuti.php', 'fa-umbrella-beach', 'My Cuti', $isActive('my_cuti'));
    $renderMenuLink('my_meeting.php', 'fa-calendar-alt', 'My Meeting/Kursus', $isActive('my_meeting'));
    $renderMenuLink('my_schedule.php', 'fa-user-clock', 'My Schedule', $isActive('my_schedule'));

    echo '<li><hr style="border-color:var(--border);margin:8px 0;"></li>';
    echo '<li class="sidebar-section">Modul Pasukan Saya</li>';
    $renderMenuLink('my_team.php', 'fa-users', 'My Team', $isActive('my_team'));
    $renderMenuLink('my_team_schedule.php', 'fa-calendar-week', 'My Team Schedule', $isActive('my_team_schedule'));

    echo '<li><hr style="border-color:var(--border);margin:8px 0;"></li>';

    $management_items = [
        ['url' => 'user_register.php', 'icon' => 'fa-user-plus', 'label' => 'Daftar Pengguna', 'allowed' => $role === 'superadmin'],
        ['url' => 'user_list.php', 'icon' => 'fa-list', 'label' => 'Senarai Pengguna', 'allowed' => $role === 'superadmin'],
        ['url' => 'user_approval.php', 'icon' => 'fa-user-check', 'label' => 'Kelulusan Pengguna', 'allowed' => in_array($role, ['admin', 'superadmin'], true)],
        ['url' => 'Senarai_Kakitangan.php', 'icon' => 'fa-users', 'label' => 'Senarai Kakitangan', 'allowed' => true],
        ['url' => 'system_config.php', 'icon' => 'fa-cogs', 'label' => 'Capaian Sistem', 'allowed' => $role === 'superadmin'],
        ['url' => 'user_activity.php', 'icon' => 'fa-history', 'label' => 'Aktiviti Pengguna', 'allowed' => $role === 'superadmin'],
        ['url' => 'deleted_items.php', 'icon' => 'fa-trash-restore', 'label' => 'Data Dipadam', 'allowed' => currentUserCanStrict($conn, 'deleted_item', 'read')],
    ];

    $management_active = $isActive('pengurusan_pengguna') !== '';
    echo '<li class="dropdown' . ($management_active ? ' active' : '') . '">';
    echo '<button type="button" class="dropdown-toggle' . ($management_active ? ' active' : '') . '" data-label="Pengurusan Pengguna" title="Pengurusan Pengguna" onclick="toggleSidebarDropdown(this, event)">';
    echo '<span><i class="fas fa-users-cog"></i> <span class="menu-text">Pengurusan Pengguna</span></span>';
    echo '<i class="fas fa-chevron-down chevron"></i>';
    echo '</button>';
    echo '<div class="dropdown-menu">';

    foreach ($management_items as $item) {
        $active_class = $current_page === $item['url'] ? ' class="active"' : '';
        $is_allowed = (bool) ($item['allowed'] ?? true);
        if (!$is_allowed) {
            $active_class = $active_class === '' ? ' class="sidebar-no-access"' : str_replace('class="', 'class="sidebar-no-access ', $active_class);
        }
        $href = $is_allowed ? $item['url'] : $buildDeniedUrl($item['label'], $item['url']);
        echo '<a href="' . htmlspecialchars($href) . '"' . $active_class . $linkAttrs($is_allowed, $item['label']) . '><i class="fas ' . htmlspecialchars($item['icon']) . '"></i> ' . htmlspecialchars($item['label']) . '</a>';
    }

    echo '</div>';
    echo '</li>';
}

function getCurrentUserMenuData($conn) {
    static $cached_user = null;

    if ($cached_user !== null) {
        return $cached_user;
    }

    $user_id = (int) ($_SESSION['user_id'] ?? 0);
    $fullname = trim((string) ($_SESSION['fullname'] ?? 'Pengguna Sistem'));
    $short_name = trim((string) ($_SESSION['short_name'] ?? ''));
    $department = normalizeDepartmentValue($_SESSION['department'] ?? '');
    $role = strtolower(trim((string) ($_SESSION['role'] ?? 'user')));
    $profile_photo = '';

    if ($user_id > 0) {
        $stmt = mysqli_prepare($conn, "SELECT fullname, short_name, department, role, profile_photo FROM users WHERE id = ? LIMIT 1");
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, "i", $user_id);
            mysqli_stmt_execute($stmt);
            $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));

            if ($row) {
                $fullname = trim((string) ($row['fullname'] ?? $fullname)) ?: $fullname;
                $short_name = trim((string) ($row['short_name'] ?? $short_name));
                $department = normalizeDepartmentValue($row['department'] ?? $department);
                $role = strtolower(trim((string) ($row['role'] ?? $role))) ?: $role;
                $profile_photo = trim((string) ($row['profile_photo'] ?? ''));

                $_SESSION['fullname'] = $fullname;
                $_SESSION['short_name'] = $short_name;
                $_SESSION['department'] = $department;
                $_SESSION['role'] = $role;
            }
        }
    }

    $display_name = $short_name !== '' ? $short_name : $fullname;
    $name_parts = preg_split('/\s+/', $display_name, -1, PREG_SPLIT_NO_EMPTY);
    $initials = '';
    foreach (array_slice($name_parts ?: ['P'], 0, 2) as $part) {
        $initials .= function_exists('mb_substr') ? mb_substr($part, 0, 1, 'UTF-8') : substr($part, 0, 1);
    }
    $initials = strtoupper($initials ?: 'P');

    if ($profile_photo !== '' && !file_exists($profile_photo)) {
        $profile_photo = '';
    }

    $cached_user = [
        'fullname' => $fullname,
        'short_name' => $short_name,
        'display_name' => $display_name,
        'department' => $department !== '' ? $department : 'Jabatan tidak ditetapkan',
        'role' => $role !== '' ? $role : 'user',
        'role_label' => strtoupper($role !== '' ? $role : 'user'),
        'profile_photo' => $profile_photo,
        'initials' => $initials,
    ];

    return $cached_user;
}

function getIncomingDraftNotifications($conn, $limit = 0) {
    $user_id = (int) ($_SESSION['user_id'] ?? 0);
    $current_role = strtolower(trim((string) ($_SESSION['role'] ?? 'user')));
    if ($user_id <= 0) {
        return ['count' => 0, 'items' => [], 'alert_ids' => []];
    }

    $limit = max(0, (int) $limit);
    $items = [];
    $alert_ids = [];

    $department_sql = "SELECT ld.id, ld.ref_no, ld.subject, ld.approval_status, ld.department_officer_status AS action_status,
            ld.department_officer_requested_at AS requested_at, ld.updated_at
        FROM letter_drafts ld
        WHERE ld.deleted_at IS NULL
          AND ld.department_officer_user_id = ?
          AND ld.approval_status NOT IN ('perlu_pindaan_penyedia','sedang_dipinda_ketua_bahagian')
          AND ld.department_officer_status IN ('menunggu','menunggu_tandatangan')";
    $department_stmt = mysqli_prepare($conn, $department_sql);
    if ($department_stmt) {
        mysqli_stmt_bind_param($department_stmt, "i", $user_id);
        mysqli_stmt_execute($department_stmt);
        $department_result = mysqli_stmt_get_result($department_stmt);
        while ($department_result && ($row = mysqli_fetch_assoc($department_result))) {
            $status = (string) ($row['action_status'] ?? 'menunggu');
            $approval_status = (string) ($row['approval_status'] ?? '');
            $items[] = [
                'id' => (int) $row['id'],
                'alert_id' => 'kb-' . (int) $row['id'] . '-' . $status,
                'icon' => 'fa-file-signature',
                'ref_no' => trim((string) ($row['ref_no'] ?? '')),
                'subject' => trim((string) ($row['subject'] ?? '')),
                'label' => $status === 'menunggu_tandatangan' ? 'Menunggu tandatangan anda' : 'Menunggu kelulusan anda',
                'time' => trim((string) (($row['requested_at'] ?? '') ?: ($row['updated_at'] ?? ''))),
                'url' => 'draft_letter_print.php?id=' . (int) $row['id'],
            ];
        }
        if ($department_result) {
            mysqli_free_result($department_result);
        }
        mysqli_stmt_close($department_stmt);
    }

    $memo_head_sql = "SELECT ld.id, ld.ref_no, ld.subject, ld.approval_status, ld.memo_preparer_head_status AS action_status,
            ld.memo_preparer_head_requested_at AS requested_at, ld.updated_at
        FROM letter_drafts ld
        WHERE ld.deleted_at IS NULL
          AND ld.doc_type = 'Memo'
          AND ld.memo_preparer_head_user_id = ?
          AND ld.memo_preparer_head_status = 'menunggu'";
    $memo_head_stmt = mysqli_prepare($conn, $memo_head_sql);
    if ($memo_head_stmt) {
        mysqli_stmt_bind_param($memo_head_stmt, "i", $user_id);
        mysqli_stmt_execute($memo_head_stmt);
        $memo_head_result = mysqli_stmt_get_result($memo_head_stmt);
        while ($memo_head_result && ($row = mysqli_fetch_assoc($memo_head_result))) {
            $status = (string) ($row['action_status'] ?? 'menunggu');
            $items[] = [
                'id' => (int) $row['id'],
                'alert_id' => 'memo-head-' . (int) $row['id'] . '-' . $status,
                'icon' => 'fa-file-signature',
                'ref_no' => trim((string) ($row['ref_no'] ?? '')),
                'subject' => trim((string) ($row['subject'] ?? '')),
                'label' => 'Memo baru untuk semakan anda',
                'time' => trim((string) (($row['requested_at'] ?? '') ?: ($row['updated_at'] ?? ''))),
                'url' => 'draft_letter_print.php?id=' . (int) $row['id'],
            ];
        }
        if ($memo_head_result) {
            mysqli_free_result($memo_head_result);
        }
        mysqli_stmt_close($memo_head_stmt);
    }

    $recipient_sql = "SELECT ld.id, ld.ref_no, ld.subject, ld.doc_type, lar.id AS recipient_id, lar.status AS action_status,
            lar.source_type, lar.requested_at, lar.responded_at, ld.updated_at
        FROM letter_approval_recipients lar
        INNER JOIN letter_drafts ld ON ld.id = lar.draft_id AND ld.deleted_at IS NULL
        WHERE lar.user_id = ?
          AND (
              lar.status IN ('menunggu','menunggu_tandatangan')
              OR (ld.doc_type IN ('Memo', 'Surat Rasmi') AND lar.source_type = 'kepada' AND lar.status = 'lulus')
          )";
    $recipient_stmt = mysqli_prepare($conn, $recipient_sql);
    if ($recipient_stmt) {
        mysqli_stmt_bind_param($recipient_stmt, "i", $user_id);
        mysqli_stmt_execute($recipient_stmt);
        $recipient_result = mysqli_stmt_get_result($recipient_stmt);
        while ($recipient_result && ($row = mysqli_fetch_assoc($recipient_result))) {
            $status = (string) ($row['action_status'] ?? 'menunggu');
            $recipient_id = (int) ($row['recipient_id'] ?? 0);
            $is_delivered_memo = in_array(strtolower(trim((string) ($row['doc_type'] ?? ''))), ['memo', 'surat rasmi'], true)
                && ($row['source_type'] ?? '') === 'kepada'
                && $status === 'lulus';
            $items[] = [
                'id' => (int) $row['id'],
                'alert_id' => 'recipient-' . $recipient_id . '-' . $status,
                'icon' => 'fa-file-signature',
                'ref_no' => trim((string) ($row['ref_no'] ?? '')),
                'subject' => trim((string) ($row['subject'] ?? '')),
                'label' => $is_delivered_memo
                    ? ((strcasecmp((string) ($row['doc_type'] ?? ''), 'Surat Rasmi') === 0) ? 'Surat Rasmi baru diterima' : 'Memo baru diterima')
                    : ($status === 'menunggu_tandatangan' ? 'Menunggu tandatangan anda' : 'Surat baru untuk kelulusan anda'),
                'time' => trim((string) (($row['responded_at'] ?? '') ?: ($row['requested_at'] ?? '') ?: ($row['updated_at'] ?? ''))),
                'url' => 'draft_letter_print.php?id=' . (int) $row['id'],
            ];
        }
        if ($recipient_result) {
            mysqli_free_result($recipient_result);
        }
        mysqli_stmt_close($recipient_stmt);
    }

    $creator_rejected_sql = "SELECT ld.id, ld.ref_no, ld.subject, ld.approval_status, ld.support_note, ld.updated_at,
            ld.support_responded_at, ld.department_officer_responded_at,
            MAX(lar.responded_at) AS recipient_responded_at
        FROM letter_drafts ld
        LEFT JOIN letter_approval_recipients lar ON lar.draft_id = ld.id AND lar.status = 'ditolak'
        WHERE ld.deleted_at IS NULL
          AND ld.approval_status IN ('ditolak','perlu_pindaan_penyedia','sedang_dipinda_ketua_bahagian')
          AND (ld.created_by = ? OR (ld.approval_status = 'ditolak' AND ld.support_user_id = ?))
        GROUP BY ld.id, ld.ref_no, ld.subject, ld.approval_status, ld.support_note, ld.updated_at, ld.support_responded_at, ld.department_officer_responded_at";
    $creator_rejected_stmt = mysqli_prepare($conn, $creator_rejected_sql);
    if ($creator_rejected_stmt) {
        mysqli_stmt_bind_param($creator_rejected_stmt, "ii", $user_id, $user_id);
        mysqli_stmt_execute($creator_rejected_stmt);
        $creator_rejected_result = mysqli_stmt_get_result($creator_rejected_stmt);
        while ($creator_rejected_result && ($row = mysqli_fetch_assoc($creator_rejected_result))) {
            $time = trim((string) (($row['recipient_responded_at'] ?? '') ?: ($row['department_officer_responded_at'] ?? '') ?: ($row['support_responded_at'] ?? '') ?: ($row['updated_at'] ?? '')));
            $alert_suffix = preg_replace('/[^0-9]/', '', $time);
            if ($alert_suffix === '') {
                $alert_suffix = 'current';
            }
            $approval_status = (string) ($row['approval_status'] ?? '');
            $creator_label = 'Surat ditolak. Sila ambil tindakan semula.';
            $creator_alert_prefix = 'creator-rejected-';
            if ($approval_status === 'perlu_pindaan_penyedia') {
                $creator_label = 'Surat perlu pindaan oleh penyedia.';
                $creator_alert_prefix = 'creator-revision-';
            } elseif ($approval_status === 'sedang_dipinda_ketua_bahagian') {
                $creator_label = 'Ketua Bahagian sedang membuat pindaan surat.';
                $creator_alert_prefix = 'creator-kb-revision-';
            }
            $items[] = [
                'id' => (int) $row['id'],
                'alert_id' => $creator_alert_prefix . (int) $row['id'] . '-' . $alert_suffix,
                'icon' => 'fa-file-circle-exclamation',
                'ref_no' => trim((string) ($row['ref_no'] ?? '')),
                'subject' => trim((string) ($row['subject'] ?? '')),
                'label' => $creator_label,
                'time' => $time,
                'url' => 'draft_letter_print.php?id=' . (int) $row['id'],
            ];
        }
        if ($creator_rejected_result) {
            mysqli_free_result($creator_rejected_result);
        }
        mysqli_stmt_close($creator_rejected_stmt);
    }

    $creator_revision_completed_sql = "SELECT ld.id, ld.ref_no, ld.subject, ld.updated_at,
            MAX(finished.created_at) AS revision_completed_at
        FROM letter_drafts ld
        INNER JOIN activity_logs started
            ON started.activity_type = 'start_department_officer_revision'
            AND started.activity_description LIKE CONCAT('%(Draft ID: ', ld.id, ')%')
        LEFT JOIN activity_logs finished
            ON finished.activity_type = 'finish_department_officer_revision'
            AND finished.activity_description LIKE CONCAT('%(Draft ID: ', ld.id, ')%')
        WHERE ld.deleted_at IS NULL
          AND ld.created_by = ?
          AND ld.approval_status = 'lulus'
        GROUP BY ld.id, ld.ref_no, ld.subject, ld.updated_at";
    $creator_revision_completed_stmt = mysqli_prepare($conn, $creator_revision_completed_sql);
    if ($creator_revision_completed_stmt) {
        mysqli_stmt_bind_param($creator_revision_completed_stmt, "i", $user_id);
        mysqli_stmt_execute($creator_revision_completed_stmt);
        $creator_revision_completed_result = mysqli_stmt_get_result($creator_revision_completed_stmt);
        while ($creator_revision_completed_result && ($row = mysqli_fetch_assoc($creator_revision_completed_result))) {
            $time = trim((string) (($row['revision_completed_at'] ?? '') ?: ($row['updated_at'] ?? '')));
            $alert_suffix = preg_replace('/[^0-9]/', '', $time);
            if ($alert_suffix === '') {
                $alert_suffix = 'current';
            }
            $items[] = [
                'id' => (int) $row['id'],
                'alert_id' => 'creator-revision-completed-' . (int) $row['id'] . '-' . $alert_suffix,
                'icon' => 'fa-file-circle-check',
                'ref_no' => trim((string) ($row['ref_no'] ?? '')),
                'subject' => trim((string) ($row['subject'] ?? '')),
                'label' => 'Surat yang dipinda telah diterima semula. Sedia untuk dicetak.',
                'time' => $time,
                'url' => 'draft_letter_print.php?id=' . (int) $row['id'],
            ];
        }
        if ($creator_revision_completed_result) {
            mysqli_free_result($creator_revision_completed_result);
        }
        mysqli_stmt_close($creator_revision_completed_stmt);
    }

    if ($current_role === 'superadmin') {
        $pending_user_sql = "SELECT id, fullname, email, department, position, registered_at
            FROM users
            WHERE status = 'pending'
            ORDER BY registered_at DESC, id DESC";
        $pending_user_result = mysqli_query($conn, $pending_user_sql);
        while ($pending_user_result && ($row = mysqli_fetch_assoc($pending_user_result))) {
            $fullname = trim((string) ($row['fullname'] ?? ''));
            $department = trim((string) ($row['department'] ?? ''));
            $position = trim((string) ($row['position'] ?? ''));
            $items[] = [
                'id' => (int) $row['id'],
                'alert_id' => 'user-pending-' . (int) $row['id'],
                'icon' => 'fa-user-clock',
                'ref_no' => trim($department . ($position !== '' ? ' - ' . $position : '')),
                'subject' => $fullname !== '' ? $fullname : trim((string) ($row['email'] ?? 'Pengguna baharu')),
                'label' => 'Pengguna baru menunggu kelulusan Superadmin',
                'time' => trim((string) ($row['registered_at'] ?? '')),
                'url' => 'user_approval.php',
            ];
        }
        if ($pending_user_result) {
            mysqli_free_result($pending_user_result);
        }
    }

    usort($items, function ($left, $right) {
        return strcmp((string) ($right['time'] ?? ''), (string) ($left['time'] ?? ''));
    });

    foreach ($items as $item) {
        $alert_ids[] = $item['alert_id'];
    }
    $alert_ids = array_values(array_unique($alert_ids));
    $read_keys = [];
    if (!empty($alert_ids)) {
        $placeholders = implode(',', array_fill(0, count($alert_ids), '?'));
        $types = 'i' . str_repeat('s', count($alert_ids));
        $params = array_merge([$user_id], $alert_ids);
        $read_stmt = mysqli_prepare($conn, "SELECT notification_key FROM user_notification_reads WHERE user_id = ? AND notification_key IN ({$placeholders})");
        if ($read_stmt) {
            mysqli_stmt_bind_param($read_stmt, $types, ...$params);
            mysqli_stmt_execute($read_stmt);
            $read_result = mysqli_stmt_get_result($read_stmt);
            while ($read_result && ($row = mysqli_fetch_assoc($read_result))) {
                $read_keys[] = (string) ($row['notification_key'] ?? '');
            }
            if ($read_result) {
                mysqli_free_result($read_result);
            }
            mysqli_stmt_close($read_stmt);
        }
    }
    $read_lookup = array_flip($read_keys);
    $unread_ids = [];
    foreach ($items as &$item) {
        $item['is_unread'] = !isset($read_lookup[$item['alert_id']]);
        if ($item['is_unread']) {
            $unread_ids[] = $item['alert_id'];
        }
    }
    unset($item);

    return [
        'count' => count($unread_ids),
        'items' => $limit > 0 ? array_slice($items, 0, $limit) : $items,
        'alert_ids' => array_values(array_unique($unread_ids)),
        'total_pending' => count($items),
    ];
}

function renderTopBarUserMenu($conn, $role = null, $current_page = '') {
    $user = getCurrentUserMenuData($conn);
    $resolved_role = strtolower(trim((string) ($role ?? $user['role'] ?? 'user')));
    $profile_photo = (string) ($user['profile_photo'] ?? '');
    $draft_notifications = getIncomingDraftNotifications($conn);
    $notification_count = (int) ($draft_notifications['count'] ?? 0);
    $notification_total_pending = (int) ($draft_notifications['total_pending'] ?? 0);
    $notification_items = $draft_notifications['items'] ?? [];
    $notification_alert_ids = $draft_notifications['alert_ids'] ?? [];
    $format_notification_time = function ($value) {
        $timestamp = strtotime((string) $value);
        return $timestamp ? date('d/m/Y H:i', $timestamp) : '';
    };
    ?>
    <div class="user-info">
        <div class="top-user-identity">
            <span class="top-user-name"><?= htmlspecialchars($user['display_name'] ?? $user['fullname']) ?></span>
            <span class="top-user-meta">
                <span class="top-user-department"><?= htmlspecialchars($user['department']) ?></span>
                <span class="top-user-role role-<?= htmlspecialchars(strtolower((string) ($user['role'] ?? 'user'))) ?>"><?= htmlspecialchars($user['role_label'] ?? strtoupper((string) ($user['role'] ?? 'user'))) ?></span>
            </span>
        </div>
        <div class="top-notification-menu" data-alert-ids="<?= htmlspecialchars(json_encode($notification_alert_ids, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), ENT_QUOTES) ?>" data-alert-count="<?= $notification_count ?>">
            <button type="button" class="top-notification-trigger<?= $notification_count > 0 ? ' has-alert' : '' ?>" aria-label="Notifikasi surat" aria-haspopup="true" aria-expanded="false">
                <i class="fas fa-bell"></i>
                <?php if ($notification_count > 0): ?>
                <span class="notification-badge"><?= $notification_count > 9 ? '9+' : $notification_count ?></span>
                <?php endif; ?>
            </button>
            <div class="top-notification-dropdown" role="menu">
                <div class="notification-head">
                    <strong>Notifikasi</strong>
                    <?php if ($notification_count > 0): ?>
                    <span class="notification-count-text"><?= $notification_count ?> baru</span>
                    <?php elseif ($notification_total_pending > 0): ?>
                    <span class="notification-count-text">Semua dibaca</span>
                    <?php endif; ?>
                </div>
                <?php if ($notification_count > 0): ?>
                <button type="button" class="notification-read-all">
                    <i class="fas fa-check-double"></i> Read All Messages
                </button>
                <?php endif; ?>
                <?php if (empty($notification_items)): ?>
                <div class="notification-empty">
                    <i class="fas fa-circle-check"></i>
                    Tiada notifikasi baru.
                </div>
                <?php else: ?>
                <div class="notification-list-scroll">
                    <?php foreach ($notification_items as $item_index => $item): ?>
                    <a
                        href="<?= htmlspecialchars($item['url']) ?>"
                        role="menuitem"
                        class="notification-item<?= !empty($item['is_unread']) ? ' unread' : '' ?><?= $item_index === 0 ? ' latest' : '' ?>"
                        data-notification-key="<?= htmlspecialchars($item['alert_id'] ?? '') ?>"
                    >
                        <span class="notification-icon"><i class="fas <?= htmlspecialchars($item['icon'] ?? 'fa-file-signature') ?>"></i></span>
                        <span>
                            <?php if ($item_index === 0): ?>
                            <span class="notification-latest-label">Terkini</span>
                            <?php endif; ?>
                            <strong><?= htmlspecialchars($item['subject'] !== '' ? $item['subject'] : 'Surat tanpa tajuk') ?></strong>
                            <small><?= htmlspecialchars($item['label']) ?></small>
                            <em><?= htmlspecialchars($item['ref_no'] !== '' ? $item['ref_no'] : '-') ?></em>
                            <?php $notification_time = $format_notification_time($item['time'] ?? ''); ?>
                            <?php if ($notification_time !== ''): ?>
                            <time class="notification-time" datetime="<?= htmlspecialchars($item['time'] ?? '') ?>">
                                <i class="fas fa-clock"></i> <?= htmlspecialchars($notification_time) ?>
                            </time>
                            <?php endif; ?>
                        </span>
                    </a>
                    <?php endforeach; ?>
                </div>
                <?php endif; ?>
            </div>
        </div>
        <div class="top-profile-menu">
            <button type="button" class="top-profile-trigger" aria-label="Menu profil" aria-haspopup="true" aria-expanded="false">
                <?php if ($profile_photo !== ''): ?>
                    <img src="<?= htmlspecialchars($profile_photo) ?>" alt="Profil <?= htmlspecialchars($user['fullname']) ?>">
                <?php else: ?>
                    <span><?= htmlspecialchars($user['initials']) ?></span>
                <?php endif; ?>
                <i class="fas fa-chevron-down" aria-hidden="true"></i>
            </button>
            <div class="top-profile-dropdown" role="menu">
                <a href="profile_pengguna.php" role="menuitem">
                    <i class="fas fa-user-circle"></i>
                    <span>Profil</span>
                </a>
                <a href="UAT.php" role="menuitem">
                    <i class="fas fa-clipboard-check"></i>
                    <span>UAT</span>
                </a>
                <a
                    href="logout.php"
                    role="menuitem"
                    onclick="return typeof confirmLogout === 'function' ? confirmLogout() : confirm('Adakah anda pasti ingin log keluar?');"
                >
                    <i class="fas fa-sign-out-alt"></i>
                    <span>Log Keluar</span>
                </a>
            </div>
        </div>
    </div>
    <?php
}

function renderAppShellAssets() {
    $is_access_preview = function_exists('isAccessPreviewRequest') && isAccessPreviewRequest();
    $access_preview_menu = trim((string) ($_GET['access_menu'] ?? ($_SESSION['access_preview_menu'] ?? 'bahagian ini')));
    if ($access_preview_menu === '') {
        $access_preview_menu = 'bahagian ini';
    }
    $access_retry_url = $_SERVER['REQUEST_URI'] ?? '';
    if ($access_retry_url === '') {
        $access_retry_params = $_GET;
        $access_retry_params['access_preview'] = '1';
        $access_retry_params['access_menu'] = $access_preview_menu;
        $access_retry_url = basename((string) ($_SERVER['PHP_SELF'] ?? 'dashboard.php')) . '?' . http_build_query($access_retry_params);
    }
    ?>
    <?php if ($is_access_preview): ?>
        <script>
            document.documentElement.classList.add('access-preview-active');
        </script>
    <?php endif; ?>
    <style>
        :root {
            --sidebar-width: 280px !important;
            --app-font: 'Segoe UI', 'Inter', Tahoma, Geneva, Verdana, sans-serif;
        }

        html,
        body,
        button,
        input,
        select,
        textarea,
        table {
            font-family: var(--app-font) !important;
        }

        .sidebar {
            width: 280px !important;
            padding-top: 0 !important;
            overflow-y: auto !important;
            overflow-x: hidden !important;
            transition: width 0.42s cubic-bezier(0.22, 1, 0.36, 1), box-shadow 0.42s ease !important;
            will-change: width !important;
        }

        .sidebar::-webkit-scrollbar {
            width: 6px !important;
        }
        .sidebar::-webkit-scrollbar-track {
            background: rgba(0, 0, 0, 0.12) !important;
        }
        .sidebar::-webkit-scrollbar-thumb {
            background: rgba(255, 255, 255, 0.32) !important;
            border-radius: 999px !important;
        }
        .sidebar::-webkit-scrollbar-thumb:hover {
            background: rgba(255, 255, 255, 0.55) !important;
        }

        .main {
            margin-left: 280px !important;
            transition: margin-left 0.42s cubic-bezier(0.22, 1, 0.36, 1) !important;
            will-change: margin-left !important;
        }

        .sidebar.collapsed {
            width: 70px !important;
        }

        .sidebar.collapsed + .main {
            margin-left: 70px !important;
        }

        .sidebar-header {
            position: sticky !important;
            top: 0 !important;
            z-index: 1100 !important;
            background: linear-gradient(180deg, #fff 0%, #f8fbfd 100%) !important;
            padding: 18px 20px 16px !important;
            margin-bottom: 14px !important;
            overflow: visible !important;
            white-space: normal !important;
            transition: padding 0.42s cubic-bezier(0.22, 1, 0.36, 1), margin 0.42s cubic-bezier(0.22, 1, 0.36, 1) !important;
        }

        .sidebar-header img {
            display: block !important;
            width: 56px !important;
            max-width: 100% !important;
            height: auto !important;
            object-fit: contain !important;
            margin: 0 auto 10px !important;
            transition: width 0.42s cubic-bezier(0.22, 1, 0.36, 1), margin 0.42s cubic-bezier(0.22, 1, 0.36, 1), filter 0.3s ease !important;
        }

        .sidebar-header h3 {
            white-space: normal !important;
            line-height: 1.25 !important;
            margin: 0 0 5px !important;
            max-height: 48px !important;
            opacity: 1 !important;
            transform: translateY(0) !important;
            transition: opacity 0.22s ease, transform 0.22s ease, max-height 0.32s ease, margin 0.32s ease !important;
        }

        .sidebar-header p {
            line-height: 1.3 !important;
            margin: 0 !important;
            max-height: 32px !important;
            opacity: 1 !important;
            transform: translateY(0) !important;
            transition: opacity 0.22s ease, transform 0.22s ease, max-height 0.32s ease !important;
        }

        .sidebar-toggle {
            position: absolute !important;
            right: 10px !important;
            top: 18px !important;
            width: 28px !important;
            height: 28px !important;
            min-width: 28px !important;
            border-radius: 50% !important;
            background: rgba(255, 255, 255, 0.20) !important;
            color: #fff !important;
            border: 1px solid rgba(255, 255, 255, 0.28) !important;
            cursor: pointer !important;
            display: flex !important;
            align-items: center !important;
            justify-content: center !important;
            font-size: 0.75rem !important;
            box-shadow: 0 10px 22px rgba(0, 0, 0, 0.14) !important;
            z-index: 1300 !important;
            flex: 0 0 auto !important;
            transition: transform 0.42s cubic-bezier(0.22, 1, 0.36, 1), background-color 0.2s ease, box-shadow 0.2s ease !important;
        }

        /* Global Unified Sidebar Dropdown Styles */
        .sidebar .sidebar-menu li a {
            display: flex !important;
            align-items: center !important;
            gap: 12px !important;
            padding: 11px 16px !important;
            color: rgba(255, 255, 255, 0.88) !important;
            text-decoration: none !important;
            border-radius: 12px !important;
            margin-bottom: 6px !important;
            transition: all 0.22s ease !important;
            font-size: 0.9rem !important;
            border: 1px solid transparent !important;
        }

        .sidebar .sidebar-menu li a:hover,
        .sidebar .sidebar-menu li a.active {
            background: rgba(255, 255, 255, 0.22) !important;
            color: #ffffff !important;
            border: 1px solid rgba(255, 255, 255, 0.38) !important;
            border-radius: 12px !important;
            box-shadow: 0 4px 16px rgba(0, 0, 0, 0.12), inset 0 1px 0 rgba(255, 255, 255, 0.20) !important;
            backdrop-filter: blur(10px) !important;
            -webkit-backdrop-filter: blur(10px) !important;
        }

        .sidebar .dropdown {
            position: relative !important;
        }

        .sidebar .dropdown-toggle {
            display: flex !important;
            align-items: center !important;
            justify-content: space-between !important;
            width: 100% !important;
            padding: 12px 18px !important;
            color: rgba(255, 255, 255, 0.88) !important;
            background: none !important;
            border: none !important;
            border-radius: 10px !important;
            cursor: pointer !important;
            font-size: 0.9rem !important;
            text-align: left !important;
            transition: all 0.2s ease !important;
        }

        .sidebar .dropdown-toggle:hover,
        .sidebar .dropdown-toggle.active,
        .sidebar .dropdown.active > .dropdown-toggle {
            background: rgba(255, 255, 255, 0.18) !important;
            color: #ffffff !important;
            box-shadow: inset 0 0 0 1px rgba(255, 255, 255, 0.18) !important;
        }

        .sidebar .dropdown-toggle i.chevron {
            transition: transform 0.3s cubic-bezier(0.4, 0, 0.2, 1) !important;
            font-size: 0.8rem !important;
        }

        .sidebar .dropdown.active > .dropdown-toggle i.chevron {
            transform: rotate(180deg) !important;
        }

        .sidebar .dropdown-menu {
            display: flex !important;
            flex-direction: column !important;
            padding-left: 36px !important;
            margin-top: 4px !important;
            max-height: 0 !important;
            opacity: 0 !important;
            visibility: hidden !important;
            overflow: hidden !important;
            transition: max-height 0.38s cubic-bezier(0.4, 0, 0.2, 1), opacity 0.28s ease, visibility 0.28s ease !important;
            position: static !important;
            float: none !important;
            background: transparent !important;
            border: none !important;
            box-shadow: none !important;
        }

        .sidebar .dropdown.active > .dropdown-menu {
            max-height: 600px !important;
            opacity: 1 !important;
            visibility: visible !important;
            padding-bottom: 8px !important;
            overflow: visible !important;
        }

        .sidebar .dropdown-menu a {
            padding: 8px 16px !important;
            color: rgba(255, 255, 255, 0.82) !important;
            text-decoration: none !important;
            border-radius: 8px !important;
            font-size: 0.85rem !important;
            display: flex !important;
            align-items: center !important;
            gap: 8px !important;
            opacity: 0 !important;
            transform: translateY(-4px) !important;
            transition: opacity 0.24s ease, transform 0.24s ease, background 0.2s ease !important;
        }

        .sidebar .dropdown.active > .dropdown-menu a {
            opacity: 1 !important;
            transform: translateY(0) !important;
            pointer-events: auto !important;
        }

        .sidebar .dropdown-menu a:hover,
        .sidebar .dropdown-menu a.active {
            background: rgba(255, 255, 255, 0.18) !important;
            color: #ffffff !important;
        }

        .sidebar.collapsed .sidebar-header {
            padding: 18px 10px 14px !important;
        }

        .sidebar.collapsed .sidebar-header img {
            width: 42px !important;
            margin: 0 auto !important;
        }

        .sidebar.collapsed .sidebar-section,
        .sidebar.collapsed li[style*="text-transform"],
        .sidebar.collapsed .dropdown-menu {
            display: none !important;
        }

        .sidebar.collapsed .sidebar-header h3,
        .sidebar.collapsed .sidebar-header p {
            display: block !important;
            max-height: 0 !important;
            margin: 0 !important;
            opacity: 0 !important;
            overflow: hidden !important;
            transform: translateY(-4px) !important;
            pointer-events: none !important;
        }

        .sidebar .menu-text {
            display: inline-block !important;
            max-width: 190px !important;
            opacity: 1 !important;
            transform: translateX(0) !important;
            transition: opacity 0.24s ease, transform 0.32s cubic-bezier(0.22, 1, 0.36, 1), max-width 0.36s cubic-bezier(0.22, 1, 0.36, 1) !important;
            vertical-align: middle !important;
        }

        .sidebar.collapsed .menu-text {
            display: inline-block !important;
            max-width: 0 !important;
            opacity: 0 !important;
            transform: translateX(-8px) !important;
            overflow: hidden !important;
            pointer-events: none !important;
        }

        .sidebar.collapsed .sidebar-menu {
            padding: 0 10px !important;
            align-items: center !important;
        }

        .sidebar.collapsed .sidebar-menu li {
            width: 100% !important;
        }

        .sidebar.collapsed .sidebar-menu li a,
        .sidebar.collapsed .sidebar-menu .dropdown-toggle {
            width: 50px !important;
            height: 50px !important;
            min-width: 50px !important;
            padding: 0 !important;
            margin: 0 auto 8px !important;
            justify-content: center !important;
            gap: 0 !important;
            text-align: center !important;
        }

        .sidebar .sidebar-menu li a,
        .sidebar .sidebar-menu .dropdown-toggle {
            transition: width 0.36s cubic-bezier(0.22, 1, 0.36, 1), min-width 0.36s cubic-bezier(0.22, 1, 0.36, 1), height 0.36s cubic-bezier(0.22, 1, 0.36, 1), padding 0.36s cubic-bezier(0.22, 1, 0.36, 1), margin 0.36s cubic-bezier(0.22, 1, 0.36, 1), gap 0.32s ease, background-color 0.2s ease, box-shadow 0.2s ease, color 0.2s ease !important;
        }

        .sidebar.collapsed .sidebar-menu li a i,
        .sidebar.collapsed .sidebar-menu .dropdown-toggle i:not(.chevron) {
            width: auto !important;
            min-width: 0 !important;
            margin: 0 !important;
            font-size: 1.12rem !important;
            line-height: 1 !important;
        }

        .sidebar.collapsed .sidebar-menu .dropdown-toggle span {
            display: inline-flex !important;
            align-items: center !important;
            justify-content: center !important;
        }

        .sidebar.collapsed .sidebar-menu .dropdown-toggle > span {
            width: 100% !important;
            gap: 0 !important;
            font-size: 0 !important;
        }

        .sidebar.collapsed .sidebar-menu .dropdown-toggle > span > i {
            display: inline-flex !important;
            align-items: center !important;
            justify-content: center !important;
            font-size: 1.12rem !important;
        }

        .sidebar.collapsed .sidebar-menu .dropdown-toggle > i.chevron,
        .sidebar.collapsed .sidebar-menu .dropdown-toggle i.chevron {
            display: none !important;
        }

        .sidebar.collapsed .sidebar-menu hr {
            width: 50px !important;
            margin: 10px auto !important;
        }

        .sidebar:not(.collapsed) .sidebar-menu li a,
        .sidebar:not(.collapsed) .sidebar-menu .dropdown-toggle,
        .sidebar:not(.collapsed) .menu-text {
            white-space: nowrap !important;
        }

        .sidebar:not(.collapsed) .sidebar-menu li a,
        .sidebar:not(.collapsed) .sidebar-menu .dropdown-toggle {
            min-width: 0 !important;
        }

        .sidebar:not(.collapsed) .menu-text {
            overflow: hidden !important;
            text-overflow: ellipsis !important;
        }

        .sidebar a.sidebar-no-access,
        .sidebar .dropdown-menu a.sidebar-no-access {
            cursor: pointer !important;
        }

        .sidebar-access-message {
            position: fixed;
            right: 24px;
            top: 96px;
            z-index: 12000;
            max-width: 420px;
            display: none;
            align-items: flex-start;
            gap: 12px;
            padding: 15px 18px;
            border: 1px solid #fde68a;
            border-left: 5px solid #f59e0b;
            border-radius: 12px;
            background: #fffbeb;
            color: #78350f;
            box-shadow: 0 18px 40px rgba(15, 23, 42, 0.16);
            font-size: 0.95rem;
            line-height: 1.45;
        }

        .sidebar-access-message.show {
            display: flex;
        }

        .sidebar-access-message i {
            margin-top: 2px;
            color: #d97706;
        }

        html.access-preview-active .main > *:not(.access-denied-overlay) {
            filter: blur(5px);
            pointer-events: none !important;
            user-select: none !important;
        }

        html.access-preview-active .main {
            position: relative;
            min-height: 100vh;
        }

        html.access-preview-active .access-denied-overlay {
            position: fixed;
            left: calc(var(--sidebar-width, 280px) + 32px);
            right: 32px;
            top: 50%;
            transform: translateY(-50%);
            z-index: 15000;
            padding: 54px 34px;
            border-radius: 20px;
            background: rgba(246, 252, 255, 0.86);
            border: 1px solid rgba(255, 255, 255, 0.72);
            box-shadow: 0 30px 80px rgba(2, 62, 95, 0.18);
            backdrop-filter: blur(18px) saturate(1.16);
            -webkit-backdrop-filter: blur(18px) saturate(1.16);
            text-align: center;
            color: var(--text-main, #163447);
        }

        html.access-preview-active .sidebar.collapsed + .main .access-denied-overlay {
            left: 102px;
        }

        .access-denied-overlay .access-denied-icon {
            width: 92px;
            height: 92px;
            margin: 0 auto 24px;
            display: flex;
            align-items: center;
            justify-content: center;
            border-radius: 24px;
            background: #fff4e6;
            color: #d97706;
            font-size: 2.35rem;
        }

        .access-denied-overlay h2 {
            color: var(--deep-blue, #023e5f);
            font-size: 2rem;
            line-height: 1.2;
            margin: 0 0 14px;
            font-weight: 800;
        }

        .access-denied-overlay .access-denied-menu {
            display: inline-flex;
            align-items: center;
            justify-content: center;
            margin: 0 0 26px;
            padding: 10px 18px;
            border-radius: 999px;
            background: #e6f4ff;
            color: var(--primary-dark, #005f99);
            font-size: 1.05rem;
            font-weight: 800;
        }

        .access-denied-overlay p {
            max-width: 780px;
            margin: 0 auto 28px;
            color: var(--text-muted, #2d5367);
            font-size: 1.05rem;
            line-height: 1.55;
        }

        .access-denied-actions {
            display: flex;
            justify-content: center;
            gap: 14px;
            flex-wrap: wrap;
        }

        .access-denied-actions a {
            display: inline-flex;
            align-items: center;
            justify-content: center;
            gap: 9px;
            min-width: 184px;
            padding: 14px 20px;
            border-radius: 13px;
            text-decoration: none !important;
            font-weight: 800;
            transition: transform 0.2s ease, filter 0.2s ease, box-shadow 0.2s ease, background-color 0.2s ease, border-color 0.2s ease;
            will-change: transform;
        }

        .access-denied-actions a:hover {
            transform: translateY(-1px);
            filter: brightness(1.04);
            box-shadow: 0 14px 28px rgba(0, 119, 182, 0.18);
        }

        .access-denied-actions a:active {
            transform: translateY(0);
            filter: brightness(0.98);
        }

        .access-denied-actions .btn-dashboard {
            background: var(--primary, #0077b6);
            color: #fff !important;
            border: 1px solid var(--primary, #0077b6);
        }

        .access-denied-actions .btn-retry {
            background: rgba(255, 255, 255, 0.74);
            color: var(--primary, #0077b6) !important;
            border: 1px solid rgba(0, 119, 182, 0.18);
        }

        @media (max-width: 992px) {
            html.access-preview-active .access-denied-overlay {
                left: 92px;
                right: 18px;
            }
        }

        @media (max-width: 640px) {
            html.access-preview-active .access-denied-overlay {
                left: 14px;
                right: 14px;
                padding: 34px 18px;
            }

            .access-denied-overlay h2 {
                font-size: 1.45rem;
            }
        }

        .user-info {
            position: relative;
            display: flex;
            align-items: center;
            gap: 14px;
            overflow: visible !important;
            z-index: 9000;
        }

        .top-bar {
            position: relative;
            overflow: visible !important;
            z-index: 8500;
        }

        .top-user-identity {
            display: flex;
            flex-direction: column;
            align-items: flex-start;
            gap: 2px;
            min-width: 0;
        }

        .user-info .top-user-name,
        .user-info .top-user-department,
        .user-info .top-user-meta {
            color: var(--primary, #0077b6) !important;
            font-weight: 700 !important;
            line-height: 1.2;
            white-space: nowrap;
            display: block;
        }

        .user-info .top-user-department {
            font-size: 0.88rem;
        }

        .user-info .top-user-meta {
            display: inline-flex;
            align-items: center;
            gap: 8px;
        }

        .user-info .top-user-role {
            display: inline-flex;
            align-items: center;
            justify-content: center;
            padding: 3px 9px;
            border-radius: 999px;
            background: rgba(0, 119, 182, 0.10);
            color: var(--primary-dark, #005f99) !important;
            border: 1px solid rgba(0, 119, 182, 0.16);
            font-size: 0.68rem;
            font-weight: 850 !important;
            line-height: 1;
            letter-spacing: 0.02em;
        }

        .user-info .top-user-role.role-superadmin {
            background: linear-gradient(90deg, #6c5ce7, #5b4cdb);
            color: #fff !important;
            border-color: transparent;
        }

        .user-info .top-user-role.role-admin {
            background: rgba(26, 153, 85, 0.13);
            color: #12653a !important;
            border-color: rgba(26, 153, 85, 0.20);
        }

        .top-profile-menu {
            position: relative;
            display: inline-flex;
            align-items: center;
        }

        .top-notification-menu {
            position: relative;
            display: inline-flex;
            align-items: center;
            font-family: var(--app-font) !important;
            overflow: visible !important;
            z-index: 9200;
        }

        .top-notification-trigger {
            position: relative;
            width: 42px;
            height: 42px;
            border: 1px solid var(--border);
            border-radius: 50%;
            background: #fff;
            color: var(--primary);
            display: inline-flex;
            align-items: center;
            justify-content: center;
            cursor: pointer;
            padding: 0;
            box-shadow: 0 2px 8px rgba(0, 0, 0, 0.08);
        }

        .top-notification-trigger.has-alert {
            color: #b45309;
            border-color: #f8d28a;
            background: #fff8e7;
        }

        .notification-badge {
            position: absolute;
            top: -5px;
            right: -4px;
            min-width: 20px;
            height: 20px;
            padding: 0 5px;
            border-radius: 999px;
            background: #dc2626;
            color: #fff !important;
            font-size: 0.68rem;
            font-weight: 800;
            display: inline-flex;
            align-items: center;
            justify-content: center;
            border: 2px solid #fff;
        }

        .top-notification-dropdown {
            position: absolute;
            top: calc(100% + 10px);
            right: 0;
            width: min(360px, calc(100vw - 36px));
            font-family: var(--app-font) !important;
            background: rgba(255, 255, 255, 0.70) !important;
            border: 1px solid rgba(255, 255, 255, 0.58);
            border-radius: 18px;
            box-shadow: 0 22px 50px rgba(2, 62, 95, 0.18);
            padding: 10px;
            display: none;
            max-height: min(520px, calc(100vh - 110px));
            overflow: hidden;
            overflow-x: hidden;
            overscroll-behavior: contain;
            z-index: 99999;
            scrollbar-width: thin;
            scrollbar-color: #8f8f8f transparent;
            backdrop-filter: blur(24px) saturate(1.35);
            -webkit-backdrop-filter: blur(24px) saturate(1.35);
        }

        .notification-list-scroll {
            display: grid;
            gap: 8px;
            max-height: min(330px, calc(100vh - 250px));
            overflow-y: auto;
            overflow-x: hidden;
            padding-right: 2px;
            overscroll-behavior: contain;
            scrollbar-width: thin;
            scrollbar-color: #8f8f8f transparent;
        }

        .notification-list-scroll::-webkit-scrollbar {
            width: 10px;
        }

        .notification-list-scroll::-webkit-scrollbar-track {
            background: transparent;
        }

        .notification-list-scroll::-webkit-scrollbar-thumb {
            background: #8f8f8f;
            border-radius: 999px;
            border: 2px solid transparent;
            background-clip: content-box;
        }

        .notification-list-scroll::-webkit-scrollbar-button {
            display: none;
            width: 0;
            height: 0;
        }

        .top-notification-menu.open .top-notification-dropdown {
            display: block;
        }

        .notification-head {
            display: flex;
            justify-content: space-between;
            gap: 10px;
            padding: 10px 12px 12px;
            border-bottom: 1px solid rgba(0, 119, 182, 0.12);
            color: var(--text-main);
            font-size: 1rem;
            font-weight: 700;
        }

        .notification-head span {
            color: var(--primary) !important;
            font-size: 0.78rem;
            font-weight: 600;
        }

        .notification-item {
            display: grid !important;
            grid-template-columns: 34px minmax(0, 1fr);
            gap: 10px;
            padding: 11px;
            border-radius: 14px;
            text-decoration: none;
            color: var(--text-main) !important;
            background: rgba(255, 255, 255, 0.58) !important;
            border: 1px solid rgba(255, 255, 255, 0.54);
            font-family: var(--app-font) !important;
            box-shadow: 0 10px 24px rgba(2, 62, 95, 0.08);
            backdrop-filter: blur(16px) saturate(1.18);
            -webkit-backdrop-filter: blur(16px) saturate(1.18);
        }

        .notification-item:hover {
            background: rgba(255, 255, 255, 0.78) !important;
        }

        .notification-item.unread {
            background: rgba(255, 248, 231, 0.72) !important;
            border-left: 3px solid #f59e0b;
        }

        .notification-item.latest {
            position: relative;
        }

        .notification-latest-label {
            display: inline-flex !important;
            align-items: center;
            justify-content: center;
            width: fit-content;
            margin-bottom: 5px;
            padding: 3px 8px;
            border-radius: 999px;
            background: #f59e0b;
            color: #111827 !important;
            font-family: var(--app-font) !important;
            font-size: 0.68rem;
            font-weight: 800;
            line-height: 1.1;
            text-transform: uppercase;
            letter-spacing: 0.02em;
        }

        .notification-read-all {
            width: 100%;
            border: 0;
            background: rgba(230, 246, 252, 0.72);
            color: var(--primary);
            border-radius: 12px;
            padding: 9px 10px;
            margin: 8px 0 4px;
            cursor: pointer;
            font-weight: 600;
            font-family: var(--app-font) !important;
            display: flex;
            align-items: center;
            justify-content: center;
            gap: 8px;
            border: 1px solid rgba(255, 255, 255, 0.54);
            backdrop-filter: blur(14px) saturate(1.18);
            -webkit-backdrop-filter: blur(14px) saturate(1.18);
        }

        .notification-read-all:hover {
            background: rgba(223, 241, 248, 0.88);
        }

        .notification-icon {
            width: 34px;
            height: 34px;
            border-radius: 50%;
            background: rgba(255, 244, 229, 0.82);
            color: #b45309 !important;
            display: inline-flex;
            align-items: center;
            justify-content: center;
        }

        .notification-item strong,
        .notification-item small,
        .notification-item em,
        .notification-item time {
            display: block;
            line-height: 1.35;
        }

        .notification-item strong {
            color: var(--primary-dark, #005f99) !important;
            font-size: 0.86rem;
            font-weight: 700;
            margin-bottom: 2px;
        }

        .notification-item small {
            color: var(--primary) !important;
            font-size: 0.78rem;
            font-weight: 600;
        }

        .notification-item em {
            color: var(--text-muted) !important;
            font-size: 0.76rem;
            font-style: normal;
            margin-top: 2px;
        }

        .notification-time {
            color: var(--text-muted) !important;
            font-family: var(--app-font) !important;
            font-size: 0.74rem;
            font-style: normal;
            font-weight: 500;
            margin-top: 4px;
        }

        .notification-time i {
            margin-right: 4px;
            color: #64748b !important;
        }

        .notification-empty {
            padding: 18px 12px;
            color: var(--text-muted);
            display: flex;
            align-items: center;
            justify-content: center;
            gap: 8px;
            font-size: 0.88rem;
        }

        .top-profile-trigger {
            position: relative;
            width: 42px;
            height: 42px;
            border: 1px solid var(--border);
            border-radius: 50%;
            background: var(--card, var(--bg-card, #fff));
            color: var(--primary);
            display: inline-flex;
            align-items: center;
            justify-content: center;
            cursor: pointer;
            padding: 0;
            overflow: visible;
            box-shadow: 0 2px 8px rgba(0, 0, 0, 0.08);
        }

        .top-profile-trigger img,
        .top-profile-trigger > span {
            width: 100%;
            height: 100%;
            border-radius: 50%;
        }

        .top-profile-trigger img {
            object-fit: cover;
            display: block;
        }

        .top-profile-trigger > span {
            display: inline-flex;
            align-items: center;
            justify-content: center;
            background: linear-gradient(135deg, var(--primary), var(--secondary));
            color: #fff !important;
            font-weight: 700;
            font-size: 0.86rem;
        }

        .top-profile-trigger .fa-chevron-down {
            position: absolute;
            right: -2px;
            bottom: -2px;
            width: 18px;
            height: 18px;
            border-radius: 50%;
            display: inline-flex;
            align-items: center;
            justify-content: center;
            background: var(--card, var(--bg-card, #fff));
            color: var(--primary);
            border: 1px solid var(--border);
            font-size: 0.62rem;
        }

        .top-profile-dropdown {
            position: absolute;
            top: calc(100% + 10px);
            right: 0;
            min-width: 190px;
            background: #fff !important;
            border: 1px solid var(--border);
            border-radius: 8px;
            box-shadow: 0 14px 30px rgba(14, 43, 68, 0.16);
            padding: 8px;
            display: none;
            z-index: 1200;
        }

        .top-profile-menu.open .top-profile-dropdown {
            display: block;
        }

        .top-profile-dropdown a,
        .top-profile-dropdown button {
            width: 100%;
            border: 0;
            background: #fff !important;
            color: var(--text-main) !important;
            text-decoration: none;
            display: flex;
            align-items: center;
            gap: 10px;
            padding: 10px 12px;
            border-radius: 6px;
            font-size: 0.9rem;
            font-weight: 600;
            text-align: left;
            cursor: pointer;
        }

        .top-profile-dropdown a:hover,
        .top-profile-dropdown button:hover {
            background: #e6f4ff;
            color: var(--primary) !important;
            text-decoration: none !important;
        }

        .top-profile-dropdown a,
        .top-profile-dropdown a:hover,
        .top-profile-dropdown a:focus,
        .top-profile-dropdown a:active,
        .top-profile-dropdown a:visited,
        .top-profile-dropdown button,
        .top-profile-dropdown button:hover,
        .top-profile-dropdown button:focus,
        .top-profile-dropdown button:active {
            text-decoration: none !important;
        }

        .top-profile-dropdown i {
            width: 18px;
            text-align: center;
            color: var(--primary);
        }

        .alert,
        .success-msg,
        .error-msg,
        .success-message,
        .error-message,
        .status-message {
            transition: opacity 0.35s ease, transform 0.35s ease, max-height 0.35s ease, margin 0.35s ease, padding 0.35s ease, border-width 0.35s ease;
            will-change: opacity, transform, max-height;
        }

        .system-message-dismissing {
            opacity: 0 !important;
            transform: translateY(-6px) !important;
            max-height: 0 !important;
            margin-top: 0 !important;
            margin-bottom: 0 !important;
            padding-top: 0 !important;
            padding-bottom: 0 !important;
            border-width: 0 !important;
            overflow: hidden !important;
            pointer-events: none !important;
        }

        .top-profile-dropdown a span,
        .top-profile-dropdown button span {
            color: inherit !important;
            font-weight: inherit;
        }

        @media (max-width: 768px) {
            .user-info {
                gap: 8px;
            }

            .top-user-identity {
                display: none;
            }

            .top-notification-dropdown {
                right: -58px;
            }
        }
    </style>
    <div class="sidebar-access-message" id="sidebarAccessMessage" role="status" aria-live="polite">
        <i class="fas fa-exclamation-circle"></i>
        <span>Anda tiada akses ke bahagian ini. Sila hubungi Superadmin untuk membuka capaian.</span>
    </div>
    <?php if ($is_access_preview): ?>
        <div class="access-denied-overlay" role="alert" aria-live="assertive">
            <div class="access-denied-icon"><i class="fas fa-lock"></i></div>
            <h2>Anda Tiada Akses Ke Bahagian Ini</h2>
            <div class="access-denied-menu"><?php echo htmlspecialchars($access_preview_menu); ?></div>
            <p>Akaun anda belum diberi capaian untuk membuka menu ini. Sila hubungi Superadmin untuk membuka capaian yang diperlukan.</p>
            <div class="access-denied-actions">
                <a class="btn-dashboard" href="dashboard.php"><i class="fas fa-home"></i> Ke Dashboard</a>
                <a class="btn-retry" href="<?php echo htmlspecialchars($access_retry_url); ?>"><i class="fas fa-rotate-right"></i> Cuba Semula</a>
            </div>
        </div>
    <?php endif; ?>
    <script>
        window.toggleSidebarDropdown = function (btn, e) {
            if (e) {
                if (typeof e.preventDefault === 'function') e.preventDefault();
                if (typeof e.stopPropagation === 'function') e.stopPropagation();
                if (typeof e.stopImmediatePropagation === 'function') e.stopImmediatePropagation();
            }
            var dropdown = btn ? btn.closest('.dropdown') : null;
            if (!dropdown) return;
            var isCurrentlyActive = dropdown.classList.contains('active');
            var sidebar = btn.closest('.sidebar') || document.getElementById('sidebar') || document;
            if (sidebar) {
                sidebar.querySelectorAll('.dropdown').forEach(function (dd) {
                    if (dd !== dropdown) {
                        dd.classList.remove('active');
                        var tog = dd.querySelector('.dropdown-toggle');
                        if (tog) tog.classList.remove('active');
                    }
                });
            }
            dropdown.classList.toggle('active', !isCurrentlyActive);
            btn.classList.toggle('active', !isCurrentlyActive);
        };

        document.addEventListener('DOMContentLoaded', function () {
            var sidebarAccessTimer = null;
            var sidebarAccessMessage = document.getElementById('sidebarAccessMessage');

            if (document.documentElement.classList.contains('access-preview-active')) {
                document.querySelectorAll('.main form').forEach(function (form) {
                    form.addEventListener('submit', function (event) {
                        event.preventDefault();
                    });
                });

                document.querySelectorAll('.main input, .main select, .main textarea, .main button').forEach(function (control) {
                    control.disabled = true;
                    control.setAttribute('aria-disabled', 'true');
                });
            }

            document.querySelectorAll('[data-access-denied="1"]').forEach(function (link) {
                link.addEventListener('click', function (event) {
                    event.preventDefault();
                    event.stopPropagation();

                    if (!sidebarAccessMessage) {
                        alert(link.getAttribute('data-access-message') || 'Anda tiada akses ke bahagian ini.');
                        return;
                    }

                    var textTarget = sidebarAccessMessage.querySelector('span');
                    if (textTarget) {
                        textTarget.textContent = link.getAttribute('data-access-message') || 'Anda tiada akses ke bahagian ini.';
                    }

                    sidebarAccessMessage.classList.add('show');
                    window.clearTimeout(sidebarAccessTimer);
                    sidebarAccessTimer = window.setTimeout(function () {
                        sidebarAccessMessage.classList.remove('show');
                    }, 4200);
                });
            });

            var autoDismissMessages = document.querySelectorAll(
                '.alert, .success-msg, .error-msg, .success-message, .error-message, .status-message'
            );

            autoDismissMessages.forEach(function (message) {
                if (!message || message.closest('.top-notification-menu')) return;
                if (message.dataset && message.dataset.autoDismiss === 'false') return;
                if (window.getComputedStyle(message).display === 'none') return;

                window.setTimeout(function () {
                    message.classList.add('system-message-dismissing');
                    window.setTimeout(function () {
                        if (message.parentNode) {
                            message.parentNode.removeChild(message);
                        }
                    }, 420);
                }, 4500);
            });

            if (window.history && window.history.replaceState && window.location.search) {
                var transientParams = [
                    'success',
                    'error',
                    'msg',
                    'support_success',
                    'support_error',
                    'source'
                ];
                var url = new URL(window.location.href);
                var changed = false;

                transientParams.forEach(function (param) {
                    if (url.searchParams.has(param)) {
                        url.searchParams.delete(param);
                        changed = true;
                    }
                });

                if (changed) {
                    window.history.replaceState({}, document.title, url.pathname + url.search + url.hash);
                }
            }

            var profileMenus = document.querySelectorAll('.top-profile-menu');
            var notificationMenus = document.querySelectorAll('.top-notification-menu');

            function closeMenus() {
                profileMenus.forEach(function (menu) {
                    menu.classList.remove('open');
                    var trigger = menu.querySelector('.top-profile-trigger');
                    if (trigger) {
                        trigger.setAttribute('aria-expanded', 'false');
                    }
                });
                notificationMenus.forEach(function (menu) {
                    menu.classList.remove('open');
                    var trigger = menu.querySelector('.top-notification-trigger');
                    if (trigger) {
                        trigger.setAttribute('aria-expanded', 'false');
                    }
                });
            }

            profileMenus.forEach(function (menu) {
                var trigger = menu.querySelector('.top-profile-trigger');
                if (trigger) {
                    trigger.addEventListener('click', function (event) {
                        event.stopPropagation();
                        var willOpen = !menu.classList.contains('open');
                        closeMenus();
                        menu.classList.toggle('open', willOpen);
                        trigger.setAttribute('aria-expanded', willOpen ? 'true' : 'false');
                    });
                }
            });

            notificationMenus.forEach(function (menu) {
                var trigger = menu.querySelector('.top-notification-trigger');
                function getMenuAlertIds() {
                    try {
                        return JSON.parse(menu.getAttribute('data-alert-ids') || '[]');
                    } catch (error) {
                        return [];
                    }
                }

                function setMenuAlertIds(alertIds) {
                    menu.setAttribute('data-alert-ids', JSON.stringify(Array.from(new Set(alertIds))));
                }

                function updateNotificationCount(nextCount) {
                    nextCount = Math.max(0, parseInt(nextCount || '0', 10));
                    menu.setAttribute('data-alert-count', String(nextCount));

                    var badge = menu.querySelector('.notification-badge');
                    if (nextCount > 0) {
                        if (!badge && trigger) {
                            badge = document.createElement('span');
                            badge.className = 'notification-badge';
                            trigger.appendChild(badge);
                        }
                        if (badge) badge.textContent = nextCount > 9 ? '9+' : String(nextCount);
                        if (trigger) trigger.classList.add('has-alert');
                    } else {
                        if (badge) badge.remove();
                        if (trigger) trigger.classList.remove('has-alert');
                    }

                    var countText = menu.querySelector('.notification-count-text');
                    if (countText) {
                        countText.textContent = nextCount > 0 ? (nextCount + ' baru') : 'Semua dibaca';
                    }

                    var readAllButton = menu.querySelector('.notification-read-all');
                    if (readAllButton && nextCount === 0) {
                        readAllButton.remove();
                    }
                }

                function markNotificationItemRead(item) {
                    if (!item || !item.classList.contains('unread')) return;
                    var key = item.getAttribute('data-notification-key') || '';
                    item.classList.remove('unread');

                    var alertIds = getMenuAlertIds().filter(function (alertId) {
                        return alertId !== key;
                    });
                    setMenuAlertIds(alertIds);
                    updateNotificationCount(parseInt(menu.getAttribute('data-alert-count') || '0', 10) - 1);
                }

                function submitNotificationRead(keys) {
                    var cleanKeys = (keys || []).filter(Boolean);
                    if (!cleanKeys.length) return Promise.resolve();

                    var body = new URLSearchParams();
                    body.append('notification_keys', JSON.stringify(cleanKeys));

                    return fetch('process_notifications.php?action=read_all', {
                        method: 'POST',
                        headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
                        body: body.toString()
                    }).then(function (response) {
                        return response.json();
                    }).catch(function () {
                        return null;
                    });
                }

                if (trigger) {
                    trigger.addEventListener('click', function (event) {
                        event.stopPropagation();
                        var willOpen = !menu.classList.contains('open');
                        closeMenus();
                        menu.classList.toggle('open', willOpen);
                        trigger.setAttribute('aria-expanded', willOpen ? 'true' : 'false');
                    });
                }

                var readAllButton = menu.querySelector('.notification-read-all');
                if (readAllButton) {
                    readAllButton.addEventListener('click', function (event) {
                        event.preventDefault();
                        event.stopPropagation();
                        var alertIds = getMenuAlertIds();
                        if (!alertIds.length) return;

                        submitNotificationRead(alertIds)
                            .then(function (result) {
                                if (!result || !result.ok) return;
                                setMenuAlertIds([]);
                                updateNotificationCount(0);
                                menu.querySelectorAll('.notification-item.unread').forEach(function (item) {
                                    item.classList.remove('unread');
                                });
                            });
                    });
                }

                menu.querySelectorAll('.notification-item.unread[data-notification-key]').forEach(function (item) {
                    item.addEventListener('click', function (event) {
                        var key = item.getAttribute('data-notification-key') || '';
                        if (!key) return;

                        event.preventDefault();
                        event.stopPropagation();
                        var targetUrl = item.href;

                        markNotificationItemRead(item);
                        submitNotificationRead([key]).finally(function () {
                            window.location.href = targetUrl;
                        });
                    });
                });

                var alertCount = parseInt(menu.getAttribute('data-alert-count') || '0', 10);
                if (alertCount > 0) {
                    try {
                        var alertIds = JSON.parse(menu.getAttribute('data-alert-ids') || '[]');
                        var storageKey = 'sistem_fail_seen_draft_notifications';
                        var seenIds = JSON.parse(localStorage.getItem(storageKey) || '[]');
                        var newIds = alertIds.filter(function (id) {
                            return seenIds.indexOf(id) === -1;
                        });

                        if (newIds.length > 0) {
                            alert('Anda mempunyai ' + alertCount + ' surat baru yang memerlukan tindakan.');
                            localStorage.setItem(storageKey, JSON.stringify(Array.from(new Set(seenIds.concat(alertIds))).slice(-100)));
                        }
                    } catch (error) {
                        // Biarkan loceng notifikasi dipaparkan walaupun localStorage tidak tersedia.
                    }
                }
            });

            document.addEventListener('click', closeMenus);
            document.addEventListener('keydown', function (event) {
                if (event.key === 'Escape') closeMenus();
            });
        });
    </script>
    <?php
}

function getLetterDraftMapByDocumentIds($conn, $document_ids) {
    $document_ids = array_values(array_filter(array_map('intval', (array) $document_ids), function ($id) {
        return $id > 0;
    }));

    if (empty($document_ids)) {
        return [];
    }

    $placeholders = implode(',', array_fill(0, count($document_ids), '?'));
    $types = str_repeat('i', count($document_ids));
    $sql = "SELECT *
        FROM letter_drafts
        WHERE document_id IN ({$placeholders}) AND deleted_at IS NULL";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return [];
    }

    mysqli_stmt_bind_param($stmt, $types, ...$document_ids);
    mysqli_stmt_execute($stmt);
    $rows = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
    $map = [];

    foreach ($rows as $row) {
        $map[(int) ($row['document_id'] ?? 0)] = $row;
    }

    return $map;
}

function getRolePermissions($conn, $role = null) {
    $sql = "SELECT role_name, module, action, is_allowed FROM role_permissions";
    $params = [];
    $types = '';

    if ($role !== null) {
        $sql .= " WHERE role_name = ?";
        $params[] = strtolower(trim((string) $role));
        $types = 's';
    }

    $sql .= " ORDER BY role_name, module, action";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return [];
    }

    if (!empty($params)) {
        mysqli_stmt_bind_param($stmt, $types, ...$params);
    }

    mysqli_stmt_execute($stmt);
    $rows = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
    $matrix = [];

    foreach ($rows as $row) {
        $matrix[$row['role_name']][$row['module']][$row['action']] = (int) ($row['is_allowed'] ?? 0);
    }

    return $matrix;
}

function getDepartmentPermissions($conn, $department = null) {
    $sql = "SELECT department, module, action, is_allowed FROM department_permissions";
    $params = [];
    $types = '';

    if ($department !== null) {
        $sql .= " WHERE department = ?";
        $params[] = normalizeDepartmentValue($department);
        $types = 's';
    }

    $sql .= " ORDER BY department, module, action";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return [];
    }

    if (!empty($params)) {
        mysqli_stmt_bind_param($stmt, $types, ...$params);
    }

    mysqli_stmt_execute($stmt);
    $rows = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
    $matrix = [];

    foreach ($rows as $row) {
        $department_key = normalizeDepartmentValue($row['department'] ?? '');
        if ($department_key === '') {
            continue;
        }
        $matrix[$department_key][$row['module']][$row['action']] = (int) ($row['is_allowed'] ?? 0);
    }

    return $matrix;
}

function getUserPermissions($conn, $user_id = null) {
    $sql = "SELECT user_id, module, action, is_allowed FROM user_permissions";
    $params = [];
    $types = '';

    if ($user_id !== null) {
        $sql .= " WHERE user_id = ?";
        $params[] = (int) $user_id;
        $types = 'i';
    }

    $sql .= " ORDER BY user_id, module, action";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return [];
    }

    if (!empty($params)) {
        mysqli_stmt_bind_param($stmt, $types, ...$params);
    }

    mysqli_stmt_execute($stmt);
    $rows = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
    $matrix = [];

    foreach ($rows as $row) {
        $user_key = (int) ($row['user_id'] ?? 0);
        if ($user_key <= 0) {
            continue;
        }
        $matrix[$user_key][$row['module']][$row['action']] = (int) ($row['is_allowed'] ?? 0);
    }

    return $matrix;
}

function getDepartmentUsers($conn, $department) {
    $department = normalizeDepartmentValue($department);
    if ($department === '') {
        return [];
    }

    $stmt = mysqli_prepare($conn, "SELECT id, fullname, email, role, status
        FROM users
        WHERE department = ?
        ORDER BY FIELD(role, 'superadmin', 'admin', 'user'), fullname ASC");
    if (!$stmt) {
        return [];
    }

    mysqli_stmt_bind_param($stmt, "s", $department);
    mysqli_stmt_execute($stmt);
    return mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
}
