<?php

use KupShop\CatalogBundle\Event\ProductEvent;
use KupShop\CatalogBundle\Search\FulltextInterface;
use KupShop\CatalogBundle\Section\SectionTree;
use KupShop\ElninoBundle\Util\Sortiment;
use KupShop\I18nBundle\Translations\ParametersListTranslation;
use KupShop\I18nBundle\Translations\ProductsTranslation;
use KupShop\I18nBundle\Translations\SectionsTranslation;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use Query\Operator;

class AbraKoza2 extends AbraElninoBase
{
    public static $abraKozaV2 = true;

    public static $flagsToHide = [];

    public static $ABRA_UIDS = [
        'StoreCard' => 'product',
        'ReceivedOrder' => 'order',
        'ReceivedOrderRow' => 'order',
        'Brand' => 'producer',
        'SeriesBrand' => 'serie',
        'Parameter' => 'parameters',
        'ParameterValue' => 'parameters_values',
        'ParameterAssortmentGroup' => 'parameters_sections',
        'ParameterStoreCard' => 'parameters_products',
        'AssortmentGroup' => 'section',
        'ComplementModel' => 'complement_model',
        'ComplementTypeSC' => 'complement_type',
        'Model' => 'model',
        'Damage' => 'damage',
        'Scent' => 'scent',
        'ElementScentModel' => 'product_scent',
        'Picture' => 'picture',
        'UnitSize' => 'unitsize',
        'SecondaryAssortmentGroup' => 'secondary_assortment_group',
    ];

    public static $PRODUCT_FIELDS = [
        'ONDELETE' => 'delete',
        // Base
        'Code' => 'code',
        'StoreMenuItem_ID' => 'ignore',
        'Quantity' => 'in_store',
        'X_LimitWEB' => 'ignore',
        'X_WEBGroup' => 'ignore',
        'VOC_CZK' => 'ignore',
        'ID' => 'ignore',
        'EAN' => 'ean',
        'X_VATRate' => 'vat',
        'SeriesBrand_ID' => 'id_serie',
        'StoreAssortmentGroup_ID' => 'id_section',
        'ComplementModel_ID' => 'id_complement_model',
        'ComplementTypeSC_ID' => 'id_complement_type',
        'Annotation' => 'annotation',
        'Brand_ID' => 'producer_id',

        // Popis
        'X_Name_Mark' => 'ignore', // Parametr Produktová řada
        'X_Note2' => 'ignore',
        'X_Description' => 'descr',

        // Flagy
        'X_Gift' => 'flag_G', // Flag dárek
        'X_Action' => 'ignore', // Flag Akce
        'X_Sample' => 'flag_V', // Flag Vzorek
        'X_Tester' => 'flag_T', // Flag Tester
        'X_Spatter' => 'flag_O', // Flag Odstrik
        'X_Set' => 'flag_S', // Flag Set
        'Quantity_ToDelivery' => 'ignore', // Flag Na ceste + parametry
        'X_VOEU' => 'ignore',

        // Parametry
        'X_SortCosmetics' => 'ignore', // Kategorie produktu + parametr
        'X_Size' => 'ignore', // Parametr Velikost
        'X_Damage' => 'ignore', // Parameter Poškození
        'X_Damage_EN' => 'ignore', // Parameter Poškození
        'X_sexB' => 'ignore', // Parametr Pohlaví
        'X_Constitution' => 'constitution', // Parametr Složení
        'X_Shade' => 'shade', // Parametr Odstín

        // Ignores
        // 'Description' => 'descr',
        'UnitSize_ID' => 'ignore',
        'SETtype' => 'ignore',
        'Name2' => 'name2',
        //      '' => 'ignore',

        'X_SETtype' => 'ignore',
        'X_SortCosmeticsPL' => 'ignore',
        'X_SortCosmeticsEN' => 'ignore',
        'X_LimitX_SortCosmeticsENWEB' => 'ignore',
        'X_Note2EN' => 'ignore',
        'X_Note2PL' => 'ignore',
        'X_ShadeEN' => 'ignore',
        'X_ShadePL' => 'ignore',
        'X_Constitution_EN' => 'ignore',
        'X_Constitution_PL' => 'ignore',
        'X_Constitution_DE' => 'ignore',
        'X_ShadeDE' => 'ignore',
        'X_Note2DE' => 'ignore',
        'X_EU' => 'ignore',
        'X_IDpar' => 'ignore',
        'X_Activate' => 'figure',
        'X_WEB_Transfer' => 'ignore',
        'X_WEB' => 'ignore',
        'Depth' => 'ignore',
        'Height' => 'ignore',
        'Width' => 'ignore',

        // Prices
        'MOC_EUR_GR' => 'ignore',
        'MOC_EUR_PE' => 'ignore',
        'VOC_PLN' => 'ignore',
        'MOC_CZK_KZ' => 'price',
        'MOC_CZK_PC' => 'ignore',
        'MOC_CZK_PE' => 'ignore',
        'VOC_EUR' => 'ignore',
        'MOC_CZK_SP' => 'ignore',
        'MOC_CZK_PV' => 'ignore',
        'MOC_CHF' => 'ignore',
        'MOC_CHF_PA' => 'ignore',
        'MOC_BGN' => 'ignore',
        'MOC_EUR_UA' => 'ignore',
        'NAK_CZK_ES' => 'ignore',
        'MOC_CZK_PE_DPH' => 'ignore',
        'NAK_EUR_SI' => 'ignore',
        'MOC_PLN_PP_DPH' => 'ignore',
        'MOC_PLN_EG_DPH' => 'ignore',
        'MOC_EUR_SL_DPH' => 'ignore',
        'MOC_HRK_LI_DPH' => 'ignore',
        'MOC_EUR_IT_DPH' => 'ignore',
        'NAK_EUR_LI' => 'ignore',
        'MOC_CZK_ES_DPH' => 'ignore',
        'MOC_EUR_IT_DPH_12' => 'ignore',
        'price_force' => 'price',
        'price_history_force' => 'price_history',
        'price_history_store' => 'price_history_store',

        'Size' => 'size_value',
        'X_Damage_PL' => 'ignore',
        'X_Damage_ID' => 'damage',
        'X_InstructionForUse' => 'ignore',
        'X_Label' => 'ignore',
        'LastID' => 'ignore',
        'Weight' => 'weight',
        'Width' => 'width',
        'Height' => 'height',
        'Depth' => 'depth',

        'Video_URL' => 'video_url',

        // GPSR contact
        'SupplierName' => 'sup_name',
        'SupplierAddress' => 'sup_addr',
        'SupplierContact' => 'sup_contact',
    ];

    public static $PICTURE_FIELDS = [
        'StoreCard_ID' => 'id_product',
        'PictureType' => 'type',
        'ONDELETE' => 'delete',
    ];

    public static $SCENT_FIELDS = [
        'Name' => 'name',
        'ONDELETE' => 'delete',
    ];

    public static $ORDER_FIELDS = [
        'Status' => 'status',
        'Note_Expedition' => 'note_expedition',
        'NoteEXPEDITION' => 'note_expedition',
        'Note_CustomerService' => 'note_service',
        'PackageNumber' => 'package_number',
        'ReasonDescription' => 'note_storno',

        'Quantity' => 'quantity',
        'ExternalOrder' => 'ExternalOrder',
    ];

    public static $SECONDARY_ASSORTMENT_GROUP_FIELDS = [
        'StoreCard_ID' => 'abra_product_id',
        'StoreAssortmentGroup_ID' => 'abra_section_id',
        'ONDELETE' => 'delete',
    ];

    public static $ENABLED_STORES = [1, 6, 15, 50, 51];

    public static array $STORES_HIERARCHY = [
        [
            'store' => 1,
            'price' => 1,
            'sum_stores' => [1, 6, 15],
        ],
    ];
    public static $EXTERNAL_SUPPLIER_ID = 0; // id from suppliers table

    public static $PRICE_LIMIT_FOR_DISCOUNT = 50000;

    protected $fulltext;

    public static $PICTURE_TYPES = [
        '1' => 'nothing',
        '2' => 'Y',
        '3' => 'nothing',
        '4' => 'K',
        '5' => 'O',
        '6' => 'V',

        '10' => 'V',
        '11' => 'V',
        '12' => 'V',
        '13' => 'V',
        '14' => 'V',
        '15' => 'V',

        // Coty fotky
        '21' => 'Y',
        '22' => 'V',
        '23' => 'V',
        '24' => 'V',
        '25' => 'V',
        '26' => 'V',
        '27' => 'V',
        '28' => 'V',
        '29' => 'V',
        '30' => 'V',

        // Loreal fotky
        '31' => 'Y',
        '32' => 'V',
        '33' => 'V',
        '34' => 'V',
        '35' => 'V',
        '36' => 'V',
        '37' => 'V',
        '38' => 'V',
        '39' => 'V',
        '40' => 'V',

        // small_partners - Menší partneři - #9698, #9700
        '41' => 'V',
        '42' => 'V',
        '43' => 'V',
        '44' => 'V',
        '45' => 'V',
        '46' => 'V',
        '47' => 'V',
        '48' => 'V',
        '49' => 'V',
        '50' => 'V',

        // small_partners_vip - Menší partneři (VIP) - #9779, #9830
        '51' => 'Y',
        '52' => 'V',
        '53' => 'V',
        '54' => 'V',
        '55' => 'V',
        '56' => 'V',
        '57' => 'V',
        '58' => 'V',
        '59' => 'V',
        '60' => 'V',

        '360' => 'G',
        '361' => 'coty_video', // 360 degrees video
        '362' => 'coty_video', // campaign video
        'default' => 'ignore',
    ];
    public static $pictureType6AsLead = false;

    /**
     * [ABRA_UID => [Array of fields to sync]].
     */
    public static $TRANSLATION_FIELDS = [
        'AssortmentGroup' => [
            'Name',
            'X_Description',
            'AlternativeName',
        ],
        'Damage' => [
            'Name',
        ],
        'Brand' => [
            'X_Description',
        ],
        'Scent' => [
            'Name',
        ],
        'UnitSize' => [
            'Code',
            'Name',
        ],
        'ComplementTypeSC' => [
            'Name',
        ],
        'Parameter' => [
            'Name',
        ],
        'ParameterValue' => [
            'Name',
        ],
        'SeriesBrand' => [
            'X_Description',
        ],
        'Model' => [
            'X_Description',
        ],
        'StoreCard' => [
            'Annotation',
            'X_Description',
            'X_Constitution',
            'Video_URL',
        ],
    ];

    public const CATEGORY_PARAMETER_ID = 28;
    public const MAIN_CATEGORY_PARAMETER_ID = 29;

    /** @var \KupShop\ElninoBundle\Util\RichContentUtil */
    protected $richContentUtil;

    protected $package_sent_messages = [
        BalikDoRuky::class => 'odeslano_cp',
        BalikNaPostu::class => 'odeslano_cp',
        Balikovna::class => 'odeslano_cp',
        BalikovnaNaAdresu::class => 'odeslano_cp',

        PPL::class => 'odeslano_ppl',
        PPLParcelShop::class => 'odeslano_ppl',

        Zasilkovna::class => 'odeslano_zasilkovna',
        ZasilkovnaDodaniNaAdresu::class => 'odeslano_zasilkovna',
        SPS::class => 'odeslano_sps',

        GLS::class => 'GLS',
        GLSParcelShop::class => 'GLS',
    ];

    protected $siteAbbr = [
        'kosmetika-zdravi.cz' => 'kzcz',
        'parfimo.ch' => 'fich',
        'eglamour.de' => 'egde',
        'parfimo.it' => 'fiit',
        'spleticna.si' => 'spsi',
        'parfumcity.ch' => 'pcch',
        'parfimo.de' => 'fide',
        'europarfemy.cz' => 'epcz',
        'parfumcity.cz' => 'pccz',
        'profumicitta.it' => 'prof',
        'parfimo.at' => 'fiat',
        'lijepa.hr' => 'lihr',
    ];

