<?php
// Copyright (c) Pickware GmbH. All rights reserved.
// This file is part of software that is released under a proprietary license.
// You must not copy, modify, distribute, make publicly available, or execute
// its contents or parts thereof without express permission by the copyright
// holder, unless otherwise permitted by law.

namespace Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter;

use DateTime;
use Enlight_Components_Db_Adapter_Pdo_Mysql;
use Psr\Log\LoggerInterface;
use Shopware\CustomModels\ViisonPickwareERP\Warehouse\Warehouse;
use Shopware\Models\Order\Status;
use Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter\OrderFilterConditionQueryComponent\OrderFilterConditionQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter\OrderItemRelevance\OrderItemRelevanceService;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\BooleanCompositionQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\ComparisonQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\DistinctSelectQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\FieldDescriptorQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\FunctionQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\PlainSqlQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\QueryBuilder;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\QueryBuilderJoinResolver;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\QueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\QueryComponentFactory;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\ScalarValueQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\SelectQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\ShippingProviderRepositoryService;

class PickingOrderFilterService
{
    // Query condition aliases
    const CONDITION_ALIAS_ORDER_HAS_VALID_STATUS = 'orderHasValidStatus';
    const CONDITION_ALIAS_ORDER_HAS_VALID_DISPATCH_METHOD = 'orderHasValidDispatchMethod';
    const CONDITION_ALIAS_ORDER_ITEM_IS_RELEASED = 'itemIsReleased';
    const CONDITION_ALIAS_ORDER_ITEM_IS_NOT_COMPLETELY_SHIPPED = 'itemIsNotCompletelyShipped';
    const CONDITION_ALIAS_ORDER_ITEM_HAS_SUFFICIENT_STOCK = 'itemHasSufficientStock';
    const CONDITION_ALIAS_ORDER_PASSES_CUSTOM_FILTER = 'orderPassesCustomFilter';
    const CONDITION_ALIAS_ORDER_PASSES_STOCK_BASED_FILTER = 'orderPassesStockBasedFilter';
    const CONDITION_ALIAS_ORDER_PASSES_ALL_ITEMS_ARE_RELEASED = 'orderPassesAllItemsAreReleasedFilter';

    // Possible types of order the item stock condition
    const ORDER_ITEM_STOCK_CONDITION_TYPE_ALL_STOCK = 0;
    const ORDER_ITEM_STOCK_CONDITION_TYPE_SOME_STOCK = 1;

    /**
     * @var Enlight_Components_Db_Adapter_Pdo_Mysql
     */
    private $database;

    /**
     * @var ShippingProviderRepositoryService
     */
    private $shippingProviderRepository;

    /**
     * @var OrderItemRelevanceService
     */
    protected $orderItemRelevance;

    /**
     * @var LoggerInterface
     */
    protected $logger;

    /**
     * @var bool
     */
    protected $debugMode;

    /**
     * @param Enlight_Components_Db_Adapter_Pdo_Mysql $database
     * @param ShippingProviderRepositoryService $shippingProviderRepository
     * @param OrderItemRelevanceService $orderItemRelevance
     * @param LoggerInterface $logger
     * @param bool $debugMode (optional)
     */
    public function __construct(
        $database,
        ShippingProviderRepositoryService $shippingProviderRepository,
        OrderItemRelevanceService $orderItemRelevance,
        LoggerInterface $logger,
        $debugMode = false
    ) {
        $this->database = $database;
        $this->shippingProviderRepository = $shippingProviderRepository;
        $this->orderItemRelevance = $orderItemRelevance;
        $this->logger = $logger;
        $this->debugMode = $debugMode;
    }

