<?php

namespace External\VarioBundle\Synchronizers;

use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
use External\VarioBundle\Exception\VarioException;
use KupShop\CatalogBundle\Parameters\ParameterFinder;
use KupShop\CatalogBundle\Product\ProductFinder;
use Query\Operator;

class ProductSynchronizer extends BaseSynchronizer
{
    protected static $type = 'otProduct';
    protected static $batch = 100;
    protected static $maxBatches = 50;

    protected $fields = [
        'ProductName' => 'common',
        'Code' => 'item',
        'CategoriesID' => 'categories',
        'RelatedProductsID' => 'relatedProducts',
        'Parametres' => 'parameters',
        'Variants' => 'variations',
        'Prices' => 'prices',
    ];

    /** @var PriceSynchronizer */
    protected $priceSynchronizer;

    /** @var ParameterFinder */
    protected $parameterFinder;

    /** @var ProductFinder */
    protected $productFinder;

    protected $newProduct = false;

    /**
     * @required
     */
    public function setPriceSynchronizer(PriceSynchronizer $priceSynchronizer): void
    {
        $this->priceSynchronizer = $priceSynchronizer;
    }

    /**
     * @required
     */
    public function setParameterFinder(ParameterFinder $parameterFinder): void
    {
        $this->parameterFinder = $parameterFinder;
    }

    /**
     * @required
     */
    public function setProductFinder(ProductFinder $productFinder): void
    {
        $this->productFinder = $productFinder;
    }

    public function sync()
    {
        parent::sync();
        if (!empty($this->getFieldsToUpdateProduct())) {
            $this->updateProducts();
        }
    }

    protected function updateProducts()
    {
        if (isDevelopment()) {
            return true;
        }
        $products = sqlQueryBuilder()->select('p.id, pv.id as id_variation, COALESCE(vv.id_vario,vp.id_vario) as id_vario')
            ->from('products', 'p')
            ->leftJoin('p', 'vario_products', 'vp', 'vp.id_product=p.id')
            ->leftJoin('p', 'products_variations', 'pv', 'pv.id_product=p.id')
            ->innerJoin('pv', 'vario_variations', 'vv', 'vv.id_variation=pv.id')
            ->andWhere(Operator::isNotNull('p.updated'))
            ->andWhere('p.updated >= NOW() - INTERVAL 1 DAY')
            ->andWhere('(JSON_VALUE(p.data, "$.lastSyncToVario") IS NULL) OR ((JSON_VALUE(p.data, "$.lastSyncToVario") < p.updated))');

        foreach ($products->execute() as $product) {
            $productObject = \Variation::createProductOrVariation($product['id'], $product['id_variation']);
            $productObject->createFromDB($product['id']);
            if ($this->updateProduct($product['id_vario'], $productObject)) {
                sqlQueryBuilder()
                    ->update('products')
                    ->set('data', 'JSON_SET(COALESCE(data, \'{}\'), \'$.lastSyncToVario\', \''.date('Y-m-d H:i:s').'\')')
                    ->where(Operator::equals(['id' => $product['id']]))
                    ->execute();
            }
        }
    }

    /**
     * Example for update flags New, Action and field WWW
     * return ['New', 'Action', 'WWW'];.
     */
    protected function getFieldsToUpdateProduct(): array
    {
        return [];
    }

    protected function updateProduct(string $varioId, \Product $product): bool
    {
        if (isDevelopment() || !($fields = $this->getFieldsToUpdateProduct())) {
            return false;
        }
        $documentObject = $this->createEmptyDocumentObject($varioId, $fields);
        $documentObject = $this->joinProductFieldsToDocument($varioId, $fields, $product, $documentObject);

        $data = [
            'data' => json_encode($documentObject),
            'fields' => $fields,
        ];

        if (!empty($documentObject->UserFields)) {
            $data['user_fields'] = $documentObject->UserFields;
        }

        $this->logger->notice('[Vario] Aktualizovaní produktu do Varia', $data);

        try {
            $this->client->updateProduct($documentObject, $fields);
        } catch (\Throwable $e) {
            $this->helper->logException($e, $e->getMessage(), $data);

            return false;
        }

        return true;
    }

