qdr.app.woo.plugins/studiou-wc-product-cat-manage/includes/class-studiou-wc-product-cat-manage-manipulator.php

<?php
/**
 * Category Manipulator class
 *
 * Handles manipulation operations for product categories
 *
 * @since      1.0.0
 * @package    Studiou_WC_Product_Cat_Manage
 * @subpackage Studiou_WC_Product_Cat_Manage/includes
 */

// If this file is called directly, abort.
if (!defined('WPINC')) {
    die;
}

class Studiou_WC_Product_Cat_Manage_Manipulator {
    
    /**
     * Database handler
     *
     * @var Studiou_WC_Product_Cat_Manage_DB
     */
    private $db;
    
    /**
     * Constructor
     *
     * @param Studiou_WC_Product_Cat_Manage_DB $db Database handler
     */
    public function __construct($db) {
        $this->db = $db;
        
        // Register AJAX handlers
        add_action('wp_ajax_studiou_wcpcm_move_products', array($this, 'handle_move_products_ajax'));
        add_action('wp_ajax_studiou_wcpcm_batch_description', array($this, 'handle_batch_description_ajax'));
        add_action('wp_ajax_studiou_wcpcm_delete_products', array($this, 'handle_delete_products_ajax'));
        add_action('wp_ajax_studiou_wcpcm_delete_products_batch', array($this, 'handle_delete_products_batch_ajax'));

        // Add a test AJAX handler to verify AJAX is working
        add_action('wp_ajax_studiou_wcpcm_test', array($this, 'handle_test_ajax'));

        // Debug: Log that the handler is being registered
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: AJAX handlers registered - move_products, batch_description, and delete_products');
        }
    }
    
    /**
     * Handle AJAX batch description request
     */
    public function handle_batch_description_ajax() {
        // Clean all output buffers and prevent any output
        while (ob_get_level()) {
            ob_end_clean();
        }
        
        // Start a new output buffer to catch any unwanted output
        ob_start();
        
        // Suppress PHP errors/notices during AJAX to prevent JSON corruption
        $original_error_reporting = error_reporting();
        error_reporting(0);
        
        // Enable error logging only
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: Batch description AJAX handler called');
            error_log('STUDIOU WC: POST data: ' . print_r($_POST, true));
        }
        
        try {
            // Check nonce
            if (!isset($_POST['nonce']) || !wp_verify_nonce($_POST['nonce'], 'studiou-wcpcm-nonce')) {
                wp_send_json_error(array(
                    'message' => __('Security check failed', 'studiou-wc-product-cat-manage')
                ));
            }
            
            // Check permissions
            if (!current_user_can('manage_woocommerce')) {
                wp_send_json_error(array(
                    'message' => __('You do not have sufficient permissions', 'studiou-wc-product-cat-manage')
                ));
            }
            
            // Validate input
            if (!isset($_POST['selected_categories']) || !isset($_POST['description'])) {
                wp_send_json_error(array(
                    'message' => __('Missing required parameters', 'studiou-wc-product-cat-manage')
                ));
            }
            
            $selected_categories = array_map('intval', $_POST['selected_categories']);
            $description = sanitize_textarea_field($_POST['description']);
            
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Batch description operation started - Categories: ' . implode(', ', $selected_categories));
                error_log('STUDIOU WC: Description: ' . $description);
            }
            
            // Validate categories
            if (empty($selected_categories)) {
                wp_send_json_error(array(
                    'message' => __('Please select at least one category', 'studiou-wc-product-cat-manage')
                ));
            }
            
            // Execute batch description operation
            $result = $this->update_categories_description($selected_categories, $description);
            
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Batch description operation completed - Updated: ' . $result['updated_count'] . ' categories');
                error_log('STUDIOU WC: Sending success response: ' . print_r($result, true));
            }
            
            // Clean any output that might have been generated
            ob_clean();
            
            // Return success response
            wp_send_json_success(array(
                'message' => $result['message'],
                'updated_count' => $result['updated_count'],
                'failed_count' => isset($result['failed_count']) ? $result['failed_count'] : 0
            ));
            
        } catch (Exception $e) {
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Batch description operation failed - ' . $e->getMessage());
                error_log('STUDIOU WC: Exception trace: ' . $e->getTraceAsString());
            }
            
            // Clean any output that might have been generated
            ob_clean();
            
            wp_send_json_error(array(
                'message' => sprintf(
                    __('Error during batch description operation: %s', 'studiou-wc-product-cat-manage'),
                    $e->getMessage()
                )
            ));
        } finally {
            // Restore original error reporting
            error_reporting($original_error_reporting);
            
            // End the output buffer
            ob_end_clean();
        }
    }
    
    /**
     * Handle AJAX move products request
     */
    public function handle_move_products_ajax() {
        // Clean all output buffers and prevent any output
        while (ob_get_level()) {
            ob_end_clean();
        }
        
        // Start a new output buffer to catch any unwanted output
        ob_start();
        
        // Suppress PHP errors/notices during AJAX to prevent JSON corruption
        $original_error_reporting = error_reporting();
        error_reporting(0);
        
        // Enable error logging only
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: AJAX handler called');
            error_log('STUDIOU WC: POST data: ' . print_r($_POST, true));
        }
        
        try {
            // Check nonce
            if (!isset($_POST['nonce']) || !wp_verify_nonce($_POST['nonce'], 'studiou-wcpcm-nonce')) {
                wp_send_json_error(array(
                    'message' => __('Security check failed', 'studiou-wc-product-cat-manage')
                ));
            }
            
            // Check permissions
            if (!current_user_can('manage_woocommerce')) {
                wp_send_json_error(array(
                    'message' => __('You do not have sufficient permissions', 'studiou-wc-product-cat-manage')
                ));
            }
            
            // Validate input
            if (!isset($_POST['source_category']) || !isset($_POST['target_category'])) {
                wp_send_json_error(array(
                    'message' => __('Missing required parameters', 'studiou-wc-product-cat-manage')
                ));
            }
            
            $source_category_id = intval($_POST['source_category']);
            $target_category_id = intval($_POST['target_category']);
            
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Move operation started - Source: ' . $source_category_id . ', Target: ' . $target_category_id);
            }
            
            // Validate categories
            if ($source_category_id === $target_category_id) {
                wp_send_json_error(array(
                    'message' => __('Source and target categories must be different', 'studiou-wc-product-cat-manage')
                ));
            }
            
            if ($source_category_id <= 0 || $target_category_id <= 0) {
                wp_send_json_error(array(
                    'message' => __('Invalid category selection', 'studiou-wc-product-cat-manage')
                ));
            }
            
            // Execute move operation
            $result = $this->move_products_between_categories($source_category_id, $target_category_id);
            
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Move operation completed - Moved: ' . $result['moved_count'] . ' products');
                error_log('STUDIOU WC: Sending success response: ' . print_r($result, true));
            }
            
            // Clean any output that might have been generated
            ob_clean();
            
            // Return success response
            wp_send_json_success(array(
                'message' => $result['message'],
                'moved_count' => $result['moved_count'],
                'source_count' => $result['source_count'],
                'target_count' => $result['target_count'],
                'failed_count' => isset($result['failed_count']) ? $result['failed_count'] : 0
            ));
            
        } catch (Exception $e) {
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Move operation failed - ' . $e->getMessage());
                error_log('STUDIOU WC: Exception trace: ' . $e->getTraceAsString());
            }
            
            // Clean any output that might have been generated
            ob_clean();
            
            wp_send_json_error(array(
                'message' => sprintf(
                    __('Error during move operation: %s', 'studiou-wc-product-cat-manage'),
                    $e->getMessage()
                )
            ));
        } finally {
            // Restore original error reporting
            error_reporting($original_error_reporting);
            
            // End the output buffer
            ob_end_clean();
        }
    }
    
    /**
     * Move all products from source category to target category
     *
     * @param int $source_category_id Source category ID
     * @param int $target_category_id Target category ID
     * @return array Result with counts and message
     * @throws Exception On error
     */
    public function move_products_between_categories($source_category_id, $target_category_id) {
        // Get source and target category terms
        $source_category = get_term($source_category_id, 'product_cat');
        $target_category = get_term($target_category_id, 'product_cat');
        
        if (is_wp_error($source_category) || is_wp_error($target_category)) {
            throw new Exception(__('Invalid category specified', 'studiou-wc-product-cat-manage'));
        }
        
        // Get initial count of products in source category using direct DB query
        $initial_source_count = $this->get_category_product_count($source_category_id);
        
        // Check if source category has any products
        if ($initial_source_count === 0) {
            $message = sprintf(
                __('No products found in source category "%s". Nothing to move.', 'studiou-wc-product-cat-manage'),
                $source_category->name
            );
            
            return array(
                'moved_count' => 0,
                'source_count' => 0,
                'target_count' => $this->get_category_product_count($target_category_id),
                'message' => $message
            );
        }
        
        // Get all products in source category using direct DB query
        $products = $this->get_products_in_category($source_category_id);
        $moved_count = 0;
        $failed_count = 0;
        
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: Found ' . count($products) . ' products in category ' . $source_category->name . ' for moving');
        }
        
        // Move each product
        foreach ($products as $product_id) {
            // Get current categories for the product
            $current_categories = wp_get_post_terms($product_id, 'product_cat', array('fields' => 'ids'));
            
            if (is_wp_error($current_categories)) {
                $failed_count++;
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    error_log('STUDIOU WC: Error getting categories for product ' . $product_id . ': ' . $current_categories->get_error_message());
                }
                continue;
            }
            
            // Remove source category and add target category
            $new_categories = array_diff($current_categories, array($source_category_id));
            $new_categories[] = $target_category_id;
            
            // Update product categories
            $result = wp_set_post_terms($product_id, $new_categories, 'product_cat');
            
            if (!is_wp_error($result)) {
                $moved_count++;
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    error_log('STUDIOU WC: Successfully moved product ' . $product_id . ' from category ' . $source_category_id . ' to ' . $target_category_id);
                }
            } else {
                $failed_count++;
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    error_log('STUDIOU WC: Error moving product ' . $product_id . ': ' . $result->get_error_message());
                }
            }
        }
        
        // Get updated counts using direct DB query
        $source_count = $this->get_category_product_count($source_category_id);
        $target_count = $this->get_category_product_count($target_category_id);
        
        // Generate detailed result message
        if ($moved_count === 0 && count($products) > 0) {
            $message = sprintf(
                __('Failed to move any products from "%s" to "%s". All %d products encountered errors during the move operation.', 'studiou-wc-product-cat-manage'),
                $source_category->name,
                $target_category->name,
                count($products)
            );
        } else {
            $message = sprintf(
                __('Moved %d products from "%s" to "%s". Source category now has %d products, target category now has %d products.', 'studiou-wc-product-cat-manage'),
                $moved_count,
                $source_category->name,
                $target_category->name,
                $source_count,
                $target_count
            );
            
            if ($failed_count > 0) {
                $message .= ' ' . sprintf(
                    __('Note: %d products failed to move due to errors.', 'studiou-wc-product-cat-manage'),
                    $failed_count
                );
            }
        }
        
        return array(
            'moved_count' => $moved_count,
            'source_count' => $source_count,
            'target_count' => $target_count,
            'failed_count' => $failed_count,
            'message' => $message
        );
    }
    
    /**
     * Get product count for a category
     *
     * @param int $category_id Category ID
     * @return int Product count
     */
    private function get_category_product_count($category_id) {
        global $wpdb;
        
        // Use direct database query for more accurate counting
        $sql = "
            SELECT COUNT(DISTINCT p.ID) 
            FROM {$wpdb->posts} p
            INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
            WHERE tt.term_id = %d 
            AND tt.taxonomy = 'product_cat'
            AND p.post_type = 'product'
            AND p.post_status IN ('publish', 'private', 'draft')
        ";
        
        $count = $wpdb->get_var($wpdb->prepare($sql, $category_id));
        
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: Category ' . $category_id . ' product count (direct DB query): ' . intval($count));
        }
        
        return intval($count);
    }
    
    /**
     * Get all products in a category
     *
     * @param int $category_id Category ID
     * @return array Array of product IDs
     */
    private function get_products_in_category($category_id) {
        global $wpdb;
        
        // Use direct database query to get products
        $sql = "
            SELECT DISTINCT p.ID 
            FROM {$wpdb->posts} p
            INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
            WHERE tt.term_id = %d 
            AND tt.taxonomy = 'product_cat'
            AND p.post_type = 'product'
            AND p.post_status IN ('publish', 'private', 'draft')
        ";
        
        $products = $wpdb->get_col($wpdb->prepare($sql, $category_id));
        
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: Found ' . count($products) . ' products in category ' . $category_id . ' (direct DB query)');
            if (!empty($products)) {
                error_log('STUDIOU WC: Product IDs: ' . implode(', ', array_slice($products, 0, 10)) . (count($products) > 10 ? ' (showing first 10)' : ''));
            }
        }
        
        return $products ? array_map('intval', $products) : array();
    }
    
    /**
     * Update descriptions for multiple categories
     *
     * @param array $category_ids Array of category IDs
     * @param string $description New description for categories
     * @return array Result with counts and message
     * @throws Exception On error
     */
    public function update_categories_description($category_ids, $description) {
        $updated_count = 0;
        $failed_count = 0;
        $category_names = array();
        
        // Debug logging
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: Starting batch description update for ' . count($category_ids) . ' categories');
        }
        
        // Update each category
        foreach ($category_ids as $category_id) {
            // Get category term
            $category = get_term($category_id, 'product_cat');
            
            if (is_wp_error($category) || !$category) {
                $failed_count++;
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    error_log('STUDIOU WC: Error getting category ' . $category_id . ': ' . ($category ? $category->get_error_message() : 'Category not found'));
                }
                continue;
            }
            
            // Update category description
            $result = wp_update_term($category_id, 'product_cat', array(
                'description' => $description
            ));
            
            if (!is_wp_error($result)) {
                $updated_count++;
                $category_names[] = $category->name;
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    error_log('STUDIOU WC: Successfully updated description for category ' . $category_id . ' (' . $category->name . ')');
                }
            } else {
                $failed_count++;
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    error_log('STUDIOU WC: Error updating category ' . $category_id . ': ' . $result->get_error_message());
                }
            }
        }
        
        // Generate result message
        if ($updated_count === 0) {
            $message = sprintf(
                __('Failed to update any category descriptions. %d categories encountered errors.', 'studiou-wc-product-cat-manage'),
                $failed_count
            );
        } else {
            $message = sprintf(
                __('Successfully updated descriptions for %d categories: %s', 'studiou-wc-product-cat-manage'),
                $updated_count,
                implode(', ', array_slice($category_names, 0, 5)) . ($updated_count > 5 ? sprintf(__(', and %d more', 'studiou-wc-product-cat-manage'), $updated_count - 5) : '')
            );
            
            if ($failed_count > 0) {
                $message .= ' ' . sprintf(
                    __('Note: %d categories failed to update due to errors.', 'studiou-wc-product-cat-manage'),
                    $failed_count
                );
            }
        }
        
        return array(
            'updated_count' => $updated_count,
            'failed_count' => $failed_count,
            'message' => $message
        );
    }
    
    /**
     * Get all categories with product counts for select lists
     *
     * @return array Array of categories with ID, name, and product count
     */
    public function get_categories_with_counts() {
        $args = array(
            'taxonomy' => 'product_cat',
            'orderby' => 'name',
            'hide_empty' => false,
        );

        $categories = get_terms($args);
        $categories_with_counts = array();

        if (!is_wp_error($categories)) {
            foreach ($categories as $category) {
                $product_count = $this->get_category_product_count($category->term_id);

                $categories_with_counts[] = array(
                    'id' => $category->term_id,
                    'name' => $category->name,
                    'count' => $product_count,
                    'display_name' => sprintf('%s (%d products)', $category->name, $product_count)
                );
            }
        }

        return $categories_with_counts;
    }

    /**
     * Handle AJAX delete products request (initial request to get product IDs)
     */
    public function handle_delete_products_ajax() {
        // Clean all output buffers and prevent any output
        while (ob_get_level()) {
            ob_end_clean();
        }

        // Start a new output buffer to catch any unwanted output
        ob_start();

        // Suppress PHP errors/notices during AJAX to prevent JSON corruption
        $original_error_reporting = error_reporting();
        error_reporting(0);

        // Enable error logging only
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: Delete products AJAX handler called');
            error_log('STUDIOU WC: POST data: ' . print_r($_POST, true));
        }

        try {
            // Check nonce
            if (!isset($_POST['nonce']) || !wp_verify_nonce($_POST['nonce'], 'studiou-wcpcm-nonce')) {
                wp_send_json_error(array(
                    'message' => __('Security check failed', 'studiou-wc-product-cat-manage')
                ));
            }

            // Check permissions
            if (!current_user_can('manage_woocommerce')) {
                wp_send_json_error(array(
                    'message' => __('You do not have sufficient permissions', 'studiou-wc-product-cat-manage')
                ));
            }

            // Validate input
            if (!isset($_POST['delete_categories'])) {
                wp_send_json_error(array(
                    'message' => __('Missing required parameters', 'studiou-wc-product-cat-manage')
                ));
            }

            $category_ids = array_map('intval', $_POST['delete_categories']);

            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Delete products operation started - Categories: ' . implode(', ', $category_ids));
            }

            // Validate categories
            if (empty($category_ids)) {
                wp_send_json_error(array(
                    'message' => __('Please select at least one category', 'studiou-wc-product-cat-manage')
                ));
            }

            // Get all products from selected categories
            $product_ids = $this->get_products_from_categories($category_ids);

            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Found ' . count($product_ids) . ' products to delete');
            }

            // Clean any output that might have been generated
            ob_clean();

            // Return product IDs for batch processing
            wp_send_json_success(array(
                'product_ids' => $product_ids,
                'total_count' => count($product_ids),
                'message' => sprintf(
                    __('Found %d products to delete', 'studiou-wc-product-cat-manage'),
                    count($product_ids)
                )
            ));

        } catch (Exception $e) {
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Delete products operation failed - ' . $e->getMessage());
                error_log('STUDIOU WC: Exception trace: ' . $e->getTraceAsString());
            }

            // Clean any output that might have been generated
            ob_clean();

            wp_send_json_error(array(
                'message' => sprintf(
                    __('Error during delete operation: %s', 'studiou-wc-product-cat-manage'),
                    $e->getMessage()
                )
            ));
        } finally {
            // Restore original error reporting
            error_reporting($original_error_reporting);

            // End the output buffer
            ob_end_clean();
        }
    }

    /**
     * Handle AJAX delete products batch request (processes products in batches)
     */
    public function handle_delete_products_batch_ajax() {
        // Clean all output buffers and prevent any output
        while (ob_get_level()) {
            ob_end_clean();
        }

        // Start a new output buffer to catch any unwanted output
        ob_start();

        // Suppress PHP errors/notices during AJAX to prevent JSON corruption
        $original_error_reporting = error_reporting();
        error_reporting(0);

        // Enable error logging only
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: Delete products batch AJAX handler called');
            error_log('STUDIOU WC: POST data: ' . print_r($_POST, true));
        }

        try {
            // Check nonce
            if (!isset($_POST['nonce']) || !wp_verify_nonce($_POST['nonce'], 'studiou-wcpcm-nonce')) {
                wp_send_json_error(array(
                    'message' => __('Security check failed', 'studiou-wc-product-cat-manage')
                ));
            }

            // Check permissions
            if (!current_user_can('manage_woocommerce')) {
                wp_send_json_error(array(
                    'message' => __('You do not have sufficient permissions', 'studiou-wc-product-cat-manage')
                ));
            }

            // Validate input
            if (!isset($_POST['product_ids'])) {
                wp_send_json_error(array(
                    'message' => __('Missing required parameters', 'studiou-wc-product-cat-manage')
                ));
            }

            $product_ids = array_map('intval', $_POST['product_ids']);

            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Processing batch of ' . count($product_ids) . ' products for deletion');
            }

            // Delete products in this batch
            $result = $this->delete_products_batch($product_ids);

            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Batch deletion completed - Deleted: ' . $result['deleted_count'] . ', Failed: ' . $result['failed_count']);
            }

            // Clean any output that might have been generated
            ob_clean();

            // Return success response
            wp_send_json_success(array(
                'deleted_count' => $result['deleted_count'],
                'failed_count' => $result['failed_count'],
                'failed_products' => $result['failed_products']
            ));

        } catch (Exception $e) {
            // Debug logging
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('STUDIOU WC: Delete products batch operation failed - ' . $e->getMessage());
                error_log('STUDIOU WC: Exception trace: ' . $e->getTraceAsString());
            }

            // Clean any output that might have been generated
            ob_clean();

            wp_send_json_error(array(
                'message' => sprintf(
                    __('Error during delete batch operation: %s', 'studiou-wc-product-cat-manage'),
                    $e->getMessage()
                )
            ));
        } finally {
            // Restore original error reporting
            error_reporting($original_error_reporting);

            // End the output buffer
            ob_end_clean();
        }
    }

    /**
     * Get all products from multiple categories
     *
     * @param array $category_ids Array of category IDs
     * @return array Array of unique product IDs (including parent products with variants)
     */
    private function get_products_from_categories($category_ids) {
        global $wpdb;

        if (empty($category_ids)) {
            return array();
        }

        $placeholders = implode(',', array_fill(0, count($category_ids), '%d'));

        // Get all products from selected categories (both simple and parent products)
        $sql = "
            SELECT DISTINCT p.ID
            FROM {$wpdb->posts} p
            INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
            WHERE tt.term_id IN ($placeholders)
            AND tt.taxonomy = 'product_cat'
            AND p.post_type = 'product'
            AND p.post_status IN ('publish', 'private', 'draft', 'pending', 'trash')
        ";

        $products = $wpdb->get_col($wpdb->prepare($sql, $category_ids));

        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('STUDIOU WC: Found ' . count($products) . ' products in categories ' . implode(', ', $category_ids));
        }

        return $products ? array_map('intval', $products) : array();
    }

    /**
     * Delete a batch of products and their variants
     *
     * @param array $product_ids Array of product IDs to delete
     * @return array Result with counts
     */
    private function delete_products_batch($product_ids) {
        $deleted_count = 0;
        $failed_count = 0;
        $failed_products = array();

        foreach ($product_ids as $product_id) {
            try {
                // Get the product object
                $product = wc_get_product($product_id);

                if (!$product) {
                    $failed_count++;
                    $failed_products[] = $product_id;
                    if (defined('WP_DEBUG') && WP_DEBUG) {
                        error_log('STUDIOU WC: Product ' . $product_id . ' not found');
                    }
                    continue;
                }

                // If it's a variable product, delete all variations first
                if ($product->is_type('variable')) {
                    $variations = $product->get_children();
                    if (defined('WP_DEBUG') && WP_DEBUG) {
                        error_log('STUDIOU WC: Deleting ' . count($variations) . ' variations for product ' . $product_id);
                    }

                    foreach ($variations as $variation_id) {
                        $variation = wc_get_product($variation_id);
                        if ($variation) {
                            $variation->delete(true); // true = force delete permanently
                        }
                    }
                }

                // Delete the product permanently
                $result = $product->delete(true); // true = force delete permanently

                if ($result) {
                    $deleted_count++;
                    if (defined('WP_DEBUG') && WP_DEBUG) {
                        error_log('STUDIOU WC: Successfully deleted product ' . $product_id);
                    }
                } else {
                    $failed_count++;
                    $failed_products[] = $product_id;
                    if (defined('WP_DEBUG') && WP_DEBUG) {
                        error_log('STUDIOU WC: Failed to delete product ' . $product_id);
                    }
                }

            } catch (Exception $e) {
                $failed_count++;
                $failed_products[] = $product_id;
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    error_log('STUDIOU WC: Exception deleting product ' . $product_id . ': ' . $e->getMessage());
                }
            }
        }

        return array(
            'deleted_count' => $deleted_count,
            'failed_count' => $failed_count,
            'failed_products' => $failed_products
        );
    }
}