<?php


namespace Shop\Infrastructure\Repositories\Product;


use Diamond\Repositories\PropelAbstractRepository;
use Propel\Runtime\ActiveQuery\Criteria;
use Propel\Runtime\ActiveQuery\Join;
use Propel\Runtime\ActiveQuery\ModelCriteria;
use Propel\Runtime\Collection\ArrayCollection;
use Propel\Runtime\Propel;
use Repo\CollectionInterface;
use Repo\CrudRepositoryBuilderInterface;
use Repo\EntityInterface;
use Repo\PaginationInterface;
use Shop\Contracts\CatalogFilterInterface;
use Shop\Domain\Product\Contracts\ProductCriteriaInterface;
use Shop\Domain\Product\Contracts\ProductRepositoryInterface;
use Shop\Domain\Product\ProductCollection;
use Shop\Infrastructure\Models\PriceAccess\PriceAccessQuery;
use Shop\Infrastructure\Models\Product\Map\ProductTableMap;
use Shop\Infrastructure\Models\Product\Product;
use Shop\Infrastructure\Models\Product\ProductQuery;
use Shop\Infrastructure\Models\ProductGroup\ProductGroupQuery;

class ProductRepository extends PropelAbstractRepository implements ProductRepositoryInterface
{
    public static function createEntity(): EntityInterface
    {
        return new Product();
    }

    /**
     * @return ModelCriteria|ProductQuery
     */
    protected function createQuery(): ModelCriteria
    {
        return ProductQuery::create();
    }

