<?php
/**
 * File State Scanner for StaticQ
 *
 * Unified health check system: one scan, one fix, one report.
 * Checks both metadata alignment AND file existence per attachment atomically.
 *
 * Handler logic is in:
 * - class-sqmedia-file-state-scanner-phase1.php (metadata checks/fixes)
 * - class-sqmedia-file-state-scanner-phase2.php (file existence checks/fixes + index building)
 *
 * @package StaticQ
 * @since 3.0.0
 */

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

// Load phase-specific handlers
require_once __DIR__ . '/class-sqmedia-file-state-scanner-phase1.php';
require_once __DIR__ . '/class-sqmedia-file-state-scanner-phase2.php';

class SQMedia_File_State_Scanner extends SQMedia_Batch_Processor {
    
    // =========================================================================
    // CONFIGURATION
    // =========================================================================
    
    /**
     * Scan type identifier - MUST match what renderer expects
     */
    protected $scan_type = 'file_state';
    
    /**
     * Cron hook name for background processing
     */
    protected $cron_hook = 'sqmedia_file_state_scan_continue';
    
    /**
     * Upload directory info cache
     */
    protected array $upload_dir;
    
    /**
     * Phase 1 handler
     */
    private SQMedia_File_State_Scanner_Phase1 $phase1;
    
    /**
     * Phase 2 handler
     */
    private SQMedia_File_State_Scanner_Phase2 $phase2;
    
    // =========================================================================
    // ISSUE CATEGORIES - Used by both scanner and UI
    // Names must match what SQMedia_Health_Check_Renderer expects
    // =========================================================================
    
    // Phase 1: Database alignment issues
    const CAT_CORRUPTED = 'corrupted';              // Missing dimensions or unreadable metadata
    const CAT_WRONG_DIMENSIONS = 'wrong_dimensions'; // Size has wrong width/height
    const CAT_MISSING_SIZES = 'missing_sizes';       // Size should exist but no metadata entry
    const CAT_DEPRECATED_SIZES = 'deprecated_sizes'; // Size in metadata but not in current theme
    const CAT_MISSING_WEBP = 'missing_webp';         // WebP enabled but missing -webp entries
    const CAT_POST_CONTENT_REF = 'post_content_ref'; // Stale file references in post_content

    // Phase 2: File alignment issues
    const CAT_UPLOAD_TO_BUCKET = 'upload_to_bucket';     // File exists local, needs upload to bucket
    const CAT_DOWNLOAD_TO_LOCAL = 'download_to_local';   // File exists bucket, needs download to local
    const CAT_GENERATE_SIZES = 'generate_sizes';         // File doesn't exist anywhere, needs generation
    const CAT_CRITICAL_MISSING = 'critical_missing';     // Master missing everywhere - unrecoverable

    // Original image issues
    const CAT_ORIGINAL_MISPLACED = 'original_misplaced';   // Original exists but in wrong location per settings
    const CAT_ORIGINAL_POLICY = 'original_policy';         // Original exists but policy says discard, or scaling alignment needed
    const CAT_ORIGINAL_MISSING = 'original_missing';       // original_image in metadata but file not found anywhere (info only, no fix)
    
    // =========================================================================
    // CONSTRUCTOR
    // =========================================================================
    
    public function __construct() {
        parent::__construct();
        $this->upload_dir = wp_upload_dir();
        
        // Initialize phase handlers
        $this->phase1 = new SQMedia_File_State_Scanner_Phase1($this);
        $this->phase2 = new SQMedia_File_State_Scanner_Phase2($this);
    }
    
    /**
     * Initialize batch size from config
     *
     * Overridable via wp-config.php: define('SQMEDIA_SCAN_BATCH_SIZE', 50);
     * Default: 25 (each item does metadata + file index lookups)
     * Range: 5–100
     */
    protected function init_batch_size(): void {
        $this->batch_size = defined('SQMEDIA_SCAN_BATCH_SIZE')
            ? max(5, min(100, (int) SQMEDIA_SCAN_BATCH_SIZE))
            : 25;
    }

    /**
     * Get fix batch size
     *
     * Unified fix does metadata + file operations per item, default 1.
     * Overridable via wp-config.php: define('SQMEDIA_FIX_BATCH_SIZE', 3);
     * Range: 1–10
     */
    protected function get_fix_batch_size(): int {
        return defined('SQMEDIA_FIX_BATCH_SIZE')
            ? max(1, min(10, (int) SQMEDIA_FIX_BATCH_SIZE))
            : 1;
    }
    
    /**
     * Get human-readable label
     */
    public function get_label(): string {
        return __('File State Scanner', 'staticq-media');
    }
    
    /**
     * Get upload directory info
     */
    public function get_upload_dir(): array {
        return $this->upload_dir;
    }
    
    /**
     * Get database instance (for phase handlers)
     */
    public function get_db(): SQMedia_Database {
        return $this->db;
    }
    
    /**
     * Get Phase 2 handler instance
     */
    public function get_phase2_handler(): SQMedia_File_State_Scanner_Phase2 {
        return $this->phase2;
    }
    
    // =========================================================================
    // PUBLIC API - Start/Resume Methods
    // =========================================================================
    