    protected function joinProductFieldsToDocument(string $varioId, array $fields, \Product $product, \stdClass $documentObject): \stdClass
    {
        foreach ($fields as $field) {
            $functionName = 'joinField'.ucfirst($field);
            if (method_exists($this, $functionName)) {
                $documentObject->{$field} = $this->{$functionName}($varioId, $product);
            } else {
                throw new VarioException(sprintf('[Vario] Metoda nebo atribut %s neexistuje', $functionName));
            }
        }

        return $documentObject;
    }

    protected function joinFieldNew(string $varioId, \Product $product)
    {
        return isset($product->campaign_codes['N']);
    }

    protected function joinFieldSellOff(string $varioId, \Product $product)
    {
        return isset($product->campaign_codes['A']);
    }

    protected function joinFieldTIP(string $varioId, \Product $product)
    {
        return isset($product->campaign_codes['D']);
    }

    protected function joinFieldWWW(string $varioId, \Product $product)
    {
        return createScriptURL([
            's' => 'product',
            'IDproduct' => $product->id,
            'TITLE' => $product->title,
            'absolute' => true,
        ]);
    }

    protected function joinFieldAction(string $varioId, \Product $product)
    {
        return isset($product->campaign_codes['D']);
    }

    protected function joinFieldTopProduct(string $varioId, \Product $product)
    {
        return isset($product->campaign_codes['R']);
    }