    /**
     * @param PaginationInterface|ProductCriteria $criteria
     * @param ModelCriteria|ProductQuery $dbCriteria
     */
    protected function modifyCriteria(PaginationInterface $criteria, ModelCriteria $dbCriteria): void
    {

        $dbCriteria
            //признак новинки
            ->addAsColumn('is_new', sprintf('udate > DATE_SUB(CURRENT_DATE, INTERVAL %s DAY) AND `shop_positions`.`sale_id` IS NULL', (int)$criteria->getNewGoodDays()))
            

            ->usePositionQuery(null,'LEFT JOIN')

            ->_if($criteria->getSortByBrandsPriority() !== null)
            ->useBrandQuery()
            ->addAsColumn('brand_priority', '`shop_brands`.name IN("' . implode('","' , $criteria->getSortByBrandsPriority()??[] ) . '")')
            ->orderBy('brand_priority', 'DESC')
            ->endUse()
            ->_endif()
            ->_if($criteria->getSortByHitSale() !== null)
            ->addAsColumn('hit_sale', '`shop_product_hit_sale`.product_id')
            ->leftJoinProductHitSale()
            ->orderBy('hit_sale', $criteria->getSortByHitSale())
            ->endUse()
            ->_endif();



        $dbCriteria
            //filter by sale
            ->_if($criteria->getFilterBySaleId() !== null)
            ->usePositionQuery(null,'LEFT JOIN')
            ->filterBySaleId($criteria->getFilterBySaleId())
            ->endUse()
            ->_endif()

            //FILTER by Size types
            ->_if($criteria->getFilterBySizeTypeIds() !== null && $criteria->getFilterBySizeValues() !== null)
            ->usePositionQuery(null,'LEFT JOIN')
            ->useShopPositionSizeQuery()
            ->filterBySizeTypeId($criteria->getFilterBySizeTypeIds())
            ->filterByValue($criteria->getFilterBySizeValues())
            ->endUse()
            ->endUse()
            ->_endif()
;


            //FILTER BY GROUPS
        $dbCriteria
            ->_if($criteria->getFilterByGroupIds() !== null)
            ->useShopProductGroupsRelQuery()
            ->filterByGroupId($criteria->getFilterByGroupIds())
            ->endUse()
            ->_endif()
            //-------------
            ->_if($criteria->getFilterBySpecial() !== null)
            ->filterBySpecial($criteria->getFilterBySpecial() === true ? 'Y' : 'N')
            ->_endif()
            //-------------
            ->_if($criteria->getFilterByActive() !== null)
            ->filterByActive($criteria->getFilterByActive() === true ? 'Y' : 'N')
            ->_endif()
            //search article
            ->_if($criteria->getSearchByArticle() !== null)
            ->filterByArticle('%' . $criteria->getSearchByArticle() . '%', Criteria::LIKE)
            ->_endif()
            //brand query

            //FILTER BY CATEGORY
            ->_if($criteria->getFilterByCategoryId() !== null)
            ->filterByCategoryId($criteria->getFilterByCategoryId())
            ->_endif()
            ->_if($criteria->getFilterByCategoryIds() !== null)
            ->filterByCategoryId($criteria->getFilterByCategoryIds())
            ->_endif();



            //-----------
            //важно не только значение но чтобы был данные в фильтре
        $dbCriteria
            ->_if(
                ($criteria->getFilterByIgnoreCategories() !== null && count($criteria->getFilterByIgnoreCategories())) ||
                $criteria->getFilterByCategoryParentIds() !== null
            )
            ->useCategoryQuery()
            ->_if($criteria->getFilterByCategoryParentIds() !== null)
            ->filterByParentId($criteria->getFilterByCategoryParentIds())
            ->_endif()
            ->filterById($criteria->getFilterByIgnoreCategories(), Criteria::NOT_IN)
            ->_and()
            ->filterByParentId($criteria->getFilterByIgnoreCategories(), Criteria::NOT_IN)
            ->_or()
            ->filterByParentId(NULL)
            ->endUse()
            ->_endif();

            //FILTER BY BRAND ID
        $dbCriteria
            ->_if($criteria->getFilterByBrandId() !== null)
            ->filterByBrandId($criteria->getFilterByBrandId())
            ->_endif()
            ->_if($criteria->getFilterByBrandIds() !== null)
            ->filterByBrandId($criteria->getFilterByBrandIds())
            ->_endif()
            //brand names
            ->_if($criteria->getFilterByBrandNames() ||
                $criteria->getSearchByBrand() !== null)
            ->useBrandQuery()
            ->_if($criteria->getFilterByBrandNames() !== null)
            ->filterByName($criteria->getFilterByBrandNames())
            ->_endif()
            ->_if($criteria->getSearchByBrand() !== null)
            ->filterByName('%' . $criteria->getSearchByBrand() . '%', Criteria::LIKE)
            ->_endif()
            ->endUse()
            ->_endif();
            //--------





        $dbCriteria
            ->_if($criteria->getSortedByPrice() !== null ||
                $criteria->getFilterByRemains() === true ||
                $criteria->getFilterByPriceListScheetIds() !== null)
            ->leftJoinPosition()

            //remains
            ->_if($criteria->getFilterByRemains() === true)
            ->filterByRemain(0, Criteria::GREATER_THAN)
            ->filterByPrice(0, Criteria::GREATER_THAN)
            ->_endif()
            //price cheets
            ->_if($criteria->getFilterByPriceListScheetIds() !== null)
            ->filterByPricelistSheetId($criteria->getFilterByPriceListScheetIds())
            ->_endif()

            ->_endif()

            //----------------
            //image
            ->_if($criteria->getFilterByImageExist() === true)
            ->filterByImg('', Criteria::GREATER_THAN)
            ->_or()
            ->usePositionQuery(null,'LEFT JOIN')
            ->filterByImage('', Criteria::GREATER_THAN)
            ->endUse()
            ->_endif()
            ->_if($criteria->getFilterByImageExist() === false)
            ->filterByImg('')
            ->_or()
            ->usePositionQuery(null,'LEFT JOIN')
            ->filterByImage('')
            ->endUse()
            ->_endif()
            //-----------
            //filter by date create
            ->_if($criteria->getFilterByDateCreate() !== null)
            ->filterBydateCreate([
                'min' => $criteria->getFilterByDateCreate()
            ])
            ->_endif()
            //-----------
            ->_if($criteria->getFilterByDateUpdate() !== null)
            ->filterBydateUpdate([
                'min' => $criteria->getFilterByDateUpdate()
            ])
            ->_endif()
            //-----------
            ->_if($criteria->getFilterByNewGoodDays() === true)
            //->filterBydateCreate(sprintf( 'cdate >= DATE_SUB(CURRENT_DATE, INTERVAL %s DAY)',$criteria->getNewGoodDays()))
            ->filterBy('dateUpdate', sprintf('udate > DATE_SUB(CURRENT_DATE, INTERVAL %s DAY) AND `shop_positions`.`sale_id` IS NULL', (int)$criteria->getNewGoodDays()), Criteria::CUSTOM)
            //->orderBydateUpdate(Criteria::DESC)
            ->_endif()
            //search by name
            ->_if($criteria->getSearchByTitle() !== null)
            ->filterByTitle('%' . $criteria->getSearchByTitle() . '%', Criteria::LIKE)
            ->_endif()
            //
            ->_if($criteria->getFilterByweights() !== null)
            ->filterByWeight($criteria->getFilterByweights())
            ->_endif()
            ->_if($criteria->getFilterByweight() !== null)
            ->filterByWeight($criteria->getFilterByweight())
            ->_endif()

            ->_if($criteria->getFilterByArticle() !== null)
            ->filterByArticle($criteria->getFilterByArticle())
            ->_endif()
            ->_if($criteria->getFilterByBrandName() !== null)
            ->useBrandQuery()
            ->filterByName($criteria->getFilterByBrandName())
            ->endUse()
            ->_endif()
            //------------------------------------------------
            //SORT--------------------------------------------
            //------------------------------------------------

            ->_if($criteria->getFilterByPriceMax())
            ->filterByPrice($criteria->getFilterByPriceMax(), Criteria::LESS_EQUAL)
            ->_endif()
            ->_if($criteria->getFilterByPriceMin())
            ->filterByPrice($criteria->getFilterByPriceMin(),Criteria::GREATER_EQUAL)
            ->_endif()
            //сортировка по цене
            ->_if($criteria->getSortedByPrice() !== null)
            ->usePositionQuery(null,'LEFT JOIN')
            ->orderByPrice($criteria->getSortedByPrice())
            ->endUse()
            ->orderByPrice($criteria->getSortedByPrice())
            ->_endif()
            //sorted by date create
            ->_if($criteria->getSortedByDateCreate() !== null)
            ->orderBydateCreate($criteria->getSortedByDateCreate())
            ->_endif()
            //sorted by date create
            ->_if($criteria->getSortedByDateUpdate() !== null)
            ->orderBydateUpdate($criteria->getSortedByDateUpdate())
            ->_endif()
            //randomsort
            ->_if($criteria->getSortedRandom() === true)
            ->addAscendingOrderByColumn('rand()')
            ->_endif()

            //destinations
            ->_if($criteria->getFilterByDestinations() !== null)
            //group
            ->usePositionQuery(null,'LEFT JOIN')
            ->usePricelistSheetQuery()
            ->usePricelistFileQuery()
            ->useProviderQuery()
            ->filterByDestination($criteria->getFilterByDestinations())
            ->endUse()
            ->endUse()
            ->endUse()
            ->endUse()
            ->_endif()

            //stocks
            ->_if($criteria->getSetFilterByStockIds() !== null)
            ->usePositionQuery(null,'LEFT JOIN')
            ->addJoinObject(new Join('pricelist_sheet_id', ' price_scheet_id', 'INNER JOIN shop_price_access'))
            ->addCond('cond1', 'shop_id', $criteria->getSetFilterByStockIds(), Criteria::IN)
            ->where(['cond1'], 'AND')
            ->endUse()
            ->_endif()
            ->groupById();
        
    }
    