    /**
     * Start unified scan (metadata + file alignment in one pass)
     *
     * @param bool $overwrite_existing When true, flags all non-master files for regeneration
     * @return array Scan start result
     */
    public function start_scan(bool $overwrite_existing = false): array {
        return $this->phase2->start_unified_scan($overwrite_existing);
    }

    /**
     * Start Phase 1 scan (Database Alignment)
     *
     * @deprecated Use start_scan() instead
     */
    public function start_phase1(): array {
        return $this->start_scan();
    }

    /**
     * Start Phase 2 scan (File Alignment)
     *
     * @deprecated Use start_scan() instead
     */
    public function start_phase2(): array {
        return $this->start_scan();
    }
    
    /**
     * Get current progress for a scan
     */
    public function get_progress(string $scan_id): array {
        $result = parent::get_progress($scan_id);

        if (!$result['success']) {
            return $result;
        }

        // Add index-building info if applicable
        $scan = $this->db->get_scan($scan_id);

        if ($scan->status === 'building_index') {
            $index_status = $scan->results['index_status'] ?? 'starting';
            $index_file_count = $scan->results['index_file_count'] ?? 0;
            $index_current_folder = $scan->results['index_current_folder'] ?? '';

            $messages = [
                'pending' => __('Starting file index...', 'staticq-media'),
                'triggered' => __('Starting file index...', 'staticq-media'),
                'starting' => __('Initializing...', 'staticq-media'),
                'building_local' => __('Scanning local files...', 'staticq-media'),
                'building_bucket' => __('Scanning bucket files...', 'staticq-media'),
                'complete' => __('Index complete, starting scan...', 'staticq-media'),
            ];

            $base_message = $messages[$index_status] ?? $messages['starting'];

            if ($index_current_folder && !in_array($index_status, ['pending', 'triggered', 'starting'])) {
                /* translators: %s: folder path being scanned */
                $base_message .= "\n" . sprintf(__('Scanning: %s', 'staticq-media'), $index_current_folder);
            }

            $result['phase_message'] = $base_message;
            $result['index_file_count'] = $index_file_count;
            $result['index_status'] = $index_status;
            $result['progress'] = 0;
        }

        return $result;
    }
    
    // =========================================================================
    // ABSTRACT METHOD IMPLEMENTATIONS
    // =========================================================================
    
    /**
     * Get all registered attachment IDs to scan
     */
    protected function get_items_to_process(array $options = []): array {
        return $this->db->get_all_registered_ids();
    }
    
    /**
     * Called before start - set phase in results
     *
     * Note: Unified scans bypass this (start_scan → start_unified_scan creates its own record).
     * This is only called for legacy direct calls to start(['phase' => N]).
     */
    protected function on_before_start(string $scan_id, array $options): array {
        $phase = $options['phase'] ?? 'unified';

        // Clean up old scan items
        $this->cleanup_old_scan_items();

        // Update results with phase info
        $this->db->update_scan($scan_id, [
            'results' => [
                'current_phase' => $phase,
                'summary' => [
                    'total' => 0,
                    'processed' => 0,
                    'clean' => 0,
                    'with_issues' => 0,
                    'sizes_expected' => 0,
                    'sizes_found' => 0,
                    'files_checked' => 0,
                    'files_ok' => 0,
                    'files_with_issues' => 0,
                ],
            ],
        ]);

        return ['ready' => true];
    }
    
    /**
     * Called when scan is cancelled
     * Clean up cron events and temporary data
     */
    protected function on_cancel(string $scan_id): void {
        // Clear any scheduled cron events for this scan
        wp_clear_scheduled_hook('sqmedia_file_state_scan_continue');
        $this->phase2->clear_all_index_build_crons();
        
        // Clean up file indexes
        $this->phase2->cleanup_indexes($scan_id);
        
        // Delete the transient
        delete_transient('sqmedia_scan_ids_' . $scan_id);
        
    }
    
    /**
     * Clean up old scan items when starting a new scan
     *
     * Deletes scan items from previous scans to prevent table bloat.
     * Keeps the most recent completed scan record for history/display.
     */
    public function cleanup_old_scan_items(): int {
        global $wpdb;

        $scans_table = SQMedia_Database::get_table(SQMedia_Database::TABLE_SCANS);
        $items_table = SQMedia_Database::get_table(SQMedia_Database::TABLE_SCAN_ITEMS);

        // Get all file_state scans (except currently active ones)
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $old_scans = $wpdb->get_results(
            "SELECT scan_id, results, status, scan_completed, scan_started FROM {$scans_table}
             WHERE scan_type = 'file_state'
             AND status NOT IN ('pending', 'running', 'scanning', 'building_index', 'fixing')
             ORDER BY COALESCE(scan_completed, scan_started) DESC"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        if (empty($old_scans)) {
            return 0;
        }

        $scan_ids_to_clean_items = [];
        $scan_ids_to_delete = [];
        $kept_completed = false;

        foreach ($old_scans as $scan) {
            // Keep the most recent completed scan record AND its items for display
            if (!$kept_completed && $scan->status === 'completed') {
                $kept_completed = true;
                continue;
            }

            $scan_ids_to_clean_items[] = $scan->scan_id;
            $scan_ids_to_delete[] = $scan->scan_id;
        }

        $deleted = 0;

        if (!empty($scan_ids_to_clean_items)) {
            $placeholders = implode(',', array_fill(0, count($scan_ids_to_clean_items), '%s'));
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $deleted = $wpdb->query(
                $wpdb->prepare(
                    "DELETE FROM {$items_table} WHERE scan_id IN ({$placeholders})",
                    ...$scan_ids_to_clean_items
                )
            );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
        }

        if (!empty($scan_ids_to_delete)) {
            $placeholders = implode(',', array_fill(0, count($scan_ids_to_delete), '%s'));
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $wpdb->query(
                $wpdb->prepare(
                    "DELETE FROM {$scans_table} WHERE scan_id IN ({$placeholders})",
                    ...$scan_ids_to_delete
                )
            );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
        }

        if ($deleted > 0 || !empty($scan_ids_to_delete)) {
        }

        // Also clean up old index files
        $this->phase2->cleanup_all_old_indexes();

        return $deleted;
    }
    