    /**
     * Returns an array containing the IDs of all orders that pass the picking order filter configured using the given
     * arguments.
     *
     * @param Warehouse $warehouse
     * @param StockBasedFilterConfiguration $stockBasedFilterConfig
     * @param DateTime $earliestPreOrderDate
     * @param OrderFilterConditionQueryComponent|null $customCondition
     * @return int[]
     */
    public function getIdsOfOrdersPassingFilter(
        Warehouse $warehouse,
        StockBasedFilterConfiguration $stockBasedFilterConfig,
        DateTime $earliestPreOrderDate,
        OrderFilterConditionQueryComponent $customCondition = null
    ) {
        // Start building the query by selecting the order ID
        $queryBuilder = new QueryBuilder();
        if ($stockBasedFilterConfig->isStockBasedFilterEnabled()) {
            $queryBuilder->select(QueryComponentFactory::createTableFieldSelectWithAlias('s_order', 'id', 'orderId'));
        } else {
            // Use as `DISTINCT` select, because without a stock based filter, no grouping will be applied to the query
            $queryBuilder->select(
                new DistinctSelectQueryComponent(new FieldDescriptorQueryComponent('s_order', 'id'), 'orderId')
            );
        }
        $queryBuilder->from('s_order');

        // Add stock based filter condition, if configured
        if ($stockBasedFilterConfig->isStockBasedFilterEnabled()) {
            // Aggregate the sum of the stock based filter condition, since its evaluated on each item, and compare it
            // depending on the mode
            $orderItemStockCondition = $this->createStockBasedOrderItemFilterConditionForConfiguration(
                $stockBasedFilterConfig,
                $warehouse
            );
            $itemStockConditionAggregation = new FunctionQueryComponent('SUM', $orderItemStockCondition);
            if ($stockBasedFilterConfig->isModeOfTypeAllItemsMustFulfillStockCondition()) {
                $orderStockCondition = new ComparisonQueryComponent(
                    $itemStockConditionAggregation,
                    '=',
                    new PlainSqlQueryComponent('COUNT(`s_order_details`.`id`)', ['s_order_details'])
                );
                if (count($stockBasedFilterConfig->getExemptDispatchMethodIds()) > 0) {
                    // Add a second, disjunct condition to the default stock based filter condition that marks an
                    // order's item stock condition as valid in case the order's dispatch method is exempt from the
                    // original item stock condition and at some stock of at least one of its items is available
                    $orderItemStockConditionWithDispatchMethodExemption = $this->createStockBasedOrderItemFilterConditionForDispatchMethodExemptionOfConfiguration(
                        $stockBasedFilterConfig,
                        $warehouse
                    );
                    $orderStockCondition = BooleanCompositionQueryComponent::createDisjunction(
                        $orderStockCondition,
                        BooleanCompositionQueryComponent::createConjunction(
                            $this->createOrderHasExemptDispatchMethodCondition(
                                $stockBasedFilterConfig->getExemptDispatchMethodIds()
                            ),
                            new ComparisonQueryComponent(
                                new FunctionQueryComponent('SUM', $orderItemStockConditionWithDispatchMethodExemption),
                                '>',
                                new ScalarValueQueryComponent(0)
                            )
                        )
                    );
                }
            } else {
                $orderStockCondition = new ComparisonQueryComponent(
                    $itemStockConditionAggregation,
                    '>',
                    new ScalarValueQueryComponent(0)
                );
            }

            // Add the stock based filter condition as an extra selected field
            $queryBuilder->addSelect(new SelectQueryComponent(
                $orderStockCondition,
                self::CONDITION_ALIAS_ORDER_PASSES_STOCK_BASED_FILTER
            ));
        }

        // Collect item conditions
        $itemConditions = array_values($this->orderItemRelevance->createPickingAppRelevanceConditions());
        $itemConditions[] = $this->createOrderItemIsNotCompletelyShippedCondition();
        // Create the pre ordered item condition
        $itemIsReleasedCondition = $this->createOrderItemIsReleasedCondition($earliestPreOrderDate);
        if ($stockBasedFilterConfig->isModeAllItemsMustHaveAllRequiredStock()) {
            // Include non-released articles in the stock based condition
            $itemIsReleasedCondition = new ComparisonQueryComponent(
                new FunctionQueryComponent('MIN', $itemIsReleasedCondition),
                '>',
                new ScalarValueQueryComponent(0)
            );
            $queryBuilder->addSelect(new SelectQueryComponent(
                $itemIsReleasedCondition,
                self::CONDITION_ALIAS_ORDER_PASSES_ALL_ITEMS_ARE_RELEASED
            ));
        } else {
            $itemConditions[] = $itemIsReleasedCondition;
        }

        // Collect and add all WHERE conditions
        $conditions = $itemConditions;
        $conditions[] = $this->createValidOrderStatusFilterCondition();
        $orderHasValidDispatchMethodCondition = $this->createValidDispatchMethodIdsFilterCondition();
        if ($orderHasValidDispatchMethodCondition) {
            $conditions[] = $orderHasValidDispatchMethodCondition;
        }
        if ($customCondition) {
            $conditions[] = $customCondition;
        }
        $queryBuilder->where(BooleanCompositionQueryComponent::createConjunction(...$conditions));

        if ($stockBasedFilterConfig->isStockBasedFilterEnabled()) {
            // GROUP BY the order ID, which does the same as DISTINCT (plus sorting) and also performs any aggregations
            // contained in the select statements
            $queryBuilder->groupBy(QueryComponentFactory::createGroupByTableField('s_order', 'id'));
        }

        // Add all required joins (and only required joins) to the query builder
        $availableJoinComponents = PickingOrderFilterJoinQueryComponentFactory::createJoinComponents($warehouse);
        if ($customCondition) {
            $orderItemJoinComponent = $customCondition->createOrderItemJoinQueryComponent($itemConditions);
            if ($orderItemJoinComponent) {
                $availableJoinComponents[] = $orderItemJoinComponent;
            }
        }
        QueryBuilderJoinResolver::addMissingJoinsToQueryBuilder($queryBuilder, $availableJoinComponents);

        // Exectute the query
        $queryString = $queryBuilder->getSql();
        $this->logDebugInfoIfEnabled('Filtering order IDs', $queryString, $stockBasedFilterConfig);
        if ($stockBasedFilterConfig->isStockBasedFilterEnabled()) {
            // Query contains more than one select statement, hence fetch all columns
            $queryResult = $this->database->fetchAll($queryString);

            // Keep only those order IDs whose respective 'order passes stock based filter' is fulfilled. In the case of
            // filter mode 'all articles have sufficient stock' assert additionally that these orders only contain
            // unshipped items, whose respective articles are already released.
            $filteredQueryResult = array_filter(
                $queryResult,
                function (array $row) use ($stockBasedFilterConfig) {
                    return (
                        intval($row[self::CONDITION_ALIAS_ORDER_PASSES_STOCK_BASED_FILTER])
                        && (!$stockBasedFilterConfig->isModeAllItemsMustHaveAllRequiredStock() || intval($row[self::CONDITION_ALIAS_ORDER_PASSES_ALL_ITEMS_ARE_RELEASED]))
                    );
                }
            );
            $result = array_column($filteredQueryResult, 'orderId');
        } else {
            // Only the order IDs is selected, hence fetch only a single column
            $result = $this->database->fetchCol($queryString);
        }

        return $result;
    }