    public function createEmptyDocumentObject(string $varioId, array $fields): \stdClass
    {
        $document = new \stdClass();
        $document->Id = '{'.$varioId.'}';
        $document->Actualize = $fields;

        $document->Book = ''; // string(30) – (Katalog.Kniha) zařazení do knihy
        $document->CategoriesID = ''; // TStringArray – (Kategorie.rowguid) pole ID kategorií stromu, kam je produkt zařazen
        $document->ManufacturerID = ''; // string – (Firmy.rowguid) ID výrobce
        $document->Manufacturer = ''; // string(30) – (Katalog.Vyrobce) název výrobce
        $document->Supplier = ''; // string(30) – (Katalog.Dodavatel) název dodavatele
        $document->ParameterGroupID = ''; // string – (T_Struktury.rowguid) ID skupiny parametrů
        $document->Parametres = ''; // TParameterValues – hodnoty parametrů
        $document->ProductSet = ''; // TProductSet – materiál produktu
        $document->AlternativesID = ''; // TStringArray – (Katalog.rowguid) pole ID alternativních produktů
        $document->IncompatiblesID = ''; // TStringArray – (Katalog.rowguid) pole ID nekompatibilních produktů
        $document->RelatedProductsID = ''; // TStringArray – (Katalog.rowguid) pole ID souvisejících produktů
        $document->AccessoriesID = ''; // TStringArray – (Katalog.rowguid) pole ID produktů příslušenství
        $document->DesignProductsID = ''; // TStringArray – (Katalog.rowguid) pole ID prokuktů provedení
        $document->FilesID = ''; // TStringArray – prázdné
        $document->Code = ''; // string(30) – (Katalog.Produkt) kód produktu (produkt)
        $document->ProductName = ''; // string(255) – (Katalog.Popis) popis produktu
        $document->ProductNumber = ''; // string(30) – (Katalog.Katalogove_cislo) katalogové číslo
        $document->Units = ''; // string(30) – (Katalog.Jednotky) jednotky
        $document->Description = ''; // string(8000) – (Katalog.Text) dlouhý text
        $document->Note = ''; // string(255) – (Katalog.Poznamka) poznámka
        $document->Classification = ''; // string(255) – (Katalog.Zarazeni) zařazení produktu
        $document->EAN = ''; // string(30) – (Katalog.Carovy_kod) čárový kód
        $document->EANInternal = ''; // string(30) – (Katalog.Carovy_kod_vlastni) čárový kód vlastní
        $document->EANs = ''; // TEANs – pole čárových kódů produktu
        $document->GPL = ''; // Currency – prázdné
        $document->ProductType = 'ZB'; // string(2) – (Katalog.Typ_zaznamu) typ produktu (MA – materiál, ZB – zboží, SL – služba, BU – sada, SE – sestava, VY – výrobek), při zápisu nutné zadat
        $document->ProductTypeName = ''; // string(30) – (Katalog.Typ_produktu) název typu produktu
        $document->VAT = ''; // string(30) – (Katalog.Sazba_DPH) sazba DPH
        $document->Price = ''; // Currency – (Katalog.Cena) cena zadaná přímo u produktu (ne z ceníku)
        $document->PriceWithVAT = false; // boolean – (nastavení Ceny katalogu včetně DPH) je cena Price s daní?
        $document->PricePurchase = ''; // Currency – (Katalog.Cena_nakupni) nákupní cena produktu
        $document->WWW = ''; // string(255) – (Katalog.Internet) URL adresa produktu
        $document->Dimensions = ''; // string(100) – (Katalog.Rozmery) rozměry
        $document->Variants = ''; // TVariants – pole variant produktu
        $document->Warranty = 0; // integer – (Katalog.Zaruka) záruka
        $document->QualityStandard = ''; // string(30) – (Katalog.Jakostni_norma) jakostní norma
        $document->Weight = 0.0; // Double – (Katalog.Hmotnost) hmotnost
        $document->Data1 = ''; // string(100) – (Katalog.Udaj_1) údaj 1
        $document->Data2 = ''; // string(100) – (Katalog.Uda_2) údaj 2
        $document->Number1 = 0.0; // double – (Katalog.Pocet_1) číslo 1
        $document->Number2 = 0.0; // double – (Katalog.Pocet_2) číslo 2
        $document->Action = false; // Boolean – (T_Katalog.B2CAkce) akce
        $document->TIP = false; // boolean – (T_Katalog.B2CHitTydne) tip
        $document->New = false; // boolean – (T_Katalog.B2CNovinka, nebo Katalog.Novinka) novinka
        $document->Sale = false; // boolean – (Katalog.Nabizet_v_dokladech) nabízet
        $document->SellOff = false; // boolean – (Katalog.Vyprodej) výprodej
        $document->TitlePage = false; // boolean – (T_Katalog.B2CTitulka) titulní strana
        $document->InvisibleComponent = false; // boolean – prázdné
        $document->Visible = false; // boolean – (T_Katalog.B2C, nebo Katalog.Publikovat) viditelný
        $document->HideOnWeb = false; // boolean – (T_Katalog.SkrytyNaInt) skrýt na webu
        $document->B2B = false; // boolean – (T_Katalog.B2B) viditelný pro B2
        $document->B2CPriority = false; // boolean – (T_Katalog.B2B) viditelný pro B2
        $document->Prices = ''; // boolean – (T_Katalog.B2B) viditelný pro B2
        $document->Image = ''; // TByteDynArray – (Katalog.Obrazek) obrázek
        $document->SmallImage = ''; // TByteDynArray – (Katalog.Obrazek_maly) malý obrázek
        $document->ElectroFee = ''; // string – (Elektrozarizeni.rowguid) ID elektropoplatku
        $document->AuthorshipFee = ''; // string – (T_Autorske_poplatky.rowguid) ID autorského poplatku
        $document->AlwaysAvailable = false; // boolean – (T_Katalog.StaleSkladem) vždy dostupný
        $document->Availbaility = ''; // string(50) – (T_Katalog.B2CDodaciLhuta, nebo Katalog.Doba_dodani) dodací lhůta
        $document->BasicUnit = ''; // string(30) – (Katalog.Z akladni_jednotka) základní jednotka
        $document->BasicUnitQuantity = 0; // integer – (Katalog.Mnozstvi_zakladnich_jednotek) množství základních jednotek
        $document->Discounts = ''; // TDiscounts – pole slev
        $document->Height = 0.0; // double – (Katalog.Vyska) výška
        $document->Width = 0.0; // double – (Katalog.Sirka) šířka
        $document->Length = 0.0; // double – (Katalog.Hloubka) délka
        $document->AlternativeDescription = ''; // string(255) – (T_Katalog.Popis_2) alternativní popis
        $document->AllowOrderByCustomer = false; // boolean – (T_Katalog.PovolitObjOdberatelem) má odběratel povoleny objednávky
        $document->AlternativeNote = ''; // string(8000) – (T_Katalog.PoznamkaVelka) alternativní poznámka
        $document->ImageRef = ''; // string(255) – (Katalog.Obrazek) odkaz na obrázek
        $document->SmallImageRef = ''; // string(255) – (Katalog.Obrazek_makly) odkaz na malý obrázek
        $document->B2BAction = false; // boolean – (T_Katalog.B2BAkce) B2B akce
        $document->ActionDescription = ''; // string(100) – (T_Katalog.Akce) popis akce
        $document->MarketingAction = ''; // string(100) – (T_Katalog.MarketingovaAkce) popis marketingové akce
        $document->B2BTIP = false; // boolean – (T_Katalog.B2BHitTydne) B2B tip
        $document->B2BNew = false; // boolean – (T_Katalog.Novinka) B2B novinka
        $document->B2BTitlePage = false; // boolean – B2B titulní stránka
        $document->B2BPriority = false; // boolean – (T_Katalog.B2BUprednostnit) B2B prioritní
        $document->B2BAvailbaility = ''; // string(50) – (T_Katalog.B2BDodaciLhuta) B2B dostupnost
        $document->TopProduct = false; // boolean – (T_Katalog.TopProdukt, nebo Katalog.Top_produkt) top produkt
        $document->B2BMinimumOrderQuantity = 0.0; // double – (Katalog.u_Mnozstvi_minimalni_shop) B2B minimální objednací množství
        $document->InPriceList = false; // boolean – (T_Katalog.VCeniku) označení prodkutku „v ceníku“
        $document->DefaultStoreID = ''; // string – (Knihy.rowguid) ID výchozího skladu
        $document->AccountCodeSell = ''; // string(30) – (Katalog.Predkontace_prodej) předkontace produktu pro prodej
        $document->Annotation = ''; // string(255) – (Katalog.Anotace) anotace produktu
        $document->RecyclingCosts = ''; // currency – (Katalog.Naklady_na_recyklaci) náklady na recyklaci elektrozařízení na ks nebo kg
        $document->CustomsTariff = ''; // string(30) – (Katalog.Celni_sazebnik) zařazení do celního sazebníku
        $document->CustomsNumberOfUnits = 0.0; // double – (Katalog.Mnozstvi_jednotek_clo) Množství jednotek pro clo
        $document->QuantityMinimum = 0.0; // double – (Katalog.Mnozstvi_minimalni) Množství minimální pro objednávání
        $document->QuantityMaximum = 0.0; // double – (Katalog.Mnozstvi_maximalni) Množství maximální pro objednávání
        $document->PurchasingStrategy = ''; // string(30) – (Katalog.Vyrobni_strategie) Nákupní strategie
        $document->UserFields = ''; // string – hodnoty uživatelských polí v XML formátu, např. <pole1>hodnota</pole1><pole2>hodnota</pole2>
        $document->DiscountCategory = ''; // string – hodnoty uživatelských polí v XML formátu, např. <pole1>hodnota</pole1><pole2>hodnota</pole2>

        return $document;
    }

