<?php
/**
 * DatabaseScannerEngine — Scans WordPress database tables for malicious content.
 *
 * BUG FIXES:
 *   - total_checked is now properly accumulated and returned
 *   - Progress transient is updated after each table (posts/options/users/usermeta)
 *     so the UI shows live progress instead of jumping 10→100%
 *   - Cancelled-scan guard checked before each table
 *
 * @package UltraguardPro\modules\database_scanner
 */

namespace UltraguardPro\modules\database_scanner;

use UltraguardPro\core\DatabaseCrud;
use UltraguardPro\core\Logger;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Database scanner must inspect live WordPress tables directly for malicious payload detection.

class DatabaseScannerEngine {

    private const STATUS_TRANSIENT = 'ultraguard_db_scan_status';

    private string $scan_id;

    private const PATTERNS = [
        'spam_links' => [
            'pattern' => '/\bhref\s*=\s*["\'][^"\']*(?:casino|pharmacy|cialis|viagra|poker|slots|gambling|xanax|tramadol)[^"\']*["\']/i',
            'level'   => 'high',
        ],
        'pharma_hack' => [
            'pattern' => '/(?:buy|cheap|discount|order)\s+(?:viagra|cialis|levitra|xanax|tramadol|ambien|phentermine)/i',
            'level'   => 'high',
        ],
        'eval_injection' => [
            'pattern' => '/eval\s*\(\s*(?:base64_decode|gzinflate|str_rot13|gzuncompress|strrev|hex2bin)/i',
            'level'   => 'critical',
        ],
        'hidden_iframe' => [
            'pattern' => '/<iframe[^>]+(?:width\s*=\s*["\']?0["\']?|height\s*=\s*["\']?0["\']?|style\s*=\s*["\'][^"\']*display\s*:\s*none)[^>]*>/i',
            'level'   => 'critical',
        ],
        'base64_payload' => [
            'pattern' => '/(?:base64_decode|eval)\s*\(\s*["\'][A-Za-z0-9+\/]{40,}={0,2}["\']\s*\)/i',
            'level'   => 'critical',
        ],
        'redirect_injection' => [
            'pattern' => '/header\s*\(\s*["\']Location\s*:/i',
            'level'   => 'high',
        ],
    ];

    public function __construct( string $scan_id = '' ) {
        $this->scan_id = $scan_id ?: ( 'dbscan_' . wp_generate_uuid4() );
    }

    public function get_scan_id(): string { return $this->scan_id; }

    // ── Orchestration ──────────────────────────────────────────────────────────

    /**
     * Run a full database scan and persist results.
     *
     * Progress milestones:
     *   10% → start / posts
     *   40% → options
     *   70% → users
     *   85% → usermeta
     *  100% → complete
     *
     * BUG FIX: total_checked is now properly accumulated across all methods.
     *
     * @return array{scan_id:string,total_checked:int,threats_found:int,tables_scanned:int}
     */
    public function scan_all(): array {
        $total_checked  = 0;
        $threats_found  = 0;
        $tables_scanned = 0;

        $scan_sections = [
            'scan_posts'    => [ 'progress' => 40, 'phase' => __( 'Scanning posts & pages…',  'ultraguard-security-suite' ) ],
            'scan_options'  => [ 'progress' => 65, 'phase' => __( 'Scanning site options…',   'ultraguard-security-suite' ) ],
            'scan_users'    => [ 'progress' => 80, 'phase' => __( 'Scanning user records…',   'ultraguard-security-suite' ) ],
            'scan_usermeta' => [ 'progress' => 92, 'phase' => __( 'Scanning user metadata…',  'ultraguard-security-suite' ) ],
        ];

        foreach ( $scan_sections as $method => $meta ) {
            // Cancelled-scan guard — check between tables
            if ( $this->is_cancelled() ) {
                Logger::info( '[DatabaseScannerEngine] Scan cancelled mid-run' );
                return [
                    'scan_id'        => $this->scan_id,
                    'total_checked'  => $total_checked,
                    'threats_found'  => $threats_found,
                    'tables_scanned' => $tables_scanned,
                ];
            }

            // Update progress before starting this table
            $this->update_progress( $meta['progress'], $meta['phase'] );

            try {
                $cursor        = 0;
                $rows_checked  = 0;
                $findings      = [];

                do {
                    $result       = $this->$method( $cursor, 200 );
                    $cursor       = (int) ( $result['next_cursor'] ?? $cursor );
                    $rows_checked += (int) ( $result['rows_checked'] ?? 0 );
                    $findings     = array_merge( $findings, (array) ( $result['findings'] ?? [] ) );
                } while ( ! empty( $result['has_more'] ) );

                $threats_found += count( $findings );
                $total_checked += $rows_checked;
                $tables_scanned++;
            } catch ( \Throwable $e ) {
                Logger::error( "[DatabaseScannerEngine] {$method} threw", [ 'error' => $e->getMessage() ] );
            }
        }

        Logger::info( '[DatabaseScannerEngine] Scan complete', [
            'scan_id'        => $this->scan_id,
            'threats_found'  => $threats_found,
            'tables_scanned' => $tables_scanned,
            'total_checked'  => $total_checked,
        ] );

        return [
            'scan_id'        => $this->scan_id,
            'total_checked'  => $total_checked,
            'threats_found'  => $threats_found,
            'tables_scanned' => $tables_scanned,
        ];
    }

