<?php
/*
 *   $Id$
 *
 *   AbanteCart, Ideal OpenSource Ecommerce Solution
 *   http://www.AbanteCart.com
 *
 *   Copyright © 2011-2026 Belavier Commerce LLC
 *
 *   This source file is subject to Open Software License (OSL 3.0)
 *   License details are bundled with this package in the file LICENSE.txt.
 *   It is also available at this URL:
 *   <http://www.opensource.org/licenses/OSL-3.0>
 *
 *  UPGRADE NOTE:
 *    Do not edit or add to this file if you wish to upgrade AbanteCart to newer
 *    versions in the future. If you wish to customize AbanteCart for your
 *    needs, please refer to http://www.AbanteCart.com for more information.
 */

/** @noinspection PhpMultipleClassDeclarationsInspection */

/** @noinspection PhpUndefinedClassInspection */
class ModelCatalogProduct extends Model
{
    protected $customer_group_id;
    public function __construct($registry)
    {
        parent::__construct($registry);
        //special prices
        if (is_object($this->customer) && $this->customer->isLogged()) {
            $this->customer_group_id = (int) $this->customer->getCustomerGroupId();
        } else {
            $this->customer_group_id = (int) $this->config->get('config_customer_group_id');
        }
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProduct($product_id)
    {
        if (!(int) $product_id) {
            return [];
        }
        $query = $this->db->query(
            "SELECT DISTINCT *,
                        pd.name AS name,
                        pd.blurb AS blurb,
                        m.name AS manufacturer,
                        ss.name AS stock_status,
                        stock_checkout,
                        lcd.unit as length_class_name, " .
            $this->_sql_avg_rating_string() . ", " .
            $this->_sql_final_price_string() . " " .
            $this->_sql_join_string() .
            " LEFT JOIN " . $this->db->table("length_class_descriptions") . " lcd
                ON (p.length_class_id = lcd.length_class_id
                    AND lcd.language_id = '" . (int) $this->config->get('storefront_language_id') . "')
            WHERE p.product_id = '" . (int) $product_id . "'
                    AND p2s.store_id = '" . (int) $this->config->get('config_store_id') . "'
                    AND p.date_available <= NOW() AND p.status = '1'"
        );
        return $query->row;
    }

    /**
     * Check if product or any option value require tracking stock subtract = 1
     *
     * @param int $product_id
     *
     * @return int
     * @throws AException
     */
    public function isStockTrackable($product_id)
    {
        if (!(int) $product_id) {
            return 0;
        }
        $track_status = 0;
        //check product option values
        $query = $this->db->query(
            "SELECT pov.product_option_value_id, pov.subtract AS subtract
            FROM " . $this->db->table("product_options") . " po
            LEFT JOIN " . $this->db->table("product_option_values") . " pov
                ON (po.product_option_id = pov.product_option_id)
            WHERE po.product_id = '" . (int) $product_id . "'  AND po.status = 1"
        );

        foreach ($query->rows as $row) {
            $track_status += (int) $row['subtract'];
        }
        //if no options - check whole product subtract
        if (!$track_status) {
            //check main product
            $query = $this->db->query(
                "SELECT subtract
                FROM " . $this->db->table("products") . " p
                WHERE p.product_id = '" . (int) $product_id . "'"
            );

            $track_status = (int) $query->row['subtract'];
        }
        return $track_status;
    }

    /**
     * Returns array with stock information
     *
     * @param array $product_ids
     *
     * @return array
     * @throws AException
     * @since 1.2.7
     *
     */
    public function getProductsStockInfo($product_ids = [])
    {
        if (!$product_ids || !is_array($product_ids)) {
            return [];
        }

        $ids = [];
        foreach ($product_ids as $id) {
            $id = (int) $id;
            if (!$id) {
                continue;
            }
            $ids[] = $id;
        }

        if (!$ids) {
            return [];
        }

        $cache_key = 'product.stock_info.' . md5(implode('', $ids));
        $cache = $this->cache->pull($cache_key);
        if ($cache !== false) {
            return $cache;
        }

        $sql = "SELECT p.product_id,
                        p.subtract,
                        SUM(COALESCE(pov.subtract,0)) as option_subtract,
                        p.quantity,
                        SUM(COALESCE(pov.quantity,0)) as option_quantity
                FROM " . $this->db->table("products") . " p
                LEFT JOIN " . $this->db->table("product_options") . " po
                    ON (po.product_id = p.product_id AND po.status = 1)
                LEFT JOIN  " . $this->db->table("product_option_values") . " pov
                    ON (po.product_option_id = pov.product_option_id)
                WHERE p.product_id IN (" . implode(', ', $ids) . ")
                GROUP BY p.product_id";
        $query = $this->db->query($sql);
        $output = [];
        foreach ($query->rows as $row) {
            $output[$row['product_id']] = [
                'subtract' => (((int) $row['subtract'] + (int) $row['option_subtract']) > 0), //boolean!
                'quantity' => ((int) $row['quantity'] + (int) $row['option_quantity']),
            ];
        }
        $this->cache->push($cache_key, $output);
        return $output;
    }

    /**
     *
     * Check if product or any option has any stock available
     *
     * @param int $product_id
     *
     * @return int
     * @throws AException
     */
    public function hasAnyStock($product_id)
    {
        if (!(int) $product_id) {
            return 0;
        }
        $trackable = false;
        $total_quantity = 0;
        //check product option values
        $query = $this->db->query(
            "SELECT pov.quantity AS quantity, pov.subtract
            FROM " . $this->db->table("product_options") . " po
            LEFT JOIN " . $this->db->table("product_option_values") . " pov
                ON (po.product_option_id = pov.product_option_id)
            WHERE po.product_id = '" . (int) $product_id . "' AND po.status = 1"
        );
        if ($query->num_rows) {
            foreach ($query->rows as $row) {
                //if tracking of stock disabled - set quantity as big
                if (!$row['subtract']) {
                    $total_quantity = true;
                    continue;
                } else {
                    $trackable = true;
                }
                //calculate only  no options without tracking
                if ($total_quantity !== true) {
                    $total_quantity += max($row['quantity'], 0);
                }
            }
            //if some of option value have subtract NO - think product is available
            if ($total_quantity == 0 && !$trackable) {
                $total_quantity = true;
            }
        }

        if (!$trackable) {
            //get product quantity without options
            $query = $this->db->query(
                "SELECT quantity, subtract
                FROM " . $this->db->table("products") . " p
                WHERE p.product_id = '" . (int) $product_id . "'"
            );
            if ($query->row['subtract']) {
                $total_quantity = (int) $query->row['quantity'];
            } else {
                $total_quantity = true;
            }
        }
        return $total_quantity;
    }

    public function getProductDataForCart($product_id)
    {
        if (!(int) $product_id) {
            return [];
        }
        $languageId = (int) $this->config->get('storefront_language_id');
        $query = $this->db->query(
            "SELECT p.*,
                    pd.name,
                    pd.meta_keywords,
                    pd.meta_description,
                    pd.description,
                    pd.blurb,
                    p2c.category_id,
                    wcd.unit AS weight_class,
                    mcd.unit AS length_class
            FROM " . $this->db->table("products") . " p
            LEFT JOIN " . $this->db->table("product_descriptions") . " pd
                ON (p.product_id = pd.product_id
                        AND pd.language_id = '" . $languageId . "')
            LEFT JOIN " . $this->db->table("products_to_categories") . " p2c 
                ON p2c.product_id = p.product_id
            LEFT JOIN " . $this->db->table("weight_classes") . " wc 
                ON (p.weight_class_id = wc.weight_class_id)
            LEFT JOIN " . $this->db->table("weight_class_descriptions") . " wcd
                ON (wc.weight_class_id = wcd.weight_class_id
                        AND wcd.language_id = '" . $languageId . "' )
            LEFT JOIN " . $this->db->table("length_classes") . " mc 
                ON (p.length_class_id = mc.length_class_id)
            LEFT JOIN " . $this->db->table("length_class_descriptions") . " mcd 
                ON (mc.length_class_id = mcd.length_class_id 
                            AND mcd.language_id = '" . $languageId . "' )
            WHERE p.product_id = '" . (int) $product_id . "' 
                AND p.date_available <= NOW() 
                AND p.status = '1'"
        );
        $output = [];
        foreach ($query->rows as $row) {
            if (isset($output['categories'])) {
                $output['categories'][] = (int) $row['category_id'];
                $row['categories'] = $output['categories'];
            } else {
                $row['categories'] = [
                    (int) $row['category_id'],
                ];
            }
            unset($row['category_id']);
            $output = $row;
        }

        return $output;
    }

    /**
     * @param int|array $category_id
     * @param array $data
     *
     * @return array
     * @throws AException
     */
    public function getProductsByCategoryId($category_id, $data)
    {
        if (!$category_id) {
            return [];
        }

        $filter = (array) $data['filter'];
        $sort = $data['sort'] ? : 'p.sort_order';
        $order = $data['order'] ? : 'ASC';
        $start = abs((int) $data['start']);
        $limit = abs((int) $data['limit'] ? : 20);
        $store_id = (int) $this->config->get('config_store_id');
        $language_id = (int) $this->config->get('storefront_language_id');
        $cache_key = 'product.listing.products_category.'
            . md5(var_export(func_get_args(), true)
            . $this->customer_group_id
            . $store_id
            . $language_id);
        $cache = $this->cache->pull($cache_key);
        if ($cache === false) {
            $sql = "SELECT DISTINCT " . $this->db->getSqlCalcTotalRows() . " p.*, 
                        p.product_id,
                        " . $this->_sql_final_price_string() . ",
                        pd.name AS name, 
                        pd.blurb,
                        m.name AS manufacturer,
                        ss.name AS stock,
                        " . $this->_sql_avg_rating_string() . ",
                        " . $this->_sql_review_count_string() . "
                        " . $this->_sql_join_string() . "
            LEFT JOIN " . $this->db->table("products_to_categories") . " p2c
                ON (p.product_id = p2c.product_id)";

            $sql .= "WHERE p.status = '1' AND p.date_available <= NOW()
                    AND p2s.store_id = '" . $store_id . "' ";
            $sql .= $this->_filter_by_rating($filter);

            if (is_array($category_id)) {
                $ids = filterIntegerIdList($category_id) ? : [0];
                if ($ids && $ids != [0]) {
                    $sql .= " AND p2c.category_id IN (" . implode(',', $ids) . ")";
                }
            } else {
                $sql .= " AND p2c.category_id = '" . (int) $category_id . "'";
            }
            if ($filter['manufacturer_id']) {
                $ids = array_map('intval', array_filter((array) $filter['manufacturer_id']));
                if ($ids) {
                    $sql .= " AND p.manufacturer_id IN (" . implode(',', $ids) . ")";
                }
            }

            $sort_data = [
                'pd.name'       => 'LCASE(pd.name)',
                'p.sort_order'  => 'p.sort_order',
                'price'         => 'final_price',
                'p.price'       => 'final_price',
                'special'       => 'final_price',
                'rating'        => 'rating',
                'date_modified' => 'p.date_modified',
                'review'        => 'review',
            ];

            if (isset($sort) && in_array($sort, array_keys($sort_data))) {
                $sql .= " ORDER BY " . $sort_data[$sort];
            } else {
                $sql .= " ORDER BY p.sort_order";
            }

            if ($order == 'DESC') {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }

            if ($start < 0) {
                $start = 0;
            }

            $sql .= " LIMIT " . (int) $start . "," . (int) $limit;
            $query = $this->db->query($sql);
            if ($query->rows) {
                $query->rows[0]['total_num_rows'] = $this->db->getTotalNumRows();
            }
            $cache = $query->rows;
            $this->cache->push($cache_key, $cache);
        }

        return $cache;
    }

    /**
     * @param int|array $category_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalProductsByCategoryId(int|array $category_id = 0)
    {
        $store_id = (int) $this->config->get('config_store_id');

        $cache_key = 'product.listing.products_by_category.'
            . md5(var_export($category_id, true))
            . '.store_' . $store_id;
        $cache = $this->cache->pull($cache_key);
        if ($cache === false) {
            $sql = "SELECT COUNT(*) AS total
                FROM " . $this->db->table("products_to_categories") . " p2c
                LEFT JOIN " . $this->db->table("products") . " p 
                    ON (p2c.product_id = p.product_id)
                LEFT JOIN " . $this->db->table("products_to_stores") . " p2s 
                    ON (p.product_id = p2s.product_id)
                WHERE p.status = '1'
                    AND p.date_available <= NOW()
                    AND p2s.store_id = '" . $store_id . "'";

            if (is_array($category_id)) {
                $ids = filterIntegerIdList($category_id) ? : [0];
                $sql .= " AND p2c.category_id IN (" . implode(',', $ids) . ")";
            } else {
                $sql .= " AND p2c.category_id = '" . $category_id . "'";
            }

            $query = $this->db->query($sql);
            $cache = $query->row['total'];
            $this->cache->push($cache_key, $cache);
        }

        return $cache;
    }

    /**
     * @param int|array $manufacturer_id
     * @param array $data
     *
     * @return array
     * @throws AException
     */
    public function getProductsByManufacturerId($manufacturer_id, $data)
    {
        extract($data);
        $filter = (array) $data['filter'];
        $sort = $data['sort'] ? : 'p.sort_order';
        $order = $data['order'] ? : 'ASC';
        $start = abs((int) $data['start']);
        $limit = abs((int) $data['limit'] ? : 20);
        $store_id = (int) $this->config->get('config_store_id');
        $cache_key = 'product.listing.brands.' . md5(var_export(func_get_args(), true))
            . $this->customer_group_id;
        $cache = $this->cache->pull($cache_key);
        if ($cache !== false) {
            return $cache;
        }
        $sql = "SELECT DISTINCT " . $this->db->getSqlCalcTotalRows() . " p.*, 
                    " . $this->_sql_final_price_string() . ",
                    pd.name AS name, 
                    pd.blurb,
                    m.name AS manufacturer,
                    ss.name AS stock,
                    " . $this->_sql_avg_rating_string() . ",
                    " . $this->_sql_review_count_string() . "
                    " . $this->_sql_join_string() . "
        LEFT JOIN " . $this->db->table("products_to_categories") . " p2c
            ON (p.product_id = p2c.product_id)
        WHERE p.status = '1' AND p.date_available <= NOW()
            AND p2s.store_id = '" . $store_id . "'";

        if (is_array($manufacturer_id)) {
            $ids = filterIntegerIdList($manufacturer_id);
            if ($ids) {
                $sql .= " AND m.manufacturer_id IN (" . implode(',', $ids) . ")";
            }
        } else {
            $sql .= " AND m.manufacturer_id = '" . (int) $manufacturer_id . "'";
        }

        if (isset($filter['category_id'])) {
            if (is_array($filter['category_id'])) {
                $ids = filterIntegerIdList($filter['category_id']) ? : [0];
                if ($ids) {
                    $sql .= " AND p2c.category_id IN (" . implode(',', $ids) . ")";
                }
            } else {
                $sql .= " AND p2c.category_id = '" . (int) $filter['category_id'] . "'";
            }
        }
        if (isset($filter['rating'])) {
            $sql .= " AND ( SELECT FLOOR(AVG(r.rating))
                         FROM " . $this->db->table("reviews") . " r
                         WHERE p.product_id = r.product_id AND status = 1
                         GROUP BY r.product_id 
                 ) ";
            if (is_array($filter['rating'])) {
                $ids = array_map('intval', array_filter($filter['rating']));
                if ($ids) {
                    $sql .= " IN (" . implode(',', $ids) . ")";
                }
            } else {
                $sql .= " = " . (int) $filter['rating'];
            }
        }

        $sort_data = [
            'pd.name'       => 'LCASE(pd.name)',
            'p.sort_order'  => 'p.sort_order',
            'p.price'       => 'final_price',
            'special'       => 'final_price',
            'rating'        => 'rating',
            'date_modified' => 'p.date_modified',
            'review'        => 'review',
        ];

        if (isset($sort) && in_array($sort, array_keys($sort_data))) {
            $sql .= " ORDER BY " . $sort_data[$sort];
        } else {
            $sql .= " ORDER BY p.sort_order";
        }

        if ($order == 'DESC') {
            $sql .= " DESC";
        } else {
            $sql .= " ASC";
        }

        if ($start < 0) {
            $start = 0;
        }

        $sql .= " LIMIT " . (int) $start . "," . (int) $limit;
        $query = $this->db->query($sql);
        if ($query->rows) {
            $query->rows[0]['total_num_rows'] = $this->db->getTotalNumRows();
        }
        $this->cache->push($cache_key, $query->rows);
        return $query->rows;
    }

