<?php

declare(strict_types=1);

namespace ShieldX\Scanner;

defined('ABSPATH') || exit;

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Scanner reads dynamic WordPress tables through $wpdb-owned table names without accepting user SQL.

final class DatabaseScanner
{
    public const ENGINE_VERSION = 'shieldx-db-scanner-0.1.0';
    public const DEEP_ENGINE_VERSION = 'shieldx-db-scanner-deep-1.0.0';
    public const DEEP_PHASE_OPTIONS = 'options';
    public const DEEP_PHASE_POSTS = 'posts';
    public const DEEP_PHASE_POSTMETA = 'postmeta';
    public const DEEP_PHASE_COMMENTS = 'comments';
    public const DEEP_PHASE_ADMINISTRATORS = 'administrators';
    private const MAX_INSPECT_BYTES = 262144;
    private const MAX_UNSERIALIZE_BYTES = 1048576;
    private const LARGE_AUTOLOAD_BYTES = 250000;
    private const ADMIN_BASELINE_OPTION = 'shieldx_db_admin_baseline';

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

    /** @var array<int,int>|null */
    private $adminBaseline;

    /** @var string */
    private $homeUrl;

    /**
     * @param array<int,int>|null $adminBaseline
     */
    public function __construct($wpdb = null, ?array $adminBaseline = null, string $homeUrl = '')
    {
        $this->wpdb = $wpdb;
        $this->adminBaseline = $adminBaseline === null ? null : array_values(array_unique(array_map('intval', $adminBaseline)));
        $this->homeUrl = $homeUrl;
    }

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

        $homeUrl = function_exists('home_url') ? (string) home_url('/') : '';

