<?php

namespace AC_SYNC\Includes\Classes\Product {


  if (!defined('ABSPATH'))
    exit; // Exit if accessed directly
  use AC_SYNC\Action_Wear_Core;
  use AC_SYNC\Includes\Classes\Api\Action_Wear_Api as Api;
  use AC_SYNC\Includes\Classes\Async\Action_Wear_Job_Queue as JobQueue;
  use AC_SYNC\Includes\Classes\Utils\Action_Wear_Utils;
  use AC_SYNC\Includes\Classes\Logger\Action_Wear_Log as Log;
  use AC_SYNC\Includes\Classes\Product\Action_Wear_Product_Image as ProductImage;
  use AC_SYNC\Includes\Classes\Price\Action_Wear_Price_Recharger as PriceRecharger;
  use AC_SYNC\Includes\Classes\Category\Action_Wear_Category_Association as CategoryAssociation;
  use AC_SYNC\Includes\Classes\Attribute\Action_Wear_Attribute as Attribute;
  use AC_SYNC\Includes\Classes\Setting\Action_Wear_Setting_Debug_Mode as DebugMode;
  use AC_SYNC\Includes\Classes\Setting\Action_Wear_Setting_Sync_Fields as SyncFields;
  use AC_SYNC\Includes\Classes\Brand\Action_Wear_Brand as Brand;
  use AC_SYNC\Includes\Classes\Api\Action_Wear_Api_Exception as ApiException;
  use ArrayIterator;

  // HOTFIX for woocommerce functions
  //TODO: [AD-25] Remove this when woocommerce fix it
  require_once dirname(__FILE__) . '/../../../../woocommerce/includes/admin/wc-admin-functions.php';

  class Action_Wear_Product
  {
    const SKIP_IMAGES = false;
    const SKIP_PRICE_RECHARGE = false;

    public static function getPrice($simple, $conf)
    {
      $base_price = $simple->prices->{1}->price;
      return self::getRechargedPrice($conf, $base_price);
    }

    public static function getRechargedPrice($conf, $price)
    {
      $base_price = $price;
      if (self::SKIP_PRICE_RECHARGE)
        return $base_price;
      $price_recharger = new PriceRecharger($base_price);
      $categories = (array) wc_get_product_category_list($conf->get_id());
      $cat_ids = array_keys($categories);
      return $price_recharger->getPrice((int) $conf->brand_id, $cat_ids);
    }

    /**
     * Static term cache for get_term_by lookups.
     * Avoids repeated DB queries for the same color/size terms across variants.
     * @var array
     */
    private static $termCache = [];

    /**
     * Reset the static term cache (call between different products if needed).
     */
    public static function resetTermCache()
    {
      self::$termCache = [];
    }

    /**
     * Get term slug by name and taxonomy, with static caching.
     *
     * @param string $name Term name
     * @param string $taxonomy Taxonomy slug (e.g. 'pa_colore')
     * @return string|false Term slug or false if not found
     */
    public static function getCachedTermSlug($name, $taxonomy)
    {
      $cacheKey = $taxonomy . ':' . $name;
      if (!isset(self::$termCache[$cacheKey])) {
        $term = get_term_by("name", $name, $taxonomy);
        self::$termCache[$cacheKey] = $term ? $term->slug : false;
      }
      return self::$termCache[$cacheKey];
    }

    public static function createVariantBySimple($product, $simple, $conf, $dimensions, $cover_id, $existingVariationId = 0)
    {
      $conf_id = $conf->get_id();
      $variation = new \WC_Product_Variation($existingVariationId);
      $variation->set_parent_id($conf_id);

      // Use cached term lookups to avoid repeated DB queries
      $color = self::getCachedTermSlug($simple->nome_colore, "pa_colore");
      $size = self::getCachedTermSlug($simple->camac_size, "pa_taglia");

      $variation->set_attributes([
        "pa_colore" => $color,
        "pa_taglia" => $size
      ]);

      $variation->set_sku($simple->sku);

      // Price and quantity will be set by separate bulk sync operations
      $variation->set_regular_price(0);
      $variation->set_manage_stock(true);
      $variation->set_stock_quantity(0);

      // Set weight, dimensions, and cover BEFORE save to avoid double save
      $variation->set_weight($simple->product_weight);
      if (isset($dimensions)) {
        if (isset($dimensions->l)) {
          $variation->set_length("");
          $variation->set_width($dimensions->l);
        }
        if (isset($dimensions->h))
          $variation->set_height($dimensions->h);
      }
      if ($cover_id !== false)
        $variation->set_image_id($cover_id);

      // Single save instead of two — saves ~10 queries per variant
      $variation->save();

      $vid = $variation->get_id();
      $categories = (array) $product->categories;
      $cat_ids = array_keys($categories);
      $brand_id = $product->brand_id;

      // Save category and brand IDs for price recharge calculation
      update_post_meta($vid, "cat_ids", json_encode($cat_ids));
      update_post_meta($vid, "brand_id", $brand_id);

      // Save original base price for future recalculations
      update_post_meta($vid, "original_base_price", $simple->prices->{1}->price);

      // Flag variation as needing price and quantity sync
      update_post_meta($vid, "_needs_price_sync", 1);
      update_post_meta($vid, "_needs_quantity_sync", 1);

      return $vid;
    }