    public function getEshopID($objectID)
    {
        $data = $objectID;
        $objectID = $data->Id;

        if (!$this->isItemAllowed($data)) {
            return null;
        }

        $this->newProduct = false;

        $id = $this->helper->getMapping('vario_products', $objectID);
        if (!$id) {
            if ($id = $this->selectSQL('products', ['code' => $data->Code], ['id'])->fetchColumn()) {
                $this->helper->createMapping('vario_products', $objectID, $id);

                return $id;
            }

            $insertData = [
                'title' => $data->Code,
                'code' => $data->Code,
                'figure' => 'N',
                'vat' => 1,
                'date_added' => date('Y-m-d H:i:s'),
            ];

            $id = sqlGetConnection()->transactional(function () use ($insertData) {
                $this->insertSQL('products', $insertData);

                return (int) sqlInsertId();
            });

            $this->helper->createMapping('vario_products', $objectID, $id);

            $this->newProduct = $objectID;

            $this->forceSyncSupply($objectID);
        }

        $this->updateSQL('products', ['updated' => date('Y-m-d H:i:s')], ['id' => $id]);

        $this->addChangedItem(['id_product' => $id, 'id_variation' => null]);

        return $id;
    }

    public function forceSyncSupply(string $objectId)
    {
        // force stock update for product if product created to avoid missing supplies
        try {
            if (getVal('force_sync') != 'ALL') {
                $this->helper->doForceSync(
                    $objectId,
                    AvailableSupplySynchronizer::getType()
                );
            }
        } catch (\Exception $e) {
            getRaven()->captureException($e);
        }
    }

