<?php
class FFLCockpit_Update_Processor {

    /**
     * Product Processed Hook: g_ffl_cockpit_products_processed
     * ---------------------------------------------------------
     * FFL Cockpit fires a single batch action after products are inserted, updated,
     * or deleted during a sync. This hook fires on WordPress's 'shutdown' action,
     * AFTER the sync response has been sent to the client, so it has zero impact
     * on sync performance.
     *
     * @hook g_ffl_cockpit_products_processed
     * @param array $products Array of processed products. Each element contains:
     *   - 'product_id' (int)    WooCommerce product (post) ID
     *   - 'action'     (string) The operation performed: 'insert', 'update', or 'delete'
     *   - 'status'     (string) The result: 'success', 'partial_success', or 'deleted'
     *
     * Only successfully processed products are included. Failed operations and
     * rolled-back transactions are excluded from the array.
     *
     * Usage example:
     *
     *   add_action('g_ffl_cockpit_products_processed', function($products) {
     *       $updated_ids = array_column($products, 'product_id');
     *       // Push all changed product IDs to a search engine in one bulk call
     *       MySearchSync::bulk_update($updated_ids);
     *   });
     *
     * Performance guidelines for listeners:
     *
     *   FFL Cockpit syncs can process hundreds of products per request. To avoid
     *   degrading the request lifecycle, listeners should:
     *
     *   - Make a single bulk API call with all product IDs (recommended)
     *   - Or write product IDs to a custom queue table and process via WP Cron
     *   - Or use wp_schedule_single_event() to defer work to a background job
     *
     *   Do NOT make individual HTTP requests per product inside this hook.
     */

    /** @var array Queued product notifications, fired on shutdown. */
    private static $processed_queue = [];

    /** @var bool Whether the shutdown hook has been registered. */
    private static $shutdown_registered = false;

    /**
     * Queue a product notification to fire on shutdown (non-blocking).
     */
    private static function queue_product_notification($product_id, $action, $status) {
        self::$processed_queue[] = [
            'product_id' => $product_id,
            'action' => $action,
            'status' => $status,
        ];
        if (!self::$shutdown_registered) {
            self::$shutdown_registered = true;
            add_action('shutdown', [__CLASS__, 'fire_product_notifications']);
        }
    }

    /**
     * Fires queued product notifications on shutdown.
     */
    public static function fire_product_notifications() {
        if (empty(self::$processed_queue)) return;

        do_action('g_ffl_cockpit_products_processed', self::$processed_queue);

        self::$processed_queue = [];
    }

    /**
     * Processes a product based on the specified action.
     *
     * @param array  $data   The product data.
     * @param string $action The action to perform: 'insert', 'update', or 'delete'.
     * @return array The result of the processing.
     */
    public static function process_product($data, $action) {
        $created_attachment_ids = [];
        try {
            // Validate action
            if (!in_array($action, ['insert', 'update', 'delete'])) {
                throw new Exception('Invalid action specified.');
            }

            // Route update and delete operations to SQL processor for better performance
            if (self::is_sql_action_supported($action)) {
                return self::process_product_sql($data, $action);
            }

            // Only 'insert' action should reach this point (updates/deletes are handled by SQL processor above)
            if ($action !== 'insert') {
                throw new Exception("Unsupported action '{$action}' for WordPress API processor. Use 'insert' for new products or ensure SQL processor handles updates/deletes.");
            }

            // Handle product insertion using WordPress/WooCommerce APIs (insert only - updates/deletes use SQL processor)
            $product = new WC_Product_Simple();
            if (!$product) {
                throw new Exception('Failed to create new product.');
            }

            // Set product properties
            if (isset($data['sku'])) $product->set_sku($data['sku']);
            if (isset($data['name'])) $product->set_name($data['name']);
            if (isset($data['description'])) $product->set_description($data['description']);
            if (isset($data['short_description'])) $product->set_short_description($data['short_description']);
            if (isset($data['regular_price'])) $product->set_regular_price($data['regular_price']);
            if (isset($data['sale_price'])) $product->set_sale_price($data['sale_price']);
            if (isset($data['stock_quantity'])) {
                // Allow explicit manage_stock override, otherwise default to 'true' when stock_quantity provided
                if (array_key_exists('manage_stock', $data)) {
                    $product->set_manage_stock($data['manage_stock']);
                } else {
                    $product->set_manage_stock(true);
                }
                $product->set_stock_quantity($data['stock_quantity']);
            }

            // Handle shipping class
            if (!empty($data['shipping_class'])) {
                self::assign_shipping_class($product, $data['shipping_class']);
            }

            // Handle product attributes
            if (!empty($data['attributes']) && is_array($data['attributes'])) {
                self::assign_product_attributes($product, $data['attributes']);
            }

            if (isset($data['meta_data'])) {
                foreach ($data['meta_data'] as $meta) {
                    $product->update_meta_data($meta['key'], $meta['value']);
                }
            }
            
            // Handle images array (either JSON string or already-decoded array)
            // IMPORTANT: For inserts, a product must have images. Do not create the product unless image processing succeeds.
            $require_images = ($action === 'insert');
            if ($require_images) {
                if (!array_key_exists('images', $data) || empty($data['images'])) {
                    throw new Exception('Product insert requires images; aborting product insert.');
                }
                $images = is_string($data['images']) ? json_decode($data['images'], true) : $data['images'];

                if (is_string($data['images']) && json_last_error() !== JSON_ERROR_NONE) {
                    throw new Exception('Invalid images JSON provided; aborting product insert.');
                }

                if (!is_array($images) || empty($images)) {
                    throw new Exception('Images were provided but could not be parsed; aborting product insert.');
                }

                $gallery_image_ids = [];
                $processed_any = false;

                foreach ($images as $index => $image_data) {
                    if (empty($image_data['src'])) {
                        continue;
                    }

                    $processed_any = true;
                    $src = $image_data['src'];

                    $attachment_id = self::upload_image_from_url($src);
                    if (!$attachment_id || !is_numeric($attachment_id)) {
                        throw new Exception("Image processing failed for {$src}; aborting product insert.");
                    }

                    $attachment_id = (int) $attachment_id;
                    $created_attachment_ids[] = $attachment_id;

                    if ($index === 0) {
                        $product->set_image_id($attachment_id);
                    } else {
                        $gallery_image_ids[] = $attachment_id;
                    }
                }

                if (!$processed_any) {
                    throw new Exception('Images were provided but contained no valid image URLs; aborting product insert.');
                }

                if (!empty($gallery_image_ids)) {
                    $product->set_gallery_image_ids($gallery_image_ids);
                }

                // Require a featured image when images are provided.
                if (!$product->get_image_id()) {
                    throw new Exception('Featured image could not be set; aborting product insert.');
                }
            }
            
            // Save the product (insert only - set default status)
            if (!empty($data['status'])) {
                $product->set_status($data['status']);
            } else {
                $product->set_status('publish');
            }

            # Save the product
            $product_id = $product->save();

            # Now do the updates post-save for insert
            // Now update the slug, if provided
            if (isset($data['slug'])) {
                wp_update_post([
                    'ID' => $product_id,
                    'post_name' => sanitize_title($data['slug']),
                ]);
            }

            // Now apply categories if needed
            // Set or update product categories by ID
            if (!empty($data['categories']) && is_array($data['categories'])) {
                $category_ids = [];

                foreach ($data['categories'] as $cat) {
                    if (isset($cat['id']) && is_numeric($cat['id'])) {
                        $category_ids[] = (int) $cat['id'];
                    }
                }

                if (!empty($category_ids)) {
                    wp_set_object_terms($product_id, $category_ids, 'product_cat');
                }
            }

            // Set or update product tags by ID
            if (!empty($data['tags']) && is_array($data['tags'])) {
                $tag_ids = [];

                foreach ($data['tags'] as $tag) {
                    if (isset($tag['id']) && is_numeric($tag['id'])) {
                        $tag_ids[] = (int) $tag['id'];
                    }
                }

                if (!empty($tag_ids)) {
                    wp_set_object_terms($product_id, $tag_ids, 'product_tag');
                }
            }
            
            self::queue_product_notification($product_id, $action, 'success');
            return ['status' => 'success', 'product_id' => $product_id];

        } catch (Exception $e) {
            // If we downloaded/created attachments but are aborting insert, clean them up.
            if (!empty($created_attachment_ids)) {
                foreach ($created_attachment_ids as $attachment_id) {
                    if (is_numeric($attachment_id)) {
                        wp_delete_attachment((int) $attachment_id, true);
                    }
                }
            }
            g_ffl_debug_log("Product processing failed: " . $e->getMessage() . ' | Data: ' . json_encode($data), 'error');
            return ['status' => 'error', 'message' => $e->getMessage()];
        }
    }

    /**
     * Assigns a shipping class to a product.
     *
     * @param WC_Product $product        The product object.
     * @param string     $shipping_class The shipping class name.
     */
    private static function assign_shipping_class($product, $shipping_class) {
        $slug = sanitize_title($shipping_class);
        $term = get_term_by('slug', $slug, 'product_shipping_class');

        if (!$term || is_wp_error($term)) {
            $result = wp_insert_term($shipping_class, 'product_shipping_class', ['slug' => $slug]);
            if (!is_wp_error($result) && isset($result['term_id'])) {
                $term_id = $result['term_id'];
            } else {
                g_ffl_debug_log("Failed to create shipping class: " . $shipping_class, 'error');
                return;
            }
        } else {
            $term_id = $term->term_id;
        }

        if (!empty($term_id)) {
            $product->set_shipping_class_id($term_id);
        }
    }

