<?php

/**
 * @package CsvImporterPlusforACF
 */

namespace CIPFA\base;

use ActionScheduler;


// make sure we don't expose any info if called directly
if (!defined('ABSPATH')) exit; // Exit if accessed directly

use DateTime;

class CIPFA_PostProcessor
{

   // Action Scheduler for thumbnail generation
   public static function cipfa_register()
   {
      // Hook to handle async thumbnail generation
      add_action(
         'cipfa_generate_thumb_for_attachment',
         array(__CLASS__, 'cipfa_generate_thumb_for_attachment')
      );

      // Admin notice for background processing
      add_action('admin_notices', function () {
         if (!class_exists('ActionScheduler')) {
            return;
         }

         $store = ActionScheduler::store();

         if (!is_object($store)) {
            return;
         }

         $pending = $store->query_actions([
            'hook'   => 'cipfa_generate_thumb_for_attachment',
            'status' => \ActionScheduler_Store::STATUS_PENDING,
            'per_page' => 1,
         ]);

         if (!empty($pending)) {
            echo '<div class="notice notice-info is-dismissible">
                <p><strong>Thumbnails are being generated in the background.</strong><br>
                This may take a few minutes depending on image size.</p>
            </div>';
         }
      });
   }

   // Function to process a chunk of CSV data and return the number of posts imported
   public static function cipfa_process_chunk($chunk, $mapping, $post_type, $edit_existing)
   {
      $total_imported_chunk = 0;
      $existing_skus = [];

      // Extract/register media URLs
      $urls_to_register = self::cipfa_prepare_media_urls($chunk);
      $registered_map = self::cipfa_bulk_register_media($urls_to_register);

      // Normalize strings
      self::cipfa_normalize_chunk_strings($chunk);

      // Replace old URLs with registered ones in the chunk
      self::cipfa_replace_registered_urls_in_chunk($chunk, $registered_map);

      // STEP 1: Prepare rows (mapping, SKU checks, author handling)
      list($posts_to_insert, $csv_ids, $chunk_skus) = self::cipfa_prepare_rows(
         $chunk,
         $mapping,
         $post_type,
         $edit_existing,
         $registered_map
      );

      // Preload valid parents and page templates
      $valid_parent_ids = [];
      $valid_page_templates = [];
      if ($post_type === 'page') {
         $valid_parent_ids = self::cipfa_preload_valid_parents_for_chunk($posts_to_insert);
         $valid_page_templates = wp_get_theme()->get_page_templates();
         foreach ($posts_to_insert as &$post_data) {
            // Validate parent
            if (isset($post_data['post_parent_raw']) && self::cipfa_is_valid_post_parent($post_data['post_parent_raw'], $valid_parent_ids)) {
               $post_data['post_parent'] = intval($post_data['post_parent_raw']);
            }
            unset($post_data['post_parent_raw']);

            // Validate template
            if (isset($post_data['page_template_raw'])) {
               $template_key = self::cipfa_is_valid_page_template($post_data['page_template_raw'], $valid_page_templates);
               if ($template_key) {
                  $post_data['page_template'] = sanitize_text_field($template_key);
               }
               unset($post_data['page_template_raw']);
            }
         }
         unset($post_data);
      }

      $is_wc_product = in_array($post_type, ['product', 'product_variation'], true);

      if ($is_wc_product && !empty($chunk_skus)) {
         // SKUS exist in this chunk
         $existing_skus = self::cipfa_get_existing_skus_for_list($chunk_skus);

         // Filter out duplicates BEFORE inserting
         list($posts_to_insert, $csv_ids) = self::cipfa_filter_duplicate_skus(
            $posts_to_insert,
            $csv_ids,
            $existing_skus,
            $edit_existing
         );
      }

      // ACF option page
      $is_options_page = strpos($post_type, 'acf_option_') === 0;

      if ($is_options_page) {
         $post_ids = array_fill(0, count($posts_to_insert), 'option');
      } else {
         // STEP 2: Bulk insert posts and handle edit mode override
         $post_ids = self::cipfa_insert_or_update_post_bulk($posts_to_insert, $existing_skus, $edit_existing, $csv_ids);
      }

      // STEP 3: Bulk meta/taxonomy/image/attributes handling
      if (!empty($post_ids)) {
         self::cipfa_process_inserted_posts($post_type, $posts_to_insert, $post_ids, $registered_map, $edit_existing, $valid_parent_ids);
      }

      $total_imported_chunk = count($post_ids);

      return $total_imported_chunk;
   }

   // Prepare chunk rows 
   private static function cipfa_prepare_rows($chunk, $mapping, $post_type, $edit_existing, $registered_map)
   {
      $posts_to_insert = [];
      $csv_ids = [];
      $chunk_skus = [];

      foreach ($chunk as $row) {
         $post_data = self::cipfa_prepare_post_data(
            $mapping,
            $row,
            $post_type,
            $edit_existing,
            $registered_map
         );

         // Handle author
         $post_data['post_author'] = self::cipfa_handle_post_author(
            $post_data['post_author'] ?? '',
            $post_data['post_author_email'] ?? null,
            $post_data['author_password'] ?? null
         );

         // Collect chunk SKUs
         if ($post_type === 'product') {
            $pf = $post_data['product_fields'] ?? [];

            if (isset($pf['_sku']) && $pf['_sku'] !== '') {
               $chunk_skus[] = trim($pf['_sku']);
            }
         }

         $csv_id = intval($post_data['csv_post_id'] ?? 0);
         $csv_ids[] = $csv_id;

         $posts_to_insert[] = $post_data;
      }

      return [
         $posts_to_insert,
         $csv_ids,
         $chunk_skus
      ];
   }