    public static function getRemoteProductsListino(array $skus)
    {
      $skus = implode(",", $skus);
      $products = Api::external("GET", "/listino?sku=$skus&output=json&compress=0", ["timeout" => 60]);
      $code = $products["code"];
      if ($code !== 200) {
        throw new ApiException($code, "API error (code $code) for SKUs: " . $skus);
      }
      $products = (array) json_decode($products["raw"]["body"]);
      $products = $products["hierarchical"];
      return $products;
    }

    /**
     * Acquire a MySQL named lock for a SKU to prevent race conditions.
     * Non-blocking (timeout=0): returns immediately if lock unavailable.
     *
     * @param string $lockName Lock identifier (max 64 chars)
     * @param int    $timeout  Seconds to wait (0 = non-blocking)
     * @return bool True if lock acquired, false otherwise (graceful degradation)
     */
    private static function acquireSkuLock(string $lockName, int $timeout): bool
    {
      global $wpdb;
      try {
        $result = $wpdb->get_var(
          $wpdb->prepare("SELECT GET_LOCK(%s, %d)", $lockName, $timeout)
        );
        return $result === '1';
      } catch (\Throwable $e) {
        return false; // Graceful degradation
      }
    }

    /**
     * Release a MySQL named lock.
     *
     * @param string $lockName Lock identifier
     */
    private static function releaseSkuLock(string $lockName): void
    {
      global $wpdb;
      try {
        $wpdb->query($wpdb->prepare("SELECT RELEASE_LOCK(%s)", $lockName));
      } catch (\Throwable $e) {
        // Silent — lock auto-releases on connection close
      }
    }

    public static function createOrUpdateWcProduct($product)
    {
      $debug = DebugMode::getDebugMode();

      // L1: Acquire per-SKU mutex (non-blocking, graceful degradation)
      $lockName = 'aw_sku_' . md5($product->sku);
      $hasLock = self::acquireSkuLock($lockName, 0);

      try {
        // 1. Load or create parent product (double-check after lock)
        $parentId = wc_get_product_id_by_sku($product->sku);
        $_product = new \WC_Product_Variable($parentId);

        // 2. Set parent properties (no save)
        self::setParentProperties($_product, $product);

        // 3. First save — needed to get ID for variants, images, and brand
        $_product->save();
        $parentId = $_product->get_id();

        if ($debug) {
          Log::write("Parent product saved: SKU {$product->sku}, ID {$parentId}", Log::INFO, Log::CONTEXT_PRODUCT_CREATION);
        }

        // 4. Brand assignment (needs valid ID, must be after save)
        if (!empty($product->brand) && isset($product->brand_id)) {
          Brand::assignBrandToProduct($parentId, $product->brand, $product->brand_id);
        }

        // 4b. Cleanup old placeholder attachments from previous runs (crash recovery)
        if ($parentId) {
          self::cleanupOldPlaceholders($parentId);
        }

        // 5. Prepare images (needs valid ID for ProductImage constructor)
        $productImage = null;
        $imageMap = [];
        if (!self::SKIP_IMAGES && !empty($product->sku)) {
          [$productImage, $imageMap] = self::prepareImages($product, $_product);
        }

        // 6. Create/update variants
        self::syncVariants($_product, $product, $productImage, $imageMap);

        if ($debug) {
          Log::write("Synced " . count((array) $product->simple_products) . " variants for SKU {$product->sku}", Log::INFO, Log::CONTEXT_PRODUCT_CREATION);
        }

        // 7. Flush image meta in batch
        if ($productImage) {
          $productImage->flushPendingMeta();
        }

        // 8. Cleanup obsolete variants
        self::cleanupObsoleteVariants($_product, $product->simple_products);

        // 9. Save final parent state (gallery images)
        if (!self::SKIP_IMAGES && $productImage) {
          $productImage->saveToMeta();
        }
        $_product->save();

        // 10. Mark as created in actionwear_products table
        self::setProductAsCreated($product->sku);

        if ($debug) {
          Log::write("Product complete: SKU {$product->sku}, variants: " . count((array) $product->simple_products), Log::INFO, Log::CONTEXT_PRODUCT_CREATION);
        }
      } finally {
        // L1: Always release lock
        if ($hasLock) {
          self::releaseSkuLock($lockName);
        }
      }
    }