    protected function createCollection(): CollectionInterface
    {
        return new ProductCollection();
    }


    /**
     * @param array $row
     * @param Product|EntityInterface|null $entity
     * @return EntityInterface
     */
    public function buildEntityFromArray(array $row, EntityInterface $entity = null): EntityInterface
    {
        $entity = parent::buildEntityFromArray($row, $entity);

        if (!isset($row['groups'])) {
            $row['groups'] = [];
        }

        $groups = ProductGroupQuery::create()->filterByPrimaryKeys($row['groups'])->find();
        $entity->setProductGroups($groups);

        return $entity;
    }


    /**
     * @param ProductCriteriaInterface $criteria
     * @return array
     * @throws \Propel\Runtime\Exception\PropelException
     */
    public function findMinMaxPricesByFilter(ProductCriteriaInterface $criteria): array
    {
        $db = $this->createQuery();
        $db->select(['prod.price']);
        $new = clone $db;

        $this->modifyCriteria($criteria, $db);

        $query2 = $new
            ->select('max_price')
            ->addAsColumn('max_price', 'MAX(prod.price * 1)')
            ->select('min_price')
            ->addAsColumn('min_price', 'MIN(prod.price * 1)')
            ->addSelectQuery($db, 'prod');

        return $query2->findOne();
    }