   //Prepare Post Data
   private static function cipfa_prepare_post_data($mapping, $row, $post_type, $edit_existing, $registered_map)
   {
      $post_data = array(
         'post_type' => $post_type,
         'post_author' => '',
      );

      $product_fields = array();
      $acf_fields = array();
      $yoast_fields = array();
      $rankmath_fields = array();
      $post_tags = array();
      $post_category = array();
      $post_content = '';
      $post_excerpt = '';
      $post_featured_image = '';
      $csv_post_id = '';
      $taxonomy_terms = array();

      // Retrieve post data from CSV based on the mapping
      foreach ($mapping as $column_name => $field_key) {
         // Retrieve the value from the row
         $column_value = $row[$column_name] ?? '';

         switch ($field_key) {
            case 'product_name':
            case 'post_title':
               $post_data['post_title'] = sanitize_text_field($column_value);
               break;
            case 'page_slug':
            case 'product_slug':
            case 'post_slug':
               $post_data['post_name'] = sanitize_title($column_value);
               break;
            case 'product_date':
            case 'post_date':
               $post_data['post_date'] = self::cipfa_set_date_based_on_formats($column_value);
               break;
            case 'product_description':
            case 'post_content':
               $post_content = wp_kses_post($column_value);
               break;
            case 'product_short_description':
            case 'post_excerpt':
               $post_excerpt = wp_kses_post($column_value);
               break;
            case 'product_image':
            case 'featured_image':
               $post_featured_image = esc_url_raw($column_value);
               break;
            case 'post_category':
               $post_category = self::cipfa_parse_terms($column_value);
               break;
            case 'tags':
               $post_tags =
                  self::cipfa_parse_terms($column_value);
               break;
            case 'post_author':
               $post_data['post_author'] = sanitize_text_field($column_value);
               break;
            case 'post_author_email':
               $post_data['post_author_email'] = sanitize_email($column_value);
               break;
            case 'author_password':
               $post_data['author_password'] = sanitize_text_field($column_value);
               break;
            case 'post_parent':
               $post_data['post_parent_raw'] = intval($column_value);
               break;
            case 'product_position':
            case 'menu_order':
               $post_data['menu_order'] = intval($column_value);
               break;
            case 'page_template':
               $post_data['page_template_raw'] = sanitize_text_field($column_value);
               break;
            case 'product_published':
            case 'post_status':
               $post_data['post_status'] = sanitize_text_field($column_value);
               break;
            case 'post_password':
               $post_data['post_password'] = sanitize_text_field($column_value);
               break;
            case 'comments':
               $post_data['comment_status'] = strtolower(sanitize_text_field($column_value)) === 'disable' ? 'closed' : 'open';
               break;
            case 'pings':
               $post_data['ping_status'] = strtolower(sanitize_text_field($column_value)) === 'disable' ? 'closed' : 'open';
               break;
            case 'option_id':
            case 'product_id':
            case 'post_id':
               $csv_post_id = intval($column_value);
               break;
            case 'product_brand':
            case 'product_visibility':
            case 'product_type':
            case 'product_cat':
            case 'product_tag':
            case 'product_shipping_class':
               if ($field_key === 'product_visibility') {
                  $allowed_visibility = [
                     'visible'              => 'visible',
                     'featured'             => 'featured',
                     'feature'              => 'featured',
                     'exclude from catalog' => 'exclude-from-catalog',
                     'exclude from search'  => 'exclude-from-search',
                     'exclude-from-catalog' => 'exclude-from-catalog',
                     'exclude-from-search'  => 'exclude-from-search'
                  ];

                  $values = [trim($column_value)];
                  $mapped_terms = array_map(function ($value) use ($allowed_visibility) {
                     $clean = strtolower(sanitize_text_field($value));
                     if (strpos($clean, 'exclude') !== false) {
                        $clean = str_replace(' ', '-', $clean);
                     }
                     return $allowed_visibility[$clean] ?? 'visible';
                  }, $values);
                  $taxonomy_terms[$column_name] = array_unique($mapped_terms);
               } elseif ($field_key === 'product_type') {
                  $allowed_type = [
                     'simple'    => 'simple',
                     'grouped'   => 'grouped',
                     'external'  => 'external',
                     'variable'  => 'variable',
                     'variation' => 'variation'
                  ];

                  $values = [trim($column_value)];
                  $mapped_terms = array_map(function ($value) use ($allowed_type) {
                     $clean = sanitize_text_field($value);
                     $lower_clean = strtolower($clean);
                     if (isset($allowed_type[$clean])) {
                        return $allowed_type[$clean];
                     }
                     return $allowed_type[$lower_clean] ?? 'simple';
                  }, $values);
                  $taxonomy_terms[$column_name] = array_unique($mapped_terms);
                  $product_fields['_product_type'] = reset($mapped_terms);
               } else {
                  $terms = array_map('sanitize_text_field', explode(',', $column_value));
                  $taxonomy_terms[$column_name] = $terms;
               }
               break;
            case 'yoast_seo_title':
               $yoast_fields['_yoast_wpseo_title'] = sanitize_text_field($column_value);
               break;
            case 'yoast_focus_Keyword':
               $yoast_fields['_yoast_wpseo_focuskw'] = sanitize_text_field($column_value);
               break;
            case 'yoast_meta_description':
               $yoast_fields['_yoast_wpseo_metadesc'] = sanitize_textarea_field($column_value);
               break;
            case 'yoast_cornerstone_content':
               $yoast_fields['_yoast_wpseo_is_cornerstone'] = intval($column_value);
               break;
            case 'yoast_show_content_in_search_results':
               $yoast_fields['_yoast_wpseo_meta-robots-noindex'] =
                  intval($column_value);
               break;
            case 'yoast_search_engines_follow_links':
               $yoast_fields['_yoast_wpseo_meta-robots-nofollow'] =
                  intval($column_value);
               break;
            case 'yoast_meta_robots_advanced':
               $allowed_yoast_robots = [
                  'noarchive'    => 'noarchive',
                  'nosnippet'    => 'nosnippet',
                  'noimageindex' => 'noimageindex',
                  'noindex'      => 'noindex',
                  'nofollow'     => 'nofollow',
                  'none'         => 'none',
                  'index'        => 'index',
                  'follow'       => 'follow',
               ];
               $input_values = is_array($column_value) ? $column_value : array_map('trim', explode(',', $column_value));
               $valid_directives = [];

               foreach ($input_values as $value) {
                  $clean_value = strtolower(sanitize_text_field($value));
                  if (isset($allowed_yoast_robots[$clean_value])) {
                     $valid_directives[] = $allowed_yoast_robots[$clean_value];
                  }
               }
               if (!empty($valid_directives)) {
                  $yoast_fields['_yoast_wpseo_meta-robots-adv'] = implode(',', array_unique($valid_directives));
               }
               break;
            case 'yoast_breadcrumb_title':
               $yoast_fields['_yoast_wpseo_bctitle'] = sanitize_text_field($column_value);
               break;
            case 'yoast_canonical_url':
               $yoast_fields['_yoast_wpseo_canonical'] = esc_url_raw($column_value);
               break;
            case 'yoast_schema_page_type':
               $allowed_schema_page_types = [
                  'WebPage'            => 'WebPage',
                  'ItemPage'           => 'ItemPage',
                  'AboutPage'          => 'AboutPage',
                  'FAQPage'            => 'FAQPage',
                  'QAPage'             => 'QAPage',
                  'ContactPage'        => 'ContactPage',
                  'MedicalWebPage'     => 'MedicalWebPage',
                  'CollectionPage'     => 'CollectionPage',
                  'CheckoutPage'       => 'CheckoutPage',
                  'RealEstateListing'  => 'RealEstateListing',
                  'ProfilePage'        => 'ProfilePage',
                  'SearchResultsPage'  => 'SearchResultsPage',
               ];
               $clean_value = trim(sanitize_text_field($column_value));
               if (isset($allowed_schema_page_types[$clean_value])) {
                  $yoast_fields['_yoast_wpseo_schema_page_type'] = $allowed_schema_page_types[$clean_value];
               }
               break;
            case 'yoast_schema_article_type':
               $allowed_article_types = [
                  'Article'                     => 'Article',
                  'NewsArticle'                 => 'NewsArticle',
                  'BlogPosting'                 => 'BlogPosting',
                  'TechArticle'                 => 'TechArticle',
                  'ScholarlyArticle'            => 'ScholarlyArticle',
                  'SocialMediaPosting'          => 'SocialMediaPosting',
                  'SatiricalArticle'            => 'SatiricalArticle',
                  'AdvertiserContentArticle'    => 'AdvertiserContentArticle',
                  'Report'                      => 'Report',
                  'None'                        => 'None',
               ];
               $clean_value = trim(sanitize_text_field($column_value));
               if (isset($allowed_article_types[$clean_value])) {
                  $yoast_fields['_yoast_wpseo_schema_article_type'] = $allowed_article_types[$clean_value];
               }
               break;
            case 'rank_math_title':
               $rankmath_fields['rank_math_title'] = sanitize_text_field($column_value);
               break;
            case 'rank_math_focus_keyword':
               $rankmath_fields['rank_math_focus_keyword'] = sanitize_text_field($column_value);
               break;
            case 'rank_math_description':
               $rankmath_fields['rank_math_description'] = sanitize_textarea_field($column_value);
               break;
            case 'rank_math_robots':
               $allowed_robots = [
                  'index' => 'index',
                  'follow' => 'follow',
                  'noindex' => 'noindex',
                  'nofollow' => 'nofollow',
                  'none' => 'none',
                  'noarchive' => 'noarchive',
                  'nosnippet' => 'nosnippet',
                  'noimageindex' => 'noimageindex',
                  'noodp' => 'noodp',
                  'noydir' => 'noydir'
               ];
               $input_values = is_array($column_value) ? $column_value : explode(',', $column_value);
               $cleaned_values = [];
               foreach ($input_values as $value) {
                  $clean = strtolower(trim(sanitize_text_field($value)));
                  if (isset($allowed_robots[$clean])) {
                     $cleaned_values[] = $allowed_robots[$clean];
                  }
               }
               if (empty($cleaned_values)) {
                  $cleaned_values = ['index', 'follow'];
               }
               $rankmath_fields['rank_math_robots'] = $cleaned_values;
               break;
            case 'rank_math_advanced_robots':
               $allowed_advanced = [
                  'max-snippet'       => 'max-snippet',
                  'max-video-preview' => 'max-video-preview',
                  'max-image-preview' => 'max-image-preview',
                  'unavailable_after' => 'unavailable_after',
               ];

               $input_values = is_array($column_value) ? $column_value : explode(',', $column_value);
               $cleaned_values = [];

               foreach ($input_values as $value) {
                  $value = sanitize_text_field(trim($value));
                  if (strpos($value, ':') !== false) {
                     list($key, $val) = array_map('trim', explode(':', $value, 2));
                     $key = strtolower($key);

                     if (isset($allowed_advanced[$key])) {
                        $cleaned_values[$allowed_advanced[$key]] = $val;
                     }
                  } else {
                     $key = strtolower($value);
                     if (isset($allowed_advanced[$key])) {
                        $cleaned_values[$allowed_advanced[$key]] = true;
                     }
                  }
               }
               if (empty($cleaned_values)) {
                  $cleaned_values = [];
               }

               $rankmath_fields['rank_math_advanced_robots'] = $cleaned_values;
               break;
            case 'rank_math_rich_snippet':
               $rankmath_fields['tmp_schema_type'] = strtolower(trim(sanitize_text_field($column_value)));
               break;
            case 'rank_math_schema_json':
               $json_data = json_decode(trim($column_value), true);
               if (json_last_error() === JSON_ERROR_NONE && is_array($json_data)) {
                  $rankmath_fields['tmp_schema_json'] = $json_data;
               }
               break;
            case 'rank_math_pillar_content':
               $value = sanitize_text_field($column_value);
               $rankmath_fields['rank_math_pillar_content'] = (trim($value) === '1') ? 'on' : 'off';
               break;
            case 'rank_math_canonical':
               $canonical_url = esc_url_raw(trim($column_value));
               $rankmath_fields['rank_math_canonical'] = $canonical_url;
               $rankmath_fields['rank_math_canonical_url'] = $canonical_url;
               break;
            case 'rank_math_primary_category_id':
               $primary_cat_id = intval($column_value);
               if ($primary_cat_id > 0) {
                  $rankmath_fields['rank_math_primary_category'] = $primary_cat_id;
               }
               break;
            case 'rank_math_facebook_title':
               $rankmath_fields['rank_math_facebook_title'] = sanitize_text_field($column_value);
               break;
            case 'rank_math_facebook_description':
               $rankmath_fields['rank_math_facebook_description'] = sanitize_textarea_field($column_value);
               break;
            case 'rank_math_facebook_image':
               $rankmath_fields['rank_math_facebook_image'] = esc_url_raw($column_value);
               break;
            case 'rank_math_twitter_title':
               $rankmath_fields['rank_math_twitter_title'] = sanitize_text_field($column_value);
               if (!empty($rankmath_fields['rank_math_twitter_title'])) {
                  $rankmath_fields['rank_math_twitter_use_facebook'] = 'off';
               }
               break;
            case 'rank_math_twitter_description':
               $rankmath_fields['rank_math_twitter_description'] = sanitize_textarea_field($column_value);
               if (!empty($rankmath_fields['rank_math_twitter_description'])) {
                  $rankmath_fields['rank_math_twitter_use_facebook'] = 'off';
               }
               break;
            case 'rank_math_twitter_image':
               $rankmath_fields['rank_math_twitter_image'] = esc_url_raw($column_value);
               if (!empty($rankmath_fields['rank_math_twitter_image'])) {
                  $rankmath_fields['rank_math_twitter_use_facebook'] = 'off';
               }
               break;
            case 'product_image_gallery':
               $product_fields['_product_image_gallery'] = sanitize_text_field($column_value);
               break;
            case 'product_sku':
               $product_fields['_sku'] = sanitize_text_field(trim($column_value));
               break;
            case 'product_virtual':
               $product_fields['_virtual'] = intval($column_value);
               break;
            case 'product_downloadable':
               $product_fields['_downloadable'] = intval($column_value);
               break;
            case 'product_stock':
               $product_fields['_stock'] = intval($column_value);
               break;
            case 'product_stock_status':
               $allowed_stock_statuses = [
                  'instock' => 'instock',
                  'in stock' => 'instock',
                  'outofstock' => 'outofstock',
                  'out of stock' => 'outofstock',
                  'onbackorder' => 'onbackorder',
                  'on backorder' => 'onbackorder'
               ];

               $column_value_clean = strtolower(trim(sanitize_text_field($column_value)));
               $column_value_clean = str_replace(' ', '', $column_value_clean);
               $product_fields['_stock_status'] = $allowed_stock_statuses[$column_value_clean] ?? 'instock';
               break;
            case 'product_manage_stock':
               $product_fields['_manage_stock'] = intval($column_value);
               break;
            case 'product_allow_backorders':
               $allowed_backorders = ['no', 'notify', 'yes'];
               $column_value_clean = strtolower(sanitize_text_field($column_value));
               $product_fields['_backorders'] = in_array($column_value_clean, $allowed_backorders) ? $column_value_clean : 'no';
               break;
            case 'product_low_stock_amount':
               $product_fields['_low_stock_amount'] = intval($column_value);
               break;
            case 'product_sold_individually':
               $product_fields['_sold_individually'] = intval($column_value);
               break;
            case 'product_tax_status':
               $allowed_tax_statuses = ['taxable', 'shipping', 'none'];
               $column_value_clean = strtolower(sanitize_text_field($column_value));
               $product_fields['_tax_status'] = in_array($column_value_clean, $allowed_tax_statuses) ? $column_value_clean : 'taxable';
               break;
            case 'product_tax_class':
               $allowed_tax_classes = [
                  'standard' => 'standard',
                  'reduced-rate' => 'reduced-rate',
                  'reduced rate' => 'reduced-rate',
                  'zero-rate' => 'zero-rate',
                  'zero rate' => 'zero-rate'
               ];

               $column_value_clean = strtolower(sanitize_text_field($column_value));
               $column_value_clean = str_replace(' ', '-', $column_value_clean);

               $product_fields['_tax_class'] = $allowed_tax_classes[$column_value_clean] ?? 'standard';
               break;
            case 'product_sale_date_price_start':
               if ($column_value !== '') {
                  $product_fields['_sale_price_dates_from'] = strtotime(
                     self::cipfa_set_date_based_on_formats($column_value, '_sale_price_dates_from')
                  );
               } else {
                  unset($product_fields['_sale_price_dates_from']);
               }
               break;
            case 'product_sale_date_price_end':
               if ($column_value !== '') {
                  $product_fields['_sale_price_dates_to'] = strtotime(
                     self::cipfa_set_date_based_on_formats($column_value, '_sale_price_dates_to')
                  );
               } else {
                  unset($product_fields['_sale_price_dates_to']);
               }
               break;
            case 'product_weight':
               $product_fields['_weight'] = !empty($column_value) ? floatval($column_value) : null;
               break;
            case 'product_length':
               $product_fields['_length'] = !empty($column_value) ? floatval($column_value) : null;
               break;
            case 'product_width':
               $product_fields['_width'] = !empty($column_value) ? floatval($column_value) : null;
               break;
            case 'product_height':
               $product_fields['_height'] = !empty($column_value) ? floatval($column_value) : null;
               break;
            case 'product_enable_reviews':
               $product_fields['_enable_reviews'] = intval($column_value);
               break;
            case 'product_purchase_note':
               $product_fields['_purchase_note'] = sanitize_text_field($column_value);
               break;
            case 'product_sale_price':
               if ($column_value !== '') {
                  $product_fields['_sale_price'] = wc_format_decimal($column_value);
               }
               break;
            case 'product_regular_price':
               if ($column_value !== '') {
                  $product_fields['_regular_price'] = wc_format_decimal($column_value);
               }
               break;
            case 'product_upsell_ids':
               $product_fields['_upsell_ids'] = array_map('intval', array_map('trim', explode(',', $column_value)));
               break;
            case 'product_crosssell_ids':
               $product_fields['_crosssell_ids'] = array_filter(
                  array_map('intval', array_map('trim', explode(',', $column_value)))
               );
               break;
            case 'product_barcode':
               $product_fields['_global_unique_id'] = sanitize_text_field($column_value);
               break;
            case 'product_download_file_name':
               $product_fields['download_file_name'] = array_map('sanitize_text_field', explode(',', $column_value));
               break;
            case 'product_download_file_url':
               $product_fields['download_file_url'] = array_map('esc_url', explode(',', $column_value));
               break;
            case 'product_download_limit':
               $product_fields['_download_limit'] = intval($column_value);
               break;
            case 'product_download_expiry':
               $product_fields['_download_expiry'] = intval($column_value);
               break;
            case 'product_wpcom_is_markdown':
               $product_fields['_wpcom_is_markdown'] = intval($column_value);
               break;
            case 'product_attribute':
               $product_fields['product_attribute'] = [];
               $attr_string = trim($column_value);

               // Normalize spaces around ; and remove duplicates
               $attr_string = preg_replace('/\s*;\s*/', ';', $attr_string);
               $attributes = array_filter(array_map('trim', explode(';', $attr_string)));

               foreach ($attributes as $attr) {

                  if (preg_match('/^([^:]+):\s*(.+)$/', $attr, $matches)) {

                     $raw_name = trim($matches[1]);
                     $values_str = trim($matches[2]);

                     $visible = 1;
                     $global = 1;

                     // Symbols for visible/global
                     if (str_ends_with($raw_name, '*!')) {
                        $visible = 0;
                        $global  = 0;
                        $name = rtrim($raw_name, '*!');
                     } elseif (str_ends_with($raw_name, '*')) {
                        $visible = 0;
                        $name = rtrim($raw_name, '*');
                     } elseif (str_ends_with($raw_name, '!')) {
                        $global = 0;
                        $name = rtrim($raw_name, '!');
                     } else {
                        $name = $raw_name;
                     }

                     // Normalize values
                     $values = array_filter(array_map('trim', explode(',', $values_str)));

                     $product_fields['product_attribute'][] = [
                        'name'    => sanitize_text_field($name),
                        'value'   => array_map('sanitize_text_field', $values),
                        'visible' => $visible,
                        'global'  => $global,
                     ];
                  }
               }
               break;
            default:
               // Handle custom taxonomies dynamically
               if (taxonomy_exists($column_name)) {
                  $taxonomy_terms[$column_name] = self::cipfa_parse_terms($column_value);
               } elseif (strpos($field_key, 'field_') !== false) {
                  if ($column_value === '' || $column_value === null) {
                     continue 2;
                  }
                  $sanitized_data = CIPFA_ACFSanitizer::cipfa_sanitize_acf_data(array($field_key => $column_value), $registered_map);
                  //Handle group fields
                  if (self::cipfa_is_group_sub_field($field_key)) {
                     $field_object = get_field_object($field_key);
                     $parent_id     = $field_object['parent'];
                     $parent_object = acf_get_field($parent_id);
                     $group_key     = $parent_object['key'];

                     // Transform sanitized data to group format
                     $transformed_data = self::cipfa_transform_to_group_format($sanitized_data);

                     if (isset($transformed_data[$field_key])) {
                        if (!isset($acf_fields[$group_key])) {
                           $acf_fields[$group_key] = [];
                        }

                        $acf_fields[$group_key][$field_object['name']] = $transformed_data[$field_key];
                     }
                  } else {
                     //Default handling for non-group fields
                     if (is_array($sanitized_data) && isset($sanitized_data[$field_key])) {
                        $acf_fields[$field_key] = $sanitized_data[$field_key];
                     } else {
                        $acf_fields[$field_key] = $sanitized_data;
                     }
                  }
               }
               break;
         }
      }

      $post_data['product_fields'] = $product_fields;
      $post_data['acf_fields'] = $acf_fields;
      $post_data['post_tags'] = $post_tags;
      $post_data['post_category'] = $post_category;
      if (isset($post_content)) {
         $post_data['post_content'] = $post_content;
      }
      if (isset($post_excerpt)) {
         $post_data['post_excerpt'] = $post_excerpt;
      }
      $post_data['post_featured_image'] = $post_featured_image;
      $post_data['csv_post_id'] = $csv_post_id;
      if (!empty($taxonomy_terms)) {
         $post_data['taxonomy_terms'] = $taxonomy_terms;
      }
      $post_data['yoast_fields'] = $yoast_fields;
      $post_data['rankmath_fields'] = $rankmath_fields;
      return $post_data;
   }

   // Parse terms
   private static function cipfa_parse_terms($value)
   {
      return array_unique(array_filter(array_map(
         fn ($v) => trim(sanitize_text_field($v)),
         preg_split('/\s*[,\|]\s*/', $value)
      )));
   }