    /**
     * Assigns attributes to a product.
     *
     * @param WC_Product $product   The product object.
     * @param array      $attributes The attributes to assign.
     */
    private static function assign_product_attributes($product, $attributes) {
        $product_attributes = [];

        foreach ($attributes as $attr) {
            if (empty($attr['id']) || empty($attr['slug']) || empty($attr['options'])) continue;

            $attribute = new WC_Product_Attribute();
            $attribute->set_id($attr['id']);
            $attribute->set_name($attr['slug']);
            $attribute->set_options($attr['options']);
            $attribute->set_visible(!empty($attr['visible']));
            $attribute->set_variation(!empty($attr['variation']));
            if (isset($attr['position'])) {
                $attribute->set_position((int)$attr['position']);
            }

            $product_attributes[] = $attribute;
        }

        $product->set_attributes($product_attributes);
    }

    private static function upload_image_from_url($url) {
        g_ffl_debug_log("Starting upload_image_from_url for: " . $url, 'debug');
        
        require_once ABSPATH . 'wp-admin/includes/file.php';
        require_once ABSPATH . 'wp-admin/includes/media.php';
        require_once ABSPATH . 'wp-admin/includes/image.php';

        $tmp = download_url($url);
        if (is_wp_error($tmp)) {
            g_ffl_debug_log("download_url failed for {$url}: " . $tmp->get_error_message(), 'error');
            return false;
        }
        
        g_ffl_debug_log("Downloaded {$url} to temporary file: " . $tmp, 'debug');

        $file_array = [
            'name'     => basename($url),
            'tmp_name' => $tmp
        ];
        
        g_ffl_debug_log("File array for media_handle_sideload: " . json_encode($file_array), 'debug');

        $attachment_id = media_handle_sideload($file_array, 0);
        if (is_wp_error($attachment_id)) {
            g_ffl_debug_log("media_handle_sideload failed for {$url}: " . $attachment_id->get_error_message(), 'error');
            wp_delete_file($tmp);
            return false;
        }

        g_ffl_debug_log("Successfully created attachment {$attachment_id} for {$url}", 'debug');
        return $attachment_id;
    }

    /**
     * Check if an action is supported by the SQL processor
     *
     * @param string $action The action to check
     * @return bool True if supported, false otherwise
     */
    public static function is_sql_action_supported($action) {
        return in_array($action, ['update', 'delete']);
    }

    /**
     * Get supported actions for SQL processor
     *
     * @return array Array of supported actions
     */
    public static function get_sql_supported_actions() {
        return ['update', 'delete'];
    }

    /**
     * Rebuild WooCommerce lookup tables and clear caches after bulk operations
     * Call this once after processing multiple products for optimal performance
     * 
     * For bulk operations, wrap your processing with:
     * wp_defer_term_counting(true);
     * // process all products
     * FFLCockpit_Update_Processor::rebuild_woocommerce_data();
     * wp_defer_term_counting(false);
     *
     * @return void
     */
    public static function rebuild_woocommerce_data() {
        // Rebuild WooCommerce lookup tables (handles pricing, stock, etc.)
        if (function_exists('wc_update_product_lookup_tables')) {
            wc_update_product_lookup_tables(); // No args = rebuild everything in batches
        }

        // Clear any relevant caches
        if (function_exists('wc_delete_product_transients')) {
            wc_delete_product_transients();
        }
    }

    /**
     * Cached table existence check for hot sync paths.
     *
     * Table availability does not change during a single request, so repeated
     * SHOW TABLES calls add avoidable DB load when processing large batches.
     */
    private static function table_exists($table_name) {
        global $wpdb;

        static $table_exists_cache = [];

        if (!isset($table_exists_cache[$table_name])) {
            $table_exists_cache[$table_name] = $wpdb->get_var(
                $wpdb->prepare("SHOW TABLES LIKE %s", $table_name)
            ) === $table_name;
        }

        return $table_exists_cache[$table_name];
    }

