<?php

/**
 * Vvveb
 *
 * Copyright (C) 2020  Ziadin Givan
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 */

namespace Vvveb\Sql;

class productsSQL extends \Vvveb\System\Db\mysqli {
	public function __construct() {
		parent::__construct();
	}

	function getProduct($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['product_id' => 'i', 'slug' => 's', 'language_id' => 'i'];

		$prevSql = $sql ?? '';
		$sql     = 'SELECT *
			FROM product as _ LEFT JOIN product_description pd ON (_.product_id = pd.product_id)  
		WHERE 
        
			1';

		if (isset($params['slug'])) {
			$sql .= ' AND pd.slug = :slug ';
		}

		if (isset($params['product_id'])) {
			$sql .= ' AND _.product_id = :product_id ';
		}

		$sql .= ' 
	LIMIT 1';

		if ($sql) {
			$stmt['_'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['_']) {
				if (method_exists($stmt['_'], 'get_result')) {
					$result = $stmt['_']->get_result();
				} else {
					$result = $this->get_result($stmt['_']);
				}
			}

			/*
			if ('_' == '_') {
				$value = $result->fetch_array(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('_' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['_'][$row['']] = $values;

							$results['_'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('_' == '_') {
					$value = $result->fetch_array(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['_'] = $result->fetch_array(MYSQLI_ASSOC);
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'SELECT image, product_image_id, sort_order, product_image_id as array_key FROM product_image AS images
		WHERE product_id = ' . ($results['product_id'] ?? 'NULL') . '';

		if ($sql) {
			$stmt['images'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['images']) {
				if (method_exists($stmt['images'], 'get_result')) {
					$result = $stmt['images']->get_result();
				} else {
					$result = $this->get_result($stmt['images']);
				}
			}

			/*
			if ('images' == '_') {
				$value = $result->fetch_array(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('product_image_id')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('images' == '_') {
							//$results[$row['product_image_id']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['images'][$row['product_image_id']] = $values;

							$results['images'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('images' == '_') {
					$value = $result->fetch_array(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['images'] = $result->fetch_array(MYSQLI_ASSOC);
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function editProduct($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['product' => 'a', 'product_id' => 'i'];

		$prevSql = $sql ?? '';
		$sql     = 'DELETE FROM product_description WHERE product_id = :product_id';

		if ($sql) {
			$stmt['product_description'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['product_description']) {
				if (method_exists($stmt['product_description'], 'get_result')) {
					$result = $stmt['product_description']->get_result();
				} else {
					$result = $this->get_result($stmt['product_description']);
				}
			}

			/*
			if ('product_description' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('product_description' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['product_description'][$row['']] = $values;

							$results['product_description'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('product_description' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['product_description'] = $this->insertId;
				}
			}
			//}
		}

		$prevSql = $sql ?? '';

		foreach ($params['product']['product_description'] as $key => $rowParent) {
			$params['each']     = $rowParent;
			$paramTypes['each'] = 'a';
			$sql                = 'INSERT INTO product_description 
		
				(';
			$sql .= '`' . implode('`,`', array_keys($params['each']));
			$sql .= '` , product_id, meta_title, meta_description, meta_keyword )
			
			VALUES ( :each, :product_id, \'\', \'\', \'\' )';

			if ($sql) {
				$stmt['product_description'] = $this->execute($sql, $params, $paramTypes);

				$result = false;

				if ($stmt['product_description']) {
					if (method_exists($stmt['product_description'], 'get_result')) {
						$result = $stmt['product_description']->get_result();
					} else {
						$result = $this->get_result($stmt['product_description']);
					}
				}

				/*
				if ('product_description' == '_') {
					$value = $this->insertId;
					if (is_array($value))
					{
						$results = $results + $value;
					} else
					{
						$results = $value;
					}
				} else { */
				if (! empty('')) {
					if ($result) {
						while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
							$values = $row;

							if (! empty('')) {
								//$values = $row[''];
								$values = $row['array_value'];
							}

							if ('product_description' == '_') {
								//$results[$row['']] = $values;
								$results[$row['array_key']] = $values;
							} else {
								//$results['product_description'][$row['']] = $values;

								$results['product_description'][$row['array_key']] = $values;
							}
						}
					}
				} else {
					if ('product_description' == '_') {
						$value = $this->insertId;

						if (is_array($value)) {
							$results = $results + $value;
						} else {
							$results = $value;
						}
					} else {
						$results['product_description'] = $this->insertId;
					}
				}
				//}
			}
		}
		unset($params['each'], $paramTypes['each']);

		$prevSql = $sql ?? '';
		$sql     = '';

		$filterArray = [
			'product_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
				'e' => 'auto_increment',
			],
			'model' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'sku' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'upc' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'ean' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'jan' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'isbn' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'mpn' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'location' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'quantity' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'stock_status_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'image' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'manufacturer_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'shipping' => [
				'd' => '1',
				'n' => false,
				't' => 'tinyint',
			],
			'price' => [
				'd' => '0.0000',
				'n' => false,
				't' => 'decimal',
			],
			'points' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'tax_class_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'date_available' => [
				'd' => '\'1000-01-01\'',
				'n' => false,
				't' => 'date',
			],
			'weight' => [
				'd' => '0.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'weight_class_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'length' => [
				'd' => '0.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'width' => [
				'd' => '0.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'height' => [
				'd' => '0.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'length_class_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'subtract' => [
				'd' => '1',
				'n' => false,
				't' => 'tinyint',
			],
			'minimum' => [
				'd' => '1',
				'n' => false,
				't' => 'int',
			],
			'sort_order' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'status' => [
				'd' => '0',
				'n' => false,
				't' => 'tinyint',
			],
			'viewed' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'date_added' => [
				'd' => 'current_timestamp()',
				'n' => false,
				't' => 'datetime',
			],
			'date_modified' => [
				'd' => 'current_timestamp()',
				'n' => false,
				't' => 'datetime',
			],
		];
		$params['product']= $this->filter($params['product'], $filterArray,false);

		$sql = '';

		if ($sql) {
			$stmt[''] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['']) {
				if (method_exists($stmt[''], 'get_result')) {
					$result = $stmt['']->get_result();
				} else {
					$result = $this->get_result($stmt['']);
				}
			}

			/*
			if ('' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results[''][$row['']] = $values;

							$results[''][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results[''] = $this->insertId;
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'UPDATE product 
			
			SET';

		list($_sql, $_params) = $this->expandList($params['product'], 'product');

		$sql .= ' ' . $_sql;

		if (is_array($_params)) {
			$paramTypes = array_merge($paramTypes, $_params);
		}

		$sql .= ' ' . '
	WHERE product_id = :product_id';

		if ($sql) {
			$stmt['product'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['product']) {
				if (method_exists($stmt['product'], 'get_result')) {
					$result = $stmt['product']->get_result();
				} else {
					$result = $this->get_result($stmt['product']);
				}
			}

			/*
			if ('product' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('product' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['product'][$row['']] = $values;

							$results['product'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('product' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['product'] = $this->insertId;
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function addProduct($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['product' => 'a'];

		$prevSql = $sql ?? '';
		$sql     = '';

		$filterArray = [
			'product_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
				'e' => 'auto_increment',
			],
			'model' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'sku' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'upc' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'ean' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'jan' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'isbn' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'mpn' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'location' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'quantity' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'stock_status_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'image' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'manufacturer_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'shipping' => [
				'd' => '1',
				'n' => false,
				't' => 'tinyint',
			],
			'price' => [
				'd' => '0.0000',
				'n' => false,
				't' => 'decimal',
			],
			'points' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'tax_class_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'date_available' => [
				'd' => '\'1000-01-01\'',
				'n' => false,
				't' => 'date',
			],
			'weight' => [
				'd' => '0.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'weight_class_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'length' => [
				'd' => '0.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'width' => [
				'd' => '0.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'height' => [
				'd' => '0.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'length_class_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'subtract' => [
				'd' => '1',
				'n' => false,
				't' => 'tinyint',
			],
			'minimum' => [
				'd' => '1',
				'n' => false,
				't' => 'int',
			],
			'sort_order' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'status' => [
				'd' => '0',
				'n' => false,
				't' => 'tinyint',
			],
			'viewed' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'date_added' => [
				'd' => 'current_timestamp()',
				'n' => false,
				't' => 'datetime',
			],
			'date_modified' => [
				'd' => 'current_timestamp()',
				'n' => false,
				't' => 'datetime',
			],
		];
		$params['product_data']= $this->filter($params['product'], $filterArray,false);

		$sql = 'INSERT INTO product 
		
			(';
		$sql .= '`' . implode('`,`', array_keys($params['product_data']));
		$sql .= '` )
			
		VALUES ( :product_data )';

		if ($sql) {
			$stmt['product'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['product']) {
				if (method_exists($stmt['product'], 'get_result')) {
					$result = $stmt['product']->get_result();
				} else {
					$result = $this->get_result($stmt['product']);
				}
			}

			/*
			if ('product' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('product' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['product'][$row['']] = $values;

							$results['product'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('product' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['product'] = $this->insertId;
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = '';

		$filterArray = [
			'product_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'language_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'slug' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'description' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
			'tag' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
			'meta_title' => [
				'd' => '\'\'',
				'n' => false,
				't' => 'varchar',
			],
			'meta_description' => [
				'd' => '\'\'',
				'n' => false,
				't' => 'varchar',
			],
			'meta_keyword' => [
				'd' => '\'\'',
				'n' => false,
				't' => 'varchar',
			],
		];
		$params['product_description']= $this->filter($params['product'], $filterArray,false);

		$sql = 'INSERT INTO product_description 
		
			( `product_id`,';
		$sql .= '`' . implode('`,`', array_keys($params['product_description']));
		$sql .= '` )
			
		VALUES ( ' . ($results['product'] ?? 'NULL') . ', :product_description )';

		if ($sql) {
			$stmt['product_description'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['product_description']) {
				if (method_exists($stmt['product_description'], 'get_result')) {
					$result = $stmt['product_description']->get_result();
				} else {
					$result = $this->get_result($stmt['product_description']);
				}
			}

			/*
			if ('product_description' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('product_description' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['product_description'][$row['']] = $values;

							$results['product_description'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('product_description' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['product_description'] = $this->insertId;
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function getProducts($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['language_id' => 'i', 'customer_group_id' => 'i', 'store_id' => 'i', 'category_id' => 'i', 'search' => 's', 'product_id' => 'a', 'order_by' => 's', 'direction' => 's', 'start' => 'i', 'limit' => 'i'];

		$prevSql = $sql ?? '';
		$sql     = 'SELECT  *,
                pd.name AS name,
                products.image';

		if (! empty($params['include_manufacturer'])) {
			$sql .= ' ,m.name AS manufacturer ';
		}

		if (! empty($params['include_image_gallery'])) {
			$sql .= ' ,(SELECT GROUP_CONCAT(pi.image) FROM product_image as pi WHERE pi.product_id = products.product_id GROUP BY pi.product_id) as images ';
		}

		if (! empty($params['include_discount']) && ! empty($params['customer_group_id'])) {
			$sql .= ' ,(SELECT price
				   FROM product_discount pd2
				   WHERE pd2.product_id = products.product_id
					 AND pd2.customer_group_id = :customer_group_id
					 AND pd2.quantity = \'1\'
					 AND ((pd2.date_start = \'0000-00-00\'
						   OR pd2.date_start < NOW())
						  AND (pd2.date_end = \'0000-00-00\'
							   OR pd2.date_end > NOW()))
				   ORDER BY pd2.priority ASC, pd2.price ASC
				   LIMIT 1) AS discount ';
		}

		if (! empty($params['include_special']) && ! empty($params['customer_group_id'])) {
			$sql .= ' ,(SELECT price
			   FROM product_special ps
			   WHERE ps.product_id = products.product_id
				 AND ps.customer_group_id = :customer_group_id
				 AND ((ps.date_start = \'0000-00-00\'
					   OR ps.date_start < NOW())
					  AND (ps.date_end = \'0000-00-00\'
						   OR ps.date_end > NOW()))
			   ORDER BY ps.priority ASC, ps.price ASC
			   LIMIT 1) AS special ';
		}

		if (! empty($params['include_reward']) && ! empty($params['customer_group_id'])) {
			$sql .= ' ,(SELECT points
			   FROM product_reward pr
			   WHERE pr.product_id = products.product_id
				 AND pr.customer_group_id = :customer_group_id
			   AS reward ';
		}

		if (! empty($params['include_stock_status'])) {
			$sql .= ' ,SELECT ss.name
			   FROM stock_status ss
			   WHERE ss.stock_status_id = products.stock_status_id
				 AND ss.language_id = :language_id) 
			  AS stock_status, ';
		}

		if (! empty($params['include_weight_class'])) {
			$sql .= ' ,(SELECT wcd.unit
			   FROM weight_class_description wcd
			   WHERE products.weight_class_id = wcd.weight_class_id
				 AND wcd.language_id = :language_id) 
			   AS weight_class ';
		}

		if (! empty($params['include_length_class'])) {
			$sql .= ' ,(SELECT lcd.unit
			   FROM length_class_description lcd
			   WHERE products.length_class_id = lcd.length_class_id
				 AND lcd.language_id = :language_id) 
			   AS length_class ';
		}

		if (! empty($params['include_rating'])) {
			$sql .= ' ,(SELECT AVG(rating) AS total
			   FROM review r1
			   WHERE r1.product_id = products.product_id
				 AND r1.status = \'1\'
			   GROUP BY r1.product_id) 
			  AS rating ';
		}

		if (! empty($params['include_reviews'])) {
			$sql .= ' ,(SELECT COUNT(*) AS total
			   FROM review r2
			   WHERE r2.product_id = products.product_id
				 AND r2.status = \'1\'
			   GROUP BY r2.product_id) AS reviews ';
		}

		$sql .= ' 
	FROM product AS products
		
			LEFT JOIN product_to_store p2s ON (products.product_id = p2s.product_id) 
			LEFT JOIN product_description pd ON (products.product_id = pd.product_id)';

		if (! empty($params['include_manufacturer'])) {
			$sql .= ' LEFT JOIN manufacturer m ON (products.manufacturer_id = m.manufacturer_id) ';
		}

		if (! empty($params['category_id'])) {
			$sql .= ' INNER JOIN product_to_category pc ON (products.product_id = pc.product_id AND pc.category_id = :category_id) ';
		}

		$sql .= ' 
	WHERE pd.language_id = :language_id AND p2s.store_id = :store_id';

		if (isset($params['query'])) {
			$sql .= ' AND pd.name LIKE CONCAT(\'%\',:query,\'%\') ';
		}

		if (isset($params['product_id']) && count($params['product_id']) > 0) {
			$sql .= ' AND products.product_id IN (:product_id) ';
		}

		if (isset($params['order_by'])) {
			$sql .= ' ORDER BY ' . ($params['order_by'] ?? 'NULL') . ' ' . ($params['direction'] ?? 'NULL') . ' ';
		}

		$sql .= ' 
	LIMIT :start, :limit';

		if ($sql) {
			$stmt['products'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['products']) {
				if (method_exists($stmt['products'], 'get_result')) {
					$result = $stmt['products']->get_result();
				} else {
					$result = $this->get_result($stmt['products']);
				}
			}

			/*
			if ('products' == '_') {
				$value = $result->fetch_all(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('products' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['products'][$row['']] = $values;

							$results['products'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('products' == '_') {
					$value = $result->fetch_all(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['products'] = $result->fetch_all(MYSQLI_ASSOC);
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'SELECT count(*) FROM (';

		$sql .= $this->sqlCount($prevSql, 'products.product_id', $this->prefix . 'product');
		$sql .= '
	
	) as count';

		if ($sql) {
			$stmt['count'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['count']) {
				if (method_exists($stmt['count'], 'get_result')) {
					$result = $stmt['count']->get_result();
				} else {
					$result = $this->get_result($stmt['count']);
				}
			}

			/*
			if ('count' == '_') {
				$value = $result->fetch_array(MYSQLI_NUM)[0];
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('count' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['count'][$row['']] = $values;

							$results['count'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('count' == '_') {
					$value = $result->fetch_array(MYSQLI_NUM)[0];

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['count'] = $result->fetch_array(MYSQLI_NUM)[0];
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}
}