    /**
     * @param ProductCriteriaInterface $criteria
     * @return array
     */
    public function findProductWeightsByCriteria(ProductCriteriaInterface $criteria): array
    {

        $query = 'SELECT
  p.weight
FROM shop_positions pp
  INNER JOIN shop_products p ON pp.product_id = p.id
   INNER JOIN shop_brands b ON p.brand_id = b.id
   WHERE p.weight > 0 ';

        if (is_array($criteria->getFilterByPriceListScheetIds()) && count($criteria->getFilterByPriceListScheetIds())) {
            $query .= 'AND pp.pricelist_sheet_id IN(' . implode(',', $criteria->getFilterByPriceListScheetIds()) . ')';
        }

        if ($criteria->getFilterByImageExist() === true) {
            $query .= "AND p.img > ''";
        }

        if (is_array($criteria->getFilterByBrandNames()) && count($criteria->getFilterByBrandNames())) {
            $query .= sprintf("AND b.name IN('%s')", implode("','", $criteria->getFilterByBrandNames()));
        }

        if ($criteria->getFilterByCategoryId() > 0) {
            $query .= "AND p.category_id = " . $criteria->getFilterByCategoryId();
        }

        if ($criteria->getFilterByRemains() > 0) {
            $query .= 'AND pp.price > 0 AND pp.remain > 0';
        }

        $query .= ' GROUP BY p.weight ORDER BY p.weight ASC';

        $con = Propel::getConnection(ProductTableMap::DATABASE_NAME);
        $stmt2 = $con->prepare($query);
        if ($stmt2->execute()) {
            return $stmt2->fetchAll(\PDO::FETCH_COLUMN);
        }

        return [];
    }


    /**
     * Получение скидочных товаров по разделам
     * @param CatalogFilterInterface $catalogFilter
     * @param int $limit
     * @return ProductCollection
     * @throws \Repo\Concrete\Exceptions\Collection
     */
    public function findSalesProductsByCategories(ProductCriteriaInterface $catalogFilter, int $limit = 10): ProductCollection
    {
        $results = $this->buildQuery()
            ->join('Position')
            ->useCategoryQuery()
            ->filterByParentCategory($catalogFilter->getFilterByCategoryId())
            ->endUse()
            ->usePositionQuery()
            ->filterByPricelistSheetId($catalogFilter->getFilterByPriceListScheetIds())
            ->filterByRemain(0, Criteria::GREATER_THAN)
            ->endUse()
            ->limit($limit)
            ->addAscendingOrderByColumn('rand()')
            ->filterByImg('', Criteria::GREATER_THAN)
            ->groupById()
            ->find();

        $collection = new ProductCollection();

        foreach ($results as $row) {
            $collection->push($row);
        }
        return $collection;
    }