    /**
     * Set all parent product properties without saving.
     */
    private static function setParentProperties(\WC_Product_Variable $_product, $product)
    {
      if (SyncFields::shouldSyncField('name')) {
        $_product->set_name($product->name);
      }
      $_product->set_sku($product->sku);

      if (SyncFields::shouldSyncField('short_description')) {
        $_product->set_short_description($product->short_description);
      }
      if (SyncFields::shouldSyncField('description')) {
        $_product->set_description($product->description ?? "");
      }

      $cat_ids = CategoryAssociation::getAssociationByProduct($product->sku);
      $_product->set_category_ids($cat_ids);

      $_product->set_attributes(Attribute::setAttributes($product));
    }

    /**
     * Fetch and prepare product images. Sets cover and gallery on the parent product object.
     *
     * @return array [$productImage, $imageMap]
     */
    private static function prepareImages($product, \WC_Product_Variable $_product)
    {
      $productImage = new ProductImage($product->sku, $_product->get_id());
      $imageMap = [];

      if (get_option("_ACTIONWEAR_USE_CONFIGURABLE", "0") == "0") {
        $simpleIter = new ArrayIterator($product->simple_products);
        $configurable_images = $simpleIter->current()->immagini_colore;
      } else {
        $allImageSkus = [$product->sku];
        foreach ($product->simple_products as $s) {
          $allImageSkus[] = $s->sku;
        }
        $imageMap = ProductImage::getImagesBySkuBatch($allImageSkus);
        $configurable_images = $imageMap[$product->sku] ?? [];
      }

      $image_ids = $productImage->setConfigurableImages($configurable_images);
      $cover_id = false;
      if (count($image_ids) > 0) {
        $cover_id = $image_ids[0];
      }
      if ($cover_id !== false) {
        $_product->set_image_id($cover_id);
      }
      $_product->set_gallery_image_ids($image_ids);

      return [$productImage, $imageMap];
    }

    /**
     * Create or update all variant products.
     * Uses direct SQL query for robust SKU→ID lookup (includes all post_status).
     */
    private static function syncVariants(\WC_Product_Variable $_product, $product, $productImage, array $imageMap)
    {
      $parentId = $_product->get_id();
      $simple_products = $product->simple_products;

      // Direct SQL query for existing children — more robust than get_children()
      // which depends on WC internal cache and may miss trashed/draft variations
      global $wpdb;
      $existingChildSkus = [];
      $results = $wpdb->get_results($wpdb->prepare(
        "SELECT p.ID, pm.meta_value as sku
         FROM {$wpdb->posts} p
         JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_sku'
         WHERE p.post_parent = %d AND p.post_type = 'product_variation' AND pm.meta_value != ''",
        $parentId
      ));
      foreach ($results as $row) {
        $existingChildSkus[$row->sku] = (int) $row->ID;
      }

      self::resetTermCache();
      $seenSkus = [];

      foreach ($simple_products as $simple) {
        if (isset($seenSkus[$simple->sku])) continue;
        $seenSkus[$simple->sku] = true;

        $existingId = $existingChildSkus[$simple->sku] ?? 0;
        $cover = false;

        if (!self::SKIP_IMAGES && $productImage) {
          $simple_images = $imageMap[$simple->sku] ?? ProductImage::getImagesBySku($simple->sku);
          if (count($simple_images) > 0) {
            $image_ids = $productImage->setSimpleImagesBySku($simple->sku, $simple_images);
            if (count($image_ids) > 0) {
              $cover = $image_ids[0];
            }
          }
        }

        $dimensions = (isset($product->camac_misure) && !empty($product->camac_misure) && isset($product->camac_misure->{$simple->camac_size}))
          ? $product->camac_misure->{$simple->camac_size} : null;
        self::createVariantBySimple($product, $simple, $_product, $dimensions, $cover, $existingId);
      }
    }

    /**
     * Delete WC variations that no longer exist in the API data.
     */
    private static function cleanupObsoleteVariants(\WC_Product_Variable $_product, $simpleProducts)
    {
      $actionwear_skus = array_map(function ($item) {
        return $item->sku;
      }, (array) $simpleProducts);
      $actionwear_skus = array_values($actionwear_skus);

      $children = $_product->get_children();
      if (empty($children)) return;

      global $wpdb;
      $idPlaceholders = implode(',', array_fill(0, count($children), '%d'));
      $childSkuResults = $wpdb->get_results($wpdb->prepare(
        "SELECT post_id, meta_value FROM {$wpdb->postmeta} WHERE post_id IN ($idPlaceholders) AND meta_key = '_sku'",
        ...$children
      ));

      $children_skus = [];
      $childSkuToId = [];
      foreach ($childSkuResults as $row) {
        $children_skus[] = $row->meta_value;
        $childSkuToId[$row->meta_value] = (int) $row->post_id;
      }

      $to_delete = array_diff($children_skus, $actionwear_skus);
      foreach ($to_delete as $delSku) {
        $id_simple = $childSkuToId[$delSku] ?? 0;
        if ($id_simple) {
          $variation = new \WC_Product_Variation($id_simple);
          if ($variation) {
            $variation->delete(true);
          }
        }
      }
    }