    /**
     * @param int $manufacturer_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalProductsByManufacturerId($manufacturer_id = 0)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("products") . "
            WHERE status = '1'
                AND date_available <= NOW()
                AND manufacturer_id = '" . (int) $manufacturer_id . "'"
        );
        return (int) $query->row['total'];
    }

    /**
     * TODO: really needed?
     *
     * @param string $tag
     * @param int $category_id
     * @param string $sort
     * @param string $order
     * @param int $start
     * @param int $limit
     *
     * @return array
     * @throws AException
     */
    public function getProductsByTag(
        $tag,
        $category_id = 0,
        $sort = 'p.sort_order',
        $order = 'ASC',
        $start = 0,
        $limit = 20
    ) {
        $start = abs((int) $start);
        $limit = abs((int) $limit);
        if ($tag) {
            $sql = "SELECT *, p.product_id,
                            " . $this->_sql_final_price_string() . ",
                            pd.name AS name, 
                            m.name AS manufacturer,
                            ss.name AS stock,
                            " . $this->_sql_avg_rating_string() . ",
                            " . $this->_sql_review_count_string() . "
                            " . $this->_sql_join_string() . "
                    LEFT JOIN " . $this->db->table("product_tags") . " pt 
                        ON (p.product_id = pt.product_id 
                            AND pt.language_id = '" . (int) $this->config->get('storefront_language_id') . "')
                    WHERE p2s.store_id = '" . (int) $this->config->get('config_store_id') . "'
                        AND (LCASE(pt.tag) = '" . $this->db->escape(mb_strtolower($tag)) . "'";

            $keywords = explode(" ", $tag);

            foreach ($keywords as $keyword) {
                $sql .= " OR LCASE(pt.tag) = '" . $this->db->escape(mb_strtolower($keyword)) . "'";
            }
            $sql .= ")";

            if ($category_id) {
                $data = [];

                foreach (explode(',', $category_id) as $category_id) {
                    $data[] = "'" . (int) $category_id . "'";
                }

                $sql .= " AND p.product_id IN (SELECT product_id
                                                FROM " . $this->db->table("products_to_categories") . "
                                                WHERE category_id IN (" . implode(",", $data) . "))";
            }

            $sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";

            $sort_data = [
                'pd.name'       => 'LCASE(pd.name)',
                'p.sort_order'  => 'p.sort_order',
                'p.price'       => 'final_price',
                'special'       => 'final_price',
                'rating'        => 'rating',
                'date_modified' => 'p.date_modified',
                'review'        => 'review',
            ];

            if (isset($sort) && in_array($sort, array_keys($sort_data))) {
                $sql .= " ORDER BY " . $sort_data[$sort];
            } else {
                $sql .= " ORDER BY p.sort_order";
            }

            if ($order == 'DESC') {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }

            if ($start < 0) {
                $start = 0;
            }

            $sql .= " LIMIT " . (int) $start . "," . (int) $limit;

            $query = $this->db->query($sql);

            $products = [];

            foreach ($query->rows as $value) {
                $products[$value['product_id']] = $this->getProduct($value['product_id']);
            }

            return $products;
        }
        return [];
    }

