<?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
        }
// if (empty($row['variants'])) {
//     $checkVariant = $this->db->query(
//         "SELECT product_id FROM sh_product_variant 
//         WHERE sku = BINARY '$sku' ORDER BY id LIMIT 1"
//     )->result();

//     if (!empty($checkVariant) && isset($checkVariant[0]->product_id)) {
//         $product_data[] = [
//             'product_id' =>  $checkVariant[0]->product_id,
//             'status' => 'archived',
//             'edited_at' => date('Y-m-d H:i:s'),
//             'is_update' => 1,
//             'is_upload' => 0
//         ];

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


        // 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();

			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();

			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();

       
         if (!empty($checkProduct)) {
            // Product exists, update it
              
            $this->db->where('sku', $sku); // Make sure to update based on SKU or ID if needed
            $this->db->update('sh_product_raw', $record); // Update the existing record
        } else {
            // Product does not exist, insert a new one
            $this->db->insert('sh_product_raw', $record); // Insert a new record
        }
        

    }       
}

// Helper function to prepare data for insertion
private function prepareJewelryData($row) {   
      $jewelry_details = [];
	  if (!empty($row['appr_ctw'])) {
		  $jewelry_details[] = "Apx Ctw: {$row['appr_ctw']}";
	  }
// 	  if (!empty($row['stone_type'])) {
// 		  $jewelry_details[] = "Stone Type: {$row['stone_type']}";
// 	  }
	  if (!empty($row['product_type'])) {
		  $jewelry_details[] = "Jewelry Type: {$row['product_type']}";
	  }
	  if (!empty($row['jewelry_category'])) {
		  $jewelry_details[] = "Jewelry Category: {$row['jewelry_category']}";
	  }
  
// 	  // 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'],
            "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['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'];
            }
        }

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

		if (!empty($row['ebay_type']) && preg_match('/\b(Pendants|Necklaces)\b/i', $row['ebay_type'])) {
			$fieldsToCheck = [
				$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);
		}
		$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 = [];

    // 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

        // 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'],
            'website_stonetype' => $rv['website_stonetype'],
            '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];
    }
    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));



		$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);

    $data = [
        'sku' => $row['sku'],
        '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'] : '') . (!empty($row['tags']) ? ' ,'.implode(',', (array)$row['tags']) : ''),
        // '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'],
        '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'),
    ];
    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") {
                    $goldOptions[] = "14k White Gold";
                }
                if (strtoupper($variant['metal']) == "14KY") {
                    $goldOptions[] = "14k Yellow 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.";
}



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) {

		if(empty($row)) {

			return '';

		}

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

		// image compare 



		$recordImage = $this->db->query(

			"SELECT json_image FROM sh_product WHERE product_id = $product_id"

		)->result();


		//print_ex($product_id);
				$oldImages = json_decode($recordImage[0]->json_image ?? '', true);
			//	print_r($oldImages);

				$newImages = json_decode($row['json_image'], true);
		//print_r($newImages);



		// 		$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
		}

		$diffImage = multiArrayDiff($oldImages, $newImages);

		// -------------------------------------------------------------
		//  bracelet_category METAFIELD COMPARISON & UPSERT
		$recordBraceletMeta = $this->db->query(
			"SELECT meta_id, value FROM sh_product_metafield 
			WHERE product_id = $product_id AND meta_key = 'bracelet_category'"
		)->result();

		$customBraceletCategoryArr = json_decode($row['json_bracelet_category'], true);

		if (!empty($recordBraceletMeta) && !empty($customBraceletCategoryArr)) {
			$diffMeta = 0;

			if (!empty($recordBraceletMeta[0]->meta_id)) {
				$oldMeta = $recordBraceletMeta[0]->value ?: '[]';
				$newMeta = json_encode($customBraceletCategoryArr);
				$diffMeta = strcmp($oldMeta, $newMeta);
			}

			if ($diffMeta != 0) {
				$this->db->update(
					'sh_product_metafield',
					['value' => json_encode($customBraceletCategoryArr), 'to_update' => 1],
					['meta_id' => $recordBraceletMeta[0]->meta_id]
				);
			}
		} else if (empty($recordBraceletMeta) && !empty($customBraceletCategoryArr)) {
			$metafieldInsertData = [
				'product_id' => $product_id,
				'namespace' => 'custom',
				'meta_key' => 'bracelet_category',
				'value' => json_encode($customBraceletCategoryArr),
				'type' => 'list.single_line_text_field',
				'to_create' => 1,
			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);
		}


		//  necklace_category METAFIELD COMPARISON & UPSERT
		$recordNecklaceMeta = $this->db->query(
			"SELECT meta_id, value FROM sh_product_metafield 
			WHERE product_id = $product_id AND meta_key = 'necklace_category'"
		)->result();

		$customNecklaceCategoryArr = json_decode($row['json_necklace_category'], true);

		if (!empty($recordNecklaceMeta) && !empty($customNecklaceCategoryArr)) {
			$diffMeta = 0;

			if (!empty($recordNecklaceMeta[0]->meta_id)) {
				$oldMeta = $recordNecklaceMeta[0]->value ?: '[]';
				$newMeta = json_encode($customNecklaceCategoryArr);
				$diffMeta = strcmp($oldMeta, $newMeta);
			}

			if ($diffMeta != 0) {
				$this->db->update(
					'sh_product_metafield',
					['value' => json_encode($customNecklaceCategoryArr), 'to_update' => 1],
					['meta_id' => $recordNecklaceMeta[0]->meta_id]
				);
			}
		} else if (empty($recordNecklaceMeta) && !empty($customNecklaceCategoryArr)) {
			$metafieldInsertData = [
				'product_id' => $product_id,
				'namespace' => 'custom',
				'meta_key' => 'necklace_category',
				'value' => json_encode($customNecklaceCategoryArr),
				'type' => 'list.single_line_text_field',
				'to_create' => 1,
			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);
		}
		  // EARRINGS_CATEGORY METAFIELD COMPARISON & UPSERT
			$recordEarMeta = $this->db->query(
				"SELECT meta_id, value FROM sh_product_metafield 
				WHERE product_id = $product_id AND meta_key = 'earrings_category'"
			)->result();

			$customEarringCategoryArr = json_decode($row['json_earrings_category'], true);

			if (!empty($recordEarMeta) && !empty($customEarringCategoryArr)) {
				$diffMeta = 0;

				if (!empty($recordEarMeta[0]->meta_id)) {
					$oldMeta = $recordEarMeta[0]->value ?: '[]';
					$newMeta = json_encode($customEarringCategoryArr);

					$diffMeta = strcmp($oldMeta, $newMeta);
				}

				if ($diffMeta != 0) {
					$this->db->update(
						'sh_product_metafield',
						[
							'value' => json_encode($customEarringCategoryArr),
							'to_update' => 1
						],
						['meta_id' => $recordEarMeta[0]->meta_id]
					);
				}

			} else if (empty($recordEarMeta) && !empty($customEarringCategoryArr)) {
				$metafieldInsertData = [
					'product_id' => $product_id,
					'namespace' => 'custom',
					'meta_key' => 'earrings_category',
					'value' => json_encode($customEarringCategoryArr),
					'type' => 'list.single_line_text_field',
					'to_create' => 1,
				];

				$this->db->insert('sh_product_metafield', $metafieldInsertData);
			}


		//  RING_CATEGORY METAFIELD COMPARISON & UPSERT
			$recordRingMeta = $this->db->query(
				"SELECT meta_id, value FROM sh_product_metafield 
				WHERE product_id = $product_id AND meta_key = 'ring_category'"
			)->result();

			$customRingCategoryArr = json_decode($row['json_ring_category'], true);

			if (!empty($recordRingMeta) && !empty($customRingCategoryArr)) {
				$diffMeta = 0;

				if (!empty($recordRingMeta[0]->meta_id)) {
					$oldMeta = $recordRingMeta[0]->value ?: '[]';
					$newMeta = json_encode($customRingCategoryArr);

					$diffMeta = strcmp($oldMeta, $newMeta);
				}

				if ($diffMeta != 0) {
					$this->db->update(
						'sh_product_metafield',
						['value' => json_encode($customRingCategoryArr), 'to_update' => 1],
						['meta_id' => $recordRingMeta[0]->meta_id]
					);
				}

			} else if (empty($recordRingMeta) && !empty($customRingCategoryArr)) {
				$metafieldInsertData = [
					'product_id' => $product_id,
					'namespace' => 'custom',
					'meta_key' => 'ring_category',
					'value' => json_encode($customRingCategoryArr),
					'type' => 'list.single_line_text_field',
					'to_create' => 1,
				];

				$this->db->insert('sh_product_metafield', $metafieldInsertData);
			}
		// -------------------------------------------------------------

		// custom metafield gemstone_details compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'gemstone_details'"

		)->result();



		$customMetafieldArr = json_decode($row['json_gemstone_details'], true);



		if(!empty($recordMeta) && !empty($customMetafieldArr)) {

			$diffMeta = 0;



			if(!empty($recordMeta[0]->meta_id)) {

				$oldMeta = $recordMeta[0]->value ?: '[]';

				$newMeta = json_encode($customMetafieldArr);



				$diffMeta = strcmp($oldMeta, $newMeta);

			}



			if($diffMeta != 0) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> json_encode($customMetafieldArr), 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($customMetafieldArr)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'gemstone_details',

				'value'=> json_encode($customMetafieldArr),

				'type'=> 'list.single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}



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

		// custom metafield jewelry_details compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'jewelry_details'"

		)->result();



		$customMetafieldArr = json_decode($row['json_jewelry_details'], true);



		if(!empty($recordMeta) && !empty($customMetafieldArr)) {

			$diffMeta = 0;



			if(!empty($recordMeta[0]->meta_id)) {

				$oldMeta = $recordMeta[0]->value ?: '[]';

				$newMeta = json_encode($customMetafieldArr);



				$diffMeta = strcmp($oldMeta, $newMeta);

			}



			if($diffMeta != 0) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> json_encode($customMetafieldArr), 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($customMetafieldArr)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'jewelry_details',

				'value'=> json_encode($customMetafieldArr),

				'type'=> 'list.single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}



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

		// custom metafield setcode compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'setcode'"

		)->result();



		$newSetcode = $row['setcode'];



		if(!empty($recordMeta) && !empty($newSetcode)) {

			

			if($recordMeta[0]->value != $newSetcode) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $newSetcode, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($newSetcode)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'setcode',

				'value'=> $newSetcode,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}

		

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

		// custom metafield jewelrybarcode compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'jewelrybarcode'"

		)->result();



		$newjewelrybarcode = $row['jewelrybarcode'];



		if(!empty($recordMeta) && !empty($newjewelrybarcode)) {

			

			if($recordMeta[0]->value != $newjewelrybarcode) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $newjewelrybarcode, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($newjewelrybarcode)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'jewelrybarcode',

				'value'=> $newjewelrybarcode,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}





		// -------------------------------------------------------------
		// custom metafield color_name compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'color_name'"

		)->result();
		
		$newColorName = $row['color_name'];



		if(!empty($recordMeta) && !empty($newColorName)) {

			

			if($recordMeta[0]->value != $newColorName) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $newColorName, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($newColorName)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'color_name',

				'value'=> $newColorName,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}

		

		// -------------------------------------------------------------
	// custom metafield stone_type compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'stone_type'"

		)->result();



		$newStoneType = $row['stone_type'];



		if(!empty($recordMeta) && !empty($newStoneType)) {

			

			if($recordMeta[0]->value != $newStoneType) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $newStoneType, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($newStoneType)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'stone_type',

				'value'=> $newStoneType,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}

		

		// -------------------------------------------------------------
    	// custom metafield colorstone_quantity compare 
    
    
    
    		$recordMeta = $this->db->query(
    
    			"SELECT meta_id, value FROM sh_product_metafield 
    
    			WHERE product_id = $product_id AND meta_key = 'colorstone_quantity'"
    
    		)->result();
    
    
    
    		$colorStoneQuantity = $row['colorstone_quantity'];
    
    
    
    		if(!empty($recordMeta) && !empty($colorStoneQuantity)) {
    
    			
    
    			if($recordMeta[0]->value != $colorStoneQuantity) {
    
    				$this->db->update(
    
    					'sh_product_metafield', 
    
    					['value'=> $colorStoneQuantity, 'to_update'=> 1 ],
    
    					['meta_id'=> $recordMeta[0]->meta_id]
    
    				);
    
    			}
    
    
    
    		} else if(empty($recordMeta) && !empty($colorStoneQuantity)) {
    
    			$metafieldInsertData = [
    
    				'product_id'=> $product_id,
    
    				'namespace'=> 'custom',
    
    				'meta_key'=> 'colorstone_quantity',
    
    				'value'=> $colorStoneQuantity,
    
    				'type'=> 'single_line_text_field',					
    
    				'to_create'=> 1,
    
    			];
    
    			$this->db->insert('sh_product_metafield', $metafieldInsertData);
    
    		}
    
    		
    
