<?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_action('wp_ajax_studiou_wcpcm_clear_media', array($this, 'handle_clear_media_ajax'));
        add_action('wp_ajax_studiou_wcpcm_clear_media_batch', array($this, 'handle_clear_media_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, delete_products, and clear_media');
        }
    }
    
    /**
     * 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
        );
    }

    /**
     * Get all media categories with file counts for select lists
     *
     * @return array Array of media categories with ID, name, and file count
     */
    public function get_media_categories_with_counts() {
        $taxonomy = $this->get_media_category_taxonomy();

        if (!$taxonomy) {
            return array();
        }

        $args = array(
            'taxonomy' => $taxonomy,
            'orderby' => 'name',
            'hide_empty' => false,
        );

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

        if (!is_wp_error($categories)) {
            foreach ($categories as $category) {
                $file_count = $this->get_media_category_file_count($category->term_id, $taxonomy);

                $categories_with_counts[] = array(
                    'id' => $category->term_id,
                    'name' => $category->name,
                    'count' => $file_count,
                    'display_name' => sprintf('%s (%d %s)', $category->name, $file_count, __('files', 'studiou-wc-product-cat-manage'))
                );
            }
        }

        return $categories_with_counts;
    }

    /**
     * Get the media category taxonomy name
     *
     * Checks for common media category taxonomies used by popular plugins
     *
     * @return string|false Taxonomy name or false if none found
     */
    private function get_media_category_taxonomy() {
        $possible_taxonomies = array(
            'media_category',
            'attachment_category',
            'media-category',
        );

        foreach ($possible_taxonomies as $taxonomy) {
            if (taxonomy_exists($taxonomy)) {
                error_log('STUDIOU WC MEDIA: Detected media taxonomy: ' . $taxonomy);
                return $taxonomy;
            }
        }

        // Try to find any taxonomy registered for attachments
        $attachment_taxonomies = get_object_taxonomies('attachment', 'names');
        if (!empty($attachment_taxonomies)) {
            // Filter out built-in taxonomies
            $custom_taxonomies = array_filter($attachment_taxonomies, function($tax) {
                return !in_array($tax, array('post_tag', 'category', 'post_format'));
            });
            if (!empty($custom_taxonomies)) {
                $found = reset($custom_taxonomies);
                error_log('STUDIOU WC MEDIA: Auto-detected attachment taxonomy: ' . $found);
                return $found;
            }
        }

        error_log('STUDIOU WC MEDIA: No media category taxonomy found. Registered attachment taxonomies: ' . implode(', ', $attachment_taxonomies ?? array()));
        return false;
    }

    /**
     * Get file count for a media category
     *
     * @param int $category_id Category term ID
     * @param string $taxonomy Taxonomy name
     * @return int File count
     */
    private function get_media_category_file_count($category_id, $taxonomy) {
        global $wpdb;

        $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 = %s
            AND p.post_type = 'attachment'
        ";

        $count = $wpdb->get_var($wpdb->prepare($sql, $category_id, $taxonomy));

        return intval($count);
    }

    /**
     * Get all media IDs from multiple media categories
     *
     * @param array $category_ids Array of category term IDs
     * @return array Array of unique attachment IDs
     */
    private function get_media_from_categories($category_ids) {
        global $wpdb;

        $taxonomy = $this->get_media_category_taxonomy();

        error_log('STUDIOU WC MEDIA: get_media_from_categories called - taxonomy: ' . ($taxonomy ?: 'false') . ', category_ids: ' . implode(', ', $category_ids));

        if (!$taxonomy || empty($category_ids)) {
            error_log('STUDIOU WC MEDIA: No taxonomy or empty category_ids, returning empty');
            return array();
        }

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

        $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 = %s
            AND p.post_type = 'attachment'
        ";

        $params = array_merge($category_ids, array($taxonomy));
        $media = $wpdb->get_col($wpdb->prepare($sql, ...$params));

        error_log('STUDIOU WC MEDIA: Query found ' . ($media ? count($media) : 0) . ' media files');

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

    /**
     * Delete a batch of media files permanently
     *
     * @param array $media_ids Array of attachment IDs to delete
     * @return array Result with counts
     */
    private function delete_media_batch($media_ids) {
        $deleted_count = 0;
        $failed_count = 0;
        $failed_media = array();
        $affected_term_ids = array();
        $taxonomy = $this->get_media_category_taxonomy();

        error_log('STUDIOU WC MEDIA: delete_media_batch called with ' . count($media_ids) . ' media IDs');

        foreach ($media_ids as $media_id) {
            try {
                $attachment = get_post($media_id);

                if (!$attachment || $attachment->post_type !== 'attachment') {
                    $failed_count++;
                    $failed_media[] = $media_id;
                    error_log('STUDIOU WC MEDIA: Media file ' . $media_id . ' not found or not an attachment');
                    continue;
                }

                // Collect affected term IDs before deletion for count update
                if ($taxonomy) {
                    $terms = wp_get_object_terms($media_id, $taxonomy, array('fields' => 'ids'));
                    if (!is_wp_error($terms)) {
                        $affected_term_ids = array_merge($affected_term_ids, $terms);
                    }
                }

                // Force delete attachment and its files from disk
                $result = wp_delete_attachment($media_id, true);

                if ($result) {
                    $deleted_count++;
                    error_log('STUDIOU WC MEDIA: Successfully deleted media file ' . $media_id);
                } else {
                    $failed_count++;
                    $failed_media[] = $media_id;
                    error_log('STUDIOU WC MEDIA: Failed to delete media file ' . $media_id);
                }

            } catch (Exception $e) {
                $failed_count++;
                $failed_media[] = $media_id;
                error_log('STUDIOU WC MEDIA: Exception deleting media file ' . $media_id . ': ' . $e->getMessage());
            }
        }

        // Force update term counts for affected categories
        if ($taxonomy && !empty($affected_term_ids)) {
            $affected_term_ids = array_unique($affected_term_ids);
            wp_update_term_count_now($affected_term_ids, $taxonomy);
            error_log('STUDIOU WC MEDIA: Updated term counts for term IDs: ' . implode(', ', $affected_term_ids));
        }

        error_log('STUDIOU WC MEDIA: Batch result - deleted: ' . $deleted_count . ', failed: ' . $failed_count);

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

    /**
     * Handle AJAX clear media request (initial request to get media IDs)
     */
    public function handle_clear_media_ajax() {
        while (ob_get_level()) {
            ob_end_clean();
        }

        ob_start();

        $original_error_reporting = error_reporting();
        error_reporting(0);

        error_log('STUDIOU WC MEDIA: Clear media AJAX handler called');
        error_log('STUDIOU WC MEDIA: POST data: ' . print_r($_POST, true));

        try {
            if (!isset($_POST['nonce']) || !wp_verify_nonce($_POST['nonce'], 'studiou-wcpcm-nonce')) {
                error_log('STUDIOU WC MEDIA: Nonce verification failed');
                wp_send_json_error(array(
                    'message' => __('Security check failed', 'studiou-wc-product-cat-manage')
                ));
                return;
            }

            if (!current_user_can('manage_woocommerce')) {
                error_log('STUDIOU WC MEDIA: Permission check failed');
                wp_send_json_error(array(
                    'message' => __('You do not have sufficient permissions', 'studiou-wc-product-cat-manage')
                ));
                return;
            }

            if (!isset($_POST['media_categories'])) {
                error_log('STUDIOU WC MEDIA: media_categories not in POST data');
                wp_send_json_error(array(
                    'message' => __('Missing required parameters', 'studiou-wc-product-cat-manage')
                ));
                return;
            }

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

            if (empty($category_ids)) {
                error_log('STUDIOU WC MEDIA: Empty category_ids after intval mapping');
                wp_send_json_error(array(
                    'message' => __('Please select at least one category', 'studiou-wc-product-cat-manage')
                ));
                return;
            }

            $media_ids = $this->get_media_from_categories($category_ids);

            error_log('STUDIOU WC MEDIA: Found ' . count($media_ids) . ' media files to delete from categories: ' . implode(', ', $category_ids));

            ob_clean();

            wp_send_json_success(array(
                'media_ids' => $media_ids,
                'total_count' => count($media_ids),
                'message' => sprintf(
                    __('Found %d media files to delete', 'studiou-wc-product-cat-manage'),
                    count($media_ids)
                )
            ));

        } catch (Exception $e) {
            error_log('STUDIOU WC MEDIA: Clear media operation failed - ' . $e->getMessage());
            error_log('STUDIOU WC MEDIA: Exception trace: ' . $e->getTraceAsString());

            ob_clean();

            wp_send_json_error(array(
                'message' => sprintf(
                    __('Error during clear media operation: %s', 'studiou-wc-product-cat-manage'),
                    $e->getMessage()
                )
            ));
        } finally {
            error_reporting($original_error_reporting);
            ob_end_clean();
        }
    }

    /**
     * Handle AJAX clear media batch request (processes media in batches)
     */
    public function handle_clear_media_batch_ajax() {
        while (ob_get_level()) {
            ob_end_clean();
        }

        ob_start();

        $original_error_reporting = error_reporting();
        error_reporting(0);

        error_log('STUDIOU WC MEDIA: Clear media batch AJAX handler called');

        try {
            if (!isset($_POST['nonce']) || !wp_verify_nonce($_POST['nonce'], 'studiou-wcpcm-nonce')) {
                error_log('STUDIOU WC MEDIA: Batch nonce verification failed');
                wp_send_json_error(array(
                    'message' => __('Security check failed', 'studiou-wc-product-cat-manage')
                ));
                return;
            }

            if (!current_user_can('manage_woocommerce')) {
                error_log('STUDIOU WC MEDIA: Batch permission check failed');
                wp_send_json_error(array(
                    'message' => __('You do not have sufficient permissions', 'studiou-wc-product-cat-manage')
                ));
                return;
            }

            if (!isset($_POST['media_ids'])) {
                error_log('STUDIOU WC MEDIA: media_ids not in POST data');
                wp_send_json_error(array(
                    'message' => __('Missing required parameters', 'studiou-wc-product-cat-manage')
                ));
                return;
            }

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

            error_log('STUDIOU WC MEDIA: Processing batch of ' . count($media_ids) . ' media files for deletion');

            $result = $this->delete_media_batch($media_ids);

            error_log('STUDIOU WC MEDIA: Batch completed - Deleted: ' . $result['deleted_count'] . ', Failed: ' . $result['failed_count']);

            ob_clean();

            wp_send_json_success(array(
                'deleted_count' => $result['deleted_count'],
                'failed_count' => $result['failed_count'],
                'failed_media' => $result['failed_media']
            ));

        } catch (Exception $e) {
            error_log('STUDIOU WC MEDIA: Clear media batch operation failed - ' . $e->getMessage());
            error_log('STUDIOU WC MEDIA: Exception trace: ' . $e->getTraceAsString());

            ob_clean();

            wp_send_json_error(array(
                'message' => sprintf(
                    __('Error during clear media batch operation: %s', 'studiou-wc-product-cat-manage'),
                    $e->getMessage()
                )
            ));
        } finally {
            error_reporting($original_error_reporting);
            ob_end_clean();
        }
    }
}