    // ── Table-level scanners ───────────────────────────────────────────────────

    /**
     * Scan wp_posts: post_content, post_title, guid.
     * Returns ['findings' => array, 'rows_checked' => int].
     */
    public function scan_posts( int $cursor = 0, int $limit = 200 ): array {
        global $wpdb;

        $limit = max( 1, min( $limit, 500 ) );
        $rows  = $wpdb->get_results(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT ID, post_content, post_title, guid FROM {$wpdb->posts}
                 WHERE post_status NOT IN ('trash','auto-draft')
                   AND ID > %d
                 ORDER BY ID ASC
                 LIMIT %d",
                $cursor,
                $limit
            ),
            ARRAY_A
        );

        if ( ! $rows ) {
            return [ 'findings' => [], 'rows_checked' => 0, 'next_cursor' => $cursor, 'has_more' => false ];
        }

        $findings = [];

        foreach ( $rows as $row ) {
            $columns = [
                'post_content' => $row['post_content'] ?? '',
                'post_title'   => $row['post_title']   ?? '',
                'guid'         => $row['guid']          ?? '',
            ];

            foreach ( $columns as $col => $value ) {
                $matches = $this->scan_value( $value );
                foreach ( $matches as $match ) {
                    $finding = $this->persist_finding( $wpdb->posts, $col, (int) $row['ID'], $match, $value );
                    if ( $finding ) { $findings[] = $finding; }
                }
            }
        }

        $next_cursor = (int) ( end( $rows )['ID'] ?? $cursor );