    /**
     * @param array $data
     *
     * @return array
     * @throws AException
     */
    public function getFilteredProducts($data)
    {
        $keyword = trim((string) $data['filter']['keyword']);
        if (!$keyword) {
            return [];
        }
        $categoryIds = filterIntegerIdList((array) $data['filter']['category_id']);
        if ($categoryIds) {
            /** @var ModelCatalogCategory $mdl */
            $mdl = $this->load->model('catalog/category');
            foreach ($categoryIds as $catId) {
                $categoryIds = array_merge($mdl->getChildrenIDs($catId), $categoryIds);
            }
        }

        $cacheKey = 'product.search.results'
            . $this->customer_group_id
            . (int) $this->config->get('storefront_language_id')
            . md5(var_export($data, true)
            . var_export($categoryIds, true));
        $output = $this->cache->pull($cacheKey);
        if ($output !== false) {
            return $output;
        }

        $manufacturerIds = filterIntegerIdList((array) $data['filter']['manufacturer_id']);
        $rating = filterIntegerIdList((array) $data['filter']['rating']);
        $description = (bool) $data['filter']['description'];
        $model = (bool) $data['filter']['model'];

        $sort = $data['sort'] ? : 'p.sort_order';
        $order = strtoupper($data['order']) == 'ASC' ? 'ASC' : 'DESC';
        $start = max((int) $data['start'], 0);
        $limit = abs((int) $data['limit']) ? : 20;

        $sql = "SELECT " . $this->db->getSqlCalcTotalRows() . " DISTINCT  p.*, 
                        p.product_id,  
                        " . $this->_sql_final_price_string() . ",
                        pd.name AS name, 
                        pd.blurb,
                        m.name AS manufacturer,
                        ss.name AS stock,
                        " . $this->_sql_avg_rating_string() . ",
                        " . $this->_sql_review_count_string() . "
                        " . $this->_sql_join_string() . "
        LEFT JOIN " . $this->db->table("product_tags") . " pt 
            ON (p.product_id = pt.product_id)
        WHERE p2s.store_id = '" . (int) $this->config->get('config_store_id') . "' ";

        $tags = explode(' ', trim($keyword));
        $tags_str = [];
        if (sizeof($tags) > 1) {
            $tags_str[] = " LCASE(pt.tag) = '" . $this->db->escape(trim($keyword)) . "' ";
        }
        foreach ($tags as $tag) {
            $tags_str[] = " LCASE(pt.tag) = '" . $this->db->escape(mb_strtolower($tag)) . "' ";
        }

        if (!$description) {
            $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(mb_strtolower($keyword), true) . "%' OR "
                . implode(' OR ', $tags_str);
        } else {
            $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(mb_strtolower($keyword), true) . "%'
                            OR " . implode(' OR ', $tags_str) . "
                            OR LCASE(pd.description) LIKE '%" . $this->db->escape(mb_strtolower($keyword), true) . "%'
                            OR LCASE(pd.blurb) LIKE '%" . $this->db->escape(mb_strtolower($keyword), true) . "%'";
        }

        if (!$model) {
            $sql .= ")";
        } else {
            $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(mb_strtolower($keyword), true) . "%')";
        }

        if ($categoryIds) {
            $sql .= " AND p.product_id IN (SELECT product_id
                                            FROM " . $this->db->table("products_to_categories") . "
                                            WHERE category_id IN (" . implode(", ", $categoryIds) . ")) ";
        }
        if ($manufacturerIds) {
            $sql .= " AND p.manufacturer_id IN (" . implode(", ", $manufacturerIds) . ") ";
        }
        if ($rating) {
            $sql .= " AND " . $this->_sql_avg_rating_string(true) . " IN (" . implode(", ", $rating) . ") ";
        }

        $sql .= " AND p.status = '1' AND p.date_available <= NOW()
                 GROUP BY p.product_id";

        $sort_data = [
            'pd.name'       => 'LCASE(pd.name)',
            'p.sort_order'  => 'p.sort_order',
            'p.price'       => 'final_price',
            'special'       => 'final_price',
            'rating'        => 'rating',
            'date_modified' => 'p.date_modified',
            'review'        => 'review',
        ];

        $sql .= " ORDER BY " . ($sort_data[$sort] ? : 'p.sort_order') . " " . $order;
        $sql .= " LIMIT " . (int) $start . "," . (int) $limit;

