<?php

namespace Query;

use Doctrine\DBAL\Connection;
use FilterBase as FilterBaseAlias;
use KupShop\CatalogBundle\Query\Search;
use KupShop\CatalogBundle\Util\FavoriteProductsUtil;
use KupShop\I18nBundle\Translations\ProductsTranslation;
use KupShop\KupShopBundle\Context\UserContext;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\LabelsBundle\Util\LabelFilterSpecs;
use KupShop\QuantityDiscountBundle\Context\QuantityDiscountContext;
use KupShop\QuantityDiscountBundle\Query\QuantityDiscount;
use KupShop\StoresBundle\Query\StoresQuery;

class FilterBase
{
    public static $paramColumn = [
        \Filter::PARAM_INT => 'value_float',
        \Filter::PARAM_FLOAT => 'value_float',
        \Filter::PARAM_FLOAT_LIST => 'value_float',
        \Filter::PARAM_CHAR => 'value_char',
        \Filter::PARAM_LIST => 'value_list',
    ];

    public static function inSale($inSale)
    {
        if ($inSale === true) {
            return Operator::andX('p.discount > 0');
        }
    }

    /**
     * @return \Closure|null
     *
     * @throws \Exception
     */
    public static function parametersFilterUtil(array $parameters, ?callable $filterConditionCallback = null, bool $useIntersect = true)
    {
        $filterConditionCallback = $filterConditionCallback ?: function ($alias, $paramId, $paramData) {
            return Operator::andX(
                self::rangeOrIn($alias, $paramData, $paramId),
                ($paramData['type'] != \Filter::PARAM_LIST) ? Operator::equals(["{$alias}.id_parameter" => $paramId]) : null // id_parameter is determined by value_list
            );
        };

        static $counter = 0;

        $qbs = [];
        foreach ($parameters as $paramId => $paramData) {
            $alias = 'pp_'.$counter++;
            $select = sqlQueryBuilder()
                ->select($alias.'.id_product')
                ->from('parameters_products', $alias)
                ->where($filterConditionCallback($alias, $paramId, $paramData));
            $qbs[] = $select;
        }

        if (empty($qbs)) {
            return null;
        }

        $existsSubquery = sqlQueryBuilder()->select('1')->where('fil_insct.id_product = p.id');

        $resultOperator = ($useIntersect) ? 'INTERSECT' : 'UNION';

        $intersectSubquery = implode(
            " {$resultOperator} ",
            array_map(
                function ($sql) use ($existsSubquery) {
                    $existsSubquery->addParameters($sql->getParameters(), $sql->getParameterTypes());

                    return '('.$sql->getSql().')';
                },
                $qbs
            )
        );

        return Operator::exists(
            $existsSubquery->from("({$intersectSubquery})", 'fil_insct')
        );
    }

    /**
     * @throws \Exception
     */
    public static function variationsFilterUtil(
        array $variationIds,
        ?callable $filterConditionCallback = null,
        bool $useIntersect = true,
    ): ?\Closure {
        $filterConditionCallback = $filterConditionCallback ?: function ($alias, $variationValueID) {
            return Operator::andX(
                Operator::equals(["{$alias}.id_value" => $variationValueID])
            );
        };
        $qbs = [];
        static $counter = 0;
        foreach ($variationIds as $variationValueID) {
            $alias = 'pvc_'.$counter++;
            $select = sqlQueryBuilder()
                ->select($alias.'.id_variation')
                ->from('products_variations_combination', $alias)
                ->where($filterConditionCallback($alias, $variationValueID));
            $qbs[] = $select;
        }

        if (empty($qbs)) {
            return null;
        }

        $existsSubquery = sqlQueryBuilder()->select('1')->where('fil_insct.id_variation = pv.id');

        $resultOperator = ($useIntersect) ? 'INTERSECT' : 'UNION';

        $intersectSubquery = implode(
            " {$resultOperator} ",
            array_map(
                function ($sql) use ($existsSubquery) {
                    $existsSubquery->addParameters($sql->getParameters(), $sql->getParameterTypes());

                    return '('.$sql->getSql().')';
                },
                $qbs
            )
        );

        return Operator::exists(
            $existsSubquery->from("({$intersectSubquery})", 'fil_insct')
        );
    }