    /**
     * Returns an array containing the IDs of all orders that are 'waiting' for stock consifering the configuration of
     * the given `$stockBasedFilterConfig` and `$warehouse`. That is, the IDs of orders that contain at least one item
     * whose remaining quantity cannot be picked because the stock of the respective article is not sufficient.
     *
     * @param Warehouse $warehouse
     * @param StockBasedFilterConfiguration $stockBasedFilterConfig
     * @return int[]
     */
    public function getIdsOfOrdersWaitingForStock(
        Warehouse $warehouse,
        StockBasedFilterConfiguration $stockBasedFilterConfig
    ) {
        // Create the 'order is waiting for stock' condition
        $orderItemStockCondition = $this->createDefaultStockBasedOrderItemFilterCondition(
            $stockBasedFilterConfig,
            $warehouse
        );
        $isWaitingForStockCondition = new ComparisonQueryComponent(
            new FunctionQueryComponent('SUM', new FunctionQueryComponent('NOT', $orderItemStockCondition)),
            '>',
            new ScalarValueQueryComponent(0)
        );

        // Build the query selecting both the order ID and the result of the 'order is waiting for stock' condition
        // and filtering only by a valid order status
        $queryBuilder = new QueryBuilder();
        $queryBuilder
            ->select(
                QueryComponentFactory::createTableFieldSelectWithAlias('s_order', 'id', 'orderId'),
                new SelectQueryComponent($isWaitingForStockCondition, 'isWaitingForStock')
            )
            ->from('s_order')
            ->where($this->createValidOrderStatusFilterCondition())
            ->groupBy(QueryComponentFactory::createGroupByTableField('s_order', 'id'));

        // Add all missing joins
        QueryBuilderJoinResolver::addMissingJoinsToQueryBuilder(
            $queryBuilder,
            PickingOrderFilterJoinQueryComponentFactory::createJoinComponents($warehouse)
        );

        // Fetch all columns as the preliminary result
        $queryString = $queryBuilder->getSql();
        $this->logDebugInfoIfEnabled(
            'Fetching orders that are waiting for stock',
            $queryString,
            $stockBasedFilterConfig
        );
        $queryResult = $this->database->fetchAll($queryString);

        // Keep only those order IDs, whose respective 'isWaitingForStock' is set
        $filteredQueryResult = array_filter(
            $queryResult,
            function (array $row) {
                return boolval($row['isWaitingForStock']);
            }
        );
        $result = array_column($filteredQueryResult, 'orderId');

        return $result;
    }

