<?php

namespace Query;

use Doctrine\DBAL\Connection;
use KupShop\I18nBundle\Translations\PhotosTranslation;
use KupShop\KupShopBundle\Config;
use KupShop\KupShopBundle\Context\UserContext;
use KupShop\KupShopBundle\Context\VatContext;
use KupShop\KupShopBundle\Query\JsonOperator;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\PricelistBundle\Context\PricelistContext;
use KupShop\SellerBundle\Context\SellerContext;

class Product
{
    public static $fieldInStore = 'p.in_store';

    public static function inCollectionsProducts(QueryBuilder $qb, array $ids)
    {
        $main_products = sqlQueryBuilder()->select('pc.id_product AS id')
            ->from('products_collections', 'pc')
            ->where('pc.id_product_related IN (:ids) OR pc.id_product IN (:ids)');
        $related_products = sqlQueryBuilder()->select('pc.id_product_related AS id')
            ->from('products_collections', 'pc')
            ->leftJoin('pc', 'products_collections', 'pc_main', 'pc_main.id_product = pc.id_product')
            ->where('pc_main.id_product IN (:ids) OR pc_main.id_product_related IN (:ids)');
        $qb->setParameter('ids', $ids, Connection::PARAM_INT_ARRAY);

        return $main_products->getSQL().' UNION '.$related_products->getSQL();
    }

