<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Max-Age: 3600");
    header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
header('Content-type: application/json');
//$time_two = microtime(true);
// ini_set('display_errors', '1');
// ini_set('display_startup_errors', '1');
// error_reporting(E_ALL);
// $ch = curl_init("https://ahmarket.com/en/rest/V1/products?".$_SERVER['QUERY_STRING']);
// curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
// curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
// curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json","Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70"));
// $result = curl_exec($ch);
// $results= json_decode($result, true);
// $allproduct=[];
// $time_two = microtime(true);
// foreach($results['items'] as $item){
//     $ch = curl_init("https://ahmarket.com/en/rest/V1/stockItems/".$item['sku']);
//     curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
//     curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//     curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json","Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70"));
//     $result2 = curl_exec($ch);
//     $result2= json_decode($result2, true);
//     $item['qty']=$result2['qty']; 
//     $item['is_in_stock']=$result2['is_in_stock'];
//     $allproduct['items'][]=$item;
// }
//$allproduct['time']='Foreach + range loop: ' . (microtime(true) - $time_two) . ' s';
//echo 'Foreach + range loop: ' . (microtime(true) - $time_two) . ' s', PHP_EOL;
// $time_one = microtime(true);
// 	for ($i=0; $i < count($results['items']); $i++) {
// 	$ch = curl_init("https://ahmarket.com/en/rest/V1/stockItems/".$results['items'][$i]['sku']);
//     curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
//     curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//     curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json","Authorization: Bearer 2dhdhimvhb2eg5pczxquwhmh1e1v9x70"));
//     $result2 = curl_exec($ch);
//     $result2= json_decode($result2, true);
//     $item['qty']=$result2['qty']; 
//     $item['is_in_stock']=$result2['is_in_stock'];
//     $allproduct['items'][]=$item;
// 	}
// echo 'For loop: ' . (microtime(true) - $time_one) . ' s', PHP_EOL;
//echo json_encode($allproduct);

use Magento\Framework\App\Bootstrap;
require '../../app/bootstrap.php';
$params = $_SERVER;
$bootstrap = Bootstrap::create(BP, $params);
$obj = $bootstrap->getObjectManager();
$state = $obj->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');
$_objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $_objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
// $row_id_sql = "SELECT e.entity_id AS 'id',
// v1.value AS 'name',
// e.sku AS 'sku',
// d1.value AS 'price',
// t1.value AS 'short_description',
// t2.value AS 'description',
// v2.value AS 'image',
// v3.value AS 'thumbnail',
// v4.value AS 'url_key',
// mg.value AS 'media_gallery',
// cids.category_ids AS 'category_ids',
// cids.category_names AS 'category_names'
// FROM  ahqa_catalog_product_entity e
// LEFT JOIN  ahqa_catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
// AND v1.store_id = 0 AND v1.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'name'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
// LEFT JOIN  ahqa_catalog_product_entity_text t1 ON e.entity_id = t1.entity_id
// AND t1.store_id = 0 AND t1.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'short_description'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
// LEFT JOIN  ahqa_catalog_product_entity_text t2 ON e.entity_id = t2.entity_id
// AND t2.store_id = 0 AND t2.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'description'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
// LEFT JOIN  ahqa_catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
// AND v2.store_id = 0 AND v2.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'image'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
// LEFT JOIN  ahqa_catalog_product_entity_varchar v3 ON e.entity_id = v3.entity_id
// AND v3.store_id = 0 AND v3.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'thumbnail'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
// LEFT JOIN  ahqa_catalog_product_entity_varchar v4 ON e.entity_id = v4.entity_id
// AND v2.store_id = 0 AND v4.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'url_key'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
// LEFT JOIN  ahqa_catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
// AND d1.store_id = 0 AND d1.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'price'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
// LEFT JOIN
// (SELECT m1.entity_id, GROUP_CONCAT(m2.value) AS value FROM  ahqa_catalog_product_entity_media_gallery_value_to_entity m1
// INNER JOIN   ahqa_catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
// AND m2.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'media_gallery'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
// GROUP BY m1.entity_id) mg ON e.entity_id = mg.entity_id
// LEFT JOIN
// (SELECT product_id, GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids, GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
// FROM  ahqa_catalog_category_product c
// INNER JOIN   ahqa_catalog_category_entity_varchar cv ON c.category_id = cv.entity_id
// AND cv.store_id = 0
// AND cv.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'name'
// AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_category'))
// GROUP BY product_id) cids ON e.entity_id = cids.product_id WHERE 1 LIMIT 1000";

