<?php

declare(strict_types=1);

namespace ShieldX\Scanner;

defined('ABSPATH') || exit;

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.SlowDBQuery.slow_db_query_meta_key,WordPress.DB.SlowDBQuery.slow_db_query_meta_value,WordPress.WP.AlternativeFunctions.file_system_operations_mkdir,WordPress.WP.I18n.NonSingularStringLiteralText -- Database remediation operates on plugin-owned dynamic backup SQL and storage folders after capability and nonce checks.

use ShieldX\Storage\FileStorage;

final class DatabaseRemediation
{
    private const MAX_UNSERIALIZE_BYTES = 1048576;

    private const AUTOLOAD_DISABLE_EXACT_ALLOWLIST = [
        'rewrite_rules',
        'dashboard_widget_options',
        'recently_activated',
        'uninstall_plugins',
    ];

    private const AUTOLOAD_DISABLE_PREFIX_ALLOWLIST = [
        '_transient_',
        '_site_transient_',
        'transient_',
        'site_transient_',
        'rss_',
        'cache_',
        'cached_',
    ];

    private const AUTOLOAD_DISABLE_CONTAINS_ALLOWLIST = [
        '_cache',
        'cache_',
        '_cached_',
    ];

    /** @var object|null */
    private $wpdb;

    /** @var ScanRepository */
    private $repository;

    /** @var FileStorage */
    private $storage;

    public function __construct($wpdb, ScanRepository $repository, FileStorage $storage)
    {
        $this->wpdb = $wpdb;
        $this->repository = $repository;
        $this->storage = $storage;
    }

    public static function fromWordPress(): self
    {
        global $wpdb;

        return new self($wpdb, ScanRepository::fromWordPress(), FileStorage::fromWordPress());
    }

    /**
     * @return array<string,mixed>
     */
    public function previewIssue(string $threatId): array
    {
        $issue = $this->repository->issueByThreatId($threatId);
        if (!$issue) {
            return ['ok' => false, 'error' => $this->text('Database issue was not found.')];
        }

        $issueStatus = (string) ($issue['status'] ?? 'active');
        $target = $this->targetFromIssue($issue);
        if (!$target) {
            return ['ok' => false, 'error' => $this->text('This scanner issue is not a database finding.')];
        }

        $row = $this->readTarget($target);
        if (!$row) {
            $readError = $this->lastDatabaseError();
            if ($readError !== '') {
                return [
                    'ok' => false,
                    'error' => $this->text('SHIELD-X could not read the database row for review. The finding was left open.'),
                    'issue' => $issue,
                    'target' => $target,
                ];
            }

            $reviewIssue = $issue;
            if ($issueStatus === 'active') {
                $reviewIssue['status'] = 'resolved';
            }

            return [
                'ok' => true,
                'issue' => $reviewIssue,
                'target' => $target,
                'row' => $this->missingTargetReviewRow($issue, $target),
                'actions' => [],
                'resolved' => true,
                'stale' => true,
                'notice' => $this->text('The database row is no longer present. SHIELD-X kept the last finding evidence below; run a recheck to close this finding after the exact row is inspected.'),
                'history' => $this->backupsForPath((string) ($issue['affected_path'] ?? ''), 5),
            ];
        }

        return [
            'ok' => true,
            'issue' => $issue,
            'target' => $target,
            'row' => $this->redactedRow($row),
            'actions' => $issueStatus === 'active' ? $this->availableActions($issue, $target) : [],
            'resolved' => $issueStatus !== 'active',
            'history' => $this->backupsForPath((string) ($issue['affected_path'] ?? ''), 5),
        ];
    }

    /**
     * @param array<string,mixed> $issue
     * @param array<string,string> $target
     * @return array<string,mixed>
     */
    private function missingTargetReviewRow(array $issue, array $target): array
    {
        return [
            'state' => $this->text('Database row no longer exists'),
            'target' => (string) ($target['path'] ?? ''),
            'kind' => (string) ($target['kind'] ?? ''),
            'id' => (string) ($target['id'] ?? ''),
            'last_recorded_rule' => (string) ($issue['rule_id'] ?? ''),
            'last_recorded_description' => (string) ($issue['description'] ?? ''),
            'last_detected' => (string) ($issue['last_detected'] ?? ''),
        ];
    }

    /**
     * @return array{ok:bool,message:string,path:string}
     */
    public function remediate(string $threatId, string $action, int $userId = 0, string $nonceAction = 'shieldx_remediate_db_issue', string $nonce = ''): array
    {
        $guardError = $this->adminWriteGuard($nonceAction, $nonce);
        if ($guardError !== '') {
            return $this->result(false, $guardError);
        }

        $issue = $this->repository->issueByThreatId($threatId);
        if (!$issue) {
            return $this->result(false, $this->text('Database issue was not found.'));
        }

        if ((string) ($issue['status'] ?? 'active') !== 'active') {
            return $this->result(false, $this->text('This database issue is already resolved.'), (string) ($issue['affected_path'] ?? ''));
        }

        $target = $this->targetFromIssue($issue);
        if (!$target) {
            return $this->result(false, $this->text('This scanner issue is not a database finding.'));
        }

        $action = $this->normalizeAction($action);
        $allowed = array_column($this->availableActions($issue, $target), 'id');
        if (!in_array($action, $allowed, true)) {
            return $this->result(false, $this->text('This action is not available for that database finding.'), (string) ($issue['affected_path'] ?? ''));
        }

        $row = $this->readTarget($target);
        if (!$row) {
            return $this->result(false, $this->text('Database row was not found.'), (string) ($issue['affected_path'] ?? ''));
        }

        $transactionSafetyError = $this->transactionSafetyError($target, $action);
        if ($transactionSafetyError !== '') {
            return $this->result(false, $transactionSafetyError, (string) ($issue['affected_path'] ?? ''));
        }

        $backup = [];
        $requiresLockedTargetRead = $action === 'delete_cron_hook';
        if (!$requiresLockedTargetRead) {
            $backup = $this->backup($issue, $target, $row, $action, $userId);
            if (empty($backup['backup_id'])) {
                return $this->result(false, $this->text('SHIELD-X could not create a secure remediation backup.'), (string) ($issue['affected_path'] ?? ''));
            }
        }

        $ok = false;

        $transactionStarted = $this->beginTransaction();
        if (!$transactionStarted) {
            return $this->result(false, $this->text('SHIELD-X could not start a database transaction for remediation.'), (string) ($issue['affected_path'] ?? ''));
        }

        try {
            if ($requiresLockedTargetRead) {
                $row = $this->readTargetForUpdate($target);
                if (!$row) {
                    $this->rollbackTransaction($transactionStarted);

                    return $this->result(false, $this->text('Database row was not found.'), (string) ($issue['affected_path'] ?? ''));
                }

                $backup = $this->backup($issue, $target, $row, $action, $userId);
                if (empty($backup['backup_id'])) {
                    $this->rollbackTransaction($transactionStarted);

                    return $this->result(false, $this->text('SHIELD-X could not create a secure remediation backup.'), (string) ($issue['affected_path'] ?? ''));
                }
            }

            if ($action === 'disable_autoload') {
                $ok = $this->disableAutoload($target);
            } elseif ($action === 'sanitize_option') {
                $ok = $this->sanitizeOption($target, $row);
            } elseif ($action === 'delete_cron_hook') {
                $ok = $this->deleteCronHook($target, $row);
            } elseif ($action === 'sanitize_post') {
                $ok = $this->sanitizePost($target, $row);
            } elseif ($action === 'sanitize_postmeta') {
                $ok = $this->sanitizePostMeta($target, $row);
            } elseif ($action === 'sanitize_comment') {
                $ok = $this->sanitizeComment($target, $row);
            } elseif ($action === 'flag_admin') {
                $ok = $this->flagAdminUser($target, $issue, $userId);
            }

            if (!$ok) {
                $this->rollbackTransaction($transactionStarted);

                return $this->result(false, $this->text('SHIELD-X could not update that database row.'), (string) ($issue['affected_path'] ?? ''));
            }

            $this->resolveRemediatedIssues($issue, $target, $action, $userId);
            $this->commitTransaction($transactionStarted);
        } catch (\Throwable $error) {
            $this->rollbackTransaction($transactionStarted);

            return $this->result(false, $this->text('SHIELD-X could not update that database row.'), (string) ($issue['affected_path'] ?? ''));
        }

        $recheck = $this->runRecheckForPaths($this->recheckPathsForTarget($target));

        return $this->result(true, sprintf($this->text('Database remediation completed. Backup: %s'), (string) ($backup['backup_id'] ?? '')), (string) ($issue['affected_path'] ?? ''), [
            'backup_id' => (string) ($backup['backup_id'] ?? ''),
            'recheck' => $recheck,
        ]);
    }

