<?php

namespace KupShop\AdminBundle\Util\Stats;

use KupShop\AdminBundle\Query\StockIn;
use KupShop\WarehouseBundle\Util\InventoryWorker;

class StoreDataLoader
{
    public ?InventoryWorker $inventoryWorker = null;

    /** @required */
    public function setInventoryWorker(?InventoryWorker $inventoryWorker = null): void
    {
        $this->inventoryWorker = $inventoryWorker;
    }

    public function getCurrentStoreValue($to = null)
    {
        $qb = $this->getCurrentStoreQb($to);
        if (findModule(\Modules::WAREHOUSE)) {
            $qb->addSelect(
                'SUM(ss.in_store*r.average_price) as without_vat,SUM((ss.in_store*r.average_price)*(1+ifnull(v.vat,0)/100)) as with_vat'
            );
        } else {
            $qb->select(
                'SUM(s.in_store*r.average_price) as without_vat,SUM((s.in_store*r.average_price)*(1+ifnull(v.vat,0)/100)) as with_vat'
            );
        }

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

    public function getCurrentStoreQb(?string $dateFormat = null)
    {
        $date = (new \DateTime($dateFormat ?? 'now'))->setTime(23, 59, 59);

        $priceSubquery = StockIn::getAveragePriceSubQuery($date);

        $sql = sqlQueryBuilder()
            ->fromProducts()
            ->joinVariationsOnProducts()
            ->joinVatsOnProducts()
            ->leftJoinSubQuery('pv', $priceSubquery, 'r', 'r.id_product = p.id AND ((pv.id IS NULL AND r.id_variation IS NULL) OR pv.id = r.id_variation)')
            ->addParameters(
                [
                    'dateTo' => $date->format('Y-m-d H:i:s'),
                ]
            );

        if (findModule(\Modules::WAREHOUSE)) {
            $logSubquery = sqlQueryBuilder()
                ->select('SUM(IF(wl.old_id_position IS NULL OR wl.old_id_position = :inventoryPositionId, wl.pieces, -wl.pieces)) as in_store_log, wl.id_product, wl.id_variation')
                ->from('warehouse_log', 'wl')
                ->andWhere('wl.old_id_position IS NULL OR wl.new_id_position IS NULL OR wl.old_id_position = :inventoryPositionId')
                ->andWhere('wl.update_datetime > :dateTo')
                ->groupBy('wl.id_product, wl.id_variation')
                ->setParameter('inventoryPositionId', $this->inventoryWorker->getInventoryPosition());

            $stockSubquery = sqlQueryBuilder()
                ->select('SUM(wp.pieces) as in_store_stock, wp.id_product, wp.id_variation')
                ->from('warehouse_products', 'wp')
                ->groupBy('wp.id_product, wp.id_variation');

            $inStoreSubquery = sqlQueryBuilder()
                ->select('ps.id as id_product, pvs.id as id_variation, (COALESCE((wp.in_store_stock), 0) - COALESCE(wl.in_store_log, 0)) as in_store')
                ->fromProducts('ps')
                ->leftJoin('ps', 'products_variations', 'pvs', 'ps.id = pvs.id_product')
                ->leftJoinSubQuery('ps', $stockSubquery, 'wp', 'wp.id_product = ps.id AND wp.id_variation <=> pvs.id')
                ->leftJoinSubQuery('ps', $logSubquery, 'wl', 'wl.id_product = ps.id AND wl.id_variation <=> pvs.id');

            $sql->leftJoinSubQuery('pv', $inStoreSubquery, 'ss', 'ss.id_product = p.id AND pv.id <=> ss.id_variation')
                ->andWhere('ss.in_store > 0');
        } else {
            $ordersSubquery = sqlQueryBuilder()
                ->select('SUM(oi.pieces) as piecesInOrder, oi.id_product, oi.id_variation')
                ->from('orders', 'o')
                ->leftJoin('o', 'order_items', 'oi', 'o.id = oi.id_order')
                ->where('o.status_storno = 0')
                ->andWhere('o.date_created > :dateTo')
                ->groupBy('oi.id_product, oi.id_variation');

            $stockInSubquery = sqlQueryBuilder()->select('SUM(sii2.quantity) pieces_stocked_in, sii2.id_product, sii2.id_variation')
                ->from('stock_in_items', 'sii2')
                ->join('sii2', 'stock_in', 'si2', 'si2.id=sii2.id_stock_in')
                ->where('COALESCE(si2.date_stock_in, si2.date_issued) >= :dateTo')
                ->andWhere('si2.id_index = \'invoice\'')
                ->groupBy('sii2.id_product, sii2.id_variation');

            $quantitySubquery = sqlQueryBuilder()->select(
                'p.id as id_product, pv.id as id_variation, COALESCE(pv.in_store, p.in_store) + COALESCE(piecesInOrder.piecesInOrder, 0) - COALESCE(pieces_stocked_in.pieces_stocked_in, 0) in_store'
            )
                ->fromProducts()
                ->joinVariationsOnProducts()
                ->leftJoinSubQuery('p', $ordersSubquery, 'piecesInOrder', 'p.id = piecesInOrder.id_product AND pv.id <=> piecesInOrder.id_variation')
                ->leftJoinSubQuery('p', $stockInSubquery, 'pieces_stocked_in', 'p.id = pieces_stocked_in.id_product AND pv.id <=> pieces_stocked_in.id_variation');

            $sql->leftJoinSubQuery('pv', $quantitySubquery, 's', 's.id_product=p.id AND (pv.id IS NULL or pv.id=s.id_variation)');
            $sql->andWhere('s.in_store > 0');
        }

        return $sql;
    }
}