<?php

declare(strict_types=1);

namespace KupShop\CatalogBundle\Util;

use KupShop\CatalogBundle\Enum\Reviews\ReviewsOrdering;
use KupShop\I18nBundle\Translations\ProductsTranslation;
use KupShop\I18nBundle\Translations\ReviewsTranslation;
use KupShop\KupShopBundle\Context\CountryContext;
use KupShop\KupShopBundle\Context\LanguageContext;
use KupShop\KupShopBundle\Context\UserContext;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\KupShopBundle\Util\StringUtil;
use KupShop\RewriteBundle\Util\Rewrite;
use Query\Operator;
use Query\QueryBuilder;
use Query\Translation;

class ReviewsUtil
{
    use \DatabaseCommunication;

    public const RANK_UNCONFIRMED = 1;
    public const RANK_CONFIRMED = 3;
    public const RANK_TOP = 5;
    public const RANK_DECLINED = 0;

    public static $rank = [
        self::RANK_UNCONFIRMED => 'not_confirmed',
        self::RANK_CONFIRMED => 'confirmed',
        self::RANK_TOP => 'top',
        self::RANK_DECLINED => 'declined',
    ];

    public static $sources = [
        self::SOURCE_HEUREKA_CZ => self::SOURCE_HEUREKA_CZ,
        self::SOURCE_HEUREKA_SK => self::SOURCE_HEUREKA_SK,
        self::SOURCE_ZBOZI_CZ => self::SOURCE_ZBOZI_CZ,
        self::SOURCE_ARUKERESO_HU => self::SOURCE_ARUKERESO_HU,
        self::SOURCE_COMPARI_RO => self::SOURCE_COMPARI_RO,
    ];

    public const SOURCE_HEUREKA_CZ = 'heureka.cz';
    public const SOURCE_HEUREKA_SK = 'heureka.sk';
    public const SOURCE_ZBOZI_CZ = 'zbozi.cz';
    public const SOURCE_ARUKERESO_HU = 'arukereso.hu';
    public const SOURCE_COMPARI_RO = 'compari.ro';

    private $error;

    public function getQueryBuilder(): QueryBuilder
    {
        $verifiedFields = ['r.source', 'r.id_order'];

        if (findModule(\Modules::SALES)) {
            $verifiedFields[] = 'r.id_sale';
        }

        $verified = 'IF('.implode(' OR ', $verifiedFields).', 1, 0)';

        $qb = sqlQueryBuilder()
            ->select('r.*', "{$verified} as verified")
            ->from('reviews', 'r')
            ->leftJoin('r', 'users', 'u', 'u.id = r.id_user')
            ->leftJoin('r', 'orders', 'o', 'o.id = r.id_order')
            ->orderBy('r.figure', 'DESC')
            ->addOrderBy('r.date', 'DESC')
            ->groupBy('r.id');

        $qb->andWhere('r.figure > '.self::RANK_UNCONFIRMED);

        if (\Settings::getDefault()['review_verified_only'] == 'Y') {
            $qb->andWhere("{$verified} = 1");
        }

        return $qb;
    }

    public function getForProduct($id_product, $translationLanguage = null)
    {
        return $this->getProductReviewsBaseQueryBuilder((int) $id_product, $translationLanguage)
            ->execute()->fetchAllAssociative();
    }

