<?php

declare(strict_types=1);

namespace SiteFort\Modules\Scanner\Repository;

use SiteFort\Modules\Scanner\Service\IssueType;
use SiteFort\Modules\Scanner\Repository\IssueDisplayContractBuilder;
use SiteFort\Modules\Scanner\Repository\IssueUIGroupingService;

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

/**
 * Persists scanner findings and issue display state.
 */
class IssueRepository {

    /**
     * Ensure database tables exist.
     */
    public static function ensure_tables() {
        global $wpdb;
        $charset_collate = \SiteFort\Core\Database\SchemaInspector::validated_charset_collate( $wpdb );

        // Main Issues Table
        $issues_table = $wpdb->prefix . 'sitefort_issues';
        $sql_issues = "CREATE TABLE $issues_table (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            issue_hash varchar(64) NOT NULL,
            scan_id varchar(64) DEFAULT NULL,
            type varchar(50) NOT NULL,
            severity varchar(20) DEFAULT 'medium',
            status varchar(50) NOT NULL,
            file_path text DEFAULT NULL,
            message text DEFAULT NULL,
            data longtext DEFAULT NULL,
            is_ignored tinyint(1) DEFAULT 0,
            
            resolved_at int(11) NOT NULL DEFAULT 0,
            first_seen_at int(11) NOT NULL DEFAULT 0,
            last_seen_at int(11) NOT NULL DEFAULT 0,
            created_at int(11) NOT NULL,
            updated_at int(11) NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY issue_hash (issue_hash),
            KEY scan_id (scan_id),
            KEY type (type),
            KEY is_ignored (is_ignored),
            KEY idx_active (is_ignored, resolved_at)
        ) $charset_collate;";