   // Process inserted posts
   private static function cipfa_process_inserted_posts($post_type, $posts_to_insert, $post_ids, $registered_map, $edit_existing, $valid_parent_ids = [])
   {
      $meta_to_insert = [];
      $categories_to_set = [];
      $tag_sets = [];
      $taxonomies_to_set = [];
      $product_attributes_bulk = [];
      $post_parents_to_set = [];

      foreach ($posts_to_insert as $index => $data) {
         if (!isset($post_ids[$index])) {
            continue;
         }

         $post_id = $post_ids[$index];
         if ($post_id == 0) {
            continue;
         }

         $acf_fields = $data['acf_fields'] ?? [];
         $yoast_fields = $data['yoast_fields'] ?? [];
         $rankmath_fields = $data['rankmath_fields'] ?? [];
         $product_fields = $data['product_fields'] ?? [];
         $product_image_gallery = $product_fields['_product_image_gallery'] ?? [];


         // Featured Image
         $featured_url = trim($data['post_featured_image'] ?? '');

         if (!empty($featured_url)) {
            $attachment_id = self::cipfa_find_attachment_id($featured_url, $registered_map);
         }

         // Rank Math processing
         if (!empty($rankmath_fields)) {
            if (isset($rankmath_fields['tmp_schema_type'], $rankmath_fields['tmp_schema_json'])) {
               $rankmath_fields = self::cipfa_build_rankmath_schema($rankmath_fields);
            }

            // Ensure rank_math_advanced_robots has default
            if (empty($rankmath_fields['rank_math_advanced_robots'])) {
               $rankmath_fields['rank_math_advanced_robots'] = 'max-snippet:-1, max-image-preview:large, max-video-preview:-1';
            }

            // Find and set Facebook/Twitter image IDs and urls
            if (!empty($rankmath_fields['rank_math_facebook_image'])) {
               $fb_url = $rankmath_fields['rank_math_facebook_image'];
               $fb_id = self::cipfa_find_attachment_id($fb_url, $registered_map);
               if ($fb_id) {
                  $rankmath_fields['rank_math_facebook_image'] = wp_get_attachment_url($fb_id);
                  $rankmath_fields['rank_math_facebook_image_id'] = $fb_id;
               }
            }

            if (!empty($rankmath_fields['rank_math_twitter_image'])) {
               $tw_url = $rankmath_fields['rank_math_twitter_image'];
               $tw_id = self::cipfa_find_attachment_id($tw_url, $registered_map);
               if ($tw_id) {
                  $rankmath_fields['rank_math_twitter_image'] = wp_get_attachment_url($tw_id);
                  $rankmath_fields['rank_math_twitter_image_id'] = $tw_id;
               }
            }
         }

         // Product fields processing
         if (!empty($product_fields)) {
            self::cipfa_update_product_price($product_fields);

            if (!empty($product_image_gallery)) {
               $product_fields['_product_image_gallery'] =
                  self::cipfa_set_product_image_gallery($post_id, $product_image_gallery, $registered_map);
            }

            if (!empty($product_fields['download_file_url'])) {
               self::cipfa_set_product_downloadable_files($product_fields, $registered_map);
            }

            $prepared_attributes = self::cipfa_prepare_product_attributes($product_fields);

            $product_attributes_bulk[] = [
               'post_id'    => $post_id,
               'attributes' => $prepared_attributes
            ];

            if (isset($product_fields['_product_image_gallery'])) {
               $product_fields['_product_image_gallery'] = trim($product_fields['_product_image_gallery'], ',');
            }
         }

         // Post parents
         if (!empty($data['post_parent']) && is_numeric($data['post_parent'])) {
            $post_parents_to_set[] = [
               'post_id'     => $post_id,
               'post_parent' => intval($data['post_parent']),
            ];
         }

         // Meta entry for bulk insertion
         $meta_entry = [
            'post_id'         => $post_id,
            'acf_fields'      => $acf_fields,
            'yoast_fields'    => $yoast_fields,
            'rankmath_fields' => $rankmath_fields,
            'product_fields'  => $product_fields,
         ];

         if (!empty($data['page_template'])) {
            $meta_entry['page_template'] = sanitize_text_field($data['page_template']);
         }

         if (!empty($data['post_featured_image'])) {
            $meta_entry['featured_image'] = $attachment_id;
         }

         if (
            !empty($acf_fields) ||
            !empty($yoast_fields) ||
            !empty($rankmath_fields) ||
            !empty($product_fields) ||
            !empty($attachment_id)
         ) {
            $meta_to_insert[] = $meta_entry;
         }

         // Categories
         if (!empty($data['post_category'])) {
            $categories_to_set[] = [
               'post_id'    => $post_id,
               'categories' => $data['post_category'],
            ];
         }

         // Taxonomies
         if (!empty($data['taxonomy_terms'])) {
            $taxonomies_to_set[] = [
               'post_id'    => $post_id,
               'taxonomies' => $data['taxonomy_terms'],
            ];
         }

         // Tags
         if (!empty($data['post_tags'])) {
            $tag_sets[] = [
               'post_id' => $post_id,
               'tags'    => $data['post_tags'],
            ];
         }
      }

      // Bulk update meta, categories, taxonomies
      if ($edit_existing && !empty($post_ids)) {
         self::cipfa_update_meta_fields_bulk($meta_to_insert, true);
      } else {
         self::cipfa_update_meta_fields_bulk($meta_to_insert);
      }

      // Delete selected taxonomy relationships in update
      self::cipfa_delete_selected_taxonomy_relations(
         $post_ids,
         $categories_to_set,
         $tag_sets,
         $taxonomies_to_set,
         $edit_existing
      );

      self::cipfa_bulk_set_categories($post_type, $categories_to_set);
      self::cipfa_bulk_set_custom_taxonomy_terms($post_type, $taxonomies_to_set);
      self::cipfa_bulk_set_tags($post_type, $tag_sets);

      if (!empty($product_attributes_bulk)) {
         $global_attrs = self::cipfa_collect_global_attributes($product_attributes_bulk);
         self::cipfa_bulk_insert_global_attributes_sql($global_attrs);
         self::cipfa_bulk_insert_product_attributes($product_attributes_bulk);
      }

      if (!empty($post_parents_to_set)) {
         self::cipfa_bulk_update_post_parents($post_parents_to_set, $valid_parent_ids);
      }
   }

   // Delete selected taxonomy relationships in update
   // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value
   private static function cipfa_delete_selected_taxonomy_relations($post_ids, $categories_to_set, $tag_sets, $taxonomies_to_set, $edit_existing)
   {
      if (!$edit_existing) {
         return;
      }

      global $wpdb;

      $replace_categories = get_option('cipfa_replace_categories') === 'replace';
      $replace_tags = get_option('cipfa_replace_tags') === 'replace';
      $replace_custom_taxonomies = get_option('cipfa_replace_custom_taxonomies') === 'replace';

      $update_categories = $replace_categories && !empty($categories_to_set);
      $update_tags       = $replace_tags && !empty($tag_sets);
      $update_custom     = $replace_custom_taxonomies && !empty($taxonomies_to_set);

      if (!$update_categories && !$update_tags && !$update_custom) {
         return;
      }

      $taxonomies_to_remove = [];

      // Categories
      if ($update_categories) {
         $taxonomies_to_remove[] = 'category';
      }

      // Tags
      if ($update_tags) {
         $taxonomies_to_remove[] = 'post_tag';
      }

      // Custom taxonomies
      if ($update_custom) {
         foreach ($taxonomies_to_set as $set) {
            if (!empty($set['taxonomies'])) {
               foreach ($set['taxonomies'] as $taxonomy_name => $terms) {
                  $taxonomies_to_remove[] =
                     sanitize_key($taxonomy_name);
               }
            }
         }
      }

      $taxonomies_to_remove = array_unique($taxonomies_to_remove);

      if (empty($taxonomies_to_remove)) {
         return;
      }

      $taxonomy_placeholders = implode(',', array_fill(
         0,
         count($taxonomies_to_remove),
         '%s'
      ));

      $tt_ids = $wpdb->get_col(
         $wpdb->prepare(
            "SELECT term_taxonomy_id 
             FROM {$wpdb->term_taxonomy}
             WHERE taxonomy IN ($taxonomy_placeholders)",
            $taxonomies_to_remove
         )
      );

      if (empty($tt_ids)) {
         return;
      }

      $chunk_size = 100;
      $chunks     = array_chunk($post_ids, $chunk_size);

      foreach ($chunks as $chunk) {

         $post_placeholders = implode(',', array_fill(
            0,
            count($chunk),
            '%d'
         ));
         $tt_placeholders   = implode(',', array_fill(
            0,
            count($tt_ids),
            '%d'
         ));

         $wpdb->query(
            $wpdb->prepare(
               "DELETE FROM {$wpdb->term_relationships}
                 WHERE object_id IN ($post_placeholders)
                 AND term_taxonomy_id IN ($tt_placeholders)",
               array_merge($chunk, $tt_ids)
            )
         );
      }
   }
   // phpcs:enable

   // Duplicate skus
   private static function cipfa_filter_duplicate_skus($posts_to_insert, $csv_ids, $existing_skus, $edit_existing)
   {
      $final_posts = [];
      $final_csv   = [];

      foreach ($posts_to_insert as $index => $post_data) {
         $product_fields = $post_data['product_fields'] ?? [];

         if (
            !$edit_existing &&
            isset($product_fields['_sku']) &&
            $product_fields['_sku'] !== ''
         ) {
            $sku = trim($product_fields['_sku']);

            if (isset($existing_skus[$sku])) {
               CIPFA_Logs::cipfa_Log('A product with this exact SKU already exists. SKU: ' . $sku);
               continue;
            }
         }

         $final_posts[] = $post_data;
         $final_csv[]   = $csv_ids[$index];
      }

      return [$final_posts, $final_csv];
   }

   // fetch only SKUs in current chunk
   // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
   private static function cipfa_get_existing_skus_for_list($skus)
   {
      global $wpdb;

      if (empty($skus)) {
         return [];
      }

      $map = [];
      $chunk_size = 500;

      foreach (array_chunk($skus, $chunk_size) as $chunk) {

         $placeholders = implode(',', array_fill(0, count($chunk), '%s'));

         $query = $wpdb->prepare(
            "
            SELECT pm.meta_value, pm.post_id
            FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
            WHERE pm.meta_key = '_sku'
              AND pm.meta_value IN ($placeholders)
              AND p.post_status <> 'trash'
              AND p.post_type IN ('product','product_variation')
            ",
            ...$chunk
         );

         $results = $wpdb->get_results($query, ARRAY_A);

         foreach ($results as $row) {
            $map[$row['meta_value']] = intval($row['post_id']);
         }
      }

      return $map;
   }
   // phpcs:enable

   // Find attachment ID for a URL
   public static function cipfa_find_attachment_id($image_url, $registered_map)
   {
      if (empty($image_url)) return 0;
      $image_url = trim($image_url);
      $attachment_id = 0;

      // Direct URL match
      foreach ($registered_map as $info) {
         $info_url = is_array($info) ? ($info['url'] ?? '') : $info;
         $info_id = is_array($info) ? ($info['id'] ?? 0) : 0;
         if (
            !empty($info_url) && $info_url === $image_url
         ) {
            $attachment_id = (int) $info_id;
            break;
         }
      }

      // Fallback for scaled variants within the same folder
      if (!$attachment_id) {
         $target_path =
            wp_parse_url($image_url, PHP_URL_PATH) ?? '';
         $target_dir  = trailingslashit(dirname($target_path));
         $target_filename = basename($target_path);
         $target_filename_noscale = preg_replace('/-scaled(\.[a-z]+)$/i', '$1', $target_filename);

         foreach ($registered_map as $info) {
            $info_url = is_array($info) ? ($info['url'] ?? '') : $info;
            $info_id = is_array($info) ? ($info['id'] ?? 0) : 0;
            if (empty($info_url)) {
               continue;
            }

            $orig_path = wp_parse_url(
               $info_url,
               PHP_URL_PATH
            );
            $orig_dir  = trailingslashit(dirname($orig_path));
            $orig_filename = basename($orig_path);
            $orig_filename_noscale = preg_replace('/-scaled(\.[a-z]+)$/i', '$1', $orig_filename);

            if ($orig_filename_noscale === $target_filename_noscale && $orig_dir === $target_dir) {
               $attachment_id = (int) $info_id;
               break;
            }
         }
      }

      // Final fallback
      if (!$attachment_id) {
         $attachment_id = attachment_url_to_postid($image_url);
      }

      return (int) $attachment_id;
   }

   // Prepare media URLs
   private static function cipfa_prepare_media_urls($chunk)
   {
      $urls = self::cipfa_extract_media_urls_from_chunk($chunk);

      $urls = array_map(function ($url) {
         return self::cipfa_validate_and_log_url($url);
      }, $urls);

      $urls = array_filter($urls);
      $urls = array_unique($urls);

      return $urls;
   }

   // Normalize strings in chunk
   private static function cipfa_normalize_chunk_strings(&$chunk)
   {
      array_walk_recursive($chunk, function (&$value) {
         if (!is_string($value)) return;

         $value = trim($value);

         if (preg_match('/^https?:\/\//i', $value)) {
            $value = esc_url_raw($value);
         }

         if (
            stripos($value, '<img') !== false || stripos($value, '<a') !== false
         ) {
            // Remove spaces inside src="" or href="" values
            $value = preg_replace_callback(
               '/\b(src|href)\s*=\s*["\']\s*([^"\']+?)\s*["\']/i',
               function ($matches) {
                  $clean_url = esc_url_raw(trim($matches[2]));
                  return $matches[1] . '="' . $clean_url . '"';
               },
               $value
            );
         }
      });
   }

   // Replace URLs in chunk
   private static function cipfa_replace_registered_urls_in_chunk(&$chunk, $registered_map)
   {
      foreach ($chunk as &$row) {
         array_walk_recursive($row, function (&$value) use ($registered_map) {
            if (!is_string($value) || $value === '') return;

            foreach ($registered_map as $old => $new) {
               // Extract the new URL if $new is an array
               $new_url = is_array($new) ? (isset($new['url']) ? $new['url'] : '') : $new;
               if (empty($new_url)) continue;

               $alt_old = str_ireplace('http://', 'https://', $old);
               $value = str_ireplace(
                  [$old, $alt_old],
                  $new_url,
                  $value
               );
            }
         });
      }
      unset($row);
   }

   //Check if sub-field belongs to a group
   public static function cipfa_is_group_sub_field($field_key)
   {
      $field = acf_get_field($field_key);
      if ($field && isset($field['parent']) && $field['parent']) {
         $parent_field = acf_get_field($field['parent']);
         if ($parent_field && $parent_field['type'] === 'group') {
            return true;
         }
      }
      return false;
   }

   //Transform data to group format
   public static function cipfa_transform_to_group_format($sanitized_data)
   {
      $formatted_data = [];

      foreach ($sanitized_data as $field_key => $value) {
         $formatted_data[$field_key] = $value;
      }

      return $formatted_data;
   }

   // Preload valid parents
   // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
   private static function cipfa_preload_valid_parents_for_chunk($chunk)
   {
      global $wpdb;

      $parent_ids = [];
      foreach ($chunk as $row) {
         if (!empty($row['post_parent_raw']) && is_numeric($row['post_parent_raw'])) {
            $parent_ids[] = (int) $row['post_parent_raw'];
         }
      }

      $parent_ids = array_values(array_unique($parent_ids));
      if (empty($parent_ids)) {
         return [];
      }

      $valid_ids = [];
      $post_type_cache = [];
      $chunk_size = 500;

      foreach (array_chunk($parent_ids, $chunk_size) as $ids_chunk) {
         $placeholders = implode(',', array_fill(0, count($ids_chunk), '%d'));
         $query = $wpdb->prepare(
            "SELECT ID, post_type FROM {$wpdb->posts} WHERE ID IN ($placeholders)",
            ...$ids_chunk
         );

         $results = $wpdb->get_results($query, ARRAY_A);

         foreach ($results as $row) {
            $post_id = (int) $row['ID'];
            $post_type = $row['post_type'];

            if (!isset($post_type_cache[$post_type])) {
               $type_obj = get_post_type_object($post_type);
               $post_type_cache[$post_type] = $type_obj && !empty($type_obj->hierarchical);
            }

            if ($post_type_cache[$post_type]) {
               $valid_ids[$post_id] = true;
            }
         }
      }

      return $valid_ids;
   }
   // phpcs:enable

   // Validate Post Parent
   private static function cipfa_is_valid_post_parent($post_parent, $valid_parent_ids)
   {
      return isset($valid_parent_ids[$post_parent]);
   }

   // Update Post Parents
   // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
   private static function cipfa_bulk_update_post_parents($post_parents_to_set, $valid_parent_ids)
   {
      global $wpdb;

      $cases = [];
      $ids = [];

      foreach ($post_parents_to_set as $entry) {
         $post_id   = isset($entry['post_id']) ? (int) $entry['post_id'] : 0;
         $parent_id = isset($entry['post_parent']) ? (int) $entry['post_parent'] : 0;

         if ($post_id <= 0 || $parent_id <= 0 || !isset($valid_parent_ids[$parent_id])) {
            continue;
         }

         $ids[]   = $post_id;
         $cases[] = $wpdb->prepare("WHEN %d THEN %d", $post_id, $parent_id);
      }

      if (!empty($cases) && !empty($ids)) {

         $chunk_size = 500;
         $id_chunks  = array_chunk($ids, $chunk_size);

         foreach ($id_chunks as $chunk) {
            $chunk_cases = array_filter($cases, function ($case) use ($chunk) {
               preg_match('/WHEN (\d+) THEN (\d+)/', $case, $matches);
               return isset($matches[1]) && in_array((int)$matches[1], $chunk, true);
            });

            $sql = "UPDATE {$wpdb->posts} 
                    SET post_parent = CASE ID " . implode(' ', $chunk_cases) . " END
                    WHERE ID IN (" . implode(',', $chunk) . ")";
            $result = $wpdb->query($sql);

            if ($result === false) {
               CIPFA_Logs::cipfa_Log('Bulk parent update failed — fallback insert triggered.');

               foreach ($chunk as $post_id) {
                  $parent_id = null;
                  foreach ($post_parents_to_set as $entry) {
                     if (
                        (int)$entry['post_id'] === $post_id
                     ) {
                        $parent_id = (int)$entry['post_parent'];
                        break;
                     }
                  }

                  if ($parent_id && isset($valid_parent_ids[$parent_id])) {
                     $update_result = wp_update_post([
                        'ID'          => $post_id,
                        'post_parent' => $parent_id,
                     ], true);

                     if (is_wp_error($update_result)) {
                        CIPFA_Logs::cipfa_Log(
                           "Fallback update failed for post {$post_id}: " . $update_result->get_error_message()
                        );
                     }
                  }
               }
            }
         }
      }
   }
   // phpcs:enable

