<?php

namespace KupShop\ProductsBatchesBundle\Util;

use Doctrine\DBAL\ParameterType;
use KupShop\CatalogBundle\ProductList\ProductCollection;
use KupShop\KupShopBundle\Util\Database\QueryHint;
use KupShop\StoresBundle\Utils\StoresInStore;
use Query\Operator;

class ProductsBatchesUtil
{
    public function getBatchNumber($id)
    {
        return sqlQueryBuilder()->select('id', 'id_product', 'id_variation', 'code', 'date_expiry')
            ->from('products_batches')
            ->where(Operator::equals(['id' => $id]))->execute()->fetch();
    }

    public function getProductNearestExpirationBatches($idProduct)
    {
        $subQuery = sqlQueryBuilder()->select('pb2.id, MIN(pb2.date_expiry) min_date_expiry')
            ->from('products_batches', 'pb2')
            ->innerJoin('pb2', 'warehouse_products', 'wp2', 'pb2.id = wp2.id_product_batch')
            ->innerJoin('wp2', 'warehouse_positions', 'wpos2', 'wp2.id_position = wpos2.id')
            ->where('pb2.date_expiry IS NOT NULL')
            ->andWhere('wp2.pieces > 0')
            ->andWhere("wpos2.code NOT LIKE '%BOX%'")
            ->groupBy('pb2.id');

        $qb = sqlQueryBuilder()->select('pb.id', 'pb.id_variation', 'pb.code', 'pb.date_expiry')
            ->from('products_batches', 'pb')
            ->innerJoin('pb', '('.$subQuery->getSQL().')', 'pb2', 'pb.id = pb2.id AND pb.date_expiry = pb2.min_date_expiry')
            ->where(Operator::equals(['pb.id_product' => $idProduct]))
            ->groupBy('pb.id_variation');

        $result = [];

        foreach ($qb->execute() as $row) {
            $vId = $row['id_variation'];

            $batch = [
                'id' => $row['id'],
                'code' => $row['code'],
                'date_expiry' => $row['date_expiry'],
            ];

            if ($vId) {
                $result['variations'] = $result['variations'] ?? [];
                $result['variations'][$vId] = $batch;
            } else {
                $result['product'] = $batch;
            }
        }

        return $result;
    }

    public function createBatchNumber($data)
    {
        $key = [
            'id_product' => $data['id_product'],
            'id_variation' => $data['id_variation'] ?? null,
            'code' => $data['code'],
        ];

        $alreadyExists = QueryHint::withRouteToMaster(function () use ($key) {
            return sqlQueryBuilder()->select('id', 'id_product', 'id_variation', 'code', 'date_expiry')
                ->from('products_batches')
                ->where(Operator::equalsNullable($key))->execute()->fetchAssociative();
        });

        if (!$alreadyExists) {
            $key['date_expiry'] = $data['date_expiry'];
            $key['id'] = sqlGetConnection()->transactional(function () use ($key) {
                sqlQueryBuilder()->insert('products_batches')
                    ->directValues($key)
                    ->execute();

                return sqlInsertId();
            });

            return $key;
        } else {
            return $alreadyExists;
        }
    }

    /**
     * For each batch from $batches in format [$idBatch => $amount, $idBatch2=> $amount2] it updates custom_data batches.
     *
     * If batch already exists in the custom_data, it only increases/decreases its amount. Or removes the batch when its amount would be <= 0.
     * If batch doesn't exist, it creates the batch in the JSON structure.
     * If custom_data doesn't contain the batch_numbers structure, it creates the batch_numbers structure next to the previous content of the custom_data.
     * If custom data is null or empty, it creates the structure for the batch_numbers.
     *
     * @param array $where   condition for update
     * @param array $batches batches numbers in format [$idBatch => $amount, $idBatch2=> $amount2]
     *
     * @return void
     */
    public function addBatchesToCustomData(string $tableName, array $where, array $batches, $columnName = 'custom_data')
    {
        $this->modifyBatchesToCustomData($tableName, $where, $batches, true, $columnName);
    }