    public function syncDelete($eshopID)
    {
        $this->syncCommon('O', $eshopID, 'figure');

        sqlQueryBuilder()
            ->delete('vario_products')
            ->where('id_product = :id AND id_variation IS NULL')
            ->setParameter('id', $eshopID)
            ->execute();
    }

    public function syncItem($value, $productID, $fieldName, object $item): void
    {
        $data = $item->Data;

        try {
            // Aktualizovat common polozky
            sqlQueryBuilder()
                ->update('products')
                ->directValues($this->getProductData($data))
                ->where(Operator::equals(['id' => $productID]))
                ->execute();
            $this->updateProductParameter(
                $this->parameterFinder->findParameter('Katalogové číslo'),
                (int) $productID,
                [$data->ProductNumber],
                false
            );
        } catch (\Throwable $e) {
            $this->helper->logException($e, $e->getMessage(), $this->getProductData($data));
        }
    }

    protected function getProductData(object $item): array
    {
        $code = $item->Code;
        $ean = empty($item->EAN) ? null : trim($item->EAN);
        $warranty = $item->Warranty;
        $weight = empty($item->Weight) ? null : $item->Weight;
        $figure = $item->Visible ? 'Y' : 'N';
        $description = $this->helper->getHTMLPurifier()->purify($item->Description);
        $annotation = $item->Annotation;
        if (empty($annotation) && !empty($description)) {
            $annotation = str_replace(PHP_EOL,
                ' ',
                trim(trim(mb_substr(html_entity_decode(strip_tags($description)), 0, 160)), '.,')).'...';
        }

        return [
            'code' => $code,
            'ean' => $ean,
            'guarantee' => $warranty,
            'weight' => $weight,
            'vat' => $this->productFinder->findVAT($this->getVatByName($item->VAT)),

            'producer' => $this->getProducer($item->Manufacturer),

            'short_descr' => $annotation,
            'long_descr' => $description,

            'figure' => $figure,

            'width' => $item->Width ?: null,
            'height' => $item->Height ?: null,
            'depth' => $item->Length ?: null,
        ];
    }

    public function syncParameters(array $parameters, int $productId): void
    {
        $params = $this->client->getParameters();

        foreach ($parameters as $parameter) {
            if (!($param = $params[$parameter->ParameterID] ?? false)) {
                continue;
            }

            $paramName = $param->ParameterName;
            $value = $parameter->Value;

            $parameterId = (int) $this->parameterFinder->findParameter($paramName);
            if (!($parameterType = $this->getParameterType($parameterId))) {
                $parameterType = 'list';
            }

            $this->updateProductParameter(
                $parameterId,
                $productId,
                [$value],
                $parameterType === 'list'
            );
        }
    }

    public function syncCampaigns(\stdClass $item, int $productId): void
    {
        $flags = [];

        if ($item->Action) {
            $flags['D'] = true;
        }

        if ($item->SellOff) {
            $flags['A'] = true;
        }

        if ($item->TopProduct) {
            $flags['R'] = true;
        }

        $currentFlags = sqlQueryBuilder()
            ->select('campaign')
            ->from('products')
            ->where(Operator::equals(['id' => $productId]))
            ->execute()->fetchColumn();

        $currentFlags = explodeFlags($currentFlags);
        $checkFlags = ['D', 'R', 'A'];

        $newFlags = array_filter($currentFlags, function ($key) {
            return !empty($key);
        }, ARRAY_FILTER_USE_KEY);
        foreach ($checkFlags as $flag) {
            if ($flags[$flag] ?? false) {
                $newFlags[$flag] = 1;
            } else {
                unset($newFlags[$flag]);
            }
        }

        sqlQueryBuilder()
            ->update('products')
            ->directValues(['campaign' => implode(',', array_keys($newFlags))])
            ->where(Operator::equals(['id' => $productId]))
            ->execute();
    }

