<?php

use KupShop\BonusProgramBundle\Utils\BonusComputer;
use KupShop\CatalogBundle\ProductList\FilterParams;
use KupShop\CatalogBundle\ProductList\MultiFetch;
use KupShop\CatalogBundle\ProductList\ProductCollection;
use KupShop\CatalogBundle\Query\RelatedProducts;
use KupShop\CatalogBundle\Util\ProductAvailabilityUtil;
use KupShop\I18nBundle\Translations\PhotosTranslation;
use KupShop\I18nBundle\Translations\ProductsUnitsTranslation;
use KupShop\I18nBundle\Util\PriceConverter;
use KupShop\KupShopBundle\Config;
use KupShop\KupShopBundle\Context\CurrencyContext;
use KupShop\KupShopBundle\Context\PosContext;
use KupShop\KupShopBundle\Context\PriceLevelContext;
use KupShop\KupShopBundle\Context\VatContext;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\KupShopBundle\Util\Price\Price;
use KupShop\KupShopBundle\Util\Price\PriceLevelPrice;
use KupShop\KupShopBundle\Util\Price\PriceUtil;
use KupShop\KupShopBundle\Util\Price\ProductPrice;
use KupShop\KupShopBundle\Util\Suppliers\SuppliersUtil;
use KupShop\KupShopBundle\Wrapper\PriceWrapper;
use KupShop\OrderingBundle\Event\OrderItemEvent;
use KupShop\PricelistBundle\Context\PricelistContext;
use KupShop\ProductsChargesBundle\Util\ChargesUtil;
use KupShop\RestrictionsBundle\Utils\Restrictions;
use Query\Operator as Op;
use Query\Translation;

/**
 * Class representing one product.
 *
 * @author Joe
 */
#[AllowDynamicProperties]
class ProductBase implements ArrayAccess
{
    use DatabaseCommunication;

    /**
     * Product ID.
     *
     * @var int
     */
    public $id = -1;

    /**
     * Product texts.
     *
     * @var string
     */
    public $title = '';
    public $descr = '';
    public $longDescr = '';
    public $parameters = '';

    public $id_block;

    /**
     * SEO texts.
     *
     * @var string
     */
    public $meta_title = '';
    public $meta_description = '';
    public $meta_keywords = '';

    /**
     * Images.
     *
     * @var array
     */
    public $image = [];
    public $photos = [];
    public $photoId = -1;
    public $photoDescr = '';
    public $photoDateUpdate;

    /**
     * Prices.
     *
     * @var int
     */
    // cena nejlevnejsi varianty, nebo cena produktu, pokud nema varianty
    public $price = 0; // ve vysledku je to string, napr. "100 Kč"
    // priceRaw = p.price - cena produktu
    public $priceRaw = 0; // Decimal
    public $priceNoVat = 0; // string, napr. "83 Kč", skoro se nepouziva, muzeme smazat?
    public $priceCommon; // ProductPrice
    // cena nejdrazsi varianty, nebo cena produktu, pokud nema varianty
    public $priceMax = 0; // array
    public $pricelistPrice; // nepouziva se?

    public $pricelistId;

    /**
     * @var Decimal
     */
    public $discount;
    public $pricelistDiscount;
    public $vat = 0;
    public $vat_id = 0;

    /**
     * Nove promenne s cenou.
     *
     * @var Decimal[]
     */
    public $price_array = [];
    public $priceCommon_array = [];

    /**
     * @var ?array
     */
    public $producer;
    /**
     * Basic product informations.
     *
     * @var unknown_type
     */
    public $producerTitle;
    public $producerImage;
    public $idProducer;
    public $code = '';
    public $guarantee = 0;
    public $campaign = '';
    public $campaign_codes = [];
    public $charges;
    public $visible;

    /**
     * Stock and delivery time informations.
     *
     * @var int
     */
    public $inStore = 0;
    public $deliveryTime = 0;
    public $deliveryTimeText = '';
    public $deliveryTimeRaw = 0;
    protected $canBuy;
    protected $canWatch;

    /**
     * Additional informations.
     *
     * @var unknown_type
     */
    public $links = [];
    public $attachments = [];
    /**
     * @var ParameterConfiguration[]
     */
    public $configurations;
    public $param;
    public $related = [];
    public $commentsOpen = false;
    public $comments = [];
    public $variations;
    public $alsoBuyed = [];
    public $templates;

    public $sets;
    public $gifts;
    public $labels;
    public $multiSets;
    public $collections;
    public $products_related;
    public $matched_id_variation;

    public ?\KupShop\ComponentsBundle\Entity\Thumbnail $thumbnail;

    public $data;
    public $sections;

    public $width;
    public $height;
    public $depth;

    /** @var \KupShop\PricelistBundle\Util\Price\PriceListPrice */
    protected $productPrice;

    public $ean;
    public $weight;
    public $in_store_suppliers;
    public ?DateTimeImmutable $forecasted_delivery_date = null;
    public $note;
    public $priceOriginal; // PriceWrapper
    public $price_buy; // array|ProductPrice
    public $priceForDiscount; // ProductPrice
    public $reviews;
    public ?array $rating = null;
    public $variationId;
    public $bonus_points;
    public $unit;
    /**
     * @var string
     */
    public $note_;
    public $id_cn;

    public $productCode;
    public array $convertors = [];

    /** @var string{'Y', 'N'} */
    public string $showInSearch = 'Y';

    public ?int $position = null;
    public ?int $pieces_sold = null;

    /**
     * Constructor.
     */
    public function __construct($id = -1)
    {
        $this->id = $id;

        $this->discount = DecimalConstants::zero();
    }

    /**
     * Fetches data from database, product ID specified in $pid.
     *
     * @param int $pid
     *
     * @return bool
     */
    public function createFromDB($pid = null)
    {
        if ($pid) {
            $this->id = $pid;
        }

        $SQL = $this->createQueryBuilder()
            ->andWhere(Op::equals(['p.id' => $this->id]))
            ->execute();

        if (sqlNumRows($SQL) == 1) {
            $prod = sqlFetchAssoc($SQL);

            $this->title = $prod['title'];

            $this->createFromArray($prod);

            return true;
        } else {
            return false;
        }
    }