    /**
     * Evaluates the picking order filter for the orders with the passed `$orderIds`. The returned result of the
     * evaluation contains all unfulfilled conditions of the orders and their items.
     * See {@link UnfulfilledPickingOrderFilterConditions} for more info on how to retrieve those conditions. Returns
     * null, if the provided $orderIds are empty.
     *
     * @param array $orderIds
     * @param Warehouse $warehouse
     * @param StockBasedFilterConfiguration $stockBasedFilterConfig
     * @param DateTime $earliestPreOrderDate
     * @param OrderFilterConditionQueryComponent|null $customCondition
     * @return UnfulfilledPickingOrderFilterConditions|null
     */
    public function getUnfulfilledFilterConditions(
        array $orderIds,
        Warehouse $warehouse,
        StockBasedFilterConfiguration $stockBasedFilterConfig,
        DateTime $earliestPreOrderDate,
        OrderFilterConditionQueryComponent $customCondition = null
    ) {
        if (count($orderIds) === 0) {
            return null;
        }

        // Create all conditions and map them to their aliases
        $itemConditions = array_merge(
            $this->orderItemRelevance->createPickingAppRelevanceConditions(),
            [
                self::CONDITION_ALIAS_ORDER_ITEM_IS_RELEASED => $this->createOrderItemIsReleasedCondition(
                    $earliestPreOrderDate
                ),
                self::CONDITION_ALIAS_ORDER_ITEM_IS_NOT_COMPLETELY_SHIPPED => $this->createOrderItemIsNotCompletelyShippedCondition(),
            ]
        );
        $conditions = $itemConditions;
        $conditions[self::CONDITION_ALIAS_ORDER_HAS_VALID_STATUS] = $this->createValidOrderStatusFilterCondition();
        $orderHasValidDispatchMethodCondition = $this->createValidDispatchMethodIdsFilterCondition();
        if ($orderHasValidDispatchMethodCondition) {
            $conditions[self::CONDITION_ALIAS_ORDER_HAS_VALID_DISPATCH_METHOD] = $orderHasValidDispatchMethodCondition;
        }
        if ($customCondition) {
            // Add the complete custom condition as well as all its composition parts for analysis
            $conditions = array_merge(
                $conditions,
                $customCondition->flatMap(self::CONDITION_ALIAS_ORDER_PASSES_CUSTOM_FILTER)
            );
        }
        if ($stockBasedFilterConfig->isStockBasedFilterEnabled()) {
            $conditions[self::CONDITION_ALIAS_ORDER_ITEM_HAS_SUFFICIENT_STOCK] = $this->createStockBasedOrderItemFilterConditionForDispatchMethodExemptionOfConfiguration(
                $stockBasedFilterConfig,
                $warehouse
            );
        }
        $conditionSelects = array_map(
            function (QueryComponent $component, $alias) {
                return new SelectQueryComponent($component, $alias);
            },
            $conditions,
            array_keys($conditions)
        );

        // Build the query selecting order ID, dispatch method ID, order item ID and all aliased conditions
        $queryBuilder = new QueryBuilder();
        $queryBuilder
            ->select(
                QueryComponentFactory::createTableFieldSelectWithAlias('s_order', 'id', 'orderId'),
                QueryComponentFactory::createTableFieldSelectWithAlias('s_order', 'dispatchID', 'dispatchMethodId'),
                QueryComponentFactory::createTableFieldSelectWithAlias('s_order_details', 'id', 'orderItemId'),
                ...$conditionSelects
            )
            ->from('s_order')
            ->where(QueryComponentFactory::createTableFieldInValuesComparison('s_order', 'id', $orderIds));

        // Add all missing joins
        $availableJoinComponents = PickingOrderFilterJoinQueryComponentFactory::createJoinComponents($warehouse);
        if ($customCondition) {
            $orderItemJoinComponent = $customCondition->createOrderItemJoinQueryComponent($itemConditions);
            if ($orderItemJoinComponent) {
                $availableJoinComponents[] = $orderItemJoinComponent;
            }
        }
        QueryBuilderJoinResolver::addMissingJoinsToQueryBuilder($queryBuilder, $availableJoinComponents);

        // Fetch all SLECTEd columns and wrap the data in an analysis result
        $queryString = $queryBuilder->getSql();
        $this->logDebugInfoIfEnabled('Fetching unmet conditions', $queryString, $stockBasedFilterConfig);
        $queryResult = $this->database->fetchAll($queryString);
        $result = new UnfulfilledPickingOrderFilterConditions($queryResult, $stockBasedFilterConfig);

        return $result;
    }