   // Validate Page template
   private static function cipfa_is_valid_page_template($template_label)
   {
      if (empty($template_label)) {
         return false;
      }

      // Get active theme’s templates
      $valid_templates = wp_get_theme()->get_page_templates();
      $normalized_label = trim(strtolower($template_label));

      foreach ($valid_templates as $file => $label) {
         if (strtolower($label) === $normalized_label) {
            return $file;
         }
      }
      return false;
   }

   // Handle Post Author
   private static function cipfa_handle_post_author($post_author, $post_author_email, $author_password = null)
   {
      $created_user = false;
      $user_id = get_current_user_id();

      if (!empty($post_author)) {

         // Try finding the user
         if (is_numeric($post_author)) {
            $user = get_user_by('ID', $post_author);
         } else {
            $user = get_user_by('login', $post_author);
            if (!$user) {
               $user = get_user_by('email', $post_author);
            }
         }

         if ($user) {
            $user_id = $user->ID;
         } else {

            // Create new user
            $username = sanitize_user($post_author);
            $email = sanitize_email($post_author_email);

            $author_password = !empty($author_password) ? $author_password : wp_generate_password();

            $userdata = array(
               'user_login' => $username,
               'user_pass'  => $author_password,
               'user_email' => $email,
               'role'       => 'author'
            );

            $new_id = wp_insert_user($userdata);

            if (!is_wp_error($new_id)) {
               $user_id = $new_id;
               $created_user = true;
            } else {
               $current_user = wp_get_current_user();
               CIPFA_Logs::cipfa_Log(
                  'Error creating author with email ' . $post_author_email .
                     ': ' . $new_id->get_error_message() .
                     '. Using current user ' . $current_user->user_login
               );
               $user_id = get_current_user_id();
            }
         }
      }

      if ($created_user) {
         update_option('cipfa_imported_user', true);
      }

      return $user_id;
   }

   // Insert or Update Post
   // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter, PluginCheck.Security.DirectDB.PrepareSQL
   private static function cipfa_insert_or_update_post_bulk($posts_data, $existing_skus, $edit_existing = false, $csv_ids = [])
   {
      global $wpdb;

      $table = $wpdb->posts;
      $insert_values = [];
      $update_rows = [];

      // Valid statuses and mapping
      $valid_statuses = [
         'publish', 'future', 'draft', 'pending', 'private', 'trash', 'auto-draft', 'inherit'
      ];
      $status_map = [
         'published' => 'publish',
         'scheduled' => 'future',
         'schedule'  => 'future',
         'trashed'   => 'trash',
         1           => 'publish',
         0           => 'draft',
         -1          => 'pending',
         2           => 'future',
         3           => 'private'
      ];

      $affected_ids = [];

      foreach ($posts_data as $index => $post_data) {
         $csv_post_id = isset($csv_ids[$index]) ? intval($csv_ids[$index]) : intval($post_data['csv_post_id'] ?? 0);

         // Skip non-simple WooCommerce products
         if (
            isset($post_data['product_fields']['_product_type']) &&
            $post_data['product_fields']['_product_type'] !== 'simple'
         ) {
            continue;
         }

         // Normalize post status
         $status = strtolower($post_data['post_status'] ?? '');
         if (array_key_exists($status, $status_map)) {
            $status = $status_map[$status];
         }
         if ($status === '' || !in_array($status, $valid_statuses)) {
            $status = 'publish';
         }

         // Fallback title
         if (empty($post_data['post_title']) && !$edit_existing) {
            $post_type_name = match ($post_data['post_type']) {
               'post' => 'post_title',
               'page' => 'page_title',
               'product' => 'product_name',
               default => 'title',
            };
            $post_data['post_title'] = 'No ' . $post_type_name . ' provided in CSV';
         }

         // Fallback author
         if (
            !$edit_existing &&
            (empty($post_data['post_author']) ||
               !is_numeric($post_data['post_author']) ||
               intval($post_data['post_author']) <= 0)
         ) {
            $post_data['post_author'] = get_current_user_id();
         }

         // Fallback post date
         if (
            !$edit_existing &&
            (empty($post_data['post_date']) ||
               strtotime($post_data['post_date']) === false)
         ) {
            $post_data['post_date'] = current_time('mysql');
         }

         $pf = $post_data['product_fields'] ?? [];
         $sku = isset($pf['_sku']) ? trim($pf['_sku']) : '';

         // Update existing posts
         if ($edit_existing && ($csv_post_id > 0 || ($sku !== '' && isset($existing_skus[$sku])))) {

            $target_id = $csv_post_id > 0 ? $csv_post_id : intval($existing_skus[$sku]);

            $existing_post = get_post($target_id);
            if (!$existing_post) {
               CIPFA_Logs::cipfa_Log('Failed to find post with ID ' . $target_id . '. Update aborted.');
               continue;
            }

            if ($existing_post->post_type === 'attachment') {
               CIPFA_Logs::cipfa_Log('Post ID ' . $target_id . ' is an attachment. Update aborted.');
               continue;
            }

            $update_rows[] = [
               'ID'                => $target_id,
               'post_title'        => !empty($post_data['post_title']) ? $post_data['post_title'] : $existing_post->post_title,
               'post_content'      => !empty($post_data['post_content']) ? $post_data['post_content'] : $existing_post->post_content,
               'post_date' => !empty($post_data['post_date'])
                  ? $post_data['post_date']
                  : $existing_post->post_date,
               'post_date_gmt' => !empty($post_data['post_date'])
                  ? get_gmt_from_date($post_data['post_date'])
                  : $existing_post->post_date_gmt,
               'post_excerpt'      => !empty($post_data['post_excerpt']) ? $post_data['post_excerpt'] : $existing_post->post_excerpt,
               'post_status' => array_key_exists('post_status', $post_data)
                  ? $status
                  : $existing_post->post_status,
               'comment_status'    => !empty($post_data['comment_status']) ? $post_data['comment_status'] : $existing_post->comment_status,
               'ping_status'       => !empty($post_data['ping_status']) ? $post_data['ping_status'] : $existing_post->ping_status,
               'post_password'     => !empty($post_data['post_password']) ? $post_data['post_password'] : $existing_post->post_password,
               'post_name'         => !empty($post_data['post_name']) ? $post_data['post_name'] : $existing_post->post_name,
               'post_parent'       => !empty($post_data['post_parent']) ? intval($post_data['post_parent']) : intval($existing_post->post_parent),
               'menu_order'        => !empty($post_data['menu_order']) ? intval($post_data['menu_order']) : intval($existing_post->menu_order),
               'post_modified'     => current_time('mysql'),
               'post_modified_gmt' => get_gmt_from_date(current_time('mysql')),
            ];

            continue;
         }

         // Insert new posts
         $insert_values[] = [
            'post_author' => intval($post_data['post_author'] ?? get_current_user_id()),
            'post_date' => $post_data['post_date'] ?? current_time('mysql'),
            'post_date_gmt' => get_gmt_from_date($post_data['post_date'] ?? current_time('mysql')),
            'post_content' => $post_data['post_content'] ?? '',
            'post_title' => $post_data['post_title'] ?? '',
            'post_excerpt' => $post_data['post_excerpt'] ?? '',
            'post_status' => $status,
            'comment_status' => $post_data['comment_status'] ?? 'open',
            'ping_status' => $post_data['ping_status'] ?? 'open',
            'post_password' => $post_data['post_password'] ?? '',
            'post_name' => !empty($post_data['post_name'])
               ? $post_data['post_name']
               : sanitize_title($post_data['post_title'] ?? ''),
            'to_ping' => '',
            'pinged' => '',
            'post_modified' => current_time('mysql'),
            'post_modified_gmt' => get_gmt_from_date(current_time('mysql')),
            'post_content_filtered' => '',
            'post_parent' => intval($post_data['post_parent'] ?? 0),
            'menu_order' => intval($post_data['menu_order'] ?? 0),
            'post_type' => sanitize_text_field($post_data['post_type']),
            'post_mime_type' => '',
            'comment_count' => 0,
         ];
      }

      //Bulk update existing posts
      if (!empty($update_rows)) {
         $max_rows_per_query = 50;
         $update_chunks = array_chunk(
            $update_rows,
            $max_rows_per_query
         );
         foreach ($update_chunks as $chunk) {
            $ids = [];
            $cases = [
               'post_title' => [],
               'post_content' => [],
               'post_excerpt' => [],
               'post_status' => [],
               'comment_status' => [],
               'ping_status' => [],
               'post_password' => [],
               'post_name' => [],
               'post_parent' => [],
               'menu_order' => [],
               'post_date' => [],
               'post_date_gmt' => [],
               'post_modified' => [],
               'post_modified_gmt' => [],
            ];

            foreach ($chunk as $row) {
               $id = intval($row['ID']);
               $ids[] = $id;

               foreach ($cases as $col => &$list) {
                  $list[] = "WHEN {$id} THEN " . $wpdb->prepare('%s', $row[$col]);
               }
            }

            // build dynamic CASE expressions
            $set_clauses = [];
            foreach ($cases as $col => $list) {
               if (!empty($list)) {
                  $set_clauses[] = "{$col} = CASE ID " . implode(' ', $list) . " END";
               }
            }

            $sql = "UPDATE {$table} SET " . implode(", ", $set_clauses) . " WHERE ID IN (" . implode(',', $ids) . ")";
            $result = $wpdb->query($sql);

            if ($result === false) {
               CIPFA_Logs::cipfa_Log('Bulk update chunk failed. SQL Error: ' . $wpdb->last_error);
            } else {
               $affected_ids = array_merge($affected_ids, $ids);
            }
         }
      }

      // Bulk insert new posts
      if (!empty($insert_values)) {
         $columns = array_keys($insert_values[0]);
         $column_list = implode(', ', array_map(fn ($c) => "`$c`", $columns));
         $max_rows_per_query = 50;

         $insert_chunks = array_chunk($insert_values, $max_rows_per_query);
         foreach ($insert_chunks as $chunk) {
            $rows_sql = [];
            $params = [];

            foreach ($chunk as $row) {
               $placeholders = [];
               foreach ($row as $value) {
                  $placeholders[] = '%s';
                  $params[] = $value;
               }
               $rows_sql[] = '(' . implode(', ', $placeholders) . ')';
            }

            $sql = "INSERT INTO {$table} ({$column_list}) VALUES " . implode(', ', $rows_sql);
            $result = $wpdb->query($wpdb->prepare($sql, $params));

            if ($result === false || $result === 0) {

               CIPFA_Logs::cipfa_Log('Failed to insert posts in bulk. Database error: ' . $wpdb->last_error . ' — fallback insert triggered.');

               // Fallback to single insert
               foreach ($chunk as $row) {
                  $post_id = wp_insert_post($row, true);
                  if (is_wp_error($post_id)) {
                     CIPFA_Logs::cipfa_Log('Fallback single post insert failed: ' . $post_id->get_error_message());
                     continue;
                  }
                  $affected_ids[] = $post_id;
               }
               continue;
            }

            $first_id = $wpdb->insert_id;
            $chunk_count = count($chunk);
            for ($i = 0; $i < $chunk_count; $i++) {
               $affected_ids[] = $first_id + $i;
            }
         }
      }

      return $affected_ids;
   }
   /* phpcs:enable */

   // Find existing attachments by file path
   public static function cipfa_find_existing_attachments($normalized_url)
   {
      $duplicate_path = get_option('cipfa_duplicate_media_path');

      if (empty($duplicate_path) || !file_exists($duplicate_path)) {
         return null;
      }

      $stored_duplicate_media = json_decode(file_get_contents($duplicate_path), true);
      if (empty($stored_duplicate_media) || !is_array($stored_duplicate_media)) {
         return null;
      }

      // Return attachment ID if exists
      if (isset($stored_duplicate_media[$normalized_url]) && !empty($stored_duplicate_media[$normalized_url])) {
         return $stored_duplicate_media[$normalized_url];
      }

      return null;
   }

   // Extract media urls from chunk
   public static function cipfa_extract_media_urls_from_chunk($chunk)
   {
      $media_urls = [];
      $site_url   = get_site_url();
      $site_host  = wp_parse_url($site_url, PHP_URL_HOST);
      $file_pattern = '/\.(jpg|jpeg|png|gif|bmp|webp|ico|svg|txt|pdf|doc|docx|ppt|pptx|pps|ppsx|odt|xls|xlsx|mp3|m4a|ogg|wav|mp4|m4v|mov|wmv|avi|mpg|ogv|3gp|3g2|webm|zip|tar|gz|ttf|otf|woff|woff2|csv|json)$/i';

      foreach ($chunk as $row) {
         $iterator = new \RecursiveIteratorIterator(new \RecursiveArrayIterator($row));

         foreach ($iterator as $key => $value) {
            if (empty($value) || !is_string($value)) {
               continue;
            }

            // Extract URLs from plain text
            preg_match_all('/https?:\/\/[^\s,"\'<>]+/i', $value, $matches_text);

            // Extract URLs from HTML attributes
            preg_match_all('/(?:src|href)\s*=\s*["\'](https?:\/\/[^"\']+)["\']/i', $value, $matches_html);

            $urls = array_merge($matches_text[0] ?? [], $matches_html[1] ?? []);

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

            foreach ($urls as $url) {
               $url = esc_url_raw(trim($url));

               // Skip invalid URLs
               if (empty($url)) {
                  continue;
               }

               // Validate extension
               if (!preg_match($file_pattern, $url)) {
                  continue;
               }

               $media_urls[] = $url;
            }
         }
      }

      // Unique, cleaned URLs
      $media_urls = array_unique($media_urls);
      return array_values($media_urls);
   }

   // Bulk register media
   public static function cipfa_bulk_register_media($urls)
   {
      if (empty($urls) || !is_array($urls)) {
         return [];
      }

      return self::cipfa_bulk_register_media_chunked($urls);
   }