    /**
     * Fetches data from given array.
     *
     * @param array $data
     *
     * @return bool
     */
    public function createFromArray($data)
    {
        $this->id = $data['id'];
        $this->id_block = $data['id_block'] ?? null;
        $this->title = $data['title'];
        $this->discount = toDecimal($data['discount']);
        $this->vat = getVat($data['vat']);
        $this->vat_id = $data['vat'];
        $this->original_vat = $data['original_vat'] ?? $data['vat'];

        $this->deliveryTime = $data['delivery_time'];
        $this->code = $data['code'];

        $this->guarantee = $data['guarantee'] ?? null;
        $this->idProducer = $data['producer'] ?? null;
        $this->longDescr = trim($data['long_descr'] ?? '');
        $this->descr = trim($data['short_descr'] ?? '');
        $this->parameters = trim($data['parameters'] ?? '');
        $this->campaign = productCampaign($data['campaign'] ?? '', $this->campaign_codes);
        $this->inStore = $data['in_store'] ?? null;
        $this->visible = $data['figure'] ?? null;
        $this->ean = $data['ean'] ?? null;
        $this->weight = $data['weight'] ?? null;

        $this->meta_title = $data['meta_title'] ?? '';
        $this->meta_description = $data['meta_description'] ?? '';
        $this->meta_keywords = $data['meta_keywords'] ?? '';
        $this->matched_id_variation = getVal('matched_id_variation', $data, '');

        $this->data = getVal('data', $data);

        $this->width = $data['width'] ?? null;
        $this->height = $data['height'] ?? null;
        $this->depth = $data['depth'] ?? null;
        $this->showInSearch = $data['show_in_search'] ?? 'Y';
        $this->productCode = $data['productCode'] ?? '';
        $this->pieces_sold = $data['pieces_sold'] ?? null;
        $this->position = $data['position'] ?? null;

        if (findModule(Modules::PRICELISTS)) {
            if (array_key_exists('pricelist_discount', $data)) {
                $this->pricelistDiscount = toDecimal($data['pricelist_discount']);
                $pricelistContext = ServiceContainer::getService(\KupShop\PricelistBundle\Context\PricelistContext::class);
                $this->pricelistId = $pricelistContext->getActiveId();

                $extend = $pricelistContext->getActive()?->getUseProductDiscount();
                if (($extend && $this->pricelistId && !$this->pricelistDiscount->isZero()) || (!$extend && $this->pricelistId)) {
                    $this->discount = $this->pricelistDiscount;
                }
            }
        }

        if (array_key_exists('id_photo', $data)) {
            $this->photoId = $data['id_photo'];
            $this->photoDescr = $data['descr_photo'];
            $this->photoDateUpdate = $data['id_photo_update'];
        }

        if (!empty($data['in_store_suppliers'])) {
            $this->in_store_suppliers = $data['in_store_suppliers'];
        }

        if (findModule('products', 'note')) {
            $this->note_ = $data['note_'] ?? '';
        }

        if (isset($data['price'])) {
            $this->fetchPrices($data);
        }

        if (findModule(Modules::BONUS_PROGRAM) && isset($data['price'])) {
            $this->bonus_points = (is_null($data['bonus_points']) ? null : toDecimal($data['bonus_points']));
        }

        if (findModule(Modules::PRODUCTS, Modules::SUB_UNITS)) {
            $this->fetchUnit($data);
        }

        $this->id_cn = getVal('id_cn', $data);
        $this->date_added = $data['date_added'] ?? '';

        if (isset($data['in_store_min'])) {
            $this->in_store_min = $data['in_store_min'];
        }

        if (isset($data['date_stock_in']) && $data['date_stock_in'] !== '0000-00-00 00:00:00') {
            try {
                $this->date_stock_in = new DateTime($data['date_stock_in']);
            } catch (Exception $e) {
            }
        }

        if (!empty($data['variationsIds'])) {
            $this->variationsIds = array_filter(explode(',', $data['variationsIds']));
        }

        if (array_key_exists('has_variations', $data)) {
            $this->has_variations = (bool) $data['has_variations'];
        }

        if (findModule(Modules::PRODUCTS_SERIAL_NUMBERS)) {
            $this->serial_number_require = $data['serial_number_require'] ?? 'N';
        }

        return true;
    }

    public function fetchProducer()
    {
        if ($this->producer) {
            return $this->producer;
        }

        if (findModule(Modules::PRODUCERS)) {
            $multiFetch = ServiceContainer::getService(MultiFetch::class);
            $multiFetch->fetchProducers(new ProductCollection([$this->id => $this]));
        }

        return $this->producer;
    }

    public function fetchPrices($data)
    {
        $currencyContext = Contexts::get(CurrencyContext::class);
        $priceConverter = ServiceContainer::getService(PriceConverter::class);

        $this->productPrice = new ProductPrice(toDecimal($data['price']), $currencyContext->getDefault(), getVat($data['vat']), $data['discount']);
        $this->productPrice->setSource($this);

        $priceForDiscountCurrency = $currencyContext->getDefault();

        if (findModule(Modules::PRICELISTS) && isset($data['pricelist_price'])) {
            /** @var \KupShop\PricelistBundle\Entity\Pricelist $activePricelist */
            $activePricelist = Contexts::get(PricelistContext::class)->getActive();

            $extend = $activePricelist?->getUseProductDiscount();
            if (($extend && $this->pricelistId && isset($data['pricelist_discount'])) || (!$extend && $this->pricelistId)) {
                $data['discount'] = $data['pricelist_discount'];
            }

            if (!$data['pricelist_currency']) {
                $data['pricelist_currency'] = $currencyContext->getDefault();
            } else {
                $data['pricelist_currency'] = $currencyContext->getALL()[$data['pricelist_currency']];
            }

            // správně by se mělo brát z ceníků - nyní se v případě různosti měn porovnavali například EUR s CZK
            $data['priceMax'] = $data['pricelist_price_max'] ?? $data['pricelist_price'];

            $originalPrice = $this->productPrice;
            $this->productPrice = new \KupShop\PricelistBundle\Util\Price\PriceListPrice(toDecimal($data['pricelist_price']), $data['pricelist_currency'], getVat($data['vat']), $data['discount']);
            $this->productPrice->setOriginalPrice($originalPrice);
            $this->productPrice->setSource($this);

            $coefficient = $activePricelist?->getCoefficient();
            if ($coefficient && in_array($data['price_source'], ['p', 'pv'])) {
                /* v pripade koeficientu na ceniku je aplikovan i na originalPrice */
                $this->productPrice->applyCoefficient(toDecimal($coefficient));
                if (!empty($data['price_for_discount'])) {
                    $data['price_for_discount'] *= $coefficient;
                }
                if (!empty($data['priceMax'])) {
                    $data['priceMax'] *= $coefficient;
                }
            }

            $this->priceOriginal = PriceWrapper::wrap($this->productPrice->getOriginalPrice());
            $this->priceRaw = $priceConverter->convert($this->productPrice->getCurrency(), $currencyContext->getDefault(), $this->productPrice->getValue());

            // prerazim CPS od produktu CPS z ceniku, pokud ji mam dostupnou
            if (!empty($data['pricelist_price_history']) && array_key_exists('pricelist_price_for_discount', $data)) {
                $data['price_for_discount'] = $data['pricelist_price_for_discount'];
                $priceForDiscountCurrency = $data['pricelist_currency'];
            }
        } else {
            $priceOriginal = new ProductPrice(toDecimal($data['priceRaw']), $currencyContext->getDefault(), getVat($data['vat']));
            $priceOriginal->setSource($this);
            $this->priceOriginal = PriceWrapper::wrap($priceOriginal);
            $this->priceRaw = $priceConverter->convert($this->productPrice->getCurrency(), $currencyContext->getDefault(), $data['priceRaw']);
        }

        $originalProductPrice = $this->productPrice;

        $pricelevelContext = Contexts::get(PriceLevelContext::class);
        if ($pricelevel = $pricelevelContext->getActive()) {
            $this->productPrice = new PriceLevelPrice($this->productPrice);
            $IDcat = isset($data['priceLevelSections']) ? explode(',', $data['priceLevelSections']) : null;
            $pricelevelDiscount = $pricelevel->getDiscount($this->id, $IDcat, $this->idProducer, $this);
            $this->productPrice->setPricelevelDiscount($pricelevelDiscount);
            $this->productPrice->setSource($this);
        }

        if (!findModule(\Modules::PRODUCTS, \Modules::SUB_MODERN_PRICES)) {
            // Legacy prices - preformatted, old "array" prices, ...

            $IDcategory = $this['priceLevelSections'] ?? null;
            $IDproducer = $this->idProducer ?? -1;

            $this->price_array = formatCustomerPrice($priceConverter->convert($originalProductPrice->getCurrency(), $currencyContext->getDefault(), $originalProductPrice->getValue()), $originalProductPrice->getDiscount(), $originalProductPrice->getVat()->asFloat(), $this->id, $IDcategory, $IDproducer, null, $currencyContext->getDefault());

            if (isset($data['priceMax'])) {
                $this->priceMax = formatCustomerPrice($data['priceMax'], $originalProductPrice->getDiscount(), $originalProductPrice->getVat()->asFloat(), $this->id, $IDcategory, $IDproducer);
            }

            $this->priceCommon_array = formatPrice(applyCurrency($data['price_common']), 0);

            $this->price = printPrice($this->price_array, ['printdealerdiscount' => true, 'currency' => $currencyContext->getActive()->getId()]);

            $this->priceNoVat = printPrice($this->price_array, ['withVat' => false]);

            if (array_key_exists('price_buy', $data)) {
                $this->price_buy = formatPrice(applyCurrency($data['price_buy']), $originalProductPrice->getVat()->asFloat());
            }
        } else {
            // TODO: Zabránit vzniku, šetřit čas
            unset($this->priceOriginal);

            if (isset($data['priceMax'])) {
                $this->priceMax = new ProductPrice(toDecimal($data['priceMax']), $originalProductPrice->getCurrency(), $originalProductPrice->getVat(), $originalProductPrice->getDiscount());
                if ($this->productPrice instanceof PriceLevelPrice) {
                    $this->priceMax = new PriceLevelPrice($this->priceMax);
                    $this->priceMax->setPricelevelDiscount($this->productPrice->getPricelevelDiscount());
                }
                $this->priceMax->setSource($this);
            }

            if (array_key_exists('price_buy', $data)) {
                $this->price_buy = new ProductPrice(toDecimal($data['price_buy']), $currencyContext->getDefault(), getVat($data['vat']));
                $this->price_buy->setSource($this);
            }

            // TODO: Jediný co zbyde "divný" je priceRaw. Zkusit najít použití a případně zabít. Pak už zbyde jen productPrice, jupí! :-)
        }

        if ($data['price_common'] > 0) {
            $price_common = toDecimal($data['price_common'])->removeVat($this->productPrice->getVat());
            $this->priceCommon = new ProductPrice($price_common, $currencyContext->getDefault(), $this->productPrice->getVat());
            $this->priceCommon->setSource($this);
        }

        if (array_key_exists('price_for_discount', $data)) {
            $price_for_discount = toDecimal($data['price_for_discount']);
            $this->priceForDiscount = new ProductPrice($price_for_discount, $priceForDiscountCurrency, $this->productPrice->getVat());
            $this->priceForDiscount->setSource($this);
        }

        $this->recalculatePricesVatFromPriceWithVat($this);
    }