    public function syncLinks(array $links, int $productId): void
    {
        sqlGetConnection()->transactional(function () use ($links, $productId) {
            sqlQueryBuilder()
                ->delete('links')
                ->where(Operator::equals(['id_product' => $productId, 'type' => 'youtube']))
                ->execute();

            foreach ($links as $photo) {
                if (!empty($photo->FileName)) {
                    // insert link
                    sqlQueryBuilder()
                        ->insert('links')
                        ->directValues(
                            [
                                'id_product' => $productId,
                                'link' => $photo->FileName,
                                'type' => 'youtube',
                            ]
                        )
                        ->execute();
                }
            }
        });
    }

    public function syncRelatedProducts($value, $productID)
    {
        $this->deleteSQL('products_related', ['id_top_product' => $productID]);

        foreach ($value as $item) {
            if (!empty($item)) {
                if ($relatedId = $this->helper->getMapping('vario_products', $this->helper->trimVarioId($item))) {
                    try {
                        $this->insertSQL(
                            'products_related',
                            [
                                'id_top_product' => $productID,
                                'id_rel_product' => $relatedId,
                            ]
                        );
                    } catch (\Exception $e) {
                    }
                }
            }
        }
    }

    public function syncCategories($value, $productID): void
    {
        sqlQueryBuilder()
            ->delete('products_in_sections')
            ->where(Operator::equals(['id_product' => $productID]))
            ->execute();

        foreach ($value ?? [] as $id) {
            if (!($sectionId = $this->helper->getMapping('vario_sections', $id))) {
                continue;
            }

            try {
                sqlQueryBuilder()
                    ->insert('products_in_sections')
                    ->directValues(
                        [
                            'id_product' => $productID,
                            'id_section' => $sectionId,
                        ]
                    )
                    ->execute();
            } catch (\Exception $e) {
            }
        }
    }

    private function updateProductFlag(string $flag, bool $bool, $productID)
    {
        $set = 'ADD_TO_SET(:flag, campaign)';
        if (!$bool) {
            $set = 'REMOVE_FROM_SET(:flag, campaign)';
        }

        sqlQuery('UPDATE products SET campaign = '.$set.' WHERE id = :id_product', ['id_product' => $productID, 'flag' => $flag]);
    }

    public function syncPrices(array $prices, int $productId): void
    {
        $this->priceSynchronizer->syncPrices($prices, $productId);
    }

    public function syncVariations(array $variations, int $productID): void
    {
        $this->cleanProductVariations($variations, $productID);

        $codeExists = [];
        foreach ($variations as &$tmpVariation) {
            if (empty($tmpVariation->ProductNumber)) {
                continue;
            }

            if ($codeExists[$tmpVariation->ProductNumber] ?? false) {
                $tmpVariation->ProductNumber .= '_'.++$codeExists[$tmpVariation->ProductNumber];
                continue;
            }

            $codeExists[$tmpVariation->ProductNumber] = 1;
        }

        // create | update variants
        foreach ($variations as $variation) {
            $id = $this->helper->findVariation($this->helper->trimVarioId($variation->ID), $productID);
            if (!$id) {
                $id = \Variations::createProductVariation($productID, [
                    $this->getVariationLabelId($variation) => $variation->VariantName,
                ]);
                $this->updateSQL('products_variations', ['date_added' => date('Y-m-d H:i:s')], ['id' => $id]);
                $this->helper->createMapping('vario_variations', $this->helper->trimVarioId($variation->ID), $id);

                $this->addChangedItem(['id_product' => $productID, 'id_variation' => $id]);
            }

            $update = [
                'code' => !empty($variation->ProductNumber) ? $variation->ProductNumber : null,
                'ean' => null,
            ];

            // updating
            if (!empty($variation->EANs) && $id) {
                if (!empty($variation->EANs[0]->EAN)) {
                    $ean = $variation->EANs[0]->EAN;
                } else {
                    $ean = null;
                }

                $update['ean'] = $ean;
            }

            try {
                $this->updateSQL('products_variations', $update, ['id' => $id]);
            } catch (UniqueConstraintViolationException $e) {
                $update['code'] = null;
                $this->updateSQL('products_variations', $update, ['id' => $id]);
            }
        }

        if ($this->newProduct !== false) {
            // force stock update for product to avoid missing supplies
            $this->forceSyncSupply($this->newProduct);
        }
    }

