<?php
/**
 * @file qtrDatabaseScanner.php
 * @brief Database and transients malware scanner
 *
 * Scans all WordPress database tables and transient cache entries for malware
 * using the same CQtrPatternsDatabase regex engine as the file scanner.
 *
 * Scanning runs in two sequential phases controlled by CQtrDbSnapshot:
 *  Phase 'tables'     — all tables discovered via SHOW TABLES, 50 rows/batch
 *  Phase 'transients' — wp_options _transient_* entries, 50 rows/batch
 *
 * Called from the cron handler on_qtr_scanner_db_scan_cron_event() which
 * is automatically scheduled when the file scan completes.
 *
 * @author Quttera
 */

if (!defined('ABSPATH')) { exit; }

require_once('qtrConfig.php');
require_once('qtrLogger.php');
require_once('qtrPatternsDb.php');
require_once('qtrReport.php');
require_once('qtrDbSnapshot.php');
require_once('qtrStats.php');

/** Maximum size of a single column value to scan (512 KB). Larger values are skipped. */
if (!defined('QTR_DB_MAX_COL_SIZE')) {
    define('QTR_DB_MAX_COL_SIZE', 512 * 1024);
}

/** Number of rows fetched per ScanBatch() call. */
if (!defined('QTR_DB_SCAN_BATCH_SIZE')) {
    define('QTR_DB_SCAN_BATCH_SIZE', 100);
}

class CQtrDatabaseScanner
{
    /** @var CQtrLogger */
    protected $_logger;

    /** @var CQtrPatternsDatabase */
    protected $_patterns_db;

    /** @var CQtrReport */
    protected $_report;

    /** @var CQtrConfig */
    protected $_config;

    /** @var bool */
    protected $_heuristic;

    /** @var CQtrStats */
    protected $_stats;

    public function __construct(bool $heuristic = false)
    {
        $this->_logger      = new CQtrLogger();
        $this->_patterns_db = new CQtrPatternsDatabase();
        $this->_report      = new CQtrReport();
        $this->_config      = new CQtrConfig();
        $this->_heuristic   = $heuristic;
        $this->_stats       = new CQtrStats();
    }

    /**
     * Load the malware pattern databases.
     * Loads both primary (patterns.db) and secondary (dbpatterns.db) databases.
     * Patterns accumulate with automatic deduplication to avoid duplicates.
     * Secondary database is optional.
     *
     * @return bool TRUE if primary database loaded, FALSE if primary fails
     */
    public function Initialize(): bool
    {
        // Load primary patterns database (required)
        $dbpath = dirname(__FILE__) . DIRECTORY_SEPARATOR . $this->_config->PatternsDbName();
        if (!is_file($dbpath)) {
            $this->_logger->Error('DB scanner: primary pattern database not found at ' . $dbpath);
            return false;
        }
        $rc = $this->_patterns_db->Load($dbpath);
        if (!$rc) {
            $this->_logger->Error('DB scanner: failed to load primary pattern database');
            return false;
        }
        $this->_logger->Info('DB scanner: primary pattern database loaded from ' . $dbpath);

        // Load secondary patterns database (optional)
        $secondary_dbpath = dirname(__FILE__) . DIRECTORY_SEPARATOR . $this->_config->SecondaryPatternsDbName();
        if (is_file($secondary_dbpath)) {
            $rc = $this->_patterns_db->Load($secondary_dbpath);
            if ($rc) {
                $this->_logger->Info('DB scanner: secondary pattern database loaded from ' . $secondary_dbpath);
            } else {
                $this->_logger->Warning('DB scanner: failed to load secondary pattern database from ' . $secondary_dbpath);
            }
        } else {
            $this->_logger->Info('DB scanner: secondary pattern database not found at ' . $secondary_dbpath . ' (optional)');
        }

        return true;
    }

    /**
     * Flush the report to persistent storage.
     * Call once after all batches are complete.
     */
    public function Finalize(): void
    {
        $this->_report->Finalize();
        $this->_logger->Info('DB scanner: finalized');
    }

    /**
     * Process one batch of rows from the current scan phase.
     *
     * @param CQtrDbSnapshot $snapshot Current scan state (mutated in place)
     * @return bool TRUE if more work remains, FALSE if the entire scan is done
     */
    public function ScanBatch(CQtrDbSnapshot $snapshot): bool
    {
        global $wpdb;

        if ($snapshot->GetPhase() === 'tables') {
            $table = $snapshot->CurrentTable();

            if ($table === null) {
                // All tables exhausted — move to transients phase
                $this->_logger->Info('DB scanner: all tables done, switching to transients phase');
                $snapshot->SetPhase('transients');
                return true; // more work (transients phase)
            }

            // Lazy-load column metadata for this table
            if (empty($snapshot->GetColumns())) {
                $snapshot->SetColumns($this->_GetTableColumns($table));
                $snapshot->SetPkColumn($this->_GetPrimaryKey($table));
            }

            $columns = $snapshot->GetColumns();
            if (empty($columns)) {
                // No string-type columns — skip table
                $this->_logger->Info(sprintf('DB scanner: no string columns in %s, skipping', $table));
                $snapshot->NextTable();
                return true;
            }

            // Fetch next batch of rows
            // Table name is sourced from SHOW TABLES (DB engine output, not user input).
            // Backtick-quoted to prevent identifier injection.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    'SELECT * FROM `' . $wpdb->_real_escape($table) . '` LIMIT %d OFFSET %d',
                    QTR_DB_SCAN_BATCH_SIZE,
                    $snapshot->GetOffset()
                ),
                ARRAY_A
            );