    public static function setProductAsCreated($sku)
    {
      global $wpdb;
      return $wpdb->update(
        "{$wpdb->prefix}actionwear_products",
        [
          "wc_created" => 1
        ],
        [
          "sku" => $sku
        ]
      );
    }

    public static function deleteAll($only_created = true)
    {
      global $wpdb;
      $wc_created = $only_created === true ? 1 : 0;
      $products = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$wpdb->prefix}actionwear_products WHERE wc_to_create = 1 AND wc_created = %d", $wc_created));
      foreach ($products as $product) {
        $sku = $product->sku;
        $id = $wpdb->get_var($wpdb->prepare("SELECT post_id FROM {$wpdb->prefix}postmeta WHERE meta_key = '_sku' AND meta_value = %s", $sku));
        if ($id !== false) {
          $wpdb->query($wpdb->prepare("DELETE FROM {$wpdb->prefix}posts WHERE ID = %d", $id));
          $wpdb->query($wpdb->prepare("DELETE FROM {$wpdb->prefix}posts WHERE post_parent = %d", $id));
        }
      }
    }

    // $selection can accept one of ALL | PRODUCTS | CATEGORIES
    public static function setProductsToImportBySelection($selection, $productsData)
    {
      global $wpdb;
      $update = false;

      if ($selection === "ALL") {
        $update = $wpdb->query("UPDATE " . $wpdb->prefix . "actionwear_products SET wc_to_create = 1;") !== FALSE;
        $skus = $wpdb->get_col("SELECT sku FROM " . $wpdb->prefix . "actionwear_products;");
        JobQueue::batchPushProductImport($skus);
      }

      if ($selection === "PRODUCTS") {
        $ids = array_map('intval', $productsData->ids);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $wpdb->query("UPDATE " . $wpdb->prefix . "actionwear_products SET wc_to_create = 0;");
        $update = $wpdb->query($wpdb->prepare("UPDATE " . $wpdb->prefix . "actionwear_products SET wc_to_create = 1 WHERE id IN ($placeholders);", ...$ids)) !== FALSE;
        $skus = $wpdb->get_col($wpdb->prepare("SELECT sku FROM " . $wpdb->prefix . "actionwear_products WHERE id IN ($placeholders);", ...$ids));
        JobQueue::batchPushProductImport($skus);
      }

      if ($selection === "CATEGORIES") {
        $category_ids = array_map('intval', $productsData->category_ids);
        $placeholders = implode(',', array_fill(0, count($category_ids), '%d'));
        $update = $wpdb->query($wpdb->prepare("UPDATE " . $wpdb->prefix . "actionwear_products SET wc_to_create = 1 WHERE id IN (SELECT DISTINCT(id_product) FROM " . $wpdb->prefix . "actionwear_categories_product WHERE id_category IN ($placeholders));", ...$category_ids)) !== FALSE;
        $skus = $wpdb->get_col($wpdb->prepare("SELECT sku FROM " . $wpdb->prefix . "actionwear_products WHERE id IN (SELECT DISTINCT(id_product) FROM " . $wpdb->prefix . "actionwear_categories_product WHERE id_category IN ($placeholders));", ...$category_ids));
        JobQueue::batchPushProductImport($skus);
      }

      if ($selection === "BRANDS") {
        $brand_ids = array_map('intval', $productsData->brand_ids);
        $placeholders_ids = implode(',', array_fill(0, count($brand_ids), '%d'));

        $brand_names = $wpdb->get_col($wpdb->prepare("SELECT name FROM " . $wpdb->prefix . "actionwear_brands WHERE id IN ($placeholders_ids);", ...$brand_ids));
        $placeholders_names = implode(',', array_fill(0, count($brand_names), '%s'));
        $update = $wpdb->query($wpdb->prepare("UPDATE " . $wpdb->prefix . "actionwear_products SET wc_to_create = 1 WHERE brand_name IN ($placeholders_names);", ...$brand_names)) !== FALSE;
        $skus = $wpdb->get_col($wpdb->prepare("SELECT sku FROM " . $wpdb->prefix . "actionwear_products WHERE brand_name IN ($placeholders_names);", ...$brand_names));
        JobQueue::batchPushProductImport($skus);
      }

      return $update;
    }