    public static function collectionsProductsSubQuery($productIds)
    {
        // tabulka [id, main_product, related_product]
        // produkt neni v kolekci -> [id, null, null]
        // produkt je hlavni v kolekci -> [id, id, related_product_1], [id, id, related_product_2], ...
        // produkt je v kolekci -> [id, main_product, related_product_1], [id, main_product, related_product_2], ...

        $products = sqlQueryBuilder()->select('id')->fromProducts()->where(Operator::inIntArray($productIds, 'id'));

        return sqlQueryBuilder()
            ->select('DISTINCT p.id, 
              COALESCE(pc_main.id_product, pc_related.id_product) AS main_product, 
              COALESCE(pc_main.id_product_related, pc_related_2.id_product_related) AS related_product')
            ->from("({$products->getSQL()})", 'p')->addParameters($products->getParameters(), $products->getParameterTypes())
            ->leftJoin('p', 'products_collections', 'pc_main', 'pc_main.id_product = p.id')
            ->leftJoin('p', 'products_collections', 'pc_related', 'pc_related.id_product_related = p.id')
            ->leftJoin('p', 'products_collections', 'pc_related_2', 'pc_related_2.id_product = pc_related.id_product');
    }

    public static function isVisible()
    {
        return Filter::byFigure('Y');
    }

    public static function inSection($sectionId)
    {
        return self::inSections([$sectionId]);
    }

    public static function inSections(array $sectionIds, $alias = 'ps')
    {
        return function (QueryBuilder $qb) use ($sectionIds) {
            $sectionIds = array_merge($sectionIds, [-1000]); // nonsense id, so there are always at least two ids - hack for better queryplan

            $existsSubquery = sqlQueryBuilder()->select('id_product')
                 ->from('products_in_sections')
                 ->where('id_product = p.id')
                 ->andWhere(Operator::inIntArray($sectionIds, 'id_section'));

            return Operator::exists(
                $existsSubquery
                ->addParameters($existsSubquery->getParameters(), $existsSubquery->getParameterTypes())
            );
        };
    }

    public static function inSectionsRecursive(array $sectionIds)
    {
        return self::inSections(
            call_user_func_array('array_merge', array_map('getDescendantCategories', $sectionIds))
        );
    }

    public static function inSectionsByLevel(array $ancestorAndLevel, array $excludeOptions = [])
    {
        if (isset($excludeOptions['ancestorID']) && isset($excludeOptions['level'])) {
            unset($ancestorAndLevel[$excludeOptions['ancestorID']][$excludeOptions['level']]);
        }

        $sections = [];
        foreach ($ancestorAndLevel as $ancestorID => $data) {
            foreach ($data as $level => $rawSectionIDs) {
                $sections[] = self::inSections(
                    call_user_func_array('array_merge', array_map('getDescendantCategories', $rawSectionIDs)),
                    'ps_'.$ancestorID.'_'.$level
                );
            }
        }

        return Operator::andX($sections);
    }

    public static function inStore($useVariations = false)
    {
        return function (QueryBuilder $qb) use ($useVariations) {
            if ($useVariations) {
                $qb->joinVariationsOnProducts();
                $inStore = $qb->expr()->gt(static::getInStoreField(true, $qb), 0);
            } else {
                $inStore = $qb->expr()->gt(static::getInStoreField(false, $qb), 0);
            }

            return $inStore;
        };
    }

    public static function inStoreVariations()
    {
        return function (QueryBuilder $qb) {
            $qb->joinVariationsOnProducts();

            // $qb->andWhere('pv.id IS NOT NULL AND pv.in_store > 0');
            $inStore = $qb->expr()->gt('COALESCE(pv.in_store, 1)', 0);

            return $inStore;
        };
    }

    public static function inStoreSupplier($useVariations = false)
    {
        if (!findModule(\Modules::PRODUCTS_SUPPLIERS)) {
            return null;
        }

        return function (QueryBuilder $qb) use ($useVariations) {
            $suppliers = sqlQueryBuilder()->select('1')
                ->from('products_of_suppliers', 'pf_pos')
                ->andWhere('p.id = pf_pos.id_product')
                ->andWhere('pf_pos.in_store > 0');
            if ($useVariations) {
                $qb->joinVariationsOnProducts();
                $suppliers->andWhere('(pv.id IS NULL and pf_pos.id_variation IS NULL) OR (pv.id=pf_pos.id_variation)');
            }

            return Operator::exists($suppliers);
        };
    }

    public static function inStoreSeller(): callable
    {
        if (!findModule(\Modules::SELLERS) || !($storeId = Contexts::get(SellerContext::class)->getActive()['id_store'] ?? null)) {
            return self::inStore();
        }

        return Filter::byStores([$storeId]);
    }

    public static function withOneProductPhotoId($variationsAsResult = false, $imageKind = null, bool $fallbackToProductPhoto = true)
    {
        return function (QueryBuilder $qb) use ($variationsAsResult, $imageKind, $fallbackToProductPhoto) {
            $photoQb = sqlQueryBuilder()
                    ->from('products', 'p2')
                    ->leftJoin('p2', 'products_variations', 'pv2', 'pv2.id_product = p2.id')
                    ->where('p2.id = p.id AND ((pv.id IS NULL) OR pv.id = pv2.id)')
                    ->addSelect(self::withProductPhotoId($variationsAsResult, $imageKind, $fallbackToProductPhoto, 'p2', 'pv2', false))
                    ->setMaxResults(1);
            $qb->addQueryBuilderParameters($photoQb);

            return '('.$photoQb->getSQL().') as id_photo';
        };
    }

    public static function withProductPhotoId($variationsAsResult = false, $imageKind = null, bool $fallbackToProductPhoto = true, $aP = 'p', $aPV = 'pv', $selectAdditional = true)
    {
        return function (QueryBuilder $qb) use ($selectAdditional, $variationsAsResult, $imageKind, $fallbackToProductPhoto, $aP, $aPV) {
            $addSelects = [];
            if ($selectAdditional) {
                $addSelects = [['date_update', 'id_photo_update']];
            }
            $joinCondition = "{$aP}.id=ppr.id_product";
            if (findModule(\Modules::PRODUCTS_VARIATIONS_PHOTOS)) {
                if (is_null($imageKind)) {
                    $imageKind = $variationsAsResult ? 'N' : 'Y';
                }
                if ($variationsAsResult) {
                    // use variation photo if present
                    // fallback to product's main photo in case product has no variation
                    $joinCondition .= " AND (ppr.id_variation = {$aPV}.id OR ({$aPV}.id IS NULL AND ppr.show_in_lead='Y'))";
                } else {
                    $joinCondition .= " AND ppr.show_in_lead='{$imageKind}'";
                }
                if ($fallbackToProductPhoto) {
                    // fallback to product photo when variation exists, but has no photo
                    $qb->leftJoin($aP, 'photos_products_relation', 'ppr2', "{$aP}.id=ppr2.id_product AND ppr2.show_in_lead='Y'");
                    $qb->leftJoin('ppr2', 'photos', 'ph2', 'ph2.id = ppr2.id_photo');
                    $aliasesToSelect = ['ph', 'ph2'];
                } else {
                    $aliasesToSelect = ['ph'];
                }
            } else {
                $joinCondition .= " AND ppr.show_in_lead='Y'";
                $aliasesToSelect = ['ph'];
            }

            $qb->leftJoin($aP, 'photos_products_relation', 'ppr', $joinCondition); // + ppr.id_variation
            $qb->leftJoin('ppr', 'photos', 'ph', 'ph.id = ppr.id_photo');
            $qb->setForceIndexForJoin('ph', 'PRIMARY');

            $createSelect = function ($field, $alias = null) use ($aliasesToSelect) {
                return 'COALESCE('.implode(
                    ',',
                    array_map(
                        function ($val) use ($field) {
                            return $val.".{$field}";
                        },
                        $aliasesToSelect
                    )
                ).') as '.($alias ?? $field);
            };

            $selects = $createSelect('id', 'id_photo');
            foreach ($addSelects as $aS) {
                $selects .= ', '.$createSelect($aS[0], $aS[1] ?? null);
            }

            if ($selectAdditional) {
                $qb->andWhere(Translation::coalesceTranslatedFields(PhotosTranslation::class, ['descr' => 'descr_photo']));
            }

            return $selects;
        };
    }

    public static function withVatAndDiscount(QueryBuilder $qb, $priceField = 'p.price')
    {
        $qb->joinVariationsOnProducts();

        $discountField = 'p.discount';

        if (findModule(\Modules::PRICELISTS)) {
            $pricelistContext = ServiceContainer::getService(PricelistContext::class);
            if ($pricelistContext->getActiveId()) {
                $qb->andWhere(\KupShop\PricelistBundle\Query\Product::applyPricelist($pricelistContext->getActiveId()))
                    ->leftJoin('prl', 'currencies', 'c', 'c.id=prl.currency');

                $priceField = 'COALESCE(prlv.price*c.rate, pv.price, prlp.price*c.rate, p.price)';
                $discountField = 'COALESCE(IF(prlv.price IS NULL, NULL, prlp.discount), IF(pv.price IS NULL, NULL, p.discount), IF(prlp.price IS NULL, NULL, prlp.discount), IF(p.price IS NULL, NULL, p.discount))';
            }
        }

        return "{$priceField} * (1+v.vat/100) * (1-{$discountField}/100)";
    }

    public static function productsAndVariationsIds(array $items): ?callable
    {
        $specs = [];
        foreach ($items as $productId => $variationId) {
            if (is_array($variationId)) {
                foreach ($variationId as $varId) {
                    $specs[] = Operator::equalsNullable(['p.id' => $productId, 'pv.id' => $varId]);
                }
            } elseif (is_null($variationId)) {
                $specs[] = Operator::equalsNullable(['p.id' => $productId]);
            } else {
                $specs[] = Operator::equalsNullable(['p.id' => $productId, 'pv.id' => $variationId]);
            }
        }

        if (!$specs) {
            return Operator::andX('1 = 0');
        }

        return Operator::orX($specs);
    }

    public static function productsIds($products)
    {
        return function (QueryBuilder $qb) use ($products) {
            $qb->setParameter('products', $products, Connection::PARAM_INT_ARRAY)
                ->andWhere(Operator::inIntArray($products, 'p.id'))
                ->addOrderBy('FIELD(p.id, :products)');
        };
    }

    public static function getProductInStock(QueryBuilder $qb)
    {
        $query = static::getInStoreField(true, $qb);

        if (findModule(\Modules::PRODUCTS_SUPPLIERS) && in_array(\Settings::getDefault()->order_availability, [\Settings::ORDER_AVAILABILITY_IN_STORE_OR_SUPPLIER_STORE, \Settings::ORDER_AVAILABILITY_ALL])) {
            // in stock = in_store (pokud je > 0) + skladem u dodavatele (pokud je > 0)
            // aby nedochazelo k dvojitemu odecitani skladovosti (sklad + pos)
            if (!findModule(\Modules::PRODUCTS_SUPPLIERS, \Modules::SUB_ALLOW_NEGATIVE_IN_STORE)) {
                $query = "GREATEST(0, {$query})";
            }
            $query .= ' + (SELECT GREATEST(0, COALESCE(SUM(pos.in_store), 0)) FROM products_of_suppliers pos WHERE p.id = pos.id_product AND ((pv.id IS NULL and pos.id_variation IS NULL) OR (pv.id=pos.id_variation)))';
        }

        if (findModule('products', 'showMax')) {
            $cfg = Config::get();
            $query = 'LEAST('.$query.', COALESCE(pv.in_store_show_max, p.in_store_show_max, '.$cfg['Modules']['products']['showMax'].'))';
        }

        // aby se na FE nedostala zaporna skladovost, minimum je 0
        if (findModule(\Modules::PRODUCTS_SUPPLIERS, \Modules::SUB_ALLOW_NEGATIVE_IN_STORE)) {
            $query = "GREATEST(0, {$query})";
        }

        return $query;
    }

    public static function getInStoreField($useVariations = true, $qb = null)
    {
        $field = 'in_store';
        if ($callable = findModule('products', 'in_store_field')) {
            if (is_callable($callable)) {
                $field = call_user_func($callable, $useVariations, $qb);
            }
        }

        if ($callable = findModule('products', 'in_store_spec')) {
            if (is_callable($callable)) {
                return call_user_func($callable, $useVariations, $qb);
            }
        }

        return static::applyProductReservationsOnInStoreField(
            $useVariations ? "COALESCE(pv.{$field}, p.{$field})" : 'p.'.$field,
            $useVariations
        );
    }

    public static function getStoresQuantityField(QueryBuilder $qb): string
    {
        if ($callable = findModule('products', 'stores_quantity_spec')) {
            if (is_callable($callable)) {
                return call_user_func($callable, $qb);
            }
        }

        return 'SUM(si.quantity)';
    }

    public static function getDiscountField(&$spec): string
    {
        $spec = null;
        $discountField = 'p.discount';
        if (findModule(\Modules::PRICE_HISTORY)) {
            $price = 'p.price';
            $price_for_discount = 'p.price_for_discount';
            $price_common = 'p.price_common';
            if (findModule(\Modules::PRODUCTS_VARIATIONS)) {
                $spec = function (QueryBuilder $qb) {
                    $qb->joinVariationsOnProducts();
                };
                $price = 'COALESCE(pv.price, p.price)';
                $price_for_discount = 'COALESCE(pv.price_for_discount, p.price_for_discount)';
                $price_common = 'COALESCE(pv.price_common, p.price_common)';
            }
            if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_COMMON)) {
                // pokud je price_for_discount deaktivovana, vypocitat slevu z price_common
                $price_for_discount = "IF({$price_for_discount} = -1, {$price_common}, {$price_for_discount})";
            }
            $discountField = "IF({$price_for_discount} > 0, (100 - {$price}*(100-p.discount)/{$price_for_discount}), p.discount)";
            $display_discount = \Settings::getDefault()['prod_display_discount'] ?? null;
            if ($display_discount == 'Y') {
                $discountField = "IF(p.discount > 0, {$discountField}, 0)";
            }
            $display_discount = \Settings::getDefault()['prod_display_discount_price_common'] ?? null;
            if ($display_discount == 'Y') {
                $discountField = "IF({$price_common} > 0, {$discountField}, 0)";
            }
        }

