<?php  defined('BASEPATH') OR exit('No direct script access allowed');



use GuzzleHttp\Client;



class Samaya_product_raw extends CI_Controller { 



	public function __construct()
	{
		parent::__construct();
		header('X-Robots-Tag: noindex');

	}

	function run_job() {
	    
		$jewelryRecords = $this->getJewelry([]);
		$jewelryRecord = $this->splitJewelryByStoneType($jewelryRecords);
		$this->processJewelryList($jewelryRecord);
		$this->archiveUnavailableRecord($jewelryRecord);
	}

	private function processJewelryList($jewelryRecord) {
	   // print_ex($jewelryRecord);
	   // die;
	   // Step 1: Check if the jewelry record is empty
    if (empty($jewelryRecord)) {
        return; // Exit if no records
    }

    // Step 2: Loop through each jewelry record
    foreach ($jewelryRecord as $row) {

        // Step 3: Skip if no variants are available
        if (empty($row['variants'])) {
            continue; // Skip this record and move to the next if no variants exist
        }
         $specialBarcodes = ["551260", "521569", "536955", "488327","170566","194017","469075","194592","531752"];
         //  if ($row['jewelrybarcode'] == "342121") {
  //  if (in_array($row['jewelrybarcode'], $specialBarcodes)) {
        // Step 4: Extract SKU and last updated timestamp
        $sku = trim($row['sku']);            
        $data_updated_at = $row['updated_at'];  
         $record = $this->prepareJewelryData($row);
     
			// -----------------------------------------------------------------

// 			$checkVariant = $this->db->query(
// 				"SELECT product_id FROM sh_product_variant 
// 				WHERE sku = BINARY '$sku' ORDER BY id LIMIT 1"
// 			)->result();
            
            $checkVariant = $this->db->query(
                    "SELECT product_id FROM sh_product_variant 
                     WHERE TRIM(sku) = TRIM(BINARY '$sku') 
                     ORDER BY id LIMIT 1"
                )->result();
              //  print_pre($checkVariant);
			if(!empty($checkVariant)) {
				$this->updateJewelry($record, $checkVariant[0]->product_id);
				continue;
			}

			// -----------------------------------------------------------------

// 			$checkVariantDeleted = $this->db->query(
// 				"SELECT product_id FROM sh_product_variant_deleted 
// 				WHERE sku = BINARY '$sku' ORDER BY id LIMIT 1"
// 			)->result();
			$checkVariantDeleted = $this->db->query(
				"SELECT product_id FROM sh_product_variant_deleted 
				 WHERE TRIM(sku) = TRIM(BINARY '$sku') 
                     ORDER BY id LIMIT 1"
			)->result();
			if(!empty($checkVariantDeleted)) {
				$this->updateJewelry($record, $checkVariantDeleted[0]->product_id);
				continue;
			}
			// -----------------------------------------------------------------
        // Step 5: Check if the SKU already exists in the `sh_product_raw` table to prevent duplicates
        // $checkProduct = $this->db->query(
        //     "SELECT id FROM sh_product_raw WHERE sku = BINARY '$sku' LIMIT 1"
        // )->result();
         $checkProduct = $this->db->query(
            "SELECT id FROM sh_product_raw WHERE TRIM(sku) = TRIM(BINARY '$sku') LIMIT 1"
        )->result();
         if (!empty($checkProduct)) {
            $this->db->where('sku', $sku); // Make sure to update based on SKU or ID if needed
            $record['is_upload'] = 0;  // Add 'is_upload' => 0 dynamically
            $this->db->update('sh_product_raw', $record); 
        } else {
            // Product does not exist, insert a new one
            $this->db->insert('sh_product_raw', $record); 
        }
    // }
    }
}

// Helper function to prepare data for insertion
private function prepareJewelryData($row) {   
     //$this->delay(1);
      $jewelry_details = [];
	  if (!empty($row['product_type'])) {
		  $jewelry_details[] = "Jewelry Type: {$row['product_type']}";
	  }
	  if (!empty($row['jewelry_category'])) {
		  $jewelry_details[] = "Jewelry Category: {$row['jewelry_category']}";
	  }
	   if (!empty($row['appr_ctw']) && $row['appr_ctw'] != "0.00 Ctw") {
		  $jewelry_details[] = "Apx Ctw: {$row['appr_ctw']}";
	  }
	  if (!empty($row['diamond_quantity'])) {
		  $jewelry_details[] = "Diamond Quantity: {$row['diamond_quantity']}";
	  }
  
// 	  // Gemstone details to prepare (Only add non-empty values)
// 	  $gemstone_details = [];
// 	  if (!empty($row['stone_type'])) {
// 		  $gemstone_details[] = "Stone Type: {$row['stone_type']}";
// 	  }
	  if (!empty($row['diamond_quantity'])) {
		  $gemstone_details[] = "No Of Stones: {$row['diamond_quantity']}";
	  }
	  if (!empty($row['avg_color'])) {
		  $gemstone_details[] = "Average Color: {$row['avg_color']}";
	  }
	  if (!empty($row['avg_clarity'])) {
		  $gemstone_details[] = "Average Clarity: {$row['avg_clarity']}";
	  }
	  if (!empty($row['appr_ctw'])) {
		  $gemstone_details[] = "Carat Weight: {$row['appr_ctw']}";
	  }
	  if (!empty($row['colorstone_quality'])) {
		  $gemstone_details[] = "Quality: {$row['colorstone_quality']}";
	  }
	  if (!empty($row['colorstone_quantity'])) {
		  $gemstone_details[] = "No Of Color Stones: {$row['colorstone_quantity']}";
	  }
        	// Ring Category Mapping
        $ringCategoryMapping = [
            'Stackable' => ['Stackable', 'Stack'],
            'Halo' => ['Halo'],
            'Cluster' => ['CUS', 'Cluster', 'Clus', 'Clst', 'Clust'],
            'Classic' => ['Classic', 'Cl'],
            '3 Stone' => ['3 Stone', '3 Stn'],
            '5 Stone' => ['5 Stone', '5 Stn'],
            'Beaded' => ['Beaded', 'Bead'],
            'Gemstone' => ['GMST','GemStn','Gemstone'],
            "Men's" => ['MNRG'],
        ];
        
        $ring_categories = [];
        
        // Check only if it's a ring and NOT earrings
        if (!empty($row['ebay_type']) &&
            stripos($row['ebay_type'], 'ring') !== false &&
            stripos($row['ebay_type'], 'earring') === false &&
            stripos($row['ebay_type'], 'earrings') === false) {
        
            // Collect values from relevant columns
            $sourceFields = [
                $row['GemStn_type'] ?? '',
                $row['category_code'] ?? '',
                $row['class_code'] ?? '',
                $row['website_brand'] ?? '',
                $row['setcode'] ?? '',
                $row['setting_style'] ?? ''
            ];
        
            $all_parts = [];
            foreach ($sourceFields as $field) {
                $field = str_replace(['/', '\\'], ',', $field);
                $parts = array_map('trim', explode(',', $field));
                $all_parts = array_merge($all_parts, $parts);
            }
        
            // Match keywords from all parts
            foreach ($ringCategoryMapping as $label => $keywords) {
                foreach ($keywords as $key) {
                    foreach ($all_parts as $part) {
                        if (strcasecmp($key, $part) === 0) {
                            $ring_categories[] = $label;
                            break 2; // Avoid duplicate label
                        }
                    }
                }
            }
			if (!empty($row['jewelry_category'])) {
				$jewelryCategory = strtolower(trim($row['jewelry_category']));

				if ($jewelryCategory === 'engagement ring' || $jewelryCategory === 'Engagement Rings' || $jewelryCategory === 'Engagement Ring') {
					$ring_categories[] = 'Engagement Ring';
				}
				if ($jewelryCategory === 'wedding ring'|| $jewelryCategory === 'Wedding Ring') {
					$ring_categories[] = 'Wedding Ring';
				}
			}
        
            $ring_categories = array_unique($ring_categories);
        }
        
    // 	$earrings_categories = [];

    //     if (!empty($row['ebay_type']) && preg_match('/\bEarrings?\b/i', $row['ebay_type']) &&
    //         stripos($row['ebay_type'], 'ring') === false &&
    //         stripos($row['ebay_type'], 'rings') === false ) {
    //         if (!empty($row['jewelry_category'])) {
    //             $earrings_categories[] = $row['jewelry_category'];
    //         }
    //     }
            $earrings_categories = [];

            if (!empty($row['ebay_type']) && stripos($row['ebay_type'], 'earring') !== false) {
                if (!empty($row['jewelry_category'])) {
                    $earrings_categories[] = $row['jewelry_category'];
                }
            }


		$necklaceCategoryMapping = [
			'Diamond Station' => ['DBYD'],
// 			'Diamond'         => ['Classic', 'Crown'],
            'Tennis Necklace' => ['Tennis Necklace'],
			'Cross'           => ['Crss'],
			'Solitaire'       => ['Sol'],
			'Fashion'         => ['FPEND', 'FNECK', 'BLANK'],
			'Gemstone'        => ['GMST','GemStn','Gemstone'],
			'Circle'          => ['Circle', 'Circ'],
		];
		$necklaces_categories = [];

		if (!empty($row['ebay_type']) && preg_match('/\b(Pendants|Necklaces)\b/i', $row['ebay_type']) && ($row['product_type'] != 'Earrings')  ) {
			$fieldsToCheck = [
			    $row['title'] ?? '',
			    $row['GemStn_type'] ?? '',
				$row['category_code'] ?? '',
				$row['class_code'] ?? '',
				$row['website_brand'] ?? '',
				$row['setcode'] ?? '',
				$row['setting_style'] ?? ''
			];

			foreach ($necklaceCategoryMapping as $category => $keywords) {
				foreach ($keywords as $keyword) {
					foreach ($fieldsToCheck as $field) {
						if (stripos($field, $keyword) !== false) {
							$necklaces_categories[] = $category;
							break 2; // break inner loops once a match is found
						}
					}
				}
			}
		 $necklaces_categories = array_unique($necklaces_categories);
		}
// 		 echo  $row['sku'];
        //  print_pre($necklaces_categories);
		$braceletCategoryMapping = [
			'Classic' => ['Classic'],
			'Crown'   => ['Crown'],
			'Station' => ['Station'],
			'Bangle'  => ['BNGL'], // Only for Bangles (type_code)
		];

		$bracelet_categories = [];

		if (!empty($row['ebay_type']) && preg_match('/\b(Bracelets|Bangles)\b/i', $row['ebay_type'])) {
			$fieldsToCheck = [
				$row['category_code'] ?? '',
				$row['class_code'] ?? '',
				$row['website_brand'] ?? '',
				$row['setcode'] ?? '',
				$row['setting_style'] ?? '',
				$row['type_code'] ?? '',
				$row['jewelry_category'] ?? '', 
			];

			foreach ($braceletCategoryMapping as $category => $keywords) {
				foreach ($keywords as $keyword) {
					foreach ($fieldsToCheck as $field) {
						if (stripos($field, $keyword) !== false) {
							$bracelet_categories[] = $category;
							break 2;
						}
					}
				}
			}
			$bracelet_categories = array_unique($bracelet_categories);
		}


    // Prepare arrays for variants, options, metals, etc.
    $variants = [];
    $options = [];
    $metals = [];
    $ctws = [];
    $qualities = [];
    $images = [];
    $videos = [];
    $optionsMergedArr = [];
//print_ex($row['variants']);
    // Step 13: Process each variant (if any)
    foreach ($row['variants'] as $rv) {
        $optionsMerged = $rv['metal'].$rv['ctw'].$rv['quality']; // Combine options

        // Skip if the option combination already exists
        if (in_array($optionsMerged, $optionsMergedArr)) {
            continue;
        }
       
        $optionsMergedArr[] = $optionsMerged; // Add the combination to the list
        // Prepare variant data
        $price = floatval($rv['price']);
        if ($price < 200) {
            $increase = 50; 
        } elseif ($price >= 200 && $price < 500) {
            $increase = 60; 
        } elseif ($price >= 500 && $price < 1000) {
            $increase = 67; 
        } elseif ($price >= 1000 && $price < 2000) {
            $increase = 73; 
        } elseif ($price >= 2000 && $price < 5000) {
            $increase = 80; 
        } else {
            $increase = 100;  
        }
        // Avoid division by zero
        if ($increase == 100) {
            $compare_at_price = $price * 2; // 100% increase means double the price
        } else {
            $compare_at_price = $price / (1 - $increase / 100);
        }
        
        $compare_at_price = round($compare_at_price);
        $cost = ''; // Assuming cost data is optional or comes from another field
        //  if (empty($rv['quality'])) {
        //         continue;
        //     }
        // Add variant data to the `variants` array
        $variants[] = [
            'sku' => trim($row['sku']),
            'sim_sku' => trim($rv['metal_v']), 
            'barcode' => $rv['item_no'],
            'price' => $price,
            'compare_at_price' => $compare_at_price,
            'cost' => $cost,
            'inventory_quantity' => $rv['inventory_quantity'],
            'metal_v' => $rv['metal_v'],
            'child_asin' => $rv['child_asin'],
            'website_stonetype' => $rv['website_stonetype'],
            'pr_ring_category' => json_encode($ring_categories),
            'size' => $rv['size'],
            'option1' => $rv['metal'],
            'option2' => $rv['ctw'],
            'option3' => $rv['quality'],
            'taxable' => 1,
            'fulfillment_service' => 'manual',
            'inventory_policy' => 'deny',
            'inventory_management' => 'shopify',
        ];

        // Add metals, CTWs, and qualities if not empty
        if (!empty($rv['metal'])) {
            $metals[] = $rv['metal'];
        }
        if (!empty($rv['ctw'])) {
            $ctws[] = $rv['ctw'];
        }
        if (!empty($rv['quality'])) {
            $qualities[] = $rv['quality'];
        }
    }
   
    // Step 14: Prepare options based on available data
     $variantsCount = count($variants);
     
    if (!empty($metals) && ($variantsCount == count($metals))) {
        $options[] = ['name' => 'Metal', 'values' => $metals];
    }
    if (!empty($ctws) && ($variantsCount == count($ctws))) {
        $options[] = ['name' => 'Ctw', 'values' => $ctws];
    }
    //echo " variantsCount = > " .$variantsCount ."qualities=>" . count($qualities)."ctws =>". count($ctws); 
    if (!empty($qualities) && ($variantsCount == count($qualities))) {
        $options[] = ['name' => 'Quality', 'values' => $qualities];
    }
    // Step 15: Process images and videos
    foreach ($row['images'] as $ri) {
        $images[] = ['name' => $ri['metal'], 'src' => $ri['src']];
    }
    foreach ($row['videos'] as $ri) {
        $videos[] = ['name' => $ri['metal'], 'src' => $ri['src']];
    }
    if (!empty($row['ring_size'])) {
            $ring_size = $row['ring_size'];
        }else{
            $ring_size = 'S7';
        }
        // $title = $row['title'];
        // $words_to_remove = array_unique([
        //     '14KW', '14KY', '10KW', '10TT', '10KY', '10KR', '14KR', '14TT',
        //     '18KY', '18KW', '18TTT', 'Lab Grown'
        // ]);
        // $title = str_replace($words_to_remove, '', $title);
        // $title = preg_replace('/\b\d+(\/\d+|\.\d+)?\s*(CTW|CT|Cttw|Ctw)\b/i', '', $title);
        // $title = preg_replace('/\b(CTW|CT|Cttw|Ctw)\b/i', '', $title);
        // $title = preg_replace('/\s+/', ' ', $title);
        // $title = trim($title);
        // $title = ucwords(strtolower($title));
        
        // $title = $row['title'];
        
        // $words_to_remove = array_unique([
        //     '14KW', '14KY', '10KW', '10TT', '10KY', '10KR', '14KR', '14TT',
        //     '18KY', '18KW', '18TTT', 'Lab Grown'
        // ]);
        
        // // Remove metal codes
        // $title = str_replace($words_to_remove, '', $title);
        
        // // Protect text inside brackets
        // preg_match_all('/\([^)]*\)/', $title, $matches);
        // $brackets = $matches[0]; // Array of all bracket contents
        
        // // Temporarily remove brackets
        // foreach ($brackets as $b) {
        //     $title = str_replace($b, '', $title);
        // }
        
        // // Remove numbers and CTW outside brackets
        // $title = preg_replace('/\b\d+(\/\d+|\.\d+)?\b/', '', $title);
        // $title = preg_replace('/\b(CTW|CT|Cttw|Ctw)\b/i', '', $title);
        
        // // Put back bracket content
        // foreach ($brackets as $b) {
        //     $title .= ' ' . $b;
        // }
        
        // // Clean up spaces and capitalization
        // $title = preg_replace('/\s+/', ' ', $title);
        // $title = trim($title);
        // $title = ucwords(strtolower($title));
        $title = $row['title'];
        $words_to_remove = array_unique([
            '14KW', '14KY', '10KW', '10TT', '10KY', '10KR', '14KR', '14TT',
            '18KY', '18KW', '18TTT', 'Lab Grown'
        ]);
        
        // Remove metal codes
        $title = str_replace($words_to_remove, '', $title);
        
        // Protect bracket content
        preg_match_all('/\([^)]*\)/', $title, $matches);
        $brackets = $matches[0];
        
        // Temporarily remove brackets
        foreach ($brackets as $b) {
            $title = str_replace($b, '', $title);
        }
        
        // Remove numbers (including decimals/fractions) and CTW/CT outside brackets
        $title = preg_replace('/\b\d+(\.\d+)?(\/\d+)?\s*(CTW|CT|Cttw|Ctw)?\b/i', '', $title);
        
        // Clean multiple spaces
        $title = preg_replace('/\s+/', ' ', $title);
        $title = trim($title);
        
        // Restore bracket content
        foreach ($brackets as $b) {
            $title .= ' ' . $b;
        }
        
        // Final cleanup and capitalization
        $title = preg_replace('/\s+/', ' ', $title);
        $title = trim($title);
        $title = ucwords(strtolower($title));

 // echo $title."<br>";

		$validClarities = ['FL','IF','VVS1','VVS2','VS1','VS2','SI1','SI2','SI3','I1','I2','I3'];
		$clarity = '';
		$parts = preg_split('/[\s\-\/\+]+/', strtoupper($row['avg_clarity']));
		foreach ($parts as $part) {
			if (in_array(trim($part), $validClarities)) {
				$clarity = trim($part);
				break;
			}
		}
    $description = !empty($row['description']) ? $row['description'] : $this->generateDescription($row);
       if (!empty($row['tags'])) {
           $tags = explode(' ', $row['tags']);  // This will split by space
            $tagsString = implode(',', $tags);  // Join them with commas
        } else {
            $tagsString = '';
        }


    $data = [
        'sku' => $row['sku'],
        'sku_2' => $row['sku_2'],
        'setcode' => $row['setcode'],
        'title' => $title ,
        // 'body_html' => $row['description'],
         'body_html' => $description,
        'product_type' => $row['product_type'],
        'jewelry_category' => $row['jewelry_category'],
        'tags' => $row['jewelry_category'] . (!empty($row['new_arrival']) ? ' ,'.$row['new_arrival'] : '') .','. $tagsString,
        // 'tags' => $row['jewelry_category'] . (!empty($row['tags']) ? ' ,'.$row['tags'] : ''),
        'handle' => '',
        'status' => $row['status'],
        'published' => '',
        'seo_description' => '',
        'seo_title' => '',
        'vendor' => 'SamayaJewelry',
        'jewelrybarcode' => $row['jewelrybarcode'],
        'color_name' => $row['color_name'],
        'stone_type' => $row['stone_type'],
        'parent_asin' => $row['parent_asin'],
        'colorstone_quantity' => $row['colorstone_quantity'],
        'metalname' => $row['metalname'],
		'stoneclarity' => $clarity,
		'metal_id' => strtolower(str_replace(' ', '-', trim($row['metalname']))),
        'metalshortname' => $row['metalshortname'],
        'diamond_quantity' => $row['diamond_quantity'],
        'ocassion' => $row['ocassion'],
        'ring_size' => $ring_size ,
        'setting_style' => is_array($row['setting_style']) ? implode(', ', $row['setting_style']) : rtrim($row['setting_style'], '/,'),
        'total_carat_weight' => $row['total_carat_weight'],
        'ebay_type' => $row['ebay_type'],
        'unit_quantity' => $row['unit_quantity'],
        'country_of_origin' => $row['country_of_origin'],
        'colorstone' => $row['colorstone_quality'],
        'json_variant' => json_encode($variants),
        'json_option' => json_encode($options),
        'json_image' => json_encode($images),
        'json_video' => json_encode($videos),
        'json_gemstone_details' => json_encode($gemstone_details),
		'json_ring_category' => json_encode($ring_categories),
        'json_jewelry_details' => json_encode($jewelry_details),
		'json_earrings_category' => json_encode($earrings_categories),
		'json_necklace_category' => json_encode($necklaces_categories),
		'json_bracelet_category' => json_encode($bracelet_categories),
        'data_created_at' => $row['created_at'],
        'data_updated_at' => $row['updated_at'],
        'added_at' => date('Y-m-d h:i'),
    ];
       //print_ex($data);
    //   /* print_pre($variants);
    //   print_pre($data);*/
    //   die;
 
    return $data; // Return the prepared data for insertion or update
    
}
//  public function generateDescription($row) {
//     $carat = !empty($row['total_carat_weight']) ? $row['total_carat_weight'] . " carat" : "";
//     $productType = !empty($row['product_type']) ? strtolower($row['product_type']) : "jewelry";
//     $setting = !empty($row['setting_style']) ? ucfirst(str_replace(['CL/', '/', '-', '_'], ' ', $row['setting_style'])) : "";
    // $goldOptions = [];
    // if (!empty($row['variants']) && is_array($row['variants'])) {
    //     foreach ($row['variants'] as $variant) {
    //         if (!empty($variant['metal'])) {
    //             if (strtoupper($variant['metal']) == "14KW"|| strtoupper($variant['metal']) == "10KW" || strtoupper($variant['metal']) == "18KW") {
    //                 $goldOptions[] = "White Gold";
    //             }
    //             if (strtoupper($variant['metal']) == "14KY" ||strtoupper($variant['metal']) == "10KY"||strtoupper($variant['metal']) == "18KY") {
    //                 $goldOptions[] = "Yellow Gold";
    //             }
    //             if (strtoupper($variant['metal']) == "14KR" ||strtoupper($variant['metal']) == "10KR"||strtoupper($variant['metal']) == "18KR") {
    //                 $goldOptions[] = "Rose Gold";
    //             }
                
