<?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($row['jewelrybarcode'] == "451334"){
        // 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['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'];
    //         }
    //     }
            $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'],
			'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 = [];
//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'],
            '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));



		$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'],
        '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.";
}



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

			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,

					...$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_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;

// 		}	
		
// 			$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 processVariantMetafield($record, $variant_id) {
    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',
    ];

    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;
    }

    $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'       => 'single_line_text_field',
            'to_create'  => 1,
        ];
        $this->db->insert('sh_variant_metafield', $insertData);
    }
}


private function splitJewelryByStoneType($jewelryRecords) {
     // $currentDateTime = date('Y-m-d H:i:s');
    //  echo $currentDateTime;
	 //   die;
    $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] ?? '';
           // 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';
            $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;
            $naturalItem['stone_type'] = 'Natural Diamond';
            $result[] = $naturalItem;
        }
    }

    return $result;
}
private function upsertMetafield($product_id, $meta_key, $newValue, $type = 'single_line_text_field', $isJson = false)
{ 
    // print_pre($newValue);
   
    // 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("sh_product_metafield", [
                "product_id" => $product_id,
                "meta_key"   => $meta_key
            ]);

            if ($existingRecord) {
                // if ($existingRecord['value'] === $newValue) {
                //         return; 
                //     }

                $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("sh_product_metafield", [
                "product_id" => $product_id,
                "meta_key"   => $meta_key
            ]);

            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("sh_product_metafield", [
        "product_id" => $product_id,
        "meta_key"   => $meta_key
    ]);

    if ($this->db->affected_rows() == 0) {
        // Record exists, update it
        $updated = $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);
	}


}