<?php

namespace KupShop\CatalogBundle\ProductList;

use KupShop\BonusProgramBundle\Utils\BonusComputer;
use KupShop\CatalogBundle\PriceLevel;
use KupShop\CatalogBundle\Repository\SectionsRepository;
use KupShop\CatalogBundle\Util\FavoriteProductsUtil;
use KupShop\CatalogBundle\Util\Product\ProductGetOnTheWay;
use KupShop\CatalogBundle\Util\RelatedProductsUtil;
use KupShop\CatalogBundle\Util\ReviewsUtil;
use KupShop\CatalogBundle\Util\Sets\SetsPriceCalculator;
use KupShop\CatalogBundle\Util\VariationsEntityUtil;
use KupShop\ConvertorsBundle\Utils\ConvertorsUtil;
use KupShop\I18nBundle\Translations\BlocksTranslation;
use KupShop\I18nBundle\Translations\ParametersListTranslation;
use KupShop\I18nBundle\Translations\ParametersTranslation;
use KupShop\I18nBundle\Translations\PhotosTranslation;
use KupShop\I18nBundle\Translations\ProducersTranslation;
use KupShop\I18nBundle\Translations\TemplatesCategoriesTranslation;
use KupShop\I18nBundle\Translations\TemplatesTranslation;
use KupShop\I18nBundle\Translations\VariationsLabelsTranslation;
use KupShop\I18nBundle\Translations\VariationsTranslation;
use KupShop\I18nBundle\Translations\VariationsValuesTranslation;
use KupShop\KupShopBundle\Context\ContextManager;
use KupShop\KupShopBundle\Context\CurrencyContext;
use KupShop\KupShopBundle\Context\PriceLevelContext;
use KupShop\KupShopBundle\Context\UserContext;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\KupShopBundle\Util\Price\Price;
use KupShop\KupShopBundle\Util\Price\PriceCalculator;
use KupShop\KupShopBundle\Util\Price\PriceLevelPrice;
use KupShop\KupShopBundle\Util\Price\PriceUtil;
use KupShop\KupShopBundle\Util\Price\ProductPrice;
use KupShop\KupShopBundle\Util\Suppliers\SuppliersUtil;
use KupShop\KupShopBundle\Wrapper\PriceWrapper;
use KupShop\LabelsBundle\Util\LabelFilterSpecs;
use KupShop\PricelistBundle\Context\PricelistContext;
use KupShop\PricelistBundle\Util\Price\PriceListPrice;
use KupShop\ProductsBatchesBundle\Util\ProductsBatchesUtil;
use KupShop\ProductsChargesBundle\Translations\ProductsChargesTranslation;
use KupShop\QuantityDiscountBundle\Context\QuantityDiscountContext;
use KupShop\QuantityDiscountBundle\Query\QuantityDiscount;
use KupShop\QuantityDiscountBundle\Util\QuantityDiscountPrice;
use KupShop\SellerBundle\Context\SellerContext;
use KupShop\SellerBundle\Utils\SellerMultiFetch;
use KupShop\UnitsBundle\Utils\MeasureUnitUtil;
use Query\Operator;
use Query\Product;
use Query\QueryBuilder;
use Query\Translation;
use Query\Variation;
use Symfony\Contracts\Service\Attribute\Required;

class MultiFetch
{
    protected ?SectionsRepository $sectionsRepository = null;
    protected ?ReviewsUtil $reviewsUtil = null;
    protected VariationsEntityUtil $variationsUtil;
    protected RelatedProductsUtil $relatedProductsUtil;

    #[Required]
    public SetsPriceCalculator $setsPriceCalculator;

    private ?ProductsBatchesUtil $productsBatchesUtil = null;

    protected function mergeToProductsCollection(ProductCollection $products, $attribute, $data, $default = null)
    {
        // Initialize attribute always to mark as already loaded even if no data present
        if ($default !== null) {
            foreach ($products as $product) {
                if (is_null($product->{$attribute})) {
                    $product->{$attribute} = $default;
                }
            }
        }

        if ($products->getEntityType() == \FilterParams::ENTITY_PRODUCT) {
            foreach ($data as $id_product => $value) {
                if ($product = $products[$id_product]) {
                    $product->{$attribute} = $value;
                }
            }

            return;
        }

        foreach ($products as $product) {
            if (isset($data[$product->id])) {
                $product->{$attribute} = $data[$product->id];
            }
        }
    }

    protected function mergeToVariationsCollection(ProductCollection $products, $attribute, $data, $default = null)
    {
        if ($default !== null) {
            foreach ($products as $product) {
                $product->{$attribute} = $default;
            }
        }

        foreach ($data as $index => $value) {
            if (isset($products[$index])) {
                $products[$index]->{$attribute} = $value;
            }
        }
    }

    public function fetchBonusPoints(ProductCollection $products): void
    {
        if (!findModule(\Modules::BONUS_PROGRAM)) {
            return;
        }

        $bonusComputer = ServiceContainer::getService(BonusComputer::class);
        $bonusComputer->multiFetchProductsBonusPoints($products);
    }

    public function fetchRating(ProductCollection $products): void
    {
        if ($products->isEmpty()) {
            return;
        }

        $qb = $this->reviewsUtil->getQueryBuilder()
            ->select('r.id_product as id, AVG(r.rating) as rating, COUNT(distinct r.id) as rating_count')
            ->groupBy('r.id_product');

        if (findModule(\Modules::PRODUCTS_COLLECTIONS) && ('Y' == (\Settings::getDefault()['review_collections'] ?? 'Y'))) {
            $qb->addSelect('pc.id AS id')
                ->joinSubQuery('r', \Query\Product::collectionsProductsSubQuery($products->getProductIds()), 'pc',
                    'r.id_product = pc.id OR r.id_product = pc.main_product OR r.id_product = pc.related_product')
                ->groupBy('pc.id');
        } else {
            $qb->andWhere(Operator::inIntArray($products->getProductIds(), 'r.id_product'));
        }

        $data = $qb->execute()->fetchAllAssociativeIndexed();

        $this->mergeToProductsCollection($products, 'rating', $data, []);
        // backward compatibility kvuli starsim sablonam, ktere spolehaji na to, ze to je v reviews
        $this->mergeToProductsCollection($products, 'reviews', $data, []);
    }

    public function fetchTemplates(ProductCollection $products, $fetchBlocks = true): void
    {
        if (!findModule(\Modules::TEMPLATES)) {
            return;
        }

        $productIds = array_map(function (\Product $product) {
            return $product->id;
        }, array_filter($products->getValues(), function (\Product $product) {
            return is_null($product->templates);
        }));

        if (!$productIds) {
            return;
        }

        $qb = sqlQueryBuilder()
            ->select('t.id_category, t.id, t.name, tc.name as category, t.id_block, t.data, t.figure, tp.id_product, tc.product_detail_position')
            ->from('templates_products', 'tp')
            ->join('tp', 'templates', 't', 't.id = tp.id_template')
            ->join('tp', 'templates_categories', 'tc', 'tc.id = t.id_category')
            ->andWhere(Operator::inIntArray($productIds, 'tp.id_product'))
            ->andWhere(Translation::coalesceTranslatedFields(TemplatesTranslation::class))
            ->andWhere(Translation::coalesceTranslatedFields(TemplatesCategoriesTranslation::class, ['name' => 'category']))
            ->andHaving('figure!="N"')
            ->orderBy('tc.position, t.position, t.name');

        $templates = $qb->execute()->fetchAllAssociative();

        if ($fetchBlocks) {
            // ulozim si ID root blocku
            $rootBlockIds = array_map(fn ($x) => $x['id_block'], $templates);

            // nafetchuju blocky, ktere patri k sablonam
            $qb = sqlQueryBuilder()
                ->select('b.*')
                ->from('blocks', 'b')
                ->andWhere(Operator::inIntArray($rootBlockIds, 'b.id_root'))
                ->andWhere(Translation::coalesceTranslatedFields(BlocksTranslation::class))
                ->orderBy('b.id_root, b.position, b.id_parent, b.id');

            $blocks = [];
            foreach ($qb->execute() as $row) {
                $blocks[$row['id_root']] = $blocks[$row['id_root']] ?? [];
                $blocks[$row['id_root']][] = $row;
            }
        }

        $result = [];
        foreach ($templates as $item) {
            if (!isset($result[$item['id_product']][$item['id_category']])) {
                $result[$item['id_product']][$item['id_category']] = [
                    'id' => $item['id_category'],
                    'title' => $item['category'],
                    'position' => $item['product_detail_position'],
                    'values' => [],
                ];
            }

            // priradim blocky
            $item['blocks'] = $blocks[$item['id_block']] ?? [];

            $result[$item['id_product']][$item['id_category']]['values'][$item['id']] = $item;
        }

        $this->mergeToProductsCollection($products, 'templates', $result, []);
    }