    public function __construct($settings = null)
    {
        parent::__construct($settings);
        $this->fulltext = ServiceContainer::getService(FulltextInterface::class);
        $this->richContentUtil = ServiceContainer::getService(\KupShop\ElninoBundle\Util\RichContentUtil::class);
    }

    // Sync functions
    public function sync_model($code, $field, $value)
    {
        $abra_model = sqlFetchAssoc($this->selectSQL('abra_models', ['id_abra' => $code]));
        if (!$abra_model) {
            $this->insertSQL('abra_models', ['id_abra' => $code]);
        }

        if (empty(static::$MODEL_FIELDS[$field])) {
            return;
        }

        $column = static::$MODEL_FIELDS[$field];

        if ($column == 'id_serie') {
            // update old_kupshop_id
            $abra_model = $this->selectSQL('abra_models', ['id_abra' => $code])->fetch();
            if (empty($abra_model['old_kupshop_id'])) {
                if (isset($this->settings['old_urls']) && $this->settings['old_urls'] == true) {
                    $maxId = sqlQuery('SELECT MAX(old_kupshop_id) as max_id FROM abra_models')->fetchColumn();
                    $this->updateSQL('abra_models', ['old_kupshop_id' => ($maxId + 1)], ['id_abra' => $code]);
                } else {
                    if ($this->selectSQL('abra_models', ['old_kupshop_id' => $code])->fetch()) {
                        $maxId = sqlQuery('SELECT MAX(old_kupshop_id) as max_id FROM abra_models')->fetchColumn();
                        $this->updateSQL('abra_models', ['old_kupshop_id' => ($maxId + 1)], ['id_abra' => $code]);
                    } else {
                        $this->updateSQL('abra_models', ['old_kupshop_id' => $code], ['id_abra' => $code]);
                    }
                }
            }
        } elseif ($column == 'id_category') {
            // is cosmetics?
            /** @var Sortiment $util */
            $util = ServiceContainer::getService(Sortiment::class);
            $sectionId = $this->selectSQL('abra_sections', ['id_abra' => $value], ['id_section'])->fetchColumn();
            $cosmetics = 'Y';
            if (in_array($sectionId, $util->getPerfumesSections())) {
                $cosmetics = 'N';
            }
            $this->updateSQL('abra_models', ['cosmetics' => $cosmetics], ['id_abra' => $code]);
        }

        if ($column === 'id_richcontent') {
            $this->richContentUtil->handleSync('abra_models', $abra_model['id_abra'], $value, $this->lang);
        }

        return parent::sync_model($code, $field, $value);
    }