    /**
     * @param StockBasedFilterConfiguration $stockBasedFilterConfig
     * @param Warehouse $warehouse
     * @return BooleanCompositionQueryComponent
     */
    protected function createStockBasedOrderItemFilterConditionForDispatchMethodExemptionOfConfiguration(
        StockBasedFilterConfiguration $stockBasedFilterConfig,
        Warehouse $warehouse
    ) {
        $itemHasSufficientStockCondition = $this->createStockBasedOrderItemFilterConditionForConfiguration(
            $stockBasedFilterConfig,
            $warehouse
        );
        if (!$stockBasedFilterConfig->isModeAtLeastOneItemMustHaveSomeStock() && count($stockBasedFilterConfig->getExemptDispatchMethodIds()) > 0) {
            // Add another bypass condition for orders with a dispatch method that is exemp from the stock based filter
            $itemHasSufficientStockCondition->addCompositionComponents(
                $this->createOrderHasExemptDispatchMethodStockBypassCondition(
                    $stockBasedFilterConfig->getExemptDispatchMethodIds()
                )
            );
        }

        return $itemHasSufficientStockCondition;
    }

    /**
     * @param StockBasedFilterConfiguration $stockBasedFilterConfig
     * @param Warehouse $warehouse
     * @return BooleanCompositionQueryComponent
     */
    protected function createStockBasedOrderItemFilterConditionForConfiguration(
        StockBasedFilterConfiguration $stockBasedFilterConfig,
        Warehouse $warehouse
    ) {
        $itemStockCondition = ($stockBasedFilterConfig->isModeAtLeastOneItemMustHaveSomeStock()) ? self::ORDER_ITEM_STOCK_CONDITION_TYPE_SOME_STOCK : self::ORDER_ITEM_STOCK_CONDITION_TYPE_ALL_STOCK;
        $orderItemStockCondition = $this->createDefaultStockBasedOrderItemFilterCondition(
            $itemStockCondition,
            $warehouse
        );

        // Add a bypass condition for items of deferred orders, since those must always be valid
        $orderItemStockCondition->addCompositionComponents($this->createOrderDeferredStockBypassCondition());

        return $orderItemStockCondition;
    }