    public function cleanProductVariations(array $variations, int $productID): void
    {
        $data = sqlQueryBuilder()->select('pv.id, vv.id_vario')
            ->from('products_variations', 'pv')
            ->leftJoin('pv', 'products', 'p', 'pv.id_product = p.id')
            ->leftJoin('pv', 'vario_variations', 'vv', 'vv.id_variation = pv.id')
            ->where(Operator::equals(['p.id' => $productID]))
            ->execute();

        $idsFromVario = array_flip(
            array_map(
                function ($x) {
                    return $this->helper->trimVarioId($x->ID);
                },
                $variations
            )
        );

        foreach ($data as $variant) {
            if (!isset($idsFromVario[$variant['id_vario']])) {
                $this->deleteSQL('products_variations', ['id' => $variant['id']]);
            }
        }
    }

    protected function getVatByName(string $vatName): ?float
    {
        switch ($vatName) {
            case 'Základní':
                return 21;
            case 'Snížená':
                return 12;
            case 'Snížená 2':
                return 12;
            default:
                return (float) $vatName;
        }
    }

    protected function isItemAllowed(object $item): bool
    {
        return true;
    }

    protected function getVariationLabelId(object $variation): int
    {
        return $this->config->getDefaultLabelId();
    }

    protected function updateProductParameter(int $parameterId, int $productId, array $values, bool $isListParameter = true)
    {
        $listValues = [];
        if ($isListParameter) {
            foreach ($values as $i => $value) {
                if ($parameterValueId = $this->parameterFinder->findParameterValue($parameterId, $value)) {
                    $listValues[] = $parameterValueId;
                }
            }
        } else {
            $listValues = $values;
        }

        $this->parameterFinder->setProductParameters($productId, $parameterId, $listValues);
    }

    protected function getProducer(string $name): ?int
    {
        static $producers;

        if (empty(trim($name))) {
            return null;
        }

        $nameOriginal = $name;
        $name = mb_strtolower($name);

        if (!$producers) {
            $producers = sqlFetchAll(sqlQueryBuilder()
                ->select('id, LOWER(name) as name')
                ->from('producers')
                ->execute(),
                ['name' => 'id']);
        }

        if (!($producers[$name] ?? false)) {
            $producers[$name] = sqlGetConnection()->transactional(function () use ($nameOriginal) {
                sqlQueryBuilder()
                    ->insert('producers')
                    ->directValues(
                        [
                            'name' => $nameOriginal,
                        ]
                    )->execute();

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

        return (int) $producers[$name];
    }

    public function syncCommon($value, $productID, $field)
    {
        switch ($field) {
            case 'ProductName':
                $column = 'title';
                $value = trim($value);
                break;
            case 'Code':
                $column = 'code';
                break;
            case 'Warranty':
                $column = 'guarantee';
                break;
            default:
                $column = $field;
        }

        $this->updateSQL('products', [$column => $value], ['id' => $productID]);
    }

    protected function postprocess()
    {
    }

    protected function getObjectID($item)
    {
        return $item->Data;
    }

    protected function getSyncAllObjects()
    {
        return $this->client->getProductIds();
    }

    private function getExtendedParameters(string $objectId, ?string $type = null): array
    {
        $data = $this->client->getClient()->GetProductExtsArray(
            $this->client->getClient()->GetProductExts($objectId)
        );

        if ($type) {
            $data = array_filter($data, function ($x) use ($type) {
                return $x->PEType === $type;
            });
        }

        // sort
        usort($data, function ($item1, $item2) {
            return $item1->Order <=> $item2->Order;
        });

        // unique filename
        $exists = [];
        foreach ($data as $key => $item) {
            if ($exists[$item->FileName] ?? false) {
                unset($data[$key]);
                continue;
            }

            $exists[$item->FileName] = true;
        }

        return $data;
    }

    private function getParameterType(int $parameterId): ?string
    {
        static $parameterTypeCache;

        if (!($parameterTypeCache[$parameterId] ?? false)) {
            $parameter = sqlQueryBuilder()
                ->select('id, value_type')
                ->from('parameters')
                ->where(Operator::equals(['id' => $parameterId]))
                ->execute()->fetch();

            if ($parameter) {
                $parameterTypeCache[$parameter['id']] = $parameter['value_type'];
            }
        }

        return $parameterTypeCache[$parameterId] ?? null;
    }
}