$row_id_sql="SELECT e.entity_id AS 'id',st.qty,st.is_in_stock,
v1.value AS 'name',
e.sku AS 'sku',
ps.value AS 'status',
e.type_id AS 'type_id',
e.type_id AS 'child_id',
d1.value AS 'price',
d2.value AS 'special_price',
t3.value AS 'short_description',
t4.value AS 'description',
t1.value AS 'deal_from_date',
t2.value AS 'deal_to_date',
v2.value AS 'image',
v3.value AS 'thumbnail',
v4.value AS 'url_key',
mg.value AS 'media_gallery',
cids.category_ids AS 'category_ids',
cids.category_names AS 'category_names'
FROM  ahqa_catalog_product_entity e
JOIN ahqa_catalog_product_entity_int ps  ON e.entity_id = ps.entity_id
AND ps.value = 1 AND ps.attribute_id = ( SELECT attribute_id FROM ahqa_eav_attribute  WHERE attribute_code ='status')
LEFT JOIN  ahqa_catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id = 0 AND v1.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'name'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_text t3 ON e.entity_id = t3.entity_id
AND t3.store_id = 0 AND t3.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'short_description'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_text t4 ON e.entity_id = t4.entity_id
AND t4.store_id = 0 AND t4.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'description'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_text t1 ON e.entity_id = t1.entity_id
AND t1.store_id = 0 AND t1.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'deal_from_date'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_text t2 ON e.entity_id = t2.entity_id
AND t2.store_id = 0 AND t2.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'deal_to_date'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
AND v2.store_id = 0 AND v2.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'image'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_varchar v3 ON e.entity_id = v3.entity_id
AND v3.store_id = 0 AND v3.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'thumbnail'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_varchar v4 ON e.entity_id = v4.entity_id
AND v2.store_id = 0 AND v4.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'url_key'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id = 0 AND d1.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'price'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN  ahqa_catalog_product_entity_decimal d2 ON e.entity_id = d2.entity_id
AND d2.store_id = 0 AND d2.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'special_price'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN (SELECT m1.entity_id, GROUP_CONCAT(m2.value) AS value FROM  ahqa_catalog_product_entity_media_gallery_value_to_entity m1
INNER JOIN   ahqa_catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
AND m2.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'media_gallery'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_product'))
GROUP BY m1.entity_id) mg ON e.entity_id = mg.entity_id
LEFT JOIN ahqa_cataloginventory_stock_item AS st on e.entity_id = st.product_id
LEFT JOIN (SELECT product_id, GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids, GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
FROM  ahqa_catalog_category_product c
INNER JOIN   ahqa_catalog_category_entity_varchar cv ON c.category_id = cv.entity_id
AND cv.store_id = 0
AND cv.attribute_id = (SELECT attribute_id FROM  ahqa_eav_attribute WHERE attribute_code = 'name'
AND entity_type_id = (SELECT entity_type_id FROM  ahqa_eav_entity_type WHERE entity_type_code = 'catalog_category'))
GROUP BY product_id) cids ON e.entity_id = cids.product_id";

    $LIMIT=10;
    $currentPage=1;
    $OFFSET=$LIMIT*($currentPage-1);
    $sortOrders='e.entity_id';
    $sortOrdersDirect='DESC';