        $query = $this->db->query($sql);
        $output = [];
        if ($query->num_rows) {
            $total_num_rows = $this->db->getTotalNumRows();
            foreach ($query->rows as $value) {
                $value['total_num_rows'] = $total_num_rows;
                $output[$value['product_id']] = $value;
            }
        }
        $this->cache->push($cacheKey, $output);
        return $output;
    }

    /**
     * @param string $tag
     * @param int $category_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalProductsByTag($tag, $category_id = 0)
    {
        $tag = trim($tag);
        if ($tag) {
            $language_id = (int) $this->config->get('storefront_language_id');
            $sql = "SELECT COUNT(DISTINCT p.product_id) AS total
                    FROM " . $this->db->table("products") . " p
                    LEFT JOIN " . $this->db->table("product_descriptions") . " pd 
                        ON (p.product_id = pd.product_id AND pd.language_id = '" . $language_id . "')
                    LEFT JOIN " . $this->db->table("product_tags") . " pt 
                        ON (p.product_id = pt.product_id AND pt.language_id = '" . $language_id . "')
                    LEFT JOIN " . $this->db->table("products_to_stores") . " p2s 
                        ON (p.product_id = p2s.product_id)
                    LEFT JOIN " . $this->db->table("manufacturers") . " m 
                        ON (p.manufacturer_id = m.manufacturer_id)
                    WHERE p2s.store_id = '" . (int) $this->config->get('config_store_id') . "'
                        AND (LCASE(pt.tag) = '" . $this->db->escape(mb_strtolower($tag)) . "'";

            $keywords = explode(" ", $tag);

            foreach ($keywords as $keyword) {
                $sql .= " OR LCASE(pt.tag) = '" . $this->db->escape(mb_strtolower($keyword)) . "'";
            }
            $sql .= ")";

            if ($category_id) {
                $data = [];

                $this->load->model('catalog/category');

                $string = rtrim($this->getPath($category_id), ',');
                $category_ids = explode(',', $string);

                foreach ($category_ids as $category_id) {
                    $data[] = "category_id = '" . (int) $category_id . "'";
                }
                $sql .= " AND p.product_id IN (SELECT product_id 
                                                FROM " . $this->db->table("products_to_categories") . " 
                                                WHERE " . implode(" OR ", $data) . ")";
            }
            $sql .= " AND p.status = '1' AND p.date_available <= NOW()";
            $query = $this->db->query($sql);

            if ($query->num_rows) {
                return $query->row['total'];
            }
        }
        return 0;
    }

    /**
     * @param int $category_id
     *
     * @return string
     * @throws AException
     */
    public function getPath($category_id)
    {
        $string = $category_id . ',';
        $results = $this->model_catalog_category->getCategories((int) $category_id);
        foreach ($results as $result) {
            $string .= $this->getPath($result['category_id']);
        }
        return $string;
    }

    /**
     * @param int $limit
     *
     * @return array
     * @throws AException
     */
    public function getLatestProducts($limit)
    {
        $limit = abs((int) $limit);
        $storeId = (int) $this->config->get('config_store_id');
        $cache_key = 'product.latest.'
            . $this->customer_group_id
            . $limit
            . '.store_' . $storeId
            . '_lang_' . $this->config->get('storefront_language_id');
        $cache = $this->cache->pull($cache_key);

        if ($cache === false) {
            $sql = "SELECT *,
                        pd.name AS name,
                        m.name AS manufacturer,
                        ss.name AS stock,
                        pd.blurb,
                        " . $this->_sql_final_price_string() . ",
                        " . $this->_sql_avg_rating_string() . ",
                        " . $this->_sql_review_count_string() . "
                        " . $this->_sql_join_string() . "
                    WHERE p.status = '1'
                            AND p.date_available <= NOW()
                            AND p2s.store_id = '" . $storeId . "'
                    ORDER BY p.date_added DESC";

            if ((int) $limit) {
                $sql .= " LIMIT " . (int) $limit;
            }

            $query = $this->db->query($sql);
            $cache = $query->rows;
            $this->cache->push($cache_key, $cache);
        }

        return $cache;
    }

    /**
     * @param int $limit
     *
     * @return array
     * @throws AException
     */
    public function getPopularProducts($limit = 0)
    {
        $limit = abs((int) $limit);
        $storeId = (int) $this->config->get('config_store_id');
        $cacheKey = 'product.popular.'
            . $this->customer_group_id
            . $limit
            . '.store_' . $storeId
            . '_lang_' . $this->config->get('storefront_language_id');
        $output = $this->cache->pull($cacheKey);
        if ($output !== false) {
            return $output;
        }

        $sql = "SELECT *,
                        pd.name AS name,
                        m.name AS manufacturer,
                        ss.name AS stock,
                        " . $this->_sql_avg_rating_string() . ",
                        " . $this->_sql_review_count_string() . "
                        " . $this->_sql_join_string() . "
                WHERE p.status = '1'
                        AND p.date_available <= NOW()
                        AND p2s.store_id = '" . $storeId . "'
                ORDER BY p.viewed DESC, p.date_added DESC";

        if ((int) $limit) {
            $sql .= " LIMIT " . (int) $limit;
        }
        $query = $this->db->query($sql);
        $output = $query->rows;
        $this->cache->push($cacheKey, $output);
        return $output;
    }

    /**
     * @param $limit
     *
     * @return array
     * @throws AException
     */
    public function getFeaturedProducts($limit)
    {
        $limit = abs((int) $limit);
        $language_id = (int) $this->config->get('storefront_language_id');
        $store_id = (int) $this->config->get('config_store_id');
        $cacheKey = 'product.featured.'
            . $limit
            . $this->customer_group_id
            . '.store_' . $store_id
            . '_lang_' . $language_id;
        $product_data = $this->cache->pull($cacheKey);
        if ($product_data === false) {
            $sql = "SELECT f.*, pd.*, ss.name AS stock, p.*
                    FROM " . $this->db->table("products_featured") . " f
                    LEFT JOIN " . $this->db->table("products") . " p
                        ON (f.product_id = p.product_id)
                    LEFT JOIN " . $this->db->table("product_descriptions") . " pd
                        ON (f.product_id = pd.product_id AND pd.language_id = '" . $language_id . "')
                    LEFT JOIN " . $this->db->table("products_to_stores") . " p2s 
                        ON (p.product_id = p2s.product_id)
                    LEFT JOIN " . $this->db->table("stock_statuses") . " ss 
                        ON (p.stock_status_id = ss.stock_status_id AND ss.language_id = '" . $language_id . "')
                    WHERE p2s.store_id = '" . $store_id . "'
                        AND p.status='1'
                        AND p.date_available <= NOW()
                    ORDER BY p.sort_order, p.date_available DESC ";

            if ((int) $limit) {
                $sql .= " LIMIT " . (int) $limit;
            }

            $query = $this->db->query($sql);
            $product_data = $query->rows;
            $this->cache->push($cacheKey, $product_data);
        }
        return $product_data;
    }

    /**
     * @param $limit
     *
     * @return array
     * @throws AException
     */
    public function getBestSellerProducts($limit)
    {
        $limit = abs((int) $limit);
        $language_id = (int) $this->config->get('storefront_language_id');
        $store_id = (int) $this->config->get('config_store_id');
        $cache_key = 'product.bestseller.'
            . $this->customer_group_id
            . $limit
            . '.store_' . $store_id
            . '_lang_' . $language_id;

        $product_data = $this->cache->pull($cache_key);
        if ($product_data === false) {
            $product_data = [];

            $sql = "SELECT op.product_id, SUM(op.quantity) AS total
                    FROM " . $this->db->table("order_products") . " op
                    LEFT JOIN `" . $this->db->table("orders") . "` o 
                        ON (op.order_id = o.order_id)
                    LEFT JOIN " . $this->db->table("products") . " p 
                        ON p.product_id = op.product_id
                    WHERE o.order_status_id > '0' AND p.status = '1' AND p.date_available <= NOW()
                    GROUP BY op.product_id
                    ORDER BY total DESC";
            if ((int) $limit) {
                $sql .= " LIMIT " . (int) $limit;
            }
            $query = $this->db->query($sql);

            if ($query->num_rows) {
                $products = [];
                foreach ($query->rows as $result) {
                    $products[] = (int) $result['product_id'];
                }

                if ($products) {
                    $sql = "SELECT pd.*, ss.name AS stock, p.*,"
                                  . $this->_sql_final_price_string() . ",
                                " . $this->_sql_avg_rating_string() . ",
                                " . $this->_sql_review_count_string() . "
                            FROM " . $this->db->table("products") . " p
                            LEFT JOIN " . $this->db->table("product_descriptions") . " pd
                                ON (p.product_id = pd.product_id AND pd.language_id = '" . $language_id . "')
                            LEFT JOIN " . $this->db->table("products_to_stores") . " p2s
                                ON (p.product_id = p2s.product_id)
                            LEFT JOIN " . $this->db->table("stock_statuses") . " ss
                                ON (p.stock_status_id = ss.stock_status_id AND ss.language_id = '" . $language_id . "')
                            WHERE p.product_id IN (" . implode(', ', $products) . ")
                                AND p.status = '1' AND p.date_available <= NOW()
                                AND p2s.store_id = '" . $store_id . "'";
                    $product_query = $this->db->query($sql);

                    if ($product_query->num_rows) {
                        $data = [];
                        foreach ($product_query->rows as $result) {
                            $data[$result['product_id']] = $result;
                        }
                        // resort by totals
                        foreach ($products as $id) {
                            if (isset($data[$id])) {
                                $product_data[] = $data[$id];
                            }
                        }
                    }
                }
            }

            $this->cache->push($cache_key, $product_data);
        }

        return $product_data;
    }

    /**
     * Update view count. Do not update modification date. See lat
     *
     * @param int $product_id
     *
     * @return null
     * @throws AException
     */
    public function updateViewed($product_id)
    {
        if (empty($product_id)) {
            return false;
        }

        $this->db->query(
            "UPDATE " . $this->db->table("products") . "
            SET viewed = viewed + 1,
                date_modified = date_modified 
            WHERE product_id = '" . (int) $product_id . "'"
        );
        return true;
    }

    /**
     * @param int $product_id
     * @param int $status
     *
     * @return null
     * @throws AException
     */
    public function updateStatus($product_id, $status = 0)
    {
        if (!$product_id) {
            return false;
        }
        $this->db->query(
            "UPDATE " . $this->db->table("products") . "
            SET status = " . (int) $status . "
            WHERE product_id = '" . (int) $product_id . "'"
        );
        $this->cache->remove(['product', 'category', 'collection']);
        return true;
    }

    /**
     * check if product option is group option
     * if yes, return array of possible groups for option_value_id
     *
     * @param $product_id
     * @param $option_id
     * @param $option_value_id
     *
     * @return array
     * @throws AException
     */
    public function getProductGroupOptions($product_id, $option_id, $option_value_id)
    {
        if (empty($product_id) || empty($option_id)) {
            return [];
        }
        $product_option = $this->db->query(
            "SELECT group_id 
            FROM " . $this->db->table("product_options") . "
            WHERE status=1 AND product_id = '" . (int) $product_id . "'
                AND product_option_id = '" . (int) $option_id . "' "
        );
        if (!$product_option->row['group_id']) {
            return [];
        }
        //get all option values of group
        $option_values = $this->db->query(
            "SELECT pov.*, povd.name
            FROM " . $this->db->table("product_options") . " po
            LEFT JOIN " . $this->db->table("product_option_values") . " pov 
                ON (po.product_option_id = pov.product_option_id)
            LEFT JOIN  " . $this->db->table("product_option_value_descriptions") . " povd
                ON (pov.product_option_value_id = povd.product_option_value_id 
                        AND povd.language_id = '" . (int) $this->config->get('storefront_language_id') . "' )
            WHERE po.status = 1 AND po.group_id = '" . (int) $product_option->row['group_id'] . "'
            ORDER BY pov.sort_order "
        );

        //find attribute_value_id of option_value
        //find all option values with attribute_value_id
        //for each option values find group id
        //add each group values to result array
        $result = [];
        $attribute_value_id = null;
        foreach ($option_values->rows as $row) {
            if ($row['product_option_value_id'] == $option_value_id) {
                $attribute_value_id = $row['attribute_value_id'];
                break;
            }
        }
        $groups = [];
        foreach ($option_values->rows as $row) {
            if ($row['attribute_value_id'] == $attribute_value_id) {
                $groups[] = $row['group_id'];
            }
        }
        $groups = array_unique($groups);
        foreach ($groups as $group_id) {
            foreach ($option_values->rows as $row) {
                if ($row['group_id'] == $group_id && $row['product_option_id'] != $option_id) {
                    $result[$row['product_option_id']][$row['product_option_value_id']] = [
                        'name'   => $row['name'],
                        'price'  => $row['price'],
                        'prefix' => $row['prefix'],
                    ];
                }
            }
        }

        return $result;
    }

    /**
     * Quick check if there are any options for the product
     *
     * @param int $product_id
     *
     * @return boolean
     * @throws AException
     */
    public function hasAnyOptions($product_id)
    {
        if (!(int) $product_id) {
            return null;
        }
        $query = $this->db->query(
            "SELECT count(*) as total 
            FROM " . $this->db->table("product_options") . " 
            WHERE status = 1 AND product_id = '" . (int) $product_id . "'"
        );
        if ($query->row['total'] > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductOptions($product_id)
    {
        if (!(int) $product_id) {
            return [];
        }
        $language_id = (int) $this->config->get('storefront_language_id');
        $cache_key = 'product.options.' . $product_id . '.lang_' . $language_id;
        $product_option_data = $this->cache->pull($cache_key);
        $elements = HtmlElementFactory::getAvailableElements();
        if ($product_option_data === false) {
            $product_option_data = [];
            $product_option_query = $this->db->query(
                "SELECT po.*, pod.option_placeholder, pod.error_text
                FROM " . $this->db->table("product_options") . " po
                LEFT JOIN " . $this->db->table("product_option_descriptions") . " pod
                    ON pod.product_option_id = po.product_option_id 
                        AND pod.language_id =  '" . $language_id . "'
                WHERE po.product_id = '" . (int) $product_id . "'
                    AND po.group_id = 0
                    AND po.status = 1
                ORDER BY po.sort_order"
            );
            if ($product_option_query) {
                foreach ($product_option_query->rows as $product_option) {
                    $attribute_values = [];
                    $product_option_value_data = [];
                    $product_option_value_query = $this->db->query(
                        "SELECT *
                        FROM " . $this->db->table("product_option_values") . "
                        WHERE product_option_id = '" . (int) $product_option['product_option_id'] . "'
                        ORDER BY sort_order"
                    );
                    if ($product_option_value_query) {
                        foreach ($product_option_value_query->rows as $product_option_value) {
                            if ($product_option_value['attribute_value_id']) {
                                //skip duplicate attributes values if it is not grouped parent/child
                                if (!$product_option_value['grouped_attribute_data']
                                    && in_array($product_option_value['attribute_value_id'], $attribute_values)) {
                                    continue;
                                }
                                $attribute_values[] = $product_option_value['attribute_value_id'];
                            }
                            $pd_opt_val_description_qr = $this->db->query(
                                "SELECT *
                                FROM " . $this->db->table("product_option_value_descriptions") . "
                                WHERE product_option_value_id = '"
                                . (int) $product_option_value['product_option_value_id'] . "'
                                    AND language_id = '" . $language_id . "'"
                            );

                            // ignore option value with 0 quantity and disabled subtract
                            if ((!$product_option_value['subtract'])
                                || (!$this->config->get('config_nostock_autodisable'))
                                || ($product_option_value['quantity'] && $product_option_value['subtract'])
                            ) {
                                $product_option_value_data[$product_option_value['product_option_value_id']] = [
                                    'product_option_value_id' => $product_option_value['product_option_value_id'],
                                    'attribute_value_id'      => $product_option_value['attribute_value_id'],
                                    'grouped_attribute_data'  => $product_option_value['grouped_attribute_data'],
                                    'group_id'                => $product_option_value['group_id'],
                                    'name'                    => $pd_opt_val_description_qr->row['name'],
                                    'option_placeholder'      => $product_option['option_placeholder'],
                                    'regexp_pattern'          => $product_option['regexp_pattern'],
                                    'error_text'              => $product_option['error_text'],
                                    'settings'                => $product_option['settings'],
                                    'children_options_names'  => $pd_opt_val_description_qr->row['children_options_names'],
                                    'txt_id'                  => $product_option_value['txt_id'],
                                    'sku'                     => $product_option_value['sku'],
                                    'price'                   => $product_option_value['price'],
                                    'prefix'                  => $product_option_value['prefix'],
                                    'weight'                  => $product_option_value['weight'],
                                    'weight_type'             => $product_option_value['weight_type'],
                                    'quantity'                => $product_option_value['quantity'],
                                    'subtract'                => $product_option_value['subtract'],
                                    'default'                 => $product_option_value['default'],
                                ];
                            }
                        }
                    }
                    $prd_opt_description_qr = $this->db->query(
                        "SELECT *
                        FROM " . $this->db->table("product_option_descriptions") . "
                        WHERE product_option_id = '" . (int) $product_option['product_option_id'] . "'
                            AND language_id = '" . $language_id . "'"
                    );

                    $product_option_data[$product_option['product_option_id']] = [
                        'product_option_id'  => $product_option['product_option_id'],
                        'attribute_id'       => $product_option['attribute_id'],
                        'group_id'           => $product_option['group_id'],
                        'name'               => $prd_opt_description_qr->row['name'],
                        'option_placeholder' => $product_option['option_placeholder'],
                        'option_value'       => $product_option_value_data,
                        'sort_order'         => $product_option['sort_order'],
                        'element_type'       => $product_option['element_type'],
                        'html_type'          => $elements[$product_option['element_type']]['type'],
                        'required'           => $product_option['required'],
                        'regexp_pattern'     => $product_option['regexp_pattern'],
                        'error_text'         => $product_option['error_text'],
                        'settings'           => $product_option['settings'],
                    ];
                }
            }

            $this->cache->push($cache_key, $product_option_data);
        }
        return $product_option_data;
    }

    /**
     * @param array $products
     *
     * @return array|mixed
     * @throws AException
     */
    public function getProductsFromIDs($products)
    {
        if (!$products) {
            return [];
        }
        $cacheKey = 'product.productsFromIDs_' . implode('_', $products);
        $cachedData = $this->cache->pull($cacheKey);
        if ($cachedData !== false) {
            return $cachedData;
        }
        $sql = "SELECT *
                FROM " . $this->db->table("products") . " p
                LEFT JOIN " . $this->db->table("product_descriptions") . " pd 
                    ON (p.product_id = pd.product_id 
                        AND pd.language_id = '" . (int) $this->config->get('storefront_language_id') . "')
                LEFT JOIN " . $this->db->table("products_to_stores") . " p2s 
                    ON (p.product_id = p2s.product_id)
                WHERE p2s.store_id = '" . (int) $this->config->get('config_store_id') . "'
                        AND p.product_id IN (" . implode(', ', $products) . ")
                        AND p.status='1'
                        AND p.date_available <= NOW()
                ORDER BY p.sort_order, p.date_available DESC";
        $query = $this->db->query($sql);
        $result = $query->rows;
        $this->cache->push($cacheKey, $result);
        return $result;
    }

    /**
     * @param int $product_id
     * @param int $product_option_id
     *
     * @return array
     * @throws AException
     */
    public function getProductOption($product_id, $product_option_id)
    {
        if (!(int) $product_id || !(int) $product_option_id) {
            return [];
        }

        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_options") . " po
            LEFT JOIN " . $this->db->table("product_option_descriptions") . " pod 
                ON (po.product_option_id = pod.product_option_id)
            WHERE po.status=1 AND po.product_option_id = '" . (int) $product_option_id . "'
                AND po.product_id = '" . (int) $product_id . "'
                AND pod.language_id = '" . (int) $this->config->get('storefront_language_id') . "'
            ORDER BY po.sort_order"
        );
        return $query->row;
    }

    /**
     * @param $product_id
     * @param $product_option_id
     *
     * @return array
     * @throws AException
     */
    public function getProductOptionValues($product_id, $product_option_id)
    {
        if (!(int) $product_id || !(int) $product_option_id) {
            return [];
        }
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_option_values") . " pov
            WHERE pov.product_option_id = '" . (int) $product_option_id . "'
                AND pov.product_id = '" . (int) $product_id . "'
            ORDER BY pov.sort_order"
        );
        return $query->rows;
    }

    /**
     * @param int $product_id
     * @param int $product_option_value_id
     *
     * @return array
     * @throws AException
     */
    public function getProductOptionValue($product_id, $product_option_value_id)
    {
        if (!(int) $product_id || !(int) $product_option_value_id) {
            return [];
        }

        $query = $this->db->query(
            "SELECT *,
                    COALESCE(povd.product_id,povd2.product_id) as product_id,
                    COALESCE(povd.language_id,povd2.language_id) as language_id,
                    COALESCE(povd.product_option_value_id,povd2.product_option_value_id) as product_option_value_id,
                    COALESCE(povd.name,povd2.name) as name
            FROM " . $this->db->table("product_option_values") . " pov
            LEFT JOIN " . $this->db->table("product_option_value_descriptions") . " povd
                    ON (pov.product_option_value_id = povd.product_option_value_id
                            AND povd.language_id = '" . (int) $this->config->get('storefront_language_id') . "' )
            LEFT JOIN " . $this->db->table("product_option_value_descriptions") . " povd2
                    ON (pov.product_option_value_id = povd2.product_option_value_id
                            AND povd2.language_id = '1' )
            WHERE pov.product_option_value_id = '" . (int) $product_option_value_id . "'
                AND pov.product_id = '" . (int) $product_id . "'
            ORDER BY pov.sort_order"
        );
        return $query->row;
    }

    /**
     * Check if any of input options are required and provided
     *
     * @param int $product_id
     * @param array $input_options
     *
     * @return array
     * @throws AException
     */
    public function validateProductOptions($product_id, $input_options)
    {
        $errors = [];
        if (empty($product_id) && empty($input_options)) {
            return [];
        }
        $product_options = $this->getProductOptions($product_id);
        if (is_array($product_options) && $product_options) {
            $this->load->language('checkout/cart');
            foreach ($product_options as $option) {
                if ($option['required']) {
                    if (empty($input_options[$option['product_option_id']])) {
                        $errors[$option['product_option_id']] =
                            $option['name'] . ': ' . $this->language->get('error_required_options');
                    }
                }

                if ($option['regexp_pattern']
                    && !preg_match($option['regexp_pattern'], (string) $input_options[$option['product_option_id']])) {
                    $errors[$option['product_option_id']] = $option['name'] . ': ' . $option['error_text'];
                }
            }
        }

        return $errors;
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductTags($product_id)
    {
        if (!(int) $product_id) {
            return [];
        }
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_tags") . "
            WHERE product_id = '" . (int) $product_id . "'
                    AND language_id = '" . (int) $this->config->get('storefront_language_id') . "'"
        );

        return $query->rows;
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductDownloads($product_id)
    {
        if (!(int) $product_id) {
            return [];
        }

        $query = $this->db->query(
            "SELECT *
             FROM " . $this->db->table("products_to_downloads") . " p2d
             LEFT JOIN " . $this->db->table("downloads") . " d 
                ON (p2d.download_id = d.download_id)
             LEFT JOIN " . $this->db->table("download_descriptions") . " dd
                ON (d.download_id = dd.download_id
                        AND dd.language_id = '" . (int) $this->config->get('storefront_language_id') . "')
             WHERE p2d.product_id = '" . (int) $product_id . "'"
        );

        return $query->rows;
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductRelated($product_id, $limit = 20)
    {
        $product_data = [];
        if (!(int) $product_id) {
            return [];
        }

        $product_related_query = $this->db->query(
            "SELECT *
             FROM " . $this->db->table("products_related") . "
             WHERE product_id = '" . (int) $product_id . "' "
            . ($limit ? " LIMIT 0," . (int) $limit : "")
        );

        foreach ($product_related_query->rows as $result) {
            $product_query = $this->db->query(
                "SELECT DISTINCT *,
                        pd.name AS name,
                        m.name AS manufacturer,
                        ss.name AS stock,
                        " . $this->_sql_avg_rating_string() . ", " .
                $this->_sql_review_count_string() .
                $this->_sql_join_string() . "
                WHERE p.product_id = '" . (int) $result['related_id'] . "'
                    AND p2s.store_id = '" . (int) $this->config->get('config_store_id') . "'
                    AND p.date_available <= NOW() 
                    AND p.status = '1' " . ($limit ? " LIMIT 0," . (int) $limit : "")
            );

            if ($product_query->num_rows) {
                $product_data[$result['related_id']] = $product_query->row;
            }
        }
        return $product_data;
    }

    /**
     * @param int $productId
     *
     * @return array
     * @throws AException
     */
    public function getProductCategories($productId, $languageId = null)
    {
        $languageId = (int) ($languageId ?? $this->config->get('config_language_id'));
        $storeId = (int) $this->config->get('config_store_id');
        $cacheKey = 'product.category_ids.' . $productId . '_' . $storeId;
        $output = $this->cache->pull($cacheKey);
        if ($output !== false) {
            return $output;
        }
        $query = $this->db->query(
            "SELECT p2c.category_id, cd.name
            FROM " . $this->db->table("products_to_categories") . " p2c
            INNER JOIN " . $this->db->table("categories_to_stores") . " c2s
                ON (c2s.category_id = p2c.category_id AND c2s.store_id = '" . $storeId . "')
            LEFT JOIN " . $this->db->table("category_descriptions") . " cd
                ON cd.category_id = p2c.category_id AND cd.language_id = '" . $languageId . "'
            WHERE p2c.product_id = '" . (int) $productId . "'"
        );
        $output = array_column($query->rows, 'name', 'category_id');
        $this->cache->push($cacheKey, $output);
        return $output;
    }

    protected function _sql_avg_rating_string($only_subquery = false)
    {
        $output = " ( SELECT FLOOR(AVG(r.rating))
                         FROM " . $this->db->table("reviews") . " r
                         WHERE p.product_id = r.product_id AND status = 1
                         GROUP BY r.product_id 
                 ) ";
        if (!$only_subquery) {
            $output .= " AS rating ";
        }
        return $output;
    }

    protected function _filter_by_rating($filter)
    {
        $output = '';
        if (isset($filter['rating'])) {
            $output = " AND ( SELECT FLOOR(AVG(r.rating))
                         FROM " . $this->db->table("reviews") . " r
                         WHERE p.product_id = r.product_id AND status = 1
                         GROUP BY r.product_id 
                 ) IN (" . implode(',', array_map('intval', (array) $filter['rating'])) . ")";
        }
        return $output;
    }

    protected function _sql_review_count_string()
    {
        return " ( SELECT COUNT(rw.review_id)
                         FROM " . $this->db->table("reviews") . " rw
                         WHERE p.product_id = rw.product_id AND rw.status = 1
                         GROUP BY rw.product_id
                 ) AS review ";
    }

    protected function _sql_final_price_string()
    {
        $sql = " ( SELECT CASE WHEN p2sp.price_prefix='%' 
                              THEN p.price - (p2sp.price * (p.price/100))
                            WHEN p2sp.price_prefix='Δ' 
                              THEN p.price - p2sp.price                            
                            ELSE p2sp.price END as special_price
                    FROM " . $this->db->table("product_specials") . " p2sp
                    WHERE p2sp.product_id = p.product_id
                            AND p2sp.customer_group_id = '" . $this->customer_group_id . "'
                            AND COALESCE(p2sp.date_start, '1970-01-01') < NOW()
                            AND COALESCE(p2sp.date_end, '2200-01-01') > NOW()
                    ORDER BY p2sp.priority ASC, special_price ASC LIMIT 1
                 ) ";
        return "COALESCE( " . $sql . ", p.price) as final_price";
    }

    /**
     * @return string
     */
    protected function _sql_join_string()
    {
        $languageId = (int) $this->config->get('storefront_language_id');
        return "FROM " . $this->db->table("products") . " p
                LEFT JOIN " . $this->db->table("product_descriptions") . " pd
                    ON (p.product_id = pd.product_id
                            AND pd.language_id = '" . $languageId . "')
                LEFT JOIN " . $this->db->table("products_to_stores") . " p2s ON (p.product_id = p2s.product_id)
                LEFT JOIN " . $this->db->table("manufacturers") . " m ON (p.manufacturer_id = m.manufacturer_id)
                LEFT JOIN " . $this->db->table("stock_statuses") . " ss
                        ON (p.stock_status_id = ss.stock_status_id
                            AND ss.language_id = '" . $languageId . "')";
    }

    /**
     * @param $productIds
     *
     * @return false|mixed
     * @throws AException
     */
    public function getProductsAllInfo($productIds = [])
    {
        if (!$productIds) {
            return false;
        }
        $productIds = filterIntegerIdList($productIds);
        $languageId = (int) $this->config->get('storefront_language_id');
        $storeId = (int) $this->config->get('config_store_id');
        $cacheKey = 'product.all_info.'
            . md5(implode('', $productIds))
            . '.' . $this->customer_group_id
            . '.store_' . $storeId
            . '_lang_' . $languageId;

        $output = $this->cache->pull($cacheKey);
        // if no cache
        if ($output === false) {
            $output = [];
            $sql = "SELECT ps.product_id, 
                        CASE WHEN ps.price_prefix='%' 
                               THEN p.price - (ps.price * (p.price/100))
                             WHEN ps.price_prefix='Δ' 
                               THEN p.price - ps.price 
                        ELSE ps.price END AS special_price
                    FROM " . $this->db->table("product_specials") . " ps
                    LEFT JOIN " . $this->db->table("products") . " p
                        ON p.product_id = ps.product_id
                    WHERE ps.product_id IN (" . implode(', ', $productIds) . ")
                            AND ps.customer_group_id = '" . $this->customer_group_id . "'
                            AND COALESCE(ps.date_start, '1970-01-01') < NOW()
                            AND COALESCE(ps.date_end, '2200-01-01') > NOW()
                    ORDER BY ps.product_id, ps.priority, special_price";
            $result = $this->db->query($sql);
            $temp = '';
            $specials = [];
            foreach ($result->rows as $row) {
                if ($row['product_id'] != $temp) {
                    $specials[$row['product_id']] = $row['special_price'];
                }
                $temp = $row['product_id'];
            }
            //avg-rating
            if ($this->config->get('display_reviews')) {
                $sql = "SELECT product_id, FLOOR(AVG(rating)) AS total
                        FROM " . $this->db->table("reviews") . "
                        WHERE status = '1' AND product_id IN (" . implode(', ', $productIds) . ")
                        GROUP BY product_id";
                $result = $this->db->query($sql);
                $rating = [];
                foreach ($result->rows as $row) {
                    $rating[$row['product_id']] = (int) $row['total'];
                }
            } else {
                $rating = false;
            }

            // discounts
            $sql = "SELECT rd.product_id,
                        CASE WHEN rd.price_prefix='%' 
                              THEN p.price - (rd.price * (p.price/100))
                            WHEN rd.price_prefix='Δ' 
                              THEN p.price - rd.price 
                            ELSE rd.price END AS discount_price
                    FROM " . $this->db->table("product_discounts") . " rd
                    LEFT JOIN " . $this->db->table("products") . " p
                        ON p.product_id = rd.product_id
                    WHERE rd.product_id IN (" . implode(', ', $productIds) . ")
                        AND rd.customer_group_id = '" . $this->customer_group_id . "'
                        AND rd.quantity = '1'
                        AND COALESCE(rd.date_start, '1970-01-01') < NOW()
                        AND COALESCE(rd.date_end, '2200-01-01') > NOW()
                    ORDER BY rd.product_id, rd.priority, discount_price";
            $result = $this->db->query($sql);
            $temp = '';
            $discounts = [];
            foreach ($result->rows as $row) {
                if ($row['product_id'] != $temp) {
                    $discounts[$row['product_id']] = $row['discount_price'];
                }
                $temp = $row['product_id'];
            }

            // options
            $sql = "SELECT po.product_id,
                            po.product_option_id,
                            po.regexp_pattern,
                            pov.product_option_value_id,
                            pov.sku,
                            pov.quantity,
                            pov.subtract,
                            pov.price,
                            pov.prefix,
                            pod.name as option_name,
                            pod.error_text as error_text,
                            povd.name as value_name,
                            po.sort_order
                        FROM " . $this->db->table("product_options") . " po
                        LEFT JOIN " . $this->db->table("product_option_values") . " pov
                            ON pov.product_option_id = po.product_option_id
                        LEFT JOIN " . $this->db->table("product_option_value_descriptions") . " povd
                            ON (povd.product_option_value_id = pov.product_option_value_id
                                    AND povd.language_id='" . $languageId . "')
                        LEFT JOIN " . $this->db->table("product_option_descriptions") . " pod
                            ON (pod.product_option_id = po.product_option_id
                                AND pod.language_id='" . $languageId . "') 
                        WHERE po.product_id in (" . implode(', ', $productIds) . ")
                        ORDER BY pov.product_option_id, pov.product_id, po.sort_order, pov.sort_order";
            $result = $this->db->query($sql);
            $temp = $temp2 = '';
            $options = [];
            foreach ($result->rows as $row) {
                if ($row['product_id'] != $temp) {
                    $temp2 = '';
                }

                if ($row['product_option_id'] != $temp2) {
                    $options[$row['product_id']][$row['product_option_id']] = [
                        'product_option_id' => $row['product_option_id'],
                        'name'              => $row['option_name'],
                        'sort_order'        => $row['sort_order'],
                    ];
                }
                $options[$row['product_id']][$row['product_option_id']]['option_value'][] = [
                    'product_option_value_id' => $row['product_option_value_id'],
                    'name'                    => $row['value_name'],
                    'sku'                     => $row['sku'],
                    'price'                   => $row['price'],
                    'prefix'                  => $row['prefix'],
                ];
                $temp = $row['product_id'];
                $temp2 = $row['product_option_id'];
            }

            foreach ($productIds as $product) {
                $output[$product]['special'] = $specials[$product];
                $output[$product]['discount'] = $discounts[$product];
                $output[$product]['options'] = $options[$product];
                $output[$product]['rating'] = $rating !== false ? (int) $rating[$product] : false;
            }
            $this->cache->push($cacheKey, $output);
        }
        return $output;
    }

    /**
     * @param array $data
     * @param string $mode
     *
     * @return false|mixed
     * @throws AException
     */
    public function getProducts($data = [], $mode = 'default')
    {
        $language_id = ( int ) $data['content_language_id'] ? : (int) $this->config->get('storefront_language_id');
        $storeId = $this->config->get('config_store_id');
        $cacheKey = 'product.get.list.' . md5(var_export($data, true)) . $language_id . $storeId;
        $output = $this->cache->pull($cacheKey);
        if ($output !== false) {
            return $output;
        }

        if ($data || $mode == 'total_only') {
            $filter = $data['filter'] ?? [];

            if ($mode == 'total_only') {
                $sql = "SELECT COUNT(DISTINCT p.product_id) as total
                        FROM " . $this->db->table("products") . " p
                        LEFT JOIN " . $this->db->table("product_descriptions") . " pd
                            ON (p.product_id = pd.product_id)";
            } else {
                $sql = "SELECT *,
                        p.product_id,
                        " . $this->_sql_final_price_string() . ",
                        pd.name AS name,
                        m.name AS manufacturer,
                        ss.name AS stock,
                        " . $this->_sql_avg_rating_string() . ",
                        " . $this->_sql_review_count_string() . "
                        " . $this->_sql_join_string();
            }

            if (isset($filter['category_id'])) {
                $sql .= " LEFT JOIN " . $this->db->table("products_to_categories") . " p2c 
                            ON (p.product_id = p2c.product_id)";
            }
            $sql .= " WHERE pd.language_id = '" . $language_id . "' 
                        AND p.date_available <= NOW() 
                        AND p.status = '1' ";

            if (!empty($data['subsql_filter'])) {
                $sql .= " AND " . $data['subsql_filter'];
            }

            $match = $filter['match'] ?? 'exact';

            if (isset($filter['keyword'])) {
                $keywords = explode(' ', $filter['keyword']);

                if ($match == 'any') {
                    $sql .= " AND (";
                    foreach ($keywords as $k => $keyword) {
                        $kw = $this->db->escape(strtolower($keyword), true);
                        $sql .= $k > 0 ? " OR" : "";
                        $sql .= " (LCASE(pd.name) LIKE '%" . $kw . "%'";
                        $sql .= " OR LCASE(p.model) LIKE '%" . $kw . "%'";
                        $sql .= " OR LCASE(p.sku) LIKE '%" . $kw . "%')";
                    }
                    $sql .= " )";
                } else {
                    if ($match == 'all') {
                        $sql .= " AND (";
                        foreach ($keywords as $k => $keyword) {
                            $kw = $this->db->escape(strtolower($keyword), true);
                            $sql .= $k > 0 ? " AND" : "";
                            $sql .= " (LCASE(pd.name) LIKE '%" . $kw . "%'";
                            $sql .= " OR LCASE(p.model) LIKE '%" . $kw . "%'";
                            $sql .= " OR LCASE(p.sku) LIKE '%" . $kw . "%')";
                        }
                        $sql .= " )";
                    } else {
                        if ($match == 'exact') {
                            $kw = $this->db->escape(strtolower($filter['keyword']), true);
                            $sql .= " AND (LCASE(pd.name) LIKE '%" . $kw . "%'";
                            $sql .= " OR LCASE(p.model) LIKE '%" . $kw . "%'";
                            $sql .= " OR LCASE(p.sku) LIKE '%" . $kw . "%')";
                        }
                    }
                }
            }

            if (isset($filter['pfrom'])) {
                $sql .= " AND final_price >= '" . (float) $filter['pfrom'] . "'";
            }
            if (isset($filter['pto'])) {
                $sql .= " AND final_price <= '" . (float) $filter['pto'] . "'";
            }
            if (isset($filter['category_id'])) {
                $sql .= " AND p2c.category_id = '" . (int) $filter['category_id'] . "'";
            }
            if (isset($filter['manufacturer_id'])) {
                $sql .= " AND p.manufacturer_id = '" . (int) $filter['manufacturer_id'] . "'";
            }

            if (isset($filter['status'])) {
                $sql .= " AND p.status = '" . (int) $filter['status'] . "'";
            }

            //If for total, we're done building the query
            if ($mode == 'total_only') {
                $query = $this->db->query($sql);
                $output = (int) $query->row['total'];
                $this->cache->push($cacheKey, $output);
                return $output;
            }

            $sort_data = [
                'name'          => 'pd.name',
                'model'         => 'p.model',
                'quantity'      => 'p.quantity',
                'price'         => 'final_price',
                'status'        => 'p.status',
                'sort_order'    => 'p.sort_order',
                'date_modified' => 'p.date_modified',
                'review'        => 'review',
                'rating'        => 'rating',
            ];

            if (isset($data['sort']) && in_array($data['sort'], array_keys($sort_data))) {
                $sql .= " ORDER BY " . $sort_data[$data['sort']];
            } else {
                $sql .= " ORDER BY pd.name";
            }

            if (isset($data['order']) && ($data['order'] == 'DESC')) {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }

            if (isset($data['start']) || isset($data['limit'])) {
                $data['start'] = max($data['start'], 0);

                if ($data['limit'] < 1) {
                    $data['limit'] = 20;
                }
                $sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
            }
            $query = $this->db->query($sql);
            $output = $query->rows;
            $this->cache->push($cacheKey, $output);
            return $output;
        } else {
            $query = $this->db->query(
                "SELECT *
                FROM " . $this->db->table("products") . " p
                LEFT JOIN " . $this->db->table("product_descriptions") . " pd 
                    ON (p.product_id = pd.product_id)
                WHERE pd.language_id = '" . $language_id . "' 
                    AND p.date_available <= NOW() 
                    AND p.status = '1'
                ORDER BY pd.name"
            );

            $output = $query->rows;
            $this->cache->push($cacheKey, $output);
            return $output;
        }
    }

    /**
     * @param array $data
     *
     * @return array|null
     * @throws AException
     */
    public function getTotalProducts($data = [])
    {
        return $this->getProducts($data, 'total_only');
    }

    /**
     * @deprecated since 1.4.4
     * @param string $sort
     * @param string $order
     * @param int $start
     * @param int $limit
     *
     * @return array
     * @throws AException
     */
    public function getProductSpecials($sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 0)
    {
        $start = abs((int) $start);
        $limit = abs((int) $limit);
        return $this->getSpecialProducts(
            [
                'sort'  => $sort,
                'order' => $order,
                'start' => $start,
                'limit' => $limit,
            ]
        );
    }

    /**
     * @param int $product_id
     *
     * @return bool
     * @throws AException
     */
    public function hasTrackOptions($product_id)
    {
        $sql = "SELECT *
                FROM " . $this->db->table('product_option_values') . " pov
                INNER JOIN " . $this->db->table('product_options') . " po
                    ON (pov.product_option_id = po.product_option_id AND po.status = 1) 
                WHERE pov.product_id=" . (int) $product_id . " AND pov.subtract = 1";
        $result = $this->db->query($sql);
        return (bool) $result->num_rows;
    }

    /**
     * @param array $data
     *
     * @return array
     * @throws AException
     * @since 1.2.7
     *
     */
    public function getSpecialProducts($data = [])
    {
        $data['sort'] = $data['sort'] ?? 'p.sort_order';
        $data['order'] = $data['order'] ?? 'ASC';
        $data['start'] = $data['start'] ?? 0;
        $data['limit'] = $data['limit'] ?? 20;

        $languageId = (int) $this->config->get('storefront_language_id');
        $storeId = (int) $this->config->get('config_store_id');
        $customerGroupId = (int) $data['customer_group_id'] ?: $this->customer_group_id;

        $cache_key = 'product.specials.' . $customerGroupId;
        $cache_key .= $this->cache->paramsToString($data);
        $cache_key .= '.store_' . $storeId . '.lang_' . $languageId;

        $cache = $this->cache->pull($cache_key);
        if ($cache !== false) {
            return $cache;
        }

        $sql = "SELECT " . $this->db->getSqlCalcTotalRows()
            . " DISTINCT ps.product_id, p.*, pd.name, pd.description, pd.blurb, ss.name AS stock";
        if ($data['avg_rating']) {
            $sql .= ", (SELECT FLOOR(AVG(rating))
                    FROM " . $this->db->table("reviews") . " r1
                    WHERE r1.product_id = ps.product_id AND r1.status = '1'
                    GROUP BY r1.product_id) AS rating\n";
        }
        $sql .= ", (SELECT CASE WHEN rd.price_prefix='%' 
                                  THEN p.price - (rd.price * (p.price/100))
                                WHEN rd.price_prefix='Δ' 
                                  THEN p.price - rd.price  
                                ELSE rd.price END AS discount_price
                    FROM " . $this->db->table("product_discounts") . " rd
                    WHERE rd.product_id = ps.product_id
                        AND rd.customer_group_id = '" . $customerGroupId . "'
                        AND rd.quantity = '1'
                        AND COALESCE(rd.date_start, '1970-01-01') < NOW()
                        AND COALESCE(rd.date_end, '2200-01-01') > NOW()
                    ORDER BY rd.priority ASC, discount_price ASC
                    LIMIT 1) as discount_price\n ";

        $sql .= "FROM " . $this->db->table("product_specials") . " ps
                LEFT JOIN " . $this->db->table("products") . " p ON (ps.product_id = p.product_id)
                LEFT JOIN " . $this->db->table("product_descriptions") . " pd
                    ON (p.product_id = pd.product_id AND language_id=" . $languageId . ")
                LEFT JOIN " . $this->db->table("products_to_stores") . " p2s
                    ON (p.product_id = p2s.product_id)
                LEFT JOIN " . $this->db->table("stock_statuses") . " ss
                    ON (p.stock_status_id = ss.stock_status_id AND ss.language_id = '" . $languageId . "')
                WHERE p.status = '1'
                    AND p.date_available <= NOW() AND p2s.store_id = '" . $storeId . "'
                    AND ps.customer_group_id = '" . $customerGroupId . "'
                    AND COALESCE(ps.date_start, '1970-01-01') < NOW()
                    AND COALESCE(ps.date_end, '2200-01-01') > NOW()
                GROUP BY ps.product_id";

        $sort_data = [
            'pd.name',
            'p.sort_order',
            'ps.price',
            'rating',
            'date_modified',
        ];

        if (in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } else {
                $sql .= " ORDER BY " . $this->db->escape($data['sort']);
            }
        } else {
            $sql .= " ORDER BY p.sort_order";
        }

        if ($data['order'] == 'DESC') {
            $sql .= " DESC";
        } else {
            $sql .= " ASC";
        }

        if ($data['start'] < 0) {
            $data['start'] = 0;
        }
        if ((int) $data['limit']) {
            $sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
        }

        $query = $this->db->query($sql);
        $total_num_rows = $this->db->getTotalNumRows();
        $output = $query->rows;

        foreach ($output as &$row) {
            $row['total_num_rows'] = $total_num_rows;
        }

        $this->cache->push($cache_key, $output);
        return $output;
    }
}