    private function beginTransaction(): bool
    {
        if (!method_exists($this->wpdb, 'query')) {
            return false;
        }

        return $this->wpdb->query('START TRANSACTION') !== false;
    }

    private function commitTransaction(bool $started): void
    {
        if ($started && method_exists($this->wpdb, 'query')) {
            $this->wpdb->query('COMMIT');
        }
    }

    private function rollbackTransaction(bool $started): void
    {
        if ($started && method_exists($this->wpdb, 'query')) {
            $this->wpdb->query('ROLLBACK');
        }
    }

    /**
     * @param array<string,string> $target
     */
    private function transactionSafetyError(array $target, string $action): string
    {
        foreach ($this->transactionTargetTables($target, $action) as $table) {
            $engine = $this->tableStorageEngine($table);
            if ($engine === '') {
                return $this->text('SHIELD-X could not verify that the target database table supports rollback.');
            }

            if (!in_array(strtolower($engine), ['innodb', 'bdb'], true)) {
                return $this->text('SHIELD-X refused database remediation because the target table is not using a transactional storage engine.');
            }
        }

        return '';
    }

    /**
     * @param array<string,string> $target
     * @return array<int,string>
     */
    private function transactionTargetTables(array $target, string $action): array
    {
        unset($action);

        if ($target['kind'] === 'options' || $target['kind'] === 'cron') {
            return [(string) $this->wpdb->options];
        }

        if ($target['kind'] === 'posts') {
            return [(string) $this->wpdb->posts];
        }

        if ($target['kind'] === 'postmeta' && isset($this->wpdb->postmeta)) {
            return [(string) $this->wpdb->postmeta];
        }

        if ($target['kind'] === 'comments' && isset($this->wpdb->comments)) {
            return [(string) $this->wpdb->comments];
        }

        if ($target['kind'] === 'users' && isset($this->wpdb->usermeta)) {
            return [(string) $this->wpdb->usermeta];
        }

        return [];
    }

    private function tableStorageEngine(string $table): string
    {
        if ($table === '' || !method_exists($this->wpdb, 'prepare') || !method_exists($this->wpdb, 'get_row')) {
            return '';
        }

        $row = $this->wpdb->get_row(
            $this->wpdb->prepare('SHOW TABLE STATUS WHERE Name = %s', $table),
            defined('ARRAY_A') ? constant('ARRAY_A') : 'ARRAY_A'
        );
        if (!is_array($row)) {
            return '';
        }

        $engine = $row['Engine'] ?? ($row['engine'] ?? '');

        return is_scalar($engine) ? (string) $engine : '';
    }

    /**
     * @return array<string,mixed>
     */
    public function recheckIssue(string $threatId, string $nonceAction = 'shieldx_recheck_db_issue', string $nonce = ''): array
    {
        $guardError = $this->adminWriteGuard($nonceAction, $nonce);
        if ($guardError !== '') {
            return $this->result(false, $guardError);
        }

        $issue = $this->repository->issueByThreatId($threatId);
        if (!$issue) {
            return $this->result(false, $this->text('Database issue was not found.'));
        }

        $target = $this->targetFromIssue($issue);
        if (!$target) {
            return $this->result(false, $this->text('This scanner issue is not a database finding.'));
        }

        $recheck = $this->runRecheckForPaths($this->recheckPathsForTarget($target));

        return $this->result(true, $this->recheckMessage($recheck), (string) ($issue['affected_path'] ?? ''), [
            'recheck' => $recheck,
            'threat_id' => $threatId,
        ]);
    }

