<?php
/**
 * Name: Unused Media Finder
 * Description: Find and delete unused media files not attached to any posts, pages, or products to free up storage space and declutter media library
 * Category: Media
 * Icon: -
 * HasSettings: true
 * UseCases: [
 *   {
 *     "title": "Reclaim Storage Space",
 *     "description": "Find and delete unused images eating up hosting space from old test uploads and deleted posts."
 *   },
 *   {
 *     "title": "Media Library Cleanup",
 *     "description": "Identify orphaned images from imported content or removed products to clean up cluttered media library."
 *   },
 *   {
 *     "title": "Reduce Backup Size",
 *     "description": "Remove unused media before backups to reduce backup file size for faster transfers and lower storage costs."
 *   }
 * ]
 * Added: 2025-09-30
 * Tags: media, unused, cleanup, optimization, storage
 * IsBeta: true
 * WhereToFind: {
 *   "location": "Switchboard Settings",
 *   "url": "",
 *   "instructions": "Scan for media files not used in posts or as featured images in module settings, then delete individually or in bulk to reclaim storage space.",
 *   "newTab": false
 * }
 */

// Prevent direct access
if (!defined('ABSPATH')) exit;

class SwitchboardUnusedMediaFinder {

    private $option_key = 'switchboard_unused_media_finder';
    private $scan_table;
    private $refs_table;
    private $trash_dir;

    public function __construct() {
        global $wpdb;
        $this->scan_table = $wpdb->prefix . 'switchboard_media_scan';
        $this->refs_table = $wpdb->prefix . 'switchboard_media_refs';
        $this->trash_dir = wp_upload_dir()['basedir'] . '/switchboard-trash';

        // Create tables if needed
        add_action('admin_init', [$this, 'maybe_create_tables']);

        // AJAX handlers
        add_action('wp_ajax_switchboard_umf_scan', [$this, 'ajax_scan']);
        add_action('wp_ajax_switchboard_umf_get_results', [$this, 'ajax_get_results']);
        add_action('wp_ajax_switchboard_umf_delete', [$this, 'ajax_delete']);
        add_action('wp_ajax_switchboard_umf_bulk_action', [$this, 'ajax_bulk_action']);
        add_action('wp_ajax_switchboard_umf_recover', [$this, 'ajax_recover']);
        add_action('wp_ajax_switchboard_umf_empty_trash', [$this, 'ajax_empty_trash']);

        // Scheduled cleanup
        add_action('switchboard_umf_cleanup_trash', [$this, 'cleanup_trash']);
    }

    /**
     * Called when module is activated
     */
    public function activate() {
        $this->create_tables();

        // Schedule trash cleanup
        if (!wp_next_scheduled('switchboard_umf_cleanup_trash')) {
            wp_schedule_event(time(), 'daily', 'switchboard_umf_cleanup_trash');
        }
    }

    /**
     * Called when module is deactivated
     */
    public function deactivate() {
        wp_clear_scheduled_hook('switchboard_umf_cleanup_trash');
    }

    /**
     * Register data locations for cleanup system
     */
    public function register_data_locations() {
        return [
            'options' => [
                $this->option_key,
                $this->option_key . '_last_scan',
                $this->option_key . '_scan_progress',
                'switchboard_umf_trash_meta',
            ],
            'option_patterns' => [],
            'tables' => [
                'switchboard_media_scan',
                'switchboard_media_refs',
            ],
            'post_meta' => [],
            'user_meta' => [],
            'transients' => [],
        ];
    }