if(isset($_REQUEST['searchCriteria'])){
    $LIMIT=(isset($_REQUEST['searchCriteria']['pageSize']))? $_REQUEST['searchCriteria']['pageSize']: 10;
    $currentPage=(isset($_REQUEST['searchCriteria']['currentPage']))? $_REQUEST['searchCriteria']['currentPage']: 1;
   
    $OFFSET=$LIMIT*($currentPage-1);
     if(isset($_REQUEST['searchCriteria']['filterGroups'])){
        foreach($_REQUEST['searchCriteria']['filterGroups'] as $filters){
        $row_id_sql.=" WHERE e.type_id!='virtual' AND ";
          if($filters['filters']){
              foreach($filters['filters'] as $filter){
                  
                  switch ($filter['field'])
                    {
                    case 'name':
                      $stringName = str_replace(' ', '%', $filter['value']);
                     // echo  $checkCondition =($filter['conditionType'])? $filter['conditionType'] : '=';
                            $row_id_sql.=" v1.value like '".$stringName."' ";
                        break;
                    case 'category_id':
                            $row_id_sql.=" cids.category_ids=".$filter['value']." ";
                        break;
                    
                    }
                  
               
                }
            }
        }
    }
    
    if(isset($_REQUEST['searchCriteria']['sortOrders'])){
        foreach($_REQUEST['searchCriteria']['sortOrders'] as $filterSort){
          if($filterSort['field']){
                switch ($filterSort['field'])
                {
                case 'price':
                    $sortOrders ='d1.value';
                    break;
                case 'entity_id':
                    $sortOrders ='e.entity_id';
                    break;
                case 'name':
                    $sortOrders ='v1.value';
                    break;
                }
            }
            
            if($filterSort['direction']){
                 $sortOrdersDirect=($filterSort['direction'])? $filterSort['direction']: 'DESC';
            }
        }
    }
    
}
$row_id_sql.=" GROUP BY category_ids ORDER BY ".$sortOrders." ".$sortOrdersDirect." LIMIT ".$LIMIT." OFFSET ".$OFFSET." "; //GROUP BY v1.value
//echo  $row_id_sql;

  // WHERE 1 LIMIT 100"; //
$row_id_sql_fetch['items'] = $connection->fetchAll($row_id_sql);

foreach($row_id_sql_fetch['items'] as $key=>$items){
     $row_id_sql_fetch['items'][$key]['special_price'] = ($items['special_price']==null)? 0: $items['special_price'];
            $row_id_sql_fetch['items'][$key]['description'] = ($items['description']==null)? '': $items['description'];
             $row_id_sql_fetch['items'][$key]['short_description'] = ($items['short_description']==null)? '': $items['short_description'];
              $row_id_sql_fetch['items'][$key]['media_gallery'] = ($items['media_gallery']==null)? $items['image']: $items['media_gallery'];
        if($row_id_sql_fetch['items'][$key]['type_id']=='configurable'){
            $product = $_objectManager->create('Magento\Catalog\Model\Product')->load($items['id']);
            $productTypeInstance = $_objectManager->get('Magento\ConfigurableProduct\Model\Product\Type\Configurable');
            $productAttributeOptions = $productTypeInstance->getConfigurableAttributesAsArray($product);
             $row_id_sql_fetch['items'][$key]['price'] = $product->getFinalPrice();            //   $stockItem = $product->getExtensionAttributes()->getStockItem();
            //   print_r($stockItem->getQty()); 
              $row_id_sql_fetch['items'][$key]['qty']=$product->getExtensionAttributes()->getStockItem()->getMinSaleQty();
             $associatedProducts = $product->getTypeInstance()->getUsedProducts($product);
            foreach ($associatedProducts as $associatedProduct) {
               $row_id_sql_fetch['items'][$key]['attribute_sku'][] = $associatedProduct->getSku(); //. PHP_EOL;
            }
            // $sku = $product->getSku(); // get SKU
            // $name = $product->getName(); // get name
          //  $attribute = $product->getData("attribute_code"); 
           // $manufacturer = $product->getManufacturer(); // get manufacturer
           
            $row_id_sql_fetch['items'][$key]['options']=$productAttributeOptions; //array('option_id'=>124,'option_price'=>12);
        }
}
echo json_encode($row_id_sql_fetch,true);
$connection->closeConnection();

?>