<?php

namespace KupShop\StoresBundle\Utils;

use KupShop\StoresBundle\Query\StoresQuery;
use Query\Operator;
use Query\QueryBuilder;

class StoresTransfers
{
    public function getMissingItems($id_store_from, $id_store_to, $auto_transfer = 'both')
    {
        if ($id_store_from == $id_store_to) {
            return [];
        }

        if (empty($auto_transfer)) {
            return [];
        }

        $missing_items = [];
        $storeId = $id_store_to == 1 ? $id_store_from : $id_store_to;
        $table_alias = 'si'.$storeId;
        $notpacked = getStatuses('notpacked');

        if (!empty($notpacked)) {
            // not packed orders, delivery podle store, Fyzicky skladem pro tuto objednávku > 0
            $active_order_items = sqlQueryBuilder()
                ->select('oi.id_product, oi.id_variation, oi.pieces,
                          COALESCE(pv.in_store, p.in_store) AS in_store')
                ->from('order_items', 'oi')
                ->leftJoin('oi', 'orders', 'o', 'o.id = oi.id_order')
                ->join('oi', 'products', 'p', 'oi.id_product = p.id')
                ->leftJoin('oi', 'products_variations', 'pv', 'pv.id = oi.id_variation')
                ->andWhere('o.status_storno = 0')
                ->andWhere(Operator::inIntArray($notpacked, 'o.status'))
                ->andWhere(StoresQuery::filterOrdersByStoreDelivery($id_store_to));

            // physically_in_store = in_store + piecesOrdered (Fyzicky skladem pro tuto objednávku)
            $piecesOrdered = sqlQueryBuilder()
                ->select('SUM(oi2.pieces)')
                ->from('order_items', 'oi2')
                ->leftJoin('oi2', 'orders', 'o2', 'oi2.id_order = o2.id')
                ->andWhere('oi2.id_product = oi.id_product AND ((oi2.id_variation IS NULL AND oi.id_variation IS NULL) OR oi.id_variation = oi2.id_variation)')
                ->andWhere('o2.date_created >= o.date_created')
                ->andWhere('o2.status_storno = 0')
                ->andWhere(Operator::inIntArray($notpacked, 'o2.status'));
            $active_order_items->addSelect("(COALESCE(pv.in_store, p.in_store) + ({$piecesOrdered->getSQL()})) AS physically_in_store")
                ->addParameters($piecesOrdered->getParameters(), $piecesOrdered->getParameterTypes())
                ->having('physically_in_store > 0');

            $qb = sqlQueryBuilder()
                ->select('t.id_product, t.id_variation,
                          SUM(LEAST(t.physically_in_store, t.pieces)) AS chci_na_sklade')
                ->from("({$active_order_items->getSQL()})", 't')
                ->addParameters($active_order_items->getParameters(), $active_order_items->getParameterTypes())
                ->join('t', 'products', 'p', 't.id_product = p.id')
                ->leftJoin('t', 'products_variations', 'pv', 'pv.id = t.id_variation')
                ->groupBy('t.id_product, t.id_variation');
            $piecesOrdered_store = sqlQueryBuilder()
                ->select('SUM(oi.pieces)')
                ->from('order_items', 'oi')
                ->leftJoin('oi', 'orders', 'o', 'oi.id_order = o.id')
                ->andWhere('oi.id_product = p.id AND ((pv.id IS NULL AND oi.id_variation IS NULL) OR oi.id_variation = pv.id)')
                ->andWhere('o.status_storno = 0')
                ->andWhere(Operator::inIntArray($notpacked, 'o.status'))
                ->andWhere(StoresQuery::filterOrdersByStoreDelivery($id_store_to));
            $qb->addSelect("COALESCE(({$piecesOrdered_store->getSQL()}), 0) AS piecesOrdered_store")
                ->addParameters($piecesOrdered_store->getParameters(), $piecesOrdered_store->getParameterTypes());

            $qb->andWhere(StoresQuery::addStoresInStoreAmounts([$id_store_from, $id_store_to]));
            // mam_na_sklade = store_in_store + piecesOrdered_store (physically_in_store)
            // chci_na_sklade > mam_na_sklade
            $qb->having("chci_na_sklade > (store{$id_store_to}_in_store + piecesOrdered_store)");

            $qb->leftJoinSubQuery('t', $this->getInTransferQueryBuilder('t', storeTo: $id_store_to), 'sttq',
                'sttq.id_product = t.id_product AND sttq.id_variation <=> t.id_variation'
            )->addSelect('COALESCE(sttq.q, 0) stores_transfers_to_quantity');

            $qb->leftJoinSubQuery('t', $this->getInTransferQueryBuilder('t', storeFrom: $id_store_from), 'stfq',
                'stfq.id_product = t.id_product AND stfq.id_variation <=> t.id_variation'
            )->addSelect('COALESCE(stfq.q, 0) stores_transfers_from_quantity');

            $missing_items1 = $qb->execute()->fetchAll();

            foreach ($missing_items1 as &$item) {
                // mam_na_sklade - reálný počet kusů skladem na cílovém skladu
                $item['mam_na_sklade'] = $item["store{$id_store_to}_in_store"] + $item['piecesOrdered_store'];
                // chci_na_sklade - kolik tam mám mít kvůli objednávkám
                $quantity = $item['chci_na_sklade'] - $item['mam_na_sklade'];
                if ($auto_transfer == 'both') {
                    $store_from_in_store = $item["store{$id_store_from}_in_store"] - $item['stores_transfers_to_quantity'] - $quantity;
                    $min_in_store = min($item["store{$id_store_to}_min_in_store"], $store_from_in_store);
                    // Když ještě řeším minimální množství, přidám ještě to co potřebuju do minimálního množství
                    $quantity += max($min_in_store, 0);
                }
                // Kolik maximálně můžu přenést - jen tolik kolik mám skladem na zdrojovém skladu ještě bez toho co už je v jiný převodce
                $store_from_in_store = $item["store{$id_store_from}_in_store"] - $item['stores_transfers_from_quantity'];
                $item['quantity'] = min($quantity - $item['stores_transfers_to_quantity'], $store_from_in_store);
                $key = $item['id_product'].'_'.($item['id_variation'] ?? '');
                $missing_items[$key] = $item;
            }
        }

        if ($auto_transfer != 'orders') {
            $qb = sqlQueryBuilder()->select("
            {$table_alias}.id_product, 
            {$table_alias}.id_variation, 
            COALESCE(pv.in_store, p.in_store) as total_in_store")
                ->from('products', 'p')
                ->leftJoin('p', 'products_variations', 'pv', 'p.id = pv.id_product');
            $qb->andWhere(StoresQuery::addStoresInStoreAmounts([$id_store_from, $id_store_to]));

            $qb->leftJoinSubQuery('p', $this->getInTransferQueryBuilder($table_alias, storeTo: $id_store_to), 'sttq',
                'sttq.id_product = p.id AND (sttq.id_variation = pv.id OR (sttq.id_variation IS NULL AND pv.id IS NULL))')
                ->addSelect('COALESCE(sttq.q, 0) stores_transfers_to_quantity');

            $qb->leftJoinSubQuery('p', $this->getInTransferQueryBuilder($table_alias, storeFrom: $id_store_from), 'stfq',
                'stfq.id_product = p.id AND (stfq.id_variation = pv.id OR (stfq.id_variation IS NULL AND pv.id IS NULL))')
                ->addSelect('COALESCE(stfq.q, 0)stores_transfers_from_quantity');

            $qb->leftJoin('p',
                'stores_items',
                $table_alias,
                "{$table_alias}.id_product = p.id AND {$table_alias}.id_variation <=> pv.id AND {$table_alias}.id_store = {$storeId}")
                ->addGroupBy("{$table_alias}.id_product, {$table_alias}.id_variation")
                ->having("store{$id_store_from}_in_store > 0")
                ->andHaving("(store{$id_store_to}_min_in_store-store{$id_store_to}_in_store-stores_transfers_to_quantity) > 0");
            $qb->addParameters(['id_store' => $id_store_to]);
            $missing_items2 = $qb->execute()->fetchAll();

            foreach ($missing_items2 as &$item) {
                // Chci převést rozdíl mezi aktuálním a požadovaným počtem, ale ještě odečtu co už je na cestě
                $quantity = $item["store{$id_store_to}_min_in_store"] - $item["store{$id_store_to}_in_store"] - $item['stores_transfers_to_quantity'];
                // Na zdrojovém skladě odečtu kusy, který už odebírám v jiný převodce, ty převést nemůžu
                $store_from_in_store = $item["store{$id_store_from}_in_store"] - $item['stores_transfers_from_quantity'];
                // Převedu jen co je možné - to menší z "chci" a "mám"
                $item['quantity'] = min($quantity, $store_from_in_store);
                $key = $item['id_product'].'_'.($item['id_variation'] ?? '');
                if (empty($missing_items[$key])) {
                    $missing_items[$key] = $item;
                }
            }
        }

        $missing_items = array_filter($missing_items, function ($i) {
            return $i['quantity'] > 0;
        });

        return $missing_items;
    }

    public function getInTransferQueryBuilder($alias, $storeFrom = null, $storeTo = null): QueryBuilder
    {
        $qb = sqlQueryBuilder()->select('COALESCE(SUM(sti.quantity), 0) q, sti.id_product, sti.id_variation')
            ->from('stores_transfers_items', 'sti')
            ->join('sti', 'stores_transfers', 'st', 'sti.id_stores_transfer = st.id')
            ->groupBy('sti.id_product, sti.id_variation');

        if ($storeFrom) {
            $qb->andWhere(Operator::equals(['st.id_store_from' => $storeFrom]));
        }

        if ($storeTo) {
            $qb->andWhere(Operator::equals(['st.id_store_to' => $storeTo]));
        }

        // When dealing with storeFrom and on_the_fly store is active, only Open transfers are still present in store
        if ($storeFrom && findModule(\Modules::STORES, \Modules::SUB_ON_THE_FLY_STORE)) {
            $qb->andWhere('st.state = "O"');
        } else {
            // Otherwise only Open transfers are still present in store
            $qb->andWhere('st.state <> "R"');
        }

        return $qb;
    }

    public function getTransferItems($id)
    {
        $qb = sqlQueryBuilder()->select('sti.id, sti.id_product, sti.id_variation, sti.quantity, 
                p.title as product_title, pv.title variation_title, p.figure,  
                SUM(COALESCE(si.quantity, 0)) as total_in_store, 
                COALESCE(pv.ean, p.ean) as ean, sti.custom_data')
            ->from('stores_transfers_items', 'sti')
            ->leftJoin('sti', 'products', 'p', 'p.id=sti.id_product')
            ->leftJoin('sti', 'products_variations', 'pv', 'pv.id=sti.id_variation')
            ->leftJoin('sti', 'stores_items', 'si', 'sti.id_product = si.id_product AND sti.id_variation <=> si.id_variation')
            ->groupBy('sti.id, sti.id_product, sti.id_variation');

        if (findModule('products_variations', 'variationCode')) {
            $qb->addSelect('COALESCE(pv.code, p.code) as code');
        } else {
            $qb->addSelect('p.code as code');
        }

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

        if (findModule('products', 'note')) {
            $qb->addSelect('COALESCE(pv.note, p.note) as note');
        }

        $qb->where(Operator::equals(['sti.id_stores_transfer' => $id]));
        $qb->andWhere(StoresQuery::addStoresInStoreAmounts());

        $qb->orderBySql('sti.id DESC');
        $SQL = $qb->execute()->fetchAll();

        return $SQL;
    }

    public static function getStoresMissingProductsQuery()
    {
        return sqlQueryBuilder()->select('si.id stores_items_id')->from('stores_items', 'si')
            ->addSelect('p.id id_product, pv.id id_variation, p.title p_title, pv.title pv_title, COALESCE(pv.in_store, p.in_store) web_in_store, SUM(si.quantity) stores_in_store')
            ->innerJoin('si', 'stores', 's', 'si.id_store = s.id and type != 2')
            ->innerJoin('si', 'products', 'p', 'p.id=si.id_product')
            ->leftJoin('si', 'products_variations', 'pv', 'pv.id = si.id_variation and pv.id_product = si.id_product')
            ->groupBy('p.id, pv.id')
            ->having('web_in_store != stores_in_store');
    }
}
