<?php

declare(strict_types=1);

namespace External\ZNZBundle\Util;

use External\ZNZBundle\MappingType;
use External\ZNZBundle\Synchronizers\ParameterSynchronizer;
use External\ZNZBundle\Synchronizers\PriceListSynchronizer;
use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\CatalogBundle\Parameters\ParameterFinder;
use KupShop\I18nBundle\Translations\VariationsValuesTranslation;
use KupShop\KupShopBundle\Context\LanguageContext;
use KupShop\KupShopBundle\Context\UserContext;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\KupShopBundle\Util\Functional\Mapping;
use KupShop\StoresBundle\Utils\StoresInStore;
use Query\Operator;

class ZNZUtil
{
    public const STORES_CACHE_KEY = 'store_StoreIdsZNZ';

    private array $znzMaxIdsCache = [];

    public function __construct(
        private readonly ParameterFinder $parameterFinder,
        private readonly StoresInStore $storesInStore,
        private readonly VariationsValuesTranslation $variationsValuesTranslation,
        private readonly ZNZConfiguration $configuration,
    ) {
    }

    public static function withRetryStrategy(callable $fn, bool $silent = true, int $maxTries = 3): mixed
    {
        $try = 0;

        do {
            $try++;

            try {
                return $fn();
            } catch (\Throwable $e) {
                if (!$silent && $try >= $maxTries) {
                    throw $e;
                }

                sleep(1);
            }
        } while ($try < $maxTries);

        return false;
    }

    public function withActivityLog(callable $fn, string $activityName): void
    {
        addActivityLog(ActivityLog::SEVERITY_NOTICE, ActivityLog::TYPE_SYNC, "[ZNZ] {$activityName}: spouštím");

        try {
            $fn();
        } catch (\Throwable $e) {
            addActivityLog(ActivityLog::SEVERITY_ERROR, ActivityLog::TYPE_SYNC, "[ZNZ] {$activityName}: vyskytla se chyba", [
                'error' => $e->getMessage(),
            ]);

            return;
        }

        addActivityLog(ActivityLog::SEVERITY_NOTICE, ActivityLog::TYPE_SYNC, "[ZNZ] {$activityName}: dokončeno");
    }

    public function getOrderNumberWithPrefix(\Order $order): string
    {
        if ($prefix = $this->configuration->getOrderPrefix($order->getLanguage())) {
            return $prefix.$order->order_no;
        }

        return $order->order_no;
    }

    public function getCurrentWebsite(?string $language = null): string
    {
        $language = $language ?: Contexts::get(LanguageContext::class)->getActiveId();

        foreach ($this->configuration->getSupportedWebsites() as $website => $cfg) {
            if (is_array($cfg['language']) && in_array($language, $cfg['language'])) {
                return $website;
            }

            if ($cfg['language'] === $language) {
                return $website;
            }
        }

        return array_keys($this->configuration->getSupportedWebsites())[0];
    }

    public function getActiveStoreIds(?string $language = null): array
    {
        $language ??= Contexts::get(LanguageContext::class)->getActiveId();

        if ($user = Contexts::get(UserContext::class)->getActive()) {
            foreach ($user->getGroups() as $group) {
                if (!empty($group['data']['znz']['stores'])) {
                    return array_values($group['data']['znz']['stores']);
                }
            }
        }

        static $storesByLanguage = [];

        if ($storesByLanguage[$language] ?? false) {
            return $storesByLanguage[$language];
        }

        if (!($stores = getCache(self::STORES_CACHE_KEY))) {
            $qb = sqlQueryBuilder()
                ->select('id, data')
                ->from('stores')
                ->where(Operator::equals(['figure' => 'Y']));

            $stores = [];
            foreach ($qb->execute() as $item) {
                $stores[$item['id']] = json_decode($item['data'] ?: '', true)['languages'] ?? [];
            }

            setCache(self::STORES_CACHE_KEY, $stores);
        }

        $result = [];
        foreach ($stores as $storeId => $languages) {
            if (in_array($language, $languages)) {
                $result[] = $storeId;
            }
        }

        return $storesByLanguage[$language] = $result;
    }