    /**
     * Same as method addBatchesToCustomData, but batch amount is set, not incremented.
     *
     * @param array $where   condition for update
     * @param array $batches batches numbers in format [$idBatch => $amount, $idBatch2=> $amount2]
     *
     * @return void
     */
    public function setBatchesToCustomData(string $tableName, array $where, array $batches, $columnName = 'custom_data')
    {
        $this->modifyBatchesToCustomData($tableName, $where, $batches, false, $columnName);
    }

    protected function modifyBatchesToCustomData(string $tableName, array $where, array $batches, bool $incrementActualValue, $columnName)
    {
        if ($incrementActualValue) {
            $futureValue = "JSON_EXTRACT({$columnName}, CONCAT('$.batch_numbers.', :batchId)) + :batchAmount";
        } else {
            $futureValue = ':batchAmount';
        }

        foreach ($batches as $idBatch => $amountBatch) {
            sqlQueryBuilder()->update($tableName)->where(Operator::equalsNullable($where))
                ->set($columnName,
                    "
            IF(JSON_CONTAINS_PATH(NULLIF({$columnName}, ''), 'one', CONCAT('$.batch_numbers.', :batchId)),
               IF({$futureValue} > 0,
                  JSON_SET({$columnName}, CONCAT('$.batch_numbers.', :batchId), {$futureValue}),
                  JSON_REMOVE({$columnName}, CONCAT('$.batch_numbers.', :batchId))
                  )
                ,
               JSON_REPLACE(
                       IF(JSON_CONTAINS_PATH(NULLIF({$columnName}, ''), 'one', '$.batch_numbers'),
                          {$columnName},
                          IF(JSON_TYPE(NULLIF({$columnName}, '')) = 'OBJECT', JSON_INSERT({$columnName}, '$.batch_numbers', JSON_OBJECT()),
                             JSON_OBJECT('batch_numbers', JSON_OBJECT()))
                           )
                   , '$.batch_numbers',
                       JSON_MERGE_PATCH(COALESCE(JSON_EXTRACT(NULLIF({$columnName}, ''), '$.batch_numbers'), JSON_OBJECT()),
                                        IF(:batchAmount > 0, JSON_OBJECT(:batchId, :batchAmount), JSON_OBJECT())))
                )
            ")
                ->setParameter('batchId', $idBatch)
                ->setParameter('batchAmount', (int) $amountBatch, ParameterType::INTEGER)->execute();
        }
    }

    /**
     * Clear batch numbers object from custom_data of the table.
     *
     * @return void
     */
    public function clearBatchesCustomData(string $tableName, array $where, $columnName = 'custom_data')
    {
        sqlQueryBuilder()->update($tableName)
            ->set($columnName, "JSON_SET({$columnName}, CONCAT('$.batch_numbers'), JSON_OBJECT())")
            ->where(Operator::equalsNullable($where))
            ->execute();
    }

    public function getProductBatches(ProductCollection $products, bool $withQuantity = false, bool $byVariations = false): ?array
    {
        $qb = sqlQueryBuilder()->select('pb.id, pb.id_product, pb.id_variation, pb.code, pb.date_expiry')
            ->from('products_batches', 'pb')
            ->where(Operator::inIntArray($products->getProductIds(), 'pb.id_product'))
            ->groupBy('pb.code');

        if ($withQuantity) {
            $qb->addSelect("
                COALESCE((SELECT SUM(DISTINCT JSON_EXTRACT(si.custom_data, CONCAT('$.batch_numbers.', pb.id)))
                FROM stores_items si LEFT JOIN stores s ON si.id_store = s.id
                WHERE JSON_EXTRACT(si.custom_data, CONCAT('$.batch_numbers.', pb.id)) IS NOT NULL
                AND si.id_product = pb.id_product AND si.id_variation <=> pb.id_variation AND s.type = :externalStoreType), 0)
                + COALESCE(SUM(DISTINCT wp.pieces), 0) AS batch_quantity
            ")
            ->setParameter('externalStoreType', StoresInStore::TYPE_EXTERNAL_STORE);
            $qb->leftJoin('pb', 'warehouse_products', 'wp', 'pb.id = wp.id_product_batch');
        }

        if ($byVariations) {
            $qb->addGroupBy('pb.id_variation');
        }

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