    /**
     *
     * @param CatalogFilterInterface $catalogFilter
     * @return type
     */
    public function findSizesByCategoryIdAndTypeId(ProductCriteriaInterface $criteria): ArrayCollection
    {

        $dbCriteria = $this->createQuery();

        $this->modifyCriteria($criteria, $dbCriteria);

        $results = $dbCriteria
            ->usePositionQuery()
            ->useShopPositionSizeQuery()
            ->groupByValue()
            ->useShopPositionSizeTypeQuery()
            ->filterById($criteria->getFilterBySizeTypeIds())
            ->endUse()
            ->endUse()
            ->endUse()
            ->withColumn("shop_position_sizes.value", "color_name")
            ->select('color_name')
            ->find();

        return $results;
    }

    /**
     *
     * @param type $shopId
     * @param type $categ_id
     */
    public function findBrandsByCategoryId(ProductCriteriaInterface $criteria): ProductCollection
    {
        $dbCriteria = $this->createQuery();

        $this->modifyCriteria($criteria, $dbCriteria);

        $query1 = $dbCriteria;

        $query1
            ->useBrandQuery()
            ->withColumn("shop_brands.name", "brand_name")
            ->endUse()
           // ->select(['brand_name','*'])
            ->addSelfSelectColumns(true)
            ->setLimit(9999)
            ->groupByBrandId();

        $query2 = $this->createQuery();

        $query2
            ->addAsColumn('brand_name', 'prod.brand_name')
            ->addSelectQuery($query1, 'prod')
            ->addAsColumn('count', 'COUNT(prod.id)')
            ->groupByBrandId()
            ->orderBy("prod.brand_name");


        $results = $query2->find();

        $collection = new ProductCollection();

        foreach ($results as $result) {
            $collection->push($result);
        }

        return $collection;
    }

    /**
     * Получение товаров с группировкой по сезонам
     * @param CatalogFilterInterface $catalogFilter
     * @return type
     */
    public function findSeazonsByCategoryId(ProductCriteriaInterface $criteria): ProductCollection
    {
        $dbCriteria = $this->createQuery();

        $this->modifyCriteria($criteria, $dbCriteria);

        $results = $dbCriteria
            ->useShopProductGroupsRelQuery()
            ->groupByGroupId()
            ->endUse()
            ->find();

        $collection = new ProductCollection();

        foreach ($results as $result) {
            $collection->push($result);
        }

        return $collection;
    }

    /**
     * @param ProductCriteriaInterface $criteria
     * @return array
     */
    public function findProductDestinationByCriteria(ProductCriteriaInterface $criteria): array
    {
        $query = 'SELECT
  prv.destination
FROM shop_positions pp
  INNER JOIN shop_products p
    ON pp.product_id = p.id
  INNER JOIN shop_brands b
    ON p.brand_id = b.id
  INNER JOIN shop_pricelist_sheets ps
    ON pp.pricelist_sheet_id = ps.id
  INNER JOIN shop_pricelist_files pf
    ON ps.pricelist_file_id = pf.id
  INNER JOIN shop_providers prv
    ON pf.provider_id = prv.id

WHERE prv.destination > ""';

        if (is_array($criteria->getFilterByPriceListScheetIds()) && count($criteria->getFilterByPriceListScheetIds())) {
            $query .= ' AND pp.pricelist_sheet_id IN(' . implode(',', $criteria->getFilterByPriceListScheetIds()) . ')';
        }

        if ($criteria->getFilterByImageExist() === true) {
            $query .= " AND p.img > ''";
        }

        if (is_array($criteria->getFilterByBrandNames()) && count($criteria->getFilterByBrandNames())) {
            $query .= sprintf(" AND b.name IN('%s')", implode("','", $criteria->getFilterByBrandNames()));
        }

        if ($criteria->getFilterByCategoryId() > 0) {
            $query .= " AND p.category_id = " . $criteria->getFilterByCategoryId();
        }

        if ($criteria->getFilterByRemains() > 0) {
            $query .= ' AND pp.price > 0 AND pp.remain > 0';
        }

        $query .= ' GROUP BY prv.destination';

        $con = Propel::getConnection(ProductTableMap::DATABASE_NAME);
        $stmt2 = $con->prepare($query);
        if ($stmt2->execute()) {
            return $stmt2->fetchAll(\PDO::FETCH_COLUMN);
        }

        return [];
    }