    protected function recalculatePricesVatFromPriceWithVat(Product $product): void
    {
        if (!PriceUtil::isProductPricesVatFromTop()) {
            return;
        }

        $fields = ['priceOriginal', 'productPrice', 'priceForDiscount'];

        $originalVatId = $product->original_vat ?? $product->vat_id;

        $originalVat = (float) getVat($originalVatId);
        $vatContext = Contexts::get(VatContext::class);
        if ($vatContext->getActive() == $vatContext::NO_VAT) {
            $originalVat = $vatContext->getVat($originalVatId)['vat'];
        }

        foreach ($fields as $field) {
            if (!isset($product->{$field})) {
                continue;
            }

            // PriceLevelPrice se uvnitr pocita specialne :/
            if ($product->{$field} instanceof PriceLevelPrice) {
                $product->{$field}->setOriginalPrice(
                    PriceUtil::recalculatePriceVatFromPriceWithVat(
                        $product->{$field}->getOriginalPrice(),
                        $originalVat,
                        $product->vat_id
                    )
                );
            }

            // prepocitam cenu tak, aby DPH bylo pocitano ze shora
            $product->{$field} = PriceUtil::recalculatePriceVatFromPriceWithVat($product->{$field}, $originalVat, $product->vat_id);
        }

        $currencyContext = Contexts::get(CurrencyContext::class);
        $priceConverter = ServiceContainer::getService(PriceConverter::class);

        // do price_array dam product price, aby to tam bylo taky prepocitany
        $this->price_array = PriceWrapper::wrap($product->getProductPrice());
        // prepocitam price raw podle aktualni productPrice - kvuli kosiku
        $priceRaw = $this->getProductPrice()->getValue();
        // pokud se jedna o cenu pocitanou pres price level, tak si do price raw vytahnu cenu z puvodni ceny
        // protoze v $value na PriceLevelPrice je ulozena cena, na kterou je aplikovana uz ta cenova hladina
        // a takovou cenu nechceme, protoze to neni priceRaw
        if ($this->getProductPrice() instanceof PriceLevelPrice) {
            $priceRaw = $this->getProductPrice()->getOriginalPrice()->getValue();
        }

        $this->priceRaw = $priceConverter->convert($this->getProductPrice()->getCurrency(), $currencyContext->getDefault(), $priceRaw);
    }

    public function fetchCharges($data = false, bool $onlyVisible = true)
    {
        if (!findModule(Modules::PRODUCTS_CHARGES)) {
            return [];
        }

        $util = ServiceContainer::getService(ChargesUtil::class);

        if ($this->charges === null) {
            $multiFetch = ServiceContainer::getService(MultiFetch::class);
            $multiFetch->fetchProductsCharges(new ProductCollection([$this->id => $this]), $onlyVisible);

            $this->charges = $util->applyPrices($this->charges, $this);
        }

        return $util->applyData($this->charges, $data, $this);
    }

    /**
     * @param $products   Product[] array of products
     * @param $parameters int[] array of parameter IDs
     *
     * @deprecated Use KupShop\CatalogBundle\ProductList\MultiFetch service instead
     *
     * @return array
     */
    public static function fetchParametersMulti($products, $parameters = null)
    {
        $multiFetch = ServiceContainer::getService(\KupShop\CatalogBundle\ProductList\MultiFetch::class);

        if (!$products instanceof \KupShop\CatalogBundle\ProductList\ProductCollection) {
            // Add idd to keys
            $products = array_combine(array_column($products, 'id'), $products);
            $products = new \KupShop\CatalogBundle\ProductList\ProductCollection($products);
        }

        return $multiFetch->fetchParameters($products, $parameters);
    }

    public function fetchParameters($parameters = null)
    {
        if (is_array($this->param)) {
            return $this->param;
        }

        if (findModule('products_parameters')) {
            self::fetchParametersMulti([$this], $parameters);
        } else {
            $this->param = [];
        }

        return $this->param;
    }

    /**
     * @param $products Product[]|ProductCollection
     */
    public static function fetchSetsMulti($products, $showOutOfStock = null): void
    {
        if (isset($products[0]->sets)) {
            return;
        }

        if (is_array($products)) {
            $products = new ProductCollection($products); // legacy support for $products as array
        }

        $multiFetch = ServiceContainer::getService(\KupShop\CatalogBundle\ProductList\MultiFetch::class);
        $multiFetch->fetchSets($products, $showOutOfStock);
    }

    /**
     * @return array
     */
    public function fetchGifts($showOutOfStock = null)
    {
        if (is_array($this->gifts)) {
            return $this->gifts;
        }

        $this->gifts = [];
        if (\findModule(\Modules::PRODUCT_GIFTS)) {
            $multiFetch = ServiceContainer::getService(\KupShop\CatalogBundle\ProductList\MultiFetch::class);
            $multiFetch->fetchGifts(new \KupShop\CatalogBundle\ProductList\ProductCollection([$this->id => $this]), $showOutOfStock);
        }

        return $this->gifts;
    }