    /**
     * @param array $parameters
     *                              [paramId => ['type' => \Filter::PARAM_(LIST|CHAR), 'values' => [value1, ...]]]
     *                              [paramId => ['type' => \Filter::PARAM_(INT|FLOAT), 'values' => [min => x, max => y] ]]
     * @param int   $excludeParamId
     *
     * @return callable
     */
    public static function byParameters(array $parameters, $excludeParamId = null)
    {
        if ($excludeParamId) {
            unset($parameters[$excludeParamId]);
        }

        return function (QueryBuilder $qb) use ($parameters) {
            return self::parametersFilterUtil($parameters);
        };
    }

    public static function byAndParameters(array $parameters): ?\Closure
    {
        $operators = [];
        foreach ($parameters as $paramId => $paramData) {
            $operators[] = self::andParameter($paramData, $paramId);
        }

        return Operator::andX($operators);
    }

    public static function byInvertParameters(array $parameters)
    {
        return function (QueryBuilder $qb) use ($parameters) {
            return Operator::not(self::parametersFilterUtil($parameters));
        };
    }

    protected static function andParameter($paramInfo, $paramId)
    {
        $expressionSpec = [
            FilterBaseAlias::PARAM_CHAR => 'self::andSpec',
            FilterBaseAlias::PARAM_LIST => 'self::andSpec',
            FilterBaseAlias::PARAM_FLOAT_LIST => 'self::andSpec',
        ];

        if (!is_string($paramInfo['type'] ?? null) || !array_key_exists($paramInfo['type'], $expressionSpec)) {
            return '';
        }

        $fieldName = self::$paramColumn[$paramInfo['type']];

        return call_user_func_array($expressionSpec[$paramInfo['type']], [$paramInfo, $paramId, $fieldName]);
    }

    protected static function rangeOrIn($alias, $paramInfo, $paramId)
    {
        $expressionSpec = [
            \Filter::PARAM_INT => 'self::minMaxSpec',
            \Filter::PARAM_FLOAT => 'self::minMaxSpec',
            \Filter::PARAM_CHAR => 'self::inSpec',
            \Filter::PARAM_LIST => 'self::inSpec',
            \Filter::PARAM_FLOAT_LIST => 'self::inSpec',
        ];

        if (!is_string($paramInfo['type'] ?? null) || !array_key_exists($paramInfo['type'], $expressionSpec)) {
            return '';
        }

        $fieldName = $alias.'.'.self::$paramColumn[$paramInfo['type']];

        return call_user_func_array($expressionSpec[$paramInfo['type']], [$paramInfo, $paramId, $fieldName]);
    }

    protected static function inSpec(array $paramInfo, $paramId, $fieldName)
    {
        return function (QueryBuilder $qb) use ($paramInfo, $paramId, $fieldName) {
            // float-list => float_list
            // can't have '-' in param name (An exception occurred: Value for :float not found in params array.)
            $param_type = str_replace('-', '_', $paramInfo['type']);
            $sqlParamName = ':'.$param_type.$paramId;
            $bindType = $paramInfo['type'] == \Filter::PARAM_LIST ? Connection::PARAM_INT_ARRAY : Connection::PARAM_STR_ARRAY;
            $qb->setParameter($sqlParamName, $paramInfo['values'], $bindType);

            return $qb->expr()->in($fieldName, $sqlParamName);
        };
    }