    /**
     * Build processing context for batch
     */
    protected function build_context(string $scan_id): array {
        $scan = $this->db->get_scan($scan_id);
        $results = $scan->results ?? [];

        $context = [
            'scan_id' => $scan_id,
            'upload_dir' => wp_upload_dir(null, false),
            'storage_destination' => SQMedia_Settings::get_storage_destination(),
            'delete_local' => SQMedia_Settings::should_delete_local_files(),
            'webp_enabled' => SQMedia_Settings::is_webp_enabled(),
            'enabled_sizes' => SQMedia_Settings::get_enabled_sizes(),
            'overwrite_existing' => $results['overwrite_existing'] ?? false,
        ];

        // Legacy support: if this is an old phase-specific scan, include phase
        $current_phase = $results['current_phase'] ?? 'unified';
        if ($current_phase !== 'unified') {
            $context['phase'] = (int) $current_phase;
        }

        return $context;
    }
    
    /**
     * Process a single attachment - unified scan (metadata + files)
     */
    protected function process_single_item(int $attachment_id, array $context): array {
        // Legacy support: old phase-specific scans
        if (isset($context['phase'])) {
            if ($context['phase'] === 1) {
                $result = $this->phase1->scan_attachment($attachment_id, $context);
                // Align storage_location only for clean attachments (broken ones get aligned after fix)
                if (empty($result['issues'])) {
                    $this->db->update_record($attachment_id, [
                        'storage_location' => $context['storage_destination'],
                    ]);
                }
                return $result;
            }
            return $this->phase2->scan_attachment($attachment_id, $context);
        }

        // ===== UNIFIED SCAN =====

        // 1. Phase 1: metadata issues
        $p1_result = $this->phase1->scan_attachment($attachment_id, $context);

        // If Phase 1 already determined critical_missing, skip Phase 2
        // (it would just store a duplicate critical_missing for the same master)
        $p1_issues = $p1_result['issues'] ?? [];
        if (in_array(self::CAT_CRITICAL_MISSING, $p1_issues, true)) {
            return [
                'success' => $p1_result['success'] ?? false,
                'issues' => $p1_issues,
                'sizes_expected' => $p1_result['sizes_expected'] ?? 0,
                'sizes_found' => $p1_result['sizes_found'] ?? 0,
                'files_checked' => 0,
                'files_ok' => 0,
                'files_with_issues' => 0,
            ];
        }

        // 3. Compute target sizes (bridges metadata → file checks)
        $target_sizes = $this->phase1->compute_target_sizes($attachment_id, $context);

        // 4. Phase 2: file issues using target sizes
        $p2_result = $this->phase2->scan_attachment_with_target($attachment_id, $context, $target_sizes);

        // 5. Merge results
        $all_issues = array_unique(array_merge(
            $p1_result['issues'] ?? [],
            $p2_result['issues'] ?? []
        ));

        // 6. Align state flags for clean attachments
        // Clean attachments won't go through the fix path, so this is the only chance to set these.
        // Broken attachments get aligned after their fix completes.
        if (empty($all_issues)) {
            $record = SQMedia_Attachment_Record::find($attachment_id);
            $desired_webp = ($record && $record->supports_webp() && SQMedia_Settings::is_webp_enabled()) ? 1 : 0;
            $update = [
                'storage_location' => $context['storage_destination'],
                'has_webp' => $desired_webp,
            ];

            // Align original_location with current settings
            if ($record && $record->get_original_image()) {
                $handling = SQMedia_Settings::get_original_file_handling();
                if ($handling === 'move_to_originals') {
                    $update['original_location'] = 'originals';
                } elseif ($handling === 'discard') {
                    $update['original_location'] = null;
                } else {
                    $update['original_location'] = 'alongside';
                }
            }

            $this->db->update_record($attachment_id, $update);
        }

        return [
            'success' => ($p1_result['success'] ?? false) && ($p2_result['success'] ?? false),
            'issues' => $all_issues,
            // Phase 1 counts
            'sizes_expected' => $p1_result['sizes_expected'] ?? 0,
            'sizes_found' => $p1_result['sizes_found'] ?? 0,
            // Phase 2 counts
            'files_checked' => $p2_result['files_checked'] ?? 0,
            'files_ok' => $p2_result['files_ok'] ?? 0,
            'files_with_issues' => $p2_result['files_with_issues'] ?? 0,
        ];
    }