    //         }
    //     }
    // }
    // if (empty($goldOptions) && !empty($row['metalname'])) {
    //     $goldOptions[] = $row['metalname'];
    // }
    // $goldOptions = array_unique($goldOptions);
    // $goldOptionText = count($goldOptions) > 1 
    //     ? "your choice of " . implode(" or ", $goldOptions) 
    //     : reset($goldOptions);
//     $stoneType = isset($row['stone_type']) ? strtolower($row['stone_type']) : "";
//     $stoneType = str_replace(['natural', 'labgrown', 'lab grown', 'lab-created', 'lab created'], '', $stoneType);
//     $stoneType = trim($stoneType);
//     return "These elegant {$carat} {$stoneType} {$productType} feature a {$setting} crafted in {$goldOptionText}. "
//          . "The secure setting ensures a comfortable fit, making them suitable for everyday wear or special occasions. "
//          . "With their timeless design and ethical craftsmanship, they add a touch of sophistication to any ensemble.";
// }

function generateDescription($item) {

    // Base values
    $type     = trim($item['product_type'] ?? "");
    $category = trim($item['jewelry_category'] ?? "");
    $stone    = trim($item['stone_type'] ?? "");
    $metal    = trim($item['metalname'] ?? "");
    $qty      = trim($item['diamond_quantity'] ?? "");
    $ringSize = trim($item['ring_size'] ?? "");
    $ctw      = trim($item['variants'][0]['ctw'] ?? ($item['appr_ctw'] ?? ""));
    $settingStyle = trim($item['setting_style'] ?? "");

    if ($ctw !== "" && strpos(strtolower($ctw), "ctw") === false) {
        $ctw .= " carat";
    }

    //--------------------------------------------------
    // NOT-DIAMOND 30 SHORT SENTENCES (GLOBAL RANDOM)
    //--------------------------------------------------

    $genericShort = [
        "Simple and stylish for daily wear.",
        "Crafted with sleek, contemporary artistry.",
        "A sleek, elegant form that radiates quiet sophistication.",
        "Perfectly shaped for a refined minimalist look.",
        "Lightweight and comfortable for everyday use.",
        "Designed to suit any modern wardrobe.",
        "Designed to radiate a gentle, luxurious luminosity.",
        "Smooth contours give it timeless appeal.",
        "Made with precise craftsmanship.",
        "A minimal piece suitable for all occasions.",
        "A versatile piece that elevates every outfit with ease.",
        "Gracefully sculpted to evoke quiet sophistication.",
        "A classic touch with modern comfort.",
        "A beautifully balanced silhouette that enhances your elegance.",
        "Ideal for layering or wearing solo.",
        "Carefully crafted for simple elegance.",
        "A versatile design for everyday moments.",
        "Comfortable for long wear.",
        "Graceful and refined, radiating understated charm.",
        "Blends seamlessly with any accessory.",
        "Timelessly simple and beautifully refined.",
        "A premium feel with a clean finish.",
        "Subtle yet perfect for today’s modern aesthetic.",
        "A thoughtfully shaped minimal form.",
        "Sleek and understated, crafted for a refined modern look.",
        "A lightweight piece for easy styling.",
        "Classic styling made for daily use.",
        "A subtle shine enhances the design.",
        "Designed to dazzle with polished elegance.",
    ];

    //--------------------------------------------------
    // OPENING (25 lines)
    //--------------------------------------------------

    // $opening = [
    //     "This $type is thoughtfully crafted for those who appreciate refined elegance.",
    //     "Designed with a touch of modern sophistication, this $type elevates any look effortlessly.",
    //     "A beautifully crafted $type that blends timeless style with contemporary appeal.",
    //     "This $type showcases exceptional craftsmanship and a graceful aesthetic.",
    //     "Elegantly designed, this $type is made to enhance your personal style.",
    // ];
    $typeupdate = rtrim($type, "s");
    
    // Opening lines
    $opening = [
        "This $typeupdate is thoughtfully crafted for those who appreciate refined elegance.",
        "Designed with a touch of modern sophistication, this $typeupdate elevates any look effortlessly.",
        "A beautifully crafted $typeupdate that blends timeless style with contemporary appeal.",
        "This $typeupdate showcases exceptional craftsmanship and a graceful aesthetic.",
        "Elegantly designed, this $typeupdate is made to enhance your personal style.",
    ];
     //--------------------------------------------------
    // STONE LINES
    //--------------------------------------------------

    $stoneLines = [];
    if ($stone && $qty && $ctw) {
        $stoneLines = [
           "$qty hand-set $stone stones totaling $ctw add refined sparkle.",
            "A polished arrangement of $stone stones brings soft radiance.",
             "$stone stones elevate the design with a pristine, luxurious glow.",
            "The $stone deliver a balanced touch of shimmer.",
            "A premium $stone centerpiece adds subtle brilliance.",
            "$stone detailing offers a gentle, elegant glow.",
        ];

    } elseif ($stone) {
        $stoneLines = [
            "A premium $stone centerpiece adds subtle brilliance.",
            "$stone detailing offers a gentle, elegant glow.",
        ];
    }

    // //--------------------------------------------------
    // // METAL LINES
    // //--------------------------------------------------

    // $metalLines = [];
    // if ($metal) {
    //     $metalLines = [
    //         "Finely forged in $metal for enduring elegance and brilliance.",
    //         "Polished to perfection in $metal for a timeless glow.",
    //         "A polished $metal finish enhances its smooth profile.",
    //         "$metal adds a warm and elegant tone.",
    //     ];
    // }
    
    $goldOptions = [];
    if (!empty($item['variants']) && is_array($item['variants'])) {
        foreach ($item['variants'] as $variant) {
            if (!empty($variant['metal'])) {
                if (strtoupper($variant['metal']) == "14KW"|| strtoupper($variant['metal']) == "10KW" || strtoupper($variant['metal']) == "18KW") {
                    $goldOptions[] = "White Gold";
                }
                if (strtoupper($variant['metal']) == "14KY" ||strtoupper($variant['metal']) == "10KY"||strtoupper($variant['metal']) == "18KY") {
                    $goldOptions[] = "Yellow Gold";
                }
                if (strtoupper($variant['metal']) == "14KR" ||strtoupper($variant['metal']) == "10KR"||strtoupper($variant['metal']) == "18KR") {
                    $goldOptions[] = "Rose Gold";
                }
                
            }
        }
    }

    if (empty($goldOptions)) {
        if (!empty($row['metalname'])) {
            $goldOptions[] = $row['metalname'];
        } 
    }
    
    // Remove duplicates
    $goldOptions = array_unique($goldOptions);
    
    // Build readable list (White Gold and Yellow Gold)
    if (count($goldOptions) == 1) {
        $selectedMetal = $goldOptions[0];
    } else {
        $last = array_pop($goldOptions);
        $selectedMetal = implode(", ", $goldOptions) . " and " . $last;
    }
    
    // Description lines
    $metalLines = [
        "Finely forged in $selectedMetal for enduring elegance and brilliance.",
        "Polished to perfection in $selectedMetal for a timeless glow.",
        "A polished $selectedMetal finish enhances its smooth profile.",
        "$selectedMetal adds a warm and elegant tone.",
    ];
   
    //--------------------------------------------------
    // CATEGORY LINES
    //--------------------------------------------------

    $categoryLines = [];
    if ($category) {
        $categoryLines = [
            "From casual to glamorous—$category fits every occasion.",
            "A $category that captures elegance in every detail.",
            "Tiny details, big impression—$category for every look.",
            "A $category designed to reflect your timeless taste.",
            "Effortless charm meets modern style in $category.",
            "A $category that sparkle with understated luxury.",
            "Classic design, contemporary appeal—$category.",
            "A $category that transform any outfit with subtle elegance.",
            "Your everyday elegance, perfected with $category.",
            "A $category made to shine, celebrate, and captivate.",
        ];
    }
     //--------------------------------------------------
    // SETTING STYLE LINES (new)
    //--------------------------------------------------
    $settingStyleLines = [];
    if ($settingStyle) {
        $settingStyleLines = [
         "The $settingStyle setting adds a distinctive, elegant touch.",
        "Crafted with a unique $settingStyle setting for an eye-catching design.",
        "The $settingStyle design gives this piece a timeless, classic appeal.",
        "$settingStyle detailing enhances the overall sophistication.",
        "Featuring a $settingStyle setting, this piece exudes refined luxury.",
        ];
    }

    //--------------------------------------------------
    // TYPE SPECIFIC
    //--------------------------------------------------

    $typeLower = strtolower($type);
    $typeLines = [];

    switch ($typeLower) {

        case "rings":
            $sizeText = $ringSize ? " Available in size $ringSize." : "";
            $typeLines = [
                "A contemporary classic that’s perfect for every day.$sizeText",
            	"Sculpted for style, polished for perfection.$sizeText",
                "Perfect for stacking or wearing solo.$sizeText",
                "A timeless ring ideal for any style.$sizeText",
            ];
            break;

        case "pendants":
            $typeLines = [
                "A minimal pendant with soft, graceful flow.",
                "Perfect for layering or wearing solo.",
    	        "Elegantly framed to complement any neckline.",
                "Designed for subtle everyday shine.",
            ];
            break;

        case "necklaces":
            $typeLines = [
                "A subtle necklace designed for timeless wear.",
            	"Gracefully drapes around the neck for a contemporary style.",
                "Perfect for layering and daily styling.",
                "A clean silhouette adds a graceful touch.",
            ];
            break;

        case "earrings":
            $typeLines = [
                "Lightweight earrings crafted for comfort.",
                "A balanced pair designed for subtle elegance.",
            	"Modern elegance captured in a flawless profile.",
                "Perfect for daily wear.",
            ];
            break;

        case "bracelets":
            $typeLines = [
               "A sleek bracelet made for everyday ease.",
	           "Comfortable on the wrist with pure elegance.",
               "A polished bracelet, perfect for minimal looks.",
               "Designed for smooth and effortless wear.",
            ];
            break;

        case "bangles":
            $typeLines = [
                "A clean circular bangle for modern simplicity.",
                "A smooth bangle profile adds quiet elegance.",
                "Perfect for stacking or wearing alone.",
                "Crafted for a timeless minimal look.",
            ];
            break;

        default:
            $typeLines = $genericShort;
    }

    //--------------------------------------------------
    // CLOSING (30 lines)
    //--------------------------------------------------

    $closing = [
        "A meaningful gift or a refined personal luxury.",
        "A perfect blend of craftsmanship and elegance.",
        "An elevated piece that brings timeless beauty.",
        "Designed to be cherished for years to come.",
        "A stunning piece that enhances any jewelry wardrobe.",
        "A thoughtful keepsake for any special moment.",
        "A luxurious touch to elevate your accessory collection.",
        "Timeless in spirit and flawless in design.",
        "A beautiful piece crafted to last for generations.",
        "An elegant addition that reflects your unique style.",
        "A refined treasure perfect for marking special milestones.",
        "A gracefully designed piece you’ll love wearing.",
        "A celebration of artistry, elegance and luxury.",
        "A polished finishing touch to any ensemble.",
        "A brilliant expression of sophisticated taste.",
        "A truly versatile piece you’ll reach for often.",
        "A stylish companion for memorable occasions.",
        "A radiant accessory made for everyday charm.",
        "A gorgeous piece that adds luxury to any moment.",
        "An everlasting symbol of style and refinement.",
        "A premium accessory crafted to perfection.",
        "A meaningful token of timeless elegance.",
        "An unforgettable gift for someone special.",
        "A statement of luxury crafted with care.",
        "A refined masterpiece designed for modern elegance.",
        "A classic choice that never goes out of style.",
        "A graceful accent for any jewelry collection.",
        "A polished design created for lasting beauty.",
        "A timeless treasure that feels truly special.",
    ];
    
    //--------------------------------------------------
    // FINAL DESCRIPTION
    //--------------------------------------------------

//     $pick = fn($array) => $array[array_rand($array)] ?? "";

//     $parts = [
//         $pick($opening),
//         $pick($stoneLines),
//         $pick($metalLines),
//         $pick($categoryLines),
//         $pick($typeLines),
//         $pick($closing),
//     ];

//     $parts = array_filter($parts);
//     $final = implode(" ", $parts);
//     $final = preg_replace('/\s+/', ' ', $final);
//   // print_ex($final);
//     return trim($final);

    $pick = fn($array) => !empty($array) ? $array[array_rand($array)] : '';
    
    $parts = [
        $pick($opening),
        $pick($settingStyleLines),
        $pick($stoneLines),
        $pick($metalLines),
        $pick($categoryLines),
        $pick($typeLines),
        $pick($closing),
    ];
    
    // Filter out any empty strings from the final array
    $parts = array_filter($parts, fn($part) => $part !== '');
    
    // Join the parts to create the final description
    $final = implode(" ", $parts);
    $final = preg_replace('/\s+/', ' ', $final);  // Remove extra spaces
    return trim($final);

}


private function archiveUnavailableRecord($jewelryRecord) {
		if (empty($jewelryRecord)) {
			return;
		}
		$product_data = [];

		$productList = $this->db->query(
			"SELECT product_id,status,
				(SELECT sku FROM sh_product_variant v 
				WHERE v.product_id=p.product_id limit 1) AS sku 
			FROM sh_product p WHERE status = 'active'"
		)->result();

		$availabelSKU = array_column($jewelryRecord, 'sku');
		$availabelSKU = array_map('trim', $availabelSKU);

		foreach ($productList as $row) {
			if(in_array(trim($row->sku ?? ''), $availabelSKU)) {
				continue;
			}

			$product_data[] = [
				'product_id'=> $row->product_id,
				'status'=> 'archived',
				'edited_at'=> date('Y-m-d h:i'),
				'is_update'=> 1,
				'is_upload'=> 0
			];
		}

		if(!empty($product_data)) {
			$this->db->update_batch('sh_product', $product_data, 'product_id');
		}

	}
	