    public function updateParametersProducers(): void
    {
        sqlQuery('INSERT IGNORE INTO parameters_producers (id_parameter, id_producer)
            SELECT pp.id_parameter, pr.id
            FROM parameters_producers pp, producers pr
            GROUP BY pr.id, pp.id_parameter;');
    }

    public function generateProductsRelated(?int $productId = null): void
    {
        $parameterId = $this->getParameterByKey('Rada');

        $qb = sqlQueryBuilder()
            ->select('p.id, GROUP_CONCAT(DISTINCT p2.id) as ids')
            ->from('products', 'p')
            ->join('p', 'products', 'p2', 'p.producer = p2.producer')
            ->join('p', 'parameters_products', 'pp', 'p.id = pp.id_product AND pp.id_parameter = :parameterId')
            ->join('p2', 'parameters_products', 'pp2', 'p2.id = pp2.id_product AND pp2.id_parameter = pp.id_parameter AND pp.value_list = pp2.value_list')
            ->andWhere('p2.id NOT IN ((SELECT pc.id_product AS id FROM products_collections pc WHERE pc.id_product_related = p.id OR pc.id_product = p.id
                UNION
                SELECT pc.id_product_related AS id FROM products_collections pc
                LEFT JOIN products_collections pc_main ON pc_main.id_product = pc.id_product
                WHERE pc_main.id_product = p.id OR pc_main.id_product_related = p.id))')
            ->setParameter('parameterId', $parameterId)
            ->groupBy('p.id, CONCAT(p.producer, "-", pp.value_list)');

        if ($productId) {
            $qb->andWhere(Operator::equals(['p.id' => $productId]));
        }

        // ID typu souvisejiciho zbozi
        $serieRelatedType = $this->configuration->getSerieRelatedTypeId();

        foreach ($qb->execute() as $item) {
            $topProductId = $item['id'];

            // rozparsuju vsechny idcka, ktere se maji nachazet v souvisejicim
            $ids = array_unique(array_map(fn ($x) => (int) $x, explode(',', $item['ids'])));
            $ids = array_filter($ids, fn ($x) => $topProductId != $x);

            $current = sqlQueryBuilder()
                ->select('id_rel_product')
                ->from('products_related')
                ->where(Operator::equals(['id_top_product' => $topProductId]))
                ->orderBy('position')
                ->execute()->fetchFirstColumn();

            // avoid unnecessary delete/insert to decrease binlog size
            if (array_values($current) === array_values($ids)) {
                continue;
            }

            // provedu smazani souvisejiciho zbozi od konkretniho produktu
            sqlQueryBuilder()
                ->delete('products_related')
                ->where(Operator::equals(['id_top_product' => $topProductId]))
                ->execute();

            $insertQb = sqlQueryBuilder()
                ->insert('products_related');

            foreach ($ids as $position => $relatedProductId) {
                $insertQb->multiDirectValues(
                    [
                        'id_top_product' => $topProductId,
                        'id_rel_product' => $relatedProductId,
                        'position' => $position,
                        'type' => $serieRelatedType,
                    ]
                );
            }

            if (!empty($ids)) {
                static::withRetryStrategy(fn () => $insertQb->execute());
            }
        }
    }