    /**
     * @return array{ok:bool,message:string,path:string}
     */
    public function restoreBackup(string $backupId, int $userId = 0, string $nonceAction = 'shieldx_restore_db_backup', string $nonce = ''): array
    {
        $guardError = $this->adminWriteGuard($nonceAction, $nonce);
        if ($guardError !== '') {
            return $this->result(false, $guardError);
        }

        $backup = $this->backupById($backupId);
        if (!$backup) {
            return $this->result(false, $this->text('Database backup was not found or failed integrity verification.'));
        }

        $target = isset($backup['target']) && is_array($backup['target']) ? $backup['target'] : null;
        $row = isset($backup['row']) && is_array($backup['row']) ? $backup['row'] : null;
        if (!$target || !$row) {
            return $this->result(false, $this->text('Database backup is incomplete.'));
        }

        $ok = $this->restoreTarget($target, $row);
        if (!$ok) {
            return $this->result(false, $this->text('SHIELD-X could not restore that database backup.'));
        }

        $backup['restored_at'] = gmdate('c');
        $backup['restored_by'] = $userId;
        $this->writeBackup($backupId, $backup);

        return $this->result(true, $this->text('Database backup restored.'), (string) ($backup['path'] ?? ''));
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    public function recentBackups(int $limit = 8): array
    {
        $directory = $this->backupDirectory();
        if (!is_dir($directory)) {
            return [];
        }

        $files = glob($directory . '/*.json');
        if (!is_array($files)) {
            return [];
        }

        $entries = [];
        foreach ($files as $file) {
            $backupId = $this->repairBackupIdFromFilename($file);
            if ($backupId === '') {
                continue;
            }

            $stat = $this->repairBackupFileStat($file);
            if (!is_array($stat)) {
                continue;
            }

            $entries[] = [
                'backup_id' => $backupId,
                'path' => $file,
                'mtime' => max(0, (int) ($stat['mtime'] ?? 0)),
                'size' => max(0, (int) ($stat['size'] ?? 0)),
            ];
        }

        usort($entries, static function (array $left, array $right): int {
            $byModified = ((int) ($right['mtime'] ?? 0)) <=> ((int) ($left['mtime'] ?? 0));

            return $byModified !== 0
                ? $byModified
                : strcmp((string) ($right['backup_id'] ?? ''), (string) ($left['backup_id'] ?? ''));
        });

        $items = [];
        foreach (array_slice($entries, 0, max(1, min(50, $limit))) as $entry) {
            $backupId = (string) ($entry['backup_id'] ?? '');
            $metadata = $this->readRepairBackupMetadata($backupId);
            if (is_array($metadata) && $this->repairBackupMetadataIsComplete($metadata, $backupId)) {
                $metadata['backup_id'] = $backupId;
                $metadata['metadata_complete'] = true;
                $metadata['size'] = max(0, (int) ($entry['size'] ?? 0));
                $items[] = $metadata;
                continue;
            }

        }

        return $items;
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    private function backupsForPath(string $path, int $limit): array
    {
        if ($path === '') {
            return [];
        }

        $items = [];
        foreach ($this->recentBackups(50) as $backup) {
            if ((string) ($backup['path'] ?? '') !== $path) {
                continue;
            }

            $items[] = $backup;
            if (count($items) >= $limit) {
                break;
            }
        }

        return $items;
    }

    /**
     * @param array<string,mixed> $issue
     * @return array<string,string>|null
     */
    private function targetFromIssue(array $issue): ?array
    {
        $path = (string) ($issue['affected_path'] ?? '');
        if (strpos($path, 'db:') !== 0) {
            return null;
        }

        $parts = explode(':', $path, 3);
        if (count($parts) !== 3 || $parts[1] === '' || $parts[2] === '') {
            return null;
        }

        return [
            'kind' => $parts[1],
            'id' => $parts[2],
            'path' => $path,
        ];
    }

    /**
     * @param array<string,string> $target
     * @return array<string,mixed>|null
     */
    private function readTarget(array $target): ?array
    {
        if (!$this->available()) {
            return null;
        }

        if ($target['kind'] === 'options' || $target['kind'] === 'cron') {
            $optionName = $target['kind'] === 'cron' ? 'cron' : $target['id'];
            return $this->row($this->wpdb->prepare(
                "SELECT option_name, option_value, autoload FROM {$this->wpdb->options} WHERE option_name = %s LIMIT 1",
                $optionName
            ));
        }

        if ($target['kind'] === 'posts') {
            return $this->row($this->wpdb->prepare(
                "SELECT ID, post_type, post_title, post_content, post_excerpt, post_status FROM {$this->wpdb->posts} WHERE ID = %d LIMIT 1",
                (int) $target['id']
            ));
        }

        if ($target['kind'] === 'postmeta' && isset($this->wpdb->postmeta)) {
            return $this->row($this->wpdb->prepare(
                "SELECT meta_id, post_id, meta_key, meta_value FROM {$this->wpdb->postmeta} WHERE meta_id = %d LIMIT 1",
                (int) $target['id']
            ));
        }

        if ($target['kind'] === 'comments' && isset($this->wpdb->comments)) {
            return $this->row($this->wpdb->prepare(
                "SELECT comment_ID, comment_post_ID, comment_author, comment_author_url, comment_content, comment_approved FROM {$this->wpdb->comments} WHERE comment_ID = %d LIMIT 1",
                (int) $target['id']
            ));
        }

        if ($target['kind'] === 'users') {
            return $this->row($this->wpdb->prepare(
                "SELECT ID, user_login, user_email, user_registered FROM {$this->wpdb->users} WHERE ID = %d LIMIT 1",
                (int) $target['id']
            ));
        }

        return null;
    }

    /**
     * @param array<string,string> $target
     * @return array<string,mixed>|null
     */
    private function readTargetForUpdate(array $target): ?array
    {
        if (!$this->available()) {
            return null;
        }

        if ($target['kind'] === 'options' || $target['kind'] === 'cron') {
            $optionName = $target['kind'] === 'cron' ? 'cron' : $target['id'];

            return $this->row($this->wpdb->prepare(
                "SELECT option_name, option_value, autoload FROM {$this->wpdb->options} WHERE option_name = %s LIMIT 1 FOR UPDATE",
                $optionName
            ));
        }

        return $this->readTarget($target);
    }

    /**
     * @param array<string,mixed> $issue
     * @param array<string,string> $target
     * @return array<int,array{id:string,label:string,tone:string}>
     */
    private function availableActions(array $issue, array $target): array
    {
        $ruleId = (string) ($issue['rule_id'] ?? '');

        if ($target['kind'] === 'cron') {
            return [['id' => 'delete_cron_hook', 'label' => $this->text('Delete cron hook'), 'tone' => 'danger']];
        }

        if ($target['kind'] === 'posts') {
            return [['id' => 'sanitize_post', 'label' => $this->text('Clean post content'), 'tone' => 'danger']];
        }

        if ($target['kind'] === 'postmeta') {
            return [['id' => 'sanitize_postmeta', 'label' => $this->text('Clean post metadata'), 'tone' => 'danger']];
        }

        if ($target['kind'] === 'comments') {
            return [['id' => 'sanitize_comment', 'label' => $this->text('Clean comment content'), 'tone' => 'danger']];
        }

        if ($target['kind'] === 'users') {
            return [['id' => 'flag_admin', 'label' => $this->text('Flag admin for review'), 'tone' => 'warning']];
        }

        if ($target['kind'] === 'options') {
            if ($ruleId === 'SX-DB-OPTION-001') {
                return $this->autoloadDisableAllowed((string) ($target['id'] ?? ''))
                    ? [['id' => 'disable_autoload', 'label' => $this->text('Disable autoload'), 'tone' => 'warning']]
                    : [];
            }

            if ($target['id'] === 'cron') {
                // A cron-option finding does not identify a single hook; only db:cron:<hook> may be remediated.
                return [];
            }

            if (in_array($target['id'], ['home', 'siteurl'], true)) {
                return [];
            }

            $actions = [['id' => 'sanitize_option', 'label' => $this->text('Clean option value'), 'tone' => 'danger']];
            if ($this->autoloadDisableAllowed((string) ($target['id'] ?? ''))) {
                $actions[] = ['id' => 'disable_autoload', 'label' => $this->text('Disable autoload'), 'tone' => 'warning'];
            }

            return $actions;
        }

        return [];
    }

    /**
     * @param array<string,string> $target
     */
    private function disableAutoload(array $target): bool
    {
        if ($target['kind'] !== 'options') {
            return false;
        }

        if (!$this->autoloadDisableAllowed((string) ($target['id'] ?? ''))) {
            return false;
        }

        return $this->wpdb->update($this->wpdb->options, ['autoload' => 'no'], ['option_name' => $target['id']]) !== false;
    }

    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $row
     */
    private function sanitizeOption(array $target, array $row): bool
    {
        if ($target['kind'] !== 'options') {
            return false;
        }

        $value = (string) ($row['option_value'] ?? '');
        if ($this->containsSerializedObject($value)) {
            return false;
        }

        $decoded = $this->maybeUnserialize($value);
        if ($this->containsObjectValue($decoded)) {
            return false;
        }

        $clean = $this->sanitizeStoredValue($decoded);
        $encoded = is_string($decoded) ? (string) $clean : $this->maybeSerialize($clean);

        return $this->wpdb->update($this->wpdb->options, ['option_value' => $encoded], ['option_name' => $target['id']]) !== false;
    }

    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $row
     */
    private function deleteCronHook(array $target, array $row): bool
    {
        $cron = $this->maybeUnserialize((string) ($row['option_value'] ?? ''));
        if (!is_array($cron)) {
            return false;
        }

        [$hookName, $eventSignature] = $this->cronHookTargetParts((string) ($target['id'] ?? ''));
        if ($hookName === '') {
            return false;
        }

        $removed = false;
        foreach ($cron as $timestamp => $hooks) {
            if (!is_array($hooks)) {
                continue;
            }

            if (!array_key_exists($hookName, $hooks)) {
                continue;
            }

            if ($eventSignature !== '' && is_array($hooks[$hookName] ?? null)) {
                if (array_key_exists($eventSignature, $hooks[$hookName])) {
                    unset($hooks[$hookName][$eventSignature]);
                    $removed = true;
                }

                if (empty($hooks[$hookName])) {
                    unset($hooks[$hookName]);
                }
            } elseif ($eventSignature === '') {
                unset($hooks[$hookName]);
                $removed = true;
            }

            if ($removed) {
                if (empty($hooks)) {
                    unset($cron[$timestamp]);
                } else {
                    $cron[$timestamp] = $hooks;
                }
            }
        }

        return $removed && $this->wpdb->update($this->wpdb->options, ['option_value' => $this->maybeSerialize($cron)], ['option_name' => 'cron']) !== false;
    }

    /**
     * @return array{0:string,1:string}
     */
    private function cronHookTargetParts(string $targetId): array
    {
        if (preg_match('/^(.+):([a-f0-9]{32})$/i', $targetId, $matches) === 1) {
            return [(string) $matches[1], strtolower((string) $matches[2])];
        }

        return [$targetId, ''];
    }

    /**
     * @param array<string,mixed> $row
     * @return array<int,string>
     */
    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $row
     */
    private function sanitizePost(array $target, array $row): bool
    {
        if ($target['kind'] !== 'posts') {
            return false;
        }

        return $this->wpdb->update($this->wpdb->posts, [
            'post_content' => $this->sanitizeString((string) ($row['post_content'] ?? '')),
            'post_excerpt' => $this->sanitizeString((string) ($row['post_excerpt'] ?? '')),
        ], ['ID' => (int) $target['id']]) !== false;
    }

    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $row
     */
    private function sanitizePostMeta(array $target, array $row): bool
    {
        if ($target['kind'] !== 'postmeta' || !isset($this->wpdb->postmeta)) {
            return false;
        }

        $value = (string) ($row['meta_value'] ?? '');
        if ($this->containsSerializedObject($value)) {
            return false;
        }

        $decoded = $this->maybeUnserialize($value);
        if ($this->containsObjectValue($decoded)) {
            return false;
        }

        $clean = $this->sanitizeStoredValue($decoded);
        $encoded = is_string($decoded) ? (string) $clean : $this->maybeSerialize($clean);

        return $this->wpdb->update($this->wpdb->postmeta, ['meta_value' => $encoded], ['meta_id' => (int) $target['id']]) !== false;
    }

    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $row
     */
    private function sanitizeComment(array $target, array $row): bool
    {
        if ($target['kind'] !== 'comments' || !isset($this->wpdb->comments)) {
            return false;
        }

        return $this->wpdb->update($this->wpdb->comments, [
            'comment_content' => $this->sanitizeString((string) ($row['comment_content'] ?? '')),
            'comment_author_url' => $this->sanitizeString((string) ($row['comment_author_url'] ?? '')),
        ], ['comment_ID' => (int) $target['id']]) !== false;
    }

    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $issue
     */
    private function flagAdminUser(array $target, array $issue, int $userId): bool
    {
        $payload = [
            'flagged_at' => gmdate('c'),
            'flagged_by' => $userId,
            'threat_id' => (string) ($issue['threat_id'] ?? ''),
            'rule_id' => (string) ($issue['rule_id'] ?? ''),
            'reason' => (string) ($issue['description'] ?? ''),
        ];

        if (function_exists('update_user_meta')) {
            return update_user_meta((int) $target['id'], 'shieldx_admin_review_flag', $payload) !== false;
        }

        if (!isset($this->wpdb->usermeta)) {
            return false;
        }

        return $this->wpdb->insert($this->wpdb->usermeta, [
            'user_id' => (int) $target['id'],
            'meta_key' => 'shieldx_admin_review_flag',
            'meta_value' => $this->maybeSerialize($payload),
        ]) !== false;
    }

    /**
     * @param array<string,mixed>  $issue
     * @param array<string,string> $target
     */
    private function resolveRemediatedIssues(array $issue, array $target, string $action, int $userId): void
    {
        $path = (string) ($issue['affected_path'] ?? '');
        if ($path === '') {
            return;
        }

        if ($action === 'sanitize_option') {
            $this->repository->resolveScannerIssuesForPathTypes($path ? [$path] : [], [
                'database_code_injection',
                'database_injected_script',
                'database_widget_script',
                'database_redirect',
            ]);
            return;
        }

        if ($action === 'sanitize_post') {
            $this->repository->resolveScannerIssuesForPathTypes([$path], [
                'database_post_script',
                'database_post_redirect',
            ]);
            return;
        }

        if ($action === 'sanitize_postmeta') {
            $this->repository->resolveScannerIssuesForPathTypes([$path], [
                'database_postmeta_code',
                'database_postmeta_script',
                'database_postmeta_redirect',
            ]);
            return;
        }

        if ($action === 'sanitize_comment') {
            $this->repository->resolveScannerIssuesForPathTypes([$path], [
                'database_comment_script',
                'database_comment_redirect',
            ]);
            return;
        }

        if ($action === 'delete_cron_hook') {
            $this->repository->resolveScannerIssuesForPathTypes([$path], ['database_cron_hook']);
            return;
        }

        if ($action === 'disable_autoload') {
            $this->repository->resolveScannerIssuesForPathTypes([$path], ['database_autoload_option']);
            return;
        }

        if ($action === 'flag_admin') {
            $this->repository->resolveScannerIssuesForPathTypes([$path], [
                'database_unknown_admin',
                'database_suspicious_admin',
            ]);
            return;
        }

        $this->repository->markIssueStatus((string) ($issue['threat_id'] ?? ''), 'resolved', $userId);
    }

    /**
     * @param array<string,string> $target
     * @return array<int,string>
     */
    private function recheckPathsForTarget(array $target): array
    {
        $paths = [(string) ($target['path'] ?? '')];

        return array_values(array_unique(array_filter($paths)));
    }

    /**
     * @param array<int,string> $paths
     * @return array<string,mixed>
     */
    private function runRecheckForPaths(array $paths): array
    {
        $scanner = $this->databaseScanner();
        $result = $scanner->scanPaths($paths);
        $issues = isset($result['issues']) && is_array($result['issues']) ? $result['issues'] : [];
        $inspectedPaths = isset($result['inspected_paths']) && is_array($result['inspected_paths'])
            ? array_values(array_filter(array_map('strval', $result['inspected_paths'])))
            : $paths;
        $activeThreatIds = [];

        foreach ($issues as $issue) {
            if (!is_array($issue)) {
                continue;
            }

            $activeThreatIds[] = (string) ($issue['threat_id'] ?? '');
            $this->repository->upsertIssue($issue);
        }

        $this->repository->resolveScannerIssuesForInspectedPaths($activeThreatIds, $inspectedPaths);

        return [
            'status' => empty($activeThreatIds) ? 'clean' : 'remaining',
            'remaining_count' => count($activeThreatIds),
            'inspected_paths' => $inspectedPaths,
            'issues' => array_values(array_filter($issues, 'is_array')),
        ];
    }

    private function recheckMessage(array $recheck): string
    {
        $remaining = (int) ($recheck['remaining_count'] ?? 0);
        if ($remaining > 0) {
            return sprintf($this->text('Recheck completed. %d database finding still matches.'), $remaining);
        }

        return $this->text('Recheck completed. No remaining database payload was found.');
    }

    private function databaseScanner(): DatabaseScanner
    {
        $homeUrl = '';
        if (function_exists('home_url')) {
            try {
                $homeUrl = (string) home_url('/');
            } catch (\Throwable $error) {
                \ShieldX\Support\ErrorLog::write('SHIELD-X database remediation home_url failed: ' . $error->getMessage());
            }
        }

        return new DatabaseScanner($this->wpdb, null, $homeUrl);
    }

    private function isSuspiciousCronHook(string $hook): bool
    {
        return (bool) preg_match('~(?:^|[_-])(?:backdoor|shell|payload|inject|base64|eval|malware|cmd)(?:[_-]|$)~i', $hook);
    }

    /**
     * @param mixed $payload
     */
    private function cronPayloadIsSuspicious($payload): bool
    {
        $surface = $this->flattenText($payload);

        return (bool) preg_match('~\b(?:eval|assert|base64_decode|gzinflate|str_rot13|shell_exec|system|passthru|proc_open|popen|exec)\s*\(~i', $surface)
            || (bool) preg_match('~\b(?:wp_remote_get|wp_remote_post|file_get_contents|curl_exec)\s*\([^)]*https?://~i', $surface)
            || (bool) preg_match('~https?://[^"\']+\.(?:php|txt|dat)(?:\?|$)~i', $surface);
    }

    /**
     * @param mixed $value
     */
    private function flattenText($value): string
    {
        if (is_scalar($value) || $value === null) {
            return (string) $value;
        }

        if (is_array($value)) {
            $parts = [];
            foreach ($value as $key => $item) {
                $parts[] = (string) $key;
                $parts[] = $this->flattenText($item);
            }

            return implode("\n", $parts);
        }

        return '';
    }

    /**
     * @param array<string,mixed> $issue
     * @param array<string,string> $target
     * @param array<string,mixed> $row
     * @return array<string,mixed>
     */
    private function backup(array $issue, array $target, array $row, string $action, int $userId): array
    {
        $this->storage->ensure();
        try {
            $backupId = bin2hex(random_bytes(8));
        } catch (\Throwable $error) {
            return [];
        }

        $payload = [
            'backup_id' => $backupId,
            'created_at' => gmdate('c'),
            'created_by' => $userId,
            'action' => $action,
            'path' => (string) ($issue['affected_path'] ?? ''),
            'threat_id' => (string) ($issue['threat_id'] ?? ''),
            'rule_id' => (string) ($issue['rule_id'] ?? ''),
            'target' => $target,
            'row' => $row,
        ];

        return $this->writeBackup($backupId, $payload) ? $payload : [];
    }

    /**
     * @return array<string,mixed>|null
     */
    private function backupById(string $backupId): ?array
    {
        if (!preg_match('/^[a-f0-9]{16}$/i', $backupId)) {
            return null;
        }

        $path = $this->backupDirectory() . '/' . strtolower($backupId) . '.json';
        if (!is_file($path)) {
            return null;
        }

        $contents = file_get_contents($path);
        if (!is_string($contents)) {
            return null;
        }

        $decoded = \ShieldX\Support\Json::decodeArray($contents);

        if (!is_array($decoded) || !$this->repairBackupSignatureValid($decoded)) {
            return null;
        }

        return strtolower((string) ($decoded['backup_id'] ?? '')) === strtolower($backupId) ? $decoded : null;
    }

    /**
     * @param array<string,mixed> $payload
     */
    private function writeBackup(string $backupId, array $payload): bool
    {
        $payload['signature'] = [
            'alg' => 'hmac-sha256',
            'value' => $this->signRepairBackupPayload($payload),
        ];
        $json = wp_json_encode($payload, JSON_PRETTY_PRINT);
        if (!is_string($json)) {
            return false;
        }

        if ($this->writeBackupJson($this->backupDirectory() . '/' . strtolower($backupId) . '.json', $json . "\n")) {
            $this->writeRepairBackupMetadata($backupId, $payload);
            return true;
        }

        return false;
    }

    private function writeBackupJson(string $path, string $json): bool
    {
        $directory = dirname($path);
        $this->makeDirectory($directory);

        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_tempnam -- Database repair backups are private SHIELD-X storage; same-directory temp files make writes atomic.
        $temporary = tempnam($directory, 'shieldx-db-repair-');
        if (!is_string($temporary) || $temporary === '') {
            return false;
        }

        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents -- This writes private SHIELD-X repair-backup JSON, not a host-managed WordPress file.
        if (file_put_contents($temporary, $json, LOCK_EX) === false) {
            $this->removeTemporaryBackupFile($temporary);
            return false;
        }

        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_chmod -- Defense-in-depth for private repair-backup JSON alongside deny rules.
        chmod($temporary, 0600);

        // phpcs:ignore WordPress.WP.AlternativeFunctions.rename_rename -- Atomic same-directory replacement prevents partial repair-backup JSON reads.
        if (!rename($temporary, $path)) {
            $this->removeTemporaryBackupFile($temporary);
            return false;
        }

        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_chmod -- Keep final repair-backup JSON private where filesystem modes are honored.
        chmod($path, 0600);

        return true;
    }

    /**
     * @param array<string,mixed> $payload
     */
    private function repairBackupSignatureValid(array $payload): bool
    {
        $signature = isset($payload['signature']) && is_array($payload['signature']) ? $payload['signature'] : [];
        $algorithm = (string) ($signature['alg'] ?? '');
        $value = (string) ($signature['value'] ?? '');
        if ($algorithm !== 'hmac-sha256' || preg_match('/^[a-f0-9]{64}$/i', $value) !== 1) {
            return false;
        }

        return hash_equals($this->signRepairBackupPayload($payload), $value);
    }

    /**
     * @param array<string,mixed> $payload
     */
    private function signRepairBackupPayload(array $payload): string
    {
        unset($payload['signature']);
        $this->sortRepairBackupPayload($payload);
        $json = wp_json_encode($payload, JSON_UNESCAPED_SLASHES);

        return is_string($json) ? hash_hmac('sha256', $json, $this->repairBackupSignatureKey()) : '';
    }

    /**
     * @param array<string,mixed> $payload
     */
    private function sortRepairBackupPayload(array &$payload): void
    {
        ksort($payload);
        foreach ($payload as &$value) {
            if (is_array($value)) {
                $this->sortRepairBackupPayload($value);
            }
        }
        unset($value);
    }

    private function repairBackupSignatureKey(): string
    {
        $salt = function_exists('wp_salt') ? (string) wp_salt('auth') : '';

        return hash('sha256', $salt . '|shieldx-db-repair-backup|' . $this->storage->baseDir(), true);
    }

    /**
     * @param array<string,mixed> $payload
     */
    private function writeRepairBackupMetadata(string $backupId, array $payload): void
    {
        $backupId = strtolower($backupId);
        if (!preg_match('/^[a-f0-9]{16}$/', $backupId)) {
            return;
        }

        $metadata = [
            'backup_id' => $backupId,
            'created_at' => (string) ($payload['created_at'] ?? ''),
            'created_by' => (int) ($payload['created_by'] ?? 0),
            'action' => (string) ($payload['action'] ?? ''),
            'path' => (string) ($payload['path'] ?? ''),
            'threat_id' => (string) ($payload['threat_id'] ?? ''),
            'rule_id' => (string) ($payload['rule_id'] ?? ''),
            'metadata_complete' => true,
        ];
        if (isset($payload['restored_at'])) {
            $metadata['restored_at'] = (string) $payload['restored_at'];
        }
        if (isset($payload['restored_by'])) {
            $metadata['restored_by'] = (int) $payload['restored_by'];
        }

        $json = wp_json_encode($metadata, JSON_UNESCAPED_SLASHES);
        if (is_string($json)) {
            $this->writeBackupJson($this->repairBackupMetadataPath($backupId), $json . "\n");
        }
    }

    /**
     * @return array<string,mixed>|null
     */
    private function readRepairBackupMetadata(string $backupId): ?array
    {
        $path = $this->repairBackupMetadataPath($backupId);
        // A missing sidecar is normal for backups created before metadata listing support.
        $raw = @file_get_contents($path);
        $decoded = is_string($raw) ? \ShieldX\Support\Json::decodeArray($raw, 65536) : null;

        return is_array($decoded) ? $decoded : null;
    }

    private function repairBackupMetadataPath(string $backupId): string
    {
        return $this->backupDirectory() . '/' . strtolower($backupId) . '.meta.json';
    }

    private function repairBackupIdFromFilename(string $file): string
    {
        $filename = basename($file);
        if (!preg_match('/^([a-f0-9]{16})\.json$/i', $filename, $matches)) {
            return '';
        }

        return strtolower($matches[1]);
    }

    /**
     * @return array<string,int>|null
     */
    private function repairBackupFileStat(string $path): ?array
    {
        // A repair backup can be deleted between glob() and the listing pass.
        $stat = @stat($path);
        if (!is_array($stat) || !isset($stat['mode']) || (((int) $stat['mode'] & 0170000) !== 0100000)) {
            return null;
        }

        return $stat;
    }

    /**
     * @param array<string,mixed> $metadata
     */
    private function repairBackupMetadataIsComplete(array $metadata, string $backupId): bool
    {
        return strtolower((string) ($metadata['backup_id'] ?? '')) === $backupId
            && isset($metadata['created_at'], $metadata['action'], $metadata['path']);
    }

    private function removeTemporaryBackupFile(string $path): void
    {
        if (is_file($path)) {
            // phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink -- Remove failed private repair-backup temp file from SHIELD-X managed storage.
            unlink($path);
        }
    }

    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $row
     */
    private function restoreTarget(array $target, array $row): bool
    {
        if ($target['kind'] === 'options' || $target['kind'] === 'cron') {
            return $this->updateAndVerifyRestoredTarget($target, $row, $this->wpdb->options, [
                'option_value' => (string) ($row['option_value'] ?? ''),
                'autoload' => (string) ($row['autoload'] ?? 'no'),
            ], ['option_name' => (string) ($row['option_name'] ?? ($target['kind'] === 'cron' ? 'cron' : $target['id']))]) !== false;
        }

        if ($target['kind'] === 'posts') {
            return $this->updateAndVerifyRestoredTarget($target, $row, $this->wpdb->posts, [
                'post_content' => (string) ($row['post_content'] ?? ''),
                'post_excerpt' => (string) ($row['post_excerpt'] ?? ''),
            ], ['ID' => (int) ($row['ID'] ?? $target['id'])]) !== false;
        }

        if ($target['kind'] === 'postmeta' && isset($this->wpdb->postmeta)) {
            return $this->updateAndVerifyRestoredTarget($target, $row, $this->wpdb->postmeta, [
                'meta_value' => (string) ($row['meta_value'] ?? ''),
            ], ['meta_id' => (int) ($row['meta_id'] ?? $target['id'])]);
        }

        if ($target['kind'] === 'comments' && isset($this->wpdb->comments)) {
            return $this->updateAndVerifyRestoredTarget($target, $row, $this->wpdb->comments, [
                'comment_content' => (string) ($row['comment_content'] ?? ''),
                'comment_author_url' => (string) ($row['comment_author_url'] ?? ''),
            ], ['comment_ID' => (int) ($row['comment_ID'] ?? $target['id'])]);
        }

        if ($target['kind'] === 'users' && function_exists('delete_user_meta')) {
            try {
                delete_user_meta((int) $target['id'], 'shieldx_admin_review_flag');

                return true;
            } catch (\Throwable $error) {
                \ShieldX\Support\ErrorLog::write('SHIELD-X database remediation user-meta restore failed: ' . $error->getMessage());

                return false;
            }
        }

        return $target['kind'] === 'users';
    }

    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $expected
     * @param array<string,mixed> $data
     * @param array<string,mixed> $where
     */
    private function updateAndVerifyRestoredTarget(array $target, array $expected, string $table, array $data, array $where): bool
    {
        try {
            $result = $this->wpdb->update($table, $data, $where);
            if ($result === false) {
                return false;
            }

            if ($this->rowsAffectedFromUpdateResult($result) > 0) {
                return true;
            }

            $current = $this->readTarget($target);
        } catch (\Throwable $error) {
            \ShieldX\Support\ErrorLog::write('SHIELD-X database remediation restore update failed: ' . $error->getMessage());

            return false;
        }

        return is_array($current) && $this->restoredTargetMatches($target, $expected, $current);
    }

    /**
     * @param mixed $result
     */
    private function rowsAffectedFromUpdateResult($result): int
    {
        if (is_int($result)) {
            return max(0, $result);
        }

        if (is_object($this->wpdb) && property_exists($this->wpdb, 'rows_affected')) {
            $affected = $this->wpdb->rows_affected;

            return is_numeric($affected) ? max(0, (int) $affected) : 0;
        }

        return $result === true ? 1 : 0;
    }

    /**
     * @param array<string,string> $target
     * @param array<string,mixed> $expected
     * @param array<string,mixed> $current
     */
    private function restoredTargetMatches(array $target, array $expected, array $current): bool
    {
        if ($target['kind'] === 'options' || $target['kind'] === 'cron') {
            return (string) ($current['option_value'] ?? '') === (string) ($expected['option_value'] ?? '')
                && (string) ($current['autoload'] ?? 'no') === (string) ($expected['autoload'] ?? 'no');
        }

        if ($target['kind'] === 'posts') {
            return (string) ($current['post_content'] ?? '') === (string) ($expected['post_content'] ?? '')
                && (string) ($current['post_excerpt'] ?? '') === (string) ($expected['post_excerpt'] ?? '');
        }

        if ($target['kind'] === 'postmeta') {
            return (string) ($current['meta_value'] ?? '') === (string) ($expected['meta_value'] ?? '');
        }

        if ($target['kind'] === 'comments') {
            return (string) ($current['comment_content'] ?? '') === (string) ($expected['comment_content'] ?? '')
                && (string) ($current['comment_author_url'] ?? '') === (string) ($expected['comment_author_url'] ?? '');
        }

        return false;
    }

    /**
     * @param mixed $value
     * @return mixed
     */
    private function sanitizeStoredValue($value)
    {
        if (is_string($value)) {
            return $this->sanitizeString($value);
        }

        if (is_array($value)) {
            foreach ($value as $key => $item) {
                $value[$key] = $this->sanitizeStoredValue($item);
            }
        }

        return $value;
    }

    private function sanitizeString(string $value): string
    {
        $clean = $this->sanitizeHtml($value);
        $swept = $this->removeDangerousTextPayloads($clean);
        if ($swept !== $clean) {
            return trim($swept);
        }

        $decoded = $this->decodeSanitizationSurface($value);
        if ($decoded !== $value) {
            $decodedClean = $this->sanitizeHtml($decoded);
            $decodedSwept = $this->removeDangerousTextPayloads($decodedClean);
            if ($decodedClean !== $decoded || $decodedSwept !== $decodedClean) {
                return trim($decodedSwept);
            }
        }

        return trim($clean);
    }

    private function sanitizeHtml(string $value): string
    {
        if (function_exists('wp_kses')) {
            return (string) wp_kses($value, $this->allowedRemediationHtml(), $this->allowedRemediationProtocols());
        }

        return $this->stripAllHtml($value);
    }

    private function autoloadDisableAllowed(string $optionName): bool
    {
        $optionName = strtolower(trim($optionName));
        if ($optionName === '') {
            return false;
        }

        if (in_array($optionName, self::AUTOLOAD_DISABLE_EXACT_ALLOWLIST, true)) {
            return true;
        }

        foreach (self::AUTOLOAD_DISABLE_PREFIX_ALLOWLIST as $prefix) {
            if (strpos($optionName, $prefix) === 0) {
                return true;
            }
        }

        foreach (self::AUTOLOAD_DISABLE_CONTAINS_ALLOWLIST as $token) {
            if (strpos($optionName, $token) !== false) {
                return true;
            }
        }

        return false;
    }

    private function containsSerializedObject(string $value): bool
    {
        if ($value === '') {
            return false;
        }

        return preg_match('~(?:^|[;{])(?:O|C):\d+:"[^"]+"~', $value) === 1;
    }

    /**
     * @param mixed $value
     */
    private function containsObjectValue($value): bool
    {
        if (is_object($value)) {
            return true;
        }

        if (!is_array($value)) {
            return false;
        }

        foreach ($value as $item) {
            if ($this->containsObjectValue($item)) {
                return true;
            }
        }

        return false;
    }

    /**
     * @return array<string,array<string,bool>>
     */
    private function allowedRemediationHtml(): array
    {
        return [
            'a' => [
                'href' => true,
                'title' => true,
                'rel' => true,
                'target' => true,
            ],
            'abbr' => ['title' => true],
            'acronym' => ['title' => true],
            'b' => [],
            'blockquote' => ['cite' => true],
            'br' => [],
            'cite' => [],
            'code' => [],
            'del' => ['datetime' => true],
            'em' => [],
            'figcaption' => [],
            'figure' => [],
            'h1' => [],
            'h2' => [],
            'h3' => [],
            'h4' => [],
            'h5' => [],
            'h6' => [],
            'i' => [],
            'img' => [
                'alt' => true,
                'height' => true,
                'loading' => true,
                'src' => true,
                'title' => true,
                'width' => true,
            ],
            'li' => [],
            'ol' => [],
            'p' => [],
            'pre' => [],
            's' => [],
            'span' => [],
            'strong' => [],
            'ul' => [],
        ];
    }

    /**
     * @return array<int,string>
     */
    private function allowedRemediationProtocols(): array
    {
        return ['http', 'https', 'mailto', 'tel'];
    }

    private function decodeSanitizationSurface(string $value): string
    {
        for ($i = 0; $i < 3; $i++) {
            $decoded = html_entity_decode($value, ENT_QUOTES | ENT_HTML5, 'UTF-8');
            $decoded = rawurldecode($decoded);
            if ($decoded === $value) {
                break;
            }
            $value = $decoded;
        }

        return $value;
    }

    /**
     * Secondary regex sweep after wp_kses has already stripped disallowed tags
     * and protocols. The allowlist is the primary defense; these expressions
     * catch script-context payloads that survive decoding or attribute cleanup.
     */
    private function removeDangerousTextPayloads(string $value): string
    {
        $value = (string) preg_replace('~\b(?:java\s*script|vb\s*script|data)\s*:~i', '', $value);
        $value = (string) preg_replace('~\b(?:alert|eval|atob|btoa|prompt|confirm|Function|setTimeout|setInterval)\s*\([^)]*\)\s*;?~i', '', $value);
        $value = (string) preg_replace('~</?\s*script\b[^>]*>?~i', '', $value);
        $value = (string) preg_replace('~\b(?:window|document|top)\s*\.\s*location(?:\s*\.\s*href|\s*\.\s*replace)?\s*=\s*[^;\n\r]+;?~i', '', $value);
        $value = (string) preg_replace('~\blocation\s*\.\s*(?:href|replace)\s*\([^;\n\r]*\)\s*;?~i', '', $value);

        return $value;
    }

    private function stripAllHtml(string $value): string
    {
        if (function_exists('wp_strip_all_tags')) {
            return (string) wp_strip_all_tags($value);
        }

        return (string) preg_replace('~<[^>]*>~', '', $value);
    }

    /**
     * @param array<string,mixed> $row
     * @return array<string,mixed>
     */
    private function redactedRow(array $row): array
    {
        $redacted = [];
        foreach ($row as $key => $value) {
            $redacted[$key] = $this->redactedValue((string) $key, $value);
        }

        return $redacted;
    }

    /**
     * @param mixed $value
     * @return mixed
     */
    private function redactedValue(string $key, $value, int $depth = 0)
    {
        if ($this->isSensitiveFieldName($key)) {
            return '[redacted]';
        }

        if ($depth > 4) {
            return '[complex value]';
        }

        if (is_array($value)) {
            $redacted = [];
            foreach ($value as $childKey => $childValue) {
                $redacted[$childKey] = $this->redactedValue((string) $childKey, $childValue, $depth + 1);
            }

            return $redacted;
        }

        if (!is_scalar($value)) {
            return '[complex value]';
        }

        $value = (string) $value;
        $serialized = $this->redactedSerializedValue($value, $depth);
        if ($serialized !== null) {
            return $serialized;
        }

        if ($this->looksLikeSecretValue($value)) {
            return '[redacted]';
        }

        return strlen($value) > 1200 ? substr($value, 0, 1200) . "\n...[truncated]" : $value;
    }

    /**
     * @return mixed|null
     */
    private function redactedSerializedValue(string $value, int $depth)
    {
        $trimmed = trim($value);
        if (strlen($trimmed) > self::MAX_UNSERIALIZE_BYTES || !preg_match('~^(?:a|O|C):~', $trimmed)) {
            return null;
        }

        $decoded = @unserialize($trimmed, ['allowed_classes' => false]);
        if ($decoded === false && $trimmed !== 'b:0;') {
            return null;
        }

        if (is_object($decoded)) {
            return '[serialized object]';
        }

        if (!is_array($decoded)) {
            return '[serialized value]';
        }

        return $this->redactedValue('serialized', $decoded, $depth + 1);
    }

    private function isSensitiveFieldName(string $key): bool
    {
        return preg_match('~(?:password|passwd|secret|token|salt|nonce|session|cookie|authorization|bearer|auth[_-]?key|api[_-]?key|private[_-]?key|client[_-]?secret|refresh[_-]?token|access[_-]?token|aws[_-]?(?:access|secret|session)?|secret[_-]?access[_-]?key|credential|(?:^|_)key(?:$|_))~i', $key) === 1;
    }

    private function looksLikeSecretValue(string $value): bool
    {
        return preg_match('#(?:\bBearer\s+[A-Za-z0-9._~+/\-=]+|AKIA[0-9A-Z]{16}|ASIA[0-9A-Z]{16})#', $value) === 1;
    }

    private function available(): bool
    {
        return is_object($this->wpdb)
            && isset($this->wpdb->options, $this->wpdb->posts, $this->wpdb->users, $this->wpdb->prefix)
            && method_exists($this->wpdb, 'get_row')
            && method_exists($this->wpdb, 'update');
    }

    /**
     * @return array<string,mixed>|null
     */
    private function row(string $sql): ?array
    {
        $format = defined('ARRAY_A') ? constant('ARRAY_A') : 'ARRAY_A';
        if (property_exists($this->wpdb, 'last_error')) {
            $this->wpdb->last_error = '';
        }
        $row = $this->wpdb->get_row($sql, $format);

        return is_array($row) ? $row : null;
    }

    private function lastDatabaseError(): string
    {
        if (!is_object($this->wpdb) || !property_exists($this->wpdb, 'last_error')) {
            return '';
        }

        $error = $this->wpdb->last_error;

        return is_scalar($error) ? trim((string) $error) : '';
    }

    /**
     * @param mixed $value
     * @return mixed
     */
    private function maybeUnserialize($value)
    {
        if (!is_string($value)) {
            return $value;
        }

        $trimmed = trim($value);
        if ($trimmed === '') {
            return $value;
        }

        if (strlen($trimmed) > self::MAX_UNSERIALIZE_BYTES) {
            return $value;
        }

        if (function_exists('is_serialized')) {
            if (!is_serialized($trimmed)) {
                return $value;
            }
        } elseif (!preg_match('~^(?:a|O|s|i|b|d|N;)[:;]~', $trimmed)) {
            return $value;
        }

        $decoded = @unserialize($trimmed, ['allowed_classes' => false]);
        if ($decoded === false && $trimmed !== 'b:0;') {
            return $value;
        }

        return $decoded;
    }

    /**
     * @param mixed $value
     */
    private function maybeSerialize($value): string
    {
        if (function_exists('maybe_serialize')) {
            return maybe_serialize($value);
        }

        return is_array($value) || is_object($value) ? serialize($value) : (string) $value;
    }

    private function backupDirectory(): string
    {
        return $this->storage->baseDir() . '/db-backups';
    }

    private function makeDirectory(string $path): void
    {
        if (is_dir($path)) {
            return;
        }

        if (function_exists('wp_mkdir_p')) {
            wp_mkdir_p($path);
            return;
        }

        mkdir($path, 0755, true);
    }

    private function normalizeAction(string $action): string
    {
        $action = strtolower(trim($action));
        return preg_match('/^[a-z0-9_]+$/', $action) ? $action : '';
    }

    /**
     * @return array{ok:bool,message:string,path:string}
     */
    private function result(bool $ok, string $message, string $path = '', array $extra = []): array
    {
        return array_merge([
            'ok' => $ok,
            'message' => $message,
            'path' => $path,
        ], $extra);
    }

    private function text(string $message): string
    {
        return $message;
    }

    private function adminReadGuard(): string
    {
        if (!function_exists('current_user_can')) {
            return $this->text('Administrator permission could not be verified for database remediation.');
        }

        $capability = defined('SHIELDX_MANAGE_CAPABILITY') ? (string) SHIELDX_MANAGE_CAPABILITY : 'manage_options';

        return current_user_can($capability)
            ? ''
            : $this->text('Administrator permission is required for database remediation.');
    }

    private function adminWriteGuard(string $nonceAction, string $nonce = ''): string
    {
        if (defined('WP_CLI') && WP_CLI) {
            if (getenv('SHIELDX_CLI_CONFIRM') === 'yes') {
                return $this->adminReadGuard() === ''
                    ? ''
                    : $this->text('WP-CLI database remediation requires an administrator context. Re-run with --user=<admin>.');
            }

            return $this->text('WP-CLI database remediation requires SHIELDX_CLI_CONFIRM=yes and an administrator-gated command.');
        }

        $readGuard = $this->adminReadGuard();
        if ($readGuard !== '') {
            return $readGuard;
        }

        if ($nonceAction === '') {
            return $this->text('Missing security token context.');
        }

        if (!function_exists('wp_verify_nonce')) {
            return $this->text('WordPress nonce verification is not available.');
        }

        if ($nonce === '') {
            $nonce = $this->requestNonce();
        }

        return $nonce !== '' && wp_verify_nonce($nonce, $nonceAction) !== false
            ? ''
            : $this->text('Security check failed. Refresh the page and try again.');
    }

    private function requestNonce(): string
    {
        foreach (['_wpnonce', '_ajax_nonce'] as $key) {
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- This helper is retrieving the nonce value so adminWriteGuard() can verify it immediately.
            if (isset($_REQUEST[$key]) && is_scalar($_REQUEST[$key])) {
                // phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized, WordPress.Security.ValidatedSanitizedInput.MissingUnslash -- Nonce value is unslashed and sanitized below before wp_verify_nonce().
                $value = $_REQUEST[$key];
                if (function_exists('wp_unslash')) {
                    $value = wp_unslash($value);
                }
                $value = (string) $value;

                return function_exists('sanitize_text_field') ? sanitize_text_field($value) : trim($value);
            }
        }

        return '';
    }
}