    /**
     * Create tables if they don't exist
     */
    public function maybe_create_tables() {
        global $wpdb;
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->scan_table)) != $this->scan_table) {
            $this->create_tables();
        }
    }

    /**
     * Create database tables
     */
    private function create_tables() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        // Scan results table
        $sql1 = "CREATE TABLE IF NOT EXISTS {$this->scan_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            time datetime DEFAULT CURRENT_TIMESTAMP,
            post_id bigint(20) NOT NULL,
            path varchar(512) DEFAULT NULL,
            size bigint(20) DEFAULT 0,
            issue varchar(50) NOT NULL DEFAULT 'UNUSED',
            ignored tinyint(1) NOT NULL DEFAULT 0,
            deleted tinyint(1) NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY post_id_idx (post_id),
            KEY issue_idx (issue),
            KEY ignored_idx (ignored),
            KEY deleted_idx (deleted)
        ) $charset_collate;";

        // References table
        $sql2 = "CREATE TABLE IF NOT EXISTS {$this->refs_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            media_id bigint(20) DEFAULT NULL,
            media_url varchar(512) DEFAULT NULL,
            origin_type varchar(100) NOT NULL,
            parent_id bigint(20) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY media_id_idx (media_id),
            KEY media_url_idx (media_url(191))
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql1);
        dbDelta($sql2);
    }

    /**
     * Get default settings
     */
    private function get_defaults() {
        return [
            'images_only' => 'no',
            'trash_days' => 30,
            'batch_size' => 50, // Smaller default for reliability
        ];
    }

    /**
     * Reset scan - clear tables for fresh scan
     */
    private function reset_scan() {
        global $wpdb;
        $wpdb->query("TRUNCATE TABLE {$this->scan_table}");
        $wpdb->query("TRUNCATE TABLE {$this->refs_table}");
        delete_option($this->option_key . '_scan_progress');
    }

    /**
     * Save scan progress for resume capability
     */
    private function save_scan_progress($offset, $total, $unused_found) {
        update_option($this->option_key . '_scan_progress', [
            'offset' => $offset,
            'total' => $total,
            'unused_found' => $unused_found,
            'updated_at' => time(),
        ]);
    }

    /**
     * Get saved scan progress
     */
    private function get_scan_progress() {
        return get_option($this->option_key . '_scan_progress', null);
    }

    /**
     * Clear scan progress (when complete)
     */
    private function clear_scan_progress() {
        delete_option($this->option_key . '_scan_progress');
    }

    /**
     * AJAX: Main scan handler
     * Single-step batched scanning - processes batch_size media items per request
     */
    public function ajax_scan() {
        check_ajax_referer('switchboard_umf', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
        }

        // Extend time limit for this request
        @set_time_limit(120);
        @ini_set('max_execution_time', 120);

        global $wpdb;

        // Ensure tables exist before scanning
        $this->maybe_create_tables();

        try {
            $settings = get_option($this->option_key, $this->get_defaults());
            $batch_size = isset($settings['batch_size']) ? intval($settings['batch_size']) : 50;
            $images_only = isset($settings['images_only']) && $settings['images_only'] === 'yes';

            $offset = isset($_POST['offset']) ? intval($_POST['offset']) : 0;
            $fresh_start = isset($_POST['fresh']) && $_POST['fresh'] === 'true';

            // On fresh start, reset everything
            if ($fresh_start && $offset === 0) {
                $this->reset_scan();
            }

        $start_time = microtime(true);

        // Build query for attachments
        $mime_clause = $images_only
            ? "AND post_mime_type IN ('image/jpeg', 'image/png', 'image/gif', 'image/webp', 'image/avif', 'image/svg+xml', 'image/bmp', 'image/tiff')"
            : "";

        // Get total count
        $total = (int) $wpdb->get_var("
            SELECT COUNT(*) FROM {$wpdb->posts}
            WHERE post_type = 'attachment'
            AND post_status = 'inherit'
            $mime_clause
        ");

        // Get batch of attachments
        $attachments = $wpdb->get_results($wpdb->prepare("
            SELECT ID, post_title, post_mime_type
            FROM {$wpdb->posts}
            WHERE post_type = 'attachment'
            AND post_status = 'inherit'
            $mime_clause
            ORDER BY ID ASC
            LIMIT %d OFFSET %d
        ", $batch_size, $offset));

        if (empty($attachments)) {
            // Scan complete - clear progress and save final results
            $this->clear_scan_progress();

            $unused_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->scan_table} WHERE deleted = 0 AND ignored = 0");
            $ignored_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->scan_table} WHERE ignored = 1");

            update_option($this->option_key . '_last_scan', [
                'time' => current_time('mysql'),
                'total_scanned' => $total,
                'unused_found' => $unused_count,
            ]);

            wp_send_json_success([
                'complete' => true,
                'total' => $total,
                'scanned' => $total,
                'unused' => $unused_count,
                'ignored' => $ignored_count,
                'trash' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->scan_table} WHERE deleted = 1"),
                'batch_time' => round(microtime(true) - $start_time, 2),
            ]);
            return;
        }

        // Process each attachment in this batch
        $batch_unused = 0;
        foreach ($attachments as $attachment) {
            $is_used = $this->check_media_usage($attachment->ID);

            if (!$is_used) {
                // Get file info
                $file_path = get_attached_file($attachment->ID);
                $file_size = $file_path && file_exists($file_path) ? filesize($file_path) : 0;
                $relative_path = $file_path ? str_replace(wp_upload_dir()['basedir'] . '/', '', $file_path) : '';

                // Check if already exists (for resume case)
                $existing = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM {$this->scan_table} WHERE post_id = %d",
                    $attachment->ID
                ));

                if (!$existing) {
                    $wpdb->insert($this->scan_table, [
                        'post_id' => $attachment->ID,
                        'path' => $relative_path,
                        'size' => $file_size,
                        'issue' => 'UNUSED',
                        'ignored' => 0,
                        'deleted' => 0,
                    ]);
                    $batch_unused++;
                }
            }
        }

        $scanned = $offset + count($attachments);
        $progress = $total > 0 ? round(($scanned / $total) * 100) : 100;
        $total_unused = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->scan_table} WHERE deleted = 0 AND ignored = 0");

        // Save progress for resume capability
        $this->save_scan_progress($scanned, $total, $total_unused);

        wp_send_json_success([
            'complete' => false,
            'total' => $total,
            'scanned' => $scanned,
            'progress' => $progress,
            'batch_unused' => $batch_unused,
            'total_unused' => $total_unused,
            'next_offset' => $offset + $batch_size,
            'batch_time' => round(microtime(true) - $start_time, 2),
        ]);

        } catch (Exception $e) {
            // Log error for debugging but don't expose internal details to user
            error_log('Switchboard UMF scan error: ' . $e->getMessage());
            wp_send_json_error(__('An error occurred during scanning. Please try again.', 'wpswitchboard'));
        } catch (Error $e) {
            error_log('Switchboard UMF fatal error: ' . $e->getMessage());
            wp_send_json_error(__('A system error occurred. Please check server logs.', 'wpswitchboard'));
        }
    }

    /**
     * Check if a media item is used anywhere
     * Returns true if used, false if unused
     *
     * Checks: WordPress core, WooCommerce, Elementor, Divi, Beaver Builder,
     * ACF, Gutenberg blocks, Yoast SEO, gallery shortcodes, and more
     */
    private function check_media_usage($attachment_id) {
        global $wpdb;

        $attachment_id = intval($attachment_id);
        $attachment_url = wp_get_attachment_url($attachment_id);
        $filename = $attachment_url ? basename($attachment_url) : '';
        $id_str = strval($attachment_id);

        // =========================================================================
        // WORDPRESS CORE CHECKS
        // =========================================================================

        // 1. Featured image check
        $is_featured = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$wpdb->postmeta} WHERE meta_key = '_thumbnail_id' AND meta_value = %s LIMIT 1",
            $id_str
        ));
        if ($is_featured) return true;

        // 2. Post content check (wp-image-ID class)
        $in_content = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$wpdb->posts}
            WHERE post_status IN ('publish', 'draft', 'pending', 'private', 'future')
            AND post_type NOT IN ('attachment', 'revision', 'nav_menu_item')
            AND post_content LIKE %s
            LIMIT 1",
            '%wp-image-' . $attachment_id . '%'
        ));
        if ($in_content) return true;

        // 3. Gutenberg block JSON check (blocks store IDs as "id":123)
        $in_blocks = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$wpdb->posts}
            WHERE post_status IN ('publish', 'draft', 'pending', 'private', 'future')
            AND post_type NOT IN ('attachment', 'revision', 'nav_menu_item')
            AND (post_content LIKE %s OR post_content LIKE %s OR post_content LIKE %s)
            LIMIT 1",
            '%"id":' . $attachment_id . '%',
            '%"id": ' . $attachment_id . '%',
            '%"mediaId":' . $attachment_id . '%'
        ));
        if ($in_blocks) return true;

        // 4. Gallery shortcode check [gallery ids="1,2,3"]
        $in_shortcode = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$wpdb->posts}
            WHERE post_status IN ('publish', 'draft', 'pending', 'private', 'future')
            AND post_type NOT IN ('attachment', 'revision', 'nav_menu_item')
            AND (
                post_content LIKE %s OR
                post_content LIKE %s OR
                post_content LIKE %s OR
                post_content LIKE %s
            )
            LIMIT 1",
            '%ids=\"' . $attachment_id . '\"%',
            '%ids=\"' . $attachment_id . ',%',
            '%,' . $attachment_id . ',%',
            '%,' . $attachment_id . '\"%'
        ));
        if ($in_shortcode) return true;

        // 5. Post content URL check (for linked images)
        if ($filename) {
            $url_in_content = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->posts}
                WHERE post_status IN ('publish', 'draft', 'pending', 'private', 'future')
                AND post_type NOT IN ('attachment', 'revision', 'nav_menu_item')
                AND post_content LIKE %s
                LIMIT 1",
                '%' . $wpdb->esc_like($filename) . '%'
            ));
            if ($url_in_content) return true;
        }

        // =========================================================================
        // POSTMETA CHECKS (covers ACF, custom fields, etc.)
        // =========================================================================

        // 6. Direct ID match in postmeta
        $in_postmeta_direct = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$wpdb->postmeta}
            WHERE meta_value = %s
            LIMIT 1",
            $id_str
        ));
        if ($in_postmeta_direct) return true;

        // 7. Serialized array check (ACF, etc.) - various formats
        $in_postmeta_serialized = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$wpdb->postmeta}
            WHERE (
                meta_value LIKE %s OR
                meta_value LIKE %s OR
                meta_value LIKE %s OR
                meta_value LIKE %s OR
                meta_value LIKE %s
            )
            LIMIT 1",
            '%"' . $attachment_id . '"%',
            '%i:' . $attachment_id . ';%',
            '%s:' . strlen($id_str) . ':"' . $attachment_id . '";%',
            '%"id";i:' . $attachment_id . ';%',
            '%"image";s:' . strlen($id_str) . ':"' . $attachment_id . '";%'
        ));
        if ($in_postmeta_serialized) return true;

        // 8. Postmeta gallery fields (comma-separated IDs)
        $in_gallery = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$wpdb->postmeta}
            WHERE meta_key LIKE %s
            AND (meta_value = %s OR meta_value LIKE %s OR meta_value LIKE %s OR meta_value LIKE %s)
            LIMIT 1",
            '%gallery%',
            $id_str,
            $attachment_id . ',%',
            '%,' . $attachment_id . ',%',
            '%,' . $attachment_id
        ));
        if ($in_gallery) return true;

        // =========================================================================
        // ELEMENTOR PAGE BUILDER
        // Elementor stores JSON in _elementor_data with structure:
        // {"settings":{"image":{"id":123,"url":"..."}}} for image widgets
        // Also: "background_image":{"id":123}, "image_size", etc.
        // =========================================================================

        if (defined('ELEMENTOR_VERSION')) {
            // Elementor stores data as JSON in _elementor_data meta
            // Pattern: "id":123 or "id":"123" (with/without quotes, with/without space)
            $in_elementor = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key = '_elementor_data'
                AND (
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s
                )
                LIMIT 1",
                '%\"id\":' . $attachment_id . ',%',
                '%\"id\":' . $attachment_id . '}%',
                '%\"id\": ' . $attachment_id . ',%',
                '%\"id\":\"' . $attachment_id . '\"%',
                '%\"image\":{\"id\":' . $attachment_id . '%',
                '%\"background_image\":{\"id\":' . $attachment_id . '%',
                '%\"attachment_id\":' . $attachment_id . '%'
            ));
            if ($in_elementor) return true;

            // Elementor URL check (catches any image URL in Elementor data)
            if ($filename) {
                $in_elementor_url = $wpdb->get_var($wpdb->prepare(
                    "SELECT 1 FROM {$wpdb->postmeta}
                    WHERE meta_key = '_elementor_data'
                    AND meta_value LIKE %s
                    LIMIT 1",
                    '%' . $wpdb->esc_like($filename) . '%'
                ));
                if ($in_elementor_url) return true;
            }
        }

        // =========================================================================
        // DIVI PAGE BUILDER
        // =========================================================================

        if (function_exists('et_setup_theme') || defined('ET_BUILDER_VERSION')) {
            // Divi stores content in post_content but also in _et_builder_version meta
            // Check for Divi image module patterns
            $in_divi = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->posts}
                WHERE post_status IN ('publish', 'draft', 'pending', 'private', 'future')
                AND (
                    post_content LIKE %s OR
                    post_content LIKE %s OR
                    post_content LIKE %s
                )
                LIMIT 1",
                '%src=\"%' . $wpdb->esc_like($filename) . '%',
                '%logo=\"%' . $wpdb->esc_like($filename) . '%',
                '%image=\"%' . $wpdb->esc_like($filename) . '%'
            ));
            if ($in_divi) return true;

            // Divi stores gallery IDs
            $in_divi_gallery = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->posts}
                WHERE post_status IN ('publish', 'draft', 'pending', 'private', 'future')
                AND (
                    post_content LIKE %s OR
                    post_content LIKE %s OR
                    post_content LIKE %s
                )
                LIMIT 1",
                '%gallery_ids=\"' . $attachment_id . '\"%',
                '%gallery_ids=\"' . $attachment_id . ',%',
                '%gallery_ids=\"%,' . $attachment_id . '%'
            ));
            if ($in_divi_gallery) return true;

            // Divi 5 uses value attribute
            $in_divi5 = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key LIKE %s
                AND meta_value LIKE %s
                LIMIT 1",
                '_et_%',
                '%' . $wpdb->esc_like($filename) . '%'
            ));
            if ($in_divi5) return true;
        }

        // =========================================================================
        // BEAVER BUILDER
        // BB stores serialized PHP arrays in _fl_builder_data and _fl_builder_draft
        // Photo module uses: "photo" => ID, generates "photo_src" => URL
        // =========================================================================

        if (class_exists('FLBuilderModel')) {
            // Beaver Builder stores data in _fl_builder_data (live) and _fl_builder_draft
            // Serialized format: s:5:"photo";s:3:"123"; or i:123;
            $in_beaver = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key IN ('_fl_builder_data', '_fl_builder_draft')
                AND (
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s
                )
                LIMIT 1",
                '%\"photo\";s:' . strlen($id_str) . ':\"' . $attachment_id . '\"%',
                '%\"photo\";i:' . $attachment_id . ';%',
                '%\"id\";s:' . strlen($id_str) . ':\"' . $attachment_id . '\"%',
                '%\"id\";i:' . $attachment_id . ';%',
                '%\"photo_ids\";%' . $attachment_id . '%'
            ));
            if ($in_beaver) return true;

            // Beaver Builder URL check (photo_src field)
            if ($filename) {
                $in_beaver_url = $wpdb->get_var($wpdb->prepare(
                    "SELECT 1 FROM {$wpdb->postmeta}
                    WHERE meta_key IN ('_fl_builder_data', '_fl_builder_draft')
                    AND meta_value LIKE %s
                    LIMIT 1",
                    '%' . $wpdb->esc_like($filename) . '%'
                ));
                if ($in_beaver_url) return true;
            }
        }

        // =========================================================================
        // OXYGEN BUILDER (ct_builder_shortcodes, ct_builder_json, _ct_builder_json)
        // =========================================================================

        if (defined('CT_VERSION')) {
            $in_oxygen = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key IN ('ct_builder_shortcodes', 'ct_builder_json', '_ct_builder_json')
                AND (
                    meta_value LIKE %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s
                )
                LIMIT 1",
                '%image_id=\"' . $attachment_id . '\"%',
                '%\"id\":' . $attachment_id . '%',
                '%' . $wpdb->esc_like($filename) . '%'
            ));
            if ($in_oxygen) return true;
        }

        // =========================================================================
        // BRICKS BUILDER (_bricks_page_content_2)
        // =========================================================================

        if (defined('BRICKS_VERSION')) {
            $in_bricks = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key = '_bricks_page_content_2'
                AND (
                    meta_value LIKE %s OR
                    meta_value LIKE %s
                )
                LIMIT 1",
                '%\"id\":' . $attachment_id . '%',
                '%' . $wpdb->esc_like($filename) . '%'
            ));
            if ($in_bricks) return true;
        }

        // =========================================================================
        // BREAKDANCE BUILDER (breakdance_data)
        // =========================================================================

        if (defined('BREAKDANCE_PLUGIN_URL')) {
            $in_breakdance = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key = 'breakdance_data'
                AND (
                    meta_value LIKE %s OR
                    meta_value LIKE %s
                )
                LIMIT 1",
                '%\"id\":' . $attachment_id . '%',
                '%' . $wpdb->esc_like($filename) . '%'
            ));
            if ($in_breakdance) return true;
        }

        // =========================================================================
        // BRIZY BUILDER
        // =========================================================================

        if (class_exists('Brizy_Editor_Post')) {
            $in_brizy = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key LIKE 'brizy%'
                AND (
                    meta_value LIKE %s OR
                    meta_value LIKE %s
                )
                LIMIT 1",
                '%\"' . $attachment_id . '\"%',
                '%' . $wpdb->esc_like($filename) . '%'
            ));
            if ($in_brizy) return true;
        }

        // =========================================================================
        // WPBAKERY / VISUAL COMPOSER
        // =========================================================================

        if (defined('WPB_VC_VERSION')) {
            $in_wpbakery = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->posts}
                WHERE post_status IN ('publish', 'draft', 'pending', 'private', 'future')
                AND (
                    post_content LIKE %s OR
                    post_content LIKE %s OR
                    post_content LIKE %s
                )
                LIMIT 1",
                '%image=\"' . $attachment_id . '\"%',
                '%images=\"' . $attachment_id . '%',
                '%img_id=\"' . $attachment_id . '\"%'
            ));
            if ($in_wpbakery) return true;
        }

        // =========================================================================
        // ACF (Advanced Custom Fields) - Deep check
        // =========================================================================

        if (class_exists('ACF')) {
            // ACF image/gallery fields store IDs in specific patterns
            $in_acf = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key NOT LIKE '\_%'
                AND (
                    meta_value = %s OR
                    meta_value LIKE %s OR
                    meta_value LIKE %s
                )
                LIMIT 1",
                $id_str,
                '%"' . $attachment_id . '"%',
                '%,' . $attachment_id . ',%'
            ));
            if ($in_acf) return true;

            // ACF repeater/flexible content (deeply nested)
            $in_acf_deep = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key LIKE %s
                AND meta_value = %s
                LIMIT 1",
                '%_image%',
                $id_str
            ));
            if ($in_acf_deep) return true;
        }

        // =========================================================================
        // YOAST SEO
        // =========================================================================

        if (defined('WPSEO_VERSION')) {
            $in_yoast = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key IN ('_yoast_wpseo_opengraph-image-id', '_yoast_wpseo_twitter-image-id')
                AND meta_value = %s
                LIMIT 1",
                $id_str
            ));
            if ($in_yoast) return true;

            // Yoast URL-based images
            if ($attachment_url) {
                $in_yoast_url = $wpdb->get_var($wpdb->prepare(
                    "SELECT 1 FROM {$wpdb->postmeta}
                    WHERE meta_key IN ('_yoast_wpseo_opengraph-image', '_yoast_wpseo_twitter-image')
                    AND meta_value LIKE %s
                    LIMIT 1",
                    '%' . $wpdb->esc_like($filename) . '%'
                ));
                if ($in_yoast_url) return true;
            }
        }

        // =========================================================================
        // RANK MATH SEO
        // =========================================================================

        if (defined('RANK_MATH_VERSION')) {
            $in_rankmath = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key = 'rank_math_facebook_image_id'
                AND meta_value = %s
                LIMIT 1",
                $id_str
            ));
            if ($in_rankmath) return true;
        }

        // =========================================================================
        // WOOCOMMERCE
        // =========================================================================

        if (class_exists('WooCommerce')) {
            // Product gallery
            $in_woo_gallery = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key = '_product_image_gallery'
                AND (meta_value = %s OR meta_value LIKE %s OR meta_value LIKE %s OR meta_value LIKE %s)
                LIMIT 1",
                $id_str,
                $attachment_id . ',%',
                '%,' . $attachment_id . ',%',
                '%,' . $attachment_id
            ));
            if ($in_woo_gallery) return true;

            // Variation images
            $in_variation = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta} pm
                INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                WHERE pm.meta_key = '_thumbnail_id'
                AND pm.meta_value = %s
                AND p.post_type = 'product_variation'
                LIMIT 1",
                $id_str
            ));
            if ($in_variation) return true;

            // Additional variation images
            $in_var_gallery = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->postmeta}
                WHERE meta_key = '_wc_additional_variation_images'
                AND (meta_value = %s OR meta_value LIKE %s OR meta_value LIKE %s OR meta_value LIKE %s)
                LIMIT 1",
                $id_str,
                $attachment_id . ',%',
                '%,' . $attachment_id . ',%',
                '%,' . $attachment_id
            ));
            if ($in_var_gallery) return true;

            // Downloadable files
            if ($attachment_url) {
                $in_downloads = $wpdb->get_var($wpdb->prepare(
                    "SELECT 1 FROM {$wpdb->postmeta}
                    WHERE meta_key = '_downloadable_files'
                    AND meta_value LIKE %s
                    LIMIT 1",
                    '%' . $wpdb->esc_like($attachment_url) . '%'
                ));
                if ($in_downloads) return true;
            }

            // Category thumbnails
            $in_term = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$wpdb->termmeta}
                WHERE meta_key = 'thumbnail_id' AND meta_value = %s
                LIMIT 1",
                $id_str
            ));
            if ($in_term) return true;

            // WooCommerce placeholder
            $placeholder = get_option('woocommerce_placeholder_image', 0);
            if ($placeholder && intval($placeholder) === $attachment_id) {
                return true;
            }
        }

        // =========================================================================
        // REVOLUTION SLIDER
        // =========================================================================

        if (class_exists('RevSliderFront')) {
            $revslider_table = $wpdb->prefix . 'revslider_slides';
            if ($wpdb->get_var("SHOW TABLES LIKE '$revslider_table'") === $revslider_table) {
                $in_revslider = $wpdb->get_var($wpdb->prepare(
                    "SELECT 1 FROM {$revslider_table}
                    WHERE params LIKE %s OR layers LIKE %s
                    LIMIT 1",
                    '%' . $wpdb->esc_like($filename) . '%',
                    '%' . $wpdb->esc_like($filename) . '%'
                ));
                if ($in_revslider) return true;
            }
        }

        // =========================================================================
        // OPTIONS TABLE (site icon, logos, etc.)
        // =========================================================================

        $in_options = $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$wpdb->options}
            WHERE (option_value = %s OR option_value = %d)
            AND option_name NOT LIKE '\_%transient%'
            LIMIT 1",
            $id_str,
            $attachment_id
        ));
        if ($in_options) return true;

        // Site icon check
        $site_icon = get_option('site_icon', 0);
        if ($site_icon && intval($site_icon) === $attachment_id) {
            return true;
        }

        // Not used anywhere
        return false;
    }

    /**
     * AJAX: Get paginated results
     */
    public function ajax_get_results() {
        check_ajax_referer('switchboard_umf', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
        }

        global $wpdb;

        $tab = isset($_POST['tab']) ? sanitize_text_field($_POST['tab']) : 'unused';
        $page = isset($_POST['page']) ? max(1, intval($_POST['page'])) : 1;
        $per_page = 50;
        $offset = ($page - 1) * $per_page;

        // Build where clause based on tab using prepared statement parameters
        switch ($tab) {
            case 'ignored':
                $ignored = 1;
                $deleted = 0;
                break;
            case 'trash':
                $ignored = 0; // Not used in trash query but needed for consistency
                $deleted = 1;
                break;
            default: // unused
                $ignored = 0;
                $deleted = 0;
        }

        // Get total count using prepared statement
        if ($tab === 'trash') {
            $total = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->scan_table} WHERE deleted = %d",
                $deleted
            ));
        } else {
            $total = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->scan_table} WHERE ignored = %d AND deleted = %d",
                $ignored,
                $deleted
            ));
        }

        // Get results using prepared statement
        if ($tab === 'trash') {
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT s.*, p.post_title, p.post_mime_type
                FROM {$this->scan_table} s
                LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID
                WHERE deleted = %d
                ORDER BY s.size DESC
                LIMIT %d OFFSET %d",
                $deleted,
                $per_page,
                $offset
            ));
        } else {
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT s.*, p.post_title, p.post_mime_type
                FROM {$this->scan_table} s
                LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID
                WHERE ignored = %d AND deleted = %d
                ORDER BY s.size DESC
                LIMIT %d OFFSET %d",
                $ignored,
                $deleted,
                $per_page,
                $offset
            ));
        }

        // Format results
        $items = [];
        foreach ($results as $row) {
            $thumb = wp_get_attachment_thumb_url($row->post_id);
            $items[] = [
                'id' => $row->id,
                'post_id' => $row->post_id,
                'title' => $row->post_title ?: basename($row->path),
                'path' => $row->path,
                'size' => size_format($row->size),
                'size_raw' => $row->size,
                'mime' => $row->post_mime_type,
                'thumb' => $thumb ?: '',
                'issue' => $row->issue,
                'time' => $row->time,
            ];
        }

        // Get counts for all tabs using prepared statements
        $counts = [
            'unused' => (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->scan_table} WHERE ignored = %d AND deleted = %d",
                0, 0
            )),
            'ignored' => (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->scan_table} WHERE ignored = %d AND deleted = %d",
                1, 0
            )),
            'trash' => (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->scan_table} WHERE deleted = %d",
                1
            )),
        ];

        // Calculate total size using prepared statement
        if ($tab === 'trash') {
            $total_size = $wpdb->get_var($wpdb->prepare(
                "SELECT SUM(size) FROM {$this->scan_table} WHERE deleted = %d",
                $deleted
            ));
        } else {
            $total_size = $wpdb->get_var($wpdb->prepare(
                "SELECT SUM(size) FROM {$this->scan_table} WHERE ignored = %d AND deleted = %d",
                $ignored,
                $deleted
            ));
        }

        wp_send_json_success([
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'per_page' => $per_page,
            'total_pages' => ceil($total / $per_page),
            'total_size' => size_format($total_size ?: 0),
            'counts' => $counts,
        ]);
    }

    /**
     * AJAX: Delete single item
     */
    public function ajax_delete() {
        check_ajax_referer('switchboard_umf', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
        }

        $id = isset($_POST['id']) ? intval($_POST['id']) : 0;
        $permanent = isset($_POST['permanent']) && $_POST['permanent'] === 'true';

        if (!$id) {
            wp_send_json_error('Invalid ID');
        }

        global $wpdb;
        $item = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$this->scan_table} WHERE id = %d", $id));

        if (!$item) {
            wp_send_json_error('Item not found');
        }

        if ($permanent) {
            $this->permanently_delete($item->post_id);
            $wpdb->delete($this->scan_table, ['id' => $id]);
        } else {
            $this->move_to_trash($item->post_id);
            $wpdb->update($this->scan_table, ['deleted' => 1], ['id' => $id]);
        }

        wp_send_json_success();
    }

    /**
     * AJAX: Bulk action
     */
    public function ajax_bulk_action() {
        check_ajax_referer('switchboard_umf', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
        }

        $action = isset($_POST['bulk_action']) ? sanitize_key($_POST['bulk_action']) : '';
        $ids = isset($_POST['ids']) ? array_map('intval', (array) $_POST['ids']) : [];

        // Validate action is one of the allowed values
        $allowed_actions = ['delete', 'delete_permanent', 'ignore', 'unignore', 'recover'];
        if (!in_array($action, $allowed_actions, true)) {
            wp_send_json_error('Invalid action');
        }

        if (empty($ids)) {
            wp_send_json_error('No items selected');
        }

        global $wpdb;
        $processed = 0;

        foreach ($ids as $id) {
            $item = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$this->scan_table} WHERE id = %d", $id));
            if (!$item) continue;

            switch ($action) {
                case 'delete':
                    $this->move_to_trash($item->post_id);
                    $wpdb->update($this->scan_table, ['deleted' => 1], ['id' => $id]);
                    $processed++;
                    break;

                case 'delete_permanent':
                    $this->permanently_delete($item->post_id);
                    $wpdb->delete($this->scan_table, ['id' => $id]);
                    $processed++;
                    break;

                case 'ignore':
                    $wpdb->update($this->scan_table, ['ignored' => 1], ['id' => $id]);
                    $processed++;
                    break;

                case 'unignore':
                    $wpdb->update($this->scan_table, ['ignored' => 0], ['id' => $id]);
                    $processed++;
                    break;

                case 'recover':
                    $this->recover_from_trash($item->post_id, $item->path);
                    $wpdb->update($this->scan_table, ['deleted' => 0], ['id' => $id]);
                    $processed++;
                    break;
            }
        }

        wp_send_json_success(['processed' => $processed]);
    }

    /**
     * AJAX: Recover from trash
     */
    public function ajax_recover() {
        check_ajax_referer('switchboard_umf', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
        }

        $id = isset($_POST['id']) ? intval($_POST['id']) : 0;

        if (!$id) {
            wp_send_json_error('Invalid ID');
        }

        global $wpdb;
        $item = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$this->scan_table} WHERE id = %d", $id));

        if (!$item) {
            wp_send_json_error('Item not found');
        }

        $this->recover_from_trash($item->post_id, $item->path);
        $wpdb->update($this->scan_table, ['deleted' => 0], ['id' => $id]);

        wp_send_json_success();
    }

    /**
     * AJAX: Empty trash
     */
    public function ajax_empty_trash() {
        check_ajax_referer('switchboard_umf', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
        }

        global $wpdb;

        $items = $wpdb->get_results("SELECT * FROM {$this->scan_table} WHERE deleted = 1");
        $count = 0;

        foreach ($items as $item) {
            $this->permanently_delete($item->post_id);
            $wpdb->delete($this->scan_table, ['id' => $item->id]);
            $count++;
        }

        $this->clean_trash_directory();

        wp_send_json_success(['deleted' => $count]);
    }

    /**
     * Move file to trash directory
     */
    private function move_to_trash($attachment_id) {
        $file = get_attached_file($attachment_id);
        if (!$file || !file_exists($file)) return false;

        // Create trash directory if needed
        if (!is_dir($this->trash_dir)) {
            wp_mkdir_p($this->trash_dir);
            file_put_contents($this->trash_dir . '/.htaccess', 'Deny from all');
        }

        $upload_dir = wp_upload_dir()['basedir'];
        $relative_path = str_replace($upload_dir . '/', '', $file);
        $trash_path = $this->trash_dir . '/' . $relative_path;

        $trash_subdir = dirname($trash_path);
        if (!is_dir($trash_subdir)) {
            wp_mkdir_p($trash_subdir);
        }

        if (rename($file, $trash_path)) {
            $metadata = wp_get_attachment_metadata($attachment_id);
            if (isset($metadata['sizes']) && is_array($metadata['sizes'])) {
                $file_dir = dirname($file);
                foreach ($metadata['sizes'] as $size => $data) {
                    $size_file = $file_dir . '/' . $data['file'];
                    $size_trash = dirname($trash_path) . '/' . $data['file'];
                    if (file_exists($size_file)) {
                        @rename($size_file, $size_trash);
                    }
                }
            }

            $trash_meta = get_option('switchboard_umf_trash_meta', []);
            $trash_meta[$attachment_id] = [
                'original_path' => $relative_path,
                'trashed_at' => time(),
            ];
            update_option('switchboard_umf_trash_meta', $trash_meta);

            return true;
        }

        return false;
    }

    /**
     * Recover file from trash
     */
    private function recover_from_trash($attachment_id, $relative_path) {
        // Sanitize path - prevent directory traversal attacks
        $relative_path = ltrim($relative_path, '/');
        if (empty($relative_path) || strpos($relative_path, '..') !== false) {
            return false;
        }

        $trash_path = $this->trash_dir . '/' . $relative_path;
        $upload_dir = wp_upload_dir()['basedir'];
        $original_path = $upload_dir . '/' . $relative_path;

        // File must exist to recover
        if (!file_exists($trash_path)) return false;

        // Verify path is within trash directory (prevent symlink attacks)
        // Use realpath on the actual file since we know it exists
        $real_trash_path = realpath($trash_path);
        $real_trash_dir = realpath($this->trash_dir);
        if ($real_trash_path === false || $real_trash_dir === false || strpos($real_trash_path, $real_trash_dir) !== 0) {
            return false;
        }

        $original_subdir = dirname($original_path);
        if (!is_dir($original_subdir)) {
            wp_mkdir_p($original_subdir);
        }

        if (rename($trash_path, $original_path)) {
            $metadata = wp_get_attachment_metadata($attachment_id);
            if (isset($metadata['sizes']) && is_array($metadata['sizes'])) {
                foreach ($metadata['sizes'] as $size => $data) {
                    $size_trash = dirname($trash_path) . '/' . $data['file'];
                    $size_original = dirname($original_path) . '/' . $data['file'];
                    if (file_exists($size_trash)) {
                        @rename($size_trash, $size_original);
                    }
                }
            }

            $trash_meta = get_option('switchboard_umf_trash_meta', []);
            unset($trash_meta[$attachment_id]);
            update_option('switchboard_umf_trash_meta', $trash_meta);

            return true;
        }

        return false;
    }

    /**
     * Permanently delete attachment
     */
    private function permanently_delete($attachment_id) {
        $trash_meta = get_option('switchboard_umf_trash_meta', []);
        if (isset($trash_meta[$attachment_id])) {
            $original_path = $trash_meta[$attachment_id]['original_path'];

            // Sanitize path - prevent directory traversal attacks
            $original_path = ltrim($original_path, '/');
            $should_delete_files = true;

            if (empty($original_path) || strpos($original_path, '..') !== false) {
                // Path is suspicious - skip file deletion but still clean up metadata
                $should_delete_files = false;
            }

            $trash_path = $this->trash_dir . '/' . $original_path;

            // Only attempt file deletion if path is valid and file exists
            if ($should_delete_files && file_exists($trash_path)) {
                // Verify path is within trash directory
                $real_trash_path = realpath($trash_path);
                $real_trash_dir = realpath($this->trash_dir);
                if ($real_trash_path !== false && $real_trash_dir !== false && strpos($real_trash_path, $real_trash_dir) === 0) {
                    @unlink($trash_path);
                    $metadata = wp_get_attachment_metadata($attachment_id);
                    if (isset($metadata['sizes']) && is_array($metadata['sizes'])) {
                        foreach ($metadata['sizes'] as $size => $data) {
                            $size_path = dirname($trash_path) . '/' . $data['file'];
                            if (file_exists($size_path)) {
                                @unlink($size_path);
                            }
                        }
                    }
                }
            }
            unset($trash_meta[$attachment_id]);
            update_option('switchboard_umf_trash_meta', $trash_meta);
        }

        wp_delete_attachment($attachment_id, true);
    }

    /**
     * Clean trash directory (remove empty folders)
     */
    private function clean_trash_directory() {
        if (!is_dir($this->trash_dir)) return;

        $iterator = new RecursiveIteratorIterator(
            new RecursiveDirectoryIterator($this->trash_dir, RecursiveDirectoryIterator::SKIP_DOTS),
            RecursiveIteratorIterator::CHILD_FIRST
        );

        foreach ($iterator as $file) {
            if ($file->isDir() && $file->getFilename() !== '.htaccess') {
                @rmdir($file->getPathname());
            }
        }
    }

    /**
     * Scheduled cleanup - delete items in trash older than retention period
     */
    public function cleanup_trash() {
        $settings = get_option($this->option_key, $this->get_defaults());
        $days = isset($settings['trash_days']) ? intval($settings['trash_days']) : 30;

        $trash_meta = get_option('switchboard_umf_trash_meta', []);
        $cutoff = time() - ($days * DAY_IN_SECONDS);

        global $wpdb;

        foreach ($trash_meta as $attachment_id => $meta) {
            if ($meta['trashed_at'] < $cutoff) {
                $this->permanently_delete($attachment_id);
                $wpdb->delete($this->scan_table, ['post_id' => $attachment_id]);
            }
        }

        $this->clean_trash_directory();
    }

    /**
     * Render settings panel
     */
    public function render_settings($settings = []) {
        global $wpdb;

        $settings = wp_parse_args($settings, $this->get_defaults());
        $last_scan = get_option($this->option_key . '_last_scan', []);
        $scan_progress = $this->get_scan_progress();

        $total_media = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'attachment'");
        $unused_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->scan_table} WHERE ignored = 0 AND deleted = 0");
        $ignored_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->scan_table} WHERE ignored = 1");
        $trash_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->scan_table} WHERE deleted = 1");
        $total_unused_size = $wpdb->get_var("SELECT SUM(size) FROM {$this->scan_table} WHERE ignored = 0 AND deleted = 0");

        // Check if there's an incomplete scan
        $has_incomplete_scan = $scan_progress && isset($scan_progress['offset']) && $scan_progress['offset'] < $scan_progress['total'];

        $nonce = wp_create_nonce('switchboard_umf');
        ?>
        <input type="hidden" name="module" value="unused-media-finder">

        <style>
        .umf-stats-grid { display: grid; grid-template-columns: repeat(4, 1fr); gap: 16px; margin-bottom: 24px; }
        .umf-stat-card { background: var(--gray-50, #f9fafb); border: 1px solid var(--gray-200, #e5e7eb); border-radius: 8px; padding: 16px; text-align: center; }
        .umf-stat-value { font-size: 28px; font-weight: 600; color: var(--gray-900, #111827); line-height: 1.2; }
        .umf-stat-value.danger { color: var(--red-600, #dc2626); }
        .umf-stat-value.success { color: var(--green-600, #16a34a); }
        .umf-stat-label { font-size: 13px; color: var(--gray-500, #6b7280); margin-top: 4px; }
        .umf-progress-wrap { display: none; background: var(--gray-50, #f9fafb); border: 1px solid var(--gray-200, #e5e7eb); border-radius: 8px; padding: 20px; margin-bottom: 24px; }
        .umf-progress-bar-container { height: 8px; background: var(--gray-200, #e5e7eb); border-radius: 4px; overflow: hidden; margin: 12px 0; }
        .umf-progress-bar { height: 100%; background: var(--primary-500, #6366f1); border-radius: 4px; transition: width 0.3s; width: 0%; }
        .umf-progress-text { font-size: 13px; color: var(--gray-600, #4b5563); }
        .umf-tabs { display: flex; gap: 4px; border-bottom: 1px solid var(--gray-200, #e5e7eb); margin-bottom: 16px; }
        .umf-tab { padding: 10px 16px; font-size: 14px; font-weight: 500; color: var(--gray-500, #6b7280); background: none; border: none; cursor: pointer; border-bottom: 2px solid transparent; margin-bottom: -1px; transition: all 0.15s; }
        .umf-tab:hover { color: var(--gray-700, #374151); }
        .umf-tab.active { color: var(--primary-600, #4f46e5); border-bottom-color: var(--primary-600, #4f46e5); }
        .umf-tab-count { display: inline-block; background: var(--gray-100, #f3f4f6); color: var(--gray-600, #4b5563); font-size: 12px; padding: 2px 8px; border-radius: 10px; margin-left: 6px; }
        .umf-tab.active .umf-tab-count { background: var(--primary-100, #e0e7ff); color: var(--primary-700, #4338ca); }
        .umf-bulk-bar { display: flex; align-items: center; gap: 12px; padding: 12px 16px; background: var(--gray-50, #f9fafb); border: 1px solid var(--gray-200, #e5e7eb); border-radius: 8px; margin-bottom: 16px; }
        .umf-bulk-bar label { display: flex; align-items: center; gap: 8px; font-size: 14px; color: var(--gray-700, #374151); cursor: pointer; }
        .umf-results-wrap { display: none; }
        .umf-results-table { width: 100%; border-collapse: collapse; font-size: 14px; }
        .umf-results-table th { text-align: left; padding: 12px; background: var(--gray-50, #f9fafb); border-bottom: 1px solid var(--gray-200, #e5e7eb); font-weight: 500; color: var(--gray-700, #374151); }
        .umf-results-table td { padding: 12px; border-bottom: 1px solid var(--gray-100, #f3f4f6); vertical-align: middle; }
        .umf-results-table tr:hover td { background: var(--gray-50, #f9fafb); }
        .umf-thumb { width: 50px; height: 50px; border-radius: 6px; object-fit: cover; background: var(--gray-100, #f3f4f6); }
        .umf-thumb-placeholder { width: 50px; height: 50px; border-radius: 6px; background: var(--gray-100, #f3f4f6); display: flex; align-items: center; justify-content: center; }
        .umf-thumb-placeholder .dashicons { font-size: 24px; color: var(--gray-400, #9ca3af); }
        .umf-file-info { display: flex; flex-direction: column; gap: 2px; }
        .umf-file-title { font-weight: 500; color: var(--gray-900, #111827); }
        .umf-file-path { font-size: 12px; color: var(--gray-500, #6b7280); max-width: 300px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }
        .umf-pagination { display: flex; align-items: center; justify-content: space-between; padding: 16px 0; border-top: 1px solid var(--gray-200, #e5e7eb); margin-top: 16px; }
        .umf-pagination-info { font-size: 14px; color: var(--gray-600, #4b5563); }
        .umf-pagination-buttons { display: flex; gap: 8px; }
        .umf-empty-state { text-align: center; padding: 48px 24px; color: var(--gray-500, #6b7280); }
        .umf-empty-state .dashicons { font-size: 48px; width: 48px; height: 48px; margin-bottom: 16px; color: var(--gray-300, #d1d5db); }
        @media (max-width: 782px) { .umf-stats-grid { grid-template-columns: repeat(2, 1fr); } }
        </style>

        <div class="umf-stats-grid">
            <div class="umf-stat-card">
                <div class="umf-stat-value"><?php echo number_format($total_media); ?></div>
                <div class="umf-stat-label">Total Media</div>
            </div>
            <div class="umf-stat-card">
                <div class="umf-stat-value <?php echo $unused_count > 0 ? 'danger' : 'success'; ?>"><?php echo number_format($unused_count); ?></div>
                <div class="umf-stat-label">Unused Files</div>
            </div>
            <div class="umf-stat-card">
                <div class="umf-stat-value"><?php echo size_format($total_unused_size ?: 0); ?></div>
                <div class="umf-stat-label">Space to Reclaim</div>
            </div>
            <div class="umf-stat-card">
                <div class="umf-stat-value"><?php echo number_format($trash_count); ?></div>
                <div class="umf-stat-label">In Trash</div>
            </div>
        </div>

        <div class="sb-sp-info-box sb-sp-info-box-tip" style="margin-bottom: 24px;">
            <h4 class="sb-sp-heading-sm">Important</h4>
            <p class="sb-sp-description" style="margin-bottom: 8px;">
                <strong>Always backup your site before deleting media files.</strong>
                Deleted files are moved to trash first and can be recovered within <?php echo intval($settings['trash_days']); ?> days.
            </p>
            <p class="sb-sp-description" style="margin: 0;">
                The scanner checks post content, featured images, galleries, WooCommerce products, and meta fields to identify truly unused media.
            </p>
        </div>

        <?php if ($has_incomplete_scan): ?>
        <div class="sb-sp-info-box sb-sp-info-box-info" style="margin-bottom: 24px;" id="umf-resume-notice">
            <h4 class="sb-sp-heading-sm">Incomplete Scan Detected</h4>
            <p class="sb-sp-description" style="margin-bottom: 12px;">
                A previous scan was interrupted at <?php echo round(($scan_progress['offset'] / $scan_progress['total']) * 100); ?>%
                (<?php echo number_format($scan_progress['offset']); ?> of <?php echo number_format($scan_progress['total']); ?> items).
                Found <?php echo number_format($scan_progress['unused_found']); ?> unused so far.
            </p>
            <div class="sb-sp-button-group">
                <button type="button" id="umf-resume-btn" class="sb-module-button sb-module-button-primary">
                    <span class="dashicons dashicons-controls-play" style="margin-right: 4px;"></span>
                    Resume Scan
                </button>
                <button type="button" id="umf-restart-btn" class="sb-module-button sb-module-button-secondary">
                    <span class="dashicons dashicons-image-rotate" style="margin-right: 4px;"></span>
                    Start Fresh
                </button>
            </div>
        </div>
        <?php endif; ?>

        <div class="sb-sp-group" id="umf-scan-controls" style="margin-bottom: 24px;<?php echo $has_incomplete_scan ? ' display:none;' : ''; ?>">
            <div class="sb-sp-button-group">
                <button type="button" id="umf-scan-btn" class="sb-module-button sb-module-button-primary">
                    <span class="dashicons dashicons-search" style="margin-right: 4px;"></span>
                    Scan for Unused Media
                </button>
                <?php if ($last_scan): ?>
                <span class="sb-sp-description" style="margin-left: 12px;">
                    Last scan: <?php echo esc_html($last_scan['time'] ?? 'Never'); ?>
                    <?php if (isset($last_scan['unused_found'])): ?>
                        (found <?php echo intval($last_scan['unused_found']); ?> unused)
                    <?php endif; ?>
                </span>
                <?php endif; ?>
            </div>
        </div>

        <div class="umf-progress-wrap" id="umf-progress">
            <strong id="umf-progress-title">Scanning your media library...</strong>
            <div class="umf-progress-bar-container">
                <div class="umf-progress-bar" id="umf-progress-bar"></div>
            </div>
            <div class="umf-progress-text" id="umf-progress-text">Starting scan...</div>
        </div>

        <div class="umf-results-wrap" id="umf-results">
            <div class="umf-tabs">
                <button type="button" class="umf-tab active" data-tab="unused">
                    Unused <span class="umf-tab-count" id="count-unused"><?php echo $unused_count; ?></span>
                </button>
                <button type="button" class="umf-tab" data-tab="ignored">
                    Ignored <span class="umf-tab-count" id="count-ignored"><?php echo $ignored_count; ?></span>
                </button>
                <button type="button" class="umf-tab" data-tab="trash">
                    Trash <span class="umf-tab-count" id="count-trash"><?php echo $trash_count; ?></span>
                </button>
            </div>

            <div class="umf-bulk-bar">
                <label><input type="checkbox" id="umf-select-all"> Select All</label>
                <div class="sb-sp-button-group" style="margin-left: auto;">
                    <button type="button" class="sb-module-button sb-module-button-secondary sb-module-button-sm" id="umf-bulk-ignore" data-action="ignore">Ignore Selected</button>
                    <button type="button" class="sb-module-button sb-module-button-secondary sb-module-button-sm" id="umf-bulk-trash" data-action="delete">Move to Trash</button>
                    <button type="button" class="sb-module-button sb-module-button-danger sb-module-button-sm" id="umf-bulk-delete" data-action="delete_permanent">Delete Permanently</button>
                </div>
            </div>

            <div id="umf-table-wrap">
                <table class="umf-results-table">
                    <thead>
                        <tr>
                            <th style="width: 40px;"></th>
                            <th style="width: 70px;">Preview</th>
                            <th>File</th>
                            <th style="width: 100px;">Size</th>
                            <th style="width: 140px;">Actions</th>
                        </tr>
                    </thead>
                    <tbody id="umf-results-body"></tbody>
                </table>
                <div class="umf-pagination">
                    <div class="umf-pagination-info" id="umf-pagination-info"></div>
                    <div class="umf-pagination-buttons">
                        <button type="button" class="sb-module-button sb-module-button-secondary sb-module-button-sm" id="umf-prev" disabled>Previous</button>
                        <button type="button" class="sb-module-button sb-module-button-secondary sb-module-button-sm" id="umf-next">Next</button>
                    </div>
                </div>
            </div>

            <div class="umf-empty-state" id="umf-empty" style="display: none;">
                <span class="dashicons dashicons-yes-alt"></span>
                <p><strong>No items found</strong></p>
                <p id="umf-empty-text">Your media library is clean!</p>
            </div>
        </div>

        <hr class="sb-sp-divider" style="margin: 32px 0;">

        <h3 class="sb-sp-heading">Settings</h3>

        <div class="sb-sp-group sb-sp-field-horizontal" style="margin-bottom: 24px;">
            <div class="sb-sp-field-horizontal-left">
                <label for="setting_images_only" class="sb-sp-label">Scan Images Only</label>
                <p class="sb-sp-description">Only scan image files (JPEG, PNG, GIF, WebP, AVIF, SVG)</p>
            </div>
            <select name="setting_images_only" id="setting_images_only" class="sb-sp-select">
                <option value="no" <?php selected($settings['images_only'], 'no'); ?>>No - Scan all media types</option>
                <option value="yes" <?php selected($settings['images_only'], 'yes'); ?>>Yes - Images only</option>
            </select>
        </div>

        <div class="sb-sp-group sb-sp-field-horizontal" style="margin-bottom: 24px;">
            <div class="sb-sp-field-horizontal-left">
                <label for="setting_trash_days" class="sb-sp-label">Trash Retention</label>
                <p class="sb-sp-description">Days to keep files in trash before permanent deletion</p>
            </div>
            <select name="setting_trash_days" id="setting_trash_days" class="sb-sp-select">
                <option value="7" <?php selected($settings['trash_days'], 7); ?>>7 days</option>
                <option value="14" <?php selected($settings['trash_days'], 14); ?>>14 days</option>
                <option value="30" <?php selected($settings['trash_days'], 30); ?>>30 days</option>
                <option value="60" <?php selected($settings['trash_days'], 60); ?>>60 days</option>
                <option value="90" <?php selected($settings['trash_days'], 90); ?>>90 days</option>
            </select>
        </div>

        <div class="sb-sp-group sb-sp-field-horizontal" style="margin-bottom: 24px;">
            <div class="sb-sp-field-horizontal-left">
                <label for="setting_batch_size" class="sb-sp-label">Batch Size</label>
                <p class="sb-sp-description">Items to process per request. Lower values for slower servers.</p>
            </div>
            <select name="setting_batch_size" id="setting_batch_size" class="sb-sp-select">
                <option value="25" <?php selected($settings['batch_size'], 25); ?>>25 (Very slow servers)</option>
                <option value="50" <?php selected($settings['batch_size'], 50); ?>>50 (Default)</option>
                <option value="100" <?php selected($settings['batch_size'], 100); ?>>100 (Fast servers)</option>
                <option value="200" <?php selected($settings['batch_size'], 200); ?>>200 (Very fast servers)</option>
            </select>
        </div>

        <script>
        jQuery(document).ready(function($) {
            var nonce = '<?php echo $nonce; ?>';
            var ajaxUrl = '<?php echo admin_url('admin-ajax.php'); ?>';
            var currentTab = 'unused';
            var currentPage = 1;
            var isScanning = false;

            var retryCount = 0;
            var maxRetries = 3;
            var totalUnused = 0;
            var isFreshStart = true;

            // Resume button handler
            $('#umf-resume-btn').on('click', function() {
                if (isScanning) return;

                isScanning = true;
                isFreshStart = false;
                retryCount = 0;
                totalUnused = <?php echo $has_incomplete_scan ? intval($scan_progress['unused_found']) : 0; ?>;

                $('#umf-resume-notice').hide();
                $('#umf-progress').show();
                $('#umf-progress-title').text('Resuming scan...');
                $('#umf-progress-bar').css('width', '<?php echo $has_incomplete_scan ? round(($scan_progress['offset'] / $scan_progress['total']) * 100) : 0; ?>%');
                $('#umf-progress-text').text('Resuming from <?php echo $has_incomplete_scan ? number_format($scan_progress['offset']) : 0; ?> items...');
                $('#umf-results').hide();

                runScan(<?php echo $has_incomplete_scan ? intval($scan_progress['offset']) : 0; ?>);
            });

            // Restart button handler
            $('#umf-restart-btn').on('click', function() {
                if (isScanning) return;
                if (!confirm('This will clear previous scan results and start fresh. Continue?')) return;

                isScanning = true;
                isFreshStart = true;
                retryCount = 0;
                totalUnused = 0;

                $('#umf-resume-notice').hide();
                $('#umf-progress').show();
                $('#umf-progress-title').text('Scanning your media library...');
                $('#umf-progress-bar').css('width', '0%');
                $('#umf-progress-text').text('Starting fresh scan...');
                $('#umf-results').hide();

                runScan(0);
            });

            // Main scan button handler
            $('#umf-scan-btn').on('click', function() {
                if (isScanning) return;
                if (!confirm('This will scan your entire media library. Continue?')) return;

                isScanning = true;
                isFreshStart = true;
                retryCount = 0;
                totalUnused = 0;
                $(this).prop('disabled', true).text('Scanning...');
                $('#umf-progress').show();
                $('#umf-progress-title').text('Scanning your media library...');
                $('#umf-progress-bar').css('width', '0%');
                $('#umf-progress-text').text('Starting scan...');
                $('#umf-results').hide();
                runScan(0);
            });

            function runScan(offset) {
                $.ajax({
                    url: ajaxUrl,
                    type: 'POST',
                    timeout: 120000, // 2 minute timeout
                    data: {
                        action: 'switchboard_umf_scan',
                        nonce: nonce,
                        offset: offset,
                        fresh: isFreshStart && offset === 0 ? 'true' : 'false'
                    },
                    success: function(response) {
                        retryCount = 0; // Reset retry count on success
                        if (response.success) {
                            var data = response.data;
                            if (data.complete) {
                                isScanning = false;
                                $('#umf-scan-btn').prop('disabled', false).html('<span class="dashicons dashicons-search" style="margin-right: 4px;"></span> Scan for Unused Media');
                                $('#umf-scan-controls').show();
                                $('#umf-progress-bar').css('width', '100%');
                                $('#umf-progress-text').text('Scan complete! Found ' + data.unused + ' unused files.');
                                setTimeout(function() {
                                    $('#umf-progress').hide();
                                    updateCounts({ unused: data.unused, ignored: data.ignored || 0, trash: data.trash || 0 });
                                    $('#umf-results').show();
                                    loadResults('unused', 1);
                                }, 1000);
                            } else {
                                // Use server-side total_unused for accurate count
                                totalUnused = data.total_unused || (totalUnused + (data.batch_unused || 0));
                                $('#umf-progress-bar').css('width', data.progress + '%');
                                $('#umf-progress-text').text('Scanned ' + data.scanned.toLocaleString() + ' of ' + data.total.toLocaleString() + ' items (' + data.progress + '%) - ' + totalUnused + ' unused found');
                                // Small delay to prevent overwhelming the server
                                setTimeout(function() {
                                    runScan(data.next_offset);
                                }, 100);
                            }
                        } else {
                            handleScanError(offset, response.data || 'Unknown error');
                        }
                    },
                    error: function(xhr, status, error) {
                        var errorMsg = 'Connection error';
                        if (status === 'timeout') {
                            errorMsg = 'Request timed out';
                        } else if (xhr.responseText) {
                            // Try to extract error from response
                            try {
                                var resp = JSON.parse(xhr.responseText);
                                if (resp.data) errorMsg = resp.data;
                            } catch(e) {
                                // If not JSON, show status and HTTP error
                                errorMsg = status + (error ? ': ' + error : '');
                            }
                        } else if (error) {
                            errorMsg = error;
                        }
                        handleScanError(offset, errorMsg);
                    }
                });
            }

            function handleScanError(offset, errorMsg) {
                retryCount++;
                if (retryCount <= maxRetries) {
                    $('#umf-progress-text').text('Error: ' + errorMsg + '. Retrying (' + retryCount + '/' + maxRetries + ')...');
                    setTimeout(function() {
                        runScan(offset);
                    }, 2000); // Wait 2 seconds before retry
                } else {
                    // After max retries, show partial results and allow resume later
                    isScanning = false;
                    $('#umf-scan-btn').prop('disabled', false).html('<span class="dashicons dashicons-search" style="margin-right: 4px;"></span> Scan for Unused Media');
                    $('#umf-scan-controls').show();
                    $('#umf-progress-text').html('Scan paused due to connection issues. <strong>Refresh the page to resume.</strong> (' + totalUnused + ' unused found so far)');
                    setTimeout(function() {
                        $('#umf-results').show();
                        loadResults('unused', 1);
                    }, 2000);
                }
            }

            function resetScanBtn() {
                isScanning = false;
                retryCount = 0;
                $('#umf-scan-btn').prop('disabled', false).html('<span class="dashicons dashicons-search" style="margin-right: 4px;"></span> Scan for Unused Media');
                $('#umf-scan-controls').show();
                $('#umf-progress').hide();
            }

            $('.umf-tab').on('click', function() {
                var tab = $(this).data('tab');
                $('.umf-tab').removeClass('active');
                $(this).addClass('active');
                currentTab = tab;
                currentPage = 1;
                loadResults(tab, 1);
                updateBulkButtons(tab);
            });

            function updateBulkButtons(tab) {
                if (tab === 'unused') {
                    $('#umf-bulk-ignore').show().text('Ignore Selected').data('action', 'ignore');
                    $('#umf-bulk-trash').show().text('Move to Trash').data('action', 'delete');
                    $('#umf-bulk-delete').show().text('Delete Permanently');
                } else if (tab === 'ignored') {
                    $('#umf-bulk-ignore').show().text('Unignore Selected').data('action', 'unignore');
                    $('#umf-bulk-trash').show().text('Move to Trash').data('action', 'delete');
                    $('#umf-bulk-delete').show().text('Delete Permanently');
                } else if (tab === 'trash') {
                    $('#umf-bulk-ignore').hide();
                    $('#umf-bulk-trash').show().text('Recover Selected').data('action', 'recover');
                    $('#umf-bulk-delete').show().text('Delete Permanently');
                }
            }

            function loadResults(tab, page) {
                $.ajax({
                    url: ajaxUrl,
                    type: 'POST',
                    data: { action: 'switchboard_umf_get_results', nonce: nonce, tab: tab, page: page },
                    success: function(response) {
                        if (response.success) {
                            var data = response.data;
                            currentPage = data.page;
                            updateCounts(data.counts);

                            if (data.items.length === 0) {
                                $('#umf-table-wrap').hide();
                                $('#umf-empty').show();
                                var emptyText = tab === 'unused' ? 'No unused media found!' : (tab === 'ignored' ? 'No ignored items.' : 'Trash is empty.');
                                $('#umf-empty-text').text(emptyText);
                            } else {
                                $('#umf-empty').hide();
                                $('#umf-table-wrap').show();
                                renderResults(data.items, tab);

                                var start = ((data.page - 1) * data.per_page) + 1;
                                var end = Math.min(data.page * data.per_page, data.total);
                                $('#umf-pagination-info').text('Showing ' + start + '-' + end + ' of ' + data.total + ' (' + data.total_size + ')');
                                $('#umf-prev').prop('disabled', data.page <= 1);
                                $('#umf-next').prop('disabled', data.page >= data.total_pages);
                            }
                        }
                    }
                });
            }

            function renderResults(items, tab) {
                var html = '';
                items.forEach(function(item) {
                    var thumb = item.thumb ? '<img src="' + item.thumb + '" class="umf-thumb" alt="">' : '<div class="umf-thumb-placeholder"><span class="dashicons dashicons-media-default"></span></div>';
                    var actions = '';
                    if (tab === 'trash') {
                        actions = '<button type="button" class="sb-sp-card-action-btn umf-action" style="margin-right: 4px;" data-action="recover" data-id="' + item.id + '">Recover</button>';
                        actions += '<button type="button" class="sb-sp-card-action-btn sb-sp-card-action-btn-delete umf-action" data-action="delete_permanent" data-id="' + item.id + '">Delete</button>';
                    } else {
                        actions = '<button type="button" class="sb-sp-card-action-btn umf-action" style="margin-right: 4px;" data-action="delete" data-id="' + item.id + '">Trash</button>';
                        actions += '<button type="button" class="sb-sp-card-action-btn sb-sp-card-action-btn-delete umf-action" data-action="delete_permanent" data-id="' + item.id + '">Delete</button>';
                    }

                    html += '<tr data-id="' + item.id + '">';
                    html += '<td><input type="checkbox" class="umf-item-check" value="' + item.id + '"></td>';
                    html += '<td>' + thumb + '</td>';
                    html += '<td><div class="umf-file-info"><span class="umf-file-title">' + escapeHtml(item.title) + '</span><span class="umf-file-path">' + escapeHtml(item.path) + '</span></div></td>';
                    html += '<td>' + item.size + '</td>';
                    html += '<td>' + actions + '</td>';
                    html += '</tr>';
                });
                $('#umf-results-body').html(html);
                $('#umf-select-all').prop('checked', false);
            }

            function updateCounts(counts) {
                $('#count-unused').text(counts.unused);
                $('#count-ignored').text(counts.ignored);
                $('#count-trash').text(counts.trash);
            }

            function escapeHtml(text) {
                if (!text) return '';
                return $('<div>').text(text).html();
            }

            $('#umf-select-all').on('change', function() {
                $('.umf-item-check').prop('checked', $(this).prop('checked'));
            });

            $(document).on('click', '.umf-action', function() {
                var action = $(this).data('action');
                var id = $(this).data('id');
                var $row = $(this).closest('tr');
                var confirmMsg = action === 'delete_permanent' ? 'Permanently delete this file?' : (action === 'recover' ? 'Recover this file?' : 'Move to trash?');
                if (!confirm(confirmMsg)) return;

                $.ajax({
                    url: ajaxUrl,
                    type: 'POST',
                    data: { action: 'switchboard_umf_bulk_action', nonce: nonce, bulk_action: action, ids: [id] },
                    success: function(response) {
                        if (response.success) {
                            $row.fadeOut(function() { $(this).remove(); });
                            loadResults(currentTab, currentPage);
                        }
                    }
                });
            });

            $('#umf-bulk-ignore, #umf-bulk-trash, #umf-bulk-delete').on('click', function() {
                var action = $(this).data('action');
                var ids = [];
                $('.umf-item-check:checked').each(function() { ids.push($(this).val()); });

                if (ids.length === 0) { alert('Please select at least one item.'); return; }

                var confirmMsg = action === 'delete_permanent' ? 'Permanently delete ' + ids.length + ' files?' : 'Process ' + ids.length + ' files?';
                if (!confirm(confirmMsg)) return;

                $.ajax({
                    url: ajaxUrl,
                    type: 'POST',
                    data: { action: 'switchboard_umf_bulk_action', nonce: nonce, bulk_action: action, ids: ids },
                    success: function(response) {
                        if (response.success) { loadResults(currentTab, currentPage); }
                    }
                });
            });

            $('#umf-prev').on('click', function() { if (currentPage > 1) loadResults(currentTab, currentPage - 1); });
            $('#umf-next').on('click', function() { loadResults(currentTab, currentPage + 1); });

            <?php if ($unused_count > 0 || $ignored_count > 0 || $trash_count > 0): ?>
            $('#umf-results').show();
            loadResults('unused', 1);
            <?php endif; ?>
        });
        </script>
        <?php
    }
}

// Initialize and register with data system
$switchboard_instance = new SwitchboardUnusedMediaFinder();
Switchboard_Data_Registry::get_instance()->auto_register($switchboard_instance, __FILE__);