    /**
     * Check for and clean up SKU conflicts in WooCommerce lookup tables
     * This helps resolve issues where SKUs appear to conflict but the products don't actually exist
     *
     * @param string|null $specific_sku Optional specific SKU to check, otherwise checks all
     * @return array Report of findings and cleanup actions
     */
    public static function cleanup_sku_conflicts($specific_sku = null) {
        global $wpdb;
        
        $report = [
            'checked_skus' => 0,
            'orphaned_lookup_entries' => 0,
            'orphaned_meta_entries' => 0,
            'cleaned_up' => [],
            'errors' => []
        ];

        try {
            // Build WHERE clause for specific SKU or all SKUs
            $sku_where = '';
            $sku_params = [];
            if ($specific_sku) {
                $sku_where = "AND sku = %s";
                $sku_params[] = $specific_sku;
                g_ffl_debug_log("Checking specific SKU: {$specific_sku}", 'debug');
            }

            // WooCommerce stores variation SKUs and lookup rows on product_variation posts.
            // Treating only post_type=product as valid will strip variation lookup rows/SKUs.
            $valid_product_post_types_sql = "'product', 'product_variation'";
            $invalid_lookup_product_sql = "(p.ID IS NULL OR p.post_type NOT IN ({$valid_product_post_types_sql}) OR p.post_status = 'trash')";
            $orphaned_sku_meta_sql = "(p.ID IS NULL OR (p.post_type IN ({$valid_product_post_types_sql}) AND p.post_status = 'trash'))";

            // Check WooCommerce product lookup table if it exists
            $lookup_table = $wpdb->prefix . 'wc_product_meta_lookup';
            $table_exists = self::table_exists($lookup_table);
            
            if ($table_exists) {
                // Find lookup entries where the product/variation does not exist or is trashed.
                // Batch in chunks of 500 to keep each query bounded — full-table scan of
                // the lookup + posts left-join can be multi-second on large stores.
                $batch_size = 500;
                $max_batches = 200; // hard ceiling against accidental runaway loops

                for ($batch = 0; $batch < $max_batches; $batch++) {
                    if ($specific_sku) {
                        $orphaned_lookup = $wpdb->get_results($wpdb->prepare("
                            SELECT pml.product_id, pml.sku
                            FROM `{$wpdb->prefix}wc_product_meta_lookup` pml
                            LEFT JOIN {$wpdb->posts} p ON pml.product_id = p.ID
                            WHERE {$invalid_lookup_product_sql}
                            AND sku = %s
                            LIMIT %d
                        ", $specific_sku, $batch_size));
                    } else {
                        $orphaned_lookup = $wpdb->get_results($wpdb->prepare("
                            SELECT pml.product_id, pml.sku
                            FROM `{$wpdb->prefix}wc_product_meta_lookup` pml
                            LEFT JOIN {$wpdb->posts} p ON pml.product_id = p.ID
                            WHERE {$invalid_lookup_product_sql}
                            LIMIT %d
                        ", $batch_size));
                    }

                    if (empty($orphaned_lookup)) {
                        break;
                    }

                    foreach ($orphaned_lookup as $orphan) {
                        g_ffl_debug_log("Found orphaned lookup entry: Product ID {$orphan->product_id}, SKU '{$orphan->sku}'", 'warning');

                        $deleted = $wpdb->delete(
                            $lookup_table,
                            ['product_id' => $orphan->product_id],
                            ['%d']
                        );

                        if ($deleted) {
                            $report['orphaned_lookup_entries']++;
                            $report['cleaned_up'][] = "Removed orphaned lookup entry for SKU '{$orphan->sku}' (Product ID: {$orphan->product_id})";
                            g_ffl_debug_log("Cleaned up orphaned lookup entry for SKU '{$orphan->sku}'", 'info');
                        } else {
                            $report['errors'][] = "Failed to remove orphaned lookup entry for SKU '{$orphan->sku}'";
                        }
                    }

                    // If the specific-SKU branch returned a batch, no need to loop —
                    // a single SKU can only match a finite (and small) number of rows.
                    if ($specific_sku || count($orphaned_lookup) < $batch_size) {
                        break;
                    }
                }
            }

            // Check for orphaned SKU meta entries. Batch the scan in chunks of 500 —
            // postmeta + posts LEFT JOIN with no LIMIT can blow past gateway timeouts
            // on stores with millions of meta rows.
            $batch_size = 500;
            $max_batches = 200;

            for ($batch = 0; $batch < $max_batches; $batch++) {
                if ($specific_sku) {
                    $orphaned_meta = $wpdb->get_results($wpdb->prepare("
                        SELECT pm.post_id, pm.meta_value as sku
                        FROM {$wpdb->postmeta} pm
                        LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                        WHERE pm.meta_key = '_sku'
                        AND pm.meta_value != ''
                        AND {$orphaned_sku_meta_sql}
                        AND pm.meta_value = %s
                        LIMIT %d
                    ", $specific_sku, $batch_size));
                } else {
                    $orphaned_meta = $wpdb->get_results($wpdb->prepare("
                        SELECT pm.post_id, pm.meta_value as sku
                        FROM {$wpdb->postmeta} pm
                        LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                        WHERE pm.meta_key = '_sku'
                        AND pm.meta_value != ''
                        AND {$orphaned_sku_meta_sql}
                        LIMIT %d
                    ", $batch_size));
                }

                if (empty($orphaned_meta)) {
                    break;
                }

                foreach ($orphaned_meta as $orphan) {
                    g_ffl_debug_log("Found orphaned meta entry: Post ID {$orphan->post_id}, SKU '{$orphan->sku}'", 'warning');

                    $deleted = $wpdb->delete(
                        $wpdb->postmeta,
                        [
                            'post_id' => $orphan->post_id,
                            'meta_key' => '_sku'
                        ],
                        ['%d', '%s']
                    );

                    if ($deleted) {
                        $report['orphaned_meta_entries']++;
                        $report['cleaned_up'][] = "Removed orphaned meta entry for SKU '{$orphan->sku}' (Post ID: {$orphan->post_id})";
                        g_ffl_debug_log("Cleaned up orphaned meta entry for SKU '{$orphan->sku}'", 'info');
                    } else {
                        $report['errors'][] = "Failed to remove orphaned meta entry for SKU '{$orphan->sku}'";
                    }
                }

                if ($specific_sku || count($orphaned_meta) < $batch_size) {
                    break;
                }
            }

            // If checking a specific SKU, also verify no current conflicts exist
            if ($specific_sku) {
                // Check if SKU exists in active products or variations
                $active_products = $wpdb->get_results($wpdb->prepare("
                    SELECT p.ID, p.post_title, pm.meta_value as sku
                    FROM {$wpdb->posts} p
                    INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
                    WHERE p.post_type IN ({$valid_product_post_types_sql})
                    AND p.post_status NOT IN ('trash', 'auto-draft')
                    AND pm.meta_key = '_sku'
                    AND pm.meta_value = %s
                ", $specific_sku));

                if (!empty($active_products)) {
                    $report['active_conflicts'] = [];
                    foreach ($active_products as $product) {
                        $report['active_conflicts'][] = [
                            'id' => $product->ID,
                            'title' => $product->post_title,
                            'sku' => $product->sku
                        ];
                        g_ffl_debug_log("SKU '{$specific_sku}' is currently used by active product: {$product->ID} - {$product->post_title}", 'warning');
                    }
                } else {
                    $report['sku_available'] = true;
                    g_ffl_debug_log("SKU '{$specific_sku}' is now available for use", 'info');
                }

                $report['checked_skus'] = 1;
            }

            // Clear relevant caches after cleanup
            if ($report['orphaned_lookup_entries'] > 0 || $report['orphaned_meta_entries'] > 0) {
                wp_cache_flush();
                if (function_exists('wc_delete_product_transients')) {
                    wc_delete_product_transients();
                }
                g_ffl_debug_log("Cleared caches after SKU cleanup", 'debug');
            }

        } catch (Exception $e) {
            $report['errors'][] = 'Error during SKU cleanup: ' . $e->getMessage();
            g_ffl_debug_log('SKU cleanup error: ' . $e->getMessage(), 'error');
        }

        return $report;
    }

    /**
     * Pure SQL processor for product operations - faster than WP/WC APIs
     * 
     * IMPORTANT: This method only supports 'update' and 'delete' operations.
     * For product insertions, use the regular process_product() method.
     *
     * @param array  $data   The product data.
     * @param string $action The action to perform: 'update' or 'delete' ONLY.
     * @return array The result of the processing.
     */
    public static function process_product_sql($data, $action) {
        global $wpdb;
        
        try {
            // Validate action - SQL processor ONLY supports update and delete
            if (!in_array($action, ['update', 'delete'])) {
                throw new Exception("Invalid action '{$action}' specified. SQL processor only supports 'update' and 'delete' operations. Use process_product() method for 'insert' operations.");
            }

            // Validate product ID is required for both update and delete
            if (empty($data['id']) || !is_numeric($data['id'])) {
                throw new Exception('Valid product ID is required for SQL processor operations.');
            }

            $product_id = (int) $data['id'];

            // Verify product exists and is a product post type
            $post_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT ID FROM {$wpdb->posts} WHERE ID = %d AND post_type = 'product'",
                $product_id
            ));

            if (!$post_exists) {
                throw new Exception('Product not found.');
            }

            if ($action === 'delete') {
                return self::sql_delete_product($product_id);
            } else {
                return self::sql_update_product($product_id, $data);
            }

        } catch (Exception $e) {
            g_ffl_debug_log("SQL Product processing failed for product {$product_id}: " . $e->getMessage() . ' | Data: ' . json_encode($data), 'error');
            return [
                'status' => 'error', 
                'product_id' => $product_id,
                'message' => $e->getMessage(),
                'errors' => [$e->getMessage()]
            ];
        }
    }

    /**
     * Delete a product using pure SQL
     *
     * @param int $product_id The product ID to delete
     * @return array The result of the deletion
     */
    private static function sql_delete_product($product_id) {
        global $wpdb;

        $errors = [];
        $warnings = [];
        $deleted_items = [];

        try {
            // Define constant to prevent deletion hook from triggering
            if (!defined('G_FFL_COCKPIT_DOING_SYNC')) {
                define('G_FFL_COCKPIT_DOING_SYNC', true);
            }
            
            // Note: Transactions only work on InnoDB tables. Mixed table types in some WP installs
            // mean rollback isn't guaranteed if a table isn't transactional
            $wpdb->query('START TRANSACTION');

            // Get associated image IDs before deletion
            $thumbnail_id = $wpdb->get_var($wpdb->prepare(
                "SELECT meta_value FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = '_thumbnail_id'",
                $product_id
            ));

            $gallery_ids = $wpdb->get_var($wpdb->prepare(
                "SELECT meta_value FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = '_product_image_gallery'",
                $product_id
            ));

            $attachment_ids = [];
            if ($thumbnail_id) {
                $attachment_ids[] = $thumbnail_id;
            }
            if ($gallery_ids) {
                $gallery_ids_array = explode(',', $gallery_ids);
                $attachment_ids = array_merge($attachment_ids, $gallery_ids_array);
            }

            // Delete product meta
            $meta_result = $wpdb->delete($wpdb->postmeta, ['post_id' => $product_id], ['%d']);
            if ($meta_result === false) {
                $errors[] = 'Failed to delete product meta data';
            } else {
                $deleted_items[] = "meta_data ({$meta_result} records)";
            }

            // Get term taxonomy IDs before deleting relationships (needed for count updates)
            $affected_tt_ids = $wpdb->get_col($wpdb->prepare(
                "SELECT term_taxonomy_id FROM {$wpdb->term_relationships} WHERE object_id = %d",
                $product_id
            ));

            // Delete product term relationships
            $terms_result = $wpdb->delete($wpdb->term_relationships, ['object_id' => $product_id], ['%d']);
            if ($terms_result === false) {
                $errors[] = 'Failed to delete product term relationships';
            } else {
                $deleted_items[] = "term_relationships ({$terms_result} records)";
                
                // Update term counts for affected taxonomies
                if (!empty($affected_tt_ids) && function_exists('wp_update_term_count_now')) {
                    $placeholders = implode(',', array_fill(0, count($affected_tt_ids), '%d'));
                    $sql = "SELECT DISTINCT taxonomy FROM {$wpdb->term_taxonomy} 
                            WHERE term_taxonomy_id IN ($placeholders)";
                    $taxonomies_to_update = $wpdb->get_results(
                        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Dynamic placeholders for IN clause, prepared with spread operator
                        $wpdb->prepare($sql, ...$affected_tt_ids)
                    );
                    
                    foreach ($taxonomies_to_update as $tax_obj) {
                        $taxonomy = $tax_obj->taxonomy;
                        $placeholders_for_tax = implode(',', array_fill(0, count($affected_tt_ids), '%d'));
                        $sql = "SELECT term_taxonomy_id FROM {$wpdb->term_taxonomy}
                                WHERE taxonomy = %s 
                                AND term_taxonomy_id IN ($placeholders_for_tax)";

                        $tt_ids_for_taxonomy = $wpdb->get_col(
                            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Dynamic placeholders for IN clause, prepared with spread operator
                            $wpdb->prepare($sql, $taxonomy, ...$affected_tt_ids)
                        );
                        
                        if ($tt_ids_for_taxonomy) {
                            wp_update_term_count_now($tt_ids_for_taxonomy, $taxonomy);
                            $deleted_items[] = "updated_term_counts_{$taxonomy} (" . count($tt_ids_for_taxonomy) . " terms)";
                        }
                    }
                }
            }

            // Delete product comments (reviews)
            $comments_result = $wpdb->delete($wpdb->comments, ['comment_post_ID' => $product_id], ['%d']);
            if ($comments_result === false) {
                $errors[] = 'Failed to delete product comments';
            } else {
                $deleted_items[] = "comments ({$comments_result} records)";
            }

            // Delete WooCommerce lookup table entries to prevent orphaned data
            $lookup_table = $wpdb->prefix . 'wc_product_meta_lookup';
            $lookup_table_exists = self::table_exists($lookup_table);
            
            if ($lookup_table_exists) {
                $lookup_result = $wpdb->delete($lookup_table, ['product_id' => $product_id], ['%d']);
                if ($lookup_result === false) {
                    $warnings[] = 'Failed to delete WooCommerce lookup table entry';
                } else {
                    $deleted_items[] = "wc_lookup_table ({$lookup_result} records)";
                }
            }

            // Delete WooCommerce product attributes lookup table entries if exists
            $attributes_lookup_table = $wpdb->prefix . 'wc_product_attributes_lookup';
            $attributes_table_exists = self::table_exists($attributes_lookup_table);
            
            if ($attributes_table_exists) {
                $attributes_result = $wpdb->delete($attributes_lookup_table, ['product_id' => $product_id], ['%d']);
                if ($attributes_result === false) {
                    $warnings[] = 'Failed to delete WooCommerce attributes lookup table entry';
                } elseif ($attributes_result > 0) {
                    $deleted_items[] = "wc_attributes_lookup_table ({$attributes_result} records)";
                }
            }

            // Delete WooCommerce downloads table entries if exists
            $downloads_table = $wpdb->prefix . 'woocommerce_downloadable_product_permissions';
            $downloads_table_exists = self::table_exists($downloads_table);
            
            if ($downloads_table_exists) {
                $downloads_result = $wpdb->delete($downloads_table, ['product_id' => $product_id], ['%d']);
                if ($downloads_result === false) {
                    $warnings[] = 'Failed to delete WooCommerce downloads table entry';
                } elseif ($downloads_result > 0) {
                    $deleted_items[] = "wc_downloads_table ({$downloads_result} records)";
                }
            }

            // Delete the product post
            $deleted = $wpdb->delete($wpdb->posts, ['ID' => $product_id], ['%d']);

            if ($deleted === false) {
                $wpdb->query('ROLLBACK');
                throw new Exception('Failed to delete product from database.');
            }

            $deleted_items[] = "product_post";

            $wpdb->query('COMMIT');

            // Defer media cleanup: queue attachment IDs for gradual deletion
            // by FFLCockpit_Sync_Endpoint::drain_media_cleanup() on later sync
            // runs. Keeps the per-product delete fast and amortizes the heavy
            // wp_delete_attachment() work across runs.
            $queued_ids = array_values(array_filter(array_map('intval', $attachment_ids)));
            if (!empty($queued_ids) && class_exists('FFLCockpit_Sync_Endpoint')) {
                FFLCockpit_Sync_Endpoint::enqueue_media_cleanup($queued_ids, (int) $product_id);
                $deleted_items[] = "media_queued (" . implode(',', $queued_ids) . ")";
            }

            // Clear any caches
            wp_cache_delete($product_id, 'posts');
            wp_cache_delete($product_id, 'post_meta');

            $result = [
                'status' => 'deleted',
                'product_id' => $product_id,
                'deleted_items' => $deleted_items
            ];

            if (!empty($errors)) {
                $result['errors'] = $errors;
            }
            if (!empty($warnings)) {
                $result['warnings'] = $warnings;
            }

            self::queue_product_notification($product_id, 'delete', 'deleted');
            return $result;

        } catch (Exception $e) {
            $wpdb->query('ROLLBACK');

            return [
                'status' => 'error',
                'product_id' => $product_id,
                'message' => $e->getMessage(),
                'errors' => array_merge($errors, [$e->getMessage()]),
                'warnings' => $warnings
            ];
        }
    }

    /**
     * Update a product using pure SQL
     *
     * @param int   $product_id The product ID to update
     * @param array $data      The product data to update
     * @return array The result of the update
     */
    private static function sql_update_product($product_id, $data) {
        global $wpdb;

        $errors = [];
        $warnings = [];

        try {
            $wpdb->query('START TRANSACTION');

            $updated_fields = [];

            // Update post table fields
            $post_updates = [];
            if (isset($data['name'])) {
                $post_updates['post_title'] = sanitize_text_field($data['name']);
                $updated_fields[] = 'name';
            }
            if (isset($data['description'])) {
                $post_updates['post_content'] = wp_kses_post($data['description']);
                $updated_fields[] = 'description';
            }
            if (isset($data['short_description'])) {
                $post_updates['post_excerpt'] = wp_kses_post($data['short_description']);
                $updated_fields[] = 'short_description';
            }
            if (isset($data['slug'])) {
                $post_updates['post_name'] = sanitize_title($data['slug']);
                $updated_fields[] = 'slug';
            }
            if (isset($data['status'])) {
                $valid_statuses = ['publish', 'draft', 'private', 'pending'];
                if (in_array($data['status'], $valid_statuses)) {
                    $post_updates['post_status'] = $data['status'];
                    $updated_fields[] = 'status';
                } else {
                    $warnings[] = "Invalid status '{$data['status']}' ignored";
                }
            }

            if (!empty($post_updates)) {
                $post_updates['post_modified'] = current_time('mysql');
                $post_updates['post_modified_gmt'] = current_time('mysql', 1);
                
                $result = $wpdb->update(
                    $wpdb->posts,
                    $post_updates,
                    ['ID' => $product_id],
                    null,
                    ['%d']
                );

                if ($result === false) {
                    $errors[] = 'Failed to update product post data';
                    throw new Exception('Failed to update product post data.');
                }
            }

            // Update product meta fields
            $meta_updates = [
                'sku' => '_sku',
                'regular_price' => '_regular_price',
                'sale_price' => '_sale_price',
                'stock_quantity' => '_stock'
            ];

            $current_regular_price = null;
            $current_sale_price = null;

            foreach ($meta_updates as $data_key => $meta_key) {
                if (isset($data[$data_key])) {
                    $meta_value = $data[$data_key];
                    
                    try {
                        // Handle different data types appropriately
                        if ($data_key === 'sku') {
                            $meta_value = sanitize_text_field($meta_value);
                            g_ffl_debug_log("Updating SKU for product $product_id to '$meta_value'", 'info');
                            // Check for SKU uniqueness to prevent conflicts.
                            // Primary path follows WooCommerce's indexed lookup-table pattern;
                            // falls back to postmeta only if the lookup table is missing.
                            if (!empty($meta_value)) {
                                $conflict = null;
                                $lookup_table = $wpdb->prefix . 'wc_product_meta_lookup';
                                $lookup_exists = self::table_exists($lookup_table);
                                if ($lookup_exists) {
                                    $conflict = $wpdb->get_var($wpdb->prepare(
                                        "SELECT p.ID
                                         FROM {$wpdb->posts} p
                                         INNER JOIN `{$wpdb->prefix}wc_product_meta_lookup` lookup ON p.ID = lookup.product_id
                                         WHERE p.post_type IN ('product', 'product_variation')
                                           AND p.post_status != 'trash'
                                           AND lookup.sku = %s
                                           AND lookup.product_id <> %d
                                         LIMIT 1",
                                        $meta_value, $product_id
                                    ));
                                } else {
                                    $conflict = $wpdb->get_var($wpdb->prepare(
                                        "SELECT pm.post_id
                                         FROM {$wpdb->postmeta} pm
                                         JOIN {$wpdb->posts} p ON p.ID = pm.post_id
                                         WHERE pm.meta_key = '_sku' AND pm.meta_value = %s
                                           AND p.post_type IN ('product', 'product_variation')
                                           AND p.post_status != 'trash'
                                           AND pm.post_id <> %d
                                         LIMIT 1",
                                        $meta_value, $product_id
                                    ));
                                }
                                g_ffl_debug_log("SKU conflict check for '$meta_value': " . ($conflict ? "conflict with product $conflict" : "no conflict"), 'debug');
                                if ($conflict) {
                                    $errors[] = "SKU '$meta_value' conflicts with product $conflict - skipped";
                                    continue; // Skip this SKU update to avoid conflicts
                                }
                            }
                        } elseif ($data_key === 'sale_price') {
                            // Handle empty sale price (remove it)
                            if ($meta_value === '' || $meta_value === null) {
                                // Delete the sale price meta
                                $delete_result = $wpdb->delete(
                                    $wpdb->postmeta,
                                    ['post_id' => $product_id, 'meta_key' => $meta_key],
                                    ['%d', '%s']
                                );
                                if ($delete_result === false) {
                                    $warnings[] = "Failed to delete sale_price meta for product $product_id";
                                }
                                // Also clear sale dates & mirror keys
                                $clear_result = $wpdb->query( $wpdb->prepare(
                                    "DELETE FROM {$wpdb->postmeta}
                                     WHERE post_id = %d
                                       AND meta_key IN ('_sale_price_dates_from','_sale_price_dates_to','_date_on_sale_from','_date_on_sale_to')",
                                    $product_id
                                ));
                                if ($clear_result === false) {
                                    $warnings[] = "Failed to clear sale date fields for product $product_id";
                                }
                                $current_sale_price = '';
                                $updated_fields[] = $data_key;
                                continue;
                            } else {
                                $meta_value = floatval($meta_value);
                                $current_sale_price = $meta_value;
                            }
                        } elseif ($data_key === 'regular_price') {
                            $meta_value = floatval($meta_value);
                            $current_regular_price = $meta_value;
                        } elseif ($data_key === 'stock_quantity') {
                            $meta_value = intval($meta_value);
                        } else {
                            $meta_value = floatval($meta_value);
                        }
                        
                        // Check if meta exists
                        $meta_exists = $wpdb->get_var($wpdb->prepare(
                            "SELECT meta_id FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = %s",
                            $product_id, $meta_key
                        ));

                        if ($meta_exists) {
                            $result = $wpdb->update(
                                $wpdb->postmeta,
                                ['meta_value' => $meta_value],
                                ['post_id' => $product_id, 'meta_key' => $meta_key],
                                ['%s'],
                                ['%d', '%s']
                            );
                        } else {
                            $result = $wpdb->insert(
                                $wpdb->postmeta,
                                [
                                    'post_id' => $product_id,
                                    'meta_key' => $meta_key,
                                    'meta_value' => $meta_value
                                ],
                                ['%d', '%s', '%s']
                            );
                        }

                        if ($result === false) {
                            $errors[] = "Failed to update {$data_key} for product {$product_id}";
                        } else {
                            $updated_fields[] = $data_key;

                            // Keep wp_wc_product_meta_lookup in sync for fields it tracks,
                            // so the indexed conflict check above stays authoritative within
                            // the sync (rebuild_woocommerce_data() may not have run yet).
                            $lookup_column = null;
                            switch ($data_key) {
                                case 'sku':            $lookup_column = 'sku'; break;
                                case 'stock_quantity': $lookup_column = 'stock_quantity'; break;
                            }
                            if ($lookup_column !== null) {
                                $lookup_table = $wpdb->prefix . 'wc_product_meta_lookup';
                                $lookup_exists = self::table_exists($lookup_table);
                                if ($lookup_exists) {
                                    $wpdb->update(
                                        $lookup_table,
                                        [$lookup_column => $meta_value],
                                        ['product_id' => $product_id],
                                        ['%s'],
                                        ['%d']
                                    );
                                }
                            }
                        }

                    } catch (Exception $e) {
                        $errors[] = "Error updating {$data_key} for product {$product_id}: " . $e->getMessage();
                    }
                }
            }

            // Recompute _price if price OR sale-date fields changed
            if (
                isset($data['regular_price']) ||
                isset($data['sale_price']) ||
                array_key_exists('sale_price_dates_from', $data) ||
                array_key_exists('sale_price_dates_to', $data)
            ) {
                try {
                    $regular_price = array_key_exists('regular_price', $data)
                        ? $data['regular_price']
                        : self::sql_get_meta($product_id, '_regular_price');

                    $sale_price = array_key_exists('sale_price', $data)
                        ? $data['sale_price']
                        : self::sql_get_meta($product_id, '_sale_price');

                    $from_ts = array_key_exists('sale_price_dates_from', $data)
                        ? self::normalize_ts($data['sale_price_dates_from'])
                        : (($v = self::sql_get_meta($product_id, '_sale_price_dates_from')) ? (is_numeric($v) ? (int)$v : strtotime($v)) : null);

                    $to_ts = array_key_exists('sale_price_dates_to', $data)
                        ? self::normalize_ts($data['sale_price_dates_to'])
                        : (($v = self::sql_get_meta($product_id, '_sale_price_dates_to')) ? (is_numeric($v) ? (int)$v : strtotime($v)) : null);

                    $formatted_price = self::compute_effective_price($regular_price, $sale_price, $from_ts, $to_ts);
                    self::sql_update_meta($product_id, '_price', $formatted_price);
                    $updated_fields[] = 'price';
                } catch (Exception $e) {
                    $errors[] = "Error computing effective price for product {$product_id}: " . $e->getMessage();
                }
            }

            // Handle stock management
            if (isset($data['stock_quantity'])) {
                try {
                    // Allow explicit manage_stock override, otherwise default to 'yes' when stock_quantity provided
                    if (array_key_exists('manage_stock', $data)) {
                        self::sql_update_meta($product_id, '_manage_stock', $data['manage_stock'] ? 'yes' : 'no');
                    } else {
                        self::sql_update_meta($product_id, '_manage_stock', 'yes');
                    }
                    self::sql_update_meta($product_id, '_stock_status', $data['stock_quantity'] > 0 ? 'instock' : 'outofstock');
                } catch (Exception $e) {
                    $errors[] = "Error updating stock management for product {$product_id}: " . $e->getMessage();
                }
            }

            // Handle shipping class
            if (!empty($data['shipping_class'])) {
                try {
                    $shipping_class_id = self::sql_assign_shipping_class($data['shipping_class']);
                    if ($shipping_class_id) {
                        // Set shipping class using term relationship (not meta data)
                        self::sql_update_product_shipping_class($product_id, $shipping_class_id);
                        $updated_fields[] = 'shipping_class';
                    } else {
                        $warnings[] = "Failed to assign shipping class '{$data['shipping_class']}' for product {$product_id}";
                    }
                } catch (Exception $e) {
                    $errors[] = "Error updating shipping class for product {$product_id}: " . $e->getMessage();
                }
            }

            // Handle product attributes
            if (isset($data['attributes']) && is_array($data['attributes'])) {
                try {
                    self::sql_assign_product_attributes($product_id, $data['attributes']);
                    $updated_fields[] = 'attributes';
                } catch (Exception $e) {
                    $errors[] = "Error updating attributes for product {$product_id}: " . $e->getMessage();
                }
            }

            // Handle additional meta data (complete replacement - remove keys not in source)
            if (isset($data['meta_data']) && is_array($data['meta_data'])) {
                try {
                    self::sql_replace_all_meta($product_id, $data['meta_data']);
                    $updated_fields[] = 'meta_data';
                } catch (Exception $e) {
                    $errors[] = "Error updating meta_data for product {$product_id}: " . $e->getMessage();
                }
            }

            // Handle global unique ID (typically stored as meta)
            if (isset($data['global_unique_id'])) {
                g_ffl_debug_log("Processing global_unique_id for product $product_id: " . $data['global_unique_id'], 'debug');
                try {
                    self::sql_update_meta($product_id, '_global_unique_id', $data['global_unique_id']);
                    $updated_fields[] = 'global_unique_id';
                    g_ffl_debug_log("Successfully updated global_unique_id for product $product_id", 'info');
                } catch (Exception $e) {
                    $errors[] = "Error updating global_unique_id for product {$product_id}: " . $e->getMessage();
                    g_ffl_debug_log("Error updating global_unique_id for product $product_id: " . $e->getMessage(), 'error');
                }
            } else {
                g_ffl_debug_log("global_unique_id not set in data for product $product_id", 'warning');
            }

            // Handle categories
            if (isset($data['categories']) && is_array($data['categories'])) {
                try {
                    self::sql_update_product_terms($product_id, $data['categories'], 'product_cat');
                    $updated_fields[] = 'categories';
                } catch (Exception $e) {
                    $errors[] = "Error updating categories for product {$product_id}: " . $e->getMessage();
                }
            }

            // Handle tags
            if (isset($data['tags']) && is_array($data['tags'])) {
                try {
                    self::sql_update_product_terms($product_id, $data['tags'], 'product_tag');
                    $updated_fields[] = 'tags';
                } catch (Exception $e) {
                    $errors[] = "Error updating tags for product {$product_id}: " . $e->getMessage();
                }
            }

            // Commit all changes
            $wpdb->query('COMMIT');

            // Handle images AFTER transaction (to avoid long locks during uploads)
            if (isset($data['images'])) {
                g_ffl_debug_log("Processing images for product {$product_id}. Images data: " . json_encode($data['images']), 'debug');
                $images = null;
                
                if (is_string($data['images'])) {
                    $images = json_decode($data['images'], true);
                    if (!is_array($images)) {
                        $warnings[] = "Invalid images JSON for product {$product_id}: " . $data['images'];
                        $images = null;
                    }
                } elseif (is_array($data['images'])) {
                    $images = $data['images'];
                }
                
                g_ffl_debug_log("Parsed images for product {$product_id}: " . json_encode($images), 'debug');
                
                if (is_array($images)) {
                    // Get existing image IDs before updating
                    $existing_thumbnail_id = self::sql_get_meta($product_id, '_thumbnail_id');
                    $existing_gallery_ids = self::sql_get_meta($product_id, '_product_image_gallery');
                    
                    $existing_attachment_ids = [];
                    if ($existing_thumbnail_id && is_numeric($existing_thumbnail_id)) {
                        $existing_attachment_ids[] = (int) $existing_thumbnail_id;
                    }
                    if ($existing_gallery_ids) {
                        $gallery_ids_array = explode(',', $existing_gallery_ids);
                        foreach ($gallery_ids_array as $id) {
                            if (is_numeric(trim($id))) {
                                $existing_attachment_ids[] = (int) trim($id);
                            }
                        }
                    }
                    $existing_attachment_ids = array_unique($existing_attachment_ids);
                    
                    // If images array is empty, clear all product images and delete old attachments
                    if (empty($images)) {
                        // Clear thumbnail
                        self::sql_update_meta($product_id, '_thumbnail_id', '');
                        // Clear gallery
                        self::sql_update_meta($product_id, '_product_image_gallery', '');
                        
                        // Delete existing attachments
                        foreach ($existing_attachment_ids as $attachment_id) {
                            $image_deleted = wp_delete_attachment($attachment_id, true);
                            if (!$image_deleted) {
                                $warnings[] = "Failed to delete old attachment {$attachment_id}";
                            }
                        }
                        
                        $updated_fields[] = 'images';
                        g_ffl_debug_log("Cleared all images for product {$product_id} and deleted " . count($existing_attachment_ids) . " old attachments", 'info');
                    } else {
                        // Process new images
                        g_ffl_debug_log("Processing " . count($images) . " images for product {$product_id}", 'debug');
                        $image_ids = [];
                        $thumbnail_set = false;
                        $image_errors = [];

                        foreach ($images as $index => $image_data) {
                            g_ffl_debug_log("Processing image {$index} for product {$product_id}: " . json_encode($image_data), 'debug');
                            if (!isset($image_data['src'])) {
                                g_ffl_debug_log("Image {$index} for product {$product_id} missing 'src' - skipping", 'warning');
                                continue;
                            }

                            try {
                                g_ffl_debug_log("Uploading image from URL: " . $image_data['src'], 'debug');
                                $attachment_id = self::upload_image_from_url($image_data['src']);
                                g_ffl_debug_log("Upload result for {$image_data['src']}: " . ($attachment_id ? $attachment_id : 'false'), 'debug');
                                
                                if ($attachment_id && is_numeric($attachment_id)) {
                                    if ($index === 0 && !$thumbnail_set) {
                                        g_ffl_debug_log("Setting thumbnail for product {$product_id} to attachment {$attachment_id}", 'info');
                                        self::sql_update_meta($product_id, '_thumbnail_id', intval($attachment_id));
                                        $thumbnail_set = true;
                                    } else {
                                        g_ffl_debug_log("Adding attachment {$attachment_id} to gallery for product {$product_id}", 'debug');
                                        $image_ids[] = intval($attachment_id);
                                    }
                                } else {
                                    $image_errors[] = "Failed to upload image from: " . $image_data['src'];
                                    g_ffl_debug_log("Failed to upload image from: " . $image_data['src'], 'error');
                                }
                            } catch (Throwable $e) {
                                $image_errors[] = "Image processing failed for {$image_data['src']}: " . $e->getMessage();
                                g_ffl_debug_log("Image processing exception for {$image_data['src']}: " . $e->getMessage(), 'error');
                            }
                        }

                        if (!empty($image_ids)) {
                            // Cast to integers, remove duplicates, and reindex
                            $image_ids = array_values(array_unique(array_map('intval', $image_ids)));
                            g_ffl_debug_log("Setting gallery for product {$product_id} to: " . implode(',', $image_ids), 'info');
                            self::sql_update_meta($product_id, '_product_image_gallery', implode(',', $image_ids));
                        } else {
                            // No gallery images, clear the gallery
                            g_ffl_debug_log("No gallery images for product {$product_id}, clearing gallery", 'info');
                            self::sql_update_meta($product_id, '_product_image_gallery', '');
                        }
                        
                        // After successfully setting new images, delete the old ones
                        if (!empty($existing_attachment_ids)) {
                            $deleted_count = 0;
                            foreach ($existing_attachment_ids as $attachment_id) {
                                // Verify it's actually an attachment before deleting
                                $is_attachment = $wpdb->get_var($wpdb->prepare(
                                    "SELECT ID FROM {$wpdb->posts} WHERE ID = %d AND post_type = 'attachment'",
                                    $attachment_id
                                ));
                                
                                if ($is_attachment) {
                                    $image_deleted = wp_delete_attachment($attachment_id, true);
                                    if ($image_deleted) {
                                        $deleted_count++;
                                    } else {
                                        $warnings[] = "Failed to delete old attachment {$attachment_id}";
                                    }
                                }
                            }
                            g_ffl_debug_log("Deleted {$deleted_count} old attachments for product {$product_id}", 'info');
                        }
                        
                        if (!empty($image_errors)) {
                            g_ffl_debug_log("Adding image errors to warnings for product {$product_id}: " . json_encode($image_errors), 'debug');
                            $warnings = array_merge($warnings, $image_errors);
                            g_ffl_debug_log("Warnings after merging image errors for product {$product_id}: " . json_encode($warnings), 'debug');
                        }
                        
                        $updated_fields[] = 'images';
                        g_ffl_debug_log("Completed image processing for product {$product_id}. Thumbnail set: " . ($thumbnail_set ? 'yes' : 'no') . ", Gallery images: " . count($image_ids), 'info');
                    }
                }
            }

            // Always update the post modified time for any product update
            $final_update_result = $wpdb->update(
                $wpdb->posts,
                [
                    'post_modified' => current_time('mysql'),
                    'post_modified_gmt' => current_time('mysql', 1)
                ],
                ['ID' => $product_id],
                ['%s', '%s'],
                ['%d']
            );

            if ($final_update_result === false) {
                $warnings[] = "Failed to update post modified time for product {$product_id}";
            }

            // Clear caches
            wp_cache_delete($product_id, 'posts');
            wp_cache_delete($product_id, 'post_meta');
            clean_post_cache($product_id);

            $result = [
                'status' => !empty($errors) ? 'partial_success' : 'success', 
                'product_id' => $product_id,
                'updated_fields' => $updated_fields
            ];

            g_ffl_debug_log("Final result construction for product {$product_id} - Errors: " . json_encode($errors) . ", Warnings: " . json_encode($warnings), 'debug');

            // Combine errors and warnings into errors array, and adjust status accordingly
            $all_errors = array_merge($errors, $warnings);
            
            if (!empty($all_errors)) {
                $result['errors'] = $all_errors;
                $result['message'] = implode('; ', $all_errors); // Put all errors/warnings in message for endpoint compatibility
                $result['status'] = !empty($errors) ? 'partial_success' : 'partial_success'; // If there are any issues, mark as partial_success
            }

            g_ffl_debug_log("Final result for product {$product_id}: " . json_encode($result), 'debug');
            self::queue_product_notification($product_id, 'update', $result['status']);
            return $result;

        } catch (Exception $e) {
            $wpdb->query('ROLLBACK');

            return [
                'status' => 'error',
                'product_id' => $product_id,
                'message' => $e->getMessage(),
                'errors' => array_merge($errors, [$e->getMessage()]),
                'warnings' => $warnings
            ];
        }
    }

    /**
     * Normalize timestamp value
     *
     * @param mixed $v The value to normalize
     * @return int|null Normalized timestamp or null
     */
    private static function normalize_ts($v) {
        if ($v === '' || $v === null) return null;
        if (is_numeric($v)) return (int)$v;
        $t = strtotime($v);
        return $t ?: null;
    }

    /**
     * Get meta value using SQL
     *
     * @param int $product_id Product ID
     * @param string $meta_key Meta key
     * @return mixed Meta value
     */
    private static function sql_get_meta($product_id, $meta_key) {
        global $wpdb;
        $value = $wpdb->get_var($wpdb->prepare(
            "SELECT meta_value
             FROM {$wpdb->postmeta}
             WHERE post_id = %d AND meta_key = %s
             ORDER BY meta_id DESC
             LIMIT 1",
            $product_id, $meta_key
        ));
        return maybe_unserialize($value);
    }

    /**
     * Update or insert a product meta value using SQL
     *
     * @param int    $product_id The product ID
     * @param string $meta_key   The meta key
     * @param mixed  $meta_value The meta value
     */
    private static function sql_update_meta($product_id, $meta_key, $meta_value) {
        global $wpdb;

        $meta_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT meta_id FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = %s",
            $product_id, $meta_key
        ));

        if ($meta_exists) {
            $wpdb->update(
                $wpdb->postmeta,
                ['meta_value' => maybe_serialize($meta_value)],
                ['post_id' => $product_id, 'meta_key' => $meta_key],
                ['%s'],
                ['%d', '%s']
            );
        } else {
            $wpdb->insert(
                $wpdb->postmeta,
                [
                    'post_id' => $product_id,
                    'meta_key' => $meta_key,
                    'meta_value' => maybe_serialize($meta_value)
                ],
                ['%d', '%s', '%s']
            );
        }
    }

    /**
     * Compute effective price considering sale dates
     *
     * @param mixed $regular_price Regular price
     * @param mixed $sale_price Sale price
     * @param int|null $from_ts Sale start timestamp
     * @param int|null $to_ts Sale end timestamp
     * @return string Formatted price
     */
    private static function compute_effective_price($regular_price, $sale_price, $from_ts = null, $to_ts = null) {
        $regular_price = floatval($regular_price);
        $effective_price = $regular_price;
        
        // Check if sale price is valid and within date window
        if (!empty($sale_price) && is_numeric($sale_price) && floatval($sale_price) > 0) {
            $sale_price = floatval($sale_price);
            $now = current_time('timestamp');
            
            // Check sale date window
            $sale_active = true;
            if ($from_ts && $now < $from_ts) $sale_active = false;
            if ($to_ts && $now > $to_ts) $sale_active = false;
            
            if ($sale_active) {
                $effective_price = $sale_price;
            }
        }
        
        // Format with WooCommerce decimals, prefer WooCommerce formatter
        $decimals = function_exists('wc_get_price_decimals') ? wc_get_price_decimals() : 2;
        if (function_exists('wc_format_decimal')) {
            return wc_format_decimal($effective_price, $decimals);
        }
        return number_format((float)$effective_price, $decimals, '.', '');
    }

    /**
     * Replace all product meta data with the provided data (complete replacement)
     *
     * @param int   $product_id The product ID
     * @param array $meta_data  Array of meta data objects with 'key' and 'value'
     */
    private static function sql_replace_all_meta($product_id, $meta_data) {
        global $wpdb;

        // Get list of meta keys to preserve (WordPress/WooCommerce core keys that should not be deleted)
        $protected_keys = [
            '_edit_lock',
            '_edit_last', 
            '_wp_old_slug',
            '_wp_old_date',
            '_visibility',
            '_featured',
            '_virtual',
            '_downloadable',
            '_download_limit',
            '_download_expiry',
            '_stock',
            '_stock_status',
            '_manage_stock',
            '_backorders',
            '_sold_individually',
            '_weight',
            '_length',
            '_width',
            '_height',
            '_upsell_ids',
            '_crosssell_ids',
            '_purchase_note',
            '_default_attributes',
            '_product_attributes',
            '_regular_price',
            '_sale_price',
            '_sale_price_dates_from',
            '_sale_price_dates_to',
            '_price',
            '_sku',
            '_thumbnail_id',
            '_product_image_gallery',
            '_tax_status',
            '_tax_class',
            '_product_shipping_class',
            '_children',
            '_product_version',
            'global_unique_id'  // Protect global_unique_id from being deleted by meta_data replacement
        ];

        // First, get all existing meta keys for this product (excluding protected keys)
        $placeholders = implode(',', array_fill(0, count($protected_keys), '%s'));

        $sql = "SELECT DISTINCT meta_key FROM {$wpdb->postmeta}
                WHERE post_id = %d
                AND meta_key NOT IN ($placeholders)";

        $existing_keys = $wpdb->get_col(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Dynamic placeholders for IN clause, prepared with spread operator
            $wpdb->prepare($sql, $product_id, ...$protected_keys)
        );


        // Build list of new meta keys from provided data
        $new_keys = [];
        foreach ($meta_data as $meta) {
            if (isset($meta['key'])) {
                $new_keys[] = $meta['key'];
            }
        }

        // Delete meta keys that exist but are not in the new data
        $keys_to_delete = array_diff($existing_keys, $new_keys);
        foreach ($keys_to_delete as $key_to_delete) {
            $wpdb->delete(
                $wpdb->postmeta,
                ['post_id' => $product_id, 'meta_key' => $key_to_delete],
                ['%d', '%s']
            );
        }

        // Update/insert all the new meta data
        foreach ($meta_data as $meta) {
            if (isset($meta['key']) && isset($meta['value'])) {
                self::sql_update_meta($product_id, $meta['key'], $meta['value']);
            }
        }
    }

    /**
     * Update product terms (categories/tags) using SQL
     *
     * @param int    $product_id The product ID
     * @param array  $terms      Array of term objects with 'id' key
     * @param string $taxonomy   The taxonomy name
     */
    private static function sql_update_product_terms($product_id, $terms, $taxonomy) {
        global $wpdb;

        // Remove existing relationships for this taxonomy
        $wpdb->query($wpdb->prepare(
            "DELETE tr FROM {$wpdb->term_relationships} tr 
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
             WHERE tr.object_id = %d AND tt.taxonomy = %s",
            $product_id, $taxonomy
        ));

        // Add new relationships
        if (!empty($terms)) {
            foreach ($terms as $term) {
                if (isset($term['id']) && is_numeric($term['id'])) {
                    $term_taxonomy_id = $wpdb->get_var($wpdb->prepare(
                        "SELECT term_taxonomy_id FROM {$wpdb->term_taxonomy} WHERE term_id = %d AND taxonomy = %s",
                        $term['id'], $taxonomy
                    ));

                    if ($term_taxonomy_id) {
                        $wpdb->insert(
                            $wpdb->term_relationships,
                            [
                                'object_id' => $product_id,
                                'term_taxonomy_id' => $term_taxonomy_id,
                                'term_order' => 0
                            ],
                            ['%d', '%d', '%d']
                        );
                    }
                }
            }
        }

        // Note: For better performance with bulk operations, wrap the whole import with:
        // wp_defer_term_counting(true) before processing and wp_defer_term_counting(false) after
        // and remove this per-product term count update
        if (function_exists('wp_defer_term_counting') && !wp_defer_term_counting(null)) {
            // Only update term counts if term counting is not deferred
            if (function_exists('wp_update_term_count_now')) {
                $tt_ids = $wpdb->get_col($wpdb->prepare(
                    "SELECT term_taxonomy_id FROM {$wpdb->term_taxonomy} WHERE taxonomy = %s",
                    $taxonomy
                ));
                if ($tt_ids) wp_update_term_count_now($tt_ids, $taxonomy);
            }
        }
    }

    /**
     * Assign shipping class using SQL (equivalent to assign_shipping_class)
     *
     * @param string $shipping_class The shipping class name
     * @return int|false The shipping class term ID or false on failure
     */
    private static function sql_assign_shipping_class($shipping_class) {
        global $wpdb;

        $slug = sanitize_title($shipping_class);
        
        // Check if shipping class term exists
        $term = $wpdb->get_row($wpdb->prepare(
            "SELECT t.term_id FROM {$wpdb->terms} t 
             INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id 
             WHERE t.slug = %s AND tt.taxonomy = 'product_shipping_class'",
            $slug
        ));

        if ($term) {
            return $term->term_id;
        }

        // Create new shipping class
        $result = $wpdb->insert(
            $wpdb->terms,
            [
                'name' => $shipping_class,
                'slug' => $slug,
                'term_group' => 0
            ],
            ['%s', '%s', '%d']
        );

        if ($result === false) {
            g_ffl_debug_log("Failed to create shipping class term: " . $shipping_class, 'error');
            return false;
        }

        $term_id = $wpdb->insert_id;

        // Insert into term_taxonomy
        $result = $wpdb->insert(
            $wpdb->term_taxonomy,
            [
                'term_id' => $term_id,
                'taxonomy' => 'product_shipping_class',
                'description' => '',
                'parent' => 0,
                'count' => 0
            ],
            ['%d', '%s', '%s', '%d', '%d']
        );

        if ($result === false) {
            // Clean up the term if taxonomy insert failed
            $wpdb->delete($wpdb->terms, ['term_id' => $term_id], ['%d']);
            g_ffl_debug_log("Failed to create shipping class taxonomy: " . $shipping_class, 'error');
            return false;
        }

        return $term_id;
    }

    /**
     * Update product shipping class using SQL (term relationship)
     *
     * @param int $product_id The product ID
     * @param int $shipping_class_id The shipping class term ID
     */
    private static function sql_update_product_shipping_class($product_id, $shipping_class_id) {
        global $wpdb;

        // First, remove any existing shipping class relationships
        $wpdb->query($wpdb->prepare(
            "DELETE tr FROM {$wpdb->term_relationships} tr 
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
             WHERE tr.object_id = %d AND tt.taxonomy = 'product_shipping_class'",
            $product_id
        ));

        // Get the term_taxonomy_id for this shipping class
        $term_taxonomy_id = $wpdb->get_var($wpdb->prepare(
            "SELECT term_taxonomy_id FROM {$wpdb->term_taxonomy} WHERE term_id = %d AND taxonomy = 'product_shipping_class'",
            $shipping_class_id
        ));

        if ($term_taxonomy_id) {
            // Insert new shipping class relationship
            $wpdb->insert(
                $wpdb->term_relationships,
                [
                    'object_id' => $product_id,
                    'term_taxonomy_id' => $term_taxonomy_id,
                    'term_order' => 0
                ],
                ['%d', '%d', '%d']
            );

            // Update term count
            $wpdb->query($wpdb->prepare(
                "UPDATE {$wpdb->term_taxonomy} SET count = count + 1 WHERE term_taxonomy_id = %d",
                $term_taxonomy_id
            ));
        }
    }

    /**
     * Assign product attributes using SQL (equivalent to assign_product_attributes)
     *
     * @param int   $product_id The product ID
     * @param array $attributes The attributes array
     */
    private static function sql_assign_product_attributes($product_id, $attributes) {
        global $wpdb;
        $product_attributes = [];

        // First, clear all existing attribute term relationships for this product
        // Get all attribute taxonomies for this product
        $existing_attribute_taxonomies = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT tt.taxonomy 
             FROM {$wpdb->term_relationships} tr
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
             WHERE tr.object_id = %d AND tt.taxonomy LIKE %s",
            $product_id, 'pa_%'
        ));

        // Remove existing attribute term relationships
        foreach ($existing_attribute_taxonomies as $taxonomy) {
            $wpdb->query($wpdb->prepare(
                "DELETE tr FROM {$wpdb->term_relationships} tr
                 INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                 WHERE tr.object_id = %d AND tt.taxonomy = %s",
                $product_id, $taxonomy
            ));
        }

        // If attributes array is empty, clear all product attributes and return
        if (empty($attributes)) {
            // Clear the _product_attributes meta to remove all attributes
            self::sql_update_meta($product_id, '_product_attributes', []);
            g_ffl_debug_log("Cleared all attributes for product {$product_id}", 'info');
            return;
        }

        foreach ($attributes as $attr) {
            $attr_name = '';
            $attr_id = 0;
            $taxonomy_name = '';
            
            // First, try to get the attribute ID directly if provided
            if (isset($attr['id']) && is_numeric($attr['id'])) {
                $attr_id = (int) $attr['id'];
                
                // Validate the attribute ID exists and get its details
                $attr_details = $wpdb->get_row($wpdb->prepare(
                    "SELECT attribute_name, attribute_label FROM {$wpdb->prefix}woocommerce_attribute_taxonomies WHERE attribute_id = %d",
                    $attr_id
                ));
                
                if ($attr_details) {
                    $attr_name = $attr_details->attribute_label;
                    $taxonomy_name = 'pa_' . $attr_details->attribute_name;
                } else {
                    // Invalid attribute ID, try to look up by name
                    $attr_id = 0;
                }
            }
            
            // If no valid ID found, try to determine from name/slug
            if ($attr_id === 0) {
                if (isset($attr['name'])) {
                    $attr_name = $attr['name'];
                } elseif (isset($attr['slug'])) {
                    $attr_name = $attr['slug'];
                }
                
                if (!empty($attr_name)) {
                    // Try multiple lookup strategies to find the attribute ID
                    $sanitized_name = sanitize_title($attr_name);
                    
                    // Strategy 1: Look up by attribute_label
                    $attr_details = $wpdb->get_row($wpdb->prepare(
                        "SELECT attribute_id, attribute_name, attribute_label FROM {$wpdb->prefix}woocommerce_attribute_taxonomies WHERE attribute_label = %s",
                        $attr_name
                    ));
                    
                    // Strategy 2: If not found, try by attribute_name (sanitized)
                    if (!$attr_details) {
                        $attr_details = $wpdb->get_row($wpdb->prepare(
                            "SELECT attribute_id, attribute_name, attribute_label FROM {$wpdb->prefix}woocommerce_attribute_taxonomies WHERE attribute_name = %s",
                            $sanitized_name
                        ));
                    }
                    
                    // Strategy 3: If still not found, try partial matching on label
                    if (!$attr_details) {
                        $attr_details = $wpdb->get_row($wpdb->prepare(
                            "SELECT attribute_id, attribute_name, attribute_label FROM {$wpdb->prefix}woocommerce_attribute_taxonomies WHERE LOWER(attribute_label) = LOWER(%s)",
                            $attr_name
                        ));
                    }
                    
                    if ($attr_details) {
                        $attr_id = (int) $attr_details->attribute_id;
                        $attr_name = $attr_details->attribute_label;
                        $taxonomy_name = 'pa_' . $attr_details->attribute_name;
                    } else {
                        // No matching global attribute found - create new global attribute
                        $attr_id = self::sql_create_product_attribute($attr_name, $sanitized_name);
                        if ($attr_id > 0) {
                            $taxonomy_name = 'pa_' . $sanitized_name;
                        } else {
                            // Failed to create, treat as custom product attribute
                            $attr_id = 0;
                            $taxonomy_name = $sanitized_name;
                        }
                    }
                }
            }

            // Skip if we don't have a name or options
            if (empty($attr_name) || empty($attr['options'])) {
                continue;
            }

            // Build the attribute data structure
            $attribute_data = [
                'name' => $taxonomy_name, // Use taxonomy name for WooCommerce compatibility
                'value' => '', // Will be populated with term names
                'position' => isset($attr['position']) ? (int) $attr['position'] : 0,
                'is_visible' => !empty($attr['visible']) ? 1 : 0,
                'is_variation' => !empty($attr['variation']) ? 1 : 0,
                'is_taxonomy' => $attr_id > 0 ? 1 : 0
            ];

            // For global attributes (with ID), we need to handle the terms properly
            if ($attr_id > 0 && !empty($taxonomy_name)) {
                // For global attributes, we need to create/assign actual taxonomy terms
                $term_values = [];
                $options = is_array($attr['options']) ? $attr['options'] : [$attr['options']];
                
                foreach ($options as $option) {
                    $option = sanitize_text_field(trim($option));
                    if (empty($option)) continue;
                    
                    // Check if term exists, create if not
                    $term_id = self::sql_get_or_create_attribute_term($taxonomy_name, $option);
                    if ($term_id) {
                        $term_values[] = $option;
                        // Assign this term to the product
                        self::sql_assign_product_attribute_term($product_id, $taxonomy_name, $term_id);
                    }
                }
                
                // Store the term names as the attribute value
                $attribute_data['value'] = implode(' | ', $term_values);
            } else {
                // For custom attributes, store the options directly
                if (is_array($attr['options'])) {
                    $attribute_data['value'] = implode(' | ', array_map('sanitize_text_field', $attr['options']));
                } else {
                    $attribute_data['value'] = sanitize_text_field($attr['options']);
                }
            }

            // Use the correct key format for the attributes array
            $key = $attr_id > 0 ? $taxonomy_name : sanitize_title($attr_name);
            $product_attributes[$key] = $attribute_data;
        }

        if (!empty($product_attributes)) {
            self::sql_update_meta($product_id, '_product_attributes', $product_attributes);
        }
    }

    /**
     * Create a new global product attribute taxonomy
     *
     * @param string $attribute_label The display name for the attribute
     * @param string $attribute_name The sanitized name for the attribute (used in taxonomy)
     * @return int The new attribute ID, or 0 on failure
     */
    private static function sql_create_product_attribute($attribute_label, $attribute_name) {
        global $wpdb;

        try {
            // Insert into woocommerce_attribute_taxonomies
            $result = $wpdb->insert(
                $wpdb->prefix . 'woocommerce_attribute_taxonomies',
                [
                    'attribute_name' => $attribute_name,
                    'attribute_label' => $attribute_label,
                    'attribute_type' => 'select', // Default to select type
                    'attribute_orderby' => 'menu_order',
                    'attribute_public' => 0
                ],
                ['%s', '%s', '%s', '%s', '%d']
            );

            if ($result === false) {
                g_ffl_debug_log("Failed to create attribute taxonomy: " . $attribute_label, 'error');
                return 0;
            }

            $attribute_id = $wpdb->insert_id;

            // Register the new taxonomy with WordPress
            $taxonomy_name = 'pa_' . $attribute_name;
            
            // Create the taxonomy if it doesn't exist
            if (!taxonomy_exists($taxonomy_name)) {
                // WooCommerce will automatically register this taxonomy on next load,
                // but we can register it immediately for this request
                register_taxonomy(
                    $taxonomy_name,
                    'product',
                    [
                        'labels' => [
                            'name' => $attribute_label,
                            'singular_name' => $attribute_label,
                        ],
                        'hierarchical' => false,
                        'show_ui' => false,
                        'show_in_nav_menus' => false,
                        'query_var' => is_admin(),
                        'rewrite' => false,
                        'public' => false,
                        'show_in_rest' => false,
                    ]
                );
            }

            // Clear any WooCommerce attribute caches
            if (function_exists('wc_clear_attribute_cache')) {
                wc_clear_attribute_cache();
            }

            // Delete the transient that WooCommerce uses to cache attribute taxonomies
            delete_transient('wc_attribute_taxonomies');

            g_ffl_debug_log("Successfully created new product attribute: {$attribute_label} (ID: {$attribute_id}, taxonomy: {$taxonomy_name})", 'info');
            
            return $attribute_id;

        } catch (Exception $e) {
            g_ffl_debug_log("Error creating product attribute '{$attribute_label}': " . $e->getMessage(), 'error');
            return 0;
        }
    }

    /**
     * Get or create an attribute term in the specified taxonomy
     *
     * @param string $taxonomy_name The taxonomy name (e.g., 'pa_color')
     * @param string $term_name The term name to find or create
     * @return int|false The term ID or false on failure
     */
    private static function sql_get_or_create_attribute_term($taxonomy_name, $term_name) {
        global $wpdb;

        $term_slug = sanitize_title($term_name);
        
        // First, check if the term already exists
        $term = $wpdb->get_row($wpdb->prepare(
            "SELECT t.term_id, tt.term_taxonomy_id 
             FROM {$wpdb->terms} t
             INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
             WHERE tt.taxonomy = %s AND (t.slug = %s OR t.name = %s)
             LIMIT 1",
            $taxonomy_name, $term_slug, $term_name
        ));

        if ($term) {
            return (int) $term->term_id;
        }

        // Term doesn't exist, create it
        try {
            // Insert into terms table
            $result = $wpdb->insert(
                $wpdb->terms,
                [
                    'name' => $term_name,
                    'slug' => $term_slug,
                    'term_group' => 0
                ],
                ['%s', '%s', '%d']
            );

            if ($result === false) {
                g_ffl_debug_log("Failed to create term: {$term_name} in taxonomy {$taxonomy_name}", 'error');
                return false;
            }

            $term_id = $wpdb->insert_id;

            // Insert into term_taxonomy table
            $result = $wpdb->insert(
                $wpdb->term_taxonomy,
                [
                    'term_id' => $term_id,
                    'taxonomy' => $taxonomy_name,
                    'description' => '',
                    'parent' => 0,
                    'count' => 0
                ],
                ['%d', '%s', '%s', '%d', '%d']
            );

            if ($result === false) {
                // Clean up the term if taxonomy insert failed
                $wpdb->delete($wpdb->terms, ['term_id' => $term_id], ['%d']);
                g_ffl_debug_log("Failed to create term taxonomy for: {$term_name} in {$taxonomy_name}", 'error');
                return false;
            }

            g_ffl_debug_log("Successfully created term: {$term_name} (ID: {$term_id}) in taxonomy {$taxonomy_name}", 'info');
            return $term_id;

        } catch (Exception $e) {
            g_ffl_debug_log("Error creating term '{$term_name}' in taxonomy '{$taxonomy_name}': " . $e->getMessage(), 'error');
            return false;
        }
    }

    /**
     * Assign an attribute term to a product (creates term relationship)
     *
     * @param int $product_id The product ID
     * @param string $taxonomy_name The taxonomy name
     * @param int $term_id The term ID to assign
     */
    private static function sql_assign_product_attribute_term($product_id, $taxonomy_name, $term_id) {
        global $wpdb;

        // Get the term_taxonomy_id
        $term_taxonomy_id = $wpdb->get_var($wpdb->prepare(
            "SELECT term_taxonomy_id FROM {$wpdb->term_taxonomy} WHERE term_id = %d AND taxonomy = %s",
            $term_id, $taxonomy_name
        ));

        if (!$term_taxonomy_id) {
            g_ffl_debug_log("Term taxonomy ID not found for term {$term_id} in taxonomy {$taxonomy_name}", 'error');
            return;
        }

        // Check if relationship already exists
        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT object_id FROM {$wpdb->term_relationships} WHERE object_id = %d AND term_taxonomy_id = %d",
            $product_id, $term_taxonomy_id
        ));

        if (!$existing) {
            // Insert the relationship
            $result = $wpdb->insert(
                $wpdb->term_relationships,
                [
                    'object_id' => $product_id,
                    'term_taxonomy_id' => $term_taxonomy_id,
                    'term_order' => 0
                ],
                ['%d', '%d', '%d']
            );

            if ($result !== false) {
                // Update term count
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$wpdb->term_taxonomy} SET count = count + 1 WHERE term_taxonomy_id = %d",
                    $term_taxonomy_id
                ));
            } else {
                g_ffl_debug_log("Failed to assign term {$term_id} to product {$product_id}", 'error');
            }
        }
    }

    /*
    //COMPRESSED IMAGE VERSION
    private static function upload_image_from_url($url) {
        require_once ABSPATH . 'wp-admin/includes/file.php';
        require_once ABSPATH . 'wp-admin/includes/media.php';
        require_once ABSPATH . 'wp-admin/includes/image.php';
    
        $tmp = download_url($url);
        if (is_wp_error($tmp)) return false;
    
        $file_array = [
            'name'     => basename($url),
            'tmp_name' => $tmp
        ];
    
        $attachment_id = media_handle_sideload($file_array, 0);
        if (is_wp_error($attachment_id)) {
            @unlink($tmp);
            return false;
        }
    
        // Recompress the image
        $image_path = get_attached_file($attachment_id);
        $image = wp_get_image_editor($image_path);
        if (!is_wp_error($image)) {
            $image->set_quality(75); // Adjust compression level
            $image->save($image_path); // Overwrites the original image with compressed version
        }
    
        return $attachment_id;
    }
    */
}
