<?php
declare(strict_types=1);

function can_access_data_maintenance(mysqli $conn): bool
{
    return can_access($conn, 'maintenance_clear_log')
        || can_access($conn, 'maintenance_clear_booking_queue')
        || can_access($conn, 'maintenance_clear_sale')
        || can_access($conn, 'maintenance_reset_operational_data')
        || can_access($conn, 'maintenance_cleanup_quick_service_guest')
        || can_access($conn, 'maintenance_compare_database_structure')
        || can_access($conn, 'maintenance_apply_database_structure')
        || can_access($conn, 'maintenance_backup_database')
        || can_access($conn, 'maintenance_restore_database');
}

function ensure_system_maintenance_schema(mysqli $conn): void
{
    static $checked = false;

    if ($checked) {
        return;
    }

    if (runtime_schema_bootstrap_is_current()) {
        $checked = true;

        return;
    }

    $checked = true;

    $conn->query("
        CREATE TABLE IF NOT EXISTS system_maintenance_logs (
            maintenance_log_id INT AUTO_INCREMENT PRIMARY KEY,
            maintenance_type VARCHAR(40) NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'completed',
            actor_employee_id INT NULL,
            actor_name VARCHAR(120) NULL,
            branch_name VARCHAR(120) NULL,
            snapshot_path VARCHAR(255) NULL,
            filters_json LONGTEXT NULL,
            preview_json LONGTEXT NULL,
            result_json LONGTEXT NULL,
            note VARCHAR(255) NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_system_maintenance_created_at (created_at),
            INDEX idx_system_maintenance_type (maintenance_type),
            INDEX idx_system_maintenance_actor (actor_employee_id),
            INDEX idx_system_maintenance_branch (branch_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
}

function maintenance_export_directory(): string
{
    $directory = dirname(__DIR__) . '/storage/maintenance_exports';

    if (!is_dir($directory)) {
        @mkdir($directory, 0777, true);
    }

    return $directory;
}

function maintenance_database_backup_directory(): string
{
    $directory = dirname(__DIR__) . '/storage/database_backups';

    if (!is_dir($directory)) {
        @mkdir($directory, 0777, true);
    }

    return $directory;
}

function maintenance_command_available(string $functionName): bool
{
    if (!function_exists($functionName)) {
        return false;
    }

    $disabled = array_map('trim', explode(',', (string) ini_get('disable_functions')));
    return !in_array($functionName, $disabled, true);
}

function maintenance_database_connection_config(mysqli $conn): array
{
    static $config = null;

    if (is_array($config)) {
        return $config;
    }

    $port = 3306;
    $portResult = $conn->query("SELECT @@port AS port");
    if ($portResult instanceof mysqli_result) {
        $portRow = $portResult->fetch_assoc();
        $port = max(1, (int) ($portRow['port'] ?? 3306));
        $portResult->close();
    }

    $config = array(
        'host' => (string) ($GLOBALS['host'] ?? '127.0.0.1'),
        'user' => (string) ($GLOBALS['user'] ?? ''),
        'pass' => (string) ($GLOBALS['pass'] ?? ''),
        'database' => (string) ($GLOBALS['db'] ?? ''),
        'port' => $port,
    );

    return $config;
}

function maintenance_database_binary_candidates(string $binary): array
{
    $binary = strtolower(trim($binary));
    $xamppRoot = dirname(dirname(dirname(__DIR__)));
    $isWindows = DIRECTORY_SEPARATOR === '\\';
    $suffix = $isWindows ? '.exe' : '';
    $candidates = array();

    if ($xamppRoot !== '') {
        $candidates[] = $xamppRoot . DIRECTORY_SEPARATOR . 'mysql' . DIRECTORY_SEPARATOR . 'bin' . DIRECTORY_SEPARATOR . $binary . $suffix;
    }

    $candidates[] = $binary . $suffix;
    $candidates[] = '/usr/bin/' . $binary;
    $candidates[] = '/usr/local/bin/' . $binary;
    $candidates[] = '/opt/lampp/bin/' . $binary;

    return array_values(array_unique($candidates));
}

function maintenance_find_database_binary(string $binary): string
{
    foreach (maintenance_database_binary_candidates($binary) as $candidate) {
        if ($candidate === '') {
            continue;
        }

        if (is_file($candidate)) {
            return $candidate;
        }

        if (str_contains($candidate, DIRECTORY_SEPARATOR)) {
            continue;
        }

        if (!maintenance_command_available('shell_exec')) {
            continue;
        }

        $resolved = trim((string) @shell_exec((DIRECTORY_SEPARATOR === '\\' ? 'where ' : 'command -v ') . escapeshellarg($candidate)));
        if ($resolved !== '') {
            $lines = preg_split('/\r\n|\r|\n/', $resolved);
            $first = trim((string) ($lines[0] ?? ''));
            if ($first !== '') {
                return $first;
            }
        }
    }

    return '';
}

function maintenance_database_capabilities(mysqli $conn): array
{
    $backupDirectory = maintenance_database_backup_directory();
    $mysqldumpPath = maintenance_find_database_binary('mysqldump');
    $mysqlPath = maintenance_find_database_binary('mysql');
    $canShellExec = maintenance_command_available('exec');
    $backupMethod = ($canShellExec && $mysqldumpPath !== '') ? 'mysqldump' : 'php';
    $restoreMethod = ($canShellExec && $mysqlPath !== '') ? 'mysql' : 'php';

    return array(
        'backup_directory' => $backupDirectory,
        'mysqldump_path' => $mysqldumpPath,
        'mysql_path' => $mysqlPath,
        'backup_method' => $backupMethod,
        'restore_method' => $restoreMethod,
        'can_backup' => is_dir($backupDirectory) && is_writable($backupDirectory),
        'can_restore' => true,
    );
}

function maintenance_database_quote_identifier(string $identifier): string
{
    return '`' . str_replace('`', '``', trim($identifier)) . '`';
}

function maintenance_database_schema_objects(mysqli $conn): array
{
    $config = maintenance_database_connection_config($conn);
    $database = trim((string) ($config['database'] ?? ''));
    if ($database === '') {
        return array('tables' => array(), 'views' => array());
    }

    $sql = 'SHOW FULL TABLES FROM ' . maintenance_database_quote_identifier($database);
    $result = $conn->query($sql);
    if (!$result instanceof mysqli_result) {
        throw new RuntimeException('Database objects could not be listed for backup.');
    }

    $tables = array();
    $views = array();
    while ($row = $result->fetch_assoc()) {
        $values = array_values($row);
        $objectName = trim((string) ($values[0] ?? ''));
        $objectType = strtoupper(trim((string) ($values[1] ?? 'BASE TABLE')));
        if ($objectName === '') {
            continue;
        }

        if ($objectType === 'VIEW') {
            $views[] = $objectName;
        } else {
            $tables[] = $objectName;
        }
    }
    $result->close();

    sort($tables);
    sort($views);

    return array(
        'tables' => $tables,
        'views' => $views,
    );
}

function maintenance_database_show_create(mysqli $conn, string $objectType, string $objectName): string
{
    $commandType = strtoupper($objectType) === 'VIEW' ? 'VIEW' : 'TABLE';
    $sql = 'SHOW CREATE ' . $commandType . ' ' . maintenance_database_quote_identifier($objectName);
    $result = $conn->query($sql);
    if (!$result instanceof mysqli_result) {
        throw new RuntimeException('Create statement could not be loaded for ' . $objectName . '.');
    }

    $row = $result->fetch_assoc() ?: array();
    $result->close();

    foreach ($row as $key => $value) {
        if (stripos((string) $key, 'Create ') === 0 && is_string($value) && trim($value) !== '') {
            $statement = rtrim(trim($value), ';');
            if ($commandType === 'VIEW') {
                $statement = (string) preg_replace('/\s+DEFINER=`[^`]+`@`[^`]+`\s+/i', ' ', $statement);
            }
            return $statement;
        }
    }

    throw new RuntimeException('Create statement was empty for ' . $objectName . '.');
}

function maintenance_database_export_value(mysqli $conn, mixed $value): string
{
    if ($value === null) {
        return 'NULL';
    }

    if (is_bool($value)) {
        return $value ? '1' : '0';
    }

    if (is_int($value) || is_float($value)) {
        return (string) $value;
    }

    return "'" . $conn->real_escape_string((string) $value) . "'";
}

function maintenance_database_execute_backup_via_php(mysqli $conn, string $prefix = 'backup'): array
{
    $backupPath = maintenance_database_backup_directory() . DIRECTORY_SEPARATOR . maintenance_database_backup_file_name($prefix);
    $schema = maintenance_database_schema_objects($conn);
    $lines = array(
        '-- CCW native SQL backup',
        '-- Generated at ' . date('Y-m-d H:i:s'),
        'SET NAMES utf8mb4;',
        'SET FOREIGN_KEY_CHECKS=0;',
        '',
    );

    foreach ((array) ($schema['tables'] ?? array()) as $tableName) {
        $lines[] = '-- Structure for table ' . $tableName;
        $lines[] = 'DROP TABLE IF EXISTS ' . maintenance_database_quote_identifier((string) $tableName) . ';';
        $lines[] = maintenance_database_show_create($conn, 'table', (string) $tableName) . ';';
        $lines[] = '';

        $result = $conn->query('SELECT * FROM ' . maintenance_database_quote_identifier((string) $tableName));
        if (!$result instanceof mysqli_result) {
            throw new RuntimeException('Data rows could not be read from ' . $tableName . '.');
        }

        $fields = $result->fetch_fields();
        $columnNames = array_map(static function (object $field): string {
            return maintenance_database_quote_identifier((string) ($field->name ?? ''));
        }, $fields);

        $batch = array();
        while ($row = $result->fetch_assoc()) {
            $values = array();
            foreach ($fields as $field) {
                $fieldName = (string) ($field->name ?? '');
                $values[] = maintenance_database_export_value($conn, $row[$fieldName] ?? null);
            }

            $batch[] = '(' . implode(', ', $values) . ')';
            if (count($batch) >= 100) {
                $lines[] = 'INSERT INTO ' . maintenance_database_quote_identifier((string) $tableName)
                    . ' (' . implode(', ', $columnNames) . ') VALUES '
                    . implode(",\n", $batch) . ';';
                $batch = array();
            }
        }
        $result->close();

        if ($batch !== array()) {
            $lines[] = 'INSERT INTO ' . maintenance_database_quote_identifier((string) $tableName)
                . ' (' . implode(', ', $columnNames) . ') VALUES '
                . implode(",\n", $batch) . ';';
        }

        $lines[] = '';
    }

    foreach ((array) ($schema['views'] ?? array()) as $viewName) {
        $lines[] = '-- Structure for view ' . $viewName;
        $lines[] = 'DROP VIEW IF EXISTS ' . maintenance_database_quote_identifier((string) $viewName) . ';';
        $lines[] = maintenance_database_show_create($conn, 'view', (string) $viewName) . ';';
        $lines[] = '';
    }

    $lines[] = 'SET FOREIGN_KEY_CHECKS=1;';
    $contents = implode(PHP_EOL, $lines) . PHP_EOL;

    if (@file_put_contents($backupPath, $contents) === false || !is_file($backupPath) || filesize($backupPath) <= 0) {
        throw new RuntimeException('Database backup file could not be written.');
    }

    $relativePath = 'storage/database_backups/' . basename($backupPath);
    maintenance_log_action($conn, array(
        'maintenance_type' => 'backup_database',
        'status' => 'completed',
        'snapshot_path' => $relativePath,
        'filters' => array(
            'database' => (string) (maintenance_database_connection_config($conn)['database'] ?? ''),
            'backup_prefix' => $prefix,
            'method' => 'php',
        ),
        'result' => array(
            'file_name' => basename($backupPath),
            'size_bytes' => (int) filesize($backupPath),
            'method' => 'php',
        ),
        'note' => 'Database backup created by native PHP exporter.',
    ));

    return array(
        'file_name' => basename($backupPath),
        'relative_path' => $relativePath,
        'size_bytes' => (int) filesize($backupPath),
        'method' => 'php',
    );
}

function maintenance_execute_database_backup_via_php(mysqli $conn, string $prefix = 'backup'): array
{
    return maintenance_database_execute_backup_via_php($conn, $prefix);
}

function maintenance_database_sql_statements_from_string(string $sql): array
{
    if (trim($sql) === '') {
        return array();
    }

    if (preg_match('/^\s*DELIMITER\s+/mi', $sql)) {
        throw new RuntimeException('This SQL backup requires mysql client restore because it contains DELIMITER statements.');
    }

    $sql = preg_replace('/\/\*![\s\S]*?\*\//', '', $sql);
    $sql = preg_replace('/\/\*[\s\S]*?\*\//', '', $sql);
    $sql = preg_replace('/^[\t ]*(--[^\r\n]*|#[^\r\n]*)$/m', '', $sql);
    $sql = str_replace("\r\n", "\n", (string) $sql);

    $statements = array();
    $buffer = '';
    $inSingle = false;
    $inDouble = false;
    $inBacktick = false;
    $escaped = false;
    $length = strlen($sql);

    for ($index = 0; $index < $length; $index++) {
        $char = $sql[$index];

        if ($inSingle || $inDouble) {
            $buffer .= $char;
            if ($escaped) {
                $escaped = false;
                continue;
            }

            if ($char === '\\') {
                $escaped = true;
                continue;
            }

            if ($inSingle && $char === "'") {
                $inSingle = false;
            } elseif ($inDouble && $char === '"') {
                $inDouble = false;
            }
            continue;
        }

        if ($inBacktick) {
            $buffer .= $char;
            if ($char === '`') {
                $inBacktick = false;
            }
            continue;
        }

        if ($char === "'") {
            $inSingle = true;
            $buffer .= $char;
            continue;
        }

        if ($char === '"') {
            $inDouble = true;
            $buffer .= $char;
            continue;
        }

        if ($char === '`') {
            $inBacktick = true;
            $buffer .= $char;
            continue;
        }

        if ($char === ';') {
            $statement = trim($buffer);
            if ($statement !== '') {
                $statements[] = $statement;
            }
            $buffer = '';
            continue;
        }

        $buffer .= $char;
    }

    $tail = trim($buffer);
    if ($tail !== '') {
        $statements[] = $tail;
    }

    return $statements;
}

function maintenance_database_sql_statements(string $filePath): array
{
    $sql = @file_get_contents($filePath);
    if ($sql === false || trim($sql) === '') {
        throw new RuntimeException('Backup file could not be read.');
    }

    return maintenance_database_sql_statements_from_string((string) $sql);
}

function maintenance_execute_database_restore_via_php(mysqli $conn, array $backupFile, array $preRestoreBackup): array
{
    $backupPath = (string) ($backupFile['absolute_path'] ?? '');
    $statements = maintenance_database_sql_statements($backupPath);
    if ($statements === array()) {
        throw new RuntimeException('No SQL statements were found in the selected backup file.');
    }

    @set_time_limit(0);

    foreach ($statements as $statement) {
        if (!$conn->query($statement)) {
            $preview = substr(trim((string) (preg_replace('/\s+/', ' ', $statement) ?: '')), 0, 160);
            throw new RuntimeException('Database restore failed near: ' . $preview);
        }
    }

    maintenance_log_action($conn, array(
        'maintenance_type' => 'restore_database',
        'status' => 'completed',
        'snapshot_path' => (string) ($backupFile['relative_path'] ?? ''),
        'filters' => array(
            'restore_file' => (string) ($backupFile['file_name'] ?? ''),
            'method' => 'php',
        ),
        'result' => array(
            'restore_file' => (string) ($backupFile['file_name'] ?? ''),
            'pre_restore_backup' => $preRestoreBackup,
            'method' => 'php',
            'statement_count' => count($statements),
        ),
        'note' => 'Database restored by native PHP importer.',
    ));

    return array(
        'restore_file' => (string) ($backupFile['file_name'] ?? ''),
        'pre_restore_backup' => $preRestoreBackup,
        'method' => 'php',
        'statement_count' => count($statements),
    );
}

function maintenance_database_backup_files(): array
{
    $directory = maintenance_database_backup_directory();
    $files = glob($directory . DIRECTORY_SEPARATOR . '*.sql');
    if (!is_array($files)) {
        return array();
    }

    $rows = array();
    foreach ($files as $path) {
        if (!is_file($path)) {
            continue;
        }

        $rows[] = array(
            'file_name' => basename($path),
            'absolute_path' => $path,
            'relative_path' => 'storage/database_backups/' . basename($path),
            'size_bytes' => (int) filesize($path),
            'modified_at' => date('Y-m-d H:i:s', (int) filemtime($path)),
        );
    }

    usort($rows, static function (array $left, array $right): int {
        return strcmp((string) ($right['modified_at'] ?? ''), (string) ($left['modified_at'] ?? ''));
    });

    return $rows;
}

function maintenance_database_find_backup_file(string $fileName): ?array
{
    $fileName = basename(trim($fileName));
    if ($fileName === '' || !preg_match('/^[A-Za-z0-9._-]+\.sql$/', $fileName)) {
        return null;
    }

    foreach (maintenance_database_backup_files() as $fileRow) {
        if ((string) ($fileRow['file_name'] ?? '') === $fileName) {
            return $fileRow;
        }
    }

    return null;
}

function maintenance_database_write_defaults_file(mysqli $conn): string
{
    $config = maintenance_database_connection_config($conn);
    $directory = maintenance_database_backup_directory();
    $fileName = '.client_' . substr((string) md5(uniqid('', true)), 0, 12) . '.cnf';
    $path = $directory . DIRECTORY_SEPARATOR . $fileName;

    $contents = implode(PHP_EOL, array(
        '[client]',
        'host=' . $config['host'],
        'user=' . $config['user'],
        'password=' . $config['pass'],
        'port=' . (int) $config['port'],
    )) . PHP_EOL;

    if (@file_put_contents($path, $contents) === false) {
        throw new RuntimeException('Database client settings could not be prepared.');
    }

    return $path;
}

function maintenance_database_backup_file_name(string $prefix = 'backup'): string
{
    $safePrefix = trim((string) preg_replace('/[^a-z0-9_]+/i', '_', strtolower($prefix)), '_');
    if ($safePrefix === '') {
        $safePrefix = 'backup';
    }

    return $safePrefix . '_ccw_' . date('Ymd_His') . '.sql';
}

function maintenance_execute_database_backup(mysqli $conn, string $prefix = 'backup'): array
{
    $capabilities = maintenance_database_capabilities($conn);
    if (!$capabilities['can_backup']) {
        throw new RuntimeException('Database backup cannot run because the backup directory is not writable.');
    }

    if ((string) ($capabilities['backup_method'] ?? 'php') === 'php') {
        return maintenance_database_execute_backup_via_php($conn, $prefix);
    }

    $connection = maintenance_database_connection_config($conn);
    $backupPath = maintenance_database_backup_directory() . DIRECTORY_SEPARATOR . maintenance_database_backup_file_name($prefix);
    $defaultsFile = maintenance_database_write_defaults_file($conn);
    $mysqldumpPath = (string) $capabilities['mysqldump_path'];

    try {
        $command = escapeshellarg($mysqldumpPath)
            . ' --defaults-extra-file=' . escapeshellarg($defaultsFile)
            . ' --default-character-set=utf8mb4 --routines --triggers --single-transaction --skip-lock-tables --add-drop-database --databases '
            . escapeshellarg((string) $connection['database'])
            . ' --result-file=' . escapeshellarg($backupPath);

        $output = array();
        $exitCode = 0;
        exec($command . ' 2>&1', $output, $exitCode);

        if ($exitCode !== 0 || !is_file($backupPath) || filesize($backupPath) <= 0) {
            throw new RuntimeException('Database backup command failed.');
        }

        $relativePath = 'storage/database_backups/' . basename($backupPath);
        maintenance_log_action($conn, array(
            'maintenance_type' => 'backup_database',
            'status' => 'completed',
            'snapshot_path' => $relativePath,
            'filters' => array(
                'database' => (string) $connection['database'],
                'backup_prefix' => $prefix,
                'method' => 'mysqldump',
            ),
            'result' => array(
                'file_name' => basename($backupPath),
                'size_bytes' => (int) filesize($backupPath),
                'method' => 'mysqldump',
            ),
            'note' => 'Database backup created by mysqldump.',
        ));

        return array(
            'file_name' => basename($backupPath),
            'relative_path' => $relativePath,
            'size_bytes' => (int) filesize($backupPath),
            'method' => 'mysqldump',
        );
    } finally {
        if (is_file($defaultsFile)) {
            @unlink($defaultsFile);
        }
    }
}

function maintenance_execute_database_restore(mysqli $conn, string $backupFileName): array
{
    $capabilities = maintenance_database_capabilities($conn);
    if (!$capabilities['can_restore']) {
        throw new RuntimeException('Database restore cannot run right now.');
    }

    $backupFile = maintenance_database_find_backup_file($backupFileName);
    if (!$backupFile) {
        throw new RuntimeException('Selected backup file was not found.');
    }

    $preRestoreBackup = maintenance_execute_database_backup($conn, 'pre_restore');

    if ((string) ($capabilities['restore_method'] ?? 'php') === 'php') {
        return maintenance_execute_database_restore_via_php($conn, $backupFile, $preRestoreBackup);
    }

    $mysqlPath = (string) $capabilities['mysql_path'];
    $defaultsFile = maintenance_database_write_defaults_file($conn);
    $backupPath = (string) ($backupFile['absolute_path'] ?? '');

    try {
        $output = array();
        $exitCode = 0;

        if (DIRECTORY_SEPARATOR === '\\') {
            $command = 'cmd /c ""' . $mysqlPath . '" --defaults-extra-file=' . escapeshellarg($defaultsFile) . ' < ' . escapeshellarg($backupPath) . '"';
            exec($command . ' 2>&1', $output, $exitCode);
        } else {
            $command = escapeshellarg($mysqlPath) . ' --defaults-extra-file=' . escapeshellarg($defaultsFile) . ' < ' . escapeshellarg($backupPath);
            exec('/bin/sh -lc ' . escapeshellarg($command) . ' 2>&1', $output, $exitCode);
        }

        if ($exitCode !== 0) {
            throw new RuntimeException('Database restore command failed.');
        }

        maintenance_log_action($conn, array(
            'maintenance_type' => 'restore_database',
            'status' => 'completed',
            'snapshot_path' => (string) ($backupFile['relative_path'] ?? ''),
            'filters' => array(
                'restore_file' => (string) ($backupFile['file_name'] ?? ''),
                'method' => 'mysql',
            ),
            'result' => array(
                'restore_file' => (string) ($backupFile['file_name'] ?? ''),
                'pre_restore_backup' => $preRestoreBackup,
                'method' => 'mysql',
            ),
            'note' => 'Database restored from backup file by mysql client.',
        ));

        return array(
            'restore_file' => (string) ($backupFile['file_name'] ?? ''),
            'pre_restore_backup' => $preRestoreBackup,
            'method' => 'mysql',
        );
    } finally {
        if (is_file($defaultsFile)) {
            @unlink($defaultsFile);
        }
    }
}

function maintenance_structure_sync_current_config(mysqli $conn): array
{
    $config = maintenance_database_connection_config($conn);

    return array(
        'use_current' => true,
        'host' => (string) ($config['host'] ?? '127.0.0.1'),
        'port' => (string) ($config['port'] ?? 3306),
        'user' => (string) ($config['user'] ?? ''),
        'pass' => '',
        'database' => (string) ($config['database'] ?? ''),
    );
}

function maintenance_structure_sync_custom_defaults(mysqli $conn): array
{
    $config = maintenance_database_connection_config($conn);

    return array(
        'use_current' => false,
        'host' => (string) ($config['host'] ?? '127.0.0.1'),
        'port' => (string) ($config['port'] ?? 3306),
        'user' => (string) ($config['user'] ?? ''),
        'pass' => '',
        'database' => '',
    );
}

function maintenance_structure_sync_input(array $raw, array $defaults): array
{
    return array(
        'use_current' => ((string) ($raw['use_current'] ?? ($defaults['use_current'] ? '1' : '0'))) === '1',
        'host' => trim((string) ($raw['host'] ?? $defaults['host'] ?? '')),
        'port' => trim((string) ($raw['port'] ?? $defaults['port'] ?? '3306')),
        'user' => trim((string) ($raw['user'] ?? $defaults['user'] ?? '')),
        'pass' => (string) ($raw['pass'] ?? $defaults['pass'] ?? ''),
        'database' => trim((string) ($raw['database'] ?? $defaults['database'] ?? '')),
    );
}

function maintenance_structure_sync_resolve_config(mysqli $conn, array $input): array
{
    if (!empty($input['use_current'])) {
        $current = maintenance_database_connection_config($conn);

        return array(
            'host' => trim((string) ($current['host'] ?? '127.0.0.1')),
            'port' => max(1, (int) ($current['port'] ?? 3306)),
            'user' => trim((string) ($current['user'] ?? '')),
            'pass' => (string) ($current['pass'] ?? ''),
            'database' => trim((string) ($current['database'] ?? '')),
            'use_current' => true,
        );
    }

    $host = trim((string) ($input['host'] ?? ''));
    $port = max(1, (int) ($input['port'] ?? 3306));
    $user = trim((string) ($input['user'] ?? ''));
    $database = trim((string) ($input['database'] ?? ''));

    if ($host === '' || $user === '' || $database === '') {
        throw new RuntimeException('Database Structure Sync requires host, username and database name for every custom connection.');
    }

    return array(
        'host' => $host,
        'port' => $port,
        'user' => $user,
        'pass' => (string) ($input['pass'] ?? ''),
        'database' => $database,
        'use_current' => false,
    );
}

function maintenance_structure_sync_masked_config(array $config): array
{
    return array(
        'host' => (string) ($config['host'] ?? ''),
        'port' => (int) ($config['port'] ?? 3306),
        'user' => (string) ($config['user'] ?? ''),
        'database' => (string) ($config['database'] ?? ''),
        'use_current' => !empty($config['use_current']),
    );
}

function maintenance_structure_sync_connect(array $config): mysqli
{
    $connection = @new mysqli(
        (string) ($config['host'] ?? '127.0.0.1'),
        (string) ($config['user'] ?? ''),
        (string) ($config['pass'] ?? ''),
        (string) ($config['database'] ?? ''),
        max(1, (int) ($config['port'] ?? 3306))
    );

    if ($connection->connect_error) {
        throw new RuntimeException('Database connection failed for ' . (string) ($config['database'] ?? '-') . ': ' . $connection->connect_error);
    }

    $connection->set_charset('utf8mb4');
    return $connection;
}

function maintenance_structure_sync_list_tables(mysqli $conn, string $database): array
{
    $stmt = db_prepare($conn, "
        SELECT TABLE_NAME, ENGINE, TABLE_COLLATION
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = ?
          AND TABLE_TYPE = 'BASE TABLE'
        ORDER BY TABLE_NAME ASC
    ");
    $stmt->bind_param('s', $database);
    $stmt->execute();
    $result = $stmt->get_result();

    $tables = array();
    while ($row = $result->fetch_assoc()) {
        $tableName = trim((string) ($row['TABLE_NAME'] ?? ''));
        if ($tableName === '') {
            continue;
        }

        $tables[$tableName] = array(
            'engine' => (string) ($row['ENGINE'] ?? ''),
            'collation' => (string) ($row['TABLE_COLLATION'] ?? ''),
        );
    }

    $stmt->close();
    return $tables;
}

function maintenance_structure_sync_list_columns(mysqli $conn, string $database): array
{
    $stmt = db_prepare($conn, "
        SELECT
            TABLE_NAME,
            COLUMN_NAME,
            ORDINAL_POSITION,
            COLUMN_TYPE,
            IS_NULLABLE,
            COLUMN_DEFAULT,
            EXTRA,
            CHARACTER_SET_NAME,
            COLLATION_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = ?
        ORDER BY TABLE_NAME ASC, ORDINAL_POSITION ASC
    ");
    $stmt->bind_param('s', $database);
    $stmt->execute();
    $result = $stmt->get_result();

    $columns = array();
    while ($row = $result->fetch_assoc()) {
        $tableName = trim((string) ($row['TABLE_NAME'] ?? ''));
        $columnName = trim((string) ($row['COLUMN_NAME'] ?? ''));
        if ($tableName === '' || $columnName === '') {
            continue;
        }

        if (!isset($columns[$tableName])) {
            $columns[$tableName] = array();
        }

        $columns[$tableName][$columnName] = array(
            'name' => $columnName,
            'position' => (int) ($row['ORDINAL_POSITION'] ?? 0),
            'column_type' => (string) ($row['COLUMN_TYPE'] ?? ''),
            'is_nullable' => strtoupper((string) ($row['IS_NULLABLE'] ?? 'NO')),
            'column_default' => $row['COLUMN_DEFAULT'],
            'extra' => trim((string) ($row['EXTRA'] ?? '')),
            'character_set_name' => (string) ($row['CHARACTER_SET_NAME'] ?? ''),
            'collation_name' => (string) ($row['COLLATION_NAME'] ?? ''),
        );
    }

    $stmt->close();
    return $columns;
}

function maintenance_structure_sync_list_indexes(mysqli $conn, string $database): array
{
    $stmt = db_prepare($conn, "
        SELECT
            TABLE_NAME,
            INDEX_NAME,
            NON_UNIQUE,
            INDEX_TYPE,
            SEQ_IN_INDEX,
            COLUMN_NAME,
            SUB_PART
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = ?
          AND INDEX_NAME <> 'PRIMARY'
        ORDER BY TABLE_NAME ASC, INDEX_NAME ASC, SEQ_IN_INDEX ASC
    ");
    $stmt->bind_param('s', $database);
    $stmt->execute();
    $result = $stmt->get_result();

    $indexes = array();
    while ($row = $result->fetch_assoc()) {
        $tableName = trim((string) ($row['TABLE_NAME'] ?? ''));
        $indexName = trim((string) ($row['INDEX_NAME'] ?? ''));
        if ($tableName === '' || $indexName === '') {
            continue;
        }

        if (!isset($indexes[$tableName][$indexName])) {
            $indexes[$tableName][$indexName] = array(
                'name' => $indexName,
                'non_unique' => (int) ($row['NON_UNIQUE'] ?? 1),
                'index_type' => strtoupper((string) ($row['INDEX_TYPE'] ?? 'BTREE')),
                'columns' => array(),
            );
        }

        $indexes[$tableName][$indexName]['columns'][] = array(
            'name' => (string) ($row['COLUMN_NAME'] ?? ''),
            'sub_part' => isset($row['SUB_PART']) ? (int) $row['SUB_PART'] : null,
        );
    }

    $stmt->close();
    return $indexes;
}

function maintenance_structure_sync_list_foreign_keys(mysqli $conn, string $database): array
{
    $stmt = db_prepare($conn, "
        SELECT
            kcu.TABLE_NAME,
            kcu.CONSTRAINT_NAME,
            kcu.COLUMN_NAME,
            kcu.ORDINAL_POSITION,
            kcu.REFERENCED_TABLE_NAME,
            kcu.REFERENCED_COLUMN_NAME,
            rc.UPDATE_RULE,
            rc.DELETE_RULE
        FROM information_schema.KEY_COLUMN_USAGE kcu
        INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
            ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
           AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
           AND rc.TABLE_NAME = kcu.TABLE_NAME
        WHERE kcu.TABLE_SCHEMA = ?
          AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
        ORDER BY kcu.TABLE_NAME ASC, kcu.CONSTRAINT_NAME ASC, kcu.ORDINAL_POSITION ASC
    ");
    $stmt->bind_param('s', $database);
    $stmt->execute();
    $result = $stmt->get_result();

    $keys = array();
    while ($row = $result->fetch_assoc()) {
        $tableName = trim((string) ($row['TABLE_NAME'] ?? ''));
        $constraintName = trim((string) ($row['CONSTRAINT_NAME'] ?? ''));
        if ($tableName === '' || $constraintName === '') {
            continue;
        }

        if (!isset($keys[$tableName][$constraintName])) {
            $keys[$tableName][$constraintName] = array(
                'name' => $constraintName,
                'referenced_table' => (string) ($row['REFERENCED_TABLE_NAME'] ?? ''),
                'update_rule' => strtoupper((string) ($row['UPDATE_RULE'] ?? 'RESTRICT')),
                'delete_rule' => strtoupper((string) ($row['DELETE_RULE'] ?? 'RESTRICT')),
                'columns' => array(),
                'referenced_columns' => array(),
            );
        }

        $keys[$tableName][$constraintName]['columns'][] = (string) ($row['COLUMN_NAME'] ?? '');
        $keys[$tableName][$constraintName]['referenced_columns'][] = (string) ($row['REFERENCED_COLUMN_NAME'] ?? '');
    }

    $stmt->close();
    return $keys;
}

function maintenance_structure_sync_list_views(mysqli $conn, string $database): array
{
    $stmt = db_prepare($conn, "
        SELECT
            TABLE_NAME,
            VIEW_DEFINITION,
            CHECK_OPTION,
            IS_UPDATABLE,
            SECURITY_TYPE
        FROM information_schema.VIEWS
        WHERE TABLE_SCHEMA = ?
        ORDER BY TABLE_NAME ASC
    ");
    $stmt->bind_param('s', $database);
    $stmt->execute();
    $result = $stmt->get_result();

    $views = array();
    while ($row = $result->fetch_assoc()) {
        $name = trim((string) ($row['TABLE_NAME'] ?? ''));
        if ($name === '') {
            continue;
        }

        $views[$name] = array(
            'name' => $name,
            'definition' => (string) ($row['VIEW_DEFINITION'] ?? ''),
            'check_option' => (string) ($row['CHECK_OPTION'] ?? ''),
            'is_updatable' => (string) ($row['IS_UPDATABLE'] ?? ''),
            'security_type' => (string) ($row['SECURITY_TYPE'] ?? ''),
        );
    }

    $stmt->close();
    return $views;
}

function maintenance_structure_sync_list_triggers(mysqli $conn, string $database): array
{
    $stmt = db_prepare($conn, "
        SELECT
            TRIGGER_NAME,
            EVENT_OBJECT_TABLE,
            ACTION_TIMING,
            EVENT_MANIPULATION,
            ACTION_STATEMENT
        FROM information_schema.TRIGGERS
        WHERE TRIGGER_SCHEMA = ?
        ORDER BY TRIGGER_NAME ASC
    ");
    $stmt->bind_param('s', $database);
    $stmt->execute();
    $result = $stmt->get_result();

    $triggers = array();
    while ($row = $result->fetch_assoc()) {
        $name = trim((string) ($row['TRIGGER_NAME'] ?? ''));
        if ($name === '') {
            continue;
        }

        $triggers[$name] = array(
            'name' => $name,
            'table_name' => (string) ($row['EVENT_OBJECT_TABLE'] ?? ''),
            'timing' => strtoupper((string) ($row['ACTION_TIMING'] ?? '')),
            'event' => strtoupper((string) ($row['EVENT_MANIPULATION'] ?? '')),
            'statement' => (string) ($row['ACTION_STATEMENT'] ?? ''),
        );
    }

    $stmt->close();
    return $triggers;
}

function maintenance_structure_sync_list_routines(mysqli $conn, string $database): array
{
    $stmt = db_prepare($conn, "
        SELECT
            ROUTINE_NAME,
            ROUTINE_TYPE,
            DTD_IDENTIFIER,
            ROUTINE_DEFINITION,
            IS_DETERMINISTIC,
            SQL_DATA_ACCESS,
            SECURITY_TYPE
        FROM information_schema.ROUTINES
        WHERE ROUTINE_SCHEMA = ?
        ORDER BY ROUTINE_TYPE ASC, ROUTINE_NAME ASC
    ");
    $stmt->bind_param('s', $database);
    $stmt->execute();
    $result = $stmt->get_result();

    $routines = array();
    while ($row = $result->fetch_assoc()) {
        $name = trim((string) ($row['ROUTINE_NAME'] ?? ''));
        $type = strtoupper((string) ($row['ROUTINE_TYPE'] ?? ''));
        if ($name === '' || $type === '') {
            continue;
        }

        $key = $type . ':' . $name;
        $routines[$key] = array(
            'name' => $name,
            'type' => $type,
            'return_type' => (string) ($row['DTD_IDENTIFIER'] ?? ''),
            'definition' => (string) ($row['ROUTINE_DEFINITION'] ?? ''),
            'is_deterministic' => (string) ($row['IS_DETERMINISTIC'] ?? ''),
            'sql_data_access' => (string) ($row['SQL_DATA_ACCESS'] ?? ''),
            'security_type' => (string) ($row['SECURITY_TYPE'] ?? ''),
        );
    }

    $stmt->close();
    return $routines;
}

function maintenance_structure_sync_fetch_schema(mysqli $conn, string $database): array
{
    return array(
        'tables' => maintenance_structure_sync_list_tables($conn, $database),
        'columns' => maintenance_structure_sync_list_columns($conn, $database),
        'indexes' => maintenance_structure_sync_list_indexes($conn, $database),
        'foreign_keys' => maintenance_structure_sync_list_foreign_keys($conn, $database),
        'views' => maintenance_structure_sync_list_views($conn, $database),
        'triggers' => maintenance_structure_sync_list_triggers($conn, $database),
        'routines' => maintenance_structure_sync_list_routines($conn, $database),
    );
}

function maintenance_structure_sync_column_signature(array $column): string
{
    return json_encode(array(
        'column_type' => strtolower((string) ($column['column_type'] ?? '')),
        'is_nullable' => strtoupper((string) ($column['is_nullable'] ?? 'NO')),
        'column_default' => $column['column_default'],
        'extra' => strtolower((string) ($column['extra'] ?? '')),
        'character_set_name' => strtolower((string) ($column['character_set_name'] ?? '')),
        'collation_name' => strtolower((string) ($column['collation_name'] ?? '')),
    ));
}

function maintenance_structure_sync_index_signature(array $index): string
{
    $columns = array();
    foreach ((array) ($index['columns'] ?? array()) as $column) {
        $columns[] = array(
            'name' => strtolower((string) ($column['name'] ?? '')),
            'sub_part' => isset($column['sub_part']) ? (int) $column['sub_part'] : null,
        );
    }

    return json_encode(array(
        'non_unique' => (int) ($index['non_unique'] ?? 1),
        'index_type' => strtoupper((string) ($index['index_type'] ?? 'BTREE')),
        'columns' => $columns,
    ));
}

function maintenance_structure_sync_foreign_key_signature(array $foreignKey): string
{
    return json_encode(array(
        'referenced_table' => strtolower((string) ($foreignKey['referenced_table'] ?? '')),
        'update_rule' => strtoupper((string) ($foreignKey['update_rule'] ?? 'RESTRICT')),
        'delete_rule' => strtoupper((string) ($foreignKey['delete_rule'] ?? 'RESTRICT')),
        'columns' => array_map('strtolower', (array) ($foreignKey['columns'] ?? array())),
        'referenced_columns' => array_map('strtolower', (array) ($foreignKey['referenced_columns'] ?? array())),
    ));
}

function maintenance_structure_sync_view_signature(array $view): string
{
    $definition = preg_replace('/\s+/', ' ', trim((string) ($view['definition'] ?? ''))) ?: '';

    return json_encode(array(
        'definition' => strtolower($definition),
        'check_option' => strtoupper((string) ($view['check_option'] ?? '')),
        'is_updatable' => strtoupper((string) ($view['is_updatable'] ?? '')),
        'security_type' => strtoupper((string) ($view['security_type'] ?? '')),
    ));
}

function maintenance_structure_sync_trigger_signature(array $trigger): string
{
    $statement = preg_replace('/\s+/', ' ', trim((string) ($trigger['statement'] ?? ''))) ?: '';

    return json_encode(array(
        'table_name' => strtolower((string) ($trigger['table_name'] ?? '')),
        'timing' => strtoupper((string) ($trigger['timing'] ?? '')),
        'event' => strtoupper((string) ($trigger['event'] ?? '')),
        'statement' => strtolower($statement),
    ));
}

function maintenance_structure_sync_routine_signature(array $routine): string
{
    $definition = preg_replace('/\s+/', ' ', trim((string) ($routine['definition'] ?? ''))) ?: '';

    return json_encode(array(
        'type' => strtoupper((string) ($routine['type'] ?? '')),
        'return_type' => strtolower((string) ($routine['return_type'] ?? '')),
        'definition' => strtolower($definition),
        'is_deterministic' => strtoupper((string) ($routine['is_deterministic'] ?? '')),
        'sql_data_access' => strtoupper((string) ($routine['sql_data_access'] ?? '')),
        'security_type' => strtoupper((string) ($routine['security_type'] ?? '')),
    ));
}

function maintenance_structure_sync_quote_default(mixed $value): string
{
    return "'" . str_replace("'", "''", (string) $value) . "'";
}

function maintenance_structure_sync_column_definition(array $column): string
{
    $parts = array();
    $parts[] = (string) ($column['column_type'] ?? '');

    $characterSet = trim((string) ($column['character_set_name'] ?? ''));
    $collation = trim((string) ($column['collation_name'] ?? ''));
    if ($characterSet !== '') {
        $parts[] = 'CHARACTER SET ' . $characterSet;
    }
    if ($collation !== '') {
        $parts[] = 'COLLATE ' . $collation;
    }

    $parts[] = strtoupper((string) ($column['is_nullable'] ?? 'NO')) === 'YES' ? 'NULL' : 'NOT NULL';

    if (array_key_exists('column_default', $column) && $column['column_default'] !== null) {
        $defaultValue = (string) $column['column_default'];
        if (preg_match('/^current_timestamp(\(\))?$/i', $defaultValue)) {
            $parts[] = 'DEFAULT CURRENT_TIMESTAMP';
        } else {
            $parts[] = 'DEFAULT ' . maintenance_structure_sync_quote_default($defaultValue);
        }
    }

    $extra = trim((string) ($column['extra'] ?? ''));
    if ($extra !== '') {
        $parts[] = $extra;
    }

    return trim(preg_replace('/\s+/', ' ', implode(' ', array_filter($parts, static fn($part): bool => trim((string) $part) !== ''))));
}

function maintenance_structure_sync_index_sql(string $tableName, array $index): string
{
    $columnSql = array();
    foreach ((array) ($index['columns'] ?? array()) as $column) {
        $fragment = maintenance_database_quote_identifier((string) ($column['name'] ?? ''));
        if (!empty($column['sub_part'])) {
            $fragment .= '(' . (int) $column['sub_part'] . ')';
        }
        $columnSql[] = $fragment;
    }

    $prefix = (int) ($index['non_unique'] ?? 1) === 0 ? 'ADD UNIQUE KEY' : 'ADD KEY';
    $indexType = strtoupper((string) ($index['index_type'] ?? 'BTREE'));
    $using = ($indexType !== '' && $indexType !== 'BTREE') ? ' USING ' . $indexType : '';

    return 'ALTER TABLE '
        . maintenance_database_quote_identifier($tableName)
        . ' ' . $prefix . ' '
        . maintenance_database_quote_identifier((string) ($index['name'] ?? ''))
        . $using
        . ' (' . implode(', ', $columnSql) . ');';
}

function maintenance_structure_sync_foreign_key_sql(string $tableName, array $foreignKey): string
{
    $columns = array_map(static fn(string $name): string => maintenance_database_quote_identifier($name), (array) ($foreignKey['columns'] ?? array()));
    $referencedColumns = array_map(static fn(string $name): string => maintenance_database_quote_identifier($name), (array) ($foreignKey['referenced_columns'] ?? array()));

    return 'ALTER TABLE '
        . maintenance_database_quote_identifier($tableName)
        . ' ADD CONSTRAINT '
        . maintenance_database_quote_identifier((string) ($foreignKey['name'] ?? ''))
        . ' FOREIGN KEY (' . implode(', ', $columns) . ') REFERENCES '
        . maintenance_database_quote_identifier((string) ($foreignKey['referenced_table'] ?? ''))
        . ' (' . implode(', ', $referencedColumns) . ')'
        . ' ON DELETE ' . strtoupper((string) ($foreignKey['delete_rule'] ?? 'RESTRICT'))
        . ' ON UPDATE ' . strtoupper((string) ($foreignKey['update_rule'] ?? 'RESTRICT'))
        . ';';
}

function maintenance_structure_sync_table_option_sql(string $tableName, array $sourceTable): string
{
    $clauses = array();
    $engine = trim((string) ($sourceTable['engine'] ?? ''));
    $collation = trim((string) ($sourceTable['collation'] ?? ''));

    if ($engine !== '') {
        $clauses[] = 'ENGINE=' . $engine;
    }

    if ($collation !== '') {
        $clauses[] = 'COLLATE=' . $collation;
    }

    if ($clauses === array()) {
        return '';
    }

    return 'ALTER TABLE ' . maintenance_database_quote_identifier($tableName) . ' ' . implode(' ', $clauses) . ';';
}

function maintenance_structure_sync_view_sql(mysqli $conn, string $viewName): string
{
    return maintenance_database_show_create($conn, 'view', $viewName) . ';';
}

function maintenance_structure_sync_trigger_sql(array $trigger): string
{
    return 'CREATE TRIGGER '
        . maintenance_database_quote_identifier((string) ($trigger['name'] ?? ''))
        . ' ' . strtoupper((string) ($trigger['timing'] ?? 'BEFORE'))
        . ' ' . strtoupper((string) ($trigger['event'] ?? 'INSERT'))
        . ' ON ' . maintenance_database_quote_identifier((string) ($trigger['table_name'] ?? ''))
        . ' FOR EACH ROW '
        . trim((string) ($trigger['statement'] ?? ''))
        . ';';
}

function maintenance_structure_sync_routine_sql(mysqli $conn, string $routineType, string $routineName): string
{
    $type = strtoupper(trim($routineType));
    if (!in_array($type, array('PROCEDURE', 'FUNCTION'), true)) {
        throw new RuntimeException('Routine type is not supported for SQL export.');
    }

    $query = 'SHOW CREATE ' . $type . ' ' . maintenance_database_quote_identifier($routineName);
    $result = $conn->query($query);
    if (!$result instanceof mysqli_result) {
        throw new RuntimeException('Routine definition could not be read for ' . $routineName . '.');
    }

    $row = $result->fetch_assoc();
    $result->close();
    if (!$row) {
        throw new RuntimeException('Routine definition could not be read for ' . $routineName . '.');
    }

    $field = $type === 'PROCEDURE' ? 'Create Procedure' : 'Create Function';
    $sql = trim((string) ($row[$field] ?? ''));
    if ($sql === '') {
        throw new RuntimeException('Routine definition could not be exported for ' . $routineName . '.');
    }

    return preg_replace('/\s+DEFINER=`[^`]+`@`[^`]+`\s+/i', ' ', $sql) . ';';
}

function maintenance_structure_sync_preview(array $sourceInput, array $targetInput, mysqli $conn): array
{
    $sourceConfig = maintenance_structure_sync_resolve_config($conn, $sourceInput);
    $targetConfig = maintenance_structure_sync_resolve_config($conn, $targetInput);

    $sourceConn = maintenance_structure_sync_connect($sourceConfig);
    $targetConn = maintenance_structure_sync_connect($targetConfig);

    try {
        $sourceSchema = maintenance_structure_sync_fetch_schema($sourceConn, (string) $sourceConfig['database']);
        $targetSchema = maintenance_structure_sync_fetch_schema($targetConn, (string) $targetConfig['database']);

        $preview = array(
            'source' => maintenance_structure_sync_masked_config($sourceConfig),
            'target' => maintenance_structure_sync_masked_config($targetConfig),
            'missing_tables' => array(),
            'extra_tables' => array(),
            'missing_columns' => array(),
            'extra_columns' => array(),
            'changed_columns' => array(),
            'missing_indexes' => array(),
            'extra_indexes' => array(),
            'changed_indexes' => array(),
            'missing_foreign_keys' => array(),
            'extra_foreign_keys' => array(),
            'changed_foreign_keys' => array(),
            'table_option_changes' => array(),
            'missing_views' => array(),
            'extra_views' => array(),
            'changed_views' => array(),
            'missing_triggers' => array(),
            'extra_triggers' => array(),
            'changed_triggers' => array(),
            'missing_routines' => array(),
            'extra_routines' => array(),
            'changed_routines' => array(),
            'safe_sql' => array(),
            'manual_sql' => array(),
            'manual_apply_items' => array(),
            'summary' => array(),
        );

        $sourceTables = (array) ($sourceSchema['tables'] ?? array());
        $targetTables = (array) ($targetSchema['tables'] ?? array());

        foreach ($sourceTables as $tableName => $sourceTable) {
            if (!isset($targetTables[$tableName])) {
                $sql = maintenance_database_show_create($sourceConn, 'table', $tableName) . ';';
                $preview['missing_tables'][] = array(
                    'table_name' => $tableName,
                    'sql' => $sql,
                );
                $preview['safe_sql'][] = $sql;
                continue;
            }

            $sourceColumns = (array) (($sourceSchema['columns'][$tableName] ?? array()));
            $targetColumns = (array) (($targetSchema['columns'][$tableName] ?? array()));

            foreach ($sourceColumns as $columnName => $sourceColumn) {
                if (!isset($targetColumns[$columnName])) {
                    $sql = 'ALTER TABLE '
                        . maintenance_database_quote_identifier($tableName)
                        . ' ADD COLUMN '
                        . maintenance_database_quote_identifier($columnName)
                        . ' ' . maintenance_structure_sync_column_definition($sourceColumn) . ';';
                    $preview['missing_columns'][] = array(
                        'table_name' => $tableName,
                        'column_name' => $columnName,
                        'sql' => $sql,
                    );
                    $preview['safe_sql'][] = $sql;
                    continue;
                }

                if (maintenance_structure_sync_column_signature($sourceColumn) !== maintenance_structure_sync_column_signature($targetColumns[$columnName])) {
                    $sql = 'ALTER TABLE '
                        . maintenance_database_quote_identifier($tableName)
                        . ' MODIFY COLUMN '
                        . maintenance_database_quote_identifier($columnName)
                        . ' ' . maintenance_structure_sync_column_definition($sourceColumn) . ';';
                    $preview['changed_columns'][] = array(
                        'table_name' => $tableName,
                        'column_name' => $columnName,
                        'source_definition' => maintenance_structure_sync_column_definition($sourceColumn),
                        'target_definition' => maintenance_structure_sync_column_definition($targetColumns[$columnName]),
                        'sql' => $sql,
                    );
                    $preview['manual_sql'][] = $sql;
                    $preview['manual_apply_items'][] = array(
                        'label' => 'Modify column ' . $tableName . '.' . $columnName,
                        'sql' => $sql,
                    );
                }
            }

            foreach ($targetColumns as $columnName => $targetColumn) {
                if (!isset($sourceColumns[$columnName])) {
                    $preview['extra_columns'][] = array(
                        'table_name' => $tableName,
                        'column_name' => $columnName,
                        'target_definition' => maintenance_structure_sync_column_definition($targetColumn),
                    );
                }
            }

            $sourceIndexes = (array) (($sourceSchema['indexes'][$tableName] ?? array()));
            $targetIndexes = (array) (($targetSchema['indexes'][$tableName] ?? array()));

            foreach ($sourceIndexes as $indexName => $sourceIndex) {
                if (!isset($targetIndexes[$indexName])) {
                    $sql = maintenance_structure_sync_index_sql($tableName, $sourceIndex);
                    $preview['missing_indexes'][] = array(
                        'table_name' => $tableName,
                        'index_name' => $indexName,
                        'sql' => $sql,
                    );
                    $preview['safe_sql'][] = $sql;
                    continue;
                }

                if (maintenance_structure_sync_index_signature($sourceIndex) !== maintenance_structure_sync_index_signature($targetIndexes[$indexName])) {
                    $preview['changed_indexes'][] = array(
                        'table_name' => $tableName,
                        'index_name' => $indexName,
                        'source_signature' => maintenance_structure_sync_index_signature($sourceIndex),
                        'target_signature' => maintenance_structure_sync_index_signature($targetIndexes[$indexName]),
                        'sql' => maintenance_structure_sync_index_sql($tableName, $sourceIndex),
                    );
                    $preview['manual_sql'][] = '-- Review index change for ' . $tableName . '.' . $indexName . PHP_EOL . maintenance_structure_sync_index_sql($tableName, $sourceIndex);
                    $preview['manual_apply_items'][] = array(
                        'label' => 'Apply index change for ' . $tableName . '.' . $indexName,
                        'sql' => maintenance_structure_sync_index_sql($tableName, $sourceIndex),
                    );
                }
            }

            foreach ($targetIndexes as $indexName => $targetIndex) {
                if (!isset($sourceIndexes[$indexName])) {
                    $preview['extra_indexes'][] = array(
                        'table_name' => $tableName,
                        'index_name' => $indexName,
                        'target_signature' => maintenance_structure_sync_index_signature($targetIndex),
                    );
                }
            }

            $sourceForeignKeys = (array) (($sourceSchema['foreign_keys'][$tableName] ?? array()));
            $targetForeignKeys = (array) (($targetSchema['foreign_keys'][$tableName] ?? array()));

            foreach ($sourceForeignKeys as $constraintName => $sourceForeignKey) {
                if (!isset($targetForeignKeys[$constraintName])) {
                    $sql = maintenance_structure_sync_foreign_key_sql($tableName, $sourceForeignKey);
                    $preview['missing_foreign_keys'][] = array(
                        'table_name' => $tableName,
                        'constraint_name' => $constraintName,
                        'sql' => $sql,
                    );
                    $preview['safe_sql'][] = $sql;
                    continue;
                }

                if (maintenance_structure_sync_foreign_key_signature($sourceForeignKey) !== maintenance_structure_sync_foreign_key_signature($targetForeignKeys[$constraintName])) {
                    $preview['changed_foreign_keys'][] = array(
                        'table_name' => $tableName,
                        'constraint_name' => $constraintName,
                        'source_signature' => maintenance_structure_sync_foreign_key_signature($sourceForeignKey),
                        'target_signature' => maintenance_structure_sync_foreign_key_signature($targetForeignKeys[$constraintName]),
                        'sql' => maintenance_structure_sync_foreign_key_sql($tableName, $sourceForeignKey),
                    );
                    $preview['manual_sql'][] = '-- Review foreign key change for ' . $tableName . '.' . $constraintName . PHP_EOL . maintenance_structure_sync_foreign_key_sql($tableName, $sourceForeignKey);
                    $preview['manual_apply_items'][] = array(
                        'label' => 'Apply foreign key change for ' . $tableName . '.' . $constraintName,
                        'sql' => maintenance_structure_sync_foreign_key_sql($tableName, $sourceForeignKey),
                    );
                }
            }

            foreach ($targetForeignKeys as $constraintName => $targetForeignKey) {
                if (!isset($sourceForeignKeys[$constraintName])) {
                    $preview['extra_foreign_keys'][] = array(
                        'table_name' => $tableName,
                        'constraint_name' => $constraintName,
                        'target_signature' => maintenance_structure_sync_foreign_key_signature($targetForeignKey),
                    );
                }
            }

            $targetTable = $targetTables[$tableName];
            if (
                strcasecmp((string) ($sourceTable['engine'] ?? ''), (string) ($targetTable['engine'] ?? '')) !== 0
                || strcasecmp((string) ($sourceTable['collation'] ?? ''), (string) ($targetTable['collation'] ?? '')) !== 0
            ) {
                $sql = maintenance_structure_sync_table_option_sql($tableName, $sourceTable);
                $preview['table_option_changes'][] = array(
                    'table_name' => $tableName,
                    'source_engine' => (string) ($sourceTable['engine'] ?? ''),
                    'target_engine' => (string) ($targetTable['engine'] ?? ''),
                    'source_collation' => (string) ($sourceTable['collation'] ?? ''),
                    'target_collation' => (string) ($targetTable['collation'] ?? ''),
                    'sql' => $sql,
                );
                if ($sql !== '') {
                    $preview['manual_sql'][] = $sql;
                    $preview['manual_apply_items'][] = array(
                        'label' => 'Apply table option change for ' . $tableName,
                        'sql' => $sql,
                    );
                }
            }
        }

        foreach ($targetTables as $tableName => $targetTable) {
            if (!isset($sourceTables[$tableName])) {
                $preview['extra_tables'][] = array(
                    'table_name' => $tableName,
                );
            }
        }

        $sourceViews = (array) ($sourceSchema['views'] ?? array());
        $targetViews = (array) ($targetSchema['views'] ?? array());
        foreach ($sourceViews as $viewName => $sourceView) {
            try {
                $sql = maintenance_structure_sync_view_sql($sourceConn, $viewName);
            } catch (Throwable $exception) {
                $sql = '-- View SQL export failed for ' . $viewName . ': ' . ($exception->getMessage() !== '' ? $exception->getMessage() : 'Unknown error.');
            }
            if (!isset($targetViews[$viewName])) {
                $preview['missing_views'][] = array(
                    'view_name' => $viewName,
                    'sql' => $sql,
                );
                $preview['manual_sql'][] = '-- Review missing view ' . $viewName . PHP_EOL . $sql;
                if (maintenance_database_sql_statements_from_string($sql) !== array()) {
                    $preview['manual_apply_items'][] = array(
                        'label' => 'Create missing view ' . $viewName,
                        'sql' => $sql,
                    );
                }
                continue;
            }

            if (maintenance_structure_sync_view_signature($sourceView) !== maintenance_structure_sync_view_signature($targetViews[$viewName])) {
                $changeSql = 'DROP VIEW IF EXISTS ' . maintenance_database_quote_identifier($viewName) . ';' . PHP_EOL . $sql;
                $preview['changed_views'][] = array(
                    'view_name' => $viewName,
                    'sql' => $changeSql,
                );
                $preview['manual_sql'][] = '-- Review view change for ' . $viewName . PHP_EOL . $changeSql;
                if (maintenance_database_sql_statements_from_string($changeSql) !== array()) {
                    $preview['manual_apply_items'][] = array(
                        'label' => 'Apply view change for ' . $viewName,
                        'sql' => $changeSql,
                    );
                }
            }
        }
        foreach ($targetViews as $viewName => $targetView) {
            if (!isset($sourceViews[$viewName])) {
                $preview['extra_views'][] = array(
                    'view_name' => $viewName,
                );
            }
        }

        $sourceTriggers = (array) ($sourceSchema['triggers'] ?? array());
        $targetTriggers = (array) ($targetSchema['triggers'] ?? array());
        foreach ($sourceTriggers as $triggerName => $sourceTrigger) {
            $sql = maintenance_structure_sync_trigger_sql($sourceTrigger);
            if (!isset($targetTriggers[$triggerName])) {
                $preview['missing_triggers'][] = array(
                    'trigger_name' => $triggerName,
                    'table_name' => (string) ($sourceTrigger['table_name'] ?? ''),
                    'sql' => $sql,
                );
                $preview['manual_sql'][] = '-- Review missing trigger ' . $triggerName . PHP_EOL . '-- Trigger SQL is not applied automatically because compound trigger bodies may require DELIMITER handling.' . PHP_EOL . $sql;
                continue;
            }

            if (maintenance_structure_sync_trigger_signature($sourceTrigger) !== maintenance_structure_sync_trigger_signature($targetTriggers[$triggerName])) {
                $preview['changed_triggers'][] = array(
                    'trigger_name' => $triggerName,
                    'table_name' => (string) ($sourceTrigger['table_name'] ?? ''),
                    'sql' => $sql,
                );
                $preview['manual_sql'][] = '-- Review trigger change for ' . $triggerName . PHP_EOL . '-- Trigger SQL is not applied automatically because compound trigger bodies may require DELIMITER handling.' . PHP_EOL . $sql;
            }
        }
        foreach ($targetTriggers as $triggerName => $targetTrigger) {
            if (!isset($sourceTriggers[$triggerName])) {
                $preview['extra_triggers'][] = array(
                    'trigger_name' => $triggerName,
                    'table_name' => (string) ($targetTrigger['table_name'] ?? ''),
                );
            }
        }

        $sourceRoutines = (array) ($sourceSchema['routines'] ?? array());
        $targetRoutines = (array) ($targetSchema['routines'] ?? array());
        foreach ($sourceRoutines as $routineKey => $sourceRoutine) {
            $routineName = (string) ($sourceRoutine['name'] ?? '');
            $routineType = (string) ($sourceRoutine['type'] ?? '');
            try {
                $sql = maintenance_structure_sync_routine_sql($sourceConn, $routineType, $routineName);
            } catch (Throwable $exception) {
                $sql = '-- Routine SQL export failed for ' . $routineType . ' ' . $routineName . ': ' . ($exception->getMessage() !== '' ? $exception->getMessage() : 'Unknown error.');
            }
            if (!isset($targetRoutines[$routineKey])) {
                $preview['missing_routines'][] = array(
                    'routine_name' => $routineName,
                    'routine_type' => $routineType,
                    'sql' => $sql,
                );
                $preview['manual_sql'][] = '-- Review missing routine ' . $routineType . ' ' . $routineName . PHP_EOL . '-- Routine SQL is not applied automatically because routine bodies may require DELIMITER handling.' . PHP_EOL . $sql;
                continue;
            }

            if (maintenance_structure_sync_routine_signature($sourceRoutine) !== maintenance_structure_sync_routine_signature($targetRoutines[$routineKey])) {
                $preview['changed_routines'][] = array(
                    'routine_name' => $routineName,
                    'routine_type' => $routineType,
                    'sql' => $sql,
                );
                $preview['manual_sql'][] = '-- Review routine change for ' . $routineType . ' ' . $routineName . PHP_EOL . '-- Routine SQL is not applied automatically because routine bodies may require DELIMITER handling.' . PHP_EOL . $sql;
            }
        }
        foreach ($targetRoutines as $routineKey => $targetRoutine) {
            if (!isset($sourceRoutines[$routineKey])) {
                $preview['extra_routines'][] = array(
                    'routine_name' => (string) ($targetRoutine['name'] ?? ''),
                    'routine_type' => (string) ($targetRoutine['type'] ?? ''),
                );
            }
        }

        $preview['summary'] = array(
            'missing_tables' => count($preview['missing_tables']),
            'missing_columns' => count($preview['missing_columns']),
            'changed_columns' => count($preview['changed_columns']),
            'missing_indexes' => count($preview['missing_indexes']),
            'missing_foreign_keys' => count($preview['missing_foreign_keys']),
            'missing_views' => count($preview['missing_views']),
            'missing_triggers' => count($preview['missing_triggers']),
            'missing_routines' => count($preview['missing_routines']),
            'warnings' => count($preview['extra_tables'])
                + count($preview['extra_columns'])
                + count($preview['extra_indexes'])
                + count($preview['extra_foreign_keys'])
                + count($preview['changed_indexes'])
                + count($preview['changed_foreign_keys'])
                + count($preview['table_option_changes'])
                + count($preview['extra_views'])
                + count($preview['changed_views'])
                + count($preview['extra_triggers'])
                + count($preview['changed_triggers'])
                + count($preview['extra_routines'])
                + count($preview['changed_routines']),
            'safe_sql_count' => count($preview['safe_sql']),
            'manual_sql_count' => count($preview['manual_sql']),
            'manual_apply_count' => count($preview['manual_apply_items']),
        );

        return $preview;
    } finally {
        $sourceConn->close();
        $targetConn->close();
    }
}

function maintenance_structure_sync_export_target_backup(array $targetConfig, string $prefix = 'structure_sync_pre_apply'): array
{
    $targetConn = maintenance_structure_sync_connect($targetConfig);
    $backupPath = maintenance_database_backup_directory() . DIRECTORY_SEPARATOR . maintenance_database_backup_file_name($prefix);

    try {
        $schema = array(
            'tables' => array_keys(maintenance_structure_sync_list_tables($targetConn, (string) ($targetConfig['database'] ?? ''))),
        );
        $lines = array(
            '-- CCW structure sync safety backup',
            '-- Generated at ' . date('Y-m-d H:i:s'),
            'SET NAMES utf8mb4;',
            'SET FOREIGN_KEY_CHECKS=0;',
            '',
        );

        foreach ((array) ($schema['tables'] ?? array()) as $tableName) {
            $lines[] = '-- Structure for table ' . $tableName;
            $lines[] = 'DROP TABLE IF EXISTS ' . maintenance_database_quote_identifier((string) $tableName) . ';';
            $lines[] = maintenance_database_show_create($targetConn, 'table', (string) $tableName) . ';';
            $lines[] = '';

            $result = $targetConn->query('SELECT * FROM ' . maintenance_database_quote_identifier((string) $tableName));
            if (!$result instanceof mysqli_result) {
                throw new RuntimeException('Target rows could not be read from ' . $tableName . ' before apply.');
            }

            $fields = $result->fetch_fields();
            $columnNames = array_map(static function (object $field): string {
                return maintenance_database_quote_identifier((string) ($field->name ?? ''));
            }, $fields);

            $batch = array();
            while ($row = $result->fetch_assoc()) {
                $values = array();
                foreach ($fields as $field) {
                    $fieldName = (string) ($field->name ?? '');
                    $values[] = maintenance_database_export_value($targetConn, $row[$fieldName] ?? null);
                }

                $batch[] = '(' . implode(', ', $values) . ')';
                if (count($batch) >= 100) {
                    $lines[] = 'INSERT INTO ' . maintenance_database_quote_identifier((string) $tableName)
                        . ' (' . implode(', ', $columnNames) . ') VALUES '
                        . implode(",\n", $batch) . ';';
                    $batch = array();
                }
            }
            $result->close();

            if ($batch !== array()) {
                $lines[] = 'INSERT INTO ' . maintenance_database_quote_identifier((string) $tableName)
                    . ' (' . implode(', ', $columnNames) . ') VALUES '
                    . implode(",\n", $batch) . ';';
            }

            $lines[] = '';
        }

        $lines[] = 'SET FOREIGN_KEY_CHECKS=1;';
        $contents = implode(PHP_EOL, $lines) . PHP_EOL;

        if (@file_put_contents($backupPath, $contents) === false || !is_file($backupPath) || filesize($backupPath) <= 0) {
            throw new RuntimeException('Structure sync backup could not be written for target database.');
        }

        return array(
            'file_name' => basename($backupPath),
            'relative_path' => 'storage/database_backups/' . basename($backupPath),
            'size_bytes' => (int) filesize($backupPath),
            'database' => (string) ($targetConfig['database'] ?? ''),
            'method' => 'php',
        );
    } finally {
        $targetConn->close();
    }
}

function maintenance_structure_sync_build_patch_sql(array $preview, string $scope = 'combined'): string
{
    $scopeKey = strtolower(trim($scope));
    $safeSql = array_values(array_filter(array_map('trim', (array) ($preview['safe_sql'] ?? array())), static fn(string $sql): bool => $sql !== ''));
    $manualSql = array_values(array_filter(array_map('trim', (array) ($preview['manual_sql'] ?? array())), static fn(string $sql): bool => $sql !== ''));

    $sections = array(
        '-- CCW Database Structure Sync Patch',
        '-- Generated at ' . date('Y-m-d H:i:s'),
        '-- Source DB: ' . (string) (($preview['source']['database'] ?? '-')),
        '-- Target DB: ' . (string) (($preview['target']['database'] ?? '-')),
        '',
    );

    if ($scopeKey === 'safe' || $scopeKey === 'combined') {
        $sections[] = '-- Safe SQL patch';
        $sections[] = $safeSql !== array() ? implode(PHP_EOL . PHP_EOL, $safeSql) : '-- No safe SQL changes detected.';
        $sections[] = '';
    }

    if ($scopeKey === 'manual' || $scopeKey === 'combined') {
        $sections[] = '-- Manual SQL / review notes';
        $sections[] = $manualSql !== array() ? implode(PHP_EOL . PHP_EOL, $manualSql) : '-- No manual review items detected.';
        $sections[] = '';
    }

    return trim(implode(PHP_EOL, $sections)) . PHP_EOL;
}

function maintenance_execute_database_structure_sync(mysqli $conn, array $sourceInput, array $targetInput): array
{
    $preview = maintenance_structure_sync_preview($sourceInput, $targetInput, $conn);
    $safeSql = array_values(array_filter(array_map('trim', (array) ($preview['safe_sql'] ?? array())), static fn(string $sql): bool => $sql !== ''));

    if ($safeSql === array()) {
        maintenance_log_action($conn, array(
            'maintenance_type' => 'database_structure_sync_apply',
            'status' => 'completed',
            'filters' => array(
                'source' => (array) ($preview['source'] ?? array()),
                'target' => (array) ($preview['target'] ?? array()),
            ),
            'preview' => array(
                'summary' => (array) ($preview['summary'] ?? array()),
            ),
            'result' => array(
                'executed_count' => 0,
                'backup' => null,
            ),
            'note' => 'Database Structure Sync found no safe statements to apply.',
        ));

        return array(
            'executed_count' => 0,
            'backup' => null,
            'summary' => (array) ($preview['summary'] ?? array()),
        );
    }

    $targetConfig = maintenance_structure_sync_resolve_config($conn, $targetInput);
    $backup = maintenance_structure_sync_export_target_backup($targetConfig);
    $targetConn = maintenance_structure_sync_connect($targetConfig);

    try {
        @set_time_limit(0);
        $targetConn->query('SET FOREIGN_KEY_CHECKS=0');
        $executed = 0;

        foreach ($safeSql as $sql) {
            $statement = rtrim($sql, " \r\n\t;");
            if ($statement === '') {
                continue;
            }

            if (!$targetConn->query($statement)) {
                throw new RuntimeException('Database Structure Sync failed near: ' . substr($statement, 0, 180));
            }
            $executed++;
        }

        $targetConn->query('SET FOREIGN_KEY_CHECKS=1');

        maintenance_log_action($conn, array(
            'maintenance_type' => 'database_structure_sync_apply',
            'status' => 'completed',
            'snapshot_path' => (string) ($backup['relative_path'] ?? ''),
            'filters' => array(
                'source' => (array) ($preview['source'] ?? array()),
                'target' => (array) ($preview['target'] ?? array()),
            ),
            'preview' => array(
                'summary' => (array) ($preview['summary'] ?? array()),
            ),
            'result' => array(
                'executed_count' => $executed,
                'backup' => $backup,
                'safe_sql_count' => count($safeSql),
                'manual_sql_count' => count((array) ($preview['manual_sql'] ?? array())),
            ),
            'note' => 'Database Structure Sync applied safe structure updates to target.',
        ));

        return array(
            'executed_count' => $executed,
            'backup' => $backup,
            'summary' => (array) ($preview['summary'] ?? array()),
        );
    } finally {
        $targetConn->close();
    }
}

function maintenance_execute_database_structure_manual_sync(mysqli $conn, array $sourceInput, array $targetInput, array $selectedManualSql): array
{
    $preview = maintenance_structure_sync_preview($sourceInput, $targetInput, $conn);
    $allowedManualSql = array_values(array_filter(array_map(
        static fn(array $item): string => trim((string) ($item['sql'] ?? '')),
        (array) ($preview['manual_apply_items'] ?? array())
    ), static fn(string $sql): bool => $sql !== ''));
    $allowedLookup = array_fill_keys($allowedManualSql, true);

    $selected = array();
    foreach ($selectedManualSql as $sqlBlock) {
        $value = trim((string) $sqlBlock);
        if ($value !== '' && isset($allowedLookup[$value])) {
            $selected[$value] = $value;
        }
    }
    $selected = array_values($selected);

    if ($selected === array()) {
        throw new RuntimeException('No manual SQL items were selected, or the selected items no longer match the current compare result.');
    }

    $targetConfig = maintenance_structure_sync_resolve_config($conn, $targetInput);
    $backup = maintenance_structure_sync_export_target_backup($targetConfig, 'structure_sync_pre_manual');
    $targetConn = maintenance_structure_sync_connect($targetConfig);

    try {
        @set_time_limit(0);
        $targetConn->query('SET FOREIGN_KEY_CHECKS=0');
        $executed = 0;

        foreach ($selected as $sqlBlock) {
            $statements = maintenance_database_sql_statements_from_string($sqlBlock);
            foreach ($statements as $statement) {
                $query = trim($statement);
                if ($query === '') {
                    continue;
                }

                if (!$targetConn->query($query)) {
                    throw new RuntimeException('Manual Database Structure Sync failed near: ' . substr($query, 0, 180));
                }
                $executed++;
            }
        }

        $targetConn->query('SET FOREIGN_KEY_CHECKS=1');

        maintenance_log_action($conn, array(
            'maintenance_type' => 'database_structure_sync_apply_manual',
            'status' => 'completed',
            'snapshot_path' => (string) ($backup['relative_path'] ?? ''),
            'filters' => array(
                'source' => (array) ($preview['source'] ?? array()),
                'target' => (array) ($preview['target'] ?? array()),
            ),
            'preview' => array(
                'summary' => (array) ($preview['summary'] ?? array()),
            ),
            'result' => array(
                'selected_item_count' => count($selected),
                'executed_count' => $executed,
                'backup' => $backup,
            ),
            'note' => 'Database Structure Sync applied selected manual SQL items to target.',
        ));

        return array(
            'selected_item_count' => count($selected),
            'executed_count' => $executed,
            'backup' => $backup,
            'summary' => (array) ($preview['summary'] ?? array()),
        );
    } finally {
        $targetConn->close();
    }
}

function maintenance_write_snapshot(string $prefix, array $payload): string
{
    $directory = maintenance_export_directory();
    $safePrefix = trim((string) preg_replace('/[^a-z0-9_]+/i', '_', strtolower($prefix)), '_');
    if ($safePrefix === '') {
        $safePrefix = 'maintenance';
    }

    $fileName = $safePrefix . '_' . date('Ymd_His') . '_' . substr((string) md5(uniqid('', true)), 0, 8) . '.json';
    $absolutePath = $directory . '/' . $fileName;
    $relativePath = 'storage/maintenance_exports/' . $fileName;

    $json = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
    if ($json === false) {
        throw new RuntimeException('Snapshot export could not be encoded.');
    }

    if (@file_put_contents($absolutePath, $json) === false) {
        throw new RuntimeException('Snapshot export could not be written.');
    }

    return $relativePath;
}

function maintenance_log_action(mysqli $conn, array $entry): int
{
    ensure_system_maintenance_schema($conn);

    $currentUser = auth_current_user($conn);
    $maintenanceType = trim((string) ($entry['maintenance_type'] ?? 'maintenance'));
    $status = trim((string) ($entry['status'] ?? 'completed'));
    $actorEmployeeId = isset($entry['actor_employee_id']) ? (int) $entry['actor_employee_id'] : (int) ($currentUser['employee_id'] ?? 0);
    $actorName = trim((string) ($entry['actor_name'] ?? ($currentUser['name'] ?? '')));
    $branchName = trim((string) ($entry['branch_name'] ?? ''));
    $snapshotPath = trim((string) ($entry['snapshot_path'] ?? ''));
    $filtersJson = audit_encode_payload($entry['filters'] ?? null);
    $previewJson = audit_encode_payload($entry['preview'] ?? null);
    $resultJson = audit_encode_payload($entry['result'] ?? null);
    $note = trim((string) ($entry['note'] ?? ''));

    if ($maintenanceType === '') {
        $maintenanceType = 'maintenance';
    }

    if ($status === '') {
        $status = 'completed';
    }

    if ($actorEmployeeId <= 0) {
        $actorEmployeeId = null;
    }

    if ($actorName === '') {
        $actorName = null;
    }

    if ($branchName === '') {
        $branchName = null;
    }

    if ($snapshotPath === '') {
        $snapshotPath = null;
    }

    if ($note === '') {
        $note = null;
    }

    $stmt = db_prepare($conn, "
        INSERT INTO system_maintenance_logs (
            maintenance_type,
            status,
            actor_employee_id,
            actor_name,
            branch_name,
            snapshot_path,
            filters_json,
            preview_json,
            result_json,
            note
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param(
        'ssisssssss',
        $maintenanceType,
        $status,
        $actorEmployeeId,
        $actorName,
        $branchName,
        $snapshotPath,
        $filtersJson,
        $previewJson,
        $resultJson,
        $note
    );
    $stmt->execute();
    $maintenanceLogId = (int) $conn->insert_id;
    $stmt->close();

    return $maintenanceLogId;
}

function maintenance_branch_options(mysqli $conn): array
{
    return auth_filter_branch_options($conn, service_branches());
}

function maintenance_parse_date(string $value): ?string
{
    $value = trim($value);
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) {
        return null;
    }

    $date = DateTime::createFromFormat('Y-m-d', $value);
    if (!$date || $date->format('Y-m-d') !== $value) {
        return null;
    }

    return $value;
}

function maintenance_selected_values($value): array
{
    if (is_array($value)) {
        return array_values(array_filter(array_map('strval', $value), static function ($item): bool {
            return trim((string) $item) !== '';
        }));
    }

    $single = trim((string) $value);
    return $single === '' ? array() : array($single);
}

function maintenance_integer_ids(array $values): array
{
    $ids = array();
    foreach ($values as $value) {
        $id = (int) $value;
        if ($id > 0) {
            $ids[] = $id;
        }
    }

    return array_values(array_unique($ids));
}

function maintenance_build_clear_log_scope(mysqli $conn, array $filters): array
{
    $errors = array();
    $dateFrom = maintenance_parse_date((string) ($filters['date_from'] ?? ''));
    $dateTo = maintenance_parse_date((string) ($filters['date_to'] ?? ''));
    $module = trim((string) ($filters['module'] ?? ''));
    $action = trim((string) ($filters['action'] ?? ''));
    $branchName = trim((string) ($filters['branch_name'] ?? ''));
    $today = date('Y-m-d');

    if ($dateFrom === null) {
        $errors[] = 'Date from is required for Clear Log.';
    }

    if ($dateTo === null) {
        $errors[] = 'Date to is required for Clear Log.';
    }

    if ($dateFrom !== null && $dateTo !== null && $dateFrom > $dateTo) {
        $errors[] = 'Date from must be earlier than or equal to Date to.';
    }

    if ($dateTo !== null && $dateTo >= $today) {
        $errors[] = 'Clear Log does not allow deleting logs from today.';
    }

    if ($branchName !== '' && !auth_can_access_branch($conn, $branchName)) {
        $errors[] = 'Branch access denied for this filter.';
    }

    $normalized = array(
        'date_from' => $dateFrom ?? '',
        'date_to' => $dateTo ?? '',
        'module' => $module,
        'action' => $action,
        'branch_name' => $branchName,
    );

    if (!empty($errors)) {
        return array(
            'ok' => false,
            'errors' => $errors,
            'filters' => $normalized,
            'where_sql' => '1 = 0',
            'types' => '',
            'params' => array(),
        );
    }

    $startAt = $dateFrom . ' 00:00:00';
    $endAt = date('Y-m-d 00:00:00', strtotime($dateTo . ' +1 day'));
    $where = array(
        'created_at >= ?',
        'created_at < ?',
    );
    $types = 'ss';
    $params = array($startAt, $endAt);

    $branchAccessCondition = auth_branch_condition($conn, 'audit_logs', $types, $params);
    if ($branchAccessCondition !== '') {
        $where[] = $branchAccessCondition;
    }

    if ($branchName !== '') {
        $where[] = 'branch_name = ?';
        $types .= 's';
        $params[] = $branchName;
    }

    if ($module !== '') {
        $where[] = 'module_key = ?';
        $types .= 's';
        $params[] = $module;
    }

    if ($action !== '') {
        $where[] = 'action_key = ?';
        $types .= 's';
        $params[] = $action;
    }

    return array(
        'ok' => true,
        'errors' => array(),
        'filters' => $normalized,
        'where_sql' => implode(' AND ', $where),
        'types' => $types,
        'params' => $params,
    );
}

function maintenance_preview_clear_log(mysqli $conn, array $filters): array
{
    $scope = maintenance_build_clear_log_scope($conn, $filters);
    $preview = array(
        'ok' => $scope['ok'],
        'can_execute' => false,
        'errors' => $scope['errors'],
        'warnings' => array(),
        'filters' => $scope['filters'],
        'summary' => array(
            'total_logs' => 0,
            'row_limit' => 50000,
        ),
        'module_rows' => array(),
        'action_rows' => array(),
    );

    if (!$scope['ok']) {
        return $preview;
    }

    $whereSql = $scope['where_sql'];
    $types = $scope['types'];
    $params = $scope['params'];

    $summaryStmt = db_prepare($conn, "SELECT COUNT(*) AS total_logs FROM audit_logs WHERE {$whereSql}");
    $summaryStmt->bind_param($types, ...$params);
    $summaryStmt->execute();
    $summary = $summaryStmt->get_result()->fetch_assoc() ?: array();
    $summaryStmt->close();

    $totalLogs = (int) ($summary['total_logs'] ?? 0);
    $preview['summary']['total_logs'] = $totalLogs;

    $moduleStmt = db_prepare($conn, "
        SELECT module_key, COUNT(*) AS row_count
        FROM audit_logs
        WHERE {$whereSql}
        GROUP BY module_key
        ORDER BY row_count DESC, module_key ASC
    ");
    $moduleStmt->bind_param($types, ...$params);
    $moduleStmt->execute();
    $moduleResult = $moduleStmt->get_result();
    while ($row = $moduleResult->fetch_assoc()) {
        $preview['module_rows'][] = $row;
    }
    $moduleStmt->close();

    $actionStmt = db_prepare($conn, "
        SELECT action_key, COUNT(*) AS row_count
        FROM audit_logs
        WHERE {$whereSql}
        GROUP BY action_key
        ORDER BY row_count DESC, action_key ASC
    ");
    $actionStmt->bind_param($types, ...$params);
    $actionStmt->execute();
    $actionResult = $actionStmt->get_result();
    while ($row = $actionResult->fetch_assoc()) {
        $preview['action_rows'][] = $row;
    }
    $actionStmt->close();

    if ($totalLogs <= 0) {
        $preview['warnings'][] = 'No audit log rows matched this filter.';
        return $preview;
    }

    if ($totalLogs > 50000) {
        $preview['warnings'][] = 'This filter matches more than 50,000 log rows. Narrow the range before clearing.';
        return $preview;
    }

    $preview['can_execute'] = true;

    return $preview;
}

function maintenance_select_rows_by_int_ids(mysqli $conn, string $table, string $column, array $ids, string $orderBy = ''): array
{
    $ids = maintenance_integer_ids($ids);
    if (empty($ids)) {
        return array();
    }

    $rows = array();
    foreach (array_chunk($ids, 500) as $chunk) {
        $placeholders = implode(',', array_fill(0, count($chunk), '?'));
        $types = str_repeat('i', count($chunk));
        $sql = "SELECT * FROM {$table} WHERE {$column} IN ({$placeholders})";
        if ($orderBy !== '') {
            $sql .= " ORDER BY {$orderBy}";
        }

        $stmt = db_prepare($conn, $sql);
        $stmt->bind_param($types, ...$chunk);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        $stmt->close();
    }

    return $rows;
}

function maintenance_delete_rows_by_int_ids(mysqli $conn, string $table, string $column, array $ids): int
{
    $ids = maintenance_integer_ids($ids);
    if (empty($ids)) {
        return 0;
    }

    $deleted = 0;
    foreach (array_chunk($ids, 500) as $chunk) {
        $placeholders = implode(',', array_fill(0, count($chunk), '?'));
        $types = str_repeat('i', count($chunk));
        $stmt = db_prepare($conn, "DELETE FROM {$table} WHERE {$column} IN ({$placeholders})");
        $stmt->bind_param($types, ...$chunk);
        $stmt->execute();
        $deleted += $stmt->affected_rows;
        $stmt->close();
    }

    return $deleted;
}

function maintenance_execute_clear_log(mysqli $conn, array $filters): array
{
    $preview = maintenance_preview_clear_log($conn, $filters);
    if (!$preview['ok']) {
        throw new RuntimeException($preview['errors'][0] ?? 'Clear Log preview is invalid.');
    }

    if (!$preview['can_execute']) {
        throw new RuntimeException($preview['warnings'][0] ?? 'Clear Log cannot run for this filter.');
    }

    $scope = maintenance_build_clear_log_scope($conn, $filters);
    $whereSql = $scope['where_sql'];
    $types = $scope['types'];
    $params = $scope['params'];

    $idStmt = db_prepare($conn, "SELECT audit_log_id FROM audit_logs WHERE {$whereSql} ORDER BY audit_log_id ASC");
    $idStmt->bind_param($types, ...$params);
    $idStmt->execute();
    $idResult = $idStmt->get_result();
    $auditLogIds = array();
    while ($row = $idResult->fetch_assoc()) {
        $auditLogIds[] = (int) ($row['audit_log_id'] ?? 0);
    }
    $idStmt->close();

    if (empty($auditLogIds)) {
        throw new RuntimeException('No audit log rows are available to clear.');
    }

    $auditRows = maintenance_select_rows_by_int_ids($conn, 'audit_logs', 'audit_log_id', $auditLogIds, 'audit_log_id ASC');
    $snapshotPath = maintenance_write_snapshot('clear_log', array(
        'generated_at' => date('c'),
        'filters' => $preview['filters'],
        'summary' => $preview['summary'],
        'rows' => $auditRows,
    ));

    $deletedCount = 0;
    $conn->begin_transaction();

    try {
        $deletedCount = maintenance_delete_rows_by_int_ids($conn, 'audit_logs', 'audit_log_id', $auditLogIds);

        $result = array(
            'deleted_logs' => $deletedCount,
            'deleted_audit_log_ids' => $auditLogIds,
        );

        maintenance_log_action($conn, array(
            'maintenance_type' => 'clear_log',
            'status' => 'completed',
            'branch_name' => (string) ($preview['filters']['branch_name'] ?? ''),
            'snapshot_path' => $snapshotPath,
            'filters' => $preview['filters'],
            'preview' => array(
                'summary' => $preview['summary'],
                'module_rows' => $preview['module_rows'],
                'action_rows' => $preview['action_rows'],
            ),
            'result' => $result,
            'note' => 'Audit log rows were permanently removed.',
        ));

        $conn->commit();

        return array(
            'snapshot_path' => $snapshotPath,
            'deleted_count' => $deletedCount,
            'filters' => $preview['filters'],
        );
    } catch (Throwable $exception) {
        $conn->rollback();
        throw $exception;
    }
}

function maintenance_allowed_booking_statuses(): array
{
    return array('waiting', 'cancelled');
}

function maintenance_build_booking_queue_scope(mysqli $conn, array $filters): array
{
    $errors = array();
    $branchName = trim((string) ($filters['branch_name'] ?? ''));
    $dateFrom = maintenance_parse_date((string) ($filters['date_from'] ?? ''));
    $dateTo = maintenance_parse_date((string) ($filters['date_to'] ?? ''));
    $statuses = array_values(array_intersect(
        maintenance_selected_values($filters['statuses'] ?? array()),
        maintenance_allowed_booking_statuses()
    ));

    if ($branchName === '') {
        $errors[] = 'Branch is required for Clear Booking / Queue.';
    } elseif (!auth_can_access_branch($conn, $branchName)) {
        $errors[] = 'Branch access denied for this filter.';
    }

    if ($dateFrom === null) {
        $errors[] = 'Date from is required for Clear Booking / Queue.';
    }

    if ($dateTo === null) {
        $errors[] = 'Date to is required for Clear Booking / Queue.';
    }

    if ($dateFrom !== null && $dateTo !== null && $dateFrom > $dateTo) {
        $errors[] = 'Date from must be earlier than or equal to Date to.';
    }

    if (empty($statuses)) {
        $statuses = maintenance_allowed_booking_statuses();
    }

    $normalized = array(
        'branch_name' => $branchName,
        'date_from' => $dateFrom ?? '',
        'date_to' => $dateTo ?? '',
        'statuses' => $statuses,
    );

    if (!empty($errors)) {
        return array(
            'ok' => false,
            'errors' => $errors,
            'filters' => $normalized,
            'sql' => '',
            'types' => '',
            'params' => array(),
        );
    }

    $placeholders = implode(',', array_fill(0, count($statuses), '?'));
    $sql = "
        SELECT
            o.order_id,
            o.bill_number,
            o.branch_name,
            o.order_date,
            o.status,
            o.payment_status,
            o.total_price,
            o.customer_id,
            o.car_id,
            (SELECT COUNT(*) FROM payments p WHERE p.order_id = o.order_id) AS payment_count,
            (SELECT COUNT(*) FROM loyalty_transactions lt WHERE lt.order_id = o.order_id) AS loyalty_count,
            (SELECT COUNT(*) FROM customer_packages cp WHERE cp.order_id = o.order_id) AS package_purchase_count,
            (SELECT COUNT(*) FROM order_package_usages opu WHERE opu.order_id = o.order_id AND opu.is_reversed = 0) AS package_usage_count,
            (SELECT COUNT(*) FROM queue q WHERE q.order_id = o.order_id) AS queue_count,
            (SELECT COUNT(*) FROM order_details od WHERE od.order_id = o.order_id) AS detail_count
        FROM orders o
        WHERE o.order_source = 'booking'
            AND o.branch_name = ?
            AND o.order_date >= ?
            AND o.order_date < ?
            AND o.status IN ({$placeholders})
        ORDER BY o.order_date DESC, o.order_id DESC
    ";

    $types = 'sss' . str_repeat('s', count($statuses));
    $params = array(
        $branchName,
        $dateFrom . ' 00:00:00',
        date('Y-m-d 00:00:00', strtotime($dateTo . ' +1 day')),
    );
    foreach ($statuses as $status) {
        $params[] = $status;
    }

    return array(
        'ok' => true,
        'errors' => array(),
        'filters' => $normalized,
        'sql' => $sql,
        'types' => $types,
        'params' => $params,
    );
}

function maintenance_preview_clear_booking_queue(mysqli $conn, array $filters): array
{
    $scope = maintenance_build_booking_queue_scope($conn, $filters);
    $preview = array(
        'ok' => $scope['ok'],
        'can_execute' => false,
        'errors' => $scope['errors'],
        'warnings' => array(),
        'filters' => $scope['filters'],
        'summary' => array(
            'matched_orders' => 0,
            'eligible_orders' => 0,
            'blocked_orders' => 0,
        ),
        'eligible_orders' => array(),
        'blocked_orders' => array(),
        'blocked_reasons' => array(),
    );

    if (!$scope['ok']) {
        return $preview;
    }

    $stmt = db_prepare($conn, $scope['sql']);
    $stmt->bind_param($scope['types'], ...$scope['params']);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();

    $preview['summary']['matched_orders'] = count($rows);

    foreach ($rows as $row) {
        $reasons = array();
        if ((string) ($row['payment_status'] ?? '') !== 'unpaid') {
            $reasons[] = 'payment_status_not_unpaid';
        }
        if ((int) ($row['payment_count'] ?? 0) > 0) {
            $reasons[] = 'has_payment';
        }
        if ((int) ($row['loyalty_count'] ?? 0) > 0) {
            $reasons[] = 'has_loyalty';
        }
        if ((int) ($row['package_purchase_count'] ?? 0) > 0) {
            $reasons[] = 'has_package_purchase';
        }
        if ((int) ($row['package_usage_count'] ?? 0) > 0) {
            $reasons[] = 'has_package_usage';
        }

        $entry = array(
            'order_id' => (int) ($row['order_id'] ?? 0),
            'bill_number' => (string) ($row['bill_number'] ?? ''),
            'branch_name' => (string) ($row['branch_name'] ?? ''),
            'order_date' => (string) ($row['order_date'] ?? ''),
            'status' => (string) ($row['status'] ?? ''),
            'payment_status' => (string) ($row['payment_status'] ?? ''),
            'total_price' => (float) ($row['total_price'] ?? 0),
            'detail_count' => (int) ($row['detail_count'] ?? 0),
            'queue_count' => (int) ($row['queue_count'] ?? 0),
        );

        if (empty($reasons)) {
            $preview['eligible_orders'][] = $entry;
            continue;
        }

        $entry['blocked_reasons'] = $reasons;
        $preview['blocked_orders'][] = $entry;
        foreach ($reasons as $reason) {
            $preview['blocked_reasons'][$reason] = (int) ($preview['blocked_reasons'][$reason] ?? 0) + 1;
        }
    }

    $preview['summary']['eligible_orders'] = count($preview['eligible_orders']);
    $preview['summary']['blocked_orders'] = count($preview['blocked_orders']);
    $preview['can_execute'] = !empty($preview['eligible_orders']);

    if (empty($rows)) {
        $preview['warnings'][] = 'No booking orders matched this filter.';
    } elseif (empty($preview['eligible_orders'])) {
        $preview['warnings'][] = 'No booking orders are eligible to clear for this filter.';
    }

    return $preview;
}

function maintenance_execute_clear_booking_queue(mysqli $conn, array $filters): array
{
    $preview = maintenance_preview_clear_booking_queue($conn, $filters);
    if (!$preview['ok']) {
        throw new RuntimeException($preview['errors'][0] ?? 'Clear Booking / Queue preview is invalid.');
    }

    if (!$preview['can_execute']) {
        throw new RuntimeException($preview['warnings'][0] ?? 'No booking orders are eligible to clear.');
    }

    $orderIds = array_map(static function (array $row): int {
        return (int) ($row['order_id'] ?? 0);
    }, $preview['eligible_orders']);
    $orderIds = maintenance_integer_ids($orderIds);

    $orderRows = maintenance_select_rows_by_int_ids($conn, 'orders', 'order_id', $orderIds, 'order_id ASC');
    $queueRows = maintenance_select_rows_by_int_ids($conn, 'queue', 'order_id', $orderIds, 'queue_id ASC');
    $detailRows = maintenance_select_rows_by_int_ids($conn, 'order_details', 'order_id', $orderIds, 'detail_id ASC');

    $snapshotPath = maintenance_write_snapshot('clear_booking_queue', array(
        'generated_at' => date('c'),
        'filters' => $preview['filters'],
        'preview' => $preview['summary'],
        'orders' => $orderRows,
        'queue' => $queueRows,
        'order_details' => $detailRows,
    ));

    $conn->begin_transaction();

    try {
        $deletedQueue = maintenance_delete_rows_by_int_ids($conn, 'queue', 'order_id', $orderIds);
        $deletedDetails = maintenance_delete_rows_by_int_ids($conn, 'order_details', 'order_id', $orderIds);
        $deletedOrders = maintenance_delete_rows_by_int_ids($conn, 'orders', 'order_id', $orderIds);

        $result = array(
            'deleted_orders' => $deletedOrders,
            'deleted_queue_rows' => $deletedQueue,
            'deleted_order_detail_rows' => $deletedDetails,
            'order_ids' => $orderIds,
            'bill_numbers' => array_values(array_filter(array_map(static function (array $row): string {
                return (string) ($row['bill_number'] ?? '');
            }, $preview['eligible_orders']))),
        );

        maintenance_log_action($conn, array(
            'maintenance_type' => 'clear_booking_queue',
            'status' => 'completed',
            'branch_name' => (string) ($preview['filters']['branch_name'] ?? ''),
            'snapshot_path' => $snapshotPath,
            'filters' => $preview['filters'],
            'preview' => array(
                'summary' => $preview['summary'],
                'blocked_reasons' => $preview['blocked_reasons'],
            ),
            'result' => $result,
            'note' => 'Booking queue data was permanently removed.',
        ));

        $conn->commit();

        return array(
            'snapshot_path' => $snapshotPath,
            'deleted_orders' => $deletedOrders,
            'deleted_queue_rows' => $deletedQueue,
            'deleted_detail_rows' => $deletedDetails,
            'filters' => $preview['filters'],
        );
    } catch (Throwable $exception) {
        $conn->rollback();
        throw $exception;
    }
}

function maintenance_allowed_sale_statuses(): array
{
    return array('done', 'cancelled');
}

function maintenance_build_sale_scope(mysqli $conn, array $filters): array
{
    $errors = array();
    $branchName = trim((string) ($filters['branch_name'] ?? ''));
    $dateFrom = maintenance_parse_date((string) ($filters['date_from'] ?? ''));
    $dateTo = maintenance_parse_date((string) ($filters['date_to'] ?? ''));
    $orderSource = trim((string) ($filters['order_source'] ?? 'both'));
    $statuses = array_values(array_intersect(
        maintenance_selected_values($filters['statuses'] ?? array()),
        maintenance_allowed_sale_statuses()
    ));

    if ($branchName === '') {
        $errors[] = 'Branch is required for Clear Sale.';
    } elseif (!auth_can_access_branch($conn, $branchName)) {
        $errors[] = 'Branch access denied for this filter.';
    }

    if ($dateFrom === null) {
        $errors[] = 'Date from is required for Clear Sale.';
    }

    if ($dateTo === null) {
        $errors[] = 'Date to is required for Clear Sale.';
    }

    if ($dateFrom !== null && $dateTo !== null && $dateFrom > $dateTo) {
        $errors[] = 'Date from must be earlier than or equal to Date to.';
    }

    if (!in_array($orderSource, array('both', 'booking', 'quick_service'), true)) {
        $orderSource = 'both';
    }

    if (empty($statuses)) {
        $statuses = maintenance_allowed_sale_statuses();
    }

    $normalized = array(
        'branch_name' => $branchName,
        'date_from' => $dateFrom ?? '',
        'date_to' => $dateTo ?? '',
        'order_source' => $orderSource,
        'statuses' => $statuses,
    );

    if (!empty($errors)) {
        return array(
            'ok' => false,
            'errors' => $errors,
            'filters' => $normalized,
            'sql' => '',
            'types' => '',
            'params' => array(),
        );
    }

    $statusPlaceholders = implode(',', array_fill(0, count($statuses), '?'));
    $where = array(
        "o.branch_name = ?",
        "o.order_date >= ?",
        "o.order_date < ?",
        "o.status IN ({$statusPlaceholders})",
    );
    $types = 'sss' . str_repeat('s', count($statuses));
    $params = array(
        $branchName,
        $dateFrom . ' 00:00:00',
        date('Y-m-d 00:00:00', strtotime($dateTo . ' +1 day')),
    );
    foreach ($statuses as $status) {
        $params[] = $status;
    }

    if ($orderSource !== 'both') {
        $where[] = 'o.order_source = ?';
        $types .= 's';
        $params[] = $orderSource;
    }

    $sql = "
        SELECT
            o.order_id,
            o.bill_number,
            o.branch_name,
            o.order_date,
            o.order_source,
            o.status,
            o.payment_status,
            o.total_price,
            o.customer_id,
            o.car_id,
            (SELECT COALESCE(SUM(p.amount), 0) FROM payments p WHERE p.order_id = o.order_id) AS paid_amount,
            (SELECT COUNT(*) FROM payments p WHERE p.order_id = o.order_id) AS payment_count,
            (SELECT COALESCE(SUM(CASE WHEN lt.transaction_type = 'earn' THEN lt.points ELSE 0 END), 0) FROM loyalty_transactions lt WHERE lt.order_id = o.order_id) AS loyalty_earn_points,
            (SELECT COUNT(*) FROM loyalty_transactions lt WHERE lt.order_id = o.order_id AND lt.transaction_type <> 'earn') AS loyalty_non_earn_count,
            (SELECT COUNT(*) FROM loyalty_transactions lt WHERE lt.order_id = o.order_id) AS loyalty_count,
            (SELECT COUNT(*) FROM order_package_usages opu WHERE opu.order_id = o.order_id AND opu.is_reversed = 0) AS active_package_usage_count,
            (SELECT COUNT(*) FROM customer_packages cp WHERE cp.order_id = o.order_id) AS package_purchase_count,
            (
                SELECT COUNT(*)
                FROM customer_packages cp
                INNER JOIN customer_package_items cpi
                    ON cpi.customer_package_id = cp.customer_package_id
                WHERE cp.order_id = o.order_id
                    AND cpi.used_uses > 0
            ) AS package_purchase_used_count
        FROM orders o
        WHERE " . implode(' AND ', $where) . "
        ORDER BY o.order_date DESC, o.order_id DESC
    ";

    return array(
        'ok' => true,
        'errors' => array(),
        'filters' => $normalized,
        'sql' => $sql,
        'types' => $types,
        'params' => $params,
    );
}

function maintenance_preview_clear_sale(mysqli $conn, array $filters): array
{
    $scope = maintenance_build_sale_scope($conn, $filters);
    $preview = array(
        'ok' => $scope['ok'],
        'can_execute' => false,
        'errors' => $scope['errors'],
        'warnings' => array(),
        'filters' => $scope['filters'],
        'summary' => array(
            'matched_orders' => 0,
            'eligible_orders' => 0,
            'blocked_orders' => 0,
            'payment_total' => 0.0,
            'loyalty_reverse_points' => 0,
            'package_purchases_to_cancel' => 0,
            'package_usages_to_reverse' => 0,
        ),
        'eligible_orders' => array(),
        'blocked_orders' => array(),
        'blocked_reasons' => array(),
    );

    if (!$scope['ok']) {
        return $preview;
    }

    $stmt = db_prepare($conn, $scope['sql']);
    $stmt->bind_param($scope['types'], ...$scope['params']);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();

    $preview['summary']['matched_orders'] = count($rows);

    foreach ($rows as $row) {
        $reasons = array();
        if ((string) ($row['payment_status'] ?? '') !== 'paid') {
            $reasons[] = 'payment_not_fully_closed';
        }
        if ((int) ($row['loyalty_non_earn_count'] ?? 0) > 0) {
            $reasons[] = 'has_non_earn_loyalty';
        }
        if ((int) ($row['package_purchase_used_count'] ?? 0) > 0) {
            $reasons[] = 'package_purchase_already_used';
        }

        $entry = array(
            'order_id' => (int) ($row['order_id'] ?? 0),
            'bill_number' => (string) ($row['bill_number'] ?? ''),
            'branch_name' => (string) ($row['branch_name'] ?? ''),
            'order_date' => (string) ($row['order_date'] ?? ''),
            'order_source' => (string) ($row['order_source'] ?? ''),
            'status' => (string) ($row['status'] ?? ''),
            'payment_status' => (string) ($row['payment_status'] ?? ''),
            'total_price' => (float) ($row['total_price'] ?? 0),
            'paid_amount' => (float) ($row['paid_amount'] ?? 0),
            'loyalty_earn_points' => (int) ($row['loyalty_earn_points'] ?? 0),
            'payment_count' => (int) ($row['payment_count'] ?? 0),
            'package_purchase_count' => (int) ($row['package_purchase_count'] ?? 0),
            'active_package_usage_count' => (int) ($row['active_package_usage_count'] ?? 0),
        );

        if (empty($reasons)) {
            $preview['eligible_orders'][] = $entry;
            $preview['summary']['payment_total'] += (float) ($row['paid_amount'] ?? 0);
            $preview['summary']['loyalty_reverse_points'] += (int) ($row['loyalty_earn_points'] ?? 0);
            $preview['summary']['package_purchases_to_cancel'] += (int) ($row['package_purchase_count'] ?? 0);
            $preview['summary']['package_usages_to_reverse'] += (int) ($row['active_package_usage_count'] ?? 0);
            continue;
        }

        $entry['blocked_reasons'] = $reasons;
        $preview['blocked_orders'][] = $entry;
        foreach ($reasons as $reason) {
            $preview['blocked_reasons'][$reason] = (int) ($preview['blocked_reasons'][$reason] ?? 0) + 1;
        }
    }

    $preview['summary']['eligible_orders'] = count($preview['eligible_orders']);
    $preview['summary']['blocked_orders'] = count($preview['blocked_orders']);
    $preview['can_execute'] = !empty($preview['eligible_orders']);

    if (empty($rows)) {
        $preview['warnings'][] = 'No sale rows matched this filter.';
    } elseif (empty($preview['eligible_orders'])) {
        $preview['warnings'][] = 'No sale rows are eligible to clear for this filter.';
    }

    return $preview;
}

function maintenance_execute_clear_sale(mysqli $conn, array $filters): array
{
    $preview = maintenance_preview_clear_sale($conn, $filters);
    if (!$preview['ok']) {
        throw new RuntimeException($preview['errors'][0] ?? 'Clear Sale preview is invalid.');
    }

    if (!$preview['can_execute']) {
        throw new RuntimeException($preview['warnings'][0] ?? 'No sale rows are eligible to clear.');
    }

    $orderIds = maintenance_integer_ids(array_map(static function (array $row): int {
        return (int) ($row['order_id'] ?? 0);
    }, $preview['eligible_orders']));

    $orderRows = maintenance_select_rows_by_int_ids($conn, 'orders', 'order_id', $orderIds, 'order_id ASC');
    $paymentRows = maintenance_select_rows_by_int_ids($conn, 'payments', 'order_id', $orderIds, 'payment_id ASC');
    $queueRows = maintenance_select_rows_by_int_ids($conn, 'queue', 'order_id', $orderIds, 'queue_id ASC');
    $detailRows = maintenance_select_rows_by_int_ids($conn, 'order_details', 'order_id', $orderIds, 'detail_id ASC');
    $loyaltyRows = maintenance_select_rows_by_int_ids($conn, 'loyalty_transactions', 'order_id', $orderIds, 'transaction_id ASC');
    $packageRows = maintenance_select_rows_by_int_ids($conn, 'customer_packages', 'order_id', $orderIds, 'customer_package_id ASC');
    $packageIds = maintenance_integer_ids(array_map(static function (array $row): int {
        return (int) ($row['customer_package_id'] ?? 0);
    }, $packageRows));
    $packageItemRows = maintenance_select_rows_by_int_ids($conn, 'customer_package_items', 'customer_package_id', $packageIds, 'customer_package_item_id ASC');
    $packageUsageRows = maintenance_select_rows_by_int_ids($conn, 'order_package_usages', 'order_id', $orderIds, 'usage_id ASC');

    $snapshotPath = maintenance_write_snapshot('clear_sale', array(
        'generated_at' => date('c'),
        'filters' => $preview['filters'],
        'preview' => $preview['summary'],
        'orders' => $orderRows,
        'payments' => $paymentRows,
        'queue' => $queueRows,
        'order_details' => $detailRows,
        'loyalty_transactions' => $loyaltyRows,
        'customer_packages' => $packageRows,
        'customer_package_items' => $packageItemRows,
        'order_package_usages' => $packageUsageRows,
    ));

    $deletedCounts = array(
        'orders' => 0,
        'payments' => 0,
        'queue' => 0,
        'order_details' => 0,
        'loyalty_transactions' => 0,
        'customer_packages' => 0,
        'customer_package_items' => 0,
        'order_package_usages' => 0,
    );
    $reversedLoyaltyPoints = 0;

    $conn->begin_transaction();

    try {
        foreach ($orderIds as $orderId) {
            $orderLoyaltyRows = maintenance_select_rows_by_int_ids($conn, 'loyalty_transactions', 'order_id', array($orderId), 'transaction_id ASC');
            foreach ($orderLoyaltyRows as $loyaltyRow) {
                $transactionId = (int) ($loyaltyRow['transaction_id'] ?? 0);
                $customerId = (int) ($loyaltyRow['customer_id'] ?? 0);
                $points = (int) ($loyaltyRow['points'] ?? 0);
                $transactionType = (string) ($loyaltyRow['transaction_type'] ?? '');

                if ($transactionType !== 'earn') {
                    throw new RuntimeException('Only earn loyalty rows can be cleared from sales.');
                }

                if ($transactionId <= 0 || $customerId <= 0 || $points <= 0) {
                    continue;
                }

                $customerStmt = db_prepare($conn, "
                    UPDATE customers
                    SET loyalty_points = GREATEST(0, loyalty_points - ?)
                    WHERE customer_id = ?
                ");
                $customerStmt->bind_param('ii', $points, $customerId);
                $customerStmt->execute();
                $customerStmt->close();

                $deleteLoyaltyStmt = db_prepare($conn, "DELETE FROM loyalty_transactions WHERE transaction_id = ?");
                $deleteLoyaltyStmt->bind_param('i', $transactionId);
                $deleteLoyaltyStmt->execute();
                $deletedCounts['loyalty_transactions'] += $deleteLoyaltyStmt->affected_rows;
                $deleteLoyaltyStmt->close();
                $reversedLoyaltyPoints += $points;
            }

            service_release_order_package_effects($conn, $orderId);

            $orderPackageRows = maintenance_select_rows_by_int_ids($conn, 'customer_packages', 'order_id', array($orderId), 'customer_package_id ASC');
            $orderPackageIds = maintenance_integer_ids(array_map(static function (array $row): int {
                return (int) ($row['customer_package_id'] ?? 0);
            }, $orderPackageRows));

            if (!empty($orderPackageIds)) {
                $orderPackageItemRows = maintenance_select_rows_by_int_ids($conn, 'customer_package_items', 'customer_package_id', $orderPackageIds, 'customer_package_item_id ASC');
                foreach ($orderPackageItemRows as $itemRow) {
                    if ((int) ($itemRow['used_uses'] ?? 0) > 0) {
                        throw new RuntimeException('A package item from this bill has already been used and cannot be cleared.');
                    }
                }

                $deletedCounts['customer_package_items'] += maintenance_delete_rows_by_int_ids($conn, 'customer_package_items', 'customer_package_id', $orderPackageIds);
                $deletedCounts['customer_packages'] += maintenance_delete_rows_by_int_ids($conn, 'customer_packages', 'customer_package_id', $orderPackageIds);
            }

            $deletedCounts['order_package_usages'] += maintenance_delete_rows_by_int_ids($conn, 'order_package_usages', 'order_id', array($orderId));
            $deletedCounts['payments'] += maintenance_delete_rows_by_int_ids($conn, 'payments', 'order_id', array($orderId));
            $deletedCounts['order_details'] += maintenance_delete_rows_by_int_ids($conn, 'order_details', 'order_id', array($orderId));
            $deletedCounts['queue'] += maintenance_delete_rows_by_int_ids($conn, 'queue', 'order_id', array($orderId));
            $deletedCounts['orders'] += maintenance_delete_rows_by_int_ids($conn, 'orders', 'order_id', array($orderId));
        }

        $result = array(
            'deleted_counts' => $deletedCounts,
            'reversed_loyalty_points' => $reversedLoyaltyPoints,
            'order_ids' => $orderIds,
            'bill_numbers' => array_values(array_filter(array_map(static function (array $row): string {
                return (string) ($row['bill_number'] ?? '');
            }, $preview['eligible_orders']))),
        );

        maintenance_log_action($conn, array(
            'maintenance_type' => 'clear_sale',
            'status' => 'completed',
            'branch_name' => (string) ($preview['filters']['branch_name'] ?? ''),
            'snapshot_path' => $snapshotPath,
            'filters' => $preview['filters'],
            'preview' => array(
                'summary' => $preview['summary'],
                'blocked_reasons' => $preview['blocked_reasons'],
            ),
            'result' => $result,
            'note' => 'Sale data was permanently removed after reversing package and loyalty side effects.',
        ));

        $conn->commit();

        return array(
            'snapshot_path' => $snapshotPath,
            'deleted_counts' => $deletedCounts,
            'reversed_loyalty_points' => $reversedLoyaltyPoints,
            'filters' => $preview['filters'],
        );
    } catch (Throwable $exception) {
        $conn->rollback();
        throw $exception;
    }
}

function maintenance_reset_operational_default_input(): array
{
    return array(
        'include_logs' => true,
        'reset_counters' => false,
    );
}

function maintenance_reset_operational_table_labels(): array
{
    return array(
        'customers' => 'Customers',
        'cars' => 'Cars',
        'orders' => 'Orders / Bills',
        'order_details' => 'Order details',
        'queue' => 'Queue rows',
        'payments' => 'Payments',
        'customer_packages' => 'Customer packages',
        'customer_package_items' => 'Customer package items',
        'order_package_usages' => 'Package usage rows',
        'loyalty_transactions' => 'Loyalty transactions',
        'audit_logs' => 'Audit logs',
        'system_maintenance_logs' => 'Maintenance logs',
    );
}

function maintenance_reset_operational_selected_tables(bool $includeLogs): array
{
    $tables = array(
        'order_package_usages',
        'customer_package_items',
        'customer_packages',
        'loyalty_transactions',
        'payments',
        'queue',
        'order_details',
        'orders',
        'cars',
        'customers',
    );

    if ($includeLogs) {
        $tables[] = 'audit_logs';
        $tables[] = 'system_maintenance_logs';
    }

    return $tables;
}

function maintenance_table_row_count(mysqli $conn, string $table): int
{
    $result = $conn->query('SELECT COUNT(*) AS total_rows FROM ' . maintenance_database_quote_identifier($table));
    if (!$result instanceof mysqli_result) {
        throw new RuntimeException('Count query failed for table ' . $table . '.');
    }

    $row = $result->fetch_assoc() ?: array();
    $result->close();

    return (int) ($row['total_rows'] ?? 0);
}

function maintenance_order_branch_summary(mysqli $conn): array
{
    $result = $conn->query("
        SELECT
            COALESCE(NULLIF(branch_name, ''), '-') AS branch_name,
            order_source,
            COUNT(*) AS order_count,
            COALESCE(SUM(total_price), 0) AS total_price
        FROM orders
        GROUP BY branch_name, order_source
        ORDER BY branch_name ASC, order_source ASC
    ");
    if (!$result instanceof mysqli_result) {
        throw new RuntimeException('Order summary could not be loaded.');
    }

    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = array(
            'branch_name' => (string) ($row['branch_name'] ?? '-'),
            'order_source' => (string) ($row['order_source'] ?? 'booking'),
            'order_count' => (int) ($row['order_count'] ?? 0),
            'total_price' => (float) ($row['total_price'] ?? 0),
        );
    }
    $result->close();

    return $rows;
}

function maintenance_delete_all_rows(mysqli $conn, string $table): int
{
    $sql = 'DELETE FROM ' . maintenance_database_quote_identifier($table);
    if (!$conn->query($sql)) {
        throw new RuntimeException('Delete failed for table ' . $table . '.');
    }

    return (int) $conn->affected_rows;
}

function maintenance_reset_table_auto_increment(mysqli $conn, string $table): void
{
    $sql = 'ALTER TABLE ' . maintenance_database_quote_identifier($table) . ' AUTO_INCREMENT = 1';
    if (!$conn->query($sql)) {
        throw new RuntimeException('Auto-increment reset failed for table ' . $table . '.');
    }
}

function maintenance_preview_reset_operational_data(mysqli $conn, array $input): array
{
    $defaults = maintenance_reset_operational_default_input();
    $includeLogs = array_key_exists('include_logs', $input)
        ? filter_var($input['include_logs'], FILTER_VALIDATE_BOOLEAN)
        : (bool) $defaults['include_logs'];
    $resetCounters = array_key_exists('reset_counters', $input)
        ? filter_var($input['reset_counters'], FILTER_VALIDATE_BOOLEAN)
        : (bool) $defaults['reset_counters'];

    $filters = array(
        'include_logs' => $includeLogs,
        'reset_counters' => $resetCounters,
    );

    $preview = array(
        'ok' => true,
        'can_execute' => false,
        'errors' => array(),
        'warnings' => array(),
        'filters' => $filters,
        'summary' => array(
            'selected_rows' => 0,
            'log_rows' => 0,
            'order_count' => 0,
            'customer_count' => 0,
            'car_count' => 0,
        ),
        'selected_counts' => array(),
        'log_counts' => array(),
        'branch_summary' => array(),
    );

    if (auth_scope($conn) !== 'all') {
        $preview['ok'] = false;
        $preview['errors'][] = 'Reset Operational Data requires All branches scope.';
        return $preview;
    }

    $labels = maintenance_reset_operational_table_labels();
    $selectedTables = maintenance_reset_operational_selected_tables($includeLogs);
    $coreTables = array(
        'customers',
        'cars',
        'orders',
        'order_details',
        'queue',
        'payments',
        'customer_packages',
        'customer_package_items',
        'order_package_usages',
        'loyalty_transactions',
    );
    $logTables = array('audit_logs', 'system_maintenance_logs');

    foreach ($coreTables as $tableName) {
        $count = maintenance_table_row_count($conn, $tableName);
        $preview['selected_counts'][$tableName] = array(
            'label' => (string) ($labels[$tableName] ?? $tableName),
            'count' => $count,
            'selected' => in_array($tableName, $selectedTables, true),
        );
        $preview['summary']['selected_rows'] += $count;
    }

    foreach ($logTables as $tableName) {
        $count = maintenance_table_row_count($conn, $tableName);
        $preview[$includeLogs ? 'selected_counts' : 'log_counts'][$tableName] = array(
            'label' => (string) ($labels[$tableName] ?? $tableName),
            'count' => $count,
            'selected' => in_array($tableName, $selectedTables, true),
        );
        if ($includeLogs) {
            $preview['summary']['selected_rows'] += $count;
            $preview['summary']['log_rows'] += $count;
        } else {
            $preview['summary']['log_rows'] += $count;
        }
    }

    $preview['summary']['order_count'] = (int) ($preview['selected_counts']['orders']['count'] ?? 0);
    $preview['summary']['customer_count'] = (int) ($preview['selected_counts']['customers']['count'] ?? 0);
    $preview['summary']['car_count'] = (int) ($preview['selected_counts']['cars']['count'] ?? 0);
    $preview['branch_summary'] = maintenance_order_branch_summary($conn);
    $preview['can_execute'] = $preview['summary']['selected_rows'] > 0;

    if (!$preview['can_execute']) {
        $preview['warnings'][] = 'No operational rows were found to reset.';
    }

    if (!$includeLogs && $preview['summary']['log_rows'] > 0) {
        $preview['warnings'][] = 'Audit and maintenance logs will be kept because Clear logs is turned off.';
    }

    if ($resetCounters) {
        $preview['warnings'][] = 'Auto-increment counters for cleared tables will restart from 1 after reset.';
    }

    return $preview;
}

function maintenance_execute_reset_operational_data(mysqli $conn, array $input): array
{
    $preview = maintenance_preview_reset_operational_data($conn, $input);
    if (!$preview['ok']) {
        throw new RuntimeException($preview['errors'][0] ?? 'Reset Operational Data preview is invalid.');
    }

    if (!$preview['can_execute']) {
        throw new RuntimeException($preview['warnings'][0] ?? 'No operational rows were found to reset.');
    }

    $includeLogs = (bool) ($preview['filters']['include_logs'] ?? false);
    $resetCounters = (bool) ($preview['filters']['reset_counters'] ?? false);
    $tablesToClear = maintenance_reset_operational_selected_tables($includeLogs);
    $preResetBackup = maintenance_execute_database_backup($conn, 'pre_reset_operational');

    $snapshotPath = maintenance_write_snapshot('reset_operational_data', array(
        'generated_at' => date('c'),
        'filters' => $preview['filters'],
        'summary' => $preview['summary'],
        'selected_counts' => $preview['selected_counts'],
        'log_counts' => $preview['log_counts'],
        'branch_summary' => $preview['branch_summary'],
        'pre_reset_backup' => $preResetBackup,
    ));

    $deletedCounts = array();

    $conn->begin_transaction();

    try {
        foreach ($tablesToClear as $tableName) {
            $deletedCounts[$tableName] = maintenance_delete_all_rows($conn, $tableName);
        }

        if ($resetCounters) {
            foreach ($tablesToClear as $tableName) {
                maintenance_reset_table_auto_increment($conn, $tableName);
            }
        }

        $result = array(
            'deleted_counts' => $deletedCounts,
            'filters' => $preview['filters'],
            'pre_reset_backup' => $preResetBackup,
            'reset_counters' => $resetCounters,
        );

        maintenance_log_action($conn, array(
            'maintenance_type' => 'reset_operational_data',
            'status' => 'completed',
            'snapshot_path' => $snapshotPath,
            'filters' => $preview['filters'],
            'preview' => array(
                'summary' => $preview['summary'],
                'warnings' => $preview['warnings'],
            ),
            'result' => $result,
            'note' => 'Operational data was fully reset while master configuration data was preserved.',
        ));

        $conn->commit();

        return array(
            'snapshot_path' => $snapshotPath,
            'pre_reset_backup' => $preResetBackup,
            'deleted_counts' => $deletedCounts,
            'filters' => $preview['filters'],
        );
    } catch (Throwable $exception) {
        $conn->rollback();
        throw $exception;
    }
}

function maintenance_quick_service_guest_cleanup_rows(mysqli $conn): array
{
    $sql = "
        SELECT
            car.car_id,
            cust.customer_id,
            cust.name AS customer_name,
            car.plate_number,
            car.province,
            car.vehicle_type,
            car.brand,
            car.model,
            GROUP_CONCAT(
                DISTINCT CASE
                    WHEN COALESCE(o.branch_name, '') <> '' THEN o.branch_name
                    ELSE NULL
                END
                ORDER BY o.branch_name
                SEPARATOR ', '
            ) AS quick_service_branches,
            MAX(CASE WHEN car.vehicle_type = 'Quick Service' THEN 1 ELSE 0 END) AS cleanup_vehicle_type,
            MAX(CASE WHEN car.brand = 'Quick Service' THEN 1 ELSE 0 END) AS cleanup_brand,
            MAX(
                CASE
                    WHEN car.model IN ('Guest', 'Walk-in') THEN 1
                    WHEN COALESCE(o.branch_name, '') <> '' AND car.model = o.branch_name THEN 1
                    ELSE 0
                END
            ) AS cleanup_model
        FROM cars car
        INNER JOIN customers cust ON cust.customer_id = car.customer_id
        INNER JOIN orders o
            ON o.car_id = car.car_id
            AND o.order_source = 'quick_service'
        WHERE cust.customer_group = 'guest'
        GROUP BY
            car.car_id,
            cust.customer_id,
            cust.name,
            car.plate_number,
            car.province,
            car.vehicle_type,
            car.brand,
            car.model
        HAVING
            cleanup_vehicle_type = 1
            OR cleanup_brand = 1
            OR cleanup_model = 1
        ORDER BY cust.customer_id ASC, car.car_id ASC
    ";

    $result = $conn->query($sql);
    if (!$result instanceof mysqli_result) {
        throw new RuntimeException('Quick Service guest cleanup preview could not be loaded.');
    }

    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = array(
            'car_id' => (int) ($row['car_id'] ?? 0),
            'customer_id' => (int) ($row['customer_id'] ?? 0),
            'customer_name' => (string) ($row['customer_name'] ?? ''),
            'plate_number' => (string) ($row['plate_number'] ?? ''),
            'province' => (string) ($row['province'] ?? ''),
            'vehicle_type' => (string) ($row['vehicle_type'] ?? ''),
            'brand' => (string) ($row['brand'] ?? ''),
            'model' => (string) ($row['model'] ?? ''),
            'quick_service_branches' => (string) ($row['quick_service_branches'] ?? ''),
            'cleanup_vehicle_type' => (int) ($row['cleanup_vehicle_type'] ?? 0) === 1,
            'cleanup_brand' => (int) ($row['cleanup_brand'] ?? 0) === 1,
            'cleanup_model' => (int) ($row['cleanup_model'] ?? 0) === 1,
        );
    }
    $result->close();

    return $rows;
}

function maintenance_preview_cleanup_quick_service_guest(mysqli $conn): array
{
    $rows = maintenance_quick_service_guest_cleanup_rows($conn);
    $customerIds = array();
    $vehicleTypeRows = 0;
    $brandRows = 0;
    $modelRows = 0;

    foreach ($rows as $row) {
        $customerIds[] = (int) ($row['customer_id'] ?? 0);
        if (!empty($row['cleanup_vehicle_type'])) {
            $vehicleTypeRows++;
        }
        if (!empty($row['cleanup_brand'])) {
            $brandRows++;
        }
        if (!empty($row['cleanup_model'])) {
            $modelRows++;
        }
    }

    $preview = array(
        'ok' => true,
        'can_execute' => !empty($rows),
        'errors' => array(),
        'warnings' => array(),
        'filters' => array(),
        'summary' => array(
            'candidate_rows' => count($rows),
            'customer_count' => count(array_unique(array_filter($customerIds))),
            'vehicle_type_rows' => $vehicleTypeRows,
            'brand_rows' => $brandRows,
            'model_rows' => $modelRows,
        ),
        'rows' => $rows,
    );

    if (empty($rows)) {
        $preview['warnings'][] = 'No old Quick Service guest vehicle defaults were found to clean.';
    }

    return $preview;
}

function maintenance_execute_cleanup_quick_service_guest(mysqli $conn): array
{
    $preview = maintenance_preview_cleanup_quick_service_guest($conn);
    if (!$preview['ok']) {
        throw new RuntimeException($preview['errors'][0] ?? 'Quick Service guest cleanup preview is invalid.');
    }

    if (!$preview['can_execute']) {
        throw new RuntimeException($preview['warnings'][0] ?? 'No old Quick Service guest vehicle defaults were found to clean.');
    }

    $preCleanupBackup = maintenance_execute_database_backup($conn, 'pre_cleanup_qs_guest_vehicle');
    $snapshotPath = maintenance_write_snapshot('cleanup_quick_service_guest', array(
        'generated_at' => date('c'),
        'summary' => $preview['summary'],
        'rows' => $preview['rows'],
        'pre_cleanup_backup' => $preCleanupBackup,
    ));

    $updatedRows = 0;
    $updatedCarIds = array();
    $conn->begin_transaction();

    try {
        $updateStmt = db_prepare($conn, "
            UPDATE cars
            SET vehicle_type = ?, brand = ?, model = ?
            WHERE car_id = ?
        ");

        foreach ($preview['rows'] as $row) {
            $carId = (int) ($row['car_id'] ?? 0);
            if ($carId <= 0) {
                continue;
            }

            $newVehicleType = !empty($row['cleanup_vehicle_type']) ? '' : (string) ($row['vehicle_type'] ?? '');
            $newBrand = !empty($row['cleanup_brand']) ? '' : (string) ($row['brand'] ?? '');
            $newModel = !empty($row['cleanup_model']) ? '' : (string) ($row['model'] ?? '');

            $updateStmt->bind_param('sssi', $newVehicleType, $newBrand, $newModel, $carId);
            $updateStmt->execute();

            if ($updateStmt->affected_rows > 0) {
                $updatedRows += $updateStmt->affected_rows;
                $updatedCarIds[] = $carId;
            }
        }

        $updateStmt->close();

        $result = array(
            'updated_rows' => $updatedRows,
            'updated_car_ids' => array_values(array_unique($updatedCarIds)),
            'pre_cleanup_backup' => $preCleanupBackup,
        );

        maintenance_log_action($conn, array(
            'maintenance_type' => 'cleanup_quick_service_guest',
            'status' => 'completed',
            'snapshot_path' => $snapshotPath,
            'filters' => array(),
            'preview' => array(
                'summary' => $preview['summary'],
                'warnings' => $preview['warnings'],
            ),
            'result' => $result,
            'note' => 'Old Quick Service guest default values were cleared from vehicle type, brand and model.',
        ));

        $conn->commit();

        return array(
            'snapshot_path' => $snapshotPath,
            'pre_cleanup_backup' => $preCleanupBackup,
            'updated_rows' => $updatedRows,
            'updated_car_ids' => array_values(array_unique($updatedCarIds)),
        );
    } catch (Throwable $exception) {
        $conn->rollback();
        throw $exception;
    }
}