            if (empty($rows)) {
                // Table exhausted — move to next table
                $this->_logger->Info(sprintf('DB scanner: finished table %s', $table));
                $snapshot->NextTable();
                return true;
            }

            $pk_col = $snapshot->GetPkColumn();
            foreach ($rows as $row) {
                if ($this->_IsTransientEntry($table, $row)) {
                    continue;
                }
                $this->_ScanRow($table, $pk_col, $columns, $row);
            }

            $snapshot->AdvanceOffset(count($rows));
            $this->_logger->Info(sprintf(
                'DB scanner: scanned %d rows from %s (offset %d)',
                count($rows), $table, $snapshot->GetOffset()
            ));
            return true;
        }

        if ($snapshot->GetPhase() === 'transients') {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT option_name, option_value FROM {$wpdb->options}
                     WHERE option_name LIKE %s
                       AND option_name NOT LIKE %s
                     ORDER BY option_id
                     LIMIT %d OFFSET %d",
                    '_transient_%',
                    '_transient_timeout_%',
                    QTR_DB_SCAN_BATCH_SIZE,
                    $snapshot->GetOffset()
                ),
                ARRAY_A
            );

            if (empty($rows)) {
                $this->_logger->Info('DB scanner: transients phase complete');
                return false; // FULLY DONE
            }

            foreach ($rows as $row) {
                $this->_ScanTransient($row['option_name'], $row['option_value']);
            }

            $snapshot->AdvanceOffset(count($rows));
            $this->_logger->Info(sprintf(
                'DB scanner: scanned %d transients (offset %d)',
                count($rows), $snapshot->GetOffset()
            ));
            return true;
        }

        // Unknown phase — treat as done
        $this->_logger->Error('DB scanner: unknown phase "' . $snapshot->GetPhase() . '"');
        return false;
    }

    /* =========================================================================
     * PRIVATE HELPERS
     * ====================================================================== */

    /**
     * Check if a row from wp_options table is a transient entry.
     *
     * Transient entries are skipped during the 'tables' phase because they're
     * separately scanned with the transients phase, avoiding duplicate scanning.
     *
     * @param string $table Table name
     * @param array  $row   Row data with option_name key
     * @return bool TRUE if this is a transient entry to skip
     */
    private function _IsTransientEntry(string $table, array $row): bool
    {
        global $wpdb;
        return $table === $wpdb->options
            && isset($row['option_name'])
            && strpos($row['option_name'], '_transient_') === 0;
    }

    /**
     * Return the string-type column names for a table via SHOW COLUMNS.
     *
     * @param string $table Table name (from SHOW TABLES — not user input)
     * @return array Array of column name strings
     */
    private function _GetTableColumns(string $table): array
    {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows = $wpdb->get_results(
            'SHOW COLUMNS FROM `' . $wpdb->_real_escape($table) . '`',
            ARRAY_A
        );

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

        $string_types = ['char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext'];
        $columns = [];
        foreach ($rows as $row) {
            $type = strtolower($row['Type'] ?? '');
            foreach ($string_types as $st) {
                if (strpos($type, $st) !== false) {
                    $columns[] = $row['Field'];
                    break;
                }
            }
        }

        return $columns;
    }

    /**
     * Return the primary key column name for a table, or NULL if none.
     *
     * @param string $table Table name (from SHOW TABLES — not user input)
     * @return string|null
     */
    private function _GetPrimaryKey(string $table): ?string
    {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $row = $wpdb->get_row(
            'SHOW KEYS FROM `' . $wpdb->_real_escape($table) . "` WHERE Key_name = 'PRIMARY'",
            ARRAY_A
        );

        return $row['Column_name'] ?? null;
    }

    /**
     * Scan all string columns of a single database row.
     *
     * Merges all column values into a single string and performs one pattern scan.
     * Reports matches at row level only (table and row index).
     * Tracks row severity and updates statistics.
     *
     * @param string      $table   Table name
     * @param string|null $pk_col  Primary key column name (for row identification)
     * @param array       $columns String-type column names to scan
     * @param array       $row     Associative array of column => value
     */
    private function _ScanRow(string $table, ?string $pk_col, array $columns, array $row): void
    {
        global $wpdb;

        // Skip the scanner's own report option to avoid self-detection false positives.
        if ( $table === $wpdb->options
            && isset($row['option_name'])
            && $row['option_name'] === QTR_REPORT ) {
            $this->_logger->Info('DB scanner: skipping scanner report option row (self-exclusion)');
            $this->_stats->IncRowsClean();
            return;
        }

        $row_id = ($pk_col !== null && isset($row[$pk_col])) ? $row[$pk_col] : 'unknown';

        // Merge all column values into single string
        $merged_value = '';
        foreach ($columns as $col) {
            $value = $row[$col] ?? null;
            if ($value !== null && $value !== '') {
                $merged_value .= (string)$value . "\n";
            }
        }

        // Skip if no data to scan
        if (strlen($merged_value) === 0) {
            $this->_stats->IncRowsClean();
            return;
        }

        // Single scan for entire row
        $row_severity = $this->_ScanValue($merged_value, $table, "row={$row_id}");
        $this->_stats->IncrementRows($row_severity);
    }

    /**
     * Scan a single transient value.
     *
     * Transients are treated as individual rows in statistics.
     * Performs single pattern scan and reports at transient level.
     *
     * @param string $option_name  The wp_options option_name (e.g. '_transient_my_key')
     * @param string $option_value The stored value
     */
    private function _ScanTransient(string $option_name, string $option_value): void
    {
        global $wpdb;
        // Strip the _transient_ prefix to get the readable key
        $key = substr($option_name, strlen('_transient_'));
        $severity = $this->_ScanValue($option_value, $wpdb->options, "transient={$key}");
        
        // Track transient as a row in statistics
        $this->_stats->IncrementRows($severity);
    }

    /**
     * Scan a merged row value or transient — the core matching logic.
     *
     * Steps:
     *  1. Skip empty or oversized values
     *  2. Attempt PHP unserialize / JSON decode to extract nested strings
     *  3. Run all extracted strings through CQtrPatternsDatabase::ScanString()
     *  4. Report any matches via CQtrReport::AddDbReport()
     *
     * @param string $raw     Raw row/transient value to scan (may contain multiple columns merged)
     * @param string $table   Table name (for reporting)
     * @param string $context Human-readable context string (row={$row_id} or transient={$key})
     * @return string Worst severity found ('clean', 'suspicious', 'pot_suspicious', 'malicious')
     */
    private function _ScanValue(string $raw, string $table, string $context): string
    {
        if (strlen($raw) === 0) {
            return 'clean';
        }

        if (strlen($raw) > QTR_DB_MAX_COL_SIZE) {
            $this->_logger->Warning(sprintf(
                'DB scanner: skipping oversized value (%d bytes) at %s / %s',
                strlen($raw), $table, $context
            ));
            return 'clean';
        }

        // Build list of strings to test — always include the raw value
        $strings = [$raw];

        // Attempt PHP deserialization (safe — allowed_classes => false prevents object injection)
        if (preg_match('/^[aOsibd]:/', $raw)) {
            $decoded = @unserialize($raw, ['allowed_classes' => false]);
            if ($decoded !== false) {
                $strings = array_merge($strings, $this->_ExtractStrings($decoded));
            }
        } elseif (strlen($raw) > 0 && ($raw[0] === '{' || $raw[0] === '[')) {
            // Attempt JSON decode
            $decoded = json_decode($raw, true);
            if ($decoded !== null) {
                $strings = array_merge($strings, $this->_ExtractStrings($decoded));
            }
        }

        // Deduplicate to avoid duplicate reports for identical strings
        $strings = array_unique($strings);

        $engine = $this->_heuristic ? 'DB Heuristic Malware Scanner' : 'DB Malware Scanner';
        $worst_severity = 'clean';

        foreach ($strings as $str) {
            $matches = $this->_patterns_db->ScanString($str, $this->_heuristic);
            if ($matches === null) {
                continue;
            }

            foreach ($matches as $match) {
                $pattern = $match[0];
                $matched = $match[1];
                $severity = $pattern->severity();

                // Track worst severity found
                if (strpos(strtolower($severity), 'malicious') !== false) {
                    $worst_severity = 'malicious';
                } elseif (strpos(strtolower($severity), 'pot') !== false && $worst_severity !== 'malicious') {
                    $worst_severity = 'pot_suspicious';
                } elseif (strpos(strtolower($severity), 'susp') !== false && $worst_severity === 'clean') {
                    $worst_severity = 'suspicious';
                }

                $this->_logger->Info(sprintf(
                    '%s: match [%s] in %s / %s: matched "%s"',
                    $engine, $pattern->name(), $table, $context, $matched
                ));

                $this->_report->AddDbReport(
                    $engine,
                    $severity,
                    DB_NAME,
                    $table,
                    $context,
                    $pattern->name(),
                    md5($pattern->name()),
                    $matched
                );
            }
        }

        return $worst_severity;
    }

    /**
     * Recursively extract all string leaf values from a mixed (array/scalar) value.
     *
     * @param mixed $data Decoded PHP value (array, string, int, etc.)
     * @return array Flat list of string values found
     */
    private function _ExtractStrings($data): array
    {
        if (is_string($data)) {
            return [$data];
        }
        if (is_array($data)) {
            $out = [];
            foreach ($data as $v) {
                $out = array_merge($out, $this->_ExtractStrings($v));
            }
            return $out;
        }
        return [];
    }
}