    public function sync_product($code, $field, $value, $value2 = null)
    {
        if ($code === '00000001') {
            return;
        }
        if (!isset(static::$PRODUCT_FIELDS[$field])) {
            return;
        }

        // TODO: Tohle sloucit s AbraElnino. Vetsina by toho mela byt stejna. Aspon vyhodit to co uz je v sync_products_switch
        $column = static::$PRODUCT_FIELDS[$field];

        $id_product = $this->getMappingFromAbra('product', $code);

        if (!$id_product) {
            $vat_id = null;
            getVat($vat_id);

            $insert = [
                'code' => $code,
                'title' => $code,
                'vat' => $vat_id,
                'delivery_time' => '-1',
                //                'date_added' => date('Y-m-d H:i:s'), // set date_added later in updateVisibility() #9593
                'figure' => 'O',
            ];
            if (ctype_digit($code)) {
                $insert['id'] = $code;
                $id_product = $code;
            }

            $this->insertSQL('products', $insert);

            // maxscale tady zlobi. Kdyz mu nasetuju IDcko natvrdo, nevrati mi ho v sqlInsertId()
            if (!$id_product) {
                $id_product = sqlInsertId();
            }

            $this->updateSQL('products', ['code' => $code], ['id' => $id_product]);

            $this->setMappingFromAbra('product', $id_product, $code);
        }

        $id_model = returnSQLResult('SELECT id_model FROM products WHERE id = :id', ['id' => $id_product]);

        $value = trim($value);
        $msgLevel = $this->getMessageLevel();

        if ($column === 'id_richcontent') {
            $this->richContentUtil->handleSync('products', $id_product, $value, $this->lang);
        }

        if ($this->isTranslationsSync()) {
            if (!$this->translationFieldSupported($field)) {
                return;
            }

            switch ($column) {
                case 'descr':
                    $this->saveTranslationObject(ProductsTranslation::class, ['id_object' => $id_product], ['long_descr' => $value]);
                    break;
                case 'annotation':
                    $this->saveTranslationObject(ProductsTranslation::class, ['id_object' => $id_product], ['short_descr' => $value]);
                    break;
                case 'constitution':
                    $this->saveTranslationObject(ProductsTranslation::class, ['id_object' => $id_product], ['set' => $value]);
                    break;
                case 'video_url':
                    $this->saveTranslationObject(ProductsTranslation::class, ['id_object' => $id_product], ['video_url' => $this->parseNullable($value)]);
                    break;
                default:
                    $this->saveTranslationObject(ProductsTranslation::class, ['id_object' => $id_product], [$column => $value]);
                    break;
            }

            $this->scheduleProductTitleUpdate($id_product, null, null, null, null, $id_model);

            return;
        }

        if (is_null($msgLevel)) {
            return false;
        }

        switch ($column) {
            case 'ean':
                $value = $this->parseNullable($value);
                $this->updateSQL('products', ['ean' => $value], ['id' => $id_product]);
                break;

            case 'flag_G':
            case 'flag_A':
            case 'flag_V':
            case 'flag_T':
            case 'flag_O':
            case 'flag_S':
                $flag = substr($column, 5);

                if ($flag == 'S') {
                    $value = !empty($value);
                } else {
                    $value = $this->parseBool($value);
                }

                $data = ['id_product' => $id_product, 'flag' => $flag];

                $this->updateProductFlag($data, $value);
                $this->scheduleProductTitleUpdate($id_product);
                break;

            case 'damage':
                $value = $this->parseNullable($value);

                if (empty($value)) {
                    $this->updateProductFlag(['id_product' => $id_product, 'flag' => 'P'], false);
                } else {
                    $this->updateProductFlag(['id_product' => $id_product, 'flag' => 'P'], true);
                }

                $this->sync_products_common($code, $field, $value, $value2, $id_product);
                break;

            case 'price':
                if (!$value2) {
                    $value2 = 1;
                }

                if (!in_array($value2, static::$ENABLED_STORES)) {
                    var_dump("ignorace ceny skladu {$value2}.");

                    return;
                }

                $value = $this->preparePriceFromAbra($value);

                $qb = sqlQueryBuilder()->select('p.data, p.price, p.display_discount, p.figure, p.price_for_discount')
                    ->fromProducts()->where(Operator::equals(['p.id' => $id_product]));
                if (findModule(Modules::PRICE_HISTORY)) {
                    $qb->addSelect('MAX(ph.price) as max_price')
                        ->leftJoin('p', 'price_history', 'ph', 'p.id = ph.id_product AND ph.date_change >= DATE_SUB(CURDATE(), INTERVAL 21 DAY)');
                }
                $product = $qb->execute()->fetch();

                // Store new price to product custom data
                $data = json_decode($product['data'] ?? '[]', true);
                $data['elnino_prices'][$value2] = $value;

                // Get current product store
                $active_store = $this->getProductStoreHierarchy($data['elnino_stores'] ?? [], $data['elnino_prices'] ?? [], $data['elnino_storeActivations'] ?? []);

                $update_data = [
                ];

                $actual = $product['price'];
                $max_price = $product['max_price'] ?? $product['price'];
                if (($value != $actual || $max_price != $product['price_for_discount'])
                    && $actual > 0
                    && $product['figure'] == 'Y'
                    && isset($active_store['price'])
                    && $active_store['price'] == $value2
                ) {
                    if (findModule(Modules::PRICE_HISTORY) && ($value > $max_price)) {
                        $max_price = $value;
                        $history = ['id_product' => $id_product, 'price' => $max_price, 'last' => 1, 'date_change' => date('Y-m-d')];
                        if (empty($product['max_price'])) {
                            $this->insertSQL('price_history', $history);
                        } else {
                            $this->updateSQL('price_history', $history, ['id_product' => $id_product, 'last' => 1]);
                        }
                    }
                    $diff = floor((1 - ($value / $max_price)) * 100);

                    $change_discount = 0;
                    if ($diff >= 3 && $actual < $this::$PRICE_LIMIT_FOR_DISCOUNT && $diff <= $this::$MAX_DISCOUNT) {
                        $change_discount = 1;
                    } else {
                        if (findModule(Modules::PRICE_HISTORY)) {
                            $change_discount = -1;
                        } elseif ($diff < -3) {
                            $change_discount = -1;
                        }
                    }

                    $flagUpdateData = ['id_product' => $id_product, 'flag' => 'D'];
                    if ($change_discount == 1) {
                        $update_data['display_discount'] = $diff;
                        $update_data['price_for_discount'] = $max_price;
                        $this->updateProductFlag($flagUpdateData, true);

                        sqlQueryBuilder()->update('products')->set('discount_date', ':date')
                            ->setParameter('date', new DateTime(), \Doctrine\DBAL\Types\Type::DATETIME)
                            ->where(\Query\Operator::equals(['id' => $id_product]))->execute();
                    } elseif ($change_discount == -1) {
                        $update_data['display_discount'] = 0;
                        $update_data['price_for_discount'] = $value;
                        $this->updateProductFlag($flagUpdateData, false);

                        sqlQueryBuilder()->update('products')->set('discount_date', ':null')->setParameter('null', null)
                            ->where(\Query\Operator::equals(['id' => $id_product]))->execute();
                    }
                }

                if ($value != $actual && isset($active_store['price']) && $active_store['price'] == $value2) {
                    $update_data['price'] = $value;
                }

                if (($active_store['store'] ?? null) != ($data['elnino_store'] ?? '')) {
                    $data['elnino_store'] = $active_store['store'] ?? null;
                }

                $update_data['data'] = json_encode($data);

                $this->updateSQL('products', $update_data, ['id' => $id_product]);

                $this->scheduleProductTitleUpdate($id_product);
                break;

            case 'price_vat':
                $value = $this->preparePriceFromEUR($value);
                $value = toDecimal($value)->removeVat(getVat());
                $this->sync_product($code, 'price_force', $value, $value2);
                break;

            case 'price_vat_history':
                $value = $this->preparePriceFromEUR($value);
                $value = toDecimal($value)->removeVat(getVat());
                $this->sync_product($code, 'price_history_force', $value, $value2);
                break;

            case 'price_history':
                if (!$value2) {
                    $value2 = 1;
                }

                if (!in_array($value2, static::$ENABLED_STORES)) {
                    var_dump("ignorace ceny skladu {$value2}.");

                    return;
                }
                $value = $this->preparePriceFromAbra($value);

                $qb = sqlQueryBuilder()->select("p.id, p.data, p.price, p.figure, p.price_for_discount, FIND_IN_SET('PARTNER', p.campaign) AS is_partner")
                    ->fromProducts()->where(Operator::equals(['p.id' => $id_product]));

                $product = $qb->execute()->fetch();

                // Store new price to product custom data
                $data = json_decode($product['data'] ?? '[]', true);
                $data['elnino_prices'][$value2] = $value;

                // Get current product store
                $stores = $data['elnino_stores'] ?? [];
                $active_store = $this->getProductStoreHierarchy($stores, $data['elnino_prices'] ?? [], $data['elnino_storeActivations'] ?? []);

                $update_data = [
                ];

                $actual = $product['price'];
                if ($value != $actual && $actual > 0 && $product['figure'] == 'Y' && isset($active_store['price']) && $active_store['price'] == $value2) {
                    $priceForDiscount = $product['price_for_discount'] ?? $product['price']; // CPS
                    if ($priceForDiscount == '0.0000') {
                        $new_discount = 0.0;
                    } else {
                        $new_discount = floor((1 - ($value / $priceForDiscount)) * 100);
                    }
                    $flag_data = ['id_product' => $id_product, 'flag' => 'D'];

                    if ($new_discount >= 3 && $actual < $this::$PRICE_LIMIT_FOR_DISCOUNT && $new_discount <= $this::$MAX_DISCOUNT) {
                        $discount = $new_discount;
                        $this->updateProductFlag($flag_data, true);
                    } else {
                        $discount = 0;
                        $this->updateProductFlag($flag_data, false);
                    }
                    $update_data['display_discount'] = $discount;
                }

                if ($value != $actual && isset($active_store['price']) && $active_store['price'] == $value2) {
                    $update_data['price'] = $value;
                }

                if (($active_store['store'] ?? null) != ($data['elnino_store'] ?? '')) {
                    $data['elnino_store'] = $active_store['store'] ?? null;
                }

                $update_data['data'] = json_encode($data);
                $this->refreshPartnerFlag($active_store, $product);
                $this->refreshSupplierInStore($stores, $active_store, $id_product);

                $this->updateSQL('products', $update_data, ['id' => $id_product]);

                $this->scheduleProductTitleUpdate($id_product);
                break;

            case 'price_history_store':
                // Dočasná funkce, která dělá to samý co price_history, ale kašle na sklady
                // to když se sklady řeší na eshopu, ne v engine, ale chci mít v engine řešení slev
                $qb = sqlQueryBuilder()->select('p.price, p.figure, p.price_for_discount')
                    ->fromProducts()->where(Operator::equals(['p.id' => $id_product]));

                $product = $qb->execute()->fetch();

                $update_data = [
                ];

                $actual = $product['price'];
                if ($value != $actual && $actual > 0 && $product['figure'] == 'Y') {
                    $priceForDiscount = $product['price_for_discount'] ?? $product['price']; // CPS
                    if ($priceForDiscount == '0.0000') {
                        $new_discount = 0.0;
                    } else {
                        $new_discount = floor((1 - ($value / $priceForDiscount)) * 100);
                    }
                    $flag_data = ['id_product' => $id_product, 'flag' => 'D'];

                    if ($new_discount >= 3 && $actual < $this::$PRICE_LIMIT_FOR_DISCOUNT && $new_discount <= $this::$MAX_DISCOUNT) {
                        $discount = $new_discount;
                        $this->updateProductFlag($flag_data, true);
                    } else {
                        $discount = 0;
                        $this->updateProductFlag($flag_data, false);
                    }
                    $update_data['display_discount'] = $discount;
                }

                $update_data['price'] = $value;

                if ($update_data) {
                    $this->updateSQL('products', $update_data, ['id' => $id_product]);
                }
                break;

            case 'price_buy':
                $new_buy = $this->preparePriceFromEUR($value);

                $product = sqlFetchArray(sqlQuery(
                    "SELECT price, price_buy
                    FROM products
                    WHERE id=:id AND NOT FIND_IN_SET('I', campaign)",
                    ['id' => $id_product]
                ));
                if (!$product) {
                    return;
                }

                $old_buy = (float) $product['price_buy'];
                $old_price = (float) $product['price'];
                if ($old_buy && $old_price) {
                    $margin = $old_price / $old_buy;
                    $new_price = $new_buy * $margin;
                    $this->updateSQL('products', ['price_buy' => $new_buy, 'price' => $new_price], ['id' => $id_product]);
                } else {
                    $this->updateSQL('products', ['price_buy' => $new_buy], ['id' => $id_product]);
                }

                $this->scheduleProductTitleUpdate($id_product);
                break;

            case 'price_buy_save_only':
                if (empty($value2) || $value2 == 1) {
                    $new_buy = $this->preparePriceFromEUR($value);
                    $this->updateSQL('products', ['price_buy' => $new_buy], ['id' => $id_product]);
                }
                break;

            case 'vat':
                $id_vat = returnSQLResult('SELECT id FROM vats WHERE vat=:vat', ['vat' => $value]);

                $this->updateSQL('products', ['vat' => $id_vat], ['id' => $id_product]);
                break;

            case 'descr':
                $this->updateSQL('products', ['long_descr' => $value], ['id' => $id_product]);
                break;

            case 'annotation':
                $this->updateSQL('products', ['short_descr' => $value], ['id' => $id_product]);
                break;

            case 'figure':
                if (!empty($value2)) {
                    // product+web+store X_Activation sync
                    $product = sqlFetchAssoc(sqlQuery("SELECT id, data, FIND_IN_SET('PARTNER', campaign) AS is_partner FROM products WHERE id=:id", ['id' => $id_product]));

                    $data = json_decode($product['data'], true);
                    $prices = $data['elnino_prices'] ?? [];
                    $stores = $data['elnino_stores'] ?? [];
                    $storeActivations = $data['elnino_storeActivations'] ?? [];
                    if ($value === 'DEL' && isset($storeActivations[$value2])) {
                        // product+web+store deactivation deletion, e.g: 2719494235~|~StoreCard~|~X_Activate~|~154323~|~DEL~|~1~|~kosmetika-zdravi.cz~|~all
                        unset($storeActivations[$value2]);
                    } elseif ($value !== 'DEL') {
                        // set product+web+store deactivation, e.g: 2719494235~|~StoreCard~|~X_Activate~|~154323~|~Ne~|~1~|~kosmetika-zdravi.cz~|~all
                        $storeActivations[$value2] = $this->parseBool($value);
                    }

                    // Get current product store
                    $active_store = $this->getProductStoreHierarchy($stores, $prices, $storeActivations);

                    $inStoreValue = array_sum(
                        array_map(function ($id_store) use ($stores) {
                            return $stores[$id_store] ?? 0;
                        }, $active_store['sum_stores'] ?? [])
                    );

                    $update_data = [
                        'in_store' => $inStoreValue,
                    ];
                    $this->refreshPartnerFlag($active_store, $product);
                    $this->refreshSupplierInStore($stores, $active_store, $id_product);

                    if (isset($active_store['price']) && ($prices[$active_store['price']] ?? false)) {
                        $update_data['price'] = $prices[$active_store['price']];
                    } else {
                        unset($update_data['price']);
                    }

                    if (($active_store['store'] ?? null) != ($data['elnino_store'] ?? '')) {
                        $data['elnino_store'] = $active_store['store'] ?? null;
                    }

                    $data['elnino_stores'] = $stores;
                    if (count($storeActivations)) {
                        $data['elnino_storeActivations'] = $storeActivations;
                    } else {
                        unset($data['elnino_storeActivations']);
                    }

                    $update_data['data'] = json_encode($data);

                    $logTransactionLevel = array_search($this->settings['web'], ['kosmetika-zdravi.cz', 'europarfemy.cz']) !== false;
                    if (!isLocalDevelopment() && $logTransactionLevel) {
                        $this->log([
                            'store X_Activate transactionLevel' => sqlGetConnection()->getTransactionNestingLevel(),
                            'productID' => $id_product,
                            'value' => $value,
                            'value2' => $value2,
                            'queue' => $this->settings['queue'],
                        ]);
                    }

                    sqlQueryBuilder()->update('products')->directValues($update_data)
                        ->where(Operator::equals(['id' => $id_product]))
                        ->execute();
                    $this->scheduleProductTitleUpdate($id_product, null, null, null, null, $id_model);
                    break;
                }

                //                $productLevel = returnSQLResult('SELECT figure_level FROM products WHERE id=:id', ['id' => $id_product]);
                //
                //                if ($msgLevel < $productLevel) {
                //                    return false;
                //                }

                // On delete, revert to zero
                if ($value === '' || $value === 'DEL') {
                    $msgLevel = 0;
                }

                $value = $this->parseBool($value);

                if ($msgLevel == 3) {
                    break; // ignore X_Activation per site (ignore legacy local deactivation)
                }

                $this->updateSQL('products', ['figure' => $value ? 'Y' : 'N', 'figure_level' => $msgLevel], ['id' => $id_product]);
                $this->scheduleProductTitleUpdate($id_product, null, null, null, null, $id_model);
                break;

            case 'in_store':
                if (!$value2) {
                    var_dump('Neni cislo skladu!!!');

                    return null;
                }

                if (!in_array($value2, static::$ENABLED_STORES)) {
                    var_dump("Ignorace skladu {$value2} !!!");

                    return null;
                }

                $value = intval($value);

                $product = sqlFetchAssoc(sqlQuery("SELECT id, data, FIND_IN_SET('PARTNER', campaign) AS is_partner FROM products WHERE id=:id", ['id' => $id_product]));

                $data = json_decode($product['data'], true);
                $prices = $data['elnino_prices'] ?? [];
                $stores = $data['elnino_stores'] ?? [];
                $stores[$value2] = $value;
                $storeActivations = $data['elnino_storeActivations'] ?? [];

                // Get current product store
                $active_store = $this->getProductStoreHierarchy($stores, $prices, $storeActivations);

                $value = array_sum(
                    array_map(function ($id_store) use ($stores) {
                        return $stores[$id_store] ?? 0;
                    }, $active_store['sum_stores'] ?? [])
                );

                $update_data = [
                    'in_store' => $value,
                ];
                $this->refreshPartnerFlag($active_store, $product);
                $this->refreshSupplierInStore($stores, $active_store, $id_product);

                if (isset($active_store['price']) && ($prices[$active_store['price']] ?? false)) {
                    $update_data['price'] = $prices[$active_store['price']];
                } else {
                    unset($update_data['price']);
                }

                if (($active_store['store'] ?? null) != ($data['elnino_store'] ?? '')) {
                    $data['elnino_store'] = $active_store['store'] ?? null;
                }

                $data['elnino_stores'] = $stores;

                $update_data['data'] = json_encode($data);

                sqlQueryBuilder()->update('products')->directValues($update_data)
                    ->where(Operator::equals(['id' => $id_product]))
                    ->execute();
                break;

            case 'producer_id':
                $id_producer = $this->getMappingFromAbra('producer', $value);
                $this->updateSQL('products', ['producer' => $id_producer], ['id' => $id_product]);

                $this->scheduleProductTitleUpdate($id_product);

                break;

            case 'id_section':
                $this->sync_products_common($code, $field, $value, $value2, $id_product);
                break;
            case 'sex':
                $data = ['id_product' => $id_product, 'id_parameter' => 18];
                $this->deleteSQL('parameters_products', $data);

                switch ($value) {
                    case 'M':
                        $value = 16;
                        break;
                    case '':
                    case 'W':
                        $value = 17;
                        break;
                    case 'U':
                        $value = 22;
                        break;
                    case 'K':
                        $value = 23;
                        break;
                    case 'B':
                        $value = 24;
                        break;
                    default:
                        throw new Exception('Nedefinované pohlaví: '.$value);
                }

                $data['value_list'] = $value;
                $this->insertSQL('parameters_products', $data);

                $this->scheduleProductTitleUpdate($id_product);

                return;

            case 'weight':
                if (findModule(Modules::PRODUCTS, Modules::SUB_WEIGHT)) {
                    $value = $value == $this->abraNull ? null : Decimal::fromString($this->preparePriceFromAbra($value));
                    $this->updateSQL($this->main_table, [$column => $value], ['id' => $id_product]);
                }
                break;

            case 'width':
            case 'height':
            case 'depth':
                $value = $value == $this->abraNull ? null
                    : Decimal::fromString($this->preparePriceFromAbra($value))->div(Decimal::fromInteger(10)); // comes in millimeters
                $this->updateSQL($this->main_table, [$column => $value], ['id' => $id_product]);
                break;

            case 'video_url':
            case 'sup_name':
            case 'sup_contact':
                $this->updateSQL('products', [$column => $this->parseNullable($value)], ['id' => $id_product]);
                break;

            case 'sup_addr':
                $value = trim($value) === ',  ,' ? '' : $value;
                $this->updateSQL('products', [$column => $this->parseNullable($value)], ['id' => $id_product]);
                break;

            case 'ignore':
                break;

            case 'delete':
                $this->deleteSQL('products', ['id' => $id_product]);
                $this->scheduleProductTitleUpdate(null, null, null, null, null, $id_model);
                break;

            default:
                $this->sync_products_common($code, $field, $value, $value2, $id_product);

                return;
        }
    }

