<?php /** @noinspection PhpMultipleClassDeclarationsInspection */
/*
 *   $Id$
 *
 *   AbanteCart, Ideal OpenSource Ecommerce Solution
 *   http://www.AbanteCart.com
 *
 *   Copyright © 2011-2025 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.
 */

/**
 * @property ModelCatalogDownload $model_catalog_download
 */
class ModelCatalogProduct extends Model
{
    public $errors = [];
    const TAG_REGEX_PATTERN = '/[^\\d\s\p{L}\-_]/u';

    /** @param array $data
     * @return int
     * @throws AException
     */
    public function addProduct($data)
    {
        $language_id = $this->language->getContentLanguageID();

        $this->db->query(
            "INSERT INTO " . $this->db->table("products") . " 
            SET model = '" . $this->db->escape($data['model']) . "',
                sku = '" . $this->db->escape($data['sku']) . "',
                location = '" . $this->db->escape($data['location']) . "',
                quantity = '" . preformatInteger($data['quantity']) . "',
                minimum = '" . preformatInteger($data['minimum']) . "',
                maximum = '" . preformatInteger($data['maximum']) . "',
                subtract = '" . (int)$data['subtract'] . "',
                stock_checkout = '" . $this->db->escape($data['stock_checkout']) . "',
                stock_status_id = '" . (int)$data['stock_status_id'] . "',
                date_available = '" . $this->db->escape($data['date_available']) . "',
                manufacturer_id = '" . (int)$data['manufacturer_id'] . "',
                shipping = '" . (int)$data['shipping'] . "',
                ship_individually = '" . (int)$data['ship_individually'] . "',
                free_shipping = '" . (int)$data['free_shipping'] . "',
                shipping_price = '" . preformatFloat($data['shipping_price'], $this->language->get('decimal_point')) . "',
                price = '" . preformatFloat($data['price'], $this->language->get('decimal_point')) . "',
                cost = '" . preformatFloat($data['cost'], $this->language->get('decimal_point')) . "',
                weight = '" . preformatFloat($data['weight'], $this->language->get('decimal_point')) . "',
                weight_class_id = '" . (int)$data['weight_class_id'] . "',
                length = '" . preformatFloat($data['length'], $this->language->get('decimal_point')) . "',
                width = '" . preformatFloat($data['width'], $this->language->get('decimal_point')) . "',
                height = '" . preformatFloat($data['height'], $this->language->get('decimal_point')) . "',
                length_class_id = '" . (int)$data['length_class_id'] . "',
                status = '" . (int)$data['status'] . "',
                tax_class_id = '" . (int)$data['tax_class_id'] . "',
                sort_order = '" . (int)$data['sort_order'] . "',                    
                settings = '" . $this->db->escape(serialize($data['settings'])) . "',
                supplier_code = " . $this->db->stringOrNull($data['supplier_code']) . ",
                supplier_id = " . $this->db->stringOrNull($data['supplier_id']) . ",
                date_added = NOW()"
        );

        $product_id = $this->db->getLastId();
        // if new product
        if (!is_int(key($data['product_description']))) {
            $update = [];
            foreach ($data['product_description'] as $field => $value) {
                $update[$language_id][$field] = $value;
            }
            $this->language->replaceDescriptions(
                'product_descriptions',
                ['product_id' => $product_id],
                $update
            );
        } else { // if cloning
            foreach ($data['product_description'] as $language_id => $value) {
                $this->db->query(
                    "INSERT INTO " . $this->db->table("product_descriptions") . " 
                    SET product_id = '" . $product_id . "',
                        language_id = '" . (int)$language_id . "',
                        name = '" . $this->db->escape($value['name']) . "',
                        meta_keywords = '" . $this->db->escape($value['meta_keywords']) . "',
                        meta_description = '" . $this->db->escape($value['meta_description']) . "',
                        description = '" . $this->db->escape($value['description']) . "',
                        blurb = '" . $this->db->escape($value['blurb']) . "'"
                );
            }
            reset($data['product_description']);
        }

        if ($data['featured']) {
            $this->setFeatured($product_id, true);
        }

        $seo_keys = [];
        if ($data['keyword']) {
            if (is_string($data['keyword'])) {
                $seo_keys = [
                    $language_id => [
                        'keyword' => SEOEncode($data['keyword'], 'product_id', $product_id),
                    ],
                ];
            } //when cloning
            else {
                if (is_array($data['keyword'])) {
                    $all_languages = $this->language->getAvailableLanguages();
                    $all_ids = [];
                    foreach ($all_languages as $l) {
                        $all_ids[] = $l['language_id'];
                    }
                    foreach ($data['keyword'] as $lang_id => $seo_key) {
                        if (!in_array($lang_id, $all_ids)) {
                            continue;
                        }
                        $seo_keys[(int)$lang_id] = [
                            'keyword' => SEOEncode($seo_key, 'product_id', $product_id),
                        ];
                    }
                }
            }
        } else {
            //Default behavior to save SEO URL keyword from product name in default language
            // when new product
            if (!is_int(key($data['product_description']))) {
                $seo_keys = [
                    $language_id => [
                        'keyword' => SEOEncode($data['product_description']['name'], 'product_id', $product_id),
                    ],
                ];
            } else { // when clones
                $product_seo_keys = $this->getProductSEOKeywords($product_id);

                $all_languages = $this->language->getAvailableLanguages();
                $all_ids = [];
                foreach ($all_languages as $l) {
                    $all_ids[] = $l['language_id'];
                }
                foreach ($product_seo_keys as $lang_id => $seo_key) {
                    if (!in_array($lang_id, $all_ids)) {
                        continue;
                    }
                    $seo_keys[(int)$lang_id] = [
                        'keyword' => SEOEncode($seo_key, 'product_id', $product_id),
                    ];
                }
            }
        }
        if ($seo_keys) {
            foreach ($seo_keys as $lang_id => $seo_key) {
                //if seo-key of new clone more than 255 characters - just do hash
                if (mb_strlen($seo_key['keyword']) > 250) {
                    $seo_key['keyword'] = md5($seo_key['keyword'] . time());
                }
                $this->language->replaceDescriptions(
                    'url_aliases',
                    [
                        'query'       => "product_id=" . $product_id,
                        'language_id' => $lang_id,
                    ],
                    [
                        $lang_id => $seo_key,
                    ]
                );
            }
        } else {
            $this->db->query(
                "DELETE
                FROM " . $this->db->table("url_aliases") . " 
                WHERE query = 'product_id=" . $product_id . "'
                    AND language_id = '" . $language_id . "'"
            );
        }

        if ($data['product_tags']) {
            $this->language->saveTags(
                'product_tags',
                ['product_id' => $product_id],
                $language_id,
                $data['product_tags']
            );
        }
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
        return $product_id;
    }

    /**
     * @param int $product_id
     * @param array $data
     *
     * @return int
     * @throws AException
     */
    public function addProductDiscount($product_id, $data)
    {
        $data['price'] = str_replace(" ", "", $data['price']);
        $this->db->query(
            "INSERT INTO " . $this->db->table("product_discounts") . "
            SET product_id = '" . (int)$product_id . "',
                customer_group_id = '" . (int)$data['customer_group_id'] . "',
                quantity = '" . preformatInteger($data['quantity']) . "',
                priority = '" . (int)$data['priority'] . "',
                price_prefix = '" . $this->db->escape($data['price_prefix']) . "',
                price = '" . preformatFloat($data['price']) . "',
                date_start = '" . $this->db->escape($data['date_start']) . "',
                date_end = '" . $this->db->escape($data['date_end']) . "'"
        );
        $id = $this->db->getLastId();
        $this->_touch_product($product_id);
        return $id;
    }

    /**
     * @param int $product_id
     * @param array $data
     *
     * @return int
     * @throws AException
     */
    public function addProductSpecial($product_id, $data)
    {
        $data['price'] = str_replace(" ", "", $data['price']);
        if (!empty($data['date_start']) && !$data['iso_date']) {
            $data['date_start'] = dateDisplay2ISO($data['date_start'], $this->language->get('date_format_short'));
        }
        if (!empty($data['date_end']) && !$data['iso_date']) {
            $data['date_end'] = dateDisplay2ISO($data['date_end'], $this->language->get('date_format_short'));
        }

        $this->db->query(
            "INSERT INTO " . $this->db->table("product_specials") . "
            SET product_id = '" . (int)$product_id . "',
                customer_group_id = '" . (int)$data['customer_group_id'] . "',
                priority = '" . (int)$data['priority'] . "',
                price_prefix = '" . $this->db->escape($data['price_prefix']) . "',
                price = '" . preformatFloat($data['price'], $this->language->get('decimal_point')) . "',
                date_start = '" . $this->db->escape($data['date_start']) . "',
                date_end = '" . $this->db->escape($data['date_end']) . "'"
        );
        $id = $this->db->getLastId();
        $this->_touch_product($product_id);
        return $id;
    }

