<?php
class FFLCockpit_Sync_Endpoint {

    private static $lock_file;
    private static $stop_file;
    private static $status_file;
    private static $queue_file;
    private static $media_cleanup_file;
    private static $files_initialized = false;
    private static array $fflckey = ['My4yMTIuMTg1LjE4Nw=='];
    private static $version = "2.0.25";

    private static function init_file_paths() {
        if (self::$files_initialized) return;
        $upload_dir = wp_upload_dir();
        $base = trailingslashit($upload_dir['basedir']) . 'fflcockpit/';
        if (!file_exists($base)) {
            wp_mkdir_p($base);
            // Protect directory from direct web access
            @file_put_contents($base . '.htaccess', 'Deny from all');
            @file_put_contents($base . 'index.php', '<?php // Silence is golden.');
        }
        // Migrate old files from plugin directory if they exist
        $old_files = [
            'process.lock' => FFLC_PATH . 'process.lock',
            'stop.signal'  => FFLC_PATH . 'stop.signal',
            'status.json'  => FFLC_PATH . 'status.json',
            'queue.json'   => FFLC_PATH . 'queue.json',
        ];
        foreach ($old_files as $name => $old_path) {
            $new_path = $base . $name;
            if (file_exists($old_path) && !file_exists($new_path)) {
                @rename($old_path, $new_path);
            } elseif (file_exists($old_path)) {
                @unlink($old_path);
            }
        }
        self::$lock_file = $base . 'process.lock';
        self::$stop_file = $base . 'stop.signal';
        self::$status_file = $base . 'status.json';
        self::$queue_file = $base . 'queue.json';
        self::$media_cleanup_file = $base . 'media_cleanup_queue.json';
        self::$files_initialized = true;
    }

