<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use Illuminate\Pagination\Paginator;
use GuzzleHttp\Client;
use GuzzleHttp\Exception\ClientException;
use Illuminate\Support\Facades\Auth;
use Tymon\JWTAuth\Facades\JWTAuth;
use App\Models\User;
use App\Models\Shopify_theme_changes;
use Illuminate\Support\Facades\View;
use App\Models\Diamond;
class JewelryController extends Controller
{
    
    public function index(Request $request)
    {
      echo "Welcome to my app.";
      
    }
    
public function load_more_data(Request $request)
{
    
 // Get pagination parameters from the request with default values
        $perPage = $request->input('per_page', 10); // Default: 10 products per page
        $page = $request->input('page', 1); // Default: First page
		  $shop_name = $request->shop_name; 
      $user_query = "select id from users where name = '$shop_name'  limit 1";
      $userid = DB::select($user_query);
      $user_ids = $userid[0]->id;
	    $limit = 50;

      $cpn_id = (int)$request->input('cpn_id');
      $CatString = $request->input('CatString');
      $sr_no = (int)$request->input('sr_no');
      $action = $request->input('action');
      $shape = $request->input('shape');	
      $style_category = $request->input('style_category');	
      $metal_type = $request->input('metal_type');	
      $metal_type = $request->input('MetalType');
      $metal_color = $request->input('metal_color');
      $dia_type = $request->input('dia_type');	      
      $from_price = $request->input('from_price');	
      $to_price = $request->input('to_price');	
      $from_carat = $request->input('from_carat');	
      $to_carat = $request->input('to_carat');	
      $FromCenterDiamond = $request->input('FromCenterDiamond');	
      $ToCenterDiamond = $request->input('ToCenterDiamond');	
      $MatchedSetsVal = $request->input('MatchedSetsVal');	
      $QualityGroupStr = $request->input('QualityGroupStr');	
      $ColorGroupStr = $request->input('ColorGroupStr');
      $ClarityGroupStr = $request->input('ClarityGroupStr');
      $ManufacturerID = (int)$request->input('ManufacturerID');
      $HtmlPage = $request->input('HtmlPage');	
      $DiamondFilter = $request->input('DiamondFilter');	
      $CenterStoneVal = $request->input('dia_type');
      $ShipsByVal = $request->input('ShipsByVal');
      $choose_option = $request->input('choose_option');
      $StyleNumber = $request->input('StyleNumber');

	  $WhereQuery = '';

    if($CenterStoneVal != '' )
    {
          
      
        $WhereQuery .= " AND pm.center_stone_type_id = '".$CenterStoneVal."'";
     
    }

    //Metal Type
$LeftTableStatus = 'No';
if($metal_type != '' )
{
  $TempQueryString = "";
  
  
  $TempProductAttriArray = @explode(',',$metal_type);
  $TempQueryString = "";
  if (is_array($TempProductAttriArray) && !empty($TempProductAttriArray)) 
  {
    for($TempDI=0; $TempDI < sizeof($TempProductAttriArray); $TempDI++)
    {
      if((int)$TempProductAttriArray[$TempDI] != 0)
      {
        if($TempQueryString != '')
        {
          $TempQueryString .= " OR ";
        }			
        $TempQueryString .= " sptmt.sptmt_metal_type_id = '".$TempProductAttriArray[$TempDI]."'";
      }
    }
  }

  if($TempQueryString != '') 
  {
    $WhereQuery .= " AND (".$TempQueryString.")";
    $LeftTableStatus = 'Yes';
  }
  
  
}
        
       $theme_querys = "select * from shopify_theme_changes where user_id = 1  limit 1";
       $retaillerid = DB::select($theme_querys);
      
         // $products = DB::connection('mysql2')->table('amipiinc_db2017.shop_products') // Use full database name
		  //$diamond_query = "select * from amipiinc_db2017.shop_products  limit 10";
		  $diamond_query = "select product_promotion ,available_type , is_build_product , is_superdeals , is_new , products_id , products_price , products_blurb , products_name , products_style_no , products_seo_url , design_id, diamond_quality_id, diamond_weight_group_id, dwg_name, dqg_alias, delivery_days, dcst_name, center_stone_type_id, (select pi.image_url from shop_products_image as pi where a.products_id = pi.products_id and pi.image_type = 'image' order by pi.sort_order asc limit 1) as image_url, 'image' as image_type , products_vendor_path, vender_id, media_path from ( (select pm.product_promotion ,pm.available_type , pm.is_build_product , pm.is_superdeals , pm.is_new , pm.products_vendor_path , pm.products_id , if(c_markup_type=1,`products_price3`+COALESCE(c_markup_value,0),`products_price3`+((`products_price3`*COALESCE(c_markup_value,0))/100)) as products_price , pd.products_blurb , pd.products_name , products_style_no , products_seo_url , pm.sort_order, pm.design_id, pm.diamond_quality_id, pm.diamond_weight_group_id, dwg.dwg_name as dwg_name, dqg.dqg_alias as dqg_alias, pm.delivery_days, css.dcst_name, pm.center_stone_type_id, vender_id, vm.media_path from `shop_products` as pm left join client_markup cmark on cmark.retailerid = 500 and products_price3 between cmark.price_from and cmark.price_to and cmark.type = '3' left join vendor_master vm on vm.vendorid = pm.vender_id left join diamond_weight_group as dwg on dwg.dwg_id = pm.diamond_weight_group_id left join diamond_quality_group as dqg on dqg.dqg_id = pm.diamond_quality_id left join diamond_center_stone_type as css on pm.center_stone_type_id = css.dcst_id left join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id where 1 AND vm.vendor_status = 1 AND vm.deleted = 0 and product_image_status = 1 AND ( ptpg.ptpg_level = '1' ) AND products_quantity <> 0 AND ( ptc.categories_id = '6' OR ptc.categories_id = '74' OR ptc.categories_id = '89' OR ptc.categories_id = '4' OR ptc.categories_id = '85' OR ptc.categories_id = '3' OR ptc.categories_id = '90') AND products_status = 1 and ( ptc.categories_id = '1' or ptc.categories_id = '3' or ptc.categories_id = '4' or ptc.categories_id = '5' or ptc.categories_id = '6' or ptc.categories_id = '7' or ptc.categories_id = '8' or ptc.categories_id = '9' or ptc.categories_id = '10' or ptc.categories_id = '11' or ptc.categories_id = '67' or ptc.categories_id = '68' or ptc.categories_id = '69' or ptc.categories_id = '70' or ptc.categories_id = '71' or ptc.categories_id = '72' or ptc.categories_id = '73' or ptc.categories_id = '74' or ptc.categories_id = '77' or ptc.categories_id = '79' or ptc.categories_id = '83' or ptc.categories_id = '84' or ptc.categories_id = '85' or ptc.categories_id = '86' or ptc.categories_id = '87' or ptc.categories_id = '88' or ptc.categories_id = '89' or ptc.categories_id = '90' or ptc.categories_id = '91' ) AND ( pts.shape_id = '1' OR pts.shape_id = '2' OR pts.shape_id = '10' OR pts.shape_id = '6' OR pts.shape_id = '3' OR pts.shape_id = '5' OR pts.shape_id = '4' OR pts.shape_id = '7' OR pts.shape_id = '8' OR pts.shape_id = '9' OR pts.shape_id = '13' OR pts.shape_id = '12' OR pts.shape_id = '14' OR pts.shape_id = '15' OR pts.shape_id = '11') AND ( sptsc.sptsc_style_category_id = '34' OR sptsc.sptsc_style_category_id = '35' OR sptsc.sptsc_style_category_id = '70' OR sptsc.sptsc_style_category_id = '265' OR sptsc.sptsc_style_category_id = '168' OR sptsc.sptsc_style_category_id = '171' OR sptsc.sptsc_style_category_id = '172' OR sptsc.sptsc_style_category_id = '250' OR sptsc.sptsc_style_category_id = '252' OR sptsc.sptsc_style_category_id = '254' OR sptsc.sptsc_style_category_id = '255' OR sptsc.sptsc_style_category_id = '280' OR sptsc.sptsc_style_category_id = '902' OR sptsc.sptsc_style_category_id = '960' OR sptsc.sptsc_style_category_id = '961' OR sptsc.sptsc_style_category_id = '984' OR sptsc.sptsc_style_category_id = '985' OR sptsc.sptsc_style_category_id = '143' OR sptsc.sptsc_style_category_id = '145' OR sptsc.sptsc_style_category_id = '196' OR sptsc.sptsc_style_category_id = '147' OR sptsc.sptsc_style_category_id = '170' OR sptsc.sptsc_style_category_id = '224' OR sptsc.sptsc_style_category_id = '910' OR sptsc.sptsc_style_category_id = '946' OR sptsc.sptsc_style_category_id = '998' OR sptsc.sptsc_style_category_id = '990' OR sptsc.sptsc_style_category_id = '999' OR sptsc.sptsc_style_category_id = '1000' OR sptsc.sptsc_style_category_id = '1001' OR sptsc.sptsc_style_category_id = '1002' OR sptsc.sptsc_style_category_id = '1003' OR sptsc.sptsc_style_category_id = '962' OR sptsc.sptsc_style_category_id = '979' OR sptsc.sptsc_style_category_id = '981' OR sptsc.sptsc_style_category_id = '73' OR sptsc.sptsc_style_category_id = '174' OR sptsc.sptsc_style_category_id = '983' OR sptsc.sptsc_style_category_id = '218' OR sptsc.sptsc_style_category_id = '266' OR sptsc.sptsc_style_category_id = '241' OR sptsc.sptsc_style_category_id = '243' OR sptsc.sptsc_style_category_id = '245' OR sptsc.sptsc_style_category_id = '247' OR sptsc.sptsc_style_category_id = '276' OR sptsc.sptsc_style_category_id = '278' OR sptsc.sptsc_style_category_id = '279' OR sptsc.sptsc_style_category_id = '896' OR sptsc.sptsc_style_category_id = '914' OR sptsc.sptsc_style_category_id = '86' OR sptsc.sptsc_style_category_id = '88' OR sptsc.sptsc_style_category_id = '91' OR sptsc.sptsc_style_category_id = '952' OR sptsc.sptsc_style_category_id = '958' OR sptsc.sptsc_style_category_id = '959' OR sptsc.sptsc_style_category_id = '963' OR sptsc.sptsc_style_category_id = '964' OR sptsc.sptsc_style_category_id = '965' OR sptsc.sptsc_style_category_id = '966' OR sptsc.sptsc_style_category_id = '967' OR sptsc.sptsc_style_category_id = '968' OR sptsc.sptsc_style_category_id = '969' OR sptsc.sptsc_style_category_id = '970' OR sptsc.sptsc_style_category_id = '972' OR sptsc.sptsc_style_category_id = '974') AND ( sptmt.sptmt_metal_type_id = '10') AND ( sptmt.sptmt_metal_type_id = '4' OR sptmt.sptmt_metal_type_id = '8' OR sptmt.sptmt_metal_type_id = '10' OR sptmt.sptmt_metal_type_id = '11' OR sptmt.sptmt_metal_type_id = '12' OR sptmt.sptmt_metal_type_id = '13' OR sptmt.sptmt_metal_type_id = '14') AND ( pm.diamond_quality_id = '1' OR pm.diamond_quality_id = '62' OR pm.diamond_quality_id = '3' OR pm.diamond_quality_id = '57' OR pm.diamond_quality_id = '6' OR pm.diamond_quality_id = '63' OR pm.diamond_quality_id = '9' OR pm.diamond_quality_id = '64' OR pm.diamond_quality_id = '12' OR pm.diamond_quality_id = '58' OR pm.diamond_quality_id = '13' OR pm.diamond_quality_id = '14' OR pm.diamond_quality_id = '52' OR pm.diamond_quality_id = '54' OR pm.diamond_quality_id = '53' OR pm.diamond_quality_id = '55' OR pm.diamond_quality_id = '17' OR pm.diamond_quality_id = '50' OR pm.diamond_quality_id = '51' OR pm.diamond_quality_id = '44' OR pm.diamond_quality_id = '45' OR pm.diamond_quality_id = '19' OR pm.diamond_quality_id = '21' OR pm.diamond_quality_id = '22' OR pm.diamond_quality_id = '23' OR pm.diamond_quality_id = '46' OR pm.diamond_quality_id = '49' OR pm.diamond_quality_id = '41' OR pm.diamond_quality_id = '40' OR pm.diamond_quality_id = '42' OR pm.diamond_quality_id = '43' OR pm.diamond_quality_id = '47' OR pm.diamond_quality_id = '48' OR pm.diamond_quality_id = '66' OR pm.diamond_quality_id = '59' OR pm.diamond_quality_id = '60' OR pm.diamond_quality_id = '65' OR pm.diamond_quality_id = '61' OR pm.diamond_quality_id = '56') AND pm.center_stone_type_id = '3' AND ( pm.vender_id = '1' OR pm.vender_id = '6' OR pm.vender_id = '16') group by pm.design_id ORDER BY sort_order ASC ) union all (select pm.product_promotion ,pm.available_type , pm.is_build_product , pm.is_superdeals , pm.is_new , pm.products_vendor_path , pm.products_id , if(c_markup_type=1,`products_price3`+COALESCE(c_markup_value,0),`products_price3`+((`products_price3`*COALESCE(c_markup_value,0))/100)) as products_price , pd.products_blurb , pd.products_name , products_style_no , products_seo_url , pm.sort_order, pm.design_id, pm.diamond_quality_id, pm.diamond_weight_group_id, dwg.dwg_name as dwg_name, dqg.dqg_alias as dqg_alias, pm.delivery_days, css.dcst_name, pm.center_stone_type_id, vender_id, vm.media_path from `shop_products` as pm left join client_markup cmark on cmark.retailerid = 500 and products_price3 between cmark.price_from and cmark.price_to and cmark.type = '3' left join vendor_master vm on vm.vendorid = pm.vender_id left join diamond_weight_group as dwg on dwg.dwg_id = pm.diamond_weight_group_id left join diamond_quality_group as dqg on dqg.dqg_id = pm.diamond_quality_id left join diamond_center_stone_type as css on pm.center_stone_type_id = css.dcst_id left join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id where 1 AND vm.vendor_status = 1 AND vm.deleted = 0 and product_image_status = 1 AND ( ptpg.ptpg_level = '1' ) AND products_quantity <> 0 AND ( ptc.categories_id = '6' OR ptc.categories_id = '74' OR ptc.categories_id = '89' OR ptc.categories_id = '4' OR ptc.categories_id = '85' OR ptc.categories_id = '3' OR ptc.categories_id = '90') AND products_status = 1 and ( ptc.categories_id = '1' or ptc.categories_id = '3' or ptc.categories_id = '4' or ptc.categories_id = '5' or ptc.categories_id = '6' or ptc.categories_id = '7' or ptc.categories_id = '8' or ptc.categories_id = '9' or ptc.categories_id = '10' or ptc.categories_id = '11' or ptc.categories_id = '67' or ptc.categories_id = '68' or ptc.categories_id = '69' or ptc.categories_id = '70' or ptc.categories_id = '71' or ptc.categories_id = '72' or ptc.categories_id = '73' or ptc.categories_id = '74' or ptc.categories_id = '77' or ptc.categories_id = '79' or ptc.categories_id = '83' or ptc.categories_id = '84' or ptc.categories_id = '85' or ptc.categories_id = '86' or ptc.categories_id = '87' or ptc.categories_id = '88' or ptc.categories_id = '89' or ptc.categories_id = '90' or ptc.categories_id = '91' ) AND ( pts.shape_id = '1' OR pts.shape_id = '2' OR pts.shape_id = '10' OR pts.shape_id = '6' OR pts.shape_id = '3' OR pts.shape_id = '5' OR pts.shape_id = '4' OR pts.shape_id = '7' OR pts.shape_id = '8' OR pts.shape_id = '9' OR pts.shape_id = '13' OR pts.shape_id = '12' OR pts.shape_id = '14' OR pts.shape_id = '15' OR pts.shape_id = '11') AND ( sptsc.sptsc_style_category_id = '34' OR sptsc.sptsc_style_category_id = '35' OR sptsc.sptsc_style_category_id = '70' OR sptsc.sptsc_style_category_id = '265' OR sptsc.sptsc_style_category_id = '168' OR sptsc.sptsc_style_category_id = '171' OR sptsc.sptsc_style_category_id = '172' OR sptsc.sptsc_style_category_id = '250' OR sptsc.sptsc_style_category_id = '252' OR sptsc.sptsc_style_category_id = '254' OR sptsc.sptsc_style_category_id = '255' OR sptsc.sptsc_style_category_id = '280' OR sptsc.sptsc_style_category_id = '902' OR sptsc.sptsc_style_category_id = '960' OR sptsc.sptsc_style_category_id = '961' OR sptsc.sptsc_style_category_id = '984' OR sptsc.sptsc_style_category_id = '985' OR sptsc.sptsc_style_category_id = '143' OR sptsc.sptsc_style_category_id = '145' OR sptsc.sptsc_style_category_id = '196' OR sptsc.sptsc_style_category_id = '147' OR sptsc.sptsc_style_category_id = '170' OR sptsc.sptsc_style_category_id = '224' OR sptsc.sptsc_style_category_id = '910' OR sptsc.sptsc_style_category_id = '946' OR sptsc.sptsc_style_category_id = '998' OR sptsc.sptsc_style_category_id = '990' OR sptsc.sptsc_style_category_id = '999' OR sptsc.sptsc_style_category_id = '1000' OR sptsc.sptsc_style_category_id = '1001' OR sptsc.sptsc_style_category_id = '1002' OR sptsc.sptsc_style_category_id = '1003' OR sptsc.sptsc_style_category_id = '962' OR sptsc.sptsc_style_category_id = '979' OR sptsc.sptsc_style_category_id = '981' OR sptsc.sptsc_style_category_id = '73' OR sptsc.sptsc_style_category_id = '174' OR sptsc.sptsc_style_category_id = '983' OR sptsc.sptsc_style_category_id = '218' OR sptsc.sptsc_style_category_id = '266' OR sptsc.sptsc_style_category_id = '241' OR sptsc.sptsc_style_category_id = '243' OR sptsc.sptsc_style_category_id = '245' OR sptsc.sptsc_style_category_id = '247' OR sptsc.sptsc_style_category_id = '276' OR sptsc.sptsc_style_category_id = '278' OR sptsc.sptsc_style_category_id = '279' OR sptsc.sptsc_style_category_id = '896' OR sptsc.sptsc_style_category_id = '914' OR sptsc.sptsc_style_category_id = '86' OR sptsc.sptsc_style_category_id = '88' OR sptsc.sptsc_style_category_id = '91' OR sptsc.sptsc_style_category_id = '952' OR sptsc.sptsc_style_category_id = '958' OR sptsc.sptsc_style_category_id = '959' OR sptsc.sptsc_style_category_id = '963' OR sptsc.sptsc_style_category_id = '964' OR sptsc.sptsc_style_category_id = '965' OR sptsc.sptsc_style_category_id = '966' OR sptsc.sptsc_style_category_id = '967' OR sptsc.sptsc_style_category_id = '968' OR sptsc.sptsc_style_category_id = '969' OR sptsc.sptsc_style_category_id = '970' OR sptsc.sptsc_style_category_id = '972' OR sptsc.sptsc_style_category_id = '974') AND ( sptmt.sptmt_metal_type_id = '10') AND ( sptmt.sptmt_metal_type_id = '4' OR sptmt.sptmt_metal_type_id = '8' OR sptmt.sptmt_metal_type_id = '10' OR sptmt.sptmt_metal_type_id = '11' OR sptmt.sptmt_metal_type_id = '12' OR sptmt.sptmt_metal_type_id = '13' OR sptmt.sptmt_metal_type_id = '14') AND ( pm.diamond_quality_id = '1' OR pm.diamond_quality_id = '62' OR pm.diamond_quality_id = '3' OR pm.diamond_quality_id = '57' OR pm.diamond_quality_id = '6' OR pm.diamond_quality_id = '63' OR pm.diamond_quality_id = '9' OR pm.diamond_quality_id = '64' OR pm.diamond_quality_id = '12' OR pm.diamond_quality_id = '58' OR pm.diamond_quality_id = '13' OR pm.diamond_quality_id = '14' OR pm.diamond_quality_id = '52' OR pm.diamond_quality_id = '54' OR pm.diamond_quality_id = '53' OR pm.diamond_quality_id = '55' OR pm.diamond_quality_id = '17' OR pm.diamond_quality_id = '50' OR pm.diamond_quality_id = '51' OR pm.diamond_quality_id = '44' OR pm.diamond_quality_id = '45' OR pm.diamond_quality_id = '19' OR pm.diamond_quality_id = '21' OR pm.diamond_quality_id = '22' OR pm.diamond_quality_id = '23' OR pm.diamond_quality_id = '46' OR pm.diamond_quality_id = '49' OR pm.diamond_quality_id = '41' OR pm.diamond_quality_id = '40' OR pm.diamond_quality_id = '42' OR pm.diamond_quality_id = '43' OR pm.diamond_quality_id = '47' OR pm.diamond_quality_id = '48' OR pm.diamond_quality_id = '66' OR pm.diamond_quality_id = '59' OR pm.diamond_quality_id = '60' OR pm.diamond_quality_id = '65' OR pm.diamond_quality_id = '61' OR pm.diamond_quality_id = '56') AND pm.center_stone_type_id = '3' AND ( pm.vender_id = '1' OR pm.vender_id = '6' OR pm.vender_id = '16') and diamond_weight_group_id <> '' and diamond_weight_group_id <> 0 and diamond_quality_id <> '' and diamond_quality_id <> 0 group by pm.design_id ORDER BY sort_order ASC ) )as a group by products_id ORDER BY sort_order ASC LIMIT 0 , 150";
          $records = DB::connection('mysql2')->select($diamond_query); 
		 //echo '<pre />'; print_r($records); die(' aaaaa');
		  foreach($records as $rec){
			  		if(!empty($rec->image_url)){
					$rec->image_url = 'https://www.mydiamondsearch.com/ampvd/product_images/' .$rec->image_url;
					}else{
						
						$rec->image_url = 'https://cdn.shopify.com/s/files/1/0697/1742/6414/files/No_image.jpg';
						
						}

              $diamond_weight_query =   "select dwg.dwg_name as name , dwg.dwg_id as id, pm.products_id as products_id from `shop_products` as pm join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id join diamond_weight_group as dwg on dwg.dwg_id = pm.diamond_weight_group_id left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id left join vendor_master vm on vm.vendorid = pm.vender_id where diamond_weight_group_id != 0 and diamond_quality_id != 0 and pm.design_id = '".$rec->design_id."' and 1 AND vm.vendor_status = 1 AND vm.deleted = 0 and product_image_status = 1 AND ( ptpg.ptpg_level = '1' ) AND products_quantity <> 0 AND products_status = 1  group by diamond_weight_group_id having(count(diamond_weight_group_id) > 0) order by dwg_sort_order ASC";
            // $rec->diamond_weight = DB::connection('mysql2')->select($diamond_weight_query);



            $diamond_weights = DB::connection('mysql2')->select($diamond_weight_query);

            if (!empty($diamond_weights)) {
                $first_weight = $diamond_weights[0];
                $last_weight = end($diamond_weights); // Get the last entry in the sorted list
                $rec->diamond_weight_max = $last_weight->name; // or ->id if needed
                $rec->diamond_weight_min = $first_weight->name;
            } else {
                $rec->diamond_weight_max = null;
                $rec->diamond_weight_min = null;
            }
            $rec->diamond_weight = $diamond_weights;
  
            $diamond_quality_query =   " select dqg.dqg_alias as name , dqg.description as description , dqg.dqg_id as id, dqg.dqg_icon as dqg_icon, dcst.dcst_name as diamond_type from `shop_products` as pm join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id join diamond_quality_group as dqg on dqg.dqg_id = pm.diamond_quality_id left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id left join vendor_master vm on vm.vendorid = pm.vender_id join diamond_center_stone_type as dcst on dcst.dcst_id = pm.center_stone_type_id where diamond_quality_id != 0 and pm.design_id = '".$rec->design_id."' and 1 AND vm.vendor_status = 1 AND vm.deleted = 0  group by diamond_quality_id having(count(diamond_quality_id) > 0) order by dqg_sort_order ASC";
            $rec->diamond_quality = DB::connection('mysql2')->select($diamond_quality_query); 


            $diamond_min_price_qry = "
                          SELECT 
                              MIN(
                                  IF(c_markup_type = 1,
                                      products_price3 + COALESCE(c_markup_value, 0),
                                      products_price3 + ((products_price3 * COALESCE(c_markup_value, 0)) / 100)
                                  )
                              ) AS min_products_price
                          FROM shop_products AS pm
                          LEFT JOIN client_markup cmark 
                              ON cmark.retailerid = 500 
                              AND products_price3 BETWEEN cmark.price_from AND cmark.price_to 
                              AND cmark.type = '3'
                          LEFT JOIN vendor_master vm ON vm.vendorid = pm.vender_id
                          WHERE 
                              vm.vendor_status = 1 
                              AND vm.deleted = 0 
                              AND product_image_status = 1 
                              AND products_quantity <> 0 
                              AND products_status = 1 
                              AND pm.center_stone_type_id = '3' 
                              AND pm.vender_id IN (1, 6, 16)
                              AND diamond_weight_group_id <> '' 
                              AND diamond_weight_group_id <> 0 
                              AND diamond_quality_id <> '' 
                              AND diamond_quality_id <> 0 and pm.design_id = '".$rec->design_id."'";

            $min_products_price = DB::connection('mysql2')->select($diamond_min_price_qry);
            $rec->min_products_price = $min_products_price[0]->min_products_price;
		  }
		//->select('*')
		//->paginate($perPage, ['*'], 'page', $page);
        // Prepare response data 
		 $dia_type = $request->query('dia_type');
     $metalTypes = $request->query('MetalType'); 
     
		 $query1 = "select  COUNT(products_id) AS diamond_count, products_price , products_blurb , products_name , products_style_no , products_seo_url , design_id, diamond_quality_id, diamond_weight_group_id, dwg_name, dqg_alias, delivery_days, dcst_name, center_stone_type_id, (select pi.image_url from shop_products_image as pi where a.products_id = pi.products_id and pi.image_type = 'image' order by pi.sort_order asc limit 1) as image_url, 'image' as image_type , products_vendor_path, vender_id, media_path from ( (select pm.product_promotion ,pm.available_type , pm.is_build_product , pm.is_superdeals , pm.is_new , pm.products_vendor_path , pm.products_id , if(c_markup_type=1,`products_price3`+COALESCE(c_markup_value,0),`products_price3`+((`products_price3`*COALESCE(c_markup_value,0))/100)) as products_price , pd.products_blurb , pd.products_name , products_style_no , products_seo_url , pm.sort_order, pm.design_id, pm.diamond_quality_id, pm.diamond_weight_group_id, dwg.dwg_name as dwg_name, dqg.dqg_alias as dqg_alias, pm.delivery_days, css.dcst_name, pm.center_stone_type_id, vender_id, vm.media_path from `shop_products` as pm left join client_markup cmark on cmark.retailerid = 500 and products_price3 between cmark.price_from and cmark.price_to and cmark.type = '3' left join vendor_master vm on vm.vendorid = pm.vender_id left join diamond_weight_group as dwg on dwg.dwg_id = pm.diamond_weight_group_id left join diamond_quality_group as dqg on dqg.dqg_id = pm.diamond_quality_id left join diamond_center_stone_type as css on pm.center_stone_type_id = css.dcst_id left join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id where 1 AND vm.vendor_status = 1 AND vm.deleted = 0 and product_image_status = 1 AND ( ptpg.ptpg_level = '1' ) AND products_quantity <> 0 AND ( ptc.categories_id = '6' OR ptc.categories_id = '74' OR ptc.categories_id = '89' OR ptc.categories_id = '4' OR ptc.categories_id = '85' OR ptc.categories_id = '3' OR ptc.categories_id = '90') AND products_status = 1 and ( ptc.categories_id = '1' or ptc.categories_id = '3' or ptc.categories_id = '4' or ptc.categories_id = '5' or ptc.categories_id = '6' or ptc.categories_id = '7' or ptc.categories_id = '8' or ptc.categories_id = '9' or ptc.categories_id = '10' or ptc.categories_id = '11' or ptc.categories_id = '67' or ptc.categories_id = '68' or ptc.categories_id = '69' or ptc.categories_id = '70' or ptc.categories_id = '71' or ptc.categories_id = '72' or ptc.categories_id = '73' or ptc.categories_id = '74' or ptc.categories_id = '77' or ptc.categories_id = '79' or ptc.categories_id = '83' or ptc.categories_id = '84' or ptc.categories_id = '85' or ptc.categories_id = '86' or ptc.categories_id = '87' or ptc.categories_id = '88' or ptc.categories_id = '89' or ptc.categories_id = '90' or ptc.categories_id = '91' ) AND ( pts.shape_id = '1' OR pts.shape_id = '2' OR pts.shape_id = '10' OR pts.shape_id = '6' OR pts.shape_id = '3' OR pts.shape_id = '5' OR pts.shape_id = '4' OR pts.shape_id = '7' OR pts.shape_id = '8' OR pts.shape_id = '9' OR pts.shape_id = '13' OR pts.shape_id = '12' OR pts.shape_id = '14' OR pts.shape_id = '15' OR pts.shape_id = '11') AND ( sptsc.sptsc_style_category_id = '34' OR sptsc.sptsc_style_category_id = '35' OR sptsc.sptsc_style_category_id = '70' OR sptsc.sptsc_style_category_id = '265' OR sptsc.sptsc_style_category_id = '168' OR sptsc.sptsc_style_category_id = '171' OR sptsc.sptsc_style_category_id = '172' OR sptsc.sptsc_style_category_id = '250' OR sptsc.sptsc_style_category_id = '252' OR sptsc.sptsc_style_category_id = '254' OR sptsc.sptsc_style_category_id = '255' OR sptsc.sptsc_style_category_id = '280' OR sptsc.sptsc_style_category_id = '902' OR sptsc.sptsc_style_category_id = '960' OR sptsc.sptsc_style_category_id = '961' OR sptsc.sptsc_style_category_id = '984' OR sptsc.sptsc_style_category_id = '985' OR sptsc.sptsc_style_category_id = '143' OR sptsc.sptsc_style_category_id = '145' OR sptsc.sptsc_style_category_id = '196' OR sptsc.sptsc_style_category_id = '147' OR sptsc.sptsc_style_category_id = '170' OR sptsc.sptsc_style_category_id = '224' OR sptsc.sptsc_style_category_id = '910' OR sptsc.sptsc_style_category_id = '946' OR sptsc.sptsc_style_category_id = '998' OR sptsc.sptsc_style_category_id = '990' OR sptsc.sptsc_style_category_id = '999' OR sptsc.sptsc_style_category_id = '1000' OR sptsc.sptsc_style_category_id = '1001' OR sptsc.sptsc_style_category_id = '1002' OR sptsc.sptsc_style_category_id = '1003' OR sptsc.sptsc_style_category_id = '962' OR sptsc.sptsc_style_category_id = '979' OR sptsc.sptsc_style_category_id = '981' OR sptsc.sptsc_style_category_id = '73' OR sptsc.sptsc_style_category_id = '174' OR sptsc.sptsc_style_category_id = '983' OR sptsc.sptsc_style_category_id = '218' OR sptsc.sptsc_style_category_id = '266' OR sptsc.sptsc_style_category_id = '241' OR sptsc.sptsc_style_category_id = '243' OR sptsc.sptsc_style_category_id = '245' OR sptsc.sptsc_style_category_id = '247' OR sptsc.sptsc_style_category_id = '276' OR sptsc.sptsc_style_category_id = '278' OR sptsc.sptsc_style_category_id = '279' OR sptsc.sptsc_style_category_id = '896' OR sptsc.sptsc_style_category_id = '914' OR sptsc.sptsc_style_category_id = '86' OR sptsc.sptsc_style_category_id = '88' OR sptsc.sptsc_style_category_id = '91' OR sptsc.sptsc_style_category_id = '952' OR sptsc.sptsc_style_category_id = '958' OR sptsc.sptsc_style_category_id = '959' OR sptsc.sptsc_style_category_id = '963' OR sptsc.sptsc_style_category_id = '964' OR sptsc.sptsc_style_category_id = '965' OR sptsc.sptsc_style_category_id = '966' OR sptsc.sptsc_style_category_id = '967' OR sptsc.sptsc_style_category_id = '968' OR sptsc.sptsc_style_category_id = '969' OR sptsc.sptsc_style_category_id = '970' OR sptsc.sptsc_style_category_id = '972' OR sptsc.sptsc_style_category_id = '974') AND ( sptmt.sptmt_metal_type_id = '10') AND ( sptmt.sptmt_metal_type_id = '4' OR sptmt.sptmt_metal_type_id = '8' OR sptmt.sptmt_metal_type_id = '10' OR sptmt.sptmt_metal_type_id = '11' OR sptmt.sptmt_metal_type_id = '12' OR sptmt.sptmt_metal_type_id = '13' OR sptmt.sptmt_metal_type_id = '14') AND ( pm.diamond_quality_id = '1' OR pm.diamond_quality_id = '62' OR pm.diamond_quality_id = '3' OR pm.diamond_quality_id = '57' OR pm.diamond_quality_id = '6' OR pm.diamond_quality_id = '63' OR pm.diamond_quality_id = '9' OR pm.diamond_quality_id = '64' OR pm.diamond_quality_id = '12' OR pm.diamond_quality_id = '58' OR pm.diamond_quality_id = '13' OR pm.diamond_quality_id = '14' OR pm.diamond_quality_id = '52' OR pm.diamond_quality_id = '54' OR pm.diamond_quality_id = '53' OR pm.diamond_quality_id = '55' OR pm.diamond_quality_id = '17' OR pm.diamond_quality_id = '50' OR pm.diamond_quality_id = '51' OR pm.diamond_quality_id = '44' OR pm.diamond_quality_id = '45' OR pm.diamond_quality_id = '19' OR pm.diamond_quality_id = '21' OR pm.diamond_quality_id = '22' OR pm.diamond_quality_id = '23' OR pm.diamond_quality_id = '46' OR pm.diamond_quality_id = '49' OR pm.diamond_quality_id = '41' OR pm.diamond_quality_id = '40' OR pm.diamond_quality_id = '42' OR pm.diamond_quality_id = '43' OR pm.diamond_quality_id = '47' OR pm.diamond_quality_id = '48' OR pm.diamond_quality_id = '66' OR pm.diamond_quality_id = '59' OR pm.diamond_quality_id = '60' OR pm.diamond_quality_id = '65' OR pm.diamond_quality_id = '61' OR pm.diamond_quality_id = '56') AND pm.center_stone_type_id = '3' AND ( pm.vender_id = '1' OR pm.vender_id = '6' OR pm.vender_id = '16') group by pm.design_id ORDER BY sort_order ASC ) union all (select pm.product_promotion ,pm.available_type , pm.is_build_product , pm.is_superdeals , pm.is_new , pm.products_vendor_path , pm.products_id , if(c_markup_type=1,`products_price3`+COALESCE(c_markup_value,0),`products_price3`+((`products_price3`*COALESCE(c_markup_value,0))/100)) as products_price , pd.products_blurb , pd.products_name , products_style_no , products_seo_url , pm.sort_order, pm.design_id, pm.diamond_quality_id, pm.diamond_weight_group_id, dwg.dwg_name as dwg_name, dqg.dqg_alias as dqg_alias, pm.delivery_days, css.dcst_name, pm.center_stone_type_id, vender_id, vm.media_path from `shop_products` as pm left join client_markup cmark on cmark.retailerid = 500 and products_price3 between cmark.price_from and cmark.price_to and cmark.type = '3' left join vendor_master vm on vm.vendorid = pm.vender_id left join diamond_weight_group as dwg on dwg.dwg_id = pm.diamond_weight_group_id left join diamond_quality_group as dqg on dqg.dqg_id = pm.diamond_quality_id left join diamond_center_stone_type as css on pm.center_stone_type_id = css.dcst_id left join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id where 1 AND vm.vendor_status = 1 AND vm.deleted = 0 and product_image_status = 1 AND ( ptpg.ptpg_level = '1' ) AND products_quantity <> 0 AND ( ptc.categories_id = '6' OR ptc.categories_id = '74' OR ptc.categories_id = '89' OR ptc.categories_id = '4' OR ptc.categories_id = '85' OR ptc.categories_id = '3' OR ptc.categories_id = '90') AND products_status = 1 and ( ptc.categories_id = '1' or ptc.categories_id = '3' or ptc.categories_id = '4' or ptc.categories_id = '5' or ptc.categories_id = '6' or ptc.categories_id = '7' or ptc.categories_id = '8' or ptc.categories_id = '9' or ptc.categories_id = '10' or ptc.categories_id = '11' or ptc.categories_id = '67' or ptc.categories_id = '68' or ptc.categories_id = '69' or ptc.categories_id = '70' or ptc.categories_id = '71' or ptc.categories_id = '72' or ptc.categories_id = '73' or ptc.categories_id = '74' or ptc.categories_id = '77' or ptc.categories_id = '79' or ptc.categories_id = '83' or ptc.categories_id = '84' or ptc.categories_id = '85' or ptc.categories_id = '86' or ptc.categories_id = '87' or ptc.categories_id = '88' or ptc.categories_id = '89' or ptc.categories_id = '90' or ptc.categories_id = '91' ) AND ( pts.shape_id = '1' OR pts.shape_id = '2' OR pts.shape_id = '10' OR pts.shape_id = '6' OR pts.shape_id = '3' OR pts.shape_id = '5' OR pts.shape_id = '4' OR pts.shape_id = '7' OR pts.shape_id = '8' OR pts.shape_id = '9' OR pts.shape_id = '13' OR pts.shape_id = '12' OR pts.shape_id = '14' OR pts.shape_id = '15' OR pts.shape_id = '11') AND ( sptsc.sptsc_style_category_id = '34' OR sptsc.sptsc_style_category_id = '35' OR sptsc.sptsc_style_category_id = '70' OR sptsc.sptsc_style_category_id = '265' OR sptsc.sptsc_style_category_id = '168' OR sptsc.sptsc_style_category_id = '171' OR sptsc.sptsc_style_category_id = '172' OR sptsc.sptsc_style_category_id = '250' OR sptsc.sptsc_style_category_id = '252' OR sptsc.sptsc_style_category_id = '254' OR sptsc.sptsc_style_category_id = '255' OR sptsc.sptsc_style_category_id = '280' OR sptsc.sptsc_style_category_id = '902' OR sptsc.sptsc_style_category_id = '960' OR sptsc.sptsc_style_category_id = '961' OR sptsc.sptsc_style_category_id = '984' OR sptsc.sptsc_style_category_id = '985' OR sptsc.sptsc_style_category_id = '143' OR sptsc.sptsc_style_category_id = '145' OR sptsc.sptsc_style_category_id = '196' OR sptsc.sptsc_style_category_id = '147' OR sptsc.sptsc_style_category_id = '170' OR sptsc.sptsc_style_category_id = '224' OR sptsc.sptsc_style_category_id = '910' OR sptsc.sptsc_style_category_id = '946' OR sptsc.sptsc_style_category_id = '998' OR sptsc.sptsc_style_category_id = '990' OR sptsc.sptsc_style_category_id = '999' OR sptsc.sptsc_style_category_id = '1000' OR sptsc.sptsc_style_category_id = '1001' OR sptsc.sptsc_style_category_id = '1002' OR sptsc.sptsc_style_category_id = '1003' OR sptsc.sptsc_style_category_id = '962' OR sptsc.sptsc_style_category_id = '979' OR sptsc.sptsc_style_category_id = '981' OR sptsc.sptsc_style_category_id = '73' OR sptsc.sptsc_style_category_id = '174' OR sptsc.sptsc_style_category_id = '983' OR sptsc.sptsc_style_category_id = '218' OR sptsc.sptsc_style_category_id = '266' OR sptsc.sptsc_style_category_id = '241' OR sptsc.sptsc_style_category_id = '243' OR sptsc.sptsc_style_category_id = '245' OR sptsc.sptsc_style_category_id = '247' OR sptsc.sptsc_style_category_id = '276' OR sptsc.sptsc_style_category_id = '278' OR sptsc.sptsc_style_category_id = '279' OR sptsc.sptsc_style_category_id = '896' OR sptsc.sptsc_style_category_id = '914' OR sptsc.sptsc_style_category_id = '86' OR sptsc.sptsc_style_category_id = '88' OR sptsc.sptsc_style_category_id = '91' OR sptsc.sptsc_style_category_id = '952' OR sptsc.sptsc_style_category_id = '958' OR sptsc.sptsc_style_category_id = '959' OR sptsc.sptsc_style_category_id = '963' OR sptsc.sptsc_style_category_id = '964' OR sptsc.sptsc_style_category_id = '965' OR sptsc.sptsc_style_category_id = '966' OR sptsc.sptsc_style_category_id = '967' OR sptsc.sptsc_style_category_id = '968' OR sptsc.sptsc_style_category_id = '969' OR sptsc.sptsc_style_category_id = '970' OR sptsc.sptsc_style_category_id = '972' OR sptsc.sptsc_style_category_id = '974') AND ( sptmt.sptmt_metal_type_id = '10') AND ( sptmt.sptmt_metal_type_id = '4' OR sptmt.sptmt_metal_type_id = '8' OR sptmt.sptmt_metal_type_id = '10' OR sptmt.sptmt_metal_type_id = '11' OR sptmt.sptmt_metal_type_id = '12' OR sptmt.sptmt_metal_type_id = '13' OR sptmt.sptmt_metal_type_id = '14') AND ( pm.diamond_quality_id = '1' OR pm.diamond_quality_id = '62' OR pm.diamond_quality_id = '3' OR pm.diamond_quality_id = '57' OR pm.diamond_quality_id = '6' OR pm.diamond_quality_id = '63' OR pm.diamond_quality_id = '9' OR pm.diamond_quality_id = '64' OR pm.diamond_quality_id = '12' OR pm.diamond_quality_id = '58' OR pm.diamond_quality_id = '13' OR pm.diamond_quality_id = '14' OR pm.diamond_quality_id = '52' OR pm.diamond_quality_id = '54' OR pm.diamond_quality_id = '53' OR pm.diamond_quality_id = '55' OR pm.diamond_quality_id = '17' OR pm.diamond_quality_id = '50' OR pm.diamond_quality_id = '51' OR pm.diamond_quality_id = '44' OR pm.diamond_quality_id = '45' OR pm.diamond_quality_id = '19' OR pm.diamond_quality_id = '21' OR pm.diamond_quality_id = '22' OR pm.diamond_quality_id = '23' OR pm.diamond_quality_id = '46' OR pm.diamond_quality_id = '49' OR pm.diamond_quality_id = '41' OR pm.diamond_quality_id = '40' OR pm.diamond_quality_id = '42' OR pm.diamond_quality_id = '43' OR pm.diamond_quality_id = '47' OR pm.diamond_quality_id = '48' OR pm.diamond_quality_id = '66' OR pm.diamond_quality_id = '59' OR pm.diamond_quality_id = '60' OR pm.diamond_quality_id = '65' OR pm.diamond_quality_id = '61' OR pm.diamond_quality_id = '56') AND pm.center_stone_type_id = '3' AND ( pm.vender_id = '1' OR pm.vender_id = '6' OR pm.vender_id = '16') and diamond_weight_group_id <> '' and diamond_weight_group_id <> 0 and diamond_quality_id <> '' and diamond_quality_id <> 0 group by pm.design_id ORDER BY sort_order ASC ) )as a group by products_id ORDER BY sort_order ASC LIMIT 0 , 150"; 
  
          $records_count =  DB::connection('mysql2')->select($query1);
          $data['records_count'] =$records_count['0']->diamond_count;
         
            $total_records = $records_count[0]->diamond_count;
			$page_name = '';
        // Generate pagination links
         // $pagination = $this->generate_pagination($page, $total_records, $limit);
          $response_data = [
            'product_id' => $product_id ?? '',
            'variants_id' => $variants_id ?? '',
            'records' => $records,
            'page_link' => $pagination ?? '',
            'total_records' => $data['records_count'],
            'theme_json' => $theme_json ?? '',
            'isLogin' => 1,
            'page_name' => $page_name,
            'retaillerid' => $retaillerid[0],
            'metalTypespr' => $metalTypes,
            'dia_typepr' =>  $dia_type
        ];
        // Return JSON response with CORS headers
        return response()->json($response_data)
            ->header('Access-Control-Allow-Origin', '*')
            ->header('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS')
            ->header('Access-Control-Allow-Headers', 'Content-Type, Authorization');
    
}




public function jewelry_details(Request $request){
  // $user = User::where('name', $request->shop)->first();   
  $response_data = [
    'records'=>'$diamond_record',
     'shop_D' => 'dfsafasf' 
     
  ]; 
  // echo '<pre / >'; print_r($diamond_record);
  return response()->json($response_data)
                     ->header('Access-Control-Allow-Origin', '*')
                     ->header('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS')
                     ->header('Access-Control-Allow-Headers', 'Content-Type, Authorization'); die;
     
  $stock_id = $request->stock_id;
$retailer_id = 7; 
$dia_type = 'Natural Diamonds';
$pricequery = $this->diamond_price($retailer_id,$dia_type);		
$retailerPermsion = $this->retailer_permsion($retailer_id);

  $diamond_query = "select product_promotion ,available_type , is_build_product , is_superdeals , is_new , products_id , products_price , products_blurb , products_name , products_style_no , products_seo_url , design_id, diamond_quality_id, diamond_weight_group_id, dwg_name, dqg_alias, delivery_days, dcst_name, center_stone_type_id, (select pi.image_url from shop_products_image as pi where a.products_id = pi.products_id and pi.image_type = 'image' order by pi.sort_order asc limit 1) as image_url, 'image' as image_type , products_vendor_path, vender_id, media_path from ( (select pm.product_promotion ,pm.available_type , pm.is_build_product , pm.is_superdeals , pm.is_new , pm.products_vendor_path , pm.products_id , if(c_markup_type=1,`products_price3`+COALESCE(c_markup_value,0),`products_price3`+((`products_price3`*COALESCE(c_markup_value,0))/100)) as products_price , pd.products_blurb , pd.products_name , products_style_no , products_seo_url , pm.sort_order, pm.design_id, pm.diamond_quality_id, pm.diamond_weight_group_id, dwg.dwg_name as dwg_name, dqg.dqg_alias as dqg_alias, pm.delivery_days, css.dcst_name, pm.center_stone_type_id, vender_id, vm.media_path from `shop_products` as pm left join client_markup cmark on cmark.retailerid = 500 and products_price3 between cmark.price_from and cmark.price_to and cmark.type = '3' left join vendor_master vm on vm.vendorid = pm.vender_id left join diamond_weight_group as dwg on dwg.dwg_id = pm.diamond_weight_group_id left join diamond_quality_group as dqg on dqg.dqg_id = pm.diamond_quality_id left join diamond_center_stone_type as css on pm.center_stone_type_id = css.dcst_id left join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id where 1 AND vm.vendor_status = 1 AND vm.deleted = 0 and product_image_status = 1 AND ( ptpg.ptpg_level = '1' ) AND products_quantity <> 0 AND ( ptc.categories_id = '6' OR ptc.categories_id = '74' OR ptc.categories_id = '89' OR ptc.categories_id = '4' OR ptc.categories_id = '85' OR ptc.categories_id = '3' OR ptc.categories_id = '90') AND products_status = 1 and ( ptc.categories_id = '1' or ptc.categories_id = '3' or ptc.categories_id = '4' or ptc.categories_id = '5' or ptc.categories_id = '6' or ptc.categories_id = '7' or ptc.categories_id = '8' or ptc.categories_id = '9' or ptc.categories_id = '10' or ptc.categories_id = '11' or ptc.categories_id = '67' or ptc.categories_id = '68' or ptc.categories_id = '69' or ptc.categories_id = '70' or ptc.categories_id = '71' or ptc.categories_id = '72' or ptc.categories_id = '73' or ptc.categories_id = '74' or ptc.categories_id = '77' or ptc.categories_id = '79' or ptc.categories_id = '83' or ptc.categories_id = '84' or ptc.categories_id = '85' or ptc.categories_id = '86' or ptc.categories_id = '87' or ptc.categories_id = '88' or ptc.categories_id = '89' or ptc.categories_id = '90' or ptc.categories_id = '91' ) AND ( pts.shape_id = '1' OR pts.shape_id = '2' OR pts.shape_id = '10' OR pts.shape_id = '6' OR pts.shape_id = '3' OR pts.shape_id = '5' OR pts.shape_id = '4' OR pts.shape_id = '7' OR pts.shape_id = '8' OR pts.shape_id = '9' OR pts.shape_id = '13' OR pts.shape_id = '12' OR pts.shape_id = '14' OR pts.shape_id = '15' OR pts.shape_id = '11') AND ( sptsc.sptsc_style_category_id = '34' OR sptsc.sptsc_style_category_id = '35' OR sptsc.sptsc_style_category_id = '70' OR sptsc.sptsc_style_category_id = '265' OR sptsc.sptsc_style_category_id = '168' OR sptsc.sptsc_style_category_id = '171' OR sptsc.sptsc_style_category_id = '172' OR sptsc.sptsc_style_category_id = '250' OR sptsc.sptsc_style_category_id = '252' OR sptsc.sptsc_style_category_id = '254' OR sptsc.sptsc_style_category_id = '255' OR sptsc.sptsc_style_category_id = '280' OR sptsc.sptsc_style_category_id = '902' OR sptsc.sptsc_style_category_id = '960' OR sptsc.sptsc_style_category_id = '961' OR sptsc.sptsc_style_category_id = '984' OR sptsc.sptsc_style_category_id = '985' OR sptsc.sptsc_style_category_id = '143' OR sptsc.sptsc_style_category_id = '145' OR sptsc.sptsc_style_category_id = '196' OR sptsc.sptsc_style_category_id = '147' OR sptsc.sptsc_style_category_id = '170' OR sptsc.sptsc_style_category_id = '224' OR sptsc.sptsc_style_category_id = '910' OR sptsc.sptsc_style_category_id = '946' OR sptsc.sptsc_style_category_id = '998' OR sptsc.sptsc_style_category_id = '990' OR sptsc.sptsc_style_category_id = '999' OR sptsc.sptsc_style_category_id = '1000' OR sptsc.sptsc_style_category_id = '1001' OR sptsc.sptsc_style_category_id = '1002' OR sptsc.sptsc_style_category_id = '1003' OR sptsc.sptsc_style_category_id = '962' OR sptsc.sptsc_style_category_id = '979' OR sptsc.sptsc_style_category_id = '981' OR sptsc.sptsc_style_category_id = '73' OR sptsc.sptsc_style_category_id = '174' OR sptsc.sptsc_style_category_id = '983' OR sptsc.sptsc_style_category_id = '218' OR sptsc.sptsc_style_category_id = '266' OR sptsc.sptsc_style_category_id = '241' OR sptsc.sptsc_style_category_id = '243' OR sptsc.sptsc_style_category_id = '245' OR sptsc.sptsc_style_category_id = '247' OR sptsc.sptsc_style_category_id = '276' OR sptsc.sptsc_style_category_id = '278' OR sptsc.sptsc_style_category_id = '279' OR sptsc.sptsc_style_category_id = '896' OR sptsc.sptsc_style_category_id = '914' OR sptsc.sptsc_style_category_id = '86' OR sptsc.sptsc_style_category_id = '88' OR sptsc.sptsc_style_category_id = '91' OR sptsc.sptsc_style_category_id = '952' OR sptsc.sptsc_style_category_id = '958' OR sptsc.sptsc_style_category_id = '959' OR sptsc.sptsc_style_category_id = '963' OR sptsc.sptsc_style_category_id = '964' OR sptsc.sptsc_style_category_id = '965' OR sptsc.sptsc_style_category_id = '966' OR sptsc.sptsc_style_category_id = '967' OR sptsc.sptsc_style_category_id = '968' OR sptsc.sptsc_style_category_id = '969' OR sptsc.sptsc_style_category_id = '970' OR sptsc.sptsc_style_category_id = '972' OR sptsc.sptsc_style_category_id = '974') AND ( sptmt.sptmt_metal_type_id = '10') AND ( sptmt.sptmt_metal_type_id = '4' OR sptmt.sptmt_metal_type_id = '8' OR sptmt.sptmt_metal_type_id = '10' OR sptmt.sptmt_metal_type_id = '11' OR sptmt.sptmt_metal_type_id = '12' OR sptmt.sptmt_metal_type_id = '13' OR sptmt.sptmt_metal_type_id = '14') AND ( pm.diamond_quality_id = '1' OR pm.diamond_quality_id = '62' OR pm.diamond_quality_id = '3' OR pm.diamond_quality_id = '57' OR pm.diamond_quality_id = '6' OR pm.diamond_quality_id = '63' OR pm.diamond_quality_id = '9' OR pm.diamond_quality_id = '64' OR pm.diamond_quality_id = '12' OR pm.diamond_quality_id = '58' OR pm.diamond_quality_id = '13' OR pm.diamond_quality_id = '14' OR pm.diamond_quality_id = '52' OR pm.diamond_quality_id = '54' OR pm.diamond_quality_id = '53' OR pm.diamond_quality_id = '55' OR pm.diamond_quality_id = '17' OR pm.diamond_quality_id = '50' OR pm.diamond_quality_id = '51' OR pm.diamond_quality_id = '44' OR pm.diamond_quality_id = '45' OR pm.diamond_quality_id = '19' OR pm.diamond_quality_id = '21' OR pm.diamond_quality_id = '22' OR pm.diamond_quality_id = '23' OR pm.diamond_quality_id = '46' OR pm.diamond_quality_id = '49' OR pm.diamond_quality_id = '41' OR pm.diamond_quality_id = '40' OR pm.diamond_quality_id = '42' OR pm.diamond_quality_id = '43' OR pm.diamond_quality_id = '47' OR pm.diamond_quality_id = '48' OR pm.diamond_quality_id = '66' OR pm.diamond_quality_id = '59' OR pm.diamond_quality_id = '60' OR pm.diamond_quality_id = '65' OR pm.diamond_quality_id = '61' OR pm.diamond_quality_id = '56') AND pm.center_stone_type_id = '3' AND ( pm.vender_id = '1' OR pm.vender_id = '6' OR pm.vender_id = '16') group by pm.design_id ORDER BY sort_order ASC ) union all (select pm.product_promotion ,pm.available_type , pm.is_build_product , pm.is_superdeals , pm.is_new , pm.products_vendor_path , pm.products_id , if(c_markup_type=1,`products_price3`+COALESCE(c_markup_value,0),`products_price3`+((`products_price3`*COALESCE(c_markup_value,0))/100)) as products_price , pd.products_blurb , pd.products_name , products_style_no , products_seo_url , pm.sort_order, pm.design_id, pm.diamond_quality_id, pm.diamond_weight_group_id, dwg.dwg_name as dwg_name, dqg.dqg_alias as dqg_alias, pm.delivery_days, css.dcst_name, pm.center_stone_type_id, vender_id, vm.media_path from `shop_products` as pm left join client_markup cmark on cmark.retailerid = 500 and products_price3 between cmark.price_from and cmark.price_to and cmark.type = '3' left join vendor_master vm on vm.vendorid = pm.vender_id left join diamond_weight_group as dwg on dwg.dwg_id = pm.diamond_weight_group_id left join diamond_quality_group as dqg on dqg.dqg_id = pm.diamond_quality_id left join diamond_center_stone_type as css on pm.center_stone_type_id = css.dcst_id left join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id where 1 AND vm.vendor_status = 1 AND vm.deleted = 0 and product_image_status = 1 AND ( ptpg.ptpg_level = '1' ) AND products_quantity <> 0 AND ( ptc.categories_id = '6' OR ptc.categories_id = '74' OR ptc.categories_id = '89' OR ptc.categories_id = '4' OR ptc.categories_id = '85' OR ptc.categories_id = '3' OR ptc.categories_id = '90') AND products_status = 1 and ( ptc.categories_id = '1' or ptc.categories_id = '3' or ptc.categories_id = '4' or ptc.categories_id = '5' or ptc.categories_id = '6' or ptc.categories_id = '7' or ptc.categories_id = '8' or ptc.categories_id = '9' or ptc.categories_id = '10' or ptc.categories_id = '11' or ptc.categories_id = '67' or ptc.categories_id = '68' or ptc.categories_id = '69' or ptc.categories_id = '70' or ptc.categories_id = '71' or ptc.categories_id = '72' or ptc.categories_id = '73' or ptc.categories_id = '74' or ptc.categories_id = '77' or ptc.categories_id = '79' or ptc.categories_id = '83' or ptc.categories_id = '84' or ptc.categories_id = '85' or ptc.categories_id = '86' or ptc.categories_id = '87' or ptc.categories_id = '88' or ptc.categories_id = '89' or ptc.categories_id = '90' or ptc.categories_id = '91' ) AND ( pts.shape_id = '1' OR pts.shape_id = '2' OR pts.shape_id = '10' OR pts.shape_id = '6' OR pts.shape_id = '3' OR pts.shape_id = '5' OR pts.shape_id = '4' OR pts.shape_id = '7' OR pts.shape_id = '8' OR pts.shape_id = '9' OR pts.shape_id = '13' OR pts.shape_id = '12' OR pts.shape_id = '14' OR pts.shape_id = '15' OR pts.shape_id = '11') AND ( sptsc.sptsc_style_category_id = '34' OR sptsc.sptsc_style_category_id = '35' OR sptsc.sptsc_style_category_id = '70' OR sptsc.sptsc_style_category_id = '265' OR sptsc.sptsc_style_category_id = '168' OR sptsc.sptsc_style_category_id = '171' OR sptsc.sptsc_style_category_id = '172' OR sptsc.sptsc_style_category_id = '250' OR sptsc.sptsc_style_category_id = '252' OR sptsc.sptsc_style_category_id = '254' OR sptsc.sptsc_style_category_id = '255' OR sptsc.sptsc_style_category_id = '280' OR sptsc.sptsc_style_category_id = '902' OR sptsc.sptsc_style_category_id = '960' OR sptsc.sptsc_style_category_id = '961' OR sptsc.sptsc_style_category_id = '984' OR sptsc.sptsc_style_category_id = '985' OR sptsc.sptsc_style_category_id = '143' OR sptsc.sptsc_style_category_id = '145' OR sptsc.sptsc_style_category_id = '196' OR sptsc.sptsc_style_category_id = '147' OR sptsc.sptsc_style_category_id = '170' OR sptsc.sptsc_style_category_id = '224' OR sptsc.sptsc_style_category_id = '910' OR sptsc.sptsc_style_category_id = '946' OR sptsc.sptsc_style_category_id = '998' OR sptsc.sptsc_style_category_id = '990' OR sptsc.sptsc_style_category_id = '999' OR sptsc.sptsc_style_category_id = '1000' OR sptsc.sptsc_style_category_id = '1001' OR sptsc.sptsc_style_category_id = '1002' OR sptsc.sptsc_style_category_id = '1003' OR sptsc.sptsc_style_category_id = '962' OR sptsc.sptsc_style_category_id = '979' OR sptsc.sptsc_style_category_id = '981' OR sptsc.sptsc_style_category_id = '73' OR sptsc.sptsc_style_category_id = '174' OR sptsc.sptsc_style_category_id = '983' OR sptsc.sptsc_style_category_id = '218' OR sptsc.sptsc_style_category_id = '266' OR sptsc.sptsc_style_category_id = '241' OR sptsc.sptsc_style_category_id = '243' OR sptsc.sptsc_style_category_id = '245' OR sptsc.sptsc_style_category_id = '247' OR sptsc.sptsc_style_category_id = '276' OR sptsc.sptsc_style_category_id = '278' OR sptsc.sptsc_style_category_id = '279' OR sptsc.sptsc_style_category_id = '896' OR sptsc.sptsc_style_category_id = '914' OR sptsc.sptsc_style_category_id = '86' OR sptsc.sptsc_style_category_id = '88' OR sptsc.sptsc_style_category_id = '91' OR sptsc.sptsc_style_category_id = '952' OR sptsc.sptsc_style_category_id = '958' OR sptsc.sptsc_style_category_id = '959' OR sptsc.sptsc_style_category_id = '963' OR sptsc.sptsc_style_category_id = '964' OR sptsc.sptsc_style_category_id = '965' OR sptsc.sptsc_style_category_id = '966' OR sptsc.sptsc_style_category_id = '967' OR sptsc.sptsc_style_category_id = '968' OR sptsc.sptsc_style_category_id = '969' OR sptsc.sptsc_style_category_id = '970' OR sptsc.sptsc_style_category_id = '972' OR sptsc.sptsc_style_category_id = '974') AND ( sptmt.sptmt_metal_type_id = '10') AND ( sptmt.sptmt_metal_type_id = '4' OR sptmt.sptmt_metal_type_id = '8' OR sptmt.sptmt_metal_type_id = '10' OR sptmt.sptmt_metal_type_id = '11' OR sptmt.sptmt_metal_type_id = '12' OR sptmt.sptmt_metal_type_id = '13' OR sptmt.sptmt_metal_type_id = '14') AND ( pm.diamond_quality_id = '1' OR pm.diamond_quality_id = '62' OR pm.diamond_quality_id = '3' OR pm.diamond_quality_id = '57' OR pm.diamond_quality_id = '6' OR pm.diamond_quality_id = '63' OR pm.diamond_quality_id = '9' OR pm.diamond_quality_id = '64' OR pm.diamond_quality_id = '12' OR pm.diamond_quality_id = '58' OR pm.diamond_quality_id = '13' OR pm.diamond_quality_id = '14' OR pm.diamond_quality_id = '52' OR pm.diamond_quality_id = '54' OR pm.diamond_quality_id = '53' OR pm.diamond_quality_id = '55' OR pm.diamond_quality_id = '17' OR pm.diamond_quality_id = '50' OR pm.diamond_quality_id = '51' OR pm.diamond_quality_id = '44' OR pm.diamond_quality_id = '45' OR pm.diamond_quality_id = '19' OR pm.diamond_quality_id = '21' OR pm.diamond_quality_id = '22' OR pm.diamond_quality_id = '23' OR pm.diamond_quality_id = '46' OR pm.diamond_quality_id = '49' OR pm.diamond_quality_id = '41' OR pm.diamond_quality_id = '40' OR pm.diamond_quality_id = '42' OR pm.diamond_quality_id = '43' OR pm.diamond_quality_id = '47' OR pm.diamond_quality_id = '48' OR pm.diamond_quality_id = '66' OR pm.diamond_quality_id = '59' OR pm.diamond_quality_id = '60' OR pm.diamond_quality_id = '65' OR pm.diamond_quality_id = '61' OR pm.diamond_quality_id = '56') AND pm.center_stone_type_id = '3' AND ( pm.vender_id = '1' OR pm.vender_id = '6' OR pm.vender_id = '16') and diamond_weight_group_id <> '' and diamond_weight_group_id <> 0 and diamond_quality_id <> '' and diamond_quality_id <> 0 group by pm.design_id ORDER BY sort_order ASC ) )as a group by products_id ORDER BY sort_order ASC LIMIT 0 , 1";
  
$diamond_record = DB::select($diamond_query);

foreach($diamond_record as $rec){
  foreach($records as $rec){
    if(!empty($rec->image_url)){
  $rec->image_url = 'https://www.mydiamondsearch.com/ampvd/product_images/' .$rec->image_url;
  }else{
    
    $rec->image_url = 'https://cdn.shopify.com/s/files/1/0697/1742/6414/files/No_image.jpg';
    
    }

      $diamond_weight_query =   "select dwg.dwg_name as name , dwg.dwg_id as id, pm.products_id as products_id from `shop_products` as pm join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id join diamond_weight_group as dwg on dwg.dwg_id = pm.diamond_weight_group_id left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id left join vendor_master vm on vm.vendorid = pm.vender_id where diamond_weight_group_id != 0 and diamond_quality_id != 0 and pm.design_id = '".$rec->design_id."' and 1 AND vm.vendor_status = 1 AND vm.deleted = 0 and product_image_status = 1 AND ( ptpg.ptpg_level = '1' ) AND products_quantity <> 0 AND products_status = 1  group by diamond_weight_group_id having(count(diamond_weight_group_id) > 0) order by dwg_sort_order ASC";
    // $rec->diamond_weight = DB::connection('mysql2')->select($diamond_weight_query);



    $diamond_weights = DB::connection('mysql2')->select($diamond_weight_query);

    if (!empty($diamond_weights)) {
        $first_weight = $diamond_weights[0];
        $last_weight = end($diamond_weights); // Get the last entry in the sorted list
        $rec->diamond_weight_max = $last_weight->name; // or ->id if needed
        $rec->diamond_weight_min = $first_weight->name;
    } else {
        $rec->diamond_weight_max = null;
        $rec->diamond_weight_min = null;
    }
    $rec->diamond_weight = $diamond_weights;

    $diamond_quality_query =   " select dqg.dqg_alias as name , dqg.description as description , dqg.dqg_id as id, dqg.dqg_icon as dqg_icon, dcst.dcst_name as diamond_type from `shop_products` as pm join shop_products_description as pd on pm.products_id = pd.products_id and language_id = '1' left join shop_products_to_shape as pts on pm.products_id = pts.products_id left join shop_products_to_style_category as sptsc on pm.products_id = sptsc.sptsc_products_id left join shop_products_to_metal_type as sptmt on pm.products_id = sptmt.sptmt_products_id join shop_products_to_categories as ptc on pm.products_id = ptc.products_id join diamond_quality_group as dqg on dqg.dqg_id = pm.diamond_quality_id left join shop_product_to_product_group as ptpg on pm.products_id = ptpg.ptpg_products_id left join vendor_master vm on vm.vendorid = pm.vender_id join diamond_center_stone_type as dcst on dcst.dcst_id = pm.center_stone_type_id where diamond_quality_id != 0 and pm.design_id = '".$rec->design_id."' and 1 AND vm.vendor_status = 1 AND vm.deleted = 0  group by diamond_quality_id having(count(diamond_quality_id) > 0) order by dqg_sort_order ASC";
    $rec->diamond_quality = DB::connection('mysql2')->select($diamond_quality_query); 


    $diamond_min_price_qry = "
                  SELECT 
                      MIN(
                          IF(c_markup_type = 1,
                              products_price3 + COALESCE(c_markup_value, 0),
                              products_price3 + ((products_price3 * COALESCE(c_markup_value, 0)) / 100)
                          )
                      ) AS min_products_price
                  FROM shop_products AS pm
                  LEFT JOIN client_markup cmark 
                      ON cmark.retailerid = 500 
                      AND products_price3 BETWEEN cmark.price_from AND cmark.price_to 
                      AND cmark.type = '3'
                  LEFT JOIN vendor_master vm ON vm.vendorid = pm.vender_id
                  WHERE 
                      vm.vendor_status = 1 
                      AND vm.deleted = 0 
                      AND product_image_status = 1 
                      AND products_quantity <> 0 
                      AND products_status = 1 
                      AND pm.center_stone_type_id = '3' 
                      AND pm.vender_id IN (1, 6, 16)
                      AND diamond_weight_group_id <> '' 
                      AND diamond_weight_group_id <> 0 
                      AND diamond_quality_id <> '' 
                      AND diamond_quality_id <> 0 and pm.design_id = '".$rec->design_id."'";

    $min_products_price = DB::connection('mysql2')->select($diamond_min_price_qry);
    $rec->min_products_price = $min_products_price[0]->min_products_price;
}

  

   
  }
  
  
    

        if($request->shop){
         $user = User::where('name', $request->shop)->first();   
        }else{
          $user = User::find(9);   
        }
        
  $shop_D = $user->api()->rest('GET', '/admin/api/2024-01/shop.json');

  $response_data = [
    'records'=>$diamond_record,
     'shop_D' => $shop_D['body'] 
     
  ];
  // echo '<pre / >'; print_r($diamond_record);
  return response()->json($response_data)
                     ->header('Access-Control-Allow-Origin', '*')
                     ->header('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS')
                     ->header('Access-Control-Allow-Headers', 'Content-Type, Authorization');
}
	
}