        return $discountField;
    }

    public static function withVat()
    {
        $vatContext = Contexts::get(VatContext::class);

        if ($vatContext->isOssActive()) {
            return function (QueryBuilder $qb) {
                $defaultVat = Contexts::get(VatContext::class)->getDefault();
                $qb->joinVatsOnProducts()
                    ->addSelect('COALESCE(v.id, :default_vat) vat, p.vat AS original_vat')
                    ->setParameter('default_vat', $defaultVat['id']);
            };
        } else {
            return function (QueryBuilder $qb) {
                $qb->addSelect('p.vat');
            };
        }
    }

    public static function withVats($countries)
    {
        return function (QueryBuilder $qb) use ($countries) {
            $defaultCn = \Settings::getDefault()['oss_vats']['default'] ?? null;
            foreach ($countries as $country) {
                $qbVat = sqlQueryBuilder()->select('vv.vat')->from('vats', 'vv')
                    ->leftJoin('vv', 'vats_cns', 'vatcns', 'vv.id = vatcns.id_vat')
                    ->andWhere('id_cn = COALESCE(p.id_cn, :defaultCn) AND id_country = :country_'.$country)
                    ->setParameter('defaultCn', $defaultCn)
                    ->setParameter('country_'.$country, $country);
                $qb->addSubselect($qbVat, 'vat_'.$country);
            }
        };
    }

    public static function allowBuyNotInStore()
    {
        // vraci spec s delivery_time, ktere nemaji povolen nakup produktu a variant co nejsou skladem (in_store = 0)
        $deliveryTimeCfg = Config::get()['Products']['DeliveryTimeConfig'] ?? [];
        if (!empty($deliveryTimeCfg)) {
            $notAllowed = array_filter($deliveryTimeCfg, function ($key) {
                if (!array_key_exists('allowBuy', $key)) {
                    return false;
                }

                return !$key['allowBuy'];
            });

            return Operator::orX(
                Operator::inIntArray(array_keys($notAllowed), 'p.delivery_time'),
                Operator::inIntArray(array_keys($notAllowed), 'pv.delivery_time'));
        }

        return Operator::andX('0=0');
    }

    protected static function applyProductReservationsOnInStoreField(string $inStoreField, bool $useVariations): string
    {
        if (!findModule(\Modules::PRODUCT_RESERVATIONS)) {
            return $inStoreField;
        }

        $subquery = sqlQueryBuilder()
            ->select('COALESCE(SUM(pr.quantity), 0)')
            ->from('product_reservations', 'pr')
            ->andWhere($useVariations ? 'pv.id = pr.id_variation' : 'p.id = pr.id_product');

        if ($ignoredTypes = Contexts::get(UserContext::class)->getIgnoredReservationTypes()) {
            $subquery->andWhere(
                Operator::not('pr.type IN ('.implode(',', array_map(fn ($x) => '"'.$x.'"', $ignoredTypes)).')')
            );
        }

        return "({$inStoreField} - ({$subquery->getSQL()}))";
    }

    public static function generatedCoupons()
    {
        return Operator::equals([JsonOperator::value('p.data', 'generate_coupon') => 'Y']);
    }
}