    public function fetchQuantityDiscounts(ProductCollection $products, bool $byVariations = false): void
    {
        if (!findModule(\Modules::QUANTITY_DISCOUNT)) {
            return;
        }

        $quantityDiscountContext = Contexts::get(QuantityDiscountContext::class);

        if (!($active = $quantityDiscountContext->getActive())) {
            return;
        }

        $quantityDiscountPrice = ServiceContainer::getService(QuantityDiscountPrice::class);

        $qb = sqlQueryBuilder()
            ->select('id_product, id_variation, pieces, discount, discount_type')
            ->from('products_quantity_discounts')
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'id_product'))
            ->andWhere(QuantityDiscount::byGroup($active))
            ->orderBy('pieces', 'ASC');

        $data = [];
        foreach ($qb->execute() as $item) {
            $data[$item['id_product']][] = $item;
        }

        $quantityDiscounts = [];
        foreach ($products as $key => $product) {
            $items = array_filter(
                $data[$product->id] ?? [],
                function (array $x) use ($products, $product) {
                    if ($products->getEntityType() === \FilterParams::ENTITY_VARIATION) {
                        return $x['id_variation'] === null || $x['id_variation'] === ($product->variationId ?? null);
                    }

                    return true;
                }
            );

            foreach ($items as $item) {
                $productPrice = $product->getProductPrice();
                if ($variation = ($product->variations[$item['id_variation']] ?? null)) {
                    $productPrice = PriceWrapper::unwrap($variation['productPrice']);
                }

                $price = $quantityDiscountPrice->calculateQuantityDiscountPriceForProductPrice($productPrice, $item);

                $quantityDiscounts[$key][] = [
                    'id_variation' => $item['id_variation'],
                    'pieces' => (int) $item['pieces'],
                    'discount' => toDecimal($item['discount']),
                    'discount_type' => $item['discount_type'],
                    'productPrice' => new ProductPrice(
                        $price->getValue(),
                        $price->getCurrency(),
                        $price->getVat()
                    ),
                ];
            }
        }

        if ($byVariations) {
            $quantityDiscountsByVariations = [];
            foreach ($quantityDiscounts as $key => $discounts) {
                foreach ($discounts as $discount) {
                    if (!($discount['id_variation'] ?? false)) {
                        continue;
                    }

                    $quantityDiscountsByVariations[$key][$discount['id_variation']][] = $discount;
                }
            }

            foreach ($products as $key => $product) {
                $product->quantityDiscountsByVariations = $quantityDiscountsByVariations[$key] ?? [];
            }
        }

        foreach ($products as $key => $product) {
            $product->quantityDiscounts = $quantityDiscounts[$key] ?? [];
        }
    }

    public function fetchMultiSets(ProductCollection $products): void
    {
        if (!findModule(\Modules::PRODUCTS, \Modules::SUB_SETS)) {
            return;
        }

        $productList = new \ProductList();
        $productList->andSpec(function (QueryBuilder $qb) use ($products) {
            $qb->addSelect('sp.id_product_master, sp.id_product as id_product_slave, sc.id as set_id_section, sc.name as set_name_section, sc.descr as set_descr_section')
                ->join('p', 'set_products', 'sp', 'sp.id_product = p.id')
                ->join('sp', 'set_categories', 'sc', 'sc.id = sp.category')
                ->andWhere(Operator::inIntArray($products->getProductIds(), 'sp.id_product_master'));
        });

        $productList->addResultModifiers(function (ProductCollection $products, $dataAll) {
            foreach ($dataAll as $productId => $data) {
                $products[$productId]->id_product_master = $data['id_product_master'];
                $products[$productId]->id_product_slave = $data['id_product_slave'];
                $products[$productId]->set_id_section = $data['set_id_section'];
                $products[$productId]->set_name_section = $data['set_name_section'];
                $products[$productId]->set_descr_section = $data['set_descr_section'];
            }
        });

        $result = [];
        foreach ($productList->getProducts() as $product) {
            if (!isset($result[$product->id_product_master][$product->set_id_section])) {
                $result[$product->id_product_master][$product->set_id_section] = [
                    'name' => $product->set_name_section,
                    'descr' => $product->set_descr_section,
                    'products' => new ProductCollection(),
                ];
            }

            $result[$product->id_product_master][$product->set_id_section]['products'][$product->id] = $product;
        }

        $this->mergeToProductsCollection($products, 'multiSets', $result);
    }

    public function fetchProductLabels(ProductCollection $products, ?string $visibility = null): void
    {
        if (!findModule(\Modules::LABELS, \Modules::SUB_PRODUCT_LABELS)) {
            return;
        }

        $productIds = array_map(function (\Product $product) {
            return $product->id;
        }, array_filter($products->getValues(), function (\Product $product) {
            return is_null($product->labels);
        }));

        if (!$productIds) {
            return;
        }

        $productsFilterSpecs = ServiceContainer::getService(LabelFilterSpecs::class);

        $filter = [];
        if (findModule(\Modules::TRANSLATIONS)) {
            $filter['translation'] = true;
        }

        if ($visibility != 'all') {
            $filter['active'] = true;
        }

        $qb = sqlQueryBuilder()
            ->select('l.*, plr.id_product')
            ->from('product_labels_relation', 'plr')
            ->join('plr', 'labels', 'l', 'plr.id_label = l.id')
            ->andWhere(Operator::inIntArray($productIds, 'plr.id_product'))
            ->addOrderBy('l.position');

        if (!empty($filter)) {
            $filterSpecs = $productsFilterSpecs->getSpecs($filter);
            $qb->andWhere($filterSpecs);
        }

        $data = [];
        $userContext = Contexts::get(UserContext::class);
        $userTypes = array_keys($userContext->getTypes());
        $userGroups = array_keys($userContext->getActive()?->getGroups() ?? []);
        foreach ($qb->execute() as $label) {
            $label['data'] = json_decode($label['data'], true);
            // urcim, zda je stitek viditelny pro danou $visibility
            $label['is_visible'] = ($visibility === 'list' && ($label['data']['list_visibility'] ?? 'N') === 'Y')
                || ($visibility === 'detail' && ($label['data']['detail_visibility'] ?? 'N') === 'Y')
                || $visibility === 'all' || $visibility === null;

            if ($visibility !== 'all') {
                if (isset($label['data']['users_types'])) {
                    $typesY = $typesN = [];
                    foreach ($label['data']['users_types'] as $type) {
                        if (preg_match('/(.+):([Y|N])$/', $type, $split)) {
                            match ($split[2]) {
                                'Y' => $typesY[] = $split[1],
                                'N' => $typesN[] = $split[1],
                            };
                        }
                    }
                    if (($typesY && !array_intersect($userTypes, $typesY))
                        || ($typesN && array_intersect($userTypes, $typesN))) {
                        continue;
                    }
                }
                if (isset($label['data']['users_groups'])) {
                    $hasUserLabelGroup = !empty(array_intersect($label['data']['users_groups'], $userGroups));
                    $invertSelect = $label['data']['users_groups_invert'] ?? false;
                    if (($invertSelect && $hasUserLabelGroup) || (!$invertSelect && !$hasUserLabelGroup)) {
                        continue;
                    }
                }
            }

            $data[$label['id_product']][$label['code'] ?? $label['id']] = $label;
        }

        $this->mergeToProductsCollection($products, 'labels', $data, []);
    }

    /** Requires that labels are already fetched - call fetchProductLabels() first */
    public function fetchProductLabelsCouponsAndDiscounts(ProductCollection $products): void
    {
        $orderDiscounts = [];
        foreach ($products as $product) {
            foreach ($product->labels ?? [] as $label) {
                foreach ($label['data']['id_order_discount'] ?? [] as $idOrderDiscount) {
                    $orderDiscounts[$idOrderDiscount] = [];
                }
            }
        }
        if (!count($orderDiscounts)) {
            return;
        }
        $qb = sqlQueryBuilder()->select("od.id AS id, JSON_EXTRACT(odt.data, '$.codes') AS coupons, JSON_VALUE(oda.data, '$.discount') AS discount")->from('order_discounts', 'od')
            ->leftJoin('od', 'order_discounts_triggers', 'odt', "odt.id_order_discount=od.id AND odt.type='coupon' AND JSON_EXTRACT(odt.data, '$.codes') IS NOT NULL")
            ->leftJoin('od', 'order_discounts_actions', 'oda', "oda.id_order_discount=od.id AND oda.type='product_discount' AND JSON_EXTRACT(oda.data, '$.discount') IS NOT NULL")
            ->where(Operator::inIntArray(array_keys($orderDiscounts), 'od.id'))
            ->groupBy('od.id, odt.id, oda.id');
        foreach ($qb->execute() as $row) {
            if (!empty($row['coupons'])) {
                $coupons = json_decode($row['coupons'], true);
                foreach ($coupons ?? [] as $coupon) {
                    if (!empty($coupon)) {
                        $orderDiscounts[$row['id']]['coupons'][$coupon] = $coupon;
                    }
                }
            }
            if (!empty($row['discount'])) {
                $orderDiscounts[$row['id']]['discounts'][$row['discount']] = $row['discount'];
            }
        }
        foreach ($orderDiscounts as &$data) {
            if (!empty($data['coupons'])) {
                $data['coupons'] = array_values($data['coupons']); // use simple array
            }
            if (!empty($data['discounts'])) {
                $data['discounts'] = array_values($data['discounts']); // use simple array
            }
        }
        array_filter($orderDiscounts, fn ($data) => !empty($data['coupons']) || !empty($data['discounts']));
        foreach ($products as $productIndex => $product) {
            foreach ($product->labels ?? [] as $labelIndex => $label) {
                foreach ($orderDiscounts as $orderDiscountID => $orderDiscount) {
                    if (in_array($orderDiscountID, $label['data']['id_order_discount'] ?? [])) {
                        $products[$productIndex]->labels[$labelIndex]['data']['order_discounts'][$orderDiscountID] = $orderDiscount;
                    }
                }
            }
        }
    }

    public function fetchSections(ProductCollection $products)
    {
        $productIds = array_map(function (\Product $product) {
            return $product->id;
        }, array_filter($products->getValues(), function (\Product $product) {
            return is_null($product->sections);
        }));

        if (!$productIds) {
            return;
        }

        $qs = sqlQueryBuilder()->select('s.id id_section, ps.id_product')->from('sections', 's')
            ->join('s', 'products_in_sections', 'ps', 'ps.id_section = s.id')->where(Operator::inIntArray($productIds, 'ps.id_product'))
            ->orderBy('ps.id_product')
            ->addOrderBy('s.priority', 'DESC')
            ->addOrderBy('ps.generated', 'ASC') // on the second level prioritize non-virtual sections
            ->addOrderBy('s.id')
            ->execute();

        $sections = ServiceContainer::getService(\KupShop\CatalogBundle\Section\SectionTree::class);

        $data = [];
        foreach ($qs as $section) {
            if ($loaded_section = $sections->getSectionById($section['id_section'])) {
                $data[$section['id_product']][] = $loaded_section;
            }
        }

        $this->mergeToProductsCollection($products, 'sections', $data, []);
    }

    public function fetchSectionsDirectly(ProductCollection $products): void
    {
        if (!$this->sectionsRepository) {
            $this->fetchSections(...func_get_args());

            return;
        }

        $productIds = array_map(function (\Product $product) {
            return $product->id;
        }, array_filter($products->getValues(), function (\Product $product) {
            return is_null($product->sections);
        }));

        if (!$productIds) {
            return;
        }

        $sectionsByProducts = $this->sectionsRepository->getProductSections($productIds);
        $sectionIds = array_unique(array_merge(...array_values($sectionsByProducts)));
        $allSections = $this->sectionsRepository->findManyById($sectionIds);

        $sections = array_fill_keys(array_keys($sectionsByProducts), []);
        foreach ($sectionsByProducts as $idProduct => $productSectionIds) {
            foreach ($productSectionIds as $sectionId) {
                if (!isset($allSections[$sectionId])) {
                    continue;
                }

                $sections[$idProduct][$sectionId] = $allSections[$sectionId];
            }
        }

        $this->mergeToProductsCollection($products, 'sections', $sections);
    }

    public function fetchProductsRelated(ProductCollection $products)
    {
        $productIds = $products->getProductIds();

        $productList = $this->relatedProductsUtil->getRelatedProductList($productIds);
        $productList->fetchImages(1);

        $products_data = [];
        foreach ($productList->getProducts() as $related_product) {
            $products_data[$related_product->id_top_product][$related_product->id] = $related_product;
        }

        $this->mergeToProductsCollection($products, 'products_related', $products_data);
    }

    public function fetchCollections(ProductCollection $products)
    {
        $productIds = $products->getProductIds();

        if (empty($productIds)) {
            return new ProductCollection();
        }

        $q = sqlQueryBuilder()->select('b.id_product AS `main_product_id`, b.id_product_related AS `product_id`, b2.id_product_related AS `collection_product_id`')
            ->from('products_collections', 'b')
            ->join('b', 'products_collections', 'b2', 'b.id_product = b2.id_product')
            ->where(Operator::inIntArray($productIds, 'b.id_product'))
            ->orWhere(Operator::inIntArray($productIds, 'b.id_product_related'));

        $data = $q->execute()->fetchAll();
        $collectionProductIds = array_unique(array_merge(array_column($data, 'main_product_id'), array_column($data, 'collection_product_id')));
        $productList = new \ProductList();
        $productList->applyDefaultFilterParams();
        $productList->andSpec(Operator::inIntArray($collectionProductIds, 'p.id'));
        $productList->fetchImages(1);
        $collection_products = $productList->getProducts();

        $products_data = [];
        $productIds = array_flip($productIds);
        foreach ($data as $row) {
            $main_product_id = (is_null($collection_products[$row['main_product_id']]) ? 0 : $row['main_product_id']);
            $product_id = (is_null($collection_products[$row['product_id']]) ? 0 : $row['product_id']);
            $collection_product_id = (is_null($collection_products[$row['collection_product_id']]) ? 0 : $row['collection_product_id']);
            if (!empty($product_id)) {
                if (($product_id == $collection_product_id) && (!empty($main_product_id))) {
                    $products_data[$product_id][$main_product_id] = $collection_products[$main_product_id];
                    if (array_key_exists($main_product_id, $productIds)) {
                        $products_data[$main_product_id][$product_id] = $collection_products[$product_id];
                        $products_data[$main_product_id][$main_product_id] = $collection_products[$main_product_id];
                    }
                }
                if (!empty($collection_product_id)) {
                    $products_data[$product_id][$collection_product_id] = $collection_products[$collection_product_id];
                }
            }
        }
        $products_data = array_intersect_key($products_data, $productIds);
        $this->mergeToProductsCollection($products, 'collections', $products_data);

        return $collection_products;
    }

    /**
     * @param $products   ProductCollection collection of products
     * @param $parameters int[] array of parameter IDs
     *
     * @return ProductCollection
     */
    public function fetchParameters(ProductCollection $products, $parameters = null)
    {
        $qb = sqlQueryBuilder()->select('pa.id, pa.value_type, pa.value_meaning, pa.figure,
            pp.value_list as value_id, pp.id_product, pl.data AS param_value_data, pp.weight')
            ->from('parameters', 'pa')
            ->leftJoin('pa', 'parameters_products', 'pp', 'pp.id_parameter=pa.id')
            ->leftJoin('pa', 'parameters_list', 'pl', 'pp.value_list=pl.id')
            ->orderBy('pa.position ASC, pp.weight DESC, pa.id ASC, pl.position');

        if (findModule(\Modules::COMPONENTS)) {
            $qb->addSelect('pa.catalog_figure');
        }

        $qb->andWhere(Translation::joinTranslatedFields(ParametersTranslation::class, function (QueryBuilder $qb, $columnName, $translatedField) {
            if ($columnName == 'unit') {
                $field = $translatedField ? "COALESCE({$translatedField},pp.unit)" : 'pp.unit';
                $qb->addSelect("COALESCE({$field}, pa.unit) as unit");

                return false;
            }
        }, ['unit', 'name', 'descr', 'name_frontend']));

        $columns = ['value', 'description'];
        if (findModule(\Modules::INDEXED_FILTER)) {
            $columns[] = 'filter_url';
        }
        $qb->andWhere(Translation::joinTranslatedFields(ParametersListTranslation::class, function (QueryBuilder $qb, $columnName, $translatedField) {
            if ($columnName == 'value') {
                $field = $translatedField ? "COALESCE({$translatedField},pl.value)" : 'pl.value';
                $qb->addSelect("CASE pa.value_type
                     WHEN 'char' THEN pp.value_char
                     WHEN 'list' THEN {$field}
                     ELSE pp.value_float
                  END as value");

                return false;
            }
        }, $columns));

        // Apply products IDs
        $qb->andWhere(Operator::inIntArray($products->getProductIds(), 'pp.id_product'));

        // Apply parameters
        if (is_array($parameters)) {
            $qb->andWhere(Operator::inIntArray($parameters, 'pa.id'));
        }

        // Apply configurations
        if (findModule(\Modules::PRODUCTS_PARAMETERS, \Modules::SUB_CONFIGURATIONS)) {
            $qb->leftJoin('pp', 'parameters_configurations', 'pc', 'pc.id_product=pp.id_product AND pc.id_parameter = pp.id_parameter')
                ->addSelect('pc.id as configurable, pp.configuration_price');
        }

        $ret = [];
        foreach ($qb->execute() as $row) {
            if (!isset($ret[$row['id_product']])) {
                $ret[$row['id_product']] = [];
            }

            $product = &$ret[$row['id_product']];

            if (!isset($product[$row['id']])) {
                $row['values'] = [];
                $product[$row['id']] = $row;
            }

            $parameter = &$product[$row['id']];
            if ($row['unit']) {
                $row['unit'] = explode('|', $row['unit'])[0];
            }

            if ($row['value_type'] == 'list') {
                $parameter['values'][$row['value_id']] = [
                    'id' => $row['value_id'],
                    'value' => $row['value'],
                    'descr' => $row['description'],
                    'unit' => $row['unit'],
                    'data' => json_decode($row['param_value_data'] ?? '[]', true),
                    'weight' => $row['weight'] ?? null,
                ];
                if (findModule(\Modules::INDEXED_FILTER)) {
                    $parameter['values'][$row['value_id']]['filter_url'] = $row['filter_url'];
                }
                $parameter['value_id'] = $row['value_id'];
            } else {
                $parameter['values'][] = [
                    'value' => $row['value'],
                    'unit' => $row['unit'],
                    'weight' => $row['weight'] ?? null,
                ];
            }
        }

        // Separate parameters and configurations
        foreach ($products as &$product) {
            $product->configurations = $product->configurations ?: [];
            $product->param = $product->param ?: [];

            foreach (getVal($product->id, $ret, []) as $id_parameter => $param) {
                if (!empty($param['configurable'])) {
                    $configuration = \ParameterConfiguration::createFromArray($param);
                    $product->configurations[$id_parameter] = $configuration;
                } else {
                    $product->param[$id_parameter] = $param;
                }
            }
        }

        return $products;
    }

    public function fetchMainImages(ProductCollection $products, $size): void
    {
        $photos = $this->getProductsPhotos($products,
            $size,
            $products->getEntityType() == \FilterParams::ENTITY_PRODUCT ? ['Y'] : false);
        foreach ($products as $product) {
            if (isset($product['variationId']) && isset($photos[$product->id.'/'.$product->variationId])) {
                $key = $product->id.'/'.$product->variationId;
                foreach ($photos[$key] as $photo) {
                    if ($photo['id_variation'] === $product['variationId']) {
                        $product->image = $photo;
                        break;
                    }
                }
            }

            if (empty($product->image) && isset($photos[$product->id])) {
                $product->image = reset($photos[$product->id]);
            }
        }
    }

    /**
     * @param array|null $type
     */
    public function fetchOtherImages(ProductCollection $products, $size, $type = null, bool $fetchVariationImages = true)
    {
        $this->mergeToProductsCollection($products, 'photos', $this->getProductsPhotos($products, $size, $type, $fetchVariationImages));
    }

    public function fetchVariationImages(ProductCollection $products, int $size, ?array $type = null): void
    {
        $photosByProducts = $this->getProductsPhotos($products, $size, $type);

        $this->mergeToProductsCollection($products, 'photos', $photosByProducts);

        foreach ($photosByProducts as $productId => $photos) {
            if (!($products[$productId] ?? false)) {
                continue;
            }

            foreach ($photos as $photo) {
                if (!empty($photo['id_variation']) && !empty($products[$productId]->variations) && ($products[$productId]->variations[$photo['id_variation']] ?? false)) {
                    $products[$productId]->variations[$photo['id_variation']]['photos'][] = $photo;
                }
            }
        }
    }

    public function fetchVariationsByLabel(ProductCollection $products, int $labelId): void
    {
        if (!findModule(\Modules::PRODUCTS_VARIATIONS)) {
            return;
        }

        $productsCollection = $products;
        $products = $products->getProductsOnly();

        $qb = sqlQueryBuilder()->select('pv.*')
            ->from('products_variations', 'pv')
            ->join('pv', 'products', 'p', 'p.id = pv.id_product')
            ->leftJoin('pv', 'products_variations_combination', 'pvc2', 'pv.id=pvc2.id_variation')
            ->leftJoin('pv', 'products_variations_choices_values', 'pvv2', 'pvv2.id = pvc2.id_value')
            ->where(Operator::inIntArray($productsCollection->getProductIds(), 'pv.id_product'))
            ->andWhere(Operator::equals(['pvc2.id_label' => $labelId]))
            ->orderBy('pv.id_product');

        $this->mergeToProductsCollection(
            $productsCollection,
            'variations',
            $this->loadVariations($qb, $products, [$labelId => []], true)
        );
    }

    /**
     * @param ProductCollection $products
     */
    public function fetchVariations($products, $labels = [], $filterOnlyVisible = true)
    {
        $productIds = array_map(function (\Product $product) {
            return $product->id;
        }, array_filter($products->getValues(), function (\Product $product) {
            return is_null($product->variations);
        }));

        if (!$productIds) {
            return;
        }

        if ($labels !== [] && findModule('products_variations')) {
            $productsCollection = $products;
            $products = $products->getProductsOnly();

            $qb = sqlQueryBuilder()->select('pv.*')
                ->from('products_variations', 'pv')
                ->join('pv', 'products', 'p', 'p.id = pv.id_product')
                ->where(Operator::inIntArray($productIds, 'pv.id_product'))
                ->orderBy('pv.id_product');

            $this->mergeToProductsCollection(
                $productsCollection,
                'variations',
                $this->loadVariations($qb, $products, $labels, false, $filterOnlyVisible),
                []
            );
        }
    }

    public function fetchVariationsEntities(ProductCollection $products, bool $filterOnlyVisible = true): void
    {
        if (!findModule(\Modules::PRODUCTS_VARIATIONS)) {
            return;
        }

        $productIds = array_map(function (\Product $product) {
            return $product->id;
        }, array_filter($products->getValues(), function (\Product $product) {
            return is_null($product->variations);
        }));

        if (!$productIds) {
            return;
        }

        $qb = sqlQueryBuilder()->select('pv.*')
            ->from('products_variations', 'pv')
            ->join('pv', 'products', 'p', 'p.id = pv.id_product')
            ->where(Operator::inIntArray($productIds, 'pv.id_product'))
            ->orderBy('pv.id_product');

        $variations = $this->loadVariations($qb, $products->getProductsOnly(), null, false, $filterOnlyVisible);

        $variationsEntities = [];
        foreach ($variations as $productId => $productVariations) {
            foreach ($productVariations as $variationId => $variationRow) {
                $variationsEntities[$productId][$variationId] = $this->variationsUtil->createEntityFromArray($variationRow);
            }
        }

        $this->mergeToProductsCollection($products, 'variations', $variationsEntities, []);
    }

    public function fetchDeliveryText(ProductCollection $products): void
    {
        if (findModule(\Modules::PRODUCTS_SUPPLIERS)) {
            $this->fetchProductOfSuppliersInStore($products);
        }

        foreach ($products as $product) {
            $product->prepareDeliveryText();
        }
    }

    public function fetchProductOfSuppliersInStore(ProductCollection $products)
    {
        if (!findModule(\Modules::PRODUCTS_SUPPLIERS)) {
            return;
        }

        $productIds = array_map(function (\Product $product) {
            return $product->id;
        }, array_filter($products->getValues(), function (\Product $product) {
            return is_null($product->in_store_suppliers);
        }));

        if (!$productIds) {
            return;
        }

        // pokud je entityType=product, tak groupuju jen podle id_product a id musi byt pouze id_product
        $columnId = 'p.id_product AS id';
        $groupBy = 'p.id_product';

        // pokud je entityType=variation, tak groupuju i podle id_variation a id musi byt id_product/id_variation
        if ($products->getEntityType() === FilterParams::ENTITY_VARIATION) {
            $columnId = "CONCAT_WS('/', p.id_product, p.id_variation) AS id";
            $groupBy = 'p.id_product, p.id_variation';
        }

        $qb = sqlQueryBuilder()
            ->select($columnId.', SUM(p.in_store) AS in_store , COALESCE(forecasted_pieces,0) as forecasted_pieces, forecasted_delivery_date')
            ->from('(
                SELECT
                  MIN(in_store) as in_store,
                  pos.id_product,
                  pos.id_variation,
                  pos.id_supplier,
                  pos.forecasted_pieces,
                  pos.forecasted_delivery_date
                FROM products_of_suppliers pos
                WHERE in_store >= 0
                GROUP BY id_product, id_variation, id_supplier
              )', 'p')
            ->where(Operator::inIntArray($products->getProductIds(), 'p.id_product'))
            ->groupBy($groupBy);

        $data = [];
        $forecastedPiecesData = [];
        $forecastedDeliveryDateData = [];
        foreach ($qb->execute() as $product) {
            $data[$product['id']] = $product['in_store'];
            $forecastedPiecesData[$product['id']] = $product['forecasted_pieces'];
            $forecastedDeliveryDateData[$product['id']] = isset($product['forecasted_delivery_date']) ? new \DateTimeImmutable($product['forecasted_delivery_date']) : null;
        }

        $this->mergeToVariationsCollection($products, 'in_store_suppliers', $data, 0);
        // `in_store_suppliers` can be replaced when `prepareDeliveryText` is called, so save in_store_suppliers to
        // `in_store_suppliers_original` so we are able to retrieve original value when needed
        $this->mergeToVariationsCollection($products, 'in_store_suppliers_original', $data, 0);
        $this->mergeToVariationsCollection($products, 'forecasted_pieces', $forecastedPiecesData, 0);
        $this->mergeToVariationsCollection($products, 'forecasted_delivery_date', $forecastedDeliveryDateData);
    }

    public function fetchSuppliers(ProductCollection $products): void
    {
        if (!findModule(\Modules::SUPPLIERS)) {
            return;
        }

        $qb = sqlQueryBuilder()
            ->select("CONCAT_WS('/', pos.id_product, pos.id_variation) AS id, s.name as supplier, 
                      pos.id_product, pos.id_variation, pos.id_supplier, pos.in_store")
            ->from('products_of_suppliers', 'pos')
            ->join('pos', 'suppliers', 's', 's.id = pos.id_supplier')
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'pos.id_product'))
            ->andWhere(Operator::orX('pos.id_variation IS NULL', Operator::inIntArray($products->getVariationIds(), 'pos.id_variation')))
            ->orderBy('pos.in_store DESC, pos.id_supplier');

        $suppliers = [];
        foreach ($qb->execute() as $row) {
            $suppliers[$row['id']][$row['id_supplier']] = ['supplier' => $row['supplier'], 'in_store' => $row['in_store']];
        }

        $this->mergeToVariationsCollection($products, 'suppliers', $suppliers);
    }

    public function fetchLinks(ProductCollection $products): void
    {
        $qb = sqlQueryBuilder()->select('l.*')->from('links', 'l')
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'l.id_product'))
            ->orderBy('l.id')
            ->execute();

        $data = [];
        foreach ($qb as $link) {
            $key = count($data[$link['id_product']] ?? []);
            $data[$link['id_product']][] = [
                'id' => $link['id'],
                'type' => $link['type'],
                'title' => empty($link['title']) ? '['.($key + 1).']' : $link['title'],
                'link' => $link['link'],
            ];
        }

        $this->mergeToProductsCollection($products, 'links', $data);
    }

    public function fetchVariationLabels(ProductCollection $products)
    {
        $qb = sqlQueryBuilder()->select("CONCAT_WS('/', p.id, pv.id) AS id, pvcl.label, pvcv.value")->fromProducts()
            ->joinVariationsOnProducts()
            ->joinVariationsLabelsAndValues()
            ->andWhere(Translation::coalesceTranslatedFields(
                VariationsLabelsTranslation::class,
                ['label']
            ))
            ->andWhere(Translation::coalesceTranslatedFields(
                VariationsValuesTranslation::class,
                ['value']
            ))
            ->andWhere(Operator::inIntArray($products->getVariationIds(), 'pv.id'))
            ->groupBy('pvc.id_variation, pvc.id_label, pvc.id_value');
        $data = [];
        foreach ($qb->execute() as $row) {
            $data[$row['id']] = $data[$row['id']] ?? [];
            $data[$row['id']][$row['label']] = $row['value'];
        }
        $this->mergeToVariationsCollection($products, 'variationLabels', $data);
    }

    public function fetchProductsPhotoIds(ProductCollection $products): void
    {
        $variationsAsResult = ($products->getEntityType() === FilterParams::ENTITY_VARIATION);

        $qb = sqlQueryBuilder()
            ->select($variationsAsResult ? 'CONCAT_WS("/", p.id, pv.id) AS id' : 'p.id')
            ->from('products', 'p')->joinVariationsOnProducts()
            ->addSelect(Product::withProductPhotoId($variationsAsResult))
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'p.id'));
        if ($variationsAsResult) {
            $qb->andWhere(Operator::orX(Operator::inIntArray($products->getVariationIds(), 'pv.id'), 'pv.id IS NULL'));
        }
        $photos = $qb->execute()->fetchAllAssociativeIndexed();

        foreach ($photos as $index => $photo) {
            if (isset($products[$index])) {
                $products[$index]->photoId = $photo['id_photo'];
                $products[$index]->photoDescr = $photo['descr_photo'];
                $products[$index]->photoDateUpdate = $photo['id_photo_update'];
            }
        }
    }

    public function fetchProductsOnTheWay(ProductCollection $products): void
    {
        $measureUnitUtil = ServiceContainer::getService(ProductGetOnTheWay::class);

        $onTheWayAll = $measureUnitUtil->getOnTheWayMulti($products->getProductIds());

        $productsOnTheWay = array_filter(array_map(fn ($val) => $val['product'] ?? null, $onTheWayAll));
        $variationsOnTheWay = array_map(fn ($val) => $val['variations'] ?? null, $onTheWayAll);

        if ($products->getEntityType() === FilterParams::ENTITY_PRODUCT) {
            $this->mergeToProductsCollection($products, 'onTheWay', $productsOnTheWay);
        }

        foreach ($variationsOnTheWay as $idProduct => $variations) {
            if ($products->getEntityType() === FilterParams::ENTITY_VARIATION) {
                $variationsMerge = [];
                foreach ($variations as $idVariation => $variation) {
                    $variationsMerge[$idProduct.'/'.$idVariation] = $variation;
                }
                $this->mergeToVariationsCollection($products, 'onTheWay', $variationsMerge, []);
                continue;
            }

            foreach ($variations as $idVariation => $variation) {
                if ($products[$idProduct]->variations[$idVariation] ?? false) {
                    $products[$idProduct]->variations[$idVariation]['onTheWay'] = $variation;
                }
            }
        }
    }

    public function fetchProductsCharges(ProductCollection $products, bool $onlyVisible = true): void
    {
        if (!findModule(\Modules::PRODUCTS_CHARGES)) {
            return;
        }

        $qb = sqlQueryBuilder()
            ->select('ch.*, pch.*, ch.id_product as charge_product_id')
            ->from('products_charges', 'pch')
            ->leftJoin('pch', 'charges', 'ch', 'pch.id_charge = ch.id')
            ->where(Operator::inIntArray($products->getProductIds(), 'pch.id_product'))
            ->andWhere(Translation::coalesceTranslatedFields(
                ProductsChargesTranslation::class
            ));

        if ($onlyVisible) {
            $qb->andWhere(Translation::joinTranslatedFields(
                ProductsChargesTranslation::class,
                function (QueryBuilder $qb, $columnName, $translatedField) {
                    $qb->andWhere(Operator::coalesce($translatedField, 'ch.figure').' = "Y" ');

                    return false;
                },
                ['figure']
            ));
        }

        if (findModule(\Modules::PRODUCTS_VARIATIONS)) {
            $qb->addSelect('ch.id_variation as charge_variation_id');
        }

        $data = array_fill_keys($products->getProductIds(), []);
        foreach ($qb->execute() as $charge) {
            $charge['data'] = json_decode($charge['data'] ?: '', true) ?: [];
            $charge_product = false;
            if ($id_product = $charge['charge_product_id'] ?? null) {
                if ($id_variation = $charge['charge_variation_id'] ?? null) {
                    $charge_product = new \Variation($id_product, $id_variation);
                } else {
                    $charge_product = new \Product($id_product);
                }
                if (!$charge_product->createFromDB()) {
                    $charge_product = false;
                }
            }
            $charge['product'] = $charge_product;
            if ($charge_product) {
                $order_not_in_store = ($charge['data']['order_not_in_store'] ?? false);
                if (!$order_not_in_store && ($charge_product->inStore <= 0)) {
                    $charge['figure'] = 'N';
                }
            }
            $chargeCurrency = ServiceContainer::getService(\KupShop\KupShopBundle\Context\CurrencyContext::class)->getOrDefault($charge['currency'] ?? null);
            $vat_id = $charge_product?->vat_id ?? $charge['vat'];
            // pokud priplatek ma Příplatkový produkt, vat id se vezme od nej, jinak - vat id nastaveny na priplatku,
            // pokud vat id = null ("podle produktů"), musi zustat null,
            // nesmi se volat getVat($charge['vat']), protoze to muze zmenit $charge['vat'] z null na default
            $charge['price'] = new Price(toDecimal($charge['price']), $chargeCurrency, getVat($vat_id));
            $data[$charge['id_product']][$charge['id']] = $charge;
        }

        $this->mergeToProductsCollection($products, 'charges', $data);
    }

    public function fetchProducers(ProductCollection $products)
    {
        $producersIds = array_map(function (\Product $product) {
            return $product->idProducer;
        }, array_filter($products->getValues(), function (\Product $product) {
            return is_null($product->producer);
        }));

        if (!$producersIds || !findModule(\Modules::PRODUCERS)) {
            return;
        }

        $qb = sqlQueryBuilder()->select('pr.*')
            ->from('producers', 'pr')
            ->where(Operator::inIntArray($producersIds, 'pr.id'))
            ->andWhere('pr.active="Y"')
            ->andWhere(Translation::coalesceTranslatedFields(ProducersTranslation::class))
            ->execute()
            ->fetchAllAssociative();

        foreach ($qb as &$producer) {
            $producer['title'] = $producer['name'];
        }

        $producers = [];
        foreach ($qb as $producerData) {
            if ($producerData['photo']) {
                $producerData['image'] = getImage($producerData['id'], $producerData['photo'], '../producer/', 7, $producerData['name'], strtotime($producerData['date_updated']));
            }
            $producerData['data'] = json_decode($producerData['data'] ?? '[]', true);
            $producers[$producerData['id']] = $producerData;
        }

        foreach ($products as $product) {
            if (is_null($product->producer)) {
                $product->producer = $producers[$product->idProducer] ?? [];
            }
        }
    }

    public function fetchFavorites(ProductCollection $products)
    {
        $productIds = $products->getProductIds();
        $data = array_fill_keys($productIds, false);

        if ($user = \User::getCurrentUser()) {
            $favoriteProducts = sqlFetchAll(sqlQueryBuilder()->select('id_product')->from('products_favorites')
                ->where(Operator::equals(['id_user' => $user->id]))
                ->andWhere(Operator::inIntArray($productIds, 'id_product'))
                ->execute(), ['id_product' => 'id_product']);
        } elseif (findModule(\Modules::PRODUCTS_FAVORITES, \Modules::SUB_PRODUCTS_FAVORITES_NOT_LOGGED)) {
            $cookieProducts = FavoriteProductsUtil::getCookieProducts();
            $favoriteProducts = array_intersect($cookieProducts, $productIds);
        }

        if (!empty($favoriteProducts)) {
            $favoriteProducts = array_fill_keys($favoriteProducts, true);
            $data = array_replace($data, $favoriteProducts);
        }

        $this->mergeToProductsCollection($products, 'is_favorite', $data);
    }

    /**
     * @param bool $byVariations - pokud je `true`, tak se do memberu `storesInStoreByVariations` nactou skladovosti
     *                           rozdelene podle variant (!standartne se nacitaji do `storesInStore`!)
     */
    public function fetchStoresInStore(ProductCollection $products, bool $byVariations = false, bool $frontedQuantity = true): void
    {
        if (!findModule(\Modules::STORES)) {
            return;
        }

        $qb = sqlQueryBuilder()
            ->select('si.id_store, s.name AS store_name, s.id_delivery, s.data as store_data, si.id_product AS id, 
            s.figure as store_figure, si.id_variation, s.type')
            ->from('stores_items', 'si')
            ->leftJoin('si', 'stores', 's', 's.id=si.id_store')
            ->where(Operator::inIntArray($products->getProductIds(), 'si.id_product'))
            ->orderBy('si.id_store');

        if ($frontedQuantity) {
            // use `getStoresQuantityField` so we are able to customize it in same way as `getInStoreField`
            $qb->addSelect(Product::getStoresQuantityField($qb).' as quantity');
        } else {
            $qb->addSelect('SUM(si.quantity) as quantity');
        }

        // pokud je entityType=product, tak groupuju jen podle id_product a id musi byt pouze id_product
        $qb->groupBy('si.id_store, si.id_product');
        if ($byVariations) {
            $qb->addGroupBy('si.id_variation');
        }

        // pokud je entityType=variation, tak groupuju i podle id_variation a id musi byt id_product/id_variation
        if ($products->getEntityType() === FilterParams::ENTITY_VARIATION) {
            $qb->addSelect("IF(si.id_variation IS NOT NULL, CONCAT_WS('/', si.id_product, si.id_variation), si.id_product) as id")
                ->groupBy('si.id_store, si.id_product, si.id_variation')
                ->andWhere(
                    Operator::orX(
                        Operator::inIntArray($products->getVariationIds(), 'si.id_variation'),
                        'si.id_variation IS NULL'
                    )
                );
        }

        $data = [];
        $dataByVariations = [];
        foreach ($qb->execute() as $row) {
            $data[$row['id']] = $data[$row['id']] ?? [];
            $item = [
                'name' => $row['store_name'],
                'figure' => $row['store_figure'],
                'id_delivery' => $row['id_delivery'],
                'data' => json_decode($row['store_data'] ?? '', true) ?? [],
                'in_store' => $row['quantity'],
                'type' => $row['type'],
            ];

            if ($byVariations && $row['id_variation']) {
                $dataByVariations[$row['id']][$row['id_variation']][$row['id_store']] = $item;
            } else {
                $data[$row['id']][$row['id_store']] = $item;
            }
        }

        $this->mergeToVariationsCollection($products, 'storesInStore', $data);
        if ($byVariations) {
            $this->mergeToVariationsCollection($products, 'storesInStoreByVariations', $dataByVariations);
        }
    }

    /**
     * Use $type = false, when you want all types (slow query optimization - mariadb bug).
     */
    private function getProductsPhotos(ProductCollection $products, $size, $type = null, bool $fetchVariationPhotos = true): array
    {
        if ($type === null) {
            $type = ['N'];
        }

        $qb = sqlQueryBuilder()->select(
            'pp.id_product, pp.id_variation, ph.id, ph.descr, ph.source, ph.image_2, pp.show_in_lead, ph.date_update'
        )
            ->from('photos', 'ph')
            ->join('ph', 'photos_products_relation', 'pp', 'pp.id_photo=ph.id')
            ->where('pp.active=\'Y\'')
            ->andWhere(Translation::coalesceTranslatedFields(PhotosTranslation::class))
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'pp.id_product'))
            ->orderBy('pp.position');

        if ($type !== false) {
            $qb->andWhere(Operator::inStringArray($type, 'pp.show_in_lead'));
        }

        if (!$fetchVariationPhotos) {
            $qb->andWhere(Operator::isNull('pp.id_variation'));
        }

        $photos = $qb->execute()->fetchAll();

        $data = [];
        foreach ($photos as $photo) {
            // skip photos with empty image_2
            if (empty($photo['image_2'])) {
                continue;
            }

            $key = $photo['id_product'];
            if ($products->getEntityType() === \FilterParams::ENTITY_VARIATION && $photo['id_variation']) {
                $key = $photo['id_product'].'/'.$photo['id_variation'];
            }

            $data[$key][] = array_merge(
                getImage($photo['id'], $photo['image_2'], $photo['source'], $size, $photo['descr'], strtotime($photo['date_update'])),
                ['show_in_lead' => $photo['show_in_lead'], 'id_variation' => $photo['id_variation']]
            );
        }

        return $data;
    }

    private function loadVariations(QueryBuilder $qb, $products, $labels, bool $isGrouped = false, bool $filterOnlyVisible = true)
    {
        $qb->addSelect('pvc.id_label id_label, pvcl.label, 
                        pvcv.id as id_value, pvcv.code as value_code, pvcv.data as value_data, pvcv.sort as variation_sort')
            ->addSelect(Product::withVat())
            ->joinVariationsLabelsAndValues()
            ->andWhere(Translation::coalesceTranslatedFields(VariationsValuesTranslation::class, ['value']))
            ->andWhere(Translation::coalesceTranslatedFields(VariationsTranslation::class))
            ->andWhere(Translation::coalesceTranslatedFields(VariationsLabelsTranslation::class));

        if ($isGrouped) {
            $qb->groupBy('pv.id_product, pvcv.id');
        } else {
            $qb->groupBy('pv.id, pvcv.id');
        }

        if (!empty($labels) && is_array($labels)) {
            $qb->andWhere(Operator::inIntArray(array_keys($labels), 'pvc.id_label'));
        }

        if (findModule(\Modules::PRODUCTS_VARIATIONS_PHOTOS)) {
            $photoQb = sqlQueryBuilder()
                ->select('ppr.id_photo')
                ->from('photos_products_relation', 'ppr')
                ->where('ppr.id_variation = pv.id AND ppr.show_in_lead IN ("Y", "N")')
                ->orderBy('ppr.position')
                ->setMaxResults(1);

            $qb->addSubselect($photoQb, 'photo');
        }

        $priceConverter = null;
        if (findModule(\Modules::PRICELISTS)) {
            $pricelistContext = ServiceContainer::getService(\KupShop\PricelistBundle\Context\PricelistContext::class);
            if ($pricelistContext->getActiveId()) {
                $priceConverter = ServiceContainer::getService(\KupShop\I18nBundle\Util\PriceConverter::class);
                $qb->andWhere(\KupShop\PricelistBundle\Query\Product::applyPricelistOnVariation($pricelistContext->getActiveId()));
            }
        }

        $qb->addSelect('COALESCE(pv.price, p.price) as price');
        if (findModule(\Modules::PRICE_HISTORY)) {
            $qb->addSelect('COALESCE(pv.price_for_discount, p.price_for_discount) as price_for_discount');
        }

        if ($filterOnlyVisible) {
            $qb->andWhere(Variation::isVisible());
        }

        $in_store_field = \Query\Product::getInStoreField(true, $qb);
        if (SuppliersUtil::isSuppliersStoreEnabled()) {
            $subquery = sqlQueryBuilder()
                ->select('pos.id_product, pos.id_variation, SUM(COALESCE(pos.in_store, 0)) AS in_store_suppliers, SUM(COALESCE(pos.forecasted_pieces, 0)) AS forecasted_pieces, MIN(pos.forecasted_delivery_date) AS forecasted_delivery_date')
                ->from('products_of_suppliers', 'pos')
                ->groupBy('pos.id_product, pos.id_variation');
            $qb
                ->addSelect("SUM(IF({$in_store_field} > 0, {$in_store_field}, COALESCE(spos.in_store_suppliers, 0))) as in_store") // Tohle je špatně
                ->addSelect("{$in_store_field} AS in_store_variation")
                ->addSelect('COALESCE(spos.in_store_suppliers, 0) AS in_store_suppliers')
                ->addSelect('COALESCE(spos.forecasted_pieces, 0) AS forecasted_pieces')
                ->addSelect('spos.forecasted_delivery_date AS forecasted_delivery_date')
                ->leftJoinSubQuery('pv', $subquery, 'spos', 'spos.id_product=p.id AND spos.id_variation=pv.id');
        } else {
            $qb->addSelect('SUM('.$in_store_field.') in_store');
        }

        $dbcfg = \Settings::getDefault();
        if ($dbcfg->prod_show_not_in_store == 'N' && $filterOnlyVisible) {
            $qb->having('in_store > 0');
        }

        $currencyContext = ServiceContainer::getService(\KupShop\KupShopBundle\Context\CurrencyContext::class);

        $result = $qb->execute()->fetchAll();

        $variantCodes = [];
        $variantValues = [];
        $variantValuesData = [];
        $variantLabels = [];
        $variantCombination = [];

        foreach ($result as $variant) {
            $variantCodes[$variant['id']][$variant['id_label']] = $variant['value_code'];
            $variantValues[$variant['id']][$variant['id_label']] = $variant['value'];
            $variantValuesData[$variant['id']][$variant['id_label']] = $variant['value_data'] ? json_decode($variant['value_data'], true) : [];
            $variantLabels[$variant['id']][$variant['id_label']] = [
                'id' => $variant['id_label'],
                'title' => $variant['label'],
            ];
            $variantCombination[$variant['id']][$variant['id_label']] = [
                'id' => $variant['id_value'],
                'title' => $variant['value'],
                'code' => $variant['value_code'],
                'data' => $variantValuesData[$variant['id']][$variant['id_label']],
                'sort' => $variant['variation_sort'] ?? 0,
            ];
        }

        $ret = [];
        foreach ($result as $index => $variant) {
            if (!empty($ret[$variant['id_product']][$variant['id']])) {
                $ret[$variant['id_product']][$variant['id']]['pos'][] = $index;
                continue;
            }

            $variant['pos'][] = $index;
            $variant['labels'] = $variantLabels[$variant['id']];
            $variant['combination'] = $variantCombination[$variant['id']];
            $variant['values'] = $variantValues[$variant['id']];
            $variant['values_codes'] = $variantCodes[$variant['id']];
            $variant['values_data'] = $variantValuesData[$variant['id']];
            $variant['original_vat'] = $variant['original_vat'] ?? $variant['vat'];
            $variant['forecasted_delivery_date'] = empty($variant['forecasted_delivery_date']) ?
                null :
                new \DateTimeImmutable($variant['forecasted_delivery_date']);

            // Backward compatibility with templates - values from old way of variations fetching
            foreach ($variantValues[$variant['id']] as $valueLabelId => $value) {
                $variant["value_{$valueLabelId}"] = $value;
            }

            foreach ($variantCodes[$variant['id']] as $codeLabelId => $code) {
                $variant["value_code_{$codeLabelId}"] = $code;
            }

            /** @var \Product $product */
            $product = $products[$variant['id_product']];

            $variant['delivery_time_text'] = getProductDeliveryText($variant['in_store'], $variant['delivery_time']);

            $variant['data'] = json_decode($variant['data'] ?? '', true) ?? [];

            // save original price
            $priceOriginal = new ProductPrice(toDecimal($variant['price']), $currencyContext->getDefault(), $product->vat);
            $variant['priceOriginal'] = PriceWrapper::wrap($priceOriginal);

            $productPrice = new ProductPrice(toDecimal($variant['price']), $currencyContext->getDefault(), $product->vat, $product->discount);

            if (!empty($variant['price_for_discount'])) {
                $priceForDiscount = new ProductPrice(toDecimal($variant['price_for_discount']), $currencyContext->getDefault(), $product->vat);
                $variant['price_for_discount'] = PriceWrapper::wrap($priceForDiscount);
            }

            // price_common in actual currency
            $original_vat = $product['original_vat'] ?? $product['vat'];
            $price_common = toDecimal($variant['price_common'])->removeVat(getVat($original_vat));
            $priceCommon = new ProductPrice($price_common, $currencyContext->getDefault(), $product->vat);
            $variant['priceCommon'] = PriceWrapper::wrap($priceCommon);

            // apply pricelist price with pricelist discount
            if (findModule(\Modules::PRICELISTS) && $priceConverter) {
                if (!$variant['pricelist_currency']) {
                    $variant['pricelist_currency'] = $currencyContext->getDefault()->getId();
                }

                $discount = $variant['from_pricelist'] != null ? $variant['pricelistDiscount'] : $product->discount->asFloat();

                /** @var \KupShop\PricelistBundle\Entity\Pricelist $activePricelist */
                $activePricelist = Contexts::get(PricelistContext::class)->getActive();

                $newProductPrice = new PriceListPrice(
                    toDecimal($variant['pricelist_price']),
                    $currencyContext->getOrDefault($variant['pricelist_currency']),
                    $product->vat,
                    $discount,
                );
                $newProductPrice->setOriginalPrice($productPrice);

                $coefficient = $activePricelist?->getCoefficient();
                if ($coefficient && in_array($variant['price_source'], ['p', 'pv'])) {
                    $variant['pricelist_price'] *= $coefficient;
                    /* v pripade koeficientu na ceniku je aplikovan i na originalPrice */
                    $newProductPrice->applyCoefficient(toDecimal($coefficient));
                }

                $productPrice = $newProductPrice;

                $variant['pricelist_price'] = $priceConverter->convert($variant['pricelist_currency'], 'CZK', $variant['pricelist_price']);
            }

            if (findModule(\Modules::PRICE_LEVELS) && $pricelevel = Contexts::get(PriceLevelContext::class)->getActive()) {
                /** @var PriceLevel $pricelevel */
                $newProductPrice = new PriceLevelPrice($productPrice);

                $pricelevelDiscount = $pricelevel->getDiscount(
                    IDp: $product->id,
                    IDprod: $product->idProducer,
                    product: $product,
                );

                $newProductPrice->setPricelevelDiscount($pricelevelDiscount);
                $productPrice = $newProductPrice;
            }

            $variant['productPrice'] = PriceWrapper::wrap($productPrice);
            $variant['price'] = $variant['productPrice'];

            \Variations::recalculatePricesVatFromPriceWithVat($variant);

            $ret[$variant['id_product']][$variant['id']] = $variant;
        }

        foreach ($ret as &$variations) {
            uasort($variations, function ($a, $b) {
                return $a['pos'] <=> $b['pos'];
            });
        }

        return $ret;
    }

    /**
     * @param bool|callable|null $showOutOfStock
     */
    public function fetchGifts(ProductCollection $products, $showOutOfStock = null): void
    {
        if (!findModule(\Modules::PRODUCT_GIFTS)) {
            return;
        }

        $gifts = sqlQueryBuilder()
            ->select('pg.*, p.title as title_raw')
            ->from('products_gifts', 'pg')
            ->join('pg', 'products', 'p', 'p.id = pg.id_product_gift')
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'pg.id_product'))
            ->groupBy('pg.id_product, pg.id_product_gift')
            ->execute()->fetchAllAssociative();

        $giftProductIds = array_map(function ($x) { return $x['id_product_gift']; }, $gifts);

        $productList = new ProductList();
        $productList->andSpec(Operator::inIntArray($giftProductIds, 'p.id'))
                    ->andSpec(function (QueryBuilder $qb) {$qb->addSelect(\Query\Product::withProductPhotoId()); });

        if (!$showOutOfStock) {
            $productList->andSpec(\Query\Product::inStore());
        }

        if (is_callable($showOutOfStock)) {
            $productList->andSpec($showOutOfStock);
        }

        $giftProducts = $productList->getProducts();

        $productGifts = [];
        foreach ($gifts as $gift) {
            if (!($giftProduct = ($giftProducts[$gift['id_product_gift']] ?? false))) {
                continue;
            }

            $giftProduct['selected_variation'] = $gift['id_variation_gift'] ?? null;
            $giftProduct['gift_pieces'] = $gift['pieces'] ?? null;
            $giftProduct['title_raw'] = $gift['title_raw'] ?? null;

            if ($giftProduct['pieces'] > 1) {
                $giftProduct['title'] .= ' ('.$giftProduct['pieces'].' ks)';
            }

            $productGifts[$gift['id_product']][$gift['id_product_gift']] = $giftProduct;
        }

        $this->mergeToProductsCollection($products, 'gifts', $productGifts, []);
    }

    public function fetchSets(ProductCollection $products, $showOutOfStock = null): void
    {
        if (!findModule(\Modules::PRODUCT_SETS)) {
            return;
        }

        $productList = new ProductList();
        $qb = $productList->getQueryBuilder()
            ->addSelect('ps.id as set_id, ps.id_product as id_product, ps.id_variation as selected_variation, ps.pieces as set_pieces, ps.price as set_price')
            ->join('p', 'products_sets', 'ps', 'p.id = ps.id_product_set')
            ->andWhere(Operator::inIntArray(array_map(function ($x) {
                return $x->id;
            }, $products->toArray()), 'ps.id_product'))
            ->andWhere('pv.id = ps.id_variation OR ps.id_variation IS NULL')
            ->groupBy('ps.id');

        if ($showOutOfStock === null) {
            $showOutOfStock = findModule('products_sets', 'showOutOfStock');
        }
        if (!$showOutOfStock) {
            $qb->andWhere(\Query\Product::inStore());
        }

        $currencyContext = Contexts::get(CurrencyContext::class);

        $ret = [];
        foreach ($qb->execute() as $row) {
            if ($row['id_variation'] !== null) {
                $set_product = new \Variation();
            } else {
                $set_product = new \Product();
            }

            $set_product->createFromArray($row);
            $set_product->prepareDeliveryText();

            $setPrice = null;
            $setProductPrice = null;
            if (!empty($row['set_price'])) {
                $setPrice = new ProductPrice(
                    toDecimal($row['set_price']),
                    $currencyContext->getDefault(),
                    $set_product->vat
                );

                $setProductPrice = clone $setPrice;
                // keep it as price level discount
                if ($set_product->getProductPrice() instanceof PriceLevelPrice) {
                    $setProductPrice = $this->setsPriceCalculator->convertProductPriceToPriceLevelPrice($setProductPrice, $set_product->getProductPrice(), false);
                }

                $set_product->setProductPrice($setProductPrice);
            }

            $set_product['selected_variation'] = $row['selected_variation'];
            $set_product['set_id'] = $row['set_id'];
            $set_product['set_pieces'] = $row['set_pieces'];
            $set_product['title_raw'] = $set_product['title'];
            $set_product['set_price'] = $setProductPrice;
            $set_product['set_price_raw'] = $setPrice?->getPriceWithVat()->asFloat();

            if ($set_product['set_pieces'] > 1) {
                $set_product['title'] .= ' ('.$set_product['set_pieces'].' '.translate_shop('pieces', 'product').')';
            }

            $ret[$row['id_product']][] = $set_product;
        }

        foreach ($products as $product) {
            $product->sets = getVal($product->id, $ret, []);

            $this->setsPriceCalculator->calculatePrice($product);
        }
    }

    public function fetchPricelists(ProductCollection $products, bool $onlyExisting = true): void
    {
        $dbcfg = \Settings::getDefault();

        $qb = sqlQueryBuilder()->select("CONCAT_WS('/', p.id, pv.id) AS id",
            'p.id as id_product',
            'pv.id as id_variation',
            'COALESCE(prlv.price, pv.price, prlp.price, p.price) as pricelist_price',
            'COALESCE(prlv.discount, prlp.discount) as pricelist_discount',
            'COALESCE(prlv.id_pricelist, prlp.id_pricelist) as from_pricelist',
            'prl.currency target_currency',
            'COALESCE(IF(prlv.price IS NULL, NULL, "prlv"), 
                            IF(prlp.price IS NULL, NULL, "prlp"), 
                            IF(pv.price IS NULL, NULL, "pv"),                           
                            IF(p.price IS NULL, NULL, "p")) as price_source',
            'COALESCE(IF(prlv.price IS NULL, NULL, prlc_v.currency), 
                            IF(pv.price IS NULL, NULL, ""), 
                            IF(prlp.price IS NULL, NULL, prlc.currency), 
                            IF(p.price IS NULL, NULL, "")) as pricelist_currency',
            'prl.name pricelist_name',
            'prl.id pricelist_id',
            'prl.coefficient AS pricelist_coefficient',
            'p.vat, p.discount as product_discount, prl.use_product_discount as use_product_discount')
            ->addSelect(\Query\Product::withVat())
            ->fromProducts()
            ->joinVariationsOnProducts()
            ->leftJoin('p', 'pricelists', 'prl', '1=1')
            ->leftJoin('p', 'pricelists_products', 'prlp', 'prl.id = prlp.id_pricelist AND prlp.id_product = p.id AND prlp.id_variation IS NULL')
            ->leftJoin('p', 'pricelists_products', 'prlv', 'prl.id = prlv.id_pricelist AND prlv.id_product = p.id AND prlv.id_variation = pv.id')
            ->leftJoin('prlv', 'pricelists', 'prlc', 'prlp.id_pricelist = prlc.id')
            ->leftJoin('prlv', 'pricelists', 'prlc_v', 'prlv.id_pricelist = prlc_v.id')
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'p.id'))
            ->groupBy('prl.id, p.id, pv.id')
            ->orderBy('prl.id, p.id, pv.id');

        if ($onlyExisting) {
            $qb->having("pricelist_discount IS NOT NULL OR (pricelist_discount IS NULL AND pricelist_currency != '')");
        }

        if (findModule(\Modules::PRICE_HISTORY)) {
            $qb->addSelect('COALESCE(prlv.price_for_discount, prlp.price_for_discount) as pricelist_price_for_discount');
        }

        $currencyContext = Contexts::get(CurrencyContext::class);

        $data = [];
        foreach ($qb->execute() as $pricelist) {
            $coefficient = $pricelist['pricelist_coefficient'] ?? null;
            if (isset($coefficient) && in_array($pricelist['price_source'], ['p', 'pv'])) {
                $pricelist['pricelist_price'] *= $coefficient;
            }

            $productPrice = new ProductPrice(
                toDecimal($pricelist['pricelist_price']),
                $currencyContext->getOrDefault($pricelist['pricelist_currency']),
                getVat($pricelist['vat']),
                $pricelist['pricelist_discount'] ?? 0
            );

            if (PriceUtil::isProductPricesVatFromTop()) {
                $productPrice = PriceUtil::recalculatePriceVatFromPriceWithVat($productPrice, (float) getVat($pricelist['original_vat']));
            }

            // prevedu cenu do meny ceniku - napr. v ceniku mam vyplnenou jen slevu, ale ne cenu, tak se mi cena nacte v CZK, ae ja bych idealne
            // potreboval, aby cena byla uz prevedena ve spravne mene podle nastaveni ceniku
            $productPrice = PriceCalculator::convert($productPrice, $currencyContext->getOrDefault($pricelist['target_currency']));

            $productKey = $pricelist['id_product'];
            if ($products->getEntityType() === FilterParams::ENTITY_VARIATION && !empty($pricelist['id_variation'])) {
                $productKey = $pricelist['id_product'].'/'.$pricelist['id_variation'];
            }

            $discount = $pricelist['pricelist_discount'] ?? 0;
            if ($pricelist['use_product_discount'] ?? '0' == '1') {
                $discount = $discount <= 0 ? $pricelist['product_discount'] : $discount;
            }

            $item = [
                'id_pricelist' => $pricelist['pricelist_id'],
                'name' => $pricelist['pricelist_name'],
                'price' => $pricelist['pricelist_price'],
                'discount' => $discount,
                'target_currency' => $pricelist['target_currency'],
                'currency' => $pricelist['pricelist_currency'],
                'vat' => $pricelist['vat'],
                'productPrice' => $productPrice,
            ];

            if (isset($pricelist['pricelist_price_for_discount'])) {
                $item['price_for_discount'] = $pricelist['pricelist_price_for_discount'];
            }

            if (!isset($data[$productKey][$pricelist['pricelist_id']])) {
                $data[$productKey][$pricelist['pricelist_id']] = array_merge($item, ['variations' => []]);
            }

            if ($products->getEntityType() !== FilterParams::ENTITY_VARIATION && $pricelist['id_variation']) {
                $data[$productKey][$pricelist['pricelist_id']]['variations'][$pricelist['id_variation']] = $item;

                if (!$productPrice->getValue()->isZero() && PriceCalculator::firstLower($productPrice, $data[$productKey][$pricelist['pricelist_id']]['productPrice'])) {
                    $data[$productKey][$pricelist['pricelist_id']]['productPrice'] = $productPrice;
                }
            }
        }

        $this->mergeToVariationsCollection($products, 'pricelists', $data);
    }

    public function fetchPriceLevelPrice(ProductCollection $products, $priceLevelID): void
    {
        $contextManager = ServiceContainer::getService(ContextManager::class);
        $contextManager->activateContexts([PriceLevelContext::class => (int) $priceLevelID], function () use ($products) {
            $pricelevelContext = Contexts::get(PriceLevelContext::class);
            if ($pricelevel = $pricelevelContext->getActive()) {
                foreach ($products as &$product) {
                    $productPrice = $product->getProductPrice();
                    if ($productPrice instanceof PriceLevelPrice) {
                        $productPrice = $productPrice->getOriginalPrice();
                    }
                    $product->priceLevelPrice[$pricelevel->id] = new PriceLevelPrice($productPrice);
                    $pricelevelDiscount = $pricelevel->getDiscount($product->id, product: $product);
                    $product->priceLevelPrice[$pricelevel->id]->setPricelevelDiscount($pricelevelDiscount);
                }
            }
        });
    }

    public function fetchDescriptionPlus(ProductCollection $products): void
    {
        if (!findModule(\Modules::PRODUCTS, \Modules::SUB_DESCR_PLUS)) {
            return;
        }

        if ($products->count() <= 0) {
            return;
        }

        $result = sqlQueryBuilder()->addSelect('p.id as id_product, GROUP_CONCAT(b.content ORDER BY b.position ASC SEPARATOR "") as description_plus')
            ->from('products', 'p')
            ->leftJoin('p', 'blocks', 'b', 'b.id_root = p.id_block')
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'p.id'))
            ->andWhere(
                Translation::joinTranslatedFields(BlocksTranslation::class,
                    function (QueryBuilder $qb, $columnName, $translatedField) {
                        if ($translatedField) {
                            $qb->addSelect("GROUP_CONCAT({$translatedField} ORDER BY b.position SEPARATOR '') as block_content");
                        } else {
                            $qb->addSelect('GROUP_CONCAT(b.content ORDER BY b.position SEPARATOR "") as block_content');
                        }
                    },
                    ['content'])
            )->groupBy('p.id');

        $allDescriptionsPlus = $result->execute()->fetchAllKeyValue();

        $this->mergeToProductsCollection($products, 'description_plus', $allDescriptionsPlus);
    }

    public function fetchAttachments(ProductCollection $products): void
    {
        if (!findModule(\Modules::ATTACHMENTS)) {
            return;
        }

        $attachments = sqlQueryBuilder()
            ->select('id_product', 'link', 'title', 'position')
            ->from('attachments')
            ->andWhere(Operator::inIntArray($products->getProductIds(), 'id_product'))
            ->addOrderBy('id_product')
            ->addOrderBy('position', 'ASC')
            ->execute()->fetchAllAssociative();

        $productAttachments = [];
        foreach ($attachments as $attachment) {
            $id = $attachment['id_product'];
            unset($attachment['id_product']);
            $productAttachments[$id][] = $attachment;
        }

        $this->mergeToProductsCollection($products, 'attachments', $productAttachments, []);
    }

    public function fetchReservations(ProductCollection $products): void
    {
        if (!findModule(\Modules::PRODUCT_RESERVATIONS)) {
            return;
        }

        $qb = sqlQueryBuilder()
            ->select('pr.*')
            ->from('product_reservations', 'pr')
            ->where(Operator::inIntArray($products->getProductIds(), 'pr.id_product'));

        $data = [];
        foreach ($qb->execute() as $item) {
            $key = $item['id_product'];
            if ($products->getEntityType() === FilterParams::ENTITY_VARIATION && !empty($item['id_variation'])) {
                $key = $item['id_product'].'/'.$item['id_variation'];
            }

            $data[$key][] = $item;
        }

        foreach ($products as $key => $product) {
            $product->reservations = $data[$key] ?? [];
        }
    }

    public function fetchActiveSellerInStore(ProductCollection $products)
    {
        if (!findModule(\Modules::SELLERS)) {
            return;
        }

        $this->fetchSellersInStore($products);

        $sellerContext = Contexts::get(SellerContext::class);

        $activeSellerId = $sellerContext->getActiveId();
        if (!$activeSellerId || empty($sellerContext->getSupported())) {
            $activeSellerId = -1;
        }

        $data = [];
        foreach ($products as $key => $product) {
            $data[$key] = $product->sellersInStore[$activeSellerId] ?? [];
        }

        $this->mergeToProductsCollection($products, 'activeSellerInStore', $data);
    }

    public function fetchSellersInStore(ProductCollection $products): void
    {
        if (!findModule(\Modules::SELLERS)) {
            return;
        }

        $sellers = Contexts::get(SellerContext::class)->getSupported();

        // pokud neni aktivni prodejna a nebo nejsou prodejny (napr. pro CZ mam prodejny, ale pro DE nemam prodejny), takze
        // getSupported vrati prazdno, tak udelam tenhle hack, diky kteremu se mi aspon nacte in_store_main, ktery musi byt
        // pro vsechny zeme stejny
        if (empty($sellers)) {
            $sellers = [-1 => ['id_store' => null]];
        }

        $sellerMultiFetch = ServiceContainer::getService(SellerMultiFetch::class);

        $productsMap = [];
        foreach ($products->getValues() as $product) {
            $productsMap[$product['id']] = null;
        }

        $sellerMultiFetch->fetchSellersInStore($sellers, $productsMap);

        $data = [];
        foreach ($sellers as $seller) {
            foreach ($seller['products'] ?? [] as $key => $item) {
                $data[$key][$seller['id']] = $item;
            }
        }

        $this->mergeToProductsCollection($products, 'sellersInStore', $data);
    }

    public function fetchConvertorsValues(ProductCollection $productCollection, $convertorsIds = [], bool $showHiddenVariations = false)
    {
        if (!findModule(\Modules::CONVERTORS)) {
            return;
        }

        $convertorsUtil = ServiceContainer::getService(ConvertorsUtil::class);
        $qb = $convertorsUtil->getConvertorsQueryBuilder($productCollection->getProductIds(), $convertorsIds, $showHiddenVariations);

        $data = [];

        foreach ($qb->execute() as $row) {
            $data[$row['id_product']][$row['id_convertor']][$row['param_value']][] = $row;
        }

        $this->mergeToProductsCollection($productCollection, 'convertors', $data);
    }

    public function fetchMeasureUnits(ProductCollection $productCollection)
    {
        if (!findModule(\Modules::PRODUCTS, \Modules::SUB_UNITS)) {
            return;
        }

        $measureUnitUtil = ServiceContainer::getService(MeasureUnitUtil::class);

        $qb = $measureUnitUtil->getUnitPriceQueryBuilder($productCollection->getProductIds());

        $data = [];

        foreach ($qb->execute() as $row) {
            if ($row['id_variation']) {
                $data[$row['id_product']][$row['id_variation']] = $row;
            } else {
                $data[$row['id_product']][] = $row;
            }
        }

        $this->mergeToProductsCollection($productCollection, 'measureUnits', $data);
    }

    public function fetchProductBatches(ProductCollection $productCollection, bool $withQuantity = false, bool $byVariations = false)
    {
        if (!findModule(\Modules::PRODUCTS_BATCHES)) {
            return;
        }

        $batches = $this->productsBatchesUtil->getProductBatches($productCollection, $withQuantity, $byVariations);
        $productBatches = [];
        $variationBatches = [];
        foreach ($batches as $row) {
            $batch['code'] = $row['code'];
            $batch['date_expiry'] = $row['date_expiry'];

            if ($withQuantity) {
                $batch['batch_quantity'] = $row['batch_quantity'];
            }

            if ($byVariations && $row['id_variation']) {
                $variationBatches[$row['id_product']][$row['id_variation']][$row['id']] = $batch;
            } else {
                $productBatches[$row['id_product']][$row['id']] = $batch;
            }
        }

        $this->mergeToProductsCollection($productCollection, 'productBatches', $productBatches);

        if ($byVariations) {
            $this->mergeToVariationsCollection($productCollection, 'variationBatches', $variationBatches);
        }
    }

    #[Required]
    final public function setProductBatchesUtil(?ProductsBatchesUtil $productsBatchesUtil): void
    {
        $this->productsBatchesUtil = $productsBatchesUtil;
    }

    #[Required]
    final public function setSectionsRepository(?SectionsRepository $sectionsRepository): void
    {
        $this->sectionsRepository = $sectionsRepository;
    }

    #[Required]
    final public function setReviewsUtil(?ReviewsUtil $reviewsUtil): void
    {
        $this->reviewsUtil = $reviewsUtil;
    }

    #[Required]
    final public function setVariationsUtil(VariationsEntityUtil $variationsUtil): void
    {
        $this->variationsUtil = $variationsUtil;
    }

    #[Required]
    final public function setRelatedProductsUtil(RelatedProductsUtil $relatedProductsUtil): void
    {
        $this->relatedProductsUtil = $relatedProductsUtil;
    }
}