// -------------------------------------------------------------
// custom metafield metalname compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'metalname'"

		)->result();



		$colorMetalName = $row['metalname'];



		if(!empty($recordMeta) && !empty($colorMetalName)) {

			

			if($recordMeta[0]->value != $colorMetalName) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $colorMetalName, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($colorMetalName)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'metalname',

				'value'=> $colorMetalName,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}
		
// -------------------------------------------------------------
// custom metafield custom.stoneclarity compare 



		$recordstoneclarity = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'stoneclarity'"

		)->result();



		$stoneclarity = $row['stoneclarity'];



		if(!empty($recordstoneclarity) && !empty($stoneclarity)) {

			

			if($recordstoneclarity[0]->value != $stoneclarity) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $stoneclarity, 'to_update'=> 1 ],

					['meta_id'=> $recordstoneclarity[0]->meta_id]

				);

			}



		} else if(empty($recordstoneclarity) && !empty($stoneclarity)) {

			$metafieldInsertData1 = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'stoneclarity',

				'value'=> $stoneclarity,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData1);

		}
// custom metafield metal_id compare 



		$recordMetaid = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'metal_id'"

		)->result();



		$colormetal_id = $row['metal_id'];



		if(!empty($recordMetaid) && !empty($colormetal_id)) {

			

			if($recordMetaid[0]->value != $colormetal_id) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $colormetal_id, 'to_update'=> 1 ],

					['meta_id'=> $recordMetaid[0]->meta_id]

				);

			}



		} else if(empty($recordMetaid) && !empty($colormetal_id)) {

			$metafieldInsertDataid = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'metal_id',

				'value'=> $colormetal_id,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertDataid);

		}

		

		// -------------------------------------------------------------
        // custom metafield metalshortname compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'metalshortname'"

		)->result();



		$Metalshortname = $row['metalshortname'];



		if(!empty($recordMeta) && !empty($Metalshortname)) {

			

			if($recordMeta[0]->value != $Metalshortname) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $Metalshortname, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($Metalshortname)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'metalshortname',

				'value'=> $Metalshortname,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}

    // -------------------------------------------------------------
    // custom metafield diamond_quantity compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_product_metafield 

			WHERE product_id = $product_id AND meta_key = 'diamond_quantity'"

		)->result();



		$DiamondQuantity = $row['diamond_quantity'];



		if(!empty($recordMeta) && !empty($DiamondQuantity)) {

			

			if($recordMeta[0]->value != $DiamondQuantity) {

				$this->db->update(

					'sh_product_metafield', 

					['value'=> $DiamondQuantity, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($DiamondQuantity)) {

			$metafieldInsertData = [

				'product_id'=> $product_id,

				'namespace'=> 'custom',

				'meta_key'=> 'diamond_quantity',

				'value'=> $DiamondQuantity,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_product_metafield', $metafieldInsertData);

		}

	// -------------------------------------------------------------
     // custom metafield ocassion compare 



	$recordMeta = $this->db->query(

		"SELECT meta_id, value FROM sh_product_metafield 

		WHERE product_id = $product_id AND meta_key = 'ocassion'"

	)->result();



	$ocassion = $row['ocassion'];



	if(!empty($recordMeta) && !empty($ocassion)) {

		

		if($recordMeta[0]->value != $ocassion) {

			$this->db->update(

				'sh_product_metafield', 

				['value'=> $ocassion, 'to_update'=> 1 ],

				['meta_id'=> $recordMeta[0]->meta_id]

			);

		}



	} else if(empty($recordMeta) && !empty($ocassion)) {

		$metafieldInsertData = [

			'product_id'=> $product_id,

			'namespace'=> 'custom',

			'meta_key'=> 'ocassion',

			'value'=> $ocassion,

			'type'=> 'single_line_text_field',					

			'to_create'=> 1,

		];

		$this->db->insert('sh_product_metafield', $metafieldInsertData);

	}

	// -------------------------------------------------------------
     // custom metafield setting_style compare 



	$recordMeta = $this->db->query(

		"SELECT meta_id, value FROM sh_product_metafield 

		WHERE product_id = $product_id AND meta_key = 'setting_style'"

	)->result();



	$SettingStyle = $row['setting_style'];



	if(!empty($recordMeta) && !empty($SettingStyle)) {

		

		if($recordMeta[0]->value != $SettingStyle) {

			$this->db->update(

				'sh_product_metafield', 

				['value'=> $SettingStyle, 'to_update'=> 1 ],

				['meta_id'=> $recordMeta[0]->meta_id]

			);

		}



	} else if(empty($recordMeta) && !empty($SettingStyle)) {

		$metafieldInsertData = [

			'product_id'=> $product_id,

			'namespace'=> 'custom',

			'meta_key'=> 'setting_style',

			'value'=> $SettingStyle,

			'type'=> 'single_line_text_field',					

			'to_create'=> 1,

		];

		$this->db->insert('sh_product_metafield', $metafieldInsertData);

	}

	// -------------------------------------------------------------
	
	    // custom metafield ring_size compare 


    if($row['ebay_type'] == 'Rings'){
    	$recordMeta = $this->db->query(
    
    		"SELECT meta_id, value FROM sh_product_metafield 
    
    		WHERE product_id = $product_id AND meta_key = 'ring_size'"
    
    	)->result();
    
    
    
    	$RingSize = $row['ring_size'];
    
    
    
    	if(!empty($recordMeta) && !empty($RingSize)) {
    
    		
    
    		if($recordMeta[0]->value != $RingSize) {
    
    			$this->db->update(
    
    				'sh_product_metafield', 
    
    				['value'=> $RingSize, 'to_update'=> 1 ],
    
    				['meta_id'=> $recordMeta[0]->meta_id]
    
    			);
    
    		}
    
    
    
    	} else if(empty($recordMeta) && !empty($RingSize)) {
    
    		$metafieldInsertData = [
    
    			'product_id'=> $product_id,
    
    			'namespace'=> 'custom',
    
    			'meta_key'=> 'ring_size',
    
    			'value'=> $RingSize,
    
    			'type'=> 'single_line_text_field',					
    
    			'to_create'=> 1,
    
    		];
    
    		$this->db->insert('sh_product_metafield', $metafieldInsertData);
    
    	}
    }
	// -------------------------------------------------------------  
	 
	 // custom metafield necklace_length compare 


     if ($row['ebay_type'] == 'Necklaces' && $row['ring_size'] != 'S7') {
    	$recordMeta = $this->db->query(
    
    		"SELECT meta_id, value FROM sh_product_metafield 
    
    		WHERE product_id = $product_id AND meta_key = 'necklace_length'"
    
    	)->result();
    
    
    
    	$necklace_length = $row['ring_size'];
    
    
    
    	if(!empty($recordMeta) && !empty($necklace_length)) {
    
    		
    
    		if($recordMeta[0]->value != $necklace_length) {
    
    			$this->db->update(
    
    				'sh_product_metafield', 
    
    				['value'=> $necklace_length, 'to_update'=> 1 ],
    
    				['meta_id'=> $recordMeta[0]->meta_id]
    
    			);
    
    		}
    
    
    
    	} else if(empty($recordMeta) && !empty($necklace_length)) {
    
    		$metafieldInsertData = [
    
    			'product_id'=> $product_id,
    
    			'namespace'=> 'custom',
    
    			'meta_key'=> 'necklace_length',
    
    			'value'=> $necklace_length,
    
    			'type'=> 'single_line_text_field',					
    
    			'to_create'=> 1,
    
    		];
    
    		$this->db->insert('sh_product_metafield', $metafieldInsertData);
    
    	}
    }
	    // custom metafield total_carat_weight compare 



	$recordMeta = $this->db->query(

		"SELECT meta_id, value FROM sh_product_metafield 

		WHERE product_id = $product_id AND meta_key = 'total_carat_weight'"

	)->result();



	$totalcaratweight = $row['total_carat_weight'];



	if(!empty($recordMeta) && !empty($totalcaratweight)) {

		

		if($recordMeta[0]->value != $totalcaratweight) {

			$this->db->update(

				'sh_product_metafield', 

				['value'=> $totalcaratweight, 'to_update'=> 1 ],

				['meta_id'=> $recordMeta[0]->meta_id]

			);

		}



	} else if(empty($recordMeta) && !empty($totalcaratweight)) {

		$metafieldInsertData = [

			'product_id'=> $product_id,

			'namespace'=> 'custom',

			'meta_key'=> 'total_carat_weight',

			'value'=> $totalcaratweight,

			'type'=> 'single_line_text_field',					

			'to_create'=> 1,

		];

		$this->db->insert('sh_product_metafield', $metafieldInsertData);

	}

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

	    // custom metafield ebay_type compare 



	$recordMeta = $this->db->query(

		"SELECT meta_id, value FROM sh_product_metafield 

		WHERE product_id = $product_id AND meta_key = 'ebay_type'"

	)->result();



	$ebaytype = $row['ebay_type'];



	if(!empty($recordMeta) && !empty($ebaytype)) {

		

		if($recordMeta[0]->value != $ebaytype) {

			$this->db->update(

				'sh_product_metafield', 

				['value'=> $ebaytype, 'to_update'=> 1 ],

				['meta_id'=> $recordMeta[0]->meta_id]

			);

		}



	} else if(empty($recordMeta) && !empty($ebaytype)) {

		$metafieldInsertData = [

			'product_id'=> $product_id,

			'namespace'=> 'custom',

			'meta_key'=> 'ebay_type',

			'value'=> $ebaytype,

			'type'=> 'single_line_text_field',					

			'to_create'=> 1,

		];

		$this->db->insert('sh_product_metafield', $metafieldInsertData);

	}

	// ------------------------------------------------------------- 
		    // custom metafield country_of_origin compare 



	$recordMeta = $this->db->query(

		"SELECT meta_id, value FROM sh_product_metafield 

		WHERE product_id = $product_id AND meta_key = 'country_of_origin'"

	)->result();


    // Set and modify the country_of_origin
    $row['country_of_origin'] = str_replace('A', '', 'USA');

	$country_of_origin = $row['country_of_origin'];



	if(!empty($recordMeta) && !empty($country_of_origin)) {

		

		if($recordMeta[0]->value != $country_of_origin) {

			$this->db->update(

				'sh_product_metafield', 

				['value'=> $country_of_origin, 'to_update'=> 1 ],

				['meta_id'=> $recordMeta[0]->meta_id]

			);

		}



	} else if(empty($recordMeta) && !empty($country_of_origin)) {

		$metafieldInsertData = [

			'product_id'=> $product_id,

			'namespace'=> 'custom',

			'meta_key'=> 'country_of_origin',

			'value'=> $country_of_origin,

			'type'=> 'single_line_text_field',					

			'to_create'=> 1,

		];

		$this->db->insert('sh_product_metafield', $metafieldInsertData);

	}

	// -------------------------------------------------------------  
	   // custom metafield unit_quantity compare 



// 	$recordMeta = $this->db->query(

// 		"SELECT meta_id, value FROM sh_product_metafield 

// 		WHERE product_id = $product_id AND meta_key = 'unit_quantity'"

// 	)->result();



// 	$unit_quantity = $row['unit_quantity'];



// 	if(!empty($recordMeta) && !empty($unit_quantity)) {

		

// 		if($recordMeta[0]->value != $unit_quantity) {

// 			$this->db->update(

// 				'sh_product_metafield', 

// 				['value'=> $unit_quantity, 'to_update'=> 1 ],

// 				['meta_id'=> $recordMeta[0]->meta_id]

// 			);

// 		}



// 	} else if(empty($recordMeta) && !empty($unit_quantity)) {

// 		$metafieldInsertData = [

// 			'product_id'=> $product_id,

// 			'namespace'=> 'custom',

// 			'meta_key'=> 'unit_quantity',

// 			'value'=> $unit_quantity,

// 			'type'=> 'single_line_text_field',					

// 			'to_create'=> 1,

// 		];

// 		$this->db->insert('sh_product_metafield', $metafieldInsertData);

// 	}
$unit_quantity = $row['unit_quantity'];
$unit_quantity -= 2;  

$recordMeta = $this->db->query(
    "SELECT meta_id, value FROM sh_product_metafield 
     WHERE product_id = $product_id AND meta_key = 'unit_quantity'"
)->result();

if (!empty($recordMeta) && !empty($unit_quantity)) {
    if ($recordMeta[0]->value != $unit_quantity) {
        $this->db->update(
            'sh_product_metafield', 
            ['value' => $unit_quantity, 'to_update' => 1],
            ['meta_id' => $recordMeta[0]->meta_id]
        );
    }
} else if (empty($recordMeta) && !empty($unit_quantity)) {
    $metafieldInsertData = [
        'product_id' => $product_id,
        'namespace' => 'custom',
        'meta_key' => 'unit_quantity',
        'value' => $unit_quantity,
        'type' => 'single_line_text_field',
        'to_create' => 1,
    ];
    $this->db->insert('sh_product_metafield', $metafieldInsertData);
}

	// ------------------------------------------------------------- 
	   // custom metafield colorstone compare 



	$recordMeta = $this->db->query(

		"SELECT meta_id, value FROM sh_product_metafield 

		WHERE product_id = $product_id AND meta_key = 'colorstone'"

	)->result();



	$colorstone = $row['colorstone'];



	if(!empty($recordMeta) && !empty($colorstone)) {

		

		if($recordMeta[0]->value != $colorstone) {

			$this->db->update(

				'sh_product_metafield', 

				['value'=> $colorstone, 'to_update'=> 1 ],

				['meta_id'=> $recordMeta[0]->meta_id]

			);

		}



	} else if(empty($recordMeta) && !empty($colorstone)) {

		$metafieldInsertData = [

			'product_id'=> $product_id,

			'namespace'=> 'custom',

			'meta_key'=> 'colorstone',

			'value'=> $colorstone,

			'type'=> 'single_line_text_field',					

			'to_create'=> 1,

		];

		$this->db->insert('sh_product_metafield', $metafieldInsertData);

	}

	// ------------------------------------------------------------- 
		$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_website_stonetype = $rv['website_stonetype'] ?? '';
        	$record = $this->db->query(

				"SELECT id, variant_id, product_id FROM sh_product_variant 

				WHERE sku LIKE '%$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']);

			if(!empty($variant_id)) {

				$variant_data[] = [

					'variant_id'=> $variant_id,

					'custom_size'=> $custom_size,

					'custom_metal_v'=> $custom_metal_v,
					
					'custom_website_stonetype'=> $custom_website_stonetype,

					...$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_website_stonetype'=> $custom_website_stonetype,   
				//	'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)	{

		if(empty($variant_id)) {

			return;

		}	
		
			$recordVstonetype = $this->db->query(

			"SELECT meta_id, value FROM sh_variant_metafield 

			WHERE variant_id = $variant_id AND meta_key = 'website_stonetype'"

		)->result();

		$newwebsite_stonetype = $record['website_stonetype'];

		if(!empty($recordVstonetype) && !empty($newwebsite_stonetype)) {



			if($recordVstonetype[0]->value != $newwebsite_stonetype) {
                // echo 'update=>'.$recordVstonetype[0]->meta_id ."=>". $newwebsite_stonetype;
				$this->db->update(

					'sh_variant_metafield', 

					['value'=> $newwebsite_stonetype, 'to_update'=> 1 ],

					['meta_id'=> $recordVstonetype[0]->meta_id]

				);

			}



		} else if(empty($recordVstonetype) && !empty($newwebsite_stonetype)) {

			$recordVstonetypeData = [

				'variant_id'=> $variant_id,

				'namespace'=> 'custom',

				'meta_key'=> 'website_stonetype',

				'value'=> $newwebsite_stonetype,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];
            // print_pre($recordVstonetypeData);
         
			$this->db->insert('sh_variant_metafield', $recordVstonetypeData);
            // echo $this->db->last_query();
		}
        // -------------------------------------------------------------

		// custom metafield metal_v compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_variant_metafield 

			WHERE variant_id = $variant_id AND meta_key = 'metal_v'"

		)->result();



		$newMetalV = $record['metal_v'];



		if(!empty($recordMeta) && !empty($newMetalV)) {



			if($recordMeta[0]->value != $newMetalV) {
                
				$this->db->update(

					'sh_variant_metafield', 

					['value'=> $newMetalV, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($newMetalV)) {

			$metafieldInsertData = [

				'variant_id'=> $variant_id,

				'namespace'=> 'custom',

				'meta_key'=> 'metal_v',

				'value'=> $newMetalV,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_variant_metafield', $metafieldInsertData);

		}



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

		// custom metafield sizing compare 



		$recordMeta = $this->db->query(

			"SELECT meta_id, value FROM sh_variant_metafield 

			WHERE variant_id = $variant_id AND meta_key = 'sizing'"

		)->result();



		$newSize = $record['size'];



		if(!empty($recordMeta) && !empty($newSize)) {



			if($recordMeta[0]->value != $newSize) {

				$this->db->update(

					'sh_variant_metafield', 

					['value'=> $newSize, 'to_update'=> 1 ],

					['meta_id'=> $recordMeta[0]->meta_id]

				);

			}



		} else if(empty($recordMeta) && !empty($newSize)) {

			$metafieldInsertData = [

				'variant_id'=> $variant_id,

				'namespace'=> 'custom',

				'meta_key'=> 'sizing',

				'value'=> $newSize,

				'type'=> 'single_line_text_field',					

				'to_create'=> 1,

			];

			$this->db->insert('sh_variant_metafield', $metafieldInsertData);

		}
		}


//  private function splitJewelryByStoneType($jewelryRecords) {
//     $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) {
//         if (!isset($item['variants']) || empty($item['variants'])) {
//             $result[] = $item;
//             continue;
//         }

//         $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] ?? '';

//             // If no image for this metal, set inventory_quantity = 0
//             $hasImage = isset($imagesByMetal[$metalName]) && !empty($imagesByMetal[$metalName]);
//             if (!$hasImage) {
//                 $variant['inventory_quantity'] = 0;
//             }

//             $stonetype = isset($variant['website_stonetype']) ? trim($variant['website_stonetype']) : '';

//             if ($stonetype === 'Lab Grown') {
//                 $labGrownVariants[] = $variant;
//             } elseif ($stonetype === '' || $stonetype === null || $stonetype === 'Natural Diamond') {
//                 $naturalDiamondVariants[] = $variant;
//             }
//         }

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

//         // Create Natural Diamond record
//         if (!empty($naturalDiamondVariants)) {
//             $naturalItem = $item;
//             $naturalItem['variants'] = $naturalDiamondVariants;
//             $naturalItem['stone_type'] = 'Natural Diamond';
//             // Keep original SKU/setcode
//             $result[] = $naturalItem;
//         }
//     }

//     return $result;
// }
private function splitJewelryByStoneType($jewelryRecords) {
    $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) {
        if (!isset($item['variants']) || empty($item['variants'])) {
            $result[] = $item;
            continue;
        }

        $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] ?? '';

            // 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);

            // Lab Grown check
            if (strpos($stonetype, 'lab grown') !== false) {
                $labGrownVariants[] = $variant;
            }
            // Natural Diamond (including variants with GemStn/Gemstone)
            elseif (
                $stonetype === '' ||
                $stonetype === 'diamond' ||
                preg_match('/^natural diamond\s*(\/\s*(gemstn|gemstone))?$/i', $stonetypeRaw)
            ) {
                $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';
            $labItem['stone_type'] = 'Lab Grown';
            $result[] = $labItem;
        }

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

    return $result;
}


/*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);
	}


}