<?php
/*
 * $Id$
 *
 * AbanteCart, Ideal OpenSource Ecommerce Solution
 * http://www.AbanteCart.com
 *
 * Copyright © 2011-2024 Belavier Commerce LLC
 *
 * This source file is subject to Open Software License (OSL 3.0)
 * License details is 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
 * @property ALanguageManager $language
 */
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 = (int) $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']."',
                    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' => (int) $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 = '".(int) $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=".(int) $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=".(int) $product_id."'
                    AND language_id = '".(int) $language_id."'"
            );
        }

        if ($data['product_tags']) {
            if (is_string($data['product_tags'])) {
                $tags = $this->getUniqueTags($data['product_tags']);
                $tags = [$language_id => $tags];
            } elseif (is_array($data['product_tags'])) {
                $tags = $data['product_tags'];
                foreach ($tags as &$tagList) {
                    $tagList = $this->getUniqueTags($tagList);
                }
                unset($tagList);
            } else {
                $tags = (array) $data['product_tags'];
            }

            foreach ($tags as $lang_id => $tagList) {
                $tagList = array_unique($tagList);

                foreach ($tagList as $tag) {
                    $tag = trim($tag);
                    $tag = preg_replace(self::TAG_REGEX_PATTERN,'',$tag);
                    if (!$tag) {
                        continue;
                    }
                    $sql = "INSERT INTO ".$this->db->table('product_tags')."
                                (product_id, language_id, tag)
                            VALUES
                                (".(int) $product_id.", ".(int) $lang_id.", '".$this->db->escape($tag)."');";
                    $this->db->query($sql);
                }
            }
        }
        $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']);
        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_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 = (int) $this->language->getContentLanguageID();

        $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",
        ];
        $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->escape($data[$f])."'";
            }
        }
        if (!empty($update)) {
            $this->db->query(
                "UPDATE `".$this->db->table("products`")
                ." SET ".implode(',', $update)
                ." WHERE product_id = '".(int) $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' => (int) $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=".(int) $product_id
                    ],
                    [
                        $language_id => [
                            'keyword' => $data['keyword']
                        ]
                    ]
                );
            } else {
                $this->db->query(
                    "DELETE
                    FROM ".$this->db->table("url_aliases")." 
                    WHERE query = 'product_id=".(int) $product_id."'
                        AND language_id = '".$language_id."'"
                );
            }
        }

        if (isset($data['product_tags'])) {
            $tags = $this->getUniqueTags($data['product_tags']);

            foreach ($tags as &$tag) {
                $tag = preg_replace(self::TAG_REGEX_PATTERN,'',$tag);
                $tag = $this->db->escape(trim($tag));
            }

            $this->language->replaceMultipleDescriptions(
                'product_tags',
                ['product_id' => (int) $product_id],
                [$language_id => ['tag' => array_unique($tags)]]
            );
        }

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

        if($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 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)
    {
        $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'));
        }
        if (!empty($data['date_start'])) {
            $data['date_start'] = dateDisplay2ISO($data['date_start'], $this->language->get('date_format_short'));
        }
        if (!empty($data['date_end'])) {
            $data['date_end'] = dateDisplay2ISO($data['date_end'], $this->language->get('date_format_short'));
        }
        $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 = '".(int) $product_discount_id."'"
            );
        }
        $this->cache->remove(['product','category','collection']);
    }

    /**
     * @param int $product_special_id
     * @param array $data
     *
     * @throws AException
     */
    public function updateProductSpecial($product_special_id, $data)
    {
        $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'));
        }
        if (!empty($data['date_start'])) {
            $data['date_start'] = dateDisplay2ISO($data['date_start'], $this->language->get('date_format_short'));
        }
        if (!empty($data['date_end'])) {
            $data['date_end'] = dateDisplay2ISO($data['date_end'], $this->language->get('date_format_short'));
        }

        $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 = '".(int) $product_special_id."'"
            );
        }
        $this->cache->remove(['product','category','collection']);
    }

    /**
     * @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'])) {
            $this->db->query(
                "DELETE FROM ".$this->db->table("products_to_stores")." 
                WHERE product_id = '".(int) $product_id."'"
            );
            foreach ($data['product_store'] as $store_id) {
                $this->db->query(
                    "INSERT INTO ".$this->db->table("products_to_stores")." 
                    SET product_id = '".(int) $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 = '".(int) $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 = '".(int) $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 = '".(int) $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 = '".(int) $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 = '".(int) $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 = '".(int) $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 = '".(int) $product_id."'"
                    );
                    $this->db->query(
                        "INSERT INTO ".$this->db->table("products_related")." 
                        SET product_id = '".(int) $related_id."', related_id = '".(int) $product_id."'"
                    );
                }
            }
        }
        $this->_touch_product($product_id);
        return true;
    }

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

    /**
     * @param int $product_id
     * @param array $data
     *
     * @return int
     * @throws AException
     */
    public function addProductOption($product_id, $data)
    {
        $am = new AAttribute_Manager();
        $attribute = $am->getAttribute($data['attribute_id']);

        if ($attribute) {
            $data['element_type'] = $attribute['element_type'];
            $data['required'] = $attribute['required'];
            $data['regexp_pattern'] = $attribute['regexp_pattern'];
            $data['placeholder'] = $attribute['placeholder'];
            $data['sort_order'] = $attribute['sort_order'];
            $data['settings'] = $attribute['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 (!empty($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($attribute){
            //add all values into 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 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']."'"
        );
        $this->cache->remove(['product', 'collection', 'category']);
        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 (empty($pd_opt_val_id) || empty($data)) {
            return null;
        }
        //If se have grouped (parent/child) options save no main attribute id
        if (is_array($attribute_value_id)) {
            $attribute_value_id = '';
        }

        $this->db->query(
            "UPDATE ".$this->db->table("product_option_values")."
            SET 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 = '".$this->db->escape($data['price'])."',
                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']."'
            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 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 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) {
            //sign to prevent converting date from display format to iso
            $item['iso_date'] = true;
            $this->addProductDiscount($new_product_id, $item);
        }
        foreach ($data['product_special'] as $item) {
            $item['iso_date'] = true;
            $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'] );

        //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', 'collection', 'category']);
    }

    /**
     * @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 instance for source layout
            $lm = new ALayoutManager($tmpl_id, $src_page_id, $src_layout_id);
            //create 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 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 become 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."'"
        );

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

        $this->cache->remove(['product', 'collection', 'category']);
        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', 'collection', 'category']);
    }

    /**
     * @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', 'collection', 'category']);
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProduct($product_id)
    {
        $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='".(int) $this->language->getContentLanguageID()."' ) 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 = '" .(int) $this->config->get('storefront_language_id')."')
            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', 'collection', 'category']);
    }

    /**
     * @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', 'collection', 'category']);
    }

    /**
     * @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 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', 'collection', 'category']);
    }

    /**
     * Main method to get complete options data for 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],
                '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' && strpos($opt_val_id, 'new') === false) {
                //delete this option value for all languages
                $this->deleteProductOptionValue($product_id, $opt_val_id);
            } else {
                if ($status == 'new') {
                    // Need to create 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 = [
            'product_option_value_id' => $option_value['product_option_value_id'],
            'language'                => $value_description_data,
            'sku'                     => $option_value['sku'],
            'txt_id'                  => $option_value['txt_id'],
            'quantity'                => $option_value['quantity'],
            'subtract'                => $option_value['subtract'],
            'price'                   => $option_value['price'],
            'cost'                    => $option_value['cost'],
            'prefix'                  => $option_value['prefix'],
            'weight'                  => $option_value['weight'],
            'weight_type'             => $option_value['weight_type'],
            'attribute_value_id'      => $option_value['attribute_value_id'],
            'grouped_attribute_data'  => $option_value['grouped_attribute_data'],
            'sort_order'              => $option_value['sort_order'],
            'default'                 => $option_value['default'],
        ];

        //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)
    {
        $product_download_data = [];

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

        foreach ($query->rows as $result) {
            $product_download_data[] = $result['download_id'];
        }

        return $product_download_data;
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductStores($product_id)
    {
        $product_store_data = [];
        $rows = $this->getProductStoresInfo($product_id);

        foreach ($rows as $result) {
            $product_store_data[] = $result['store_id'];
        }

        return $product_store_data;
    }

    /**
     * @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 array_column($query->rows, 'category_id');
    }

    /**
     * @param int $product_id
     *
     * @return array
     * @throws AException
     */
    public function getProductRelated($product_id)
    {
        $product_related_data = [];

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

        foreach ($query->rows as $result) {
            $product_related_data[] = $result['related_id'];
        }
        return $product_related_data;
    }

    /**
     * @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;
        $product_seo_keys = [];

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

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

        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')
    {
        if (!empty($data['content_language_id'])) {
            $language_id = (int) $data['content_language_id'];
        } else {
            $language_id = (int) $this->config->get('storefront_language_id');
        }

        if ($data['store_id']) {
            $store_id = (int) $data['store_id'];
        } else {
            $store_id = (int) $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 *, p.product_id ";
                $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 (isset($filter['category']) && $filter['category'] > 0) {
                $sql .= " LEFT JOIN ".$this->db->table("products_to_categories")
                    ." p2c ON (p.product_id = p2c.product_id) ";
            }

            $sql .= ' WHERE 1=1 ';

            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 = [
                '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',
            ];

            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'])) {
                if ($data['start'] < 0) {
                    $data['start'] = 0;
                }

                if ($data['limit'] < 1) {
                    $data['limit'] = 20;
                }
                $sql .= " LIMIT ".(int) $data['start'].",".(int) $data['limit'];
            }
            $query = $this->db->query($sql);
            return $query->rows;
        } 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 product will be displayed on storefront and returns 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 do 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 bool
     * @throws AException
     */
    public function hasTrackOptions($product_id)
    {
        $sql = "SELECT *
                FROM ".$this->db->table('product_option_values')." pov
                INNER JOIN ".$this->db->table('product_options')." po
                    ON (pov.product_option_id = po.product_option_id AND po.status = 1) 
                WHERE pov.product_id=".(int) $product_id." AND pov.subtract = 1";
        $result = $this->db->query($sql);
        return ($result->num_rows);
    }

    /**
     *
     * Check if 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 options 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)
                VALUES( 
                    ".(int) $product_id.", 
                    ".((int) $product_option_value_id ? : 'NULL').", 
                    ".(int) $location_id.", 
                    ".(int) $location_details['quantity'].", 
                    ".(int) $location_details['sort_order']."
                );"
            );

            $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', 'collection', 'category']);
        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 $string
     *
     * @return array
     */
    public function getUniqueTags($string)
    {
        $tags = array_map('trim', explode(',', $string));
        return array_intersect_key($tags, array_unique(array_map('strtolower', $tags)));
    }

    /**
     * @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', $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;
            }
            $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;
    }
}