    /**
     * On batch complete - update issue counts for live display
     */
    protected function on_batch_complete(string $scan_id, array $batch_results, array $cumulative_results): void {
        // Get current counts from scan_items table
        $attachment_counts = $this->db->get_scan_item_counts($scan_id);
        $file_counts = $this->db->get_scan_file_counts($scan_id);
        
        // Update results
        $cumulative_results['issue_counts'] = $attachment_counts;  // Attachments per category
        $cumulative_results['file_counts'] = $file_counts;          // Files per category
        $cumulative_results['total_issues'] = array_sum($attachment_counts);
        $cumulative_results['total_issue_files'] = array_sum($file_counts);
        
        $this->db->update_scan($scan_id, [
            'results' => $cumulative_results,
        ]);
    }
    
    /**
     * On scan complete - finalize results
     */
    protected function on_complete(string $scan_id, array $results): void {
        // Get final counts
        $attachment_counts = $this->db->get_scan_item_counts($scan_id);
        $file_counts = $this->db->get_scan_file_counts($scan_id);
        
        $results['issue_counts'] = $attachment_counts;
        $results['file_counts'] = $file_counts;
        $results['total_issues'] = array_sum($attachment_counts);
        $results['total_issue_files'] = array_sum($file_counts);
        
        $this->db->update_scan($scan_id, [
            'results' => $results,
        ]);
        
        $this->log('Scan completed', [
            'scan_id' => $scan_id,
            'total' => $results['summary']['total'] ?? 0,
            'with_issues' => $results['summary']['with_issues'] ?? 0,
            'files_checked' => $results['summary']['files_checked'] ?? 0,
            'files_ok' => $results['summary']['files_ok'] ?? 0,
            'issue_counts' => $attachment_counts,
            'file_counts' => $file_counts,
        ]);

        // Log summary to debug log for scan-to-scan comparison
        if (defined('WP_DEBUG_LOG') && WP_DEBUG_LOG) {
            $bucket_count = $results['bucket_file_count'] ?? 'unknown';
            $local_count = $results['local_file_count'] ?? 'unknown';
            $critical = $attachment_counts[self::CAT_CRITICAL_MISSING] ?? 0;
            $critical_files = $file_counts[self::CAT_CRITICAL_MISSING] ?? 0;
        }
    }
    
    /**
     * Recover item IDs if transient was lost
     * 
     * For file state scanner, we can recover by getting all registered attachment IDs
     */
    protected function recover_item_ids(string $scan_id, object $scan): ?array {
        // Get all registered attachment IDs
        $item_ids = $this->db->get_all_registered_ids();

        if (empty($item_ids)) {
            return null;
        }


        return $item_ids;
    }
    
    // =========================================================================
    // SHARED UTILITY METHODS
    // =========================================================================
    
    /**
     * Store an issue in the scan_items table
     */
    public function store_issue(string $scan_id, string $category, int $attachment_id, ?array $details = null): void {
        $this->db->add_scan_item($scan_id, $category, $attachment_id, $details ?? []);
    }
    
    /**
     * Calculate expected dimensions for a size based on WordPress resize logic
     * 
     * Uses WordPress's wp_constrain_dimensions() to properly handle
     * cases where max_w or max_h is 0 (meaning "auto scale").
     * 
     * @param int $orig_w Original width
     * @param int $orig_h Original height
     * @param int $max_w Maximum width for this size
     * @param int $max_h Maximum height for this size
     * @param bool $crop Whether this size uses cropping
     * @return array|null Expected dimensions or null if image too small
     */
    public function calculate_expected_dimensions(int $orig_w, int $orig_h, int $max_w, int $max_h, bool $crop): ?array {
        // Handle 0 values for target (meaning "no constraint on this dimension")
        if ($max_w <= 0 && $max_h <= 0) {
            return null; // No constraints at all
        }
        
        // If master dimensions are unknown/corrupted, assume master is large enough
        // to generate all sizes. This is a reasonable assumption since:
        // 1. Editors typically upload images larger than theme sizes
        // 2. Small source images are rare in modern workflows
        // 3. This prevents confusing count discrepancies in the UI
        if ($orig_w <= 0 || $orig_h <= 0) {
            if ($crop) {
                // Cropped: need both dimensions specified
                if ($max_w <= 0 || $max_h <= 0) {
                    return null;
                }
                return [
                    'width' => $max_w,
                    'height' => $max_h,
                ];
            }
            // Non-cropped: if either dimension is unconstrained (0), we can't
            // compute a proper filename without the master's aspect ratio
            if ($max_w <= 0 || $max_h <= 0) {
                return null;
            }
            return [
                'width' => $max_w,
                'height' => $max_h,
            ];
        }
        
        // Use large value for unconstrained dimension
        $constrain_w = $max_w > 0 ? $max_w : 9999;
        $constrain_h = $max_h > 0 ? $max_h : 9999;
        
        if ($crop) {
            // Cropped: need both dimensions specified
            if ($max_w <= 0 || $max_h <= 0) {
                return null; // Can't crop without both dimensions
            }
            
            // If original is smaller than crop target, no crop possible
            if ($orig_w < $max_w || $orig_h < $max_h) {
                return null;
            }
            
            return [
                'width' => $max_w,
                'height' => $max_h,
            ];
        }
        
        // Use WordPress function for proportional resize
        list($dest_w, $dest_h) = wp_constrain_dimensions($orig_w, $orig_h, $constrain_w, $constrain_h);
        
        // If dimensions didn't change, no resize needed
        if ($dest_w >= $orig_w && $dest_h >= $orig_h) {
            return null;
        }
        
        return [
            'width' => $dest_w,
            'height' => $dest_h,
        ];
    }
    
