<?php

namespace KupShop\StoresBundle\Query;

use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\StoresBundle\Utils\StoresInStore;
use Query\Operator;
use Query\QueryBuilder;

class StoresQuery
{
    public static function addStoresInStoreAmounts($selectedStores = null, bool $addQuantity = true, $addMinQuantity = true): callable
    {
        return function (QueryBuilder $qb) use ($selectedStores, $addQuantity, $addMinQuantity) {
            $stores = ServiceContainer::getService(StoresInStore::class)->getStores();

            $qb->andWhere('1');

            // quantity decrement - vyselectuje skladovost externich skladu, aby se mohla odecist od celkoveho mnozstvi skladem
            $quantityDecrementQb = static::getQuantityDecrementQueryBuilder();

            $qb->addQueryBuilderParameters($quantityDecrementQb);

            foreach ($stores as $key => $store) {
                if ($selectedStores && !in_array($key, $selectedStores)) {
                    continue;
                }

                $table_alias = 'si'.$key;

                // prepare base sub query
                $subQuery = sqlQueryBuilder()
                     // temporary fix of mariadb bug - use index
                    ->from('stores_items', $table_alias.' USE INDEX (stores_items_products_id_fk)')
                    ->where("{$table_alias}.id_product = p.id AND {$table_alias}.id_variation <=> pv.id AND {$table_alias}.id_store = {$key}");

                // add sub query select
                /*
                 * Na detailu produktu se ovečkárně přestala zobrazovat dostupnost pouze na prodejně.
                 * Při příchodu na detail produktu není vybrána varianta a ve StoresQuery addStoresInStoreAmounts se nezobrazuje celkový součet napříč všech variant.
                 * Nevěděli jsme, proč bylo: $qb->addSubselect($subQuery->select("SUM(COALESCE({$table_alias}.quantity, 0))"), "store{$key}_in_store");
                 */
                if ($addQuantity) {
                    $qb->addSubselect($subQuery->select("COALESCE(SUM({$table_alias}.quantity), 0)"), "store{$key}_in_store");
                }

                if ($addMinQuantity) {
                    $qb->addSubselect($subQuery->select("COALESCE({$table_alias}.min_quantity, 0)"), "store{$key}_min_in_store");
                }
            }
        };
    }

    public static function filterByStoresInStore(array $storeIds): callable
    {
        return function (QueryBuilder $qb) use ($storeIds) {
            $qb->joinVariationsOnProducts();

            $filterSpecs = [];

            foreach ($storeIds as $storeId) {
                $existsSubQuery = sqlQueryBuilder()
                    ->select('si_filter.id')
                    ->from('stores_items', 'si_filter')
                    ->andWhere(
                        Operator::andX(
                            'si_filter.id_product = p.id AND pv.id <=> si_filter.id_variation',
                            Operator::equals(['si_filter.id_store' => $storeId]),
                            'si_filter.quantity > 0'
                        )
                    );

                $filterSpecs[] = Operator::exists($existsSubQuery);
            }

            return Operator::orX($filterSpecs);
        };
    }

    public static function filterOrdersByStoreDelivery(int $id_store): callable
    {
        return function (QueryBuilder $qb) use ($id_store) {
            $delivery_operator = 'false';
            if ($store = ServiceContainer::getService(StoresInStore::class)->getStores()[$id_store] ?? null) {
                $id_delivery = $store['id_delivery'];
                if ($id_delivery) {
                    $id_delivery_types = sqlFetchAll(sqlQueryBuilder()->select('id')->from('delivery_type')
                        ->andWhere(Operator::equals(['id_delivery' => $id_delivery]))->execute(),
                        ['id' => 'id']);
                    $delivery_operator = Operator::inIntArray($id_delivery_types, 'o.id_delivery');
                } elseif ($store['type'] == StoresInStore::TYPE_MAIN_STORE) {
                    $id_delivery = array_filter(array_column(ServiceContainer::getService(StoresInStore::class)->getStores(), 'id_delivery'));
                    if ($id_delivery) {
                        $id_delivery_types = sqlFetchAll(sqlQueryBuilder()->select('id')->from('delivery_type')
                            ->andWhere(Operator::inIntArray($id_delivery, 'id_delivery'))->execute(),
                            ['id' => 'id']);
                        $delivery_operator = Operator::not(Operator::inIntArray($id_delivery_types, 'o.id_delivery'));
                    }
                }
            }

            $qb->andWhere($delivery_operator);
        };
    }

    private static function getQuantityDecrementQueryBuilder(): QueryBuilder
    {
        return sqlQueryBuilder()
            ->select('COALESCE(SUM(quantity), 0)')
            ->from('stores_items USE INDEX(stores_items_products_id_fk)')
            ->where('id_product = p.id AND id_variation <=> pv.id AND id_store IN (SELECT id FROM stores WHERE type != :externalStoreType)')
            ->setParameter('externalStoreType', StoresInStore::TYPE_EXTERNAL_STORE);
    }
}