    /*update jewelry to local DB*/
	private function updateJewelry($row, $product_id) {
	  // print_ex($row);

		if(empty($row)) {
			return '';
		}
		// -------------------------------------------------------------
		// image compare 
		$recordImage = $this->db->query(

			"SELECT json_image FROM sh_product WHERE product_id = $product_id"

		)->result();
				$oldImages = json_decode($recordImage[0]->json_image ?? '', true);
				$newImages = json_decode($row['json_image'], true);	
				// 		$diffImage = multiArrayDiff($oldImages, $newImages);
		// Check if $oldImages and $newImages are valid arrays before passing them
		if (!is_array($oldImages)) {
			$oldImages = []; // Set to an empty array if invalid or null
		}

		if (!is_array($newImages)) {
			$newImages = []; // Set to an empty array if invalid or null
		}
      // print_ex($row['updated_at']); die;
		$diffImage = multiArrayDiff($oldImages, $newImages);
		// -------------------------------------------------------------
    // 	// Category JSONs
        $this->upsertMetafield($product_id, 'bracelet_category', $row['json_bracelet_category'], 'list.single_line_text_field', true);
        $this->upsertMetafield($product_id, 'necklace_category', $row['json_necklace_category'], 'list.single_line_text_field', true);
        $this->upsertMetafield($product_id, 'earrings_category', $row['json_earrings_category'], 'list.single_line_text_field', true);
        $this->upsertMetafield($product_id, 'ring_category', $row['json_ring_category'], 'list.single_line_text_field', true);
         // Details JSON
        $this->upsertMetafield($product_id, 'gemstone_details', $row['json_gemstone_details'], 'list.single_line_text_field', true);
        $this->upsertMetafield($product_id, 'jewelry_details', $row['json_jewelry_details'], 'list.single_line_text_field', true);
        $this->upsertMetafield($product_id, 'colorstone', $row['colorstone']);
        // Plain text fields
        $this->upsertMetafield($product_id, 'sku_2', $row['sku_2']);
        $this->upsertMetafield($product_id, 'setcode', $row['setcode']);
        $this->upsertMetafield($product_id, 'jewelrybarcode', $row['jewelrybarcode']);
        $this->upsertMetafield($product_id, 'color_name', $row['color_name']);
        $this->upsertMetafield($product_id, 'stone_type', $row['stone_type']);
        $this->upsertMetafield($product_id, 'parent_asin', $row['parent_asin']);
        $this->upsertMetafield($product_id, 'colorstone_quantity', $row['colorstone_quantity']);
        $this->upsertMetafield($product_id, 'metalname', $row['metalname']);
        $this->upsertMetafield($product_id, 'stoneclarity', $row['stoneclarity']);
        $this->upsertMetafield($product_id, 'metal_id', $row['metal_id']);
        $this->upsertMetafield($product_id, 'metalshortname', $row['metalshortname']);
        $this->upsertMetafield($product_id, 'diamond_quantity', $row['diamond_quantity']);
        $this->upsertMetafield($product_id, 'ocassion', $row['ocassion']);
        $this->upsertMetafield($product_id, 'setting_style', $row['setting_style']);
        $this->upsertMetafield($product_id, 'total_carat_weight', $row['total_carat_weight']);
        $this->upsertMetafield($product_id, 'ebay_type', $row['ebay_type']);
         // Set and modify the country_of_origin
            $row['country_of_origin'] = str_replace('A', '', 'USA');
        
        	$country_of_origin = $row['country_of_origin'];
        
        $this->upsertMetafield($product_id, 'country_of_origin', $country_of_origin);
        
        // Special cases
        if ($row['ebay_type'] == 'Rings') {
            $this->upsertMetafield($product_id, 'ring_size', $row['ring_size']);
        }
        if ($row['ebay_type'] == 'Necklaces' && $row['ring_size'] != 'S7') {
            $this->upsertMetafield($product_id, 'necklace_length', $row['ring_size']);
        }


	// ------------------------------------------------------------- 
		$product_data = [

			'title'=> $row['title'],

			'handle'=> $row['handle'],

			'body_html'=> $row['body_html'],

			'product_type'=> $row['product_type'],

			'tags'=> $row['tags'],

			'status'=> $row['status'],

			'seo_title'=> $row['seo_title'],

			'seo_description'=> $row['seo_description'],

			'published'=> $row['published'],

		    'json_option' => $row['json_option'],

		    'json_video' => $row['json_video'],

		    // 'json_gemstone_details' => $row['json_gemstone_details'],

			'edited_at'=> date('Y-m-d h:i'),

			'is_update'=> 1,

 			'is_upload'=> 0

		];



		if(!empty($diffImage)) {

			$product_data['json_image'] = $row['json_image'];

			$product_data['is_image_upload'] = 0;

		}



		$variants = json_decode(($row['json_variant'] ?: ''), true);

		$variant_data = [];

		$new_variant_data = [];



		foreach($variants as $rv) {
		   $sku = trim($rv['sku'] ?? '');

			$barcode = $rv['barcode'] ?? '';

			$custom_size = $rv['size'] ?? '';

			$custom_metal_v = $rv['metal_v'] ?? '';
			
			$custom_child_asin = $rv['child_asin'] ?? '';
			
			$custom_website_stonetype = $rv['website_stonetype'] ?? '';
			
			$custom_pr_ring_category= $rv['pr_ring_category'] ?? '';
			
        	$record = $this->db->query(

				"SELECT id, variant_id, product_id FROM sh_product_variant 

				WHERE TRIM(sku) = TRIM(BINARY '$sku') AND barcode LIKE '%$barcode%'"

			)->result();
			
// 			echo $this->db->last_query();
// 			echo "<br>";
			
			$auto_id = $record[0]->id ?? '';

			$variant_id = $record[0]->variant_id ?? '';

			$var_product_id = $record[0]->product_id ?? '';

			if(!empty($variant_id)) {

				$this->processVariantMetafield($rv, $variant_id);

			}



			unset($rv['size'], $rv['metal_v'],$rv['website_stonetype'],$rv['child_asin'],$rv['pr_ring_category']);

			if(!empty($variant_id)) {

				$variant_data[] = [

					'variant_id'=> $variant_id,

					'custom_size'=> $custom_size,

					'custom_metal_v'=> $custom_metal_v,
					
					'custom_child_asin'=> $custom_child_asin,
					
					'custom_website_stonetype'=> $custom_website_stonetype,
					
					'custom_pr_ring_category' => $custom_pr_ring_category,

					...$rv

				];



				continue;

			}
        	if(empty($variant_id)) {

				$this->db->delete('sh_product_variant', ['id'=> $auto_id]);		



				$new_variant_data[] = [

					'product_id'=> $product_id,

					'custom_size'=> $custom_size,

					'custom_metal_v'=> $custom_metal_v,
					
					'custom_child_asin'=> $custom_child_asin,
					
                    'custom_website_stonetype'=> $custom_website_stonetype, 
                    
                    'custom_pr_ring_category' => $custom_pr_ring_category,
                    
				//	'custom_quality'=> $custom_quality,

					'is_new'=> 1,

					...$rv				

				];

				

			}



		}



		$this->db->update('sh_product', $product_data, ['product_id'=> $product_id]);



		if(!empty($variant_data)) {

// 		print_pre($variant_data);

			$this->db->update_batch('sh_product_variant', $variant_data, 'variant_id');



			// Delete Unavailable Variants

			$availableVariantIdArr = array_column($variant_data, 'variant_id');

			$availableVariantId = implode("','", $availableVariantIdArr);

			$this->db->query(

				"INSERT INTO sh_product_variant_deleted SELECT * FROM sh_product_variant  

				WHERE product_id = '$product_id' AND variant_id NOT IN('$availableVariantId')"

			);



			$this->db->query(

				"DELETE FROM sh_product_variant 

				WHERE product_id = '$product_id' AND variant_id NOT IN('$availableVariantId')"

			);

		}



		if(!empty($new_variant_data)) {

			$this->db->insert_batch('sh_product_variant', $new_variant_data);

		}



	}	




private function processVariantMetafield($record, $variant_id) {
    // print_ex($record);
    if (empty($variant_id)) {
        return;
    }

    // List of metafields to process: key => source field in $record
    $metafields = [
        'website_stonetype' => 'website_stonetype',
        'metal_v'           => 'metal_v',
        'sizing'            => 'size',
        'child_asin'            => 'child_asin',
    ];
     if (!empty($record['pr_ring_category']) && $record['inventory_quantity'] > 0  &&  $record['pr_ring_category'] !== '[]') {
        $metafields['pr_ring_category'] = 'pr_ring_category';
    }
        
   // print_pre($record['inventory_quantity']);
   // print_pre($metafields);
    foreach ($metafields as $meta_key => $record_key) {
        $new_value = $record[$record_key] ?? null;
       
        $this->updateOrInsertMetafield($variant_id, $meta_key, $new_value);
    }
}

private function updateOrInsertMetafield($variant_id, $meta_key, $new_value) {
    // if (empty($new_value)) {
    //     return;
    // }
    if($meta_key === 'pr_ring_category') {
        $type = 'list.single_line_text_field';
    }else{
        $type = 'single_line_text_field';
    }
      $existing1 = $this->db->query(
        "SELECT meta_id, value FROM sh_variant_metafield WHERE variant_id = ? AND meta_key = ?",
        [$variant_id, $meta_key]
    )->result();

    if (empty($new_value)) {
        if (!empty($existing1)) {
            $this->db->update(
                'sh_variant_metafield',
                ['value' => '', 'to_update' => 1],  // Blank update karo aur to_update set karo
                ['meta_id' => $existing1[0]->meta_id]
            );
        }
        return;
    }

    $existing = $this->db->query(
        "SELECT meta_id, value FROM sh_variant_metafield WHERE variant_id = ? AND meta_key = ?",
        [$variant_id, $meta_key]
    )->result();

    if (!empty($existing)) {
        if ($existing[0]->value !== $new_value) {
            $this->db->update(
                'sh_variant_metafield',
                ['value' => $new_value, 'to_update' => 1],
                ['meta_id' => $existing[0]->meta_id]
            );
        }
    } else {
        $insertData = [
            'variant_id' => $variant_id,
            'namespace'  => 'custom',
            'meta_key'   => $meta_key,
            'value'      => $new_value,
            'type'       => $type,
            'to_create'  => 1,
        ];
        $this->db->insert('sh_variant_metafield', $insertData);
    }
}


private function splitJewelryByStoneType($jewelryRecords) {
      // Setting Style Map
        $styleMap = [
            "SOL" => "Solitaire",
            "PB/" => " ",
            "SB/" => " ",
            "Twisted/" => "Infinity",
            "Crown/" => "Crown",
            "Classic" => "Classic",
            "Classic/" => "Classic",
            "Classic/Paperclip" => "Classic/Paperclip",
            "Classic/Bar" => "Classic/Bar",
            "Classic/Wrap" => "Classic/Wrap",
            "Classic/Stack/" => "Classic/Stackable",
            "Classic/Stack" => "Classic/Stackable",
            "Classic/ Stack/" => "Classic/Stackable",
            "Half/Stack" => "Stackable",
            "Half/Stack/" => "Stackable",
            "Half/Stack/Curved" => "Curved/Stackable",
            "UPrg/Half/Stack" => "U Prong/Stackable",
            "UPrg/Half/Stack/" => "U Prong/Stackable",
            "UPrg/Full/Stack/" => "U Prong/Stackable",
            "UPrg/" => "U Prong",
            "UPrg/5 Stn/" => "U Prong/5 Stone",
            "Classic/Full/Stack/" => "Classic/Stackable",
            "Classic/Sclp/Half/" => "Classic/Scallop",
            "Classic/Sclp/Half/Stack/" => "Classic/Scallop/Stackable",
            "Classic/Sclp/Full/Stack/" => "Classic/Scallop/Stackable",
            "CL/Solitaire" => "Classic/Solitaire",
            "CL /Solitaire" => "Classic/Solitaire",
            "CL/Flex/Btn" => "Classic/Flexible",
            "Star/" => "Solitaire",
            "Shr Prg/" => "Shared Prong",
            "Shr Prg/Stack/" => "Shared Prong/Stackable",
            "SH Prg/5 Stn/" => "Shared Prong/5 stones",
            "Stack/" => "Stackable",
            "Stack/Half/" => "Stackable",
            "Wrap/Spring" => "Wrap/Spring",
            "5 Stn/" => "5 Stone",
            "Tension/" => "Tension",
            "Bead/Full/Stack/" => "Beaded/Stackable",
            "F Pave/Half/Stack/" => "French Pave Setting/Stackable"
        ];

    $result = [];

    // Map metal codes to full metal names
    $metalMap = [
        'KW' => 'White Gold',
        'KY' => 'Yellow Gold',
        'KR' => 'Rose Gold',
        'TT' => 'Two Tone'
    ];

    foreach ($jewelryRecords as $item) {
        $sku_2 = substr($item['variants'][0]['metal_v'], 0, 9);
        $item['sku_2'] = $sku_2 ?? '' ;
         if (!empty($item['setting_style'])) {
            $raw = stripslashes($item['setting_style']);  // "PB\/" → "PB/"
            $item['setting_style'] = $styleMap[$raw] ?? $raw;
        }
        $item['GemStn_type'] =  $item['stone_type'] ?? '' ; 
        if (!isset($item['variants']) || empty($item['variants'])) {
            $result[] = $item;
            continue;
        }
            // Add this sorting here:
        usort($item['variants'], function($a, $b) {
            $aEmpty = empty($a['child_asin']);
            $bEmpty = empty($b['child_asin']);
            if ($aEmpty === $bEmpty) {
                return 0;
            }
            return $aEmpty ? 1 : -1;
        });


        $labGrownVariants = [];
        $naturalDiamondVariants = [];

        // Group images by metal for easy lookup
        $imagesByMetal = [];
        if (isset($item['images']) && is_array($item['images'])) {
            foreach ($item['images'] as $img) {
                $metalName = $img['metal'] ?? '';
                $imagesByMetal[$metalName][] = $img['src'];
            }
        }

        // Classify variants and handle image checks
        foreach ($item['variants'] as $variant) {
            $variantMetal = $variant['metal'] ?? '';
            $metalCode = substr($variantMetal, -2); // e.g., "KW"
            $metalName = $metalMap[$metalCode] ?? '';
           // echo 'website_stonetype'. $variant['metal_v'].'=>'.$variant['website_stonetype']."<br>";
            // If no image for this metal, set inventory_quantity = 0
            $hasImage = isset($imagesByMetal[$metalName]) && !empty($imagesByMetal[$metalName]);
            if (!$hasImage) {
                $variant['inventory_quantity'] = 0;
            }

            // Normalize stone type
            $stonetypeRaw = isset($variant['website_stonetype']) ? trim($variant['website_stonetype']) : '';
            $stonetype = strtolower($stonetypeRaw);
            
          //  echo  $variant['metal_v'].'=>'.$stonetype."<br>";
            // Lab Grown check
            if (strpos($stonetype, 'lab grown') !== false) {
                //echo 'lab grown'. $variant['metal_v'].'=>'.$stonetype."<br>";;
                $labGrownVariants[] = $variant;
            }
            // Natural Diamond (including variants with GemStn/Gemstone/gemstone)
            // elseif (
            //     $stonetype === '' ||
            //     $stonetype === 'diamond' ||
            //     preg_match('/^natural diamond\s*(\/\s*(gemstn|gemstone))?$/i', $stonetypeRaw)
            // ) {
            //     echo 'Natural Diamond'.$variant['metal_v'].'=>'.$stonetype."<br>";
            //     $naturalDiamondVariants[] = $variant;
            // }
            else{
                //  echo 'else'. $variant['metal_v'].'=>'.$stonetype."<br>";
                 $naturalDiamondVariants[] = $variant;
            }
        }

        // Create Lab Grown record if exists
        if (!empty($labGrownVariants)) {
            $labItem = $item;
            $labItem['variants'] = $labGrownVariants;
            $labItem['sku'] = rtrim($labItem['sku'], '/') . '/Lab';
            $labItem['setcode'] = rtrim($labItem['setcode'], '/') . '/Lab';
            if (!empty($labItem['stone_type'])) {
            $labItem['stone_type'] = 'Lab Grown';
            }
             if (!empty($item['parent_asin_lab'])) {
                $labItem['parent_asin'] = $item['parent_asin_lab'];
            }
            $result[] = $labItem;
        }

        // Create Natural Diamond record if exists
        if (!empty($naturalDiamondVariants)) {
            $naturalItem = $item;
            $naturalItem['variants'] = $naturalDiamondVariants;
            if (!empty($naturalItem['stone_type'])) {
            $naturalItem['stone_type'] = 'Natural Diamond';
            }
            $result[] = $naturalItem;
        }
    }
//print_ex($result);
    return $result;
}

private function upsertMetafield($product_id, $meta_key, $newValue, $type = 'single_line_text_field', $isJson = false)
{
    // Handle JSON values
    if ($isJson) {
        $decoded = json_decode($newValue, true);

        // Delete if null, empty array, empty string, or '0'
        if ($decoded === null || $decoded === [] || $newValue === '' || $newValue === '0') {
            // Check if record exists before deleting
            $existingRecord = $this->db->get_where("sh_product_metafield", [
                "product_id" => $product_id,
                "meta_key"   => $meta_key
            ])->row_array();  // Get as associative array

            if ($existingRecord) {
                $this->db->delete("sh_product_metafield", ["product_id" => $product_id, "meta_key" => $meta_key]);
            }
            return;
        }

        // Valid JSON to string
        $newValue = json_encode($decoded, JSON_UNESCAPED_UNICODE);
    } else {
        // Non-JSON values
        if ($newValue === null || $newValue === '' || $newValue === '[]' || $newValue === '0') {
            // Check if record exists before deleting
            $existingRecord = $this->db->get_where("sh_product_metafield", [
                "product_id" => $product_id,
                "meta_key"   => $meta_key
            ])->row_array();  // Get as associative array

            if ($existingRecord) {
                $this->db->delete("sh_product_metafield", ["product_id" => $product_id, "meta_key" => $meta_key]);
            }
            return;
        }
    }

    // Check if the record already exists
    $existingRecord = $this->db->get_where("sh_product_metafield", [
        "product_id" => $product_id,
        "meta_key"   => $meta_key
    ])->row_array();  // Get as associative array

    if ($existingRecord) {
        // If the value is different, update the record
        if ($existingRecord['value'] !== $newValue) {
            $this->db->update(
                "sh_product_metafield",
                ["value" => $newValue, "to_update" => 1],
                ["product_id" => $product_id, "meta_key" => $meta_key]
            );
        }
    } else {
        // Record doesn't exist, insert new one
        $this->db->insert("sh_product_metafield", [
            "product_id" => $product_id,
            "namespace"  => "custom",
            "meta_key"   => $meta_key,
            "value"      => $newValue,
            "type"       => $type,
            "to_create"  => 1,
        ]);
    }
}


/*get jewelry list*/
	private function getJewelry($params) {
		$record = [];

		$client = new Client();

		$headers = [];
		  
		$response = $client->request('GET', 'https://shop.simplexdiam.com/admin/auto_shopify_api', [
			'http_errors' => false,
			'headers' => $headers,
		    'query' => $params
		]);
		  
		if($response->getStatusCode() > 300) {
		    return $record;
		}

		$body = $response->getBody();
		$parsedBody = json_decode($body, true);

		$record = $parsedBody['records'];

		$dataLog = [
            'request' => json_encode($params),
            'response' => json_encode($parsedBody),
            'method' => 'GET',
            'endpoint' => "https://shop.simplexdiam.com/admin/auto_shopify_api",
            'added_at' => date('Y-m-d h:i'),
        ];
        $this->db->insert('external_api_log', $dataLog);

		return $record;
	}

	private function delay($sec) {
		sleep($sec);
	}


}