    /**
     * Vraci vsechny recenze + k tomu nacte produkty. Pouziva se pro stranku recenze, kde je mozne prochazet vsechny recenze
     * a pak pripadne pro slider s recenzema napr. na hlavni strance kam potrebujeme dostat treba nahodne recenze ze vsech.
     */
    public function getReviews(?string $translationLanguage = null, array $languages = [], bool $withEmptyReviews = true, ?callable $spec = null): array
    {
        $qb = $this->getProductReviewsQueryBuilder(null, $translationLanguage, $languages, $withEmptyReviews);

        if ($spec) {
            $qb->andWhere($spec);
        }

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

        $productIds = array_unique(array_map(fn ($x) => $x['id_product'], $reviews));

        // fetch products - potrebuju hlavne kvuli obrazkum produktu
        $productList = ServiceContainer::getService(\KupShop\CatalogBundle\ProductList\ProductList::class);
        $productList->andSpec(Operator::inIntArray($productIds, 'p.id'));
        $productList->fetchImages(1);
        $products = $productList->getProducts();

        foreach ($reviews as &$review) {
            $review['product'] = $products[$review['id_product']] ?? null;
        }

        // vratim recenze a i product collectionu, aby se pripadne dalo neco dofetchovat
        return [$products, $reviews];
    }

    public function getReviewsTotals(): array
    {
        $result = $this->getQueryBuilder()
            ->select('AVG(r.rating) as rating, COUNT(distinct r.id) as count')
            ->resetQueryPart('groupBy')
            ->execute()->fetchAssociative();

        return [
            'rating' => $result['rating'] ?? null,
            'count' => $result['count'] ?? 0,
        ];
    }

    public function getProductReviews(int $productId, ?string $translationLanguage = null, array $languages = [], ?int $limit = null, ?int $offset = null, ?ReviewsOrdering $ordering = null): array
    {
        if (findModule(\Modules::TRANSLATIONS) && $translationLanguage === null) {
            $translationLanguage = Contexts::get(LanguageContext::class)->getActiveId();
        }

        $qb = $this->getProductReviewsQueryBuilder($productId, $translationLanguage, $languages);

        if ($limit) {
            $qb->setMaxResults($limit);
        }

        if ($offset) {
            $qb->setFirstResult($offset);
        }

        if ($ordering) {
            $qb->orderBy(
                sort: $ordering->orderBy->value,
                order: $ordering->orderDir->uppercase()
            );
        }

        return $qb->execute()->fetchAllAssociative();
    }

    public function getProductReviewsCount(int $productId, ?string $translationLanguage, array $languages = []): int
    {
        return (int) $this->getProductReviewsQueryBuilder($productId, $translationLanguage, $languages)
            ->select('COUNT(DISTINCT r.id)')
            ->groupBy('r.id_product')
            ->execute()->fetchOne();
    }

    /**
     * Returns an overview of product reviews for a given product.
     *
     * The returned array contains ratings (1–5) as keys and the number of reviews for each rating as values.
     *
     * @return array<int, int> Associative array of [rating => count]
     */
    public function getProductReviewsOverview(int $productId, ?string $translationLanguage, array $languages = []): array
    {
        $result = $this->getProductReviewsQueryBuilder($productId, $translationLanguage, $languages)
            ->select('r.rating, COUNT(distinct r.id) as count')
            ->groupBy('r.rating')
            ->execute()->fetchAllKeyValue();

        $overview = [];

        foreach (range(5, 1) as $i) {
            $overview[$i] = $result[$i] ?? 0;
        }

        return $overview;
    }

    protected function getProductReviewsQueryBuilder(?int $productId, ?string $translationLanguage, array $languages = [], bool $withEmptyReviews = false): QueryBuilder
    {
        $qb = $this->getProductReviewsBaseQueryBuilder($productId, $translationLanguage);

        if (!$withEmptyReviews) {
            // odfiltruju pryc recenze bez textu
            $qb->andWhere(
                $this->emptyReviewSpec()
            );
        }

        // ofiltruju recenze podle jazyka
        if ($translationLanguage) {
            $filterLanguages = [$translationLanguage];
            if (!empty($languages[$translationLanguage])) {
                $filterLanguages = array_merge($filterLanguages, $languages[$translationLanguage]);
            }

            // chci recenze pouze pro aktualni jazyk
            $qb->andWhere(
                Operator::orX(
                    // bud pouze recenze, ktere jsou primo v danem jazyce
                    Operator::inStringArray($filterLanguages, 'r.id_language'),
                    // nebo recenze, ktere jsou prelozene
                    Operator::inStringArray($filterLanguages, "r_{$translationLanguage}.id_language"),
                )
            );
        }

        return $qb;
    }