    public function fetchLabels($visibility = null)
    {
        if ($this->labels) {
            return $this->labels;
        }

        if (\findModule(\Modules::LABELS, Modules::SUB_PRODUCT_LABELS)) {
            $multiFetch = ServiceContainer::getService(MultiFetch::class);
            $multiFetch->fetchProductLabels(new ProductCollection([$this->id => $this]), $visibility);
        } else {
            $this->labels = [];
        }

        return $this->labels;
    }

    /**
     * @return array(Product)
     */
    public function fetchSets($showOutOfStock = null)
    {
        if (is_array($this->sets)) {
            return $this->sets;
        }

        if (findModule(\Modules::PRODUCT_SETS)) {
            self::fetchSetsMulti([$this->id => $this], $showOutOfStock);
        } else {
            $this->sets = [];
        }

        return $this->sets;
    }

    /**
     * @return \KupShop\CatalogBundle\Entity\Section[]
     */
    public function fetchSections()
    {
        if (is_array($this->sections)) {
            return $this->sections;
        }

        $multiFetch = ServiceContainer::getService(\KupShop\CatalogBundle\ProductList\MultiFetch::class);

        $multiFetch->fetchSections(new \KupShop\CatalogBundle\ProductList\ProductCollection([$this->id => $this]));

        return $this->sections;
    }