    protected static function andSpec(array $paramInfo, $paramId, $fieldName): \Closure
    {
        return function () use ($paramInfo, $fieldName) {
            $operands = [];
            $counter = 0;
            foreach ($paramInfo['values'] as $value) {
                $subQbAlias = 'pp_sub_'.$counter++;
                $existsSubQb = sqlQueryBuilder()
                    ->select('1')
                    ->from('parameters_products', $subQbAlias)
                    ->andWhere(Operator::andX(
                        Operator::equals([$subQbAlias.'.'.$fieldName => $value]),
                        'p.id ='.$subQbAlias.'.id_product'
                    ));
                $operands[] = Operator::exists($existsSubQb);
            }

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

    protected static function minMaxSpec(array $paramInfo, $paramId, $fieldName)
    {
        return Operator::between($fieldName, new \Range($paramInfo['values']['min'], $paramInfo['values']['max']));
    }

    /**
     * Filters by coalesce(variant.price, product.price).
     *
     * @return callable
     */
    public static function byPriceRange(\Range $priceRange)
    {
        return self::doFilterByPriceRange($priceRange, 'COALESCE(pv.price, p.price)');
    }

    public static function byDiscountRange(\Range $discountRange)
    {
        return function (QueryBuilder $qb) use ($discountRange) {
            $discountFieldDefinition = Contexts::get(UserContext::class)->getOriginalPriceType()->getDiscountFieldDefinition();

            $qb->andWhere($discountFieldDefinition->getSpec());

            return self::minMaxSpec(['values' => [
                'min' => $discountRange->min(),
                'max' => $discountRange->max(),
            ]], null, "ROUND({$discountFieldDefinition->getField()}, 1)");
        };
    }

    /**
     * Filters by product.price only.
     *
     * @return callable
     */
    public static function byProductPriceRange(\Range $priceRange)
    {
        return self::doFilterByPriceRange($priceRange, 'p.price');
    }

    protected static function doFilterByPriceRange(\Range $priceRange, $priceField)
    {
        return function (QueryBuilder $qb) use ($priceRange, $priceField) {
            if ($priceRange->isNotNull()) {
                $qb->joinVatsOnProducts();
            }

            return Operator::between(Product::withVatAndDiscount($qb, $priceField), $priceRange);
        };
    }

    /**
     * Filter in_store range by coalesce(variant.in_store, product.in_store).
     *
     * @return callable
     */
    public static function byInStoreRange(\Range $inStoreRange)
    {
        return self::doFilterByInStoreRange($inStoreRange, 'COALESCE(pv.in_store, p.in_store)');
    }

    /**
     * Filters by product.in_store only.
     *
     * @return callable
     */
    public static function byProductInStoreRange(\Range $inStoreRange)
    {
        return self::doFilterByInStoreRange($inStoreRange, 'p.in_store');
    }

    protected static function doFilterByInStoreRange(\Range $inStoreRange, $inStoreField)
    {
        return function (QueryBuilder $qb) use ($inStoreRange, $inStoreField) {
            if ($inStoreRange->isNotNull()) {
                $qb->joinVariationsOnProducts();
            }

            return Operator::between($inStoreField, $inStoreRange);
        };
    }

    public static function byInStore($inStoreMethod, $useVariations = true)
    {
        if ($inStoreMethod == \Filter::IN_STORE_SUPPLIER) {
            return Operator::orX(Product::inStore($useVariations), Product::inStoreSupplier($useVariations));
        }

        return Product::$inStoreMethod($useVariations);
    }

    public static function byProductInStore($inStoreMethod)
    {
        return self::byInStore($inStoreMethod, false);
    }

    public static function byProducers(array $producerIds)
    {
        static $counter = 0;

        return function (QueryBuilder $qb) use ($producerIds, &$counter) {
            $paramName = 'producerIds_'.$counter++;
            $qb->joinProducersOnProducts();
            $qb->setParameter($paramName, $producerIds, Connection::PARAM_INT_ARRAY);

            return $qb->expr()->in('COALESCE(pr.id, 0)', ":{$paramName}");
        };
    }

    public static function byTemplates(array $templateIds)
    {
        return function (QueryBuilder $qb) use ($templateIds) {
            $qb->joinTemplatesOnProducts();
            $qb->andWhere(Operator::inIntArray($templateIds, 't.id'));
        };
    }

    /**
     * @param array $variations
     *                              [labelId => [valueId1, valueId2, ...]]
     * @param int   $excludeLabelId
     *
     * @return callable
     *
     * @todo jedna funkce pro parametry i varianty?
     */
    public static function byVariations(array $variations, $excludeLabelId = null)
    {
        if ($excludeLabelId) {
            unset($variations[$excludeLabelId]);
        }

        return function (QueryBuilder $qb) use ($variations) {
            $qb->joinVariationsOnProducts();
            $andExpressions = [];
            foreach ($variations as $labelId => $valueIds) {
                $alias = 'pvc_'.$labelId;
                $sqlParamLabelId = ':labelId_'.$labelId;
                $sqlParamValueIds = ':valueIds_'.$labelId;
                $qb->join('pv', 'products_variations_combination', $alias, "pv.id = {$alias}.id_variation");
                $andExpressions[] = $qb->expr()->andX(
                    $qb->expr()->in("{$alias}.id_value", $sqlParamValueIds),
                    $qb->expr()->eq("{$alias}.id_label", $sqlParamLabelId)
                );
                $qb->setParameter($sqlParamValueIds, [...$valueIds, -1], Connection::PARAM_INT_ARRAY);
                $qb->setParameter($sqlParamLabelId, $labelId);
            }

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

    public static function byCampaigns(array $campaigns, $operator = 'AND')
    {
        return function (QueryBuilder $qb) use ($campaigns, $operator) {
            $conditions = [];

            // Favorites
            if (($index = array_search('F', $campaigns)) !== false) {
                $userContext = Contexts::get(UserContext::class);
                if ($userContext->getActiveId()) {
                    $qb->join('p', 'products_favorites', 'pf', 'p.id = pf.id_product');
                    $qb->setParameter('id_user', $userContext->getActiveId());
                    $conditions[] = 'pf.id_user = :id_user';
                } else {
                    $conditions[] = Operator::inIntArray(FavoriteProductsUtil::getCookieProducts(), 'p.id');
                }

                unset($campaigns[$index]);
            }

            // Remaining campaigns
            if ($campaigns) {
                $conditions[] = Operator::findInSet($campaigns, \Filter::getCampaignField(), $operator);
            }

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

    public static function byLabels(array $labelIds, bool|array $filterActiveParams = false)
    {
        $null_cat = array_search(-1, $labelIds);
        $labels = sqlQueryBuilder()
            ->select('*')
            ->from('product_labels_relation', 'plr')
            ->andWhere('p.id = plr.id_product');

        if ($filterActiveParams) {
            $labels->leftJoin('plr', 'labels', 'l', 'plr.id_label = l.id');

            $filterActiveParams = array_merge(
                ['active' => true],
                findModule(\Modules::TRANSLATIONS) ? ['translation' => true] : [],
                is_array($filterActiveParams) ? $filterActiveParams : []);

            $productsFilterSpecs = ServiceContainer::getService(LabelFilterSpecs::class);
            $filterSpecs = $productsFilterSpecs->getSpecs($filterActiveParams);
            $labels->andWhere($filterSpecs);
        }

        if ($null_cat !== false) {
            unset($labelIds[$null_cat]);
            $specs[] = Operator::not(Operator::exists($labels));
        } else {
            $labels
                ->andWhere(Operator::inIntArray($labelIds, 'plr.id_label'));
            $specs[] = Operator::exists($labels);
        }

        return Operator::orX($specs);
    }

    public static function byProductCodes(array $productCodes): callable
    {
        return Operator::inStringArray($productCodes, 'p.code');
    }

    public static function byRelatedProducts(array $relatedProducts)
    {
        return function (QueryBuilder $qb) use ($relatedProducts) {
            $qb->join('p', 'products_related', 'pr', 'pr.id_rel_product = p.id');

            return Operator::inIntArray($relatedProducts, 'pr.id_top_product');
        };
    }

    public static function byStores(array $stores): callable
    {
        return StoresQuery::filterByStoresInStore($stores);
    }

    public static function byFigure(string $figure): callable
    {
        static $counter = 0;

        return function (QueryBuilder $qb) use ($figure, &$counter) {
            $paramName = 'visible'.$counter++;
            $expression = null;

            $qb->setParameter($paramName, $figure);
            $qb->andWhere(
                Translation::joinTranslatedFields(
                    ProductsTranslation::class,
                    function (QueryBuilder $qb, $columnName, $translatedField) use (&$expression, $paramName) {
                        if ($translatedField != null) {
                            $expression = "COALESCE({$translatedField}, p.{$columnName}) = :{$paramName}";
                        } else {
                            $expression = "p.figure = :{$paramName}";
                        }

                        return false;
                    },
                    ['figure']
                )
            );

            return $expression;
        };
    }

    public static function byVariationsSizeConvertion(array $variationsSizeConvertion)
    {
        $variations = [];
        $products = [];
        $showOnlyInStore = \Settings::getDefault()['prod_show_not_in_store'] == 'N';

        foreach ($variationsSizeConvertion as $item) {
            foreach ($item['value'] ?? [] as $value) {
                $qb = sqlQueryBuilder()->select('pv.id')
                    ->from('convertors_table_cache', 'ctc')
                    ->join('ctc', 'convertors_table_values', 'ctv', 'ctc.id_table = ctv.id_table')
                    ->join('ctc', 'products_variations', 'pv', 'pv.id_product = ctc.id_product')
                    ->join('pv', 'products_variations_combination', 'pvc', 'pvc.id_variation = pv.id and pvc.id_value = ctv.id_label_value')
                    ->join('pv', 'products', 'p', 'p.id = pv.id_product')
                    ->where(Operator::equals(['ctv.id_parameter_value' => $value]))
                    ->groupBy('pv.id');

                $qbProducts = sqlQueryBuilder()->select('pp.id_product')
                    ->from('parameters_list', 'pl')
                    ->join('pl', 'parameters_products', 'pp', 'pl.id_parameter = pp.id_parameter')
                    ->join('pp', 'products', 'p', 'pp.id_product = p.id')
                    ->andWhere(Operator::equals(['pp.value_list' => $value]))
                    ->groupBy('pp.id_product');

                $ids = sqlFetchAll($qb->execute(), 'id');
                $productsIds = sqlFetchAll($qbProducts->execute(), 'id_product');

                $variations = array_merge($variations, array_keys($ids));
                $products = array_merge($products, array_keys($productsIds));
            }
        }

        return function (QueryBuilder $qb) use ($variations, $products) {
            $qb->joinVariationsOnProducts();

            return Operator::OrX(
                Operator::inIntArray($variations, 'pv.id'),
                Operator::inIntArray($products, 'p.id')
            );
        };
    }

    /**
     * @return callable
     */
    public static function isVisible()
    {
        $expressions = [Product::isVisible(), Variation::isVisible()];

        return Operator::andX($expressions);
    }

    /**
     * @param bool Filter only products having any category
     *
     * @return callable
     */
    public static function hasCategory(bool $requiresCategory, bool $virtualSectionsIncluded = true)
    {
        if (!$requiresCategory) {
            return null;
        }

        return function (QueryBuilder $qb) use ($virtualSectionsIncluded) {
            $onlyWithSectionQb = sqlQueryBuilder()->select('*')
                ->from('products_in_sections');

            $spec = ['p.id = products_in_sections.id_product'];
            if (!$virtualSectionsIncluded) {
                $spec[] = Operator::equals(['products_in_sections.generated' => 0]);
            }

            $onlyWithSectionQb->andWhere(Operator::andX($spec));

            $qb->andWhere(\Query\Operator::exists($onlyWithSectionQb));
        };
    }

    public static function search(string $searchTerm, array $additionalFields = []): callable
    {
        return static function (QueryBuilder $qb) use ($searchTerm, $additionalFields) {
            // kopie fallbacku pro adminy ze SearchView
            $fields = [
                ['field' => 'p.title', 'match' => 'both', 'order' => true],
                ['field' => 'p.short_descr', 'match' => 'both'],
                ['field' => 'p.code', 'match' => 'left', 'order' => true],
                ['field' => 'pr.name', 'match' => 'both'],
            ];

            $qb->joinProducersOnProducts();

            if (is_numeric($searchTerm)) {
                $fields[] = ['field' => 'p.ean', 'match' => 'numeric', 'order' => true];

                if (findModule(\Modules::PRODUCTS_VARIATIONS)) {
                    $fields[] = ['field' => 'pv.ean', 'match' => 'numeric', 'order' => true];
                }
            }

            if (findModule(\Modules::PRODUCTS_VARIATIONS, 'variationCode')) {
                $qb->joinVariationsOnProducts();

                $fields[] = ['field' => 'pv.code', 'match' => 'left', 'order' => true];
            }

            foreach ($additionalFields as $field) {
                $fields[] = $field;
            }

            return Search::searchFields($searchTerm, $fields);
        };
    }

    public static function byQuantityDiscounts(array $quantityDiscounts): ?callable
    {
        if (!findModule(\Modules::QUANTITY_DISCOUNT)) {
            return null;
        }

        if (!($groupId = Contexts::get(QuantityDiscountContext::class)->getActive())) {
            return null;
        }

        return function (QueryBuilder $qb) use ($groupId, $quantityDiscounts) {
            $qb->join('p', 'products_quantity_discounts', 'pqd', 'p.id = pqd.id_product');

            $specs = [];
            foreach ($quantityDiscounts as $piecesFrom) {
                $specs[] = Operator::equals(['pqd.pieces' => $piecesFrom]);
            }

            return Operator::andX(
                QuantityDiscount::byGroup($groupId),
                Operator::orX($specs)
            );
        };
    }

    public static function byShowInSearch(bool $showInSearch): ?callable
    {
        if (!$showInSearch) {
            return null;
        }

        return function (QueryBuilder $qb) {
            $qb->andWhere("p.show_in_search = 'Y'");
        };
    }
}

if (empty($subclass)) {
    class Filter extends FilterBase
    {
    }
}