    /**
     * @param int $product_id
     * @param array $data
     *
     * @throws AException
     */
    public function updateProduct($product_id, $data)
    {
        $language_id = $this->language->getContentLanguageID();
        $product_id = (int)$product_id;

        $fields = [
            "model",
            "sku",
            "location",
            "quantity",
            "minimum",
            "maximum",
            "subtract",
            "stock_checkout",
            "stock_status_id",
            "date_available",
            "manufacturer_id",
            "shipping",
            "ship_individually",
            "free_shipping",
            "shipping_price",
            "call_to_order",
            "price",
            "cost",
            "weight",
            "weight_class_id",
            "length",
            "width",
            "height",
            "length_class_id",
            "status",
            "tax_class_id",
            "sort_order",
            "settings",
            "supplier_code",
            "supplier_id"
        ];
        $preformat_fields = [
            "shipping_price",
            "price",
            "cost",
            "weight",
            "length",
            "width",
            "height",
        ];

        $update = ['date_modified = NOW()'];
        foreach ($fields as $f) {
            if (isset($data[$f])) {
                if (in_array($f, $preformat_fields)) {
                    $data[$f] = preformatFloat($data[$f], $this->language->get('decimal_point'));
                }
                $update[] = $f . " = " . $this->db->stringOrNull($data[$f]);
            }
        }

        if (!empty($update)) {
            $this->db->query(
                "UPDATE `" . $this->db->table("products`")
                . " SET " . implode(',', $update)
                . " WHERE product_id = '" . $product_id . "'"
            );
        }

        if (!empty($data['product_description'])) {
            foreach ($data['product_description'] as $field => $value) {
                $fields = [
                    'name',
                    'description',
                    'meta_keywords',
                    'meta_description',
                    'blurb'
                ];
                $update = [];
                foreach ($fields as $f) {
                    if ($f == $field) {
                        $update[$f] = $value;
                    }
                }

                if (!empty($update)) {
                    $this->language->replaceDescriptions(
                        'product_descriptions',
                        ['product_id' => $product_id],
                        [$language_id => $update]
                    );
                }
            }
        }

        if (isset($data['featured'])) {
            $this->setFeatured($product_id, (bool)$data['featured']);
        }

        if (isset($data['keyword'])) {
            $data['keyword'] = SEOEncode($data['keyword'], 'product_id', $product_id);
            if ($data['keyword']) {
                //if seo-key of new clone more than 255 characters - just do hash
                if (mb_strlen($data['keyword']) > 250) {
                    $data['keyword'] = md5($data['keyword'] . time());
                }
                $this->language->replaceDescriptions(
                    'url_aliases',
                    [
                        'query' => "product_id=" . $product_id
                    ],
                    [
                        $language_id => [
                            'keyword' => $data['keyword']
                        ]
                    ]
                );
            } else {
                $this->db->query(
                    "DELETE
                    FROM " . $this->db->table("url_aliases") . " 
                    WHERE query = 'product_id=" . $product_id . "'
                        AND language_id = '" . $language_id . "'"
                );
            }
        }

        if (isset($data['product_tags'])) {
            $this->language->saveTags(
                'product_tags',
                ['product_id' => $product_id],
                $language_id,
                $data['product_tags']
            );
        }

        if (isset($data['stock_location'])) {
            $this->updateProductStockLocations($data['stock_location'], $product_id, 0);
        }

        if (isset($data['weight_class_id'])) {
            $aW = new AWeight($this->registry);
            $unit = $aW->getUnit($data['weight_class_id']);
            if ($unit) {
                //get ALL option values of all options
                $optionValues = $this->getProductOptionValues($product_id, 0);
                foreach ($optionValues as $ov) {
                    //if the weight unit of based product has been changed
                    if ($ov['weight_type']
                        && $ov['weight_type'] != '%'
                        && $ov['weight_type'] != $unit
                    ) {
                        $ov['weight_type'] = $unit;
                        $this->updateProductOptionValue(
                            $ov['product_option_value_id'],
                            '',
                            $ov
                        );
                    }
                }
            }
        }

        $this->_touch_product($product_id);
    }

    /**
     * @param int $product_discount_id
     * @param array $data
     *
     * @throws AException
     */
    public function updateProductDiscount($product_discount_id, $data)
    {
        $product_discount_id = (int)$product_discount_id;
        $fields = ["customer_group_id", "quantity", "priority", "price_prefix", "price", "date_start", "date_end",];
        if (isset($data['price'])) {
            $data['price'] = preformatFloat($data['price'], $this->language->get('decimal_point'));
        }
        $update = [];
        foreach ($fields as $f) {
            if (isset($data[$f])) {
                $update[] = $f . " = '" . $this->db->escape($data[$f]) . "'";
            }
        }
        if (!empty($update)) {
            $this->db->query(
                "UPDATE " . $this->db->table("product_discounts") . " 
                SET " . implode(',', $update) . "
                WHERE product_discount_id = '" . $product_discount_id . "'"
            );
        }

        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
    }

    /**
     * @param int $product_special_id
     * @param array $data
     *
     * @throws AException
     */
    public function updateProductSpecial($product_special_id, $data)
    {
        $product_special_id = (int)$product_special_id;
        $fields = ["customer_group_id", "priority", "price_prefix", "price", "date_start", "date_end",];
        if (isset($data['price'])) {
            $data['price'] = preformatFloat($data['price'], $this->language->get('decimal_point'));
        }

        $update = [];
        foreach ($fields as $f) {
            if (isset($data[$f])) {
                $update[] = $f . " = '" . $this->db->escape($data[$f]) . "'";
            }
        }
        if (!empty($update)) {
            $this->db->query(
                "UPDATE `" . $this->db->table("product_specials`")
                . " SET " . implode(',', $update)
                . " WHERE product_special_id = '" . $product_special_id . "'"
            );
        }
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
    }

    /**
     * @param int $product_id
     * @param array $data
     *
     * @return bool
     * @throws AException
     */
    public function updateProductLinks($product_id, $data)
    {
        if (!(int)$product_id || !$data) {
            return false;
        }

        if (isset($data['product_store'])) {
            $data['product_store'] = array_unique(array_map('intval',$data['product_store']));
            $this->db->query(
                "DELETE FROM " . $this->db->table("products_to_stores") . " 
                WHERE product_id = '" . $product_id . "'"
            );
            foreach ($data['product_store'] as $store_id) {
                $this->db->query(
                    "INSERT INTO " . $this->db->table("products_to_stores") . " 
                    SET product_id = '" . $product_id . "', store_id = '" . (int)$store_id . "'"
                );
            }
        }

        if (isset($data['product_download'])) {
            $this->db->query(
                "DELETE FROM " . $this->db->table("products_to_downloads") . " 
                WHERE product_id = '" . $product_id . "'"
            );
            foreach ($data['product_download'] as $download_id) {
                if ((int)$download_id) {
                    $this->db->query(
                        "INSERT INTO " . $this->db->table("products_to_downloads")
                        . " SET product_id = '" . $product_id . "', download_id = '" . (int)$download_id . "'"
                    );
                }
            }
        }

        if (isset($data['product_category'])) {
            $this->db->query(
                "DELETE FROM " . $this->db->table("products_to_categories") . " 
                WHERE product_id = '" . $product_id . "'"
            );
            foreach ($data['product_category'] as $category_id) {
                if ((int)$category_id) {
                    $this->db->query(
                        "INSERT INTO " . $this->db->table("products_to_categories") . " 
                        SET product_id = '" . $product_id . "', category_id = '" . (int)$category_id . "'"
                    );
                }
            }
        }

        if (isset($data['product_related'])) {
            $this->db->query(
                "DELETE FROM " . $this->db->table("products_related") . " 
                WHERE product_id = '" . $product_id . "'"
            );
            foreach ($data['product_related'] as $related_id) {
                if ((int)$related_id) {
                    $this->db->query(
                        "INSERT INTO " . $this->db->table("products_related") . " 
                        SET product_id = '" . $product_id . "', related_id = '" . (int)$related_id . "'"
                    );
                    $this->db->query(
                        "DELETE FROM " . $this->db->table("products_related") . " 
                        WHERE product_id = '" . (int)$related_id . "' 
                            AND related_id = '" . $product_id . "'"
                    );
                    $this->db->query(
                        "INSERT INTO " . $this->db->table("products_related") . " 
                        SET product_id = '" . (int)$related_id . "', related_id = '" . $product_id . "'"
                    );
                }
            }
        }
        $this->_touch_product($product_id);
        return true;
    }

    /**
     * @param array $idList
     *
     * @return bool
     * @throws AException
     */
    public function relateProducts(array $idList)
    {
        $idList = filterIntegerIdList($idList);
        if (!$idList) {
            return false;
        }
        foreach ($idList as $productId) {
            foreach ($idList as $relatedId) {
                if ($relatedId && $relatedId != $productId) {
                    $this->db->query(
                        "DELETE FROM " . $this->db->table("products_related") . " 
                        WHERE product_id = " . $relatedId . " 
                            AND related_id = " . $productId
                    );
                    $this->db->query(
                        "INSERT INTO " . $this->db->table("products_related") . " 
                        SET product_id = " . $relatedId . ", related_id = " . $productId
                    );
                }
            }
        }
        $this->cache->remove(['product']);
        return true;
    }

    /**
     * @param int $product_id
     * @param array $data
     *
     * @return int
     * @throws AException
     */
    public function addProductOption($product_id, $data)
    {
        $data['with_values'] = $data['with_values'] ?? true;
        $am = new AAttribute_Manager();

        $attributeInfo = $data['attribute_id'] && $data['with_values'] ? $am->getAttribute((int)$data['attribute_id']) : [];

        if ($attributeInfo) {
            $data['element_type'] = $attributeInfo['element_type'];
            $data['required'] = $attributeInfo['required'];
            $data['regexp_pattern'] = $attributeInfo['regexp_pattern'];
            $data['placeholder'] = $attributeInfo['placeholder'];
            $data['sort_order'] = $attributeInfo['sort_order'];
            $data['settings'] = $attributeInfo['settings'];
        } else {
            $data['placeholder'] = $data['option_placeholder'];
        }

        $this->db->query(
            "INSERT INTO " . $this->db->table("product_options") . "
                (product_id,
                 attribute_id,
                 element_type,
                 required,
                 sort_order,
                 group_id,
                 status,
                 regexp_pattern,
                 settings)
            VALUES ('" . (int)$product_id . "',
                '" . (int)$data['attribute_id'] . "',
                '" . $this->db->escape($data['element_type']) . "',
                '" . (int)$data['required'] . "',
                '" . (int)$data['sort_order'] . "',
                '" . (int)$data['group_id'] . "',
                '" . (int)$data['status'] . "',
                '" . $this->db->escape($data['regexp_pattern']) . "',
                '" . $this->db->escape($data['settings']) . "'
                )"
        );
        $product_option_id = $this->db->getLastId();

        if ($data['option_name']) {
            $attributeDescriptions = [
                $this->language->getContentLanguageID() => [
                    'name'        => $data['option_name'],
                    'error_text'  => $data['error_text'],
                    'placeholder' => $data['placeholder'],
                ],
            ];
        } else {
            $attributeDescriptions = $am->getAttributeDescriptions($data['attribute_id']);
        }

        foreach ($attributeDescriptions as $language_id => $desc) {
            $this->language->replaceDescriptions(
                'product_option_descriptions',
                [
                    'product_option_id' => (int)$product_option_id,
                    'product_id'        => (int)$product_id,
                ],
                [
                    $language_id => [
                        'name'               => $desc['name'],
                        'error_text'         => $desc['error_text'],
                        'option_placeholder' => $data['placeholder'],
                    ],
                ]
            );
        }

        //add empty option value for single value attributes
        $elements_with_options = HtmlElementFactory::getElementsWithOptions();
        if (!in_array($data['element_type'], $elements_with_options)) {
            $this->insertProductOptionValue($product_id, $product_option_id, '', '', []);
        } elseif ($attributeInfo && !$attributeInfo['children_count']) {
            //add all values into a product option
            $values = $am->getAttributeValues($data['attribute_id']);
            foreach ($values as $gaValue) {
                $gaValue['price'] = $gaValue['price_modifier'];
                $gaValue['prefix'] = $gaValue['price_prefix'];
                $this->addProductOptionValueAndDescription($product_id, $product_option_id, $gaValue);
            }
        }

        $this->_touch_product($product_id);
        return $product_option_id;
    }