    // =========================================================================
    // FIXING - Routes to appropriate phase handler
    // =========================================================================
    
    /**
     * Fix all issues from a scan
     */
    public function fix_all(string $scan_id): array {
        $scan = $this->db->get_scan($scan_id);

        if (!$scan) {
            return ['success' => false, 'error' => __('Scan not found.', 'staticq-media')];
        }

        $current_phase = $scan->results['current_phase'] ?? 'unified';

        if ($current_phase === 1) {
            return $this->phase1->fix_all($scan_id, $scan);
        }

        if ($current_phase === 2) {
            return $this->phase2->fix_all($scan_id, $scan);
        }

        // Unified: fix both phases
        $p1 = $this->phase1->fix_all($scan_id, $scan);
        $p2 = $this->phase2->fix_all($scan_id, $scan);

        return [
            'success' => true,
            'total' => ($p1['total'] ?? 0) + ($p2['total'] ?? 0),
            'fixed' => ($p1['fixed'] ?? 0) + ($p2['fixed'] ?? 0),
            'failed' => ($p1['failed'] ?? 0) + ($p2['failed'] ?? 0),
            'errors' => array_merge($p1['errors'] ?? [], $p2['errors'] ?? []),
        ];
    }
    
    /**
     * Start fix operation with cron-based processing
     * 
     * @param string $scan_id Scan ID
     * @return array Result with success status
     */
    public function start_fix(string $scan_id): array {
        global $wpdb;
        
        $scan = $this->db->get_scan($scan_id);
        
        if (!$scan) {
            return ['success' => false, 'error' => __('Scan not found.', 'staticq-media')];
        }
        
        // Clear any stuck lock from previous run
        delete_transient('sqmedia_fix_lock_' . $scan_id);
        
        // Get list of attachment IDs to fix (excluding critical missing)
        $table = SQMedia_Database::get_table(SQMedia_Database::TABLE_SCAN_ITEMS);
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $attachment_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",
            $scan_id,
            self::CAT_CRITICAL_MISSING
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        if (empty($attachment_ids)) {
            return ['success' => false, 'error' => __('No fixable issues found.', 'staticq-media')];
        }

        $attachment_ids = array_map('intval', $attachment_ids);
        $total = count($attachment_ids);

        // Store fix queue in transient
        set_transient('sqmedia_fix_ids_' . $scan_id, $attachment_ids, WEEK_IN_SECONDS);

        // Test bucket public access if we have download operations
        // This determines whether we use fast HTTP downloads or slower SDK
        $bucket_is_public = false;
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $download_items = $wpdb->get_results($wpdb->prepare(
            "SELECT details FROM {$table} WHERE scan_id = %s AND action_type = %s LIMIT 1",
            $scan_id,
            self::CAT_DOWNLOAD_TO_LOCAL
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        
        
        if (!empty($download_items)) {
            $raw_details = $download_items[0]->details;
            // Details could be JSON string or serialized array
            if (is_string($raw_details) && strpos($raw_details, '{') === 0) {
                $details = json_decode($raw_details, true);
            } else {
                $details = maybe_unserialize($raw_details);
            }
            
            // Details can be {file: ...} or {items: [{file: ...}, ...]}
            $test_file = null;
            if (!empty($details['file'])) {
                $test_file = $details['file'];
            } elseif (!empty($details['items'][0]['file'])) {
                $test_file = $details['items'][0]['file'];
            }
            
            if ($test_file) {
                $s3 = sqmedia()->get_storage_handler();
                if ($s3) {
                    $test_path = SQMedia_Settings::get_current_prefix() . '/' . $test_file;
                    $bucket_is_public = $s3->test_bucket_public_access($test_path);
                } else {
                }
            } else {
            }
        } else {
        }
        
        // Update scan status to fixing
        $results = $scan->results;
        $results['fix_total'] = $total;
        $results['fix_processed'] = 0;
        $results['fix_success'] = 0;
        $results['fix_errors'] = [];
        $results['fix_started_at'] = current_time('mysql');
        $results['bucket_is_public'] = $bucket_is_public;
        
        $this->db->update_scan($scan_id, [
            'status' => 'fixing',
            'results' => $results,
        ]);
        
        // Schedule cron for background processing
        if (!wp_next_scheduled('sqmedia_file_state_fix_continue', [$scan_id])) {
            wp_schedule_single_event(time() + 1, 'sqmedia_file_state_fix_continue', [$scan_id]);
        }
        
        return [
            'success' => true,
            'scan_id' => $scan_id,
            'total' => $total,
            'bucket_is_public' => $bucket_is_public,
        ];
    }
    
    /**
     * Get fix progress (read-only, no processing)
     * 
     * Used by AJAX polling to show progress without blocking.
     * Cron handles actual processing.
     * 
     * @param string $scan_id Scan ID
     * @return array Progress information
     */
    public function get_fix_progress(string $scan_id): array {
        $scan = $this->db->get_scan($scan_id);
        
        if (!$scan) {
            return ['success' => false, 'error' => __('Scan not found.', 'staticq-media')];
        }
        
        $results = $scan->results;
        $total = $results['fix_total'] ?? 0;
        $processed = $results['fix_processed'] ?? 0;
        $progress = $total > 0 ? round(($processed / $total) * 100) : 0;
        
        // Check if completed or cancelled
        if (in_array($scan->status, ['completed', 'cancelled', 'failed'], true)) {
            return [
                'success' => true,
                'done' => true,
                'status' => $scan->status,
                'total' => $total,
                'processed' => $processed,
                'fixed' => $results['fix_success'] ?? 0,
                'errors' => count($results['fix_errors'] ?? []),
                'progress' => 100,
            ];
        }
        
        // Check if paused
        if ($scan->status === 'paused') {
            return [
                'success' => true,
                'done' => false,
                'status' => 'paused',
                'total' => $total,
                'processed' => $processed,
                'fixed' => $results['fix_success'] ?? 0,
                'errors' => count($results['fix_errors'] ?? []),
                'progress' => $progress,
            ];
        }
        
        // Ensure cron is scheduled (safety net if it missed a tick)
        if (!wp_next_scheduled('sqmedia_file_state_fix_continue', [$scan_id])) {
            wp_schedule_single_event(time() + 1, 'sqmedia_file_state_fix_continue', [$scan_id]);
        }

        // In progress
        return [
            'success' => true,
            'done' => false,
            'status' => 'fixing',
            'total' => $total,
            'processed' => $processed,
            'fixed' => $results['fix_success'] ?? 0,
            'errors' => count($results['fix_errors'] ?? []),
            'progress' => $progress,
        ];
    }
    
    /**
     * Process a batch of fixes (called via AJAX polling or cron)
     * 
     * @param string $scan_id Scan ID
     * @return array Progress information
     */
    public function process_fix_batch(string $scan_id): array {

        // Prevent concurrent processing (AJAX + cron race condition)
        $lock_key = 'sqmedia_fix_lock_' . $scan_id;
        if (get_transient($lock_key)) {
            return $this->get_fix_progress($scan_id);
        }
        set_transient($lock_key, time(), 60);

        $scan = $this->db->get_scan($scan_id);
        
        if (!$scan) {
            delete_transient($lock_key);
            return ['success' => false, 'error' => __('Scan not found.', 'staticq-media')];
        }

        // Check if completed or cancelled (may already be done via cron)
        if (in_array($scan->status, ['completed', 'cancelled', 'failed'], true)) {
            delete_transient($lock_key);
            return [
                'success' => true,
                'done' => true,
                'status' => $scan->status,
                'total' => $scan->results['fix_total'] ?? 0,
                'processed' => $scan->results['fix_processed'] ?? 0,
                'fixed' => $scan->results['fix_success'] ?? 0,
                'errors' => count($scan->results['fix_errors'] ?? []),
            ];
        }

        // Check if paused
        if ($scan->status === 'paused') {
            delete_transient($lock_key);
            return [
                'success' => true,
                'done' => false,
                'status' => 'paused',
                'total' => $scan->results['fix_total'] ?? 0,
                'processed' => $scan->results['fix_processed'] ?? 0,
                'fixed' => $scan->results['fix_success'] ?? 0,
                'errors' => count($scan->results['fix_errors'] ?? []),
            ];
        }
        
        // Get fix queue
        $attachment_ids = get_transient('sqmedia_fix_ids_' . $scan_id);
        
        if (!$attachment_ids || !is_array($attachment_ids)) {
            // Transient expired — recover remaining items from DB
            $attachment_ids = $this->recover_fix_queue($scan_id);
            if (!$attachment_ids) {
                $this->db->update_scan($scan_id, ['status' => 'failed']);
                return ['success' => false, 'error' => __('Fix queue lost.', 'staticq-media')];
            }

            // Recovery returns only remaining (unfixed) items, so reset index to 0
            $results = $scan->results;
            $already_done = ($results['fix_total'] ?? 0) - count($attachment_ids);
            $results['fix_processed'] = max(0, $already_done);
            $this->db->update_scan($scan_id, ['results' => $results]);

            set_transient('sqmedia_fix_ids_' . $scan_id, $attachment_ids, WEEK_IN_SECONDS);

            // Re-fetch scan to get updated results
            $scan = $this->db->get_scan($scan_id);
        }

        $results = $scan->results;
        $processed = $results['fix_processed'] ?? 0;
        $total = $results['fix_total'] ?? count($attachment_ids);
        $current_phase = $results['current_phase'] ?? 'unified';
        $is_unified = ($current_phase === 'unified');
        $batch_size = $this->get_fix_batch_size();

        // Map processed count to queue index (queue may be shorter after recovery)
        $queue_index = $processed - (max(0, $total - count($attachment_ids)));
        $queue_index = max(0, $queue_index);

        // Process batch
        $batch_end = min($processed + $batch_size, $total);

        for ($i = $processed; $i < $batch_end; $i++) {
            $attachment_id = $attachment_ids[$queue_index] ?? null;
            $queue_index++;

            if (!$attachment_id) {
                break;
            }

            if ($is_unified) {
                // Unified fix: metadata first, then files — atomic per attachment
                $p1_result = $this->phase1->fix_attachment($attachment_id, $scan_id);
                $p2_result = $this->phase2->fix_attachment($attachment_id, $scan_id);

                // Large file upload still in progress — don't advance, retry next pass
                if (!empty($p2_result['chunked_upload_pending'])) {
                    break;
                }

                $total_fixed = ($p1_result['fixed'] ?? 0) + ($p2_result['fixed'] ?? 0);
                $all_errors = array_merge($p1_result['errors'] ?? [], $p2_result['errors'] ?? []);

                $fix_result = [
                    'success' => $total_fixed > 0 && empty($all_errors),
                    'fixed' => $total_fixed,
                    'errors' => $all_errors,
                ];

                // Align state flags only when all fixes are complete
                $this->align_state_flags_after_fix($attachment_id, $scan_id);
            } elseif ($current_phase === 1) {
                $fix_result = $this->phase1->fix_attachment($attachment_id, $scan_id);
            } else {
                $fix_result = $this->phase2->fix_attachment($attachment_id, $scan_id);
            }

            $results['fix_processed']++;

            if (!empty($fix_result['success']) && $fix_result['fixed'] > 0) {
                $results['fix_success']++;
            } elseif (!empty($fix_result['errors'])) {
                $results['fix_errors'][] = [
                    'attachment_id' => $attachment_id,
                    'errors' => $fix_result['errors'],
                ];
            } else {
                // No issues remaining and no errors — already resolved, count as fixed
                $results['fix_success']++;
            }
        }
        
        // Check if done
        $done = ($results['fix_processed'] >= $total);
        
        if ($done) {
            // Complete the fix
            $this->db->update_scan($scan_id, [
                'status' => 'completed',
                'results' => $results,
                'scan_completed' => current_time('mysql'),
            ]);
            
            // Clear transients
            delete_transient('sqmedia_fix_ids_' . $scan_id);
            delete_transient($lock_key);

            // Clear cron
            wp_clear_scheduled_hook('sqmedia_file_state_fix_continue', [$scan_id]);
            
            return [
                'success' => true,
                'done' => true,
                'status' => 'completed',
                'total' => $total,
                'processed' => $results['fix_processed'],
                'fixed' => $results['fix_success'],
                'errors' => count($results['fix_errors']),
            ];
        }
        
        // Update progress and release lock
        $this->db->update_scan($scan_id, ['results' => $results]);
        delete_transient($lock_key);

        // Reschedule cron for next batch (backup if AJAX stops)
        if (!wp_next_scheduled('sqmedia_file_state_fix_continue', [$scan_id])) {
            wp_schedule_single_event(time() + 1, 'sqmedia_file_state_fix_continue', [$scan_id]);
        }
        
        $progress = round(($results['fix_processed'] / $total) * 100);
        
        return [
            'success' => true,
            'done' => false,
            'status' => 'fixing',
            'total' => $total,
            'processed' => $results['fix_processed'],
            'fixed' => $results['fix_success'],
            'errors' => count($results['fix_errors']),
            'progress' => $progress,
        ];
    }
    
    /**
     * Recover fix queue from scan_items table
     */
    private function recover_fix_queue(string $scan_id): ?array {
        global $wpdb;
        
        $table = SQMedia_Database::get_table(SQMedia_Database::TABLE_SCAN_ITEMS);
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $attachment_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",
            $scan_id,
            self::CAT_CRITICAL_MISSING
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        if (empty($attachment_ids)) {
            return null;
        }

        return array_map('intval', $attachment_ids);
    }
    
    /**
     * Pause fix operation
     */
    public function pause_fix(string $scan_id): array {
        $scan = $this->db->get_scan($scan_id);
        
        if (!$scan) {
            return ['success' => false, 'error' => __('Scan not found.', 'staticq-media')];
        }
        
        
        // Allow pausing if fixing (or already paused - idempotent)
        if (!in_array($scan->status, ['fixing', 'paused'], true)) {
            /* translators: %s: current scan status */
            return ['success' => false, 'error' => sprintf(__('Cannot pause: status is %s', 'staticq-media'), $scan->status)];
        }
        
        // Clear cron
        wp_clear_scheduled_hook('sqmedia_file_state_fix_continue', [$scan_id]);
        
        $this->db->update_scan($scan_id, ['status' => 'paused']);
        
        return ['success' => true];
    }
    
    /**
     * Resume fix operation
     */
    public function resume_fix(string $scan_id): array {
        
        $scan = $this->db->get_scan($scan_id);
        
        if (!$scan) {
            return ['success' => false, 'error' => __('Scan not found.', 'staticq-media')];
        }
        
        
        // Allow resuming if paused (or fixing - idempotent)
        if (!in_array($scan->status, ['paused', 'fixing'], true)) {
            /* translators: %s: current scan status */
            return ['success' => false, 'error' => sprintf(__('Cannot resume: status is "%s"', 'staticq-media'), $scan->status ?? 'NULL')];
        }
        
        $this->db->update_scan($scan_id, ['status' => 'fixing']);
        
        // Schedule cron
        if (!wp_next_scheduled('sqmedia_file_state_fix_continue', [$scan_id])) {
            wp_schedule_single_event(time() + 1, 'sqmedia_file_state_fix_continue', [$scan_id]);
        }
        
        return ['success' => true];
    }
    
    /**
     * Stop fix operation (mark as completed with partial results)
     * Unlike pause, this ends the fix and shows remaining issues
     */
    public function stop_fix(string $scan_id): array {
        $scan = $this->db->get_scan($scan_id);
        
        if (!$scan) {
            return ['success' => false, 'error' => __('Scan not found.', 'staticq-media')];
        }
        
        if (!in_array($scan->status, ['fixing', 'paused'], true)) {
            return ['success' => false, 'error' => __('Fix not in progress.', 'staticq-media')];
        }
        
        // Clear cron
        wp_clear_scheduled_hook('sqmedia_file_state_fix_continue', [$scan_id]);
        
        // Clear lock and queue transients
        delete_transient('sqmedia_fix_lock_' . $scan_id);
        delete_transient('sqmedia_fix_ids_' . $scan_id);
        
        // Mark as completed (not cancelled - work was done)
        $results = $scan->results;
        $results['stopped_early'] = true;
        
        $this->db->update_scan($scan_id, [
            'status' => 'completed',
            'results' => $results,
            'scan_completed' => current_time('mysql'),
        ]);
        
        return ['success' => true];
    }
    
    /**
     * Fix a single attachment
     */
    public function fix_attachment(int $attachment_id, string $scan_id = ''): array {
        if (!$scan_id) {
            return ['success' => false, 'error' => __('Scan ID required.', 'staticq-media')];
        }

        $scan = $this->db->get_scan($scan_id);
        if (!$scan) {
            return ['success' => false, 'error' => __('Scan not found.', 'staticq-media')];
        }

        $current_phase = $scan->results['current_phase'] ?? 'unified';

        if ($current_phase === 1) {
            return $this->phase1->fix_attachment($attachment_id, $scan_id);
        }

        if ($current_phase === 2) {
            return $this->phase2->fix_attachment($attachment_id, $scan_id);
        }

        // Unified: fix metadata first, then files
        $p1 = $this->phase1->fix_attachment($attachment_id, $scan_id);
        $p2 = $this->phase2->fix_attachment($attachment_id, $scan_id);

        // Align state flags only when all fixes are complete
        $this->align_state_flags_after_fix($attachment_id, $scan_id);

        return [
            'success' => (($p1['fixed'] ?? 0) + ($p2['fixed'] ?? 0)) > 0,
            'fixed' => ($p1['fixed'] ?? 0) + ($p2['fixed'] ?? 0),
            'errors' => array_merge($p1['errors'] ?? [], $p2['errors'] ?? []),
            'attachment_id' => $attachment_id,
        ];
    }
    
    // =========================================================================
    // POST-FIX STATE ALIGNMENT
    // =========================================================================

    /**
     * Align state flags after all fix phases complete for an attachment.
     *
     * Only updates storage_location, has_webp, and original_location when
     * every issue is resolved. Called once after both Phase 1 and Phase 2
     * fixes have run — never from inside a single phase, so partial-fix
     * scenarios can't leave stale flags.
     */
    private function align_state_flags_after_fix(int $attachment_id, string $scan_id): void {
        $remaining = $this->db->get_attachment_scan_items($scan_id, $attachment_id);

        if (!empty($remaining)) {
            return; // Still has unresolved issues — don't align yet
        }

        $record = SQMedia_Attachment_Record::find($attachment_id);
        if (!$record) {
            return;
        }

        $update = ['storage_location' => SQMedia_Settings::get_storage_destination()];
        if (SQMedia_Settings::is_webp_enabled() && $record->supports_webp()) {
            $update['has_webp'] = 1;
        }

        // Align original_location with current settings
        if ($record->get_original_image()) {
            $handling = SQMedia_Settings::get_original_file_handling();
            if ($handling === 'move_to_originals') {
                $update['original_location'] = 'originals';
            } elseif ($handling === 'discard') {
                $update['original_location'] = null;
            } else {
                $update['original_location'] = 'alongside';
            }
        }

        $this->db->update_record($attachment_id, $update);
    }

    // =========================================================================
    // PHASE 2 SPECIFIC PUBLIC METHODS
    // =========================================================================
    
    /**
     * Build file indexes for Phase 2
     * Called via cron: sq_build_file_index
     */
    public function build_file_indexes(string $scan_id): bool {
        return $this->phase2->build_file_indexes($scan_id);
    }
    
    /**
     * Clean up index files
     */
    public function cleanup_indexes(string $scan_id): void {
        $this->phase2->cleanup_indexes($scan_id);
    }
}