   // Bulk register media chunked
   // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.WP.AlternativeFunctions.rename_rename
   public static function cipfa_bulk_register_media_chunked($urls)
   {
      global $wpdb;

      $registered = [];

      $stored_duplicate_media = [];
      $duplicate_file = get_option('cipfa_duplicate_media_path');

      if ($duplicate_file && file_exists($duplicate_file)) {
         $stored_duplicate_media = json_decode(file_get_contents($duplicate_file), true) ?: [];
      }

      $duplicate_index = array_fill_keys(array_keys($stored_duplicate_media), true);

      $upload_dir = wp_upload_dir();
      $now = current_time('mysql');

      // Get max packet
      $max_packet_result = $wpdb->get_var("SELECT @@max_allowed_packet");
      $max_allowed_packet = $max_packet_result ? (int)$max_packet_result : (16 * 1024 * 1024);
      $max_query_size = $max_allowed_packet * 0.8;

      // Estimate sizes
      $estimated_row_size = 800;
      $query_overhead      = 5000;
      $size_based_chunk_size = floor(($max_query_size - $query_overhead) / $estimated_row_size);

      $default_chunk_size = min(50, $size_based_chunk_size);
      $chunk_size = apply_filters('cipfa_bulk_media_chunk_size', $default_chunk_size);
      $chunk_size = max(10, min(200, $chunk_size));

      // Pre-split main chunks
      $url_chunks = array_chunk($urls, $chunk_size);

      $leftover_urls = [];

      foreach ($url_chunks as $chunk_index => $url_chunk) {

         // Merge leftover URLs to the beginning of this chunk
         if (!empty($leftover_urls)) {
            $url_chunk = array_merge($leftover_urls, $url_chunk);
            $leftover_urls = [];
         }

         $insert_rows = [];
         $meta_rows   = [];
         $file_map    = [];

         // Loop through URLs inside this chunk
         foreach ($url_chunk as $url_index => $url) {

            if (isset($duplicate_index[$url])) {
               $existing_attachment = $stored_duplicate_media[$url];
               $wp_media_id = $existing_attachment ? $existing_attachment : attachment_url_to_postid($url);

               if ($wp_media_id) {
                  $registered[$url] = [
                     'id'  => $wp_media_id,
                     'url' => wp_get_attachment_url($wp_media_id),
                  ];
                  continue;
               }
            }

            $wp_media_id = attachment_url_to_postid($url);
            if ($wp_media_id) {
               $registered[$url] = [
                  'id'  => $wp_media_id,
                  'url' => wp_get_attachment_url($wp_media_id),
               ];
               $stored_duplicate_media[$url] = $wp_media_id;
               $duplicate_index[$url] = true;
               continue;
            }

            $filename = sanitize_file_name(basename(wp_parse_url($url, PHP_URL_PATH)));
            $filetype = wp_check_filetype($filename);
            if (empty($filetype['type'])) continue;

            $destination_path = trailingslashit($upload_dir['path']) . $filename;
            $relative_file    = str_replace(trailingslashit($upload_dir['basedir']), '', $destination_path);
            $guid             = trailingslashit($upload_dir['url']) . $filename;

            // mapping to local file system
            $media_path = str_replace(
               wp_normalize_path(trailingslashit(home_url())),
               wp_normalize_path(ABSPATH),
               $url
            );
            $media_path = wp_normalize_path($media_path);

            if (!file_exists($media_path) && stripos($url, $upload_dir['baseurl']) === 0) {
               $relative   = str_replace(trailingslashit($upload_dir['baseurl']), '', $url);
               $media_path = trailingslashit($upload_dir['basedir']) . $relative;
            }

            // Copy file if exists locally
            if (file_exists($media_path)) {
               if (realpath($media_path) !== realpath($destination_path)) {
                  @copy($media_path, $destination_path);
               }
            } else {
               // Remote fetch validation
               $response = wp_remote_head($url);
               if (is_wp_error($response) || wp_remote_retrieve_response_code($response) >= 400) {
                  CIPFA_Logs::cipfa_Log("Media file not found. URL: {$url}");
                  continue;
               }

               $max_attempts = 3;
               $attempt = 0;
               $copied = false;

               while ($attempt < $max_attempts && !$copied) {
                  $copied = @copy($url, $destination_path);
                  if (!$copied) {
                     $attempt++;
                     if ($attempt < $max_attempts) {
                        usleep(500000 * (2 ** ($attempt - 1)));
                     }
                  }
               }

               if (!$copied) {
                  CIPFA_Logs::cipfa_Log("Failed to copy media after {$max_attempts} attempts: {$url}");
                  continue;
               }
            }

            $current_row = $wpdb->prepare(
               "(%d, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
               get_current_user_id(),
               sanitize_file_name($filename),
               '',
               'inherit',
               'attachment',
               $now,
               $now,
               $guid,
               $filetype['type'],
               ''
            );

            $estimated_query_size =
               strlen("INSERT INTO {$wpdb->posts} (...) VALUES ") +
               (count($insert_rows) * strlen($current_row)) +
               (count($insert_rows) - 1) +
               strlen($current_row);

            if (
               $estimated_query_size > $max_query_size * 0.9 && !empty($insert_rows)
            ) {
               CIPFA_Logs::cipfa_Log("Query size limit approaching, processing batch early.");

               $leftover_urls = array_slice($url_chunk, $url_index);

               break;
            }

            $insert_rows[] = $current_row;

            $file_map[$url] = [
               'file' => ltrim($relative_file, '/'),
               'guid' => $guid,
            ];
         }

         if (empty($insert_rows)) continue;

         $sql = "
            INSERT INTO {$wpdb->posts}
            (post_author, post_title, post_content, post_status, post_type, post_date, post_date_gmt, guid, post_mime_type, post_excerpt)
            VALUES " . implode(',', $insert_rows);

         $result = $wpdb->query($sql);

         if ($result === false || $result === 0) {
            CIPFA_Logs::cipfa_Log("Failed to insert media batch {$chunk_index}: {$wpdb->last_error} — fallback insert triggered.");
            self::cipfa_process_individual_fallback($url_chunk, $registered, $file_map, $upload_dir, $stored_duplicate_media);
            continue;
         }

         $first_id = $wpdb->insert_id;
         $count    = count($insert_rows);
         $ids      = range($first_id, $first_id + $count - 1);

         $i = 0;
         foreach ($file_map as $original_url => $info) {
            $id = $ids[$i++];
            if (!$id) continue;

            $stored_duplicate_media[$original_url] = $id;
            $duplicate_index[$original_url] = true;

            $meta_rows[] = [$id, '_wp_attached_file', $info['file']];

            $registered[$original_url] = [
               'id'  => $id,
               'url' => $info['guid'],
            ];
         }

         if (!empty($meta_rows)) {
            self::cipfa_process_meta_in_chunks($meta_rows, $chunk_size);
         }

         $meta_rows_extra = [];
         foreach ($registered as $url => $entry) {
            if (empty($entry['id']) || empty($file_map[$url]['file'])) continue;

            $relative_path = ltrim($file_map[$url]['file'], '/');
            $attach_data   = maybe_serialize([
               'file'  => $relative_path,
               'sizes' => []
            ]);

            $meta_rows_extra[] = [$entry['id'], '_wp_attachment_metadata', $attach_data];
         }

         if (!empty($meta_rows_extra)) {
            self::cipfa_process_meta_in_chunks($meta_rows_extra, $chunk_size);
         }

         usleep(max(50000, min(500000, count($insert_rows) * 10000)));
      }

      if ($duplicate_file) {
         $temp_file = $duplicate_file . '.tmp';
         file_put_contents($temp_file, json_encode($stored_duplicate_media, JSON_PRETTY_PRINT));
         rename($temp_file, $duplicate_file);
      }

      // Schedule thumbnail generation
      self::cipfa_schedule_thumbnail_generation($registered, $chunk_size);

      return $registered;
   }
   // phpcs:enable

   // Fallback method for individual inserts when batch fails
   private static function cipfa_process_individual_fallback($url_chunk, &$registered, $file_map, $upload_dir, &$stored_duplicate_media)
   {
      global $wpdb;

      foreach ($url_chunk as $url) {
         if (!isset($file_map[$url])) continue;

         $info = $file_map[$url];
         $filename = sanitize_file_name(basename(wp_parse_url($url, PHP_URL_PATH)));
         $filetype = wp_check_filetype($filename);

         $post_data = [
            'post_author'    => get_current_user_id(),
            'post_title'     => $filename,
            'post_content'   => '',
            'post_status'    => 'inherit',
            'post_type'      => 'attachment',
            'post_date'      => current_time('mysql'),
            'post_date_gmt'  => current_time('mysql', 1),
            'guid'           => $info['guid'],
            'post_mime_type' => $filetype['type'],
            'post_excerpt'   => ''
         ];

         $attachment_id = wp_insert_post($post_data, true);

         if (!is_wp_error($attachment_id)) {
            update_post_meta($attachment_id, '_wp_attached_file', $info['file']);

            $relative_path = str_replace(
               trailingslashit($upload_dir['basedir']),
               '',
               $upload_dir['basedir'] . '/' . $info['file']
            );

            $attach_data = [
               'file'  => ltrim($relative_path, '/'),
               'sizes' => [],
            ];
            update_post_meta($attachment_id, '_wp_attachment_metadata', $attach_data);

            $stored_duplicate_media[$url] = $attachment_id;
            $registered[$url] = [
               'id'  => $attachment_id,
               'url' => $info['guid'],
            ];
         }
      }
   }

