<?php
/**
 * Database operations for StaticQ
 * 
 * @package suspended-cloud/cloudflare-media-offload
 * @since 3.0.0
 */

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

class SQMedia_Database {
    
    /**
     * Table names (without prefix)
     * 
     * TABLE_RECORDS: Main attachment tracking (simplified - no per-file status)
     * TABLE_BATCHES: Registration batch history
     * TABLE_SCANS: Scan session metadata
     * TABLE_SCAN_ITEMS: Individual issues found during scans
     */
    const TABLE_RECORDS = 'sqmedia_records';
    const TABLE_FILES = 'sqmedia_records'; // Backward compatibility alias
    const TABLE_BATCHES = 'sqmedia_migration_batches';
    const TABLE_SCANS = 'sqmedia_scan_results';
    const TABLE_SCAN_ITEMS = 'sqmedia_scan_items';

    // Backup table names (without prefix)
    const TABLE_BACKUP_MLS_POSTMETA = 'sqmedia_backup_mls_postmeta';
    const TABLE_BACKUP_MLS_POSTS = 'sqmedia_backup_mls_posts';
    const TABLE_BACKUP_PCS_POSTS = 'sqmedia_backup_pcs_posts';
    
    /**
     * Get full table name with prefix
     */
    public static function get_table(string $table): string {
        global $wpdb;
        return $wpdb->prefix . $table;
    }
    
    /**
     * Create all database tables
     */
    public static function create_tables(): void {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        
        // Main records table (simplified - WordPress postmeta is source of truth for sizes)
        $table_records = self::get_table(self::TABLE_RECORDS);
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $sql_records = "CREATE TABLE {$table_records} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            attachment_id BIGINT UNSIGNED NOT NULL,
            
            storage_location ENUM('local', 'bucket', 'both') NOT NULL DEFAULT 'local',
            has_webp TINYINT(1) NOT NULL DEFAULT 0,
            
            master_path VARCHAR(512) NOT NULL,
            master_dir VARCHAR(255) NOT NULL,
            master_filename VARCHAR(255) NOT NULL,
            
            original_image VARCHAR(255) NULL,
            original_location ENUM('alongside', 'originals') NULL DEFAULT NULL,

            registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            registered_via ENUM('upload', 'migration', 'manual') NOT NULL DEFAULT 'upload',
            batch_id VARCHAR(50) NULL,
            
            status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
            retry_count TINYINT(2) NOT NULL DEFAULT 0,
            last_error TEXT NULL,
            priority INT NOT NULL DEFAULT 10,
            
            last_verified DATETIME NULL,
            
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            
            PRIMARY KEY (id),
            UNIQUE KEY idx_attachment_id (attachment_id),
            KEY idx_storage_location (storage_location),
            KEY idx_has_webp (has_webp),
            KEY idx_batch_id (batch_id),
            KEY idx_status (status),
            KEY idx_status_retry (status, retry_count),
            KEY idx_priority_status (priority, status),
            KEY idx_registered_via (registered_via),
            KEY idx_master_dir (master_dir)
        ) {$charset_collate};";
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        dbDelta($sql_records);
        
        // Migration batches table
        $table_batches = self::get_table(self::TABLE_BATCHES);
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $sql_batches = "CREATE TABLE {$table_batches} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            batch_id VARCHAR(50) NOT NULL,
            
            status ENUM('running', 'paused', 'completed', 'cancelled') NOT NULL DEFAULT 'running',
            
            total_attachments INT UNSIGNED NOT NULL DEFAULT 0,
            processed_attachments INT UNSIGNED NOT NULL DEFAULT 0,
            registered_attachments INT UNSIGNED NOT NULL DEFAULT 0,
            skipped_attachments INT UNSIGNED NOT NULL DEFAULT 0,
            error_attachments INT UNSIGNED NOT NULL DEFAULT 0,
            
            start_date DATE NULL,
            end_date DATE NULL,
            
            results LONGTEXT NULL,
            
            started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            completed_at DATETIME NULL,
            cancelled_at DATETIME NULL,
            