    /**
     * @param int $product_id
     * @param int $product_option_id
     *
     * @throws AException
     */
    public function deleteProductOption($product_id, $product_option_id)
    {
        $am = new AAttribute_Manager();
        $attribute = $am->getAttributeByProductOptionId($product_option_id);
        $group_attribute = $am->getAttributes(['limit' => null], 0, $attribute['attribute_id']);
        if (count($group_attribute)) {
            //delete children options/values
            $children = $this->db->query(
                "SELECT product_option_id
                FROM " . $this->db->table("product_options") . "
                WHERE product_id = '" . (int)$product_id . "'
                    AND group_id = '" . (int)$product_option_id . "'"
            );
            foreach ($children->rows as $g_attribute) {
                $this->_deleteProductOption($product_id, $g_attribute['product_option_id']);
            }
        }

        $this->_deleteProductOption($product_id, $product_option_id);
        $this->_touch_product($product_id);
    }

    /**
     * @param int $product_id
     * @param int $product_option_id
     *
     * @throws AException
     */
    protected function _deleteProductOption($product_id, $product_option_id)
    {
        $values = $this->getProductOptionValues($product_id, $product_option_id);
        foreach ($values as $v) {
            $this->deleteProductOptionValue($product_id, $v['product_option_value_id']);
        }

        $this->db->query(
            "DELETE FROM " . $this->db->table("product_options") . "
            WHERE product_id = '" . (int)$product_id . "'
                AND product_option_id = '" . (int)$product_option_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_option_descriptions") . "
            WHERE product_id = '" . (int)$product_id . "'
                AND product_option_id = '" . (int)$product_option_id . "'"
        );
    }

    //Add new product option value and value descriptions for all global attributes languages or current language

    /**
     * @param int $product_id
     * @param int $option_id
     * @param array $data
     *
     * @return int|null
     * @throws AException
     */
    public function addProductOptionValueAndDescription($product_id, $option_id, $data)
    {
        if (empty($product_id) || empty($option_id) || empty($data)) {
            return null;
        }

        $attribute_value_id = $data['attribute_value_id'];
        if (is_array($data['attribute_value_id'])) {
            $attribute_value_id = '';
        }

        $am = new AAttribute_Manager();
        //build grouped attributes if this is a parent attribute
        if (is_array($data['attribute_value_id'])) {
            //add children option values from global attributes
            $groupData = [];
            foreach ($data['attribute_value_id'] as $child_option_id => $attribute_value_id) {
                #special serialized data for grouped options
                $groupData[] = [
                    'attr_id'   => $child_option_id,
                    'attr_v_id' => $attribute_value_id
                ];
            }
            $data['grouped_attribute_data'] = serialize($groupData);
        }

        $pd_opt_val_id = $this->insertProductOptionValue($product_id, $option_id, $attribute_value_id, '', $data);

        //Build options value descriptions
        if (is_array($data['attribute_value_id'])) {
            //add children option values description from global attributes
            $group_description = [];
            $descr_names = [];
            foreach ($data['attribute_value_id'] as $attribute_value_id) {
                #special insert for grouped options
                foreach ($am->getAttributeValueDescriptions($attribute_value_id) as $language_id => $name) {
                    $group_description[$language_id][] = [
                        'attr_v_id' => $attribute_value_id,
                        'name'      => $name
                    ];
                    $descr_names[$language_id][] = $name;
                }
            }

            // Insert generic merged name
            $grouped_names = null;
            foreach ($descr_names as $language_id => $name) {
                if (count($group_description[$language_id])) {
                    $grouped_names = serialize($group_description[$language_id]);
                }
                $this->insertProductOptionValueDescriptions(
                    $product_id, $pd_opt_val_id, implode(' / ', $name),
                    $language_id, $grouped_names
                );
            }
        } else {
            if (!$data['attribute_value_id']) {
                //We save custom option value for the current language
                $valueDescriptions = [
                    $this->language->getContentLanguageID() => $data['name']
                ];
            } else {
                //We have global attributes, copy option value text from there.
                $valueDescriptions = $am->getAttributeValueDescriptions((int)$data['attribute_value_id']);
            }
            foreach ($valueDescriptions as $language_id => $name) {
                $this->insertProductOptionValueDescriptions($product_id, $pd_opt_val_id, $name, $language_id);
            }
        }

        $this->_touch_product($product_id);
        return $pd_opt_val_id;
    }