        // Ignored Issues Table (Simplified)
        $ignored_table = $wpdb->prefix . 'sitefort_ignored_issues';
        $sql_ignored = "CREATE TABLE $ignored_table (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            issue_hash varchar(64) NOT NULL,
            ignored_at int(11) NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY issue_hash (issue_hash)
        ) $charset_collate;";

        // Scan Runs (Immutable Execution History)
        $runs_table = $wpdb->prefix . 'sitefort_scan_runs';
        $sql_runs = "CREATE TABLE $runs_table (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            scan_id varchar(64) NOT NULL,
            config longtext DEFAULT NULL,
            component_status longtext DEFAULT NULL,
            started_at int(11) NOT NULL,
            finished_at int(11) DEFAULT 0,
            PRIMARY KEY  (id),
            UNIQUE KEY scan_id (scan_id)
        ) $charset_collate;";

        // Issue Instances (Immutable Occurrence History + Staging)
        $instances_table = $wpdb->prefix . 'sitefort_issue_instances';
        $sql_instances = "CREATE TABLE $instances_table (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            issue_hash varchar(64) NOT NULL,
            scan_run_id varchar(64) NOT NULL,
            type varchar(50) DEFAULT NULL,
            severity varchar(20) DEFAULT NULL,
            status varchar(50) DEFAULT NULL,
            file_path text DEFAULT NULL,
            message text DEFAULT NULL,
            evidence longtext DEFAULT NULL,
            created_at int(11) NOT NULL,
            PRIMARY KEY  (id),
            KEY issue_ref (issue_hash, scan_run_id)
        ) $charset_collate;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql_issues );
        dbDelta( $sql_ignored );
        dbDelta( $sql_runs );
        dbDelta( $sql_instances );
        foreach ( [ $issues_table, $ignored_table, $runs_table, $instances_table ] as $table ) {
            \SiteFort\Core\Database\SchemaInspector::flush_cache( $table );
            \SiteFort\Core\Database\SchemaInspector::ensure_table_uses_innodb( $table );
        }

        $issues_identifier    = self::table_identifier( $issues_table );
        $instances_identifier = self::table_identifier( $instances_table );

        // Fallback: Ensure new columns exist (dbDelta can be finicky with column additions)
        $row = self::get_results_prepared( "SHOW COLUMNS FROM {$issues_identifier} LIKE %s", 'first_seen_at' );
        if ( empty( $row ) ) {
            self::query_literal( "ALTER TABLE {$issues_identifier} ADD COLUMN first_seen_at int(11) NOT NULL DEFAULT 0 AFTER is_ignored" );
        }
        $row = self::get_results_prepared( "SHOW COLUMNS FROM {$issues_identifier} LIKE %s", 'last_seen_at' );
        if ( empty( $row ) ) {
            self::query_literal( "ALTER TABLE {$issues_identifier} ADD COLUMN last_seen_at int(11) NOT NULL DEFAULT 0 AFTER first_seen_at" );
        }
        $row = self::get_results_prepared( "SHOW COLUMNS FROM {$issues_identifier} LIKE %s", 'resolved_at' );
        if ( empty( $row ) ) {
            self::query_literal( "ALTER TABLE {$issues_identifier} ADD COLUMN resolved_at int(11) NOT NULL DEFAULT 0 AFTER is_ignored" );
        }

        // Ensure instances table has full metadata columns (hardcoded safe whitelist).
        $cols = [ 'type', 'severity', 'status', 'file_path', 'message' ];
        foreach ( $cols as $col ) {
            $row = self::get_results_prepared( "SHOW COLUMNS FROM {$instances_identifier} LIKE %s", $col );
            if ( empty( $row ) ) {
                $col_type = ( $col === 'file_path' || $col === 'message' ) ? 'text' : 'varchar(50)';
                self::query_literal( "ALTER TABLE {$instances_identifier} ADD COLUMN `{$col}` {$col_type} DEFAULT NULL" );
            }
        }
    }

    /**
     * Generate a stable issue hash.
     *
     * @param string $type Issue type.
     * @param string $stable_key Stable identifier.
     * @return string 64-character hex hash.
     */
    public static function generateHash( $type, $stable_key ) {
        $input = strtolower( $type ) . ':' . $stable_key;
        return hash( 'sha256', $input );
    }

    /**
     * Create a scan run record.
     */
    public static function createScanRun( $scan_id, $config, $started_at ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_scan_runs';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Scan run insert.
        return $wpdb->insert( $table, [
            'scan_id'    => $scan_id,
            'config'     => wp_json_encode( $config ),
            'started_at' => $started_at
        ] );
    }
    
    /**
     * Stage an issue finding (Write to Instances/Staging).
     * Does NOT update the main active issues table.
     * 
     * @param array $issue Issue data
     */
    public static function stage( $issue ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issue_instances';
        
        $hash = $issue['hash'] ?? '';
        if ( empty( $hash ) ) return false;
        $scan_run_id = isset( $issue['scan_id'] ) ? (string) $issue['scan_id'] : 'unknown';

        $data = [
            'issue_hash'  => $hash,
            'scan_run_id' => $scan_run_id,
            'type'        => $issue['type'] ?? 'unknown',
            'severity'    => $issue['severity'] ?? 'medium',
            'status'      => $issue['status'] ?? 'unknown',
            'file_path'   => $issue['file'] ?? $issue['file_path'] ?? null,
            'message'     => $issue['message'] ?? null,
            'evidence'    => isset( $issue['data'] ) ? wp_json_encode( $issue['data'] ) : null,
            'created_at'  => time()
        ];

        $existing = self::get_row_prepared(
            "SELECT * FROM " . self::table_identifier( $table ) . " WHERE issue_hash = %s AND scan_run_id = %s ORDER BY id DESC LIMIT 1",
            $hash,
            $scan_run_id
        );

        if ( is_array( $existing ) ) {
            $merged = self::merge_staged_issue_payload( $existing, $issue );

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Staged issue update keeps one mutable row per hash within a scan run.
            return false !== $wpdb->update(
                $table,
                [
                    'type'      => $merged['type'] ?? $data['type'],
                    'severity'  => $merged['severity'] ?? $data['severity'],
                    'status'    => $merged['status'] ?? $data['status'],
                    'file_path' => $merged['file'] ?? $merged['file_path'] ?? $data['file_path'],
                    'message'   => $merged['message'] ?? $data['message'],
                    'evidence'  => isset( $merged['data'] ) ? wp_json_encode( $merged['data'] ) : $data['evidence'],
                ],
                [ 'id' => (int) $existing['id'] ],
                [ '%s', '%s', '%s', '%s', '%s', '%s' ],
                [ '%d' ]
            );
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Staged issue insert.
        return $wpdb->insert( $table, $data );
    }

    /**
     * Merge a repeated staged finding for the same scan/hash into one canonical row.
     *
     * @param array $existing_row
     * @param array $incoming_issue
     * @return array
     */
    private static function merge_staged_issue_payload( array $existing_row, array $incoming_issue ): array {
        $existing_data = [];
        if ( isset( $existing_row['evidence'] ) && is_string( $existing_row['evidence'] ) && $existing_row['evidence'] !== '' ) {
            $decoded = json_decode( $existing_row['evidence'], true );
            if ( is_array( $decoded ) ) {
                $existing_data = $decoded;
            }
        }

        $existing_issue = [
            'type'     => (string) ( $existing_row['type'] ?? 'unknown' ),
            'severity' => (string) ( $existing_row['severity'] ?? 'medium' ),
            'status'   => (string) ( $existing_row['status'] ?? 'unknown' ),
            'file'     => $existing_row['file_path'] ?? null,
            'message'  => $existing_row['message'] ?? null,
            'data'     => $existing_data,
        ];

        if ( ( $existing_issue['type'] ?? '' ) !== 'user' || ( $incoming_issue['type'] ?? '' ) !== 'user' ) {
            return $incoming_issue;
        }

        $preferred = self::select_preferred_user_issue( $existing_issue, $incoming_issue );
        $secondary = $preferred === $existing_issue ? $incoming_issue : $existing_issue;
        $preferred_data = isset( $preferred['data'] ) && is_array( $preferred['data'] ) ? $preferred['data'] : [];
        $secondary_data = isset( $secondary['data'] ) && is_array( $secondary['data'] ) ? $secondary['data'] : [];

        $preferred['data'] = self::merge_user_issue_data( $preferred_data, $secondary_data );
        return $preferred;
    }

    /**
     * Choose the stronger user-account issue when multiple detectors hit the same account.
     *
     * @param array $first
     * @param array $second
     * @return array
     */
    private static function select_preferred_user_issue( array $first, array $second ): array {
        $severity_rank = [
            'low'      => 0,
            'medium'   => 1,
            'high'     => 2,
            'critical' => 3,
        ];
        $reason_rank = [
            'privileged_creation_event_anomaly'    => 1000,
            'hidden_privileged_user'               => 900,
            'shadow_manage_capability'             => 850,
            'pattern_and_meta'                     => 800,
            'missing_meta'                         => 780,
            'malformed_privileged_capability_meta' => 750,
            'inconsistent_privileged_user_level'   => 730,
            'weak_password'                        => 720,
            'pattern_match'                        => 700,
        ];

        $first_reason = sanitize_key( (string) ( $first['data']['reason'] ?? '' ) );
        $second_reason = sanitize_key( (string) ( $second['data']['reason'] ?? '' ) );
        $first_score = ( $severity_rank[ strtolower( (string) ( $first['severity'] ?? 'low' ) ) ] ?? -1 ) * 10000
            + ( $reason_rank[ $first_reason ] ?? 0 );
        $second_score = ( $severity_rank[ strtolower( (string) ( $second['severity'] ?? 'low' ) ) ] ?? -1 ) * 10000
            + ( $reason_rank[ $second_reason ] ?? 0 );

        return $first_score >= $second_score ? $first : $second;
    }

    /**
     * Merge supplemental user-account context from multiple detectors.
     *
     * @param array $primary
     * @param array $secondary
     * @return array
     */
    private static function merge_user_issue_data( array $primary, array $secondary ): array {
        $merged = $primary;

        foreach ( [ 'user_id', 'user_login', 'user_email', 'blog_id', 'registered', 'expected_user_level', 'actual_user_level' ] as $key ) {
            if ( ! isset( $merged[ $key ] ) || $merged[ $key ] === '' || $merged[ $key ] === null ) {
                if ( isset( $secondary[ $key ] ) && $secondary[ $key ] !== '' && $secondary[ $key ] !== null ) {
                    $merged[ $key ] = $secondary[ $key ];
                }
            }
        }

        foreach ( [ 'roles', 'direct_caps', 'privilege_sources', 'missing_profile_meta', 'related_reasons', 'suppressed_reasons' ] as $list_key ) {
            $merged[ $list_key ] = self::merge_string_lists( $primary[ $list_key ] ?? [], $secondary[ $list_key ] ?? [] );
        }

        $primary_reason = sanitize_key( (string) ( $primary['reason'] ?? '' ) );
        $secondary_reason = sanitize_key( (string) ( $secondary['reason'] ?? '' ) );
        $merged['related_reasons'] = self::merge_string_lists(
            $merged['related_reasons'] ?? [],
            array_filter( [ $primary_reason, $secondary_reason ] )
        );
        $merged['suppressed_reasons'] = self::merge_string_lists(
            $merged['suppressed_reasons'] ?? [],
            array_filter( [ $secondary_reason ] )
        );

        $primary_evidence = isset( $primary['evidence'] ) && is_array( $primary['evidence'] ) ? $primary['evidence'] : [];
        $secondary_evidence = isset( $secondary['evidence'] ) && is_array( $secondary['evidence'] ) ? $secondary['evidence'] : [];
        $merged['evidence'] = self::merge_user_issue_evidence( $primary_evidence, $secondary_evidence );

        if ( empty( $merged['event'] ) && ! empty( $secondary['event'] ) ) {
            $merged['event'] = $secondary['event'];
        }

        return $merged;
    }

    /**
     * Merge nested evidence arrays for user-account findings.
     *
     * @param array $primary
     * @param array $secondary
     * @return array
     */
    private static function merge_user_issue_evidence( array $primary, array $secondary ): array {
        $merged = $primary;

        foreach ( $secondary as $key => $value ) {
            if ( ! array_key_exists( $key, $merged ) || $merged[ $key ] === '' || $merged[ $key ] === null ) {
                $merged[ $key ] = $value;
                continue;
            }

            if ( is_array( $merged[ $key ] ) && is_array( $value ) ) {
                $merged[ $key ] = self::merge_string_lists( $merged[ $key ], $value );
            }
        }

        return $merged;
    }

    /**
     * Merge string lists while preserving input order.
     *
     * @param mixed $first
     * @param mixed $second
     * @return array<int,string>
     */
    private static function merge_string_lists( $first, $second ): array {
        $values = [];
        foreach ( [ $first, $second ] as $list ) {
            if ( ! is_array( $list ) ) {
                continue;
            }

            foreach ( $list as $item ) {
                if ( ! is_string( $item ) ) {
                    continue;
                }

                $candidate = trim( $item );
                if ( $candidate === '' || in_array( $candidate, $values, true ) ) {
                    continue;
                }

                $values[] = $candidate;
            }
        }

        return $values;
    }

    /**
     * Commit a staged scan to the main issues table.
     *
     * @param string $scan_id
     */
    public static function commit( $scan_id ) {
        global $wpdb;
        $t_issues = $wpdb->prefix . 'sitefort_issues';
        $t_instances = $wpdb->prefix . 'sitefort_issue_instances';
        $t_ignored = $wpdb->prefix . 'sitefort_ignored_issues';
        $issues_identifier = self::table_identifier( $t_issues );
        $instances_identifier = self::table_identifier( $t_instances );
        $ignored_identifier = self::table_identifier( $t_ignored );

        // Count staged instances before committing (for validation).
        $staged_count = (int) self::get_var_prepared(
            "SELECT COUNT(*) FROM {$instances_identifier} WHERE scan_run_id = %s",
            $scan_id
        );

        if ( $staged_count === 0 ) {
            return 0;
        }

        // Upsert staged instances into the active issues table.

        $sql = "INSERT INTO {$issues_identifier}
                (issue_hash, scan_id, type, severity, status, file_path, message, data, is_ignored, created_at, updated_at, first_seen_at, last_seen_at, resolved_at)
                SELECT
                    staged.issue_hash,
                    staged.scan_run_id,
                    staged.type,
                    staged.severity,
                    staged.status,
                    staged.file_path,
                    staged.message,
                    staged.evidence,
                    CASE WHEN ignored.issue_hash IS NULL THEN 0 ELSE 1 END,
                    staged.created_at,
                    staged.created_at,
                    staged.created_at, -- first_seen default
                    staged.created_at, -- last_seen default
                    0 -- resolved_at
                FROM {$instances_identifier} staged
                LEFT JOIN {$ignored_identifier} ignored
                    ON ignored.issue_hash = staged.issue_hash
                WHERE staged.scan_run_id = %s
                ON DUPLICATE KEY UPDATE
                    scan_id = VALUES(scan_id),
                    is_ignored = VALUES(is_ignored),
                    last_seen_at = VALUES(last_seen_at),
                    updated_at = VALUES(updated_at),
                    resolved_at = 0, -- Mark Active
                    -- Update metadata in case it changed
                    type = VALUES(type),
                    severity = VALUES(severity),
                    status = VALUES(status),
                    message = VALUES(message),
                    data = VALUES(data)";

        $result = self::query_prepared( $sql, $scan_id );

        if ( $result === false ) {
            sitefort_log( sprintf(
                '[SiteFort] IssueRepository::commit() SQL FAILED for scan_id=%s. staged=%d, error=%s',
                $scan_id,
                $staged_count,
                $wpdb->last_error
            ) );
            return false;
        }

        return $result;
    }

    /**
     * Normalize file-skipped preservation options for stale cleanup.
     *
     * @param array<int,string> $safe_types Current publish type scope.
     * @param array             $options    Publish options.
     * @return array{types:array<int,string>,skip_reasons:array<int,string>}
     */
    private static function normalize_skipped_file_preservation_options( array $safe_types, array $options ): array {
        $raw = is_array( $options['preserve_skipped_file_findings'] ?? null )
            ? $options['preserve_skipped_file_findings']
            : [];

        $types = array_values(
            array_unique(
                array_filter(
                    (array) ( $raw['types'] ?? [] ),
                    static function ( $type ): bool {
                        return is_string( $type ) && preg_match( '/^[a-z_]+$/', $type );
                    }
                )
            )
        );

        if ( ! empty( $safe_types ) ) {
            $types = array_values( array_intersect( $types, $safe_types ) );
        }

        $skip_reasons = array_values(
            array_unique(
                array_filter(
                    (array) ( $raw['skip_reasons'] ?? [] ),
                    static function ( $reason ): bool {
                        return is_string( $reason ) && preg_match( '/^[a-z0-9_]+$/', $reason );
                    }
                )
            )
        );

        return [
            'types'        => $types,
            'skip_reasons' => $skip_reasons,
        ];
    }

    /**
     * Publish a staged scan.
     *
     * @param string $scan_id
     * @param array  $types   Optional types to limit the publish scope.
     * @param array  $options Optional publish behavior flags.
     * @return bool
     */
    public static function publish( $scan_id, $types = [], array $options = [] ) {
        if ( ! $scan_id ) {
            return false;
        }

        global $wpdb;
        $issues_table = $wpdb->prefix . 'sitefort_issues';
        $instances_table = $wpdb->prefix . 'sitefort_issue_instances';
        $file_states_table = $wpdb->prefix . 'sitefort_file_states';

        $safe_types = [];
        if ( ! empty( $types ) ) {
            $safe_types = array_values( array_filter( $types, function ( $type ) {
                return is_string( $type ) && preg_match( '/^[a-z_]+$/', $type );
            } ) );

            // Abort if a scoped publish request contains no valid types.
            if ( empty( $safe_types ) ) {
                sitefort_log( sprintf(
                    '[SiteFort] publish() aborted: invalid type scope for scan_id=%s, raw_types=%s',
                    $scan_id,
                    wp_json_encode( $types )
                ) );
                return false;
            }
        }

        $skipped_file_preservation = self::normalize_skipped_file_preservation_options( $safe_types, $options );

        $staged_count = (int) self::get_var_prepared(
            "SELECT COUNT(*) FROM " . self::table_identifier( $instances_table ) . " WHERE scan_run_id = %s",
            $scan_id
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Transaction control.
        $wpdb->query( 'START TRANSACTION' );

        // 1) Promote staged findings to active issues.
        $commit_result = self::commit( $scan_id );
        if ( $commit_result === false ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Transaction control.
            $wpdb->query( 'ROLLBACK' );
            sitefort_log( sprintf(
                '[SiteFort] publish() rollback: commit failed for scan_id=%s, staged=%d, types=%s',
                $scan_id,
                $staged_count,
                wp_json_encode( $safe_types )
            ) );
            return false;
        }

        // 2) Remove stale unresolved findings from previous scans in the scanned scope.
        $issues_identifier = self::table_identifier( $issues_table );
        $file_states_identifier = self::table_identifier( $file_states_table );
        $issue_alias = 'active_issues';

        $where = "{$issue_alias}.resolved_at = 0 AND ({$issue_alias}.scan_id != %s OR {$issue_alias}.scan_id IS NULL)";
        $params = [ $scan_id ];

        if ( ! empty( $safe_types ) ) {
            $placeholders = implode( ',', array_fill( 0, count( $safe_types ), '%s' ) );
            $where .= " AND {$issue_alias}.type IN ($placeholders)";
            $params = array_merge( $params, $safe_types );
        }

        if ( ! empty( $skipped_file_preservation['types'] ) && ! empty( $skipped_file_preservation['skip_reasons'] ) ) {
            $type_placeholders = implode( ',', array_fill( 0, count( $skipped_file_preservation['types'] ), '%s' ) );
            $reason_placeholders = implode( ',', array_fill( 0, count( $skipped_file_preservation['skip_reasons'] ), '%s' ) );
            $where .= " AND NOT (
                {$issue_alias}.type IN ($type_placeholders)
                AND {$issue_alias}.file_path IS NOT NULL
                AND {$issue_alias}.file_path <> ''
                AND EXISTS (
                    SELECT 1
                    FROM {$file_states_identifier} skipped_files
                    WHERE skipped_files.scan_id = %s
                    AND skipped_files.skip_reason IN ($reason_placeholders)
                    AND skipped_files.file_path = {$issue_alias}.file_path
                    LIMIT 1
                )
            )";
            $params = array_merge(
                $params,
                $skipped_file_preservation['types'],
                [ $scan_id ],
                $skipped_file_preservation['skip_reasons']
            );
        }

        $delete_result = self::query_prepared(
            "DELETE {$issue_alias} FROM {$issues_identifier} {$issue_alias} WHERE {$where}",
            ...$params
        );
        if ( $delete_result === false ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Transaction control.
            $wpdb->query( 'ROLLBACK' );
            sitefort_log( sprintf(
                '[SiteFort] publish() rollback: stale cleanup failed for scan_id=%s, types=%s, db_error=%s',
                $scan_id,
                wp_json_encode( $safe_types ),
                $wpdb->last_error
            ) );
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Transaction control.
        $wpdb->query( 'COMMIT' );

        // 3) Publish pointer only after successful commit+swap.
        \SiteFort\Includes\SettingsStore::set_field(
            \SiteFort\Includes\SettingsStore::COL_STATE,
            'published_scan_id',
            $scan_id
        );

        // 4) Keep only the latest scan artifacts for normal operation.
        self::pruneInstances( $scan_id );
        self::pruneScanRuns( $scan_id );

        return true;
    }

    /**
     * Discard staged instances for a scan (used for stop/fail paths).
     */
    public static function discardStaged( $scan_id ) {
        if ( ! $scan_id ) return 0;
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issue_instances';
        return (int) self::query_prepared(
            "DELETE FROM " . self::table_identifier( $table ) . " WHERE scan_run_id = %s",
            $scan_id
        );
    }

    /**
     * Delete a scan run record (used when a scan is stopped/aborted with no findings).
     */
    public static function discardScanRun( $scan_id ) {
        if ( ! $scan_id ) return 0;
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_scan_runs';
        return (int) self::query_prepared(
            "DELETE FROM " . self::table_identifier( $table ) . " WHERE scan_id = %s",
            $scan_id
        );
    }

    /**
     * Update the scan run with frozen component_status and finished_at timestamp.
     * Called when a scan completes, stops, or fails to freeze the state.
     *
     * @param string $scan_id
     * @param array $component_status Frozen status map [slug => status]
     * @param string $final_status 'complete', 'stopped', or 'failed'
     */
    public static function updateScanRunStatus( $scan_id, $component_status, $final_status = 'complete' ) {
        if ( ! $scan_id ) return false;
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_scan_runs';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Updates the plugin-owned scan-run row with its frozen completion state.
        return $wpdb->update(
            $table,
            [
                'component_status' => wp_json_encode( $component_status ),
                'finished_at'      => time(),
            ],
            [ 'scan_id' => $scan_id ],
            [ '%s', '%d' ],
            [ '%s' ]
        );
    }

    /**
     * Get a scan run record by scan_id.
     *
     * @param string $scan_id
     * @return array|null
     */
    public static function getScanRun( $scan_id ) {
        if ( ! $scan_id ) return null;
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_scan_runs';

        $row = self::get_row_prepared(
            "SELECT * FROM " . self::table_identifier( $table ) . " WHERE scan_id = %s",
            $scan_id
        );

        if ( $row && ! empty( $row['component_status'] ) ) {
            $decoded = json_decode( $row['component_status'], true );
            $row['component_status'] = is_array( $decoded ) ? $decoded : [];
        }

        return $row ?: null;
    }

    /**
     * Keep instances for the published scan, delete everything else.
     */
    private static function pruneInstances( $keep_scan_id ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issue_instances';
        self::query_prepared(
            "DELETE FROM " . self::table_identifier( $table ) . " WHERE scan_run_id != %s",
            $keep_scan_id
        );
    }

    /**
     * Keep the published scan run, delete everything else.
     */
    private static function pruneScanRuns( $keep_scan_id ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_scan_runs';
        self::query_prepared(
            "DELETE FROM " . self::table_identifier( $table ) . " WHERE scan_id != %s",
            $keep_scan_id
        );
    }

    /**
     * Retrieve current unresolved issues across scan runs.
     * Includes ignored and non-ignored records so UI tabs can split client-side.
     *
     * @param int|null $limit
     * @param int $offset
     * @return array
     */
    public static function getCurrentIssues( $limit = null, $offset = 0, $group_for_ui = false ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $query = "SELECT * FROM " . self::table_identifier( $table ) . " WHERE resolved_at = 0 ORDER BY updated_at DESC";
        $params = [];

        $effective_limit = $limit ?: 5000;
        if ( $group_for_ui ) {
            return self::fetch_grouped_issue_page(
                $query,
                $params,
                $effective_limit,
                $offset,
                static function ( array $row ): array {
                    return self::enrichIssue( $row );
                }
            );
        } else {
            $query .= ' LIMIT %d OFFSET %d';
            $params[] = $effective_limit;
            $params[] = $offset;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
        $rows = self::get_results_prepared( $query, ...$params );
        if ( empty( $rows ) ) {
            return [];
        }

        $issues = array_map( [ self::class, 'enrichIssue' ], $rows );
        if ( $group_for_ui ) {
            return IssueUIGroupingService::group_and_slice( $issues, $effective_limit, $offset );
        }

        return $issues;
    }

    public static function getCurrentIssuesByType( string $type, int $limit = 500, int $offset = 0 ): array {
        $type = sanitize_key( $type );
        if ( $type === '' || $limit < 1 ) {
            return [];
        }

        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $rows = self::get_results_prepared(
            "SELECT * FROM " . self::table_identifier( $table ) . " WHERE resolved_at = 0 AND type = %s ORDER BY updated_at DESC LIMIT %d OFFSET %d",
            $type,
            $limit,
            max( 0, $offset )
        );

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

        return array_map( [ self::class, 'enrichIssue' ], $rows );
    }

    /**
     * Retrieve staged issues for a running scan.
     * 
     * @param string $scan_id
     * @param int|null $limit
     * @param int $offset
     * @return array
     */
    public static function getStagedIssues( $scan_id, $limit = null, $offset = 0, $group_for_ui = false ) {
        global $wpdb;
        $t_instances = $wpdb->prefix . 'sitefort_issue_instances';
        $t_issues = $wpdb->prefix . 'sitefort_issues';
        
        $sql = "SELECT i.*, m.is_ignored 
             FROM " . self::table_identifier( $t_instances ) . " i
             LEFT JOIN " . self::table_identifier( $t_issues ) . " m ON i.issue_hash = m.issue_hash
             WHERE i.scan_run_id = %s";
             
        $params = [ $scan_id ];

        if ( $group_for_ui ) {
            $effective_limit = $limit ?: 5000;
            return self::fetch_grouped_issue_page(
                $sql,
                $params,
                $effective_limit,
                $offset,
                static function ( array $row ): array {
                    $row['file_path'] = $row['file_path'] ?? $row['id'];
                    $row['data'] = $row['evidence'] ?? null;
                    $row['is_ignored'] = isset($row['is_ignored']) ? (int)$row['is_ignored'] : 0;
                    return self::enrichIssue( $row );
                }
            );
        } elseif ( $limit !== null ) {
            $sql .= " LIMIT %d OFFSET %d";
            $params[] = $limit;
            $params[] = $offset;
        }

        $results = self::get_results_prepared( $sql, ...$params );

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

        $issues = array_map( function($row) {
            $row['file_path'] = $row['file_path'] ?? $row['id'];
            $row['data'] = $row['evidence'] ?? null;
            $row['is_ignored'] = isset($row['is_ignored']) ? (int)$row['is_ignored'] : 0;
            return self::enrichIssue( $row );
        }, $results );

        if ( $group_for_ui ) {
            $effective_limit = $limit ?: 5000;
            return IssueUIGroupingService::group_and_slice( $issues, $effective_limit, $offset );
        }

        return $issues;
    }

    public static function getStagedIssuesByType( string $scan_id, string $type, int $limit = 500, int $offset = 0 ): array {
        $scan_id = sanitize_text_field( $scan_id );
        $type = sanitize_key( $type );
        if ( $scan_id === '' || $type === '' || $limit < 1 ) {
            return [];
        }

        global $wpdb;
        $t_instances = $wpdb->prefix . 'sitefort_issue_instances';
        $t_issues = $wpdb->prefix . 'sitefort_issues';

        $rows = self::get_results_prepared(
            "SELECT i.*, m.is_ignored
             FROM " . self::table_identifier( $t_instances ) . " i
             LEFT JOIN " . self::table_identifier( $t_issues ) . " m ON i.issue_hash = m.issue_hash
             WHERE i.scan_run_id = %s AND i.type = %s
             LIMIT %d OFFSET %d",
            $scan_id,
            $type,
            $limit,
            max( 0, $offset )
        );

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

        return array_map( function ( $row ) {
            $row['file_path'] = $row['file_path'] ?? $row['id'];
            $row['data'] = $row['evidence'] ?? null;
            $row['is_ignored'] = isset( $row['is_ignored'] ) ? (int) $row['is_ignored'] : 0;
            return self::enrichIssue( $row );
        }, $rows );
    }

    /**
     * Fetch only as many raw rows as needed to build the requested grouped UI page.
     *
     * The previous implementation always hydrated the full heuristic window before
     * grouping. This preserves that window as the worst-case cap, but stops early
     * when enough grouped cards are available for the requested page.
     *
     * @param string   $base_query Base SELECT without LIMIT/OFFSET.
     * @param array    $base_params Prepared-query params for the base SELECT.
     * @param int      $limit Requested grouped issue count.
     * @param int      $offset Requested grouped issue offset.
     * @param callable $row_mapper Maps a raw DB row to an enriched issue.
     * @return array
     */
    private static function fetch_grouped_issue_page( string $base_query, array $base_params, int $limit, int $offset, callable $row_mapper ): array {
        $fetch_limit = IssueUIGroupingService::resolve_fetch_limit( $limit, $offset );
        $chunk_size  = IssueUIGroupingService::resolve_fetch_chunk_size( $limit );
        $issues      = [];
        $raw_offset  = 0;

        while ( $raw_offset < $fetch_limit ) {
            $chunk_limit = min( $chunk_size, $fetch_limit - $raw_offset );
            $query = $base_query . ' LIMIT %d OFFSET %d';
            $params = array_merge( $base_params, [ $chunk_limit, $raw_offset ] );
            $rows = self::get_results_prepared( $query, ...$params );

            if ( empty( $rows ) ) {
                break;
            }

            foreach ( $rows as $row ) {
                $mapped = $row_mapper( $row );
                if ( is_array( $mapped ) ) {
                    $issues[] = $mapped;
                }
            }

            if ( count( $rows ) < $chunk_limit ) {
                break;
            }

            if ( IssueUIGroupingService::has_enough_for_page( $issues, $limit, $offset ) ) {
                break;
            }

            $raw_offset += $chunk_limit;
        }

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

        return IssueUIGroupingService::group_and_slice( $issues, $limit, $offset );
    }

    /**
     * Save or update a single issue immediately.
     *
     * Staging/commit is the preferred scan-write path, but this method remains
     * for direct one-off persistence flows.
     */
    public static function save( $issue ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $hash = $issue['hash'] ?? '';
        if ( empty( $hash ) ) {
            return false;
        }

        $is_ignored = self::isIgnored( $hash ) ? 1 : 0;
        
        $now = time();
        $created_at = $issue['created_at'] ?? $now;

        $data = [
            'issue_hash'    => $hash,
            'scan_id'       => $issue['scan_id'] ?? null,
            'type'          => $issue['type'] ?? 'unknown',
            'severity'      => $issue['severity'] ?? 'medium',
            'status'        => $issue['status'] ?? 'unknown',
            'file_path'     => $issue['file'] ?? $issue['file_path'] ?? null,
            'message'       => $issue['message'] ?? null,
            'data'          => isset( $issue['data'] ) ? wp_json_encode( $issue['data'] ) : null,
            'is_ignored'    => $is_ignored,
            'first_seen_at' => $created_at,
            'last_seen_at'  => $now,
            'created_at'    => $created_at,
            'updated_at'    => $now,
            'resolved_at'   => 0,
        ];

        $existing = self::getByHash( $hash );
        if ( is_array( $existing ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct issue update.
            return false !== $wpdb->update(
                $table,
                [
                    'scan_id'      => $data['scan_id'],
                    'type'         => $data['type'],
                    'severity'     => $data['severity'],
                    'status'       => $data['status'],
                    'file_path'    => $data['file_path'],
                    'message'      => $data['message'],
                    'data'         => $data['data'],
                    'is_ignored'   => $data['is_ignored'],
                    'last_seen_at' => $data['last_seen_at'],
                    'updated_at'   => $data['updated_at'],
                    'resolved_at'  => 0,
                ],
                [ 'issue_hash' => $hash ]
            );
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Inserts the current plugin-owned issue row for a direct persistence flow.
        return false !== $wpdb->insert( $table, $data );
    }

    /**
     * Find an issue by hash.
     * 
     * @param string $hash Issue hash
     * @return array|null Issue data or null
     */
    public static function find( $hash ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $row = self::get_row_prepared(
            "SELECT * FROM " . self::table_identifier( $table ) . " WHERE issue_hash = %s",
            $hash
        );

        if ( $row && ! empty( $row['data'] ) ) {
            $row['data'] = json_decode( $row['data'], true );
        }

        return $row;
    }

    /**
     * Find an active file-backed issue by stored file path.
     *
     * @param string $relative_path Normalized path relative to ABSPATH.
     * @return array|null Issue data or null.
     */
    public static function findByFilePath( string $relative_path ) {
        $relative_path = ltrim( wp_normalize_path( $relative_path ), '/' );
        if ( '' === $relative_path ) {
            return null;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $absolute_path = wp_normalize_path( trailingslashit( ABSPATH ) . $relative_path );
        $row = self::get_row_prepared(
            "SELECT * FROM " . self::table_identifier( $table ) . " WHERE resolved_at = 0 AND (file_path = %s OR file_path = %s) ORDER BY updated_at DESC LIMIT 1",
            $relative_path,
            $absolute_path
        );

        if ( $row && ! empty( $row['data'] ) ) {
            $row['data'] = json_decode( $row['data'], true );
        }

        return $row ?: null;
    }

    /**
     * Check if an issue is ignored.
     * 
     * @param string $hash Issue hash
     * @return bool
     */
    public static function isIgnored( $hash ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_ignored_issues';

        $count = self::get_var_prepared(
            "SELECT COUNT(*) FROM " . self::table_identifier( $table ) . " WHERE issue_hash = %s",
            $hash
        );

        return (int) $count > 0;
    }

    /**
     * Ignore an issue.
     * 
     * @param string $hash Issue hash
     * @return bool Success
     */
    public static function ignore( $hash ) {
        global $wpdb;
        
        // Add to ignored table
        $ignored_table = $wpdb->prefix . 'sitefort_ignored_issues';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Upserts the plugin-owned ignore marker for an issue hash.
        $wpdb->replace( $ignored_table, [
            'issue_hash' => $hash,
            'ignored_at' => time(),
        ] );

        // Update denormalized flag in issues table
        $issues_table = $wpdb->prefix . 'sitefort_issues';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Keeps the denormalized ignore flag aligned on the plugin-owned issues table.
        $wpdb->update( $issues_table, 
            [ 'is_ignored' => 1, 'updated_at' => time() ], 
            [ 'issue_hash' => $hash ] 
        );

        return true;
    }

    /**
     * Unignore an issue.
     * 
     * @param string $hash Issue hash
     * @return bool Success
     */
    public static function unignore( $hash ) {
        global $wpdb;
        
        // Remove from ignored table
        $ignored_table = $wpdb->prefix . 'sitefort_ignored_issues';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Removes the plugin-owned ignore marker for an issue hash.
        $wpdb->delete( $ignored_table, [ 'issue_hash' => $hash ] );

        // Update denormalized flag in issues table
        $issues_table = $wpdb->prefix . 'sitefort_issues';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Keeps the denormalized ignore flag aligned on the plugin-owned issues table.
        $wpdb->update( $issues_table, 
            [ 'is_ignored' => 0, 'updated_at' => time() ], 
            [ 'issue_hash' => $hash ] 
        );

        return true;
    }

    /**
     * Get all active (non-ignored) issues with pagination.
     * 
     * @param int|null $limit Max records to return
     * @param int|null $offset Offset for pagination
     * @param string|null $type Filter by type
     * @return array Array of issues
     */
    public static function getActive( $limit = null, $offset = 0, $type = null ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $where = "WHERE is_ignored = 0 AND resolved_at = 0";
        $params = [];

        if ( $type ) {
            $where .= " AND type = %s";
            $params[] = $type;
        }

        $query = "SELECT * FROM " . self::table_identifier( $table ) . " {$where} ORDER BY created_at DESC";

        $effective_limit = $limit ?: 5000;
        $query .= ' LIMIT %d OFFSET %d';
        $params[] = $effective_limit;
        $params[] = $offset;

        $rows = self::get_results_prepared( $query, ...$params );

        return array_map( [ self::class, 'enrichIssue' ], $rows );
    }

    public static function getActivePrioritized( $limit = 50, $offset = 0, $type = null ) {
        return self::getPrioritizedIssues( false, $limit, $offset, $type );
    }

    /**
     * Get all ignored issues with pagination.
     * 
     * @param int|null $limit
     * @param int|null $offset
     * @return array Array of issues
     */
    public static function getIgnored( $limit = null, $offset = 0 ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $query = "SELECT * FROM " . self::table_identifier( $table ) . " WHERE is_ignored = 1 AND resolved_at = 0 ORDER BY updated_at DESC";

        $effective_limit = $limit ?: 5000;
        $query .= ' LIMIT %d OFFSET %d';

        $rows = self::get_results_prepared( $query, $effective_limit, $offset );

        return array_map( [ self::class, 'enrichIssue' ], $rows );
    }

    public static function getIgnoredPrioritized( $limit = 50, $offset = 0, $type = null ) {
        return self::getPrioritizedIssues( true, $limit, $offset, $type );
    }

    public static function countCurrentNonSystem( bool $ignored = false ): int {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $skip_types = IssueType::informationalTypes();
        $placeholders = implode( ',', array_fill( 0, count( $skip_types ), '%s' ) );

        return (int) self::get_var_prepared(
            "SELECT COUNT(*) FROM " . self::table_identifier( $table ) . " WHERE is_ignored = %d AND resolved_at = 0 AND type NOT IN ($placeholders)",
            $ignored ? 1 : 0,
            ...$skip_types
        );
    }

    private static function getPrioritizedIssues( bool $ignored, $limit = 50, $offset = 0, $type = null ): array {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $where = 'WHERE is_ignored = %d AND resolved_at = 0';
        $params = [ $ignored ? 1 : 0 ];

        if ( $type ) {
            $where .= ' AND type = %s';
            $params[] = $type;
        } else {
            $skip_types = IssueType::informationalTypes();
            $placeholders = implode( ',', array_fill( 0, count( $skip_types ), '%s' ) );
            $where .= " AND type NOT IN ($placeholders)";
            $params = array_merge( $params, $skip_types );
        }

        $query = "SELECT * FROM " . self::table_identifier( $table ) . " {$where}
            ORDER BY
                CASE LOWER(severity)
                    WHEN 'critical' THEN 0
                    WHEN 'high' THEN 1
                    WHEN 'medium' THEN 2
                    WHEN 'low' THEN 3
                    WHEN 'info' THEN 4
                    ELSE 5
                END ASC,
                updated_at DESC,
                created_at DESC,
                id DESC
            LIMIT %d OFFSET %d";

        $effective_limit = max( 1, (int) ( $limit ?: 50 ) );
        $params[] = $effective_limit;
        $params[] = max( 0, (int) $offset );

        $rows = self::get_results_prepared( $query, ...$params );

        return array_map( [ self::class, 'enrichIssue' ], $rows );
    }

    /**
     * Get all issues (active + ignored) for frontend.
     * 
     * @param int|null $limit
     * @param int|null $offset
     * @return array Array of issues with is_ignored flag
     */
    public static function getAll( $limit = null, $offset = 0, $scan_id = null ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $where_clauses = [];
        $params = [];

        if ( $scan_id ) {
            $where_clauses[] = "scan_id = %s";
            $params[] = $scan_id;
        }

        if ( ! empty( $where_clauses ) ) {
            $where_sql = "WHERE " . implode( " AND ", $where_clauses );
        } else {
            $where_sql = "";
        }
        $query = "SELECT * FROM " . self::table_identifier( $table ) . " {$where_sql} ORDER BY updated_at DESC";

        $effective_limit = $limit ?: 5000;
        $query .= " LIMIT %d OFFSET %d";
        $params[] = $effective_limit;
        $params[] = $offset;

        $rows = self::get_results_prepared( $query, ...$params );

        return array_map( [ self::class, 'enrichIssue' ], $rows );
    }

    /**
     * Clear issues by scan ID.
     * Used to prevent ghost items when starting a new scan.
     * 
     * @param string $scan_id Scan session ID
     * @return int Number of rows deleted
     */
    public static function clearByScanId( $scan_id ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        // Only delete non-ignored issues (preserve user's ignore decisions)
        return self::query_prepared(
            "DELETE FROM " . self::table_identifier( $table ) . " WHERE scan_id = %s AND is_ignored = 0",
            $scan_id
        );
    }

    /**
     * Clear all non-ignored issues.
     * Used when starting a completely fresh scan.
     * 
     * @return int Number of rows deleted
     */
    public static function clearActive() {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        return self::query_literal( "DELETE FROM " . self::table_identifier( $table ) . " WHERE is_ignored = 0" );
    }

    /**
     * Fetch a single issue row by hash.
     *
     * @param string $hash Issue hash.
     * @return array|null Row as associative array, or null if not found.
     */
    public static function getByHash( string $hash ): ?array {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';
        $row = self::get_row_prepared(
            "SELECT * FROM " . self::table_identifier( $table ) . " WHERE issue_hash = %s LIMIT 1",
            $hash
        );
        return is_array( $row ) ? $row : null;
    }

    /**
     * Subset of the given paths that have an active (unresolved, non-ignored)
     * integrity finding.
     *
     * @param array<int,string> $file_paths Relative file paths.
     * @return array<int,string>
     */
    public static function getActiveIntegrityPaths( array $file_paths ): array {
        global $wpdb;

        $file_paths = array_values( array_unique( array_filter( array_map( 'strval', $file_paths ), static function ( $path ) {
            return $path !== '';
        } ) ) );
        if ( empty( $file_paths ) ) {
            return [];
        }

        $table        = self::table_identifier( $wpdb->prefix . 'sitefort_issues' );
        $placeholders = implode( ',', array_fill( 0, count( $file_paths ), '%s' ) );
        $sql          = "SELECT DISTINCT file_path FROM {$table}
                WHERE resolved_at = 0
                  AND is_ignored = 0
                  AND type = %s
                  AND file_path IN ({$placeholders})";

        $rows  = self::get_results_prepared( $sql, IssueType::INTEGRITY, ...$file_paths );
        $paths = [];
        foreach ( $rows as $row ) {
            if ( isset( $row['file_path'] ) ) {
                $paths[] = (string) $row['file_path'];
            }
        }

        return $paths;
    }

    /**
     * Whether the file has an integrity finding, staged this scan or active from
     * a previous one.
     *
     * @param string $scan_id   Current scan UUID.
     * @param string $file_path Relative file path.
     * @return bool
     */
    public static function hasIntegrityIssueForPath( string $scan_id, string $file_path ): bool {
        if ( $file_path === '' ) {
            return false;
        }

        if ( ! empty( self::getActiveIntegrityPaths( [ $file_path ] ) ) ) {
            return true;
        }

        if ( $scan_id === '' ) {
            return false;
        }

        global $wpdb;
        $instances = self::table_identifier( $wpdb->prefix . 'sitefort_issue_instances' );
        $staged    = self::get_var_prepared(
            "SELECT 1 FROM {$instances} WHERE scan_run_id = %s AND type = %s AND file_path = %s LIMIT 1",
            $scan_id,
            IssueType::INTEGRITY,
            $file_path
        );

        return null !== $staged;
    }

    /**
     * Delete a specific issue by hash.
     * Used for "Mark as Fixed" action.
     *
     * @param string $hash Issue hash
     * @return bool Success
     */
    public static function delete( $hash ) {
        global $wpdb;
        
        // Delete from issues table
        $issues_table = $wpdb->prefix . 'sitefort_issues';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes a single plugin-owned issue row.
        $deleted = $wpdb->delete( $issues_table, [ 'issue_hash' => $hash ] );
        
        // Also delete from ignored table if present
        $ignored_table = $wpdb->prefix . 'sitefort_ignored_issues';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes any matching plugin-owned ignore row for the same issue hash.
        $wpdb->delete( $ignored_table, [ 'issue_hash' => $hash ] );
        
        return $deleted !== false;
    }

    /**
     * Delete a staged issue instance by hash for a specific scan.
     *
     * @param string $scan_id Scan session ID.
     * @param string $hash Issue hash.
     * @return int Number of rows deleted.
     */
    public static function deleteStagedByHash( $scan_id, $hash ) {
        if ( ! $scan_id || ! $hash ) {
            return 0;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issue_instances';

        return (int) self::query_prepared(
            "DELETE FROM " . self::table_identifier( $table ) . " WHERE scan_run_id = %s AND issue_hash = %s",
            $scan_id,
            $hash
        );
    }

    /**
     * Bulk variant of delete() + deleteStagedByHash(): clears matching rows
     * from the issues, ignored, and staged tables in IN-list chunks.
     *
     * @param array<int,string> $hashes  Issue hashes.
     * @param string            $scan_id Scan whose staged rows are also cleared.
     * @return void
     */
    public static function deleteByHashesWithStaged( array $hashes, string $scan_id ): void {
        $hashes = array_values( array_filter( array_map( 'strval', $hashes ) ) );
        if ( empty( $hashes ) ) {
            return;
        }

        global $wpdb;
        $issues_table  = self::table_identifier( $wpdb->prefix . 'sitefort_issues' );
        $ignored_table = self::table_identifier( $wpdb->prefix . 'sitefort_ignored_issues' );
        $staged_table  = self::table_identifier( $wpdb->prefix . 'sitefort_issue_instances' );

        foreach ( array_chunk( $hashes, 100 ) as $chunk ) {
            $placeholders = implode( ',', array_fill( 0, count( $chunk ), '%s' ) );
            self::query_prepared( "DELETE FROM {$issues_table} WHERE issue_hash IN ({$placeholders})", ...$chunk );
            self::query_prepared( "DELETE FROM {$ignored_table} WHERE issue_hash IN ({$placeholders})", ...$chunk );
            if ( $scan_id !== '' ) {
                self::query_prepared(
                    "DELETE FROM {$staged_table} WHERE scan_run_id = %s AND issue_hash IN ({$placeholders})",
                    $scan_id,
                    ...$chunk
                );
            }
        }
    }

    public static function updateIssueData( string $hash, array $data_updates ): bool {
        if ( $hash === '' ) {
            return false;
        }

        $row = self::find( $hash );
        if ( ! is_array( $row ) ) {
            return false;
        }

        $data = isset( $row['data'] ) && is_array( $row['data'] ) ? $row['data'] : [];
        $data = array_replace_recursive( $data, $data_updates );
        $encoded = wp_json_encode( $data );
        if ( ! is_string( $encoded ) ) {
            return false;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Updates one plugin-owned issue row.
        return false !== $wpdb->update(
            $table,
            [
                'data'       => $encoded,
                'updated_at' => time(),
            ],
            [ 'issue_hash' => $hash ],
            [ '%s', '%d' ],
            [ '%s' ]
        );
    }

    public static function updateStagedIssueData( string $scan_id, string $hash, array $data_updates ): bool {
        if ( $scan_id === '' || $hash === '' ) {
            return false;
        }

        $row = self::findStagedByHash( $scan_id, $hash );
        if ( ! is_array( $row ) ) {
            return false;
        }

        $data = isset( $row['data'] ) && is_array( $row['data'] ) ? $row['data'] : [];
        $data = array_replace_recursive( $data, $data_updates );
        $encoded = wp_json_encode( $data );
        if ( ! is_string( $encoded ) ) {
            return false;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issue_instances';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Updates one plugin-owned staged issue row.
        return false !== $wpdb->update(
            $table,
            [
                'evidence' => $encoded,
            ],
            [
                'scan_run_id' => $scan_id,
                'issue_hash'  => $hash,
            ],
            [ '%s' ],
            [ '%s', '%s' ]
        );
    }

    /**
     * Find a staged issue by hash within a scan.
     *
     * @param string $scan_id Scan session ID.
     * @param string $hash Issue hash.
     * @return array|null Issue data or null.
     */
    public static function findStagedByHash( $scan_id, $hash ) {
        if ( ! $scan_id || ! $hash ) {
            return null;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issue_instances';

        $row = self::get_row_prepared(
            "SELECT * FROM " . self::table_identifier( $table ) . " WHERE scan_run_id = %s AND issue_hash = %s",
            $scan_id,
            $hash
        );

        if ( $row && ! empty( $row['evidence'] ) ) {
            $decoded = json_decode( $row['evidence'], true );
            if ( is_array( $decoded ) ) {
                $row['data'] = $decoded;
            }
        }

        return $row ?: null;
    }

    /**
     * Find a staged file-backed issue by stored file path.
     *
     * @param string $scan_id Scan session ID.
     * @param string $relative_path Normalized path relative to ABSPATH.
     * @return array|null Issue data or null.
     */
    public static function findStagedByFilePath( string $scan_id, string $relative_path ) {
        $scan_id = sanitize_text_field( $scan_id );
        $relative_path = ltrim( wp_normalize_path( $relative_path ), '/' );
        if ( '' === $scan_id || '' === $relative_path ) {
            return null;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issue_instances';

        $absolute_path = wp_normalize_path( trailingslashit( ABSPATH ) . $relative_path );
        $row = self::get_row_prepared(
            "SELECT * FROM " . self::table_identifier( $table ) . " WHERE scan_run_id = %s AND (file_path = %s OR file_path = %s) ORDER BY created_at DESC LIMIT 1",
            $scan_id,
            $relative_path,
            $absolute_path
        );

        if ( $row && ! empty( $row['evidence'] ) ) {
            $decoded = json_decode( $row['evidence'], true );
            if ( is_array( $decoded ) ) {
                $row['data'] = $decoded;
            }
        }

        return $row ?: null;
    }

    /**
     * Get count of issues for a specific scan session.
     * 
     * @param string $scan_id
     * @return int
     */
    public static function countByScanId( $scan_id ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';
        
        $count = self::get_var_prepared(
            "SELECT COUNT(*) FROM " . self::table_identifier( $table ) . " WHERE scan_id = %s AND resolved_at = 0",
            $scan_id
        );
        
        return (int) $count;
    }

    /**
     * Get issue counts for dashboard.
     * 
     * @return array ['active' => int, 'ignored' => int, 'by_type' => [...]]
     */
    public static function getCounts() {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        // Informational types (system errors, skipped items) show as notices but never count toward totals.
        $skip_types = IssueType::informationalTypes();
        $placeholders = implode( ',', array_fill( 0, count( $skip_types ), '%s' ) );

        $active = (int) self::get_var_prepared(
            "SELECT COUNT(*) FROM " . self::table_identifier( $table ) . " WHERE is_ignored = 0 AND resolved_at = 0 AND type NOT IN ($placeholders)",
            ...$skip_types
        );
        $ignored = (int) self::get_var_prepared(
            "SELECT COUNT(*) FROM " . self::table_identifier( $table ) . " WHERE is_ignored = 1 AND resolved_at = 0 AND type NOT IN ($placeholders)",
            ...$skip_types
        );

        $by_type = self::get_results_prepared(
            "SELECT type, COUNT(*) as count FROM " . self::table_identifier( $table ) . " WHERE is_ignored = 0 AND resolved_at = 0 AND type NOT IN ($placeholders) GROUP BY type",
            ...$skip_types
        );

        $type_counts = [];
        foreach ( $by_type as $row ) {
            $type_counts[ $row['type'] ] = (int) $row['count'];
        }

        return [
            'active'  => $active,
            'ignored' => $ignored,
            'by_type' => $type_counts,
        ];
    }

    /**
     * Get the highest severity among active (non-ignored, non-resolved) issues.
     *
     * @return string One of 'critical', 'high', 'medium', 'low', or 'none'.
     */
    public static function getMaxSeverity(): string {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';

        $skip_types = IssueType::informationalTypes();
        $placeholders = implode( ',', array_fill( 0, count( $skip_types ), '%s' ) );

        $severity = self::get_var_prepared(
            "SELECT severity FROM " . self::table_identifier( $table ) . "
             WHERE is_ignored = 0 AND resolved_at = 0 AND type NOT IN ($placeholders)
             ORDER BY FIELD(severity, 'critical', 'high', 'medium', 'low')
             LIMIT 1",
            ...$skip_types
        );

        return $severity ? (string) $severity : 'none';
    }

    /**
     * Enrich raw DB row with presentation metadata.
     *
     * Delegates to IssueDisplayContractBuilder::enrich() which owns the full
     * enrichment pipeline (field normalization, display contract, action policy).
     *
     * @param array $row Raw DB row
     * @return array Enriched issue
     */
    private static function enrichIssue( array $row ): array {
        return IssueDisplayContractBuilder::enrich( $row );
    }

    public static function countActive( $type = null ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issues';
        
        $where = "is_ignored = 0 AND resolved_at = 0";
        $params = [];
        
        if ( $type ) {
            $where .= " AND type = %s";
            $params[] = $type;
        }
        
        $sql = "SELECT COUNT(*) FROM " . self::table_identifier( $table ) . " WHERE {$where}";
        
        return (int) self::get_var_prepared( $sql, ...$params );
    }

    /**
     * Count staged issues for a running scan.
     * 
     * @param string $scan_id
     * @return int
     */
    public static function countStaged( $scan_id ) {
        global $wpdb;
        $table = $wpdb->prefix . 'sitefort_issue_instances';
        return (int) self::get_var_prepared( 
            "SELECT COUNT(*) FROM " . self::table_identifier( $table ) . " WHERE scan_run_id = %s", 
            $scan_id 
        );
    }

    private static function table_identifier( string $table ): string {
        if ( $table === '' || strlen( $table ) > 64 || ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) {
            throw new \InvalidArgumentException( 'Invalid table identifier.' );
        }

        return '`' . str_replace( '`', '``', $table ) . '`';
    }

    /**
     * Run a prepared SELECT returning multiple rows.
     *
     * @param string $sql SQL with placeholders.
     * @param mixed  ...$params Query parameters.
     * @return array<int,array<string,mixed>>
     */
    private static function get_results_prepared( string $sql, ...$params ): array {
        global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
        $prepared = empty( $params ) ? $sql : $wpdb->prepare( $sql, ...$params );
        if ( ! is_string( $prepared ) || '' === $prepared ) {
            return [];
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
        $results = $wpdb->get_results( $prepared, ARRAY_A );
        return is_array( $results ) ? $results : [];
    }

    /**
     * Run a prepared SELECT returning one row.
     *
     * @param string $sql SQL with placeholders.
     * @param mixed  ...$params Query parameters.
     * @return array<string,mixed>|null
     */
    private static function get_row_prepared( string $sql, ...$params ): ?array {
        global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
        $prepared = empty( $params ) ? $sql : $wpdb->prepare( $sql, ...$params );
        if ( ! is_string( $prepared ) || '' === $prepared ) {
            return null;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
        $row = $wpdb->get_row( $prepared, ARRAY_A );
        return is_array( $row ) ? $row : null;
    }

    /**
     * Run a prepared SELECT returning one scalar.
     *
     * @param string $sql SQL with placeholders.
     * @param mixed  ...$params Query parameters.
     * @return mixed
     */
    private static function get_var_prepared( string $sql, ...$params ) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
        $prepared = empty( $params ) ? $sql : $wpdb->prepare( $sql, ...$params );
        if ( ! is_string( $prepared ) || '' === $prepared ) {
            return null;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
        return $wpdb->get_var( $prepared );
    }

    /**
     * Run a prepared write query.
     *
     * @param string $sql SQL with placeholders.
     * @param mixed  ...$params Query parameters.
     * @return int|false
     */
    private static function query_prepared( string $sql, ...$params ) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
        $prepared = empty( $params ) ? $sql : $wpdb->prepare( $sql, ...$params );
        if ( ! is_string( $prepared ) || '' === $prepared ) {
            return false;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
        return $wpdb->query( $prepared );
    }

    /**
     * Run a literal write query after caller-side validation.
     *
     * @param string $sql Fully assembled SQL.
     * @return int|false
     */
    private static function query_literal( string $sql ) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Executes validated DDL/DML against plugin-owned issue tables.
        return $wpdb->query( $sql );
    }
}