    /**
     * @param bool $fetchAll
     *
     * @return array(Product)
     */
    public function fetchCollections($fetchAll = true)
    {
        if (is_array($this->collections)) {
            return $this->collections;
        }

        $dbcfg = Settings::getDefault();

        $this->collections = [];

        if (findModule('products_collections')) {
            $qb = sqlQueryBuilder()
                ->select('p.id, p.figure, p.title, p.code, MIN(COALESCE(pv.price, p.price)) as price, MAX(COALESCE(pv.price, p.price)) as priceMax,
				p.price_common, p.vat, p.discount, p.producer, p.delivery_time, p.campaign, p.figure, p.in_store, p.price as priceRaw');

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

            if (findModule(Modules::PRODUCTS, Modules::SUB_UNITS)) {
                $qb->addSelect('p.unit, pu.short_name, pu.long_name as unit_long_name')
                    ->leftJoin('p', 'products_units', 'pu', 'p.unit=pu.id');
                if (findModule(Modules::PRODUCTS, Modules::SUB_UNITS_FLOAT)) {
                    $qb->addSelect('pu.pieces_precision');
                }
            }

            if (findModule(Modules::RESTRICTIONS)) {
                $restrictions = ServiceContainer::getService(Restrictions::class);
                $qb->andWhere($restrictions->getRestrictionSpec());
            }

            $qbOthers = clone $qb;

            $qb->addSelect('ps.id_product = :id_product as own')
                ->from('products_collections', 'ps')
                ->leftJoin('ps', 'products', 'p', 'p.id = IF(ps.id_product = :id_product, ps.id_product_related, ps.id_product)')
                ->leftJoin('p', 'products_variations', 'pv', 'p.id=pv.id_product')
                ->andWhere('(ps.id_product=:id_product OR ps.id_product_related=:id_product)')
                ->groupBy('p.id')
                ->setParameter('id_product', $this->id);

            $own = [];
            $others = [];
            foreach ($qb->execute() as $row) {
                $product = new Product();
                $product->createFromArray($row);

                if ($row['own']) {
                    if ($dbcfg->prod_show_not_in_store == 'N' && !isAdministration() && $row['in_store'] <= 0) {
                        continue;
                    } else {
                        $own[$product->id] = $product;
                    }
                } else {
                    $others[$product->id] = $product;
                }
            }

            if ($others && $fetchAll) {
                $qbOthers->addSelect('ps.id_product');

                if ($dbcfg->prod_show_not_in_store == 'N' && !isAdministration()) {
                    $qbOthers->andWhere('p.in_store > 0');
                }

                $qbOthers->from('products_collections', 'ps')
                    ->leftJoin('ps', 'products', 'p', 'p.id = ps.id_product_related')
                    ->leftJoin('p', 'products_variations', 'pv', 'p.id=pv.id_product')
                    ->andWhere(Op::andX(Op::inIntArray(array_keys($others), 'ps.id_product'),
                        Op::not(Op::equals(['p.id' => $this->id]))))
                    ->groupBy('p.id')
                    ->orderBy('ps.id_product');

                foreach ($others as &$other_prod) {
                    if ($dbcfg->prod_show_not_in_store == 'N' && !isAdministration() && $other_prod->inStore <= 0) {
                        $other_prod->visible = 'N';
                    }
                }

                foreach ($qbOthers->execute() as $other) {
                    $product = new Product();
                    $product->createFromArray($other);

                    $others[$other['id_product']]->collections[] = $product;
                }
            }

            $this->collections = ['own' => $own, 'others' => $others];
        }

        return $this->collections;
    }

    public function fetchProductsRelated()
    {
        if (is_array($this->products_related)) {
            return $this->products_related;
        }

        $this->products_related = [];

        if (findModule('products_related')) {
            $SQL = sqlQueryBuilder()
                ->select('p.title')->fromProducts()
                ->addSelect(RelatedProducts::relatedProductsSpec([$this->id]));

            if (findModule(Modules::DYNAMIC_RELATED_PRODUCTS)) {
                $SQL->andWhere(Op::isNull('pr.id_products_related_dynamic'));
            }

            foreach ($SQL->execute() as $row) {
                $this->products_related[] = $row;
            }
        }

        return $this->products_related;
    }

    public function fetchRating(): ?array
    {
        if ($this->rating !== null) {
            return $this->rating;
        }

        $this->rating = [];

        if (findModule(Modules::REVIEWS)) {
            $multiFetch = ServiceContainer::getService(MultiFetch::class);
            $multiFetch->fetchRating(new ProductCollection([$this->id => $this]));
        }

        return $this->rating;
    }

    // backward compatibility
    public function fetchReviews()
    {
        return $this->reviews ??= $this->fetchRating();
    }

    public function isVisible()
    {
        return $this->visible != 'N';
    }

    public function isOld()
    {
        return $this->visible == 'O';
    }

    public function canBuy(): bool
    {
        if (isset($this->canBuy)) {
            return $this->canBuy;
        }

        if (empty($this->deliveryTimeText)) {
            $this->prepareDeliveryText();
        }

        return ProductAvailabilityUtil::canBuy($this->deliveryTime, $this->isOld(), $this->canWatch, $this->canBuy);
    }

    public function canWatch(): ?bool
    {
        if (!findModule(Modules::WATCHDOG)) {
            return null;
        }

        if (!isset($this->canWatch)) {
            $this->canBuy();
        }

        return $this->canWatch ?? false;
    }

    public function hasVariations()
    {
        return $this->has_variations ??= returnSQLResult('SELECT COUNT(*) FROM '.getTableName('products_variations')." pv WHERE pv.id_product={$this->id}") > 0;
    }

    public function findVariation($id)
    {
        if (!empty($this->variations['variations'][$id])) {
            return $this->variations['variations'][$id];
        }

        return null;
    }

    public function fetchVariations($in_store_only = false)
    {
        if (!is_null($this->variations)) {
            return $this->variations;
        }

        $this->variations = [];

        if (findModule('products_variations')) {
            // nacist varianty k produktu
            $this->variations['variations'] = Variations::getProductVariations(intval($this->id), $in_store_only);

            // docist jmenovky, pokud jsou varianty
            if (count($this->variations['variations']) > 0) {
                $this->variations['labels'] = Variations::getProductLabels(intval($this->id));
            }
        }

        return $this->variations;
    }

    /**
     * @param $products Product[]
     */
    public static function fetchVariationsMulti($products, $labels)
    {
        $multiFetch = ServiceContainer::getService(\KupShop\CatalogBundle\ProductList\MultiFetch::class);

        if (!$products instanceof \KupShop\CatalogBundle\ProductList\ProductCollection) {
            $products = new \KupShop\CatalogBundle\ProductList\ProductCollection($products);
        }

        $multiFetch->fetchVariations($products, $labels);
    }

    public function fetchLinks()
    {
        $SQL = sqlQuery('SELECT *
				FROM '.getTableName('links')."
				WHERE id_product='".$this->id."' ");

        foreach ($SQL as $key => $link) {
            if (empty($link['title'])) {
                $link['title'] = '['.($key + 1).']';
            }

            $this->links[] = $link;
        }

        sqlFreeResult($SQL);
    }

    public function fetchAttachments()
    {
        $SQL = sqlQueryBuilder()->select('*')
            ->from('attachments')
            ->where(Op::equals(['id_product' => $this->id]))
            ->orderBy('position', 'ASC')
            ->execute();

        foreach ($SQL as $attachment) {
            $link = $attachment['link'];
            $attachment['remote'] = preg_match('@^([a-z]{2,4}://)@i', $link);

            if (!$attachment['remote'] && isset($link[0]) && $link[0] == '/') {
                $attachment['size'] = @filesize('.'.urldecode($link));
            }

            $this->attachments[] = $attachment;
        }

        sqlFreeResult($SQL);
    }

    public function fetchImages($mainSize, $additionalSize = null)
    {
        if ($mainSize) {
            if ($this->photoId != -1) {
                $this->image = getImage($this->photoId, null, null, $mainSize, $this->photoDescr, strtotime($this->photoDateUpdate ?? ''));
            } else {
                $this->image = leadImage($this->id, $mainSize);
            }
        }

        if ($additionalSize) {
            $qb = sqlQueryBuilder()
                ->select('ph.id, ph.descr, ph.source, ph.image_2, ppr.show_in_lead, ph.date_update')
                ->from('photos_products_relation', 'ppr')
                ->leftJoin('ppr', 'photos', 'ph', 'ppr.id_photo = ph.id')
                ->where(Translation::coalesceTranslatedFields(PhotosTranslation::class))
                ->andWhere('ppr.id_product=:id_product AND ppr.active="Y"')
                ->groupBy('ph.id')
                ->setParameter('id_product', $this->id)
                ->orderBy('position', 'ASC');

            if (findModule(Modules::PRODUCTS_VARIATIONS_PHOTOS)) {
                $qb->having('MAX(ppr.show_in_lead) != \'Y\'');
            } else {
                $qb->andWhere('ppr.show_in_lead != "Y"');
            }

            if (findModule(Modules::VIDEOS)) {
                $qb->addSelect('id_cdn id_video')
                    ->leftJoin('ph', 'videos', 'v', 'v.id_photo = ph.id');
            }

            $SQL = $qb->execute();

            while (($image = sqlFetchAssoc($SQL)) !== false) {
                // skip main image, that is attached to an variation
                if ($image['id'] == ($this->image['id'] ?? null)) {
                    continue;
                }

                if (empty($image['image_2'])) {
                    continue;
                }

                $this->photos[] = array_merge(
                    getImage($image['id'], $image['image_2'], $image['source'], $additionalSize, $image['descr'], strtotime($image['date_update'])),
                    ['show_in_lead' => $image['show_in_lead'], 'id_video' => $image['id_video'] ?? null]
                );
            }

            sqlFreeResult($SQL);
        }
    }

    /**
     * Sanitize and fill-in texts.
     */
    public function prepareTexts()
    {
        // Sanitize text
        $this->title = htmlspecialchars($this->title);
    }

    public function prepareDeliveryText()
    {
        $cfg = Config::get();

        $inStore = $this->inStore;

        $this->deliveryTimeRaw = $this->deliveryTime;
        $this->deliveryTimeText = getProductDeliveryText($inStore, $this->deliveryTime);

        if ($inStore <= 0) {
            $inStoreSuppliers = $this->getInStoreSuppliers();
            if (findModule(\Modules::PRODUCTS_SUPPLIERS, \Modules::SUB_ALLOW_NEGATIVE_IN_STORE)) {
                $inStoreSuppliers += $this->inStore;
            }

            if ($inStoreSuppliers > 0) {
                if (findModule(\Modules::PRODUCTS_SUPPLIERS) && findModule(\Modules::PRODUCTS_SUPPLIERS, \Modules::SUB_DELIVERY_TIME)) {
                    $this->deliveryTime = $cfg['Modules']['products_suppliers']['delivery_time'];
                    $languageContext = \KupShop\KupShopBundle\Util\Contexts::get(\KupShop\KupShopBundle\Context\LanguageContext::class);

                    if (!$languageContext->translationActive()) {
                        $this->deliveryTimeText = sprintf($cfg['Products']['DeliveryTime'][$cfg['Modules']['products_suppliers']['delivery_time']], strval($inStoreSuppliers));
                    } else {
                        $this->deliveryTimeText = sprintf(translate_shop($this->deliveryTime, 'deliveryTime'), strval($inStoreSuppliers));
                    }
                }
            }
            $this->inStore = $inStore;
            $this->in_store_suppliers = $inStoreSuppliers;
        }
    }

    /**
     * @param Decimal $pieces
     *
     * @return Decimal
     */
    public function getPrice($variationId, $data, $pieces)
    {
        $price = toDecimal($this->priceRaw);

        if (!is_null($variationId)) {
            $price = Variations::getCustomPrice($variationId, $price);
        }

        $dispatcher = ServiceContainer::getService('event_dispatcher');
        $event = $dispatcher->dispatch(new OrderItemEvent($this, $variationId, $price, $pieces, $data), OrderItemEvent::CALCULATE_PRICE);
        $price = $event->getPrice();

        if (findModule(\Modules::PRODUCTS_PARAMETERS, \Modules::SUB_CONFIGURATIONS) && !empty($data['configurations'])) {
            $this->fetchParameters();

            $config = $data['configurations'];
            foreach ($this->configurations as $id_parameter => $parameter) {
                $values = $parameter->fetchValues(false);
                if (!empty($config[$id_parameter]) && array_key_exists($config[$id_parameter], $values)) {
                    $configurationPriceWithVat = $values[$config[$id_parameter]]->configuration_price;
                    $price = $price->add($configurationPriceWithVat->removeVat($this->vat)->removeDiscount($this->discount));
                }
            }
        }

        return $price;
    }

    public function printNote($data)
    {
        $ret = '';

        if (findModule(\Modules::PRODUCTS_PARAMETERS, \Modules::SUB_CONFIGURATIONS) && !empty($data['configurations'])) {
            $this->fetchParameters();

            $parts = [];
            $config = $data['configurations'];
            foreach ($this->configurations as $id_parameter => $parameter) {
                $values = $parameter->fetchValues();
                if (!empty($config[$id_parameter]) && array_key_exists($config[$id_parameter], $values)) {
                    $value = $values[$config[$id_parameter]];
                    $parts[] = "{$parameter->name}: {$value->value}";
                } elseif ($parameter['value_type'] == 'char' && !empty($config[$id_parameter])) {
                    $parts[] = "{$parameter->name}: {$config[$id_parameter]}";
                }
            }

            if ($parts) {
                $ret .= join(', ', $parts);
            }
        }

        return $ret;
    }

    public function parseNote($data): array
    {
        if (!$data) {
            return [];
        }

        $data = json_decode($data, true);

        return is_array($data) ? $data : [];
    }

    public function dumpNote($data)
    {
        return json_encode($data);
    }

    public function storeIn($variationId, $count)
    {
        $posContext = Contexts::get(PosContext::class);
        if (!$posContext->getActiveId() && findModule('orders', \Modules::SUB_STORE_IN_DISABLE) && $count >= 0) {
            return;
        }

        if (!empty($variationId)) {
            $table = getTableName('products_variations');
            $id = $variationId;
        } else {
            $table = getTableName('products');
            $id = $this->id;
        }

        $query = "SELECT in_store FROM {$table}
	   WHERE id={$id} FOR UPDATE";
        if (($inStoreOrigin = returnSQLResult($query)) !== false) {
            $inStoreNew = $inStoreOrigin + $count;

            if ($count < 0 && $inStoreNew < 0) {
                $reserved = max($inStoreOrigin, 0);
            } else {
                $reserved = -$count;
            }

            sqlQuery("UPDATE {$table}
	            SET in_store={$inStoreNew}
	            WHERE id={$id}");
        } else {
            return logError(__FILE__, __LINE__, "Variation id {$variationId} does not exists for product id {$this->id}!");
        }

        // React to store depletion
        if ($inStoreNew == 0 && $inStoreOrigin > 0) {
            $dbcfg = Settings::getDefault();

            if (!empty($variationId)) {
                if ($dbcfg->prod_do_after_order == 'delete') {
                    sqlQuery('DELETE FROM '.getTableName('products_variations')."
	                  WHERE id={$variationId}");
                }
                if ($dbcfg->prod_do_after_order == 'status') {
                    sqlQuery('UPDATE '.getTableName('products_variations')."
	                  SET delivery_time='-2'
	                  WHERE id={$variationId}");
                }
                if ($dbcfg->prod_do_after_order == 'hide') {
                    $this->storeInHideAction($variationId);
                }
            } else {
                if ($dbcfg->prod_do_after_order == 'delete') {
                    $this->delete();
                }
                if ($dbcfg->prod_do_after_order == 'status') {
                    sqlQuery('UPDATE '.getTableName('products')."
	                  SET delivery_time='-2'
	                  WHERE id={$this->id}");
                }
                if ($dbcfg->prod_do_after_order == 'hide') {
                    sqlQuery('UPDATE '.getTableName('products')."
               SET figure='O'
               WHERE id={$this->id}");
                }
            }
        }

        $this->updateInStore();

        return $reserved;
    }

    public function storeInHideAction($variationId)
    {
        sqlQuery('UPDATE '.getTableName('products_variations')."
      	            SET figure='N'
      	            WHERE id={$variationId}");

        $this->updateInStore();

        // hide also whole project if whole product is not available
        if ($this->inStore <= 0) {
            sqlQuery('UPDATE '.getTableName('products')."
   	               SET figure='O'
   	               WHERE id={$this->id}");
        }
    }

    public function sell($variationId, $count)
    {
        $reserved = null;

        sqlGetConnection()->transactional(function () use ($variationId, $count, &$reserved) {
            // Update pieces_sold in all cases
            sqlQuery('UPDATE products
	         SET pieces_sold=pieces_sold+:count
	         WHERE id=:id',
                ['id' => $this->id, 'count' => $count]
            );

            // Check if store management is enabled
            if (Settings::getDefault()->prod_subtract_from_store != 'Y') {
                return;
            }

            // Subtract items from store
            $reserved = $this->storeIn($variationId, -$count);
        });

        return $reserved;
    }

    public function updateInStore()
    {
        sqlQuery("UPDATE products p
	         SET p.in_store=(
            SELECT COALESCE(SUM(GREATEST(pv.in_store, 0)), p.in_store) 
            FROM products_variations pv
            WHERE pv.id_product=p.id
            )
            WHERE p.id={$this->id}");

        $this->inStore = returnSQLResult("SELECT p.in_store
	      FROM products p 
	      WHERE p.id={$this->id}");
    }

    public function updateDeliveryTime()
    {
        global $cfg;

        $delivery_times = sqlQueryBuilder()->select('delivery_time')->from('products_variations')
            ->where(
                Op::equals([
                    'id_product' => $this->id,
                    'figure' => 'Y',
                ]))
            ->execute()->fetchAll();

        if (empty($delivery_times)) {
            return true;
        }

        $minDelay = null;
        $bestID = null;
        foreach ($delivery_times as $variation) {
            $index = $variation['delivery_time'];
            switch ($index) {
                case -2:
                    $delay = 99;
                    break;
                case -1:
                    $delay = 2.5;
                    break;
                default:
                    $delay = abs($index);
                    break;
            }
            $delay = $cfg['Products']['DeliveryTimeDays'][$variation['delivery_time']] ?? $delay;
            if ($minDelay === null || $delay < $minDelay) {
                $minDelay = $delay;
                $bestID = $index;
            }
        }

        $this->deliveryTime = $bestID;

        return sqlAffectedRows(sqlQuery('UPDATE '.getTableName('products')." p
   	   SET p.delivery_time='{$this->deliveryTime}'
   	   WHERE p.id={$this->id}"));
    }

    // Delete product or variation based on $variation_id. If $variation_id is null, delete product. If it is last variation, delete also product.
    public function deleteVariation($variation_id = null)
    {
        $ret = 0;
        $variationsCount = returnSqlResult("SELECT COUNT(*)
	         FROM products_variations pv
	         WHERE pv.id_product={$this->id}");

        if ($variationsCount > 0 && $variation_id == null) {
            return $ret;
        }

        if ($variation_id) {
            sqlQuery("UPDATE products_variations SET figure='N' WHERE id={$variation_id}");
            $ret++;

            $emptyProduct = returnSqlResult("SELECT COUNT(*) > 0
	         FROM products_variations pv 
	         WHERE pv.id_product={$this->id} AND pv.figure = 'Y' ");

            if ($emptyProduct) {
                return $ret;
            }
        }

        sqlQuery("UPDATE products SET figure='O' WHERE id={$this->id}");

        $ret++;

        return $ret;
    }

    public function delete()
    {
        $img = new Photos('product');

        $SQL = sqlQuery('SELECT ph.id
	           FROM '.getTableName('photos').' AS ph
	           LEFT JOIN '.getTableName('photos-products')." AS php ON ph.id=php.id_photo
	           WHERE php.id_product={$this->id} ");

        foreach ($SQL as $row) {
            $IDph = $row['id'];
            $samePhotoCount = returnSQLResult('SELECT COUNT(*) FROM '.getTableName('photos-products')." php WHERE php.id_photo={$IDph}");
            if ($samePhotoCount <= 1) {
                $img->erasePhoto($IDph);
            }
        }

        // smazat sekce
        sqlQuery('DELETE FROM '.getTableName('products-sections')." WHERE id_product={$this->id}", '@');
        // smazat odkazy
        sqlQuery('DELETE FROM '.getTableName('links')." WHERE id_product={$this->id}", '@');
        // smazat prilohy
        sqlQuery('DELETE FROM '.getTableName('attachments')." WHERE id_product={$this->id}", '@');
        // smazat souvisejici zbozi
        sqlQuery('DELETE FROM '.getTableName('products-related')." WHERE id_top_product={$this->id}", '@');
        // smazat zbozi
        sqlQuery('DELETE FROM '.getTableName('products')." WHERE id={$this->id}", '@');
    }

    public function checkCodeUnique()
    {
        if (returnSQLResult('SELECT COUNT(*) FROM '.getTableName('products')." p WHERE p.code LIKE '{$this->code}' AND p.id!='{$this->id}'") == 0) {
            return true;
        }

        // Strip "(1)"
        $code = preg_replace('/ \([0-9]+\)$/', '', $this->code);

        $index = 1;
        while (returnSQLResult('SELECT COUNT(*) FROM '.getTableName('products')." p WHERE p.code LIKE '{$code} ({$index})' AND p.id!='{$this->id}'") > 0) {
            $index++;
        }

        $this->code = "{$code} ({$index})";

        return false;
    }

    public function getInStoreOffset($variation_id = null)
    {
        $query = 'SELECT SUM(oi.pieces)
   	   FROM '.getTableName('order_items').' oi
   	   LEFT JOIN '.getTableName('orders').' o ON oi.id_order=o.id,
   	   '.getTableName('orders')." o2
   	   WHERE o2.id={$_GET['IDo']} AND o.date_created >= o2.date_created AND oi.id_product={$this->id}
   	   AND o.status_storno=0 AND o.status IN (".join(',', getStatuses('active')).')';

        if (!empty($variation_id)) {
            $query .= "AND oi.id_variation={$variation_id}";
        }

        return intval(returnSQLResult($query));
    }

    public function getInStoreSuppliers($id_variation = null)
    {
        if ((!findModule(\Modules::PRODUCTS_SUPPLIERS) && !findModule(\Modules::SUPPLIERS)) || !SuppliersUtil::isSuppliersStoreEnabled()) {
            return 0;
        }

        if (isset($this->in_store_suppliers) && ($id_variation == $this->variationId)) {
            return $this->in_store_suppliers;
        }

        $old = $this->in_store_suppliers ?? null;
        $this->in_store_suppliers = null;

        $multiFetch = ServiceContainer::getService(MultiFetch::class);
        $productListId = $this->id;
        if ($id_variation) {
            $productListId .= '/'.$id_variation;
        }
        $productCollection = new ProductCollection([$productListId => $this]);
        if ($id_variation) {
            $productCollection->setEntityType(FilterParams::ENTITY_VARIATION);
        }
        $multiFetch->fetchProductOfSuppliersInStore($productCollection);

        $result = $this->in_store_suppliers;

        if ($id_variation != $this->variationId) {
            $this->in_store_suppliers = $old;
        }

        return $result;
    }

    public function getSuppliers($id_variation = null)
    {
        if (!findModule(\Modules::PRODUCTS_SUPPLIERS) && !findModule(\Modules::SUPPLIERS)) {
            return [];
        }

        $query = 'SELECT pos.id_variation, pos.in_store, pos.id_supplier
               FROM '.getTableName('products_of_suppliers').' pos
               WHERE pos.id_product=:id';

        if (!empty($id_variation)) {
            $query .= ' AND pos.id_variation=:id_variation';
        }

        $SQL = sqlQuery($query, ['id' => $this->id, 'id_variation' => $id_variation]);

        $suppliers = [];
        foreach ($SQL as $row) {
            $variation = $row['id_variation'] ?: '0';
            $suppliers[$variation][$row['id_supplier']] = $row['in_store'];
        }

        return $suppliers;
    }

    public static function isValid($productId, $variationId)
    {
        $where = selectQueryCreate(['p.id' => $productId, 'pv.id' => $variationId], true);
        $count = returnSQLResult('SELECT COUNT(*)
	         FROM '.getTableName('products').' p
	         LEFT JOIN '.getTableName('products_variations')." pv ON pv.id_product = p.id
	         WHERE {$where}");

        return $count == 1;
    }

    public static function automaticUpdateFlagForNewProducts($id_product = false)
    {
        $cfg = Config::get();
        $dbcfg = Settings::getDefault();

        if (!empty($dbcfg['prod_flag_automatic_new']) && !empty($cfg['Products']['Flags']['N'])) {
            $field = empty($dbcfg['prod_flag_automatic_new_field']) ? 'date_added' : $dbcfg['prod_flag_automatic_new_field'];

            $where = ($id_product) ? ' id=:id AND' : '';
            $where .= " NOT FIND_IN_SET('MN', campaign) AND";

            sqlQuery("UPDATE products SET campaign = ADD_TO_SET('N', campaign) WHERE {$where} NOT FIND_IN_SET('N', campaign) AND DATE_ADD({$field}, INTERVAL :days DAY) >  CURDATE()", ['id' => $id_product, 'days' => $dbcfg['prod_flag_automatic_new']]);

            sqlQuery("UPDATE products SET campaign = REMOVE_FROM_SET('N', campaign) WHERE {$where} FIND_IN_SET('N', campaign) AND DATE_ADD({$field}, INTERVAL :days DAY) <  CURDATE() ", ['id' => $id_product, 'days' => $dbcfg['prod_flag_automatic_new']]);
        }
    }

    public static function getName($id)
    {
        return returnSQLResult('SELECT title FROM products WHERE id=:id', ['id' => $id]);
    }

    /**
     * @return string
     */
    public function getTableFields()
    {
        $dbCfg = Settings::getDefault();

        $fields = "p.id, pv.id as variation_id, p.title, p.code, p.ean, p.short_descr, p.long_descr, p.parameters,
            MIN(COALESCE(pv.price, p.price)) as price, MAX(COALESCE(pv.price, p.price)) as priceMax, p.price as priceRaw, p.price_common, p.discount,
				p.producer, p.guarantee, p.delivery_time, DATE_FORMAT(p.updated, '{$dbCfg->date_format}') AS d_update, (pv.id IS NOT NULL) as has_variations,
				p.campaign, p.figure, p.data, p.width, p.height, p.depth, p.code as productCode, COALESCE(pv.date_added, p.date_added) as date_added, GROUP_CONCAT(pv.id) as variationsIds, p.show_in_search";

        if (findModule(\Modules::BONUS_PROGRAM)) {
            $fields .= ', p.bonus_points';
        }

        if (findModule(Modules::PRODUCTS, Modules::SUB_DESCR_PLUS)) {
            $fields .= ',p.id_block';
        }

        if (findModule('products', 'note')) {
            $fields .= ', COALESCE(pv.note, p.note) note_';
        }

        if (findModule(Modules::STOCK_IN)) {
            $fields .= ', p.date_stock_in';
        }

        if (findModule(Modules::PRODUCTS_SERIAL_NUMBERS)) {
            $fields .= ', p.serial_number_require';
        }

        return $fields;
    }

    /* dalsi field_ methody nepridavat do class Product, ale do ProductWrapper */

    public function field_productPrice()
    {
        return PriceWrapper::wrap($this->productPrice);
    }

    public function field_price_array()
    {
        if (empty($this->price_array)) { // SUB_MODERN_PRICES
            return PriceWrapper::wrap($this->productPrice);
        }

        return $this->price_array;
    }

    public function field_price_buy()
    {
        if ($this->price_buy instanceof ProductPrice) {
            return PriceWrapper::wrap($this->price_buy);
        }

        return $this->price_buy;
    }

    public function field_priceCommon()
    {
        if ($this->priceCommon instanceof ProductPrice) {
            return PriceWrapper::wrap($this->priceCommon);
        }

        return $this->priceCommon;
    }

    public function field_priceMax()
    {
        if ($this->priceMax instanceof ProductPrice) {
            return PriceWrapper::wrap($this->priceMax);
        }

        return $this->priceMax;
    }

    public function field_originalPrice()
    {
        return PriceWrapper::wrap($this->productPrice->getOriginalPrice());
    }

    public function field_bonus_points()
    {
        return $this->getBonusPoints($this->variationId);
    }

    public function field_producerTitle()
    {
        return $this->producer['name'] ?? null;
    }

    public function field_producerImage()
    {
        return $this->producer['photo'] ?? null;
    }

    public function field_weight()
    {
        if (!empty($this->weight)) {
            return $this->weight;
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_WEIGHT)) {
            $this->weight = 0.;
            foreach ($this->fetchSets() as $set_product) {
                $this->weight += ($set_product->weight ?? 0) * $set_product->set_pieces;
            }
        }

        return $this->weight;
    }

    /* dalsi field_ methody nepridavat do class Product, ale do ProductWrapper */

    /**
     * @return string
     */
    public function offsetMethod($offset)
    {
        return 'field_'.$offset;
    }

    /**
     * Implements ArrayAccess interface.
     */
    public function offsetSet($offset, $value): void
    {
        $this->{$offset} = $value;
    }

    public function offsetExists($offset): bool
    {
        return isset($this->{$offset});
    }

    public function offsetUnset($offset): void
    {
        unset($this->{$offset});
    }

    public function offsetGet($offset): mixed
    {
        $method = $this->offsetMethod($offset);
        if (method_exists($this, $method)) {
            $res = call_user_func([$this, $method]);

            return $res;
        }

        return isset($this->{$offset}) ? $this->{$offset} : null;
    }

    /**
     * @return \Doctrine\DBAL\Query\QueryBuilder
     */
    protected function createQueryBuilder()
    {
        $qb = sqlQueryBuilder()
            ->select($this->getTableFields())
            ->addSelect(\Query\Product::withVat())
            ->fromProducts()
            ->joinVariationsOnProducts()
            ->groupBy('p.id')
            ->setMaxResults(1);

        $inStoreField = \Query\Product::getInStoreField(false, $qb);

        if (findModule('products', 'showMax')) {
            $qb->addSelect('LEAST('.$inStoreField.', COALESCE(p.in_store_show_max, '.findModule('products', 'showMax').')) in_store');
        } else {
            $qb->addSelect($inStoreField.' in_store');
        }

        if (findModule('seo')) {
            $qb->addSelect('p.meta_title, p.meta_description, p.meta_keywords');
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_WEIGHT)) {
            $qb->addSelect('p.weight');
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_BUY)) {
            $qb->addSelect('p.price_buy');
        }

        if (findModule(\Modules::PRICELISTS)) {
            $pricelistContext = ServiceContainer::getService(\KupShop\PricelistBundle\Context\PricelistContext::class);

            if ($pricelistContext->getActiveId()) {
                $qb->andWhere(\KupShop\PricelistBundle\Query\Product::applyPricelist($pricelistContext->getActiveId(), $this->variationId ?? null));
            }
        }

        $qb->andWhere(
            Query\Translation::coalesceTranslatedFields(
                \KupShop\I18nBundle\Translations\ProductsTranslation::class
            )
        );

        if (findModule(Modules::PRODUCTS, Modules::SUB_UNITS)) {
            $qb->leftJoin('p', 'products_units', 'pu', 'p.unit=pu.id')
                ->addSelect('p.unit, pu.short_name, pu.short_name_admin, pu.long_name as unit_long_name')
                ->andWhere(Translation::coalesceTranslatedFields(ProductsUnitsTranslation::class));
        }

        if (findModule(Modules::PRODUCTS, Modules::SUB_UNITS_FLOAT)) {
            $qb->addSelect('pu.pieces_precision');
        }

        if (findModule(Modules::OSS_VATS)) {
            $qb->addSelect('p.id_cn');
        }

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

        if (findModule(\Modules::COMPONENTS)) {
            $qb->addSelect(\Query\Product::withProductPhotoId());
        }

        return $qb;
    }

    /**
     * Bacha! Může vracet i null - například ve fulltextu, kde se volá createFromArray, ale není selectnutý field price.
     *
     * @return ProductPrice|null
     */
    public function getProductPrice()
    {
        return $this->productPrice;
    }

    public function setProductPrice($productPrice): void
    {
        $this->productPrice = $productPrice;
    }

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

        $multiFetch = ServiceContainer::getService(MultiFetch::class);
        $multiFetch->fetchBonusPoints(new ProductCollection([$this->id => $this]));
    }

    protected function calculateBonusPoints($id_variation = null): Decimal
    {
        $bonusComputer = ServiceContainer::getService(BonusComputer::class);

        return $bonusComputer->getProductsBonusPoints($this, $id_variation);
    }

    public function getBonusPoints($id_variation = null): ?Decimal
    {
        if (!findModule(Modules::BONUS_PROGRAM)) {
            return null;
        }

        $id_variation = $id_variation ?: $this->matched_id_variation ?: null;

        if (isset($this->bonus_points_cache[$id_variation])) {
            return $this->bonus_points_cache[$id_variation];
        }

        if ($id_variation) {
            $variation = $this->findVariation($id_variation);
            if (isset($variation['bonus_points'])) { // rucni nastaveni bodu primo u varianty
                return $this->bonus_points_cache[$id_variation] = toDecimal($variation['bonus_points']);
            }
        }

        if ($this->bonus_points) { // rucni nastaveni bodu primo u produktu
            return $this->bonus_points_cache[$id_variation] = $this->bonus_points;
        }

        return $this->bonus_points_cache[$id_variation] = $this->calculateBonusPoints($id_variation);
    }

    private function fetchUnit($data)
    {
        if (empty($data['unit'])) {
            $this->unit = [];

            return;
        }

        $this->unit = [
            'id' => $data['unit'],
            'short_name' => $data['short_name'],
            'short_name_admin' => $data['short_name_admin'] ?? $data['short_name'],
            'long_name' => $data['unit_long_name'],
        ];

        if (findModule(Modules::PRODUCTS, Modules::SUB_UNITS_FLOAT)) {
            switch ($data['pieces_precision']) {
                case 0.0001: $this->unit['pieces_precision'] = 4;
                    break;
                case 0.001: $this->unit['pieces_precision'] = 3;
                    break;
                case 0.01: $this->unit['pieces_precision'] = 2;
                    break;
                case 0.1: $this->unit['pieces_precision'] = 1;
                    break;
                case 0:
                default: $this->unit['pieces_precision'] = 0;
            }

            $this->unit['step'] = $data['pieces_precision'];
        }
    }

    /**
     * @return array|mixed
     */
    public function getData()
    {
        if (!$this->data) {
            return [];
        }

        return json_decode($this->data, true);
    }

    /**
     * @return bool
     */
    public function isVirtual()
    {
        $generate_coupon = ($this->getData()['generate_coupon'] ?? 'N');
        if ($generate_coupon == 'Y') {
            return true;
        }

        return false;
    }

    /**
     * @return string
     */
    public function getCN()
    {
        return $this->id_cn ?? Settings::getDefault()['oss_vats']['default'] ?? '';
    }
}

if (empty($subclass)) {
    class Product extends ProductBase
    {
    }
}