    /**
     * @param int $minItemStockType
     * @param Warehouse $warehouse
     * @return BooleanCompositionQueryComponent
     */
    protected function createDefaultStockBasedOrderItemFilterCondition($minItemStockType, Warehouse $warehouse)
    {
        return BooleanCompositionQueryComponent::createDisjunction(
            $this->createArticleStockNotManagedStockBypassCondition(),
            $this->createOrderIsBeingPickedStockBypassCondition($warehouse),
            $this->createOrderPickingCompleteStockBypassCondition(),
            $this->createOrderReadyForShippingStockBypassCondition(),
            $this->createOrderItemHasSufficientStockCondition($minItemStockType)
        );
    }

    /**
     * @return PlainSqlQueryComponent
     */
    protected function createArticleStockNotManagedStockBypassCondition()
    {
        return new PlainSqlQueryComponent(
            '`s_articles_attributes`.`pickware_stock_management_disabled` = 1',
            ['s_articles_attributes']
        );
    }

    /**
     * @param Warehouse $warehouse
     * @return BooleanCompositionQueryComponent
     */
    protected function createOrderIsBeingPickedStockBypassCondition(Warehouse $warehouse)
    {
        return BooleanCompositionQueryComponent::createConjunction(
            new PlainSqlQueryComponent(
                sprintf('`s_order`.`status` = %d', Status::ORDER_STATE_IN_PROCESS),
                ['s_order']
            ),
            new PlainSqlQueryComponent(
                sprintf('`s_order_attributes`.`pickware_processing_warehouse_id` = %s', $warehouse->getId()),
                ['s_order_attributes']
            )
        );
    }

    /**
     * @return PlainSqlQueryComponent
     */
    protected function createOrderPickingCompleteStockBypassCondition()
    {
        return new PlainSqlQueryComponent(
            sprintf('`s_order`.`status` = %d', Status::ORDER_STATE_COMPLETED),
            ['s_order']
        );
    }

    /**
     * @return PlainSqlQueryComponent
     */
    protected function createOrderDeferredStockBypassCondition()
    {
        return new PlainSqlQueryComponent(
            sprintf('`s_order`.`status` = %d', Status::ORDER_STATE_PARTIALLY_COMPLETED),
            ['s_order']
        );
    }

    /**
     * @return PlainSqlQueryComponent
     */
    protected function createOrderReadyForShippingStockBypassCondition()
    {
        return new PlainSqlQueryComponent(
            sprintf('`s_order`.`status` = %d', Status::ORDER_STATE_READY_FOR_DELIVERY),
            ['s_order']
        );
    }

    /**
     * @param int $minItemStockType
     * @return PlainSqlQueryComponent
     */
    protected function createOrderItemHasSufficientStockCondition($minItemStockType)
    {
        $leftOperand = 'GREATEST(0, (IFNULL(`pickware_erp_warehouse_article_detail_stock_counts`.`stock`, 0) - IFNULL(`pickware_erp_warehouse_article_detail_stock_counts`.`reservedStock`, 0)))';
        // Right operand is 'left for shipping'
        $rightOperand = '
            GREATEST(0, (
                `s_order_details`.`quantity`
                 + IFNULL(`derived__cancelled_returned_quantities`.`cancelledReturned`, 0)
                 - `s_order_details`.`shipped`
                 - IFNULL(`s_order_details_attributes`.`pickware_picked_quantity`, 0)
            ))
        ';
        if ($minItemStockType === self::ORDER_ITEM_STOCK_CONDITION_TYPE_SOME_STOCK) {
            $rightOperand = sprintf('LEAST(1, (%s))', $rightOperand);
        }
        $queryString = $leftOperand . ' >= ' . $rightOperand;
        $requiredTables = [
            's_order_details',
            's_order_details_attributes',
            'pickware_erp_warehouse_article_detail_stock_counts',
            'derived__cancelled_returned_quantities',
        ];

        return new PlainSqlQueryComponent($queryString, $requiredTables);
    }