    public static function tableIsEmpty()
    {
      global $wpdb;
      return (int) $wpdb->get_var("SELECT COUNT(*) FROM " . $wpdb->prefix . "actionwear_products") === 0;
    }

    public static function addCategoriesQueries($cats_tree, &$categories_queries)
    {
      global $wpdb;
      foreach ($cats_tree as $category) {
        $categories_queries[] = $wpdb->prepare(
          "(%s, %s, %s, %d, %d, %d, %d)",
          $category->id,
          $category->parent_id,
          $category->name,
          $category->is_active ?? 1,
          $category->product_count,
          $category->position ?? 1,
          $category->level
        );
        if (isset($category->children_data) && count($category->children_data) > 0)
          self::addCategoriesQueries($category->children_data, $categories_queries);
      }
    }

    public static function generateCategories()
    {

      global $wpdb;
      $categories = Api::externalMagento("/categories", [], "?a=1");
      $code = $categories["code"];
      if ($code !== 200) {
        $error = "Si è verificato un errore nell'acquisizione delle categories da /categories, codice di stato: $code";
        Log::write($error, Log::ERROR, Log::CONTEXT_CAMAC_API, __LINE__, __FILE__);
        throw new \Exception($error);
      }
      $categories = json_decode($categories["raw"]["body"]);

      $categories_queries = [];

      $categories_queries[] = $wpdb->prepare(
        "(%s, %s, %s, %d, %d, %d, %d)",
        $categories->id,
        $categories->parent_id,
        $categories->name,
        $categories->is_active ?? 1,
        $categories->product_count,
        $categories->position ?? 1,
        $categories->level
      );

      self::addCategoriesQueries($categories->children_data, $categories_queries);

      $query = "INSERT IGNORE INTO " . $wpdb->prefix . "actionwear_categories (id_actionwear, parent_id_actionwear, name, is_active, product_count, position, level) VALUES " . implode(",", $categories_queries);
      $wpdb->query($query);

      $generated_ids = $wpdb->get_col("SELECT id_actionwear FROM {$wpdb->prefix}actionwear_categories");
      $query = "INSERT IGNORE INTO " . $wpdb->prefix . "actionwear_categories_associations (camac_category_id) VALUES (\"" . implode('"),("', $generated_ids) . "\")";

      $wpdb->query($query);
    }

    public static function generateBrands($brands)
    {
      global $wpdb;

      $values = [];

      foreach ($brands as $brand_id => $brand) {
        $values[] = $wpdb->prepare(
          "(%d, %s)",
          $brand_id,
          $brand
        );
      }

      $query = "INSERT IGNORE INTO " . $wpdb->prefix . "actionwear_brands (id_actionwear, name) VALUES " . implode(",", $values);
      $wpdb->query($query);

      return true;
    }

    public static function isCamacThumbnail($thumbnail_id)
    {
      return (int) get_post_meta($thumbnail_id, "camac_product_id", true) > 0;
    }

    public static function isCamacProduct($product_id)
    {
      return get_post_meta($product_id, "camac_cdn_urls", true) !== "";
    }

    /**
     * Cleanup old placeholder attachments from previous (possibly interrupted) runs.
     * Removes tracked placeholders via camac_attachment_ids meta, plus any
     * untracked "ghost" placeholders identified by guid='/'.
     *
     * @param int $parentId WC product ID
     */
    public static function cleanupOldPlaceholders($parentId)
    {
      global $wpdb;

      // 1. Delete tracked placeholders from previous run
      $oldIds = get_post_meta($parentId, 'camac_attachment_ids', true);
      if (!empty($oldIds)) {
        $ids = array_filter(array_map('intval', explode(',', $oldIds)));
        foreach ($ids as $id) {
          wp_delete_attachment($id, true);
        }
      }

      // 2. Delete ghost placeholders (created but meta never flushed)
      // guid='/' is a unique marker set by createPlaceHolder() — no legit attachment has this
      $ghosts = $wpdb->get_col($wpdb->prepare(
        "SELECT ID FROM {$wpdb->posts}
         WHERE post_parent = %d
           AND post_type = 'attachment'
           AND post_mime_type = 'image/png'
           AND guid = '/'",
        $parentId
      ));
      foreach ($ghosts as $ghostId) {
        wp_delete_attachment((int) $ghostId, true);
      }

      // 3. Clean image meta on parent
      delete_post_meta($parentId, 'camac_cdn_urls');
      delete_post_meta($parentId, 'camac_attachment_ids');

      // 4. Reset WC image references on parent
      $parent = wc_get_product($parentId);
      if ($parent) {
        $parent->set_image_id('');
        $parent->set_gallery_image_ids([]);
        $parent->save();
      }
    }