    /**
     * @param ProductCriteriaInterface $criteria
     * @return ProductCollection
     */
    public function findProductsNamesByCriteria(ProductCriteriaInterface $criteria): array
    {
        $query = 'SELECT
  p.title, pp.image, p.id
FROM shop_positions pp
  INNER JOIN shop_products p
    ON pp.product_id = p.id
  INNER JOIN shop_brands b
    ON p.brand_id = b.id
  INNER JOIN shop_pricelist_sheets ps
    ON pp.pricelist_sheet_id = ps.id
  INNER JOIN shop_pricelist_files pf
    ON ps.pricelist_file_id = pf.id
  INNER JOIN shop_providers prv
    ON pf.provider_id = prv.id

WHERE p.title LIKE "' . $criteria->getSearchByTitle() . '%"';

        if (is_array($criteria->getFilterByPriceListScheetIds()) && count($criteria->getFilterByPriceListScheetIds())) {
            $query .= ' AND pp.pricelist_sheet_id IN(' . implode(',', $criteria->getFilterByPriceListScheetIds()) . ')';
        }

        if ($criteria->getFilterByImageExist() === true) {
            $query .= " AND p.img > ''";
        }

        if (is_array($criteria->getFilterByBrandNames()) && count($criteria->getFilterByBrandNames())) {
            $query .= sprintf(" AND b.name IN('%s')", implode("','", $criteria->getFilterByBrandNames()));
        }

        if ($criteria->getFilterByCategoryId() > 0) {
            $query .= " AND p.category_id = " . $criteria->getFilterByCategoryId();
        }

        if ($criteria->getFilterByRemains() > 0) {
            $query .= ' AND pp.price > 0 AND pp.remain > 0';
        }

        $query .= ' GROUP BY p.title';

        $con = Propel::getConnection(ProductTableMap::DATABASE_NAME);
        $stmt2 = $con->prepare($query);
        if ($stmt2->execute()) {
            return $stmt2->fetchAll(\PDO::FETCH_ASSOC);
        }

        return [];
    }

    /**
     *
     * @param ProductCriteriaInterface $criteria
     * @return array
     */
    public function getStocksByCriteria(ProductCriteriaInterface $criteria): array
    {

        $query = 'SELECT
    ss.id, ss.title
FROM shop_positions pp
         INNER JOIN shop_products p
                    ON pp.product_id = p.id
         INNER JOIN shop_brands b
                    ON p.brand_id = b.id
        INNER JOIN shop_price_access spa
                    ON pp.pricelist_sheet_id = spa.price_scheet_id
        INNER JOIN shop_shops ss
                    ON spa.shop_id = ss.id';

        if (is_array($criteria->getFilterByPriceListScheetIds()) && count($criteria->getFilterByPriceListScheetIds())) {
            $query .= ' AND pp.pricelist_sheet_id IN(' . implode(',', $criteria->getFilterByPriceListScheetIds()) . ')';
        }

        if ($criteria->getFilterByImageExist() === true) {
            $query .= " AND p.img > ''";
        }

        if (is_array($criteria->getFilterByBrandNames()) && count($criteria->getFilterByBrandNames())) {
            $query .= sprintf(" AND b.name IN('%s')", implode("','", $criteria->getFilterByBrandNames()));
        }

        if ($criteria->getFilterByCategoryId() > 0) {
            $query .= " AND p.category_id = " . $criteria->getFilterByCategoryId();
        }

        if ($criteria->getFilterByRemains() > 0) {
            $query .= ' AND pp.price > 0 AND pp.remain > 0';
        }

        $query .= ' GROUP BY ss.id';

        $con = Propel::getConnection(ProductTableMap::DATABASE_NAME);
        $stmt2 = $con->prepare($query);
        if ($stmt2->execute()) {
            $result = [];
            foreach ($stmt2->fetchAll(\PDO::FETCH_ASSOC) as $res) {
                $result[$res['id']] = $res['title'];
            }

            return $result;
        }

        return [];
    }

}