<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

use Illuminate\Pagination\Paginator;
use GuzzleHttp\Client; 

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 JewelryControllerjk 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; 
      $userid = DB::table('users')->where('name', $shop_name)->value('id');
      $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';
		}
		
		
	}
        
       $retaillerid = DB::table('shopify_theme_changes')
    ->where('user_id', 1)
    ->first(['id', 'theme_name', 'updated_at']);
      
         // $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 (ptpg.ptpg_level = '1') 
        AND products_quantity <> 0        
        AND products_status = 1 
       ".$WhereQuery." 
       
      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 (ptpg.ptpg_level = '1') 
          AND products_quantity <> 0           
          AND products_status = 1          
         ".$WhereQuery."
        group by 
          pm.design_id 
        ORDER BY 
          sort_order ASC
      )
  ) as a 
group by 
  products_id 
ORDER BY 
  sort_order ASC 
LIMIT 
  0, 150
";

    // die(' aaaaa');
          $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';
						
						}

          $rec->diamond_weight = DB::connection('mysql2')
    ->table('shop_products as pm')
    ->join('shop_products_description as pd', function ($join) {
        $join->on('pm.products_id', '=', 'pd.products_id')
            ->where('pd.language_id', '=', '1');
    })
    ->join('shop_products_to_categories as ptc', 'pm.products_id', '=', 'ptc.products_id')
    ->join('diamond_quality_group as dqg', 'dqg.dqg_id', '=', 'pm.diamond_quality_id')
    ->join('diamond_center_stone_type as dcst', 'dcst.dcst_id', '=', 'pm.center_stone_type_id')
    ->leftJoin('shop_product_to_product_group as ptpg', 'pm.products_id', '=', 'ptpg.ptpg_products_id')
    ->leftJoin('vendor_master as vm', 'vm.vendorid', '=', 'pm.vender_id')
    ->where('pm.diamond_quality_id', '!=', 0)
    ->where('pm.design_id', $rec->design_id)
    ->where('vm.vendor_status', 1)
    ->where('vm.deleted', 0)
    ->groupBy('pm.diamond_quality_id')
    ->orderBy('dqg.dqg_sort_order', 'ASC')
    ->select([
        'dqg.dqg_alias as name',
        'dqg.description',
        'dqg.dqg_id as id',
        'dqg.dqg_icon',
        'dcst.dcst_name as diamond_type'
    ])
    ->get(); 

          $rec->diamond_quality = DB::connection('mysql2')
    ->table('shop_products as pm')
    ->join('shop_products_description as pd', function ($join) {
        $join->on('pm.products_id', '=', 'pd.products_id')
            ->where('pd.language_id', '=', '1');
    })
    ->join('shop_products_to_categories as ptc', 'pm.products_id', '=', 'ptc.products_id')
    ->join('diamond_quality_group as dqg', 'dqg.dqg_id', '=', 'pm.diamond_quality_id')
    ->join('diamond_center_stone_type as dcst', 'dcst.dcst_id', '=', 'pm.center_stone_type_id')
    ->leftJoin('shop_product_to_product_group as ptpg', 'pm.products_id', '=', 'ptpg.ptpg_products_id')
    ->leftJoin('vendor_master as vm', 'vm.vendorid', '=', 'pm.vender_id')
    ->where('pm.diamond_quality_id', '!=', 0)
    ->where('pm.design_id', $rec->design_id)
    ->where('vm.vendor_status', 1)
    ->where('vm.deleted', 0)
    ->groupBy('pm.diamond_quality_id')
    ->orderBy('dqg.dqg_sort_order', 'ASC')
    ->select([
        'dqg.dqg_alias as name',
        'dqg.description',
        'dqg.dqg_id as id',
        'dqg.dqg_icon',
        'dcst.dcst_name as diamond_type'
    ])
    ->get(); 

		  }
		//->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,
  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 (ptpg.ptpg_level = '1') 
        AND products_quantity <> 0        
        AND products_status = 1 
       ".$WhereQuery." 
       
      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 (ptpg.ptpg_level = '1') 
          AND products_quantity <> 0           
          AND products_status = 1          
         ".$WhereQuery."
        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');
    
}
	
}