    public static function getQuantitiesDetail($id)
    {
      $keys = ["quantity_supplier", "total_arrivals", "arrivals_detail", "supplier_detail", "supplier_days"];
      $to_decode = [$keys[2], $keys[3]];
      $result = [];
      foreach ($keys as $key) {
        $result[$key] = in_array($key, $to_decode) ? json_decode(get_post_meta($id, $key, true)) : get_post_meta($id, $key, true);
      }
      return $result;
    }

    public static function getQuantitiesDetails(array $ids)
    {
      if (empty($ids)) return [];

      // Pre-fetch all meta for the given IDs in a single query
      global $wpdb;
      $keys = ["quantity_supplier", "total_arrivals", "arrivals_detail", "supplier_detail", "supplier_days"];
      $to_decode = ["arrivals_detail", "supplier_detail"];

      $id_placeholders = implode(',', array_fill(0, count($ids), '%d'));
      $key_placeholders = implode(',', array_fill(0, count($keys), '%s'));
      $params = array_merge($ids, $keys);
      $metaRows = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT post_id, meta_key, meta_value FROM {$wpdb->postmeta} WHERE post_id IN ($id_placeholders) AND meta_key IN ($key_placeholders)",
          ...$params
        )
      );

      // Build lookup map
      $metaMap = [];
      foreach ($metaRows as $row) {
        $metaMap[$row->post_id][$row->meta_key] = $row->meta_value;
      }

      $result = [];
      foreach ($ids as $id) {
        $entry = [];
        foreach ($keys as $key) {
          $value = $metaMap[$id][$key] ?? '';
          $entry[$key] = in_array($key, $to_decode) ? json_decode($value) : $value;
        }
        $result[$id] = $entry;
      }
      return $result;
    }

    public static function checkDefaultAttributesCreation()
    {
      if (!class_exists("woocommerce")) {
        $error = new \WP_Error('500', "Per poter attivare questo plugin devi avere installato ed attivato il plugin di WooCommerce");
        if (is_wp_error($error)) {
          wp_die($error);
        }
      }
      $attributes = wc_get_attribute_taxonomies();
      $names = [];
      foreach ($attributes as $attribute) {
        $names[] = $attribute->attribute_label;
      }
      if (!in_array("Colore", $names)) {
        wc_create_attribute([
          "name" => "Colore",
          "has_archives" => true
        ]);
      }
      if (!in_array("Taglia", $names)) {
        wc_create_attribute([
          "name" => "Taglia",
          "has_archives" => true
        ]);
      }
      if (!in_array("Color Group", $names)) {
        wc_create_attribute([
          "name" => "Color Group",
          "slug" => "camac_color_group",
          "has_archives" => true
        ]);
      }
    }

    public static function generateImages()
    {
      global $wpdb;
      $images = Api::external("GET", "/productsimage", ["timeout" => 30000]);
      $code = $images["code"];
      if ($code !== 200) {
        $error = "Si è verificato un errore nell'acquisizione delle immagini da /productsimages con codice di stato: $code";
        Log::write($error, Log::ERROR, Log::CONTEXT_CAMAC_API, __LINE__, __FILE__);
        throw new \Exception($error);
      }
      $images = json_decode($images["raw"]["body"]);

      // Data fetched successfully — now safe to truncate and re-insert
      $wpdb->query("TRUNCATE " . $wpdb->prefix . "actionwear_images");

      $values = [];
      $i = 0;
      $portion = 0;
      $values[] = [];
      foreach ($images as $image) {
        if ($i % 3000 === 0) {
          $values[] = [];
          $portion++;
        }
        $sku = trim($image->sku);
        $values[$portion][] = $wpdb->prepare("(%s, %s)", $sku, json_encode($image->media_gallery));
        $i++;
      }
      foreach ($values as $values_portion) {
        $raw = trim(implode(",", $values_portion));
        if (empty($raw))
          continue;
        $wpdb->query("INSERT IGNORE INTO {$wpdb->prefix}actionwear_images (sku, images) VALUES $raw");
      }
      return true;
    }

    public static function filterDeletedProducts()
    {
      global $wpdb;

      try {
        $pricesAll = Api::external(
          "GET",
          "/pricesall?compress=0&output=json"
        );
      } catch (\Exception $e) {
        Log::write($e->getMessage(), Log::ERROR, Log::CONTEXT_CAMAC_API);
        throw new \Exception($e->getMessage());
      }

      $pricesAll = json_decode($pricesAll['raw']['body'], true);
      $currentProducts = $wpdb->get_col("SELECT sku FROM `{$wpdb->prefix}actionwear_products`");

      // Use hash map for O(1) dedup instead of in_array O(n)
      $output = [];
      foreach ($pricesAll[0] as $price) {
        $output[$price['sku_camac']] = true;
      }

      $deletedProducts = array_diff($currentProducts, array_keys($output));

      // Batch DELETE instead of one-by-one
      if (!empty($deletedProducts)) {
        $deletedProducts = array_values($deletedProducts);
        $placeholders = implode(',', array_fill(0, count($deletedProducts), '%s'));
        $wpdb->query($wpdb->prepare("DELETE FROM `{$wpdb->prefix}actionwear_products` WHERE sku IN ($placeholders)", ...$deletedProducts));
      }
    }

    public static function generateProducts()
    {

      global $wpdb;

      try {
        $products = self::getListinoLite();
        $brands = [];
        $query_values = [];
        foreach ($products as $sku => $product) {
          $cover = $product["cover"] ?? "";
          if (!empty($cover)) {
            $cover = str_replace("://pim.action-wear.com", "://media.action-wear.com", $cover);
          }
          $brand_id = $product["brand_id"] ?? 0;
          $has_new = !empty($product["new"]) ? 1 : 0;
          $has_new_color = !empty($product["new_color"]) ? 1 : 0;
          $query_values[] = $wpdb->prepare("(%s, %s, %s, %s, %d, %d, %d)", $sku, $cover, $product["name"], $product["brand"], 1, $has_new, $has_new_color);
          if ($brand_id !== 0 && !array_key_exists($brand_id, $brands)) {
            $brands[$brand_id] = $product["brand"];
          }
        }
        self::generateBrands($brands);

        // Data fetched successfully — now safe to truncate and re-insert
        $wpdb->query("TRUNCATE " . $wpdb->prefix . "actionwear_categories_product");

        $query = "INSERT IGNORE INTO " . $wpdb->prefix . "actionwear_products (sku, cover, name, brand_name, is_configurable, has_new, has_new_color) VALUES " . implode(',', $query_values) . ";";
        $wpdb->query($query);
        self::generateProductCategoriesAssociations($products);
      } catch (\Exception $e) {
        Log::write($e->getMessage(), Log::ERROR, Log::CONTEXT_CAMAC_API);
        throw new \Exception($e->getMessage());
      }

    }

    public static function getIdProductSkuMap()
    {
      global $wpdb;
      $results = $wpdb->get_results("SELECT id, sku FROM " . $wpdb->prefix . "actionwear_products", ARRAY_A);
      $map = [];
      foreach ($results as $row) {
        $map[$row['sku']] = (int) $row['id'];
      }
      return $map;
    }

    public static function generateProductCategoriesAssociations($products)
    {
      global $wpdb;
      $categories_query = [];

      $idProductSkuMap = self::getIdProductSkuMap();

      foreach ($products as $sku => $product) {
        if (isset($product["categories"]) && count((array) $product["categories"]) > 0) {
          $categories = array_keys((array) $product["categories"]);
          $id_product = $idProductSkuMap[$sku] ?? null;
          if ($id_product) {
            foreach ($categories as $cat) {
              $categories_query[] = $wpdb->prepare("(%d, %s)", $id_product, $cat);
            }
          }
        }
      }

      if (count($categories_query) > 0) {
        $i = 0;
        $portion = 0;
        $partial = [];
        $partial[] = [];
        foreach ($categories_query as $cat_query) {
          if ($i % 3000 === 0) {
            $portion++;
            $partial[] = [];
          }
          $partial[$portion][] = $cat_query;
          $i++;
        }
        foreach ($partial as $partial_query) {
          $query = "INSERT IGNORE INTO " . $wpdb->prefix . "actionwear_categories_product (id_product, id_category) VALUES " . implode(',', $partial_query) . ";";
          $wpdb->query($query);
        }
      }

    }

    public static function getListinoLite()
    {
      $all_products = Api::external("GET", "/listino-lite");
      $code = $all_products["code"];
      if ($code !== 200) {
        $error = "Si è verificato un errore nell'acquisizione del catalogo listino-lite con codice di stato: $code";
        Log::write($error, Log::ERROR, Log::CONTEXT_CAMAC_API, __LINE__, __FILE__);
        throw new \Exception($error);
      }
      $body = json_decode($all_products["raw"]["body"]);

      // if body is Object converto to array
      if (is_object($body))
        $body = json_decode(json_encode($body), true);

      if (isset($body["error"])) {
        $error = "Il listino lite è in elaborazione...";
        Log::write($error, Log::ERROR, Log::CONTEXT_CAMAC_API, __LINE__, __FILE__);
        throw new \Exception($error);
      }
      return $body;
    }

    public static function getUrlPricesAll()
    {
      $all_products = Api::external("GET", "/pricesall?output=csv");
      $code = $all_products["code"];
      if ($code !== 200) {
        $error = "Si è verificato un errore nell'acquisizione del catalogo pricesall con codice di stato: $code";
        Log::write($error, Log::ERROR, Log::CONTEXT_CAMAC_API, __LINE__, __FILE__);
        throw new \Exception($error);
      }
      $body = json_decode($all_products["raw"]["body"]);
      if (Action_Wear_Utils::isJson($body))
        $body = json_decode($body);
      if (isset($body->error)) {
        $error = "Il listino è in elaborazione...";
        Log::write($error, Log::ERROR, Log::CONTEXT_CAMAC_API, __LINE__, __FILE__);
        throw new \Exception($error);
      }
      return $body;
    }

    public static function getUrlListino()
    {
      $all_products = Api::external("GET", "/listino?output=json&noprice=1", ["timeout" => 90]);
      $code = $all_products["code"];
      if ($code !== 200) {
        $error = "Si è verificato un errore nell'acquisizione del catalogo listino con codice di stato: $code";
        Log::write($error, Log::ERROR, Log::CONTEXT_CAMAC_API, __LINE__, __FILE__);
        throw new \Exception($error);
      }
      $body = $all_products["raw"]["body"];
      $body = stripslashes(trim($body, '"'));

      // check if $body is valid url
      if (!filter_var($body, FILTER_VALIDATE_URL)) {
        $error = "Il listino è in elaborazione...";
        Log::write($error, Log::ERROR, Log::CONTEXT_CAMAC_API, __LINE__, __FILE__);
        throw new \Exception($error);
      }

      return $body;
    }

    public function testBoolGetRemoteListino($sku)
    {
      $result = $this->getRemoteProductsListino([$sku]);
      return (bool) $result;
    }

    /**
     * Detect and remove duplicate WooCommerce products sharing the same SKU.
     * Priority: keep the product with an image; fallback to the most recent (highest ID).
     * Safety: never deletes products referenced in WooCommerce orders.
     *
     * @return int Number of duplicate products removed
     */
    public static function cleanupDuplicateSkus(): int
    {
        global $wpdb;

        $duplicates = $wpdb->get_results(
            "SELECT pm.meta_value as sku, GROUP_CONCAT(p.ID ORDER BY p.ID ASC) as ids, COUNT(*) as cnt
             FROM {$wpdb->postmeta} pm
             JOIN {$wpdb->posts} p ON p.ID = pm.post_id AND p.post_type = 'product'
             JOIN {$wpdb->prefix}actionwear_products ap ON ap.sku = pm.meta_value
             WHERE pm.meta_key = '_sku' AND pm.meta_value != ''
             GROUP BY pm.meta_value
             HAVING cnt > 1"
        );

        $cleaned = 0;
        foreach ($duplicates as $dup) {
            $ids = array_map('intval', explode(',', $dup->ids));

            $keepId = self::selectBestProduct($ids);
            $deleteIds = array_filter($ids, function ($id) use ($keepId) {
                return $id !== $keepId;
            });

            foreach ($deleteIds as $deleteId) {
                // Safety: do not delete products referenced in WooCommerce orders
                $orderRefs = (int) $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) FROM {$wpdb->prefix}woocommerce_order_items oi
                     JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
                     WHERE oim.meta_key = '_product_id' AND oim.meta_value = %d",
                    $deleteId
                ));
                if ($orderRefs > 0) {
                    Log::write(
                        "DuplicateCleanup: Skipping duplicate SKU {$dup->sku} #{$deleteId} - has {$orderRefs} order references",
                        Log::WARNING,
                        Log::CONTEXT_CRONJOB
                    );
                    continue;
                }

                $product = wc_get_product($deleteId);
                if ($product) {
                    Log::write(
                        "DuplicateCleanup: Removing duplicate SKU {$dup->sku}, keeping #{$keepId}, deleting #{$deleteId}",
                        Log::WARNING,
                        Log::CONTEXT_CRONJOB
                    );
                    $product->delete(true);
                    $cleaned++;
                }
            }
        }

        return $cleaned;
    }

    /**
     * Select the best product ID to keep from a list of duplicates.
     * Prefers the product with a thumbnail; falls back to the highest ID (most recent).
     *
     * @param int[] $ids Product IDs (sorted ASC)
     * @return int The ID to keep
     */
    private static function selectBestProduct(array $ids): int
    {
        $withImage = [];

        foreach ($ids as $id) {
            if (get_post_thumbnail_id($id)) {
                $withImage[] = $id;
            }
        }

        if (!empty($withImage)) {
            return end($withImage);
        }

        return end($ids);
    }
  }
}