        return [
            'findings'     => $findings,
            'rows_checked' => count( $rows ),
            'next_cursor'  => $next_cursor,
            'has_more'     => count( $rows ) === $limit,
        ];
    }

    /**
     * Scan wp_options: option_value for non-autoload options.
     */
    public function scan_options( int $cursor = 0, int $limit = 200 ): array {
        global $wpdb;

        $limit = max( 1, min( $limit, 500 ) );
        $rows  = $wpdb->get_results(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT option_id, option_name, option_value FROM {$wpdb->options}
                 WHERE option_name NOT LIKE %s
                   AND LENGTH(option_value) > 20
                   AND option_id > %d
                 ORDER BY option_id ASC
                 LIMIT %d",
                '\_%',
                $cursor,
                $limit
            ),
            ARRAY_A
        );

        if ( ! $rows ) {
            return [ 'findings' => [], 'rows_checked' => 0, 'next_cursor' => $cursor, 'has_more' => false ];
        }

        $findings = [];

        foreach ( $rows as $row ) {
            $matches = $this->scan_value( $row['option_value'] ?? '' );
            foreach ( $matches as $match ) {
                $finding = $this->persist_finding( $wpdb->options, 'option_value', (int) $row['option_id'], $match, $row['option_value'] );
                if ( $finding ) { $findings[] = $finding; }
            }
        }

        $next_cursor = (int) ( end( $rows )['option_id'] ?? $cursor );

        return [
            'findings'     => $findings,
            'rows_checked' => count( $rows ),
            'next_cursor'  => $next_cursor,
            'has_more'     => count( $rows ) === $limit,
        ];
    }

    /**
     * Scan wp_users: user_url, display_name for suspicious patterns.
     */
    public function scan_users( int $cursor = 0, int $limit = 200 ): array {
        global $wpdb;

        $limit = max( 1, min( $limit, 500 ) );
        $rows  = $wpdb->get_results(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT ID, user_login, user_email, user_url, display_name FROM {$wpdb->users}
                 WHERE ID > %d
                 ORDER BY ID ASC
                 LIMIT %d",
                $cursor,
                $limit
            ),
            ARRAY_A
        );

        if ( ! $rows ) {
            return [ 'findings' => [], 'rows_checked' => 0, 'next_cursor' => $cursor, 'has_more' => false ];
        }

        $findings = [];

        foreach ( $rows as $row ) {
            foreach ( [ 'user_url', 'display_name' ] as $col ) {
                $matches = $this->scan_value( $row[ $col ] ?? '' );
                foreach ( $matches as $match ) {
                    $finding = $this->persist_finding( $wpdb->users, $col, (int) $row['ID'], $match, $row[ $col ] );
                    if ( $finding ) { $findings[] = $finding; }
                }
            }
        }

        $next_cursor = (int) ( end( $rows )['ID'] ?? $cursor );

        return [
            'findings'     => $findings,
            'rows_checked' => count( $rows ),
            'next_cursor'  => $next_cursor,
            'has_more'     => count( $rows ) === $limit,
        ];
    }

    /**
     * Scan wp_usermeta: meta_value for suspicious content.
     */
    public function scan_usermeta( int $cursor = 0, int $limit = 200 ): array {
        global $wpdb;

        $limit = max( 1, min( $limit, 500 ) );
        $rows  = $wpdb->get_results(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT umeta_id, user_id, meta_key, meta_value FROM {$wpdb->usermeta}
                 WHERE meta_key NOT LIKE %s
                   AND LENGTH(meta_value) > 30
                   AND umeta_id > %d
                 ORDER BY umeta_id ASC
                 LIMIT %d",
                '\_%',
                $cursor,
                $limit
            ),
            ARRAY_A
        );

        if ( ! $rows ) {
            return [ 'findings' => [], 'rows_checked' => 0, 'next_cursor' => $cursor, 'has_more' => false ];
        }

        $findings = [];

        foreach ( $rows as $row ) {
            $matches = $this->scan_value( $row['meta_value'] ?? '' );
            foreach ( $matches as $match ) {
                $finding = $this->persist_finding( $wpdb->usermeta, 'meta_value', (int) $row['umeta_id'], $match, $row['meta_value'] );
                if ( $finding ) { $findings[] = $finding; }
            }
        }

        $next_cursor = (int) ( end( $rows )['umeta_id'] ?? $cursor );

        return [
            'findings'     => $findings,
            'rows_checked' => count( $rows ),
            'next_cursor'  => $next_cursor,
            'has_more'     => count( $rows ) === $limit,
        ];
    }

    // ── Pattern matching ──────────────────────────────────────────────────────

    private function scan_value( string $value ): array {
        if ( strlen( $value ) < 5 ) { return []; }

        $matches = [];
        foreach ( self::PATTERNS as $type => $def ) {
            if ( preg_match( $def['pattern'], $value, $m ) ) {
                $matches[] = [
                    'threat_type'     => $type,
                    'threat_level'    => $def['level'],
                    'matched_pattern' => mb_substr( $m[0], 0, 255 ),
                ];
            }
        }
        return $matches;
    }

    // ── Persistence ───────────────────────────────────────────────────────────

    private function persist_finding(
        string $db_table,
        string $column,
        int    $row_id,
        array  $match,
        string $full_value
    ): ?array {
        // Deduplicate within the same scan
        $exists = DatabaseCrud::record_exists( 'ultraguard_db_scan_results', [
            'scan_id'     => $this->scan_id,
            'db_table'    => $db_table,
            'column_name' => $column,
            'row_id'      => $row_id,
            'threat_type' => $match['threat_type'],
        ] );

        if ( $exists ) { return null; }

        $excerpt = mb_substr( $full_value, 0, 300 );

        $inserted = DatabaseCrud::insert_raw( 'ultraguard_db_scan_results', [
            'scan_id'         => $this->scan_id,
            'db_table'        => $db_table,
            'column_name'     => $column,
            'row_id'          => $row_id,
            'threat_type'     => $match['threat_type'],
            'threat_level'    => $match['threat_level'],
            'matched_pattern' => $match['matched_pattern'],
            'content_excerpt' => $excerpt,
            'status'          => 'pending',
        ] );

        return $inserted ? $match : null;
    }

    // ── DB injection cleaner ──────────────────────────────────────────────────

    /**
     * Replace malicious content in the specified DB row/column with a clean value.
     *
     * @param string $db_table   Full table name (e.g. wp_posts).
     * @param string $column     Column to update.
     * @param int    $row_id     Primary key value.
     * @param string $dirty_val  Original value (unused but kept for signature compat).
     * @param string $clean_val  Replacement value — empty string means strip injection.
     */
    public function clean_db_injection(
        string $db_table,
        string $column,
        int    $row_id,
        string $dirty_val,
        string $clean_val
    ): bool {
        global $wpdb;

        if ( ! $db_table || ! $column || ! $row_id ) {
            return false;
        }

        // Resolve primary-key column for the table
        $pk = $this->resolve_pk( $db_table );
        if ( ! $pk ) { return false; }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL
        $result = $wpdb->update(
            $db_table,
            [ $column => $clean_val ],
            [ $pk     => $row_id ],
            [ '%s' ],
            [ '%d' ]
        );

        if ( $result === false ) {
            Logger::error( '[DatabaseScannerEngine] clean_db_injection failed', [
                'table'  => $db_table,
                'column' => $column,
                'row_id' => $row_id,
                'error'  => $wpdb->last_error,
            ] );
            return false;
        }

        // Mark result as cleaned in our scan results table
        DatabaseCrud::update(
            'ultraguard_db_scan_results',
            [ 'status' => 'cleaned', 'cleaned_at' => current_time( 'mysql', true ) ],
            [ 'db_table' => $db_table, 'column_name' => $column, 'row_id' => $row_id ]
        );

        Logger::info( '[DatabaseScannerEngine] Injection cleaned', [
            'table'  => $db_table,
            'column' => $column,
            'row_id' => $row_id,
        ] );

        return true;
    }

    // ── Progress helpers ──────────────────────────────────────────────────────

    private function update_progress( int $pct, string $phase ): void {
        $current = get_transient( self::STATUS_TRANSIENT ) ?: [];
        set_transient( self::STATUS_TRANSIENT, array_merge( $current, [
            'status'   => 'running',
            'progress' => min( $pct, 99 ),
            'phase'    => $phase,
        ] ), HOUR_IN_SECONDS );
    }

    private function is_cancelled(): bool {
        $status = get_transient( self::STATUS_TRANSIENT );
        return ( $status['status'] ?? '' ) === 'cancelled';
    }

    // ── Utilities ─────────────────────────────────────────────────────────────

    private function resolve_pk( string $table ): ?string {
        global $wpdb;

        $map = [
            $wpdb->posts    => 'ID',
            $wpdb->options  => 'option_id',
            $wpdb->users    => 'ID',
            $wpdb->usermeta => 'umeta_id',
            $wpdb->comments => 'comment_ID',
            $wpdb->terms    => 'term_id',
        ];

        return $map[ $table ] ?? null;
    }
}