    private static function get_client_ip(): string {
        $ip = isset($_SERVER['REMOTE_ADDR']) ? sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR'])) : 'unknown';
        if (!empty($_SERVER['HTTP_CF_CONNECTING_IP'])) {
            // CloudFlare can also have multiple IPs, parse like X-Forwarded-For
            $cf_ips = explode(',', sanitize_text_field(wp_unslash($_SERVER['HTTP_CF_CONNECTING_IP'])));
            $ip = trim($cf_ips[0]);
        } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
            $forwarded_ips = explode(',', sanitize_text_field(wp_unslash($_SERVER['HTTP_X_FORWARDED_FOR'])));
            $ip = trim($forwarded_ips[0]);
        }
        return trim($ip);
    }

    public static function fflcockpit_is_allowed(array $fflckey, ?WP_REST_Request $request = null): bool {
        $ip = self::get_client_ip();

        // 1. Get the securely stored token
        $stored_token = get_option('g_ffl_cockpit_key');
        
        // 2. Try to retrieve the token from the request
        $request_token = ($request) ? $request->get_header('X-GFFL-Token') : '';

        // 3. Check IP allowlist
        $fflckey = array_map('base64_decode', $fflckey);
        $allowed_ips = array_map('trim', $fflckey);
        $ip_allowed = in_array($ip, $allowed_ips, true);
        
        // 4. Check token if both stored token and request token exist
        $token_valid = false;
        if (!empty($stored_token) && !empty($request_token)) {
            $token_valid = hash_equals($stored_token, $request_token);
        } 
        
        return $ip_allowed && $token_valid;
    }

    public static function register_routes() {
        register_rest_route('fflcockpit/v1', '/queue', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_enqueue_only'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/process', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_process'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/stop', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_stop'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/clear', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'clear_queue'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/status', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'get_status'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);
        
        register_rest_route('fflcockpit/v1', '/export', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_export'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/download-export', [
            'methods' => 'GET',
            'callback' => [__CLASS__, 'handle_download_export'],
            'permission_callback' => [__CLASS__, 'download_permission_callback'],
        ]);

        register_rest_route('fflcockpit/v1', '/remote-file-exists', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_remote_file_exists'],
            'permission_callback' => fn() => true,
        ]);

        register_rest_route('fflcockpit/v1', '/cleanup-orphaned-media', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_cleanup_orphaned_media'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/server_status', [
            'methods' => 'GET',
            'callback' => [__CLASS__, 'handle_server_status'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/rebuild-woocommerce-data', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_rebuild_woocommerce_data'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/cleanup-sku-conflicts', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_cleanup_sku_conflicts'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/update-term-counts', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_update_term_counts'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        register_rest_route('fflcockpit/v1', '/media-cleanup/clear', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_media_cleanup_clear'],
            'permission_callback' => function($request) { return self::fflcockpit_is_allowed(self::$fflckey, $request); },
        ]);

        // AI Helpdesk endpoint - accessible to logged-in users with cockpit access
        register_rest_route('fflcockpit/v1', '/ai-helpdesk', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_ai_helpdesk'],
            'permission_callback' => function() { 
                return is_user_logged_in() && current_user_can('g_ffl_cockpit_access'); 
            },
        ]);

        // AI Helpdesk polling endpoint - poll for async result
        register_rest_route('fflcockpit/v1', '/ai-helpdesk-poll', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_ai_helpdesk_poll'],
            'permission_callback' => function() { 
                return is_user_logged_in() && current_user_can('g_ffl_cockpit_access'); 
            },
        ]);

        // AI Knowledge Base endpoint - admin only
        register_rest_route('fflcockpit/v1', '/ai-knowledge', [
            'methods' => 'POST',
            'callback' => [__CLASS__, 'handle_ai_knowledge'],
            'permission_callback' => function() { 
                return current_user_can('manage_options'); 
            },
        ]);
    }

    /**
     * Handle AI Helpdesk question - submits to external API, returns request_id for polling
     */
    public static function handle_ai_helpdesk(WP_REST_Request $request) {
        $body = $request->get_json_params();
        $question = isset($body['question']) ? sanitize_text_field($body['question']) : '';
        
        if (empty($question)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => 'Question is required'
            ], 400);
        }

        $api_key = get_option('g_ffl_cockpit_key');
        if (empty($api_key)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => 'API key not configured'
            ], 500);
        }

        // Submit question to external API (async - returns request_id immediately)
        $response = wp_remote_post('https://ffl-api.garidium.com', [
            'timeout' => 15,
            'headers' => [
                'Accept' => 'application/json',
                'Content-Type' => 'application/json',
                'x-api-key' => $api_key,
            ],
            'body' => wp_json_encode([
                'action' => 'ask_ai_helpdesk',
                'data' => [
                    'question' => $question,
                    'api_key' => $api_key,
                    'use_claude' => false,
                    'top_k' => 5,
                ]
            ]),
        ]);

        if (is_wp_error($response)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => $response->get_error_message()
            ], 500);
        }

        $response_body = wp_remote_retrieve_body($response);
        $data = json_decode($response_body, true);

        return new WP_REST_Response($data, 200);
    }

    /**
     * Handle AI Helpdesk polling - checks status of async request
     */
    public static function handle_ai_helpdesk_poll(WP_REST_Request $request) {
        $body = $request->get_json_params();
        $request_id = isset($body['request_id']) ? sanitize_text_field($body['request_id']) : '';
        
        if (empty($request_id)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => 'request_id is required'
            ], 400);
        }

        $api_key = get_option('g_ffl_cockpit_key');
        if (empty($api_key)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => 'API key not configured'
            ], 500);
        }

        // Poll for result from external API
        $response = wp_remote_post('https://ffl-api.garidium.com', [
            'timeout' => 10,
            'headers' => [
                'Accept' => 'application/json',
                'Content-Type' => 'application/json',
                'x-api-key' => $api_key,
            ],
            'body' => wp_json_encode([
                'action' => 'get_ai_helpdesk_result',
                'data' => [
                    'request_id' => $request_id,
                ]
            ]),
        ]);

        if (is_wp_error($response)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => $response->get_error_message()
            ], 500);
        }

        $response_body = wp_remote_retrieve_body($response);
        $data = json_decode($response_body, true);

        return new WP_REST_Response($data, 200);
    }

    /**
     * Handle AI Knowledge Base addition - admin only, proxies to external API
     */
    public static function handle_ai_knowledge(WP_REST_Request $request) {
        $body = $request->get_json_params();
        $question = isset($body['question']) ? sanitize_text_field($body['question']) : '';
        $answer = isset($body['answer']) ? sanitize_textarea_field($body['answer']) : '';
        
        if (empty($question) || empty($answer)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => 'Question and answer are required'
            ], 400);
        }

        $api_key = get_option('g_ffl_cockpit_key');
        if (empty($api_key)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => 'API key not configured'
            ], 500);
        }

        // Call external API
        $response = wp_remote_post('https://ffl-api.garidium.com', [
            'timeout' => 30,
            'headers' => [
                'Accept' => 'application/json',
                'Content-Type' => 'application/json',
                'x-api-key' => $api_key,
            ],
            'body' => wp_json_encode([
                'action' => 'add_ai_knowledge',
                'data' => [
                    'question' => $question,
                    'answer' => $answer,
                    'api_key' => $api_key,
                ]
            ]),
        ]);

        if (is_wp_error($response)) {
            return new WP_REST_Response([
                'success' => false,
                'error' => $response->get_error_message()
            ], 500);
        }

        $response_body = wp_remote_retrieve_body($response);
        $data = json_decode($response_body, true);

        return new WP_REST_Response($data, 200);
    }

    public static function handle_enqueue_only(WP_REST_Request $request) {
        $t_handler_start = microtime(true);

        self::init_file_paths();
        $t_after_init = microtime(true);

        $body = $request->get_json_params();
        $t_after_body_parse = microtime(true);

        $products = $body['products'] ?? [];
        $clear = $body['clear'] ?? false;
        $action = $body['action'] ?? null;

        $t_after_clear = $t_after_body_parse;
        if ($clear) {
            self::clear_queue_internal();
            $t_after_clear = microtime(true);
        }

        $queue_file_size_before = (file_exists(self::$queue_file)) ? (int) @filesize(self::$queue_file) : 0;
        self::enqueue_job($body);
        $queue_file_size_after = (file_exists(self::$queue_file)) ? (int) @filesize(self::$queue_file) : 0;
        $t_after_enqueue = microtime(true);

        // Load existing status file (or initialize it)
        $status = file_exists(self::$status_file)
            ? json_decode(file_get_contents(self::$status_file), true)
            : [];

        // If missing or corrupted, reset it
        if (!is_array($status) || empty($status)) {
            self::reset_status_file();
            $status = json_decode(file_get_contents(self::$status_file), true);
        }
        $t_after_status_read = microtime(true);

        // Update base metadata
        $status['plugin_version'] = self::$version;
        $status['status'] = 'queued';

        // Add counts to appropriate action bucket
        if (in_array($action, ['insert', 'update', 'delete'])) {
            if (!isset($status[$action])) {
                $status[$action] = [
                    'queued' => 0,
                    'processed' => 0,
                    'successful' => 0,
                    'failed' => 0,
                    'errors' => []
                ];
            }
            $status[$action]['queued'] += count($products);
        }

        // Total job count and queued count
        $status['job_count'] = ($status['job_count'] ?? 0) + 1;
        $status['queued'] = ($status['queued'] ?? 0) + count($products);
        $status['remaining'] = $status['queued'] - ($status['processed'] ?? 0);

        $body_size_bytes = strlen((string) $request->get_body());

        $status['timings'] = [
            'init_ms'         => round(($t_after_init - $t_handler_start) * 1000, 2),
            'body_parse_ms'   => round(($t_after_body_parse - $t_after_init) * 1000, 2),
            'clear_ms'        => round(($t_after_clear - $t_after_body_parse) * 1000, 2),
            'enqueue_ms'      => round(($t_after_enqueue - $t_after_clear) * 1000, 2),
            'status_read_ms'  => round(($t_after_status_read - $t_after_enqueue) * 1000, 2),
            'body_size_bytes' => $body_size_bytes,
            'products_count'  => count($products),
            'queue_file_size_before_bytes' => $queue_file_size_before,
            'queue_file_size_after_bytes'  => $queue_file_size_after,
        ];

        $t_before_status_write = microtime(true);
        file_put_contents(self::$status_file, json_encode($status, JSON_PRETTY_PRINT));
        $t_after_status_write = microtime(true);

        $status['timings']['status_write_ms'] = round(($t_after_status_write - $t_before_status_write) * 1000, 2);
        $status['timings']['handler_total_ms'] = round(($t_after_status_write - $t_handler_start) * 1000, 2);

        return new WP_REST_Response($status, 202);
    }
    
    
    public static function clear_queue(?WP_REST_Request $request = null) {
        self::init_file_paths();
        self::clear_queue_internal();
        return new WP_REST_Response(['status' => 'queue cleared'], 200);
    }

    private static function clear_queue_internal(): void {
        if (file_exists(self::$queue_file)) {
            wp_delete_file(self::$queue_file);
        }

        if (file_exists(self::$lock_file)) {
            wp_delete_file(self::$lock_file);
        }

        self::reset_status_file();
    }

    public static function handle_process(WP_REST_Request $request) {
        self::init_file_paths();
        $start_time = microtime(true);
        $start_time_utc = gmdate('Y-m-d\TH:i:s\Z', time());
    
        $body = $request->get_json_params();
        $timeout = $body['timeout'] ?? 900;
    
        // Lock to prevent concurrent processing using WP_Filesystem
        global $wp_filesystem;
        if (empty($wp_filesystem)) {
            require_once ABSPATH . '/wp-admin/includes/file.php';
            WP_Filesystem();
        }
        
        // Check if lock file exists (simple file-based locking)
        if ($wp_filesystem->exists(self::$lock_file)) {
            // Check if lock is stale (older than timeout)
            $lock_time = $wp_filesystem->mtime(self::$lock_file);
            if ($lock_time && (time() - $lock_time) < $timeout) {
                return new WP_REST_Response(['status' => 'processing already in progress'], 202);
            }
            // Remove stale lock
            $wp_filesystem->delete(self::$lock_file);
        }
        
        // Create lock file
        $wp_filesystem->put_contents(self::$lock_file, time());

        // inside handle_process before processing jobs
        file_put_contents(self::$status_file, json_encode([
            'plugin_version' => self::$version,
            'start_time_utc' => gmdate('Y-m-d\TH:i:s\Z'),
            'status' => 'processing'
        ] + (file_exists(self::$status_file) ? json_decode(file_get_contents(self::$status_file), true) : [])));

        // Begin processing jobs from the queue
        self::process_jobs($start_time, $start_time_utc, $timeout);
    
        return new WP_REST_Response(['status' => 'completed'], 200);
    }
    

    private static function enqueue_job(array $job): void {
        $queue = [];
    
        if (file_exists(self::$queue_file)) {
            $contents = file_get_contents(self::$queue_file);
            $queue = json_decode($contents, true);
            if (!is_array($queue)) {
                $queue = [];
            }
        }
    
        $queue[] = $job;
    
        file_put_contents(self::$queue_file, json_encode($queue));
    }
    
    private static function process_jobs(float $start_time, string $start_time_utc, int $timeout): void {
        $jobs = [];
        try{
            do {
                if (file_exists(self::$queue_file)) {
                    $queued = json_decode(file_get_contents(self::$queue_file), true);
                    if (is_array($queued) && count($queued) > 0) {
                        $jobs = $queued;
                        wp_delete_file(self::$queue_file);
                    }
                }

                // Set start_time_utc only once for the entire processing run
                $status = file_exists(self::$status_file)
                    ? json_decode(file_get_contents(self::$status_file), true)
                    : [];

                if (empty($status['start_time_utc'])) {
                    $status['plugin_version'] = self::$version;
                    $status['start_time_utc'] = $start_time_utc;
                    $status['status'] = 'processing';
                    file_put_contents(self::$status_file, json_encode($status));
                }

                foreach ($jobs as $job) {
                    self::process_single_job($job, $start_time, $start_time_utc, $timeout);
                }

                $jobs = [];
            } while (file_exists(self::$queue_file));

            // Drain deferred media cleanup queue with whatever time remains
            self::drain_media_cleanup($start_time, $timeout);
        } finally {
            // Ensure lock is released even if exception or error occurs
            global $wp_filesystem;
            if (empty($wp_filesystem)) {
                require_once ABSPATH . '/wp-admin/includes/file.php';
                WP_Filesystem();
            }
            $wp_filesystem->delete(self::$lock_file);
        }
    }

    /**
     * Append attachment IDs to the deferred media cleanup queue.
     * Called by the update processor after a product is SQL-deleted so the
     * heavy wp_delete_attachment() work happens on later sync runs.
     */
    public static function enqueue_media_cleanup(array $attachment_ids, int $source_product_id): void {
        self::init_file_paths();

        $attachment_ids = array_values(array_unique(array_filter(array_map('intval', $attachment_ids))));
        if (empty($attachment_ids)) {
            return;
        }

        $queue = [];
        if (file_exists(self::$media_cleanup_file)) {
            $contents = file_get_contents(self::$media_cleanup_file);
            $decoded = json_decode($contents, true);
            if (is_array($decoded)) {
                $queue = $decoded;
            }
        }

        foreach ($attachment_ids as $attachment_id) {
            $queue[] = [
                'attachment_id' => $attachment_id,
                'source_product_id' => $source_product_id,
                'attempts' => 0,
            ];
        }

        $tmp = self::$media_cleanup_file . '.tmp';
        if (file_put_contents($tmp, json_encode($queue)) !== false) {
            @rename($tmp, self::$media_cleanup_file);
        }

        // Keep status.json's pending count current so /status doesn't have to
        // re-read the (potentially large) queue file on every poll.
        if (file_exists(self::$status_file)) {
            $status_contents = file_get_contents(self::$status_file);
            $status = !empty($status_contents) ? json_decode($status_contents, true) : null;
            if (is_array($status)) {
                if (!isset($status['media_cleanup']) || !is_array($status['media_cleanup'])) {
                    $status['media_cleanup'] = ['deleted' => 0, 'skipped_in_use' => 0, 'failed' => 0];
                }
                $status['media_cleanup']['pending'] = count($queue);
                file_put_contents(self::$status_file, json_encode($status));
            }
        }
    }

    /**
     * Process the deferred media cleanup queue until the run timeout is hit
     * or the queue is empty. Skips attachments still referenced by surviving
     * products; drops entries after 3 failed delete attempts.
     */
    private static function drain_media_cleanup(float $start_time, int $timeout): void {
        global $wpdb;

        if (!file_exists(self::$media_cleanup_file)) {
            return;
        }

        $contents = file_get_contents(self::$media_cleanup_file);
        $queue = json_decode($contents, true);
        if (!is_array($queue) || empty($queue)) {
            wp_delete_file(self::$media_cleanup_file);
            return;
        }

        $status = file_exists(self::$status_file)
            ? json_decode(file_get_contents(self::$status_file), true)
            : [];
        if (!is_array($status)) {
            $status = [];
        }
        if (!isset($status['media_cleanup']) || !is_array($status['media_cleanup'])) {
            $status['media_cleanup'] = ['deleted' => 0, 'skipped_in_use' => 0, 'failed' => 0];
        }

        $batch_size = 200;

        while (!empty($queue)) {
            if (file_exists(self::$stop_file)) {
                break;
            }
            if (microtime(true) - $start_time > $timeout) {
                break;
            }

            $batch = array_splice($queue, 0, $batch_size);
            $entries_by_attachment_id = [];
            foreach ($batch as $entry) {
                $attachment_id = (int) ($entry['attachment_id'] ?? 0);
                if ($attachment_id <= 0) {
                    continue;
                }
                $entries_by_attachment_id[$attachment_id] = $entry;
            }

            if (empty($entries_by_attachment_id)) {
                continue;
            }

            $attachment_ids = array_keys($entries_by_attachment_id);
            $id_placeholders = implode(',', array_fill(0, count($attachment_ids), '%d'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders generated from integer attachment IDs.
            $existing_attachment_ids = $wpdb->get_col($wpdb->prepare(
                "SELECT ID FROM {$wpdb->posts} WHERE ID IN ({$id_placeholders}) AND post_type = 'attachment'",
                ...$attachment_ids
            ));
            $existing_attachment_ids = array_map('intval', (array) $existing_attachment_ids);
            if (empty($existing_attachment_ids)) {
                continue;
            }

            $referenced_attachment_ids = self::get_referenced_attachment_ids($existing_attachment_ids);

            foreach ($existing_attachment_ids as $attachment_index => $attachment_id) {
                if (file_exists(self::$stop_file) || microtime(true) - $start_time > $timeout) {
                    $remaining_batch = [];
                    $remaining_attachment_ids = array_slice($existing_attachment_ids, $attachment_index);
                    foreach ($remaining_attachment_ids as $remaining_attachment_id) {
                        $remaining_batch[] = $entries_by_attachment_id[$remaining_attachment_id];
                    }
                    $queue = array_merge($remaining_batch, $queue);
                    break 2;
                }

                $entry = $entries_by_attachment_id[$attachment_id];

                if (isset($referenced_attachment_ids[$attachment_id])) {
                    $status['media_cleanup']['skipped_in_use']++;
                    continue;
                }

                $deleted = wp_delete_attachment($attachment_id, true);
                if ($deleted) {
                    $status['media_cleanup']['deleted']++;
                } else {
                    $entry['attempts'] = (int) ($entry['attempts'] ?? 0) + 1;
                    if ($entry['attempts'] < 3) {
                        $queue[] = $entry;
                    } else {
                        $status['media_cleanup']['failed']++;
                    }
                }
            }
        }

        if (empty($queue)) {
            wp_delete_file(self::$media_cleanup_file);
        } else {
            // Atomic write: a fatal during file_put_contents() would otherwise
            // truncate the queue file and we'd lose the remaining backlog on
            // the next decode.
            $tmp = self::$media_cleanup_file . '.tmp';
            if (file_put_contents($tmp, json_encode(array_values($queue))) !== false) {
                @rename($tmp, self::$media_cleanup_file);
            }
        }

        $status['media_cleanup']['pending'] = count($queue);
        file_put_contents(self::$status_file, json_encode($status));
    }

    /**
     * Build a lookup of queued attachments still referenced by product image meta.
     *
     * Thumbnail references can use an indexed meta_value IN lookup. Gallery
     * references are stored by WooCommerce as comma-separated IDs, so cache a
     * parsed lookup for the cleanup run instead of using FIND_IN_SET per image.
     */
    private static function get_referenced_attachment_ids(array $attachment_ids): array {
        global $wpdb;

        $attachment_ids = array_values(array_unique(array_filter(array_map('intval', $attachment_ids))));
        if (empty($attachment_ids)) {
            return [];
        }

        $referenced = [];
        $id_lookup = array_fill_keys($attachment_ids, true);
        $meta_value_placeholders = implode(',', array_fill(0, count($attachment_ids), '%s'));
        $meta_values = array_map('strval', $attachment_ids);
        static $gallery_referenced_ids = null;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders generated from sanitized attachment IDs.
        $thumbnail_refs = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT meta_value
             FROM {$wpdb->postmeta}
             WHERE meta_key = '_thumbnail_id'
               AND meta_value IN ({$meta_value_placeholders})",
            ...$meta_values
        ));

        foreach ((array) $thumbnail_refs as $attachment_id) {
            $attachment_id = (int) $attachment_id;
            if (isset($id_lookup[$attachment_id])) {
                $referenced[$attachment_id] = true;
            }
        }

        if ($gallery_referenced_ids === null) {
            $gallery_referenced_ids = [];
            $gallery_values = $wpdb->get_col($wpdb->prepare(
                "SELECT meta_value
                 FROM {$wpdb->postmeta}
                 WHERE meta_key = %s
                   AND meta_value != %s",
                '_product_image_gallery',
                ''
            ));

            foreach ((array) $gallery_values as $gallery_value) {
                $gallery_ids = preg_split('/\s*,\s*/', (string) $gallery_value, -1, PREG_SPLIT_NO_EMPTY);
                foreach ($gallery_ids as $gallery_id) {
                    $gallery_id = (int) $gallery_id;
                    if ($gallery_id > 0) {
                        $gallery_referenced_ids[$gallery_id] = true;
                    }
                }
            }
        }

        foreach ($attachment_ids as $attachment_id) {
            if (isset($gallery_referenced_ids[$attachment_id])) {
                $referenced[$attachment_id] = true;
            }
        }

        return $referenced;
    }

    private static function process_single_job(array $job, float $start_time, string $start_time_utc, int $timeout): void {
        $action = $job['action'] ?? '';
        $products = $job['products'] ?? [];
    
        if (!is_array($products) || empty($products)) {
            return;
        }
    
        // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- Intentional timeout adjustment for bulk processing
        @set_time_limit($timeout);
        // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- Intentional timeout adjustment for bulk processing
        @ini_set('max_execution_time', $timeout);
    
        $status = file_exists(self::$status_file)
            ? json_decode(file_get_contents(self::$status_file), true)
            : [];
    
        // Set base structure if not set
        if (!isset($status['plugin_version'])) {
            $status['plugin_version'] = self::$version;
        }
        if (!isset($status['start_time_utc'])) {
            $status['start_time_utc'] = $start_time_utc;
        }
        if (!isset($status['status'])) {
            $status['status'] = 'processing';
        }

        file_put_contents(self::$status_file, json_encode($status));
    
        foreach ($products as $product) {
            if (file_exists(self::$stop_file)) {
                wp_delete_file(self::$stop_file);
                $status['status'] = 'stopped';
                break;
            }
    
            if (microtime(true) - $start_time > $timeout) {
                $status['status'] = 'stopped due to timeout';
                break;
            }
    
            $result = FFLCockpit_Update_Processor::process_product($product, $action);
            $status[$action]['processed']++;
            $status['processed'] = ($status['processed'] ?? 0) + 1;
            $status['remaining'] = max(0, ($status['queued'] ?? 0) - $status['processed']);

            $error_info = [
                'id' => $product['id'] ?? null,
                'sku' => $product['sku'] ?? null,
                'upc' => $product['upc'] ?? null,
                'error' => $result['message'] ?? $result['error'] ?? 'Unknown error'
            ];
    
            if ($action === 'delete') {
                if ($result['status'] === 'deleted') {
                    $status[$action]['successful']++;
                    $status[$action]['remove_mapping'][] = ['id' => (int)$product['id']];
                } else {
                    $status[$action]['failed']++;
                    $status[$action]['errors'][] = $error_info;
                    $removable = in_array(($result['code'] ?? ''), ['woocommerce_rest_product_invalid_id', 'woocommerce_rest_invalid_id'], true)
                                || preg_match('/(product does not exist|invalid id|no post found|post does not exist)/i', $result['message'] ?? $result['error'] ?? '');

                    if ($removable) {
                        $status[$action]['remove_mapping'][] = ['id' => (int)$product['id']];
                    }
                }
            } elseif ($action === 'update' || $action === 'insert') {
                if ($result['status'] === 'success') {
                    $status[$action]['successful']++;
                } else {
                    $status[$action]['failed']++;
                    $status[$action]['errors'][] = $error_info;
                }
            }
    
            $status['end_time_utc'] = gmdate('Y-m-d\TH:i:s\Z');
            $status['elapsed_time'] = round(microtime(true) - $start_time);
            file_put_contents(self::$status_file, json_encode($status));
        }
    
        if (!isset($status['status']) || $status['status'] === 'processing') {
            $status['status'] = 'completed';
        }
    
        $status['end_time_utc'] = gmdate('Y-m-d\TH:i:s\Z');
        $status['elapsed_time'] = round(microtime(true) - $start_time);
        file_put_contents(self::$status_file, json_encode($status));
    }

    public static function handle_stop(WP_REST_Request $request) {
        self::init_file_paths();
        file_put_contents(self::$stop_file, 'stop');
        return new WP_REST_Response(['status' => 'stop signal issued'], 200);
    }

    public static function get_status(WP_REST_Request $request) {
        self::init_file_paths();

        $queue_meta = self::get_media_cleanup_queue_file_meta();

        if (!file_exists(self::$status_file)) {
            return new WP_REST_Response([
                'status' => 'No status available.',
                'plugin_version' => self::$version,
                'media_cleanup' => [
                    'deleted' => 0,
                    'skipped_in_use' => 0,
                    'failed' => 0,
                    'pending' => 0,
                ],
                'media_cleanup_queue' => $queue_meta,
            ], 200);
        }

        $contents = file_get_contents(self::$status_file);
        $status = !empty($contents) ? json_decode($contents, true) : null;

        if (!is_array($status) || empty($status)) {
            return new WP_REST_Response([
                'status' => 'No status available.',
                'plugin_version' => self::$version,
                'media_cleanup' => [
                    'deleted' => 0,
                    'skipped_in_use' => 0,
                    'failed' => 0,
                    'pending' => 0,
                ],
                'media_cleanup_queue' => $queue_meta,
            ], 200);
        }

        if (!isset($status['media_cleanup']) || !is_array($status['media_cleanup'])) {
            $status['media_cleanup'] = ['deleted' => 0, 'skipped_in_use' => 0, 'failed' => 0, 'pending' => 0];
        } elseif (!isset($status['media_cleanup']['pending'])) {
            $status['media_cleanup']['pending'] = 0;
        }
        $status['media_cleanup_queue'] = $queue_meta;

        return new WP_REST_Response($status, 200);
    }

    /**
     * Return on-disk metadata for the media cleanup queue file. Cheap —
     * stats only, never reads or parses the file contents.
     */
    private static function get_media_cleanup_queue_file_meta(): array {
        if (empty(self::$media_cleanup_file) || !file_exists(self::$media_cleanup_file)) {
            return [
                'exists' => false,
                'size_bytes' => 0,
                'modified_at' => null,
            ];
        }
        $size = @filesize(self::$media_cleanup_file);
        $mtime = @filemtime(self::$media_cleanup_file);
        return [
            'exists' => true,
            'size_bytes' => $size === false ? 0 : (int) $size,
            'modified_at' => $mtime ? gmdate('Y-m-d\TH:i:s\Z', $mtime) : null,
        ];
    }

    /**
     * Abandon the media cleanup queue. Deletes the queue file and any tmp
     * sibling, zeroes the counters in status.json. Queued attachments stay
     * in the media library (already disconnected from any active product)
     * until cleaned up by some other means.
     */
    public static function handle_media_cleanup_clear(WP_REST_Request $request) {
        self::init_file_paths();

        $entries_dropped = 0;
        $bytes_freed = 0;

        if (file_exists(self::$media_cleanup_file)) {
            $bytes_freed += (int) @filesize(self::$media_cleanup_file);
            $contents = @file_get_contents(self::$media_cleanup_file);
            if (!empty($contents)) {
                $decoded = json_decode($contents, true);
                if (is_array($decoded)) {
                    $entries_dropped = count($decoded);
                }
            }
            @wp_delete_file(self::$media_cleanup_file);
        }

        $tmp_file = self::$media_cleanup_file . '.tmp';
        if (file_exists($tmp_file)) {
            $bytes_freed += (int) @filesize($tmp_file);
            @wp_delete_file($tmp_file);
        }

        if (file_exists(self::$status_file)) {
            $status_contents = file_get_contents(self::$status_file);
            $status = !empty($status_contents) ? json_decode($status_contents, true) : null;
            if (is_array($status)) {
                $status['media_cleanup'] = [
                    'deleted' => 0,
                    'skipped_in_use' => 0,
                    'failed' => 0,
                    'pending' => 0,
                ];
                file_put_contents(self::$status_file, json_encode($status));
            }
        }

        return new WP_REST_Response([
            'cleared' => true,
            'entries_dropped' => $entries_dropped,
            'bytes_freed' => $bytes_freed,
        ], 200);
    }


    public static function handle_export(WP_REST_Request $request) {
        self::init_file_paths();
  try {
    $start_time = time();

    // --- runtime & safety switches ---
    ignore_user_abort(true);
    // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- Intentional timeout removal for bulk processing
    @set_time_limit(0);
    // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- Intentional memory increase for bulk processing
    @ini_set('memory_limit','2G');

    if (!self::fflcockpit_is_allowed(self::$fflckey, $request)) {
      return new WP_REST_Response(['error'=>'Not authorized'], 401);
    }

    // Optionally disable external object cache (Redis/Memcached) for this heavy, one-off read
    $had_ext_cache = function_exists('wp_using_ext_object_cache') ? wp_using_ext_object_cache() : false;
    if ($had_ext_cache) wp_using_ext_object_cache(false);

    // Defer counters & avoid cache pollution
    wp_suspend_cache_addition(true);
    wp_defer_term_counting(true);
    wp_defer_comment_counting(true);

    global $wpdb;

    // --- output target (single-pass gzip; no readback) ---
    $upload_dir = wp_upload_dir();
    $path = trailingslashit($upload_dir['basedir']) . 'fflcockpit/products-export.json.gz';
    if (file_exists($path)) wp_delete_file($path);
    $gz = gzopen($path, 'w5'); // level 5 = much faster, tiny size tradeoff
    if (!$gz) throw new Exception('Could not open gzip file for writing');

    // We output a flat array of product objects: [...]
    gzwrite($gz, '[');

    // --- precompute taxonomy lists (base + attribute taxonomies) ---
    $attr_taxonomies = wc_get_attribute_taxonomies() ?: [];
    $attribute_tax_names = [];
    $attribute_tax_ids   = [];
    $attribute_labels    = []; // Store human-readable labels
    foreach ($attr_taxonomies as $ta) {
      $tax = 'pa_' . sanitize_title($ta->attribute_name);
      $attribute_tax_names[]     = $tax;
      $attribute_tax_ids[$tax]   = (int) wc_attribute_taxonomy_id_by_name($tax);
      $attribute_labels[$tax]    = $ta->attribute_label; // Store the human-readable label
    }
    $base_tax = ['product_cat','product_tag','product_brand','product_shipping_class'];
    $all_tax  = array_merge($base_tax, $attribute_tax_names);

    // --- total count (optional; for metrics) ---
    $total_count = (int)$wpdb->get_var($wpdb->prepare("
      SELECT COUNT(*) FROM {$wpdb->posts}
      WHERE post_type = %s AND post_status IN (%s, %s, %s)
    ", 'product', 'publish', 'private', 'draft'));

    // --- derive permalink pattern from first product (for efficiency) ---
    $permalink_pattern = null;
    $sample_product_id = $wpdb->get_var($wpdb->prepare("
      SELECT ID FROM {$wpdb->posts}
      WHERE post_type = %s AND post_status IN (%s, %s, %s)
      ORDER BY ID ASC LIMIT 1
    ", 'product', 'publish', 'private', 'draft'));
    if ($sample_product_id) {
      $sample_permalink = get_permalink($sample_product_id);
      $sample_slug = get_post_field('post_name', $sample_product_id);
      if ($sample_permalink && $sample_slug) {
        // Extract the pattern by replacing the slug with a placeholder
        $permalink_pattern = str_replace($sample_slug, '{SLUG}', $sample_permalink);
      }
    }

            // Build WooCommerce internal meta keys list to mirror REST meta filtering
        $internal_meta_keys = [];
        if (class_exists('WC_Data_Store')) {
            try {
                $product_data_store = WC_Data_Store::load('product');
                if (is_object($product_data_store) && method_exists($product_data_store, 'get_internal_meta_keys')) {
                    $internal_meta_keys = (array) $product_data_store->get_internal_meta_keys();
                }
            } catch (\Throwable $__) {
                // Fallback handled below
            }
        }
            if (empty($internal_meta_keys)) {
                // Conservative fallback, based on WC_Product_Data_Store_CPT::$internal_meta_keys and common internal keys
                $internal_meta_keys = [
                    // Pricing
                    '_price','_regular_price','_sale_price','_sale_price_dates_from','_sale_price_dates_to',
                    // Inventory
                    '_manage_stock','_stock','_stock_status','_backorders','_low_stock_amount','_sold_individually',
                    // Tax
                    '_tax_status','_tax_class',
                    // Shipping/Dimensions
                    '_weight','_length','_width','_height','_shipping_class',
                    // Downloads
                    '_downloadable','_virtual','_downloadable_files','_download_limit','_download_expiry','_file_path','_file_paths',
                    // Relationships
                    '_upsell_ids','_crosssell_ids','_children','_product_image_gallery','_thumbnail_id',
                    // Attributes/Defaults
                    '_product_attributes','_default_attributes','_variation_description','_product_version',
                    // Reviews/Ratings
                    '_wc_average_rating','_wc_review_count','_wc_rating_count',
                    // SKU
                    '_sku',
                    // Core WP internals often excluded by WC
                    '_edit_lock','_edit_last',
                    // Non-underscored internal used by WC
                    'total_sales',
                ];
            }
            // Augment with underscore-prefixed product property keys (mirrors WC_Data_Store_WP behavior)
            if (class_exists('WC_Product_Simple')) {
                try {
                    $tmp_product = new WC_Product_Simple();
                    if (method_exists($tmp_product, 'get_data')) {
                        $prop_keys = array_keys((array) $tmp_product->get_data());
                        $prefixed  = array_map(function($k){ return '_' . $k; }, $prop_keys);
                        $internal_meta_keys = array_values(array_unique(array_merge($internal_meta_keys, $prefixed)));
                    }
                } catch (\Throwable $__) {}
            }

            // Ensure known non-underscored internal key is excluded (matches Woo behavior)
        if (!in_array('total_sales', $internal_meta_keys, true)) {
            $internal_meta_keys[] = 'total_sales';
        }

    // --- keyset pagination (fast on big tables) ---
    $batch   = 500;   // adjust to taste
    $last_id = 0;
    $count   = 0;
    $first   = true;

    while (true) {
      $ids = $wpdb->get_col($wpdb->prepare("
        SELECT ID
        FROM {$wpdb->posts}
        WHERE post_type = %s AND post_status IN (%s, %s, %s) AND ID > %d
        ORDER BY ID ASC
        LIMIT %d
      ", 'product', 'publish', 'private', 'draft', $last_id, $batch));

      if (!$ids) break;
      $last_id = end($ids);
      
      // Build IN clause with proper placeholders for PHPCS compliance
      $post_placeholders_str = implode(',', array_fill(0, count($ids), '%d'));

      // --- posts (core fields) ---
      // Build complete SQL string to avoid PHPCS placeholder variable detection
      // Safe: $post_placeholders_str contains only comma-separated '%d' placeholders generated via array_fill.
      // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
      $posts = $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_title, post_excerpt, post_content FROM {$wpdb->posts} WHERE ID IN ({$post_placeholders_str})", ...$ids ), OBJECT_K );


      // --- postmeta: one pass; build BOTH quick lookup and meta_data rows ---
      // Safe: $post_placeholders_str contains only comma-separated '%d' placeholders generated via array_fill.
      // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
      $metas = $wpdb->get_results( $wpdb->prepare( "SELECT meta_id, post_id, meta_key, meta_value FROM {$wpdb->postmeta} WHERE post_id IN ({$post_placeholders_str})", ...$ids ), ARRAY_A );

      $meta_by_post       = []; // [pid][key] = value (for internal use, with underscore stripped)
      $meta_rows_by_post  = []; // [pid] = [{id,key,value}, ...] (for API output, WooCommerce format)
      
      foreach ($metas as $m) {
        $pid2 = (int)$m['post_id'];
        $original_key = $m['meta_key'];
        $key  = ltrim($original_key, '_'); // stripped version for internal lookups
        $val  = maybe_unserialize($m['meta_value']);
        
        // For internal lookups (prices, SKU, etc.)
        $meta_by_post[$pid2][$key] = $val;
        
                // WooCommerce-equivalent filtering for meta_data exposure:
                // - Exclude internal product meta keys (as defined by WC product data store)
                // - Exclude any keys starting with 'wp_'
                $is_internal = in_array($original_key, $internal_meta_keys, true);
                $has_wp_prefix = (strpos($original_key, 'wp_') === 0);
                if ($is_internal || $has_wp_prefix) {
                    continue;
                }

                $meta_rows_by_post[$pid2][] = [
                    'id'    => (int) $m['meta_id'],
                    'key'   => $original_key,
                    'value' => $val,
                ];
      }

      // --- terms: one query for ALL taxonomies needed (brand/cat/tag/shipping + attributes) ---
      // Build placeholders for taxonomies and product IDs
      $tax_placeholders_str = implode(',', array_fill(0, count($all_tax), '%s'));
      $id_placeholders_str = implode(',', array_fill(0, count($ids), '%d'));
      
      // Build complete SQL string to avoid PHPCS placeholder variable detection
      $prepare_args = array_merge( $ids, $all_tax ); 
      // Safe: Placeholders are generated programmatically via array_fill/implode.
      // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
      $term_rows = $wpdb->get_results( $wpdb->prepare( "SELECT tr.object_id AS pid, tt.taxonomy, t.term_id, t.name, t.slug FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id = tt.term_id WHERE tr.object_id IN ({$id_placeholders_str}) AND tt.taxonomy IN ({$tax_placeholders_str})", ...$prepare_args ), ARRAY_A );





      $terms_by_post_tax = []; // [pid][taxonomy] = [[id,name,slug], ...]
      foreach ($term_rows as $r) {
        $pid2 = (int)$r['pid'];
        $tx   = $r['taxonomy'];
        $terms_by_post_tax[$pid2][$tx][] = [
          'id'   => (int)$r['term_id'],
          'name' => $r['name'],
          'slug' => $r['slug'],
        ];
      }

      // --- images: bulk thumbnail + gallery mapping; then bulk pull attachment posts & ALT ---
      $prepare_args = array_merge( $ids, ['_thumbnail_id', '_product_image_gallery'] ); 
      // Safe: $post_placeholders_str contains only comma-separated '%d' placeholders generated via array_fill.
      // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
      $img_meta = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, meta_key, meta_value FROM {$wpdb->postmeta} WHERE post_id IN ({$post_placeholders_str}) AND meta_key IN (%s,%s)", ...$prepare_args ), ARRAY_A );

      $thumb  = [];    // [pid] => att_id
      $gallery = [];   // [pid] => [att_id,...]
      foreach ($img_meta as $r) {
        if ($r['meta_key'] === '_thumbnail_id') {
          $thumb[(int)$r['post_id']] = (int)$r['meta_value'];
        } else {
          $gallery[(int)$r['post_id']] = array_filter(array_map('intval', explode(',', (string)$r['meta_value'])));
        }
      }

      $all_att = array_unique(array_filter(array_merge(
        array_values($thumb),
        ...(!empty($gallery) ? array_values($gallery) : [[]])
      )));

      $att_map = []; // [att_id] => (ID, post_title, guid, post_date_gmt, post_modified_gmt)
      $alts    = []; // [att_id] => alt text
      if ($all_att) {
        $att_placeholders_str = implode(',', array_fill(0, count($all_att), '%d'));
        
        // Build complete SQL strings to avoid PHPCS placeholder variable detection
        // Safe: $att_placeholders_str contains only comma-separated '%d' placeholders generated via array_fill.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
        $att_map = $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_title, guid, post_date_gmt, post_modified_gmt FROM {$wpdb->posts} WHERE ID IN ({$att_placeholders_str})", ...$all_att ), OBJECT_K );

        $prepare_args = array_merge( $all_att, ['_wp_attachment_image_alt'] ); 
        // Safe: $att_placeholders_str contains only comma-separated '%d' placeholders generated via array_fill.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        $alt_rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, meta_value FROM {$wpdb->postmeta} WHERE post_id IN ({$att_placeholders_str}) AND meta_key = %s", ...$prepare_args ), ARRAY_A );


        foreach ($alt_rows as $ar) {
          $alts[(int)$ar['post_id']] = $ar['meta_value'];
        }
      }

      // --- build rows & stream ---
      foreach ($ids as $pid) {
        $p = $posts[$pid] ?? null; if (!$p) continue;

        $meta        = $meta_by_post[$pid]      ?? [];
        $meta_arr    = $meta_rows_by_post[$pid] ?? [];

        // terms
        $brands      = $terms_by_post_tax[$pid]['product_brand']        ?? [];
        $categories  = $terms_by_post_tax[$pid]['product_cat']          ?? [];
        $tags        = $terms_by_post_tax[$pid]['product_tag']          ?? [];
        $ship_class  = $terms_by_post_tax[$pid]['product_shipping_class'][0]['slug'] ?? '';

        // attributes from attribute taxonomies
        $attrs = [];
        $pos   = 1;
        foreach ($attribute_tax_names as $tax) {
          if (!empty($terms_by_post_tax[$pid][$tax])) {
            $vals = $terms_by_post_tax[$pid][$tax];
            $attrs[] = [
              'id'        => $attribute_tax_ids[$tax] ?? 0,
              'name'      => $attribute_labels[$tax] ?? $tax, // Use human-readable label instead of slug
              'slug'      => $tax,
              'position'  => $pos++,
              'visible'   => true,
              'variation' => false,
              'options'   => array_map(fn($t) => $t['name'], $vals),
            ];
          }
        }

        // images
        $imgs = [];
        if (!empty($thumb[$pid]) && isset($att_map[$thumb[$pid]])) {
          $ap = $att_map[$thumb[$pid]];
          $imgs[] = [
            'id'                => (int)$ap->ID,
            'date_created'      => $ap->post_date_gmt      ? gmdate('Y-m-d\TH:i:s', strtotime($ap->post_date_gmt))      : null,
            'date_created_gmt'  => $ap->post_date_gmt      ? gmdate('Y-m-d\TH:i:s', strtotime($ap->post_date_gmt))      : null,
            'date_modified'     => $ap->post_modified_gmt  ? gmdate('Y-m-d\TH:i:s', strtotime($ap->post_modified_gmt))  : null,
            'date_modified_gmt' => $ap->post_modified_gmt  ? gmdate('Y-m-d\TH:i:s', strtotime($ap->post_modified_gmt))  : null,
            'src'               => $ap->guid,
            'name'              => $ap->post_title,
            'alt'               => $alts[$ap->ID] ?? '',
          ];
        }
        if (!empty($gallery[$pid])) {
          foreach ($gallery[$pid] as $aid) {
            if (isset($att_map[$aid])) {
              $ap = $att_map[$aid];
              $imgs[] = [
                'id'                => (int)$ap->ID,
                'date_created'      => $ap->post_date_gmt      ? gmdate('Y-m-d\TH:i:s', strtotime($ap->post_date_gmt))      : null,
                'date_created_gmt'  => $ap->post_date_gmt      ? gmdate('Y-m-d\TH:i:s', strtotime($ap->post_date_gmt))      : null,
                'date_modified'     => $ap->post_modified_gmt  ? gmdate('Y-m-d\TH:i:s', strtotime($ap->post_modified_gmt))  : null,
                'date_modified_gmt' => $ap->post_modified_gmt  ? gmdate('Y-m-d\TH:i:s', strtotime($ap->post_modified_gmt))  : null,
                'src'               => $ap->guid,
                'name'              => $ap->post_title,
                'alt'               => $alts[$ap->ID] ?? '',
              ];
            }
          }
        }

        // numeric helpers (avoid notices)
        $num_or_null = static function($v) {
          return (isset($v) && $v !== '' && is_numeric($v)) ? 0 + $v : null;
        };

        // build row (mirrors your last shape)
        $row = [
          'id'                 => $pid,
          'upc'                => $meta['upc'] ?? '',
          'global_unique_id'   => $meta['global_unique_id'] ?? '',
          'stock_quantity'     => isset($meta['stock']) ? (int)$meta['stock'] : null, // _stock
          'regular_price'      => $num_or_null($meta['regular_price'] ?? null),
          'sale_price'         => $num_or_null($meta['sale_price'] ?? null),
          'price'              => $num_or_null($meta['price'] ?? null), // The computed effective price
          'sku'                => $meta['sku'] ?? '',
          'brands'             => $brands,
          'attributes'         => $attrs,
          'meta_data'          => $meta_arr,
          'name'               => $p->post_title,
          'short_description'  => $p->post_excerpt,
          'description'        => $p->post_content,
          'slug'               => get_post_field('post_name', $pid),
          'permalink'          => $permalink_pattern ? str_replace('{SLUG}', get_post_field('post_name', $pid), $permalink_pattern) : get_permalink($pid),
          'distid'             => $meta['distid'] ?? null,
          'map_price'          => (isset($meta['map_price']) && $meta['map_price'] !== 'None' && is_numeric($meta['map_price'])) ? 0 + $meta['map_price'] : null,
          'unit_price'         => $num_or_null($meta['unit_price'] ?? null),
          'ffl_req'            => (($meta['firearm_product'] ?? '') === 'yes') ? 1 : 0,
          'drop_ship_flg'      => (in_array($meta['drop_ship_flg'] ?? '', ['1','1.0']) ? 1 : 0),
          'automated_listing'  => (($meta['automated_listing'] ?? '') === 'True'),
          'tags'               => $tags,
          'categories'         => $categories,
          'images'             => $imgs,
          'shipping_class'     => $ship_class,
          'manage_stock'       => (($meta['manage_stock'] ?? '') === 'yes'),
        ];

        if (!$first) gzwrite($gz, ',');
        gzwrite($gz, json_encode($row, JSON_UNESCAPED_SLASHES));
        $first = false;
        $count++;
      }

      // batch housekeeping (cheap and infrequent)
      wp_cache_flush();
      gc_collect_cycles();
      // usleep(500); // enable only if DB load spikes
    }

    gzwrite($gz, ']');
    gzclose($gz);

    // restore switches
    wp_defer_term_counting(false);
    wp_defer_comment_counting(false);
    wp_suspend_cache_addition(false);
    if ($had_ext_cache) wp_using_ext_object_cache(true);

    // Generate a temporary download token (valid for 15 minutes)
    $download_token = wp_generate_password(32, false);
    set_transient('ffl_download_token_' . $download_token, time(), 900); // 15 minutes

    return new WP_REST_Response([
      'status'         => 'completed',
      'count'          => $count,
      'download_url'   => rest_url('fflcockpit/v1/download-export?token=' . $download_token),
      'execution_time' => time() - $start_time,
      'memory_peak'    => memory_get_peak_usage(true),
      'file_size'      => @filesize($path),
      'plugin_version' => self::$version,
      'total_products' => $total_count,
    ], 200);

  } catch (\Throwable $e) {
    if (isset($gz) && is_resource($gz)) { @gzclose($gz); }
    if (isset($path) && file_exists($path)) { wp_delete_file($path); }

    // try to restore switches if we threw early
    try {
      wp_defer_term_counting(false);
      wp_defer_comment_counting(false);
      wp_suspend_cache_addition(false);
      if (isset($had_ext_cache) && $had_ext_cache) wp_using_ext_object_cache(true);
    } catch (\Throwable $__) {}

    g_ffl_debug_log('FFL Export Error: '.$e->getMessage().' in '.$e->getFile().':'.$e->getLine());

    return new WP_REST_Response([
      'error'         => 'Export failed',
      'message'       => $e->getMessage(),
      'file'          => $e->getFile(),
      'line'          => $e->getLine(),
      'memory_usage'  => memory_get_peak_usage(true),
      'time_elapsed'  => time() - ($start_time ?? time()),
    ], 500);
  }
}

    
public static function handle_download_export(WP_REST_Request $request) {
    self::init_file_paths();
    try {
        // Validate token (your export endpoint generates this)
        $token = (string) $request->get_param('token');
        $ok = !empty($token) && get_transient('ffl_download_token_' . $token) !== false;

        // Fall back to API-key auth (X-GFFL-Token) when the short-lived
        // download token has expired or was evicted (e.g. external object
        // cache flush). The route's permission_callback already trusts this
        // same header, so the handler must honor it too — otherwise a fully
        // authenticated client gets a spurious 401 "Invalid or expired token".
        if (!$ok) {
            $stored_api_key = get_option('g_ffl_cockpit_key');
            $request_token  = $request->get_header('X-GFFL-Token');
            $ok = !empty($stored_api_key) && !empty($request_token)
                && hash_equals($stored_api_key, $request_token);
        }

        if (!$ok) {
            return new WP_REST_Response(['error' => 'Invalid or expired token'], 401);
        }

        // Optional: make it single-use
        // delete_transient('ffl_download_token_' . $token);

        $upload_dir = wp_upload_dir();
        $path = trailingslashit($upload_dir['basedir']) . 'fflcockpit/products-export.json.gz';

        if (!file_exists($path)) {
            return new WP_REST_Response(['error' => 'Export file not found'], 404);
        }

        $filesize = filesize($path);
        if ($filesize === false || $filesize < 20) {
            return new WP_REST_Response(['error' => 'Export file appears invalid'], 500);
        }

        ignore_user_abort(true);
        @set_time_limit(0);

        // Prevent any output compression from touching the gzip bytes
        @ini_set('zlib.output_compression', 'Off');
        @ini_set('output_buffering', 'Off');
        @ini_set('implicit_flush', '1');

        // Clear ALL output buffers (WP/plugins often stack buffers)
        while (ob_get_level() > 0) {
            @ob_end_clean();
        }

        nocache_headers();

        header('Content-Description: File Transfer');
        header('Content-Type: application/gzip');
        header('Content-Disposition: attachment; filename="fflcockpit-products-export.json.gz"');
        header('Content-Transfer-Encoding: binary');
        header('X-Content-Type-Options: nosniff');
        header('Connection: close');
        header('Content-Length: ' . $filesize);

        $fh = fopen($path, 'rb');
        if (!$fh) {
            return new WP_REST_Response(['error' => 'Could not open export file'], 500);
        }

        // Stream in chunks (more reliable than readfile)
        $chunk = 1024 * 1024; // 1MB
        while (!feof($fh)) {
            $buf = fread($fh, $chunk);
            if ($buf === false) break;
            echo $buf;
            flush();
        }
        fclose($fh);

        exit;

    } catch (\Throwable $e) {
        return new WP_REST_Response([
            'error'   => 'Download failed',
            'message' => $e->getMessage(),
            'file'    => $e->getFile(),
            'line'    => $e->getLine(),
        ], 500);
    }
}





    
    

    public static function handle_remote_file_exists(WP_REST_Request $request) {
        $url = $request->get_param('url');
        if (!$url) {
            return new WP_REST_Response(['exists' => false, 'error' => 'No URL provided'], 400);
        }
        
        // Use get_headers to check if the file exists
        $headers = @get_headers($url, 1);
        if ($headers && isset($headers[0]) && (strpos($headers[0], '200') !== false)) {
            return new WP_REST_Response(['exists' => true], 200);
        }
        
        return new WP_REST_Response(['exists' => false], 200);
    }

    public static function handle_cleanup_orphaned_media(WP_REST_Request $request) {
        try {
            $timeout = 900;
            // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- Intentional timeout adjustment for media cleanup
            @set_time_limit($timeout);
            // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- Intentional timeout adjustment for media cleanup
            @ini_set('max_execution_time', $timeout);
            
            $dry_run = $request->get_param('dry_run') ?? false;
            $batch_size = $request->get_param('batch_size') ?? 100;
            
            $start_time = microtime(true);
            $orphaned_attachments = [];
            $deleted_count = 0;
            $checked_count = 0;
            
            // Get currently active product attachment IDs for comparison
            global $wpdb;
            $active_product_attachment_ids = $wpdb->get_col($wpdb->prepare("
                SELECT DISTINCT pm.meta_value
                FROM {$wpdb->postmeta} pm
                INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                WHERE p.post_type = %s
                AND p.post_status NOT IN (%s, %s)
                AND pm.meta_key IN (%s, %s)
                AND pm.meta_value != %s
            ", 'product', 'trash', 'auto-draft', '_thumbnail_id', '_product_image_gallery', ''));
            
            // Parse gallery IDs and flatten the array for active products
            $active_image_ids = [];
            foreach ($active_product_attachment_ids as $value) {
                if (strpos($value, ',') !== false) {
                    $gallery_ids = explode(',', $value);
                    $active_image_ids = array_merge($active_image_ids, $gallery_ids);
                } else {
                    $active_image_ids[] = $value;
                }
            }
            $active_image_ids = array_unique(array_filter($active_image_ids));
            
            // Get all attachments in the system
            $offset = 0;
            $limit = $batch_size;
            
            do {
                $attachments = get_posts([
                    'post_type' => 'attachment',
                    'post_status' => 'inherit',
                    'posts_per_page' => $limit,
                    'offset' => $offset,
                    'orderby' => 'ID',
                    'order' => 'ASC'
                ]);
                
                foreach ($attachments as $attachment) {
                    $checked_count++;
                    
                    // Skip if this attachment is currently used by an active product
                    if (in_array($attachment->ID, $active_image_ids)) {
                        continue;
                    }
                    
                    // Check if this appears to be a product-related image that's now orphaned
                    $is_product_orphan = self::is_product_related_orphan($attachment->ID);
                    
                    if ($is_product_orphan) {
                        $orphaned_attachments[] = [
                            'id' => $attachment->ID,
                            'title' => $attachment->post_title,
                            'file' => get_attached_file($attachment->ID),
                            'url' => wp_get_attachment_url($attachment->ID)
                        ];
                        
                        // If not a dry run, delete the attachment
                        if (!$dry_run) {
                            $deleted = wp_delete_attachment($attachment->ID, true);
                            if ($deleted) {
                                $deleted_count++;
                            }
                        }
                    }
                    
                    // Check timeout
                    if (microtime(true) - $start_time > ($timeout - 30)) {
                        break 2; // Break out of both loops
                    }
                }
                
                // Process in batches to avoid memory issues
                wp_cache_flush();
                gc_collect_cycles();
                
                $offset += $limit;
                
            } while (count($attachments) === $limit && (microtime(true) - $start_time < ($timeout - 30)));
            
            $elapsed_time = round(microtime(true) - $start_time, 2);
            
            return new WP_REST_Response([
                'status' => 'completed',
                'dry_run' => $dry_run,
                'checked_count' => $checked_count,
                'orphaned_count' => count($orphaned_attachments),
                'deleted_count' => $deleted_count,
                'elapsed_time' => $elapsed_time,
                'orphaned_files' => $dry_run ? $orphaned_attachments : array_slice($orphaned_attachments, 0, 10) // Limit response size
            ], 200);
            
        } catch (Exception $e) {
            return new WP_REST_Response([
                'error' => 'Cleanup failed',
                'message' => $e->getMessage(),
                'file' => $e->getFile(),
                'line' => $e->getLine()
            ], 500);
        }
    }

    private static function is_product_related_orphan($attachment_id) {
        // ENHANCED APPROACH: More aggressive detection for truly orphaned product images
        // Handles cases where products were deleted along with their meta data
        
        global $wpdb;
        
        // SAFETY CHECK FIRST: Never delete if currently used in any content
        $used_in_content = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->posts} 
            WHERE (post_content LIKE %s OR post_content LIKE %s OR post_content LIKE %s)
            AND post_status IN ('publish', 'private', 'draft')
            AND post_type IN ('post', 'page', 'product')
        ", '%wp-image-' . $attachment_id . '%', '%attachment_' . $attachment_id . '%', '%' . wp_get_attachment_url($attachment_id) . '%'));
        
        if ($used_in_content > 0) {
            return false; // Still being used, don't delete
        }
        
        // Check if it's a critical file (logos, favicons, etc.)
        $file_path = get_attached_file($attachment_id);
        $title = get_the_title($attachment_id);
        $filename = basename($file_path ?: '');
        
        // Never delete these critical files
        $critical_patterns = [
            '/logo/i', '/favicon/i', '/icon/i', '/banner/i', '/header/i', '/footer/i',
            '/background/i', '/bg[-_]/i', '/theme/i', '/design/i', '/layout/i',
            '/hero/i', '/slider/i', '/carousel/i', '/gallery/i'
        ];
        
        foreach ($critical_patterns as $pattern) {
            if (preg_match($pattern, $title) || preg_match($pattern, $filename) || 
                ($file_path && preg_match($pattern, $file_path))) {
                return false; // Don't delete critical files
            }
        }
        
        // PRIMARY EVIDENCE: Check if it was EVER referenced in product meta (including deleted products)
        $was_ever_product_thumbnail = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->postmeta} pm
            WHERE pm.meta_key = '_thumbnail_id'
            AND pm.meta_value = %s
        ", $attachment_id));
        
        if ($was_ever_product_thumbnail > 0) {
            return true; // Definitely was a product thumbnail at some point
        }
        
        // Check if it was ever in a product gallery
        $gallery_references = $wpdb->get_results($wpdb->prepare("
            SELECT pm.meta_value 
            FROM {$wpdb->postmeta} pm
            WHERE pm.meta_key = '_product_image_gallery'
            AND pm.meta_value LIKE %s
        ", '%' . $attachment_id . '%'));
        
        foreach ($gallery_references as $reference) {
            $gallery_ids = explode(',', $reference->meta_value);
            if (in_array((string)$attachment_id, $gallery_ids)) {
                return true; // Was definitely in a product gallery
            }
        }
        
        // SECONDARY EVIDENCE: Check if it was directly attached to any product post (including deleted ones)
        $direct_product_attachment = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->posts} p
            WHERE p.post_parent IN (
                SELECT ID FROM {$wpdb->posts} 
                WHERE post_type = 'product'
            )
            AND p.ID = %d
        ", $attachment_id));
        
        if ($direct_product_attachment > 0) {
            return true; // Was directly attached to a product
        }
        
        // TERTIARY EVIDENCE: Check for WooCommerce-specific upload paths
        if ($file_path && preg_match('/\/woocommerce_uploads\/|\/product[\-_]images\/|\/wc[\-_]uploads\//', $file_path)) {
            return true; // In a WooCommerce-specific directory
        }
        
        // HISTORICAL EVIDENCE: Check if the attachment has metadata that suggests it was product-related
        $attachment_meta = get_post_meta($attachment_id);
        
        // Look for WooCommerce-specific meta keys that might have been set
        $wc_meta_keys = ['_wc_attachment_source', '_product_id', '_woocommerce_exclude_image'];
        foreach ($wc_meta_keys as $meta_key) {
            if (isset($attachment_meta[$meta_key])) {
                return true; // Has WooCommerce-specific metadata
            }
        }
        
        // NEW ENHANCED DETECTION: For older images with no database history
        $upload_date = get_the_date('U', $attachment_id);
        $days_old = (time() - $upload_date) / (24 * 60 * 60);
        
        // Add minimum age check - don't delete very recent uploads unless we have strong evidence
        if ($days_old < 1) {
            return false; // Don't delete images uploaded in the last day without strong evidence
        }
        
        // For images older than 1 day, use more aggressive pattern matching
        if ($days_old > 1) {
            // Strong product-like filename patterns
            $strong_product_patterns = [
                '/^[A-Z0-9]{3,}-[A-Z0-9]{2,}/',        // Clear SKU patterns: ABC-123, DEF-456
                '/^\d{8,14}$/',                         // Clear UPC patterns: 123456789012
                '/^[A-Z]{2,5}\d{3,}[A-Z]{0,3}$/',   // Manufacturer codes: SIG365, GLOCK19G5
                '/^[A-Z0-9]{6,}$/',                     // Generic product codes: ABCD1234
                '/product[\-_]?\d+/i',                  // product123, product-456
                '/sku[\-_]?[a-z0-9]{3,}/i',            // sku-abc123
                '/item[\-_]?[a-z0-9]{3,}/i',           // item-abc123
                '/model[\-_]?[a-z0-9]{3,}/i',          // model-abc123
            ];
            
            $strong_filename_match = false;
            foreach ($strong_product_patterns as $pattern) {
                if (preg_match($pattern, $title) || preg_match($pattern, $filename)) {
                    $strong_filename_match = true;
                    break;
                }
            }
            
            if ($strong_filename_match) {
                return true; // Strong filename pattern indicates product image
            }
            
            // Check if image has no meaningful metadata and looks generic
            $alt_text = get_post_meta($attachment_id, '_wp_attachment_image_alt', true);
            $description = wp_get_attachment_caption($attachment_id);
            
            $has_meaningful_metadata = !empty($alt_text) || !empty($description) || 
                                      (!empty($title) && !preg_match('/^(image|img|photo|picture|untitled)/i', $title));
            
            if (!$has_meaningful_metadata && $days_old > 7) {
                // Generic looking image with no metadata - check if it looks like a bulk upload
                
                // Look for common camera/bulk upload patterns
                $bulk_upload_patterns = [
                    '/^IMG[\-_]?\d{3,}/i',              // IMG_1234, IMG-5678
                    '/^DSC[\-_]?\d{3,}/i',             // DSC1234, DSC_5678
                    '/^\d{8,}[\-_][a-z0-9]/i',         // 20231201_abc
                    '/^[a-z0-9]+[-_]\d{4,}/i',         // abc_2023, def-1234
                    '/^[a-f0-9]{8,}$/i',               // Hash-like filenames
                    '/^\d{4}-\d{2}-\d{2}/i',           // Date-based names
                ];
                
                foreach ($bulk_upload_patterns as $pattern) {
                    if (preg_match($pattern, $filename)) {
                        // This looks like a bulk uploaded image with no clear purpose
                        // Check if there's any evidence it's NOT a product image
                        
                        // Check if it's used in menus, widgets, or customizer
                        $used_in_navigation = $wpdb->get_var($wpdb->prepare("
                            SELECT COUNT(*) 
                            FROM {$wpdb->postmeta} pm
                            WHERE pm.meta_key IN ('_menu_item_object_id', '_thumbnail_id')
                            AND pm.meta_value = %s
                            AND pm.post_id IN (
                                SELECT ID FROM {$wpdb->posts} 
                                WHERE post_type IN ('nav_menu_item', 'page', 'post')
                                AND post_status = 'publish'
                            )
                        ", $attachment_id));
                        
                        if ($used_in_navigation == 0) {
                            return true; // Looks like orphaned bulk upload
                        }
                        break;
                    }
                }
            }
        }
        
        // Check if the attachment was uploaded around the time products were created/updated
        // This is circumstantial evidence but can be useful for bulk imports
        $upload_date_str = get_the_date('Y-m-d', $attachment_id);
        if ($upload_date_str && $days_old > 1) {
            // Look for products created within 7 days of this image upload
            $nearby_products = $wpdb->get_var($wpdb->prepare("
                SELECT COUNT(*) 
                FROM {$wpdb->posts} 
                WHERE post_type = 'product'
                AND ABS(DATEDIFF(post_date, %s)) <= 7
            ", $upload_date_str));
            
            // Only consider this evidence if there were products created nearby AND the filename suggests product use
            if ($nearby_products > 0) {
                $product_filename_patterns = [
                    '/^[A-Z0-9]{3,}-[A-Z0-9]{2,}/',    // Clear SKU patterns: ABC-123
                    '/^\d{8,14}$/',                     // Clear UPC patterns: 123456789012
                    '/^[A-Z]{2,5}\d{3,}[A-Z]{0,2}$/',  // Manufacturer codes: SIG365, GLOCK19G5
                    '/product[\-_]?\d+/i',              // product123, product-456
                    '/sku[\-_]?[a-z0-9]{3,}/i',        // sku-abc123
                ];
                
                foreach ($product_filename_patterns as $pattern) {
                    if (preg_match($pattern, $title) || preg_match($pattern, $filename)) {
                        return true; // Strong filename pattern + timing evidence
                    }
                }
            }
        }
        
        // If we get here, we have no clear evidence this was ever a product image
        return false;
    }

    private static function is_attachment_orphaned($attachment_id) {
        global $wpdb;
        
        // Check if attachment is used as featured image for any product
        $featured_image_usage = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            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 = %d 
            AND p.post_type = 'product'
            AND p.post_status != 'trash'
        ", $attachment_id));
        
        if ($featured_image_usage > 0) {
            return false;
        }
        
        // Check if attachment is in any product gallery
        $gallery_usage = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
            WHERE pm.meta_key = '_product_image_gallery' 
            AND pm.meta_value LIKE %s 
            AND p.post_type = 'product'
            AND p.post_status != 'trash'
        ", '%' . $attachment_id . '%'));
        
        if ($gallery_usage > 0) {
            // Double-check that it's actually in the gallery (not just a substring match)
            $gallery_posts = $wpdb->get_results($wpdb->prepare("
                SELECT pm.meta_value 
                FROM {$wpdb->postmeta} pm
                INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                WHERE pm.meta_key = '_product_image_gallery' 
                AND pm.meta_value LIKE %s 
                AND p.post_type = 'product'
                AND p.post_status != 'trash'
            ", '%' . $attachment_id . '%'));
            
            foreach ($gallery_posts as $post) {
                $gallery_ids = explode(',', $post->meta_value);
                if (in_array($attachment_id, $gallery_ids)) {
                    return false;
                }
            }
        }
        
        // Check if attachment is directly attached to any product
        $direct_attachment = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->posts} 
            WHERE post_parent IN (
                SELECT ID FROM {$wpdb->posts} 
                WHERE post_type = 'product' 
                AND post_status != 'trash'
            )
            AND ID = %d
        ", $attachment_id));
        
        if ($direct_attachment > 0) {
            return false;
        }
        
        // If we get here, the attachment appears to be orphaned
        return true;
    }

    private static function get_taxonomy_terms($product_id, $taxonomy) {
        $terms = get_the_terms($product_id, $taxonomy);
        if (!$terms || is_wp_error($terms)) return [];
        
        return array_map(function($term) {
            return [
                'id' => $term->term_id,
                'name' => $term->name,
                'slug' => $term->slug
            ];
        }, $terms);
    }



    private static function reset_status_file() {
        $default_status = [
            'plugin_version' => self::$version,
            'start_time_utc' => null,
            'end_time_utc' => null,
            'elapsed_time' => 0,
            'status' => 'idle',
            'job_count' => 0,
            'queued' => 0,
            'processed' => 0,
            'remaining' => 0,
            'insert' => [
                'queued' => 0,
                'processed' => 0,
                'successful' => 0,
                'failed' => 0,
                'errors' => []
            ],
            'update' => [
                'queued' => 0,
                'processed' => 0,
                'successful' => 0,
                'failed' => 0,
                'errors' => []
            ],
            'delete' => [
                'queued' => 0,
                'processed' => 0,
                'successful' => 0,
                'failed' => 0,
                'errors' => [],
                'remove_mapping' => []
            ]
        ];
    
        file_put_contents(self::$status_file, json_encode($default_status, JSON_PRETTY_PRINT));
    }

    public static function download_permission_callback(WP_REST_Request $request) {
        // Check for temporary download token (transient-based)
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- REST API endpoint with token-based authentication
        $token = $request->get_param('token') ?: (isset($_GET['token']) ? sanitize_text_field(wp_unslash($_GET['token'])) : '');
        if (!empty($token)) {
            $stored_time = get_transient('ffl_download_token_' . $token);
            if ($stored_time !== false) {
                return true;
            }
        }

        // Allow if X-GFFL-Token header matches the stored API key (no IP check needed for downloads)
        $stored_api_key = get_option('g_ffl_cockpit_key');
        $request_token = $request->get_header('X-GFFL-Token');
        if (!empty($stored_api_key) && !empty($request_token) && hash_equals($stored_api_key, $request_token)) {
            return true;
        }

        // Allow full authenticated access (IP + token)
        if (self::fflcockpit_is_allowed(self::$fflckey, $request)) {
            return true;
        }

        // Allow unauthenticated access from localhost/same server
        $client_ip = isset($_SERVER['REMOTE_ADDR']) ? sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR'])) : '';
        $server_ip = isset($_SERVER['SERVER_ADDR']) ? sanitize_text_field(wp_unslash($_SERVER['SERVER_ADDR'])) : '';

        if (in_array($client_ip, ['127.0.0.1', '::1', 'localhost', $server_ip])) {
            return true;
        }

        return false;
    }

    public static function handle_server_status(WP_REST_Request $request) {
        // Initialize with basic safe defaults
        $response_data = [
            'plugin' => [
                'name' => 'FFL Cockpit',
                'version' => self::$version,
            ],
            'timestamp' => gmdate('Y-m-d\TH:i:s\Z'),
        ];
        
        // Helper function to format bytes
        $format_bytes = function($size) {
            try {
                if ($size === 0) return '0 B';
                $base = log($size, 1024);
                $suffixes = ['B', 'KB', 'MB', 'GB', 'TB'];
                return round(pow(1024, $base - floor($base)), 2) . ' ' . $suffixes[floor($base)];
            } catch (Exception $e) {
                return $size . ' bytes';
            } catch (Throwable $e) {
                return $size . ' bytes';
            }
        };

        // Helper function to safely get ini value
        $get_ini_value = function($key) {
            try {
                $value = @ini_get($key);
                return $value !== false ? $value : 'Not set';
            } catch (Exception $e) {
                return 'Error';
            } catch (Throwable $e) {
                return 'Error';
            }
        };

        // Convert ini values to bytes for comparison
        $ini_to_bytes = function($val) {
            try {
                $val = trim($val);
                $last = strtolower($val[strlen($val)-1] ?? '');
                $val = (int) $val;
                switch($last) {
                    case 'g': $val *= 1024;
                    case 'm': $val *= 1024;
                    case 'k': $val *= 1024;
                }
                return $val;
            } catch (Exception $e) {
                return 0;
            } catch (Throwable $e) {
                return 0;
            }
        };

        // Basic server info (safest)
        try {
            $server_info = [
                'php_version' => phpversion(),
                'operating_system' => defined('PHP_OS') ? PHP_OS : 'Unknown',
            ];
            
            if (isset($_SERVER['SERVER_SOFTWARE'])) {
                $server_info['server_software'] = sanitize_text_field(wp_unslash($_SERVER['SERVER_SOFTWARE']));
            }
            
            if (@php_uname('m')) {
                $server_info['architecture'] = @php_uname('m');
            }
            
            // Add server load if possible
            if (function_exists('sys_getloadavg')) {
                $load = @sys_getloadavg();
                if ($load !== false) {
                    $server_info['server_load'] = $load;
                }
            }
            
            $response_data['server'] = $server_info;
        } catch (Exception $e) {
            $response_data['server'] = ['error' => 'Server info unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['server'] = ['error' => 'Server info unavailable: ' . $e->getMessage()];
        }

        // Memory usage
        try {
            $memory_current = memory_get_usage(true);
            $memory_peak = memory_get_peak_usage(true);
            $memory_limit = $ini_to_bytes($get_ini_value('memory_limit'));
            
            $response_data['memory_usage'] = [
                'current' => $format_bytes($memory_current),
                'current_bytes' => $memory_current,
                'peak' => $format_bytes($memory_peak),
                'peak_bytes' => $memory_peak,
                'limit' => $format_bytes($memory_limit),
                'limit_bytes' => $memory_limit,
                'usage_percentage' => $memory_limit > 0 ? round(($memory_current / $memory_limit) * 100, 2) : 0,
            ];
        } catch (Exception $e) {
            $response_data['memory_usage'] = ['error' => 'Memory info unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['memory_usage'] = ['error' => 'Memory info unavailable: ' . $e->getMessage()];
        }

        // PHP Configuration
        try {
            $opcache_status = function_exists('opcache_get_status') ? @opcache_get_status() : false;
            $response_data['php_config'] = [
                'memory_limit' => $get_ini_value('memory_limit'),
                'max_execution_time' => $get_ini_value('max_execution_time'),
                'max_input_vars' => $get_ini_value('max_input_vars'),
                'post_max_size' => $get_ini_value('post_max_size'),
                'upload_max_filesize' => $get_ini_value('upload_max_filesize'),
                'max_file_uploads' => $get_ini_value('max_file_uploads'),
                'max_input_time' => $get_ini_value('max_input_time'),
                'default_socket_timeout' => $get_ini_value('default_socket_timeout'),
                'user_agent' => $get_ini_value('user_agent'),
                'auto_detect_line_endings' => $get_ini_value('auto_detect_line_endings'),
                'opcache_enabled' => $opcache_status !== false && !empty($opcache_status['opcache_enabled']),
                'opcache_memory_consumption' => $opcache_status ? ($opcache_status['memory_usage']['used_memory'] ?? 0) : 0,
                'opcache_hit_rate' => $opcache_status ? round(($opcache_status['opcache_statistics']['opcache_hit_rate'] ?? 0), 2) : 0,
            ];
        } catch (Exception $e) {
            $response_data['php_config'] = ['error' => 'PHP config unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['php_config'] = ['error' => 'PHP config unavailable: ' . $e->getMessage()];
        }

        // PHP Extensions  
        try {
            $response_data['php_extensions'] = [
                'curl' => extension_loaded('curl'),
                'gd' => extension_loaded('gd'),
                'imagick' => extension_loaded('imagick'),
                'mbstring' => extension_loaded('mbstring'),
                'mysql' => extension_loaded('mysql') || extension_loaded('mysqli') || extension_loaded('mysqlnd'),
                'zip' => extension_loaded('zip'),
                'xml' => extension_loaded('xml'),
                'json' => extension_loaded('json'),
                'openssl' => extension_loaded('openssl'),
                'fileinfo' => extension_loaded('fileinfo'),
            ];
        } catch (Exception $e) {
            $response_data['php_extensions'] = ['error' => 'Extension info unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['php_extensions'] = ['error' => 'Extension info unavailable: ' . $e->getMessage()];
        }

        // Database info (with progressive fallback)
        try {
            global $wpdb;
            $database_info = [];
            
            // Basic database info
            try {
                if (isset($wpdb) && is_object($wpdb) && method_exists($wpdb, 'db_version')) {
                    $database_info['mysql_version'] = $wpdb->db_version();
                }
            } catch (Exception $e) {
                $database_info['mysql_version'] = 'Error: ' . $e->getMessage();
            } catch (Throwable $e) {
                $database_info['mysql_version'] = 'Error: ' . $e->getMessage();
            }
            
            try {
                if (function_exists('get_num_queries')) {
                    $database_info['queries_this_request'] = get_num_queries();
                }
            } catch (Exception $e) {
                $database_info['queries_this_request'] = 'Error: ' . $e->getMessage();
            } catch (Throwable $e) {
                $database_info['queries_this_request'] = 'Error: ' . $e->getMessage();
            }
            
            if (isset($wpdb->charset)) {
                $database_info['charset'] = $wpdb->charset;
            }
            if (isset($wpdb->collate)) {
                $database_info['collate'] = $wpdb->collate;
            }
            
            // Try MySQL configuration (may fail on restricted hosts)
            try {
                if (isset($wpdb) && method_exists($wpdb, 'get_results')) {
                    $mysql_config = $wpdb->get_results($wpdb->prepare("SHOW VARIABLES WHERE Variable_name IN (
                        %s, %s, %s, %s, %s, %s, %s, %s
                    )", 'max_connections', 'wait_timeout', 'interactive_timeout', 'max_allowed_packet',
                        'innodb_buffer_pool_size', 'query_cache_size', 'tmp_table_size', 'max_heap_table_size'), ARRAY_A);
                    
                    if ($mysql_config && is_array($mysql_config)) {
                        $mysql_vars = [];
                        foreach ($mysql_config as $var) {
                            if (isset($var['Variable_name']) && isset($var['Value'])) {
                                $mysql_vars[$var['Variable_name']] = $var['Value'];
                            }
                        }
                        $database_info['mysql_config'] = $mysql_vars;
                    }
                }
            } catch (Exception $e) {
                $database_info['mysql_config'] = ['error' => 'MySQL config query failed: ' . $e->getMessage()];
            } catch (Throwable $e) {
                $database_info['mysql_config'] = ['error' => 'MySQL config query failed: ' . $e->getMessage()];
            }

            // Try database size (may fail on restricted hosts)
            try {
                if (isset($wpdb) && method_exists($wpdb, 'get_var') && isset($wpdb->dbname)) {
                    $database_size = $wpdb->get_var($wpdb->prepare("SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'DB Size in MB' FROM information_schema.tables WHERE table_schema = %s", $wpdb->dbname));
                    $database_info['database_size'] = $database_size ?: 0;
                }
            } catch (Exception $e) {
                $database_info['database_size'] = 'Error: ' . $e->getMessage();
            } catch (Throwable $e) {
                $database_info['database_size'] = 'Error: ' . $e->getMessage();
            }
            
            $response_data['database'] = $database_info;
        } catch (Exception $e) {
            $response_data['database'] = ['error' => 'Database info unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['database'] = ['error' => 'Database info unavailable: ' . $e->getMessage()];
        }

        // Disk space
        try {
            $disk_free = 0;
            $disk_total = 0;
            $upload_dir = wp_upload_dir();
            if (isset($upload_dir['basedir']) && is_dir($upload_dir['basedir'])) {
                $disk_free = @disk_free_space($upload_dir['basedir']) ?: 0;
                $disk_total = @disk_total_space($upload_dir['basedir']) ?: 0;
            }
            
            $response_data['disk_space'] = [
                'free' => $format_bytes($disk_free),
                'free_bytes' => $disk_free,
                'total' => $format_bytes($disk_total),
                'total_bytes' => $disk_total,
                'used_percentage' => $disk_total > 0 ? round((($disk_total - $disk_free) / $disk_total) * 100, 2) : 0,
            ];
        } catch (Exception $e) {
            $response_data['disk_space'] = ['error' => 'Disk space info unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['disk_space'] = ['error' => 'Disk space info unavailable: ' . $e->getMessage()];
        }

        // WordPress info
        try {
            $wp_info = [
                'version' => get_bloginfo('version'),
                'multisite' => is_multisite(),
                'debug_enabled' => defined('WP_DEBUG') && WP_DEBUG,
                'cache_enabled' => wp_using_ext_object_cache(),
            ];
            
            // Try to get plugin info (may fail on restricted hosts)
            try {
                $active_plugins_list = get_option('active_plugins', []);
                $wp_info['active_plugins_count'] = count($active_plugins_list);
                
                if (!function_exists('get_plugins')) {
                    require_once ABSPATH . 'wp-admin/includes/plugin.php';
                }
                $all_plugins = get_plugins();
                
                $plugin_details = [];
                foreach ($active_plugins_list as $plugin_path) {
                    if (isset($all_plugins[$plugin_path])) {
                        $plugin_info = $all_plugins[$plugin_path];
                        $plugin_details[] = [
                            'name' => $plugin_info['Name'] ?? 'Unknown',
                            'version' => $plugin_info['Version'] ?? 'Unknown',
                            'author' => isset($plugin_info['Author']) ? wp_strip_all_tags($plugin_info['Author']) : 'Unknown',
                            'description' => isset($plugin_info['Description']) ? wp_trim_words(wp_strip_all_tags($plugin_info['Description']), 20) : '',
                            'path' => $plugin_path,
                            'network' => $plugin_info['Network'] ?? false,
                        ];
                    }
                }
                
                usort($plugin_details, function($a, $b) {
                    return strcmp($a['name'], $b['name']);
                });
                
                $wp_info['active_plugins'] = $plugin_details;
            } catch (Exception $e) {
                $wp_info['active_plugins'] = ['error' => 'Plugin enumeration failed: ' . $e->getMessage()];
            } catch (Throwable $e) {
                $wp_info['active_plugins'] = ['error' => 'Plugin enumeration failed: ' . $e->getMessage()];
            }
            
            $response_data['wordpress'] = $wp_info;
        } catch (Exception $e) {
            $response_data['wordpress'] = ['error' => 'WordPress info unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['wordpress'] = ['error' => 'WordPress info unavailable: ' . $e->getMessage()];
        }

        // WooCommerce info
        try {
            $wc_info = [
                'is_installed' => class_exists('WooCommerce'),
                'version' => defined('WC_VERSION') ? WC_VERSION : 'Not installed',
            ];
            
            if (class_exists('WooCommerce')) {
                try {
                    $wc_info['api_enabled'] = get_option('woocommerce_api_enabled', 'no') === 'yes';
                    $wc_info['legacy_api_enabled'] = get_option('woocommerce_enable_legacy_rest_api', 'no') === 'yes';
                    $wc_info['webhook_delivery'] = get_option('woocommerce_webhook_delivery_enabled', 'yes') === 'yes';
                    $wc_info['session_handler'] = get_option('woocommerce_session_handler', 'db');
                    $wc_info['geolocation_enabled'] = get_option('woocommerce_default_customer_address', 'base') !== 'base';
                    $wc_info['product_count'] = wp_count_posts('product')->publish ?? 0;
                    $wc_info['order_count'] = wp_count_posts('shop_order')->publish ?? 0;
                } catch (Exception $e) {
                    $wc_info['additional_info'] = 'Error getting WC settings: ' . $e->getMessage();
                } catch (Throwable $e) {
                    $wc_info['additional_info'] = 'Error getting WC settings: ' . $e->getMessage();
                }
            }
            
            $response_data['woocommerce'] = $wc_info;
        } catch (Exception $e) {
            $response_data['woocommerce'] = ['error' => 'WooCommerce info unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['woocommerce'] = ['error' => 'WooCommerce info unavailable: ' . $e->getMessage()];
        }

        // Performance indicators (safe calculations)
        try {
            $memory_limit = isset($response_data['memory_usage']['limit_bytes']) ? $response_data['memory_usage']['limit_bytes'] : 0;
            $memory_current = isset($response_data['memory_usage']['current_bytes']) ? $response_data['memory_usage']['current_bytes'] : 0;
            $mysql_vars = isset($response_data['database']['mysql_config']) && is_array($response_data['database']['mysql_config']) ? $response_data['database']['mysql_config'] : [];
            $disk_total = isset($response_data['disk_space']['total_bytes']) ? $response_data['disk_space']['total_bytes'] : 0;
            $disk_free = isset($response_data['disk_space']['free_bytes']) ? $response_data['disk_space']['free_bytes'] : 0;
            $opcache_status = function_exists('opcache_get_status') ? @opcache_get_status() : false;
            $php_extensions = isset($response_data['php_extensions']) && is_array($response_data['php_extensions']) ? $response_data['php_extensions'] : [];

            $response_data['performance_indicators'] = [
                'memory_ok' => $memory_limit > 0 ? ($memory_current / $memory_limit) < 0.8 : true,
                'execution_time_ok' => (int)$get_ini_value('max_execution_time') >= 300 || $get_ini_value('max_execution_time') === '0',
                'opcache_ok' => $opcache_status !== false && !empty($opcache_status['opcache_enabled']),
                'disk_space_ok' => $disk_total > 0 ? (($disk_total - $disk_free) / $disk_total) < 0.9 : true,
                'max_input_vars_ok' => (int)$get_ini_value('max_input_vars') >= 3000,
                'post_max_size_ok' => $ini_to_bytes($get_ini_value('post_max_size')) >= (64 * 1024 * 1024),
                'mysql_packet_size_ok' => isset($mysql_vars['max_allowed_packet']) ? (int)$mysql_vars['max_allowed_packet'] >= (64 * 1024 * 1024) : null,
                'mysql_timeout_ok' => isset($mysql_vars['wait_timeout']) ? (int)$mysql_vars['wait_timeout'] >= 300 : null,
                'required_extensions_ok' => count(array_filter($php_extensions)) === count($php_extensions),
            ];
        } catch (Exception $e) {
            $response_data['performance_indicators'] = ['error' => 'Performance analysis unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['performance_indicators'] = ['error' => 'Performance analysis unavailable: ' . $e->getMessage()];
        }

        // WooCommerce Performance Analysis
        try {
            $wc_performance = [];
            
            if (class_exists('WooCommerce')) {
                // Test product operation timing
                $start_time = microtime(true);
                $test_product = wc_get_product(false);
                $product_creation_time = (microtime(true) - $start_time) * 1000;
                $wc_performance['product_creation_ms'] = round($product_creation_time, 2);
                
                // Analyze hooks that could slow down product operations
                global $wp_filter;
                $performance_hooks = [
                    'save_post' => 'Runs on all post saves (including products)',
                    'woocommerce_process_product_meta' => 'WooCommerce product meta processing',
                    'woocommerce_update_product' => 'WooCommerce product updates',
                    'woocommerce_new_product' => 'WooCommerce new product creation',
                    'wp_insert_post' => 'WordPress post insertion',
                    'transition_post_status' => 'Post status changes',
                    'woocommerce_product_object_updated_props' => 'Product property updates',
                ];
                
                $hook_analysis = [];
                foreach ($performance_hooks as $hook => $description) {
                    if (isset($wp_filter[$hook])) {
                        $priorities = array_keys($wp_filter[$hook]->callbacks);
                        $total_callbacks = 0;
                        $plugin_callbacks = [];
                        
                        foreach ($wp_filter[$hook]->callbacks as $priority => $callbacks) {
                            foreach ($callbacks as $callback) {
                                $total_callbacks++;
                                
                                // Try to identify the plugin/theme responsible
                                $callback_source = 'Unknown';
                                if (isset($callback['function'])) {
                                    if (is_array($callback['function']) && is_object($callback['function'][0])) {
                                        $class = get_class($callback['function'][0]);
                                        $callback_source = $class;
                                    } elseif (is_string($callback['function'])) {
                                        $callback_source = $callback['function'];
                                    }
                                }
                                
                                // Try to map to a plugin
                                $plugin_name = 'Core/Theme';
                                if (strpos($callback_source, 'WooCommerce') !== false) {
                                    $plugin_name = 'WooCommerce';
                                } elseif (function_exists('get_plugins')) {
                                    // This is a simplified approach - in reality you'd need to trace the file
                                    foreach (get_option('active_plugins', []) as $plugin) {
                                        $plugin_slug = dirname($plugin);
                                        if (stripos($callback_source, $plugin_slug) !== false) {
                                            $plugin_name = $plugin_slug;
                                            break;
                                        }
                                    }
                                }
                                
                                if (!isset($plugin_callbacks[$plugin_name])) {
                                    $plugin_callbacks[$plugin_name] = 0;
                                }
                                $plugin_callbacks[$plugin_name]++;
                            }
                        }
                        
                        $hook_analysis[$hook] = [
                            'description' => $description,
                            'total_callbacks' => $total_callbacks,
                            'priority_levels' => count($priorities),
                            'plugin_breakdown' => $plugin_callbacks,
                            'risk_level' => $total_callbacks > 10 ? 'high' : ($total_callbacks > 5 ? 'medium' : 'low')
                        ];
                    }
                }
                
                $wc_performance['hook_analysis'] = $hook_analysis;
                
                // Database query analysis for common WooCommerce operations
                try {
                    global $wpdb;
                    
                    // Check for slow query log if accessible
                    $slow_query_info = [];
                    try {
                        $slow_query_status = $wpdb->get_row("SHOW VARIABLES LIKE 'slow_query_log'", ARRAY_A);
                        if ($slow_query_status) {
                            $slow_query_info['enabled'] = $slow_query_status['Value'] === 'ON';
                            $slow_query_time = $wpdb->get_row("SHOW VARIABLES LIKE 'long_query_time'", ARRAY_A);
                            if ($slow_query_time) {
                                $slow_query_info['threshold_seconds'] = (float)$slow_query_time['Value'];
                            }
                        }
                    } catch (Exception $e) {
                        $slow_query_info['error'] = 'Cannot access slow query settings';
                    }
                    
                    // Check for common performance issues
                    $query_performance = [];
                    
                    // Check if meta tables have proper indexes
                    $meta_tables = ['postmeta', 'usermeta', 'termmeta'];
                    foreach ($meta_tables as $table) {
                        try {
                            // Sanitize table name - only allow known meta table names
                            if (!in_array($table, ['postmeta', 'usermeta', 'termmeta'], true)) {
                                continue;
                            }
                            $table_name = $wpdb->prefix . $table;
                            // Use a safe static query for SHOW INDEX
                            $indexes = $wpdb->get_results("SHOW INDEX FROM `" . esc_sql($table_name) . "`", ARRAY_A);
                            $has_meta_key_index = false;
                            foreach ($indexes as $index) {
                                if ($index['Column_name'] === 'meta_key') {
                                    $has_meta_key_index = true;
                                    break;
                                }
                            }
                            $query_performance[$table . '_meta_key_indexed'] = $has_meta_key_index;
                        } catch (Exception $e) {
                            $query_performance[$table . '_index_check'] = 'Error: ' . $e->getMessage();
                        }
                    }
                    
                    // Check for large meta tables that could slow product operations.
                    // Both queries are full-table scans of wp_postmeta (the orphan check
                    // joins against wp_posts too) — cached for 15 min so routine status
                    // polls don't repeatedly hammer the DB.
                    try {
                        $meta_stats = get_transient('ffl_cockpit_postmeta_stats');
                        if (!is_array($meta_stats)) {
                            $postmeta_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->postmeta}");
                            $orphaned_meta = (int) $wpdb->get_var("
                                SELECT COUNT(*)
                                FROM {$wpdb->postmeta} pm
                                LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                                WHERE p.ID IS NULL
                            ");
                            $meta_stats = [
                                'postmeta_count' => $postmeta_count,
                                'orphaned_meta' => $orphaned_meta,
                                'cached_at' => gmdate('Y-m-d\TH:i:s\Z'),
                            ];
                            set_transient('ffl_cockpit_postmeta_stats', $meta_stats, 15 * MINUTE_IN_SECONDS);
                        }
                        $query_performance['postmeta_rows'] = (int) $meta_stats['postmeta_count'];
                        $query_performance['postmeta_size_concern'] = $meta_stats['postmeta_count'] > 1000000;
                        $query_performance['orphaned_postmeta'] = (int) $meta_stats['orphaned_meta'];
                        $query_performance['postmeta_stats_cached_at'] = $meta_stats['cached_at'] ?? null;
                    } catch (Exception $e) {
                        $query_performance['meta_analysis'] = 'Error: ' . $e->getMessage();
                    }
                    
                    $wc_performance['database_analysis'] = [
                        'slow_query_log' => $slow_query_info,
                        'query_performance' => $query_performance
                    ];
                    
                } catch (Exception $e) {
                    $wc_performance['database_analysis'] = ['error' => 'Database analysis failed: ' . $e->getMessage()];
                }
                
                // Plugin conflict detection
                $conflict_indicators = [];
                
                // Check for known problematic plugin combinations
                $active_plugins = get_option('active_plugins', []);
                $problematic_combinations = [
                    'seo' => ['yoast-seo', 'wordpress-seo', 'all-in-one-seo-pack', 'rankmath'],
                    'cache' => ['w3-total-cache', 'wp-rocket', 'wp-super-cache', 'litespeed-cache'],
                    'security' => ['wordfence', 'ithemes-security', 'all-in-one-wp-security'],
                    'backup' => ['updraftplus', 'backwpup', 'duplicator'],
                    'optimization' => ['autoptimize', 'wp-optimize', 'clearfy']
                ];
                
                foreach ($problematic_combinations as $category => $plugins) {
                    $active_in_category = [];
                    foreach ($active_plugins as $plugin) {
                        $plugin_slug = dirname($plugin);
                        if (in_array($plugin_slug, $plugins)) {
                            $active_in_category[] = $plugin_slug;
                        }
                    }
                    if (count($active_in_category) > 1) {
                        $conflict_indicators[$category . '_conflict'] = [
                            'plugins' => $active_in_category,
                            'risk' => 'Multiple ' . $category . ' plugins may conflict'
                        ];
                    }
                }
                
                // Check for plugins known to slow WooCommerce
                $wc_heavy_plugins = [
                    'woocommerce-subscriptions' => 'Subscriptions can add significant overhead',
                    'woocommerce-memberships' => 'Membership checks on every request',
                    'woocommerce-bookings' => 'Complex availability calculations',
                    'yith-woocommerce' => 'YITH plugins often add heavy processing',
                    'jetpack' => 'Jetpack modules can slow admin operations',
                    'elementor' => 'Page builders can slow admin pages',
                    'visual-composer' => 'Page builders can slow admin pages'
                ];
                
                $heavy_plugins_detected = [];
                foreach ($active_plugins as $plugin) {
                    $plugin_slug = dirname($plugin);
                    foreach ($wc_heavy_plugins as $heavy_plugin => $impact) {
                        if (strpos($plugin_slug, $heavy_plugin) !== false) {
                            $heavy_plugins_detected[$plugin_slug] = $impact;
                        }
                    }
                }
                
                if (!empty($heavy_plugins_detected)) {
                    $conflict_indicators['performance_heavy_plugins'] = $heavy_plugins_detected;
                }
                
                $wc_performance['conflict_detection'] = $conflict_indicators;
                
                // Memory usage during product operations
                $memory_before = memory_get_usage(true);
                
                // Simulate a product save operation to measure overhead
                try {
                    // Create a test product object (doesn't save to DB)
                    $test_product = new WC_Product_Simple();
                    $test_product->set_name('Test Product');
                    $test_product->set_regular_price('10.00');
                    
                    $memory_after_creation = memory_get_usage(true);
                    $creation_memory = $memory_after_creation - $memory_before;
                    
                    $wc_performance['memory_analysis'] = [
                        'product_object_memory_bytes' => $creation_memory,
                        'product_object_memory' => $format_bytes($creation_memory)
                    ];
                } catch (Exception $e) {
                    $wc_performance['memory_analysis'] = ['error' => 'Memory analysis failed: ' . $e->getMessage()];
                }
            }
            
            $response_data['woocommerce_performance'] = $wc_performance;
        } catch (Exception $e) {
            $response_data['woocommerce_performance'] = ['error' => 'WooCommerce performance analysis unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['woocommerce_performance'] = ['error' => 'WooCommerce performance analysis unavailable: ' . $e->getMessage()];
        }

        // Theme and Plugin Performance Impact
        try {
            $performance_impact = [];
            
            // Analyze admin-ajax usage (common performance killer)
            $admin_ajax_analysis = [];
            if (defined('DOING_AJAX') && DOING_AJAX) {
                $admin_ajax_analysis['currently_in_ajax'] = true;
                // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Diagnostic data collection, not processing form data  
                if (isset($_POST['action'])) {
                    // phpcs:ignore WordPress.Security.NonceVerification.Missing, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Diagnostic data, sanitized for output
                    $admin_ajax_analysis['current_action'] = sanitize_text_field(wp_unslash($_POST['action']));
                }
            }
            
            // Check for common performance killers in wp_options
            try {
                global $wpdb;
                
                // Large autoloaded options
                $large_autoload = $wpdb->get_results($wpdb->prepare("
                    SELECT option_name, LENGTH(option_value) as size_bytes 
                    FROM {$wpdb->options} 
                    WHERE autoload = %s 
                    AND LENGTH(option_value) > %d 
                    ORDER BY size_bytes DESC 
                    LIMIT %d
                ", 'yes', 100000, 10), ARRAY_A);
                
                if ($large_autoload) {
                    $autoload_issues = [];
                    foreach ($large_autoload as $option) {
                        $autoload_issues[] = [
                            'option' => $option['option_name'],
                            'size' => $format_bytes($option['size_bytes']),
                            'size_bytes' => $option['size_bytes']
                        ];
                    }
                    $performance_impact['large_autoloaded_options'] = $autoload_issues;
                }
                
                // Total autoload size
                $total_autoload = $wpdb->get_var($wpdb->prepare("
                    SELECT SUM(LENGTH(option_value)) 
                    FROM {$wpdb->options} 
                    WHERE autoload = %s
                ", 'yes'));
                
                $performance_impact['total_autoload_size'] = [
                    'bytes' => (int)$total_autoload,
                    'formatted' => $format_bytes($total_autoload),
                    'concern_level' => $total_autoload > 1000000 ? 'high' : ($total_autoload > 500000 ? 'medium' : 'low')
                ];
                
            } catch (Exception $e) {
                $performance_impact['wp_options_analysis'] = 'Error: ' . $e->getMessage();
            }
            
            // Check for excessive post revisions
            try {
                $revision_count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s", 'revision'));
                $performance_impact['post_revisions'] = [
                    'count' => (int)$revision_count,
                    'concern_level' => $revision_count > 10000 ? 'high' : ($revision_count > 5000 ? 'medium' : 'low')
                ];
            } catch (Exception $e) {
                $performance_impact['revision_analysis'] = 'Error: ' . $e->getMessage();
            }
            
            // Theme performance indicators
            $theme_info = wp_get_theme();
            $performance_impact['theme'] = [
                'name' => $theme_info->get('Name'),
                'version' => $theme_info->get('Version'),
                'is_child_theme' => is_child_theme(),
                'template' => $theme_info->get('Template')
            ];
            
            $response_data['performance_impact'] = $performance_impact;
        } catch (Exception $e) {
            $response_data['performance_impact'] = ['error' => 'Performance impact analysis unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['performance_impact'] = ['error' => 'Performance impact analysis unavailable: ' . $e->getMessage()];
        }

        // Recommendations (enhanced with performance insights)
        try {
            $recommendations = [];
            
            if (isset($response_data['memory_usage']['limit_bytes']) && $response_data['memory_usage']['limit_bytes'] < (512 * 1024 * 1024)) {
                $recommendations['memory_limit'] = 'Increase memory_limit to at least 512MB for large product operations';
            }
            
            $max_exec_time = (int)$get_ini_value('max_execution_time');
            if ($max_exec_time < 300 && $get_ini_value('max_execution_time') !== '0') {
                $recommendations['max_execution_time'] = 'Increase max_execution_time to at least 300 seconds';
            }
            
            if ((int)$get_ini_value('max_input_vars') < 3000) {
                $recommendations['max_input_vars'] = 'Increase max_input_vars to at least 3000 for bulk operations';
            }
            
            $opcache_status = function_exists('opcache_get_status') ? @opcache_get_status() : false;
            if (!($opcache_status !== false && !empty($opcache_status['opcache_enabled']))) {
                $recommendations['opcache'] = 'Enable OPcache for better PHP performance';
            }
            
            if (isset($response_data['database']['mysql_config']['max_allowed_packet']) && (int)$response_data['database']['mysql_config']['max_allowed_packet'] < (64 * 1024 * 1024)) {
                $recommendations['mysql_packet_size'] = 'Increase MySQL max_allowed_packet to at least 64MB';
            }
            
            if (!wp_using_ext_object_cache()) {
                $recommendations['external_cache'] = 'Consider using Redis or Memcached for object caching';
            }
            
            // Performance-specific recommendations
            if (isset($response_data['performance_impact']['total_autoload_size']['concern_level']) && 
                $response_data['performance_impact']['total_autoload_size']['concern_level'] === 'high') {
                $recommendations['autoload_cleanup'] = 'Large autoloaded options detected. Consider cleaning up wp_options table';
            }
            
            if (isset($response_data['woocommerce_performance']['hook_analysis'])) {
                $high_risk_hooks = array_filter($response_data['woocommerce_performance']['hook_analysis'], 
                    function($hook) { return $hook['risk_level'] === 'high'; });
                if (!empty($high_risk_hooks)) {
                    $recommendations['hook_optimization'] = 'High number of callbacks detected on product hooks. Consider plugin audit';
                }
            }
            
            if (isset($response_data['performance_impact']['post_revisions']['concern_level']) && 
                $response_data['performance_impact']['post_revisions']['concern_level'] === 'high') {
                $recommendations['revision_cleanup'] = 'Excessive post revisions detected. Consider cleanup and limiting revisions';
            }
            
            if (isset($response_data['woocommerce_performance']['conflict_detection']) && 
                !empty($response_data['woocommerce_performance']['conflict_detection'])) {
                $recommendations['plugin_conflicts'] = 'Potential plugin conflicts detected. Review active plugins for performance impact';
            }
            
            $response_data['recommendations'] = $recommendations;
        } catch (Exception $e) {
            $response_data['recommendations'] = ['error' => 'Recommendations unavailable: ' . $e->getMessage()];
        } catch (Throwable $e) {
            $response_data['recommendations'] = ['error' => 'Recommendations unavailable: ' . $e->getMessage()];
        }

        return new WP_REST_Response($response_data, 200);
    }

    /**
     * Handle rebuilding WooCommerce data (lookup tables, caches, etc.)
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response
     */
    public static function handle_rebuild_woocommerce_data(WP_REST_Request $request) {
        try {
            $start_time = microtime(true);
            
            // Set longer execution time for this operation
            // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- Intentional timeout adjustment for WooCommerce rebuild
            @set_time_limit(300); // 5 minutes
            // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- Intentional timeout adjustment for WooCommerce rebuild
            @ini_set('max_execution_time', 300);
            
            // Optional: Get parameters for what to rebuild
            $body = $request->get_json_params();
            $rebuild_lookup_tables = $body['rebuild_lookup_tables'] ?? true;
            $clear_caches = $body['clear_caches'] ?? true;
            $defer_term_counting = $body['defer_term_counting'] ?? true;
            
            $results = [];
            $errors = [];
            
            // Start with deferred term counting if requested
            if ($defer_term_counting) {
                wp_defer_term_counting(true);
                $results[] = 'Term counting deferred';
            }
            
            try {
                // Call the rebuild function from the Update Processor
                if (class_exists('FFLCockpit_Update_Processor') && method_exists('FFLCockpit_Update_Processor', 'rebuild_woocommerce_data')) {
                    FFLCockpit_Update_Processor::rebuild_woocommerce_data();
                    $results[] = 'WooCommerce lookup tables rebuilt';
                } else {
                    // Fallback: Call WooCommerce functions directly
                    if ($rebuild_lookup_tables && function_exists('wc_update_product_lookup_tables')) {
                        wc_update_product_lookup_tables();
                        $results[] = 'WooCommerce product lookup tables rebuilt';
                    }
                    
                    if ($clear_caches && function_exists('wc_delete_product_transients')) {
                        wc_delete_product_transients();
                        $results[] = 'WooCommerce product transients cleared';
                    }
                }
                
                // Additional cache clearing
                if ($clear_caches) {
                    // Clear WordPress object cache
                    wp_cache_flush();
                    $results[] = 'WordPress object cache flushed';
                    
                    // Clear WooCommerce specific caches
                    if (function_exists('wc_clear_template_cache')) {
                        wc_clear_template_cache();
                        $results[] = 'WooCommerce template cache cleared';
                    }
                    
                    // Clear product data cache
                    if (class_exists('WC_Cache_Helper')) {
                        WC_Cache_Helper::get_transient_version('product', true);
                        $results[] = 'WooCommerce product cache version incremented';
                    }
                    
                    // Clear taxonomy caches
                    $taxonomies = get_object_taxonomies('product');
                    foreach ($taxonomies as $taxonomy) {
                        delete_option("{$taxonomy}_children");
                        clean_taxonomy_cache($taxonomy);
                    }
                    $results[] = 'Product taxonomy caches cleared';
                }
                
                // Update term counts if we deferred them
                if ($defer_term_counting) {
                    wp_defer_term_counting(false);
                    $results[] = 'Term counting resumed and updated';
                }
                
                // Force garbage collection
                if (function_exists('gc_collect_cycles')) {
                    $collected = gc_collect_cycles();
                    $results[] = "Garbage collection completed ({$collected} cycles collected)";
                }
                
            } catch (Exception $e) {
                $errors[] = 'Error during rebuild: ' . $e->getMessage();
                g_ffl_debug_log('WooCommerce rebuild error: ' . $e->getMessage());
                
                // Ensure term counting is resumed even if there's an error
                if ($defer_term_counting) {
                    wp_defer_term_counting(false);
                }
            }
            
            $elapsed_time = round(microtime(true) - $start_time, 2);
            
            $response = [
                'status' => empty($errors) ? 'success' : 'partial_success',
                'message' => empty($errors) ? 'WooCommerce data rebuild completed successfully' : 'WooCommerce data rebuild completed with some errors',
                'elapsed_time' => $elapsed_time,
                'results' => $results,
                'timestamp' => gmdate('Y-m-d\TH:i:s\Z'),
                'plugin_version' => self::$version
            ];
            
            if (!empty($errors)) {
                $response['errors'] = $errors;
            }
            
            return new WP_REST_Response($response, empty($errors) ? 200 : 207);
            
        } catch (Exception $e) {
            g_ffl_debug_log('WooCommerce rebuild endpoint error: ' . $e->getMessage());
            
            return new WP_REST_Response([
                'status' => 'error',
                'message' => 'Failed to rebuild WooCommerce data: ' . $e->getMessage(),
                'timestamp' => gmdate('Y-m-d\TH:i:s\Z'),
                'plugin_version' => self::$version
            ], 500);
        }
    }

    /**
     * Handle cleaning up SKU conflicts in WooCommerce lookup tables
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response
     */
    public static function handle_cleanup_sku_conflicts(WP_REST_Request $request) {
        try {
            $start_time = microtime(true);
            
            // Get optional SKU parameter
            $body = $request->get_json_params();
            $specific_sku = $body['sku'] ?? null;
            
            if ($specific_sku) {
                g_ffl_debug_log("Starting SKU conflict cleanup for specific SKU: {$specific_sku}");
            } else {
                g_ffl_debug_log("Starting general SKU conflict cleanup");
            }
            
            // Call the cleanup function from the Update Processor
            if (class_exists('FFLCockpit_Update_Processor') && method_exists('FFLCockpit_Update_Processor', 'cleanup_sku_conflicts')) {
                $report = FFLCockpit_Update_Processor::cleanup_sku_conflicts($specific_sku);
            } else {
                throw new Exception('SKU cleanup function not available');
            }
            
            $elapsed_time = round(microtime(true) - $start_time, 2);
            
            $status = 'success';
            $message = 'SKU conflict cleanup completed successfully';
            
            if (!empty($report['errors'])) {
                $status = 'partial_success';
                $message = 'SKU conflict cleanup completed with some errors';
            }
            
            if ($specific_sku && isset($report['sku_available'])) {
                $message = "SKU '{$specific_sku}' is now available for use";
            } elseif ($specific_sku && isset($report['active_conflicts'])) {
                $status = 'conflict_exists';
                $message = "SKU '{$specific_sku}' is still in use by existing products";
            }
            
            $response = [
                'status' => $status,
                'message' => $message,
                'sku_checked' => $specific_sku,
                'elapsed_time' => $elapsed_time,
                'cleanup_report' => $report,
                'timestamp' => gmdate('Y-m-d\TH:i:s\Z'),
                'plugin_version' => self::$version
            ];
            
            return new WP_REST_Response($response, $status === 'conflict_exists' ? 409 : 200);
            
        } catch (Exception $e) {
            g_ffl_debug_log('SKU cleanup endpoint error: ' . $e->getMessage());
            
            return new WP_REST_Response([
                'status' => 'error',
                'message' => 'Failed to cleanup SKU conflicts: ' . $e->getMessage(),
                'sku_checked' => $specific_sku ?? null,
                'timestamp' => gmdate('Y-m-d\TH:i:s\Z'),
                'plugin_version' => self::$version
            ], 500);
        }
    }

    /**
     * Handle term count updates endpoint
     */
    public static function handle_update_term_counts(WP_REST_Request $request) {
        try {
            $start_time = microtime(true);
            
            // Get parameters
            $body = $request->get_json_params();
            $taxonomies = $body['taxonomies'] ?? null; // null = all product taxonomies
            $force_update = $body['force_update'] ?? false;
            
            $results = [];
            $updated_counts = [];
            
            // Get taxonomies to update
            if ($taxonomies) {
                $taxonomies_to_update = array_intersect($taxonomies, get_object_taxonomies('product'));
            } else {
                $taxonomies_to_update = get_object_taxonomies('product');
            }
            
            foreach ($taxonomies_to_update as $taxonomy) {
                $start_tax_time = microtime(true);
                
                // Get all term taxonomy IDs for this taxonomy
                global $wpdb;
                $tt_ids = $wpdb->get_col($wpdb->prepare( "SELECT tt.term_taxonomy_id FROM {$wpdb->term_taxonomy} tt JOIN {$wpdb->terms} t ON t.term_id = tt.term_id WHERE tt.taxonomy = %s ORDER BY t.name ASC", $taxonomy ));
                
                if (!empty($tt_ids)) {
                    // Update term counts
                    if (function_exists('wp_update_term_count_now')) {
                        wp_update_term_count_now($tt_ids, $taxonomy);
                    } else {
                        _update_post_term_count($tt_ids, $taxonomy);
                    }
                    
                    // Clear taxonomy cache
                    clean_taxonomy_cache($taxonomy);
                    delete_option("{$taxonomy}_children");
                    
                    $tax_time = round((microtime(true) - $start_tax_time) * 1000, 2);
                    $updated_counts[$taxonomy] = [
                        'terms_updated' => count($tt_ids),
                        'time_ms' => $tax_time
                    ];
                }
            }
            
            $total_time = round((microtime(true) - $start_time) * 1000, 2);
            
            return new WP_REST_Response([
                'status' => 'success',
                'message' => 'Term counts updated successfully',
                'taxonomies_processed' => count($taxonomies_to_update),
                'updated_counts' => $updated_counts,
                'total_terms_updated' => array_sum(array_column($updated_counts, 'terms_updated')),
                'execution_time_ms' => $total_time,
                'timestamp' => gmdate('Y-m-d\TH:i:s\Z'),
                'plugin_version' => self::$version
            ], 200);
            
        } catch (Exception $e) {
            g_ffl_debug_log('Term count update endpoint error: ' . $e->getMessage());
            
            return new WP_REST_Response([
                'status' => 'error',
                'message' => 'Failed to update term counts: ' . $e->getMessage(),
                'timestamp' => gmdate('Y-m-d\TH:i:s\Z'),
                'plugin_version' => self::$version
            ], 500);
        }
    }
    
    
}