    protected function getProductStoreHierarchy(array $stock, array $prices, array $storeActivations)
    {
        foreach (static::$STORES_HIERARCHY as $store) {
            $pieces = $stock[$store['store']] ?? 0;
            $price = $prices[$store['price']] ?? 0;
            $storeActive = $storeActivations[$store['store']] ?? true;
            if ($pieces > 0 && $price > 0 && $storeActive) {
                return $store;
            }
        }

        return [];
    }

    public function updateVisibility($models, $products)
    {
        if ($products) {
            $allStoresDeactivatedJsonString = json_encode(array_fill_keys(static::$ENABLED_STORES, false));

            $SQL = sqlQuery('UPDATE products p
               LEFT JOIN photos_products_relation AS ppr ON ppr.id_product = p.id AND ppr.show_in_lead = "Y"
               LEFT JOIN (
                 SELECT i_s.*, i_ps.id_product FROM products_in_sections i_ps
                 INNER JOIN sections i_s ON i_s.id=i_ps.id_section AND i_s.figure = \'Y\'
                 WHERE i_ps.id_product IN (:id_products)
               ) s ON s.id_product=p.id
               SET p.figure = \'O\'
               WHERE p.figure = \'Y\' AND (
                   ppr.id_photo IS NULL OR p.price <= 0 OR (damage IS NOT NULL AND p.in_store <= 0) OR p.id_model IS NULL OR s.id IS NULL
                   OR JSON_CONTAINS(JSON_QUERY(p.data, \'$.elnino_storeActivations\'), :allStoresDeactivated)
                ) AND p.id IN (:id_products)',
                ['id_products' => $products, 'allStoresDeactivated' => $allStoresDeactivatedJsonString],
                ['id_products' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
            );

            if (sqlAffectedRows($SQL) > 0) {
                var_dump(['Skryto produktů bez obrázků nebo nulovou cenou nebo bez modelu nebo vyprodaných poškozených krabiček', sqlAffectedRows($SQL)]);
            }

            $SQL = sqlQuery('UPDATE products p
               LEFT JOIN photos_products_relation AS ppr ON ppr.id_product = p.id AND ppr.show_in_lead = "Y"
               LEFT JOIN products_in_sections ps ON ps.id_product=p.id
               LEFT JOIN sections s ON s.id=ps.id_section AND s.figure = \'Y\'
               SET p.figure = \'Y\', p.date_added = IF(p.date_added IS NULL OR p.date_added = \'0000-00-00 00:00:00\', NOW(), p.date_added)
               WHERE p.figure = \'O\' AND ppr.id_photo IS NOT NULL AND p.price > 0 AND (damage IS NULL OR p.in_store > 0) AND (p.id_model IS NOT NULL) AND (s.id IS NOT NULL)
                    AND NOT COALESCE(JSON_CONTAINS(JSON_QUERY(p.data, \'$.elnino_storeActivations\'), :allStoresDeactivated), FALSE)
                    AND p.id IN (:id_products)',
                ['id_products' => $products, 'allStoresDeactivated' => $allStoresDeactivatedJsonString],
                ['id_products' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
            );

            if (sqlAffectedRows($SQL) > 0) {
                var_dump(['Zobrazeno produktů s obrázkem a cenou a skladem poškozených krabiček', sqlAffectedRows($SQL)]);
            }

            if (!empty(static::$flagsToHide)) {
                $where = [];
                foreach (static::$flagsToHide as $flag) {
                    $where[] = 'FIND_IN_SET("'.$flag.'", p.campaign)';
                }
                $where = join(' OR ', $where);

                $SQL = sqlQuery(
                    'UPDATE products p
                SET p.figure = "N"
                WHERE p.figure = "Y" AND ('.$where.') AND p.id IN (:id_products)',
                    ['id_products' => $products],
                    ['id_products' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
                );

                if (sqlAffectedRows($SQL) > 0) {
                    var_dump(['Skryto produktů s flagem "O" (odstřik) nebo "V" (vzorek)'], sqlAffectedRows($SQL));
                }
            }

            $gift = translate('gift', 'elnino', true) ?: 'dárek';

            $SQL = sqlQuery('UPDATE products p
                           SET p.name2 = CONCAT_WS(\' \', p.name2, :gift)
                           WHERE p.id IN (:id_products) AND FIND_IN_SET(\'G\', p.campaign) AND p.name2 NOT LIKE :gift_all',
                ['id_products' => $products, 'gift' => $gift, 'gift_all' => "%{$gift}%"],
                ['id_products' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);

            if (sqlAffectedRows($SQL) > 0) {
                var_dump(['Přidáno doplnků názvu produktu jako "dárek"', sqlAffectedRows($SQL)]);
            }

            $SQL = sqlQuery("UPDATE products p
                           SET p.name2 = REPLACE(REPLACE(p.name2, :gift, ''), '  ', ' ')
                           WHERE p.id IN (:id_products) AND FIND_IN_SET('G', p.campaign) = 0 AND p.name2 LIKE :gift_all",
                ['id_products' => $products, 'gift' => $gift, 'gift_all' => "%{$gift}%"],
                ['id_products' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);
            if (sqlAffectedRows($SQL) > 0) {
                var_dump(['Odebráno doplňků názvu produktu jako "dárek"', sqlAffectedRows($SQL)]);
            }

            $SQL = sqlQuery('UPDATE products 
                            SET figure = "N"
                            WHERE FIND_IN_SET("G", campaign) AND id IN (:id_products)',
                ['id_products' => $products],
                ['id_products' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);

            if (sqlAffectedRows($SQL) > 0) {
                var_dump(['Skryto produktů označených jako dárek'], sqlAffectedRows($SQL));
            }
        }

        if ($models) {
            $SQL = sqlQuery('UPDATE products p
                JOIN (
                  SELECT am.id_abra, MAX(p.figure) as max_figure, am.id_photo FROM abra_models am 
                  JOIN products p ON p.id_model = am.id_abra 
                  WHERE am.id_abra IN (:id_models) GROUP BY am.id_abra HAVING max_figure = \'Y\'
                ) t ON p.id_model = t.id_abra
                SET p.figure = \'O\'
                WHERE p.figure = \'Y\' AND t.id_photo IS NULL',
                ['id_models' => $models],
                ['id_models' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
            );

            if (sqlAffectedRows($SQL) > 0) {
                var_dump(['Skryto produktů bez obrázků nebo bez viditelné varianty', sqlAffectedRows($SQL)]);
            }

            $SQL = sqlQuery('UPDATE products p
                JOIN (
                  SELECT am.id_abra, MAX(p.figure) as max_figure, am.id_photo FROM abra_models am 
                  JOIN products p ON p.id_model = am.id_abra 
                  WHERE am.id_abra IN (:id_models) GROUP BY am.id_abra
                ) t ON p.id_model = t.id_abra
                LEFT JOIN photos_products_relation AS ppr ON ppr.id_variation = p.id AND ppr.show_in_lead = "N"
                SET p.figure = \'Y\'
                WHERE p.figure = \'O\' AND t.id_photo IS NOT NULL AND p.price > 0 AND FIND_IN_SET("G", p.campaign) = 0 AND ppr.id_photo IS NOT NULL',
                ['id_models' => $models],
                ['id_models' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
            );

            if (sqlAffectedRows($SQL) > 0) {
                var_dump(['Zobrazeno produktů v "prodej ukončen" s obrázkem a cenou a viditelnou variantou', sqlAffectedRows($SQL)]);
            }
        }

        //        // Update fulltext search index
        //        if ($this->fulltext && $products) {
        //            foreach ($products as $id_product) {
        //                $this->fulltext->updateProduct($id_product);
        //            }
        //        }
    }

    public function updateScheduledProducts()
    {
        if (!empty($this->cwd)) {
            chdir($this->cwd);
        }

        $this->collectUpdatedProducts();

        // Update products
        if ($product_list = $this->getDelayedUpdate('product')) {
            $productIds = array_keys($product_list);
            $this->updateVisibility(null, $productIds);

            $this->updateFeedPricesByContentAPI($productIds);

            // update products for each language
            foreach ($this->languageContext->getSupported() as $language) {
                $qb = sqlQueryBuilder()
                    ->select('p.*, s.name AS section_name, pr.name AS producer_name, abs.name AS serie_name,abd.name AS damage_name, abcm.name AS complement_model_name,
            abct.name AS complement_type_name, ap.id_serie, ap.id_section, ap.id_complement_model, abs.id_producer, abs.id_serie AS id_serie_kupshop, abs.sex, abm.id_abra as id_model')
                    ->from('products', 'p')
                    ->leftJoin('p', 'abra_models', 'abm', 'abm.id_abra = p.id_model')
                    ->leftJoin('ap', 'abra_sections', 'absec', 'absec.id_abra = abm.id_section')
                    ->leftJoin('absec', 'sections', 's', 's.id = absec.id_section')
                    ->leftJoin('ap', 'abra_complement_models', 'abcm', 'abcm.id_abra = abm.id_complement_model')
                    ->leftJoin('p', 'abra_complement_types', 'abct', 'abct.id_abra = p.id_complement_type')
                    ->leftJoin('p', 'abra_damages', 'abd', 'abd.id_abra = p.damage')
                    ->leftJoin('p', 'abra_products', 'ap', 'p.id = ap.id_product')
                    ->leftJoin('ap', 'abra_series', 'abs', 'abs.id_abra = ap.id_serie')
                    ->leftJoin('abs', 'producers', 'pr', 'pr.id = abs.id_producer')
                    ->andWhere(\Query\Operator::inIntArray($productIds, 'p.id'))
                    ->groupBy('p.id');

                if ($language->getId() != $this->languageContext->getDefaultId()) {
                    // join translations
                    $qb->andWhere(
                        \Query\Translation::coalesceTranslatedFields(
                            \KupShop\I18nBundle\Translations\SectionsTranslation::class, ['name' => 'section_name'], $language->getId()
                        )
                    )->andWhere(
                        \Query\Translation::coalesceTranslatedFields(
                            \KupShop\ElninoBundle\Translations\AbraDamagesTranslation::class, ['name' => 'damage_name'], $language->getId()
                        )
                    )->andWhere(
                        \Query\Translation::coalesceTranslatedFields(
                            \KupShop\ElninoBundle\Translations\AbraModelsTranslation::class, null, $language->getId()
                        )
                    )->andWhere(
                        \Query\Translation::coalesceTranslatedFields(
                            \KupShop\ElninoBundle\Translations\AbraComplementTypeTranslation::class, ['name' => 'complement_type_name'], $language->getId()
                        )
                    )->andWhere(
                        \Query\Translation::coalesceTranslatedFields(
                            \KupShop\I18nBundle\Translations\ProductsTranslation::class, null, $language->getId()
                        )
                    );
                }

                foreach ($qb->execute() as $product) {
                    try {
                        $this->updateProduct($product, $language->getId());
                    } catch (Exception $e) {
                        exception_handler($e);
                    }
                }
            }

            $this->updateVisibility(false, $productIds);

            $this->deleteDelayedUpdate($product_list);
        }

        // Update models
        if ($model_list = $this->getDelayedUpdate('model')) {
            $modelIds = array_keys($model_list);
            $this->updateVisibility($modelIds, null);

            $qb = sqlQueryBuilder()
                ->select(
                    'abm.id_abra as id, s.name AS section_name, pr.name AS producer_name, abs.name AS serie_name,
                     abcm.name AS complement_model_name, abs.sex, abm.id_section, pr.id AS id_producer,
                     abs.id_abra AS id_serie, abm.descr AS long_descr'
                )
                ->from('abra_models', 'abm')
                ->leftJoin('abm', 'abra_sections', 'absec', 'absec.id_abra = abm.id_section')
                ->leftJoin('absec', 'sections', 's', 's.id = absec.id_section')
                ->leftJoin('abm', 'abra_series', 'abs', 'abs.id_abra = abm.id_serie')
                ->leftJoin('abm', 'abra_complement_models', 'abcm', 'abcm.id_abra = abm.id_complement_model')
                ->leftJoin('abs', 'producers', 'pr', 'pr.id = abs.id_producer')
                ->andWhere(\Query\Operator::inIntArray($modelIds, 'abm.id_abra'))
                ->groupBy('abm.id_abra');

            foreach ($qb->execute() as $model) {
                try {
                    $this->updateModel($model);
                } catch (Exception $e) {
                    exception_handler($e);
                }
            }

            $this->updateVisibility($modelIds, false);

            $this->deleteDelayedUpdate($model_list);
        }
    }

    public function updatePhotosProductsRelation($abraProductID, $kupshopProductID)
    {
        $apRows = sqlQueryBuilder()
            ->select('ap.*, ppr.id_photo AS ppr_id_photo, ppr.show_in_lead AS show_in_lead')->from('abra_pictures', 'ap')
            ->leftJoin('ap', 'photos_products_relation', 'ppr', 'ppr.id_photo = ap.id_picture AND ppr.id_product=:product_id')
            ->setParameter('product_id', $kupshopProductID)
            ->where(\Query\Operator::equals(['ap.id_abra_product' => $abraProductID]))
            ->andWhere('ap.id_picture IS NOT NULL AND ap.type IS NOT NULL')
            ->execute();

        $cotyAbraPictures = [];
        $lorealAbraPictures = [];
        $smallPartnersVip = [];
        $otherAbraPictures = [];
        $cotyMain = null;
        $lorealMain = null;
        $smallPartnersVipMain = null;
        foreach ($apRows as $ap) {
            if ($ap['type'] == 21) {
                $cotyMain = $ap;
            }
            if ($ap['type'] == 31) {
                $lorealMain = $ap;
            }
            if ($ap['type'] == 51) {
                $smallPartnersVipMain = $ap;
            }
            if ($ap['type'] >= 21 && $ap['type'] <= 30) {
                $cotyAbraPictures[] = $ap;
            } elseif ($ap['type'] >= 31 && $ap['type'] <= 40) {
                $lorealAbraPictures[] = $ap;
            } elseif ($ap['type'] >= 51 && $ap['type'] <= 60) {
                $smallPartnersVip[] = $ap;
            } else {
                $otherAbraPictures[] = $ap;
            }
        }
        if (count($cotyAbraPictures) || count($lorealAbraPictures) || count($smallPartnersVip)) {
            $forcedOtherPictures = [];
            // other pictures must NOT be displayed when any COTY/loreal/small_partners_vip picture is present
            $IDs = array_map(
                function ($val) { return $val['id_picture']; },
                array_filter($otherAbraPictures, function ($val) use (&$forcedOtherPictures, &$cotyAbraPictures, &$lorealAbraPictures, &$smallPartnersVip,
                    &$cotyMain, &$lorealMain, &$smallPartnersVipMain
                ) {
                    if (in_array($val['type'], [5])) {
                        // force displaying shade (type 5) even with COTY/loreal/small_partners_vip pictures present
                        $forcedOtherPictures[] = $val;

                        return false;
                    } elseif ($val['type'] == 2 && (
                        (count($cotyAbraPictures) && is_null($cotyMain))
                        || (count($lorealAbraPictures) && is_null($lorealMain))
                        || (count($smallPartnersVip) && is_null($smallPartnersVipMain))
                    )) {
                        // force display type 2 when 31/21/51 is missing but other loreal/coty/small_partners_vip pictures are present (#9774)
                        $forcedOtherPictures[] = $val;

                        return false;
                    }

                    return !empty($val['ppr_id_photo']);
                })
            );
            if (count($IDs)) {
                sqlQueryBuilder()->delete('photos_products_relation')
                    ->where(\Query\Operator::inStringArray($IDs, 'id_photo'))
                    ->andWhere(\Query\Operator::equals(['id_product' => $kupshopProductID]))
                    ->execute();
            }
            $toInsert = array_merge($cotyAbraPictures, $lorealAbraPictures, $smallPartnersVip, $forcedOtherPictures);
        } else {
            $toInsert = $otherAbraPictures;
        }
        foreach ($toInsert as $photo) {
            $type = static::$PICTURE_TYPES[$photo['type']] ?? static::$PICTURE_TYPES['default'] ?? 'ignore';
            if (!empty($photo['ppr_id_photo']) && $photo['show_in_lead'] !== $type && $type !== 'ignore' && $type !== 'nothing') {
                // for existing relations update show_in_lead if it doesn't match
                sqlQuery(
                    'UPDATE photos_products_relation SET show_in_lead = :new_show_in_lead
                        WHERE id_photo=:id_photo AND id_product=:id_product AND show_in_lead=:show_in_lead',
                    ['id_photo' => $photo['id_picture'], 'id_product' => $kupshopProductID, 'show_in_lead' => $photo['show_in_lead'], 'new_show_in_lead' => $type]
                );
            } elseif ($type === 'ignore' || $type == 'nothing' || !empty($photo['ppr_id_photo'])) {
                continue;
            }
            sqlQuery("INSERT IGNORE INTO photos_products_relation (id_photo, id_product, id_variation, show_in_lead, active)
                       VALUES (:id_picture, :id_product , :id_variation, :show_in_lead , 'Y')",
                ['id_picture' => $photo['id_picture'], 'id_product' => $kupshopProductID, 'show_in_lead' => $type, 'id_variation' => null]);
        }
        // coty main photo takes precedence when loreal main photo is also present #9669
        $mainPicturesToMakeSecondary = [];
        if (isset($cotyMain) && (isset($lorealMain) || isset($smallPartnersVipMain))) {
            if (isset($lorealMain)) {
                $mainPicturesToMakeSecondary[] = $lorealMain['id_picture'];
            }
            if (isset($smallPartnersVipMain)) {
                $mainPicturesToMakeSecondary[] = $smallPartnersVipMain['id_picture'];
            }
        } elseif (isset($lorealMain) && isset($smallPartnersVipMain)) {
            // otherwise Loreal main photo takes precedence
            if (isset($smallPartnersVipMain)) {
                $mainPicturesToMakeSecondary[] = $smallPartnersVipMain['id_picture'];
            }
        }
        if (count($mainPicturesToMakeSecondary)) {
            sqlQueryBuilder()->update('photos_products_relation')
                ->set('show_in_lead', "'V'")
                ->where(\Query\Operator::equals([
                    'id_product' => $kupshopProductID,
                    'id_variation' => null,
                    'show_in_lead' => 'Y',
                ]))
                ->andWhere(\Query\Operator::inStringArray($mainPicturesToMakeSecondary, 'id_photo')) // column_name in ppr is id_photo not id_picture
                ->execute();
        }
    }

    public function updateProduct($product, $language = null)
    {
        if (empty($product['id_serie']) || empty($product['id_section'])) {
            // Incomplete product, suspend
            $this->updateSQL('products', ['id_model' => null], ['id' => $product['id']]);

            return false;
        } else {
            $id_model = returnSQLResult('SELECT am.id_abra
                FROM abra_models am
                WHERE '.$this->createWhere([
                'am.id_serie' => $product['id_serie'],
                'am.id_section' => $product['id_section'],
                'am.id_complement_model' => $product['id_complement_model'],
            ]).' ORDER BY am.id_abra', [
                'id_serie' => $product['id_serie'],
                'id_section' => $product['id_section'],
                'id_complement_model' => $product['id_complement_model'],
            ]);
        }

        if (!$id_model) {
            // No model found
            $this->updateSQL('products', ['id_model' => null], ['id' => $product['id']]);

            return false;
        }

        // Insert photos
        $this->updatePhotosProductsRelation($product['code'], $product['id']);

        // Update product title
        $flags = explodeFlags($product['campaign']);
        $campaign = $this->getCampaignsNames(
            array_filter($flags, function ($k) {
                return in_array($k, ['V', 'T', 'O', 'S']);
            }, ARRAY_FILTER_USE_KEY)
        );

        // Update flag SET when X_Constitution is not empty and X_Set is empty #9969
        $setFlag = 'SET';
        $setValue = !empty($product['set']) && !($flags['S'] ?? false);
        if ($setValue != (bool) ($flags['SET'] ?? false)) {
            $setData = ['id_product' => $product['id'], 'flag' => $setFlag];
            $this->updateProductFlag($setData, $setValue);
        }

        $modelTitle = $this->generateModelTitle($product)['title'];

        // Doplnek typu produktu + Name2 + velikost + odstín + poškození + (vzorek/odstřik/tester/kazeta)
        $attributes = [
            $product['complement_type_name'],
            $product['name2'],
            $product['size'],
            $product['shade'],
            $product['damage_name'],
            join(' ', $campaign),
        ];

        $title = join(' ', array_filter($attributes));
        $title = join(' ', [$modelTitle, $title]);

        if ($product['id_model'] != $id_model) {
            // Update product photo
            $this->scheduleProductTitleUpdate($product['id']);
            $this->scheduleProductTitleUpdate(null, null, null, null, null, $id_model);
        }

        $this->checkLeadProductPhoto($product['id']);

        // update product section
        $id_sections = $this->getSectionIDs($product['id_section'], $product['sex']);
        $this->deleteSQL('products_in_sections', ['id_product' => $product['id'], 'deletable' => 1]);
        foreach ($id_sections as $id_section) {
            sqlQuery(
                'REPLACE INTO products_in_sections (id_product, id_section, deletable)
                      VALUES (:id_product, :id_section, :deletable)',
                ['id_product' => $product['id'], 'id_section' => $id_section, 'deletable' => 1]
            );
        }

        // update secondary sections (assortment groups)
        if (!empty($product['sex'])) { // sex is required to properly assign section
            $this->deleteSQL('products_in_sections', ['id_product' => $product['id'], 'deletable' => -1]);
            $secondarySections = sqlQueryBuilder()->select('id_abra_section')->from('abra_secondary_assortment_group')
                ->where(Operator::equals(['id_product' => $product['id']]))
                ->andWhere("id_abra_section != '0'")->orderBy('id_abra');
            foreach ($secondarySections->execute() as $secondarySection) {
                $sectionIDs = $this->getSectionIDs($secondarySection['id_abra_section'], $product['sex']);
                foreach ($sectionIDs as $sectionID) {
                    sqlQuery('INSERT IGNORE INTO products_in_sections SET id_product=:id_product, id_section=:id_section, deletable=:deletable', [
                        'id_product' => $product['id'],
                        'id_section' => $sectionID,
                        'deletable' => -1, // set lower ordering priority for secondary sections
                    ]);
                }
            }
        }

        // update category parameter
        if ($product['id_section']) {
            if ($valueListID = $this->selectSQL('abra_sections', ['id_abra' => $product['id_section']], ['id_parameters_list'])->fetchColumn()) {
                $data = ['id_parameter' => self::CATEGORY_PARAMETER_ID, 'id_product' => $product['id']];
                $this->deleteSQL('parameters_products', $data);
                $data['value_list'] = $valueListID;
                $this->insertSQL('parameters_products', $data);
            }

            // update main category parameter
            if ($sectionId = $this->selectSQL('abra_sections', ['id_abra' => $product['id_section']], ['id_section'])->fetchColumn()) {
                // check parameter exists
                if (!$this->selectSQL('parameters', ['id' => self::MAIN_CATEGORY_PARAMETER_ID])->fetch()) {
                    $this->insertSQL('parameters', [
                        'id' => self::MAIN_CATEGORY_PARAMETER_ID,
                        'name' => 'Hlavní kategorie',
                        'value_type' => 'list',
                    ]);
                }

                // find top section
                if ($section = ServiceContainer::getService(SectionTree::class)->get()->getSectionById($sectionId)) {
                    $topSection = null;
                    // get first section that have record in abra_sections
                    foreach ($section->getParents() as $parent) {
                        if ($this->selectSQL('abra_sections', ['id_section' => $parent->getId()])->fetch()) {
                            $topSection = $parent;
                            break;
                        }
                    }

                    if ($topSection) {
                        if (!($valueListID = $this->selectSQL('parameters_list', ['id_parameter' => self::MAIN_CATEGORY_PARAMETER_ID, 'value' => $topSection->getName()], ['id'])->fetchColumn())) {
                            $this->insertSQL(
                                'parameters_list',
                                [
                                    'id_parameter' => self::MAIN_CATEGORY_PARAMETER_ID,
                                    'value' => $topSection->getName(),
                                ]
                            );
                            $valueListID = sqlInsertId();
                        }

                        $data = ['id_parameter' => self::MAIN_CATEGORY_PARAMETER_ID, 'id_product' => $product['id']];
                        $this->deleteSQL('parameters_products', $data);
                        $data['value_list'] = $valueListID;
                        $this->insertSQL('parameters_products', $data);

                        if ($language !== null && ($language != $this->languageContext->getDefaultId())) {
                            if ($sectionTranslatedName = $this->selectSQL('sections_translations', ['id_section' => $topSection->getId(), 'id_language' => $language], ['name'])->fetchColumn()) {
                                $this->saveTranslationObject(ParametersListTranslation::class, ['id_object' => $valueListID, 'id_language' => $language], ['value' => $sectionTranslatedName]);
                            }
                        }
                    }
                }
            }
        }

        // check for new product
        if (empty($product['id_model']) && $id_model && $product['newly_added']) {
            $productObj = new Product();
            $productObj->createFromDB($product['id']);
            $eventDispatcher = ServiceContainer::getService('event_dispatcher');
            $event = new ProductEvent($productObj);
            $eventDispatcher->dispatch($event, ProductEvent::PRODUCT_CREATED);
            // remove newly_added from product
            sqlQueryBuilder()->update('products')
                ->set('newly_added', 0)
                ->where(Operator::equals(['id' => $product['id']]))
                ->execute();
        }

        $updateTitle = true;
        // neaktualizovat title u darku - aktualizovat ho pouze jednou pri vytvoreni
        if (isset($flags['G']) && !empty($product['title']) && $product['title'] != $product['code']) {
            $updateTitle = false;
        }

        if ($language === null || ($language == $this->languageContext->getDefaultId())) {
            $update = ['id_model' => $id_model, 'id_serie' => $product['id_serie_kupshop']];
            if ($updateTitle) {
                $update['title'] = $title;
            }
            if (!empty($product['id_producer'])) {
                $update['producer'] = $product['id_producer'];
            }

            $this->updateSQL('products', $update, ['id' => $product['id']]);
        } else {
            if ($updateTitle) {
                $this->saveTranslationObject(ProductsTranslation::class, ['id_object' => $product['id'], 'id_language' => $language], ['title' => $title]);
            }
        }

        return true;
    }

    public function hideIncompleteProducts()
    {
        // Skryt vyrobce bez viditelnych produktu
        $SQL = sqlQuery('UPDATE producers pr
                SET active = IF((SELECT COUNT(*) FROM products WHERE figure = "Y" AND producer = pr.id) > 0, "Y", "N")');

        if (sqlAffectedRows($SQL) > 0) {
            var_dump(['Skryto/Zobrazeno výrobců s/bez viditelných produktů', sqlAffectedRows($SQL)]);
        }
    }

    public static $sections;

    public function sync_section($code, $field, $value)
    {
        $ids = $this->getMappingFromAbraMultiple('section', $code);

        if (!$ids) {
            // Create Sections
            $vat_id = null;
            getVat($vat_id);

            $parents = [1, 2, 4];

            // Do bytu
            if ($code == 'EL00000101' || $code == 'DL00000101') {
                $parents = [3];
            }

            foreach ($parents as $parent) {
                $this->insertSQL('sections', [
                    'name' => $value,
                    'behaviour' => 2,
                    'figure' => 'Y',
                    'orderby' => 'sell',
                    'orderdir' => 'DESC',
                ]);
                $id = sqlInsertId();

                if (!$this->selectSQL('sections', ['id' => $parent])->fetch()) {
                    $parent = null;
                }

                $this->insertSQL('sections_relation', ['id_section' => $id, 'id_topsection' => $parent, 'position' => 999]);

                $this->setMappingFromAbra('section', $id, $code);
                $ids[] = $id;
            }
        }

        $column = static::$SECTION_FIELDS[$field];

        if ($this->isTranslationsSync()) {
            if (!$this->translationFieldSupported($field)) {
                return;
            }

            switch ($column) {
                case 'descr':
                    $blocks = ServiceContainer::getService(\KupShop\I18nBundle\Translations\BlocksTranslation::class);
                    foreach ($ids as $id) {
                        $idRootBlock = $this->selectSQL('sections', ['id' => $id], ['id_block'])->fetchColumn();
                        if ($idRootBlock) {
                            $objectID = $this->selectSQL('blocks', ['id_parent' => $idRootBlock, 'id_root' => $idRootBlock], ['id'])->fetchColumn();
                        } else {
                            // create empty block
                            $service = $this->getBlockUtil();
                            $objectID = $service->insertFirstBlock('sections', $id, '');
                        }

                        if ($objectID) {
                            $blocks->saveSingleObject($this->lang, $objectID, ['content' => $value]);
                        }
                    }
                    break;
                case 'name':
                    foreach ($ids as $id) {
                        $this->saveTranslationObject(SectionsTranslation::class, ['id_object' => $id], ['name' => $value]);

                        if ($valueListID = $this->selectSQL('abra_sections', ['id_abra' => $code], ['id_parameters_list'])->fetchColumn()) {
                            $this->saveTranslationObject(ParametersListTranslation::class, ['id_object' => $valueListID], ['value' => $value]);
                        }
                    }
                    break;
                default:
                    foreach ($ids as $id) {
                        $this->saveTranslationObject(SectionsTranslation::class, ['id_object' => $id], [$column => $value]);
                    }
                    break;
            }

            foreach ($ids as $index => $id) {
                $this->scheduleProductTitleUpdate(null, null, null, null, $id);
            }

            return;
        }

        if (!static::$sections) {
            static::$sections = ServiceContainer::getService(SectionTree::class);
        }

        switch ($column) {
            case 'parent_id':
                $parent_ids = $this->getMappingFromAbraMultiple('section', $value);
                if (!$parent_ids) {
                    if ($value > 0) {
                        echo 'Unknown section ID: '.$value;
                    }
                    // throw new Exception('Unknown section ID: '.$value);

                    break;
                }

                // Do bytu
                if ($value == 'EL00000101' || $code == 'DL00000101') {
                    $parent_ids = [3];
                }

                foreach ($ids as $index => $id) {
                    if (empty($parent_ids[$index])) {
                        $this->deleteSQL('sections', ['id' => $id]);
                        continue;
                    }

                    $this->updateSQL('sections_relation', ['id_section' => $id, 'id_topsection' => $parent_ids[$index]], ['id_section' => $id]);
                }

                return;

            case 'position':
                sqlQuery('UPDATE sections_relation
                     SET position=:position
                     WHERE id_section IN (:ids)', ['ids' => $ids, 'position' => $value], ['ids' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);

                return;

            case 'plural':
                if (empty($value)) {
                    return;
                }

                foreach ($ids as $index => $id) {
                    $section = static::$sections->getSectionById($id);
                    $map = [
                        '1' => 'pro ženy',
                        '2' => 'pro muže',
                        // '3' => "do bytu",
                        '4' => 'pro děti',
                    ];

                    if (empty($section['parents'])) {
                        continue;
                    }

                    $index = key($section['parents']);

                    $plural = $value;
                    if (!empty($map[$index])) {
                        $plural .= ' '.translate($map[$index], 'sections');
                    }

                    $this->updateSQL('sections', ['plural' => $plural], ['id' => $id]);
                }
                break;

            case 'ignore':
                return;

            case 'descr':
                $service = $this->getBlockUtil();
                foreach ($ids as $index => $id) {
                    $service->insertFirstBlock('sections', $id, $value);
                }

                return;

            case 'figure':
                $value = $this->parseBool($value);
                $value = $value ? 'Y' : 'N';
                foreach ($ids as $index => $id) {
                    $this->updateSQL('sections', [$column => $value], ['id' => $id]);
                }
                break;

            case 'delete':
                foreach ($ids as $index => $id) {
                    $this->deleteSQL('sections', ['id' => $id]);
                }

                return;
            case 'name':
                // check if category parameter exists
                if (!$this->selectSQL('parameters', ['id' => self::CATEGORY_PARAMETER_ID])->fetch()) {
                    $this->insertSQL('parameters', [
                        'id' => self::CATEGORY_PARAMETER_ID,
                        'value_type' => 'list',
                        'position' => 0,
                    ]);
                }

                foreach ($ids as $index => $id) {
                    $this->updateSQL('sections', ['name' => $value], ['id' => $id]);

                    // update category parameter
                    if (!empty($value)) { // je nesmysl ukladat prazdnou hodnotu
                        if (!($valueListID = $this->selectSQL('abra_sections', ['id_abra' => $code], ['id_parameters_list'])->fetchColumn())) {
                            if (!($valueListID = $this->selectSQL('parameters_list', ['id_parameter' => self::CATEGORY_PARAMETER_ID, 'value' => $value], ['id'])->fetchColumn())) {
                                $this->insertSQL(
                                    'parameters_list',
                                    [
                                        'id_parameter' => self::CATEGORY_PARAMETER_ID,
                                        'value' => $value,
                                    ]
                                );
                                $valueListID = sqlInsertId();
                            }
                            $this->updateSQL('abra_sections', ['id_parameters_list' => $valueListID], ['id_abra' => $code]);
                        } else {
                            $this->updateSQL('parameters_list', ['value' => $value], ['id' => $valueListID]);
                        }
                    }
                }
                break;

            case 'replacement':
                $ids_new = $this->getMappingFromAbraMultiple('section', $value);
                if ($ids && $ids_new) {
                    foreach ($ids as $key => $id) {
                        $id_new = $ids_new[$key] ?? null;
                        if ($id_new) {
                            $this->createRewrite(
                                \KupShop\ElninoBundle\Util\Rewrite::TYPE_CATEGORY,
                                $id_new,
                                [
                                    's' => 'category',
                                    'IDcat' => $id,
                                ]
                            );
                        }
                    }
                }
                break;

            case null:
                var_dump('Section: Nedefinovaná položka synchronizace: '.$field);

                return;

            default:
                foreach ($ids as $index => $id) {
                    $this->updateSQL('sections', [$column => $value], ['id' => $id]);
                }
        }

        foreach ($ids as $index => $id) {
            $this->scheduleProductTitleUpdate(null, null, null, null, $id);
        }
    }

    public function getSectionIDs($abra_id, $sex)
    {
        if (!$abra_id) {
            return null;
        }

        $sections = sqlFetchAll(sqlQuery('SELECT abs.id_section AS id, sr.id_topsection
                  FROM abra_sections  abs
                  LEFT JOIN sections_relation sr ON sr.id_section = abs.id_section
                  WHERE id_abra=:code
                  ORDER BY abs.id_section',
            ['code' => $abra_id]
        ));

        if (empty($sections)) {
            var_dump('unknown section: '.$abra_id);

            return null;
        }

        // Return "do bytu"
        foreach ($sections as $section) {
            if ($section['id_topsection'] == '3') {
                return [$section['id']];
            }
        }

        $return = null;

        if ($sex == 'W') {
            $return = [$sections[0]];
        } elseif ($sex == 'M') {
            if (count($sections) < 2) {
                throw new Exception('Malo sekci?');
            }

            $return = [$sections[1]];
        } elseif ($sex == 'K') {
            if (count($sections) > 2) {
                $return = [$sections[2]];
            } elseif (count($sections) == 2) {
                $this->insertSQL('sections', ['name' => $sections['id'], 'behaviour' => 2, 'figure' => 'Y']);
                $this->setMappingFromAbra('section', sqlInsertId(), $abra_id);
            } else {
                throw new Exception('Malo sekci?');
            }
        } elseif ($sex == 'U' || $sex == '' || $sex == 'B') {
            if (count($sections) < 2) {
                throw new Exception('Malo sekci?');
            }

            $return = array_slice($sections, 0, 2);
        } else {
            throw new Exception('wtf??');
        }

        return array_map(function ($x) {
            return $x['id'];
        }, $return);
    }

    /************************* ORDERS *****************************/

    public function syncOrder($id)
    {
        // Ignore order for now if sync failed
        $cacheId = 'order-sync-failed-'.$id;

        if (getCache($cacheId)) {
            return;
        }

        try {
            AbraBase::syncOrder($id);
        } catch (RuntimeException $e) {
            // Wait 2 minutes before sync retry
            setCache($cacheId, $id, 120);

            throw $e;
        }

        // Pokud by se stala chyba, vylitla by exception. Tady vím, že je to OK
        $order = new Order();
        $order->createFromDB($id);
        $order->logHistory(translate('abra_order_send', 'abra'));
        $order->clearModified();
    }

    public function getOrder($id)
    {
        $order = new Order();
        $order->createFromDB($id, true, true, true);

        return $this->contextManager->activateOrder($order, function () use ($order) {
            $delivery = null;
            $payment = null;
            $department = null;
            $forcedDeliveryAdName = null;
            $CH_Recipient = null; // CH_Recipient -> X_sRecipient
            if (findModule('eshop_delivery')) {
                $delivery_type = $order->getDeliveryType($order->getDeliveryId());
                $delivery = $this->getMappingToAbra('delivery', $delivery_type->id_delivery) ?: null;
                $payment = $this->getMappingToAbra('payment', $delivery_type->id_payment) ?: null;
                if ($order->total_price->isZero()) {
                    $payment = 'DP';
                }

                $deliveryClass = $delivery_type->getDelivery();
                if ($deliveryClass instanceof BalikDoRuky) {
                    $department = 'A';
                } elseif ($deliveryClass instanceof Zasilkovna) {
                    $department = $deliveryClass->getInfo()['labelRouting'];
                } elseif ($deliveryClass instanceof PPLParcelShop) {
                    $deliveryInfo = $deliveryClass->getInfo();
                    $department = $deliveryInfo['code'] ?? $deliveryInfo['id'];
                    $forcedDeliveryAdName = $deliveryInfo['name'];
                    $CH_Recipient = 'Parcelshop '.$deliveryInfo['id']; // #9769
                } elseif ($deliveryClass instanceof SPSParcelShop) {
                    $deliveryInfo = $deliveryClass->getInfo();
                    $department = $deliveryInfo['id'];
                    $forcedDeliveryAdName = $deliveryInfo['name'];
                    $CH_Recipient = 'Parcelshop '.$deliveryInfo['id']; // #9769
                }

                $paymentClass = $delivery_type->getPayment();
                if ($paymentClass->getPayMethod() == \Payment::METHOD_COD) {
                    $currencyContext = \KupShop\KupShopBundle\Util\Contexts::get(\KupShop\KupShopBundle\Context\CurrencyContext::class);
                    $order->total_price = roundPrice($order->total_price, currency: $currencyContext->getOrDefault($order->getCurrency()));
                }
            }

            $user_abra_id = getVal('elnino_abra_id',
                sqlFetchAssoc($this->selectSQL('users', ['id' => $order->id_user], ['elnino_abra_id'])), 519194);

            if ($this->settings['web'] == 'perfumy-hurtownia.pl') {
                $order->total_price = roundPrice($order->total_price);
            }

            $sync_order = [
                // Header
                'WEBsource' => $this->settings['web'],
                'DocumentNumber' => $order->order_no,
                'WEBnote' => $this->prepareTextForOrder($order->note_user),
                'Currency' => $order->currency,
                'PaymentCode' => $payment,
                'TransportCode' => $delivery,
                'Amount' => $this->preparePriceToAbra($order->total_price),

                'Department' => $department,
                'Points' => null,
                'SMS' => 'A',
                'DocumentID' => $order->id,
                'Status' => 0,
                'CustomerServiceNote' => null,

                // Customer
                'Code' => $user_abra_id,
                'Email' => $this->prepareFieldLengthForOrder($order->invoice_email, 100),
                'PhoneNumber' => $this->prepareFieldLengthForOrder(!empty($order->delivery_phone) ? $order->delivery_phone : $order->invoice_phone, 30),

                // Delivery address
                'adName' => $this->prepareFieldLengthForOrder(!empty($forcedDeliveryAdName) ? $forcedDeliveryAdName : (!empty($order->delivery_firm) ? $order->delivery_firm : $order->delivery_custom_address), 50),
                'adFirstName' => $this->prepareFieldLengthForOrder($order->delivery_name, 50),
                'adLastName' => $this->prepareFieldLengthForOrder($order->delivery_surname, 50),
                'adStreet' => $this->prepareFieldLengthForOrder($order->delivery_street, 50),
                'adCity' => $this->prepareFieldLengthForOrder($order->delivery_city, 50),
                'adPostCode' => $this->prepareFieldLengthForOrder($order->delivery_zip, 10),
                'adCountryCode' => $this->prepareFieldLengthForOrder($order->invoice_country, 3),

                // Invoice address
                'amName' => $this->prepareFieldLengthForOrder(!empty($order->invoice_firm) ? $order->invoice_firm : $order->invoice_custom_address, 50),
                'amFirstName' => $this->prepareFieldLengthForOrder($order->invoice_name, 50),
                'amLastName' => $this->prepareFieldLengthForOrder($order->invoice_surname, 50),
                'amStreet' => $this->prepareFieldLengthForOrder($order->invoice_street, 50),
                'amCity' => $this->prepareFieldLengthForOrder($order->invoice_city, 50),
                'amPostCode' => $this->prepareFieldLengthForOrder($order->invoice_zip, 10),

                'OrgIdentNumber' => $this->prepareFieldLengthForOrder($order->invoice_ico, 15),
                'VATIdentNumber' => $this->prepareFieldLengthForOrder($order->invoice_dic, 20),
                'CH_Kanton' => null,
                'CH_Recipient' => $this->prepareFieldLengthForOrder(!empty($CH_Recipient) ? $CH_Recipient : $order->invoice_state, 60),

                'Rows' => $this->getOrderRows($order),
            ];

            if ($order->id_language != $this->languageContext->getDefaultId()) {
                $sync_order['CH_Kanton'] = $order->id_language;
            }

            if ($order->hasSameAddress()) {
                $empty_af = [
                    'amName' => null,
                    'amFirstName' => null,
                    'amLastName' => null,
                    'amStreet' => null,
                    'amCity' => null,
                    'amPostCode' => null,
                ];

                $sync_order = array_merge($sync_order, $empty_af);
            }

            return $sync_order;
        });
    }

    public function sync_order($code, $field, $value, $value2)
    {
        if (!isset(static::$ORDER_FIELDS[$field])) {
            var_dump('Nedefinovaná položka synchronizace:', $field, $value, $value2);

            return;
        }

        $column = static::$ORDER_FIELDS[$field];
        $product_code = null;

        if ($column == 'quantity' || $column == 'price') {
            [$code, $product_code] = explode('~', $code);
        }

        $id = $this->getMappingFromAbra('order', $code);

        if (!$id) {
            var_dump("Neznámá objednávka: {$code}");

            return false;
        }

        $value = trim($value);
        $value2 = trim($value2);

        $order = new Order();
        $order->createFromDB($id);

        return $this->sync_order_switch($value, $column, $order, $id, $product_code, $value2);
    }

    public function getPackageSentMessage(Delivery $delivery)
    {
        foreach ($this->package_sent_messages as $delivery_class => $message) {
            if ($delivery instanceof $delivery_class) {
                return $message;
            }
        }

        return null;

        throw new Exception('Nezmámý způsbob doručení balíku: '.$delivery->id);
    }

    protected function sync_order_switch($value, $column, Order $order, $id, $product_code, $value2 = null)
    {
        switch ($column) {
            case 'status':
                if ($value == 6) {
                    $order->setData('storno_from_abra', true);
                    $order->storno(false, 'orderAbraCancelled');

                    return true;
                }

                // Package Sent
                if ($value == 10) {
                    if (!$order->package_id) {
                        $order->logHistory('Missing package number!');
                    }

                    $SQL = sqlQueryBuilder()->select('id')->from('orders_history')
                        ->where(Operator::equals(['id_order' => $order->id, 'notified' => 1, 'id_status' => 10]));

                    if ($SQL->execute()->rowCount() > 0) {
                        // already sent
                        $order->changeStatus($value);

                        return true;
                    }

                    if ($message = $this->getPackageSentMessage($order->getDeliveryType()->getDelivery())) {
                        $order->changeStatus($value, null, true, $message);

                        return true;
                    }

                    return false;
                }

                // Ignore statuses > 7
                if ($value > 7) {
                    return false;
                }

                // Ignore status "new"
                if ($value <= 0) {
                    return false;
                }

                $msg = translate('abra_order_status_change', 'abra').' '.$value2;

                // Merge statuses with message
                $history = $order->getHistory(true);
                $lastMessage = end($history);

                if ($value == 2) {
                    if (strpos($lastMessage['comment'], translate('abra_order_error', 'abra')) !== false) {
                        $this->deleteSQL('orders_history', ['id' => $lastMessage['id']]);
                        $msg = $lastMessage['comment'];
                    }
                } elseif ($lastMessage['id_status'] == $value) {
                    return false;
                }

                // Disable status change from done/cancel
                if ($order->status >= 6 && $value < 6) {
                    $value = $order->status;
                }

                $order->changeStatus($value, $msg, false);
                break;

            case 'note_expedition':
                $noteExpedition = translate('abra_order_error', 'abra');
                $order->logHistory("{$noteExpedition}:<br>{$value}");
                break;

            case 'note_service':
                // $order->logHistory("Problém opraven:<br>{$value}");
                break;

            case 'package_number':
                $orderData = $order->getDataAll(true);
                $orderData['packages_elnino'] = $orderData['packages_elnino'] ?? [];
                $assignToPackageSource = function ($packageID) use (&$orderData) {
                    if (str_starts_with($packageID, 'ZNZ_')) {
                        $orderData['packages_elnino']['znz'] = $packageID;
                    } else {
                        $orderData['packages_elnino']['elnino'] = $packageID;
                    }
                };
                if (empty($orderData['packages_elnino']) && !empty($orderData['packages'])) {
                    // legacy fallback
                    foreach ($orderData['packages'] as $packageID) {
                        $assignToPackageSource($packageID);
                    }
                }
                $orderData['packages'][$value] = $value;
                $assignToPackageSource($value);
                $order->setDataAll($orderData);
                $removePrefix = function ($str) {
                    $prefix = 'ZNZ_';
                    if (str_starts_with($str, $prefix)) {
                        $str = substr($str, strlen($prefix));
                    }

                    return $str;
                };
                $packageIDs = join(',', array_map($removePrefix, array_values($orderData['packages_elnino'])));
                $this->updateSQL('orders', ['package_id' => $packageIDs], ['id' => $id]);
                $order->package_id = $packageIDs;
                $order->logHistory("Číslo balíku: <b><a href=\"{ODKAZ_SLEDOVANI_BALIKU}\" target='_blank'>{$value}</a></b>");
                break;

            case 'quantity':
                $data = ['id_order' => $id, 'product_code' => $product_code];
                $items = sqlQuery(
                    'SELECT oi.id
                    FROM order_items oi
                    JOIN products_variations pv ON pv.id = oi.id_variation
                    WHERE id_order=:id_order AND pv.code=:product_code',
                    $data
                );

                if (sqlNumRows($items) != 1) {
                    return;
                }
                // logError(__FILE__, __LINE__, "Nelze najít změněnou položku objednávky: ".print_r($data, true));

                $item_id = sqlFetchAssoc($items)['id'];
                $order->updateItem($item_id, $value);

                break;

            case 'note_storno':
                $order->logHistory("Důvod stornování objednávky:<br><b>{$value}</b>");
                break;

            case 'ExternalOrder':
                $order->setData('Abra_ExternalOrder', $value);
                break;

            case 'ignore':
                break;

            default:
                throw new Exception('Nedefinovany sloupec: '.$column);
        }
    }

    public function sync_producer($code, $field, $value)
    {
        if ((static::$PRODUCER_FIELDS[$field] ?? false) === 'id_richcontent') {
            $id = $this->getMappingFromAbra('producer', $code);

            return $this->richContentUtil->handleSync('producers', $id, $value, $this->lang);
        } else {
            return parent::sync_producer($code, $field, $value);
        }
    }

    public function sync_serie($code, $field, $value)
    {
        if ((static::$SERIES_FIELDS[$field] ?? false) === 'id_richcontent') {
            $id = $this->getMappingFromAbra('serie', $code);

            return $this->richContentUtil->handleSync('abra_series', $id, $value, $this->lang);
        } else {
            return parent::sync_serie($code, $field, $value);
        }
    }

    public function updateOrder($id, $status, $message)
    {
        sqlGetConnection()->transactional(function () use ($id, $status, $message) {
            sqlQuery('SELECT COUNT(*) FROM abra_orders FOR UPDATE');

            $sync_order = $this->getOrder($id);

            $sync_order['CustomerServiceNote'] = $this->prepareFieldLengthForOrder($this->prepareTextForOrder($message), 200);
            $sync_order['Status'] = $status;

            $order = new Order();
            $order->createFromDB($id);
            $newOrderAbraId = false;

            $this->log(['Update order initiated', 'id' => $id, 'order' => $sync_order]);
            try {
                if ($order->isHardModified()) {
                    $this->log(['Update order full', 'id' => $id, 'order' => $sync_order]);
                    $newOrderAbraId = true;
                    $ret = $this->client->__call('weSetReceivedOrder', $sync_order);
                } else {
                    // soft modification
                    $this->log(['Update order soft', 'id' => $id, 'order' => $sync_order]);
                    $allowedFields = [
                        'WEBnote', 'CustomerServiceNote',
                        //                        'Department',
                        'Email', 'PhoneNumber',
                        'adName', 'adFirstName', 'adLastName', 'adStreet', 'adCity', 'adPostCode',
                        'amName', 'amFirstName', 'amLastName', 'amStreet', 'amCity', 'amPostCode',
                        'OrgIdentNumber', 'Code',
                        //                        'Status',
                        'CH_Recipient', // provincie pro parfimo.it
                    ];
                    if ($status >= 0) {
                        $allowedFields[] = 'Status';
                    }
                    $finalFields = [
                        'WEBsource' => $sync_order['WEBsource'],
                        'DocumentNumber' => $sync_order['DocumentNumber'],
                        'Rows' => [],
                    ];
                    foreach ($allowedFields as $allowedField) {
                        $finalFields['Rows'][$allowedField] = $allowedField.'~|~'.$sync_order[$allowedField];
                    }
                    $this->log(['Update order partial', 'fields' => $finalFields]);
                    $ret = $this->client->__call('weSetReceivedOrderSimpleUpdate', $finalFields);
                }
            } catch (Exception $e) {
                $this->log(['Update order', 'exception' => $e->getMessage()]);
                $this->printTrace();
                throw new AbraUpdateOrderException($e->getMessage(), $e->getCode(), $e);
            }

            if ($ret[0] == 'True') {
                $this->log(['Update order' => 'success', 'id' => $id, 'abra_id' => $ret[2]]);
                if ($newOrderAbraId) {
                    $this->deleteSQL('abra_orders', ['id_order' => $id]);
                    $this->setMappingFromAbra('order', $id, $ret[2]);
                }
            } else {
                $this->log(['Update order' => 'error', 'id' => $id, 'error' => $ret[1]]);
                $this->printTrace();
                throw new AbraUpdateOrderException("Order {$id} sync failed: {$ret[1]} - {$ret[2]}");
            }

            $order = new Order();
            $order->createFromDB($id);
            $order->clearModified();
        });
    }

    public function updateOrderStatuses()
    {
        // Authorize orders sent to Abra with online payment in status New
        $this->authorizeOrders(function (Query\QueryBuilder $qb) {
            $qb->andWhere("o.status = 0 AND (o.note_user = '' OR o.note_user IS NULL)")
                ->andWhere(\Query\Order::byPaymentMethods(['METHOD_COD']));
        }, translate_shop('order_authorized', 'abra'));

        // Authorize orders sent to Abra with online payment in status New
        $this->authorizeOrders(function (Query\QueryBuilder $qb) {
            $qb->andWhere("o.status = 0 AND o.status_payed = 1 AND (o.note_user = '' OR o.note_user IS NULL)")
                ->andWhere(\Query\Order::byPaymentMethods(['METHOD_ONLINE']));
        }, translate_shop('order_paid_authorized', 'abra'));
    }

    public function authorizeOrders($spec, $message)
    {
        $orders = sqlQueryBuilder()->select('o.id, o.modified')->from('orders', 'o')
            ->join('o', 'abra_orders', 'ao', 'ao.id_order = o.id')
            ->join('o', 'delivery_type', 'dt', 'dt.id = o.id_delivery')
            ->andWhere(\Query\Operator::not(\Query\Operator::findInSet(['D'], 'o.flags')))
            ->andWhere($spec)->execute();
        foreach ($orders as $order) {
            $this->log(['AbraKoza2 authorizeOrders', 'id' => $order['id'], 'order' => $order]);
            if ($order['modified']) {
                $this->updateOrder($order['id'], 1, translate_shop('order_fully_authorized', 'abra'));
                $order_obj = new Order($order['id']);
                $order_obj->createFromDB($order['id']);
                $order_obj->changeStatus(1, translate_shop('order_fully_authorized', 'abra'), false);
            } else {
                $this->changeOrderStatus($order['id'], 1, $message);
            }
        }
    }

    public function changeOrderStatus($id_order, $status, $comment, $service_note = null, $emailType = null)
    {
        try {
            $order = new Order();
            $order->createFromDB($id_order);

            $this->log(['Change order status', 'id' => $id_order, 'code' => $order->order_no, 'status' => $status, 'comment' => $comment]);

            // nevolat storno do abry pokud uz byla objednavka stornovana z abry
            if ($order->getData('storno_from_abra') !== true) {
                $ret = $this->client->__call('weSetAttribute2', [$this->settings['web'], 'ReceivedOrders', 'ExternalNumber', $order->order_no, 'X_Status', $status]);

                if ($service_note) {
                    $service_note = $this->prepareFieldLengthForOrder($this->prepareTextForOrder($service_note), 200);
                    $this->client->__call('weSetAttribute2', [$this->settings['web'], 'ReceivedOrders', 'ExternalNumber', $order->order_no, 'X_NoteCUSTOMERSERVICE', $service_note]);
                }

                if ($ret[0] != 'True') {
                    throw new Exception("{$ret[1]} - {$ret[2]}");
                }
            }

            $order->changeStatus($status, $comment, isset($emailType), null, $emailType);
        } catch (Exception $e) {
            $this->log(['Change order status', 'id' => $id_order, 'exception' => $e->getMessage()]);

            throw $e;
        }
    }

    public function preparePriceFromEUR($value)
    {
        return $this->preparePriceFromAbra($value);
    }

    protected function computeVariationPrice($id)
    {
    }

    public function checkLeadProductPhoto($id)
    {
        if (static::$pictureType6AsLead) {
            // UPDATE type 6 to 'Y' and corresponding type 2 to 'V' (original type 6 show_in_lead)
            sqlQuery("
                UPDATE photos_products_relation ppr_type2
                    INNER JOIN photos_products_relation ppr_type6 ON ppr_type6.id_product=ppr_type2.id_product AND ppr_type6.show_in_lead != 'Y'
                    INNER JOIN abra_pictures ap_type6 ON ap_type6.id_picture=ppr_type6.id_photo AND ap_type6.type = 6
                SET ppr_type2.show_in_lead = ppr_type6.show_in_lead,
                    ppr_type6.show_in_lead = ppr_type2.show_in_lead
                WHERE ppr_type2.id_product=:id_product AND ppr_type2.show_in_lead = 'Y';
            ", ['id_product' => $id]);
        }

        // check & update RefreshModelLeadPhotosScript
        // set main MODEL photo
        $photo = sqlFetchAssoc(sqlQuery("SELECT ppr.*, p.id_model
                        FROM products p
                        JOIN products mp ON mp.id_model=p.id_model
                        JOIN photos_products_relation ppr ON mp.id = ppr.id_product
                        WHERE p.id=:id_product AND mp.figure='Y' AND ppr.show_in_lead = 'Y'
                        ORDER BY mp.in_store > 0 DESC, 
                          FIND_IN_SET('T', mp.campaign), FIND_IN_SET('V', mp.campaign), FIND_IN_SET('O', mp.campaign),
                          FIND_IN_SET('P', mp.campaign), FIND_IN_SET('S', mp.campaign),
                          mp.id_complement_type IS NULL DESC,
                          mp.id_complement_type='3D40000101',
                          mp.size_value DESC, mp.price DESC LIMIT 1",
            ['id_product' => $id]));

        if ($photo && !empty($photo['id_model'])) {
            $this->updateSQL('abra_models', ['id_photo' => $photo['id_photo']], ['id_abra' => $photo['id_model']]);
        }
    }

    protected function updateFeedPricesByContentAPI($productIds)
    {
        $api = \KupShop\KupShopBundle\Util\Compat\ServiceContainer::getService(\KupShop\ElninoBundle\Util\ContentAPI\GoogleApi::class);
        if (!$api->isEnabled()) {
            return false;
        }
        $filterParams = new FilterParams();

        $items = sqlQueryBuilder()->select('p.id, p.price, p.in_store, v.vat, p.discount, p.vat as vat_id')
            ->from('products', 'p')
            ->join('p', 'vats', 'v', 'v.id = p.vat')
            ->where(Operator::inIntArray($productIds, 'p.id'))
            ->andWhere($filterParams->getSpec())
            ->groupBy('p.id')
            ->execute()->fetchAll();

        foreach ($items as $item) {
            $api->addEntry($item);
        }

        $api->sendBatch();
    }

    protected function refreshSupplierInStore(array $stores, array $activeStore, int $idProduct): void
    {
        if (!findModule(\Modules::PRODUCTS_SUPPLIERS)) {
            return; // skip products_of_suppliers refresh without products_suppliers module
        }
        if (!empty($activeStore['external_supplier'])) {
            $supplierInStore = $stores[$activeStore['store']] ?? 0;
        } else {
            $supplierInStore = 0;
        }
        $supplierProductIDArr = [
            'id_product' => $idProduct,
            'id_variation' => null,
            'id_supplier' => static::$EXTERNAL_SUPPLIER_ID,
            'code' => null,
        ];
        $supplierProduct = sqlQueryBuilder()->select('id')->from('products_of_suppliers')
            ->where(Operator::equalsNullable($supplierProductIDArr))
            ->setMaxResults(1)->execute()->fetchAssociative();
        if ($supplierProduct) {
            $this->updateSQL('products_of_suppliers', ['in_store' => $supplierInStore], ['id' => $supplierProduct['id']]);
        } elseif ($supplierInStore > 0) {
            $supplierProductIDArr['in_store'] = $supplierInStore;
            $this->insertSQL('products_of_suppliers', $supplierProductIDArr);
        }
    }

    protected function refreshPartnerFlag(array $activeStore, array $product): void
    {
        $campaign = null;
        if (empty($activeStore['external_supplier']) && $product['is_partner'] >= 1) {
            $campaign = "REMOVE_FROM_SET('PARTNER', campaign)";
        } elseif (static::isExternalPartnerSupplierEnabled() && ($activeStore['external_supplier'] ?? false) && $product['is_partner'] <= 0) {
            $campaign = "ADD_TO_SET('PARTNER', campaign)";
        }
        if (!empty($campaign)) {
            sqlQueryBuilder()->update('products')->set('campaign', $campaign)
                ->where(Operator::equals(['id' => $product['id']]))
                ->execute();
        }
    }

    public static function isExternalPartnerSupplierEnabled(): bool
    {
        static $enabled = null;
        if (is_null($enabled)) {
            $enabled = false;
            foreach (static::$STORES_HIERARCHY as $store) {
                if (!empty($store['external_supplier'])) {
                    $enabled = true;
                    break;
                }
            }
        }

        return $enabled;
    }
}

class AbraUpdateOrderException extends Exception
{
}