    /**
     * @param int $product_id
     * @param int $pd_opt_val_id
     * @param int $language_id
     *
     * @return null|stdClass
     * @throws AException
     */
    public function getProductOptionValueDescriptions($product_id, $pd_opt_val_id, $language_id)
    {
        if (empty($product_id) || empty($pd_opt_val_id) || empty($language_id)) {
            return null;
        }
        return $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_option_value_descriptions") . "
            WHERE product_option_value_id = '" . (int)$pd_opt_val_id . "'
                AND product_id = '" . (int)$product_id . "'
                AND language_id = '" . (int)$language_id . "' "
        );
    }

    /**
     * @param int $product_id
     * @param int $pd_opt_val_id
     * @param string $name
     * @param int $language_id
     * @param null|string $grp_attr_names
     *
     * @return int|null
     * @throws AException
     */
    public function insertProductOptionValueDescriptions(
        $product_id,
        $pd_opt_val_id,
        $name,
        $language_id,
        $grp_attr_names = null
    )
    {
        if (empty($product_id) || empty($pd_opt_val_id) || empty($language_id)) {
            return null;
        }

        $this->language->replaceDescriptions(
            'product_option_value_descriptions',
            [
                'product_option_value_id' => (int)$pd_opt_val_id,
                'product_id'              => (int)$product_id,
            ],
            [
                $language_id => [
                    'name'                    => $name,
                    'grouped_attribute_names' => $grp_attr_names,
                ],
            ]
        );
        $this->cache->remove('product');
        return $this->db->getLastId();
    }

    /**
     * @param int $product_id
     * @param int $pd_opt_val_id
     * @param string $name
     * @param int $language_id
     * @param null|string $grp_attr_names
     *
     * @return null|int
     * @throws AException
     */
    public function updateProductOptionValueDescriptions(
        $product_id,
        $pd_opt_val_id,
        $name,
        $language_id,
        $grp_attr_names = null
    )
    {
        if (empty($product_id) || empty($pd_opt_val_id) || empty($language_id)) {
            return null;
        }
        $this->language->replaceDescriptions(
            'product_option_value_descriptions',
            [
                'product_option_value_id' => (int)$pd_opt_val_id,
                'product_id'              => (int)$product_id,
            ],
            [
                $language_id => [
                    'name'                    => $name,
                    'grouped_attribute_names' => $grp_attr_names,
                ],
            ]
        );
        $this->cache->remove('product');
        return $pd_opt_val_id;
    }

    /**
     * @param int $product_id
     * @param int $pd_opt_val_id
     * @param int $language_id
     *
     * @return null
     * @throws AException
     */
    public function deleteProductOptionValueDescriptions($product_id, $pd_opt_val_id, $language_id = 0)
    {
        if (empty($product_id) || empty($pd_opt_val_id)) {
            return false;
        }
        $add_language = '';
        if ($language_id) {
            $add_language = " AND language_id = '" . (int)$language_id . "'";
        }
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_option_value_descriptions") . "
            WHERE product_id = '" . (int)$product_id . "'
                AND product_option_value_id = '" . (int)$pd_opt_val_id . "'" . $add_language
        );
        $this->cache->remove('product');
        return true;
    }

    /**
     * @param int $product_id
     * @param int $option_id
     * @param int $attribute_value_id
     * @param int $pd_opt_val_id
     * @param array $data
     *
     * @return int|false
     * @throws AException
     */
    public function insertProductOptionValue($product_id, $option_id, $attribute_value_id, $pd_opt_val_id, $data)
    {
        if (empty($product_id) || empty($option_id)) {
            return false;
        }
        $this->db->query(
            "INSERT INTO " . $this->db->table("product_option_values") . "
            SET product_option_id = '" . (int)$option_id . "',
                product_id = '" . (int)$product_id . "',
                group_id = '" . (int)$pd_opt_val_id . "',
                txt_id = '" . $this->db->escape($data['txt_id']) . "',
                sku = '" . $this->db->escape($data['sku']) . "',
                quantity = '" . $this->db->escape($data['quantity']) . "',
                subtract = '" . $this->db->escape($data['subtract']) . "',
                price = '" . preformatFloat($data['price'], $this->language->get('decimal_point')) . "',
                cost = '" . $this->db->escape($data['cost']) . "',
                prefix = '" . $this->db->escape($data['prefix']) . "',
                weight = '" . preformatFloat($data['weight'], $this->language->get('decimal_point')) . "',
                weight_type = '" . $this->db->escape($data['weight_type']) . "',
                attribute_value_id = '" . $this->db->escape($attribute_value_id) . "',
                grouped_attribute_data = '" . $this->db->escape($data['grouped_attribute_data']) . "',
                sort_order = '" . (int)$data['sort_order'] . "',
                `default` = '" . (int)$data['default'] . "',
                supplier_code = " . $this->db->stringOrNull($data['supplier_code']) . ",
                supplier_id = " . $this->db->stringOrNull($data['supplier_id'])
        );
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
        return $this->db->getLastId();
    }

    /**
     *  Update singe product option value
     *
     * @param int $pd_opt_val_id
     * @param int $attribute_value_id
     * @param array $data
     *
     * @return null|int
     * @throws AException
     */
    public function updateProductOptionValue($pd_opt_val_id, $attribute_value_id, $data)
    {
        if (!$pd_opt_val_id || !$data) {
            return null;
        }
        $data['attribute_value_id'] = $data['attribute_value_id'] ?: $attribute_value_id;
        //If se have grouped (parent/child) options, save no main attribute id
        if (is_array($attribute_value_id)) {
            $data['attribute_value_id'] = '';
        }

        $this->data['columns'] = [
            'txt_id'                 => 'string',
            'sku'                    => 'string',
            'quantity'               => 'int',
            'subtract'               => 'int',
            'price'                  => 'float',
            'cost'                   => 'float',
            'prefix'                 => 'string',
            'require_shipping'       => 'int',
            'weight'                 => 'float',
            'weight_type'            => 'string',
            'attribute_value_id'     => 'int',
            'grouped_attribute_data' => 'string',
            'sort_order'             => 'int',
            'default'                => 'int',
            'supplier_code'          => 'string',
            'supplier_id'            => 'string'
        ];
        $this->extensions->hk_InitData($this, __FUNCTION__);
        $updArray = [];
        foreach ($this->data['columns'] as $column => $type) {
            if (isset($data[$column])) {
                $value = '';
                if ($type == 'int') {
                    $value = (int)$data[$column];
                } elseif ($type == 'string') {
                    $value = $this->db->stringOrNull($data[$column]);
                } elseif ($type == 'float') {
                    if ($column == 'weight') {
                        $data[$column] = preformatFloat($data[$column], $this->language->get('decimal_point'));
                    }
                    $value = (float)$data[$column];
                }
                $updArray[] = "`" . $column . "` =  " . $value;
            }
        }
        if (!$updArray) {
            return false;
        }

        $this->db->query(
            "UPDATE " . $this->db->table("product_option_values") . "
            SET " . implode(",\n", $updArray) . "
            WHERE product_option_value_id = '" . (int)$pd_opt_val_id . "'  "
        );
        return $pd_opt_val_id;
    }

    /**
     *    Update product option value and value descriptions for set language
     *
     * @param int $product_id
     * @param int $pd_opt_val_id
     * @param array $data
     * @param int $language_id
     *
     * @throws AException
     */
    public function updateProductOptionValueAndDescription($product_id, $pd_opt_val_id, $data, $language_id)
    {
        $attribute_value_id = $data['attribute_value_id'];
        if (is_array($data['attribute_value_id'])) {
            $attribute_value_id = '';
        }

        $am = new AAttribute_Manager();
        //build grouped attributes if this is a parent attribute
        if (is_array($data['attribute_value_id'])) {
            //update children option values from global attributes
            $groupData = [];
            foreach ($data['attribute_value_id'] as $child_option_id => $attr_val_id) {
                #special serialized data for grouped options
                $groupData[] = [
                    'attr_id'   => $child_option_id,
                    'attr_v_id' => $attr_val_id
                ];
            }
            $data['grouped_attribute_data'] = serialize($groupData);
        }

        $this->updateProductOptionValue($pd_opt_val_id, $attribute_value_id, $data);

        if (is_array($data['attribute_value_id'])) {
            //update children option values description from global attributes
            $group_description = [];
            $descr_names = [];
            foreach ($data['attribute_value_id'] as $attr_val_id) {
                #special insert for grouped options
                foreach ($am->getAttributeValueDescriptions($attr_val_id) as $lang_id => $name) {
                    if ($language_id == $lang_id) {
                        $group_description[$language_id][] = [
                            'attr_v_id' => $attr_val_id,
                            'name'      => $name
                        ];
                        $descr_names[$language_id][] = $name;
                    }
                }
            }
            // Insert generic merged name
            foreach ($descr_names as $lang_id => $name) {
                if ($language_id == $lang_id && count($group_description[$language_id])) {
                    $group_description[$language_id][] = $name;
                    $grouped_names = serialize($group_description[$language_id]);
                    $this->updateProductOptionValueDescriptions(
                        $product_id, $pd_opt_val_id, implode(' / ', $name),
                        $language_id, $grouped_names
                    );
                }
            }
        } else {
            if (!$data['attribute_value_id']) {
                $exist = $this->getProductOptionValueDescriptions($product_id, $pd_opt_val_id, $language_id);
                if ($exist->num_rows) {
                    $this->updateProductOptionValueDescriptions(
                        $product_id, $pd_opt_val_id, $data['name'],
                        $language_id
                    );
                } else {
                    $this->insertProductOptionValueDescriptions(
                        $product_id, $pd_opt_val_id, $data['name'],
                        $language_id
                    );
                }
            } else {
                $valueDescriptions = $am->getAttributeValueDescriptions((int)$data['attribute_value_id']);
                foreach ($valueDescriptions as $lang_id => $name) {
                    if ($language_id == $lang_id) {
                        //Update only the language that we currently work with
                        $this->updateProductOptionValueDescriptions($product_id, $pd_opt_val_id, $name, $language_id);
                    }
                }
            }
        }
        $this->_touch_product($product_id);
    }

    /**
     * @param int $product_id
     * @param int $pd_opt_val_id
     * @param int $language_id
     *
     * @return null
     * @throws AException
     */
    public function deleteProductOptionValue($product_id, $pd_opt_val_id, $language_id = 0)
    {
        if (empty($product_id) || empty($pd_opt_val_id)) {
            return false;
        }

        $this->_deleteProductOptionValue($product_id, $pd_opt_val_id, $language_id);

        //delete children values
        $children = $this->db->query(
            "SELECT product_option_value_id 
            FROM " . $this->db->table("product_option_values") . "
            WHERE product_id = '" . (int)$product_id . "'
                AND group_id = '" . (int)$pd_opt_val_id . "'"
        );
        foreach ($children->rows as $g_attribute) {
            $this->_deleteProductOptionValue($product_id, $g_attribute['product_option_value_id'], $language_id);
        }

        $this->_touch_product($product_id);
        return true;
    }

    /**
     * @param int $product_id
     * @param int $pd_opt_val_id
     * @param int $language_id
     *
     * @return bool
     * @throws AException
     */
    public function _deleteProductOptionValue($product_id, $pd_opt_val_id, $language_id)
    {
        if (empty($product_id) || empty($pd_opt_val_id)) {
            return false;
        }
        $add_language = '';
        if ($language_id) {
            $add_language = " AND language_id = '" . (int)$language_id . "'";
        }

        $this->db->query(
            "DELETE FROM " . $this->db->table("product_option_value_descriptions") . "
            WHERE product_id = '" . (int)$product_id . "'
                AND product_option_value_id = '" . (int)$pd_opt_val_id . "'" . $add_language
        );

        //Delete product_option_values that have no values left in descriptions
        $sql = "DELETE FROM " . $this->db->table("product_option_values") . " 
                WHERE product_option_value_id = '" . (int)$pd_opt_val_id . "' 
                    AND product_option_value_id NOT IN 
                                                ( SELECT product_option_value_id 
                                                  FROM " . $this->db->table("product_option_value_descriptions") . "
                                                  WHERE product_id = '" . (int)$product_id . "'
                                                    AND product_option_value_id = '" . (int)$pd_opt_val_id . "')";
        $this->db->query($sql);
        //get product resources
        $rm = new AResourceManager();
        $resources = $rm->getResourcesList(
            [
                'object_name' => 'product_option_value',
                'object_id'   => (int)$pd_opt_val_id,
            ]
        );
        foreach ($resources as $r) {
            $rm->unmapResource(
                'product_option_value',
                $pd_opt_val_id,
                $r['resource_id']
            );
            //remove if orphan
            if (!$rm->isMapped($r['resource_id'])) {
                $rm->deleteResource($r['resource_id']);
            }
        }
        return true;
    }

    /**
     * @param int $product_id
     *
     * @return bool|array
     * @throws AException
     */
    public function copyProduct($product_id)
    {
        if (empty($product_id)) {
            return false;
        }

        $sql = "SELECT DISTINCT *, p.product_id
                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') . "')
                WHERE p.product_id = '" . (int)$product_id . "'";
        $query = $this->db->query($sql);

        if (!$query->num_rows) {
            return false;
        }

        $data = $query->row;
        $data = array_merge($data, ['product_description' => $this->getProductDescriptions($product_id)]);
        foreach ($data['product_description'] as $lang => $desc) {
            $data['product_description'][$lang]['name'] .= ' ( Copy )';
        }
        $data = array_merge($data, ['product_option' => $this->getProductOptions($product_id)]);
        $data['keyword'] = '';

        $data = array_merge($data, ['product_discount' => $this->getProductDiscounts($product_id)]);
        $data = array_merge($data, ['product_special' => $this->getProductSpecials($product_id)]);
        $data = array_merge($data, ['product_download' => $this->getProductDownloads($product_id)]);
        $data = array_merge($data, ['product_category' => $this->getProductCategories($product_id)]);
        $data = array_merge($data, ['product_store' => $this->getProductStores($product_id)]);
        $data = array_merge($data, ['product_related' => $this->getProductRelated($product_id)]);
        $data = array_merge($data, ['product_tags' => $this->getProductTags($product_id)]);
        $data = array_merge($data, ['keyword' => $this->getProductSEOKeywords($product_id)]);

        //set status to off for a cloned product
        $data['status'] = 0;

        //get product resources
        $rm = new AResourceManager();
        $resources = $rm->getResourcesList(
            [
                'object_name' => 'products',
                'object_id'   => $product_id,
                'sort'        => 'sort_order',
            ]
        );

        $new_product_id = $this->addProduct($data);

        foreach ($data['product_discount'] as $item) {
            $this->addProductDiscount($new_product_id, $item);
        }
        foreach ($data['product_special'] as $item) {
            $this->addProductSpecial($new_product_id, $item);
        }

        $this->updateProductLinks($new_product_id, $data);
        $this->_clone_product_options($new_product_id, $data);

        foreach ($resources as $r) {
            $rm->mapResource(
                'products',
                $new_product_id,
                $r['resource_id']
            );
        }

        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);

        //clone layout for the product if present
        $layout_clone_result = $this->_clone_product_layout($product_id, $new_product_id);

        return [
            'name'         => $data['name'],
            'id'           => $new_product_id,
            'layout_clone' => $layout_clone_result,
        ];
    }

    /**
     * @param int $product_id
     * @param array $data
     *
     * @throws AException
     */
    protected function _clone_product_options($product_id, $data)
    {
        //Do not use before close review.
        //Note: This is done only after product cloning. This is not to be used on existing product.
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_options") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_option_descriptions") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_option_values") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_option_value_descriptions") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );

        if (isset($data['product_option'])) {
            foreach ($data['product_option'] as $product_option) {
                $sql = "INSERT INTO " . $this->db->table("product_options") . " 
                        SET product_id = '" . (int)$product_id . "',
                            sort_order = '" . (int)$product_option['sort_order'] . "'";
                if ($product_option['attribute_id']) {
                    $sql .= ", attribute_id = '" . (int)$product_option['attribute_id'] . "'";
                }
                if ($product_option['group_id']) {
                    $sql .= ", group_id = '" . (int)$product_option['group_id'] . "'";
                }
                if ($product_option['element_type']) {
                    $sql .= ", element_type = '" . $this->db->escape($product_option['element_type']) . "'";
                }
                if ($product_option['required']) {
                    $sql .= ", required = '" . (int)$product_option['required'] . "'";
                }
                if ($product_option['regexp_pattern']) {
                    $sql .= ", regexp_pattern = '" . $this->db->escape($product_option['regexp_pattern']) . "'";
                }
                $this->db->query($sql);
                $product_option_id = $this->db->getLastId();

                foreach ($product_option['language'] as $language_id => $language) {
                    $this->language->replaceDescriptions(
                        'product_option_descriptions',
                        [
                            'product_option_id' => (int)$product_option_id,
                            'product_id'        => (int)$product_id,
                        ],
                        [
                            $language_id => [
                                'name'               => $language['name'],
                                'error_text'         => $language['error_text'],
                                'option_placeholder' => $language['placeholder'],
                            ],
                        ]
                    );
                }

                if (isset($product_option['product_option_value'])) {
                    //get product resources
                    $rm = new AResourceManager();
                    foreach ($product_option['product_option_value'] as $pd_opt_vals) {
                        $pd_opt_vals['price'] = str_replace(" ", "", $pd_opt_vals['price']);

                        $this->db->query(
                            "INSERT INTO " . $this->db->table("product_option_values") . " 
                            SET product_option_id = '" . (int)$product_option_id . "',
                                product_id = '" . (int)$product_id . "',
                                txt_id = '" . $this->db->escape($pd_opt_vals['txt_id']) . "',
                                sku = '" . $this->db->escape($pd_opt_vals['sku']) . "',
                                quantity = '" . (int)$pd_opt_vals['quantity'] . "',
                                subtract = '" . (int)$pd_opt_vals['subtract'] . "',
                                price = '" . preformatFloat(
                                $pd_opt_vals['price'],
                                $this->language->get('decimal_point')
                            ) . "',
                                weight = '" . (float)$pd_opt_vals['weight'] . "',
                                weight_type = '" . $this->db->escape($pd_opt_vals['weight_type']) . "',
                                prefix = '" . $this->db->escape($pd_opt_vals['prefix']) . "',
                                attribute_value_id = '" . $this->db->escape($pd_opt_vals['attribute_value_id']) . "',
                                grouped_attribute_data = '" . $this->db->escape($pd_opt_vals['grouped_attribute_data']) . "',
                                group_id = '" . $this->db->escape($pd_opt_vals['group_id']) . "',
                                sort_order = '" . (int)$pd_opt_vals['sort_order'] . "',
                                `default` = '" . (int)$pd_opt_vals['default'] . "'"
                        );

                        $pd_opt_val_id = $this->db->getLastId();
                        // clone resources of option value
                        if ($pd_opt_vals['product_option_value_id']) {
                            $resources = $rm->getResourcesList(
                                [
                                    'object_name' => 'product_option_value',
                                    'object_id'   => $pd_opt_vals['product_option_value_id'],
                                ]
                            );
                            foreach ($resources as $r) {
                                $rm->mapResource(
                                    'product_option_value',
                                    $pd_opt_val_id,
                                    $r['resource_id']
                                );
                            }
                        }

                        foreach ($pd_opt_vals['language'] as $language_id => $lang_data) {
                            $grouped_attribute_names = serialize($lang_data['children_options_names']);

                            $this->language->replaceDescriptions(
                                'product_option_value_descriptions',
                                [
                                    'product_option_value_id' => (int)$pd_opt_val_id,
                                    'product_id'              => (int)$product_id,
                                ],
                                [
                                    $language_id => [
                                        'name'                    => $lang_data['name'],
                                        'grouped_attribute_names' => $grouped_attribute_names,
                                    ],
                                ]
                            );
                        }
                    }
                }
            }
        }

        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
    }

    /**
     * @param int $product_id
     * @param int $new_product_id
     *
     * @return null
     * @throws AException
     */
    protected function _clone_product_layout($product_id, $new_product_id)
    {
        if (!has_value($product_id) && !has_value($new_product_id)) {
            return false;
        }

        //clone layout for the product if present
        $lm = new ALayoutManager();
        $pages = $lm->getPages('pages/product/product', 'product_id', (int)$product_id);
        if (count($pages) && has_value($pages[0]['page_id'])) {
            $tmpl_id = $this->config->get('config_storefront_template');
            $src_layout_id = $pages[0]['layout_id'];
            $src_page_id = $pages[0]['page_id'];
            //create an instance for source layout
            $lm = new ALayoutManager($tmpl_id, $src_page_id, $src_layout_id);
            //create a new page
            $page_info = [
                'controller' => 'pages/product/product',
                'key_param'  => 'product_id',
                'key_value'  => $new_product_id,
            ];

            $product_info = $this->getProductDescriptions($new_product_id);
            if ($product_info) {
                foreach ($product_info as $language_id => $description) {
                    if (!has_value($language_id)) {
                        continue;
                    }
                    $page_info['page_descriptions'][$language_id] = $description;
                }
            }
            //save a new page
            $new_page_id = $lm->savePage($page_info);

            $default_language_id = $this->language->getDefaultLanguageID();
            $layout_name = 'Product: ' . $product_info[$default_language_id]['name'];
            //create instance for new layout
            $lm = new ALayoutManager($tmpl_id, $new_page_id, '');
            return $lm->clonePageLayout($src_layout_id, '', $layout_name);
        }
        return false;
    }

    /**
     * @param int $product_id
     *
     * @return bool
     * @throws AException
     */
    public function deleteProduct($product_id)
    {
        if (!(int)$product_id) {
            return false;
        }
        $rm = new AResourceManager();
        $this->db->query(
            "DELETE FROM " . $this->db->table("products") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_descriptions") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $resources = $rm->getResourcesList(
            [
                'object_name' => 'products',
                'object_id'   => (int)$product_id,
            ]
        );
        foreach ($resources as $r) {
            $rm->unmapResource(
                'products',
                $product_id,
                $r['resource_id']
            );
            //if resource becomes orphan - delete it
            if (!$rm->isMapped($r['resource_id'])) {
                $rm->deleteResource($r['resource_id']);
            }
        }

        //delete product's downloads
        $this->load->model('catalog/download');
        $downloads = $this->model_catalog_download->getProductDownloadsDetails(
            $product_id,
            ['subsql_filter' => 'AND d.shared<>1']
        );
        foreach ($downloads as $d) {
            $this->model_catalog_download->deleteDownload($d['download_id']);
        }

        //cascade removing product options with values and their images
        $options = $this->getProductOptions($product_id);
        foreach ($options as $option) {
            $this->_deleteProductOption($product_id, $option['product_option_id']);
        }

        $this->db->query(
            "DELETE FROM " . $this->db->table("product_discounts") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("products_related") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("products_to_downloads") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("products_to_categories") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("reviews") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("products_to_stores") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("url_aliases") . " 
            WHERE query = 'product_id=" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_tags") . " 
            WHERE product_id='" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("products_featured") . " 
            WHERE product_id='" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_specials") . " 
            WHERE product_id='" . (int)$product_id . "'"
        );
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_stock_locations") . " 
            WHERE product_id='" . (int)$product_id . "'"
        );

        $lm = new ALayoutManager();
        $lm->deletePageLayout('pages/product/product', 'product_id', (int)$product_id);

        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
        return true;
    }

    /**
     * @param int $product_discount_id
     *
     * @throws AException
     */
    public function deleteProductDiscount($product_discount_id)
    {
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_discounts") . " 
            WHERE product_discount_id = '" . (int)$product_discount_id . "'"
        );
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
    }

    /**
     * @param int $product_special_id
     *
     * @throws AException
     */
    public function deleteProductSpecial($product_special_id)
    {
        $this->db->query(
            "DELETE FROM " . $this->db->table("product_specials") . " 
            WHERE product_special_id='" . (int)$product_special_id . "'"
        );
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProduct($product_id)
    {
        $languageId = (int)$this->language->getContentLanguageID();
        $query = $this->db->query(
            "SELECT DISTINCT *, p.product_id, COALESCE(pf.product_id, 0) as featured,
                (SELECT keyword
                 FROM " . $this->db->table("url_aliases") . " 
                 WHERE query = 'product_id=" . (int)$product_id . "'
                    AND language_id='" . $languageId . "' ) AS keyword
            FROM " . $this->db->table("products") . " p
            LEFT JOIN " . $this->db->table("products_featured") . " pf 
                ON pf.product_id = p.product_id
            LEFT JOIN " . $this->db->table("product_descriptions") . " pd
                    ON (p.product_id = pd.product_id
                            AND pd.language_id = '" . $languageId . "')
            WHERE p.product_id = '" . (int)$product_id . "'"
        );
        return $query->row;
    }

    /**
     * @param int $product_discount_id
     *
     * @return array
     * @throws AException
     */
    public function getProductDiscount($product_discount_id)
    {
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_discounts") . " 
            WHERE product_discount_id = '" . (int)$product_discount_id . "'"
        );
        return $query->row;
    }

    /**
     * @param int $product_special_id
     *
     * @return array
     * @throws AException
     */
    public function getProductSpecial($product_special_id)
    {
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_specials") . " 
            WHERE product_special_id = '" . (int)$product_special_id . "'"
        );
        return $query->row;
    }

    /**
     * @param int $product_id
     * @param bool $act
     *
     * @throws AException
     */
    public function setFeatured($product_id, $act = true)
    {
        $this->db->query(
            "DELETE FROM " . $this->db->table("products_featured") . " 
            WHERE product_id='" . (int)$product_id . "'"
        );
        if ($act) {
            $this->db->query(
                "INSERT INTO " . $this->db->table("products_featured") . " 
                 SET product_id = '" . (int)$product_id . "'"
            );
        }
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
    }

    /**
     * @param array $data
     *
     * @throws AException
     */
    public function addFeatured($data)
    {
        $this->db->query("DELETE FROM " . $this->db->table("products_featured"));
        if (isset($data['featured_product'])) {
            foreach ($data['featured_product'] as $product_id) {
                $this->db->query(
                    "INSERT INTO " . $this->db->table("products_featured") . " 
                    SET product_id = '" . (int)$product_id . "'"
                );
            }
        }
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
    }

    /**
     * @return array
     * @throws AException
     */
    public function getFeaturedProducts()
    {
        $query = $this->db->query(
            "SELECT product_id 
            FROM " . $this->db->table("products_featured")
        );
        $featured = [];
        foreach ($query->rows as $product) {
            $featured[] = $product['product_id'];
        }
        return $featured;
    }

    /**
     * @param string $keyword
     *
     * @return array
     * @throws AException
     */
    public function getProductsByKeyword($keyword)
    {
        if ($keyword) {
            $query = $this->db->query(
                "SELECT *, p.product_id
                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') . "')
                WHERE (LCASE(pd.name) LIKE '%" . $this->db->escape(mb_strtolower($keyword), true) . "%'
                        OR LCASE(p.model) LIKE '%" . $this->db->escape(mb_strtolower($keyword), true) . "%')"
            );
            return $query->rows;
        } else {
            return [];
        }
    }

    /**
     * @param        $category_id
     * @param string $mode
     *
     * @return array|int
     * @throws AException
     */
    public function getProductsByCategoryId($category_id, $mode = 'default')
    {
        $query = $this->db->query(
            "SELECT *, p.product_id
            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_categories") . " p2c 
                ON (p.product_id = p2c.product_id)
            WHERE p2c.category_id = '" . (int)$category_id . "'
            ORDER BY pd.name"
        );
        if ($mode == 'total_only') {
            return $query->num_rows;
        }
        return $query->rows;
    }

    /**
     * @param int $product_id
     * @param int $language_id
     *
     * @return array
     * @throws AException
     */
    public function getProductDescriptions($product_id, $language_id = 0)
    {
        $product_description_data = [];
        $language_id = (int)$language_id;

        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_descriptions") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );

        foreach ($query->rows as $result) {
            $product_description_data[$result['language_id']] = [
                'name'             => $result['name'],
                'meta_keywords'    => $result['meta_keywords'],
                'meta_description' => $result['meta_description'],
                'description'      => $result['description'],
                'blurb'            => $result['blurb'],
            ];
        }

        return $language_id ? $product_description_data[$language_id] : $product_description_data;
    }

    /**
     * check attribute before add to product options
     * can't add the attribute that is already in group attribute that assigned to product
     *
     * @param $product_id
     * @param $attribute_id
     *
     * @return int
     * @throws AException
     */
    public function isProductGroupOption($product_id, $attribute_id)
    {
        $product_option = $this->db->query(
            "SELECT COUNT(*) as total 
            FROM " . $this->db->table("product_options") . "
            WHERE product_id = '" . (int)$product_id . "'
                AND attribute_id = '" . (int)$attribute_id . "'
                AND group_id != 0
            ORDER BY sort_order"
        );
        return $product_option->row['total'];
    }

    /**
     * @param int $attribute_id
     * @param int $group_id
     *
     * @return int
     * @throws AException
     */
    public function getProductOptionByAttributeId($attribute_id, $group_id)
    {
        $product_option = $this->db->query(
            "SELECT product_option_id 
            FROM " . $this->db->table("product_options") . "
            WHERE attribute_id = '" . (int)$attribute_id . "'
                AND group_id = '" . (int)$group_id . "'
            ORDER BY sort_order"
        );
        return $product_option->row['product_option_id'];
    }

    /**
     *    Get single option data
     *
     * @param int $product_id
     * @param int $option_id
     *
     * @return array|null
     * @throws AException
     */
    public function getProductOption($product_id, $option_id = 0)
    {
        $product_option = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_options") . " 
            WHERE product_id = '" . (int)$product_id . "'
                AND product_option_id = '" . (int)$option_id . "'
            ORDER BY sort_order"
        );

        $product_option_description = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_option_descriptions") . "
            WHERE product_option_id = '" . (int)$option_id . "'"
        );
        $product_option_description_data = [];
        foreach ($product_option_description->rows as $result) {
            $product_option_description_data[$result['language_id']] = [
                'name'               => $result['name'],
                'option_placeholder' => $result['option_placeholder'],
                'error_text'         => $result['error_text'],
            ];
        }

        if ($product_option->num_rows) {
            $row = $product_option->row;
            $row['language'] = $product_option_description_data;
            return $row;
        } else {
            return null;
        }
    }

    /**
     * @param int $product_option_id
     * @param array $data
     *
     * @throws AException
     */
    public function updateProductOption($product_option_id, $data)
    {
        $fields = [
            "sort_order",
            "status",
            "required",
            "regexp_pattern",
            "settings",
        ];
        $update = [];
        foreach ($fields as $f) {
            if (isset($data[$f])) {
                if ($f == 'settings') {
                    $data[$f] = serialize($data[$f]);
                }
                $update[] = $f . " = '" . $this->db->escape($data[$f]) . "'";
            }
        }
        if (!empty($update)) {
            $this->db->query(
                "UPDATE " . $this->db->table("product_options") . " 
                SET " . implode(',', $update) . "
                WHERE product_option_id = '" . (int)$product_option_id . "'"
            );
        }

        if (!empty($data['name'])) {
            $language_id = $this->language->getContentLanguageID();

            $this->language->replaceDescriptions(
                'product_option_descriptions',
                ['product_option_id' => (int)$product_option_id],
                [
                    (int)$language_id => [
                        'name'               => $data['name'],
                        'error_text'         => $data['error_text'],
                        'option_placeholder' => $data['option_placeholder'],
                    ],
                ]
            );
        }

        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
    }

    /**
     * Main method to get complete options data for the product
     *
     * @param int $product_id
     * @param int $group_id
     *
     * @return array
     * @throws AException
     */
    public function getProductOptions($product_id, $group_id = 0)
    {
        $product_option_data = [];
        $group_select = '';
        if (is_int($group_id)) {
            $group_select = "AND group_id = '" . (int)$group_id . "'";
        }
        $product_option = $this->db->query(
            "SELECT *
             FROM " . $this->db->table("product_options") . " 
             WHERE product_id = '" . (int)$product_id . "' "
            . $group_select .
            " ORDER BY sort_order"
        );
        foreach ($product_option->rows as $product_option) {
            $option_data = $this->getProductOption($product_id, $product_option['product_option_id']);
            $option_data['product_option_value'] = $this->getProductOptionValues(
                $product_id,
                $product_option['product_option_id']
            );
            $product_option_data[] = $option_data;
        }
        return $product_option_data;
    }

    /**
     *    Main function to be called to update option values.
     *
     * @param int $product_id
     * @param int $option_id
     * @param array $data
     *
     * @return null
     * @throws AException
     */
    public function updateProductOptionValues($product_id, $option_id, $data)
    {
        if (!is_array($data['product_option_value_id']) || !$option_id || !$product_id) {
            return false;
        }
        $language_id = $this->language->getContentLanguageID();

        foreach ($data['product_option_value_id'] as $opt_val_id => $status) {
            $option_value_data = [
                'attribute_value_id'     => $data['attribute_value_id'][$opt_val_id],
                'grouped_attribute_data' => $data['grouped_attribute_data'][$opt_val_id],
                'name'                   => $data['name'][$opt_val_id],
                'sku'                    => $data['sku'][$opt_val_id],
                'txt_id'                 => $data['txt_id'][$opt_val_id],
                'quantity'               => $data['quantity'][$opt_val_id],
                'subtract'               => $data['subtract'][$opt_val_id],
                'price'                  => preformatFloat(
                    $data['price'][$opt_val_id],
                    $this->language->get('decimal_point')
                ),
                'cost'                   => $data['cost'][$opt_val_id],
                'prefix'                 => $data['prefix'][$opt_val_id],
                'sort_order'             => $data['sort_order'][$opt_val_id],
                'require_shipping'       => $data['require_shipping'][$opt_val_id],
                'weight'                 => $data['weight'][$opt_val_id],
                'weight_type'            => $data['weight_type'][$opt_val_id],
                'default'                => ($data['default_value'] == $opt_val_id ? 1 : 0),
            ];

            //Check if new, delete or update
            if ($status == 'delete' && !str_contains($opt_val_id, 'new')) {
                //delete this option value for all languages
                $this->deleteProductOptionValue($product_id, $opt_val_id);
            } else {
                if ($status == 'new') {
                    // Need to create a new option value
                    $this->addProductOptionValueAndDescription(
                        $product_id,
                        $option_id,
                        $option_value_data
                    );
                } else {
                    //Existing need to update
                    $this->updateProductOptionValueAndDescription(
                        $product_id,
                        $opt_val_id,
                        $option_value_data,
                        $language_id
                    );
                }
            }
        }
        $this->_touch_product($product_id);
        return true;
    }

    /**
     * @param int $product_id
     * @param int $option_value_id
     *
     * @return array
     * @throws AException
     */
    public function getProductOptionValue($product_id, $option_value_id)
    {
        $product_option_value = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_option_values") . "
            WHERE product_id = '" . (int)$product_id . "'
                AND product_option_value_id = '" . (int)$option_value_id . "'
                AND group_id = 0
            ORDER BY sort_order"
        );

        $option_value = $product_option_value->row;
        $value_description_data = [];
        $value_description = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_option_value_descriptions") . "
            WHERE product_option_value_id = '" . (int)$option_value['product_option_value_id'] . "'"
        );

        foreach ($value_description->rows as $description) {
            //regular option value name
            $value_description_data[$description['language_id']]['name'] = $description['name'];
            //get children (grouped options) individual names array
            if ($description['grouped_attribute_names']) {
                $value_description_data[$description['language_id']]['children_options_names'] =
                    unserialize($description['grouped_attribute_names']);
            }
        }

        $result = array_merge(
            $option_value,
            [
                'language' => $value_description_data
            ]
        );

        //get children (grouped options) data
        $child_option_values = unserialize($result['grouped_attribute_data']);
        if (is_array($child_option_values) && sizeof($child_option_values)) {
            $result['children_options'] = [];
            foreach ($child_option_values as $child_value) {
                $result['children_options'][$child_value['attr_id']] = (int)$child_value['attr_v_id'];
            }
        }

        return $result;
    }

    /**
     * @param int $product_id
     * @param int $option_id
     *
     * @return array
     * @throws AException
     */
    public function getProductOptionValues($product_id, $option_id = 0)
    {
        $result = [];
        $product_option_value = $this->db->query(
            "SELECT product_option_value_id 
            FROM " . $this->db->table("product_option_values") . "
            WHERE product_id = '" . (int)$product_id . "'
                " . ($option_id ? "AND product_option_id = '" . (int)$option_id . "'" : '') . "
            ORDER BY product_option_id, sort_order"
        );

        foreach ($product_option_value->rows as $option_value) {
            $result[] = $this->getProductOptionValue($product_id, $option_value['product_option_value_id']);
        }

        return $result;
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductDiscounts($product_id)
    {
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_discounts") . " 
            WHERE product_id = '" . (int)$product_id . "'
            ORDER BY quantity, priority, price"
        );

        return $query->rows;
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductSpecials($product_id)
    {
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_specials") . " 
            WHERE product_id = '" . (int)$product_id . "'
            ORDER BY priority, price"
        );
        return $query->rows;
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductDownloads($product_id)
    {
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("products_to_downloads") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        return filterIntegerIdList(array_column($query->rows, 'download_id'));
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductStores($product_id)
    {
        return array_map('intval', array_column($this->getProductStoresInfo($product_id), 'store_id'));
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductStoresInfo($product_id)
    {
        $product_id = (int)$product_id;
        if (!$product_id) {
            return [];
        }
        $query = $this->db->query(
            "SELECT p2s.*,
                s.name as store_name,
                ss.`value` as store_url,
                sss.`value` as store_ssl_url
            FROM " . $this->db->table("products_to_stores") . " p2s
            LEFT JOIN " . $this->db->table("stores") . " s ON s.store_id = p2s.store_id
            LEFT JOIN " . $this->db->table("settings") . " ss
                ON (ss.store_id = p2s.store_id AND ss.`key`='config_url')
            LEFT JOIN " . $this->db->table("settings") . " sss
                ON (sss.store_id = p2s.store_id AND sss.`key`='config_ssl_url')
            WHERE p2s.product_id = '" . (int)$product_id . "'"
        );

        return $query->rows;
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductCategories($product_id)
    {
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("products_to_categories") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        return filterIntegerIdList(array_column($query->rows, 'category_id'));
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductRelated($product_id)
    {
        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("products_related") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );
        return filterIntegerIdList(array_column($query->rows, 'related_id'));
    }

    /**
     * @param int $product_id
     * @param int $language_id
     *
     * @return array
     * @throws AException
     */
    public function getProductTags($product_id, $language_id = 0)
    {
        $language_id = (int)$language_id;
        $product_tag_data = [];

        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("product_tags") . " 
            WHERE product_id = '" . (int)$product_id . "'"
        );

        $tag_data = [];

        foreach ($query->rows as $result) {
            $tag_data[$result['language_id']][] = $result['tag'];
        }

        foreach ($tag_data as $language => $tags) {
            $product_tag_data[$language] = implode(',', $tags);
        }

        if ($language_id) {
            return $product_tag_data[$language_id];
        } else {
            return $product_tag_data;
        }
    }

    /**
     * @param int $product_id
     * @param int $language_id
     *
     * @return array
     * @throws AException
     */
    public function getProductSEOKeywords($product_id, $language_id = 0)
    {
        $language_id = (int)$language_id;

        $query = $this->db->query(
            "SELECT *
            FROM " . $this->db->table("url_aliases") . "
            WHERE `query` = 'product_id=" . (int)$product_id . "'"
        );
        $product_seo_keys = array_column($query->rows, 'keyword', 'language_id');

        if ($language_id) {
            return $product_seo_keys[$language_id];
        } else {
            return $product_seo_keys;
        }
    }

    /**
     * @param array $data
     * @param string $mode
     *
     * @return array|int
     * @throws AException
     */
    public function getProducts($data = [], $mode = 'default')
    {
        $language_id = (int)$data['content_language_id'] ?: $this->config->get('storefront_language_id');
        $store_id = (int)($data['store_id'] ?? $this->config->get('current_store_id'));

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

            if ($mode == 'total_only') {
                $sql = "SELECT COUNT(*) as total ";
            } else {
                $sql = "SELECT " . $this->db->getSqlCalcTotalRows() . " DISTINCT pd.*, p.* ";
                $sql .= ", (SELECT 
                                CASE WHEN SUM(COALESCE(ppov.subtract,0))>0
                                 THEN SUM( CASE WHEN ppov.quantity > 0 THEN ppov.quantity ELSE 0 END)
                                ELSE pp.quantity END as quantity
                            FROM " . $this->db->table("products") . " pp
                            LEFT JOIN " . $this->db->table("product_options") . " ppo
                                ON ppo.product_id = pp.product_id
                            LEFT JOIN  " . $this->db->table("product_option_values") . " ppov
                                ON (ppo.product_option_id = ppov.product_option_id AND ppov.subtract>0)
                            WHERE pp.product_id = p.product_id
                            GROUP BY pp.product_id) as quantity ";
            }
            $sql .= " 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 . "')
                    INNER JOIN " . $this->db->table('products_to_stores') . " ps
                        ON (p.product_id = ps.product_id AND ps.store_id = '" . $store_id . "') ";

            if ($filter['category']) {
                $sql .= " INNER JOIN " . $this->db->table("products_to_categories") . " p2c 
                            ON (p.product_id = p2c.product_id) ";
            }
            $sql .= ' WHERE 1=1 ';

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

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

            if (isset($filter['match'])) {
                $match = $filter['match'];
            }

            if (isset($filter['exclude']['product_id'])) {
                $exclude = $filter['exclude']['product_id'];
                $excludes = [];
                if (is_array($exclude)) {
                    foreach ($exclude as $ex) {
                        $excludes[] = (int)$ex;
                    }
                } elseif ((int)$exclude) {
                    $excludes = [(int)$exclude];
                }

                if ($excludes) {
                    $sql .= " AND p.product_id NOT IN (" . implode(',', $excludes) . ") ";
                }
            }

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

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

            if (isset($filter['pfrom'])) {
                $sql .= " AND p.price >= '" . (float)$filter['pfrom'] . "'";
            }
            if (isset($filter['pto'])) {
                $sql .= " AND p.price <= '" . (float)$filter['pto'] . "'";
            }
            if ($filter['category']) {
                /** @var ModelCatalogCategory $mdl */
                $mdl = $this->load->model('catalog/category', 'storefront');
                $childrenIds = $mdl->getChildrenIds($filter['category'], 'all');
                $childrenIds[] = (int)$filter['category'];
                $childrenIds = array_filter(array_unique($childrenIds));
                $sql .= " AND p2c.category_id IN (" . (implode(',', $childrenIds)) . ")";
            }
            if ($filter['sku']) {
                $sql .= " AND p.sku LIKE '%" . $this->db->escape($filter['sku']) . "%'";
            }
            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);
                return $query->row['total'];
            }

            $sort_data = array_merge(
                [
                    'product_id'    => 'p.product_id',
                    'name'          => 'pd.name',
                    'model'         => 'p.model',
                    'sku'           => 'p.sku',
                    'quantity'      => 'quantity',
                    'price'         => 'p.price',
                    'status'        => 'p.status',
                    'sort_order'    => 'p.sort_order',
                    'date_modified' => 'p.date_modified',
                ],
                (array)$data['sort_by']
            );

            if (isset($data['sort']) && array_key_exists($data['sort'], $sort_data)) {
                $sql .= " ORDER BY " . $sort_data[$data['sort']];
            } else {
                //for faster SQL set default to ID-based order
                $sql .= " ORDER BY p.product_id";
            }

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

            if (isset($data['start']) || isset($data['limit'])) {
                $data['start'] = max(0, (int)$data['start']);
                $data['limit'] = $data['limit'] < 1 ? 20 : (int)$data['limit'];
                $sql .= " LIMIT " . $data['start'] . "," . $data['limit'];
            }
            $query = $this->db->query($sql);
            $totalRows = $this->db->getTotalNumRows();
            $output = [];
            foreach ($query->rows as $row) {
                $row['total_num_rows'] = $totalRows;
                $output[] = $row;
            }
            return $output;
        } else {
            $cache_key = 'product.lang_' . $language_id;
            $product_data = $this->cache->pull($cache_key);
            if ($product_data === false) {
                $query = $this->db->query(
                    "SELECT *, p.product_id
                    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 . "')
                    ORDER BY pd.name"
                );
                $product_data = $query->rows;
                $this->cache->push($cache_key, $product_data);
            }

            return $product_data;
        }
    }

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

    /**
     * @param int $stock_status_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalProductsByStockStatusId($stock_status_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("products") . "
            WHERE stock_status_id = '" . (int)$stock_status_id . "'"
        );
        return $query->row['total'];
    }

    /**
     * @param int $tax_class_id
     *
     * @return int mixed
     * @throws AException
     */
    public function getTotalProductsByTaxClassId($tax_class_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("products") . "
            WHERE tax_class_id = '" . (int)$tax_class_id . "'"
        );
        return $query->row['total'];
    }

    /**
     * @param int $weight_class_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalProductsByWeightClassId($weight_class_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("products") . "
            WHERE weight_class_id = '" . (int)$weight_class_id . "'"
        );
        return $query->row['total'];
    }

    /**
     * @param int $length_class_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalProductsByLengthClassId($length_class_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("products") . "
            WHERE length_class_id = '" . (int)$length_class_id . "'"
        );
        return $query->row['total'];
    }

    /**
     * @param int $option_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalProductsByOptionId($option_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("product_to_option") . "
            WHERE option_id = '" . (int)$option_id . "'"
        );
        return $query->row['total'];
    }

    /**
     * @param int $download_id
     *
     * @return int
     * @throws AException
     */
    public function getTotalProductsByDownloadId($download_id)
    {
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
            FROM " . $this->db->table("products_to_downloads") . "
            WHERE download_id = '" . (int)$download_id . "'"
        );
        return $query->row['total'];
    }

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

    /**
     * function checks if a product is displayed on the storefront and returns an array with messages about causes
     *
     * @param $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductCondition($product_id)
    {
        $product_id = (int)$product_id;
        if (!$product_id) {
            return [];
        }

        $language_id = (int)$this->language->getContentLanguageID();

        $sql = "SELECT p.*,
                       p.quantity as base_quantity,
                       p.subtract as base_subtract,
                       pov.*,
                       pod.name as option_name,
                       povd.name as option_value_name
                FROM " . $this->db->table('products') . " p
                LEFT JOIN " . $this->db->table('product_option_values') . " pov
                    ON pov.product_id = p.product_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 = " . $language_id
            . ")
                LEFT JOIN " . $this->db->table('product_option_descriptions') . " pod
                    ON (pod.product_option_id = pov.product_option_id AND pod.language_id = " . $language_id . ")
                WHERE p.product_id = " . $product_id;

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

        // id product disabled does not run other checks
        if (!$result->row['status']) {
            return [$this->language->get('text_product_disabled')];
        }

        $output = [];
        // check is product available
        if (dateISO2Int($result->row['date_available']) > time()) {
            $output[] = $this->language->get('text_product_unavailable');
        }
        $hasTrackOptions = $this->hasTrackOptions($product_id);
        $out_of_stock = false;
        //check is stock track for whole product(not options) enabled and product quantity more than 0
        if ($result->row['base_subtract']
            && $result->row['base_quantity'] <= 0
            && !$hasTrackOptions
        ) {
            $output[] = $this->language->get('text_product_out_of_stock');
            $out_of_stock = true;
        }

        $error_txt = [];
        if ($hasTrackOptions) {
            foreach ($result->rows as $row) {
                if ($row['subtract'] && $row['quantity'] <= 0) {
                    $error_txt[] = $row['option_name'] . ' => ' . $row['option_value_name'];
                    $out_of_stock = true;
                }
            }
        }

        if ($out_of_stock && $error_txt) {
            $output[] = $this->language->get('text_product_option_out_of_stock');
            $output = array_merge($output, $error_txt);
        }

        return $output;
    }

    public function getOrderProductOptions($product_id)
    {
        if (!(int)$product_id) {
            return [];
        }
        $language_id = $this->language->getContentLanguageID();
        $elements = HtmlElementFactory::getAvailableElements();

        $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 (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 = '" . (int)$language_id . "'"
                        );

                        $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'],
                            'sku'                     => $product_option_value['sku'],
                            'txt_id'                  => $product_option_value['txt_id'],
                            '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 = '" . (int)$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'],
                ];
            }
        }

        return $product_option_data;
    }

    protected function _touch_product($product_id)
    {
        $product_id = (int)$product_id;
        if (!$product_id) {
            return false;
        }
        $this->db->query(
            "UPDATE " . $this->db->table('products') . " 
            SET date_modified = NOW() 
            WHERE product_id ='" . $product_id . "'"
        );
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
        return true;
    }

    /**
     * @param int $product_id
     *
     * @return int
     * @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 $result->num_rows;
    }

    /**
     *
     * Check if a product or any option has any stock available
     *
     * @param int $product_id
     *
     * @return int|true - integer as quantity, true as availability when track-stock is off
     * @throws AException
     */
    public function hasAnyStock($product_id)
    {
        if (!(int)$product_id) {
            return 0;
        }
        $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) {
            $notrack_qnt = 0;
            foreach ($query->rows as $row) {
                //if tracking of stock disabled - set quantity as big
                if (!$row['subtract']) {
                    $notrack_qnt += 10000000;
                    continue;
                }
                $total_quantity += max($row['quantity'], 0);
            }
            //if any of the option value have "subtract" NO - think product is available
            if ($total_quantity == 0 && $notrack_qnt) {
                $total_quantity = true;
            }
        } else {
            //get product quantity without options
            $query = $this->db->query(
                "SELECT quantity
                FROM " . $this->db->table("products") . " p
                WHERE p.product_id = '" . (int)$product_id . "'"
            );
            $total_quantity = (int)$query->row['quantity'];
        }
        return $total_quantity;
    }

    /**
     * @param int $product_id
     *
     * @param int $product_option_value_id
     *
     * @return array
     * @throws AException
     */
    public function getProductStockLocations($product_id, $product_option_value_id = 0)
    {
        $sql = "SELECT *
                FROM " . $this->db->table('product_stock_locations') . " psl
                LEFT JOIN " . $this->db->table('locations') . " l
                    ON l.location_id = psl.location_id
                WHERE psl.product_id=" . (int)$product_id;
        if ($product_option_value_id) {
            $sql .= " AND psl.product_option_value_id = " . (int)$product_option_value_id;
        } else {
            $sql .= " AND psl.product_option_value_id IS NULL";
        }

        $sql .= " ORDER BY psl.sort_order";

        $result = $this->db->query($sql);
        return $result->rows;
    }

    public function updateProductStockLocations($locations, $product_id, $product_option_value_id)
    {
        //remove first
        $this->db->query(
            "DELETE
            FROM " . $this->db->table("product_stock_locations") . " 
            WHERE product_id = " . (int)$product_id . "
                    AND product_option_value_id " . ((int)$product_option_value_id
                ? " = " . (int)$product_option_value_id
                : "IS NULL")
        );

        //if no locations set - stop
        if (!$locations) {
            return false;
        }

        $totals = [];
        $locations_exists = false;
        foreach ($locations as $location_id => $location_details) {
            if (!(int)$location_id) {
                continue;
            }

            $this->db->query(
                "INSERT INTO " . $this->db->table("product_stock_locations") . "
                    (product_id, product_option_value_id, location_id, quantity, sort_order, supplier_code, supplier_id)
                VALUES( 
                    " . (int)$product_id . ", 
                    " . $this->db->intOrNull($product_option_value_id) . ", 
                    " . (int)$location_id . ", 
                    " . (int)$location_details['quantity'] . ", 
                    " . (int)$location_details['sort_order'] . ",
                    " . $this->db->stringOrNull($location_details['supplier_code']) . ",
                    " . $this->db->stringOrNull($location_details['supplier_id']) . "
                );"
            );

            $locations_exists = true;
            $totals[] = (int)$location_details['quantity'];
        }

        //update_total_quantity
        if ($locations_exists) {
            if (!$product_option_value_id) {
                $this->db->query(
                    "UPDATE `" . $this->db->table("products`") . " 
                    SET quantity= '" . (int)array_sum($totals) . "'
                    WHERE product_id = " . (int)$product_id
                );
            } elseif (array_sum($totals)) {
                $this->db->query(
                    "UPDATE `" . $this->db->table("product_option_values`") . " 
                    SET quantity= '" . (int)array_sum($totals) . "'
                    WHERE product_option_value_id=" . (int)$product_option_value_id
                );
            }
        }
        $this->cache->remove(['product', 'category', 'collection', 'storefront_menu']);
        return true;
    }

    /**
     * @param $order_product_id
     *
     * @return array
     * @throws AException
     */
    public function getOrderProductStockLocations($order_product_id)
    {
        $sql = "SELECT ops.*, COALESCE(ps.quantity,'absent') as available_quantity 
                FROM " . $this->db->table('order_product_stock_locations') . " ops
                LEFT JOIN " . $this->db->table('product_stock_locations') . " ps
                    ON (ps.product_id = ops.product_id 
                        AND COALESCE(ps.product_option_value_id,0) = COALESCE(ops.product_option_value_id,0)
                        AND ps.location_id = ops.location_id ) 
                WHERE ops.order_product_id=" . (int)$order_product_id . "
                ORDER BY ops.sort_order, ps.sort_order";
        $result = $this->db->query($sql);

        $product_id = 0;
        $product_option_value_id = 0;
        $order_stocks = $sort_order = [];
        foreach ($result->rows as $row) {
            $locId = $row['location_id'];
            $order_stocks[$locId] = $row;
            $product_id = $row['product_id'];
            $product_option_value_id = (int)$row['product_option_value_id'];
            $sort_order[$locId] = $row['sort_order'];
        }
        if ($order_stocks) {
            $sql = "SELECT psl.*, CONCAT(l.name, ' ', l.description) as location_name
                    FROM " . $this->db->table('product_stock_locations') . " psl
                    LEFT JOIN " . $this->db->table('locations') . " l
                        ON l.location_id = psl.location_id
                    WHERE psl.product_id = " . $product_id . " 
                            AND psl.product_option_value_id "
                . (!$product_option_value_id
                    ? ' IS NULL '
                    : ' = ' . (int)$product_option_value_id) . "
                            AND psl.location_id NOT IN (" . implode(',', array_keys($order_stocks)) . ")
                    ORDER BY psl.sort_order ASC";
            $result = $this->db->query($sql);
            foreach ($result->rows as $row) {
                $row['available_quantity'] = $row['quantity'];
                $row['quantity'] = 0;
                $order_stocks[$row['location_id']] = $row;
                $sort_order[$row['location_id']] = $row['sort_order'];
            }
        }
        array_multisort($sort_order, SORT_ASC, $order_stocks);
        return $order_stocks;
    }

    /**
     * @param int $optionId
     * @param array $data
     * @return array
     * @throws AException
     */
    public function validateOptionValues(int $optionId, array $data)
    {
        $optionId = (int)$optionId;
        $this->errors = [];
        $this->load->language('catalog/attribute');

        $txtIds = array_filter(array_map('trim', (array)$data['txt_id']));
        if (count($txtIds) != count(array_unique($txtIds))) {
            $this->errors['txt_id'] = $this->language->get('error_not_unique');
        }

        if (!$this->errors && $txtIds) {
            $sql = "SELECT pov.*, pod.name 
                    FROM `" . $this->db->table("product_option_values") . "` pov
                    LEFT JOIN  `" . $this->db->table("product_option_descriptions") . "` pod
                        ON (pod.product_option_id = pov.product_option_id 
                            AND pod.language_id = '" . (int)$this->language->getContentLanguageID() . "')
                    WHERE `txt_id` IN ('" . implode("','", $txtIds) . "')";
            if ($optionId) {
                $sql .= " AND pov.product_option_id <> " . (int)$optionId;
            }
            $gaValueIds = filterIntegerIdList((array)$data['attribute_value_id']);
            if ($gaValueIds) {
                $sql .= " AND pov.attribute_value_id NOT IN (" . implode(',', $gaValueIds) . ") ";
            }

            $exists = $this->db->query($sql);
            if ($exists->num_rows) {
                $this->errors['txt_id'] = $this->language->get('error_not_unique') . " (";
                $dd = [];
                foreach ($exists->rows as $row) {
                    $dd[] = '<a target="_blank" href="' . $this->html->getSecureUrl(
                            'catalog/product_options',
                            '&product_id=' . $row['product_id'])
                        . '">' . $row['name'] . '</a>';
                }
                $this->errors['txt_id'] .= implode(', ', $dd) . " )";
            }
        }

        $this->extensions->hk_ValidateData($this, [__FUNCTION__]);
        return $this->errors;
    }
}