    protected function getProductReviewsBaseQueryBuilder(?int $productId, ?string $translationLanguage = null): QueryBuilder
    {
        $qb = $this->getQueryBuilder()
            ->addSelect('COALESCE(u.name, o.invoice_name) user_name, COALESCE(u.city, o.invoice_city) user_city, 
                u.gender as user_gender');

        if ($translationLanguage) {
            $qb->andWhere(Translation::coalesceTranslatedFields(ReviewsTranslation::class, ['pros' => 'pros', 'cons' => 'cons', 'summary' => 'summary', 'response' => 'response', 'id_language' => 'translation_language'], $translationLanguage))
                ->addSelect('r.pros as pros_original, r.cons as cons_original, r.summary as summary_original, r.response as response_original');
        }

        if ($productId !== null) {
            if (findModule(\Modules::PRODUCTS_COLLECTIONS) && ('Y' == (\Settings::getDefault()['review_collections'] ?? 'Y'))) {
                $qb->addSelect('pc.id AS id_product_master, p.title AS product_title')
                    ->joinSubQuery('r', \Query\Product::collectionsProductsSubQuery([$productId]), 'pc',
                        'r.id_product = pc.id OR r.id_product = pc.main_product OR r.id_product = pc.related_product')
                    ->join('r', 'products', 'p', 'p.id = r.id_product')
                    ->groupBy('r.id');

                if ($translationLanguage) {
                    $qb->andWhere(Translation::coalesceTranslatedFields(
                        ProductsTranslation::class, ['title' => 'product_title'], $translationLanguage)
                    );
                }
            } else {
                $qb->andWhere(Operator::inIntArray([$productId], 'r.id_product'));
            }
        }

        return $qb;
    }

    public function hasComment($review)
    {
        return !empty($review['summary']) || !empty($review['pros']) || !empty($review['cons']);
    }

    public function hasTranslatedComment($review)
    {
        return ($review['translation_language'] ?? '') != $review['id_language'];
    }

    public function hasUserAlreadyRated($id_product, $id_user = null)
    {
        if (!$id_user) {
            $id_user = Contexts::get(UserContext::class)->getActiveId();
        }

        if (!$id_user) {
            return false;
        }

        return $this->selectSQL('reviews', ['id_user' => $id_user, 'id_product' => $id_product])->rowCount() > 0;
    }

    public function hasUserBoughtProduct($id_product, $id_user = null, $email = null, $orderNo = null)
    {
        if (!$id_user && !$email) {
            if (!($id_user = Contexts::get(UserContext::class)->getActiveId())) {
                return false;
            }
        }

        if (!($result = $this->hasUserBoughtProductByType((int) $id_product, 'order', $id_user, $email, $orderNo))) {
            $result = $this->hasUserBoughtProductByType((int) $id_product, 'sale', $id_user, $email, $orderNo);
        }

        return $result;
    }

    private function hasUserBoughtProductByType(int $productId, string $type = 'order', ?int $userId = null, ?string $email = null, ?string $code = null): ?array
    {
        if ($type === 'sale') {
            if (!findModule(\Modules::SALES)) {
                return null;
            }

            $qb = sqlQueryBuilder()
                ->select('s.id, si.id_product, "sale" as type')
                ->from('sales_items', 'si')
                ->join('si', 'sales', 's', 's.id = si.id_sale')
                ->leftJoin('s', 'users', 'u', 'u.id = s.id_user')
                ->andWhere(static::getUserBoughtProductSpec($productId, $userId, 's', 'si'));

            if ($email && $code) {
                $qb->andWhere(Operator::equals(['s.code' => $code, 'u.email' => $email]));
            }

            return $qb->execute()->fetchAssociative() ?: null;
        }

        $qb = sqlQueryBuilder()->select('o.id, oi.id_product, "order" as type, order_no')
            ->from('order_items', 'oi')
            ->join('oi', 'orders', 'o', 'oi.id_order = o.id')
            ->andWhere(static::getUserBoughtProductSpec($productId, $userId));

        if ($email && $code) {
            $qb->andWhere(Operator::equals(['o.order_no' => $code, 'o.invoice_email' => $email]));
        }

        return $qb->execute()->fetchAssociative() ?: null;
    }

    private static function getUserBoughtProductSpec(int $productId, ?int $userId, string $objectAlias = 'o', string $itemsAlias = 'oi'): callable
    {
        return function (QueryBuilder $qb) use ($productId, $userId, $objectAlias, $itemsAlias) {
            $andX = [];

            if ($userId) {
                $andX[] = Operator::equals(["{$objectAlias}.id_user" => $userId]);
            }

            if (findModule(\Modules::PRODUCTS_COLLECTIONS) && ('Y' == (\Settings::getDefault()['review_collections'] ?? 'Y'))) {
                $qb->setParameter('ids', [$productId], \Doctrine\DBAL\Connection::PARAM_INT_ARRAY)
                    ->orderBy("{$itemsAlias}.id_product = {$productId}", 'DESC');

                $collectionProducts = \Query\Product::inCollectionsProducts($qb, [$productId]);

                $andX[] = Operator::orX(
                    Operator::equals(["{$itemsAlias}.id_product" => $productId]),
                    "{$itemsAlias}.id_product IN ({$collectionProducts})"
                );
            } else {
                $andX[] = Operator::equals(["{$itemsAlias}.id_product" => $productId]);
            }

            return Operator::andX($andX);
        };
    }

    /**
     * @return bool
     *
     * @throws Exception
     */
    public function importReviews($key = null)
    {
        $count = 0;
        if (empty($key)) {
            $dbcfg = \Settings::getDefault();

            if (!empty($dbcfg['analytics']['heureka_overeno']['ID'])) {
                $key = $dbcfg['analytics']['heureka_overeno']['ID'];
                $count += $this->getHeurekaReviewsByKey($key, 'cs', 'https://www.heureka.cz/direct/dotaznik/export-product-review.php?key=');
            }

            if (findModule(\Modules::TRANSLATIONS)) {
                $skSettings = \Settings::getFromCache('sk');
                $heurekaSkKey = $skSettings['analytics']['heureka_overeno']['ID'] ?? null;
            } else {
                $heurekaSkKey = $dbcfg->analytics['heureka_overeno']['ID_SK'] ?? null;
            }

            if (!empty($heurekaSkKey)) {
                $count += $this->getHeurekaReviewsByKey($heurekaSkKey, 'sk', 'https://www.heureka.sk/direct/dotaznik/export-product-review.php?key=');
            }
        }

        $zbozi_cz = $dbcfg['analytics']['zbozi_cz_feedback'] ?? null;
        if (!empty($zbozi_cz['API_key'])) {
            $key = $zbozi_cz['API_key'];
            $id = $zbozi_cz['ID'];
            $this->importReviewsZbozi($key, $id);
        }

        foreach (array_intersect(array_keys(Contexts::get(CountryContext::class)->getAll()), ['HU', 'RO']) as $lang) {
            $lang = strtolower($lang);
            $dbcfg = \Settings::getFromCache($lang);
            if (!empty($dbcfg['analytics']['trusted_shop']['id'])) {
                $count += $this->importArukeresoAndCompari($dbcfg['analytics']['trusted_shop']['id'], $lang);
            }
        }

        return $count;
    }

    private function importArukeresoAndCompari(string $key, string $lang)
    {
        $savedCount = 0;
        $url = $lang == 'hu' ? 'https://www.arukereso.hu/admin/gettrustedproductreview.php?t=xml&wak=' : 'https://www.compari.ro/admin/gettrustedproductreview.php?t=xml&wak=';
        $res = curl_init();
        curl_setopt($res, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($res, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($res, CURLOPT_URL, $url.$key);

        $result = curl_exec($res);

        curl_close($res);

        try {
            $xml = simplexml_load_string($result);
        } catch (\Exception $e) {
            $xml = false;
        }

        if ($xml === false) {
            return false;
        }

        sqlGetConnection()->transactional(function () use ($xml, &$savedCount, $lang) {
            $reviewIDsForImport = [];
            $skipIDs = [];

            foreach ($xml->ProductReview as $review) {
                $reviewIDsForImport[] = $this->getExternalId($review->ReviewDate, $lang);
            }

            $result = sqlQueryBuilder()->select('external_id')->from('reviews')
                ->where(Operator::inIntArray($reviewIDsForImport, 'external_id'))
                ->andWhere(Operator::equals(['source' => $lang == 'hu' ? self::SOURCE_ARUKERESO_HU : self::SOURCE_COMPARI_RO]))
                ->execute()->fetchAll();
            foreach ($result as $row) {
                $skipIDs[] = $row['external_id'];
            }

            foreach ($xml->ProductReview as $review) {
                $externalId = $this->getExternalId($review->ReviewDate, $lang);

                if (in_array($externalId, $reviewIDsForImport)
                    && !in_array($externalId, $skipIDs)
                    && $this->selectSQL('products', ['id' => $review->ItemCode])->rowCount()
                ) {
                    $values = [
                        'external_id' => $externalId,
                        'id_product' => trim((string) $review->ItemCode),
                        'figure' => static::RANK_UNCONFIRMED,
                        'date' => isset($review->ReviewDate) ? trim((string) $review->ReviewDate) : null,
                        'rating' => isset($review->SUM_Values) ? (float) $review->SUM_Values : null,
                        'pros' => isset($review->Pros) ? trim((string) $review->Pros) : null,
                        'cons' => isset($review->Cons) ? trim((string) $review->Cons) : null,
                        'summary' => isset($review->AspectsOfChoice) ? trim((string) $review->AspectsOfChoice) : null,
                        'source' => $lang == 'hu' ? self::SOURCE_ARUKERESO_HU : self::SOURCE_COMPARI_RO,
                    ];

                    if (findModule(\Modules::TRANSLATIONS)) {
                        $values['id_language'] = $lang;
                    }

                    $this->insertSQL('reviews', $values);

                    $skipIDs[] = $externalId;
                    $savedCount++;
                }
            }
        });

        return $savedCount;
    }

    private function getExternalId($string, $lang)
    {
        // compari a arukereso nemaji id hodnoceni, tak si vytvorime z datumu a jazyka
        return $lang.'_'.$string;
    }

    public function autoConfirm()
    {
        $dbcfg = \Settings::getDefault();

        if (!empty($dbcfg->review_automatic_confirm)) {
            return sqlQueryBuilder()->update('reviews')
                ->set('figure', self::RANK_CONFIRMED)
                ->where('date < DATE_SUB(NOW(), INTERVAL :review_automatic_confirm DAY)')
                ->andWhere('figure = '.self::RANK_UNCONFIRMED)
                ->setParameter('review_automatic_confirm', $dbcfg->review_automatic_confirm)
                ->execute();
        }

        return 0;
    }

    public function importReviewsZbozi($key, $id)
    {
        // load data from url
        $res = curl_init();
        curl_setopt($res, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($res, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($res, CURLOPT_URL, 'https://api.zbozi.cz/v1/shop/product-reviews?limit=1000');
        curl_setopt($res, CURLOPT_USERPWD, $id.':'.$key);

        $result = curl_exec($res);

        curl_close($res);

        $json_data = json_decode($result, true);
        if (empty($data = ($json_data['data'] ?? null))) {
            return;
        }

        $reviewIDsForImport = array_map(function ($call) {
            return $call['productReviewId'];
        }, $data);
        // nechceme importovat duplicity po uprave delky external_id v tabulce reviews
        // predtim se external_id orezavalo na 32 znaky
        $reviewIDsForImport32 = array_map(function ($productReviewId) {
            return substr($productReviewId, 0, 32);
        }, $reviewIDsForImport);
        $reviewIDsForImport = array_merge($reviewIDsForImport, $reviewIDsForImport32);

        // ignore already saved reviews
        $skipIDs = sqlFetchAll(sqlQueryBuilder()->select('external_id')->from('reviews')
            ->where(Operator::inStringArray($reviewIDsForImport, 'external_id'))
            ->andWhere(Operator::equals(['source' => self::SOURCE_ZBOZI_CZ]))
            ->execute(),
            'external_id');
        // save new reviews
        foreach ($data as $values) {
            if (!isset($skipIDs[$values['productReviewId']]) && !isset($skipIDs[substr($values['productReviewId'], 0, 32)])) {
                // save reviews when have id of product/ variation
                if (findModule('marketing', 'product_identifier') == 'code') {
                    $product = sqlQueryBuilder()->select('p.id as id_product, pv.id as id_variation')
                        ->fromProducts()
                        ->joinVariationsOnProducts()
                        ->where(Operator::equals(['p.code' => $values['productData']['itemId'],
                            'pv.code' => $values['productData']['itemId'], ], 'OR'))
                        ->execute()->fetch();
                    if (!$product) {
                        continue;
                    }
                } else {
                    $productID = explode('_', $values['productData']['itemId']);
                    $product = ['id_product' => $productID[0], 'id_variation' => isset($productID[1]) ? $productID[1] : null];
                }
                $saving_reviews = [
                    'external_id' => $values['productReviewId'],
                    'id_product' => isset($product['id_product']) ? $product['id_product'] : $values['productData']['itemId'],
                    'id_product_variation' => isset($product['id_variation']) ? $product['id_variation'] : null,
                    'figure' => static::RANK_UNCONFIRMED,
                    'date' => $this->prepareDateTime((new \DateTime())->setTimestamp((int) $values['createTimestamp'])),
                    'rating' => isset($values['ratingStars']) ? (float) $values['ratingStars'] : null,
                    'pros' => isset($values['positiveComments']) ? trim((string) $values['positiveComments']) : null,
                    'cons' => isset($values['negativeComments']) ? trim((string) $values['negativeComments']) : null,
                    'summary' => isset($values['text']) ? trim((string) $values['text']) : null,
                    'name' => isset($values['userName']) ? trim((string) $values['userName']) : null,
                    'source' => self::SOURCE_ZBOZI_CZ,
                ];
                try {
                    $this->insertSQL('reviews', $saving_reviews);
                } catch (\Exception $e) {
                    break;
                }
                $skipIDs[$values['productReviewId']] = 1;
            }
        }
    }

    private function getHeurekaReviewsByKey($key, $lang, $url)
    {
        $res = curl_init();
        curl_setopt($res, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($res, CURLOPT_RETURNTRANSFER, true);
        // curl_setopt($res, CURLOPT_FOLLOWLOCATION, true);  // Does not work in open_basedir
        curl_setopt($res, CURLOPT_URL, $url.$key);

        $result = curl_exec($res);

        curl_close($res);

        try {
            $xml = simplexml_load_string($result);
        } catch (\Exception $e) {
            $xml = false;
        }

        if ($xml === false) {
            if (StringUtil::startsWith($result, 'INFO')) {
                $this->error = $result;
            } else {
                $this->error = $key.' -> Invalid xml';
                echo $key.' -> Invalid xml<br/>';
            }

            return false;
        }

        $savedCount = 0;

        sqlGetConnection()->transactional(function () use ($xml, &$savedCount, $lang) {
            $reviewIDsForImport = [];
            // rating_id is not as unique as it should be, so we skip duplicates
            $skipIDs = [];

            foreach ($xml->product as $product) {
                foreach ($product->reviews->children() as $review) {
                    if (trim((string) $review->rating_id_type) === 'offer') {
                        // https://sluzby.heureka.cz/napoveda/widget-a-ikonky-ze-sluzby-overeno-zakazniky/
                        // rating_id_type (offer|product) - Recenze je vázána na nabídku (offer) nebo na produkt - ovlivňuje rating_id.
                        continue; // ignore offer reviews to avoid duplicates
                    }
                    $reviewIDsForImport[] = (int) $review->rating_id;
                }
            }

            // ignore already saved reviews
            $result = sqlQueryBuilder()->select('external_id')->from('reviews')
                ->where(Operator::inIntArray($reviewIDsForImport, 'external_id'))
                ->andWhere(Operator::equals(['source' => $lang == 'cs' ? self::SOURCE_HEUREKA_CZ : self::SOURCE_HEUREKA_SK]))
                ->execute()->fetchAll();
            foreach ($result as $row) {
                $skipIDs[] = $row['external_id'];
            }

            // save new reviews
            foreach ($xml->product as $product) {
                $shop_product = null;

                foreach ($product->reviews->children() as $review) {
                    if (in_array((int) $review->rating_id, $reviewIDsForImport)
                        && !in_array((int) $review->rating_id, $skipIDs)
                    ) {
                        if (!$shop_product) {
                            $shop_product = $this->findReviewProduct($product);

                            if (!$shop_product) {
                                break;
                            }
                        }

                        $order = $this->findReviewOrder($product);

                        $values = [
                            'external_id' => (int) $review->rating_id,
                            'id_order' => $order ? $order->id : null,
                            'id_user' => $order ? $order->id_user : null,
                            'id_product' => $shop_product['id_product'],
                            'id_product_variation' => $shop_product['id_variation'],
                            'figure' => static::RANK_UNCONFIRMED,
                            'date' => $this->prepareDateTime((new \DateTime())->setTimestamp((int) $review->unix_timestamp)),
                            'rating' => isset($review->rating) ? (float) $review->rating : null,
                            'recommends' => isset($review->recommends) ? (int) $review->recommends : null,
                            'pros' => isset($review->pros) ? trim((string) $review->pros) : null,
                            'cons' => isset($review->cons) ? trim((string) $review->cons) : null,
                            'summary' => isset($review->summary) ? trim((string) $review->summary) : null,
                            'name' => isset($review->name) ? trim((string) $review->name) : null,
                            'source' => $lang == 'cs' ? self::SOURCE_HEUREKA_CZ : self::SOURCE_HEUREKA_SK,
                        ];

                        if (findModule(\Modules::TRANSLATIONS)) {
                            $values['id_language'] = $lang;
                        }

                        $this->insertSQL('reviews', $values);

                        $skipIDs[] = (int) $review->rating_id;
                        $savedCount++;
                    }
                }
            }
        });

        return $savedCount;
    }

    public function removeDuplicates()
    {
        $conn = sqlGetConnection();
        try {
            $conn->beginTransaction();

            sqlQuery('DELETE r1 FROM reviews r1, reviews r2
             WHERE (
                     (r1.id_product > 0 AND r2.id_product > 0) AND r1.id_product = r2.id_product
                     OR r1.id_product_variation = r2.id_product_variation
                 )
                 AND r1.rating = r2.rating
                 AND (r2.summary IS NOT NULL OR r2.pros IS NOT NULL OR r2.cons IS NOT NULL)
                 AND ((r1.summary IS NULL AND r2.summary IS NULL) OR r1.summary = r2.summary)
                 AND ((r1.pros IS NULL AND r2.pros IS NULL) OR r1.pros = r2.pros)
                 AND ((r1.cons IS NULL AND r2.cons IS NULL) OR r1.cons = r2.cons)
                 AND r1.external_id IS NULL AND r2.external_id IS NOT NULL;

            DELETE r1 FROM reviews r1, reviews r2
                WHERE (
                        (r1.id_product > 0 AND r2.id_product > 0) AND r1.id_product = r2.id_product
                        OR r1.id_product_variation = r2.id_product_variation
                    )
                    AND r1.rating = r2.rating
                    AND (r2.summary IS NOT NULL OR r2.pros IS NOT NULL OR r2.cons IS NOT NULL)
                    AND ((r1.summary IS NULL AND r2.summary IS NULL) OR r1.summary = r2.summary)
                    AND ((r1.pros IS NULL AND r2.pros IS NULL) OR r1.pros = r2.pros)
                    AND ((r1.cons IS NULL AND r2.cons IS NULL) OR r1.cons = r2.cons)
                    AND r1.date < r2.date;

            DELETE r1 FROM reviews r1, reviews r2
                WHERE (
                        (r1.id_product > 0 AND r2.id_product > 0) AND r1.id_product = r2.id_product
                        OR r1.id_product_variation = r2.id_product_variation
                    )
                    AND r1.rating = r2.rating
                    AND (r2.summary IS NOT NULL OR r2.pros IS NOT NULL OR r2.cons IS NOT NULL)
                    AND ((r1.summary IS NULL AND r2.summary IS NULL) OR r1.summary = r2.summary)
                    AND ((r1.pros IS NULL AND r2.pros IS NULL) OR r1.pros = r2.pros)
                    AND ((r1.cons IS NULL AND r2.cons IS NULL) OR r1.cons = r2.cons)
                    AND r1.id < r2.id;
         ');

            $conn->commit();
        } catch (\Exception $e) {
            $conn->rollBack();
            throw $e;
        }
    }

    public function findReviewProduct($product)
    {
        $regex = '@_z(\d+)([^=]+=(\d+))?@i';

        if (!($matchOK = preg_match($regex, (string) $product->url, $matches))) {
            // also try canonical version of product URL
            if (findModule(\Modules::DB_REWRITE)) {
                $rewriteBundle = ServiceContainer::getService(Rewrite::class);
                $newUrl = $rewriteBundle->resolve(parse_url((string) $product->url, PHP_URL_PATH));
            } else {
                $rewrite = new \Rewrite();
                $newUrl = $rewrite->resolve(parse_url((string) $product->url, PHP_URL_PATH));
            }
            $matches = null;
            $matchOK = $newUrl ? preg_match($regex, $newUrl, $matches) : false;
        }

        if ($matchOK) {
            $product = ['id_variation' => $matches[3] ?? null, 'id_product' => $matches[1]];

            if ($product['id_variation']) {
                if ($this->selectSQL('products_variations',
                    ['id' => $product['id_variation'], 'id_product' => $product['id_product']])->rowCount()
                ) {
                    return $product;
                }

                $product['id_variation'] = null;
            }

            if ($this->selectSQL('products', ['id' => $product['id_product']])->rowCount()) {
                return $product;
            }
        }

        return null;
    }

    /**
     * @return \Order|null
     */
    private function findReviewOrder($product)
    {
        try {
            return \Order::createFromDbOrderNo($product->order_id);
        } catch (\InvalidArgumentException $e) {
            return null;
        }
    }

    public function getError()
    {
        return $this->error;
    }

    protected function emptyReviewSpec(): string|callable
    {
        return Operator::orX(
            'r.summary IS NOT NULL AND r.summary != ""',
            'r.pros IS NOT NULL AND r.pros != ""',
            'r.cons IS NOT NULL AND r.cons != ""'
        );
    }
}