    /** Nastaví cenu produktu podle nejnižší ceny u variant */
    public function updateProductPrices(): void
    {
        // update product prices
        sqlQuery('UPDATE products p
            JOIN products_variations pv ON p.id = pv.id_product
            SET p.price = COALESCE((SELECT MIN(pv2.price)
            FROM products_variations pv2
            WHERE pv2.id_product = p.id AND pv2.price > 0 AND pv2.in_store > 0 AND pv2.figure = "Y"), p.price);');

        if ($this->configuration->isB2BMode()) {
            sqlQuery('INSERT INTO pricelists_products (id_pricelist, id_product, price)
                    SELECT 
                        id_pricelist, id_product, MIN(price) as price
                    FROM pricelists_products
                    WHERE id_variation IS NOT NULL AND price > 0
                    GROUP BY id_pricelist, id_product
                    ON DUPLICATE KEY UPDATE price = VALUES(price);');
        }
    }

    public function generateProductCommonPrices(): void
    {
        // aktualizace price_common pro produkty
        sqlQueryBuilder()
            ->update('products', 'p')
            ->join('p', 'znz_products', 'zp', 'zp.id_product=p.id AND zp.id_variation IS NULL')
            ->join('zp', 'znz_products', 'zp2', 'zp2.id_znz = JSON_VALUE(zp.data, "$.discountProductId")')
            ->join('zp2', 'products', 'p2', 'p2.id = zp2.id_product')
            ->join('p2', 'vats', 'v', 'v.id = p2.vat')
            ->leftJoin('zp2', 'products_variations', 'pv2', 'pv2.id = zp2.id_variation')
            ->set('p.price_common', '(COALESCE(pv2.price, p2.price) * ((v.vat + 100) / 100))')
            ->where('JSON_VALUE(zp.data, "$.discountProductId") IS NOT NULL')
            ->execute();

        // aktualizace price_common pro varianty
        sqlQueryBuilder()
            ->update('products_variations', 'pv')
            ->join('pv', 'znz_products', 'zp', 'pv.id_product = zp.id_product AND pv.id = zp.id_variation')
            ->join('zp', 'znz_products', 'zp2', 'zp2.id_znz = JSON_VALUE(zp.data, "$.discountProductId")')
            ->join('zp2', 'products', 'p2', 'p2.id = zp2.id_product')
            ->join('p2', 'vats', 'v', 'v.id = p2.vat')
            ->leftJoin('zp2', 'products_variations', 'pv2', 'pv2.id = zp2.id_variation')
            ->set('pv.price_common', '(COALESCE(pv2.price, p2.price) * ((v.vat + 100) / 100))')
            ->where('JSON_VALUE(zp.data, "$.discountProductId") IS NOT NULL')
            ->execute();
    }

    public function updateProductsPositions(): void
    {
        sqlQueryBuilder()
            ->update('products', 'p')
            ->join('p', 'parameters_products', 'pp', 'p.id = pp.id_product AND id_parameter = (SELECT id_parameter FROM znz_parameters WHERE id_znz = \'Baleni\')')
            ->join('pp', 'parameters_list', 'pl', ' pl.id = pp.value_list')
            ->set('p.position', 10000)
            ->where(Operator::equals(['pl.value' => 'Vzorek']))
            ->execute();
    }

    /**
     * Aktualizace viditelnosti produktu.
     */
    public function updateProductsVisibility(): void
    {
        // zobrazim produkty, ktere maji cenu a maji byt videt
        sqlQueryBuilder()
            ->update('products', 'p')
            ->leftJoin('p', 'znz_products', 'zp', 'p.id = zp.id_product AND zp.id_variation IS NULL')
            ->leftJoin('zp', 'znz_products_website', 'zpw', 'zpw.id_znz = zp.id_znz')
            ->set('p.figure', ':figure')
            ->andWhere('p.price > 0 AND ((p.figure = "N" AND JSON_VALUE(zp.data, "$.znzFigure") = "Y" AND zpw.id_znz IS NOT NULL) OR zp.id_znz IS NULL)')
            ->setParameter('figure', 'Y')
            ->execute();

        // zobrazim varianty, ktere maji cenu a maji byt videt
        sqlQueryBuilder()
            ->update('products_variations', 'pv')
            ->leftJoin('pv', 'znz_products', 'zp', 'pv.id_product = zp.id_product AND pv.id = zp.id_variation')
            ->leftJoin('zp', 'znz_products_website', 'zpw', 'zpw.id_znz = zp.id_znz')
            ->set('pv.figure', ':figure')
            ->andWhere('pv.price > 0 AND ((pv.figure = "N" AND JSON_VALUE(zp.data, "$.znzFigure") = "Y" AND zpw.id_znz IS NOT NULL) OR zp.id_znz IS NULL)')
            ->setParameter('figure', 'Y')
            ->execute();

        // skryju produkty, ktere maji nulovou cenu
        sqlQueryBuilder()
            ->update('products', 'p')
            ->leftJoin('p', 'products_variations', 'pv', 'p.id = pv.id_product')
            ->set('p.figure', ':figure')
            ->andWhere(Operator::equals(['p.figure' => 'Y']))
            ->andWhere('COALESCE(pv.price, p.price) <= 0')
            ->setParameter('figure', 'N')
            ->execute();

        // skryju varianty, ktere maji nulovou cenu
        sqlQueryBuilder()
            ->update('products_variations', 'pv')
            ->join('pv', 'products', 'p', 'p.id = pv.id_product')
            ->set('pv.figure', ':figure')
            ->andWhere(Operator::equals(['pv.figure' => 'Y']))
            ->andWhere('COALESCE(pv.price, p.price) <= 0')
            ->setParameter('figure', 'N')
            ->execute();
    }

    public function getUserNameParts(string $name): array
    {
        $userName = explode(' ', $name);
        $name = array_shift($userName);
        $surname = implode(' ', $userName);

        return [
            'name' => trim($name),
            'surname' => trim($surname),
        ];
    }

    public function getProductVat(int $productId): \Decimal
    {
        static $productVats = [];

        if (!($productVats[$productId] ?? false)) {
            $vat = sqlQueryBuilder()
                ->select('v.vat')
                ->from('products', 'p')
                ->join('p', 'vats', 'v', 'v.id = p.vat')
                ->where(Operator::equals(['p.id' => $productId]))
                ->sendToMaster()
                ->execute()->fetchOne();

            if ($vat === false) {
                $vat = getVat();
            }

            $productVats[$productId] = toDecimal((float) $vat);
        }

        return $productVats[$productId];
    }

    public function getProductMapping($znzId): array
    {
        $item = sqlQueryBuilder()
            ->select('id_product, id_variation')
            ->from('znz_products')
            ->where(Operator::equals(['id_znz' => $znzId]))
            // posilam na master, protoze kdyz nacitam mapping, tak to potrebuju mit vzdycky aktualni
            ->sendToMaster()
            ->execute()->fetchAssociative();

        if ($item) {
            return [(int) $item['id_product'], !empty($item['id_variation']) ? (int) $item['id_variation'] : null];
        }

        return [null, null];
    }

    public function setProductMappingData($znzId, array $data): void
    {
        $qb = sqlQueryBuilder()
            ->update('znz_products')
            ->where(Operator::equals(['id_znz' => $znzId]));

        foreach ($data as $key => $value) {
            $valueKey = 'value_'.$key;
            $qb->set('data', 'JSON_SET(COALESCE(data, "{}"), "$.'.$key.'", :'.$valueKey.')')
                ->setParameter($valueKey, $value);
        }

        $qb->execute();
    }

    public function getProductMappingData(int $znzId, string $key): mixed
    {
        $result = sqlQueryBuilder()
            ->select('JSON_VALUE(data, "$.'.$key.'")')
            ->from('znz_products')
            ->where(Operator::equals(['id_znz' => $znzId]))
            ->sendToMaster()
            ->execute()->fetchOne();

        if ($result) {
            return $result;
        }

        return null;
    }

    public function createProductMapping($znzId, int $productId, ?int $variationId): void
    {
        sqlQueryBuilder()
            ->insert('znz_products')
            ->directValues(
                [
                    'id_znz' => $znzId,
                    'id_product' => $productId,
                    'id_variation' => $variationId,
                ]
            )
            ->execute();
    }

    public function getParameterValueMapping(int $parameterId, $znzId): ?int
    {
        $id = sqlQueryBuilder()
            ->select('id_parameters_value')
            ->from('znz_parameters_values')
            ->where(Operator::equals(['id_znz' => $znzId, 'id_parameter' => $parameterId]))
            ->sendToMaster()
            ->execute()->fetchOne();

        if (!$id) {
            return null;
        }

        return (int) $id;
    }

    public function getLabelValueMapping(int $labelId, mixed $znzId): ?int
    {
        $id = sqlQueryBuilder()
            ->select('id_label_value')
            ->from('znz_label_values')
            ->where(Operator::equals(['id_znz' => $znzId, 'id_label' => $labelId]))
            ->sendToMaster()
            ->execute()->fetchOne();

        if (!$id) {
            return null;
        }

        return (int) $id;
    }

    public function createParameterValueMapping(int $parameterId, int $parameterValueId, $znzId): void
    {
        sqlQueryBuilder()
            ->insert('znz_parameters_values')
            ->directValues(
                [
                    'id_znz' => $znzId,
                    'id_parameter' => $parameterId,
                    'id_parameters_value' => $parameterValueId,
                ]
            )
            ->execute();
    }

    public function getUserMapping($znzId): ?array
    {
        $data = sqlQueryBuilder()
            ->select('*')
            ->from('znz_users')
            ->where(Operator::equals(['id_znz' => $znzId]))
            ->execute()->fetchAssociative();

        if (!$data) {
            return null;
        }

        return $data;
    }

    public function getZNZId(string $type, int $id)
    {
        $id = sqlQueryBuilder()
            ->select('id_znz')
            ->from('znz_'.$type.'s')
            ->where(Operator::equals(['id_'.$type => $id]))
            ->sendToMaster()
            ->execute()->fetchOne();

        if (!$id) {
            return null;
        }

        return $id;
    }

    public function getMapping(string $type, mixed $znzId): ?int
    {
        $id = sqlQueryBuilder()
            ->select('id_'.$type)
            ->from('znz_'.$type.'s')
            ->where(Operator::equals(['id_znz' => $znzId]))
            ->sendToMaster()
            ->execute()->fetchOne();

        if (!$id) {
            return null;
        }

        return (int) $id;
    }

    public function createMapping(string $type, $znzId, int $id): void
    {
        sqlQueryBuilder()
            ->insert('znz_'.$type.'s')
            ->directValues(
                [
                    'id_znz' => $znzId,
                    'id_'.$type => $id,
                ]
            )
            ->execute();
    }

    public function getProductByCode(string $code): array
    {
        $productId = sqlQueryBuilder()
            ->select('id')
            ->from('products')
            ->where(Operator::equals(['code' => $code]))
            ->execute()->fetchOne();

        if ($productId) {
            return [(int) $productId, null];
        }

        $variation = sqlQueryBuilder()
            ->select('id, id_product')
            ->from('products_variations')
            ->where(Operator::equals(['code' => $code]))
            ->execute()->fetchAssociative();

        if ($variation) {
            return [(int) $variation['id_product'], (int) $variation['id']];
        }

        return [null, null];
    }

    public function getPriceListByZNZId(mixed $znzId): ?int
    {
        static $priceListCache = [];

        if (!array_key_exists($znzId, $priceListCache)) {
            $priceListCache[$znzId] = $this->getMapping(PriceListSynchronizer::getType(), $znzId);
        }

        return $priceListCache[$znzId];
    }

    public function getParameterValueByZNZId(int $id, ?int $parameterId): ?string
    {
        if ($parameterId) {
            $parameterValueId = $this->getParameterValueMapping($parameterId, $id);
        } else {
            $parameterValueId = $this->getMapping(ParameterSynchronizer::$typeValues, $id);
        }

        // kouknu do parametru, ze tam ta hodnota je, abych ji mel odkud vzit
        if (!$parameterValueId) {
            return null;
        }

        // nactu si hodnotu parametru
        return (string) sqlQueryBuilder()
            ->select('value')
            ->from('parameters_list')
            ->where(Operator::equals(['id' => $parameterValueId]))
            ->execute()->fetchOne();
    }

    /**
     * Aktualizuje hodnoty jmenovky varianty podle parametru. Protoze pro tvoreni hodnoty pouzivame parametrovy
     * ciselnik, takze je potreba pri aktualizaci parametru provadet i aktualizaci hodnoty jmenovky.
     */
    public function updateLabelValueByParameterValue(int $labelValueId, int $parameterValueId, ?string $language = null): void
    {
        $value = sqlQueryBuilder()
            ->select('value')
            ->from('parameters_list')
            ->where(Operator::equals(['id' => $parameterValueId]))
            ->execute()->fetchOne();

        if (!$value) {
            return;
        }

        // aktualizuju hodnoty jmenovky varianty podle hodnoty z parametru
        sqlQueryBuilder()
            ->update('products_variations_choices_values')
            ->directValues(
                [
                    'value' => $value,
                ]
            )
            ->where(Operator::equals(['id' => $labelValueId]))
            ->execute();

        // pokud updatuju pouze vychozi jazyk, tak preklady nemusim resit
        if ($language === Contexts::get(LanguageContext::class)->getDefaultId()) {
            return;
        }

        $qb = sqlQueryBuilder()
            ->select('id_language, value')
            ->from('parameters_list_translations')
            ->where(Operator::equals(['id_parameters_list' => $parameterValueId]));

        if ($language) {
            $qb->andWhere(Operator::equals(['id_language' => $language]));
        }

        // zaktualizuju preklady jmenovky varianty podle hodnoty parametru
        foreach ($qb->execute() as $item) {
            $this->variationsValuesTranslation->saveSingleObject($item['id_language'], $labelValueId, [
                'value' => $item['value'],
            ]);
        }
    }

    public function getLabelByKey(string $key): int
    {
        static $labels = [];

        if ($labels[$key] ?? false) {
            return $labels[$key];
        }

        if (!($labelId = $this->getMapping(MappingType::MAPPING_LABEL, $key))) {
            $labelId = $this->getLabelId($key);

            $this->createMapping(MappingType::MAPPING_LABEL, $key, $labelId);
        }

        return $labels[$key] = $labelId;
    }

    public function getLabelId(string $label): int
    {
        $labelId = sqlQueryBuilder()
            ->select('id')
            ->from('products_variations_choices_labels')
            ->where(Operator::equals(['label' => trim($label)]))
            ->execute()->fetchOne();

        if (!$labelId) {
            $labelId = sqlGetConnection()->transactional(function () use ($label) {
                sqlQueryBuilder()
                    ->insert('products_variations_choices_labels')
                    ->directValues(['label' => $label])
                    ->execute();

                return (int) sqlInsertId();
            });
        }

        return (int) $labelId;
    }

    public function getParameterByKey(string $key, string $type = 'list', bool $createIfMissing = true): ?int
    {
        static $parameters;

        if ($parameters === null) {
            $parameters = Mapping::mapKeys(sqlQueryBuilder()
                ->select('id_znz, id_parameter')
                ->from('znz_parameters')
                ->execute()->fetchAllAssociative(), fn ($k, $v) => [$v['id_znz'], $v['id_parameter']]);
        }

        // pokud poslu $createIfMissing=false, tak vracim null kdyz parametr neexistuje
        if (!($parameters[$key] ?? false) && $createIfMissing === false) {
            return null;
        }

        if (!($parameters[$key] ?? false)) {
            // pokud neexistuje, tak ho vytvorim
            $parameterId = (int) $this->parameterFinder->findParameter($key, $type);
            // ulozim si mapping
            $this->createMapping(ParameterSynchronizer::getType(), $key, $parameterId);

            $parameters[$key] = $parameterId;
        }

        return $parameters[$key];
    }

    public function recalculateStores(?array $productIds = null, bool $withVariationsInStoreRecalculate = true): void
    {
        $getQuantitySubQuery = function (bool $variations = false) {
            $alias = 'p';
            $productIdColumn = 'id';
            if ($variations) {
                $alias = 'pv';
                $productIdColumn = 'id_product';
            }

            $qb = sqlQueryBuilder()
                ->select('COALESCE(SUM(GREATEST(si.quantity, 0)), 0)')
                ->from('stores_items', 'si')
                ->join('si', 'stores', 's', 's.id = si.id_store')
                ->andWhere('si.id_product = '.$alias.'.'.$productIdColumn)
                ->andWhere(Operator::equals(['s.figure' => 'Y']));

            if ($variations) {
                $qb->andWhere('si.id_variation = pv.id');
            } else {
                $qb->andWhere('si.id_variation IS NULL');
            }

            return $qb;
        };

        /**
         * Explanation for the "wierd" logic bellow:
         * Amount of locked rows is highly reduced when update is executed only on specific ids.
         * So when $productIds parameter is empty, we still try to find the ids to update before running the actual update query
         */

        // ----------- Products recalc query (optimized for reducing db locks -> reducing deadlocks)
        $productsSubQuery = $getQuantitySubQuery();

        $productsRecalcBaseQb = sqlQueryBuilder()
            ->leftJoin('p', 'products_variations', 'pv', 'pv.id_product = p.id')
            ->andWhere('p.in_store != ('.$productsSubQuery->getSQL().')')
            // recalc products chci spoustet pouze pro nevariatni produkty, protoze by to tady nastavilo akorat nesmysl, ktery to nize stejnak prerazi
            ->andWhere('pv.id IS NULL')
            ->addQueryBuilderParameters($productsSubQuery);

        $productUpdateIds = $productIds;

        // When all products should be recalculated, update only products with incorrect in_store
        if ($productUpdateIds === null) {
            $productUpdateIds = (clone $productsRecalcBaseQb)->select('p.id')->from('products', 'p')->groupBy('p.id')->execute()->fetchFirstColumn();
            $productUpdateIds = count($productUpdateIds) > 1000 ? null : $productUpdateIds;
        }

        $productsUpdateQb = $productsRecalcBaseQb
            ->update('products', 'p')
            ->set('p.in_store', '('.$productsSubQuery->getSQL().')');

        if ($productUpdateIds !== null) {
            $productsUpdateQb->andWhere(Operator::inIntArray($productUpdateIds, 'p.id'));
        }

        // ---------- Variations recalc query (optimized for reducing db locks -> reducing deadlocks)
        $variationsSubQuery = $getQuantitySubQuery(true);

        $variationsRecalcBaseQb = sqlQueryBuilder()
            ->andWhere('pv.in_store != ('.$variationsSubQuery->getSQL().')')
            ->addQueryBuilderParameters($variationsSubQuery);

        $variationsUpdateIds = $productIds;

        // When all variations should be recalculated, update only variations with incorrect in_store
        if ($variationsUpdateIds === null) {
            $variationsUpdateIds = (clone $variationsRecalcBaseQb)->select('pv.id_product')
                ->from('products_variations', 'pv')->groupBy('pv.id_product')->execute()->fetchFirstColumn();
            $variationsUpdateIds = count($variationsUpdateIds) > 1000 ? null : $variationsUpdateIds;
        }

        $variationsUpdateQb = $variationsRecalcBaseQb
            ->update('products_variations', 'pv')
            ->set('pv.in_store', '('.$variationsSubQuery->getSQL().')');

        if ($variationsUpdateIds !== null) {
            $variationsUpdateQb->andWhere(Operator::inIntArray($variationsUpdateIds, 'pv.id_product'));
        }

        sqlGetConnection()->transactional(function () use ($productsUpdateQb, $variationsUpdateQb, $withVariationsInStoreRecalculate) {
            // recalculate products
            $productsUpdateQb->execute();

            // recalculate variations
            $variationsUpdateQb->execute();

            // prenest sklad variant na produkt - pokud je zapnuto
            if ($withVariationsInStoreRecalculate) {
                \Variations::recalcInStore();
            }
        });
    }

    public function getZNZMaxId(int $znzId, string $table, string $key): ?int
    {
        $cacheKey = implode('-', [$znzId, $table, $key]);

        return $this->znzMaxIdsCache[$cacheKey] ?? null;

        // zkusme to bez toho, teoreticky staci to mit v pameti, porotoze primarne jde o zmeny, ktere chodi blizko sebe
        //        if (isset($this->znzMaxIdsCache[$cacheKey])) {
        //            return $this->znzMaxIdsCache[$cacheKey];
        //        }
        //
        //        $maxId = sqlQueryBuilder()
        //            ->select('id_max')
        //            ->from('znz_max_ids')
        //            ->where(
        //                Operator::equals(
        //                    [
        //                        'id_znz' => $znzId,
        //                        'znz_table' => $table,
        //                        'znz_key' => $key,
        //                    ]
        //                )
        //            )
        //            ->sendToMaster()
        //            ->execute()->fetchOne();
        //
        //        return $this->znzMaxIdsCache[$cacheKey] = $maxId ?: null;
    }

    public function updateZNZMaxId(int $znzId, string $table, string $key, int $maxId): void
    {
        $cacheKey = implode('-', [$znzId, $table, $key]);

        // update cache to make sync faster
        $this->znzMaxIdsCache[$cacheKey] = $maxId;

        // zkusme to bez toho, teoreticky staci to mit v pameti, porotoze primarne jde o zmeny, ktere chodi blizko sebe
        //        sqlQueryBuilder()
        //            ->insert('znz_max_ids')
        //            ->directValues(
        //                [
        //                    'id_znz' => $znzId,
        //                    'znz_table' => $table,
        //                    'znz_key' => $key,
        //                    'id_max' => $maxId,
        //                ]
        //            )
        //            ->onDuplicateKeyUpdate(['id_max'])
        //            ->execute();
    }

    public function sortColorLabelValues(): void
    {
        $colorLabelId = $this->getLabelByKey('Barva');

        $qb = sqlQueryBuilder()
            ->select('pvcv.id, pv.data')
            ->from('products_variations_choices_values', 'pvcv')
            ->join('pvcv', 'products_variations_combination', 'pvc', 'pvc.id_label = pvcv.id_label AND pvc.id_value = pvcv.id')
            ->join('pvc', 'products_variations', 'pv', 'pv.id = pvc.id_variation')
            ->where(Operator::equals(['pvcv.id_label' => $colorLabelId]))
            ->groupBy('pvcv.id');

        $colors = [];
        foreach ($qb->execute() as $item) {
            $data = json_decode($item['data'] ?: '', true) ?: [];
            $color = $data['color'][0] ?? null;
            if (!$color) {
                $colors[] = [
                    'id' => $item['id'],
                    'rgb' => [255, 255, 255],
                    'position' => 99999,
                ];
                continue;
            }

            $rgb = sscanf("#{$color}", '#%02x%02x%02x');

            $colors[] = [
                'id' => $item['id'],
                'rgb' => $rgb,
            ];
        }

        usort($colors, function ($a, $b) {
            $hsv1 = $this->rgbToHsv($a['rgb']);
            $hsv2 = $this->rgbToHsv($b['rgb']);

            return ($hsv1['hue'] + $hsv1['saturation'] + $hsv1['value']) <=> ($hsv2['hue'] + $hsv2['saturation'] + $hsv2['value']);
        });

        foreach ($colors as $position => $item) {
            if (!empty($item['position'])) {
                $position = $item['position'];
            }

            sqlQueryBuilder()
                ->update('products_variations_choices_values')
                ->directValues(['sort' => $position])
                ->where(Operator::equals(['id' => $item['id']]))
                ->execute();
        }
    }

    public function sortParameterAlphabet(int $parameterId): void
    {
        $parameters = sqlQueryBuilder()->select('id, value')
            ->from('parameters_list', 'pl')
            ->where(Operator::equals(['id_parameter' => $parameterId]))
            ->execute()->fetchAllAssociative();

        $collator = new \Collator(Contexts::get(LanguageContext::class)->getActive()->getLocale());
        $collator->setAttribute(\Collator::NUMERIC_COLLATION, \Collator::ON);

        usort($parameters, function ($a, $b) use ($collator) {
            return $collator->compare($a['value'], $b['value']);
        });

        $i = 0;
        foreach ($parameters as $parameter) {
            sqlQueryBuilder()->update('parameters_list', 'pl')
                ->directValues(['position' => ++$i])
                ->where(Operator::equals(['id' => $parameter['id']]))
                ->execute();
        }
    }

    public function getSeriesTemplateCategoryId(): int
    {
        static $seriesTemplateCategoryId;

        if (!$seriesTemplateCategoryId) {
            $tmpCategoryId = sqlQueryBuilder()
                ->select('id')
                ->from('templates_categories')
                ->where(Operator::equals(['name' => 'Značka řada']))
                ->execute()->fetchOne();

            if (!$tmpCategoryId) {
                $tmpCategoryId = sqlGetConnection()->transactional(function () {
                    sqlQueryBuilder()
                        ->insert('templates_categories')
                        ->directValues(['name' => 'Značka řada'])
                        ->execute();

                    return (int) sqlInsertId();
                });
            }

            $seriesTemplateCategoryId = $tmpCategoryId;
        }

        return $seriesTemplateCategoryId;
    }

    /** Vrati ID spolecne sablony, nebo vytvori spolecnou sablonu pro radu a vyrobce produktu */
    public function getSeriesTemplateId(int $znzOptionId): int
    {
        static $templates;

        if ($templates === null) {
            $templates = Mapping::mapKeys(sqlQueryBuilder()
                ->select('id, JSON_VALUE(data, "$.znzOptionId") as id_znz')
                ->from('templates')
                ->where('JSON_VALUE(data, "$.znzOptionId") IS NOT NULL')
                ->execute()->fetchAllAssociative(), fn ($k, $v) => [$v['id_znz'], $v['id']]);
        }

        if (!($templates[$znzOptionId] ?? null)) {
            $parameterId = $this->getParameterByKey('ZnackaRadaOption');

            $templateName = $znzOptionId;
            if ($valueId = $this->getParameterValueMapping($parameterId, $znzOptionId)) {
                $parameterValue = sqlQueryBuilder()
                    ->select('value')
                    ->from('parameters_list')
                    ->where(Operator::equals(['id' => $valueId]))
                    ->execute()->fetchOne();

                if ($parameterValue) {
                    // add znzOptionId to avoid duplicates
                    $templateName = "{$parameterValue} ({$znzOptionId})";
                }
            }

            $templates[$znzOptionId] = sqlGetConnection()->transactional(function () use ($znzOptionId, $templateName) {
                sqlQueryBuilder()
                    ->insert('templates')
                    ->directValues(
                        [
                            'id_category' => $this->getSeriesTemplateCategoryId(),
                            'name' => $templateName,
                            'data' => json_encode(['znzOptionId' => $znzOptionId]),
                        ]
                    )->execute();

                return (int) sqlInsertId();
            });
        }

        return $templates[$znzOptionId];
    }

    public function getDefaultStore(?string $language = null): ?array
    {
        $stores = $this->getEnabledStores();
        $language = $language ?: Contexts::get(LanguageContext::class)->getActiveId();

        $filtered = [];
        foreach ($stores as $store) {
            if (empty($store['data']['languages']) || in_array($language, $store['data']['languages'])) {
                $filtered[] = $store['id'];
            }
        }

        if ($storeId = reset($filtered)) {
            return $stores[$storeId];
        }

        return null;
    }

    public function getStoreByDeliveryId(int $deliveryId): ?array
    {
        foreach ($this->getEnabledStores() as $store) {
            if ($deliveryId == $store['id_delivery']) {
                return $store;
            }
        }

        return null;
    }

    public function getEnabledStores(): array
    {
        return array_filter($this->storesInStore->getStores(), fn ($x) => $x['figure'] === 'Y');
    }

    public function cleanMovedProducts(): void
    {
        sqlQueryBuilder()
            ->delete('products')
            ->where(Operator::like(['code' => 'MOVED_%']))
            ->execute();

        sqlQueryBuilder()
            ->delete('products_variations')
            ->where(Operator::like(['code' => 'MOVED_%']))
            ->execute();
    }

    /**
     * Tuhle fci pouzivam na mistech, kde potrebuji resit variantu podle skladu.
     *
     * Varianta per sklad je pouze na B2B a je to kvuli ruznym cenam na ruznych skladech, pripadne i moznosti vyberu
     * z jakeho skladu chce B2B zbozi objednat.
     */
    public function getProductVariationIdWithStoreCheck(int $productId, ?int $variationId, ?string $znzStoreId): ?int
    {
        // pokud nejsem v B2B modu, tak vracim rovnou ID varianty, ktere jsem dostal
        if (!$this->configuration->isB2BMode()) {
            return $variationId;
        }

        if (!$znzStoreId) {
            return null;
        }

        // pokud jsem v B2B modu, tak vracim variantu per sklad
        return $this->withRuntimeCache(
            fn () => $this->getProductVariationByStore($productId, $znzStoreId),
            ['B2BVariations', $productId, $variationId]
        );
    }

    public function isMessageWithStoreRequiredValid(array $item): bool
    {
        if (!$this->configuration->isB2BMode()) {
            return true;
        }

        return !empty($item['IdSklad']);
    }

    /**
     * Najde / vytvori variantu u produktu podle ID skladu (id skladu ze ZNZ).
     */
    public function getProductVariationByStore(int $productId, string $znzStoreId): int
    {
        $znzId = "{$productId}-{$znzStoreId}";

        $variationId = sqlQueryBuilder()
            ->select('id_variation')
            ->from('znz_b2b_variations')
            ->where(Operator::equals(['id_znz' => $znzId]))
            ->sendToMaster()
            ->execute()->fetchOne();

        // variation does not exists, create one
        if (!$variationId) {
            $variationId = sqlGetConnection()->transactional(function () use ($znzId, $productId, $znzStoreId) {
                $variationId = (int) \Variations::createProductVariation($productId, [
                    $this->getLabelId('Sklad') => $znzStoreId,
                ]);

                // get product code
                $productCode = sqlQueryBuilder()
                    ->select('code')
                    ->from('products')
                    ->where(Operator::equals(['id' => $productId]))
                    ->execute()->fetchOne() ?: $znzId;

                // set variation code to productCode_znzStoreId
                sqlQueryBuilder()
                    ->update('products_variations')
                    ->directValues(['code' => "{$productCode}_{$znzStoreId}"])
                    ->where(Operator::equals(['id' => $variationId]))
                    ->execute();

                // create variation mapping
                sqlQueryBuilder()
                    ->insert('znz_b2b_variations')
                    ->directValues(
                        [
                            'id_znz' => $znzId,
                            'id_znz_store' => $znzStoreId,
                            'id_product' => $productId,
                            'id_variation' => $variationId,
                        ]
                    )->execute();

                return $variationId;
            });
        }

        return $variationId;
    }

    public function withRuntimeCache(callable $getter, array $cacheKey): mixed
    {
        static $runtimeCache = [];

        $key = implode('-', array_filter($cacheKey));

        if ($runtimeCache[$key] ?? null) {
            return $runtimeCache[$key];
        }

        return $runtimeCache[$key] = $getter();
    }

    public function getCountryCodeForHelios(string $country): string
    {
        return $this->getCountryMap()[$country] ?? $country;
    }

    public function getCountryCodeByHelios(string $country): string
    {
        return array_flip($this->getCountryMap())[$country] ?? $country;
    }

    public function getDownloader(): \Downloader
    {
        static $downloader;

        if (!$downloader) {
            $downloader = new \Downloader();
            $downloader->setMethod('curl');
        }

        return $downloader;
    }

    /**
     * Returns specific country aliases for Helios.
     */
    private function getCountryMap(): array
    {
        return [
            'GR' => 'EL',
        ];
    }

    private function rgbToHsv(array $rgb): array
    {
        $red = $rgb[0] / 255;
        $green = $rgb[1] / 255;
        $blue = $rgb[2] / 255;

        $min = min($red, $green, $blue);
        $max = max($red, $green, $blue);

        switch ($max) {
            case 0:
                // If the max value is 0.
                $hue = 0;
                $saturation = 0;
                $value = 0;
                break;
            case $min:
                // If the maximum and minimum values are the same.
                $hue = 0;
                $saturation = 0;
                $value = round($max, 4);
                break;
            default:
                $delta = $max - $min;
                if ($red == $max) {
                    $hue = 0 + ($green - $blue) / $delta;
                } elseif ($green == $max) {
                    $hue = 2 + ($blue - $red) / $delta;
                } else {
                    $hue = 4 + ($red - $green) / $delta;
                }
                $hue *= 60;
                if ($hue < 0) {
                    $hue += 360;
                }
                $saturation = $delta / $max;
                $value = round($max, 4);
        }

        return ['hue' => $hue, 'saturation' => $saturation, 'value' => $value];
    }
}