            PRIMARY KEY (id),
            UNIQUE KEY idx_batch_id (batch_id),
            KEY idx_status (status)
        ) {$charset_collate};";
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        dbDelta($sql_batches);
        
        // Scan results table
        $table_scans = self::get_table(self::TABLE_SCANS);
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $sql_scans = "CREATE TABLE {$table_scans} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            scan_id VARCHAR(50) NOT NULL,
            scan_type VARCHAR(30) NOT NULL,
            
            status ENUM('pending', 'running', 'building_index', 'scanning', 'paused', 'fixing', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'pending',
            
            total_items INT UNSIGNED NOT NULL DEFAULT 0,
            processed_items INT UNSIGNED NOT NULL DEFAULT 0,
            
            results LONGTEXT NULL,
            
            scan_started DATETIME NULL,
            scan_completed DATETIME NULL,
            
            PRIMARY KEY (id),
            UNIQUE KEY idx_scan_id (scan_id),
            KEY idx_scan_type (scan_type),
            KEY idx_status (status)
        ) {$charset_collate};";
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        dbDelta($sql_scans);
        
        // Scan items table - stores individual issues for "Fix All"
        $table_scan_items = self::get_table(self::TABLE_SCAN_ITEMS);
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $sql_scan_items = "CREATE TABLE {$table_scan_items} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            scan_id VARCHAR(50) NOT NULL,
            action_type VARCHAR(30) NOT NULL,
            attachment_id BIGINT UNSIGNED NOT NULL,
            details TEXT NULL,
            
            PRIMARY KEY (id),
            KEY idx_scan_action (scan_id, action_type),
            KEY idx_attachment (attachment_id)
        ) {$charset_collate};";
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        dbDelta($sql_scan_items);
        
        // Log table creation
        if (defined('WP_DEBUG_LOG') && WP_DEBUG_LOG) {
        }
    }
    
    /**
     * Maybe upgrade tables (called on admin_init)
     * Only runs once per request
     */
    public static function maybe_upgrade_tables(): void {
        // Only run once per request
        static $done = false;
        if ($done) return;
        $done = true;
        
        self::upgrade_tables();
    }
    
    /**
     * Upgrade existing tables to add new ENUM values
     * dbDelta doesn't modify ENUM columns, so we need to ALTER TABLE
     */
    public static function upgrade_tables(): void {
        global $wpdb;
        
        $table_scans = self::get_table(self::TABLE_SCANS);
        
        // Check if table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $table_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_scans
        ));
        
        if (!$table_exists) {
            return;
        }
        
        // Get current column definition
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $column_info = $wpdb->get_row("SHOW COLUMNS FROM {$table_scans} WHERE Field = 'status'");
        
        if (!$column_info) {
            return;
        }
        
        // Check if 'fixing' is already in the ENUM (new status we added)
        if (strpos($column_info->Type, 'fixing') === false) {
            // ALTER TABLE to add new ENUM values
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $wpdb->query("ALTER TABLE {$table_scans} MODIFY COLUMN status ENUM('pending', 'running', 'building_index', 'scanning', 'paused', 'fixing', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'pending'");
            
            if (defined('WP_DEBUG_LOG') && WP_DEBUG_LOG) {
            }
        }
        
        // Check if 'paused' is already in the ENUM
        if (strpos($column_info->Type, 'paused') === false) {
            // ALTER TABLE to add paused
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $wpdb->query("ALTER TABLE {$table_scans} MODIFY COLUMN status ENUM('pending', 'running', 'building_index', 'scanning', 'paused', 'fixing', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'pending'");

            if (defined('WP_DEBUG_LOG') && WP_DEBUG_LOG) {
            }
        }

        // Add original_location column to records table if missing
        $table_records = self::get_table(self::TABLE_RECORDS);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $col = $wpdb->get_var($wpdb->prepare(
            "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'original_location'",
            DB_NAME,
            $table_records
        ));

        if (!$col) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $wpdb->query("ALTER TABLE {$table_records} ADD COLUMN original_location ENUM('alongside', 'originals') NULL DEFAULT NULL AFTER original_image");

            if (defined('WP_DEBUG_LOG') && WP_DEBUG_LOG) {
            }
        }
    }
    
    /**
     * Drop all plugin tables
     */
    public static function drop_tables(): void {
        global $wpdb;
        
        $tables = [
            self::TABLE_RECORDS,
            self::TABLE_BATCHES,
            self::TABLE_SCANS,
            self::TABLE_SCAN_ITEMS,
        ];
        
        foreach ($tables as $table) {
            $full_name = self::get_table($table);
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $wpdb->query("DROP TABLE IF EXISTS {$full_name}");
        }
    }
    
    // =========================================================================
    // ATTACHMENT RECORDS (sq_records)
    // =========================================================================
    
    /**
     * Get record by attachment ID
     */
    public function get_record(int $attachment_id): ?object {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE attachment_id = %d",
            $attachment_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Check if attachment is registered
     */
    public function is_registered(int $attachment_id): bool {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE attachment_id = %d",
            $attachment_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        return $count > 0;
    }
    
    /**
     * Create new record
     */
    public function create_record(array $data): ?int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // Safeguard: original_image must be a string or null, not an array
        if (isset($data['original_image']) && !is_string($data['original_image'])) {
            $data['original_image'] = null;
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->insert($table, $data);
        
        return $result ? $wpdb->insert_id : null;
    }
    
    /**
     * Update record
     */
    public function update_record(int $attachment_id, array $data): bool {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // Safeguard: original_image must be a string or null, not an array
        if (isset($data['original_image']) && !is_string($data['original_image'])) {
            $data['original_image'] = null;
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $table,
            $data,
            ['attachment_id' => $attachment_id]
        );
        
        return $result !== false;
    }
    
    /**
     * Delete record
     */
    public function delete_record(int $attachment_id): bool {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->delete(
            $table,
            ['attachment_id' => $attachment_id],
            ['%d']
        );
        
        return $result !== false;
    }
    
    /**
     * Delete all records from a batch
     */
    public function delete_by_batch(string $batch_id): int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->query($wpdb->prepare(
            "DELETE FROM {$table} WHERE batch_id = %s",
            $batch_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        return (int) $result;
    }
    
    /**
     * Get records by multiple IDs
     */
    public function get_records_by_ids(array $attachment_ids): array {
        global $wpdb;
        
        if (empty($attachment_ids)) {
            return [];
        }
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        $ids = implode(',', array_map('intval', $attachment_ids));
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results(
            "SELECT * FROM {$table} WHERE attachment_id IN ({$ids})"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        $result = [];
        foreach ($rows as $row) {
            $result[$row->attachment_id] = $row;
        }
        
        return $result;
    }
    
    /**
     * Get unregistered attachment IDs
     */
    public function get_unregistered_attachment_ids(array $filters = [], int $limit = 0, int $offset = 0): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // Base condition - always attachment post type
        $where = "p.post_type = 'attachment'";
        
        // Filter by media type based on settings
        if (!SQMedia_Settings::is_all_media_enabled()) {
            // Images only
            $where .= " AND p.post_mime_type LIKE 'image/%'";
        }
        
        if (!empty($filters['start_date'])) {
            $where .= $wpdb->prepare(" AND p.post_date >= %s", $filters['start_date']);
        }
        if (!empty($filters['end_date'])) {
            $where .= $wpdb->prepare(" AND p.post_date <= %s", $filters['end_date']);
        }
        
        $limit_sql = $limit > 0 ? $wpdb->prepare("LIMIT %d OFFSET %d", $limit, $offset) : '';
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $query = "
            SELECT p.ID 
            FROM {$wpdb->posts} p
            LEFT JOIN {$table} r ON p.ID = r.attachment_id
            WHERE {$where} AND r.attachment_id IS NULL
            ORDER BY p.post_date DESC
            {$limit_sql}
        ";
        
        return $wpdb->get_col($query);
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Count unregistered attachments
     */
    public function count_unregistered_attachments(array $filters = []): int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // Base condition - always attachment post type
        $where = "p.post_type = 'attachment'";
        
        // Filter by media type based on settings
        if (!SQMedia_Settings::is_all_media_enabled()) {
            // Images only
            $where .= " AND p.post_mime_type LIKE 'image/%'";
        }
        
        if (!empty($filters['start_date'])) {
            $where .= $wpdb->prepare(" AND p.post_date >= %s", $filters['start_date']);
        }
        if (!empty($filters['end_date'])) {
            $where .= $wpdb->prepare(" AND p.post_date <= %s", $filters['end_date']);
        }
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $query = "
            SELECT COUNT(p.ID)
            FROM {$wpdb->posts} p
            LEFT JOIN {$table} r ON p.ID = r.attachment_id
            WHERE {$where} AND r.attachment_id IS NULL
        ";
        
        return (int) $wpdb->get_var($query);
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get all registered attachment IDs
     */
    public function get_all_registered_ids(): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_col("SELECT attachment_id FROM {$table}");
    }
    
    /**
     * Get all registered attachment IDs sorted by file path
     * This optimizes Phase 2 scanning by ensuring files from the same chunk are processed together
     */
    public function get_all_registered_ids_sorted_by_path(): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_col("SELECT attachment_id FROM {$table} ORDER BY master_path ASC");
    }
    
    /**
     * Get total count of registered records.
     *
     * Called on every `init` to decide whether to instantiate the URL
     * rewriter, so it has to stay cheap and quiet when the table does not
     * exist — for example, when WordPress's Plugin Check tool runs our code
     * under an alternate `wp_pc_` table prefix that was never activated.
     * Errors are suppressed for this query only and a missing table is
     * treated as "zero records" rather than a DB error in debug.log.
     */
    public function get_total_record_count(): int {
        global $wpdb;

        $table = self::get_table(self::TABLE_RECORDS);

        $previous_suppress = $wpdb->suppress_errors(true);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
        $wpdb->suppress_errors($previous_suppress);

        return (int) $count;
    }
    
    /**
     * Get records by status
     */
    public function get_records_by_status(string $status, int $limit = 100, int $offset = 0): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} WHERE status = %s ORDER BY updated_at DESC LIMIT %d OFFSET %d",
            $status,
            $limit,
            $offset
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get counts by storage location
     */
    public function get_location_counts(): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $results = $wpdb->get_results("
            SELECT storage_location, COUNT(*) as count 
            FROM {$table} 
            GROUP BY storage_location
        ", ARRAY_A);
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        $counts = [
            'local' => 0,
            'bucket' => 0,
            'both' => 0,
        ];
        
        foreach ($results as $row) {
            $counts[$row['storage_location']] = (int) $row['count'];
        }
        
        return $counts;
    }
    
    /**
     * Get counts by has_webp flag
     */
    public function get_webp_counts(): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $with_webp = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE has_webp = 1");
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $without_webp = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE has_webp = 0");
        
        return [
            'with_webp' => $with_webp,
            'without_webp' => $without_webp,
        ];
    }
    
    // =========================================================================
    // QUEUE OPERATIONS (using status field in main table)
    // =========================================================================
    
    /**
     * Insert a new attachment as pending (for new uploads)
     */
    public function insert_pending(int $attachment_id, string $master_path, int $priority = 10): ?int {
        $master_dir = dirname($master_path);
        $master_filename = basename($master_path);
        
        if ($master_dir === '.') {
            $master_dir = '';
        }
        
        $data = [
            'attachment_id' => $attachment_id,
            'master_path' => $master_path,
            'master_dir' => $master_dir,
            'master_filename' => $master_filename,
            'storage_location' => 'local',
            'has_webp' => 0,
            'status' => 'pending',
            'priority' => $priority,
            'retry_count' => 0,
            'registered_via' => 'upload',
        ];
        
        return $this->create_record($data);
    }
    
    /**
     * Get pending attachments for processing
     */
    public function get_pending_attachments(int $limit = 10, int $max_retries = 3): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} 
             WHERE status IN ('pending', 'failed') 
             AND retry_count < %d
             ORDER BY priority ASC, created_at ASC 
             LIMIT %d",
            $max_retries,
            $limit
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Update attachment status
     */
    public function update_status(int $attachment_id, string $status, ?string $error = null): bool {
        $data = ['status' => $status];
        
        if ($error !== null) {
            $data['last_error'] = $error;
        }
        
        if ($status === 'completed') {
            $data['last_error'] = null;
        }
        
        return $this->update_record($attachment_id, $data);
    }
    
    /**
     * Mark attachment as processing
     */
    public function mark_processing(int $attachment_id): bool {
        return $this->update_status($attachment_id, 'processing');
    }
    
    /**
     * Mark attachment as completed
     */
    public function mark_completed(int $attachment_id): bool {
        return $this->update_status($attachment_id, 'completed');
    }
    
    /**
     * Mark attachment as failed and increment retry count
     */
    public function mark_failed(int $attachment_id, string $error = ''): bool {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->query($wpdb->prepare(
            "UPDATE {$table} 
             SET status = 'failed', 
                 retry_count = retry_count + 1, 
                 last_error = %s 
             WHERE attachment_id = %d",
            $error,
            $attachment_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        return $result !== false;
    }
    
    /**
     * Reset attachment to pending (for manual retry)
     */
    public function reset_attachment(int $attachment_id): bool {
        return $this->update_record($attachment_id, [
            'status' => 'pending',
            'retry_count' => 0,
            'last_error' => null,
        ]);
    }
    
    /**
     * Bulk reset failed attachments to pending
     */
    public function bulk_retry_failed(int $max_retries = 3): int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->query($wpdb->prepare(
            "UPDATE {$table} 
             SET status = 'pending', retry_count = 0, last_error = NULL 
             WHERE status = 'failed' AND retry_count >= %d",
            $max_retries
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        return (int) $result;
    }
    
    /**
     * Get queue/status stats
     */
    public function get_status_stats(): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $results = $wpdb->get_results("
            SELECT status, COUNT(*) as count 
            FROM {$table} 
            GROUP BY status
        ", ARRAY_A);
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        $stats = [
            'pending' => 0,
            'processing' => 0,
            'completed' => 0,
            'failed' => 0,
            'total' => 0,
        ];
        
        foreach ($results as $row) {
            $stats[$row['status']] = (int) $row['count'];
            $stats['total'] += (int) $row['count'];
        }
        
        return $stats;
    }
    
    /**
     * Get pending count
     */
    public function get_pending_count(): int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table} WHERE status = 'pending'"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get failed count
     */
    public function get_failed_count(): int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table} WHERE status = 'failed'"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get failed attachments
     */
    public function get_failed_attachments(int $limit = 100): array {
        return $this->get_records_by_status('failed', $limit);
    }
    
    /**
     * Check if attachment is offloaded (completed status)
     */
    public function is_offloaded(int $attachment_id): bool {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $status = $wpdb->get_var($wpdb->prepare(
            "SELECT status FROM {$table} WHERE attachment_id = %d",
            $attachment_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        return $status === 'completed';
    }
    
    // =========================================================================
    // MIGRATION BATCHES (sq_migration_batches)
    // =========================================================================
    
    /**
     * Create migration batch
     */
    public function create_batch(array $data = []): string {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_BATCHES);
        
        $batch_id = 'batch_' . time() . '_' . wp_generate_password(6, false, false);
        
        $insert_data = [
            'batch_id' => $batch_id,
            'status' => 'running',
            'total_attachments' => $data['total_attachments'] ?? 0,
            'start_date' => $data['start_date'] ?? null,
            'end_date' => $data['end_date'] ?? null,
            'results' => isset($data['results']) ? json_encode($data['results']) : json_encode([]),
        ];
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->insert($table, $insert_data);
        
        return $batch_id;
    }
    
    /**
     * Get batch by ID
     */
    public function get_batch(string $batch_id): ?object {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_BATCHES);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE batch_id = %s",
            $batch_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        if ($row) {
            $row->results = json_decode($row->results, true) ?: [];
        }
        
        return $row;
    }
    
    /**
     * Update batch
     */
    public function update_batch(string $batch_id, array $data): bool {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_BATCHES);
        
        if (isset($data['results']) && is_array($data['results'])) {
            $data['results'] = json_encode($data['results']);
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $table,
            $data,
            ['batch_id' => $batch_id]
        );
        
        return $result !== false;
    }
    
    /**
     * Get active batch
     */
    public function get_active_batch(): ?object {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_BATCHES);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_row(
            "SELECT * FROM {$table} WHERE status IN ('running', 'paused') ORDER BY id DESC LIMIT 1"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        if ($row) {
            $row->results = json_decode($row->results, true) ?: [];
        }
        
        return $row;
    }
    
    /**
     * Get batch history
     */
    public function get_batch_history(int $limit = 10): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_BATCHES);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} ORDER BY started_at DESC LIMIT %d",
            $limit
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        foreach ($rows as $row) {
            $row->results = json_decode($row->results, true) ?: [];
        }
        
        return $rows;
    }
    
    // =========================================================================
    // SCANS (sq_scan_results)
    // =========================================================================
    
    /**
     * Create scan record
     */
    public function create_scan(string $scan_type): string {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCANS);
        
        $scan_id = $scan_type . '_' . time() . '_' . wp_generate_password(6, false, false);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->insert($table, [
            'scan_id' => $scan_id,
            'scan_type' => $scan_type,
            'status' => 'pending',
            'results' => json_encode([]),
        ]);
        
        return $scan_id;
    }
    
    /**
     * Get scan by ID
     */
    public function get_scan(string $scan_id): ?object {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCANS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE scan_id = %s",
            $scan_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        if ($row) {
            $row->results = json_decode($row->results, true) ?: [];
        }
        
        return $row;
    }
    
    /**
     * Update scan
     */
    public function update_scan(string $scan_id, array $data): bool {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCANS);
        
        if (isset($data['results']) && is_array($data['results'])) {
            $data['results'] = json_encode($data['results']);
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $table,
            $data,
            ['scan_id' => $scan_id]
        );
        
        return $result !== false;
    }
    
    /**
     * Get active scan by type
     */
    public function get_active_scan(string $scan_type): ?object {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCANS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE scan_type = %s AND status IN ('pending', 'running', 'scanning', 'building_index', 'fixing', 'paused') ORDER BY id DESC LIMIT 1",
            $scan_type
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        if ($row) {
            $row->results = json_decode($row->results, true) ?: [];
        }
        
        return $row;
    }
    
    /**
     * Get latest completed scan by type
     */
    public function get_latest_scan(string $scan_type): ?object {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCANS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE scan_type = %s AND status = 'completed' ORDER BY scan_completed DESC LIMIT 1",
            $scan_type
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        if ($row) {
            $row->results = json_decode($row->results, true) ?: [];
        }
        
        return $row;
    }
    
    /**
     * Get currently running scan by type
     */
    public function get_running_scan(string $scan_type): ?object {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCANS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE scan_type = %s AND status IN ('running', 'scanning', 'building_index', 'fixing') ORDER BY scan_started DESC LIMIT 1",
            $scan_type
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        if ($row) {
            $row->results = json_decode($row->results, true) ?: [];
        }
        
        return $row;
    }
    
    /**
     * Get last completed scan by type (alias for get_latest_scan)
     */
    public function get_last_completed_scan(string $scan_type): ?object {
        return $this->get_latest_scan($scan_type);
    }

    /**
     * Get scan history by type (all statuses, most recent first)
     */
    public function get_scan_history(string $scan_type, int $limit = 10): array {
        global $wpdb;

        $table = self::get_table(self::TABLE_SCANS);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} WHERE scan_type = %s ORDER BY id DESC LIMIT %d",
            $scan_type,
            $limit
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ($rows as $row) {
            $row->results = json_decode($row->results, true) ?: [];
        }

        return $rows;
    }

    // =========================================================================
    // LIBRARY STATS
    // =========================================================================
    
    /**
     * Get total image attachments in WordPress
     */
    public function get_total_image_attachments(): int {
        global $wpdb;
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} 
             WHERE post_type = 'attachment' 
             AND post_mime_type LIKE 'image/%'"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get total non-image attachments in WordPress
     */
    public function get_total_other_attachments(): int {
        global $wpdb;
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} 
             WHERE post_type = 'attachment' 
             AND post_mime_type NOT LIKE 'image/%'"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get total attachments based on current settings
     * Returns only images if manage_all_media is off, all attachments if on
     */
    public function get_total_managed_attachments(): int {
        global $wpdb;
        
        if (SQMedia_Settings::is_all_media_enabled()) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            return (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->posts} 
                 WHERE post_type = 'attachment'"
            );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        }
        
        return $this->get_total_image_attachments();
    }
    
    /**
     * Get counts of registered images vs other media
     */
    public function get_registered_type_counts(): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_RECORDS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $images = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table} r
             INNER JOIN {$wpdb->posts} p ON r.attachment_id = p.ID
             WHERE p.post_mime_type LIKE 'image/%'"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $other = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table} r
             INNER JOIN {$wpdb->posts} p ON r.attachment_id = p.ID
             WHERE p.post_mime_type NOT LIKE 'image/%'"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        return [
            'images' => $images,
            'other' => $other,
            'total' => $images + $other,
        ];
    }
    
    /**
     * Get comprehensive library stats
     */
    public function get_library_stats(): array {
        $total_images = $this->get_total_image_attachments();
        $total_other = $this->get_total_other_attachments();
        $total_managed = $this->get_total_managed_attachments();
        $total_registered = $this->get_total_record_count();
        $registered_types = $this->get_registered_type_counts();
        $status_stats = $this->get_status_stats();
        $location_counts = $this->get_location_counts();
        $webp_counts = $this->get_webp_counts();
        
        return [
            'total_attachments' => $total_managed,
            'total_images' => $total_images,
            'total_other' => $total_other,
            'registered' => $total_registered,
            'registered_images' => $registered_types['images'],
            'registered_other' => $registered_types['other'],
            'unregistered' => $total_managed - $total_registered,
            'pending' => $status_stats['pending'],
            'processing' => $status_stats['processing'],
            'completed' => $status_stats['completed'],
            'failed' => $status_stats['failed'],
            'location_local' => $location_counts['local'],
            'location_bucket' => $location_counts['bucket'],
            'location_both' => $location_counts['both'],
            'with_webp' => $webp_counts['with_webp'],
            'without_webp' => $webp_counts['without_webp'],
            'manage_all_media' => SQMedia_Settings::is_all_media_enabled(),
        ];
    }
    
    // =========================================================================
    // SCAN ITEMS (sq_scan_items) - Individual issues found during scans
    // =========================================================================
    
    /**
     * Insert scan items in batch
     * 
     * @param string $scan_id Scan identifier
     * @param string $action_type Action type (e.g., 'corrupted', 'missing_size', 'upload')
     * @param array $items Array of ['attachment_id' => int, 'details' => array|null]
     * @return int Number of rows inserted
     */
    public function insert_scan_items(string $scan_id, string $action_type, array $items): int {
        global $wpdb;
        
        if (empty($items)) {
            return 0;
        }
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        $inserted = 0;
        
        // Batch insert in chunks of 100 for efficiency
        $chunks = array_chunk($items, 100);
        
        foreach ($chunks as $chunk) {
            $values = [];
            $placeholders = [];
            
            foreach ($chunk as $item) {
                $attachment_id = is_array($item) ? ($item['attachment_id'] ?? $item[0]) : $item;
                $details = is_array($item) && isset($item['details']) ? json_encode($item['details']) : null;
                
                $placeholders[] = '(%s, %s, %d, %s)';
                $values[] = $scan_id;
                $values[] = $action_type;
                $values[] = $attachment_id;
                $values[] = $details;
            }
            
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $sql = "INSERT INTO {$table} (scan_id, action_type, attachment_id, details) VALUES " . implode(', ', $placeholders);
            $wpdb->query($wpdb->prepare($sql, $values));
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $inserted += count($chunk);
        }
        
        return $inserted;
    }
    
    /**
     * Add a single scan item (for incremental additions during scan)
     */
    public function add_scan_item(string $scan_id, string $action_type, int $attachment_id, ?array $details = null): bool {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // Check if already exists for this attachment/action_type
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $existing = $wpdb->get_row($wpdb->prepare(
            "SELECT id, details FROM {$table} WHERE scan_id = %s AND action_type = %s AND attachment_id = %d",
            $scan_id, $action_type, $attachment_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        if ($existing) {
            // Aggregate: merge this file into existing items array
            $existing_details = $existing->details ? json_decode($existing->details, true) : [];
            
            // Ensure items array structure
            if (!isset($existing_details['items'])) {
                // Convert old single-item format to items array
                $existing_details = ['items' => [$existing_details]];
            }
            
            // Add new item
            $existing_details['items'][] = $details;
            
            // Update with merged details
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->update(
                $table,
                ['details' => json_encode($existing_details)],
                ['id' => $existing->id]
            ) !== false;
        }
        
        // New item - store with items array structure
        $item_details = ['items' => [$details]];
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->insert($table, [
            'scan_id' => $scan_id,
            'action_type' => $action_type,
            'attachment_id' => $attachment_id,
            'details' => json_encode($item_details),
        ]) !== false;
    }
    
    /**
     * Get scan items with pagination (for display)
     */
    public function get_scan_items(string $scan_id, string $action_type, int $limit = 100, int $offset = 0): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT attachment_id, details FROM {$table} 
             WHERE scan_id = %s AND action_type = %s 
             ORDER BY attachment_id ASC 
             LIMIT %d OFFSET %d",
            $scan_id, $action_type, $limit, $offset
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        $items = [];
        foreach ($results as $row) {
            $items[] = [
                'attachment_id' => (int) $row->attachment_id,
                'details' => $row->details ? json_decode($row->details, true) : null,
            ];
        }
        
        return $items;
    }
    
    /**
     * Get scan items with pagination, optionally filtered by action type
     * Returns raw objects with action_type, attachment_id, details
     */
    public function get_scan_items_raw(string $scan_id, ?string $action_type = null, int $limit = 100, int $offset = 0): array {
        global $wpdb;

        $table = self::get_table(self::TABLE_SCAN_ITEMS);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ($action_type) {
            return $wpdb->get_results($wpdb->prepare(
                "SELECT action_type, attachment_id, details FROM {$table}
                 WHERE scan_id = %s AND action_type = %s
                 ORDER BY attachment_id ASC
                 LIMIT %d OFFSET %d",
                $scan_id, $action_type, $limit, $offset
            ));
        }
        return $wpdb->get_results($wpdb->prepare(
            "SELECT action_type, attachment_id, details FROM {$table}
             WHERE scan_id = %s
             ORDER BY attachment_id ASC
             LIMIT %d OFFSET %d",
            $scan_id, $limit, $offset
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Get all scan items for a specific attachment
     */
    public function get_attachment_scan_items(string $scan_id, int $attachment_id): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results($wpdb->prepare(
            "SELECT action_type, details FROM {$table} 
             WHERE scan_id = %s AND attachment_id = %d",
            $scan_id, $attachment_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get all attachment IDs for an action type (for "Fix All")
     */
    public function get_scan_item_ids(string $scan_id, string $action_type): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_col($wpdb->prepare(
            "SELECT attachment_id FROM {$table} WHERE scan_id = %s AND action_type = %s ORDER BY attachment_id ASC",
            $scan_id, $action_type
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get count of items for an action type
     */
    public function get_scan_item_count(string $scan_id, string $action_type): int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE scan_id = %s AND action_type = %s",
            $scan_id, $action_type
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Get all action type counts for a scan (counts rows)
     */
    public function get_scan_item_counts(string $scan_id): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT action_type, COUNT(*) as count FROM {$table} WHERE scan_id = %s GROUP BY action_type",
            $scan_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        $counts = [];
        foreach ($results as $row) {
            $counts[$row->action_type] = (int) $row->count;
        }
        
        return $counts;
    }
    
    /**
     * Get file counts for a scan (counts individual files inside items arrays)
     */
    public function get_scan_file_counts(string $scan_id): array {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT action_type, details FROM {$table} WHERE scan_id = %s",
            $scan_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        $counts = [];
        
        foreach ($results as $row) {
            $action_type = $row->action_type;
            
            if (!isset($counts[$action_type])) {
                $counts[$action_type] = 0;
            }
            
            $details = $row->details ? json_decode($row->details, true) : [];
            
            // Count items in the array, or 1 if old single-item format
            if (isset($details['items']) && is_array($details['items'])) {
                $counts[$action_type] += count($details['items']);
            } else {
                $counts[$action_type] += 1;
            }
        }
        
        return $counts;
    }
    
    /**
     * Get count of unique attachments with issues for a scan
     */
    public function get_scan_attachments_with_issues(string $scan_id): int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT attachment_id) FROM {$table} WHERE scan_id = %s",
            $scan_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Delete scan items for a scan (cleanup)
     */
    public function delete_scan_items(string $scan_id): int {
        global $wpdb;
        
        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->query($wpdb->prepare(
            "DELETE FROM {$table} WHERE scan_id = %s",
            $scan_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }
    
    /**
     * Remove a specific item (after successful fix)
     */
    public function remove_scan_item(string $scan_id, string $action_type, int $attachment_id): bool {
        global $wpdb;

        $table = self::get_table(self::TABLE_SCAN_ITEMS);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->delete($table, [
            'scan_id' => $scan_id,
            'action_type' => $action_type,
            'attachment_id' => $attachment_id,
        ]) !== false;
    }

    /**
     * Get distinct item IDs (attachment_id) that have any of the given action types
     */
    public function get_scan_items_distinct_ids(string $scan_id, array $action_types): array {
        global $wpdb;

        $table = self::get_table(self::TABLE_SCAN_ITEMS);
        $placeholders = implode(', ', array_fill(0, count($action_types), '%s'));

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $ids = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT attachment_id FROM {$table}
             WHERE scan_id = %s AND action_type IN ($placeholders)
             ORDER BY attachment_id ASC",
            ...array_merge([$scan_id], $action_types)
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return array_map('intval', $ids);
    }

    /**
     * Get all scan items for a specific item ID (all categories)
     */
    public function get_scan_items_for_id(string $scan_id, int $item_id): array {
        global $wpdb;

        $table = self::get_table(self::TABLE_SCAN_ITEMS);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results($wpdb->prepare(
            "SELECT action_type, details FROM {$table}
             WHERE scan_id = %s AND attachment_id = %d",
            $scan_id, $item_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Count scan items, optionally filtered by action type
     */
    public function count_scan_items(string $scan_id, ?string $action_type = null): int {
        global $wpdb;

        $table = self::get_table(self::TABLE_SCAN_ITEMS);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ($action_type) {
            return (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE scan_id = %s AND action_type = %s",
                $scan_id, $action_type
            ));
        }
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE scan_id = %s",
            $scan_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Get paginated distinct item IDs (attachment_id / post_id) with optional category filter
     */
    public function get_scan_distinct_items_paginated(string $scan_id, ?string $action_type = null, int $limit = 20, int $offset = 0): array {
        global $wpdb;

        $table = self::get_table(self::TABLE_SCAN_ITEMS);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ($action_type) {
            $ids = $wpdb->get_col($wpdb->prepare(
                "SELECT DISTINCT attachment_id FROM {$table}
                 WHERE scan_id = %s AND action_type = %s
                 ORDER BY attachment_id ASC
                 LIMIT %d OFFSET %d",
                $scan_id, $action_type, $limit, $offset
            ));
        } else {
            $ids = $wpdb->get_col($wpdb->prepare(
                "SELECT DISTINCT attachment_id FROM {$table}
                 WHERE scan_id = %s
                 ORDER BY attachment_id ASC
                 LIMIT %d OFFSET %d",
                $scan_id, $limit, $offset
            ));
        }
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return array_map('intval', $ids);
    }

    /**
     * Count distinct item IDs with optional category filter
     */
    public function count_scan_distinct_items(string $scan_id, ?string $action_type = null): int {
        global $wpdb;

        $table = self::get_table(self::TABLE_SCAN_ITEMS);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ($action_type) {
            return (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT attachment_id) FROM {$table}
                 WHERE scan_id = %s AND action_type = %s",
                $scan_id, $action_type
            ));
        }
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT attachment_id) FROM {$table}
             WHERE scan_id = %s",
            $scan_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    // =========================================================================
    // TABLE BACKUP METHODS
    // =========================================================================

    /**
     * Create backup of WordPress tables for a scanner
     *
     * @param string $scanner 'mls' (Media Library Scanner) or 'pcs' (Post Content Scanner)
     * @return array{success: bool, tables: array, error?: string}
     */
    public static function create_table_backup(string $scanner): array {
        global $wpdb;

        if ($scanner === 'mls') {
            $tables = [
                $wpdb->postmeta => self::get_table(self::TABLE_BACKUP_MLS_POSTMETA),
                $wpdb->posts    => self::get_table(self::TABLE_BACKUP_MLS_POSTS),
            ];
            $option_key = 'sqmedia_backup_mls_at';
        } elseif ($scanner === 'pcs') {
            $tables = [
                $wpdb->posts => self::get_table(self::TABLE_BACKUP_PCS_POSTS),
            ];
            $option_key = 'sqmedia_backup_pcs_at';
        } else {
            return ['success' => false, 'error' => 'Invalid scanner type'];
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        foreach ($tables as $source => $backup) {
            $wpdb->query("DROP TABLE IF EXISTS {$backup}");
            // CREATE TABLE ... LIKE copies full structure: indexes, auto_increment, primary keys
            $result = $wpdb->query("CREATE TABLE {$backup} LIKE {$source}");
            if ($result === false) {
                return [
                    'success' => false,
                    'error' => sprintf(
                        /* translators: %s: table name */
                        __('Failed to backup table %s', 'staticq-media'),
                        $source
                    ),
                ];
            }
            $result = $wpdb->query("INSERT INTO {$backup} SELECT * FROM {$source}");
            if ($result === false) {
                $wpdb->query("DROP TABLE IF EXISTS {$backup}");
                return [
                    'success' => false,
                    'error' => sprintf(
                        /* translators: %s: table name */
                        __('Failed to copy data for table %s', 'staticq-media'),
                        $source
                    ),
                ];
            }
        }
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        update_option($option_key, current_time('mysql'));

        return [
            'success' => true,
            'tables'  => array_values($tables),
        ];
    }

    /**
     * Get backup status for a scanner
     *
     * @param string $scanner 'mls' or 'pcs'
     * @return array{exists: bool, date?: string, tables?: array}
     */
    public static function get_backup_status(string $scanner): array {
        global $wpdb;

        if ($scanner === 'mls') {
            $option_key = 'sqmedia_backup_mls_at';
            $backup_tables = [
                self::get_table(self::TABLE_BACKUP_MLS_POSTMETA),
                self::get_table(self::TABLE_BACKUP_MLS_POSTS),
            ];
        } elseif ($scanner === 'pcs') {
            $option_key = 'sqmedia_backup_pcs_at';
            $backup_tables = [
                self::get_table(self::TABLE_BACKUP_PCS_POSTS),
            ];
        } else {
            return ['exists' => false];
        }

        $date = get_option($option_key, '');
        if (empty($date)) {
            return ['exists' => false];
        }

        // Verify at least one backup table actually exists
        $first_table = $backup_tables[0];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $first_table));
        if (!$table_exists) {
            delete_option($option_key);
            return ['exists' => false];
        }

        return [
            'exists' => true,
            'date'   => $date,
            'tables' => $backup_tables,
        ];
    }
}