    /**
     * @return BooleanCompositionQueryComponent
     */
    protected function createOrderHasExemptDispatchMethodStockBypassCondition(array $exemptDispatchMethodIds)
    {
        return BooleanCompositionQueryComponent::createConjunction(
            $this->createOrderHasExemptDispatchMethodCondition($exemptDispatchMethodIds),
            $this->createOrderItemHasSufficientStockCondition(self::ORDER_ITEM_STOCK_CONDITION_TYPE_SOME_STOCK)
        );
    }

    /**
     * @param int[] $exemptDispatchMethodIds
     * @return QueryComponent
     */
    protected function createOrderHasExemptDispatchMethodCondition(array $exemptDispatchMethodIds)
    {
        return QueryComponentFactory::createTableFieldInValuesComparison(
            's_order',
            'dispatchID',
            $exemptDispatchMethodIds
        );
    }

    /**
     * @return QueryComponent
     */
    protected function createValidOrderStatusFilterCondition()
    {
        return QueryComponentFactory::createTableFieldInValuesComparison(
            's_order',
            'status',
            [
                Status::ORDER_STATE_OPEN,
                Status::ORDER_STATE_IN_PROCESS,
                Status::ORDER_STATE_COMPLETED,
                Status::ORDER_STATE_PARTIALLY_COMPLETED,
                Status::ORDER_STATE_READY_FOR_DELIVERY,
                Status::ORDER_STATE_PARTIALLY_DELIVERED,
            ]
        );
    }

    /**
     * @return QueryComponent|null
     */
    protected function createValidDispatchMethodIdsFilterCondition()
    {
        // Get the IDs of all dispatch methods that are handled by a shipping provider
        $validDispatchMethodIds = array_map(
            function ($shippingProvider) {
                return $shippingProvider->validDispatchIds();
            },
            $this->shippingProviderRepository->getProviders()
        );
        $validDispatchMethodIds = array_unique(array_merge([], ...$validDispatchMethodIds));
        if (count($validDispatchMethodIds) === 0) {
            return null;
        }

        return QueryComponentFactory::createTableFieldInValuesComparison('s_order', 'dispatchID', $validDispatchMethodIds);
    }

    /**
     * @param DateTime $itemPreOrderDate
     * @return PlainSqlQueryComponent
     */
    protected function createOrderItemIsReleasedCondition(DateTime $itemPreOrderDate)
    {
        return new PlainSqlQueryComponent(
            sprintf(
                'DATE(IFNULL(`s_articles_details`.`releasedate`, IFNULL(`s_order_details`.`releasedate`, \'1970-01-01\'))) <= \'%s\'',
                $itemPreOrderDate->format('Y-m-d')
            ),
            [
                's_articles_details',
                's_order_details',
            ]
        );
    }

    /**
     * @return PlainSqlQueryComponent
     */
    protected function createOrderItemIsNotCompletelyShippedCondition()
    {
        return new PlainSqlQueryComponent(
            '`s_order_details`.`shipped` < `s_order_details`.`quantity` + IFNULL(`derived__cancelled_returned_quantities`.`cancelledReturned`, 0)',
            [
                's_order_details',
                'derived__cancelled_returned_quantities',
            ]
        );
    }

    /**
     * @param string $message
     * @param string|null $queryString
     * @param StockBasedFilterConfiguration|null $stockBasedFilterConfig
     */
    protected function logDebugInfoIfEnabled(
        $message,
        $queryString = null,
        StockBasedFilterConfiguration $stockBasedFilterConfig = null
    ) {
        if (!$this->debugMode) {
            return;
        }

        try {
            $this->logger->debug(
                sprintf('%s: %s', __CLASS__, $message),
                [
                    'queryString' => $queryString,
                    'stockBasedFilterMode' => ($stockBasedFilterConfig) ? $stockBasedFilterConfig->getFilterMode() : null,
                ]
            );
        } catch (\Exception $e) { // phpcs:ignore Generic.CodeAnalysis.EmptyStatement
            // Ignore logging exceptions
        }
    }
}