   // Process meta inserts in chunks if query is too large
   // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
   private static function cipfa_process_meta_in_chunks($meta_rows, $meta_chunk_size)
   {
      global $wpdb;

      $meta_chunks = array_chunk($meta_rows, $meta_chunk_size);

      foreach ($meta_chunks as $meta_chunk) {

         $placeholders = [];
         $values = [];

         foreach ($meta_chunk as $row) {
            $placeholders[] = "(%d, %s, %s)";
            $values[] = $row[0];
            $values[] = $row[1];
            $values[] = $row[2];
         }

         $sql = "
               INSERT INTO {$wpdb->postmeta} (post_id, meta_key, meta_value)
               VALUES " . implode(',', $placeholders) . "
               ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)
           ";

         $prepared = $wpdb->prepare(
            $sql,
            $values
         );
         $result = $wpdb->query($prepared);

         // Fallback
         if ($result === false || $result === 0) {
            CIPFA_Logs::cipfa_Log("Failed to insert media meta chunk. Error: " . $wpdb->last_error . " — fallback insert triggered.");

            foreach ($meta_chunk as $row) {
               update_post_meta($row[0], $row[1], $row[2]);
            }
         }

         usleep(50000);
      }
   }
   // phpcs:enable

   // Schedule thumbnail generation with chunking
   private static function cipfa_schedule_thumbnail_generation($registered, $chunk_size)
   {
      $remote_addr = isset($_SERVER['REMOTE_ADDR'])
         ? sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR']))
         : '';
      $is_local = in_array($remote_addr, ['127.0.0.1', '::1']) || strpos(home_url(), 'localhost') !== false;
      $registered_chunks = array_chunk($registered, $chunk_size, true);

      foreach ($registered_chunks as $thumb_chunk) {
         foreach ($thumb_chunk as $entry) {
            if (empty($entry['id'])) continue;

            if ($is_local) {
               if (method_exists(__CLASS__, 'cipfa_generate_thumb_for_attachment')) {
                  self::cipfa_generate_thumb_for_attachment($entry['id']);
               }
            } else {
               if (function_exists('as_enqueue_async_action')) {
                  $action_id = as_enqueue_async_action(
                     'cipfa_generate_thumb_for_attachment',
                     ['attachment_id' => $entry['id']],
                     'cipfa_import_thumbs'
                  );

                  if (empty($action_id) || !as_has_scheduled_action('cipfa_generate_thumb_for_attachment', ['attachment_id' => $entry['id']])) {
                     if (method_exists(__CLASS__, 'cipfa_generate_thumb_for_attachment')) {
                        self::cipfa_generate_thumb_for_attachment($entry['id']);
                     }
                  }
               } else {
                  if (method_exists(__CLASS__, 'cipfa_generate_thumb_for_attachment')) {
                     self::cipfa_generate_thumb_for_attachment($entry['id']);
                  }
               }
            }
         }
         // 0.05 second delay
         usleep(50000);
      }
   }

   //Set unique slug
   // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter, PluginCheck.Security.DirectDB.PrepareSQL
   private static function cipfa_generate_unique_slug($slug, $term_table, $tt_table, $taxonomy)
   {
      global $wpdb;

      $original_slug = $slug;
      $counter = 2;

      while (true) {
         $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT t.term_id FROM {$term_table} t
             INNER JOIN {$tt_table} tt ON tt.term_id = t.term_id
             WHERE t.slug = %s AND tt.taxonomy = %s",
            $slug,
            $taxonomy
         ));

         if (!$exists) {
            break;
         }

         $slug = $original_slug . '-' . $counter;
         $counter++;
      }

      return $slug;
   }
   // phpcs:enable

   //Set Categories
   // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter, PluginCheck.Security.DirectDB.PrepareSQL
   private static function cipfa_bulk_set_categories($post_type, $category_sets)
   {
      global $wpdb;

      if (empty($category_sets)) {
         return;
      }

      $taxonomy   = 'category';
      $term_table = $wpdb->terms;
      $tt_table   = $wpdb->term_taxonomy;
      $rel_table  = $wpdb->term_relationships;
      $batch_size = 500;

      $cache = [];
      $created_categories = false;
      $pending_rel = [];

      $existing_terms = $wpdb->get_results(
         $wpdb->prepare("
            SELECT t.term_id, tt.term_taxonomy_id, t.name, tt.parent
            FROM {$term_table} t
            INNER JOIN {$tt_table} tt ON tt.term_id = t.term_id
            WHERE tt.taxonomy = %s
        ", $taxonomy)
      );

      foreach ($existing_terms as $term) {
         $key = strtolower($term->name) . '|' . intval($term->parent);
         $cache[$key] = [
            'term_id' => intval($term->term_id),
            'tt_id'   => intval($term->term_taxonomy_id)
         ];
      }

      foreach ($category_sets as $set) {
         $post_id = intval($set['post_id']);
         $post_categories = $set['categories'] ?? [];
         if (empty($post_categories)) continue;

         foreach ($post_categories as $path) {
            $categories = array_map('trim', explode('>', html_entity_decode($path)));

            if (empty($categories)) continue;

            $parent_id = 0;
            $last_tt_id = 0;

            foreach ($categories as $category_name) {
               if ($category_name === '') continue;

               $key = strtolower($category_name) . '|' . $parent_id;

               if (!isset($cache[$key])) {
                  $slug = self::cipfa_generate_unique_slug(
                     sanitize_title($category_name),
                     $term_table,
                     $tt_table,
                     $taxonomy
                  );
                  $wpdb->insert($term_table, [
                     'name' => $category_name,
                     'slug' => $slug
                  ], ['%s', '%s']);
                  $term_id = $wpdb->insert_id;
                  $wpdb->insert($tt_table, [
                     'term_id' => $term_id,
                     'taxonomy' => $taxonomy,
                     'parent' => $parent_id
                  ], ['%d', '%s', '%d']);

                  $tt_id = $wpdb->insert_id;

                  $cache[$key] = [
                     'term_id' => $term_id,
                     'tt_id' => $tt_id,
                  ];

                  $created_categories = true;
               }

               $term_info = $cache[$key];
               $last_tt_id = $term_info['tt_id'];
               $parent_id = $term_info['term_id'];
            }

            if ($last_tt_id > 0) {
               $pending_rel[] = $wpdb->prepare("(%d, %d)", $post_id, $last_tt_id);
            }

            // Batch insert relationships
            if (count($pending_rel) >= $batch_size) {
               $sql = "INSERT IGNORE INTO {$rel_table} (object_id, term_taxonomy_id) VALUES " . implode(',', $pending_rel);
               $wpdb->query($sql);
               $pending_rel = [];
            }
         }
      }

      // Insert remaining relationships
      if (!empty($pending_rel)) {
         $sql = "INSERT IGNORE INTO {$rel_table} (object_id, term_taxonomy_id) VALUES " . implode(',', $pending_rel);
         $wpdb->query($sql);
      }

      // Update category counts
      $wpdb->query($wpdb->prepare(
         "UPDATE {$tt_table} tt
         LEFT JOIN (
             SELECT tr.term_taxonomy_id, COUNT(DISTINCT tr.object_id) as cnt
             FROM {$rel_table} tr
             INNER JOIN {$wpdb->posts} p ON p.ID = tr.object_id
             WHERE p.post_type = %s
             GROUP BY tr.term_taxonomy_id
         ) rel_count ON tt.term_taxonomy_id = rel_count.term_taxonomy_id
         SET tt.count = IFNULL(rel_count.cnt, 0)
         WHERE tt.taxonomy = %s",
         $post_type,
         $taxonomy
      ));

      if ($created_categories) {
         update_option('cipfa_imported_categories', true);
      }
   }
   // phpcs:enable

   //Set Custom texonomy
   // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter,PluginCheck.Security.DirectDB.PrepareSQL
   private static function cipfa_bulk_set_custom_taxonomy_terms($post_type, $taxonomy_sets)
   {
      global $wpdb;

      if (empty($taxonomy_sets)) {
         return;
      }

      $term_table  = $wpdb->terms;
      $tt_table    = $wpdb->term_taxonomy;
      $rel_table   = $wpdb->term_relationships;
      $batch_size  = 500;

      $cache = [];
      $created_any_term = false;
      $pending_rel = [];

      $used_taxonomies = [];

      foreach ($taxonomy_sets as $set) {
         if (empty($set['taxonomies'])) {
            continue;
         }

         foreach ($set['taxonomies'] as $taxonomy => $_terms) {
            $taxonomy = sanitize_key($taxonomy);

            if (taxonomy_exists($taxonomy)) {
               $used_taxonomies[$taxonomy] = true;
            }
         }
      }

      if (empty($used_taxonomies)) {
         return;
      }

      $taxonomies = array_keys($used_taxonomies);

      // Load only relevant existing terms
      $existing_terms = $wpdb->get_results(
         $wpdb->prepare(
            "SELECT t.term_id, tt.term_taxonomy_id, t.name, tt.parent, tt.taxonomy
             FROM {$term_table} t
             INNER JOIN {$tt_table} tt ON tt.term_id = t.term_id
             WHERE tt.taxonomy IN (" . implode(',', array_fill(0, count($taxonomies), '%s')) . ")",
            ...$taxonomies
         )
      );

      foreach ($existing_terms as $term) {
         $key = strtolower($term->taxonomy) . '|' . strtolower($term->name) . '|' . intval($term->parent);
         $cache[$key] = [
            'term_id' => intval($term->term_id),
            'tt_id'   => intval($term->term_taxonomy_id),
         ];
      }

      foreach ($taxonomy_sets as $set) {
         $post_id = intval($set['post_id']);
         $taxonomy_terms = $set['taxonomies'] ?? [];
         if (empty($taxonomy_terms)) continue;

         foreach ($taxonomy_terms as $taxonomy => $terms) {
            $taxonomy = sanitize_key($taxonomy);

            if (!taxonomy_exists($taxonomy)) {
               continue;
            }

            foreach ($terms as $term_path) {
               $hierarchical_terms = array_map(
                  'trim',
                  explode('>', sanitize_text_field(html_entity_decode($term_path)))
               );

               if (empty($hierarchical_terms)) continue;

               $parent_id = 0;
               $last_tt_id = 0;

               foreach ($hierarchical_terms as $term_name) {
                  if ($term_name === '') continue;

                  $key = strtolower($taxonomy) . '|' . strtolower($term_name) . '|' . $parent_id;

                  if (!isset($cache[$key])) {

                     $wpdb->insert(
                        $term_table,
                        [
                           'name' => $term_name,
                           'slug' => self::cipfa_generate_unique_slug(
                              sanitize_title($term_name),
                              $term_table,
                              $tt_table,
                              $taxonomy
                           )
                        ],
                        ['%s', '%s']
                     );

                     $term_id = $wpdb->insert_id;

                     $wpdb->insert($tt_table, [
                        'term_id' => $term_id,
                        'taxonomy' => $taxonomy,
                        'parent' => $parent_id
                     ], ['%d', '%s', '%d']);

                     $tt_id = $wpdb->insert_id;

                     $cache[$key] = [
                        'term_id' => $term_id,
                        'tt_id' => $tt_id,
                     ];

                     $created_any_term = true;
                  }

                  $term_info = $cache[$key];
                  $last_tt_id = $term_info['tt_id'];
                  $parent_id = $term_info['term_id'];
               }

               if ($last_tt_id > 0) {
                  $pending_rel[] = $wpdb->prepare("(%d, %d)", $post_id, $last_tt_id);
               }

               // Batch insert relationships
               if (count($pending_rel) >= $batch_size) {
                  $sql = "INSERT IGNORE INTO {$rel_table} (object_id, term_taxonomy_id) VALUES " . implode(',', $pending_rel);
                  $wpdb->query($sql);
                  $pending_rel = [];
               }
            }
         }
      }

      // Insert remaining relationships
      if (!empty($pending_rel)) {
         $sql = "INSERT IGNORE INTO {$rel_table} (object_id, term_taxonomy_id) VALUES " . implode(',', $pending_rel);
         $wpdb->query($sql);
      }

      // Update term counts
      $wpdb->query($wpdb->prepare(
         "UPDATE {$tt_table} tt
         LEFT JOIN (
             SELECT tr.term_taxonomy_id, COUNT(DISTINCT tr.object_id) as cnt
             FROM {$rel_table} tr
             INNER JOIN {$wpdb->posts} p ON p.ID = tr.object_id
             WHERE p.post_type = %s
             GROUP BY tr.term_taxonomy_id
         ) rel_count ON tt.term_taxonomy_id = rel_count.term_taxonomy_id
         SET tt.count = IFNULL(rel_count.cnt, 0)",
         $post_type
      ));

      if ($created_any_term) {
         update_option('cipfa_imported_custom_taxonomies', true);
      }
   }
   // phpcs:enable

   //Set Tags
   // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
   private static function cipfa_bulk_set_tags($post_type, $tag_sets)
   {
      global $wpdb;

      if (empty($tag_sets)) {
         return;
      }

      $taxonomy   = 'post_tag';
      $term_table = $wpdb->terms;
      $tt_table   = $wpdb->term_taxonomy;
      $rel_table  = $wpdb->term_relationships;
      $batch_size = 500;

      $created_or_changed = false;
      $cache = [];

      $existing = $wpdb->get_results(
         $wpdb->prepare(
            "SELECT t.slug, tt.term_taxonomy_id
          FROM {$term_table} t
          INNER JOIN {$tt_table} tt ON tt.term_id = t.term_id
          WHERE tt.taxonomy = %s",
            $taxonomy
         )
      );

      foreach ($existing as $tag) {
         $cache[$tag->slug] = (int)$tag->term_taxonomy_id;
      }

      $new_tags = [];
      foreach ($tag_sets as $set) {
         $tag_names = array_filter(array_map('trim', (array)($set['tags'] ?? [])));
         foreach ($tag_names as $tag_name) {
            $slug = sanitize_title($tag_name);
            if (!isset($cache[$slug])) {
               $new_tags[$slug] = $tag_name;
            }
         }
      }

      if (!empty($new_tags)) {

         $chunks = array_chunk($new_tags, $batch_size, true);
         $inserted_term_ids = [];

         foreach ($chunks as $chunk) {

            // Insert terms
            $values = [];
            $placeholders = [];

            foreach ($chunk as $tag_name) {
               $values[] = $tag_name;
               $values[] = sanitize_title($tag_name);
               $placeholders[] = "(%s, %s)";
            }

            $sql = "INSERT INTO {$term_table} (name, slug) VALUES " . implode(',', $placeholders);
            $result_terms = $wpdb->query($wpdb->prepare($sql, $values));

            if ($result_terms === false) {
               CIPFA_Logs::cipfa_Log('Bulk tag insert FAILED: ' . $wpdb->last_error);
               continue;
            }

            // Fetch inserted term_ids
            $slugs = array_keys($chunk);

            $terms = $wpdb->get_results(
               $wpdb->prepare(
                  "SELECT term_id, slug FROM {$term_table} WHERE slug IN (" . implode(',', array_fill(0, count($slugs), '%s')) . ")",
                  ...$slugs
               )
            );

            foreach ($terms as $term) {
               $inserted_term_ids[$term->slug] = (int)$term->term_id;
            }

            // Insert term_taxonomy rows
            $values = [];
            $placeholders = [];

            foreach ($inserted_term_ids as $term_id) {
               $values[] = $term_id;
               $values[] = $taxonomy;
               $placeholders[] = "(%d, %s, 0)";
            }

            if ($placeholders) {
               $sql = "INSERT INTO {$tt_table} (term_id, taxonomy, parent) VALUES " . implode(',', $placeholders);
               $result_tt = $wpdb->query($wpdb->prepare($sql, $values));

               if ($result_tt === false) {
                  CIPFA_Logs::cipfa_Log('Bulk tag term_taxonomy insert FAILED: ' . $wpdb->last_error);
               }
            }
         }

         $term_ids = array_values($inserted_term_ids);

         $taxonomy_rows = $wpdb->get_results(
            $wpdb->prepare(
               "SELECT term_id, term_taxonomy_id
       FROM {$tt_table}
       WHERE term_id IN (" . implode(',', array_fill(0, count($term_ids), '%d')) . ")",
               ...$term_ids
            )
         );

         foreach ($taxonomy_rows as $row) {
            $slug = array_search((int)$row->term_id, $inserted_term_ids, true);
            if ($slug !== false) {
               $cache[$slug] = (int)$row->term_taxonomy_id;
            }
         }

         $created_or_changed = true;
      }

      // Insert term relationships
      $pending_rel = [];

      foreach ($tag_sets as $set) {
         $post_id = (int)$set['post_id'];
         $tag_names = array_filter(array_map('trim', (array)($set['tags'] ?? [])));

         foreach ($tag_names as $tag) {
            $slug = sanitize_title($tag);
            if (isset($cache[$slug])) {
               $pending_rel[] = $wpdb->prepare(
                  "(%d, %d)",
                  $post_id,
                  $cache[$slug]
               );
            }
         }

         if (
            count($pending_rel) >= $batch_size
         ) {

            $sql = "INSERT IGNORE INTO {$rel_table} (object_id, term_taxonomy_id) VALUES " . implode(',', $pending_rel);
            $wpdb->query($sql);

            $pending_rel = [];
            $created_or_changed = true;
         }
      }

      if (!empty($pending_rel)) {

         $sql = "INSERT IGNORE INTO {$rel_table} (object_id, term_taxonomy_id) VALUES " . implode(',', $pending_rel);
         $wpdb->query($sql);

         $created_or_changed = true;
      }

      if ($created_or_changed) {

         $wpdb->query(
            $wpdb->prepare(
               "
            UPDATE {$tt_table} tt
            LEFT JOIN (
               SELECT tr.term_taxonomy_id, COUNT(DISTINCT tr.object_id) cnt
               FROM {$rel_table} tr
               INNER JOIN {$wpdb->posts} p ON p.ID = tr.object_id
               WHERE p.post_type = %s
               GROUP BY tr.term_taxonomy_id
            ) rel ON tt.term_taxonomy_id = rel.term_taxonomy_id
            SET tt.count = IFNULL(rel.cnt, 0)
            WHERE tt.taxonomy = %s
            ",
               $post_type,
               $taxonomy
            )
         );

         update_option(
            'cipfa_imported_tags',
            true
         );
      }
   }
   // phpcs:enable

   // Media URL validate
   public static function cipfa_validate_and_log_url($url)
   {
      $url = esc_url_raw($url);

      if (empty($url)) {
         return false;
      }

      // Remove the protocol from the URLs
      $acf_image_no_protocol = preg_replace('#^https?://#', '', $url);
      $site_url_no_protocol = preg_replace('#^https?://#', '', get_site_url());

      // Extract the domain parts from both URLs
      $post_image_domain = wp_parse_url('http://' . $acf_image_no_protocol, PHP_URL_HOST);
      $site_url_domain = wp_parse_url('http://' . $site_url_no_protocol, PHP_URL_HOST);

      // Check if the domains match but protocols are different
      if (
         $post_image_domain === $site_url_domain
      ) {
         $image_protocol = wp_parse_url($url, PHP_URL_SCHEME);
         $site_protocol = wp_parse_url(get_site_url(), PHP_URL_SCHEME);

         if ($image_protocol !== $site_protocol) {
            $corrected_url = str_replace("$image_protocol://", "$site_protocol://", $url);

            CIPFA_Logs::cipfa_Log("Protocol mismatch detected and corrected in images/files URLs");

            return $corrected_url;
         }
      }

      // Skip external URLs
      if (stripos($url, get_site_url()) === false) {
         CIPFA_Logs::cipfa_Log("Uploading images/files from external URLs is a Pro feature. The free version can only use files that are already in your Media Library or elsewhere on this WordPress site.");
         return false;
      }

      return $url;
   }

   // Set Date
   private static function cipfa_set_date_based_on_formats($column_value)
   {
      $column_value = trim((string)$column_value);

      $wp_timezone = wp_timezone();

      if (is_numeric($column_value)) {
         return wp_date('Y-m-d H:i:s', intval($column_value), $wp_timezone);
      }

      $column_value = str_replace(['.', ','], ['-', '/'], $column_value);

      $formats = [
         'Y-m-d H:i:s',
         'Y-m-d H:i',
         'Y-m-d',

         'd/m/Y H:i:s',
         'd/m/Y H:i',
         'd/m/Y',

         'm/d/Y H:i:s',
         'm/d/Y H:i',
         'm/d/Y',

         'd-M-y H:i:s',
         'd-M-y H:i',
         'd-M-y',

         'Y-m-d h:i:s A',
         'Y-m-d h:i A',

         'd/m/Y h:i:s A',
         'd/m/Y h:i A',

         'm/d/Y h:i:s A',
         'm/d/Y h:i A',
      ];

      foreach ($formats as $format) {
         $date_obj = DateTime::createFromFormat($format, $column_value, $wp_timezone);

         if ($date_obj instanceof DateTime) {
            $errors = DateTime::getLastErrors();

            if (
               $date_obj &&
               ($errors === false ||
                  ($errors['warning_count'] === 0 && $errors['error_count'] === 0))
            ) {

               // If time is missing, set current WP time
               if (strpos($format, 'H') === false && strpos($format, 'h') === false) {
                  $current_time = new DateTime('now', $wp_timezone);
                  $date_obj->setTime(
                     (int)$current_time->format('H'),
                     (int)$current_time->format('i'),
                     (int)$current_time->format('s')
                  );
               }

               return $date_obj->format('Y-m-d H:i:s');
            }
         }
      }

      $timestamp = strtotime($column_value);
      if ($timestamp !== false) {
         return wp_date('Y-m-d H:i:s', $timestamp, $wp_timezone);
      }

      // fallback to current WP time
      return current_time('mysql');
   }

   // Prepare product attributes
   private static function cipfa_prepare_product_attributes($product_fields)
   {
      $prepared = [];

      if (!isset($product_fields['product_attribute']) || !is_array($product_fields['product_attribute'])) {
         return $prepared;
      }

      foreach ($product_fields['product_attribute'] as $attr_data) {

         if (!isset($attr_data['name']) || !isset($attr_data['value'])) {
            continue;
         }

         $attr_label = trim((string)$attr_data['name']);
         if ($attr_label === '') {
            continue;
         }

         $attr_slug = sanitize_title($attr_label);
         if ($attr_slug === '') {
            continue;
         }

         // Normalize and clean values
         $raw_values = (array)$attr_data['value'];
         $terms = [];

         foreach ($raw_values as $val) {
            $val = trim((string)$val);
            if ($val !== '') {
               $terms[] = $val;
            }
         }

         // Remove duplicate values
         $terms = array_values(array_unique($terms, SORT_STRING));

         $slugs = [];
         $slug_map = [];

         foreach ($terms as $val) {
            $base_slug = sanitize_title($val);

            if ($base_slug === '') {
               continue;
            }

            $slug = $base_slug;
            $i = 2;

            // prevent duplicate slugs
            while (isset($slug_map[$slug])) {
               $slug = $base_slug . '-' . $i;
               $i++;
            }

            $slug_map[$slug] = true;
            $slugs[] = $slug;
         }

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

         // Visible / Global flags
         $is_visible = isset($attr_data['visible']) ? (int)$attr_data['visible'] : 1;
         $is_global  = isset($attr_data['global']) ? (int)$attr_data['global'] : 1;

         if ($is_global) {
            $taxonomy_name = "pa_{$attr_slug}";

            $prepared[$taxonomy_name] = [
               'name'        => $taxonomy_name,
               'label'       => $attr_label,
               'value'       => implode('|', $slugs),
               'terms'       => $terms,
               'term_slugs'  => $slugs,
               'is_visible'  => $is_visible,
               'is_taxonomy' => 1,
            ];
         } else {
            // Non-global attribute
            $prepared[$attr_slug] = [
               'name'        => $attr_label,
               'value'       => implode('|', $terms),
               'is_visible'  => $is_visible,
               'is_taxonomy' => 0,
            ];
         }
      }

      return $prepared;
   }

   // Collect global attributes
   private static function cipfa_collect_global_attributes($product_attributes_bulk)
   {
      $global_attrs = [];

      if (empty($product_attributes_bulk) || !is_array($product_attributes_bulk)) {
         return $global_attrs;
      }

      foreach ($product_attributes_bulk as $item) {
         if (empty($item['attributes']) || !is_array($item['attributes'])) {
            continue;
         }

         foreach ($item['attributes'] as $taxonomy => $attr) {
            if (empty($attr['is_taxonomy'])) continue;

            $labels = $attr['terms'] ?? [];
            $slugs  = $attr['term_slugs'] ?? [];

            if (empty($labels) && empty($slugs)) continue;

            $taxonomy_name = $attr['name'];
            $label = $attr['label'] ?? ucfirst(str_replace('pa_', '', $taxonomy_name));

            if (!isset($global_attrs[$taxonomy_name])) {
               $global_attrs[$taxonomy_name] = [
                  'label' => $label,
                  'terms' => [],
               ];
            }

            if (empty($slugs)) {
               $slugs = array_map('sanitize_title', array_map('trim', (array)$labels));
            }

            foreach ($slugs as $idx => $slug) {
               $slug = (string) trim($slug);
               if ($slug === '') continue;

               $label_for_slug = isset($labels[$idx]) ? trim($labels[$idx]) : $slug;
               if ($label_for_slug === '') $label_for_slug = $slug;

               if (!isset($global_attrs[$taxonomy_name]['terms'][$slug])) {
                  $global_attrs[$taxonomy_name]['terms'][$slug] = $label_for_slug;
               }
            }
         }
      }

      foreach ($global_attrs as $taxonomy => $data) {
         $terms_map = $data['terms'] ?? [];
         $global_attrs[$taxonomy]['terms'] = array_map(
            fn ($s, $l) => ['slug' => $s, 'label' => $l],
            array_keys($terms_map),
            array_values($terms_map)
         );
      }

      return $global_attrs;
   }

   // Bulk insert global attributes
   // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
   private static function cipfa_bulk_insert_global_attributes_sql($global_attrs)
   {
      global $wpdb;

      if (empty($global_attrs) || !is_array($global_attrs)) return;

      $wc_table       = $wpdb->prefix . 'woocommerce_attribute_taxonomies';
      $terms_table    = $wpdb->terms;
      $term_tax_table = $wpdb->term_taxonomy;

      $tax_registered = [];
      $term_cache     = [];
      $term_tax_cache = [];
      $missing_attrs  = [];
      $missing_terms  = [];
      $BATCH_SIZE     = 500;

      foreach ($global_attrs as $taxonomy => $data) {
         $attr_name = str_replace('pa_', '', $taxonomy);
         $label     = $data['label'] ?? ucfirst($attr_name);

         $exists = $wpdb->get_var(
            $wpdb->prepare("SELECT attribute_id FROM {$wc_table} WHERE attribute_name = %s", $attr_name)
         );

         if (!$exists) {
            $missing_attrs[$attr_name] = ['attribute_label' => $label];
         }

         if (empty($tax_registered[$taxonomy])) {
            register_taxonomy($taxonomy, 'product', [
               'label'        => $label,
               'public'       => false,
               'hierarchical' => false,
               'show_ui'      => false,
               'rewrite'      => false,
            ]);
            $tax_registered[$taxonomy] = true;
         }

         // Prepare terms
         foreach ($data['terms'] as $pair) {
            $slug = sanitize_title(trim((string)$pair['slug']));
            $name = trim((string)$pair['label']);
            if ($slug === '') continue;

            $missing_terms[$taxonomy][$slug] = $name;
         }
      }

      // Bulk insert
      if (!empty($missing_attrs)) {
         $chunks = array_chunk($missing_attrs, $BATCH_SIZE, true);
         foreach ($chunks as $chunk) {
            $values = [];
            foreach ($chunk as $attr_name => $attr_data) {
               $values[] = $wpdb->prepare(
                  "(%s, %s, %s, %s, %d)",
                  $attr_name,
                  $attr_data['attribute_label'],
                  'select',
                  'menu_order',
                  1
               );
            }

            $sql = "INSERT INTO {$wc_table} (attribute_name, attribute_label, attribute_type, attribute_orderby, attribute_public) VALUES " . implode(',', $values);
            $result = $wpdb->query($sql);
            if ($result === false) {
               CIPFA_Logs::cipfa_Log('Bulk global attribute insert FAILED. SQL Error: ' . $wpdb->last_error);
            }
         }
      }

      foreach ($missing_terms as $taxonomy => $terms) {
         $slugs = array_keys($terms);
         if (empty($slugs)) continue;

         $chunks = array_chunk($slugs, $BATCH_SIZE);
         foreach ($chunks as $chunk_slugs) {
            $placeholders = implode(',', array_fill(
               0,
               count($chunk_slugs),
               '%s'
            ));

            $sql = "SELECT t.term_id, t.slug, tt.term_taxonomy_id
                    FROM {$terms_table} t
                    INNER JOIN {$term_tax_table} tt ON tt.term_id = t.term_id
                    WHERE tt.taxonomy = %s AND t.slug IN ($placeholders)";

            $rows = $wpdb->get_results($wpdb->prepare($sql, $taxonomy, ...$chunk_slugs), ARRAY_A);
            if ($rows === false) {
               CIPFA_Logs::cipfa_Log("Select existing terms FAILED for taxonomy {$taxonomy}. SQL Error: " . $wpdb->last_error);
            }

            foreach ($rows as $r) {
               $term_cache["{$taxonomy}|{$r['slug']}"] = (int)$r['term_id'];
               $term_tax_cache["{$taxonomy}|{$r['term_id']}"] = (int)$r['term_taxonomy_id'];
               unset($missing_terms[$taxonomy][$r['slug']]);
            }
         }
      }

      foreach ($missing_terms as $taxonomy => $terms) {
         if (empty($terms)) continue;

         $chunks = array_chunk($terms, $BATCH_SIZE, true);
         foreach ($chunks as $chunk) {
            $values = [];
            foreach ($chunk as $slug => $name) {
               $values[] = $wpdb->prepare(
                  "(%s, %s)",
                  $name,
                  $slug
               );
            }

            $sql = "INSERT INTO {$terms_table} (name, slug) VALUES " . implode(',', $values);
            $result = $wpdb->query($sql);
            if ($result === false) {
               CIPFA_Logs::cipfa_Log("Bulk term insert FAILED for taxonomy {$taxonomy}. SQL Error: " . $wpdb->last_error);
            }

            $slugs_list = array_keys($chunk);
            $placeholders = implode(',', array_fill(
               0,
               count($slugs_list),
               '%s'
            ));

            $sql = "SELECT term_id, slug FROM {$terms_table} WHERE slug IN ($placeholders)";
            $rows = $wpdb->get_results($wpdb->prepare($sql, ...$slugs_list), ARRAY_A);
            if ($rows === false) {
               CIPFA_Logs::cipfa_Log("Reload inserted terms FAILED for taxonomy {$taxonomy}. SQL Error: " . $wpdb->last_error);
            }

            foreach ($rows as $r) {
               $term_cache["{$taxonomy}|{$r['slug']}"] = (int)$r['term_id'];
            }
         }
      }

      $term_tax_values = [];
      foreach ($missing_terms as $taxonomy => $terms) {
         foreach ($terms as $slug => $name) {
            $term_id = $term_cache["{$taxonomy}|{$slug}"] ?? 0;
            if ($term_id && !isset($term_tax_cache["{$taxonomy}|{$term_id}"])) {
               $term_tax_values[] = $wpdb->prepare("(%d, %s, %s, %d, %d)", $term_id, $taxonomy, '', 0, 0);
            }
         }
      }

      $term_tax_chunks = array_chunk(
         $term_tax_values,
         $BATCH_SIZE
      );
      foreach ($term_tax_chunks as $chunk) {
         $sql = "INSERT INTO {$term_tax_table} (term_id, taxonomy, description, parent, count) VALUES " . implode(',', $chunk);
         $result = $wpdb->query($sql);
         if ($result === false) {
            CIPFA_Logs::cipfa_Log('Bulk term_taxonomy insert FAILED. SQL Error: ' . $wpdb->last_error);
         }
      }
   }
   // phpcs:enable

   // Bulk assign prepared product attributes
   // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
   private static function cipfa_bulk_insert_product_attributes($product_attributes_bulk)
   {
      global $wpdb;

      if (empty($product_attributes_bulk) || !is_array($product_attributes_bulk)) return;

      $postmeta_table = $wpdb->postmeta;
      $term_rel_table = $wpdb->term_relationships;
      $term_tax_table = $wpdb->term_taxonomy;
      $terms_table = $wpdb->terms;

      $needed_taxonomies = [];
      foreach ($product_attributes_bulk as $product_attr) {
         $attrs = $product_attr['attributes'] ?? [];
         foreach ($attrs as $taxonomy => $attr) {
            if (empty($attr['is_taxonomy'])) continue;
            $term_slugs = !empty($attr['term_slugs']) && is_array($attr['term_slugs'])
               ? $attr['term_slugs']
               : array_map('sanitize_title', array_filter(array_map('trim', explode('|', (string)($attr['value'] ?? '')))));
            foreach ($term_slugs as $s) {
               $s = (string) trim($s);
               if ($s === '') continue;
               $needed_taxonomies[$taxonomy][$s] = true;
            }
         }
      }

      $global_term_cache = [];
      if (!empty($needed_taxonomies)) {
         foreach ($needed_taxonomies as $taxonomy => $slugs_map) {
            $slugs = array_keys($slugs_map);
            $placeholders = implode(',', array_fill(
               0,
               count($slugs),
               '%s'
            ));
            $sql = "SELECT t.term_id, t.slug, tt.term_taxonomy_id
                    FROM {$terms_table} t
                    INNER JOIN {$term_tax_table} tt ON tt.term_id = t.term_id
                    WHERE tt.taxonomy = %s AND t.slug IN ({$placeholders})";
            $params = array_merge([$taxonomy], $slugs);
            $prepared = $wpdb->prepare($sql, $params);
            $rows = $wpdb->get_results($prepared, ARRAY_A);
            if ($rows === false) {
               CIPFA_Logs::cipfa_Log("Failed to select product attribute terms for taxonomy {$taxonomy}. SQL Error: " . $wpdb->last_error);
            }
            foreach ($rows as $r) {
               $global_term_cache["{$taxonomy}|{$r['slug']}"] = [
                  'term_id' => (int)$r['term_id'],
                  'term_tax_id' => (int)$r['term_taxonomy_id']
               ];
            }
         }
      }

      $meta_inserts = [];
      $meta_params = [];
      $term_relationship_inserts = [];
      $term_tax_counts = [];
      $max_rows_per_query = 500;
      $meta_batch_count = 0;

      foreach ($product_attributes_bulk as $product_attr) {
         $post_id = (int)($product_attr['post_id'] ?? 0);
         $attrs   = $product_attr['attributes'] ?? [];

         if (!$post_id || empty($attrs)) continue;

         $meta_value = [];

         foreach ($attrs as $key => $attr) {
            $is_taxonomy = !empty($attr['is_taxonomy']);
            $name        = $attr['name'] ?? '';
            $visible     = isset($attr['is_visible']) ? (int)$attr['is_visible'] : 1;
            $value       = $attr['value'] ?? '';

            if ($name === '') continue;

            $position = 0;

            if (!$is_taxonomy) {
               $val = is_array($value) ? implode(
                  '|',
                  $value
               ) : $value;
               $meta_value[$key] = [
                  'name' => $name,
                  'value' => $val,
                  'position' => $position,
                  'is_visible' => $visible,
                  'is_variation' => 0,
                  'is_taxonomy' => 0,
               ];
               continue;
            }

            // Get term slugs
            $term_slugs = !empty($attr['term_slugs']) && is_array($attr['term_slugs'])
               ? $attr['term_slugs']
               : array_map('sanitize_title', array_filter(array_map('trim', explode('|', (string)$value))));

            if (empty($term_slugs)) continue;

            $meta_value[$key] = [
               'name' => $name,
               'value' => '',
               'position' => $position,
               'is_visible' => $visible,
               'is_variation' => 0,
               'is_taxonomy' => 1,
            ];

            // Assign relationships using preloaded cache
            foreach ($term_slugs as $slug_raw) {
               $slug = sanitize_title(trim((string)$slug_raw));
               if ($slug === '') continue;

               $cache_key = "{$name}|{$slug}";
               if (!empty($global_term_cache[$cache_key])) {
                  $term_tax_id = $global_term_cache[$cache_key]['term_tax_id'];
                  $term_relationship_inserts[] = "($post_id, $term_tax_id)";
                  $term_tax_counts[$term_tax_id] = ($term_tax_counts[$term_tax_id] ?? 0) + 1;
               } else {
                  $term_id = $wpdb->get_var($wpdb->prepare(
                     "SELECT t.term_id FROM {$terms_table} t INNER JOIN {$term_tax_table} tt ON tt.term_id = t.term_id WHERE t.slug = %s AND tt.taxonomy = %s",
                     $slug,
                     $name
                  ));
                  if (!$term_id) continue;
                  $term_tax_id = $wpdb->get_var($wpdb->prepare(
                     "SELECT term_taxonomy_id FROM {$term_tax_table} WHERE term_id = %d AND taxonomy = %s",
                     $term_id,
                     $name
                  ));
                  if (!$term_tax_id) continue;
                  $term_relationship_inserts[] = "($post_id, $term_tax_id)";
                  $term_tax_counts[$term_tax_id] = ($term_tax_counts[$term_tax_id] ?? 0) + 1;
               }
            }
         }

         // add postmeta prepared row
         if (!empty($meta_value)) {
            $meta_inserts[] = $wpdb->prepare("(%d, %s, %s)", $post_id, '_product_attributes', maybe_serialize($meta_value));
            $meta_batch_count++;
         }

         if ($meta_batch_count >= $max_rows_per_query) {
            $sql = "INSERT INTO {$postmeta_table} (post_id, meta_key, meta_value) VALUES " . implode(',', $meta_inserts) .
               " ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)";
            $result = $wpdb->query($sql);
            if (
               $result === false
            ) {
               CIPFA_Logs::cipfa_Log("Failed to insert product meta for post_id {$post_id}. SQL Error: " . $wpdb->last_error);
            }
            $meta_inserts = [];
            $meta_batch_count = 0;
         }
      }

      // Insert remaining meta rows
      if (!empty($meta_inserts)) {
         $sql = "INSERT INTO {$postmeta_table} (post_id, meta_key, meta_value) VALUES " . implode(',', $meta_inserts) .
            " ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)";
         $result = $wpdb->query($sql);
         if ($result === false) {
            CIPFA_Logs::cipfa_Log("Failed to insert remaining product meta. SQL Error: " . $wpdb->last_error);
         }
      }

      // Batch insert term relationships
      if (!empty($term_relationship_inserts)) {
         $BATCH_TERMS_REL = 500;
         $chunks = array_chunk($term_relationship_inserts, $BATCH_TERMS_REL);
         foreach ($chunks as $chunk) {
            $sql = "INSERT IGNORE INTO {$term_rel_table} (object_id, term_taxonomy_id) VALUES " . implode(',', $chunk);
            $result = $wpdb->query($sql);
            if ($result === false) {
               CIPFA_Logs::cipfa_Log("Failed to insert product term relationships. SQL Error: " . $wpdb->last_error);
            }
         }
      }

      // Batch update term_taxonomy counts
      if (!empty($term_tax_counts)) {
         foreach ($term_tax_counts as $term_tax_id => $count) {
            $result = $wpdb->query($wpdb->prepare(
               "UPDATE {$term_tax_table} SET count = count + %d WHERE term_taxonomy_id = %d",
               $count,
               $term_tax_id
            ));
            if ($result === false) {
               CIPFA_Logs::cipfa_Log("Failed to update term_taxonomy count for term_taxonomy_id {$term_tax_id}. SQL Error: " . $wpdb->last_error);
            }
         }
      }
   }
   // phpcs:enable

   //Set Product Download file name and URL
   private static function cipfa_set_product_downloadable_files(&$product_fields, $registered_map = [])
   {
      $file_names = $product_fields['download_file_name'] ?? [];
      $file_urls  = $product_fields['download_file_url'];
      $product_downloadable_files = [];

      $register = new \Automattic\WooCommerce\Internal\ProductDownloads\ApprovedDirectories\Register();
      $site_host = wp_parse_url(home_url(), PHP_URL_HOST) ?: '';

      foreach ($file_urls as $index => $url) {
         if (empty($url)) continue;

         $url = trim($url);
         $is_approved = $register->is_valid_path($url);
         $url_host = wp_parse_url($url, PHP_URL_HOST) ?: '';

         // If URL is local AND not from a Woo "approved directory"
         if (!$is_approved && $url_host === $site_host) {

            $attachment_id = self::cipfa_find_attachment_id($url, $registered_map);

            if ($attachment_id) {
               $url = wp_get_attachment_url($attachment_id);
               $new_dir = trailingslashit(dirname($url));

               // Ensure directory is approved
               if (!$register->approved_directory_exists($new_dir)) {
                  $register->add_approved_directory($new_dir, true);
               }
            }
         }

         // File name
         $name = !empty($file_names[$index])
            ? $file_names[$index]
            : basename(wp_parse_url($url, PHP_URL_PATH) ?? '');

         $unique_key = 'download_' . ($index + 1);

         $product_downloadable_files[$unique_key] = [
            'name' => $name,
            'file' => $url,
         ];
      }

      if (!empty($product_downloadable_files)) {
         $product_fields['_downloadable_files'] = $product_downloadable_files;
      }
   }

   //Set Product Gallery images
   private static function cipfa_set_product_image_gallery($post_id, $gallery_urls, $registered_map = [])
   {
      if (empty($gallery_urls)) {
         return '';
      }

      // Convert CSV-style string to array if needed
      if (!is_array($gallery_urls)) {
         $gallery_urls = array_filter(array_map(
            'trim',
            explode(',', $gallery_urls)
         ));
      }

      if (empty($gallery_urls)) {
         return '';
      }

      $attachment_ids = [];

      foreach ($gallery_urls as $image_url) {
         if (empty($image_url)) {
            continue;
         }

         $image_url = trim($image_url);
         $attachment_id = self::cipfa_find_attachment_id($image_url, $registered_map);

         if ($attachment_id) {
            $attachment_ids[] = $attachment_id;
         }
      }

      // Return comma-separated list of IDs
      return implode(',', $attachment_ids);
   }

   // Determine active price
   private static function cipfa_update_product_price(&$product_fields)
   {
      if (isset($product_fields['_regular_price'])) {
         if (
            isset($product_fields['_sale_price']) && wc_format_decimal($product_fields['_sale_price']) <
            wc_format_decimal($product_fields['_regular_price'])
         ) {
            $product_fields['_price'] = $product_fields['_sale_price'];
         } else {
            $product_fields['_price'] = $product_fields['_regular_price'];
            unset($product_fields['_sale_price']);
            unset($product_fields['_sale_price_dates_from']);
            unset($product_fields['_sale_price_dates_to']);
         }
      }
   }

   // Mapping Fields
   public static function cipfa_mapping_acf_fields($fields, $parent_label = '', $is_inside_pro_parent = false, $parent_type = '')
   {
      $free_fields = [];
      $pro_fields  = [];

      foreach ($fields as $field) {
         // Skip UI-only fields and ACFE fields
         if (
            in_array($field['type'], [
               'message', 'tab', 'accordion', 'clone'
            ]) ||
            strpos($field['type'], 'acfe_') === 0
         ) {
            continue;
         }

         if (!empty($field['layouts'])) {
            foreach ($field['layouts'] as $layout) {
               if (!empty($layout['sub_fields'])) {
                  $nested = self::cipfa_mapping_acf_fields(
                     $layout['sub_fields'],
                     $parent_label ? $parent_label . ' - ' . $layout['label'] : $layout['label'],
                     true,
                     'flexible_content'
                  );
                  $free_fields = array_merge($free_fields, $nested['free']);
                  $pro_fields  = array_merge($pro_fields, $nested['pro']);
               }
            }
            continue;
         }

         $label = $parent_label ? $parent_label . ' - ' . $field['label'] : $field['label'];

         // Pro-only field types
         $is_pro_only = in_array($field['type'], [
            'file', 'link', 'post_object', 'relationship', 'flexible_content', 'gallery',
            'repeater', 'checkbox', 'radio', 'button_group', 'wysiwyg', 'oembed', 'page_link',
            'taxonomy', 'user', 'google_map', 'date_time_picker', 'time_picker', 'color_picker', 'icon_picker'
         ]);

         // Add suffix for subfields
         $suffix = '';
         if ($parent_type === 'repeater') {
            $suffix = ' (Repeater Sub Field)';
         } elseif ($parent_type === 'flexible_content') {
            $suffix = ' (Flexible Sub Field)';
         } elseif ($parent_type === 'repeater_in_flexible') {
            $suffix = ' (Repeater in Flexible)';
         }

         if ($suffix) {
            $label .= $suffix;
         }

         $field['label'] = $label;

         if (!empty($field['sub_fields'])) {
            $child_parent_type = $parent_type;
            if ($field['type'] === 'repeater' && $parent_type === 'flexible_content') {
               $child_parent_type = 'repeater_in_flexible';
            } elseif (in_array($field['type'], ['repeater', 'flexible_content'])) {
               $child_parent_type = $field['type'];
            }

            $nested = self::cipfa_mapping_acf_fields(
               $field['sub_fields'],
               $label,
               $is_inside_pro_parent || $is_pro_only,
               $child_parent_type
            );

            $free_fields = array_merge(
               $free_fields,
               $nested['free']
            );
            $pro_fields  = array_merge($pro_fields, $nested['pro']);
            continue;
         }

         // Normal fields
         if (
            $is_pro_only || $is_inside_pro_parent
         ) {
            $pro_fields[] = $field;
         } else {
            $free_fields[] = $field;
         }
      }

      return [
         'free' => $free_fields,
         'pro'  => $pro_fields
      ];
   }

   // Build Rank Math Schema fields
   private static function cipfa_build_rankmath_schema($rankmath_fields)
   {
      $allowed_schema = [
         'article'       => 'Article',
         'newsarticle'   => 'NewsArticle',
         'blogposting'   => 'BlogPosting',
         'product'       => 'Product',
         'course'        => 'Course',
         'event'         => 'Event',
         'recipe'        => 'Recipe',
         'jobposting'    => 'JobPosting',
         'book'          => 'Book',
         'music'         => 'MusicRecording',
         'video'         => 'VideoObject',
         'service'       => 'Service',
         'software'      => 'SoftwareApplication',
         'faq'           => 'FAQPage',
         'howto'         => 'HowTo',
         'person'        => 'Person',
         'organization'  => 'Organization',
         'localbusiness' => 'LocalBusiness',
         'webpage'       => 'WebPage',
      ];

      if (empty($rankmath_fields['tmp_schema_type']) && empty($rankmath_fields['tmp_schema_json'])) {
         return $rankmath_fields;
      }

      $clean = $rankmath_fields['tmp_schema_type'] ?? strtolower($rankmath_fields['tmp_schema_json']['@type'] ?? 'article');

      if (!isset($allowed_schema[$clean])) {
         $clean = 'article';
      }

      $schema_type = $allowed_schema[$clean];
      $json_data   = $rankmath_fields['tmp_schema_json'] ?? [];

      $rankmath_fields['_rank_math_schema_type'] = $clean;
      $rankmath_fields['_rank_math_schema'] = [
         $clean => [
            '@type'       => $schema_type,
            'schema-type' => $clean,
            'id'          => $clean,
            'metadata'    => [
               'title'       => ucfirst($clean) . ' Schema',
               'description' => '',
            ],
         ],
      ];

      $rankmath_fields['rank_math_schema_' . $clean] = array_merge(
         [
            'metadata' => [
               'title'       => ucfirst($clean),
               'type'        => 'template',
               'shortcode'   => uniqid('s-'),
               'isPrimary'   => '1',
               'name'        => '%seo_title%',
               'description' => '%seo_description%',
            ],
         ],
         $json_data
      );

      unset($rankmath_fields['tmp_schema_type'], $rankmath_fields['tmp_schema_json']);

      return $rankmath_fields;
   }

   // Generate thumnails
   public static function cipfa_generate_thumb_for_attachment($attachment_id)
   {
      $file = get_attached_file($attachment_id);
      if (!$file || !file_exists($file)) return;

      $meta = wp_generate_attachment_metadata($attachment_id, $file);
      if ($meta && !empty($meta['sizes'])) {
         wp_update_attachment_metadata($attachment_id, $meta);
      }
   }

   //Update meta Fields
   // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value
   private static function cipfa_update_meta_fields_bulk($bulk_meta_data, $replace_existing = false)
   {
      global $wpdb;

      $meta_inserts = [];
      $fallback_data = [];
      $max_rows = 500;

      if ($replace_existing) {

         $meta_to_delete = [];

         // Collect all post_id => meta_keys
         foreach ($bulk_meta_data as $meta_group) {

            $is_options = ($meta_group['post_id'] === 'option');

            // Skip options pages for delete logic
            if ($is_options) continue;

            $post_id = (int)$meta_group['post_id'];
            $keys = [];

            foreach (['yoast_fields', 'rankmath_fields', 'product_fields'] as $group) {
               if (!empty($meta_group[$group])) {
                  $keys = array_merge($keys, array_keys($meta_group[$group]));
               }
            }

            if (!empty($meta_group['featured_image'])) $keys[] = '_thumbnail_id';
            if (!empty($meta_group['page_template'])) $keys[] = '_wp_page_template';

            if (!empty($keys)) {
               $meta_to_delete[$post_id] = array_unique($keys);
            }
         }

         $batch_values = [];
         $current_rows = 0;
         $max_rows_per_query = 500;

         foreach ($meta_to_delete as $post_id => $keys) {
            foreach ($keys as $key) {
               $batch_values[] = [
                  'post_id' => $post_id,
                  'meta_key' => $key
               ];
               $current_rows++;

               if ($current_rows >= $max_rows_per_query) {
                  self::cipfa_execute_delete_batch($batch_values, $wpdb);
                  $batch_values = [];
                  $current_rows = 0;
               }
            }
         }

         if (!empty($batch_values)) {
            self::cipfa_execute_delete_batch($batch_values, $wpdb);
         }
      }

      foreach ($bulk_meta_data as $meta_group) {

         $is_options = ($meta_group['post_id'] === 'option');
         $post_id = $is_options ? 'option' : (int)$meta_group['post_id'];

         // ACF
         if (!empty($meta_group['acf_fields'])) {
            foreach ($meta_group['acf_fields'] as $field_key => $val) {
               if (
                  $val === '' && $val !== '0'
               ) continue;
               update_field($field_key, $val, $post_id);
            }
         }

         if (!$is_options) {

            // Yoast / RankMath / Product fields
            foreach (['yoast_fields', 'rankmath_fields', 'product_fields'] as $group) {
               if (empty($meta_group[$group])) continue;

               foreach ($meta_group[$group] as $key => $val) {
                  if (
                     $val === '' && $val !== '0'
                  ) continue;

                  $meta_inserts[] = $wpdb->prepare(
                     "(%d,%s,%s)",
                     $post_id,
                     $key,
                     maybe_serialize($val)
                  );

                  $fallback_data[] = [$post_id, $key, $val];
               }
            }

            // Featured image
            if (!empty($meta_group['featured_image'])) {
               $val = $meta_group['featured_image'];
               $meta_inserts[] = $wpdb->prepare(
                  "(%d,%s,%s)",
                  $post_id,
                  '_thumbnail_id',
                  $val
               );
               $fallback_data[] = [$post_id, '_thumbnail_id', $val];
            }

            // Page template
            if (!empty($meta_group['page_template'])) {
               $val = sanitize_text_field($meta_group['page_template']);
               $meta_inserts[] = $wpdb->prepare(
                  "(%d,%s,%s)",
                  $post_id,
                  '_wp_page_template',
                  $val
               );
               $fallback_data[] = [$post_id, '_wp_page_template', $val];
            }
         }

         // BATCH EXECUTE
         if (count($meta_inserts) >= $max_rows) {
            self::cipfa_execute_meta_batch($meta_inserts, $fallback_data);
            $meta_inserts = [];
            $fallback_data = [];
         }
      }

      if (!empty($meta_inserts)) {
         self::cipfa_execute_meta_batch(
            $meta_inserts,
            $fallback_data
         );
      }
   }
   // phpcs:enable

   // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
   private static function cipfa_execute_delete_batch($batch_values, $wpdb)
   {
      $conditions = [];

      foreach ($batch_values as $row) {
         $conditions[] = $wpdb->prepare(
            '(post_id = %d AND meta_key = %s)',
            $row['post_id'],
            $row['meta_key']
         );
      }

      if (!empty($conditions)) {
         $query = 'DELETE FROM ' . $wpdb->postmeta . ' WHERE ' . implode(' OR ', $conditions);
         $wpdb->query($query);
      }
   }
   // phpcs:enable

   // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
   private static function cipfa_execute_meta_batch($meta_inserts, $fallback_data)
   {
      global $wpdb;

      $query = 'INSERT INTO ' . $wpdb->postmeta .
         ' (post_id, meta_key, meta_value) VALUES ' .
         implode(',', $meta_inserts) .
         ' ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)';

      $result = $wpdb->query($query);

      // Fallback
      if ($result === false || $result === 0) {

         if ($result === false) {
            CIPFA_Logs::cipfa_Log('Bulk meta insert FAILED. SQL Error: ' . $wpdb->last_error . ' — fallback insert triggered.');
         } else {
            CIPFA_Logs::cipfa_Log('Bulk meta insert affected 0 rows — fallback insert triggered.');
         }

         foreach ($fallback_data as $row) {
            list($post_id, $meta_key, $meta_value) = $row;
            update_post_meta($post_id, $meta_key, $meta_value);
         }
      }
   }
   // phpcs:enable

}
add_action('init', ['CIPFA\\base\\CIPFA_PostProcessor', 'cipfa_register'], 5);