        return new self($wpdb, null, $homeUrl);
    }

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

    /**
     * @return array<string,mixed>
     */
    public function scan(): array
    {
        $issues = [];
        $inspectedPaths = [];
        $itemsScanned = 0;

        foreach ($this->scanOptions($itemsScanned, $inspectedPaths) as $issue) {
            $issues[] = $issue;
        }

        foreach ($this->scanPosts($itemsScanned, $inspectedPaths) as $issue) {
            $issues[] = $issue;
        }

        foreach ($this->scanPostMeta($itemsScanned, $inspectedPaths) as $issue) {
            $issues[] = $issue;
        }

        foreach ($this->scanComments($itemsScanned, $inspectedPaths) as $issue) {
            $issues[] = $issue;
        }

        foreach ($this->scanAdminUsers($itemsScanned, $inspectedPaths) as $issue) {
            $issues[] = $issue;
        }

        $activeThreatIds = [];
        foreach ($issues as $issue) {
            $activeThreatIds[] = (string) ($issue['threat_id'] ?? '');
        }

        return [
            'engine_version' => self::ENGINE_VERSION,
            'available' => $this->available(),
            'items_scanned' => $itemsScanned,
            'issues_found' => count($issues),
            'inspected_paths' => array_values(array_unique($inspectedPaths)),
            'active_threat_ids' => array_values(array_unique(array_filter($activeThreatIds))),
            'issues' => $issues,
        ];
    }

    /**
     * Scan one deterministic Deep database batch.
     *
     * @param int|string $cursor
     * @return array<string,mixed>
     */
    public function scanDeepBatch(string $phase, $cursor = 0, int $limit = 250): array
    {
        if (!$this->available()) {
            throw new \RuntimeException('Database scanner is unavailable.');
        }

        $limit = max(1, min(1000, $limit));
        if (property_exists($this->wpdb, 'last_error')) {
            $this->wpdb->last_error = '';
        }
        $rows = $this->deepRows($phase, $cursor, $limit);
        if (property_exists($this->wpdb, 'last_error') && (string) $this->wpdb->last_error !== '') {
            throw new \RuntimeException('Deep database scan query failed.');
        }
        $issues = [];
        $inspectedPaths = [];
        $itemsScanned = 0;
        $nextCursor = $cursor;

        foreach ($rows as $row) {
            $itemsScanned++;
            $nextCursor = $this->deepRowCursor($phase, $row);
            foreach ($this->inspectDeepRow($phase, $row, $itemsScanned, $inspectedPaths) as $issue) {
                $issues[] = $issue;
            }
        }

        $activeThreatIds = [];
        foreach ($issues as $issue) {
            $activeThreatIds[] = (string) ($issue['threat_id'] ?? '');
        }

        return [
            'engine_version' => self::DEEP_ENGINE_VERSION,
            'available' => true,
            'phase' => $phase,
            'cursor' => $nextCursor,
            'items_scanned' => $itemsScanned,
            'issues_found' => count($issues),
            'inspected_paths' => array_values(array_unique($inspectedPaths)),
            'active_threat_ids' => array_values(array_unique(array_filter($activeThreatIds))),
            'issues' => $issues,
            'phase_complete' => count($rows) < $limit,
            'administrator_ids' => $phase === self::DEEP_PHASE_ADMINISTRATORS
                ? array_values(array_filter(array_map(static function (array $row): int {
                    return (int) ($row['ID'] ?? 0);
                }, $rows)))
                : [],
        ];
    }

    /**
     * @param array<int,string> $paths
     * @return array<string,mixed>
     */
    public function scanPaths(array $paths): array
    {
        $requested = array_values(array_unique(array_filter(array_map('strval', $paths))));
        $scan = $this->scan();
        $scanInspected = isset($scan['inspected_paths']) && is_array($scan['inspected_paths'])
            ? array_values(array_unique(array_filter(array_map('strval', $scan['inspected_paths']))))
            : [];

        $inspectedPaths = $this->targetInspectedPaths($requested, $scanInspected);
        $issues = [];

        foreach ((array) ($scan['issues'] ?? []) as $issue) {
            if (!is_array($issue)) {
                continue;
            }

            if (in_array((string) ($issue['affected_path'] ?? ''), $inspectedPaths, true)) {
                $issues[] = $issue;
            }
        }

        $activeThreatIds = [];
        foreach ($issues as $issue) {
            $activeThreatIds[] = (string) ($issue['threat_id'] ?? '');
        }

        return [
            'engine_version' => self::ENGINE_VERSION,
            'available' => !empty($scan['available']),
            'items_scanned' => (int) ($scan['items_scanned'] ?? 0),
            'issues_found' => count($issues),
            'inspected_paths' => $inspectedPaths,
            'active_threat_ids' => array_values(array_unique(array_filter($activeThreatIds))),
            'issues' => $issues,
        ];
    }

    /**
     * @param array<int,string> $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function scanOptions(int &$itemsScanned, array &$inspectedPaths): array
    {
        $issues = [];

        if (!$this->available()) {
            return $issues;
        }

        $table = (string) $this->wpdb->options;
        $rows = $this->rows(
            "SELECT option_name, LEFT(option_value, " . self::MAX_INSPECT_BYTES . ") AS option_value, autoload FROM {$table}
             WHERE autoload IN ('yes', 'on', 'auto', 'auto-on')
                OR option_name IN ('cron', 'siteurl', 'home')
                OR option_name LIKE 'widget_%'
                OR option_name LIKE 'redirect%'
                OR option_name LIKE 'redirection%'
                OR option_name LIKE 'insert%'
                OR option_name LIKE 'ihaf%'
                OR option_name LIKE 'wpcode%'
             LIMIT 800"
        );

        foreach ($rows as $row) {
            $itemsScanned++;
            $name = (string) ($row['option_name'] ?? '');
            $value = (string) ($row['option_value'] ?? '');
            $autoload = strtolower((string) ($row['autoload'] ?? ''));
            $path = 'db:options:' . $name;
            $inspectedPaths[] = $path;
            $surface = $this->inspectable($value);

            if ($name === 'cron') {
                foreach ($this->scanCronOption($value, $itemsScanned, $inspectedPaths) as $issue) {
                    $issues[] = $issue;
                }
            }

            if ($this->isLargeAutoload($autoload, $value)) {
                $issues[] = $this->issue(
                    'SX-DB-OPTION-001',
                    'database_autoload_option',
                    'medium',
                    'Large autoloaded option can hide injected payloads and slow every WordPress request.',
                    $path,
                    $name . ' is ' . strlen($value) . ' bytes'
                );
            }

            if ($this->containsExecutableCode($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-CODE-001',
                    'database_code_injection',
                    'critical',
                    'Executable PHP or shell-style code marker was found in a database option.',
                    $path,
                    $this->evidence($surface)
                );
            }

            if ($this->containsSuspiciousScript($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-SCRIPT-001',
                    strpos($name, 'widget_') === 0 ? 'database_widget_script' : 'database_injected_script',
                    'high',
                    'Suspicious script, iframe, event handler, or JavaScript URL was found in stored site content.',
                    $path,
                    $this->evidence($surface)
                );
            }

            if ($this->containsSuspiciousRedirect($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-REDIRECT-001',
                    'database_redirect',
                    'high',
                    'Suspicious redirect JavaScript or meta refresh was found in stored site content.',
                    $path,
                    $this->evidence($surface)
                );
            }

            if (in_array($name, ['siteurl', 'home'], true) && $this->homeUrl !== '' && $this->hostChanged($this->homeUrl, $value)) {
                $issues[] = $this->issue(
                    'SX-DB-REDIRECT-002',
                    'database_site_url',
                    'critical',
                    'WordPress site URL option points to a different host than the expected site URL.',
                    $path,
                    $name . '=' . $value
                );
            }
        }

        return $issues;
    }

    /**
     * @param array<int,string> $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function scanPosts(int &$itemsScanned, array &$inspectedPaths): array
    {
        $issues = [];

        if (!$this->available()) {
            return $issues;
        }

        $table = (string) $this->wpdb->posts;
        $rows = $this->rows(
            "SELECT ID, post_type, post_title, post_content, post_excerpt
             FROM {$table}
             WHERE post_status NOT IN ('trash', 'auto-draft')
             ORDER BY post_modified_gmt DESC, ID DESC
             LIMIT 300"
        );

        foreach ($rows as $row) {
            $itemsScanned++;
            $id = (int) ($row['ID'] ?? 0);
            $path = 'db:posts:' . (string) $id;
            $inspectedPaths[] = $path;
            $surface = $this->inspectable((string) ($row['post_content'] ?? '') . "\n" . (string) ($row['post_excerpt'] ?? ''));

            if ($this->containsSuspiciousScript($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-SCRIPT-002',
                    'database_post_script',
                    'high',
                    'Suspicious script, iframe, event handler, or JavaScript URL was found in a post or page.',
                    $path,
                    $this->postEvidence($row, $surface)
                );
            }

            if ($this->containsSuspiciousRedirect($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-REDIRECT-003',
                    'database_post_redirect',
                    'high',
                    'Suspicious redirect JavaScript or meta refresh was found in a post or page.',
                    $path,
                    $this->postEvidence($row, $surface)
                );
            }
        }

        return $issues;
    }

    /**
     * @param array<int,string> $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function scanPostMeta(int &$itemsScanned, array &$inspectedPaths): array
    {
        $issues = [];

        if (!$this->available() || !isset($this->wpdb->postmeta)) {
            return $issues;
        }

        $table = (string) $this->wpdb->postmeta;
        $rows = $this->rows(
            "SELECT meta_id, post_id, meta_key, meta_value
             FROM {$table}
             WHERE meta_value <> ''
             ORDER BY meta_id DESC
             LIMIT 300"
        );

        foreach ($rows as $row) {
            $itemsScanned++;
            $metaId = (int) ($row['meta_id'] ?? 0);
            $postId = (int) ($row['post_id'] ?? 0);
            $metaKey = (string) ($row['meta_key'] ?? '');
            $path = 'db:postmeta:' . (string) ($metaId > 0 ? $metaId : $postId . ':' . $metaKey);
            $inspectedPaths[] = $path;
            $surface = $this->inspectable((string) ($row['meta_value'] ?? ''));

            if ($this->containsExecutableCode($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-CODE-002',
                    'database_postmeta_code',
                    'critical',
                    'Executable PHP or shell-style code marker was found in post metadata.',
                    $path,
                    $this->postMetaEvidence($row, $surface)
                );
            }

            if ($this->containsSuspiciousScript($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-SCRIPT-003',
                    'database_postmeta_script',
                    'high',
                    'Suspicious script, iframe, event handler, or JavaScript URL was found in post metadata.',
                    $path,
                    $this->postMetaEvidence($row, $surface)
                );
            }

            if ($this->containsSuspiciousRedirect($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-REDIRECT-004',
                    'database_postmeta_redirect',
                    'high',
                    'Suspicious redirect JavaScript or meta refresh was found in post metadata.',
                    $path,
                    $this->postMetaEvidence($row, $surface)
                );
            }
        }

        return $issues;
    }

    /**
     * @param array<int,string> $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function scanComments(int &$itemsScanned, array &$inspectedPaths): array
    {
        $issues = [];

        if (!$this->available() || !isset($this->wpdb->comments)) {
            return $issues;
        }

        $table = (string) $this->wpdb->comments;
        $rows = $this->rows(
            "SELECT comment_ID, comment_post_ID, comment_author, comment_author_url, comment_content
             FROM {$table}
             WHERE comment_approved NOT IN ('trash', 'spam')
             ORDER BY comment_ID DESC
             LIMIT 300"
        );

        foreach ($rows as $row) {
            $itemsScanned++;
            $commentId = (int) ($row['comment_ID'] ?? 0);
            $path = 'db:comments:' . (string) $commentId;
            $inspectedPaths[] = $path;
            $surface = $this->inspectable((string) ($row['comment_content'] ?? '') . "\n" . (string) ($row['comment_author_url'] ?? ''));

            if ($this->containsSuspiciousScript($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-SCRIPT-004',
                    'database_comment_script',
                    'high',
                    'Suspicious script, iframe, event handler, or JavaScript URL was found in a comment.',
                    $path,
                    $this->commentEvidence($row, $surface)
                );
            }

            if ($this->containsSuspiciousRedirect($surface) || $this->containsRemoteLoader($surface)) {
                $issues[] = $this->issue(
                    'SX-DB-REDIRECT-005',
                    'database_comment_redirect',
                    'high',
                    'Suspicious redirect or remote loader was found in a comment.',
                    $path,
                    $this->commentEvidence($row, $surface)
                );
            }
        }

        return $issues;
    }

    /**
     * @param array<int,string> $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function scanAdminUsers(int &$itemsScanned, array &$inspectedPaths): array
    {
        $issues = [];

        if (!$this->available()) {
            return $issues;
        }

        $users = (string) $this->wpdb->users;
        $usermeta = (string) $this->wpdb->usermeta;
        $capabilityKey = (string) $this->wpdb->prefix . 'capabilities';
        $rows = $this->rows(
            "SELECT u.ID, u.user_login, u.user_email, u.user_registered, um.meta_value AS capabilities
             FROM {$users} u
             INNER JOIN {$usermeta} um ON u.ID = um.user_id
             WHERE um.meta_key = %s AND um.meta_value LIKE %s
             LIMIT 200",
            [$capabilityKey, '%administrator%']
        );
        $adminIds = [];
        $baseline = $this->adminBaseline();
        $baselineKnown = is_array($baseline);

        foreach ($rows as $row) {
            $itemsScanned++;
            $id = (int) ($row['ID'] ?? 0);
            $adminIds[] = $id;
            $path = 'db:users:' . (string) $id;
            $inspectedPaths[] = $path;
            $login = (string) ($row['user_login'] ?? '');
            $email = (string) ($row['user_email'] ?? '');

            if ($baselineKnown && !in_array($id, $baseline, true)) {
                $issues[] = $this->issue(
                    'SX-DB-USER-001',
                    'database_unknown_admin',
                    'high',
                    'Administrator account is not part of the SHIELD-X admin baseline.',
                    $path,
                    $this->adminEvidence($login, $email)
                );
            }

            if (!$this->isValidEmail($email) || $this->isSuspiciousAdminLogin($login)) {
                $issues[] = $this->issue(
                    'SX-DB-USER-002',
                    'database_suspicious_admin',
                    'medium',
                    'Administrator account has a suspicious username pattern or invalid email address.',
                    $path,
                    $this->adminEvidence($login, $email)
                );
            }
        }

        if (!$baselineKnown) {
            $this->storeAdminBaseline($adminIds);
        }

        return $issues;
    }

    /**
     * @param array<int,string> $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function scanCronOption(string $value, int &$itemsScanned, array &$inspectedPaths): array
    {
        $issues = [];
        $cron = $this->maybeUnserialize($value);

        if (!is_array($cron)) {
            return $issues;
        }

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

            foreach ($hooks as $hook => $events) {
                $hook = (string) $hook;

                if ($hook === 'version') {
                    continue;
                }

                $path = 'db:cron:' . $hook;
                $inspectedPaths[] = $path;

                if (!is_array($events)) {
                    $itemsScanned++;
                    $surface = $this->inspectable($hook . "\n" . $this->flattenText($events));
                    if ($this->isSuspiciousCronHook($hook) || $this->containsExecutableCode($surface) || $this->containsRemoteLoader($surface)) {
                        $issues[] = $this->issue(
                            'SX-DB-CRON-001',
                            'database_cron_hook',
                            'high',
                            'Suspicious WP-Cron hook or cron arguments were found in the database.',
                            $path,
                            'timestamp=' . (string) $timestamp . '; ' . $this->evidence($surface)
                        );
                    }
                    continue;
                }

                foreach ($events as $eventKey => $event) {
                    $itemsScanned++;
                    $eventPath = $path;
                    $signature = $this->cronEventSignature($eventKey, $event);
                    if ($signature !== '') {
                        $eventPath .= ':' . $signature;
                        $inspectedPaths[] = $eventPath;
                    }

                    $surface = $this->inspectable($hook . "\n" . $this->flattenText($event));
                    if (!$this->isSuspiciousCronHook($hook) && !$this->containsExecutableCode($surface) && !$this->containsRemoteLoader($surface)) {
                        continue;
                    }

                    $issues[] = $this->issue(
                        'SX-DB-CRON-001',
                        'database_cron_hook',
                        'high',
                        'Suspicious WP-Cron hook or cron arguments were found in the database.',
                        $eventPath,
                        'timestamp=' . (string) $timestamp . '; ' . $this->evidence($surface)
                    );
                }
            }
        }

        return $issues;
    }

    /**
     * @param mixed $eventKey
     * @param mixed $event
     */
    private function cronEventSignature($eventKey, $event): string
    {
        $key = is_scalar($eventKey) ? strtolower((string) $eventKey) : '';
        if (preg_match('/^[a-f0-9]{32}$/', $key) === 1) {
            return $key;
        }

        if (!is_array($event) || !array_key_exists('args', $event)) {
            return '';
        }

        return md5(serialize($event['args']));
    }

    /**
     * @param int|string $cursor
     * @return array<int,array<string,mixed>>
     */
    private function deepRows(string $phase, $cursor, int $limit): array
    {
        if ($phase === self::DEEP_PHASE_OPTIONS) {
            $table = (string) $this->wpdb->options;

            return $this->rows(
                "SELECT option_name, LEFT(option_value, " . self::MAX_INSPECT_BYTES . ") AS option_value, autoload
                 FROM {$table}
                 WHERE option_name > %s
                 ORDER BY option_name ASC
                 LIMIT {$limit}",
                [(string) $cursor]
            );
        }

        if ($phase === self::DEEP_PHASE_POSTS) {
            $table = (string) $this->wpdb->posts;

            return $this->rows(
                "SELECT ID, post_type, post_title,
                        LEFT(post_content, " . self::MAX_INSPECT_BYTES . ") AS post_content,
                        LEFT(post_excerpt, " . self::MAX_INSPECT_BYTES . ") AS post_excerpt
                 FROM {$table}
                 WHERE ID > %s AND post_status NOT IN ('trash', 'auto-draft')
                 ORDER BY ID ASC
                 LIMIT {$limit}",
                [(int) $cursor]
            );
        }

        if ($phase === self::DEEP_PHASE_POSTMETA && isset($this->wpdb->postmeta)) {
            $table = (string) $this->wpdb->postmeta;

            return $this->rows(
                "SELECT meta_id, post_id, meta_key,
                        LEFT(meta_value, " . self::MAX_INSPECT_BYTES . ") AS meta_value
                 FROM {$table}
                 WHERE meta_id > %s AND meta_value <> ''
                 ORDER BY meta_id ASC
                 LIMIT {$limit}",
                [(int) $cursor]
            );
        }

        if ($phase === self::DEEP_PHASE_COMMENTS && isset($this->wpdb->comments)) {
            $table = (string) $this->wpdb->comments;

            return $this->rows(
                "SELECT comment_ID, comment_post_ID, comment_author, comment_author_url,
                        LEFT(comment_content, " . self::MAX_INSPECT_BYTES . ") AS comment_content
                 FROM {$table}
                 WHERE comment_ID > %s AND comment_approved NOT IN ('trash', 'spam')
                 ORDER BY comment_ID ASC
                 LIMIT {$limit}",
                [(int) $cursor]
            );
        }

        if ($phase === self::DEEP_PHASE_ADMINISTRATORS) {
            $users = (string) $this->wpdb->users;
            $usermeta = (string) $this->wpdb->usermeta;
            $capabilityKey = (string) $this->wpdb->prefix . 'capabilities';

            return $this->rows(
                "SELECT u.ID, u.user_login, u.user_email, u.user_registered, um.meta_value AS capabilities
                 FROM {$users} u
                 INNER JOIN {$usermeta} um ON u.ID = um.user_id
                 WHERE u.ID > %s AND um.meta_key = %s AND um.meta_value LIKE %s
                 ORDER BY u.ID ASC
                 LIMIT {$limit}",
                [(int) $cursor, $capabilityKey, '%administrator%']
            );
        }

        throw new \InvalidArgumentException('Unsupported Deep database scan phase.');
    }

    /**
     * @param array<string,mixed> $row
     * @return int|string
     */
    private function deepRowCursor(string $phase, array $row)
    {
        if ($phase === self::DEEP_PHASE_OPTIONS) {
            return (string) ($row['option_name'] ?? '');
        }
        if ($phase === self::DEEP_PHASE_POSTS) {
            return (int) ($row['ID'] ?? 0);
        }
        if ($phase === self::DEEP_PHASE_POSTMETA) {
            return (int) ($row['meta_id'] ?? 0);
        }
        if ($phase === self::DEEP_PHASE_COMMENTS) {
            return (int) ($row['comment_ID'] ?? 0);
        }

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

    /**
     * @param array<string,mixed> $row
     * @param array<int,string>   $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function inspectDeepRow(string $phase, array $row, int &$itemsScanned, array &$inspectedPaths): array
    {
        if ($phase === self::DEEP_PHASE_OPTIONS) {
            return $this->inspectDeepOption($row, $itemsScanned, $inspectedPaths);
        }
        if ($phase === self::DEEP_PHASE_POSTS) {
            return $this->inspectDeepPost($row, $inspectedPaths);
        }
        if ($phase === self::DEEP_PHASE_POSTMETA) {
            return $this->inspectDeepPostMeta($row, $inspectedPaths);
        }
        if ($phase === self::DEEP_PHASE_COMMENTS) {
            return $this->inspectDeepComment($row, $inspectedPaths);
        }
        if ($phase === self::DEEP_PHASE_ADMINISTRATORS) {
            return $this->inspectDeepAdministrator($row, $inspectedPaths);
        }

        return [];
    }

    /**
     * @param array<string,mixed> $row
     * @param array<int,string>   $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function inspectDeepOption(array $row, int &$itemsScanned, array &$inspectedPaths): array
    {
        $issues = [];
        $name = (string) ($row['option_name'] ?? '');
        $value = (string) ($row['option_value'] ?? '');
        $autoload = strtolower((string) ($row['autoload'] ?? ''));
        $path = 'db:options:' . $name;
        $inspectedPaths[] = $path;
        $surface = $this->inspectable($value);

        if ($name === 'cron') {
            foreach ($this->scanCronOption($value, $itemsScanned, $inspectedPaths) as $issue) {
                $issues[] = $issue;
            }
        }
        if ($this->isLargeAutoload($autoload, $value)) {
            $issues[] = $this->issue('SX-DB-OPTION-001', 'database_autoload_option', 'medium', 'Large autoloaded option can hide injected payloads and slow every WordPress request.', $path, $name . ' is ' . strlen($value) . ' bytes');
        }
        if ($this->containsExecutableCode($surface)) {
            $issues[] = $this->issue('SX-DB-CODE-001', 'database_code_injection', 'critical', 'Executable PHP or shell-style code marker was found in a database option.', $path, $this->evidence($surface));
        }
        if ($this->containsSuspiciousScript($surface)) {
            $issues[] = $this->issue('SX-DB-SCRIPT-001', strpos($name, 'widget_') === 0 ? 'database_widget_script' : 'database_injected_script', 'high', 'Suspicious script, iframe, event handler, or JavaScript URL was found in stored site content.', $path, $this->evidence($surface));
        }
        if ($this->containsSuspiciousRedirect($surface)) {
            $issues[] = $this->issue('SX-DB-REDIRECT-001', 'database_redirect', 'high', 'Suspicious redirect JavaScript or meta refresh was found in stored site content.', $path, $this->evidence($surface));
        }
        if (in_array($name, ['siteurl', 'home'], true) && $this->homeUrl !== '' && $this->hostChanged($this->homeUrl, $value)) {
            $issues[] = $this->issue('SX-DB-REDIRECT-002', 'database_site_url', 'critical', 'WordPress site URL option points to a different host than the expected site URL.', $path, $name . '=' . $value);
        }

        return $issues;
    }

    /**
     * @param array<string,mixed> $row
     * @param array<int,string>   $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function inspectDeepPost(array $row, array &$inspectedPaths): array
    {
        $issues = [];
        $path = 'db:posts:' . (string) ((int) ($row['ID'] ?? 0));
        $inspectedPaths[] = $path;
        $surface = $this->inspectable((string) ($row['post_content'] ?? '') . "\n" . (string) ($row['post_excerpt'] ?? ''));

        if ($this->containsSuspiciousScript($surface)) {
            $issues[] = $this->issue('SX-DB-SCRIPT-002', 'database_post_script', 'high', 'Suspicious script, iframe, event handler, or JavaScript URL was found in a post or page.', $path, $this->postEvidence($row, $surface));
        }
        if ($this->containsSuspiciousRedirect($surface)) {
            $issues[] = $this->issue('SX-DB-REDIRECT-003', 'database_post_redirect', 'high', 'Suspicious redirect JavaScript or meta refresh was found in a post or page.', $path, $this->postEvidence($row, $surface));
        }

        return $issues;
    }

    /**
     * @param array<string,mixed> $row
     * @param array<int,string>   $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function inspectDeepPostMeta(array $row, array &$inspectedPaths): array
    {
        $issues = [];
        $metaId = (int) ($row['meta_id'] ?? 0);
        $postId = (int) ($row['post_id'] ?? 0);
        $metaKey = (string) ($row['meta_key'] ?? '');
        $path = 'db:postmeta:' . (string) ($metaId > 0 ? $metaId : $postId . ':' . $metaKey);
        $inspectedPaths[] = $path;
        $surface = $this->inspectable((string) ($row['meta_value'] ?? ''));

        if ($this->containsExecutableCode($surface)) {
            $issues[] = $this->issue('SX-DB-CODE-002', 'database_postmeta_code', 'critical', 'Executable PHP or shell-style code marker was found in post metadata.', $path, $this->postMetaEvidence($row, $surface));
        }
        if ($this->containsSuspiciousScript($surface)) {
            $issues[] = $this->issue('SX-DB-SCRIPT-003', 'database_postmeta_script', 'high', 'Suspicious script, iframe, event handler, or JavaScript URL was found in post metadata.', $path, $this->postMetaEvidence($row, $surface));
        }
        if ($this->containsSuspiciousRedirect($surface)) {
            $issues[] = $this->issue('SX-DB-REDIRECT-004', 'database_postmeta_redirect', 'high', 'Suspicious redirect JavaScript or meta refresh was found in post metadata.', $path, $this->postMetaEvidence($row, $surface));
        }

        return $issues;
    }

    /**
     * @param array<string,mixed> $row
     * @param array<int,string>   $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function inspectDeepComment(array $row, array &$inspectedPaths): array
    {
        $issues = [];
        $path = 'db:comments:' . (string) ((int) ($row['comment_ID'] ?? 0));
        $inspectedPaths[] = $path;
        $surface = $this->inspectable((string) ($row['comment_content'] ?? '') . "\n" . (string) ($row['comment_author_url'] ?? ''));

        if ($this->containsSuspiciousScript($surface)) {
            $issues[] = $this->issue('SX-DB-SCRIPT-004', 'database_comment_script', 'high', 'Suspicious script, iframe, event handler, or JavaScript URL was found in a comment.', $path, $this->commentEvidence($row, $surface));
        }
        if ($this->containsSuspiciousRedirect($surface) || $this->containsRemoteLoader($surface)) {
            $issues[] = $this->issue('SX-DB-REDIRECT-005', 'database_comment_redirect', 'high', 'Suspicious redirect or remote loader was found in a comment.', $path, $this->commentEvidence($row, $surface));
        }

        return $issues;
    }

    /**
     * @param array<string,mixed> $row
     * @param array<int,string>   $inspectedPaths
     * @return array<int,array<string,mixed>>
     */
    private function inspectDeepAdministrator(array $row, array &$inspectedPaths): array
    {
        $issues = [];
        $id = (int) ($row['ID'] ?? 0);
        $path = 'db:users:' . (string) $id;
        $inspectedPaths[] = $path;
        $login = (string) ($row['user_login'] ?? '');
        $email = (string) ($row['user_email'] ?? '');
        $baseline = $this->adminBaseline();

        if (is_array($baseline) && !in_array($id, $baseline, true)) {
            $issues[] = $this->issue('SX-DB-USER-001', 'database_unknown_admin', 'high', 'Administrator account is not part of the SHIELD-X admin baseline.', $path, $this->adminEvidence($login, $email));
        }
        if (!$this->isValidEmail($email) || $this->isSuspiciousAdminLogin($login)) {
            $issues[] = $this->issue('SX-DB-USER-002', 'database_suspicious_admin', 'medium', 'Administrator account has a suspicious username pattern or invalid email address.', $path, $this->adminEvidence($login, $email));
        }

        return $issues;
    }

    /**
     * @param array<int,int> $adminIds
     */
    public function completeDeepAdministratorBaseline(array $adminIds): void
    {
        if ($this->adminBaseline() !== null) {
            return;
        }

        $this->storeAdminBaseline(array_values(array_unique(array_map('intval', $adminIds))));
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    private function rows(string $sql, array $args = []): array
    {
        if (!$this->available()) {
            return [];
        }

        if (!empty($args)) {
            if (!method_exists($this->wpdb, 'prepare')) {
                return [];
            }

            $prepared = $this->wpdb->prepare($sql, ...$args);
            if (!is_string($prepared) || $prepared === '') {
                return [];
            }

            $sql = $prepared;
        }

        $format = defined('ARRAY_A') ? constant('ARRAY_A') : 'ARRAY_A';
        $rows = $this->wpdb->get_results($sql, $format);

        return is_array($rows) ? array_values(array_filter($rows, 'is_array')) : [];
    }

    private function inspectable(string $value): string
    {
        return strlen($value) > self::MAX_INSPECT_BYTES ? substr($value, 0, self::MAX_INSPECT_BYTES) : $value;
    }

    /**
     * @param array<int,string> $requested
     * @param array<int,string> $scanInspected
     * @return array<int,string>
     */
    private function targetInspectedPaths(array $requested, array $scanInspected): array
    {
        $inspected = [];

        foreach ($requested as $path) {
            if ($path === '') {
                continue;
            }

            if ($path === 'db:options:cron') {
                if (in_array($path, $scanInspected, true)) {
                    $inspected[$path] = $path;
                }

                foreach ($scanInspected as $candidate) {
                    if (strpos($candidate, 'db:cron:') === 0) {
                        $inspected[$candidate] = $candidate;
                    }
                }
                continue;
            }

            if (in_array($path, $scanInspected, true)) {
                $inspected[$path] = $path;
            }
        }

        return array_values($inspected);
    }

    private function isLargeAutoload(string $autoload, string $value): bool
    {
        return in_array($autoload, ['yes', 'on', 'auto', 'auto-on'], true) && strlen($value) >= self::LARGE_AUTOLOAD_BYTES;
    }

    private function containsExecutableCode(string $value): bool
    {
        return (bool) preg_match('~\b(?:eval|assert|base64_decode|gzinflate|str_rot13|shell_exec|system|passthru|proc_open|popen|exec)\s*\(~i', $value);
    }

    private function containsSuspiciousScript(string $value): bool
    {
        if (preg_match('~<script\b[^>]*>.*?(?:eval\s*\(|atob\s*\(|document\.write|fromCharCode)~is', $value)) {
            return true;
        }

        if (preg_match('~<script\b[^>]*(?:eval\s*\(|atob\s*\(|document\.write|fromCharCode|src\s*=\s*["\']?https?://[^"\'>\s]+(?:\.ru|\.cn|\.top|\.xyz|\.click|/gate|/inject))~is', $value)) {
            return true;
        }

        if (preg_match('~<iframe\b[^>]*(?:display\s*:\s*none|width\s*=\s*["\']?0|height\s*=\s*["\']?0)~is', $value)) {
            return true;
        }

        if ($this->containsSuspiciousIframeSource($value)) {
            return true;
        }

        return (bool) preg_match('~\bon(?:error|load)\s*=|javascript\s*:~i', $value);
    }

    private function containsSuspiciousIframeSource(string $value): bool
    {
        if (!preg_match_all('~<iframe\b[^>]*\bsrc\s*=\s*["\']?(https?://[^"\'<>\s]+)~is', $value, $matches)) {
            return false;
        }

        foreach ($matches[1] as $url) {
            if (!$this->isTrustedEmbedUrl((string) $url)) {
                return true;
            }
        }

        return false;
    }

    private function isTrustedEmbedUrl(string $url): bool
    {
        $host = function_exists('wp_parse_url') ? wp_parse_url($url, PHP_URL_HOST) : '';
        $host = is_string($host) ? strtolower($host) : '';
        if ($host === '') {
            return false;
        }

        foreach (['youtube.com', 'youtube-nocookie.com', 'youtu.be', 'vimeo.com', 'player.vimeo.com', 'soundcloud.com', 'open.spotify.com', 'maps.google.com', 'www.google.com'] as $trustedHost) {
            if ($host === $trustedHost || substr($host, -strlen('.' . $trustedHost)) === '.' . $trustedHost) {
                return true;
            }
        }

        return false;
    }

    private function containsSuspiciousRedirect(string $value): bool
    {
        return (bool) preg_match('~<meta\b[^>]+http-equiv\s*=\s*["\']?refresh[^>]+url\s*=~is', $value)
            || (bool) preg_match('~\b(?:window|document|top)\.location(?:\.href|\.replace)?\s*=~i', $value)
            || (bool) preg_match('~\blocation\.(?:href|replace)\s*\(~i', $value);
    }

    private function containsRemoteLoader(string $value): bool
    {
        return (bool) preg_match('~\b(?:wp_remote_get|wp_remote_post|file_get_contents|curl_exec)\s*\([^)]*https?://~i', $value)
            || (bool) preg_match('~https?://[^"\']+\.(?:php|txt|dat)(?:\?|$)~i', $value);
    }

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

    private function isSuspiciousAdminLogin(string $login): bool
    {
        return (bool) preg_match('~^(?:test|temp|backup|support|service|system|wpservice|wp_service|administrator\d+|admin\d+)$~i', $login);
    }

    private function isValidEmail(string $email): bool
    {
        return $email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
    }

    private function hostChanged(string $expected, string $actual): bool
    {
        $expectedHost = $this->urlHost($expected);
        $actualHost = $this->urlHost($actual);

        return $expectedHost !== '' && $actualHost !== '' && strtolower($expectedHost) !== strtolower($actualHost);
    }

    private function urlHost(string $url): string
    {
        if (function_exists('wp_parse_url')) {
            $parsed = wp_parse_url($url);
            $host = is_array($parsed) ? ($parsed['host'] ?? '') : '';

            return is_string($host) ? $host : '';
        }

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

        $withoutScheme = preg_replace('~^[a-z][a-z0-9+.-]*://~i', '', $trimmed) ?: $trimmed;
        $withoutUser = preg_replace('~^[^/@]*@~', '', $withoutScheme) ?: $withoutScheme;
        $hostPort = preg_split('~[/?#]~', $withoutUser, 2)[0] ?? '';
        $host = preg_replace('~:\d+$~', '', $hostPort) ?: $hostPort;

        return trim($host, " \t\n\r\0\x0B[]");
    }

    /**
     * @return array<int,int>|null
     */
    private function adminBaseline(): ?array
    {
        if ($this->adminBaseline !== null) {
            return $this->adminBaseline;
        }

        if (!function_exists('get_option')) {
            return null;
        }

        $baseline = get_option(self::ADMIN_BASELINE_OPTION, null);

        return is_array($baseline) ? array_values(array_unique(array_map('intval', $baseline))) : null;
    }

    /**
     * @param array<int,int> $adminIds
     */
    private function storeAdminBaseline(array $adminIds): void
    {
        if (function_exists('update_option')) {
            update_option(self::ADMIN_BASELINE_OPTION, array_values(array_unique(array_map('intval', $adminIds))), false);
        }
    }

    /**
     * @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 flattenText($value): string
    {
        if (is_scalar($value)) {
            return (string) $value;
        }

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

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

        return implode(' ', array_filter($parts, static function (string $part): bool {
            return $part !== '';
        }));
    }

    /**
     * @param array<string,mixed> $row
     */
    private function postEvidence(array $row, string $surface): string
    {
        return 'post=' . (string) ($row['ID'] ?? '') . '; type=' . (string) ($row['post_type'] ?? '') . '; title=' . substr((string) ($row['post_title'] ?? ''), 0, 80) . '; ' . $this->evidence($surface);
    }

    /**
     * @param array<string,mixed> $row
     */
    private function postMetaEvidence(array $row, string $surface): string
    {
        return 'post=' . (string) ($row['post_id'] ?? '') . '; meta_key=' . substr((string) ($row['meta_key'] ?? ''), 0, 80) . '; ' . $this->evidence($surface);
    }

    /**
     * @param array<string,mixed> $row
     */
    private function commentEvidence(array $row, string $surface): string
    {
        return 'comment=' . (string) ($row['comment_ID'] ?? '') . '; post=' . (string) ($row['comment_post_ID'] ?? '') . '; author=' . substr((string) ($row['comment_author'] ?? ''), 0, 80) . '; ' . $this->evidence($surface);
    }

    private function evidence(string $surface): string
    {
        $surface = trim(preg_replace('~\s+~', ' ', $surface) ?: $surface);

        return 'content length ' . strlen($surface) . ' bytes; sha256 ' . substr(hash('sha256', $surface), 0, 16);
    }

    private function adminEvidence(string $login, string $email): string
    {
        return 'login_sha256=' . substr(hash('sha256', strtolower(trim($login))), 0, 16)
            . '; email_sha256=' . substr(hash('sha256', strtolower(trim($email))), 0, 16);
    }

    /**
     * @return array<string,mixed>
     */
    private function issue(string $ruleId, string $type, string $severity, string $description, string $path, string $evidence): array
    {
        return [
            'rule_id' => $ruleId,
            'type' => $type,
            'severity' => $severity,
            'status' => 'active',
            'source' => 'scanner',
            'description' => $description,
            'affected_path' => $path,
            'evidence' => $evidence,
            'threat_id' => 'sx-' . substr(sha1($ruleId . '|' . $path), 0, 29),
        ];
    }
}
